excel公式总结（一）

包含了：运算符（文本、算数、比较、单元格等）、数组公式、常用函数一包含（IF函数、vlookup函数、筛选求和函数=subtotal、条件函数、舍入运算函数=round函数、随机选取函数=rand、数学函数、逻辑判断函数、排错函数、转换函数、截取函数）注意事项类等。方便大家快速找寻函数，帮助大家进行函数入门。

excel公式介绍（一）

运算符

算术运算符

+ 加 - 减 * 乘 / 除 % 百分比 ^ 乘幂

文本连字符

& 连接

比较运算符

= 等于 > 大于 < 小于 >= 大于或等于 <= 小于或等于 <> 不等于

单元格引用

, 联合运算符 多个引用合并成一个引用 : 区域运算符 引用两个单元格之间全部区域

运算优先级

最高优先级 引用运算符里面的冒号（：）以及逗号（，）和空格运算符。 第二优先级 负号（-） 第三优先级 百分号（%） 第四优先级 求幂（^） 第五优先级 乘法（*）和除法（/） 第六优先级 加法（+）和减法（-） 第七优先级 连接运算符（&） 第八优先级 等于（=），大于（>），小于（<），大于等于（>=），小于等于（<=），不等于（<>）

ps：表格的下边缘 为 1048576 因为2的20次方是1048576

数组公式

定义：原来是一个一个运算、现在是一组一组运算，就叫做数组。

显示：按住ctrl+shift 再点击回车，出现中括号，表示确定为数组， 就会呈现数组的结果

记忆：三键结尾

按住ctrl+shift 再点击回车，出现中括号，表示确定为数组， 就会呈现数组的结果

IF函数

IF函数

IF函数+NOW函数

场景

用NOW函数和IF函数和循环引用进行实时记录数据值和数据产生时间

即：录入数据并在其后一列记录数据记录时间

原理

NOW函数可以获取时间

IF函数可以进行判断条件

示例

如果A2不是空 如果（B2）本来就有时间，那我就等于我自己本身 如果（B2）自己没有时间，就等于当前时间

vlookup函数

vlookup函数

vlookup函数+混合引用

可以引入column函数来进行自动变化，我要索引的列

举例

公式为：

子主题

条件函数=sum/countif

条件求和

sumif函数（单条件求和）

sumif函数（区域，条件，【求和区域】）

解释

区域是指：正在该区域内进行搜索匹配字段

条件是指：你所要求得目标字段那个单元格

求和区域：就是你要算那个数字那一列

最后的结果输出的是符合该条件下的总和

sumifs函数（多条件求和）

sumifs函数（求和区域，区域1，条件1，【区域2，条件2】.....）

ps：sumifs函数（多条件求和，逻辑上是既....又....的值）

解释

求和区域：就是你要算那个数字那一列

区域1，在区域1中找条件1单元格内容

区域2，在区域1中找条件2单元格内容

条件和区域总是成对出现的

条件计数

countif 函数

countif 函数用来统计该区域内，统计该数值出现了多少次

场景

利用公式制定输入规则/数据有效性

小于一定数值时，才可输入。（小于等于才可输入）

只能防止手动填写的，不能防止复制粘贴的

数据有效性只会对空白单元输入时候有效，其他已输入单元格不会更改或者仅提示

countifs函数

countifs函数则是表示多个区域和条件计数（2个以上的区域计数的和）

其他条件统计函数

最大值条件函数

maxifs

最小值条件函数

minfs

平均数条件函数

averageifs

筛选求和函数=subtotal

subtotal函数

异同

sum无论你是否使用筛选都对筛选无反应，总是求的是总集

subtotal中的sum函数会随着你筛选的数值条件不同而使求和的结果发生改变

筛选求和

subtotal中的sum，即=subtotal（9，某列）1-11的函数：只会求去筛选之后的值

筛选+隐藏求和

subtotal中的sum，即=subtotal（109，某列）101-111的函数：会求去筛选之后的值以及隐藏之后的值

函数适用范围：sum>subtotal9>subtotal109

舍入运算函数=round函数

四舍五入

round函数

写法：=round（单元格，2/3/4）后面的数字代表保留几位小数

直接进位

对小数部分直接进位，向前补一位

roundup函数

写法：=roundup（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数

直接舍去

对小数部分直接进行舍去，不向前进行补位

写法：=rounddown（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数

round函数中，数字也可以用负数来表示，比如12345，=round（单元格，-2），就会变为12300，-2就可以表示为可以按整百取整

随机选取函数=rand

rand函数

写法：=rand（），默认括号内不写数字，会在0-1之间随机产生数字，小数点后十五位的数字

randbetween函数

写法：=randbetween（1-5）

随机好随，但是难点在于防止重复

randbetween可以用于取某一范围的整数

数组公式

数组：一组乘一组，一排乘以一排

数组公式三键结尾

sumproduct函数

sumproduct就是数组版的sum

举例

先乘后加数据等

多条件统计实例

第五列判断海鲜粉=海鲜粉

第六列判断销售一部=销售一部

第七列表示为且的关系（求交集）、两个true相乘为1，两个/一true一flase相乘为0。

因此第八列用第七列乘单价

第九列用sum处理即可解决

sumifs函数其实更快，要比sumproduct函数快17倍

数学函数

ABS绝对值

绝对值（正负都为正值），处理的是数字

写法：=ABS（单元格），然后下拉

MEDIAN中位值

求某一区域的中位值

若为偶数列：最中间那两个的平均值 ， 奇数列：最中间那个

写法：=MEDIAN（那一列）

MODE函数求众数

出现次数最多的，如果出现次数一样，就取第一个次数出现最多的

写法：=MODE（那一列）

MOD函数求余数

写法：=MOD（分母，分子）

除以2还余1就叫做奇数，能除赶紧就叫做偶数

ROW函数（行号）/column函数（列号）

写法：=ROW（单元格）就可以知道行数

写法：=column（单元格）就可以知道列数

逻辑判断函数

IF函数（条件函数）

如果，是...怎么样....，否则不是....怎么样.....

IF嵌套（嵌套条件函数）

N情况，就用N-1个IF

举例：如果是省就显示XX省，市就显示XX市。可以用if多层嵌套

IF函数区间用法

错误写法

在表格中位于某个区间，永远不能用：20>A2>1这样的写法来表示某个单元格位于某个区间，表示大于某值，小于某值。

原因

A2会和20做一个判断，得到ture和false在和1进行判断，结果无意义

正确写法

像剥洋葱一样，一层一层往下去拨。写的时候要理清楚逻辑结构

IF函数与逻辑运算

AND函数

AND函数表示的且的关系，ABC...D等条件都满足

举例：and+if函数

举例：AND和OR函数

=if（or（and（B2=“男”，C2>=22），and（B2=“女”，A2>=20）），“是”，“否”）

由内往外，由小往大

排错函数

IFERROR查错误函数

出错函数范围太宽，有可能是函数写错了，也有可能是公式确实找不到信息。但都会改为你想要改的值。

写法：IFERROR（错误列，错误之后显示的）

=IFERROR（A2，0）如果A2错误就显示为0

IFNA函数查错误函数

IFNA函数用于查询公式确实找不到信息情况，如果公式错误依旧会显示公式出错误。

写法同IFERROR函数写法

IFNA函数和IFERROR区别

范围不同，所有的错误都算ERROR，所以IFERROR会屏蔽所有错误，而IFNA函数专门用来屏蔽VLOOKUP找不到的错误

转换函数

IFS函数

IF函数的升级版，加S表示多情况下。

在此函数中每一种都需要写清楚情况

=IFS（E6>=2000000，“A级”，E6>=1200000，“B级”，E6>=600000，“C级”，E6<600000，“D级”）

解释为：

若E6大于2百万，为A级别，

否则，大于120万，为B级

否则，大于60万，为C级

否则，小于60万，为D级

SWITCH函数

一 一对应关系

截取函数

字节函数（文本函数带B）

理解字节：

一个中文字是两个字节，字母和数字是单字节

如果是字节是文本，取不到时候就会留取空白，比如：空白的，取五个字节，那么输出结果为：空白 ，后面有“的”一半，就会用空格来表示

左截取

从左截取：=left（，）

从左截取：=leftb（，）

右截取

从右截取：=right（，）

从右截取：=rightb（，）

中间截取

从中间截取：=mid（A2，2，3）

从中间截取：=midb（A2，2，3）

字符、字节数量

计算字符数量：=len（）

计算字节数量：=lenb（）

可以利用字节、字符数来进行分列文本和数字情况（等同于智能分列）

场景

分列汉字

分列数字：先分列汉字，在倒退数字

注意事项类

绝对引用和相对引用的区别

绝对引用，即便选中整个区域，公式也无法进行复用 在使用相对引用时，公式或函数中的行或列会随着行号和列标的变化而自动变化。 绝对引用的特点就是在使用绝对引用时，公式或函数中的行或列是绝对不会发生变化的。

混合引用

A1 相对引用，横向纵向都可以拉，拉取会变 $A$1 绝对引用，横向纵向都不可以拉，拉取不会变 $A1 混合引用，纵向可以拉，拉取会变，横向不可拉。 A$1 混合引用，横向可以拉，拉取会变，纵向不可拉。

用引用公式来做9*9乘法表格