Excel学习

例子可在文件-新建-模板中找到。

*快速填充

ctrl-e，需要将内容融入一个表格内，插入-表格，然后关掉数据-筛选，就可以填充了。

ctrl-D，选中单元格，然后快捷键向下填充。

*快速分析

ctrl-q，开始-条件格式

规范录入数据：横向录入是规范的，tab，shift+tab左移，shift-enter上移

时间：用 - 或者 / 隔开，数据-筛选中可以进行日期筛选。开始-常规中进行短日期和长日期的筛选

ctrl+;直接输入今天日期，ctrl+shift+;当前时间，先ctrl+;，空格，再ctrl+shift+;

=now()函数显示时间

百分比：开始-常规-百分比添加百分比，在其下方有增加小数位数和减少小数位数，或者设置单元格式为百分比，设置小数位数

分数录入：前面需要一个0，然后空格2，例如0 1/5。或者设置单元格格式为分数

身份证：数字有效精读最长15位。在身份证号前面先输入’，或者调整成文本。

原位填充：ctrl-enter，选中所有需要输入的单元格，输入第一个内容，然后按快捷键

*数据收集

好用的软件，简道云

输入提示：excel的规范行为人的填写。选中一列单元格，数据-数据验证-输入信息-勾选选定单元格时显示输入信息-填写表格和输入信息

下拉列表：数据-数据验证-设置-序列-来源中，来源中，用英文逗号隔开，例如’男,女’

数据验证：数据验证-设置-循序：文本长度-等于-18位，另外需要加出错警告

单行数据多个excel文件进行数据合并，多表合并：新建表格-数据-新建查询-从文件夹-选择文件夹-组合-合并和加载-选择sheet-选择小三角形，将空白的勾去掉

*数据爬取

网页爬取（仅限表格）：新建查询-自Web-加载到-选择位置，右键可以进行刷新。

自动刷新：数据-全部刷新下的小三角-连接属性-刷新频率。

*数据分析

修改字段刷新后字段会发生变化，点击选项卡的查询-编辑，会弹出一个编辑器，在编辑器中进行字段名修改。筛选出需要的数据，修改单元格格式为数字可进行排序。关闭并上载即可得到需要的数据。

1. 检查原表>检查是否存在数据缺失/异常值/不规范等情况。进行标黄处理
2. 确认问题>和业务团队确认是否有收集信息的疏漏
3. 清洗数据>评估并决定如何进行处理
4. 普通区域创建表>快捷键Ctrl+T（创建超级表，选项卡可出现设计。设计-转换为区域可去掉超级表）

创建超级表并调用Power Query：选中单元格-插入-表格-数据选项卡-来自表格区域（调用PQ），之后进行数据筛选。

拆分列：在PQ中，点击拆分列，按分隔符。

自定义列：新列名，=if [金额]>100 then [金额]*0.95 else [金额]

点击文件，关闭并上载至。

PQ可以记录我们操作的任意步骤，出错时可以回溯。

*单元格格式

1. 文本>默认左对齐，文本不可参与计算
2. 数值>默认右对齐，数值计算精度是15位
3. 逻辑值>默认居中对齐，True和False既能用来判断，也能参与计算。

单元格常规格式：设置单元格格式(ctrl+1)

1. 对齐居中：选中单元格-启动器-对齐-跨列居中
2. 对齐中自动换行，完整显示单元格内容
3. 关闭网格线：页面布局-网格线-查看

单元格数字格式：编辑栏中显示没有格式的数据

1. 开始-数字组-常规、数字、货币
2. 如果显示为“#####”，可能是单元格长度不够，可以进行拖拽调整；或者双击字母；或者选中多列-开始-格式-自动调整列宽
3. 自定义格式：自定义通用格式

数字/文本格式代码：

1. 三个常用的数值占位符：0 # ？。

   • 0.0：小数位数多于0的数量，自动四舍五入至0的数位。
   • 000.000：当数字比代码的数量少时显示无意义的0
   • ###.###：#作为数字占位符，只显示有意义的数值
   • ???.???：?以空格代替0，可用于小数点对齐及分数显示

2. 文本占位符：文本重复@ ，@“同学”

3. 0;-0;0;@：可用于数值隐藏，自定义格式。利用数字格式4个区域的定义 (正数:负数:零值:文本)，可以对各区域内容独立定义。

4. 颜色：[红色]000.000;[绿色]0;0;[蓝色]@“同学”。

   可供选择的颜色：[黑色][白色][红色][绿色][蓝色] [黄色][洋红色]

5. <=>条件组成比较运算符和值：[绿色][>=80]0;[红色][<60]0;0

   • [条件1];[条件2];[其他]
   • [条件];[其他]

6. 日期：y-m-d h:m:s 通过改变代码的数量和顺序改变日期的显示方式。

   yy.mmm 周aaa显示为 21.Jan 周五

*数据验证

数据-数据验证，对已经有的数据，进行数据验证设置后，可以进行数据-圈释无效数据

*条件格式

开始-条件格式，设置条件。条件格式-管理规则中可以调整规则。条件格式-清除规则进行规则的清楚。

条件格式-图表集-管理规则可以设置区间的图表规则，也可以只显示图标。

插入-迷你图，插入需要的迷你图。点击设计，可以设置高点和低点，以及颜色。

条件格式-数据条得到一列数据的直观显示。

数据-删除重复值，复制后删除重复值。条件格式-突出显示-重复值-可选项唯一值。之后可以筛选-按颜色筛选。

*定位

定位excel中的所有图片(图片被认定为对象)：开始-查找-定位条件-对象-确定。然后按住ctrl键取消非图片对象。

开始-查找和选择-选择对象-框选即可选择所有对象。

定位之后，邮件点击删除，删除整行。

取消合并单元格后，进行填充：开始-查找和选择-定位-空值，然后在单元格中输入=B12，用鼠标点击也可，然后按下ctrl-enter可进行填充。

*工作簿、工作表等

一个文件夹下，可以在视图-切换窗口中进行excel文件的选择。

两个sheet同时显示：视图-新建窗口，进行窗口拖拽可看到不同的sheet，视图-并排查看，视图-全部重排-并排查看。

ctrl+箭头，箭头在左下角，进行工作簿的切换，按住ctrl，滚动到第一页sheet和最后一页sheet。箭头上右键，可以选择工作表。

右键sheet，可以移动工作表，勾选建立副本，否则原来工作表的内容会消失。

右键隐藏和取消隐藏。

单元格选择：点击第一个，按住shift点击最后一个。

快速移动：ctrl+方向键、双击单元格边框。

快速区域选中：ctrl+shift+方向键

视图-冻结首行：在看下面的数据时，字段依旧显示。或者冻结窗口，此时该窗格的左侧和上侧会被冻结。

*打印

部分区域打印：选择需要的区域，打印中选择打印选定区域。

正标打印多出两列：自定义边距中选择窄页边距，或者无缩放-将所有列调整为一列。

居中打印：打印-页面设置-页边距-居中方式-勾选水平垂直。

行号打印：页面设置-工作表-打印-行和列标题。

标题打印：多页打印时，第二页开始字段消失。选项卡-页面局部-打印标题-工作表-打印标题-顶端标题行-选择区域。这时打印便有了标题行。

*函数

alt + =：选中区域求和。

输入=和需要的函数，用tab补全，用enter会报错。

或者= if 在照妖镜(选项卡下方)中找到 fx ,可以避免输入法的来回切换。输入时ctrl+A快捷调用fx面板。

相对引用和绝对引用：使用美元符号$进行锁定。合计求平均值：R4 / $R$22，按下F4填充美元符号。

*统计函数

countif函数用来进行条件筛选。

不等于用<>来表示，用单元格判断"<"&A1，需要用&来连接。

excel占位符：*表示任意多，?表示一个，~表示占位符。

进行拖拽填充可以算后面的数。

*求和和平均

使用sumif和averageif来进行条件筛选，比如无序序列中，某个销售员的绩效总额。结合通配符可以更加高校。

sumproduct进行数组求和。

*数据透视表

Excel数据透视表大全，3小时从小白到大神！_哔哩哔哩_bilibili

分成不同页：

数据透视表的数据是复制源数据到数据缓存中，对数据透视表的内容修改不影响数据源，所以有了刷新这个操作。

可以拖动字段以显示优先级，称为父字段项和子字段项，不同优先级字段得到不同的侧重点。

选项：有数据透视表选项和折叠整个字段。双击+-单元格可以展开和折叠，右边可以将显示- +-隐藏。清楚可以重做透视表。移动数据透视表可以更改位置。

设计中可以更改样式。报表布局-大纲显示将字段放到不同的列中。填充空白字段，在大纲布局和表格布局中，填充父行标签的空白。 空行可以插入一行空白让显示更加清爽，总计中可以让显示和隐藏总计。

合并父行标签单元格：选项-选项-合并且居中排列带标签的单元格。

水平排列字段：

数据特别多，可以先点击推迟布局更新，调整完后，再点击右下角的更新。

修改数据点击刷新，添加数据点击修改数据源，或者将数据转为列表(插入-表格)，这时点击刷新就可以。

多表数据透视：先按ALT+D，然后松开D，再按P，弹出来的界面多重合并计算数据区域-下一步-创建单页字段-添加。可以进行自定义页字段。选择区域时，一定要选择没有总计的部分。创建后可以选择进行透视表最上方进行数据筛选。

外部数据源：创建数据透视表-选择链接，数据首行包含列标题。刷新-链接属性设置更新时间。

选择中可以选择字段和数值，也可以选择整个透视表。

一定要注重报表样式。