Excel公式进阶教程
系列教程
内容介绍
本文将介绍编辑公式，学习函数的一般方法，介绍几个十分常用的查找函数，并且介绍数组公式的使用
公式编辑器的使用
Excel中函数很多，公式编辑器里将会有函数的帮助信息，所以会使用公式编辑器是学习进阶教程的第一步，在下方的公式选项卡中，点击最左边的“插入函数”
弹出来插入函数的对话框，这里面包含所有Excel的函数，可以通过搜索来找到自己想要的函数，还可以通过选择类别来筛选函数，当选定某一个函数时，还会在下方说明函数的具体功能和参数信息
在列表中选定了对应的函数后，点击确定或直接双击函数即可进入公式的编辑模式（本例就选择了ABS函数）
可以看到这里可以逐个输入函数的参数，这个函数是返回输入的数的绝对值，在Number参数中输入 \(-1\) ，如下图所示，计算结果 \(1\) 动态地展现了出来
常用的查找相关函数
INDEX函数
INDEX函数是一个非常重要的函数，它有三个参数，第一个参数是Array，是一个单元格区域(或数组常量)，第二个参数是Row_num表示区域内行的序号，同样第三个参数Column_num表示区域内列的序号
比如有如下Excel表：
| A | B | |
| 1 | 水果编号 | 水果名称 |
| 2 | 1001 | 苹果 |
| 3 | 1002 | 梨 |
| 4 | 1003 | 香蕉 |
| 5 | 1004 | 橘子 |
现在在其它空单元格中输入INDEX函数，Array为A2:B5，Row_num和Column_num为(1,1), (2,1), (4,2)，那么结果分别为“1001”，“1002”，“橘子”，INDEX函数其实单元格引用的另一种方式，它一般与其它函数配合使用
MATCH函数
MATCH就通常与INDEX函数配合使用，MATCH函数能返回查找的结果所在的行号(或列号)，有三个参数，第一个参数Lookup_value指要查找的值，第二个参数Lookup_array指要查找的单元格区域，第三个参数Match_type为匹配方式，可以不填，有默认值0，-1表示将查找小于或等于Lookup_value的最大值，而1表示将查找大于或等于Lookup_value的最小值，0表示精确匹配，如果查不到将返回#N/A的错误
这样MATCH函数就能与INDEX函数配合使用了
还是上面那张表：
| A | B | C | D | |
| 1 | 水果编号 | 水果名称 | 水果编号 | 1001 |
| 1 | 1001 | 苹果 | 水果名称 | 苹果 |
| 2 | 1002 | 梨 | ||
| 3 | 1003 | 香蕉 | ||
| 4 | 1004 | 橘子 |
希望通过输入水果编号得到相应的水果名称，此时就要在D2中输入如下公式：
=INDEX(A2:B5,MATCH(D1,A2:A5,0),2)
注：一般情况下，单元格区域使用绝对引用，所以修改一下公式：
=INDEX($A$2:$B$5,MATCH(D1,$A$2:$A$5,0),2)
LOOKUP函数
之前的例子中使用INDEX函数与MATCH函数实现了查找功能，LOOKUP函数也能完成这项任务，使用LOOKUP函数不需要其它函数嵌套，其参数也只有三个，公式会更加简介
LOOKUP函数的第一个参数Lookup_value为要查找的值，第二个参数Lookup_vector为查找的目标区域，可以是一行或者一列，第三个参数Result_vector为所要显示的结果区域，同样是一行或一列，这样之前的公式可用如下公式代替：
=LOOKUP(D1,$A$1:$A$4,$B$1:$B$4)
数组公式
Excel中的数组公式是对普通公式的强化，数组公式非常强大，但是平均的运算时间也会增长，先来看看数组公式与普通公式的一些主要区别：
| 普通公式 | 数组公式 | |
|---|---|---|
| 基本运算 | 单个值之间的运算 | 数组之间的运算 |
| 函数参数 | 参数为数组时才能使用数组 | 参数为单个值时也可使用数组 |
| 返回值 | 返回值为单个值 | 返回值为单个数组，即可以返回多个值 |
如何输入数组公式
数组公式输入时与普通公式相同，但在最后完成编辑时需要使用组合键Ctrl+Alt+Enter，而不是普通公式编辑完成时仅用Enter键，选中含有数组公示的单元格，可以看到上方编辑栏上公式被加上了一对大括号{}，但是这对大括号不能在输入公式的时候直接加上
数组公式的算术运算
举一个实际的例子，比如有下表：
| A | B | C | |
| 1 | 单价 | 数量 | 总价 |
| 2 | 10 | 100 | 1000 |
| 3 | 20 | 200 | 4000 |
| 4 | 30 | 300 | 9000 |
| 5 | 40 | 400 | 16000 |
| 6 | 30000 |
要求所有总价的和，使用普通公式需要添加一列“总价”，即使有时候我们并不想要这一列，这样只能将其隐藏，而使用数组公式，可以完全省去这一列，公式如下：
=SUM(A2:A5*B2:B5)
使用普通公式，上述公式会返回错误，而使用数组公式，上述公式会返回正确结果
两个数组之间的运算，Excel会逐个将对应位置的值进行运算，如果两个数组大小不一致，Excel会进行自动填充
如果知道矩阵的运算法则，会发现数组的加减法相当于矩阵的加减法，数组的乘除法相当于矩阵的点乘和点除，至于对矩阵的另一些数学操作，Excel使用函数进行了实现
数组公式的多个返回值
数组公式最终返回一个数组，如果最终数组的大小不是 \(1 \times 1\) ，在输入前要选择好单元格区域，然后再输入公式，最后使用组合键，如果选择的单元格区域大小小于返回的数组大小，那么数组将被截断
举例：
| A | B | |
| 1 | 100 | FALSE |
| 2 | 90 | FALSE |
| 3 | 80 | TRUE |
| 4 | 70 | FALSE |
选中B1:B4后输入公式=$A$1:$A$4=80就能得到如上结果，如果选中B1:B3后输入同样公式，结果如下：
| A | B | |
| 1 | 100 | FALSE |
| 2 | 90 | FALSE |
| 3 | 80 | TRUE |
| 4 | 70 |
删除数组时同样要选择与返回数组区域大小相同或更大的区域进行删除，否则会提示“无法更改部分数组”
数组公式的排序
例如有一列数进行升序和降序的排列：
| A | B | C | |
| 1 | 原始顺序 | 升序排列 | 降序排列 |
| 2 | 4 | 1 | 4 |
| 3 | 2 | 2 | 3 |
| 4 | 3 | 3 | 2 |
| 5 | 1 | 4 | 1 |
如果使用普通公式，在B2中输入：
=SMALL($A$2:$A$5,ROW()-ROW($A$2)+1)
接着使用自动填充，然后在C2中输入：
=LARGE($A$2:$A$5,ROW()-ROW($A$2)+1)
最后再使用自动填充
如果使用数组公式，分别选中B2:B5和C2:C5，分别输入公式：
=SMALL($A$2:$A$5,ROW($A$2:$A$5)-ROW($A$2)+1)
=LARGE($A$2:$A$5,ROW($A$2:$A$5)-ROW($A$2)+1)
可以发现，当数组与单个数进行运算时，数组中的每一个元素分别与之进行运算
