2.2 查询函数

本节将用几个具体的实例来讲解查询函数的运用方法，单个函数的含义和用法请读者自行学习或查看本书前作《平衡掌控者——游戏数值战斗设计》。

2.2.1 一对一逆向查询

如果让数值策划来票选Excel中使用率第一的函数，那我相信VLOOKUP函数应该会拔得头筹。但在实际工作中，我们常常会遇到一个尴尬的问题，那就是要查询的第一个参数lookup_value有时不在要查询数据所在列的前列，而是在要查询数据所在列的后列，此时就需要使用逆向查询了，如图2-9所示。

下面给出两种方案，都可以解决这个问题。

第1种方案采用了INDEX+MATCH函数，这应该算是最常规的做法了。我们会用公式求值的例子来给大家展示具体解析步骤。选择B10单元格后，单击“公式求值”按钮（Excel上部“公式”选项卡中的一个功能按钮），这时会弹出如图2-10所示的对话框。

在该对话框中单击“求值”按钮，公式就会执行下画线部分并获得运算结果，如图2-11所示。

在这里可以看到，由于要查询的技能ID编号在$A$2：$A$7单元格区域中的排位与技能名称在$B$2：$B$7单元格区域中的排位是一样的，所以可以先用MATCH函数查询A10单元格的值“入门攻击3级”在单元格区域$B$2：$B$7中的排序值，然后使用INDEX函数和这个排序值在$A$2：$A$7单元格区域中找到对应的技能ID编号，如图2-12所示。

需要注意：在实际操作过程中，一定要注意$A$2：$A$7和$B$2：$B$7这两个单元格区域中的排序值是否一致，不然就成了乱序查询，会出现查询值错位的情况。

下面再看看第2种方案，它采用的是VLOOKUP+IF函数数组化的做法，这种做法理解起来可能比第1种略复杂一些，但可以通过这个案例学习IF函数数组化的知识。这个方案的思路是，用IF函数生成数组，重新生成一个查询区域，该单元格区域内以技能名称为第1列，以技能ID编号为第2列，然后就可以直接使用VLOOKUP函数进行查询了。下面还是使用公式求值功能来进行解析，单击一次“求值”按钮后，如图2-13所示。

此时再次单击“求值”按钮，看看IF函数的数组化（IF函数的数组化是指用IF函数将单元格区域中的数值转化为数组），如图2-14所示。

在这里，我们用到了IF函数的数组化功能。IF函数是条件判断函数，但其实它还有一个强大的功能，那就是可以通过判断条件来生成数组。而VLOOKUP函数本质上查询的也是数组，只是无法进行逆向查询，所以我们用IF函数的数组化实现了数组内数值的重新排序。

下面单独看一下IF（{1，0}，$B$2：$B$7，$A$2：$A$7），将这个公式输入单元格试试。不好意思，估计你得到的是＃VALUE！，因为它产生的是内存数组，不会直接显示。还记得《平衡掌控者——游戏数值战斗设计》一书中介绍的数组公式吗？我们可以按Ctrl+Shift+Enter组合键再输入一次试试，记得要选中单元格区域B13：C18，如图2-15所示。

IF函数中用数组作为第1个参数，这样也会产生一个数组，而产生数组的元素就看第1个参数中的值是1还是0了，1代表True而0代表False。{1，0}的第1个值为1，所以产生数组的第1个值取自$B$2：$B$7，第2个值取自$A$2：$A$7，这样自然就生成了一个数组。

特别说明：公式求值功能非常重要，是我们排查、解析公式的主要手段，所以一定要熟练运用它。另外，一定要选中公式所在的单元格后再单击“公式求值”按钮。

2.2.2 一对多查询

在工作中，一对一查询应该是用得最多的，但有时我们也需要一对多查询，比如若想查询都在哪些地方消耗了绿宝石，这时可以通过A13单元格中的值进行查询，如图2-16所示。

第1种解法用的是常规公式，大家理解起来会方便一些，但在运用中会显得略微复杂。

首先通过辅助列1获得哪些行是符合“绿宝石”这一查询条件的（比如，F2单元格中的公式为IF（C2=$A$13，1，0），F2单元格下面单元格中的公式以此类推），然后通过辅助列2使每一个符合查询条件的行都可以按序号查询，并且序号值在与自己重复的值中排序最靠前（G2单元格中的公式为SUM（F$2：$F2））。序号这一列都是纯数字，其中不包含公式。最终，通过INDEX函数对符合查询条件的行依次定位来获取结果。

第2种解法采用的是数组公式，公式本身较难理解，但整体相对简单，如图2-17所示。

下面来逐步理解这个数组公式，首先其中使用了IF函数的数组化功能，得到了符合条件的数组，如图2-18和图2-19所示。

接下来是非常关键的一步，对于符合条件的TRUE值，我们希望可以将其更改为数值方式来表示（方便排序）。我们对之前数组的值进行了一次判断，将TRUE值变为对应的行号，而将FALSE值变为65536（其实是一个足够大的值，在早期版本的Excel中行号最大值就是2¹⁶，即65536，这里沿用了这个值），如图2-20所示。

下面的几步操作相对好理解一些。我们通过SMALL函数找到这个数组中最小的值，然后用INDEX函数进行定位，就可以找到符合条件的值了，如图2-21和图2-22所示。公式最后的&""是将最终结果转化为文本格式的手段，如无须转化，将其去掉即可。

2.2.3 多对一查询

除了上面提及的一对一查询、一对多查询外，多对一查询也用得比较多（但在游戏数值中多对多查询的用途较少，并且通常可以通过前3种查询的相关公式进行推导来实现，感兴趣的读者可以自己查找相关资料进行学习）。

下面看一个例子，还是上一节的表格，这次我们希望查询战士在进阶3时所需的材料和材料数量。

在第1种解法中，我们用到了辅助列（A列，插入的新列），直接在其中生成新的查询索引，辅助列将“职业”和“进阶”两个条件合二为一，然后在辅助列中查询到符合条件的值“战士3”，进而可以获得战士在进阶3时所需的材料和材料数量，如图2-23所示。

第2种解法用到了数组公式，其与一对一逆向查询的方法颇为相似，这里就不再赘述了，如图2-24所示。

2.2.4 交叉查询

在工作中，某些时候会遇到通过攻击类型和防御类型的匹配来计算伤害系数的情况，这时候就需要用到交叉查询了。比如，通过A10和A12单元格的值来查询A14单元格的值，如图2-25所示。

第1种解法运用了OFFSET+MATCH函数。先用MATCH函数进行行定位和列定位，如图2-26和图2-27所示。

然后运用OFFSET函数，在单元格区域中定位到查询结果，如图2-28所示。

第2种解法运用了INDIRECT+MATCH函数，如图2-29所示。

第2种解法中运用MATCH函数的原理和第1种解法中的是一样的，不同的是接下来的步骤中运用了INDIRECT函数对地址的引用功能。公式求值最后的结果如图2-30所示。

INDIRECT函数对地址的引用用法是R1C1引用样式（将在2.4.1节介绍相关内容）的，这种方式更适合做运算处理，因为列号也可以参与运算。传统的A1引用样式无法运用A+1=B的操作，后续会进行详细的介绍。

2.2.5 区分字母大小写的查询

需要强调一下，如果没有必要，千万不要在设计上做大小写字母区分，因为这会在无形中增加数值策划的工作量（区分字母大小写的工作）。但如果真的遇到了这种情况，也不是没有解决方案的。

来看看下面的例子，其中就需要在实际的查询过程中区分字母大小写，因为A1和a1是两架不同的飞船（其实完全可以避免这种情况，改变命名规则，ID就可以不一样）。如果使用VLOOKUP函数，那么会无法区分字母的大小写，此时就需要巧妙地运用LOOKUP+EXACT组合函数了，如图2-31所示。

在这里，首先运用了EXACT函数的对比功能，而这个功能是区分字母大小写的。然后会得到一个数组，符合条件的值为TRUE，不符合条件的值为FALSE，如图2-32和图2-33所示。

接下来就是比较巧妙的地方了。我们用0作为分子，数组中的值作为分母，来计算新的数组。由于FALSE在运算中对应的是0，所以它的值就变为＃DIV/0！错误值（这是Excel对报错信息的一种提示，＃DIV/0！表示有0作为分母）。而TRUE在运算中对应的是1，所以它的值会变为0/1=0，如图2-34所示。

最后，利用LOOKUP函数查询到最终结果（LOOKUP函数可用于查询数组）。

2.2.6 去除重复值

去除重复值是一个非常普通的操作，本节将介绍使用公式去除重复值，而关于Excel自带的去除重复值功能，读者可以查找相关资料进行学习。在实际工作中，公式解法和Excel的自带功能各有利弊，可以酌情使用。

第1种解法用的是LOOKUP+COUNTIF组合函数，如图2-35所示。

这种解法理解起来略有难度。首先用COUNTIF函数进行一次去除重复值判断，判断目前出现的ID是否已经在列表中出现过。若该ID已经在列表中出现过，则在数组中将其变为1，这样就变相地实现了去除重复值。下面选中C10单元格来进行公式求值，如图2-36和图2-37所示。

然后通过LOOKUP函数查询到结果值。

第2种解法用的是数组公式，其思路与一对多查询的思路类似，如图2-38所示。

首先运用MATCH函数生成了一个数组，然后用IF函数判断数组中的值是否与行号匹配，符合条件的数值等于行号，若不符合条件，则为其赋值4⁸，接着用SMALL函数进行排序，最终用INDEX函数查询到结果值。大家可自行用公式求值功能进行解析。