Excel函数公式大全

Excel函数公式大全Excel函数是提升数据处理效率、实现自动化分析的核心工具。掌握常用函数，不仅能简化复杂运算，更能让数据“说话”。本文将系统梳理Excel中最实用、最核心的函数类别及其典型应用，旨在为不同层次的用户提供一份清晰、易用的参考指南。我们将避免枯燥的理论堆砌，侧重于实际场景中的灵活运用与常见技巧。一、基础数据运算与统计这类函数是Excel的基石，用于对数值型数据进行快速计算和概括性描述。SUM函数：求和作用：计算指定单元格区域内所有数值的总和。语法：`SUM(number1,[number2],...)`示例：`=SUM(A1:A10)`计算A1到A10单元格区域的数值总和。若区域中包含文本，文本将被忽略。AVERAGE函数：求平均值作用：计算指定单元格区域内所有数值的算术平均值。语法：`AVERAGE(number1,[number2],...)`示例：`=AVERAGE(B2:B20)`计算B2到B20单元格区域数值的平均值，同样忽略文本和空单元格。COUNT函数：计数作用：计算指定单元格区域内包含数字的单元格个数。语法：`COUNT(value1,[value2],...)`示例：`=COUNT(C1:C50)`返回C1到C50中包含数字的单元格数量，空单元格、文本或错误值不计数。MAX与MIN函数：最大值与最小值作用：分别返回指定单元格区域内的最大数值和最小数值。语法：`MAX(number1,[number2],...)`，`MIN(number1,[number2],...)`示例：`=MAX(D1:D100)`找出D列前100行中的最大数；`=MIN(D1:D100)`找出最小数。COUNTIF与SUMIF函数：条件计数与条件求和作用：COUNTIF对满足单个条件的单元格进行计数；SUMIF对满足单个条件的单元格数值求和。语法：`COUNTIF(range,criteria)`，`SUMIF(range,criteria,[sum_range])`示例：*`=COUNTIF(E:E,"完成")`统计E列中值为“完成”的单元格个数。*`=SUMIF(F:F,"部门A",G:G)`在F列中找到所有“部门A”对应的G列数值并求和。二、数据查找与引用当数据量庞大时，高效定位和提取所需信息至关重要，这类函数为此提供了强大支持。VLOOKUP函数：垂直查找作用：在表格或区域的首列查找指定的值，并返回该值所在行中指定列处的数值。语法：`VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])`*`lookup_value`：要查找的值。*`table_array`：要查找的区域，首列必须包含`lookup_value`。*`col_index_num`：返回数据在`table_array`中相对于首列的列号。*`range_lookup`：可选，`TRUE`（默认）为近似匹配，`FALSE`为精确匹配。示例：`=VLOOKUP("产品A",A1:C100,3,FALSE)`在A1到C100的首列（A列）查找“产品A”，并返回其所在行第3列（C列）的值。注意，VLOOKUP的查找值必须在首列。INDEX与MATCH函数：灵活查找组合作用：*`INDEX`：返回表格或区域中指定行与列交叉处的数值。*`MATCH`：返回指定值在指定数组中的相对位置。两者结合可实现比VLOOKUP更灵活的查找，尤其适用于反向查找或查找列不在首列的情况。语法：*`INDEX(array,row_num,[column_num])`*`MATCH(lookup_value,lookup_array,[match_type])`(`match_type`0为精确匹配)示例：`=INDEX(C1:C100,MATCH("产品B",A1:A100,0))`先用MATCH在A1:A100中找到“产品B”的行号，再用INDEX返回C列对应行号的值。这等效于VLOOKUP，但查找列可以是任意列。OFFSET函数：动态引用作用：以指定的引用为起点，通过给定偏移量得到新的引用。语法：`OFFSET(reference,rows,cols,[height],[width])`示例：`=OFFSET(A1,2,3,1,1)`表示从A1单元格开始，向下偏移2行，向右偏移3列，引用1行1列的区域（即D3单元格）。常用于创建动态图表或动态数据区域。三、文本处理与转换在数据清洗、规范和格式化过程中，文本函数扮演着不可或缺的角色。LEFT,RIGHT,MID函数：文本提取作用：*`LEFT(text,[num_chars])`：从文本字符串的左侧开始提取指定数目的字符。*`RIGHT(text,[num_chars])`：从文本字符串的右侧开始提取指定数目的字符。*`MID(text,start_num,num_chars)`：从文本字符串的指定位置开始提取指定数目的字符。示例：*`=LEFT("Excel函数",2)`返回“Ex”。*`=RIGHT("Excel函数",2)`返回“函数”。*`=MID("Excel函数",3,2)`返回“ce”。CONCATENATE函数(或&运算符)：文本合并作用：将多个文本字符串合并为一个文本字符串。`&`运算符也能实现相同功能，且更简洁。语法：`CONCATENATE(text1,[text2],...)`或`text1&text2&...`示例：`=CONCATENATE(A2,"",B2)`或`=A2&""&B2`，将A2和B2的文本用空格连接起来。TEXT函数：数值转文本（格式化）作用：将数值转换为按指定数字格式表示的文本。语法：`TEXT(value,format_text)`示例：`=TEXT(NOW(),"yyyy年mm月dd日")`将当前日期转换为“2023年10月05日”这样的文本格式。`=TEXT(1234.56,"￥#,##0.00")`显示为“￥1,234.56”。TRIM函数：清除多余空格作用：清除文本中多余的空格，包括开头、结尾的空格以及单词之间的多个空格（只保留一个）。语法：`TRIM(text)`示例：`=TRIM("Excel函数")`返回“Excel函数”。SUBSTITUTE函数：文本替换作用：在文本字符串中用新文本替换旧文本。语法：`SUBSTITUTE(text,old_text,new_text,[instance_num])`*`instance_num`：可选，指定替换第几次出现的旧文本。示例：`=SUBSTITUTE("Excel2016","2016","2021")`返回“Excel2021”。四、日期与时间处理Excel对日期和时间有特殊的存储方式，相关函数能轻松实现日期计算、提取等操作。TODAY与NOW函数：获取当前日期和时间作用：*`TODAY()`：返回当前系统日期（不包含时间），且会自动更新。*`NOW()`：返回当前系统日期和时间，且会自动更新。语法：无参数。示例：`=TODAY()`返回类似“2023/10/05”的日期。`=NOW()`返回类似“2023/10/0514:30:22”的日期时间。DATEDIF函数：计算日期差作用：计算两个日期之间的年数、月数或天数。（注意：此函数在Excel中未完全公开，但非常实用）语法：`DATEDIF(start_date,end_date,unit)`*`unit`："Y"（年）,"M"（月）,"D"（日）,"MD"（忽略年和月的日差）,"YM"（忽略年的月差）,"YD"（忽略年的日差）。示例：`=DATEDIF("2020/1/1",TODAY(),"Y")`计算从2020年1月1日到今天过了多少年。YEAR,MONTH,DAY函数：提取年、月、日作用：从给定日期中分别提取出年份、月份、日。语法：`YEAR(serial_number)`,`MONTH(serial_number)`,`DAY(serial_number)`示例：`=YEAR("2023/10/05")`返回2023。`=MONTH("2023/10/05")`返回10。DATE函数：创建日期作用：根据给定的年、月、日参数创建一个日期。语法：`DATE(year,month,day)`示例：`=DATE(2023,10,5)`返回日期值“2023/10/05”。五、逻辑判断与条件计算通过逻辑函数，可以让Excel根据特定条件自动做出判断并返回相应结果。IF函数：条件判断作用：执行真假值判断，并根据逻辑测试的真假值返回不同的结果。语法：`IF(logical_test,value_if_true,[value_if_false])`示例：`=IF(A2>=60,"及格","不及格")`如果A2单元格的值大于等于60，则返回“及格”，否则返回“不及格”。IF函数可以嵌套，实现更复杂的多条件判断，但嵌套层数不宜过多，以免公式难以维护。AND与OR函数：多条件组合判断作用：*`AND(logical1,[logical2],...)`：所有参数的逻辑值为真时，返回TRUE；只要有一个为假，返回FALSE。*`OR(logical1,[logical2],...)`：任何一个参数的逻辑值为真时，返回TRUE；所有都为假，返回FALSE。示例：`=IF(AND(A2>80,B2>80),"优秀","")`如果A2和B2都大于80，则返回“优秀”。IS类函数：信息判断作用：用于检验数值的类型或状态，并返回TRUE或FALSE。常用的有`ISNUMBER`（是否为数字）、`ISBLANK`（是否为空单元格）、`ISTEXT`（是否为文本）等。语法：`ISNUMBER(value)`,`ISBLANK(value)`,等。示例：`=IF(ISNUMBER(A2),A2*2,"非数字")`如果A2是数字，则乘以2，否则显示“非数字”。六、进阶数据统计与分析掌握这些函数，能应对更复杂的数据分析场景，实现高效的数据汇总。SUMIFS函数：多条件求和作用：对满足多个条件的单元格区域求和。语法：`SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)`示例：`=SUMIFS(G:G,F:F,"部门A",H:H,"男")`对F列是“部门A”且H列是“男”的对应G列数值求和。COUNTIFS函数：多条件计数作用：对满足多个条件的单元格区域进行计数。语法：`COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...)`示例：`=COUNTIFS(A:A,">=2023/1/1",A:A,"<=2023/12/31",B:B,"已完成")`统计A列日期在2023年内且B列状态为“已完成”的记录数。SUMPRODUCT函数：数组乘积之和作用：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。它非常灵活，常被用来实现多条件求和、计数等复杂逻辑。语法：`SUMPRODUCT(array1,[array2],[array3],...)`示例：`=SUMPRODUCT((F:F="部门A")*(H:H="男")*(G:G))`这与前面SUMIFS的示例效果相同，通过数组逻辑判断实现多条件求和。总结与建议Excel函数的世界博大精深，本文仅涵盖了