Excel 函数——查找和引用函数.pdf

1、第5章 查找和引用函数 在Excel中，查找和引用函数的主要功能是查询各种信息。在数据量很大的工作表中，Excel的查找和引用函数能起到很大的作用。在实际应用中，查找和引用函数会和其他类型的函数一起综合应用，完成复杂的查找或者定位。在本章中，将根据函数的性质，分为查找和引用函数两个小节详细讲解。 5.1 查找函数 查找函数的主要功能是快速的确定和定位所需要的信息。这类函数的主要功能是检索：根据实际需要，在工作表或者在多个工作薄中获取需要的信息或者数据。在本小节中，将详细讲解各个函数的功能。 5.1.1 ADDRESS 函数：返回引用地址 【功能说明】该函数根据给定的行号和列号，返回某一个具体的

2、单元格的地址。 【语法表达式】ADDRESS(row-num，column-num，abs-num，a1，sheet-text) 【参数说明】 z Row-num：代表行号，表示单元格在哪一行的数字。例如，单元格D2，就表示其在第2行。 z Column-num：代表列标号，表示单元格在哪一列的数字。例如，单元格D2，就表示其在D列。 z Abs-num：代表引用类型。使用函数时，其值可以是1、2、3、4中的任意一个值。该参数也可以省略。如果省略该参数，系统将其默认为1。数字和其代表的引用类型的关系，如表5.1所示。 表5.1 数字和代表类型 数字 引用类型 1 代表绝对引用 2 代表绝对行号

3、，相对列标 3 代表相对行号，绝对列标 4 代表相对引用 z A1：代表引用样式的逻辑值。如果参数a1为TRUE或者省略，那么函数将返回a1样式的引用；如果a1为FALSE，那么函数将返回R1C1样式的引用。 z Sheet-text：代表一文本，指定作为外部引用时的工作表名称。如果省略该参数，那么表示不使用任何工作表名。 【使用说明】如果参数a1是指定的四个数字之外的任意数字，函数都将返回错误值#value!。 【实际应用】 某公司组织员工进行某种游戏，现在有员工编号和游戏序号。根据最后得奖序号来判断员工编号的单元格。 在单元格E2中输入公式“=ADDRESS(3,1,1)”，得到得奖员工编

4、号所在单元格，如图5.1所示。 图5.1 获取得奖单元格 【应用说明】在Excel中，引用类型有绝对引用、相对引用、混合引用三种类型。引用样式有a1和R1C1两种。 5.1.2 AREAS 函数：返回区域个数 【功能说明】给定一个引用，利用AREAS函数计算该引用中包含的区域的个数。该引用区域可以是连续的单元格区域，也可以是某个具体的单元格。 【语法表达式】AREAS(reference) 【参数说明】Reference：代表对单元格或单元格区域的引用。使用函数是，其值可是是单个单元格，也可以是单元格区域。如果需要引用多个不连续的单元格区域，那么需要用括号把各引用区域引起来。 【使用说明】在引

5、用多个不连续的单元格区域时，一定要用大括号把引用区域括起来。 【实际应用】 某总公司将各个不同部门的销量统计在不同的单元格范围内，现在需要统计公司所有的区域总数。 在单元格B2中输入“=AREAS(A4:B11,D4:E11,A15:B21,D14:E21,G14:H21)”，计算的区域总数结果如图5.2所示。 图5.2 返回区域个数 【应用说明】单元格也是小区域，因此AREAS函数也可以统计单元格的个数。该函数还可以应用到经济销售的其他领域，用来统计销售项目个数或者销售区域总数。 5.1.3 CHOOSE 函数：从列表中选择数值 【功能说明】该函数利用给定的参数值，返回数值参数列表中的一个值

6、。列表中的数值个数介于1-29这29个数值之间。 【语法表达式】CHOOSE(index-num,value1,value2) 【参数说明】 z Index-num：用来指定所选定的数值参数，其中Index-num的参数必须是介于1-29之间的数字表达式或者单元格字段。这里包括了三种情况： （1）当Index-num的值为1时，CHOOSE函数会返回Value1的值；为2时，会返回Value2的值；依次类推。 （2）当Index-num的值是一个小数，那么按四舍五入取整后，再返回（1）。 （3）当Index-num的值小于1或者大于列表中最后一个值的序列号，函数将返回一个错误值#VALUE！。

7、 z Value1，value2为1到29个数值参数。函数根据Index-num提供的参数值，从中选择一个数值或者一项要执行的操作。这些数值参数可以是数值表达式、列表、公式、单元格引用、定义名称、函数或者文本。 【使用说明】 z Index-num中的参数可以是数值表达式，也可以是字段，其运算结果是一个介于1和29之间数值。如果Index-num是一个数组，则在计算时，每一个值都会参与计算。 z 数值参数间需要用逗号隔开。 【实际应用】 某小公司中包含了多个员工的薪酬信息，根据不同的工资等级，公司需要为这些员工选取不同的奖金比例等级。 在单元格C2中输入函数表达式“=CHOOSE(INT(B2

8、/3000),“4%“,“8%“,“12%“)，计算单元格B2中工资额的奖金比例，然后利用自动填充功能，计算其他工资额的奖金比例，结果如图5.3所示。 图5.3 选取奖金比例 【应用说明】CHOOSE函数的数值参数还可以为区域被引用。 5.1.4 COLUMN 函数：返回列号 【功能说明】该函数用来返回指定引用的列号。列号的范围是1256之间的任意整数。 【语法表达式】COLUMN(reference) 【参数说明】表示单元格，或单元格区域。也就是，一个单元格，或单元格区域。 【使用说明】 z 如果省略参数reference，那么返回函数COLUMN所在单元格的列号。 z 如果参数refere

9、nce 是单元格区域，且函数COLUMN 以水平数组的方式输入，那么函数将返回位于单元格区域首列的单元格的列号。 【实际应用】 某公司统计了三个部门的员工编号，下面需要通过员工编号判断所在部门。 在单元格E3中输入“=COLUMN(C6)”，判断员工编号314所在的部门，如图5.4示。 图5.4 确定员工所在的部门 【应用说明】参数reference只能引用一个单元格区域，不能同时引用多个单元格区域。 5.1.5 COLUMNS 函数：返回引用的列数 【功能说明】计算引用的单元格区域包含的列数，返回的结果是1256之间的数值。 【语法表达式】COLUMNS (array) 【参数说明】arra

10、y：代表要计算其列数的数组或数组公式，可以是引用的单元格区域。 【使用说明】该函数不能省略参数。 【实际应用】 某公司统计了各部门的员工编号，现在需要统计部门总数。 在单元格E3中输入“=COLUMNS(A2:C10)”，结果如图5.5示。 图5.5 返回引用区域的数目 【应用说明】如果参数是单元格、单元格区域、数组、数组公式以外的任何值，函数将返回错误值#Value！。 5.1.6 HLOOKUP 函数：实现水平查找 【功能说明】在数据表或数值数组的首行查找指定的数值，并在数据表中指定行的同一列中返回一个数值。 【语法表达式】HLOOKUP(lookup-value，table-array，

11、row-index-num，range-lookup) 【参数说明】 z Lookup-value：表示需要在数据表第一行中查找的数值。该参数可以是数值、引用或文本字符串。 z Table-array：表示需要在其中查找数据的数据表。该参数可以引用单元格区域或区域名称。第一行的数值可以为文本、数字或逻辑值，文本不区分大小写。 z Row-index-num 表示行号，也就是在table-array中待返回的表格中相匹配的值的行序号。 z Range-lookup：表示逻辑值，用来指明函数HLOOKUP查找时是精确匹配，还是近似匹配。当range-lookup值为TRUE，或省略该参数时，函数将

12、返回近似匹配值。也就是，如果函数找不到精确匹配值，那么，函数将返回小于lookup-value的最大数值；当其值为FALSE时，那么函数会查找精确匹配值，否则，函数将返回错误值#N/A！。 【使用说明】 z HLOOKUP函数中H表示“行”。 z 如果range-lookup值为TRUE，那么table-array的第一行的数值必须按升序排列，如-2、-1、0、1、2A-Z、FALSE、TRUE；否则，该函数将找不到能匹配的数值；如果其值为FALSE，那么，table-array可以不用排序。 z 使用函数时，如果参数row-index-num值小于1，那么函数将返回错误值#VALUE！。 z

13、 如果参数row-index-num的值大于table-array的行数时，函数将返回错误值#REF！。 z 在函数计算时，如果HLOOKUP找不到lookup-value，且range-lookup的值为TRUE，那么函数返回小于lookup-value的最大值；如果HLOOKUP小于table-array第一行中的最小数值，函数将返回错误值#N/A！。 【实际应用】 某厂商销售A、B和C三种商品，在销售单上，用户需要获取商品的单价。 在单元格C5中输入函数“=HLOOKUP(A5,$A$1:$C$2,2,FALSE)”，选取商品A单价，然后利用自动填充功能，得到其他项目的单价，如图5.6所

14、示。 图5.6 选取单价信息 【应用说明】当比较值位于数据表的首行，并且要查找下面给定行中的数据时，可以使用此函数。当比较值位于要查找的数据左边的一列时，则要使用VLOOKUP函数。 5.1.7 HYPERLINK 函数：创建跳转 【功能说明】利用该函数可以创建一个快捷方式，也就是页面跳转。 【语法表达式】HYPERLINK (link-location，friendly-name) 【参数说明】 z Link-location：表示文档的路径和文件名，可以把这个文档当作文本打开。该参数还可以为文档中某一更具体的位置指定路径，路径可以是存储在硬盘驱动器上的文件，也可以是服务器上的“通用命名规范

15、”路径，还可以是“统一资源定位符”。 z Friendly-name：表示单元格中显示出来的跳转文本值或数字值，单元格的内容呈蓝色并带有下划线；如果省略该参数，Link-location在单元格里将作为跳转文本显示。 【使用说明】 z Link-location可以是被双引号括起来的文本字符串，也可以是包含了文本字符串链接的单元格。 z Friendly-name可以是数值、文本字符串、名称或包含跳转文本或数值的单元格。如果该参数返回错误值#VALUE！，那么单元格就是显示错误值以代替跳转文本。 z 如果需要选定一个包含超链接的单元格并且不跳往超链接的目标文件，可以单击单元格区域，同时按住鼠标

16、按钮，直到光标变成一个十字，然后释放鼠标按钮即可。 【实际应用】 某公司统计了员工的信息，但是公司将其年龄设置为超连接。 在单元格C2中输入公式“=HYPERLINK(“30岁“,“年龄“)”，设置第一名员工的年龄超连接。然后依次输入其他员工的超链接公式，结果如图5.7所示。 图5.7 查看超连接结果 【应用说明】通过创建的快捷方式可以打开存储在网络服务器和Internet中的文件。单击HYPERLINK函数所在的单元格时，表格能打开存储在link-location中的文件。 5.1.8 LOOKUP 函数：查找数据 【功能说明】从单行或者是单列的单元格区域中，或者从一个数组中，返回需要查找的

17、值。它有两种形式：向量形式和数组形式，都只包含一行或一列区域。 【语法表达式】向量LOOKUP (lookup-value，lookup-vector，result-vector) 数组LOOKUP (lookup- value，array) 【参数说明】 在向量LOOKUP中： z Lookup-value：表示函数在第一个向量中搜索的值。该参数可以是数字、文本、逻辑值、名称，也可以引用值。 z Lookup-vector：只包含一行或一列的区域。该参数可以是文本、数字或者逻辑值。设置参数的值时，必须以升序顺序放置，否则函数可能无法提供正确值。 z Result-vector：也只包含一行或

18、一列区域，而且，大小必须与lookup-vector一致。 在数组LOOKUP中： z lookup- value：表示函数在数组中搜获的值。使用函数时，其值可以是数字、文本、逻辑值、名称，也可以是引用值。 z array：表示数组名，参数必须以升序顺序放置，否则，函数无法提供正确的值。 【使用说明】 在向量LOOKUP中： z 如果要查询的值列表较大或者值可能会随时间而改变时，而且是指定行或列的位置，应使用向量形式的LOOKUP。向量形式的LOOKUP可以在单行或单列区域中查找值，返回第二个单行或单列区域中相同位置的值。 z 如果函数找不到lookup-vector，那么，它与lookup-

19、vector中小于或等于lookup-vector的最大值匹配。如果lookup-value小于lookup-vector中的最小值，函数会返回错误值#N/A！。 在数组LOOKUP中： z 如果函数找不到lookup-value，它将使用数组中小于或等于lookup-value的最大值；如果小于lookup-value第一行或列中的最小值，这个最小值取决于数组的维度，函数将返回错误值#N/A！。 z 如果要查询的值列表较小或者在一段时间内保持不变时，而且要匹配的值在数组的第一行或列中时，应使用数组形式的LOOKUP。数组形式的LOOKUP可以在数组的第一行或列中查找指定值，返回数组最后一行或

20、列中相同位置的值。 z 数组包含要与lookup-value进行比较的文本、数字、逻辑值的单元格区域，而且是根据数组的维度进行搜索。 z 如果数组包含的列数多于行数，函数将在第一行中搜索lookup-value；如果数组是正方形的或者行数多于列数，那么，函数将在第一列中进行搜索；函数始终会选择行或者列中的最后一个值。 【实际应用】 某销售公司统计了各部门员工的销量数据，现在需要确定最大销量的员工部门。 在单元格B13中输入“=LOOKUP(MAX(C2:C11),$C$2:$C$11,A2:A11)”，确定最大销量员工所在的部门，如图5.8所示。 图5.8 确定员工的部门 【应用说明】LOOK

21、UP函数中大小写文本是一样的。 5.1.9 MATCH 函数：在数组中查找 【功能说明】返回指定方式下与指定数值匹配的数组中元素的相对应的位置。 【语法表达式】MATCH(lookup-value，lookup-array，match-type) 【参数说明】 z Lookup-value：表示需要在数据表中查找的数值。也就是是需要在参数Lookup-array中查找的数值。使用函数时，该参数可以是数值、数字、文本、逻辑值的单元格引用。 z Lookup-array：表示数组或者数组引用。其中，数组可以是包含要查找的数值的连续单元格区域。 z Match-type：一个数字。其值可以是-1、0

22、、或者1。该参数指明查找值的方法，如表5.2所示。 表5.2 match-type参数说明 Match-type值 函数查找范围 Lookup-array排序方式 1或省略 查找小于等于look-value的最大数值 必须是升序排列 0 查找等于look-value的第一个数值 可以是任意次序排列 -1 查找大于等于look-value的最小数值 必须是降序排列 【使用说明】 z 如果省略参数Match-type，系统将默认其值为1。 z MATCH函数返回的是lookup-array中目标值的位置，并非数值本身。 z 查找文本时，函数不区分大小写。 z 函数如果查找不到目标值的位置，就返回错

23、误值#N/A！。 【实际应用】 已知某产品单价表，查询某产品品牌所在的数据列。 在单元格B8中输入表达式“=MATCH(A8,A2:A6)”，查询商品D品牌在“商品”数据列的数据列，在本例中返回的结果是4，结果如图5.9所示。 图5.9 查询产品品牌的数据列 【应用说明】如果参数Match-type的值为0，且Lookup-value是文本，Lookup-array可以包含通配符、星号和问号。星号可以匹配任何字符序列，问号只能匹配单个字符。 5.1.10 ROW 函数:返回引用的行号 【功能说明】返回指定单元格的行号，或者是指定单元格区域中首行的单元格的行号。返回的值是165536之间的任意数

24、。 【语法表达式】ROW(reference) 【参数说明】Reference：表示指定的单元格或单元格区域。 【使用说明】 z 如果参数reference是单元格区域，那么函数返回区域中首行的单元格的行号。 z 参数Reference不能引用多个单元格区域。 z 如果省略Reference参数，那么返回函数ROW所在的单元格的行号。 【实际应用】 某公司统计了112月的销量，现在需要通过销量来确定该销量对应的月份。 在单元格B15中输入公式“=ROW(B9)-1”，判断销量对应的月份，如图5.10所示。 图5.10 查看销量所在的月份 【应用说明】如果Reference是单元格区域，并且函数

25、ROW作为垂直数组输入，那么函数ROW会返回一个垂直数组形式的Reference行号。 5.1.11 ROWS 函数:返回引用的行数 【功能说明】该函数可以返回所引用的单元格区域的行数，也可以返回数组的行数。 【语法表达式】ROWS(array) 【参数说明】Array：表示需要计算其行数的数组、数组公式或引用单元格区域。 【使用说明】此函数可以根据用户提供的参数，返回特定的单元格所在行数，这个用法与Row的数组形式使用有点类似。 【实际应用】 某公司将每月的销量统计在一列中，现在需要计算已经统计的月份。 在单元格B12中输入公式“=ROWS(A2:A10)”，判断统计的月份，如图5.11所示

26、。 图5.11 统计销量的月份 【应用说明】如果查询的是数组的行数，如果公式不是以数组形式输入的，和ROW函数返回的结果是相同的。 5.1.12 RTD 函数：获取实时数据 【功能说明】该函数可以RTD服务器中返回实时数据。【语法表达式】RTD(ProgID，server，topic1，topic2，) 【参数说明】 z ProgID：表示已安装在本地计算机上，经过注册的COM自动化加载宏的ProgID名称，这个名称要求用括号括起来。 z Server：表示运行加载宏的服务器名称。如果没有服务器，那么，程序是在本地计算机上运行，那么该参数为空白，不然，要用双引号将服务器的名称用引号引起来。如果

27、在VBA中使用RTD，就必须用双重引号将服务器名称引起来，或者对赋予VBA NUllString属性，即使服务器只在本地计算机上运行。 z Topic1，topic2，：表示28个参数，从序列1到28，这些参数放在一起时，只代表一个唯一的实时数据。 【使用说明】 z 必须在本地计算机上创建并且要注册RTD COM自动化加载宏。如果没有安装实时数据服务器，那么，在要使用RTD函数时，在单元格中会返回错误值。 z 如果服务器要继续更新结果，那么与其它的函数会有不同点，RTD函数的公式会在工作簿表格中处于自动计算模式下更改。 【实际应用】 现在已知巴黎时间，转换为北京时间。 在单元格B12中输入公式

28、“=RTD(“smart“,“,B1)”，转换时间，如图5.12所示。 图5.12 转换时间 【应用说明】RTD服务器是增加用户设置的命令或专用功能，扩大了Excel应用功能的辅助程序，方便COM加载项的运用。其中，COM加载项是指用户通过添加自定义的标签或指令来扩充Excel功能的补充程序。 5.1.13 VLOOKUP 函数：实行竖直查找 【功能说明】在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。函数VLOOKUP中的“V”表示垂直查找。 【语法表达式】VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

29、【参数说明】 z lookup_value：表示需要在表格第一列中查找的数值。Lookup_value可以直接输入数值，也可以指定单元格。如果lookup_value小于table_array的第一列中的最小值，函数将返回一个错误值“#N/A！”。 z Table_array：表示两列或者多列数据，应当使用对单元格区域的引用，或者引用单元格区域名称。Table_array第一列中的值是由lookup_value搜索的值，这些值可以是文本、数字或逻辑值，且不区分大小写。 z Col_index_num：表示在Table-array中需要返回的匹配值的序列号。如果col_index_num的参数值

30、为1，函数将返回table_array第一列的数值；如果col_index_num的参数值为2，函数将返回table_array第二列的数值，以此类推。 z range_lookup：表示一个逻辑值，规定函数VLOOKUP查找精确的匹配值，还是近似匹配值。 【使用说明】 z 在 table_array 第一列中搜索文本值时，table_array 第一列中的数据不应该出现前导空格、尾随空格、不一致的直引号（或 “）、弯引号（或“）或非打印字符。如果出现了这些特殊符号，那么，VLOOKUP函数可能返回错误值或意外值。 z 当col_index_num的参数值小于1时，VLOOKUP函数会返回错误

31、值#VALUE！。 z 当col_index_num的参数值大于table_array的列数，那么VLOOKUP函数会返回错误值#N/A。 z 当参数range_lookup值为TRUE或省略时，函数返回精确匹配值或近似匹配值，如果找不到精确匹配值，那么函数会返回小于lookup_value的最大数值。 z table_array第一列中的值必须以升序排序，否则函数无法返回正确的值。可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排列。 z 当参数range_lookup值为FALSE时，table_array第一列可以任意顺序排列，而且函数会寻找精确的匹配值。如果在tab

32、le-array第一列中有两个或两个以上的值与lookup_value相匹配，那么函数会返回第一个找到的值；如果找不到精确匹配值，函数会返回错误值#N/A。 z 在搜索数字或日期的值时，table_array 第一列中的数据不要保存为文本形式，否则，VLOOKUP 函数继续返回错误值或意外值。 【实际应用】 某公司统计了各部门员工的销量，现在需要根据员工编号来查看其对应的销量。 在单元格B14中输入“=VLOOKUP(B13,B2:C11,2,FALSE)”，查看员工编号是117的销量，结果如图5.13所示。 图5.13 查询销量 【应用说明】如果range_lookup值为FALSE，且lo

33、okup_value是文本值，那么在lookup_value中可以使用通配符、问号和星号。问号匹配任意单个字符，星号精辟培任一字符序列。如果查找的是问号和星号本身，应当在该字符前键入波形符，也就是“”。 5.2 引用函数 在Excel中，所有的函数操作都是基于单元格地址的。因此，在多种处理情况下，用户需要首先获取或者分析单元格的地址。在本小节中，将详细讲解各种引用函数。 5.2.1 INDEX 函数：返回指定内容 【功能说明】该函数分为数组形式INDEX函数和引用形式INDEX函数两种。数组形式的INDEX函数，根据给定的行号和列号，返回指定行列交叉处的单元格的值。引用形式的INDEX函数，函

34、数返回指定行列交叉处的单元格的引用。 【语法表达式】数组形式：INDEX(array，row_num，column_num) 引用形式：INDEX(reference，row_num，column_num，area_num) 【参数说明】 数组形式 z Array：表示单元格区域或数组常量。如果数组中只包含一列或一行，那么可以不使用row_num或者column_num参数；如果数组中包含多行和多列，且使用了row_num或column_num中的一个参数，那么INDEX函数将以数组形式返回整行或整列。 z Row_num：表示数组中某一指定行的行号，用来确定函数最后返回的数值所在的行。使用函

35、数时，当row_num的值为1时，可是省略该参数，但不能省略column_num。 z Column_num：表示数组中某一列的列号，用来确定函数最后返回的数值所在的列。使用函数时，当column_num的值为1时，可是省略该参数，但不能省略row_num。 引用形式： z Reference：表示引用单个单元格，或引用多个单元格。如果引用不连续的单元格，需要用括号把各单元格括起来；如果引用的单元格区域中只包含一行或一列，那么参数row_num或column_num就可选可不选了。 z Row_num：表示引用中的某一行的行号。 z Column_num：表示引用中的某一列的列号。 z Are

36、a_num：表示从引用区域中，返回 row_num和column_num的交叉的区域。选择或输入第一个区域的编号是1，第二个区域的编号是2，依此类推。如果省略该参数，那么INDEX函数使用区域1。 【使用说明】 在数组形式的INDEX函数中： z 如果INDEX的第一个参数是数组常量，应该选择数组形式。 z 如果同时使用了row_num和column_num参数，INDEX函数会返回row_num和column_num交叉处单元格中的值。 z 如果将row_num或者column_num设置为“0”或者“零”，INDEX函数会分别返回整行或者整列的值。要将返回的值用作数组，那么在行的水平单元格

37、区域要以数组公式的形式输入INDEX函数；如果要输入数组公式，那么，需要同时按【ctrl】、【shift】和【enter】键。 z Row_num和column_num参数必须指向数组中某个单元格，否则INDEX将返回一个错误值#REF！。 在引用形式的INDEX函数中： z 在Reference和area_num选择了特定区域后，row_num和column_num将选择一个特定的单元格，row_num1是该区域中的第一行， column_num1是该区域的第一列，依此类推。函数返回的引用是row_num和column_num的交叉点。 z 如果将row_num或者column_num设置为

38、0或者“零”，INDEX将分别返回整列或整行的引用。 z Row_num或者column_num和area_num必须指向引用的某个单元格；否则INDEX将返回错误值#N/A！。如果省略参数row_num和column_num，那么函数将返回area_num指定的引用区域。 【实际应用】 已知各产品的单价表，查询某产品的单价。 在单元格B2中输入函数表达式“=INDEX($A$2:$B$6,3,2)”，选择单价表区域中的第三行第二列的单价数据，也就是鼠标的单价，结果如图5.14所示。 图5.14 查询产品单价 【应用说明】函数的结果是一个引用，根据使用的公式，函数返回值可以做引用或值。因此该函

39、数可以与其他函数嵌套使用。 5.2.2 INDIRECT 函数：返回指定的引用 【功能说明】当需要更改公式中引用的某个单元格，而不更改公式本身，此时就可以用INDIRECT函数。函数返回指定单元格的值。 【语法表达式】INDIRECT(ref-text，a1) 【参数说明】 z Ref-text：表示单元格的引用。单元格的引用，可以是A1-样式的引用，也可以是R1C1-样式的引用，也可以是对定义为引用的名称或者对文本字符串单元格的引用。如果这个参数引用了不合法的单元格，那么函数将返回一个错误值#REF!。 z A1：代表一个逻辑值。用来指明包含在单元格ref-text中的引用类型。当参数a1值

40、为TRUE或省略时，ref-text代表是A1样式的引用；当参数a1为FALSE时，ref-text代表是R1C1样式的引用。 【使用说明】 z 如果参数ref-text引用的是另一个工作簿，也就是外部引用，那么外部源工作簿必须处于打开状态，否则函数INDIRECT会返回错误值#REF！。 z 如果使用“剪切”命令，或是插入或者删除行或列，使单元格发生了移动，那么单元格引用将被更新。 【实际应用】 已知各产品的单价表，根据其中单元格区域来查询单价。 在单元格B8中输入函数表达式“=INDIRECT(ADDRESS(2,2,1,TRUE)”，引用单元格B2中的单价数据33.80。在上面的函数中，

41、首先使用ADDRESS返回单元格的引用，然后使用INDIRECT返回单元格中的内容，如图5.15所示。 图5.15 查询结果 【应用说明】在Excel总，如果希望无论单元格上方的行是否被删除，或者单元格是否被移动，都在公式保持相同的单元格引用，可以使用 INDIRECT 工作表函数。 5.2.3 OFFSET 函数：调整新的引用 【功能说明】本函数以指定的引用为参照系，通过给定的偏移量，经过计算以得到新的引用。 【语法表达式】OFFSET(reference，rows，cols，height，width) 【参数说明】 z Reference：表示偏移量参照系的引用区域。这个参数必须是对单元格

42、或者相连单元格区域的引用，否则函数OFFSET会返回一个错误值#VALUE！。 z Rows：表示相对于偏移量参照系的左上角单元格，向上或者向下偏移的行数。如果将参数rows设为5，那么表示目标引用区域的左上角单元格比reference低5行。行数为正数时，表示在参照系起始引用的下方；行数为负数时，表示在参照系起始引用的上方。 z Cols：表示相对于偏移量参照系的左上角单元格，向左或者向右偏移的列数。如果将参数cols设为5，则表示目标引用区域的左上角的单元格比reference靠右5列。列数为正数时，表示在参照系引用的右边；列数为负数时，表示在参照系起始引用的左边。 z Height：表示

43、高度，也就是所要返回的引用区域的行数。Height必须是正数。 z Width：表示宽度，也就是所要返回的引用区域的列数。Width必须是正数。 【使用说明】 z 如果行数和列数得偏移量超出工作表边缘，函数会返回一个错误值#REF！。 z 如果省略了参数height或者width，那么函数在计算时会默认其高度和宽度与reference相同。 z 本函数只是返回一个引用，并非是移动选定的单元格或者是更改选定区域；OFFSET还可以与其它函数嵌套使用。 【实际应用】 某个销售公司统计了若干个销售人员的销售额，公司需要了解销售总额，但是统计是一个动态过程，需要随时计算销售总额，基础信息如图5.21所

44、示。 （1）在单元格B20中输入函数表达式“=SUM(OFFSET(B2,ROW()-ROW(B2)”，设置动态求解的公式，结果如图5.16所示。 图5.16 设置动态统计的公式 （2）添加新的销售记录。在原始记录后添加新记录，查看总和的变化，如图5.17所示。 图5.17 动态求解函数 【应用说明】函数返回的引用可以是一个单元格或者是一个单元格区域，而且可以指定需要返回的行数或者列数。 5.2.4 TRANSPOSE 函数：返回转置单元格区域 【功能说明】将以行单元格区域转置成一列单元格区域，或者反过来操作也可以。 【语法表达式】TRANSPOSE(array) 【参数说明】Array：表示需要进行转置的数组或者是工作表中的单元格区域。在这里，数组的转置表示将数组的第一行作为新数组的第一列，数组的第二行作为新数组的第二列，依此类推。 【使用说明】公式输入时，需要数组公式输入，否则会返回单个结果而不是数组。 【实际应用】 某销售公司统计了若干销售人员的销售额。现在需要将销售信息由列项转换为行项。 选择单元格A13J14，然后输入公式“=TRANSPOSE(A1:B10)”，然后同时按下CtrlShiftEnter键，得到的结果如图5.18所示。 图5.18 转换的结果 【应用说明】该函数在矩阵运算中应用十分广泛。