文章目录
Excel 中阶技巧
1. 使用Excel List
1.1 理解Excel List
(1)为了方便自己,也方便Excel进行各种操作,第一行得是表头。
(2)一张表中间不要有空行,否则Excel会判定成多张表。
1.2 单字段排序
点中某字段下的任意单元格→点击排序
1.3 多字段排序
点击排序→添加条件
1.4 自定义排序
点击排序→次序→自定义序列→输入序列→添加
1.5 自动筛选工具
点击表中任意单元格→点筛选→在表头中筛选
1.6 Subtotal分类汇总(分组聚合)
要分组的字段先排序→点分类汇总→选择分组字段、汇总方式、汇总项
1.7 将List转化为Table
点击表中任意单元格→点套用表格格式→选区域,确定。好处是:
(1)快速调节表格样式。
(2)汇总方便(自己用函数加的汇总不受筛选影响,表格加的汇总受影响)。
1.8 找到重复数据
选中要查找重复值的区域→条件格式→突出显示单元格规则→重复值→设置格式
1.9 删除重复数据
选中整个要去重的表区域→删除重复值→选择判断重复的字段
2. Excel List 函数
这块挺重要的,我好像在哪儿的练习题还是面试题看到过。
2.1 DSUM函数的基本用法
效果:按筛选条件求和
(1)需要筛选的字段、需要满足的条件单独放在某几个单元格中。
(2)DSUM()的三个参数分别是:database:整个表/数据源所在区域;field:用以求和的字段名/在选中区域的第几列;criteria:含有筛选条件的区域。
特别注意:条件更新时,DSUM的第三个参数条件区域要手动更新,不然选定区域就错了!!!
2.2 DSUM函数+or筛选条件
效果:按筛选条件求和
需要筛选的字段、需要满足的条件单独放在某几个单元格中。同一字段(类别),不同枚举值之间的或关系,直接在该字段下面加;不同字段之间的或关系,要错开写。下图用和DSUM类似的DCOUNT演示:
部门是AD或AC:
部门是AD或位置在1号楼:
2.3 DSUM函数+and筛选条件
效果:按筛选条件求和
需要筛选的字段、需要满足的条件单独放在某几个单元格中。想表示且的条件并列写。
部门是AD且位置在1号楼:
部门是AD且位置在1号楼或者部门时AC且位置在2号楼:
总结:D开头的database函数效果和sumif、sumifs差不多,但是看起来更直观,条件多的时候用起来更方便。最重要的是,表示“或”非常方便。
2.4 DAVERAGE
同DSUM。
2.5 DCOUNT
同DSUM。
2.6 SUBTOTAL
分类汇总函数:相当于把Excel常用聚合函数整合到一块儿的一个函数。和直接使用聚合函数的区别在于SUBTOTAL受到筛选器的影响!!!转化成表格后新增的汇总行就用的SUBTOTAL!!!
SUBTOTAL的两个参数分别是:函数序号、聚合区域。函数序号对应表如下:普通的序号计算时会包含隐藏值,再加100后的序号计算时不会包含隐藏值。
3. Excel Data Validation
为输入单元格的数据添加验证条件,以免输入错误带来过多异常值,为后续数据清洗增加负担。
3.1 创建一个下拉菜单
数据验证→设置条件
3.2 给数值设定范围
3.3 自定义报错信息
三种错误信息的区别:
停止:只能输入正确信息,否则一直报错。
警告:弹出警告,但可以选择重输或者无视。
信息:只弹出警告,完全不影响输入信息。
3.4 函数与数据验证
database函数的参数在输入时可能会输错,导致整个函数失效,此时加入数据验证可以有效防止这种情况。
4. Excel导入/导出数据
4.1 从txt(csv)文件导入数据
获取数据→来自文件→从文本→设置分隔符→加载/加载到→选择显示方式和存放位置。
转换数据按钮:可以进入Power Querry界面进行数据清洗。
导入数据时选择“表”和“仅创建连接”的区别:表会显示在Excel sheet中,而仅创建连接只会显示在Power Querry中。
点击“全部刷新”按钮可以刷新来自文件的数据。
4.2 从Access数据库导入数据
4.3 导出数据
方法1:
方法2:
会有这些提示信息,可以无视:
5. Excel透视表
5.1 创建透视表
创建透视表时不要直接框选区域,先把区域转换成表格(套用表格格式),然后通过表格名字来引用数据。如果不这么做,数据的行列有增减时,透视表不会动态变化。
5.2 透视表基本用法
行/列标签:按什么维度拆解。行相当于Pandas pivot_table的index参数,列相当于columns参数。
值:统计什么字段。相当于values参数。
5.3 对透视表继续分组聚合
选中要聚合的字段的枚举值→数据透视表分析→分组选择,重复上述步骤,直到该字段所有组分好。
需要改名的话选中了直接输入,开始输入才有输入提示框
上面的表示父级,下面的表示子级:
当然也可以根据已有的字段统计:
如图所示,地理位置为父级,销售员为子级
5.4 格式化透视表
加入格式时不要像平常一样直接框选单元格然后改样式,因为透视表的行列是动态增减的,有时单元格格式不会同步刷新。更推荐参照以下做法:
5.5 修改透视表的聚合值
数值型字段进行统计时默认进行求和,类别型字段默认进行计数。
(1)如果对同一个数值型字段想进行多种聚合统计,就多拖几次。相当于Pandas的透视表的values参数传列表(如果想不同字段按不同聚合函数统计就传字典)进去。
(2)如果想进一步计算同环比,操作如下:
基本字段:拆解维度的名字;基本项:以基本字段中的哪一项作为基准来计算,如果算环比就选择“上一个”。
效果:
5.6 透视表的下钻
双击想要钻取的行,Excel会生成一个按此行信息筛选出来的新表,比如下图就会生成一张包含4月数据的新表。
还有一种用法,如果别人需要他那份数据,但我们并不想把整个原始表格发给他们,那么就可以先创建透视表,统计完后再双击生成各个子表,把每个人那份子表发给他们。
5.7 创建透视图
数据透视表分析→数据透视图。
左下角可以进行筛选,右下角可以上下钻取
5.8 筛选透视表
筛选字段拖到筛选框中→在透视表上方筛选
5.9 使用切片器筛选透视表
数据透视表分析→插入切片器→选择字段,插入。
切片器比透视表自带的筛选功能的优势在于:(1)更美观;(2)可以随意拖拉调整位置,方便制作dashboard。
如果是日期字段可以选插入日程表,会把日期自动拆分出年月来,筛选时更方便。
6. Excel Pwer Pivot
和PowerBI的一样。
6.1 激活Power Pivot
注意:这里以365版本为例,不需要额外安装!
选项→加载项→COM加载项→转到→勾选Power Pivot→重启Excel
6.2 建立数据模型
Power Pivot→添加到数据模型。有多少张表要加进来就重复点多少次。
6.3 建立表关系
(1)方法1:打开关系图视图→把需要作联结的字段从一张表拖到另一张表。如下图就建立好了一个一对多的关系。
(2)方法2:右键任意字段进入创建关系对话框→选好两张表做联结的字段
6.4 创建透视表
点击Power Pivot界面的数据透视表→自动跳回Excel主界面。此时透视表处会有模型里的所有表。然后就可以愉快地根据维度表统计事实表了。
6.5 Excel Power Pivot KPI
(1)方法1:进入Power Pivot→选中某列进行汇总→选中汇总单元格点击创建KPI→设置目标值(中间的值)/阈值/色阶样式(有时数据越低越好,有时越高越好,需要掉换红绿颜色的方向)/图标样式→把KPI的状态拖入值统计即可。
(2)方法2:如果没有Power Pivot就用条件列来做同样的事。
7. 处理大数据集的技巧
7.1 冻结窗格
选中冻结起点(起点以上、以左的格子会被冻结)→视图→冻结窗格→选择冻结行还是列。效果是冻结的行/列会保持在表格上方,主要是方便我们划下去了也能看到表头。
7.2 创建分组
选中想要组合的几行/几列→数据→组合。可以用来快速地显示和隐藏字段。
7.3 打印选项
(1)让每页的表格都具有表头:页面布局→打印标题→工作表→选择表头区域。
(2)如果表因为列数太多被从中间分开了,打印时想要从左到右、从上到下连续打印:页面布局→打印标题→工作表→打印顺序→先行后列。
7.4 跨表运算/三维公式
公式中选定数据时直接点进目标表去选单元格即可。
7.5 合并计算
数据→合并计算→引用位置中选区域→标签位置→添加。
标签位置:用以合并的维度标签位于框选区域的哪个位置。