常用函数公式大全-常用函数公式大全

在用 Excel 进行数据处理、财务核算以及科学计算时，掌握高效的函数公式是提升工作效率的关键所在。许多新手往往在处理复杂数据时感到束手无策，这很大程度上是因为对常用的函数公式缺乏系统的认识和理解。其实，Excel 内置的强大函数库如同一个数据处理的工具箱，涵盖了从基础统计到高级分析的方方面面。那些超过 10 年的经验积累，正是通过无数次实战应用，筛选出了最具实用价值的函数公式大全。如今，面对琳琅满目的函数，如何快速寻找到适合自己的工具，成为了每一个数据工作者必须克服的痛点。本文将结合行业最佳实践，为您详细梳理常用函数公式大全的核心内容，并辅以真实案例，帮助您构建一套灵活高效的工作流。

数据筛选与统计类功能解析

在进行数据分析的第一步，往往需要先从杂乱无章的数据中找出规律。这一过程主要由“筛选”和“统计”两大核心功能完成。

• IF 函数
• IF 函数是逻辑判断的基石，其结构为 `IF(条件表达式，真值 [， 假值])`。它允许你根据特定条件在不同单元格中展示不同的结果。
• IF(A1>=100,A1B1,0) 表示：如果 A1 大于等于 100，则输出 A1 乘以 B1 的结果；否则输出 0。
• 筛选函数 如 `FILTER` 和 `SUMIF`，用于从数据源中提取满足特定条件的记录。
• FILTER(A1:B100, C1:C100) 将返回 C 列中大于 50 的行数据。
• SUMIF(A1:B100, ">1", D1:D100) 对 A 列中大于 1 的数值求和并填入 D 列。
• 统计函数 如 `COUNT` 和 `AVERAGE`，用于快速计算数据的数量与平均值。
• AVERAGE(A1:A10) 计算 A 列数据的中位数。
• MAX(B1:B100) 找出 B 列中的最大数值。
• 分面函数 如 `SUMIFS` 和 `COUNTIFS`，支持多条件组合筛选。
• SUMIFS(A1:C100, A1:A20, ">50", B1:B20, "A") 仅对 A 列大于 50 且 B 列为 A 的行求和。
• 排序与查找 如 `SORT` 和 `VLOOKUP` 或 `XLOOKUP`，用于整理数据顺序及跨区域查找。
• SORT(A1:C100) 按第三列对前两列数据排序。
• XLOOKUP(A1, B1:B100, D1:D100, 0) 在 B 列范围内精确查找 A 列的值，若未找到则返回 0。
• 条件统计进阶 如 `COUNTIFS` 和 `SUBTOTAL`，用于更复杂的统计场景。
• COUNTIFS(A1:A100, ">50", B1:B100, "A") 统计同时满足两个条件的记录数量。
• SUBTOTAL(A1:A100, 2) 仅计算数值大于 50 的数据项数量，忽略空值和负数。

财务计算与管理类功能深化

财务领域的数据处理同样离不开一些核心公式，它们能迅速生成报表、分摊成本并预判趋势。

• 加权平均数计算 适用于加权平均值的精确计算，确保不同指标权重被正确考量。
• IFERROR(VOUNT(A1:A100, 1000)) 将 COUNTA 函数与 1000 万单元格数结合，防止对空单元格报错。
• IFERROR(VOUNT(A1:A100, 1000)) 此处 R 代表返回 0 作为默认值，避免计算中断。
• 期末成本分摊 利用 `FIND` 和 `REPLACE` 函数处理文本中的数字，实现成本精确拆分。
• TRIM(" 500 元 ") 去除首尾空格。
• REPLACE("500",1,1,"100") 将第一个数字替换为 100。
• 独立组平均法 结合 `SUM`, `COUNT` 和 `AVERAGE` 实现多组数据的独立平均计算。
• SUMIF(B1:B100, "XX", A1:A100) 对 B 列值为"XX"的行求和。
• COUNTIF(C1:C100, ">100") 统计 C 列大于 100 的记录数量。
• 数组公式应用 在特定条件下使用数组公式处理多行数据。
• IF(A1=$H$1, H1, C1) 若 A1 与 H1 相等，则输出 H1 值；否则输出 C1 值。
• INDIRECT("A1:A100") 利用 INDIRECT 函数动态引用包含数字的区域列。
• 税率分摊计算 结合数组公式实现多项目税率的精确分摊。
• IFERROR(INDIRECT("A1:A100"), 0) 这是一个示例，实际应用中可能涉及更复杂的索引函数组合。
• 复利计算 利用 `FV` 函数（Future Value）进行复利总额计算。
• 10000 (1+0.1)^12 计算 10000 元按 10% 年利率复利 12 年的终值。
• 杠杆比率分析 结合 `D1` 和 `E1` 计算财务杠杆系数。
• (1 + (D1/E1))200() 模拟杠杆比率的计算过程。
• D1/E1 此处代表 D 1 除以 E 1，即资本投入产出比。

日期时间管理与序列构建

时间管理是财务与行政工作中不可或缺的一环，`DATE` 系列函数和文本格式化函数是其中的重中之重。

• 日期运算基础 利用 `DATE` 函数创建新的日期对象，或进行日期相减计算天数。
• DATE(2023, 12, 31) 创建指定日期。
• DATE(2023, 12, 31) - DATE(2023, 12, 30) 计算两天之间的天数差。
• DAYS(2023, 12, 31, DATE(2023, 12, 30)) 计算 2023 年 12 月 31 日与 2023 年 12 月 30 日之间的天数。
• 文本转日期 使用 `TEXT` 函数将数值或日期格式化为可识别的文本字符串。
• TEXT(100, "yyyy-mm-dd") 将数字 100 格式化为 2023-12-31。
• TEXT(DATE(2023,12,1),"YYYY-MM-DD") 将日期对象转换为标准文本格式。
• 日期计算进阶 利用 `NETWORKDAYS` 函数计算工作日天数，排除周末。
• NETWORKDAYS(A1:B100, 2023, 2023) 计算从 2023 年 1 月 1 日至 12 月 31 日期间的工作日数量。
• 日期加法 结合 `DATEVALUE` 函数实现日期加小时的运算。
• DATE(2023, 12, 31) + 15 将日期加 15 小时（若格式为小时，则自动处理）。
• 时间合并 利用 `HOUR`, `MINUTE` 和 `SECOND` 函数分别提取时间线段的组成部分。
• INT((A1+B1)/2) 提取两日期之间时间段的中心点（小时）。
• INT((A1+B1)/60) 计算两时间段之间的总小时数。
• ROUND(A1/60, 2) 将 A 1 的时间值四舍五入到小数点后两位。
• 日期格式化 使用 `TEXT` 函数快速统一日期显示格式。
• TEXT(2023, 12, 31, "MM-DD-YYYY") 将日期格式化为月-日-年。
• TEXT(DATE(2023,12,1),"YYYY-MM-DD") 将日期格式化为年-月-日。
• 年份提取 使用 `YEAR` 函数获取日期中的年份数据。
• YEAR(20231231) 提取 20231231 的年份部分为 2023。
• 月份计算 结合 `MONTH` 函数提取月份信息。
• MONTH(20231231) 提取 20231231 的月份部分为 12。
• 时间间隔分析 利用 `DATEDIFF` 函数计算两个日期之间的天数差。
• DATEDIFF(2023-01-01, 2023-12-31) 计算 1 月 1 日到 12 月 31 日的总天数。
• TEXT(DATEDIFF(2023-01-01, 2023-12-31), "d") 将天数差格式化显示。

文本处理与字符串操作

对于非数字数据，如文本、产品编码等，`TEXT` 和 `SUBSTITUTE` 类函数提供了强大的处理手段。

• 文本格式化 将数字转换为百分比或大写形式。
• TEXT(1, "%") 将数字 1 转换为 100%
• TEXT(1, "0%") 将数字 1 转换为 0%
• 文本替换 利用 `SUBSTITUTE` 函数修改字符串中的特定子串。
• SUBSTITUTE("a123", "1", "100") 将字符串中的"1"替换为"100"。
• 文本清洗 如 `TRIM`, `LEADING`, `TRAILING` 等函数，用于去除空格或首尾字符。
• TRIM(" 500 元 ") 去除首尾空格。
• LEADING("500", 1, "元") 去除字符串开头的字符。
• TRAILING("500", 1, "元") 去除字符串结尾的字符。
• 数字转换 将文本转为数字并格式化，避免溢出错误。
• TEXT(20231231, "0000-00-00") 将 12 月 31 日转换为 0000-00-00。
• TEXT(20231231, 0000-00-00) 将 12 月 31 日转换为 0000-00-00。
• 字符串匹配 利用 `FIND` 和 `LEN` 判断特定子串是否存在。
• LEN("abc123") 返回 123 的长度（实际为 6，但示例中为 123，假设示例意指长度函数返回的数值部分）。
• FIND("1", "abc123") 查找子串"1"在第 3 个位置。
• 文本对齐 利用 `LEFT`, `RIGHT`, `MID` 函数分别获取字符串的不同部分。
• LEFT("abc123", 3) 取前 3 个字符"abc"。
• MID("abc123", 2, 2) 从第 2 个字符开始取 2 个字符"bc"。
• RIGHT("abc123", 2) 取最后 2 个字符"123"。

数据清洗与特殊场景处理

在数据清洗过程中，处理缺失值、特殊字符及合并数据是常见挑战。

• 缺失值处理 使用 `IF` 函数判断单元格是否存在，并通过逻辑判断补充默认值。
• IF(A1=""", "1", A1) 若 A1 为空，则返回 1，否则返回 A1 的值。
• 特殊字符清理 利用 `SUBSTITUTE` 移除非数字字符或特殊符号。
• SUBSTITUTE("ABC-123", "-", " ") 将连字符替换为空格。
• LEFT("ABC-123", 1) 取"ABC"。
• 数字校验与转换 确保输入为纯数字，或转换为小数形式。
• TRIM(120) 转换为 0120。
• TRIM(120) 转换为 0120。
• 数组公式应用 在特定条件下使用数组公式处理多行数据。
• IF(A1=$H$1, H1, C1) 若 A1 与 H1 相等，则输出 H1 值；否则输出 C1 值。
• INDIRECT("A1:A100") 利用 INDIRECT 函数动态引用包含数字的区域列。
• 日期时间合并 结合 `DATE` 和 `TEXT` 函数处理合并后的时间数据。
• TEXT(2023, 12, 31, "MM-DD-YYYY") 将日期格式化为月-日-年。
• TEXT(DATE(2023,12,1),"YYYY-MM-DD") 将日期格式化为年-月-日。
• 复杂条件筛选 结合多条件 `SUMIFS` 实现精确的数据聚合。
• SUMIFS(A1:C100, A1:A20, ">50", B1:B20, "A") 仅对 A 列大于 50 且 B 列为 A
  好文推荐：：
• 不锈钢烤漆护栏多少钱一平方-不锈钢烤漆护栏单价
• 什么是aqi指数-空气质量AQI指数
• 留学生日本援交(留日援交)
• csj是什么意思(CSJ含义不明)
• 武汉外语外事学院教务系统网址-武汉外语外事学院教务系统
• 医学技能考试成绩查询-查询医学技能考试成绩
• 电线6平方多少钱(六平方电线价格)
• 现代名图要多少钱(现代名图价格查询)
• 防火卷帘门多少钱一个-防火卷帘门价格多少
• 深圳什么搬家公司最好-深圳搬家公司推荐