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

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

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

图7.64 捐款查询表

解题步骤

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

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

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

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

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

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

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

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

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

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

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

图7.66 引用赵光文的捐款

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

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

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