Excel学习笔记：P32-从此让写公式变得超级简单！

---

一、动态阵列

一直以来，Excel的公式都有一个特点，就是一个函数返回一个值。比如我能使用VLOOKUP函数根据学号查询某个同学的姓名。

但是VLOOKUP不能用班级作为查找条件，查找出班级有哪些学生。

不过Excel已经开通了动态阵列功能，它可以用一个公式传回多笔数据，而无需去使用复杂的旧版阵列公式。
现在，我有一个记录业务员销售情况的数据表。

我们先将其转换为表格。设定为表格的好处之一是我们新增或删除资料时，公式的范围也会同步调整，省下手动修改的麻烦。转换好的表格会自动套用带状填色的设计，如果想维持原本表格的外观，可以在选单中移除这些色彩。


配合动态阵列这个新功能，Excel也推出了几个新函数

1.1 UNIQUE函数

UNIQUE函数的功能是去除表格中重复的数据。
示例：我想知道表格中有哪几种产品，操作如下：
输入UNIQUE，并将鼠标移动至产品列的上方边框处，出现黑色箭头后单击。如此返回的就不是一个数值，而是一列清单。这种一次列出多个查询结果的特性被定义为：Spilling(溢出)
溢出具有一些特性：
①它的周围会显示蓝色的边框，方便我们辨识溢出所包含的范围。

②如果想要修改溢出范围内的公式，我们必须回到开头的第一个单元格。其它栏位的公式会显示为灰色，代表我们无法针对它们进行任何的编辑。

③如果公式在溢出时遇到了阻碍，比如我在单元格中随意输入几个文字，那么公式会因为无法溢出而显示错误信息，直到我将这个资料拖动至别处。



④我们在表格中新增数据时，溢出的范围也会自动调整大小

现在我们来进行测试，利用SUMIF函数计算每项商品的销售总金额。SUMIF函数用法如下：

依照SUMIF的语法，我们指定产品栏列为搜寻范围。指定原子笔作为筛选条件。加总范围为销售金额列。求出金额后，再将公式往下复制。

但是有个缺陷便是：当我在表格新增数据时，虽然旁边的动态阵列公式会自动向下溢出，但SUMIF函数仍然得手动拖动的方式来复制公式。

这是因为SUMIF不具备自动溢出功能。如果我们也想SUMIF函数具备此功能，我们可以在公式内加入动态阵列语法。也就是在指定原子笔作为筛选条件时，在原子笔后方加上一个井号 #，使公式能够参照整个溢出范围。

因此，此时SUMIF函数在运算时，就会发挥动态阵列的特性。当我们新增或删除数据，SUMIF都会及时更新总金额。

1.2 Filter函数

Filter函数可以根据我们设定的条件来筛选表格中的数据，函数结构如图：

示例：如果我想找出所有有关原子笔的销售订单。
可以先将原子笔这个关键字放在一个空白单元格，然后输入FILTER函数，并选中相关的表格、数据列等。

输入公式后，按下回车，效果如下：

我们也可以更改筛选内容，此时表格也会跟着动态调节。

然而我们将筛栏清空后，FILTER函数会因为查询不到结果而产生了错误的代码。对此，FILTER提供了第三个参数，可以让我们指定错误信息。


另外，如果你觉得手动输入筛选条件太麻烦，我们可以打开数据标签，来制作一个下拉选单，方便我们输入资料。值得注意的是，在数据来源中，指定原子笔单元格，需要在后面加上#，将参照范围扩展到其它的溢出范围。这样表格进行修改或更新时，下拉选单也能跟着自动跟新。

现在我们就可以根据下拉选单进行查看商品的销售信息了。而且表格中新增商品信息时，下拉选单也会自动更新。

除了一般的条件筛选之外，FILTER也可以做到双重栏位的筛选。
示例：我想从原子笔的交易记录中查找出特定业务员的交易信息，如小美。操作方式为将鼠标放至公式中来，并加上第二个条件。 具体步骤为：首先使用圆括号将第一个条件框起来，然后输入，再输入第二个条件，并用圆括号框起来。这样FILTER就可以找出同时符合这两个条件的交易记录。*

1.3 SORT函数

SORT函数的功能便是将资料进行排序。繁体中文按照笔画多少进行排序，简体中文按照拼音顺序进行排序。

示例：现在我要将商品信息进行排序。操作步骤如下：直接在UNIQUE前面加上SORT即可。

SORT还可以指定排序栏位与排序规则。

示例：我想将数据根据销售金额由高到低进行排序。

操作步骤如下：