excel 表格常用公式大全,是把日常办公高频函数按照统计 判断 查找 日期 文本五大类汇总,覆盖求和 计数 排名 匹配 日期提取 文本处理这些场景,掌握这套公式,绝大多数表格计算都可以直接套用,很多新手只记住零散函数,不清楚分类和适用场景,也容易混淆参数,下面分类整理公式语法 示例和用途,方便日常查阅。
一 统计求和类
1 SUM 求和
语法 SUM (数据区域)
示例 =SUM (A2:A20)
用途 对指定区域所有数值相加
2 SUMIF 单条件求和
语法 SUMIF (条件区域,条件,[求和区域])
示例 =SUMIF (B2:B20,"销售一部",C2:C20)
用途 满足单个条件之后再求和
3 SUMIFS 多条件求和
语法 SUMIFS (求和区域,条件区域 1, 条件 1, 条件区域 2, 条件 2)
示例 =SUMIFS (C2:C20,B2:B20,"销售一部",D2:D20,">10000")
用途 同时满足多个条件求和
4 AVERAGE 平均值
语法 AVERAGE (数据区域)
示例 =AVERAGE (A2:A20)
用途 计算一组数值算术平均数
5 COUNT 计数
语法 COUNT (数据区域)
示例 =COUNT (A2:A20)
用途 只统计区域里面数字单元格数量
6 COUNTA 非空计数
语法 COUNTA (数据区域)
示例 =COUNTA (A2:A20)
用途 统计所有非空单元格,包含文本数字
7 COUNTIF 单条件计数
语法 COUNTIF (条件区域,条件)
示例 =COUNTIF (B2:B20,"合格")
用途 按单个条件统计个数
8 COUNTIFS 多条件计数
语法 COUNTIFS (条件区域 1, 条件 1, 条件区域 2, 条件 2)
示例 =COUNTIFS (B2:B20,"合格",C2:C20,">80")
用途 多条件同时满足统计数量
9 RANK 排名
语法 RANK (数值,全部数据区域,排序方式)
示例 =RANK (A2,$A$2:$A$20,0)
用途 计算数值在整组数据里面名次,0 代表降序
二 逻辑判断类
1 IF 条件判断
语法 IF (判断条件,成立返回值,不成立返回值)
示例 =IF (A2>=60,"及格","不及格")
用途 单条件判断输出两种结果,支持多层嵌套做多分级
2 AND 并且
语法 AND (条件 1, 条件 2, 条件 3)
用途 全部条件同时成立返回 TRUE,一般搭配 IF 使用
3 OR 或者
语法 OR (条件 1, 条件 2, 条件 3)
用途 任意一个条件成立返回 TRUE,一般搭配 IF 使用
4 IFERROR 错误容错
语法 IFERROR (原公式,出错显示内容)
示例 =IFERROR (VLOOKUP (A2,B:C,2,0),"无匹配")
用途 公式报错的时候自定义显示内容,隐藏 N/A DIV/0! 错误
三 查找引用类
1 VLOOKUP 纵向查找
语法 VLOOKUP (查找值,数据表区域,返回列序号,匹配类型)
示例 =VLOOKUP (A2,$B$2:$D$20,3,0)
用途 按查找值在区域第一列搜索,返回该行指定列内容,0 为精确匹配
2 XLOOKUP 新版查找
语法 XLOOKUP (查找值,查找区域,返回区域,[找不到的值])
示例 =XLOOKUP (A2,B2:B20,C2:C20,"无数据")
用途 替代 vlookup,支持反向查找,不受查找列必须在第一列限制
3 INDEX 索引取值
语法 INDEX (数据区域,行序号,[列序号])
用途 根据行列编号提取单元格内容,常搭配 MATCH 使用
4 MATCH 匹配位置
语法 MATCH (查找值,查找区域,匹配类型)
用途 返回查找值在区域里面的位置序号
四 日期时间类
1 YEAR 提取年份
语法 YEAR (日期单元格)
示例 =YEAR (A2)
用途 从日期里面提取四位年份
2 MONTH 提取月份
语法 MONTH (日期单元格)
示例 =MONTH (A2)
用途 提取月份数字
3 DAY 提取日
语法 DAY (日期单元格)
示例 =DAY (A2)
用途 提取日期当中几号
4 TODAY 当前日期
语法 TODAY ()
用途 自动获取系统当天日期,打开表格自动更新
5 DATEDIF 间隔计算
语法 DATEDIF (起始日期,结束日期,单位)
示例 =DATEDIF (A2,TODAY (),"Y")
用途 计算两个日期之间年 月 日差值,常用来计算年龄
五 文本处理类
1 LEFT 左侧提取
语法 LEFT (文本单元格,提取字符个数)
示例 =LEFT (A2,4)
用途 从文字最左边截取指定长度字符
2 RIGHT 右侧提取
语法 RIGHT (文本单元格,提取字符个数)
示例 =RIGHT (A2,3)
用途 从文字最右边截取指定长度字符
3 MID 中间截取
语法 MID (文本单元格,起始位置,字符个数)
示例 =MID (A2,3,4)
用途 从指定位置开始截取文字
4 LEN 字符长度
语法 LEN (文本单元格)
示例 =LEN (A2)
用途 计算单元格文字总字符数量
5 TRIM 清除多余空格
语法 TRIM (文本单元格)
示例 =TRIM (A2)
用途 删除文字前后和中间多余空格,解决 vlookup 匹配失败问题
6 TEXT 格式转换
语法 TEXT (数值,格式代码)
示例 =TEXT (A2,"yyyy-mm-dd")
用途 把数值日期转换成指定格式文本
常用公式分类速查表
| 类别 | 函数 | 核心功能 |
|---|---|---|
| 统计求和 | SUM SUMIF SUMIFS | 单区域求和 单条件求和 多条件求和 |
| 计数排名 | COUNT COUNTIF RANK | 数字计数 条件计数 数值排名 |
| 逻辑判断 | IF AND OR IFERROR | 条件判断 多条件并且或者 错误隐藏 |
| 查找引用 | VLOOKUP XLOOKUP | 纵向匹配 新版双向查找 |
| 日期函数 | YEAR MONTH DATEDIF | 提取年月 计算日期间隔 |
| 文本函数 | LEFT MID TRIM | 截取文字 清理空格 |
深度补充要点
1 所有公式必须以等号开头,参数之间使用英文逗号分隔,不能使用中文标点
2 下拉批量计算的时候,固定区域需要加美元符号绝对引用
3 新版 excel 部分旧函数会标记兼容函数,推荐使用新版替代版本
4 WPS 表格这套常用公式语法和微软 excel 基本通用
【常见问题】
问题 1 excel 表格常用公式大全里面 vlookup 和 xlookup 优先选哪一个
回答 1 excel 表格常用公式大全里面,老版本 excel 没有 xlookup,优先使用 vlookup,新版 excel 推荐 xlookup,excel 表格常用公式大全里面 xlookup 支持反向查找,比 vlookup 限制更少
问题 2 excel 表格常用公式大全里面多条件求和用哪个函数
回答 1 excel 表格常用公式大全里面多条件求和使用 SUMIFS,excel 表格常用公式大全里面 SUMIF 只能单条件求和,多条件场景不要混用
问题 3 excel 表格常用公式大全公式报错怎么处理
回答 1 excel 表格常用公式大全里面公式报错,可以优先使用 IFERROR 容错函数包裹原公式,excel 表格常用公式大全里面 IFERROR 专门用来隐藏错误值,方便表格整洁
免责声明:本文 excel 表格常用公式大全讲解基于微软 excel 通用版本,WPS 表格函数逻辑基本一致,不同版本函数支持存在细微差异,内容仅作为办公学习参考,不代表官方函数手册。
本文来自作者[俗世烟火拾知]投稿,不代表万里行信息网立场,如若转载,请注明出处:https://www.wlx315.cn/kpjh/202611/7685.html
评论列表(4条)
我是万里行信息网的签约作者“俗世烟火拾知”!
希望本篇文章《excel 表格常用公式大全 办公速查》能对你有所帮助!
本站[万里行信息网]内容主要涵盖:生活百科,小常识,生活小窍门,知识分享
本文概览:excel 表格常用公式大全,是把日常办公高频函数按照统计 判断 查找 日期 文本五大类汇总,覆盖求和 计数 排名 匹配 日期提取 文本处理这些场景,掌握这套公式,绝大多数表格计算都可以直接套用,很多新手只记住零散函数,不清楚分类和适用场景,也容易混淆参数,下面分类整理公式语法 示例和用途,方便日常查阅。