函数公式极速入门

Excel中，公式就是能够自动计算结果的算式，等于号【=】就是它的标记，Excel公式最主要的运算方式有3种：算术运算、比较运算和文本运算。

算术运算的结果就是数值，比较结果的运算是False和True，文本运算则是用【&】符号拼接文本，当被连接的是文本、符号和日期时，都需要加上英文的双引号，比如【=B1&”001″】，如果日期没有加上英文双引号，也能被四算但其中的斜杠会被当成除号处理。。

Excel公式中的运算也有优先级，比如=6-3>3会返回False因为会先计算比较运算符前面的 部分再比较是否大于3，Excel中的运算方式优先次序如下：

百分比>幂>乘/除>加/减>文本连接符&>比较运算符（大于小于等于不等于<>），如果要改变运算顺序，需要使用小括号()包住优先计算的部分。

相对引用和绝对引用

Excel中，一个单元格可以使用另一个单元格的值作为计算参数，这个操作可以被称为“引用”，引用的好处是，被引用的单元格的值发生变更，引用它的另一个单元格也会自动更新计算结果。

绝对引用是指在公式中使用的单元格地址（如 $A$1），相对引用是指在公式中使用的单元格相对地址（如 A1）。

在绝对引用中，行号和列号都是固定的，不随着公式的移动而改变。相对引用中，行号和列号是相对于公式所在的单元格而变化的，随着公式的移动而改变。

可以使用美元符号（英文输入法shift+4）【$】表示绝对引用，在单元格标识中加上2个$，比如【$B$1】但最方便的方法是在编辑栏中选中单元格标识然后按下【F4】键。

除了绝对引用和相对引用，还有混合引用：

比如【$B1】表示列标不变但是行号会变，而【B$1】则表示列标签会改变但行号不变。

什么是函数

函数是 Excel 中预先定义好的计算功能，用于解决一些常见的计算问题。例如，SUM 函数可以用来计算某个区域的和，AVERAGE 函数可以用来计算某个区域的平均数。

公式则是用户自己定义的计算公式，可以根据需要实现复杂的计算。例如，你可以使用公式来计算一个单元格的值与另一个单元格的值的差。

总的来说，函数是 Excel 给你的一些计算工具，而公式是你自己定义的计算公式，可以实现更加复杂的计算。

函数的正确输入方法

输入等于号后，继续输入函数名称开头的字幕，Excel就会自动匹配函数列表，输入越多字幕匹配结果越精确，如果不急的写法可以直接从列表中选择。

函数参数中，所有的运算符号都必须是英文半角符号，否则会出现计算错误。

【,】英文逗号用于隔开多个参数，表示“与”

【:】英文冒号表示引用区域，表示“到”，比如SUM(A1:A10)表示计算a1到a10单元格之间的 数值的总和

【!】用于引用其他工作表中的区域，表示“的”，比如SUM(Sheet2!A1:A10)表示Sheet2公司坐标的A1到A10区域的所有值的和。

跨工作表引用时，工作表名称和叹号不需要手动输入，选择工作表区域，名称和感叹号会自动加上。

函数的类别

函数根据功能可以分为财务、逻辑、文本、日期时间、查找、引用、数学与三角函数等，其实掌握少数的核心函数就能解决大部分工作内容。

让Excel变聪明的IF和逻辑函数

IF函数可以判断条件是否成立，选择性输出结果，IF函数的格式为【=IF(判断条件,如果条件成立输出什么结果,如果条件不成立输出什么结果)】

多条件逻辑函数AND和OR

当判断的条件不止一个时，就需要联合AND或OR函数，从字面仪式上就是“与”和“或”的关系，前者只有在两个条件都成立时才返回True，后者只要两个条件中的任意一个成立就返回True，否则返回False。

当输入多层IF嵌套时，连在一起看起来非常复杂，可以展开编辑栏，并且通过ALT加回车快捷键，对每一层IF条件进行强制换行，这样看起来更加直观一些。

使用多层嵌套时，第二个IF可以理解为，如果第一个条件成立，还要再进行什么判断

=if(条件1,if(条件2,返回值1,返回值2),返回值)

上面的公式表示，如果条件1成立，那么再判断它是否符合第二个条件，符合第一个条件则返回值1，符合第二个条件则返回值2，如果刚开始就不符合条件1，就进行返回值3，当然第3个参数也可以插入IF条件语句。

查找核对必备的VLOOKUP家族

EXCEL中查找匹配数据的场景非常常见，因此VLOOKUP的使用频率非常高，它的使用场景包括“按工号从另一张表查找对应的姓名性别等信息，按照等级对照表，将绩效评分和等级一一匹配、核对2张表中重复的记录等…”

查询首选VLOOKUP函数

lookup在英语中就是“查找”的意思，只要碰到从一张表中查询并列出匹配信息的情形，首先要考虑的就是VLOOKUP能不能做到。

有点像PANDAS中，根据DataFrame的index的值，查找数据表中某一列中对应的内容

VLOOKUP有4个参数，语法功能如下：

第1个参数是查找内容,

第2个参数是表格范围（查找内容必须处于该表第一列

第3个参数是返回表格中哪一列的内容，第2列就填2，以此类推

第4个参数是False或True，False表示精确匹配

有时候，要匹配的数据和要查找的表格不在同一张工作表中，但操作方法还是一样的。

如果第4个参数设置为True表示近似匹配，会从比目标小的值中选出最接近目标的值

在近似匹配中，如果查找的在index列中不存在，就会从比它小的数值中找到最接近的，然后返回对应列的值

match函数

match函数用于返回查找内容在所选表格的第几行，也可以将查找范围设置为某一行，那么就会返回查找内容是那一行的第几格。

它有3个参数，第一个参数是查找内容，第2个参数是要查找哪一列或哪几列，第三个参数是可选参数，可以是0、1和-1，如果忽略则默认为1，精确查找是0.

第三个参数表示匹配类型，0表示精确匹配，如果在设置的查找范围内找不到目标则返回#N/A，

1表示查找小于或等于目标的最大值，要使用这个参数，查找范围内的数值必须按照升序排列也就是从小到大，如果找不到则返回#N/A

-1表示查找大于或等于目标的最小值，要使用这个参数，查找范围内的数值必须按照降序排列，也就是从大到小。

match函数可以返回查找内容 在 所指定的范围内 处于第几行，或者处于这一行中的第几位

需要注意的是：如果要进行拖动快速填充，那么第二个位置的查找范围的单元格号码要按F4锁定再拖动，否则在拖动后查找范围也会自动跟着变化。

index函数

index函数可以返回，所选范围内第几行第几列的单元格的值，比如=INDEX(A1:B17,2,1)就会返回，所选区域范围内第二行第一列的单元格的值

如果一张表格内有2列，要根据右边一列的内容返回左边一列对应的值，可以使用如下公式：

=index(自定义表格范围,match(查找值,右边列,0),1)

思路是，用match函数得到查找内容所在的行，作为index函数第二个参数，然后第三个参数设置1也就是第一列，

有了返回值的行号和列号，就能用index函数返回对应位置的内容

常用函数及应用实例

数值取整和四舍五入

ROUND系列函数

在一般情况下，可以通过开始选项卡下的【数字】模块中的两个按钮，设置数字单元格的小数位数，这种方法不会修改该单元格原始的值，只会修改显示的形式

但也有许多函数可以修改小数的位数显示

【round】四舍五入，功能是【计算保留去掉指定的位数后的值】

它有两个参数，第一个是目标单元格，第二个参数是要保留的位数，

值得了解的是，第二个参数允许是负数，可以对数值进行保留整十，整百…

如果是【-1】，可以四舍五入到10分位，比如可以将123这个数变成120，125变成130，如果是【-2】则可以四舍五入到百分位，比如123可以变成100

如果第二个参数是正数，则非常好理解了，根据第二个参数的数值，设置小数的位数，比如A1的数值为123.25，如果是【=round(A1,1)】，就会将123.25四舍五入为123.3

round函数有两个兄弟函数是【ROUNDUP】和【ROUNDDOWN】,前者的功能是向上舍入，后者的功能是向下舍弃

也就是说，通过roundup对某个小数进行舍入，在设置的位数后只要还有数字，通通向上舍入

比如roundup(121.11,1)会变成121.12,

roundup第二个参数也支持负数，比如参数【-1】表示保留10位数，【=ROUNDUP(121,-1)】会变成130，

rounddown函数，可以设定保留几位整数或几位小数，

如果第二参数是正数，可以理解为，第二个参数是几就保留几位小数，比如【roundup(121.22,1)】会显示为121.2

如果第二参数是负数，那就保留几位整数，后面的数都显示为0，比如【=ROUNDDOWN(121,-1)】会显示为【120】

INT函数进行数字取整

【INT】函数只有一个参数，那就是要取整的数值，它不会对数值进行四舍五入，只会保留数值的整数部分

需要注意的是对负数小数的取整,它也算向下舍入

int(0.6)=0

int(-0.6)=-1

int(-1.6)=-2

TRUNC函数保留指定位数，或取整

TRUNC函数有两个参数，第一个是要操作的数值，第二个是保留的指定位数，

如果第二参数是正数，即保留几位小数，如果是负数，则是根据十分位百分位取整，比如=TRUNC(121,-1)会变成120,

trunc只能让小数位数变短，比如trunc(121.1,6)不能让1为小数变为6位小数，依然还是1位小数

文本合并和提取处理

&符号拼接内容

&符号可以用于单元格或字符串的拼接，如果是拼接字符串，需要将字符串放入””内

CONCAT合并多个文本

CONCAT函数可以让多个单元格或一片单元格区域的内容直接拼接在一起，它的参数就是要拼接的单元格，拼接出的结果不会有分隔符

需要注意的是：CONCAT函数只有2016版OFFICE开始才支持

TEXTJOIN带分隔符合并多个文本

textjoin函数与concat的区别是，前者允许你在合并文本时，指定一个分隔符，并且可以选择是否忽略空值

TEXTJOIN的格式是【textjoin(“分隔符”,”True/False”,单元格…)】

其中第二个参数True/False是选择要不要忽略空值，True为忽略（如果没忽略空值，合并结果内容里可能会出现连续两个分隔符的情况，空格不算空值）

例子【=TEXTJOIN(“-“,TRUE,A1:C10)】

提取文本LEFT/RIGHT/MID/LEN

LEFT和RIGHT很简单，指定从左侧提取还是右侧提取，以及提取多少个字符

比如LEFT(A1,6)，就是提取指定单元格从左边开始的前6个字符

MID有3个参数，分别是文本、起始位置、字符数

比如MID(A1,6,10)表示从A1单元格中第6个字符开始，提取10个字符

LEN函数最简单，计算单元格或字符串的字符数

比如=LEN(A1)

以上函数经常被组合在一起使用，以实现更专门的目的

LENB计算文本字节长度

len函数与LENB函数类似，但是前者把所有字符串都看做1个单位，而lanb会把包括中日韩字符等双字节字符集的字符，算作2个单位

SEARCH检索字符位置

在一个文本字符串中搜索另一个文本字符串，并返回其开始位置的字符编号

使用格式：SEARCH(“要查找的内容”,”在哪个文本查找”,从指定位置开始开始搜索（可选参数，默认为1）)

比如：

=SEARCH(“一”,”爱你一万零一年”)会返回3

=SEARCH(“一”,”爱你一万零一年”,5)会返回6

注意事项：

EXCEL的起始位置数字都是1，坐标数字基于左边第一个字符计算

SEARCH函数不会区分大小写

在没找到目标的情况下返回#VALUE!

可以在要查找的内容的参数中，使用通配符包括问号【?】（匹配任意单个字符）和型号【*】匹配任意一串字符，如果要查找真正的问号或型号，可以在相关字符前面键入波形符(~)

还有一个类似的函数是【searchB】，功能一致，但是在SEARCHB中，中文字符会以2个单位计算，比如【=SEARCHB(“一”,”爱你一万年”)】会返回【5】

find

find与search的功能类似，也是查找一个字符串在另一个字符串的位置，但find的要求更精确，它会区分大小写，以及不能使用通配符

FIND(“要查找的内容”,”在什么文本查找”,”从指定位置开始搜索，可选参数，默认为1″)

除此之外还有一个函数是findb，中文字符会以2个单位计算

REPLACE 根据指定位置 替换文本字符串中的一部分

如果有一段文本，想要修改其中的一小部分，可以使用REPLACE函数

REPLACE函数的格式【REPLACE(“原始文本”,开始替换的位置,要替换的字符数量,”用于替换的新文本”)】

需要注意的是，新文本会完完整整的插入旧文本，也就是说，第三个参数要替换的字符数量，可以理解为，从原始文本中划出哪个位置，放入新文本

比如【=REPLACE(“old text”,2,2,”pppppppp”)】得到的结果为【opppppppp text

】

另外如果设置的起始位置大于字符数，比如总共10个字符，但却设置在第100个字符的位置处开始替换，那么新字符会直接放在旧字符的结尾，相当于使用了&符号连接了两个字符

起始字符的位置按1开始计算

存在一个REPLACEB函数，唯一的区别是，中日韩语言的字符，一个按两个单位算

SUBSTITUTE 根据指定字符串来替换

SUBSTITUTE函数类似与CTRL+H

它有3个必要参数以及1个可选参数

格式是SUBSTITUTE(“原始文本”,”被替换的文本”,”新文本”,指定第几个被替换)

其中“被替换的文本”相当于CTRL+H的第一个输入框（查找内容），“新文本”相当于CTRL+H的第二个框（替换为），最后一个是可选参数，可以指定仅仅替换第N个匹配对象

比如：【=SUBSTITUTE(“OLD TEXT”,”T”,”A”)】中，指定将原始文本中的T换成A，得到的结果就是【OLD AEXA】

如果加上最后一个可选参数，可以指定 仅替换第N个符合条件的字符，比如【=SUBSTITUTE(“OLD TEXT”,”T”,”A”,2)】，最后的参数2指定了只有第2个T被替换，得到的结果是【OLD TEXA】

清洗数据中的多余字符

TRIM删除文本中的多余空格

除了单词之间正常的1个空格之外，TRIM会删除其他多余的空格（连续空格只保留1个）

使用格式：TRIM(“文本”)

【=TRIM(“T R I M”)】得到的结果是【T R I M】

TRIM不会删除制表符TAB

CLEAN删除文本中不能打印的字符

如果你的文本中含有一些看不见的奇怪字符，这些通常是从其他地方复制或导入时带过来的，使用CLEAN函数可以帮你清除这些隐藏的杂乱字符，让文本变得“干净”。

使用格式：CLEAN(“TEXT”)

文本强制转换数值

有时候从其他地方到处的数据，虽然带有日期，但日期格式因为在EXCEL中不算标准，所以EXCEL会将它们识别为文本

如果EXCEL将一个日期单元格识别为文本，而两个文本单元格因为不是数值无法比大小，可以让文本型的日期单元格【*1】或者【–】（负负得正），来得到该日期对应的数值

比如A1单元格的内容是【’2017年12月18日】或者【’2017/12/18】,注意单元格开头的【’】号，意味着他们是文本单元格，可以通过【=A1*1】或者【=–A1】，来得到一个正常的日期数值

需要注意的是，EXCEL起始日期为1900年1月1日，该日期对应的数值为1

如果希望通过文本强制转换数值的方式，从一个日期文本得到一个标准的Excel日期对象，如果文本的内容是诸如【19980210】这样的8位数字，这样的方法不可行，因为数字1对应1900年1月1日，那么【19980210】对应着相对天数后的日期（约两千万年之后），

我们可以通过TEXT函数，将【19980210】这样的内容转换为比如【1998-02-10】这样的格式，然后就能拿到正常的日期了

如果A1单元格内容为文本【’19980210】，那么我们可以通过【=TEXT(A1,”0000-00-00″)*1】，先将A1单元格转换成【1998-02-10】的文本，然后让该文本【*1】得到日期格式的数值

TEXT将数值格式化为文本，并按照指定的格式显示

TEXT可以将数值格式化为文本，并按照指定的格式显示，TEXT相当于数字格式功能，区别在于，数字格式只能改变形式却不会改变内容本身，而text函数是从里岛外都一起改成格式参数的样子

TEXT函数让你把数字按照你想要的格式（比如日期、货币、百分比）等转换为文本形式，这样就可以在展示数据的时候更加符合阅读习惯

使用格式：TEXT(要转换的数值,”指定的格式”)

“指定的格式”是以字符串的形式指定的，如”yyyy-mm-dd”表示日期格式

例1 经典用法：

数值：1234

公式：=TEXT(1234, “0.00”)

结果：显示为 “1234.00”，即使原数值没有小数部分，也会显示两位小数。

例2 使用日期格式：

数值：43101（Excel中的日期序列号）

公式：=TEXT(43101, “yyyy-mm-dd”)

结果：显示为 “2017-12-31”

例3 货币格式应用：

数值：1234.56

公式：=TEXT(1234.56, “$#,##0.00”)

结果：按货币格式显示 “￥1,234.56”

例4 百分比显示：

数值：0.85

公式：=TEXT(0.85, “0%”)

结果：显示为 “85%”

例5 自定义文本和数字组合：

数值：123

公式：=TEXT(123, “在库存中有000个单位”)

结果：显示为 “在库存中有123个单位”

1. 理解基本的格式符号

0：表示数字必须在此位置显示。

#：表示数字在此位置显示，但不重要的前导零或尾随零不显示。

?：用来在对齐数字时占位，但不显示不必要的零。

@：表示文本项。

$、%、+、-、, 和 / 等符号用于表示货币、百分比、千位分隔符等。

2. 学习常用的格式代码

日期和时间：

“yyyy-mm-dd”：完整的日期格式。

“ddd, mmm dd, yyyy”：带星期的日期格式。

“hh:mm AM/PM”：12小时制时间格式。

“hh:mm:ss”：包含秒的时间格式。

数字：

“0.00”：两位小数的数字。

“#,##0″：千位分隔符的整数。

“0%”：百分比格式。

货币：

“$#,##0.00″：带有千位分隔符和两位小数的美元格式。

“¥#,##0.00″：日元货币格式。

日期和时间计算

DATEDIF计算两个日期的间隔

由于日期和时间的本质是数字，所以两个日期对象可以直接相减，得到两者之间的间隔

需要注意的是，如果在公式编辑框中插入一段日期，同时在这个编辑框中与另一个对象进行运算，需要将日期文本用双引号包裹，否则【/】号会被EXCEL理解为 除号，或者出错

比如【=”2024/5/10″-“2024/5/9″】

两个时间单位进行运算，得到的会是1个小数（以1天为完整单位），比如1小时用小数表示可能就是【0.041666667】，表示0.0417天，如果想要将这个小数转换为小时显示，比如想知道0.0417天等于多少小时，只需将该数字乘以24

在不要求精确的情况下，

如果要计算两个日期相隔多少个月，可以在两个日期相减后，再除以30，

同理，如果要计算两个日期之间相隔多少年，可以在两个日期之间相减后，除以365

需要精确的计算间隔，那就需要使用DATEDIF函数了

DATEDIF有三个参数，分别是起始日期，结束日期，日期单位

需要注意的是，结束日期必须大于起始日期

格式为：DATEDIF(起始日期,结束日期,”日期单位”)

日期单位有以下几种：

Y（年）/M（月）/D（天）/

MD（不计年月的天数差）/YM（不记年的月数差）/YD（不记年的天数差）

“不记年”表示将两个日期视作同一年，“不计年月”表示将连个日期视作同年同月

比如

=DATEDIF(“2023/05/10″,”2024/05/10″,”d”)得到的结尾为366

=DATEDIF(“2023/05/10″,”2024/05/10″,”md”)得到的结果为0

DATEDIF似乎是个隐藏函数，即输入函数时不会出现提示

TODAY返回当前日期，以及制作倒计时

=TODAY()即刻范围计算机系统当前的日期，该函数没有参数

如果只想输入今天的日期，希望EXCEL以后不再更新，中英文输入法下可以通过【CTRL+分号】

TODAY()函数也可以用来制作倒计时，倒计时=截止日期-TODAY()

其他可以不写参数的函数，NOW/ROW/COLUMN/RAND

以下函数 没有 或 不需要 参数，都是使用后直接返回相关结果

NOW()函数可以返回计算机系统当前（加载，或输入函数的那一刻）的日期和时间，比如【2024/5/10 6:25

】,每次打开或刷新文档，这个时间都会更新

ROW()函数可以返回当前单元格或指定单元格，在没有参数的情况下，返回当前单元格所在的行号，也可以指定单元格或区域，如果参数是多个单元格，而这些单元格处于不同的行，这个函数会返回多个行号，而且这些行号也就是返回结果，会溢出到函数所在单元格下面的行

简单来说就是，如果目标区域在不同的行，那ROW()函数的返回结果也会有多行数字

此外有个函数叫ROWS()，可以计算目标单元格区域有多少行

COLUMN()与ROW()函数同理，只不过它计算的事列号，另外列号是数字形式，而不是EXCEL中的 ABCD…

同样的，当目标单元格在多列，它返回的结果也会溢出到其他单元格

此外有个函数叫COLUMNS()，可以计算目标单元格区域中有多少列

RAND()函数可以返回从0到1之间的随机数，通常大于等于0，小于1，该函数没有参数

每次加载或输入一个rand()函数，这个sheet也其他rand()函数也会刷新

F9键可以刷新整个工作簿中所有的公式（shift+f9仅刷新当前工作表），也就可以用来刷新rand()函数的返回值，

此外，如果希望rand()函数得到结果后保持固定，可以复制再粘贴为纯文本

NETWORKDAYS计算两个日期间相隔的工作日

NETWORKDASY()函数可以计算两个日期段内的工作日总数，周六与周日不算工作日，此外第三个可选参数，可以指定日期为节假日

如果开始日期晚于结束日期，函数会返回负数

公式格式为：NETWORKDAYS(起始日期,结束日期,节假日)

比如【’=NETWORKDAYS(C315,C316,D319:D321)】

从日期时间中提取年、月、日、星期、小时、分、秒

YEAR	从日期中提取出”年”
MONTH	从日期中提取出”月”
DAY	从日期中提取出”日”
WEEKDAY	计算出与日期相对应的星期
HOUR	从时间中提取出”时”
MINUTE	从时间中提取出”分”
SECOND	从时间中计算出”秒”

DATE根据年月日合成一个标准日期

DATE函数有3个参数，分别代表了年、月、日，根据这3个参数可以生成一个标准的日期

比如

另外，如果代表月份的数字不在1-12这个范围内，可以是小于等于0也可以大于12，EXCEL会自动对日期进行进一步计算，比如将【2013 0 10】这三个数字用date函数合并成日期，就会得到2012/12/10，

代表日的参数也是同理

另外需要注意的是，代表年份的参数最好是四位数字的标准年，如果不是4为数字，它会以1900年作为第一年进行换算，比如0代表1900年，参数100可以得到2000年

EDATE求指定天数、月数后的日期

一个方法是：提取原日期中的年月入，在月数上增加相应的数字，再用DATE函数合成一个新的日期

更简单的是使用EDATE函数，可以返回：某个日期 增加或减少几个月 后的日期

使用格式：EDATE(“开始日期”,月数)，其中第二个参数月数可以是正数也可以是负数，正数表示相加，负数表示减掉

比如【=EDATE(“2023-01-01”,1)】可以得到【2023-02-01】，但需要注意的事，该函数默认返回的好像是数字形式，需要手动调整成日期形式

该函数会自动考虑天数的情况，比如1月31日，增加1个月，得到的会是2月28日或2月29日（根据是否为闰年自动调整）

EOMONTH计算月底最后一天的日期

如果能理解EDATE，立即接EOMONTH就非常容易，两个参数非常相似，但是EOMONTH返回的是，计算了月份后的日期，当月的最后一天

使用格式：EOMONTH(“日期”,月份数量)

比如2023年1月1日，下个月的最后一天是什么日期，可以写【=EOMONTH(“2023-01-01”,1)】，返回结果是2023/2/28，

如果第二参数为0，即可用于计算该日期当月的最后一天的日期

第二参数可以为负数

数学运算和统计

MAX函数可以得到一组数字中，最大的一个数字，而日期本质上也是数字，因此MAX函数同样也能得到一组日期中，最大的一个日期

COUNT统计一组单元格内数值单元格的数量

COUNT函数可以统计一组单元格内，数值单元格（包括日期单元格）的数量

非数值类型（比如文本、错误值、逻辑值）或空单元格，都不在统计范围之内

COUNTA统计一组单元格内所有非空单元格的数量

不论数据类型，只要是非空单元格，都在它的统计范围之内

COUNTIF统计符合条件的单元格个数

使用格式：COUNTIF(单元格区域,”条件”)

需要注意的是，“条件”参数可以非常灵活，可以是另一个单元格，也可以自定义条件，自定义条件需要放在双引号之内，也可以是自定义条件加上＆连接符再加上另一个单元格，自定义条件内还可以使用通配符

以下是一些使用案例：

统计A列中大于1的单元格数量=COUNTIF(A:A,”>1″)

统计A列中名字是苹果*的单元格数量=COUNTIF(A:A,”苹果*”)，也可以是【*果*】表示查找名字带有果的单元格

统计6个字符的单元格数量=COUNTIF(A:A,”??????”)

条件参数中的文本，可以与另一个单元格连接，比如设置条件为大于等于另一个单元格，注意有&符号：=COUNTIF(A:A,”>=”&B1)

查找文本单元格的数量（不包含数字、公式等）：=COUNTIF(A:A,”*”)

统计非空单元格的数量（<>表示不等于空/不等于）：=COUNTIF(单元格区域,”<>”)

查找某个区域内制出现过一次的品名：=IF(COUNTIF($B$82:$B$92,G86)=1,G86,””)

SUMIF根据指定条件对区域中的单元格求和

SUMIF可以在一个区域内找到符合特定条件的单元格，并将这些单元格的数值加起来

使用格式：SUMIF(检查区域,”自定义条件”,求和区域)

其中检查区域类似于VLOOKUP中的查找区域，求和区域类似于VLOOKUP中匹配出来的值

自定义条件会在查找区域中查找符合条件的单元格

自定义条件可以是数字、表达式或文本形式（表达式指的是运算符或通配符）

理论上匹配区域的单元格数量和求和区域的单元格数量应该一样，但求和区域的单元格要是数量较少也会自动扩选？

AVERAGEIF根据指定条件对区域中的单元格求平均值

用法给SUMIF一样，只不过它返回条件是目标的平均值

SUMIFS根据多个条件求和

SUMIFS函数可以让你在EXCEL中该设置多个条件，只对满足所有这些条件的单元格进行求和计算

SUMIFS函数要求EXCEL的版本不低于2016

SUMIFS当然也可以只根据1个条件求和，可以替代掉SUMIF

SUMIFS函数使用格式：

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

sum_range表示求和区域（类似于VLOOK中被匹配出来的值）

criteria_range表示查找区域

criteria表示自定义条件

在SUMIFS中，求和区域被放在第一个参数

比如有下面这个表格，我们要计算产品A同时是类别1的销售额总和

我们的公式就可以是【=SUMIFS(销售额列,产品列,”产品A”,类别列,”类别1″)】

第一个参数是销售额这一列，之后的参数分别是：查找区域1、查找条件1、查找区域2、查找条件2

条件可以使用通配符，通常非数值都会有双引号

产品	类别	销售额
产品A	类别1	100
产品B	类别2	150
产品C	类别1	200
产品A	类别2	120
产品B	类别1	180
产品C	类别2	90
产品A	类别1	80

AVERAGEIFS根据多个条件求平均值

根据一个或多个条件求平均值，使用方法与SUMIFS相同，但返回的是匹配结果的平均值

仅支持EXCEL2016以上的版本

COUNTIFS计算符合多个条件的单元格数量

仅支持EXCEL2016以上的版本

如果你想知道有多少个单元格 同时满足多个特定条件，可以使用COUNTIFS

使用格式为：=COUNTIFS(查找区域1,条件1,查找区域2,条件2)

比如有下面这样的表格，我们想要统计技术部中评分大于3的有多少人，

我们可以使用公式：=COUNTIFS(部门列,”技术部”,评分列,”>3″)

姓名	部门	评分
美玲	销售部	3
婉儿	财务部	4
明轩	技术部	5
雅琪	销售部	4
俊杰	技术部	3
佳琳	财务部	5
雅婷	财务部	3

MAXIFS返回满足一个或多个条件的最大数值

如果你想找出符合特定条件（比如某个区域、某个时间段或其他条件）的最大数值，可以使用MAXIFS函数

使用格式：MAXIFS(最大值区域,条件区域1,条件1,条件区域2,条件2,…)

条件区域和条件，这两个参数的单元格区域必须具有相同的大小和形状

如果没有任何单元格满足所有条件，将返回错误值#N/A

条件可以使数值比较、文本匹配、日期等

MAXIFS会先匹配出符合条件的单元格，然后使用最大值，最大值所在的列，需要在该函数第一个参数中设置

比如有下面这样一个表格，我们希望得到 技术部的 最大销售额值

可以用公式：=MAXIFS(最大值列,部门列,”技术部”)

我们还可以继续增加条件，

在技术部中给所有销售额中，找出低于190的销售额中，最大的销售额，

可以这样写：=MAXIFS(最大值所在列,部门列,”技术部”,销售额列”<190″)

姓名	部门	销售额
雅丽	销售部	100
婉婷	财务部	150
晨曦	技术部	200
美华	销售部	120
日出	技术部	180
雅雯	财务部	90
美玲	财务部	80

MINIFS返回符合一个或多个条件的最小值

使用格式与MAXIFS相同，但返回的是最小值

IFS根据不同条件指定不同返回结果

IFS函数允许用户在一个公式中测试多个条件，并且返回第一个为True的条件

如果所有的条件都没有触发，则返回错误值#N/A

使用格式：IFS(条件1,返回结果1,条件2,返回结果2)

比如=IFS(A1>1,”OK”,A2>1,”OK”)

RANK排名计算

RANK函数用于确定一个数字在一数据列表中的排名，

如果在数据数组中存在多个相同的数字，RANK函数会返回这个数字的最高排名（会出现多个数字同一个排名的情况，比如两个人排在第1位，第三个数字排在第三位，没有排名第二）

使用格式：RANK(要判断的单元格,查找的数组区域,0或者1)

一定要记住，查找的数组区域要按F4锁定绝对引用，否则自动填充时会出现偏移！！！

第三个参数如果为0，最大数排第一，如果为1那就是最小数排第一

如果希望排名不出现间隔，比如两个人排第一，第三个人排第二而不是第三，可以参考《跟秋叶一起学EXCEL一书》

生成随机数据（F9可刷新工作簿）

RAND生成一个大于0且小于1的随机数

RAND()函数可以返回从0到1之间的随机数，通常大于等于0，小于1，该函数没有参数

每次加载或输入一个rand()函数，这个sheet也其他rand()函数也会刷新

F9键可以刷新整个工作簿中所有的公式（shift+f9仅刷新当前工作表），也就可以用来刷新rand()函数的返回值，

此外，如果希望rand()函数得到结果后保持固定，可以复制再粘贴为纯文本

RANDBETWEEN生成一个指定范围内的随机整数

RANDBETWEEN函数会返回一个介于指定的两个整数之间的随机整数，包括这两个整数在内

将单元格调整为日期格式，也就相当于生成随机的日期

使用格式：RANDBETWEEN(最小值,最大值)

生成的随机数可能出现重复，可以先生成随机数，再删除重复值

IFERROR指定如果公式返回错误值，则显示什么

IFERROR函数用于检测公式是否会出现错误，如果出现错误，返回自定义结果，没有出现错误，则正常显示公式原来的返回结果。

使用格式：IFERROR(需要检测的公式或表达式,出错时的返回结果)

公式常见错误怎么处理

公式出错检查清单

类型	要点	说明	解决思路
数据规范	单元格格式	公式单元格为文本格式，只显示公式， 不显示计算结果	①分列法： 批量转换常规格式 ②公式法: *1、–
	数字格式	编辑栏中实际内容不一致	统一内容和数字格式
	数据错漏	拼写错误、漏字、多字	填补空缺： 替换、修改、删除
	拼写不一致	同一项内容多种拼写方式	统一数据： 筛选、替换、批量填充等
	多余空格空行	存在多余空格和不可见符号	剔除空格空行：替换、定位、排序、填充等
	假日期、假数字	无法计算	①分列法： ②公式法
公式书写	名称拼写	名称字母顺序写错， 写错、多、漏掉字母	逐字符检查核对
	标点符号	多余、遗漏， 中文标点	逐个符号、逐个参数核对
	嵌套函数	括号位置错误	按括号颜色分辨嵌套层次
参数规范	遗漏	缺必要参数， 遗漏分隔符	查看函数帮助和提示进行修正
	过多	多余分隔符	检查分隔符，删除多余符号
	不符合要求	不符合函数本身对参数的需求。如SUMIF 中求和区域和条件区域必须同等大小	查看函数帮助和提示进行修正
数据引用	引用方式	要固定引用的范围采用相对引用，致公式填充时引用范围偏移	①双击进入编辑状态，检查引用框位置 ②公式选项卡下， 显示公式按钮，查看公式引用情况 ③F4键切换引用方式
	范围不完整	遗漏了新增、修改的或其他数据	重新选择引用范围： 拖曳引用框边角，或修改公式参数
	丢失	数据源删改导致丢失	同上
	偏离	数据源移动导致偏移	同上

排错工具和技巧

【公式】选项卡下有一系列的公式审核、查错工具，当公式出现错误可以尝试利用这些工具进行排查

【显示公式】可以让工作表“显示公式”而不显示公式结果，如果选中公式单元格再点击显示公式，还能自动以彩色引用框显示该公式所引用的数据和区域

【追踪引用单元格】（查看该公式引用了哪些单元格）可以让公式引用的单元格与公式所在的单元格之间创建箭头（需要先选中公式单元格，然后再点击这个按钮，就能得到该公式引用的单元格的箭头）

【追踪从属单元格】（查看该单元格被哪些公式引用）先选中单元格，然后点击“追踪”，就能通过箭头看见该单元格被哪些公式所引用

【公式求值】该功能会出现一个对话框，可以将公式一步一步分解，逐步查看计算结果，从而帮助发现错误的具体位置，更加明确错误源头来自哪里