标签：引用

在图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.在公式中引用名称时，不能对名称添加引号。

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

经常听说公式包含相对引用和绝对引用，它们的概念是什么？有何分别？在何时该选用相对引用、何时选用绝对引用呢？

解题步骤

绝对引用是指基于参照单元格的绝对地址而产生的引用，不管公式所在单元格如何变化，公式都总是引用固定单元格的值。绝对引用的标志是行号、列标前的$符号。

例如，在图7.1中D3单元格引用B2的值，由于公式“=$B$2”中列标B和行号2前面都有绝对符号$，因此将D4单元格的公式向下填充再向右填充，始终只能用引B2单元格的值，效果如图7.2所示。

图7.1 使用绝对引用方式引用B2的值

图7.2 填充公式后仍然只能引用B2的值

相对引用是指基于参照单元格的相对位置而产生的引用，如果公式所在单元格的位置改变，引用也会随之改变。例如，在图7.3中E4单元格的公式“=B2”引用了B2的值，当把公式向下填充到E5时，公式“=B2”会变成“=B3”，效果如图7.4所示。

图7.3 引用B2的值

图7.4 填充公式后引用B3的值

如果将E5单元格的公式向右填充到F5，F5单元格的公式将变成“=C3”。

以上就是相对引用的特点——将公式向任意方向填充时，引用对象也相应地变化。

形象、通俗地讲，“XX市XX镇XX街XX号大厦一楼男厕”，这属于绝对引用，不管你身在何处都可以通过这个地址找到该男厕；“出门右拐10米，再左拐5米，第一道大门进门后右手边男厕”，这属于相对引用，它基于发言人所在位置，然后指定方向从而找到新的位置，发言人在不同地址，根据此描述会产生不同的地址。

除了相对引用和绝对引用以外，还有一种混合引用，包含列相对行绝对和列绝对行相对两种，“A$5”属于列相对行绝对的混合引用，当公式横向填充时引用会相应地变化，纵向填充时则不会变化；“$A5”属于列绝对行相对的混合引用，当公式纵向填充时引用会相应地变化，横向填充时则不会变化。

当单元格中已经存在相对引用的公式时，要将它转换成绝对引用或相对引用，仅需选中公式中的单元格地址并按F4键即可，每按一次变化一次。

以上只是阐述相对引用、绝对引用、混合引用的基本概念，以及它们之间的区别。在实际应用中去展示相对引用和绝对引用可以让读者理解得更透彻。

以计算排名为例，按以下步骤操作可以深入理解相对引用与绝对引用。

1.在图 7.5 所示的工作表中的 C2 单元格输入公式“=RANK.EQ（B2，B2：B11）”，公式的计算结果为9，表明60分在B2：B11这个区域中属于降序第9名，效果如图7.5所示。

图7.5 在C2输入相对引用的排名公式

2.将C2单元格的公式向下填充到C11，公式的计算结果见图7.6。很显然，计算结果有问题，B9单元格的98和B11单元格的85并列第一名，同时有3个数值不同的单元格并列第二名。产生这种问题的根源在于 Rank.EQ 函数的第二参数使用了相对引用，导致引用对象出错，从而计算结果也出错（见图7.7）。

原本应该是计算每个单元格的成绩在 B2：B11 区域的排名，由于 Rank.EQ 函数的第二参数使用了相对引用，当公式向下填充时引用对象会由B2：B11变成B3：B12，B4：B13，…，B10：B19，B11：B20，排名时的参考对象是错的，排名结果自然就相应出错。

图7.6 将C2的公式填充到C11

图7.7 C11的公式引用出错

解决以上问题的方法是将相对引用B2：B11改成$B$11：$B$20，操作步骤如下。

3.如图7.8所示，选择C2单元格，然后在编辑栏选中B2：B11，并按下F4键，B2：B11会变成$B$11：$B$20，效果如图7.9所示。

图7.8 选中需要切换引用方式的地址

图7.9 切换引用方式

4.将C2的公式向下填充到C11，公式的所有运算结果都不再出错，效果如下图7.10所示。

图7.10 绝对引用下的公式计算结果

图7.10中，RANK.EQ函数的第一参数是相对引用，当公式向下填充时，B2会变成B3，B4，B5，…这符合工作需求，也正是相对引用的特点；RANK.EQ函数的第二参数$B$2：$B$11是绝对引用，因此不管公式在哪个单元格都是引用相同的对象，从而确保计算结果的准确性。

事实上，由于C2的排名公式只需要向下填充，不需要向右填充，因此绝对引用也可以改成列相对行绝对的混合引用，公式为“=RANK.EQ（B2，B$2：B$11）”。

将$B$2：$B$11变成B$2：B$11的方法是在公式中选择$B$2：$B$11，然后按下F4键即可。

知识扩展

1.当公式只需要在单个单元格中使用时，公式中的引用采用相对引用还是绝对引用对公式的计算结果没有任何影响，只有需要将公式填充到其他单元格时才需要注意引用方式。

2.当C2单元格的公式使用了相对引用时，把C2复制到D10后，公式中的引用会产生变化。如果既不想将公式中的引用改成绝对引用，又想要C2的公式复制到D10后引用对象不变，那么不能复制单元格，因该进入编辑栏复制公式，然后将公式粘贴到D10。

3.要用好相对引用和绝对引用，除了需要掌握本例中所讲的知识以外，还需要掌握每一个函数的功能，以及每个参数的含义，否则不知道哪一个参数需要用相对引用，哪一个参数一定不能用相对引用。