点击获取AI摘要

Excel 知识笔记

本文主要介绍 Excel 中的一些高级知识和常用函数，重点涵盖数据透视表、常用函数（SUM、SUMIF、SUMIFS、SUBTOTAL、IF、VLOOKUP、XLOOKUP、INDEX、MATCH）以及周报开发的相关内容。

1. 📊 数据透视表（Pivot Table）

1.1. 数据透视表简介

数据透视表是 Excel 中用于快速汇总、分析和展示大量数据的工具。它能够根据用户的需求动态调整数据的展示形式，例如按类别汇总、计算总和或平均值等。

1.2. 创建数据透视表

选择数据区域（确保数据有表头）。
进入“插入”选项卡，点击“数据透视表”按钮。
在弹出的对话框中，选择新建工作表或现有工作表。
在数据透视表字段窗格中拖拽字段至行、列、数值和筛选区域。

将文本型拖拽至行，数值型拖拽至值，可以实现sum+group by的效果
双击字段可以对其进行重命名
在字段、项目和集中可以插入自定义计算字段，输入完成后修改名称点击修改即可添加，添加完成后会出现在数据透视表字段中，勾选后即可添加到数据透视表中

excel_pivot4

1.3. 数据透视表技巧

字段筛选和排序：利用字段列表中的筛选按钮对数据进行快速筛选和排序。
分组数据：右击行/列标签，选择“分组”，例如按日期分组按月或季度统计。
数据格式化：右键单击数据区域，选择“值字段设置”，自定义数据汇总方式和数字格式。
拖拽字段到对应区域：
- 行/列：分组维度（时间等）
- 值：计算指标（求和/计数/平均值/占比）
- 筛选器：数据过滤，直接拖拽字段即可在数据透视表增加筛选项
- 插入切片器：可视化联动筛选，点击不同的筛选项，数据透视表会自动更新表内数值，同时，切片器处于当前工作表外的工作表时点击，同样可以筛选当前表的内容，而筛选器只能在当前数据透视表内使用
- 刷新数据：右键 → 刷新
点击数据透视表，插入数据透视图，右键可以更改图表类型，可以插入多张数据透视图，图表也会根据切片器内容进行变换

excel_pivot6

excel_pivot7

插入组合图可以将多种图表混合展示

excel_pivot8

2.📈 进阶数据透视表技巧

1. 计算字段与计算项

计算字段：在值区域添加自定义公式
分析 → 字段、项目和集 → 计算字段
示例：利润率 = (销售额 - 成本)/销售额

时间智能计算（结合Power Pivot）

1	MTD销售额 := TOTALMTD(SUM(数据[销售额]), 数据[日期])

2. 动态数据源

超级表（Ctrl+T）
将数据区域转换为表 → 透视表自动扩展范围
连接Power Query
通过ETL清洗后的数据自动更新透视表

3. 常用函数详解

2.1. SUM

功能：对一组数值求和。
语法： =SUM(number1, [number2], …)
示例：
1
=SUM(A1:A10)

2.2. SUMIF

功能：根据单一条件对范围内符合条件的数值求和。
语法： =SUMIF(range, criteria, [sum_range])
示例：
1
=SUMIF(B1:B10, ">100", C1:C10)

2.3. SUMIFS

功能：根据多个条件对范围内符合条件的数值求和。
语法： =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
sumifs(要返回的值所在列, 查找条件1所在列, 查找限定条件1, 查找条件2所在列, 查找限定条件2……)

示例：

1	=SUMIFS(C1:C10, A1:A10, "产品A", B1:B10, ">100")

通常会设计计算月总和，环比等数值，因此需要计算上个月的这一天用来作为被减项用于筛选时间范围，Excel中，日期表示的是从1900.01.01到今天的天数，有以下几种方法可以计算上个月
- EDATE(start_date，months)返回一串日期，指示起始日期之前/之后的月数，如EDATE(A1，-1)表示这天的前一个月，7月31日会返回6月30日
- EOMONTH(start_date，months)返回一串日期，表示指定月数之前或之后的月份的最后一天（只针对月份操作）如EOMONTH(A1,-1)+1表示这个时间月份的第一天，比如8月（无论几号）会返回8月1日，不+1则会返回7月31日
- DATE(YEAR(A1),MONTH(A1),1)返回本月的第一天，最直观的形式，在date函数外再-1即可返回上月的最后一天

2.4. SUBTOTAL

功能：返回数据列表或数据库中的分类汇总。subtotal值会根据字段筛选而变化。
语法： =SUBTOTAL(function_num, ref1, [ref2], …)
常用 function_num：
- 9：求和
- 1：求平均值
示例：
1
=SUBTOTAL(9, A1:A10)

2.5. IF

功能：根据条件返回不同的结果，一般需要多层嵌套使用。
语法： =IF(logical_test, value_if_true, value_if_false)

示例：

1	=IF(A1>100, "大于100", "小于等于100")

2.6. VLOOKUP

功能：在数据区域的首列中搜索指定值，并返回该值所在行中指定列的内容。
语法： =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP(查找值, 查找区域(要查找的必须在第一列), 返回第几列, 0)
示例：
1
=VLOOKUP("产品A", A1:D10, 3, FALSE)

2.7. XLOOKUP

功能：现代化的查找函数，能够向任意方向查找数据，取代 VLOOKUP/HLOOKUP。
语法： =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP(查找值, 查找值所在列, 返回列, 未找到返回值(可选), 匹配模式(可选), 搜索模式(可选))

示例：

1	=XLOOKUP("产品A", A1:A10, C1:C10, "未找到")

2.8. INDEX 和 MATCH

INDEX
- 功能：返回数组中指定位置的值。
- 语法： =INDEX(array, row_num, [column_num])
- INDEX(区域, 行号, 列号)：根据行号从某列中返回对应的值
MATCH
- 功能：返回指定值在一维数组中的相对位置。
- 语法： =MATCH(lookup_value, lookup_array, [match_type])
- MATCH(查找值, 区域, 0)：在某行/列中查找指定的值，并返回其列/行号

组合使用示例：

1
2
3

=INDEX(C1:C10, MATCH("产品A", A1:A10, 0))

index(数据区域,match(行查找顶,index数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0))

同时使用xlookup和index+match实现的相同功能

index_match

当函数不清晰时，可以先把要实现的函数单独写好，再复制进完整的表达式，同时可以配合ALT+ENTER在函数中换行避免混淆

4.🔢 常用函数总结

基础聚合

函数	语法	说明	周报应用示例
SUM	`=SUM(range)`	基础求和	`=SUM(C2:C100)` → 周总销售额
SUMIF	`=SUMIF(range, criteria, [sum_range])`	单条件求和	`=SUMIF(A2:A100, "华东", C2:C100)` → 华东区销售总额
SUMIFS	`=SUMIFS(sum_range, criteria_range1, criteria1, ...)`	多条件求和	`=SUMIFS(C2:C100, A2:A100, "华东", B2:B100, ">2023-01-01")`
SUBTOTAL	`=SUBTOTAL(function_num, range)`	分类汇总（支持筛选）	`=SUBTOTAL(9, C2:C100)` → 筛选后可见数据求和

逻辑判断

IF	`=IF(logical_test, [value_if_true], [value_if_false])`
示例	`=IF(C2>10000, "达标", "未达标")` → 销售目标判断

查找匹配

函数	语法	特点
VLOOKUP	`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`	纵向查找，需注意首列匹配和列索引号
XLOOKUP	`=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`	支持双向查找、错误处理，更灵活
INDEX+MATCH	`=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))`	灵活组合，支持多维度查找

5.🔍 高阶函数扩展

动态数组函数（Excel 365+）

函数	作用	周报应用
FILTER	条件筛选数据区域	`=FILTER(订单表, (销售额>10000)*(区域="华东"))`
SORT	动态排序	`=SORT(UNIQUE(产品列表),,-1)`
UNIQUE	去重提取	`=UNIQUE(销售大区)`

时间处理函数

1
2
3

=WEEKNUM(A2)  // 返回日期所属周数（周报核心函数）
=EDATE(A2,3)  // 计算3个月后的日期（常用于滚动预测）
=TEXT(A2,"YYYY-MMM")  // 日期转"2023-Jul"格式

错误处理

1 2	=IFERROR(VLOOKUP(...), "未找到") // 屏蔽#N/A错误 =AGGREGATE(9,6,C2:C100) // 忽略隐藏行和错误值的求和

6.周报开发

[ ] 掌握数据引用、公式填写和自动化拖拽等基本功能
[ ] 理解数据美化、数据计算与逻辑结构的构建

整体流程建议：

先搭建框架： 建立好各区域结构及基本引用和公式
再填数据： 在框架内填充各项指标数据和日期
最后美化： 格式设置、风格统一等作为最后一步处理，避免中途美化造成数据修改的不便

6.1 周报框架搭建

1. 周报结构

四大区域：
1. 标题（包含时间范围）
2. 目标看板（单独区域展示周报的目标内容，如累计值，环比，同比等，同时包含筛选器）
3. 结果指标（GMV、商家实收、到手率等）
4. 过程指标（曝光人数、转化率等）
小看板区域（3、4）显示关键指标的概览，通常包括目标、区域、过程指标和结果指标。

2. 基础设置

标题：A2单元格填写2020年8月第二周
日期列：
- 输入起始日期（如在A13输入2020-08-10）
- 后续日期使用公式=A13+1并拖动填充（需从第二个单元格开始拖动）向下拖动填充至 A19，生成 8 月 10 日 - 16 日的日期。
星期列：
- 右键单元格 → 设置单元格格式 → 数字 → 日期 → 选择 “周几” 格式（如 “周三”）
日期联动：
- 所有与日期相关的操作都建议使用引用方式，这样如果改变某个日期数据，其他依赖数据也会自动更新，极大地提升工作效率
- 使用公式动态引用日期，避免手动修改（选中 A13:A19，根据需要设置为 “短日期” 格式，如=TEXT(A2, "m月d日")）

3. 指标体系构建

结果指标（核心数据）

指标	说明	计算公式
GMV	总成交额	直接引用原数据或通过 SUMIFS 计算
商家实收	商家实际收入	同上
到手率	实收 / GMV，反映收入转化率	`=商家实收/GMV`
有效订单	有效订单数	直接引用
无效订单	无效订单数	直接引用
客单价	GMV / 有效订单，反映平均消费金额	`=GMV/有效订单`

过程指标（流量漏斗）

指标	说明	计算公式
曝光人数	店铺曝光次数	直接引用
进店人数	点击进入店铺的人数	直接引用
进店转化率	进店人数 / 曝光人数，反映流量承接能力	`=进店人数/曝光人数`
下单人数	实际下单的人数	直接引用
下单转化率	下单人数 / 进店人数，反映转化效率	`=下单人数/进店人数`
营销占比	cpc总费用 / GMV，反映投放效果	`=cpc总费用 / GMV`

6.2 核心函数与数据引用

1. 数据验证（筛选器）

步骤：
1. 选择目标单元格（如平台筛选器）
2. 数据 → 数据验证 → 允许“序列”
3. 输入选项：全部,美团,饿了么（英文逗号分隔）
4. 点击确定，生成下拉菜单
作用：通过选择平台（全部 / 美团 / 饿了么），动态筛选对应数据。

2. 动态条件求和（SUMIFS）

配合筛选器的公式逻辑：

=IF(
  平台单元格="全部",
  SUMIF(日期列, 当前日期, GMV列),
  SUMIFS(GMV列, 日期列, 当前日期, 平台列, 平台单元格)
)

绝对引用与相对引用：
- 固定不变的列或单元格使用绝对引用，例如: H$5, A:A
- 需要随拖拽变化的行或列使用相对引用，例如: A13
- 混合引用用于锁定列或行，例如: A$13
- 锁定区域：$A$2:$X$1000
- 锁定条件单元格：$H$5（平台筛选器）

3. 动态列引用（INDEX+MATCH）

目标：让函数自动识别表头列，提高灵活性（如 GMV 列、商家实收列可动态切换），因此需要先写出日期列，平台列（筛选器），求和列的公式

查找列名位置：

使用MATCH 找到目标列在表头中的位置，再使用INDEX 根据列位置提取数据
1
=INDEX(原表数据区域, 0, MATCH(列名标题, 表头行, 0))

示例（日期列动态引用）：

1	=INDEX($A:$X, 0, MATCH("日期", $A$1:$X$1, 0))

示例（动态求和 GMV 列）：

1	=SUMIFS(INDEX(数据区域, , MATCH("GMV", 表头, 0)), 日期列, 当天日期, 平台列, H5)

拖拽填充：

从第二个有公式的单元格开始拖拽，确保公式自动更新。
如果直接从第一个单元格拖拽，可能会只是简单的序列填充，而没有公式。
例如，若第一个单元格手动修改为“9”，则后续单元格不会自动更新公式中的逻辑。

注意:
使用INDEX和MATCH组合动态获取日期列、GMV列和平台列，替换SUMIF和SUMIFS公式中对应的部分
替换后的公式会变得很长，要仔细核对括号和参数。

6.3 关键指标计算

1. 计算型指标

到手率：=商家实收/GMV

注意：结果设为百分比格式（保留 2 位小数）

=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0))),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5))

逻辑在2中已经陈述

客单价：=GMV/有效订单
转化率（进店 / 下单）：
- 进店转化率：=进店人数/曝光人数
- 下单转化率：=下单人数/进店人数

营销占比：=CPC总费用 / GMV

=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$A$1:$X$1,0))),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5))/$C13

2. 周累计与周环比（时间维度分析）

周累计（当周总和）
- 方法：对当周 7 天的数据求和（使用SUM函数或快捷键Alt+=）
  例：曝光人数周累计：=SUM(H13:H19)（H13-H19 为每天曝光人数）
周环比（与上周对比）
- 公式：=(本周数据/上周数据)-1（结果为百分比，正增长为绿色，负增长为红色）
- 将日期范围向前偏移 7 天（如本周第一天为 A13，上周第一天为A13-7）
- 上周GMV数据公式：
1
2
3
4
5
6
=SUMIFS(
数值列,
日期列, ">="&起始日期-7,
日期列, "<="&结束日期-7(或<当前日期),
平台列, 平台筛选器
)

例：有效订单周环比

=A9/IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(F$12,'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),">="&$A13-7,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),"<"&$A13),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(F$12,'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),">="&$A13-7,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),"<"&$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5))-1

其中A9为本周有效订单

3. 业务进度计算

当月GMV总和：日期条件为大于等于本月第一天（在SUMIFS中介绍了3种实现方式）
1
=SUMIFS(GMV列, 日期列, ">="&EOMONTH(TODAY(),-1)+1)

进度公式：=当月累计GMV/目标值目标值的设置使用IF嵌套，将在6.5中提到

=IF($H$5="全部",SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),">="&EOMONTH(A13,-1)+1,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&$A19),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$A$1:$X$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),">="&DATE(YEAR(A13),MONTH(A13),1),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$A$1:$X$1,0)),"<="&$A19,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$A$1:$X$1,0)),$H$5))/$H$8

逻辑：判断筛选器是否为全部，是则使用sumifs计算要求的值，条件设置大于本月第一天，小于本周的最后一天，否则多使用sumifs增加一个判断平台的条件，其余相同。

6.4 可视化与格式优化

1. 数值格式

百分比：选中转化率、到手率等单元格，按Ctrl+Shift+%。
保留小数：右键→设置单元格格式→数值→小数位数（如 2 位）/直接在开始菜单点击。

2. 条件格式

数据条：可视化进度（如业务进度）

操作：选中单元格 →【开始】→【条件格式】→【新建规则】→【基于各自值设置所有单元格的格式】→【格式样式：数据条】→【选择类型】
如：选中数值区域 → 条件格式 → 数据条 → 类型选择数字 → 最大值设置为1 → 设置颜色 → 渐变填充
颜色标记：标记正负值（如周环比）

操作：新建规则 → 只为包含以下内容的单元格设置格式 → “单元格值> 0” 设为绿色，“≤0” 设为红色（设置两次）。
图标集：显示趋势（上升 / 下降箭头）

操作：条件格式 → 新建规则 → 基于各自值设置所有单元格的格式 → 图标集 → 选择 “三向箭头”或自定义中间值（0）为横线 → 分别设置>0, =0, <0的图标 → 类型设置为数字
- 大于0：绿色字体 + ↑图标
- 等于0：红色字体 + -图标
- 小于0：红色字体 + ↓图标
低于GMV平均值标记：(注意混合引用)
- 标记低于周平均值的GMV数值
- 选中结果指标区域 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格
1
=$C13 < AVERAGE($C$13:$C$19)
- 格式 → 下划线 → 加粗
格式刷
- 单击格式刷：复制格式到单个单元格
- 双击格式刷：复制格式到多个单元格
点击管理自定义规则即可查看、修改设置的规则

3. 迷你图制作

步骤：
1. 选中数据区域（如一周的曝光人数）
2. 【插入】 → 【迷你图】→ 折线图
3. 指定放置单元格，设置标记（显示数据点）和高点颜色

4. 美化技巧

格式优先：先确保数据计算正确，在数据与公式全部建立后，最后进行整体的美化设计
**隐藏网格线：**视图 → 取消勾选“网格线”
合并单元格：合并标题单元格并居中，标题和列名加粗、放大
**添加主题色：**给结果指标和过程指标的表头（列名）添加主题色
边框与字体：给小看板、指标表格添加外边框，统一字体（推荐微软雅黑），调整字体、对齐方式等

6.5 目标看板与业务进度

1. 目标设置

按平台设定 GMV 目标（如全部 = 20 万，美团 = 15 万，饿了么 = 5 万）。
示例：在 H8 单元格输入公式
=IF(H5="全部",200000,IF(H5="美团",150000,50000))。

2. 业务进度

公式：=截至目前GMV/目标（如 =SUMIFS(GMV列, 日期列, ">="&DATE(YEAR(A13),MONTH(A13),1))表示大于当月第一天）。

已在6.3.3中做出陈述
进度条：通过条件格式→数据条可视化，最大值设为 1（100%）。

6.6 自动化维护

日期联动：所有日期、星期、平台筛选均通过引用和公式实现，修改A13单元格日期（初始设置日期的单元格）后，所有关联数据自动更新
数据扩展：新增数据时，调整公式引用范围（如$A:$X改为$A:$Z）
模板复用：保存为模板文件（.xltx），每周复制使用

提示

所有公式中的区域引用需根据实际表格调整（如A:X为示例列范围）

建议逐步测试每个函数模块，使用F9键分解验证公式逻辑

格式美化可最后进行，避免干扰数据处理

最终效果