2024全网最全Excel函数与公式应用

引言

Excel是一款广泛应用于商业、教育和个人领域的电子表格软件。它提供了许多强大的函数和公式，可以帮助我们处理和分析数据，提高工作效率。本文将介绍一些常用的Excel函数和公式，并提供详细的应用示例，帮助读者更好地掌握Excel的数据处理能力。

一、基础函数与公式

1. SUMIFS函数：用于根据多个条件对指定范围内的数值进行求和。
2. AVERAGEIFS函数：根据多个条件计算指定范围内数值的平均值。
3. COUNTIF函数：根据指定条件计算满足条件的单元格个数。
4. COUNTIFS函数：根据多个条件计算满足条件的单元格个数。
5. ROUND函数：用于四舍五入一个数值到指定的小数位数。
6. INT函数：返回一个数值的整数部分，将小数部分舍去。
7. MOD函数：返回两个数的余数。
8. ABS函数：返回一个数值的绝对值。
9. EXP函数：返回自然常数e的指数幂。
10. LOG函数：返回一个数值的对数。

二、逻辑函数与公式

1. AND函数：用于对多个条件进行逻辑与运算，只有所有条件均为真时才返回真。
2. OR函数：用于对多个条件进行逻辑或运算，只要有一个条件为真就返回真。
3. NOT函数：对逻辑值进行取反操作，将真变为假，假变为真。
4. IF函数：根据指定条件进行判断，为真时返回指定值，为假时返回另一个值。
5. IFERROR函数：用于判断公式计算是否出错，若有错误则返回指定值，否则返回公式计算结果。

以下是逻辑函数的一些示例应用：

AND函数示例

假设我们有一个销售数据表格，其中列A是销售额，列B是利润率。我们想判断哪些销售既达到了销售额目标（大于等于1000），又具有良好的利润率（大于等于0.1）。可以使用AND函数来判断。在C2单元格中输入以下公式：

=AND(A2>=1000, B2>=0.1)

然后将公式拖动到其他单元格中，即可判断每一行中的销售是否同时满足目标销售额和利润率。

OR函数示例

继续上述销售数据表格的例子，我们想找出那些销售以及达到了销售额目标（大于等于1000），或具有良好的利润率（大于等于0.1）。可以使用OR函数来判断。在D2单元格中输入以下公式：

=OR(A2>=1000, B2>=0.1)

然后将公式拖动到其他单元格中，即可判断每一行中的销售是否达到目标销售额或满足利润率要求。

NOT函数示例

假设我们有一个表格，其中列A中记录了某种产品的库存状态（“有货"或"缺货”）。现在我们想创建一列B来表示是否缺货，可以使用NOT函数来实现。在B2单元格中输入以下公式：

=NOT(A2="有货")

然后将公式拖动到其他单元格中，即可判断每一行中的产品是否缺货。

IF函数示例

假设我们有一个学生成绩表格，其中列A是学生的分数。我们想判断每个学生的成绩是否及格（大于等于60分）。可以使用IF函数来实现。在B2单元格中输入以下公式：

=IF(A2>=60, "及格", "不及格")

然后将公式拖动到其他单元格中，即可根据学生的分数判断其是否及格。

IFERROR函数示例

假设我们有一个表格，在A列和B列中分别记录了数值，C列中想计算A列对应元素除以B列对应元素的结果。有时候B列中可能存在0，会导致除法错误。为了避免错误出现，我们可以使用IFERROR函数。在C2单元格中输入以下公式：

=IFERROR(A2/B2, "除数为0")

如果B列的值为0，那么C列将显示"除数为0"；否则，C列将显示除法的计算结果。

这些示例演示了逻辑函数在Excel中的应用。它们可以帮助您根据条件进行逻辑判断、避免错误和处理数据。根据您实际的需求，您可以调整这些函数的参数和条件，以满足不同的计算要求。

三、文本函数与公式

1. LEFT函数：返回文本字符串的左边指定数量的字符。
2. RIGHT函数：返回文本字符串的右边指定数量的字符。
3. MID函数：返回文本字符串中指定起始位置和长度的字符。
4. LEN函数：返回文本字符串的长度。
5. CONCATENATE函数：将多个文本字符串连接成一个字符串。
6. SUBSTITUTE函数：替换文本字符串中的指定内容。

以下是关于文本函数与公式的示例应用：

LEFT函数示例

假设我们有一列包含学生的姓名，现在我们想从姓名中提取出每个学生的姓氏（前面的部分）。在B2单元格中输入以下公式：

=LEFT(A2, 1)

然后将公式拖动到其他单元格中，即可提取每个学生姓名的姓氏。

RIGHT函数示例

如果我们有一列包含学生的学号，现在我们想从学号中提取后面的两位数字，以表示班级编号。在B2单元格中输入以下公式：

=RIGHT(A2, 2)

然后将公式拖动到其他单元格中，即可提取每个学生学号中的班级编号。

MID函数示例

假设我们有一列包含学生的身份证号码，现在我们想从身份证号码中提取出生日期（中间8位数字）。在B2单元格中输入以下公式：

=MID(A2, 7, 8)

然后将公式拖动到其他单元格中，即可提取每个学生身份证号码中的出生日期。

LEN函数示例

如果我们有一个文本字符串，现在我们想知道它的长度是多少。在B2单元格中输入以下公式：

=LEN(A2)

然后将公式拖动到其他单元格中，即可计算每个文本字符串的长度。

CONCATENATE函数示例

如果我们有名字和姓氏的列，并且想要将它们连接在一起形成完整的姓名。在C2单元格中输入以下公式：

=CONCATENATE(A2, " ", B2)

然后将公式拖动到其他单元格中，即可将每个学生的名字和姓氏连接成完整的姓名。

SUBSTITUTE函数示例

假设我们有一个包含文本字符串的列，其中包含一个特定的单词或短语。现在我们想要将其中的特定单词替换为另一个单词。在B2单元格中输入以下公式：

=SUBSTITUTE(A2, "旧单词", "新单词")

然后将公式拖动到其他单元格中，即可替换每个文本字符串中的特定单词或短语。

以上示例演示了文本函数与公式在Excel中的应用。这些函数能够帮助您提取、连接和操作文本字符串，从而实现更复杂的数据处理和分析任务。根据您的具体需求和数据结构，您可以灵活运用这些函数，并结合其他函数进行更复杂的文本处理操作。

四、日期和时间函数与公式

1. TODAY函数：返回当前日期。
2. NOW函数：返回当前日期和时间。
3. DATE函数：根据指定的年、月、日返回日期。
4. TIME函数：根据指定的小时、分钟、秒返回时间。
5. YEAR函数：返回一个日期的年份。
6. MONTH函数：返回一个日期的月份。
7. DAY函数：返回一个日期的日份。
8. HOUR函数：返回一个时间的小时数。
9. MINUTE函数：返回一个时间的分钟数。
10. SECOND函数：返回一个时间的秒数。
11. DATEDIF函数：计算两个日期之间的天数、月数或年数。

以下是日期和时间函数的示例应用：

TODAY函数示例

TODAY函数可以返回当前日期。在A1单元格中输入以下公式：

=TODAY()

按下回车键后，A1单元格将显示当前日期。

NOW函数示例

NOW函数可以返回当前日期和时间。在B1单元格中输入以下公式：

=NOW()

按下回车键后，B1单元格将显示当前日期和时间。

DATE函数示例

DATE函数可以根据指定的年、月、日返回日期。在C1单元格中输入以下公式：

=DATE(2022, 12, 31)

按下回车键后，C1单元格将显示指定年月日的日期，即2022年12月31日。

TIME函数示例

TIME函数可以根据指定的小时、分钟、秒返回时间。在D1单元格中输入以下公式：

=TIME(10, 30, 0)

按下回车键后，D1单元格将显示指定小时分钟秒的时间，即10:30:00。

YEAR函数示例

YEAR函数可以返回一个日期的年份。假设在A1单元格中输入日期"2023/06/15"，在B1单元格中输入以下公式：

=YEAR(A1)

按下回车键后，B1单元格将显示该日期的年份，即2023。

MONTH函数示例

MONTH函数可以返回一个日期的月份。假设在A1单元格中输入日期"2023/06/15"，在B1单元格中输入以下公式：

=MONTH(A1)

按下回车键后，B1单元格将显示该日期的月份，即6。

DAY函数示例

DAY函数可以返回一个日期的日份。假设在A1单元格中输入日期"2023/06/15"，在B1单元格中输入以下公式：

=DAY(A1)

按下回车键后，B1单元格将显示该日期的日份，即15。

HOUR函数示例

HOUR函数可以返回一个时间的小时数。假设在A1单元格中输入时间"15:30:00"，在B1单元格中输入以下公式：

=HOUR(A1)

按下回车键后，B1单元格将显示该时间的小时数，即15。

MINUTE函数示例

MINUTE函数可以返回一个时间的分钟数。假设在A1单元格中输入时间"15:30:00"，在B1单元格中输入以下公式：

=MINUTE(A1)

按下回车键后，B1单元格将显示该时间的分钟数，即30。

SECOND函数示例

SECOND函数可以返回一个时间的秒数。假设在A1单元格中输入时间"15:30:45"，在B1单元格中输入以下公式：

=SECOND(A1)

按下回车键后，B1单元格将显示该时间的秒数，即45。

DATEDIF函数示例

DATEDIF函数可以计算两个日期之间的天数、月数或年数。在A1、B1单元格分别输入起始日期和结束日期，然后在C1单元格中输入以下公式：

=DATEDIF(A1, B1, "D")

按下回车键后，C1单元格将显示起始日期和结束日期之间的天数差。

以上示例演示了日期和时间函数在Excel中的应用。这些函数可以帮助您进行日期和时间的计算、提取和格式转换。根据您的具体需求，您可以灵活地组合和调整这些函数，以满足不同的日期和时间处理要求。

五、统计函数与公式

1. MEDIAN函数：返回一组数值的中位数。
2. MODE函数：返回一组数值中出现次数最多的数值。
3. STDEV函数：计算一组数值的标准差。
4. VAR函数：计算一组数值的方差。
5. RANK函数：计算一组数值在该组数值中的排名。

以下是统计函数的示例应用：

MEDIAN函数示例

MEDIAN函数可以返回一组数值的中位数。假设在A1到A5单元格中分别输入数值1, 2, 3, 4, 5，那么在B1单元格中输入以下公式：

=MEDIAN(A1:A5)

按下回车键后，B1单元格将显示这组数值的中位数，即3。

MODE函数示例

MODE函数可以返回一组数值中出现次数最多的数值。假设在A1到A7单元格中分别输入数值2, 3, 4, 3, 2, 4, 4，那么在B1单元格中输入以下公式：

=MODE(A1:A7)

按下回车键后，B1单元格将显示这组数值中出现次数最多的数值，即4。

STDEV函数示例

STDEV函数可以计算一组数值的标准差。假设在A1到A5单元格中分别输入数值1, 2, 3, 4, 5，那么在B1单元格中输入以下公式：

=STDEV(A1:A5)

按下回车键后，B1单元格将显示这组数值的标准差。

VAR函数示例

VAR函数可以计算一组数值的方差。假设在A1到A5单元格中分别输入数值1, 2, 3, 4, 5，那么在B1单元格中输入以下公式：

=VAR(A1:A5)

按下回车键后，B1单元格将显示这组数值的方差。

RANK函数示例

RANK函数可以计算一组数值在该组数值中的排名。假设在A1到A5单元格中分别输入数值85, 92, 78, 88, 96，那么在B1单元格中输入以下公式：

=RANK(A2, A$2:A$6)

按下回车键后，B1单元格将显示数值92在该组数值中的排名。

以上示例演示了统计函数在Excel中的应用。这些函数可以帮助您对一组数值进行基本的统计分析，如计算中位数、众数、标准差、方差和排名等。根据您的具体需求和数据集，您可以灵活地使用这些函数，并结合其他函数进行更复杂的统计分析。

六、条件函数与公式

1. VLOOKUP函数：根据指定的查找值，在数据区域中进行垂直查找并返回相应的值。
2. HLOOKUP函数：根据指定的查找值，在数据区域中进行水平查找并返回相应的值。
3. INDEX函数和MATCH函数：结合使用可以实现更灵活的数据查找与定位。

以下是查找和定位函数的示例应用：

VLOOKUP函数示例

VLOOKUP函数可以根据指定的查找值，在数据区域中进行垂直查找并返回相应的值。假设在A1到B4单元格中输入以下数据：

| 学生姓名 | 成绩 |
|---------|------|
| 张三    | 85   |
| 李四    | 92   |
| 王五    | 78   |

现在，在D1单元格中输入要查找的学生姓名（比如"李四"），然后在E1单元格中输入以下公式：

=VLOOKUP(D1, A1:B4, 2, FALSE)

按下回车键后，E1单元格将显示查找到的学生姓名对应的成绩，即92。

HLOOKUP函数示例

HLOOKUP函数可以根据指定的查找值，在数据区域中进行水平查找并返回相应的值。假设在A1到D2单元格中输入以下数据：

|     | 英语 | 数学 | 物理 |
|-----|------|------|------|
| 张三 | 85   | 90   | 95   |

现在，在A4单元格中输入要查找的学生姓名（比如"张三"），然后在B4单元格中输入以下公式：

=HLOOKUP(A4, A1:D2, 2, FALSE)

按下回车键后，B4单元格将显示查找到的学生姓名对应的英语成绩，即85。

INDEX函数和MATCH函数示例

INDEX函数和MATCH函数可以结合使用，实现更灵活的数据查找与定位。假设在A1到C4单元格中输入以下数据：

| 学生姓名 | 英语 | 数学 |
|---------|------|------|
| 张三    | 85   | 90   |
| 李四    | 92   | 88   |
| 王五    | 78   | 94   |

现在，在E1单元格中输入要查找的学生姓名（比如"王五"），然后在F1单元格中输入以下公式：

=INDEX(B2:C4, MATCH(E1, A2:A4, 0), 2)

按下回车键后，F1单元格将显示查找到的学生姓名对应的数学成绩，即94。

以上示例演示了查找和定位函数在Excel中的应用。这些函数可以帮助您根据指定的查找值在数据区域中进行垂直或水平的查找，并返回相应的值。通过灵活地使用这些函数，您可以进行各种数据查询和定位操作，从而更好地分析和处理数据。

七、高级函数与公式

1. IFNA函数：用于处理公式中的#N/A错误值，返回指定的值。
2. INDEX-MATCH函数组合：通过MATCH函数找到查找值在数据列中的位置，再使用INDEX函数返回相应的结果。
3. INDIRECT函数：根据一个字符串构造一个单元格或区域的引用。
4. CHOOSE函数：根据序号从一组值中选择返回一个值。

以下是更多函数的示例应用：

IFNA函数示例

IFNA函数用于处理公式中的#N/A错误值。假设在A1单元格中输入了一个VLOOKUP函数，该函数可能返回#N/A错误值。在B1单元格中输入以下公式：

=IFNA(A1, "Not Found")

按下回车键后，如果A1单元格的值是#N/A，那么B1单元格将显示"Not Found"；否则，B1单元格将显示A1单元格的值。

INDEX-MATCH函数组合示例

INDEX函数和MATCH函数可以结合使用，通过MATCH函数找到查找值在数据列中的位置，然后使用INDEX函数返回相应的结果。假设在A1到B4单元格中输入以下数据：

| 姓名   | 年龄 |
|--------|------|
| 张三   | 25   |
| 李四   | 30   |
| 王五   | 28   |

现在，在D1单元格中输入要查找的姓名（比如"李四"），然后在E1单元格中输入以下公式：

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

按下回车键后，E1单元格将显示查找到的姓名对应的年龄，即30。

INDIRECT函数示例

INDIRECT函数可以根据一个字符串构造一个单元格或区域的引用。假设在A1到A5单元格中分别输入数值1到5，然后在B1单元格中输入以下公式：

=INDIRECT("A"&A1)

按下回车键后，B1单元格将显示A1单元格的值，即1。此处，“A”&A1将动态构造出"A1"的字符串，INDIRECT函数根据该字符串返回对应的单元格的值。

CHOOSE函数示例

CHOOSE函数根据序号从一组值中选择返回一个值。假设在A1到A3单元格中分别输入数值10, 20, 30，然后在B1单元格中输入以下公式：

=CHOOSE(2, A1, A2, A3)

按下回车键后，B1单元格将显示第2个参数对应的值，即20。

以上示例展示了更多函数在Excel中的用法。根据具体的需求，您可以灵活地使用这些函数，处理各种数据场景，并提取所需的结果。

结论

本文介绍了Excel中的基础函数与公式、逻辑函数与公式、文本函数与公式、日期和时间函数与公式、统计函数与公式、条件函数6. PRODUCT函数：用于计算指定范围内数值的乘积。
7. ROUND函数：用于将数值四舍五入到指定的小数位数。
8. COUNTA函数：用于计算指定范围内的非空单元格数量。
9. SUMIF函数：根据指定条件对指定范围内的数值进行求和。
10. AVERAGEIF函数：根据指定条件对指定范围内的数值进行求平均值。
11. COUNTIF函数：根据指定条件计算指定范围内满足条件的单元格数量。
12. CONCATENATE函数：用于连接多个文本字符串。
13. LEFT函数和RIGHT函数：分别用于提取文本字符串的左边和右边的指定字符数。
14. LEN函数：用于计算文本字符串的长度。
15. MID函数：用于提取文本字符串中指定位置和长度的子字符串。
16. TRIM函数：用于去除文本字符串中的空格字符。
17. UPPER函数和LOWER函数：分别用于将文本字符串转换为大写和小写。
18. PROPER函数：用于将文本字符串的每个单词的首字母转换为大写。
19. DATE函数：用于创建日期，可指定年、月、日。
20. TIME函数：用于创建时间，可指定时、分、秒。

这些基础函数与公式是Excel中常用且非常实用的工具。通过熟练掌握它们的用法，您可以更轻松地进行数值计算、条件判断、文本处理和日期时间计算等操作。在实际应用中，结合不同的函数和公式，您可以更灵活地处理各种数据和情况，提高工作效率。接下来，我们将介绍逻辑函数与公式的应用。

⭐️ 好书推荐

《Excel函数与公式应用大全for Excel 365 & Excel 2021》

【内容简介】

本书以 Excel 2021 为蓝本，全面系统地介绍了 Excel 365 & Excel 2021 函数与公式的技术原理、应用技巧与实战案例。内容包括函数与公式基础，文本处理、查找引用、统计求和、Web 类函数、宏表函数、自定义函数、数据库函数等常用函数的应用，以及数组公式、动态数组、多维引用等。

本书采用循序渐进的方式，由易到难地介绍各个知识点，适合各个水平的 Excel 用户，既可作为初学者的入门指南，又可作为中、高级用户的参考手册。

📚 京东购买链接：《Excel函数与公式应用大全for Excel 365 & Excel 2021》

《AI提示工程实战：从零开始利用提示工程学习应用大语言模型》

【内容简介】

随着大语言模型的快速发展，语言AI已经进入了新的阶段。这种新型的语言AI模型具有强大的自然语言处理能力，能够理解和生成人类语言，从而在许多领域中都有广泛的应用前景。大语言模型的出现将深刻影响人类的生产和生活方式。本书将介绍提示工程的基本概念和实践，旨在帮助读者了解如何构建高质量的提示内容，以便更高效地利用大语言模型进行工作和学习。

本书内容通俗易懂，案例丰富，适合所有对大语言模型和提示工程感兴趣的读者。无论是初学者还是进阶读者，都可以从本书中获得有价值的信息和实用技巧，帮助他们更好地应对各种挑战和问题。

📚 京东购买链接：《AI提示工程实战：从零开始利用提示工程学习应用大语言模型》