分类：Excel 2016实用技巧大全

包含212个案例，每个案例都分为疑难场景、解题步骤和知识扩展3部分，其中疑难场景用于描述问题；解题步骤用于阐述解决问题的方法，按步骤先后详细地罗列出来，同时配有操作过程与操作结果的截图，并在图中提供箭头指示操作对象，从而帮助读者快速掌握解题方法；知识扩展用于补充解题方案中部分代码的相关语法、思路，或者提供其他解题方案，使读者对每个步骤了解得更透彻，并可举一反三。

Excel 能否将公式名称化？

能否简化过长的公式，使其便于书写，同时又便于识别？

例如，在图7.93～图7.95所示的案例中，需要在F列根据内销单价和出口单价计算产值，其公式如下：

I1和I2需要根据生产表的产量按出口和内销分别统计产值，两公式如下：

第二个和第三个公式本身比较简短，也比较好理解，因此不需要简化，第一个公式则需要简化。

图7.93 内销单价表

图7.94 外销单价表

图7.95 生产表

解题步骤

将公式转换成名称，或者将公式中的部分表达式转换成名称有利于简化公式及理解公式，本例中计算产值的公式较长，可通过以下步骤转换成名称。

1.选择F2单元格，然后按下组合键＜Ctrl+F3＞，弹出“名称”对话框。

2.单击“新建”按钮，弹出“新建名称”对话框，然后将名称设置为“良品数”，将引用位置设置为公式“=C2-D2”，设置界面如图7.96所示。

3.单击“确定”按钮关闭对话框，然后再次新建一个名称“单价”的对话框，并将引用位置设置为以下公式，设置界面如图7.97所示。

图7.96 定义名称“良品数”

图7.97 定义名称“单价”

4.单击“确定”按钮关闭对话框。返回工作表界面后在F2中输入以下公式：

=良品数*单价

5.双击F2单元格，将公式向下填充到F18，计算结果如图7.98所示。

图7.98 使用名称简化公式

知识扩展

1.通过名称简化后的公式计算结果和未使用名称的公式计算结果一致，但单元格中的公式更简短，而且便于阅读理解。通常情况下制表者对函数的认识深入一些，查看报表者却可能完全不懂函数，公式名称化之后则比较容易理解公式的计算过程。

2.定义名称时，如果名称中存在引用，则要区分相对引用还是绝对引用。

在名称中使用相对引用时，定义名称前选择哪一个单元格就显得格外重要了。本例中选择F2后再定义名称，且名称中的引用都是相对引用，因此该名称只适合在F2单元格中调用。如果选中F2以外的任意单元格后定义名称，F2中的公式都会得到错误结果。

简单而言，要在哪个单元格输入公式，就要先选中哪个单元格，然后再定义名称。如果名称中的引用是绝对引用则例外。

3.在公式中调用名称时，千万不能在名称前后使用双引号。

Excel 能否提取单元格中位于左边的所有数字？

图7.85中的C列包含了数值和单位，现要求在E列提取其数字，忽略单位，是否有办法一次性提取成功？

解题步骤

提取左边的数字有多种方法，每种方法各有所长。使用基础操作可以完成，使用公式也可以完成，其中公式又分多种思路。本例讲述Lookup+Left组合的思路，具体操作步骤如下：

1.选择E2，然后输入以下公式：

2.双击E2的公式，使其自动填充到E11单元格，此时计算结果如图7.86所示。

图7.86 提取每个单元格左边的数字

知识扩展

1.ROW函数用于计算单元格的行号，它的参数必须是单元格。

=ROW（G1）——结果为1，G1单元格的行号为1；

=ROW（C65536）——结果为65536；

=ROW（G1：D3）——当以普通公式的形式输入公式时，它的结果为1；当以数组公式的形式输入公式时，它的结果为{1，2，3}，即选择纵向3个连续的单元格后输入公式，并按＜Ctrl+Shift+Enter＞键结束，若在单个单元格中输入公式只能得到G1的行号1。

由于Excel的计算精度是15位，因此本例采用ROW（$1：$15）作为LEFT函数的参数提取1到15位字符，这是最通用的做法。

2.LEFT函数的功能是提取字符串左边若干位字符，其语法如下：

=LEFT（＂中华人民共和国＂，3）——计算结果为“中华人”，提取左边3位；

=LEFT（A1，ROW（$1：$15）））——由于表达式“ROW（$1：$15）”可以生成1到15的自然数序列，因此此公式可以提取C2单元格的左边1～15位字符。当选择纵向15个连续的单元格后输入公式，并按＜Ctrl+Shift+Enter＞组合键结束时可以产生15个运算结果，效果如图7.87所示。

图7.87 逐一提取左边1～15位字符

3.LOOKUP函数功能相当强大，用法也相当复杂，要完整地介绍 LOOKUP函数的用法需要10页以上。本例只用到数组形式的模糊搜索，因此仅介绍此种情况下的LOOKUP用法。

数组形式、模糊搜索条件下的LOOKUP语法如下：

第一参数是搜索对象，可以是一个字符串、一个数字，也可以是一个单元格。第二参数是一个数组或一个区域（区域的值本质上就是一个数组），LOOKUP 会在第二参数中搜索第一参数的值，如果找不到第一参数的值，那么继续搜索比它小的最大值，找到后返回该值。

使用 lOOKUP 函数时，要求其第二参数的数据源升序排序，否则查找结果可能与预期不一致。

在图7.88中，公式“=LOOKUP（15，A1：A5）”的含义是在A1：A5中查找15，由于A1：A5中刚好有一个15，因此公式结果为15；图7.89中公式“=LOOKUP（40，A1：A5）”的含义是在A1：A5中查找40，由于找不到40，继续查找比40小的最大值，数据35符合条件，因此公式的返回值是35。

图7.88 查找15并返回15

图7.89 查找40，找不到时返回比它小的最大值

4.当使用LOOKUP函数执行模糊搜索（找不到目标值就继续查找比目标小的最大值）时，为了提升公式的通用性，通常会将LOOKUP的查找目标设置为最大值10的15次方（再大就没有必要了，超出了Excel的计算精度），然后在实际搜索过程中会搜索比它小的最大值。下面的例子可以展示LOOKUP搭配LEFT函数执行模糊搜索的过程。

图7.90中展示了LEFT函数从642578中逐一提取1位、2位、3位、4位、5位和6位字符时的运算过程和结果，运算结果包含6个值；图7.91则展示LOOKUP函数从这个数值中查找10^15的过程，由于找不到目标值，因此从数组中提取一个小于目标值的最大值642578作为最终结果。

Left函数的运算结果总是文本形式的，因此要把它们当作数值去参与运算时，需要在右边添加表达式“*1”，从而将文本型数字转换成数值。

图7.90 LEFT函数逐一提取左边N位字符

图7.91 使用LOOKUP从数组中搜索10^15

5.公式“=LOOKUP（10^15，LEFT（C2，ROW（$1：$15））*1）”的运算过程比较复杂，可以通过图7.92来理解公式。

图7.92 图示LOOKUP+LEFT函数的运算过程

Excel 可否逐一罗列两列数据的相同值？

是否可以逐一罗列出两列数据的相同值？当数据源中的值变化时，提取出来的相同值也相应地更新。

以图7.80为例，A列和B列分别是去年和今年的优秀员工姓名，要求在D列罗列出两年都是优秀员工的姓名。

图7.80 去年与今年的优秀员工

解题步骤

判断两列是否存在相同项，使用COUNTIF函数计数即可，然后配合IF函数排除不符合条件的值，具体操作步骤如下。

1.在D1中输入标题“相同项”（见图7.81）。

2.选择D2：D11区域，然后输入以下数组公式：

输入公式后必须按组合键＜Ctrl+Shift+Enter＞结束，只按Enter键无法得到正确结果。

图7.81 提取两列的相同项

知识扩展

1.COUNTIF函数用于计算满足条件的单元格数量，第一参数是区域，第二参数是条件，条件可以是单个的也可以包含多个，当使用单个条件时统计结果也是单个的；当使用多个条件时，统计结果也包含多个，不过必须使用＜Ctrl+Shift+Enter＞组合键输入公式，否则只能取得单个结果。

2.本例中表达式“COUNTIF（B2：B10，A2：A11）”的含义是逐一统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，如果未出现在B2：B10中则返回值为0，否则返回出现次数。表达式的计算结果包含10个值，使用数组公式才能一次性取得这10个值。

3.公式“=IF（COUNTIF（B2：B10，A2：A11）＞0，A2：A11，＂＂）”的含义是如果A2：A11区域中某个单元格的值出现在B2：B10区域，那么就返回该值本身，否则返回空文本。

IF函数在本例中的作用是排除不符合条件的值，使其返回空文本。可以通过以下方式理解公式的运算过程。

首先用COUNTIF函数统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，效果如图7.82所示。

图7.82 统计A2∶C11的值在B2∶B10中的出现次数

图 7.82 中公式的返回值大于 0 时表示该姓名同时出现在去年和今年的区域中，此时用“COUNTIF（B2：B10，A2：A11）＞0”作为IF函数的条件执行判断就可以提取符合条件的姓名，排除其他姓名。

4.本例公式提取出来的姓名未放置在相邻的单元格中，查看时不太方便。改用以下数组公式可以让结果放置在相邻的单元格。

以上公式的使用方法是：在D2单元格输入公式，按组合键＜Ctrl+Shift+Enter＞结束，然后按下填充柄将公式向下填充到D11单元格中（见图7.83）。

图7.83 将两列的相同值放在相邻的单元格

此公式的含义是：如果A2：A11区域中某个单元格的值出现在B2：B10区域中，那么返回其行号，否则返回行号9999。接着使用SMALL函数对这些行号从小到大排序，最后使用INDEX函数从A列提取目标姓名，姓名的行号由SMALL函数生成的升序行号决定。

由于符合条件的姓名都是生成该姓名所在行的行号（本例为2、4、10），不符合条件时则生成9999，因此SMALL函数产生的升序行号其实是2、4、10、9999、9999、9999、9999、9999、9999、9999，如图7.84所示。

图7.84 模拟SMALL函数生成的行号

此时使用INDEX函数从以上行号提取姓名即可，由于A9999没有姓名，因此INDEX会返回数值0，为了将0转换成空白，在INDEX函数的后面添加了表达式“&＂＂”。

以上公式比较复杂，对于未系统学习数组公式的用户而言理解比较困难。不过这属于本案例的补充知识，读者可以忽略此公式，或者加入本书的售后服务群向图书作者提问。

Excel 如何将多种币别的金额统一为人民币汇总？

某公司的客户包含国内客户和海外客户，不同客户下单时采用的币别也不相同。例如图7.77中5家公司使用了人民币、美金、台币和港币4种币别对产品报价，现要求将它们统一转换成人民币，方便后续结算、汇总。如何才能快速统一币别呢？

图7.77 订单表

美金、台币和港币与人民币的汇率都统一按表7.2所示的汇率计算。

表7-2 汇率

解题步骤

本例采用IF函数执行条件判断比较简便，具体操作步骤如下。

1.在F2单元格中输入以下公式：

2.双击 F2 单元格的填充柄，从而将公式自动填充到已用区域的最后一行，效果如图 7.78所示。

图7.78 用人民币统一计算每个客户的订单金额

3.选择F2：F6区域，反复单击功能区的“开始”→“减少小数位数”，直到金额统一显示两位小数。

4.选择F7，然后按组合键＜Alt+=＞，从而快速生成求和公式，此时订单表结果如图7.79所示。

图7.79 按人民币汇总订单金额

知识扩展

1.IF函数是条件判断函数，它的语法如下：

第一参数代表条件，通常是一个表达式，其计算结果为True或False；第二参数代表条件为True时的返回值，第三参数则是条件为False时的返回值。简言之，IF函数的功能是第一参数成立时公式就返回第二参数的值，否则返回第三参数的值。

=IF（A1＞=60，＂及格＂，＂不及格＂）——当A1的值大于等于60时，公式的结果为“及格”，否则结果为“不及格”。

=IF（A1＜0，＂录入有误＂，IF（A1＞=60，＂及格＂，＂不及格＂））——公式表示A1的值小于0时结果为“录入有误”，A1的值大于等于60时结果为“及格”，其他情况下结果为“不及格”。本公式使用了两个IF函数嵌套，从而可以应付三种情况，单个IF函数只能应付两种情况，非此即彼的判断才使用单个IF函数。

在本例中使用了3个IF函数嵌套，它表示E2的值是NTD时用0.197作为汇率，E2的值是USD时用6.34作为汇率，E2的值是HKD时用0.818作为汇率，其余情况下使用1作为汇率。

2.使用IF嵌套时要注意括号的数量必须配对，使用了多少个左括号就必须有多少个右括号。本例公式中3个IF函数嵌套使用，有3个左括号，因此公式必须以3个右括号结尾。

Excel 能否引用每个单元格的第二行数据？

图7.75中A列的省名和市名分别放在一个单元格的两行中，现要求在D列提取出所有市名，能否一次性完成？

图7.75 参赛人员资料表

解题步骤

单元格的第一行和第二行之间存在换行符，这是一个明显的规律，因此可以使用函数查找换行符的位置，然后使用函数提取该位置之后的所有字符，从而取得市名，具体操作步骤如下。

1.在D1中输入标题“市名”。

2.在D2中输录入以下公式：

3.双击D3单元格的填充柄，从而使公式向下填充到D11，公式会自动取得所有市名，效果如图7.76所示。

图7.76 提取市名

知识扩展

1.CHAR（10）代表换行符，FIND 函数是位置查找函数，表达式“FIND（CHAR（10），A2）”的含义是在A2单元格中查找换行符，返回换行位所在位置，如果A2单元格不存在换行符则会返回错误值。FIND函数的语法如下：

第一参数代表要查找的字符，第二参数代表被查找的字符，第三参数用于指定从第几个字符开始查找，它是可选参数，当忽略参数时表示从第一个字符开始查找。

整体而言，Find函数的功能是在第二参数中查找第一参数的值，查找位置由第三参数决定，返回值是第一参数在第二参中出现的位置。

2.MID函数用于从字符中指定位置开始提取指定长度的字符串，它的语法如下：

第一参数是字符串对象，第二参数代表起始位置，第三参数代表长度。如果要用一句话说明MID 函数的功能，就是从第一参数中提取部分字符，该字符的位置与长度分别由第二、第三参数决定。

=MID（＂中华人民共和国＂，3，2）——从第3位开始提取两个字，结果为“人民”；

=MID（＂中华人民共和国＂，1，7）——从第1位开始提取7个字，结果为“中华人民共和国”；

=MID（＂中华人民共和国＂，3，9）——从第3位开始提取9个字，结果为“人民共和国”，

提取长度大于实际的字符数量时会忽略多余的长度。

本例中MID的第二参数由 FIND函数计算而来，因为换行符的位置并不固定，只能用函数才能取得准确位置。

MID 的第三参数原本应该使用函数计算，从而取得市名长度，本例采取了更通用、更简便的方法——由于市名长度不会大于9位，同时又由于MID的第3参数会忽略超出的部分，因此直接采用9（换成99也行），只要大于等于市名的最大长度就可以提取出目标。

3.IF函数的功能是条件判断，符合指定条件时返回一个值，否则返回另一个值。本例中用IF函数判断A列的当前行是否有字符，如果没有则直接返回空文本，从而避免公式得到错误值。

在实际工作中，极少情况下是采用单个函数就能解决问题的，通常需要多个函数嵌套，用函数的计算结果作为另一个函数的参数，从而满足复杂的工作需求。

Excel 能按单元格背景色分类求和吗？

基于某些原因，图 7.71 中的业绩使用多种颜色加以标示，现要求对不同颜色的区域分类汇总，Excel如何才能实现？

图7.71 用颜色标示的业绩表

解题步骤

Excel的所有工作表函数都无法识别颜色，只有早期版本的宏表函数get.cell可以做到，因此本例使用get.cell函数搭配SUMIF函数解题，具体步骤如下。

1.选择C2单元格，按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

2.单击“新建”按钮弹出“新建名称”对话框，将名称设置为“颜色”，将引用位置设置为“=get.cell（63，B2）”，设置界面如图7.72所示。

图7.72 新建名称“颜色”

3.单击“确定”按钮，返回工作表界面。

4.在C1中输入“颜色”，在C2中输入公式“=颜色”，然后双击C2的填充柄从而取得B2：B11区域中每个单元格的颜色编码，效果如图7.73所示。

5.在F2单元格中输入公式“=SUMIF（$C$2：$C$11，颜色，$B$2：$B$11）”，公式的结果是无背景色的业绩之和。

6.将F2单元格的公式向下填充到F4，公式会分别计算出无色、红色和黄色三种颜色的业绩之和，效果如图7.74所示。

图7.73 识别数据源中的颜色编码

图7.74 对所有背景色的业绩分类汇总

知识扩展

1.只要使用了宏表函数，工作簿就不能保存为xlsx格式，否则定义的名称会自动丢失，从而导致公式计算出错。

2.get.cell函数的第一参数是63时表示计算单元格的颜色编码，0表示无色、3表示红色、6表示黄色、55表示蓝色、50表示绿色……get.cell函数的第二参数表示要在其中提取颜色的单元格，只能是单个单元格。

3.定义名称“颜色”前选择了 C2，名称的引用对象是“=get.cell（63，B2）”，公式的计算对象B2是相对引用，位于C2左方，因此名称“颜色”的含义就是提取左边一个单元格的颜色编码。

在C2单元格输入公式“=颜色”可以生成B2的颜色编码，在F2单元格输入公式“=颜色”则可以生成E2的颜色编码。

4.C 列作为公式“=SUMIF（$C$2：$C$11，颜色，$B$2：$B$11）”的辅助区域，它提供了参考数据，没有C列的值就无法计算出业绩汇总，因此C列的值不能删除。如果觉得C列多余，可以隐藏C列。

Excel 能否根据销量和单价对不同产品分类汇总？

能否根据图7.68所示的销售明细在图7.69中对每个产品的销售额分类汇总？

图7.68 销量明细表

图7.69 汇总表

解题步骤

本例问题需要分两步进行，先用SUMIF函数对每本书的销量分类汇总，然后使用VLOOKUP函数引用每本书的单价，两者相乘即为图书的销售额。

1.进入汇总工作表，在B2单元格中输入以下公式：

2.双击B2单元格右下角的填充柄，将公式向下填充到A7，公式会将所有图书的销售额都瞬间计算出来，结果如图7.70所示。

图7.70 填充公式获取所有图书的销售额

知识扩展

1.SUMIF函数的功能是按条件分类汇总，它的语法如下：

第一参数代表要计算的数据源，第二参数代表条件，第三参数代表求和的区域，第三参数是可选参数，当忽略第3参数时则把第一参数当作求和区域。

在本例中“SUMIF（销量明细！$B$2：$B$11，汇总！A2，销量明细！$D$2：$D$11）”表示在销量明细的B2：B11中查找汇总表的A2单元格的值，找到后对销量明细的D2：D11区域对应位置的销量求和。此处要注意的是“对应位置”。例如，在B2：B11中查找“Excel函数、图表与透视表从入门到精通”，目标在B7和B9，那么最后参与求和单元格则是D7和D9。

2.SUMIF函数只对销量分类求和，引用单价要借助VLOOKUP来完成，两者搭配应用才能实现需求。

3.本例公式需要向下填充，因此所有关于区域的引用都要采用绝对引用，而被查找的书名在填充过程中需要不断变化，则必须采用相对引用。

Excel 是否可以引用值时也一并引用单元格格式？

在图7.64中，B2：B11区域的金额既有美元又有人民币，通过单元格格式区分。在F2单元格中输入姓名，然后在G2单元格通过公式查询F2的姓名所对应的捐款数额。

现在的问题在于：G2 单元格的公式只能引用数值，不能将格式一并引用过去，从而导致公式产生的金额看不出是美元还是人民币，有什么办法实现引用值的同时又引用格式呢？

图7.64 捐款查询表

解题步骤

Excel的查找和引用函数都会忽略单元格的格式，但是可借助早期的宏表函数GET.CELL提取目标单元格的格式信息，然后利用TETX函数将公式的返回值设置为相同格式即可，具体操作步骤如下。

1.选择G2单元格，按组合键＜Ctrl+F3＞，弹出“名称管理器”对话框。

2.单击“新建”按钮，弹出“新建名称”对话框，然后将名称设置为“格式”，将引用位置设置为以下公式：

图7.65是定义名称的设置界面。

图7.65 定义名为“格式”的名称

3.单击“确定”按钮返回工作表界面，并在G2单元格中输入以下公式：

公式会返回F2的姓名所对应的捐款数量，而且公式返回值与原数据的显示样式一致，效果如图7.66所示。

图7.66 引用赵光文的捐款

4.将F2单元格的值修改为“谢有金”，公式将返回“$720.00”，效果如图7.67所示。

图7.67 引用谢有金的捐款

B2：B11区域的单元格格式各不相同，其前置的币别符号是通过单元格格式产生的，而非手工输入的，以上步骤可以实现引用单元格的值时也将格式一并引用过来。

知识扩展

1.GET.CELL是宏表函数，只能用于xlsm和xls格式的工作簿中，假设在xlsx格式的工作簿中使用，那么重新打开工作簿后它会自动消失，因此本例的案例文件采用的是xlsm格式。

2.GET.CELL用于获取与单元格相关的数十项信息，当第一参数是7时表示提取单元格的格式信息。TEXT函数用于对单元格的数值指定格式，因此将它与GET.CELL函数搭配使用可以引用指定单元格的格式。

3.公式“=GET.CELL（7，Index（$B2：B$11，MATCH（$F$2，$A$2：$A$11，0），0））”中的MATCH函数用于计算F2的姓名在A2：A11区域中的位置，然后使用INDEX函数引用该位置的单元格，将此单元格作为GET.CELL函数的第二参数则可以提取此单元格的格式信息。

此格式信息被赋予名称“格式”，然后将名称作为 Text 函数的第二参数去限定 VLOOKUP函数找到的捐款数的格式。原本VLOOKUP函数找到的捐款数只是720或1045，通过TEXT函数限定格式后会变成“$720.00”或“¥1，045.00”。

4.在公式中引用名称时，不能对名称添加引号。

Excel 能不能根据产品名称引用对应的单价？

图7.61所示为各产品单值，图7.62所示的日产量表需要计算每个产品的产值，产值等于产量乘以单价，有什么办法让公式自动搜索当前产品对应的单价，避免人工查找单价，既耗时又易出错？

图7.61 单价表

图7.62 产值表

解题步骤

根据品名从单价表中引用对应的单价，最简便的方法是使用VLOOKUP函数引用目标，操作步骤如下。

1.选择E2单元格，并输入以下公式（见图7.63）：

要注意公式中第二参数是绝对引用，第一参数是相对引用。

图7.63 计算引用第一个产品的产值

2.双击E2单元格的填充柄，将公式向下填充，从而计算出所有产品的产值。

知识扩展

1.VLOOKUP 函数用于从列表左侧查找数据，找到后返回右边某列中对应位置的值。在本例中，VLOOKUP函数用于从单价表A2：B10区域的左边一列A2：A10中查找品名，当找到品名后，返回第2列中对应位置的单价。

2.VLOOKUP函数的语法如下：

其中第一参数代表要查找的值，本例中是品名；第二参数是要在其中查找的一个列表，它既可以是区域也可以是数组，本例中是单价表中的A2：B10区域；第三参数代表列数，即返回值位于列表中的第几列，必须是大于等于1、小于等于总列数的整数值；第四参数代表匹配方式，当赋值为False时表示精确匹配，赋值为True时表示糊模匹配，本例采用的是前者。

也可以用以下语句来说明VLOOKUP函数的语法：

VLOOKUP （要查什么，在哪里找，返回第几列的，[查找方式]）

3.由于本例的公式需要向下填充，从而一次性引用所有产品的单价，因此公式的第二参数“单价表！$A$2：$B$10”需要采用绝对引用，否则可能查找不到单价。

VLOOKUP的第一参数是品名，在填充公式时需要它不断变化，因此只能采用相对引用方式。

4.为了避免查找出错，在书写品名时必须确保产量表中的品名与单价表中的品名一致，有任何差异就可能查找失败，从而产生错误值。例如，在产量表中将“一字刀”写成了“一字力”，再或者在“刀”右方多了一个空格，都会导致公式结果为错误值。

Excel 是否可以根据姓名和科目自动查找对应成绩？

图 7.53 所示的成绩表中行标题为姓名，列标题为科目，能否在输入姓名和科目名称后自动给出对应的成绩？

解题步骤

根据已知的两个条件查询对应的数据，达成此需求有多种方法，其中使用公式灵活性最好，具体操作步骤如下。

1.在I1：K1中分别输入“姓名”、“科目”和“成绩”。

2.在I2：J2分别输入“朱明”和“化学”。

3.在K2单元格输入以下公式：

此时公式返回朱明的化学成绩94，效果如图7.54所示。

图7.54 查找朱明的化学成绩

4.将姓名修改为“曹锦荣”，将科目修改为“语文”，此时K2的公式会返回成绩“100”（见图7.55）。

图7.55 查找曹锦荣的语文成绩

知识扩展

1.MATCH函数用于计算一个字符串在一维数组或单行/单列区域的出现位置。例如，在图7.55中，曹锦荣在A2：C11区域中的出现位置是第8位，那么公式“=MATCH（I2，A2：A11，0）”的返回值就是8。MATCH函数的语法如下：

其中第一参数是查找对象，第二参数是一维数组或单列区域/单行区域，第三参数则用于控制查找方式，赋值为0时表示精确查找，赋值为1或-1时表示模糊查找，工作中用得最多的是精确查找。简言之，MATCH函数的功能就是在第二参数中查找第一参数的位置，并由第三参数决定查找方式。

2.INDEX函数的功能是在一个区域或数组中按位置查找对应的值，由于MATCH函数的功能正是计算位置，因此INDEX函数经常搭配MATCH函数使用，本例正是此类型的应用案例。

3.在本例公式中，第一个MATCH函数用于计算纵向位置，第二个MATCH函数用于计算横向位置，两者的交叉点刚好是B2：G11区域中对应的成绩。

4.了解公式的含义和计算过程的最好方法是使用公式求值工具。以图7.54中的公式为例，了解公式的方法是选中K2单元格，然后单击功能区的“公式”→“公式求值”，并在对话框中单击“求值”按钮，图7.56所示的窗口中表达式“MATCH（I2，A2：A11，0）”的下画线表明下一步会计算这个表达式，此时再次单击“求值”按钮，对话框会变为图 7.57 所示的效果，图中说明了两个问题，一是表达式“MATCH（I2，A2：A11，0）”的计算结果为6，二是下一步要计算的表达式是J2的值。