第2章_Excel_知识点笔记

Excel 知识点总结（第2章）

来自：第2章_Excel_知识点笔记，原笔记

基础操作

状态栏：快速查看计数/求和等数据（右键可配置）。
筛选（Ctrl+Shift+L）：按条件显示数据，支持多列交集筛选。
排序：升序（A→Z/小→大）、降序（Z→A/大→小）。

数据工具

数据验证：创建下拉框（序列输入需用英文逗号分隔）。
删除重复值：清理重复行。
条件格式：高亮关键数据。

布局与显示

合并单元格：合并后居中（慎用，影响数据处理）。
取消网格线：视图更简洁。

聚合函数

1.最大值、最小值、中位数、众数

MAX：返回一组数值中的最大值。例如，=MAX(B1:B10)会找出B1到B10单元格中的最大数值。
MIN：返回一组数值中的最小值。例如，=MIN(E1:E10)会找出E1到E10单元格中的最小数值。
MEDIAN：返回一组数值的中位数。例如，=MEDIAN(K1:K12)会计算K1到K12的中位数值。
MODE：返回一组数值中出现频率最高的数（众数）。例如，=MODE(L1:L8)会找出L1到L8中出现次数最多的数值。

2.平均数

AVERAGE：计算一组数值的平均值。例如，=AVERAGE(C1:C8)会计算C1到C8单元格中所有数字的平均值。
AVERAGEIF：按条件计算平均值。例如，=AVERAGEIF(I1:I30, ">=60")会计算I1到I30中≥60的数值的平均值。
AVERAGEIFS：多条件求平均值，计算满足多个条件的数值平均值。=AVERAGEIFS(M1:M50, N1:N50, "是", O1:O50, ">0") → 对N列为“是”且O列>0的M列数值求平均。

3.计数

COUNT：统计一组单元格中包含数字的个数。例如，=COUNT(D1:D3)会计算D1到D3单元格中有多少单元格包含数字。

仅统计包含数字的单元格（数值、日期、时间等），忽略文本、逻辑值、错误值、空白单元格。

如果有多个相同的数字（如 5, 5, 10），=COUNT(D1:D3) 会返回 3（每个数字都会被计数，无论是否重复）。
COUNTIF：单条件计数。例如，=COUNTIF(F1:F20, ">50")会统计F1到F20中大于50的单元格个数。

=COUNTIF(范围, 条件)
范围：要统计的单元格区域（如 A1:A10）。
条件：可以是数字、文本、表达式或通配符（如 “>50”、“苹果”、“A*”）。
例子：
=COUNTIF(B1:B20, “苹果”) // 统计B列中等于“苹果”的单元格数量
=COUNTIF(C1:C30, “>50”) // 统计C列中大于50的单元格数量
=COUNTIF(D1:D15, “<100”) // 统计D列中小于100的单元格数量
=COUNTIF(E1:E50, “A*”) // 统计E列中以“A”开头的文本数量
=COUNTIF(F1:F10, “电脑”) // 统计F列中包含“电脑”的单元格数量
=COUNTIF(G1:G100, “<>”) // 统计G列中非空单元格数量
=COUNTIF(H1:H30, “>=2024/1/1”) // 统计H列中≥2024年1月1日的日期数量

注意事项：
>不区分大小写： “apple” 和 “Apple” 视为相同。
>支持通配符：
* 匹配任意多个字符（如 “A*” 匹配 Apple、Air）。
? 匹配单个字符（如 “A?” 匹配 At，但不匹配 Air）。
忽略错误值：COUNTIF 不会统计 #N/A、#VALUE! 等错误值。
COUNTIFS：多条件计数。 =COUNTIFS(A1:A10, ">10", B1:B10, "<20")。
COUNTA：统计非空单元格数量（包括文本和数字）。例如，=COUNTA(J1:J50)会统计J1到J50中所有非空单元格的数量。仅忽略真正空白的单元格。

4.求和

SUM：用于计算一组数值的总和。例如，=SUM(A1:A5)会计算A1到A5单元格中所有数字的和。
SUMIF：按条件求和。例如，=SUMIF(G1:G15, "苹果", H1:H15)会计算G列中为“苹果”时对应H列的数值之和。

语法：=SUMIF(条件范围, 条件, [求和范围])
示例：
=SUMIF(A1:A10, “>50”, B1:B10) → 对A列中大于50的对应B列数值求和。
=SUMIF(C1:C5, “苹果”, D1:D5) → 对C列为“苹果”的对应D列数值求和。
SUMIFS：多条件求和，根据多个条件对范围内的单元格求和。

语法：=SUMIFS(求和范围, 条件范围1, 条件1, 条件范围2, 条件2, …)
示例：
=SUMIFS(E1:E20, F1:F20, “>100”, G1:G20, “<200”) → 对F列>100且G列<200的对应E列数值求和。
=SUMIFS(H1:H10, I1:I10, “A”, J1:J10, “>=10”) → 对I列为“A”且J列≥10的对应H列数值求和。
SUMPRODUCT：多条件求和/计数的高级用法，可替代部分SUMIFS或COUNTIFS功能。

示例：=SUMPRODUCT((P1:P10="是")*(Q1:Q10>10)*R1:R10) → 对P列为“是”且Q列>10的R列数值求和。

5.方差与标准差

STDEV / STDEV.P / STDEV.S：计算标准差（STDEV.P用于总体，STDEV.S用于样本）。

例如，=STDEV.S(M1:M20)会计算M1到M20的样本标准差。
VAR / VAR.P / VAR.S：计算方差（VAR.P用于总体，VAR.S用于样本）。

例如，=VAR.P(N1:N25)会计算N1到N25的总体方差。

6.四分位数

QUARTILE / QUARTILE.INC / QUARTILE.EXC：返回数据集的四分位数（INC包含0和1，EXC不包含）。

例如，=QUARTILE.INC(O1:O40, 1)会计算O1到O40的第1四分位数（25%分位）。

四分位数（Quartile）是将一组数据从小到大排序后，分成四等份的临界值：
Q1（第1四分位）：25% 的数据 ≤ Q1（即下四分位）
Q2（第2四分位）：50% 的数据 ≤ Q2（即中位数）
Q3（第3四分位）：75% 的数据 ≤ Q3（即上四分位）
✅ 推荐使用 QUARTILE.INC（除非有特殊需求）。

quart 参数：
- 0：最小值（等同 MIN 函数）
- 1：Q1（25%分位）
- 2：Q2（50%分位，等同 MEDIAN）
- 3：Q3（75%分位）
- 4：最大值（等同 MAX 函数）

函数	包含范围	适用场景	示例（计算Q1）
`QUARTILE`	同 `QUARTILE.INC`	旧版兼容（Excel 2010前）	`=QUARTILE(A1:A10, 1)`
`QUARTILE.INC`	包含最小值和最大值（0和1）	默认方法（与统计学常用一致）	`=QUARTILE.INC(A1:A10, 1)`
`QUARTILE.EXC`	排除最小值和最大值（0和1）	更严格的数据分析	`=QUARTILE.EXC(A1:A10, 1)`

PERCENTILE / PERCENTILE.INC / PERCENTILE.EXC：返回数据集的百分位数。

例如，=PERCENTILE.INC(P1:P100, 0.9)会计算P1到P100的90%分位值。

错误处理

IFERROR：错误处理，当公式返回错误时显示指定值，否则返回原结果。

语法：=IFERROR(公式, 错误时返回的值)
示例：
=IFERROR(1/0, “除零错误”) → 返回“除零错误”（原公式为#DIV/0!）。
=IFERROR(VLOOKUP(A1, B:C, 2, 0), “未找到”) → 如果查找失败显示“未找到”。
IFNA：专门处理#N/A错误，与IFERROR类似但仅针对#N/A。

示例：=IFNA(VLOOKUP(A1, B:C, 2, 0), "无匹配") → 仅当#N/A时显示“无匹配”。

文本处理函数

场景	推荐函数
提取部分文本	`LEFT` / `RIGHT` / `MID`
合并或拆分文本	`TEXTJOIN` / `TEXTSPLIT`
清洗数据	`TRIM` / `CLEAN`
大小写转换	`UPPER` / `LOWER` / `PROPER`
查找与替换	`FIND` / `SUBSTITUTE`
数值与文本互转	`TEXT` / `VALUE`

1. 基本文本提取与计算

LEFT：从文本左侧提取指定数量的字符。

示例：=LEFT("Excel", 2) → 返回 "Ex"（提取前2个字符）。
用途：提取前缀（如姓名首字母、产品编码前缀）。
RIGHT：从文本右侧提取指定数量的字符。

示例：=RIGHT("Hello", 3) → 返回 "llo"（提取后3个字符）。
用途：提取后缀（如文件扩展名、电话号码后几位）。
MID：从文本中间指定位置提取字符。

示例：=MID("ABCDEF", 2, 3) → 返回 "BCD"（从第2字符开始取3位）。
用途：提取固定格式的中间部分（如身份证出生日期段）。
LEN：计算文本长度（包括空格）。

示例：=LEN("Excel") → 返回 5。
用途：校验输入长度（如密码位数限制）。

2. 文本连接与拆分

CONCATENATE / CONCAT / &：合并多个文本。

示例：="A" & "B" 或 =CONCAT("A", "B") → 返回 "AB"。
用途：拼接姓名、地址等字段。
TEXTJOIN：按分隔符合并文本，可忽略空值。

示例：=TEXTJOIN("-", TRUE, "A", "", "B") → 返回 "A-B"。
用途：合并多列数据并自动跳过空白。
TEXTSPLIT（新版Excel）：按分隔符拆分文本为数组。

示例：=TEXTSPLIT("A,B,C", ",") → 返回 {"A","B","C"}。
用途：快速分列（如拆分CSV数据）。

3. 文本清洗与格式化

TRIM：删除文本首尾空格及重复空格。

示例：=TRIM(" Excel ") → 返回 "Excel"。
用途：清理导入数据中的多余空格。
CLEAN：删除文本中的非打印字符（如换行符）。

示例：=CLEAN(A1) → 清除A1中的乱码。
用途：处理从网页或数据库导入的脏数据。
UPPER / LOWER / PROPER：转换大小写。
示例：
- =UPPER("excel") → "EXCEL"
- =PROPER("john doe") → "John Doe"（首字母大写）。
  用途：标准化姓名、标题等格式。

4. 查找与替换

FIND / SEARCH：查找字符位置（FIND区分大小写，SEARCH不区分）。
示例：
- =FIND("n", "Excel") → 返回 4（"n"在第4位）。
- =SEARCH("e", "Excel") → 返回 1（不区分大小写）。
  用途：定位关键词或分隔符位置。
SUBSTITUTE：替换指定文本。

示例：=SUBSTITUTE("A-B-C", "-", "/") → 返回 "A/B/C"。
用途：批量修改符号或关键词。
REPLACE：按位置替换字符。

示例：=REPLACE("ABCD", 2, 2, "XY") → 返回 "AXYD"（从第2字符开始替换2位）。
用途：掩码处理（如隐藏手机号中间四位）。

5. 高级文本处理

TEXT：将数值/日期格式化为指定文本样式。

示例：=TEXT(1234.5, "$#,##0.00") → 返回 "$1,234.50"。
用途：自定义显示格式（如金额加货币符号）。
VALUE：将文本格式的数字转为数值。

示例：=VALUE("123") → 返回 123（可参与计算）。
用途：修复文本型数字导致的计算错误。
REPT：重复文本指定次数。

示例：=REPT("*", 5) → 返回 "*****"。
用途：快速生成填充符或简易图表。

匹配与逻辑函数

`VLOOKUP`

=VLOOKUP("李四", A1:C4, 3, FALSE)

VLOOKUP 是 Excel 中最常用的查找函数之一，用于在表格中垂直查找数据并返回对应值。

“垂直查找”指的是按列方向（从上到下）查找数据的行为。

Excel 表格的列是垂直排列的（纵向），而 VLOOKUP 的查找范围（表格区域）通常是多列组成的区域，函数会先在第一列中垂直搜索目标值，找到后横向（向右）返回对应行的其他列的值。

VLOOKUP（Vertical Lookup）：按列查找，适合列式数据。
HLOOKUP（Horizontal Lookup）：按行查找，适合行式数据（但实际使用较少）。

🔎 核心语法

VLOOKUP(查找值, 查找范围, 返回列号, [匹配模式])

查找值：要搜索的值（如商品编号、姓名）。
查找范围：包含查找值和返回值的表格区域（建议用绝对引用 A1:D100）。
返回列号：从查找范围的第1列开始数，返回值所在的列数（如第3列填 3）。
匹配模式：

FALSE 或 0：精确匹配（最常用）。

TRUE 或 1：模糊匹配（用于数值区间）。

📌 实战案例

假设有一个表格如下（A1:C4）：

姓名 (A)	年龄 (B)	部门 ©
张三	25	销售部
李四	30	技术部
王五	28	市场部

用 VLOOKUP 查找“李四”的部门：

=VLOOKUP("李四", A1:C4, 3, FALSE)

垂直搜索：在 A列（姓名列） 中从上到下查找“李四”。
横向返回：找到后，向右移动到第3列（C列），返回对应的“技术部”。

⚠️ 常见错误 & 解决
#N/A 错误：

原因：查找值不存在，或拼写不一致（如“李四” vs “李四 ”）。
解决：用 TRIM() 清除空格，或用 IFERROR 隐藏错误： =IFERROR(VLOOKUP(F2, A1:D4, 4, FALSE), "未找到")

返回错误列：

原因：列号数错（如工资是第4列，但误填 3 会返回工龄）。
技巧：用 COLUMN() 动态获取列号（如 COLUMN(D1) 返回 4）。
数据未锁定：

错误：下拉公式时查找范围变动（如 A1:D4 变成 A2:D5）。

解决：用绝对引用 A1:D4。

🚀 高效技巧

1. 反向查找（从左往右查）

VLOOKUP 只能从左向右查，若需用“工资”查“姓名”，改用：
=INDEX(A1:A4, MATCH(F2, D1:D4, 0))

2. 批量查找

下拉公式自动匹配多行数据（记得锁定区域 A1:D4）。
结合下拉菜单：

在 F2 设置数据验证（菜单），选择姓名自动显示工资。

`IF`

条件判断（如=IF(A1>10,"达标","未达标")）。

Power Query（数据清洗）

导入数据：从工作簿、文件夹或当前区域。
处理空值：向上/向下填充。
拆分列：按分隔符或字符数拆分文本。
合并查询：表连接（内连接、左连接等）。
追加查询：多表上下拼接。

透视表与可视化

创建透视表：聚合数据（求和、平均值等）。
值显示方式：差异、差异百分比、总计百分比。
切片器：交互式筛选（连接多个透视表）。
GETPIVOTDATA：动态引用透视表数据。

效率工具

格式刷：快速复制样式（双击可多次使用）。
主题颜色：统一调整文档配色。

核心技巧

数据清洗优先：使用Power Query处理重复值、空值和非标准格式。
透视表分析：快速汇总数据，结合切片器实现动态报表。
函数嵌套：如IFERROR(VLOOKUP(...),"未找到")提升公式健壮性。
避免合并单元格：影响函数和透视表计算，改用“跨越合并”优化布局。

案例分析

1-计算月环比

这个公式是一个复杂的Excel公式，结合了IFERROR、SUMIFS和数学运算。我来逐步分析它的结构和功能：

公式结构

=IFERROR(C15/SUMIFS(源数据!$E:$E,源数据!$H:$H,$A15,源数据!$I:$I,$B15,源数据!$O:$O,$H$4-1)-1,"-")

组成部分解析

SUMIFS函数：
- 作用：多条件求和
- 参数：
  - 源数据!$E:$E - 要求和的列（E列）
  - 源数据!$H:$H,$A15 - 第一个条件：H列等于A15单元格的值
  - 源数据!$I:$I,$B15 - 第二个条件：I列等于B15单元格的值
  - 源数据!$O:$O,$H$4-1 - 第三个条件：O列等于H4单元格值减1
数学运算部分：
- C15/SUMIFS(...)-1 - 用C15单元格的值除以SUMIFS的结果，然后减1
IFERROR函数：
- 作用：如果前面的计算出现错误（如除以零），则返回"-"而不是错误值

引用类型分析

绝对引用：
- 源数据!$E:$E、源数据!$H:$H等 - 列引用固定
- $H$4 - 完全固定引用H4单元格
混合引用：
- $A15、$B15 - 列固定，行相对（复制公式时行号会变）
相对引用：
- C15 - 完全相对引用

公式的实际功能

这个公式计算的是：

在"源数据"表中找出所有满足三个条件的行：
- H列值等于当前表的A15单元格值
- I列值等于当前表的B15单元格值
- O列值等于H4单元格值减1
对这些行的E列值求和
用当前表的C15单元格值除以这个和，然后减1
如果计算过程中出现任何错误，显示"-"

典型应用场景

这种公式常用于：

计算增长率或变化率（(新值/旧值)-1）
数据对比分析（如本月与上月对比）
带有条件筛选的比率计算

潜在问题

如果SUMIFS返回0，会导致除以零错误（被IFERROR捕获）
引用整列（如E:E）可能影响计算性能
条件中的$H$4-1需要确保H4是数值类型

优化建议

考虑限制数据范围（如E2:E1000）而非整列引用
可以添加注释说明公式的用途
如果H4是日期，确保减1的逻辑正确（可能需要使用EDATE等日期函数）

引用状态切换

在 Excel 中，切换 绝对引用（$A $1 ） * * 、 * * 混合引用（$ A1 或 A$1） 和 相对引用（A1） 的快捷键是：

使用方法

选中公式中的单元格引用（例如 A1）。
按 F4 键（Windows）或 Command + T（Mac）：
- 第1次按：A1 → $A$1（绝对引用）
- 第2次按：$A$1 → A$1（锁定行）
- 第3次按：A$1 → $A1（锁定列）
- 第4次按：$A1 → A1（相对引用）
- 循环切换，直到你想要的引用方式。

示例

输入公式 =A1，选中 A1 后按 F4：
- 第一次：=A1 → =$A$1（绝对引用）
- 第二次：=$A$1 → =A$1（锁定行）
- 第三次：=A$1 → =$A1（锁定列）
- 第四次：=$A1 → =A1（相对引用）

适用场景

绝对引用（$A$1）：复制公式时，引用始终不变（适用于固定参数）。
混合引用（$A1 或 A$1）：
- $A1：列固定，行可变（适用于向下填充公式）。
- A$1：行固定，列可变（适用于向右填充公式）。
相对引用（A1）：复制公式时，引用会随位置变化（适用于动态计算）。

总结

引用类型	示例	适用场景
绝对引用	`$A$1`	固定行和列（如常量、固定参数）
混合引用（锁定行）	`A$1`	行固定，列可变（如向右填充公式）
混合引用（锁定列）	`$A1`	列固定，行可变（如向下填充公式）
相对引用	`A1`	行和列均可变（如动态计算）