【Excel中阶技巧】表结构、函数、数据验证、导入导出、透视表、Power Pivot、其他技巧

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 合并计算

数据→合并计算→引用位置中选区域→标签位置→添加。

标签位置：用以合并的维度标签位于框选区域的哪个位置。