数据分析（五）Excel常见函数 - 关联搜索、逻辑运算和计算统计

1. 关联搜索类

1.1 VLOOKUP函数

• 功能
  – VLOOKUP是Excel中最常用的垂直查找函数。它用于在表格或区域的第一列中查找指定的值，并返回同一行中其他列的值。
• 语法
  – VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。其中，lookup_value是要查找的值；table_array是查找的区域（包含查找列和结果列）；col_index_num是结果所在的列数（相对于查找区域的列数）；range_lookup是一个可选参数，为TRUE或FALSE，TRUE表示近似匹配（默认值，要求查找区域第一列已排序），FALSE表示精确匹配。
• 示例
  – 假设有一个产品价格表，A列为产品名称，B列为价格。在另一个工作表中，A列输入产品名称，要在B列查找对应的价格，可以使用VLOOKUP函数。如果数据在Sheet1的A1:B10区域，在Sheet2的A1输入产品名称，B1单元格输入=VLOOKUP(A1,Sheet1!$A$1:$B$10,2,FALSE)，向下填充即可得到相应产品的价格。

1.2. HLOOKUP函数

• 功能
  – HLOOKUP与VLOOKUP类似，不同的是它是水平查找函数，用于在表格或区域的第一行中查找指定的值，并返回同一列中其他行的值。
• 语法
  – HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])。参数含义与VLOOKUP类似，只是这里的row_index_num是结果所在的行数（相对于查找区域的行数）。
• 示例
  – 假设有一个成绩表，第一行是学科名称，第一列是学生姓名，要查找某个学科下某个学生的成绩，可以使用## HLOOKUP函数。

1.3 INDEX函数

• 功能
  – INDEX函数用于返回表格或区域中的值或引用。它可以返回指定行和列交叉处的单元格内容。
• 语法
  – INDEX(array, row_num, [col_num])。其中，array是要返回值的单元格区域或数组常量；row_num是要返回值的行号；col_num是可选参数，为要返回值的列号，如果省略，则返回指定行的整行内容。
• 示例
  – 假设有一个数据区域A1:E10，要返回第3行第4列的单元格内容，可以使用=INDEX(A1:E10,3,4)。

1.4 MATCH函数

• 功能
  – MATCH函数用于在指定区域中查找指定的值，并返回该值在区域中的相对位置。
• 语法
  – MATCH(lookup_value, lookup_array, [match_type])。lookup_value是要查找的值；lookup_array是查找的区域；match_type是可选参数，为 - 1、0或1， - 1表示查找小于或等于lookup_value的最大值（要求lookup_array已排序且为降序），0表示精确查找（最常用），1表示查找小于或等于lookup_value的最大值（要求lookup_array已排序且为升序）。
• 示例
  – 可以与INDEX函数配合使用。例如，要查找某个值在一个列表中的位置，然后根据这个位置获取另一个相关列表中的对应值。

1.5 INDEX - MATCH组合

• 功能
  – 这种组合克服了VLOOKUP函数只能从左向右查找的局限性，并且在多条件查找等复杂场景下更加灵活。
• 示例
  – 假设有一个员工信息表，A列是员工姓名，B列是部门，C列是工资。要根据员工姓名和部门查找工资，可以使用INDEX - MATCH组合。先使用MATCH函数分别查找员工姓名和部门在各自列中的位置，然后使用INDEX函数根据这两个位置找到对应的工资。例如，在其他工作表中，A1输入员工姓名，B1输入部门，C1单元格可以输入=INDEX(Sheet1!$C:$C,MATCH(A1& B1,Sheet1!$A:$A&Sheet1!$B:$B,0))（这里假设数据在Sheet1中，使用&符号连接两个条件进行多条件查找）。

1.6 ROW 函数（Excel）

• 功能
  – 返回引用的行号。如果省略引用，则默认返回公式所在单元格的行号。
• 语法
  – ROW ([reference])。其中 reference 是可选的单元格或单元格区域引用。
• 示例
  – 在 A1 单元格中输入公式 = ROW ()，则会返回 1，表示 A1 单元格位于第 1 行。如果在 A10 单元格输入 = ROW (A1:B5)，则会返回 1，因为 A1 是这个区域中的第一个单元格，其行号为 1。

1.7 OFFSET 函数（Excel）

• 功能
  – 以指定的引用为参照系，通过给定偏移量得到新的引用。可以根据一个起始单元格，按照指定的行数和列数进行偏移，从而获取到不同位置的单元格或区域。
• 语法
  – OFFSET (reference, rows, cols, [height], [width])。其中，reference 是偏移量参照系的起始引用单元格或区域；rows 是相对于起始引用单元格或区域向下（为正数）或向上（为负数）偏移的行数；cols 是相对于起始引用单元格或区域向右（为正数）或向左（为负数）偏移的列数；height 和 width 是可选参数，分别表示新引用区域的高度（行数）和宽度（列数），如果省略，则默认与 reference 的高度和宽度相同。
• 示例
  – 假设 A1 单元格的值为 10，要获取 A1 单元格向下 2 行、向右 3 列（即 D3 单元格）的值，可以使用 = OFFSET (A1, 2, 3)。如果要获取以 D3 为起始单元格，高度为 2 行、宽度为 3 列的区域，可以使用 = OFFSET (A1, 2, 3, 2, 3)。

1.8 HYPERLINK 函数

• 语法
  – HYPERLINK(link_location, [friendly_name])。
• 参数说明
  – link_location：这是必需的参数，表示超链接的目标地址。可以是一个网址（如 “https://www.example.com”）、文件路径（如 “C:\Documents\file.xlsx”）或者本工作簿中的单元格引用（如 “Sheet2!A1”）等。
• friendly_name：这是可选参数，表示显示在单元格中的超链接文本。如果省略该参数，将显示 link_location 中的地址作为超链接文本。
• 示例
  – 在 Excel 工作表中，如果要创建一个指向百度网站的超链接，可以在一个单元格中输入公式：=HYPERLINK (“https://www.baidu.com”," 百度 ")。这里 “https://www.baidu.com” 是超链接的目标地址，“百度” 是显示在单元格中的友好名称（超链接文本）。
  – 如果要创建一个指向本工作簿中 Sheet2 工作表 A1 单元格的超链接，并且显示文本为 “跳转到 Sheet2 - A1”，则可以输入公式：=HYPERLINK (“Sheet2!A1”, “跳转到 Sheet2 - A1”)。
  – 若要创建指向本地文件（例如名为 “report.pdf” 位于 “D:\Files” 文件夹下）的超链接，可以输入公式：=HYPERLINK (“D:\Files\report.pdf”, “查看报告”)。

2. 逻辑运算类

2.1 IF 函数

• 功能
  – 根据指定的条件来判断并返回不同的值。
• 语法
  – IF (logical_test, value_if_true, value_if_false)。其中，logical_test 是要测试的条件，可以是任何比较运算（如等于、大于、小于等）；value_if_true 是当条件为真时返回的值；value_if_false 是当条件为假时返回的值。
• 示例
  – 在成绩表中，如果学生成绩大于等于 60 分判定为及格，否则为不及格。假设成绩在 A 列，在 B 列输入公式 = IF (A1>= 60,“及格”,“不及格”)，然后向下填充即可。

2.2 AND 函数

• 功能
  – 当所有参数的逻辑值为真时返回真，只要有一个参数为假就返回假。
• 语法
  – AND (logical1, logical2,…)。可以有多个逻辑参数，最多可包含 255 个。
• 示例
  – 在判断员工是否同时满足业绩达标（A 列）和考勤合格（B 列）时，在 C 列输入公式 = AND (A1, B1)，如果 A1 和 B1 都为真（业绩达标且考勤合格），则 C1 为真，否则为假。

2.3 OR 函数

• 功能
  – 只要有一个参数的逻辑值为真就返回真，只有当所有参数都为假时才返回假。
• 语法
  – OR (logical1, logical2,…)，同样最多可包含 255 个逻辑参数。
• 示例
  – 假设在招聘中，候选人只要满足学历要求（A 列）或者工作经验要求（B 列）就可以进入下一轮面试。在 C 列输入公式 = OR (A1, B1) 来判断是否符合要求。

2.4 NOT 函数

• 功能
  – 对参数的逻辑值取反。如果参数为真，则返回假；如果参数为假，则返回真。
• 语法
  – NOT (logical)，只有一个逻辑参数。
• 示例
  – 如果 A1 单元格中的逻辑值为真，在 B1 单元格输入公式 = NOT (A1)，则 B1 单元格的值为假。

2.5 IFERROR 函数

• 功能
  – 用于捕获和处理公式中的错误。如果公式计算结果为错误值（如 #VALUE!、#REF! 等），则返回指定的值；如果公式计算结果正常，则返回公式的计算结果。
• 语法
  – IFERROR (value, value_if_error)。其中，value 是要检查是否存在错误的公式或表达式；value_if_error 是当公式出现错误时要返回的值。
• 示例
  – 在进行除法运算时，如果除数可能为 0，如 A1/A2，为避免出现 #DIV/0! 错误，可以在 B1 单元格输入公式 = IFERROR (A1/A2, 0)，当 A2 为 0 时，B1 将返回 0 而不是错误值。

3. 计算统计类

3.1 SUM 函数

• 功能
  – 用于计算单元格区域中所有数值的总和。
• 语法
  – SUM (number1,[number2,…])。其中 number1 为必需参数，可以是数字，或者是包含数字的名称、单元格区域或单元格引用等；number2,… 为可选参数，最多可包含 255 个可选参数。
• 示例
  – 计算 A1 到 A10 单元格区域内数字的总和，可在其他单元格输入公式 = SUM (A1:A10)。

3.2 AVERAGE 函数

• 功能
  – 计算单元格区域中所有数值的平均值。
• 语法
  – AVERAGE (number1,[number2,…])。参数含义与 SUM 函数类似。
• 示例
  – 求 B1 到 B20 单元格区域内数字的平均值，公式为 = AVERAGE (B1:B20)。

3.3 COUNT 函数

• 功能
  – 计算包含数字的单元格个数。
• 语法
  – COUNT (value1,[value2,…])。其中 value1 为必需参数，value2,… 为可选参数，最多可包含 255 个参数，这些参数可以是任何类型的值，但只有包含数字的单元格才会被计数。
• 示例
  – 在 C1 到 C50 单元格区域中统计包含数字的单元格个数，可使用公式 = COUNT (C1:C50)。

3.4 COUNTA 函数

• 功能
  – 计算非空单元格的个数。
• 语法
  – COUNTA (value1,[value2,…])。参数可以是任何类型的值，只要单元格不为空就会被计数。
• 示例
  – 统计 D1 到 D100 单元格区域中非空单元格的个数，公式为 = COUNTA (D1:D100)。

3.5 COUNTIF 函数

• 功能
  – 用于计算满足某个条件的单元格的数量。
• 语法
  – COUNTIF (range,criteria)。其中 range 为要计算其中满足条件的单元格数目的单元格区域；criteria 为确定哪些单元格将被计数的条件，可以是数字、表达式、单元格引用或文本字符串。
• 示例
  – 在 E1 到 E50 单元格区域中，统计等于 10 的单元格个数，公式为 = COUNTIF (E1:E50,10)。

3.6 COUNTIFS 函数

• 功能
  – 用于计算满足多个条件的单元格的数量。
• 语法
  – COUNTIFS (criteria_range1,criteria1,[criteria_range2,criteria2,…])。其中 criteria_range1 为第一个要计算其中满足条件的单元格数目的单元格区域；criteria1 为第一个条件；后面可依次添加更多的条件范围和条件。
• 示例
  – 在 F1 到 F100 单元格区域中，统计大于 5 且小于 15 的单元格个数，可使用公式 = COUNTIFS (F1:F100,“>5”,F1:F100,“<15”)。

3.7 MAX 函数

• 功能
  – 返回一组数值中的最大值。
• 语法
  – MAX(number1,[number2,…])。
• 示例
  – 在 G1 到 G30 单元格区域中找出最大值，公式为 = MAX (G1:G30)。

3.8 MIN 函数

• 功能
  – 返回一组数值中的最小值。
• 语法
  – MIN(number1,[number2,…])。
• 示例
  – 在 H1 到 H40 单元格区域中找出最小值，公式为 = MIN (H1:H40)。

3.9 SUMIF 函数

• 功能
  – 根据指定的条件对单元格区域中的数值进行求和。
• 语法
  – SUMIF (range,criteria,[sum_range])。其中 range 为要根据条件进行计算的单元格区域；criteria 为条件；sum_range 为可选参数，是要进行求和的实际单元格区域，如果省略，则对 range 区域进行求和。
• 示例
  – 在 I1 到 I60 单元格区域中，对满足条件（如等于 “产品 A”）的单元格对应的 J1 到 J60 单元格区域中的数值进行求和。如果条件在 I 列，求和区域在 J 列，公式为 = SUMIF (I1:I60,“产品 A”,J1:J60)。

3.10 SUMIFS 函数

• 功能
  – 在满足多个条件的基础上对指定单元格区域中的数值进行求和。
• 语法
  – SUMIFS (sum_range,criteria_range1,criteria1,[criteria_range2,criteria2,…])。其中 sum_range 为要进行求和的单元格区域；criteria_range1 为第一个要计算其中满足条件的单元格数目的单元格区域；criteria1 为第一个条件；后面可依次添加更多的条件范围和条件。
• 示例
  – 在 K1 到 K100 单元格区域中，对满足条件（如大于 10 且小于 50）的单元格对应的 L1 到 L100 单元格区域中的数值进行求和，公式为 = SUMIFS (L1:L100,K1:K100,“>10”,K1:K100,“<50”)。

3.11 RAND 函数

• 功能：
  – 返回一个大于等于 0 且小于 1 的随机数，每次工作表重新计算时这个数字都会改变。
• 语法：
  – RAND ()，没有参数。例如，在一个单元格中输入 = RAND ()，会得到一个随机的小数。
• 应用：
  – 如果要生成 0 到 10 之间的随机数，可以使用公式 = RAND () * 10。

3.12 RANDBETWEEN 函数

• 功能：
  – 返回位于两个指定数之间的 一个随机整数。
• 语法：
  – RANDBETWEEN (bottom, top)，其中 “bottom” 是下限，“top” 是上限。例如，要生成 1 到 100 之间的随机整数，可以在单元格中输入 = RANDBETWEEN (1, 100)。
• 功能
  – 用于返回数据集的四分位数。四分位数将数据分为四部分，每个部分包含大约四分之一（25%）的数据点。
• 语法
  – QUARTILE (array, quart)。其中，array 是要求四分位数的数组或数据区域；quart 决定要返回哪一个四分位数，具体取值如下：
  ■ 如果 quart = 0，返回最小值（这与通常定义的四分位数稍有不同，但 Excel 中这样规定）。
  ■ 如果 quart = 1，返回第 1 个四分位数（Q1），即数据集中 25% 的数据点小于等于此值。
  ■ 如果 quart = 2，返回第 2 个四分位数（Q2），也就是中位数，数据集中 50% 的数据点小于等于此值。
  ■ 如果 quart = 3，返回第 3 个四分位数（Q3），此时 75% 的数据点小于等于此值。
  ■ 如果 quart = 4，返回最大值。
• 示例
  – 假设有一组数据在 A1:A10 单元格区域，要计算这组数据的第 1 个四分位数（Q1），在其他单元格输入公式 = QUARTILE (A1:A10, 1)。

3.13 STDEV 函数（Excel）

• 功能
  – 用于估算基于样本的标准偏差。标准偏差反映了数据相对于平均值的离散程度。
• 语法
  – STDEV (number1,[number2,…])。其中 number1 为必需参数，number2,… 为可选参数，最多可包含 255 个参数。这些参数可以是数字，或者是包含数字的名称、单元格区域或单元格引用等。
• 示例
  – 假设有一组数据在 A1:A10 单元格区域，要计算这组数据的样本标准偏差，在其他单元格输入公式 = ##3.14 STDEV (A1:A10)。

3.14 SUBTOTAL函数

• 功能
  – 在 Excel 中，SUBTOTAL 函数用于返回数据列表或数据库中的分类汇总。它可以执行多种计算操作（如求和、平均值、计数等），并且在对筛选后的数据进行操作时非常有用，能够根据可见单元格进行计算，忽略隐藏行的数据。
• 语法
  – SUBTOTAL(function_num, ref1, [ref2,…])。
  – function_num：这是一个数字，用于指定要执行的汇总函数类型，取值范围为 1 - 11 或 101 - 111。其中，1 - 11 用于在包含隐藏值和非隐藏值的情况下进行计算，101 - 111 用于仅在可见单元格（忽略隐藏行）的情况下进行计算。常见的 function_num 取值及对应的函数如下：
  ■ 1 或 101：AVERAGE（平均值）
  ■ 2 或 102：COUNT（计数）
  ■ 3 或 103：COUNTA（非空单元格计数）
  ■ 4 或 104：MAX（最大值）
  ■ 5 或 105：MIN（最小值）
  ■ 9 或 109：SUM（求和）
  – ref1, [ref2,…]：这些是要进行分类汇总的单元格区域或引用，可以有多个区域，最多可包含 254 个。
• 示例
  – 求和示例
  ■ 假设在 A1:A10 单元格区域有数据，要对这些数据进行求和，并且在数据筛选时仅对可见单元格求和。可以在其他单元格（如 A11）中输入公式 = SUBTOTAL (109, A1:A10)。
  – 计数示例
  ■ 如果要对 B1:B20 单元格区域中的非空单元格进行计数，并且忽略隐藏行的情况，在其他单元格（如 B21）中输入公式 = SUBTOTAL (103, B1:B20)。

3.15 ROUND 函数（Excel）

• 功能
  – 用于将数字按照指定的位数进行四舍五入。
• 语法
  – ROUND (number, num_digits)。其中，number 是要进行四舍五入的数字，可以是直接输入的数字、单元格引用或者包含数字的公式；num_digits 是指定的位数，它决定了要将数字舍入到小数点后的哪一位。
• 示例
  – 如果要将数字 3.14159 四舍五入到小数点后两位，可以使用公式 = ROUND (3.14159, 2)，结果为 3.14。
  – 若要将数字 12345 四舍五入到千位（相当于小数点前三位），可以使用公式 = ROUND (12345, - 3)，结果为 12000。这里 num_digits 为负数时，表示将数字舍入到小数点左边的相应位数。

3.16 INT函数

• 功能
  – INT 函数用于将一个数值向下取整为最接近的整数。它直接截断小数部分，只保留整数部分，不进行四舍五入操作。
• 语法
  – INT (number)，其中 number 是需要进行向下取整操作的数值，可以是直接输入的数字、单元格引用或者包含数字的公式。
• 示例
  – 如果 A1 单元格中的数值为 3.9，在 B1 单元格中输入公式 = INT (A1)，则 B1 单元格的结果为 3。
  – 若 A2 单元格中的数值为 - 2.3，使用公式 = INT (A2)，得到的结果为 - 2。