excel的公式大全：Excel公式大全，解锁数据处理与分析的超级工具

在现代办公和数据管理中,Microsoft Excel 是不可或缺的利器，而 Excel 的真正强大之处，很大程度上在于它的公式功能，掌握公式，就如同掌握了 Excel 的核心语言，能让你高效地处理数据、进行分析、做出决策，本文将为你梳理一些最常用、最实用的 Excel 公式，助你快速提升工作效率。

Excel 公式以 开头，可以执行计算、函数调用、引用数据等多种操作，理解公式的结构和常用函数是关键。

基础运算与单元格引用

1. 算术运算符：

   • 加法
   • 减法
   • 乘法
   • 除法
   • 百分比
   • ^ 乘方
   • 示例： =A1 + B1 (将 A1 和 B1 单元格的值相加)

2. 单元格引用：

   • A1：引用位于第一行第一列的单元格。
   • B2：引用位于第二行第二列的单元格。
   • A1:B5：引用从 A1 到 B5 的矩形区域。
   • Sheet1!A1：引用名为“Sheet1”的工作表中的 A1 单元格。
   • $A$1：绝对引用，复制公式时 A1 单元格的值不会改变。
   • A$1：混合引用，列相对变化，行绝对不变。
   • A1:：引用 A 列从第一行开始到当前行的区域。
   • :A1：引用当前列从第一行开始到 A1 单元格的区域。

常用函数大全

函数是预定义的公式,通常执行特定计算并返回结果，以下是几大类常用函数：

1. 统计函数：

   • SUM(值1, [值2], ...)：计算所有参数的总和。示例： =SUM(A1:A10) (计算 A1 到 A10 单元格的和)
   • AVERAGE(值1, [值2], ...)：计算所有参数的平均值。示例： =AVERAGE(B1:B5) (计算 B1 到 B5 的平均值)
   • COUNT(值1, [值2], ... )：计算参数列表中数字的个数。示例： =COUNT(C1:C10) (统计 C1 到 C10 区域内的数字个数)
   • COUNTA(值1, [值2], ... )：计算参数列表中非空值的个数。示例： =COUNTA(D1:D10) (统计 D1 到 D10 区域内的非空单元格数)
   • MAX(值1, [值2], ... )：返回所有参数中的最大值。示例： =MAX(E1:E10) (找出 E1 到 E10 的最大值)
   • MIN(值1, [值2], ... )：返回所有参数中的最小值。示例： =MIN(F1:F10) (找出 F1 到 F10 的最小值)

2. 文本函数：

   • TEXT(值, 文本格式)：将数值转换为指定格式的文本。示例： =TEXT(TODAY(), "yyyy-mm-dd") (将当前日期显示为 "2023-10-27" 格式)
   • CONCATENATE(文本1, [文本2], ... ) 或 &：连接多个文本字符串。示例： =CONCATENATE(A2, " ", B2) 或 =A2 & " " & B2 (将 A2 和 B2 单元格的内容用空格连接起来)
   • LEFT(文本, [数字])：返回文本字符串中指定数字的字符，位于字符串的开始处。示例： =LEFT(C2, 3) (返回 C2 单元格内容的前 3 个字符)
   • RIGHT(文本, [数字])：返回文本字符串中指定数字的字符，位于字符串的结尾处。示例： =RIGHT(D2, 2) (返回 D2 单元格内容的后 2 个字符)
   • MID(文本, 开始位置, 长度)：从文本字符串的指定位置开始，返回指定长度的字符。示例： =MID(E2, 5, 3) (从 E2 单元格内容的第 5 个字符开始，取 3 个字符)
   • TRIM(文本)：删除文本中多余的空格。示例： =TRIM(F2) (去除 F2 单元格内容两端和单词间的多余空格)
   • LOWER(文本)：将文本转换为小写。示例： =LOWER(G2) (将 G2 单元格内容转为小写)
   • UPPER(文本)：将文本转换为大写。示例： =UPPER(H2) (将 H2 单元格内容转为大写)
   • PROPER(文本)：将文本的首字母转换为大写，其余字母转换为小写。示例： =PROPER(I2) (规范 I2 单元格内容的大小写)

3. 逻辑函数：

   • IF(条件, [值如果为TRUE], [值如果为FALSE])：根据指定条件的真假返回不同的值。示例： =IF(B2 > 60, "及格", "不及格") (B2 大于 60，显示“及格”，否则显示“不及格”)
   • AND(条件1, [条件2], ... )：判断所有条件是否都为 TRUE，如果都为 TRUE，则返回 TRUE，否则返回 FALSE。示例： =AND(B2 > 60, C2 > 80) (判断 B2 是否大于 60 且 C2 是否大于 80)
   • OR(条件1, [条件2], ... )：判断任意条件是否为 TRUE，如果至少有一个条件为 TRUE，则返回 TRUE，全部为 FALSE 才返回 FALSE。示例： =OR(B2 > 90, C2 > 90) (判断 B2 或 C2 是否大于 90)
   • NOT(条件)：将 TRUE 变为 FALSE，将 FALSE 变为 TRUE。示例： =NOT(B2 > 100) (判断 B2 是否不大于 100)
   • TRUE()：返回逻辑值 TRUE。
   • FALSE()：返回逻辑值 FALSE。

4. 查找和引用函数：

   • VLOOKUP(查找值, 表格区域, 索引列号, [匹配方式])：在表格或区域的第一列中查找指定的值，并返回相同行中指定列号的数据。示例： =VLOOKUP(A2, E2:F10, 2, FALSE) (在 E2:F10 区域的第一列查找 A2 的值，然后返回找到行的第二列的值，精确匹配)
   • HLOOKUP(查找值, 表格区域, 索引行号, [匹配方式])：在表格或区域的第一行中查找指定的值，并返回相同列中指定行号的数据。(使用频率相对 VLOOKUP 低)
   • XLOOKUP(查找目标, 查找数组, 返回数组, [若未找到], [匹配模式], [搜索模式], [比较类型])：更强大的查找函数，可以向左查找、模糊查找等。(Excel 365/2021 新增)
   • INDEX(数组, [行号], [列号])：返回表格中行列交汇处的值，或数组中的指定元素。示例： =INDEX(E2:F10, 3, 2) (返回 E2:F10 区域中第 3 行第 2 列的值)
   • MATCH(查找值, 查找数组, [匹配模式])：在数组中查找指定值，返回该值在数组中的位置。示例： =MATCH(G2, A2:A10, 0) (在 A2:A10 中精确查找 G2 的值，返回其相对位置)
   • SUMIF(条件范围, 条件, [求和范围])：根据指定条件对区域中的单个单元格、单元格区域或数组中的数字求和。示例： =SUMIF(B2:B10, ">60", C2:C10) (统计 B2:B10 中大于 60 的对应 C2:C10 单元格的值之和)
   • SUMIFS(求和范围, 条件范围1, 条件1, [条件范围2], [条件2], ...)：根据多个条件对区域中的单个单元格、单元格区域或数组中的数字求和。示例： =SUMIFS(C2:C10, B2:B10, ">60", D2:D10, "<100") (统计 B2:B10 大于 60 且 D2:D10 小于 100 的对应 C2:C10 单元格的值之和)

5. 日期和时间函数：

   • TODAY()：返回当前日期。
   • NOW()：返回当前日期和时间。
   • DATE(年, 月, 日)：返回代表特定日期的序列号。示例： =DATE(2023, 10, 27)
   • DATEDIF(开始日期, 结束日期, [类型])：计算两个日期之间的差值。(相对冷门但非常有用) 示例： =DATEDIF(A2, B2, "y") (计算 A2 和 B2 之间的整年数)
   • YEAR(日期)：提取日期中的年份。
   • MONTH(日期)：提取日期中的月份。
   • DAY(日期)：提取日期中的日。
   • TEXT(值, "yyyy-mm-dd")：如前所述，可以格式化日期。

6. 数学和三角函数：

   • ROUND(数字, 小数位数)：将数字舍入到指定的小数位数。示例： =ROUND(12.3456, 2) (结果为 12.35)
   • ROUNDUP(数字, 小数位数)：向上舍入数字，远离零。示例： =ROUNDUP(12.3456, 2) (即使 12.3456，结果也是 12.35)
   • ROUNDDOWN(数字, 小数位数)：向下舍入数字，远离零。示例： =ROUNDDOWN(12.3456, 2) (结果为 12.34)
   • ABS(数字)：返回数字的绝对值。
   • SQRT(数字)：返回数字的平方根。
   • PI()：返回圆周率 π 的值。
   • SIN(数字)：返回数字（角度）的正弦值。(需将角度转换为弧度，或使用 RADIANS 函数)
   • COS(数字)：返回数字（角度）的余弦值。
   • TAN(数字)：返回数字（角度）的正切值。

7. 信息函数：

   • ISBLANK(值)：检查值是否为空。示例： =IF(ISBLANK(A2), "未填写", "已填写")
   • ISTEXT(值)：检查值是否为文本。
   • ISNUMBER(值)：检查值是否为数字。
   • ISLOGICAL(值)：检查值是否为逻辑值 TRUE 或 FALSE。
   • TYPE(值)：返回值的数据类型代码。

高级技巧与注意事项

• 数组公式： 在输入完公式后，通常需要按 Ctrl + Shift + Enter 而不是仅仅 Enter，Excel 365 的动态数组公式简化了这一点，输入后按 Enter 即可。
• 名称管理器： 可以为复杂的范围或公式定义名称，使引用更清晰、公式更易读。
• 错误值处理： 使用 IFERROR(表达式, 错误值替换) 来优雅地处理可能出现的错误（如 #N/A, #VALUE! 等）。
• F4 键： 在公式中引用单元格时，按 F4 键可以在相对引用、绝对引用、混合引用（行或列绝对）之间切换。
• 使用向导： Excel 提供了函数向导（按 Shift + F3 或在函数插入对话框中点击“插入函数”按钮），可以帮助你查找和使用函数。

Excel 公式的世界远不止这些，这只是冰山一角，掌握这些基础和常用公式，足以解决大部分日常工作中的数据处理需求，最重要的是勤加练习，结合实际工作场景去应用和探索，当你熟练运用这些公式时，你会发现 Excel 的强大功能，工作效率将得到质的飞跃，建议多查阅 Excel 帮助文档，或利用在线资源（如 Microsoft Learn, ExcelIsFun 等）进行更深入的学习，祝你 Excel 之路越走越宽广！