文章目录
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 合并计算
数据→合并计算→引用位置中选区域→标签位置→添加。
标签位置：用以合并的维度标签位于框选区域的哪个位置。
1782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
