EXCEL常用函数

数学和三角函数中求和类的函数（SUM、SUMIF、SUMIFS、SUMPRODUCT）归在统计函数中。

函数有嵌套时，先写里面的嵌套函数，再写外面的函数。

1. 查找和引用函数

1.1 VLOOKUP函数（只能从左往右找）

069-VLOOKUP函数基本应用——王佩丰·微软最有价值专家
VLOOKUP函数根据关键字取回相关的信息。

语法：

VLOOKUP (查找值，在哪里找（选区），引用选区第几列，精确/模糊匹配)

选区 最好直接选择整列区域A:C，而不仅是只有数据的那些行列区域A1:C20.
因为如果向下填充单元格，那么只有数据的行列区域也会跟着变。
所以如果要选择只有数据的那些行列区域，那么就要额外锁定该区域，使其由相对引用变为绝对引用。

选择好选区之后，就在选区所在的表中，继续将函数后面的其它参数填写完毕。
不要点其它表，否则选区所在的表会发生变化，从而发生错误。

VLOOKUP函数常见错误排查：
EXCEL中纯数字有两种形态，其中：数值形态（单元格内靠右）不能连接其它字符，文本形态（单元格内靠左）不能加减乘除。

因此，在单元格数值后面，连接&：空内容（一对双引号）或 一个星号 “*” ，就可以将纯数字转化为文本。 （不可以连接其它字符）

而在文本后面连接进行乘除1，就可以将文本转化为数值。
只能使用乘除，而且只能乘1，或者除1.（不能使用加减，也不能乘除其它数值。）

总之，相同含义的纯数字必须保持同一种形态，才能相等（用=计算之后，才能返回TRUE. ）

070-VLOOKUP常见错误排查

VLOOKUP函数使用注意事项及可能返回的错误类型：

1. VLOOKUP函数只会在选区的第一列找查找值，所以选区的第一列必须是被查找的列。
   （VLOOKUP函数的局限性，只能根据左边的值，找右边的值。）
   如果不满足这个条件，或者选区范围正确但第一列中没有查找值，那么都会返回 “#N/A” ，代表找不到，没有可提供的值。

MATCH 结合 INDEX函数，即可以根据左边的值，找右边的值； 也可以根据右边的值，找左边的值。

1.1.1 VLOOKUP函数 与 IFNA函数、IFERROR函数

可以使用 IFNA(VLOOKUP函数，当发生#N/A错误时的返回值) 来屏蔽VLOOKUP函数中 “#N/A” 错误。
不能用来屏蔽其它错误。
075-用IFNA函数屏蔽VLOOKUP错误

=IFNA(VLOOKUP(F5,A:C,3,0),"")  #只有发生#N/A错误时，才返回空

2. 当VLOOKUP函数中，引用列号参数小于1或者缺失，就会返回 “#VALUE!” 错误。

3. 如果要引用的列超出了选区的最大列数，那么就会返回 “#REF!” ，代表reference，引用错误。

4. 如果函数名称写错，就会返回 “#NAME!” 错误。
   （在其它函数中，如果函数名称写错，或者使用了中文引号（应该使用英文半角引号），那么也会返回 “#NAME!” 错误。）

【这四种错误，都属于ERROR类型。还有一种是 “#DIV/0!”（分母为0）的错误。】

=IFERROR((B4-C4)/B4,"")  #可以屏蔽（任何函数中）以上所有错误类型，发生任意错误时都会返回空

1.1.2 VLOOKUP函数 与 （固定位数）文本截取函数

返回身份证号前六位地区编码的地区名称。
076-利用文本函数截取文字
【注意文本形式与数值形式要进行转换。】

1.1.3 VLOOKUP函数 与 MATCH函数（返回多列结果）

091-VLOOKUP返回多列结果

1.1.4 VLOOKUP函数 与 LOOKUP函数（多条件查询）

1.1.4.1 方法一：创建辅助列，并使用VLOOKUP函数

092-VLOOKUP函数多条件匹配

1.1.4.2 方法二：使用LOOKUP函数

1.1.5 VLOOKUP函数部分匹配

093-公司名不完整如何查询

在要查找值的前面及后面（或者一侧）连接上通配符 “ * ”，表示包含查找值（或者以查找值开头、结尾的）所有字段内容都要进行匹配。

1.1.6 VLOOKUP函数 与 INDIRECT函数（跨多表抓取数据）

097-VLOOKUP函数跨多表抓取数据

使用INDIRECT函数从多个表中取数据：每个表中的表头应该一样（列名称及其列号）。
最起码查找的范围，表头应该一样。

但某个列的某个值在其列下面的哪一行：
一样可以：参见1.4 INDIRECT函数（间接引用）中的例子。
不一样也可以：参见本例。

在这样的多个表中取数据，就要先将表名称全部列出来。

此时“二月”的“总计”数据不同于其它月份总计的位置，在B4单元格。

除二月之外，其它月份的“总计”值都在B5单元格。

再使用1.4 INDIRECT函数（间接引用）中的方法就无法一次性全部实现了。
如下图中的第一列所示，二月并没有取到正确的总计值。

如果只使用VLOOKUP函数，那么就要在每一个表中都进行查找，需要使用多次VLOOKUP函数。

= VLOOKUP("总计",X月!A:B,2,0)

但每个VLOOKUP函数的公式之间，又只有引用的 “X月” 表名称不同，其它部分都完全一样。
（有时，前面的查找值也不一样，但可以在下拉拖拽时，自动变换正确。
这种情况下，也可以使用 VLOOKUP函数 与 INDIRECT函数 实现跨多表获取数据。）

【所有重复性的工作，都会有简单的实现方法。】

所以要使用 VLOOKUP函数 与 INDIRECT函数，从而用一个公式下拉填充之后，也完成其它“总计”数据的获取。

此时仍然遵循 1.4 INDIRECT函数（间接引用）中 INDIRECT函数三步走写法：
（1）先手工写几个VLOOKUP函数公式，观察每个公式之间的联系。
【发现只有查找范围中，引用的 “X月” 表名称不同，其它部分都完全一样。】

= VLOOKUP("总计",X月!A:B,2,0)

（2）拆分第（1）步中的公式，将查找范围中，固定的部分用双引号包上，不固定的部分删除掉，引用到A3单元格。最后再把两个部分连接&起来。

A3&"!A:B"

（3）外面包INDIRECT函数。

INDIRECT(A3&"!A:B")

当把 INDIRECT(A3&“!A:B”) 作为VLOOKUP函数的查找范围参数时：
区别于单纯的字符串连接&，INDIRECT函数的作用在于：
会自动识别连接&后的字符串A3!A:B，中的 A3! 为表名称，“A:B” 为要在该表 A3! 中查找的范围。并且激活该范围。

整个公式：
=VLOOKUP("总计",INDIRECT(A3&"!A:B"),2,0)

1.1.7 VLOOKUP函数 与 条件格式

099-查询并标记出停产商品

1.1.8 VLOOKUP函数 与 数据验证

100-禁止输入停产商品

1.2 MATCH 和 INDEX函数（从左往右找 + 从右往左找）

090-MATCH+INDEX黄金搭档

这两个函数本身完成的任务很简单，但组合起来就很强大。
因为它们所涉及的行/列（MATCH函数中的lookup_array参数，INDEX函数中的array参数），可以不一样。

核心就是将MATCH函数返回的行号数字结果，作为INDEX函数的第二个参数row_num.
或者将MATCH函数返回的列号数字结果，作为INDEX函数的第三个参数column_num.

108-函数抓取图片（EXCEL版）

1.2.1 MATCH函数（找某个内容所在的行号、列号）

excel中match怎么用——PingCode智库

MATCH函数用于查找指定项在数组或区域中的相对位置。

基本语法为：

MATCH(lookup_value, lookup_array, [match_type])

lookup_value：要查找的值。

lookup_array：包含可能查找值的单元格区域。
MATCH函数的查找范围 lookup_array 只能是单行，或者单列，不能是多行多列的区域。

match_type：匹配类型，默认为1。可以为1、0或-1。
匹配类型详解：
1（默认）：查找小于或等于lookup_value的最大值，要求查找数组按升序排列。
0：查找等于lookup_value的值，要求查找数组可以是任意顺序。
-1：查找大于或等于lookup_value的最小值，要求查找数组按降序排列。

返回值：MATCH函数返回的是数字。
如果MATCH函数的查找范围lookup_array为单行，那么返回的就是查找lookup_value在该行中，所在的列号。
如果MATCH函数的查找范围lookup_array为单列，那么返回的就是查找lookup_value在该列中，所在的行号。

1.2.2 INDEX函数（取特定单元格中的内容）

excel表格index函数怎么用——PingCode智库

INDEX函数用于从指定的数组中返回指定行和列交叉处的值。
值是什么内容，INDEX就返回什么内容，数据类型也保持不变。
这里的 INDEX不是索引编号的意思，而是引用的内容。

基本语法如下：

INDEX(array, row_num, [column_num])

array：要查找的数组或单元格区域/范围。
row_num：数组中要返回的行号。
【如果范围array为单列，那么只需要指定要返回的行号row_num. 】
column_num：数组中要返回的列号，可选参数。

1.2.2.1 利用滚动条制作信息查询表

101-利用滚动条制作信息查询表

1.3 LOOKUP函数

1.4 INDIRECT函数（间接引用、跨表获取数据）

excel怎么样引用其它单元格内容——PingCode智库
直接引用： 是最简单的方法。只需在目标单元格中输入等号（=），然后点击需要引用的单元格。

096-认识INDIRECT函数

098-INDIRECT跨表的常见错误

INDIRECT函数主要用于：
① 动态 引用 单元格、跨工作表引用、创建动态范围名称。
② 激活 与 要引用的 单元格、工作表、范围 形式相同的 文本字符串，使其变成可以引用的区域。

=INDIRECT(ref_text, [a1])

ref_text：
这是一个文本字符串，用于定义要引用的单元格地址或者范围。可以是直接输入的字符串，也可以是包含地址的单元格。

a1：[可选]
这是一个逻辑值，用于指定引用样式。
如果为TRUE或省略，则ref_text被解释为A1样式引用；
如果为FALSE，则ref_text被解释为R1C1样式引用。

此时，每个月的“总计”值，都在B5单元格。
区别1.1.6 VLOOKUP函数 与 INDIRECT函数（跨多表抓取数据）

使用INDIRECT函数从多个表中取数据：每个表中的表头应该一样（列名称及其列号）。
最起码查找的范围，表头应该一样。

但某个列的某个值在其列下面的哪一行：
一样可以：参见本例。
不一样也可以：参见1.1.6 VLOOKUP函数 与 INDIRECT函数（跨多表抓取数据）中的例子。

在这样的多个表中取数据，就要先将表名称全部列出来。

INDIRECT函数三步走写法：
（1）先手工写几个公式，观察每个公式之间的联系。
【总结（直接引用）那一列。】

（2）拆分第（1）步中的公式，将固定的部分用双引号包上，不固定的部分删除掉，引用到单元格。最后再把两个部分连接&起来。
【总结（不加INDIRECT）那一列。注意，这不是引用，只单纯是一个字符串的连接。】

（3）外面包INDIRECT函数。
【总结（加上INDIRECT）那一列。】
INDIRECT函数自动识别其参数中的内容 “H3&“!B5”” 为 “表格名称H3! 及 表中的单元格B5” ，并且激活该表H3!及其单元格B5，从而实现跨表取数。

1.4.1 制作二级下拉菜单

103-制作二级下拉菜单

1.5 OFFSET函数

104-认识OFFSET函数
excel offset函数怎么用——PingCode智库

105-动态抓取数据生成折线图

主要功能：通过指定的行和列偏移量来引用一个单元格或单元格区域。

基本语法：

OFFSET(reference, rows, cols, [height], [width])

reference: 起始引用单元格。

rows: 从起始单元格开始的（下移）行偏移量。
cols: 从起始单元格开始的（右移）列偏移量。
【同EXCEL中计算年份/月份/天数的间隔值一样，下移、右移时，均不包含起始行列，但包含终止行列。】

height: 【可选】返回区域的高度（行数）。
width: 【可选】返回区域的宽度（列数）。
【以前面三个参数确定的单元格为左上角，即为第一行第一列（即包含起始行和起始列），取height行，取width列。】

COUNTA每一个不为空的单元格都包含。

由于rows、height和COUNTA之间的关系，最终要取的行数height，与要下移的行数rows之间，
正好满足关系式：rows = COUNTA(A:A) - height

2. 逻辑函数

2.1 IF函数

072-处理条件判断-IF函数
IF函数是从左到右，依次执行。（并不是先执行括号中的。）

两种情况：

IF(条件，满足条件时的返回值，不满足条件是的返回值)

三种情况：多重判断，嵌套使用两个IF函数

IF(条件1，满足条件1时的返回值1，IF(条件2，满足条件2时的返回值2，既不满足条件1也不满足条件2时的返回值3))

用IF进行连续区间的判断时，只需要写一个界限就可以。
073-根据工龄算年假-IF区间判断

因为只有不满足上一个区间，才会进入包含下一个区间的在内部嵌套的IF函数中。
IF(B2<2,5,IF(B2<5,10,15))
只有不满足小于2的，即大于等于2的，才会判断其是不是小于5；
既不满足小于2的，也不满足小于5的，即大于等于5的，就是剩余的情况。
这里看似只关注了区间上限，但其实区间的下限也都可以被包含进去了。

2.2 AND函数 和 OR函数

一般 AND函数 和 OR函数 都在IF函数的条件中出现。

EXCEL中的 AND函数 和 OR函数 与编程语言，DQL语言中 的AND和OR用法均不相同。
074-AND函数和OR函数

在EXCEL中 AND函数 和 OR函数 的用法：
AND(条件1，条件2，......)
OR(条件1，条件2，......)

3. 文本函数

用文本函数得到的结果，未加处理，一定是文本。

通过LFET、RIGHT、MID、FIND、LEN五个函数的灵活组合，实现文本中任意规则的截取。

3.1 （固定位数）文本截取函数

076-利用文本函数截取文字

可以实现文本截取的其它方式：
①快速填充 016-神奇的智能填充
②分列——固定宽度方式 037-利用分列工具整理数据

LFET(要截取的文本，从最左开始截取的字符长度)
RIGHT(要截取的文本，从最右开始截取的字符长度)
MID (要截取的文本，截取的起始位置（从左边第一个为1开始），向右截取的字符长度)
【区别取余函数 MOD(被除数，除数)】

3.2 FIND函数

077-FIND函数查找字符位置

FIND(find_text, within_text, [start_num])参数解释：
find_text：要查找的文本或字符，用英文引号括起来。
within_text：要在其中查找find_text的文本。
[start_num]：可选参数，表示从within_text的第几个字符开始查找。如果省略此参数，将从文本的左侧第一个字符开始查找。FIND函数的返回值是 所查找的文本或字符 从左侧开始数的位置数。

可以实现文本截取的其它方式： 同上
①快速填充 016-神奇的智能填充
②分列——固定宽度方式 037-利用分列工具整理数据

=LEFT(A2,FIND("@",A2)-1)
=RIGHT(A2,LEN(A2)-FIND("@",A2))  / =MID(A2,FIND("@",A2)+1,10000)

3.3 LEN函数

LEN(text)   #返回值为字符串的长度

3.4 SUBSTITUTE函数

078-SUBSTITUTE替换文字
函数版的替换工具

SUBSTITUTE(原始文本, 要替换的文本, 新文本, [替换第几个])

3.5 ASC函数 和 WIDECHAR函数

079-ASC函数全角转半角

文本函数四：SUBSTITUTE、ASC、WIDECHAR、UPPER、LOWER、PROPER函数

ASC函数：将全角字符（双字符，如中文）转换为半角字符（单字符）
语法：ASC(text)，参数Text 为要进行半角转换的文本。参数可以是文本也可以是对单元格的引用。
说明：若不包含全角字母则保持不变。

WIDECHAR函数：将半角字符（单字符）转换为全角字符（双字符，如中文）。
语法：WIDECHAR(text)，参数text指要转换为全角字符的文本。参数可以是文本也可以是对单元格的引用。
说明：若要转换的原文本中不包含任何半角字符，则原文本保持不变。

3.5 TEXTJOIN函数 和 连字符&

080-TEXTJOIN链接文本
TEXTJOIN函数是文本连接函数，使用分隔符连接列表或文本字符串区域。

语法：

=TEXTJOIN(分隔符, 忽略空白单元格, 字符串1, 字符串2, 字符串3, ……)等价于："字符串1"&"字符串2"&"字符串3"……

分隔符 ：
文本字符串，或者为空，或用双引号引起来的一个或多个字符，或对有效文本字符串的引用。如果提供一个数字，则将被视为文本。

忽略空白单元格 ：
如果为 TRUE，则忽略空白单元格，如果是False，则不忽略空值。

字符串1… ：
为 1 到 253 个要联接的文本项。这些文本项可以是文本字符串或字符串数组，如单元格区域。

4. 日期和时间函数

4.1 DATE函数（计算精准日期）

081-DATE函数生成日期

EXCEL中日期的第一天：1900年1月1日，对应的数值为1；
最大的一天：9999年12月31日，对应的数值为2958465.

=DATE( year, month, day )

DATE函数不会得到非法的日期。
因此可以先用 YEAR、MONTH、DAY函数提取出相应的年份、月份、日，并进行计算之后，再结合DATE函数推算精准日期。

Excel Date 功能——晓阳
year – 1到4位数字之间的数字表示年份。
如果year在0到1900之间，Excel会自动将1900添加到Year中。
如果年份在1900到9999之间，则该值将用作年份值。

month -数字表示月份值。 （通常从1到12）
如果month大于12，Excel会将月份数添加到指定年份的第一个月。 例如，DATE（2018,14,2）将返回日期2/2/2019。
如果month小于1，则Excel将从指定年份的第一个月减去（month的绝对值加1）。 例如，DATE（2018，-4,15）将返回日期8/15/2017。

day -数字表示日期值。 （通常从1到31）
如果day大于指定月份中的天数，则Excel会将该天数添加到该月的第一天。 例如，DATE（2018, 1, 35）将返回日期2/4/2018。
如果day小于1，Excel将从指定月份的第一天减去（day的绝对值加1）。 例如，DATE（2018, 9, -10）将返回日期8/21/2018。

# 从身份证中截取日期数字，再转换为日期。
=DATE(MID(B2,7,4), MID(B2,11,2), MID(B2,12,2))

4.2 YEAR、MONTH、DAY函数

082-YEAR.MONTH.DAY函数

# 提取日期中的年份、月份、日
=YEAR(你要查的日期) 
=MONTH(你要查的日期)
=DAY(你要查的日期)# 如果输入的日期本身有其它运算，则会先进行该日期的计算，YEAR、MONTH、DAY函数再提取新日期的相应元素。

默认情况下，Excel计算日期时，不会包括起始日期的那一天，但会包含终止日期的那一天。

起始日期+n，代表将“起始日期的下一天”作为第一天，往后推n天，将第n天作为终止日期。
起始日期-n，代表将“起始日期的前一天”作为第一天，往前推n天，将第n天作为终止日期。

如果起始日期中只到年份，且没有设置月-日，那么就会将该年的第一天作为起始日期的那一天。
如果起始日期中只到月份，且没有设置日，那么就会将该月的第一天作为起始日期的那一天。
Excel计算终止日期时，都不会包括这些起始日期的那一天，只会包含终止日期的那一天。

例子中是实现月份的增加（先学习思路【先拆后组】，再谈具体实现）：

=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))
/* 将年份、月份、日先提取出来，将月份增加一定值后，再组合成新的日期，
并由DATE函数返回其代表的合法的日期。也代表：将合同日期的下一天，作为第一天，向后延一定的月份。
但这样不符合实际要求。*//* 而在实际中，是将合同日期作为第一天，向后延一定的月份。
月份还是一样，先提取出来，再加上结款周期；但最后的结款日期中的天数要减 1 . 
最后再由DATE函数返回其代表的合法的日期。*/
=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)-1)

4.3 DATEDIF函数（计算精准日期间隔）

083-DATEDIF函数计算工龄
Excel的DATEDIF函数及其用法实例——求日期之间的间隔——小白修炼晋级中

DATEDIF函数作用：计算两个日期间的天数、月数或者年数。

语法：DATEDIF（Start_Date,End_Date,Unit）
即：DATEDIF(开始日期，结束日期，信息类型)

参数说明：
Start_Date：必需，为一个日期，第一个日期或起始日期；
End_Date：必需，为一个日期，最后一个日期或结束（终止）日期，不小于Start_Date；
Unit：必需，为所需信息的返回类型，如下表所示。

后面的三种Unit参数，代表的就是在最后一个周期内的时间。
假如两个日期之间的间隔为2年3个月8天，
“YM”将忽略年数差，只返回3个月；
“YD”将忽略年数差，返回3个月8天所代表的天数；
“MD”将忽略年数差和月份差，只返回8天；

4.3.1 计算间隔天数

如果DATEDIF函数的第三个参数为“D”，那么就是计算两个日期之间的间隔天数。
同样，默认情况下，Excel计算两日期之间的间隔天数时，也不会包括起始日期的那一天，但会包含终止日期的那一天。

在不使用DATEDIF函数时：终止日期 - 起始日期
代表将终止日期的那一天作为第一天，一直前推到起始日期的后一天（不包括起始日期的那一天），所走过的天数。

如果起始或终止日期中只到月份，且没有设置日，那么就会将该月的第一天作为起始或终止日期的那一天。
Excel计算日期间隔时，不会包括起始日期的那一天，只会包含终止日期的那一天。

使用DATEDIF函数且第三个参数为“D”时，规则同上。

4.3.2 计算间隔年份数

如果DATEDIF函数的第三个参数为“Y”，那么就是计算两个日期之间的间隔年份数。
默认情况下，Excel计算两日期之间的间隔年份数时，也不会包括起始日期的那一年，但会包含终止日期的那一年。

但在每年中，都会包含起始日期中的月和日代表的那一天。
因此，只有终止日期中的月和日，不小于 起始日期中的月和日，在终止日期所在的年份中，才算满了一年。

在实际应用中，如果某公司的规定是：
当入职日期为2008年7月5日，在2021年7月4日下午正常打卡下班（之后离职），也算是满了13年工龄。
那么此时，可以在离职日期后面加1，再使用DATEDIF函数计算间隔年份，这样EXCEL计算出来的结果就符合该公司规定了。

4.3.3 计算间隔月份数

如果DATEDIF函数的第三个参数为“M”，那么就是计算两个日期之间的间隔月份数。

默认情况下，Excel计算两日期之间的间隔月份数时，也不会包括起始日期的那一月，但会包含终止日期的那一月。

但在每月中，都会包含起始日期中的日代表的那一天。
因此，只有终止日期中的日，不小于 起始日期中的日，在终止日期所在的月份中，才算满了一月。

4.4 星期运算函数

4.4.1 WEEKNUM函数

084-与星期有关的函数

核心功能：返回日期在本年度中属于第几周（1-54之间的数值）。

=WEEKNUM(serial_number, [return_type])

serial_number：必需，表示要计算周数的日期。
return_type：可选，表示一周的起始日类型。
默认值为1，表示周日为一周的开始；
2表示周一为一周的开始。

因此，要先明确一周的第一天从哪天开始，从而决定某年的第一周包含哪些日期；之后本年度的每一周，所包含的日期都会受到影响。

以2017年的为例：

每周从周日开始时：

每周从周一开始时：

以2025年的为例：

每周从周日开始时：

每周从周一开始时：

4.4.2 WEEKDAY函数

084-与星期有关的函数

核心功能是返回代表一周中第几天的值。其返回结果是一个介于 1 - 7 之间的整数。

语法结构为：
=WEEKDAY(serial_number, [return_type])
=WEEKDAY(日期, 类型代码)

serial_number：
这个参数是一个日期，Excel会将其转换为一个序列号。
例如，如果你输入"2023-10-01"，Excel会将其转换为44500，这是一个内部的日期序列号。

return_type（可选）：
这个参数决定了函数返回值的类型。默认值为1.
1：返回1（星期日）到7（星期六）
2：返回1（星期一）到7（星期日）————常用2
3：返回0（星期一）到6（星期日）

【每周从哪一天开始，影响的只是某个日期在整年中所处的周数。这只是后来才出现的计数方式。】
即使将周日当作每周的开始，也可以在WEEKDAY函数中，使用类型2.
因为WEEKDAY函数只是返回当前日期是星期几，不管每周是从哪一天开始，都不会影响其对应的星期数。

应用：
实现考勤表中日期的自动标记，如将周末全部标红。

5. 统计函数

（1）带“IF(S)”的统计函数的参数，都是范围range在前，条件criteria在后面。

（2）而且都要注意一个问题：
EXCEL只能识别最长15位的数字。
在日常应用中，身份证号，银行卡号等超15位的数字，都应该使用文本形式进行存储。

但即使已经使用了文本形式存储这些信息，EXCEL在对其进行比较或者判断时，仍然只会比较前15位。
只要两个字符串，前15位一样，EXCEL就会认为它们相等。

因此，在使用COUNTIF、COUNTIFS、SUMIF、SUMIFS函数时，如果这些字符串出现在它们的条件参数criteria中，就需要在这些长字符串的后面，连接&一个 “ * ” 号，使得这些长字符串仍然严格保持文本形式，参与比较和判断。

只能使用 “ * ” 号，不能使用其它符号，也不能省略（与“VLOOKUP函数常见错误排查” 中的情况不同）。
因为这里的 “ * ” 号相当于通配符，代表除了其前面的15位字符之外，后面的所有字符也要参与比较。

（3）COUNTIF、COUNTIFS、SUMIF、SUMIFS函数也经常和 1.4 INDIRECT函数一起使用，从而实现跨表统计。

097-VLOOKUP函数跨多表抓取数据
解释见 1.1.6 VLOOKUP函数 与 INDIRECT函数（跨多表抓取数据）

5.1 COUNTIF函数

085-COUNTIF与COUNTIFS函数
excel里面的countif函数怎么用—— PingCode

功能：条件计数，计算范围内符合特定条件的单元格数量。

语法：=COUNTIF(range, criteria)

range：要应用条件的单元格范围。
criteria：用于定义条件的标准，可以是数字、表达式、文本或函数。
例如，COUNTIF(A1:A10, “>=10”)会计算A1到A10范围内大于或等于10的单元格数量。

5.2 COUNTIFS函数

COUNTIFS函数的强大之处就在于它可以处理多个条件。

COUNTIFS函数的参数包括一个或多个条件范围和相应的条件，每个条件范围与条件成对出现。

语法：=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

criteria_range1：
必需。 在其中计算关联条件的第一个区域。

criteria1 ：
必需。 条件的形式为数字、表达式、单元格引用或文本，它定义了criteria_range1中要计数的单元格需要满足的条件。

criteria_range2, criteria2, … ：
可选。 为附加的区域及其关联条件。 最多允许 127 个区域/条件对。

每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。
但这些区域无需彼此相邻。

5.3 SUMIF函数

086-SUMIF与SUMINFS函数
excel表格中sumif函数怎么用——PingCode

=SUMIF(range, criteria, [sum_range])

range：
这是你要应用条件的单元格区域。可以是数字、文本或日期。

criteria：
这是你要应用的条件。可以是一个数字、表达式、单元格引用或文本。

sum_range：
这是实际要求和的单元格范围。如果省略，该函数将对range参数中满足条件的单元格进行求和。

5.4 SUMIFS函数

086-SUMIF与SUMINFS函数
在excel中怎么用sumifs函数——PingCode

注意参数顺序，与SUMIF的不同。

语法是：
=SUMIFS(sum_range, criteria_range1, criteria1 [criteria_range2, criteria2], ...)

sum_range：是需要求和的单元格区域；
criteria_range1：是第一个条件的范围；
criteria1：是第一个条件。

后面的criteria_range2和criteria2是可选的第二个条件及其范围。

5.5 SUMPRODUCT函数

sumproduct在excel中怎么用—— PingCode智库

SUMPRODUCT函数主要用于数组间的乘积求和。

其基本语法为：
=SUMPRODUCT(array1, [array2], [array3], ...)

array1, array2, array3等是需要进行乘积运算的数组。
array1，代表第一个数组或范围，是必需的。
array2, array3，…均可选，代表其他数组或范围。这些数组的大小必须与array1相同。

SUMPRODUCT函数首先对对应位置的数组元素进行相乘，然后对所有乘积结果进行求和。

5.6 SUBTOTAL函数

5.6.1 创建动态图表

094-综合案例：SUBTOTAL动态报表

语法: =SUBTOTAL(function_num,ref1,ref2, ...)

Function_num：
为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。

ref：
代表需要统计的范围。

代码为9的SUM函数 和 代码为109的SUM的函数的相同点和不同点：

5.6.2 创建连续编号

095-SUBTOTAL实战-创建连续编号

注意：SUBTOTAL函数本身的功能是统计，即分类和汇总。
使用SUBTOTAL函数时，表中的最后一行会被EXCEL误认为统计项，所以不能对使用SUBTOTAL函数的那一列进行筛选。

5.7 COUNT函数

excel表格怎么用count函数——PingCode智库

=COUNT(value1, [value2], ...)

主要功能：计算指定范围内，有数值的单元格的个数。
（即不为空，且内容为数值类型的，单元格的个数。）

5.8 COUNTA函数

excel中的counta函数怎么用——PingCode智库

=COUNTA(value1, [value2], ...)

主要功能：统计所有非空单元格的数量。
会将包含空格字符的单元格视为非空单元格。

COUNTA每一个不为空的单元格都包含。

5.9 随机数

EXCEL怎么随机已经有的数据——PingCode智库

5.9.1 RAND函数

excel rand函数怎么做——PingCode智库

语法： =RAND()

RAND函数返回一个0到1之间的随机小数。
不需要任何参数，直接在单元格中输入即可生成一个随机数。

5.9.2 RANDBETWEEN函数

111-自制带照片抽奖工具
excel中randbetween怎么用——PingCode智库

语法为：RANDBETWEEN（bottom, top）

bottom是生成的随机数的下限，top是生成的随机数的上限。
例如，=RANDBETWEEN（1, 100）会返回一个1到100之间的随机整数。

按下F9键，其结果会自动刷新。

6. 数学和三角函数

6.1 数值舍入函数

087-ROUND函数四舍五入

6.1.1 ROUND函数—四舍五入

=ROUND(数字，小数点后保留几位) 

6.1.2 ROUNDUP函数—直接进位

=ROUNDUP(数字，小数点后保留几位) 

6.1.3 ROUNDDOWN函数—直接舍去

=ROUNDDOWN(数字，小数点后保留几位)

⭐

012-文本与数值格式
013-数字加单位-自定义数字格式
014-强大的选择性粘贴

025-分类汇总统计数据
026-多层级的分类汇总

027-强大的定位工具
033-利用定位快速输入公式

可以用113-认识PowerQuery工具的数据进行验证，快速整理数据：

选中D列，取消合并单元格→
定位到D列中的空值→
在第一个空值D3处输入公式=D2→
按下Ctrl+Enter键

029-设置数据有效性工具
030-制作下拉列表

102-认识名称工具
103-制作二级下拉菜单 —— INDIRECT函数
105-动态抓取数据生成折线图
108-函数抓取图片（EXCEL版）

对于固定的区域，用公式定义名称之前，要把引用的单元格全部都改为绝对引用。

107-让文本公式重新运算

对于可变动的区域，不需要将引用的单元格全部都改为绝对引用。

evaluate是宏表函数，不能在EXCEL中直接使用。需要定义成名称才能在EXCEL中使用。

045-认识数据透视表
049-同时显示多种统计维度
050-超便捷的按日期统计数据
054-透视表布局中的细节设置

更新数据透视表的两种方式：
058-利用表格更新透视表数据
106-OFFSET函数与数据透视表

071-创建员工信息查询模板
101-利用滚动条制作信息查询表
108-函数抓取图片（EXCEL版）
110-切片器切换图片

图表

021-用数据条增加数据可读性
028-用分组工具让报表更简洁

040-利用统计数据自动生成月度报表
041-用超链接创建目录
O42-超链接函数HYPERLINK
043-利用图片创建超链接按钮
044-图片对齐-为多个按钮快速布局

062-创建数据透视图
063-利用切片器创建动态图表

067-自定义切片器样式
068-自定义配色方案

094-综合案例：SUBTOTAL动态报表

105-动态抓取数据生成折线图

110-切片器切换图片

112-条件格式创建甘特图

其它相关内容：
035-相对引用与绝对引用
084-与星期有关的函数
088-单元格混合引用
089-条件格式与公式-整行标记数据