公式案例合集
INDIRECT 通过字符串或变量引用单元格和区域
INDIRECT(ref_text, [a1])
释义：返回由文本字符串指定的引用，可以是单元格，也可以是区域。
参数说明：
- Ref_text，必需。对包含 A1 样式及 R1C1 样式的引用、定义为引用的名称或对单元格的引用作为文本字符串的单元格的引用。如果
ref_text不是有效的单元格引用, 则间接返回#REF!；如果ref_text引用另一个工作簿（外部引用），则必须打开另一个工作簿。如果源工作簿未打开, 则间接返回#REF!。注意：Excel Web App 中不支持外部引用；如果ref_text引用的单元格区域超出 1048576 的行限制或列限制 16384（XFD）, 则间接返回#REF!错误。此行为不同于早于 Microsoft Office Excel 2007 的 Excel 版本, 这将忽略超过的限制并返回值。 - A1，可选。一个逻辑值，用于指定包含在单元格
ref_text中的引用的类型。如果 a1 为 TRUE 或省略，ref_text被解释为 A1-样式的引用；如果 a1 为 FALSE，则将ref_text解释为 R1C1 样式的引用。
使用案例：
- 可通过变量来构造表名，设 A3 值为 1，C3 值为 sheet，要返回名称
sheet1表中E3:E200区域的引用：=INDIRECT(C3&A3"!E3:E200")；返回单元格：=INDIRECT(C3&A3"!B1")
ISNUMBER 判断单元格内容是否为数字
ISNUMBER()
释义：如果目标单元格为数值则返回 TRUE，否则 FALSE。
使用案例：
- 结合 IF 函数，可使用公式判断单元格值：
=IF(ISNUMBER(A1),"是","否") - 若要判定文本型数值，需要利用 VALUE() 函数对内容进行数值转换，如果转换成功说明确实为数值；如果出错则说明不是数值。上述公式改为：
=IF(ISERROR(VALUE(A1)),"否","是")
LOOKUP 函数中 0/ 含义解读
| A | B | C | D | H | I |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | 编号 | 姓名 | 销量 | 姓名 | 销量 |
| 3 | 1 | 王东 | 66 | 王东 | ？ |
| 4 | 2 | 张三 | 56 | ||
| 5 | 3 | 李四 | 67 | ||
| 6 | 4 | 王五 | 56 | ||
| 7 | 5 | 二三 | 33 | ||
| 8 | 6 | 西西 | 57 | ||
| 9 | 7 | 南北 | 20 |
查询王东的销量，在 I3 输入公式：
1 | LOOKUP(1,0/(B3:B9=H3),C3:C9) |
公式解读
B3:B9=H3 的运算结果
如果
A=B，会返回结果 TRUE，TRUE 在运算中相当于数字 1如果
A<>B，会返回结果 FALSE，FALSE 在运算中相当于数字 0
所以：B3:B9=H3 的运算结果是有 TRUE 和 FALSE 构成的一组值，结果如下图：
| A | B | C | D | H | I | J | K |
|---|---|---|---|---|---|---|---|
| 1 | |||||||
| 2 | 编号 | 姓名 | 销量 | 姓名 | 销量 | (B3:B9=H3)的运算结果 | 0/(B3:B9=H3) |
| 3 | 1 | 王东 | 66 | 王东 | ？ | TRUE | 0 |
| 4 | 2 | 张三 | 56 | FALSE | #DIV/0! | ||
| 5 | 3 | 李四 | 67 | FALSE | #DIV/0! | ||
| 6 | 4 | 王五 | 56 | FALSE | #DIV/0! | ||
| 7 | 5 | 二三 | 33 | FALSE | #DIV/0! | ||
| 8 | 6 | 西西 | 57 | FALSE | #DIV/0! | ||
| 9 | 7 | 南北 | 20 | FALSE | #DIV/0! |
0/(B3:B9=H3) 的结果如上图。
提取所需值原理
LOOKUP 函数查找时可以忽略错误值且，这样一组数值忽略后只剩下一个值 0。
LOOKUP 函数当查找的值不存在时，按照小于此值的最大值进行匹配。故设置查找值为 1，从而实现查询的目的。
备注：
0/的目的就是把符合条件的值变为 0，不符合条件的变为错误，利用 LOOKUP 函数的特征查找到符合条件的值。
多条件查询
查询销售员在相应地区的销售额。
方法：
在目标单元格中输入公式：=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),C3:C9),"")
释义：
1、原理和单条件查询是一样的。
2、TRUE*TRUE=1，TRUE*FALSE=0
| A | B | C | D | E | F | H | I | J | J | K |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ||||||||||
| 2 | 编号 | 姓名 | 销量 | 性别 | 地区 | 姓名 | 地区 | 销量 | (B3:B9=H3)运算结果 | (E3:E9=I3)运算结果 |
| 3 | 1 | 王东 | 66 | 男 | 北京 | 王东 | 北京 | ？ | TRUE | TRUE |
| 4 | 2 | 张三 | 56 | 男 | 上海 | FALSE | FALSE | |||
| 5 | 3 | 李四 | 67 | 女 | 苏州 | FALSE | FALSE | |||
| 6 | 4 | 王五 | 56 | 女 | 上海 | FALSE | FALSE | |||
| 7 | 5 | 二三 | 33 | 男 | 天津 | FALSE | FALSE | |||
| 8 | 6 | 西西 | 57 | 女 | 上海 | FALSE | FALSE | |||
| 9 | 7 | 南北 | 20 | 男 | 上海 | FALSE | FALSE |
MATCH
运算方式
这个函数有三个参数：第一个参数是查找对象，第二参数指定查找的范围或是数组，第三参数为查找的匹配方式。
第三参数有三个选项：0、1、-1，分别表示精确匹配、升序查找、降序查找模式。
例 1：以下公式返回 2
1 | =MATCH(“A”,{“C”,”A”,”B”,”A”,”D”},0) |
第三参数使用 0，表示在第 2 个参数的数组中精确字母 A 第一次出现的位置为 2，不考虑第 2 次出现位置，且第 2 个参数无需排序。
例 2：以下公式返回 3
1 | =MATCH(6,{1,3,5,7},1) |
第三参数使用 1，（也可省略），其中第 2 个参数的数组要求按升序排列，并查找出小于或等于 6 的最大值（即数组中的 5）在第 3 个元素位置。
例 3：以下公式返回 2。
1 | =MATCH(8,{11,9,6,5,3,1},-1) |
其中第 2 个参数的数组要求按降序排列，并查找出大于或等于 8 的最小值（即数组中的 9）在第 2 个元素位置。
MATCH 函数与 INDEX
函数逆向查询
由于实际应用中，只要求返回位置的问题不多，好像 MATCH 函数一时派不上用场了。其实这个函数更多的时候，是与其他引用类函数组合应用，最典型的使用是与 INDEX 函数组合，能够完成类似 VLOOKUP 函数和 HLOOKUP 函数的查找功能，并且可以实现逆向查询，即从左向右或是从下向上查询。
如下图所示，需要根据 E 列的姓名在 A 列查询对应的部门。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 部门 | 姓名 | 部门 | 姓名 | |
| 2 | 财务部 | 小兰 | ？ | 小美 | |
| 3 | 销售部 | 小翠 | |||
| 4 | 采购部 | 小美 | |||
| 5 | 人资部 | 小花 | |||
| 6 | 安监部 | 小青 | |||
| 7 | 质保部 | 小丽 | |||
| 8 | 仓储部 | 小芳 |
这种逆向查询的数据可以使用 LOOKUP 函数，用 INDEX+MATCH 函数实现的方法。D2 单元格输入以下公式：
1 | =INDEX(A:A,MATCH(E2,B:B,)) |
返回查询结果为采购部。
INDEX 函数是常用的引用类函数之一，可以在一个区域引用或数组范围中，根据指定的行号和列号来返回一个值。
MATCH(E2,B:B,) 部分，第三参数简写，表示使用 0，即精确匹配方式查询 E2 单元格姓名 小美 在 B 列的位置，结果为 4。计算结果用作 INDEX 函数的参数，INDEX 函数再根据指定的行号返回 A 列中对应的值。
使用 INDEX 函数和 MATCH 函数的组合应用来查询数据，公式看似相对复杂一些，但在实际应用中，更加灵活多变。
1、查找首次出现的位置
除了使用特定的值作为查询参数，也可以使用逻辑值进行查询。以下图为例，是某公司的销售数据。需要查询首次超过平均销售额的月份。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 月份 | 销售额 | 首次超过平均销售额的月份 | |
| 2 | 一月 | 855 | ？ | |
| 3 | 二月 | 827 | ||
| 4 | 三月 | 893 | ||
| 5 | 四月 | 899 | ||
| 6 | 五月 | 895 | ||
| 7 | 六月 | 921 | ||
| 8 | 七月 | 897 | ||
| 8 | 八月 | 932 | ||
| 8 | 九月 | 943 | ||
| 8 | 十月 | 910 | ||
| 8 | 十一月 | 821 | ||
| 8 | 十二月 | 951 |
D2 单元格使用以下数组公式：
1 | =INDEX(A2:A13,MATCH(TRUE,B2:B13>AVERAGE(B2:B13),)) |
数组公式要按 Shift+Ctrl+Enter 组合键
计算过程：
AVERAGE(B2:B13)部分，用来计算出B2:B13单元格的平均值 895.33。B2:B13>AVERAGE(B2:B13)部分，用B2:B13与平均值分别作比较，得到由逻辑值 TRUE 或是 FALSE 组成的内存数组：{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}- MATCH 函数第一参数使用逻辑值 TRUE，使用精确匹配方式查询 TRUE 在数组中第一次出现的位置，结果为 4。本例中的第一参数也可以写成
1=1，返回逻辑值 TRUE，与直接使用 TRUE 效果相同。 - MATCH 函数的计算结果用作 INDEX 函数的参数，INDEX 函数再根据指定的行号返回 A 列中对应的月份。
2、查找最后一次出现的位置
除了查询首次出现的位置，MATCH 函数还可以查询最后一次出现的位置。以下图为例，需要查询最后次超过平均销售额的月份。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 月份 | 销售额 | 最后一次超过平均销售额的月份 | |
| 2 | 一月 | 855 | ？ | |
| 3 | 二月 | 827 | ||
| 4 | 三月 | 893 | ||
| 5 | 四月 | 899 | ||
| 6 | 五月 | 895 | ||
| 7 | 六月 | 921 | ||
| 8 | 七月 | 897 | ||
| 9 | 八月 | 932 | ||
| 10 | 九月 | 892 | ||
| 11 | 十月 | 864 | ||
| 12 | 十一月 | 821 | ||
| 13 | 十二月 | 843 |
D2 单元格使用以下数组公式：
1 | =INDEX(A2:A13,MATCH(1,0/(B2:B13>AVERAGE(B2:B13)))) |
数组公式要按 Shift+Ctrl+Enter 组合键
计算过程：
- 先使用 AVERAGE 函数计算出
B2:B13单元格的平均值。 - 再用
B2:B13与平均值分别作比较，得到由逻辑值 TRUE 或是 FALSE 组成的内存数组。用 0 除以这个内存数组，返回以下结果：`