EXCEL常用函数公式大全及举例

1 EXCEL常用函数公式大全及举例 一 相关概念 一 函数语法 由函数名 括号 参数组成 例 求和函数 SUM A1 B2 参数与参数之 间用逗号 隔开 二 运算符 1 公式运算符 加 减 乘 除 百分号 乘幂 2 比较运算符 大与 小于 等于 小于等于 不等于 3 引用运算符 区域运算符 联合运算符 三 单元格的相对引用与绝对引用 例 A1 A1 锁定第 A 列 A 1 锁定第 1 行 A 1 锁定第 A 列与第 1 行 二 常用函数 一 数学函数 1 求和 SUM 数值 1 数值 2 2 条件求和 SUMIF 查找的范围 条件 即对象 要求和的范围 例 1 SUMIF A1 A4 200 B1 B4 函数意思 对第 A1 栏至 A4 栏中 大于等于 200 的数值对应的第 B1 列至 B4 列中数值求和 2 2 SUMIF A1 A4 300 C1 C4 函数意思 对第 A1 栏至 A4 栏中 小于 300 的数值对应的第 C1 栏至 C4 栏中数值求和 3 求个数 COUNT 数值 1 数值 2 例 1 COUNT A1 A4 函数意思 第 A1 栏至 A4 栏求个数 2 COUNT A1 C4 函数意思 第 A1 栏至 C4 栏求个数 4 条件求个数 COUNTIF 范围 条件 例 1 COUNTIF A1 A4 200 函数意思 第 A1 栏至 A4 栏中不等于 200 的栏求个数 2 COUNTIF A1 C4 1000 函数意思 第 A1 栏至 C4 栏中大于等 1000 的栏求个数 5 求算术平均数 AVERAGE 数值 1 数值 2 例 1 AVERAGE A1 B2 2 AVERAGE A1 A4 6 四舍五入函数 ROUND 数值 保留的小数位数 7 排位函数 RANK 数值 范围 序别 1 升序 0 降序 例 1 RANK A1 A1 A4 1 函数意思 第 A1 栏在 A1 栏至 A4 栏中按升序排序 返回排名值 2 RANK A1 A1 A4 0 函数意思 第 A1 栏在 A1 栏至 A4 栏中按降序排序 返回排名值 8 乘积函数 PRODUCT 数值 1 数值 2 9 取绝对值 ABS 数字 10 取整 INT 数字 3 二 逻辑函数 条件函数 IF 条件 执行条件 执行条件假 可执行七层嵌套 例 A1 5 A2 3 A3 6 A4 7 IF A1 A2 1 0 1 真 IF A1A2 IF A3 A4 8 9 1 9 三 文本函数 1 截取函数 LEFT 文本 截取长度 从左边截取 RIGHT 文本 截取长度 从右边截取 MID 文本 开始位 截取长度 从中间截取 2 计算字符长度 LEN 文本 文本的长度 3 合并字符函数 CONCATENATE 文本 1 文本 2 4 在字符串中查找特定字符 FIND 文本 范围 数值 数值表示查找第几个 FIND a abcadeafga 2 4 FIND a abcadeafga 3 7 FIND a abcadeafga 4 10 5 比较两个字符是否完全相符 EXACT 文本 1 文本 2 EXACT a a TRUE EXACT a ab FALSE EXACT a A FALSE 6 将数值转化为文本 TEXT 数值 参数 参数一般为 0 7 将数值型字符转换为数值 VALUE 数值文本 四 判断公式 4 1 把公式产生的错误显示为空 公式 C2 IFERROR A2 B2 说明 如果是错误值则显示为空 否则正常显示 2 IF 多条件判断返回值 公式 C2 IF AND A2 500 B2 未到期 补款 说明 两个条件同时成立用 AND 任一个成立用 OR 函数 五 统计公式 1 统计两个表格重复的内容 公式 B2 COUNTIF Sheet15 A A A2 说明 如果返回值大于 0 说明在另一个表中存在 0 则不存在 5 2 统计不重复的总人数 公式 C2 SUMPRODUCT 1 COUNTIF A2 A8 A2 A8 说明 用 COUNTIF 统计出每人的出现次数 用 1 除的方式把出现次数变成分母 然后相加 六 求和公式 1 隔列求和 公式 H3 SUMIF A 2 G 2 H 2 A3 G3 或者 SUMPRODUCT MOD COLUMN B3 G3 2 0 B3 G3 说明 如果标题行没有规则用第 2 个公式 6 2 单条件求和 3 单条件模糊求和 4 多条件模糊求和 5 多表相同位置求和 公式 B2 SUM Sheet1 Sheet19 B2 说明 在表中间删除或添加表后 公式结果会自动更新 6 按日期和产品求和 七 查找与引用公式 1 单条件查找公式 公式 1 C11 VLOOKUP B11 B3 F7 4 FALSE 说明 查找是 VLOOKUP 最擅长的 基本用法 7 2 双向查找公式 公式 INDEX C3 H7 MATCH B10 B3 B7 0 MATCH C10 C2 H2 0 说明 利用 MATCH 函数查找位置 用 INDEX 函数取值 3 查找最后一条符合条件的记录 公式 详见下图 说明 0 条件 可以把不符合条件的变成错误值 而 lookup 可以忽略错误 值 8 4 多条件查找 公式 详见下图 说明 公式原理同上一个公式 5 指定区域最后一个非空值查找 6 按数字区域间取对应的值