夜雨聆风第一部分：基础计算类（10个核心公式）
1. SUM函数 - 基础求和
=SUM(A1:A10)
功能：计算A1到A10这10个单元格的总和
应用场景：计算月度销售额总计、费用合计等
=SUM(A1, B1, C1)
功能：计算A1、B1、C1三个独立单元格的和
注意：用逗号分隔表示不连续的单元格
=SUM(A1:A10, C1:C10)
功能：同时计算两个区域的总和
实用技巧：可以一次性汇总多个部门的数据2. SUMIF函数 - 单条件求和
=SUMIF(B2:B100, "北京", C2:C100)
参数解析：
第1参数 B2:B100 → 条件判断区域（在哪里找条件）
第2参数 "北京" → 要查找的条件值
第3参数 C2:C100 → 实际求和的区域
场景：统计北京地区的总销售额
=SUMIF(费用表!A:A, A2, 费用表!C:C)
跨表求和的写法
功能：在"费用表"的A列中找当前表A2单元格的值，求和对应C列3. SUMIFS函数 - 多条件求和（比SUMIF更强大）
=SUMIFS(D2:D100, A2:A100, "销售部", B2:B100, "北京", C2:C100, ">=5000")
参数结构：先写求和区域，再写条件区域和条件（可以多组）
第1参数 D2:D100 → 要求和的数值区域
第4-5参数 B2:B100, "北京" → 第二组条件：地区为北京
功能：统计销售部在北京地区且金额≥5000的所有订单总额
重要：所有条件必须同时满足才会被求和4. AVERAGE函数 - 平均值计算
=AVERAGE(D2:D50)
功能：计算D2到D50的平均值
注意：自动忽略文本和空单元格
场景：计算学生平均分、员工平均工资
=AVERAGEIF(F:F, ">5000")
功能：计算F列中所有大于5000的数值的平均值
扩展：AVERAGEIFS用于多条件平均5. COUNT家族 - 计数函数
=COUNT(A1:A100)
功能：统计A1:A100范围内数字单元格的数量
注意：文本、逻辑值、错误值、空单元格都不计入
场景：统计有效成绩数量
=COUNTA(A1:A100)
功能：统计A1:A100范围内非空单元格的数量
注意：包含文本、数字、日期等所有非空内容
场景：统计已填写的问卷份数
=COUNTBLANK(A1:A100)
功能：统计A1:A100范围内的空单元格数量
场景：检查表格填写完整度6. COUNTIF函数 - 条件计数
=COUNTIF(G:G, "已完成")
功能：统计G列中值为"已完成"的单元格数量
场景：统计任务完成数量
=COUNTIF(H:H, ">=60")
功能：统计H列中大于等于60的单元格数量
场景：统计及格人数
=COUNTIF(I:I, "*加班*")
功能：统计I列中包含"加班"二字的单元格数量
注意：*是通配符，代表任意字符
场景：统计涉及加班的记录数7. COUNTIFS函数 - 多条件计数
=COUNTIFS(A2:A100, "销售部", B2:B100, "张三", C2:C100, ">=10000")
参数结构：条件区域1, 条件1, 条件区域2, 条件2, ...
功能：统计销售部、张三负责、金额≥10000的订单数量
所有条件必须同时满足才会计数8. MAX/MIN函数 - 极值查找
=MAX(D2:D100)
功能：找出D2:D100中的最大值
场景：找出最高销售额、最高分
=MIN(D2:D100)
功能：找出D2:D100中的最小值
场景：找出最低温度、最低报价
=MAXIFS(数值区域, 条件区域1, 条件1, ...)
Excel 2016及以上版本可用
功能：按条件查找最大值9. ROUND函数家族 - 精确舍入
=ROUND(3.14159, 2)
功能：将3.14159四舍五入到2位小数 → 3.14
参数1：要舍入的数字
参数2：保留的小数位数（正数向右，负数向左）
=ROUNDUP(2.1, 0)
功能：向上舍入 → 3
场景：计算最少包装数量
=ROUNDDOWN(2.9, 0)
功能：向下舍入 → 2
场景：保守估算
=INT(3.9)
功能：取整数部分 → 3
注意：直接去掉小数，不是四舍五入10. SUMPRODUCT - 多数组求和（功能强大）
=SUMPRODUCT((A2:A100="北京")*(B2:B100="销售部")*(C2:C100))
功能：计算北京地区销售部的总销售额
原理：先逻辑判断生成0和1的数组，然后对应相乘求和
=SUMPRODUCT((A2:A100="北京")*(C2:C100>5000), D2:D100)
功能：统计北京地区金额大于5000的订单总额
应用：复杂条件的加权求和第二部分：查找与引用类（8个核心公式）
11. VLOOKUP - 垂直查找（最常用）
=VLOOKUP(F2, A:D, 4, FALSE)
参数详解：
第1参数 F2 → 要查找的值（查找谁）
第2参数 A:D → 查找范围（在哪里找）
重要：查找值必须在范围的第一列！
第3参数 4 → 返回第几列的数据（从查找范围的第一列开始数）
第4参数 FALSE → 精确匹配（TRUE为模糊匹配）
实际案例：根据员工ID查找姓名
=VLOOKUP(G2, 员工表!A:B, 2, FALSE)
功能：在"员工表"的A列查找G2的值，找到后返回同一行B列的值
⚠️ 常见错误：
1. 查找区域没锁定 → 公式下拉会变化
正确：$A$2:$D$100
2. 返回列数算错 → 数一下，不要目测12. HLOOKUP - 水平查找
=HLOOKUP("二月", A1:D3, 3, FALSE)
功能：在第一行查找"二月"，找到后返回下方第3行的值
场景：横向表格的数据查找13. INDEX + MATCH组合 - VLOOKUP的替代方案
=INDEX(C:C, MATCH(F2, A:A, 0))
分解理解：
MATCH(F2, A:A, 0) → 在A列查找F2的位置（行号）
INDEX(C:C, 上面得到的行号) → 返回C列对应行的值
优势：比VLOOKUP灵活，查找值不必在第一列14. XLOOKUP - 新一代查找函数（Excel 365专属）
=XLOOKUP(F2, A:A, C:C, "未找到", 0, 1)
参数详解：
第1参数 F2 → 要查找的值
第2参数 A:A → 查找范围
第3参数 C:C → 返回范围（可以和查找范围不同列）
第4参数 "未找到" → 找不到时的返回值
第5参数 0 → 精确匹配
第6参数 1 → 从第一条开始搜索
优势：功能强大，使用简单，支持反向查找15. LOOKUP - 模糊查找
=LOOKUP(F2, A2:A100, B2:B100)
功能：在A列查找F2，返回对应B列的值
注意：要求查找区域必须升序排列
场景：根据分数区间确定等级16. INDIRECT - 间接引用
=INDIRECT("Sheet2!A" & B1)
功能：B1单元格的值是10，则公式等价于Sheet2!A10
场景：动态引用不同工作表的数据
=SUM(INDIRECT("C" & ROW() & ":C" & ROW()+10))
功能：动态计算当前行向下10行的总和17. OFFSET - 偏移引用
=OFFSET(A1, 3, 2)
功能：以A1为起点，向下3行，向右2列 → 返回C4单元格的值
参数：起点, 行偏移, 列偏移, [高度], [宽度]
=SUM(OFFSET(A1, 0, 0, 5, 3))
功能：计算A1开始的5行3列区域的总和18. CHOOSE - 选择引用
=CHOOSE(MATCH(D2, {"A","B","C"}, 0), "优秀", "良好", "及格")
功能：根据D2的值返回对应的等级
场景：简单的编码转换第三部分：文本处理类（8个核心公式）
19. LEFT/RIGHT/MID - 文本截取
=LEFT(A1, 3)
功能：从A1文本的左边开始取3个字符
场景：提取员工编号的前缀（如"BJ001"取"BJ"）
=RIGHT(A1, 4)
功能：从A1文本的右边开始取4个字符
场景：提取手机号后4位
=MID(A1, 4, 2)
功能：从A1文本的第4个字符开始，取2个字符
场景：提取身份证中的出生月份
示例：MID("510102199001011234", 11, 2) → "01"（1月出生）20. LEN - 文本长度
=LEN(A1)
功能：计算A1单元格文本的字符数
注意：一个汉字、字母、数字都算1个字符
场景：验证身份证号码长度是否正确（18位）21. FIND/SEARCH - 查找字符位置
=FIND("@", A1)
功能：查找"@"在A1文本中的位置（从左边数第几个字符）
区分大小写：FIND区分大小写，SEARCH不区分
=SEARCH("张", A1)
功能：查找"张"在A1中的位置
场景：提取邮箱的用户名部分 =LEFT(A1, FIND("@", A1)-1)22. SUBSTITUTE - 替换文本
=SUBSTITUTE(A1, "旧文本", "新文本", [替换第几个])
功能：将A1中的"旧文本"替换为"新文本"
=SUBSTITUTE(A1, " ", "")
功能：删除A1中的所有空格
场景：清理数据中的多余空格
=SUBSTITUTE(A1, CHAR(10), ", ")
功能：将换行符替换为逗号和空格
CHAR(10)是换行符的代码23. REPLACE - 替换指定位置文本
=REPLACE(A1, 4, 2, "**")
功能：从A1的第4个字符开始，替换2个字符为"**"
场景：隐藏手机号中间部分 =REPLACE(A1, 4, 4, "****")24. TEXT - 格式化文本
=TEXT(A1, "0.00%")
功能：将A1的数字转换为百分比格式，保留2位小数
=TEXT(B1, "yyyy年mm月dd日")
功能：将B1的日期转换为中文格式
场景：生成规范的日期文本
=TEXT(C1, "￥#,##0.00")
功能：格式化为货币格式，千位分隔25. CONCATENATE / CONCAT / TEXTJOIN - 文本合并
=CONCATENATE(A1, " ", B1)
功能：合并A1和B1，中间加空格
新版本建议用CONCAT或TEXTJOIN
=TEXTJOIN(", ", TRUE, A1:A10)
功能：用逗号和空格连接A1:A10，忽略空单元格
参数：分隔符, 是否忽略空值, 要合并的区域
场景：将多个姓名合并为一个字符串26. TRIM - 清除空格
=TRIM(A1)
功能：清除A1文本中首尾的空格，中间多个空格保留一个
场景：清理从系统导出的不规范数据第四部分：日期与时间类（7个核心公式）
27. TODAY/NOW - 当前日期时间
=TODAY()
功能：返回当前日期（自动更新）
场景：制作每日报表的日期标记
=NOW()
功能：返回当前日期和时间
注意：每次打开文件或重新计算时更新28. DATE - 构建日期
=DATE(2024, 3, 15)
功能：构建2024年3月15日的日期值
参数：年, 月, 日
场景：将分开的年月日组合成标准日期29. DATEDIF - 日期差值（隐藏函数但很强大）
=DATEDIF(A1, TODAY(), "Y")
功能：计算A1日期到今天的整年数
参数：开始日期, 结束日期, 单位代码
单位代码：
"Y" → 整年数
"M" → 整月数
"D" → 天数
"YM" → 忽略年的月数差
"YD" → 忽略年的天数差
"MD" → 忽略年月的天数差
实际案例：计算员工工龄
=DATEDIF(入职日期, TODAY(), "Y") & "年" & DATEDIF(入职日期, TODAY(), "YM") & "个月"30. YEAR/MONTH/DAY - 提取日期成分
=YEAR(A1) 提取年份 → 2024
=MONTH(A1) 提取月份 → 3
=DAY(A1) 提取日 → 15
=WEEKDAY(A1, 2) 提取星期几（2表示周一=1,周日=7）31. EDATE - 月份加减
=EDATE(A1, 3)
功能：A1日期加3个月
=EDATE(A1, -2)
功能：A1日期减2个月
场景：计算合同到期日、保修期截止日32. WORKDAY - 工作日计算
=WORKDAY(A1, 10, 节假日表!$A$1:$A$20)
功能：A1日期后第10个工作日（排除周末和指定节假日）
场景：计算项目交付日期33. NETWORKDAYS - 工作日天数
=NETWORKDAYS(开始日期, 结束日期, 节假日表!$A$1:$A$20)
功能：计算两个日期之间的工作日天数
场景：计算项目实际工作天数第五部分：逻辑判断类（5个核心公式）
34. IF函数 - 条件判断
=IF(A1>=60, "及格", "不及格")
基本结构：IF(条件, 条件成立时返回值, 条件不成立时返回值)
嵌套IF示例：成绩评级
=IF(A1>=90, "优秀", IF(A1>=80, "良好", IF(A1>=60, "及格", "不及格")))
注意：Excel 2016+建议用IFS函数代替多层嵌套35. IFS函数 - 多条件判断（Excel 2016+）
=IFS(A1>=90, "优秀", A1>=80, "良好", A1>=60, "及格", TRUE, "不及格")
结构：IFS(条件1, 结果1, 条件2, 结果2, ..., 默认结果)
比嵌套IF更清晰易读36. AND/OR/NOT - 逻辑组合
=IF(AND(A1>=60, B1>=60), "双科及格", "有不及格科目")
AND：所有条件都成立才返回TRUE
=IF(OR(A1>=90, B1>=90), "有单科优秀", "无单科优秀")
OR：任意条件成立就返回TRUE
=IF(NOT(A1=""), "已填写", "未填写")
NOT：逻辑取反37. IFERROR - 错误处理
=IFERROR(VLOOKUP(A1, 数据表!A:B, 2, FALSE), "未找到")
功能：如果VLOOKUP出错（如找不到），返回"未找到"而不是错误值
场景：让表格更美观，避免#N/A等错误显示38. IFNA - 专门处理#N/A错误
=IFNA(VLOOKUP(A1, 数据表!A:B, 2, FALSE), 0)
功能：只处理#N/A错误，其他错误仍会显示
比IFERROR更精确第六部分：统计与分析类（7个核心公式）
39. RANK - 排名
=RANK(A2, $A$2:$A$100, 0)
功能：计算A2在A2:A100中的排名
参数：要排名的值, 排名范围, 排序方式(0降序/1升序)
注意：相同数值获得相同排名40. LARGE/SMALL - 第N大/小值
=LARGE(A:A, 3)
功能：找出A列第3大的值
=SMALL(B:B, 5)
功能：找出B列第5小的值
应用：计算前3名平均分
=AVERAGE(LARGE(C:C, {1,2,3}))41. QUARTILE - 四分位数
=QUARTILE(A:A, 1)
功能：计算A列的第1四分位数（25%位置的值）
参数：数据区域, 四分位类型(0最小值,1Q1,2中位数,3Q3,4最大值)
场景：薪酬分析、成绩分布分析42. FREQUENCY - 频率分布
=FREQUENCY(数据区域, 分段点区域)
注意：这是数组公式，需按Ctrl+Shift+Enter
场景：统计各分数段人数43. CORREL - 相关系数
=CORREL(A2:A100, B2:B100)
功能：计算A列和B列的相关系数（-1到1之间）
场景：分析两个变量的相关关系44. FORECAST - 预测
=FORECAST(目标X值, 已知Y值区域, 已知X值区域)
功能：根据线性回归预测未来值
场景：销售额预测、趋势分析45. GROWTH - 指数增长预测
=GROWTH(已知Y值区域, 已知X值区域, 新X值区域)
功能：指数增长趋势预测
场景：符合指数增长的业务预测第七部分：高级与专业类（5个核心公式）
46. SUBTOTAL - 分类汇总
=SUBTOTAL(9, A2:A100)
功能：对A2:A100求和（9代表SUM）
特点：忽略被筛选隐藏的行
=SUBTOTAL(1, A:A) 平均值（AVERAGE）
=SUBTOTAL(2, A:A) 计数（COUNT）
=SUBTOTAL(3, A:A) 计数（COUNTA）
场景：制作可筛选的汇总表47. AGGREGATE - 更强大的汇总
=AGGREGATE(9, 6, A:A)
功能：对A列求和（9代表SUM），忽略错误值和隐藏行
参数：函数编号, 忽略选项, 数据区域
比SUBTOTAL更强大48. ARRAY FORMULAS - 数组公式
{=MAX(IF(A2:A100="北京", B2:B100))}
注意：输入后按Ctrl+Shift+Enter，会显示大括号{}
功能：找出北京地区的最高销售额
场景：复杂条件计算49. GETPIVOTDATA - 透视表数据提取
=GETPIVOTDATA("销售额", $A$3, "地区", "北京", "月份", "1月")
功能：从数据透视表提取特定数据
场景：基于透视表制作动态报表50. PMT - 贷款计算
=PMT(年利率/12, 期数, 贷款总额)
功能：计算每期还款额
场景：房贷、车贷计算快来评论区留言告诉我：
你们当下最迫切想要学会的是哪个公式？在工作里，大家使用 Excel 时具体遭遇了哪些难题？
我会从众多留言中精心挑选出 10 个最具代表性的问题，专门为大家制作详细的解决方案视频哦！
要是这篇干货对你们有实实在在的帮助，那就请大家：
👍点赞：让更多像你们一样有需要的小伙伴能够看到它；
🔔关注：这样就能获取我持续更新的超实用 Excel 技巧啦；
🔄分享：动动手指分享给同事和朋友，咱们一起在职场上乘风破浪，共同提升！
#Excel #办公效率 #职场技能 #数据分析 #教程分享
