【数据分析】Excel中的常用函数公式总结

0 引用方式

0.1 相对引用

相对引用是Excel中最基本的引用方式之一。当公式中引用了单元格时，公式会根据相对位置自动调整引用。例如，如果你在单元格B2中输入公式=A1，那么当你复制这个公式到B3时，公式会自动调整为=A2，保持相对位置的关系。

A1: 10
B2: =A1    // 结果为10
B3: =A2    // 结果为10（相对引用自动调整）

0.2 绝对引用

绝对引用是一种在复制公式时保持引用不变的方法。在Excel中，使用$符号来创建绝对引用。例如，=$A$1会始终引用第一行的A列，无论公式被复制到哪里。

A1: 10
B2: =$A$1   // 结果为10
C2: =$A$1   // 结果为10（绝对引用不变）

0.3 混合引用

混合引用是相对引用和绝对引用的结合。可以将$符号放在行或列的其中一个，使其保持不变。例如，= $A1将保持列A不变，但是行数会随着复制而改变。

A1: 10
B2: =$A1   // 结果为10
B3: =$A2   // 结果为10（列A不变，行数相对引用）

0.4 3D引用

3D引用能够引用多个工作表中的单元格。语法为Sheet1:Sheet3!A1，表示引用从Sheet1到Sheet3的A1单元格。这在处理多个相关工作表时非常有用。

Sheet1: A1: 10
Sheet2: A1: 20
Sheet3: A1: 30Summary Sheet: B1: =SUM(Sheet1:Sheet3!A1)   // 结果为60 (10 + 20 + 30)

0.5 命名引用

命名引用是一种给单元格或单元格范围分配易记名称的方式。通过“公式”选项卡中的“定义名称”功能，可以为单元格或范围分配一个名称，然后在公式中使用该名称引用。

A1: 10定义名称 MyValue 为 =Sheet1!$A$1B2: =MyValue   // 结果为10

1 基础函数

1.1 加法、减法、乘法和除法

加法：C1=A1+B1

减法：C1=A1-B1

乘法：C1=A1*B1

除法：C1=A1/B1

1.2 平均数

计算一组数字的平均值：

B1=AVERAGE(A1:A10)

1.3 求和

计算一组数字的总和：

B1=SUM(A1:A10)

1.4 最大值和最小值

最大值：

B1=MAX(A1:A10)

最小值：

B1=MIN(A1:A10)

2 文本函数

2.1 合并单元格内容

合并A1和B1单元格的内容：C1=A1&B1

2.2 查找

在文本中查找特定字符串，并返回所查找关键词（第一个文本字符串）在第二个文本字符串的下标位置（从1开始）：

C1=SEARCH("关键词", A1)

2.3 替换

替换文本中的特定字符串：

C1=SUBSTITUTE(A1, "旧值", "新值")

3 逻辑函数

3.1 IF函数

基本的IF语句，根据条件表达式的值（IF括号中第一个值）返回结果，若条件为真，返回IF括号中的第二个值，否则返回第三个值：

A2=IF(A1>10, "大于10", "小于等于10")

3.2 AND和OR函数

AND函数：如果其所有参数均为 TRUE，则返回 TRUE：

C1=AND(A1>10, B1<20)

OR函数：如果任一参数为 TRUE，则返回 TRUE：

C1=OR(A1>10, B1<20)

3.3 IFERROR函数

如果公式的计算结果错误，则返回指定的值；否则返回公式的结果。

C1=IFERROR(A1/B1, "除数不能为零")

4 统计函数

4.1 COUNT函数

计算参数列表中数字的数量：

B1=COUNT(A1:A10)

4.2 COUNTIF函数

根据条件计算符合条件的数字的数量：

B1=COUNTIF(A1:A10, ">10")

4.3 SUMIF函数

根据条件计算符合条件的数字的总和：

B1=SUMIF(A1:A10, ">10")

4.4 SUBTOTAL函数

对数据进行分类汇总，使用方法：

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

其中，function_num是函数指定的参数，不同数值对应不同的具体函数：

function_num（包含隐藏值）	function_num（忽略隐藏值）	函数	含义
1	101	AVERAGE	求平均
2	102	COUNT	求数字的数量
3	103	COUNTA	求不为空的单元格的个数
4	104	MAX	求最大值
5	105	MIN	求最小值
6	106	PRODUCT	求参数中数字的乘积
7	107	STDEV	求样本标准差
8	108	STDEVP	求总体标准差
9	109	SUM	求和
10	110	VAR	求样本方差
11	111	VARP	求总体方差

注：

• STDEV的计算方法是将样本的平均数的方差进行开平方得出的。
  $$\text{STDEV}=\sqrt{\frac{\sum (X_i-\bar{X}{)}^2}{n-1}}$$
  • $X_i$代表数据集中的每个数据点。
  • $\bar{X}$ 代表数据集的平均值。
  • $n$代表数据点的数量。
• STDEVP的计算方法是先求一组资料中各数值与其算术平均数离差平方和的平均数，然后取其平方根。
  $$\text{STDEVP}=\sqrt{\frac{\sum (X_i-\bar{X}{)}^2}{n}}$$
• VAR的计算方法是:
  $$\text{VAR}=\frac{\sum (X_i-\bar{X}{)}^2}{n-1}$$
• VARP的计算方法是:
  $$\text{VARP}=\frac{\sum (X_i-\bar{X}{)}^2}{n}$$

5 时间和日期函数

5.1 TODAY和NOW函数

返回当前日期：

A1=TODAY()

返回当前日期和时间：

A1=NOW()

5.2 DATEDIF函数

计算两个日期之间相隔的天数、月数或年数，用于计算年龄很有用：

C1=DATEDIF(A1, B1, "D") //返回A1日期和B1日期之间相隔的天数

其中，第三个参数Unit即指定计算相隔的是天数、月数或年数等，具体如下：

Unit	返回结果
“Y”	一段时期内的整年数
“M”	一段时期内的整月数
“D”	一段时期内的整天数
“MD”	开始日期和结束日期的天数之差，忽略日期中的月份和年份
“YM”	开始日期和结束日期的月份之差， 忽略日期中的天和年份
“YD”	开始日期和结束日期的日期部分之差， 忽略日期中的年份

注：第二个日期参数需在第一个日期参数之前，否则结果将为#NUM！。

6 高级数学和三角函数

6.1 POWER函数

计算一个数的指数：

B1=POWER(A1, 2) //返回A1中数的平方

6.2 SQRT函数

计算平方根：

B1=SQRT(A1) //返回根号A1中的数

6.3 SIN、COS和TAN函数

计算角度的正弦值：

B1=SIN(A1)

计算角度的余弦值：

B1=COS(A1)

计算角度的正切值：

B1=TAN(A1)

6.4 ROUND函数

将数字四舍五入到指定的位数：

A1=ROUND(3.75, 0)   // 舍入到整数，结果为 4
B1=ROUND(3.75, 1)   // 舍入到一位小数，结果为 3.8
C1=ROUND(123, -1)   // 舍入到整十，结果为 120
D1=ROUND(4567, -2)  // 舍入到整百，结果为 4600
E1=ROUND(1234, -3)  // 负数表示舍入到整数位数，结果为 1000

ROUND函数的基本语法如下：

ROUND(number, num_digits)

其中，

• number：要舍入的数字。
• num_digits：舍入到的小数位数。可以为正数、负数或零。
  • 正数：舍入到指定的小数位数。
  • 负数：舍入到整数倍数，例如 -1 表示舍入到十位，-2 表示舍入到百位，以此类推。
  • 零：舍入到整数。

7 数据筛选和查找

7.1 VLOOKUP函数

基于某个键值在表格中按行查找数据：

VLOOKUP函数参数如下：

VLOOKUP(要查找的内容，要查找的内容所在范围，包含要返回的值的范围内的列号，近似(TRUE)或精确匹配(FALSE))

C1=VLOOKUP(A1, B1:D10, 2, FALSE)

注：当存在多条满足条件的记录时，只能返回第1个满足条件的记录。

7.2 HLOOKUP函数

基于某个键值在表格中按列查找数据，类似于VLOOKUP，HLOOKUP函数参数如下：

HLOOKUP(要查找的内容，要查找的内容所在范围，包含要返回的值的范围内的行号，近似(TRUE)或精确匹配(FALSE))

C1=HLOOKUP(A1, B1:D10, 2, FALSE)

注：当存在多条满足条件的记录时，只能返回第1个满足条件的记录。

7.3 INDEX和MATCH函数

结合使用INDEX和MATCH进行更灵活的查找：

C1=INDEX(B1:D10, MATCH(A1, A1:A10, 0), 2)

其中，INDEX 函数返回表格或区域中的值或值的引用，MATCH 函数在引用或数组中查找值并返回该值在所查找范围中的相对位置。

8 函数计算错误

Excel中如果遇到函数计算错误，可能有以下几种情况。

8.1 #DIV/0! - 除零错误

这个错误表示一个公式尝试除以零。

A1: 10
B1: 0
C1: =A1/B1   // #DIV/0!

解决方法： 在除数前加入条件判断，确保除数不为零。

C1: =IF(B1<>0, A1/B1, "N/A")

8.2 #VALUE! - 类型不匹配或无效的参数

这个错误表示公式中使用了无效或不匹配的数据类型。

A1: "ABC"
B1: =A1+1   // #VALUE!

解决方法： 确保公式中使用的参数类型匹配，或使用适当的函数进行类型转换。

B1: =VALUE(A1)+1   // 结果为 101

8.3 #NAME? - 未识别的函数或公式名

这个错误表示Excel无法识别使用的函数或公式名称。

A1: 10
B1: =SUMM(A1)   // #NAME?

解决方法：检查拼写错误，确保使用的是正确的函数或公式名称。

B1: =SUM(A1)   // 结果为 10

8.4 #REF! - 无效的引用

这个错误表示公式引用了不存在的单元格或范围。

A1: 10
B1: =A1+C1   // #REF!

解决方法： 确保公式引用的单元格或范围存在，并且没有被删除。

B1: =A1   // 结果为 10

8.5 #NUM! - 数值错误

这个错误表示公式包含了一个数学错误，例如负数的平方根。

A1: -1
B1: =SQRT(A1)   // #NUM!

解决方法： 检查公式中的数学运算，确保不包含无法计算的数学操作。

B1: =SQRT(ABS(A1))   // 结果为 1

8.6 #N/A! - 无法使用的值

这个错误表示公式中引用的值无法使用，通常是因为查找函数未找到匹配项。

A1: 100
B1: =VLOOKUP(A1, Sheet2!A:B, 2, FALSE)   // #N/A!

解决方法：使用适当的错误处理函数，例如IFNA，来处理这种情况。

B1: =IFNA(VLOOKUP(A1, Sheet2!A:B, 2, FALSE), "Not Found")