一、Excel 常用操作

Tips:

尽量不要用 Excel 中的日期格式存储日期数据，用字符串格式。

二、EDATE & DATE

在 Excel 中，EDATE 和 DATE 是两个常用的日期函数，它们具有不同的功能。下面是对它们的详细解释：

1. EDATE 函数

EDATE 函数用于返回从给定日期起，按月数偏移的日期。

语法：

1

EDATE(start_date, months)

• start_date：起始日期，可以是一个日期值或一个包含日期的单元格。
• months：偏移的月份数。如果是正数，返回的是未来的日期；如果是负数，返回的是过去的日期。

示例：

假设：

• A1 单元格包含日期 2024年1月1日，你想要知道从这个日期起，3个月之后是何时。

公式：

1

=EDATE(A1, 3)

结果：2024年4月1日，因为 3 个月后是 2024年4月1日。

如果想要返回 3个月之前的日期，可以使用负数：

1

=EDATE(A1, -3)

结果：2023年10月1日。

常见应用：

• 用于计算发票到期日（例如，30天、60天、90天后的日期）。
• 用于按月份推算业务周期或贷款期限。

2. DATE 函数

DATE 函数用于根据指定的年份、月份和日期来返回一个日期值。

语法：

1

DATE(year, month, day)

• year：年份，通常是一个四位数的年份（例如，2024）。
• month：月份，可以是 1 到 12 的数字，或使用负数和大于 12 的数来跨年计算月份。
• day：日期，表示该月份中的具体日期，通常是 1 到 31。

示例：

假设：

• 你想根据年份 2024、月份 7、日期 31 来生成一个日期。

公式：

1

=DATE(2024, 7, 31)

结果：2024年7月31日。

特殊情况：

• 如果 month 超过 12 或为负数，Excel 会自动处理年份的溢出。例如：

1

=DATE(2024, 14, 31)

结果：2025年2月28日，因为 14 月是 2025年的第2个月（2月）。

• 如果 day 超过了该月的最大日期，Excel 会自动调整日期。例如：

1

=DATE(2024, 2, 30)

结果：2024年3月1日，因为 2024年2月只有29天，30天会被自动调整为 3月1日。

常见应用：

• 用于创建特定日期，或者从年、月、日等不同数据源构造日期。
• 用于日期的计算和比较，尤其是在涉及多个字段的日期时。

3.总结对比：

这两个函数都是处理日期的常用工具，选择哪一个取决于具体需求：

• 如果你需要按月推算日期，使用 EDATE。
• 如果你需要根据年、月、日来生成特定日期，使用 DATE。
• 按月推算日期：使用 EDATE 或 EOMONTH。
• 按天数推算日期：使用 WORKDAY 或 WORKDAY.INTL。
• 计算日期差异：使用 DAYS 或 YEARFRAC

三、SUM & SUBTOTAL

在 Excel 中，SUM 和 SUBTOTAL 都是常用的数学函数，用于对一组数字进行求和。它们的主要区别在于如何处理数据范围和隐藏行。以下是详细的比较和说明：

1. SUM 函数

SUM 是 Excel 中最基本的求和函数，旨在对指定的数字范围进行简单的求和。

语法：

1

SUM(number1, [number2], ...)

• **number1, number2, …**：要相加的数字或单元格范围。

示例：

假设你有一个数据范围 A1:A5，其中包含数字 10, 20, 30, 40, 和 50。

1

=SUM(A1:A5)

结果：150（10 + 20 + 30 + 40 + 50）

特点：

• SUM 函数会 对所有可见和隐藏的单元格 进行求和，即使这些行被隐藏（如通过过滤或手动隐藏），也会将其包括在求和中。
• 它适用于所有需要对一系列数值进行加总的情况。

2. SUBTOTAL 函数

SUBTOTAL 函数更为灵活，除了可以执行求和外，还能进行其他统计计算。SUBTOTAL 的一个显著特性是：它可以忽略被隐藏的行，如果你使用自动筛选（Filter）或手动隐藏了行时，SUBTOTAL 只对可见的数据进行操作。

语法：

1

SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num：一个数字，指定要使用的汇总函数。对于求和，function_num 应设置为 9 或 109。
  • 9：表示求和，包括隐藏的行。
  • 109：表示求和，忽略隐藏的行。
• **ref1, ref2, …**：要进行汇总的范围，可以是单元格范围或多个范围。

示例：

假设你有一个数据范围 A1:A5，其中包含数字 10, 20, 30, 40, 和 50，并且 A3 行被隐藏。

如果你使用 SUBTOTAL 来计算求和并忽略隐藏的行（例如，使用过滤或手动隐藏行）：

1

=SUBTOTAL(109, A1:A5)

• 结果将是 120（10 + 20 + 40 + 50），因为第 3 行（数字 30）被隐藏，SUBTOTAL 忽略了这行。

如果你使用 9（表示包括隐藏行的求和）：

1

=SUBTOTAL(9, A1:A5)

• 结果将是 150（10 + 20 + 30 + 40 + 50），即使第 3 行被隐藏，SUM 也会计算所有行。

常见的 function_num 参数：

• 1：AVERAGE（平均值）
• 2：COUNT（计数）
• 3：COUNTA（非空单元格计数）
• 9：SUM（求和）
• 10：MAX（最大值）
• 11：MIN（最小值）

示例：不同功能的 function_num

1
2
3
4

=SUBTOTAL(9, A1:A5)    ' 求和，包括隐藏行
=SUBTOTAL(109, A1:A5)   ' 求和，忽略隐藏行
=SUBTOTAL(1, A1:A5)     ' 平均值，包括隐藏行
=SUBTOTAL(101, A1:A5)   ' 平均值，忽略隐藏行

3. SUM 与 SUBTOTAL 的区别

4. SUBTOTAL 更高效的使用场景：

• 当你有一个通过 筛选 或 手动隐藏 行的表格时，SUBTOTAL 会忽略隐藏的行，这对于避免错误的计算结果很有帮助。
• 例如，当你对销售数据进行筛选时，只希望对筛选出的（可见的）数据进行求和，而不包括被隐藏的数据行。这时，使用 SUBTOTAL 比使用 SUM 更为合适。

5.总结：

• 使用 SUM 时，它会对所有数据（包括隐藏的行）进行求和，适用于需要对整个数据范围求和的场景。
• 使用 SUBTOTAL 时，你可以选择是否忽略隐藏的行，并且它支持更多的统计功能，特别适用于有筛选或隐藏行的情况。

四、SUMIF & SUMIFS

在 Excel 中，SUMIF 和 SUMIFS 函数都用于条件求和，但它们在使用上有一些不同。下面是对这两个函数的详细解释：

1. SUMIF 函数

SUMIF 用于在满足指定条件时对范围内的数值进行求和。它只接受一个条件进行判断，适用于简单的单一条件求和。

语法：

1

SUMIF(range, criteria, [sum_range])

• range：要应用条件的单元格范围。
• criteria：条件，可以是一个数值、表达式、文本，甚至是一个单元格引用。
• **[sum_range]**：可选。实际要求和的范围。如果省略，则默认使用 range 作为要求和的范围。

示例：

假设在 A2:A6 中是销售员姓名，在 B2:B6 中是他们对应的销售额，你想要求和 销售员“张三” 的销售额。

公式：

1

=SUMIF(A2:A6, "张三", B2:B6)

结果：13500，因为“张三”在 B2:B6 中对应的销售额是 5000 + 4000 + 4500。

常见用法：

• 使用 SUMIF 进行单一条件求和。例如，求和大于某个值、等于某个值、包含特定文本等。
• SUMIF 支持以下条件表达式：
  • 数字：例如 ">5000"（大于5000）
  • 文本：例如 "张三"（等于“张三”）
  • 通配符："?"（匹配一个字符）、"*" （匹配多个字符）

2. SUMIFS 函数

SUMIFS 是 SUMIF 的扩展，允许使用 多个条件 来进行求和。你可以对多个范围应用条件，只有当所有条件都满足时，才会进行求和。

语法：

1

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range：实际要求和的单元格范围。
• criteria_range1：第一个条件范围。
• criteria1：第一个条件。
• **[criteria_range2, criteria2]**：可选，第二个条件范围及条件。可以有多个条件范围和条件。

示例：

假设在 A2:A6 中是销售员姓名，在 B2:B6 中是销售额，在 C2:C6 中是销售的月份，你想要求和 “张三” 在 “1月” 的销售额。

公式：

1

=SUMIFS(B2:B6, A2:A6, "张三", C2:C6, "1月")

结果：9000，因为只有在“张三”并且在“1月”时，销售额才会被加总（5000 + 4000）。

常见用法：

• 使用 SUMIFS 进行多个条件的求和。例如，求和在特定日期、特定销售员、特定产品等情况下的销售额。
• SUMIFS 支持条件的比较运算符：
  • 数字：">1000"、"<5000" 等
  • 文本：例如 "张三"
  • 日期：例如 ">2024-01-01" 等
  • 通配符："?" 和 "*"

SUMIF 与 SUMIFS 的区别

示例比较：

假设你有一个销售表，包含销售员、销售额和月份，表格如下：

使用 SUMIF：

假设你只想计算 张三 的总销售额：

1

=SUMIF(A2:A6, "张三", B2:B6)

结果：13500。

使用 SUMIFS：

假设你想计算 张三 在 1月 的销售额：

1

=SUMIFS(B2:B6, A2:A6, "张三", C2:C6, "1月")

结果：9000（5000 + 4000）。

3.总结：

• 使用 SUMIF 时，只能处理 单一条件 的求和，适用于较简单的求和场景。
• 使用 SUMIFS 时，可以处理 多个条件 的求和，适用于需要根据多个条件进行筛选和求和的复杂场景。

希望这能帮助你更好地理解 SUMIF 和 SUMIFS 的区别和使用场景！

五、VLOOKUP

VLOOKUP 是 Excel 中常用的查找函数之一，用于在一个数据表中根据某个条件查找值，并返回该值所在行的其他列的数据。它非常适合用于基于某个关键字从大型数据表中检索相关信息。

1. VLOOKUP 函数简介

VLOOKUP 的全称是 Vertical Lookup，意思是 垂直查找。它在数据表的第一列中查找一个值，然后返回该值所在行其他列的内容。

语法：

1

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：你想要查找的值。可以是单元格引用或具体的数值或文本。
• table_array：查找的范围或表格。查找值将从这一范围的第一列开始查找。
• col_index_num：返回的值所在列的列号。列号是相对于 table_array 的第一列而言的。例如，1 表示 table_array 的第一列，2 表示第二列，依此类推。
• **[range_lookup]**：可选参数，决定是否进行近似匹配。
  • TRUE 或省略：进行近似匹配（默认）。如果没有精确匹配，则返回小于 lookup_value 的最大值。
  • FALSE：进行精确匹配。如果找不到精确匹配，返回 #N/A 错误。

2. VLOOKUP 示例

假设你有一个员工信息表，包含员工的 ID、姓名、部门等信息。你希望根据员工的 ID 查找对应的姓名。

查找员工ID为 1003 的姓名

公式：

1

=VLOOKUP(1003, A2:C5, 2, FALSE)

解释：

• 1003：查找值是 1003。
• A2:C5：查找的范围是 A2 到 C5。
• 2：返回查找值所在行的第2列，即“姓名”列。
• FALSE：精确匹配，只有找到员工ID为 1003 时才会返回姓名。

结果：王五

查找员工ID为 1005 的姓名（不存在该ID）

公式：

1

=VLOOKUP(1005, A2:C5, 2, FALSE)

结果：#N/A，因为找不到 ID 为 1005 的员工。

3. 使用 VLOOKUP 时的注意事项

3.1. 查找值必须位于 table_array 的第一列

VLOOKUP 总是从 table_array 的 第一列 开始查找，因此，查找值必须在 table_array 的第一列。否则，VLOOKUP 无法工作。

3.2. 近似匹配（TRUE 或省略）

如果 [range_lookup] 为 TRUE 或省略，VLOOKUP 会执行近似匹配。此时，查找值必须按升序排列，否则可能得到不准确的结果。

• 如果你希望进行近似匹配（例如查找一个介于两个值之间的值），请确保查找列已按升序排列。
• 近似匹配的常见用途包括计算税率表、价格表等。

3.3. 精确匹配（FALSE）

如果 [range_lookup] 为 FALSE，VLOOKUP 将执行 精确匹配。如果查找值没有找到，VLOOKUP 将返回 #N/A 错误。

3.4. 列号必须大于等于 2

VLOOKUP 只能返回查找值所在行的 后续列 的数据，即 col_index_num 必须大于等于 2。如果你想返回查找列的值，通常会使用 INDEX 和 MATCH 函数的组合。

3.5. VLOOKUP 的限制

• VLOOKUP 只能查找 左侧 的列，不能反向查找。例如，你不能在表格的最后一列查找某个值并返回第一列的结果。
• 它只能返回 一列数据，如果你需要从多个列中返回结果，可以使用多次 VLOOKUP 或者考虑其他函数组合，如 INDEX 和 MATCH。

4. VLOOKUP 的常见应用场景

4.1. 查找价格

假设你有一个商品价格表，想根据商品 ID 查找对应商品的价格：

公式：

1

=VLOOKUP("A102", A2:C4, 3, FALSE)

结果：150元（返回商品B的价格）。

4.2. 查找员工的部门

假设你有一个员工表，根据员工ID查找其所属的部门：

公式：

1

=VLOOKUP(1002, A2:C5, 3, FALSE)

结果：市场部（返回员工ID为1002的部门）。

4.3. 使用 VLOOKUP 进行评分查询

假设你有一个分数等级表，根据学生的分数返回相应的等级：

公式：

1

=VLOOKUP(85, A2:B6, 2, TRUE)

结果：B（因为85分落在“80-89”区间）。

5. 总结

• VLOOKUP 是一个强大的查找工具，适用于从一个数据表中根据条件查找数据并返回相关信息。
• 它只能在查找值的 左侧列 进行查找，并且只能返回查找值所在行的 后续列 数据。
• 需要确保查找表格的排列和选择适当的匹配类型（精确或近似匹配）。

六、INDEX & MATCH

INDEX 和 MATCH 函数介绍

INDEX 和 MATCH 函数是 Excel 中非常强大的函数，可以单独使用，也可以结合起来使用，完成更为复杂和灵活的查找操作。与传统的 VLOOKUP 或 HLOOKUP 不同，INDEX 和 MATCH 提供了更高的灵活性和效率，尤其是在处理大数据集时，能够解决 VLOOKUP 无法处理的一些问题。

下面，我将分别介绍这两个函数，并详细说明它们如何结合使用。

1. INDEX 函数

INDEX 函数用于从一个数据范围中返回一个值，基于给定的行号和列号。它有两种使用方式：数组形式 和 引用形式。

数组形式（最常用）

1

INDEX(array, row_num, [column_num])

• array：包含数据的数组或数据范围。
• row_num：数据范围中的行号，用于指定要返回的值所在的行。
• **[column_num]**：可选。数据范围中的列号，用于指定要返回的值所在的列。如果是单列或单行数据，则可以省略此项。

示例 1：从单列数组中提取一个值

假设有如下商品列表：

你想返回第三行（即商品C）。

公式：

1

=INDEX(A1:A4, 3)

• A1:A4 是数据范围。
• 3 是行号，表示返回第三行的值。

结果：商品C

示例 2：从二维数组中提取一个值

假设有如下员工信息表：

你希望返回第二行第三列的值（即第二个员工的部门）。

公式：

1

=INDEX(A1:C4, 2, 3)

• A1:C4 是数据范围。
• 2 是行号，表示第二行。
• 3 是列号，表示第三列（部门列）。

结果：市场部

2. MATCH 函数

MATCH 函数用于在指定的范围中查找某个值，并返回该值的相对位置。与 INDEX 不同，MATCH 只返回 位置索引，不返回实际的值。

语法：

1

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value：你要查找的值，可以是一个具体的数值、文本、或者单元格引用。
• lookup_array：包含要查找值的数组或范围。
• **[match_type]**：可选。指定匹配的类型：
  • 1（默认）：查找小于或等于 lookup_value 的最大值。要求 lookup_array 按升序排列。
  • 0：查找精确匹配的值。
  • 1：查找大于或等于 lookup_value 的最小值。要求 lookup_array 按降序排列。

示例：查找某个值的位置信息

假设有如下员工信息表：

你希望找出 “王五” 在表中的位置。

公式：

1

=MATCH("王五", B1:B4, 0)

• “王五” 是查找值。
• B1:B4 是包含姓名的范围。
• 0 表示进行精确匹配。

结果：3，即王五在第3行。

3. INDEX 和 MATCH 组合使用

INDEX 和 MATCH 的组合使用，能实现更灵活和强大的查找功能。通过使用 MATCH 查找行号或列号，INDEX 可以根据这个位置返回对应的数据。结合 INDEX 和 MATCH 可以克服 VLOOKUP 的一些限制（比如只能在查找表的第一列查找）。

例子：通过 INDEX 和 MATCH 查找员工的部门

假设你有一个员工信息表，如下所示：

你希望根据员工的姓名查找其所属的部门。

步骤：

1. 使用 MATCH 查找姓名所在行号。
2. 使用 INDEX 根据行号返回部门。

公式：

1

=INDEX(C1:C4, MATCH("王五", B1:B4, 0))

• MATCH("王五", B1:B4, 0) 返回 "王五" 在姓名列中的位置（即第3行）。
• INDEX(C1:C4, 3) 根据行号 3 从部门列（C列）中返回对应的部门。

结果：销售部

例子：通过 INDEX 和 MATCH 查找指定员工的姓名

如果你要根据员工ID查找对应的姓名，可以使用类似的组合。

假设你有一个员工ID列（A列）和姓名列（B列）：

你希望根据员工ID查找姓名，假设要查找员工ID为 1003 的姓名。

步骤：

1. 使用 MATCH 查找员工ID所在的行号。
2. 使用 INDEX 根据行号返回姓名。

公式：

1

=INDEX(B1:B4, MATCH(1003, A1:A4, 0))

• MATCH(1003, A1:A4, 0) 查找员工ID 1003 在 A 列的位置（即第3行）。
• INDEX(B1:B4, 3) 返回该行对应的姓名，即第3行的姓名 王五。

结果：王五

4. INDEX 和 MATCH 的优点

• 灵活性：INDEX 和 MATCH 组合可以从任何位置（包括不在第一列的地方）查找数据，而 VLOOKUP 只能从数据范围的第一列查找。
• 效率：在处理非常大的数据集时，INDEX 和 MATCH 的组合通常比 VLOOKUP 更高效，尤其是在数据列数很大时。
• 支持横向查找：VLOOKUP 是纵向查找，HLOOKUP 是横向查找，而 INDEX 和 MATCH 可以在任何方向上查找数据，提供更多的灵活性。

5. 总结

• INDEX 用于返回数组中指定位置的值。
• MATCH 用于查找某个值在数组中的位置。
• INDEX 和 MATCH 的组合 提供了比 VLOOKUP 更强大和灵活的查找能力，尤其是在处理复杂的数据结构时。

通过将 INDEX 和 MATCH 组合使用，可以进行灵活的数据查找和返回操作，弥补了 VLOOKUP 和 HLOOKUP 函数的一些局限性。