本期的主题是关于Excel的INDIRECT函数,我们会介绍该函数的基本用法以及与其他函数相结合、与名称相结合等的应用。
INDIRECT函数也是查询类函数的一种,它与其他的查询类函数不同的是,使用间接引用,即引用的是单元格地址,而非单元格数据本身。
01INDIRECT函数引用单元格地址
在如下的案例中,我们通过INDIRECT函数来查询“Agent Name”及其对应的“Contact Number”。
直接引用单元格的话,可直接使用“=G3”来进行查询,而使用INDIRECT函数的话,则间接引用了C12单元格中的数据“G3”。
在C11单元格中输入INDIRECT函数,输入参数“C12”。
按Enter键后,返回的数据是G3单元格中的名称“Connor Egan”。
INDIRECT函数可提供两个参数,第一个参数ref_text为必要参数,而第二个参数为可选参数,主要用于选择不同的单元格样式,默认情况使用的是“a1”类型,还可以选择另一张类型“R1C1”。
在D12单元格中是“R1C1”类型,而“R3C8”所表示的是H3单元格。
在D11单元格中输入INDIRECT函数,选择D12单元格作为第一个参数,而第二个参数需选择“FALSE”。
按Enter键后,即可返回H3第一个中的“Contact Number”。
然而在实际的工作中,我们通常不会单独在某些单元格中给出所要查询数据的单元格地址,因此在使用INDIRECT函数时,其参数会是文本、单元格引用和公式的结合。
02INDIRECT与MATCH函数的结合
在C11单元格中输入INDIRECT函数后,第一个参数使用文本“G”与函数MATCH的结合(MATCH函数用于获取单元格所在的行数)。
同理,我们可以通过上述的方式来查询“Contact Number”,改参数中的“G”列为“H”列即可。
03INDIRECT函数与名称的结合
在C6单元格中,我们要计算出总销售额,根据C5单元格中所选择的月份,统计数据也相应地变化。
各个月份的销售额数据已创建了对应的名称,如October工作表中的销售数据列的名称为“October”。
在C6单元格中,输入SUM函数,其参数使用INDIRECT函数,而INDIRECT函数的参数为C5单元格中已创建的名称“October”。
通过类似的方式,我们可以计算出C7单元格中对应月份销售额前三的平均值。
在C7单元格中输入AVERAGE函数,参数为LARGE函数,而LARGE函数的第一个参数为INDIRECT函数,第二个参数k使用ROW函数,通过ROW(1:3)来得到排在前三的三个数据,而非单独的一个数据(第一、第二或第三),完成公式后按Ctrl+Shift+Enter键,此处为计算多列数据所需,即使用数组公式。
04INDIRECT函数与数据验证结合创建级联列表框
在使用数据验证的“序列”功能时,可以与INDIRECT函数相结合创建级联列表框,即一个下拉选框会影响与另一个下拉选框是联动的。
一个“County”下有一个或多个“District”,选择不同的“County”时,对应的另一个下拉选框可供选择与之对应的“District”列表。
选择C4单元格,点击“数据”选项卡下的“数据验证”,选择“序列”,在“来源”框中使用INDIRECT函数,参数选择C3单元格中的“County”名称,从而可引用与之对应的“District”列表。
点击“确定”后,我们在C4单元格中,可以看到一个下拉选框的列表,是与C3单元格中的“Buckinghamshire”相对应的所有地区。
05INDIRECT函数与结构化引用结合
在C8单元格中计算出“October”这一时间的“Chiltern”这一地区的销售总额,所使用的函数为SUMIFS。
此例中SUMIFS函数的第一个参数为“INDIRECT(C5)”,即October的销售额数据列;第二个参数“INDIRECT("Sales_"&C5&"[District]")”,在INDIRECT函数中通过表格名称"Sales_"&C5(表格名称的名称惯例以Sales_开始,工作表名称为C5单元格中的“October”)与结构化引用"[District]"(数据表格中的“District”列)结合。
按Enter键后,即可得到“Chiltern”地区在“October”的销售总额。
INDIRECT函数通过间接引用的功能,可以与名称、结构化引用、其他函数及数据验证等功能相结合,创造出计算力很强的公式,以应对用户不同需求和数据的变化。
联系客服