ch3-5查找引用类函数.doc

3.12 查找及引用函数在Excel同一工作簿的不同工作表之间进行数据传递和信息查询有两种最有效的方法。一种是利用命名进行数据和信息传递，因为在Excel的不同工作表之间，名字是共享的，在一个工作表中定义的名字可在不同工作表之间直接引用。另一种方法就是用查找类函数把一个工作表中的数据查询到另一工作表中，而且可以在工作表之间进行信息的自动传递，所以查找及引用类函数用处极大。查找引用函数能通过地址、行、列对工作表的单元格进行访问，也可通过这些函数从单元格的地址中求出其所在的行或列，进而查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。1ADDRESS 函数ADDRESS函数用指的行号和列标，建立文本类型的单元格地址。其用法如下：ADDRESS(row_num, column_num, abs_num, A1, sheet_text)其中，row_num在单元格引用中使用的行号；column_num在单元格引用中使用的列标；abs_num指明返回的单元格引用类型。可能返回的引用类型如表3-9所示。表3-9 ADDRESS返回的引用类型abs_num返回的引用类型1（或省略）绝对引用2绝对行号，相对列标abs_num返回的引用类型3相对行号，绝对列标4相对引用A1用以指明A1或R1C1引用样式的逻辑值。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用；如果A1为FALSE，函数ADDRESS返回R1C1样式的引用。sheet_text为一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。例如，ADDRESS(6,3) = “$C$6”，ADDRESS(6,1,2) =A$6，ADDRESS(2,3,1,FALSE, Book1 Sheet1)=Book1Sheet1!R2C3，ADDRESS(2,3,1,true, Book1Sheet1)=Book1 Sheet1!$C$2。2AREAS函数计算指定参数中包含的区域个数（区域表示连续的单元格组或某个单元格）。其用法如下：AREAS(reference)reference为某一单元格或单元格区域的引用，也可以是多个单元格区域的引用。如果需要将几个引用指定为一个参数，则必须用括号将其括起来，以免Excel 将逗号作为参数间的分隔符。例如，AREAS(D2:F10)=1，AREAS(A2:D10,F5,G6:I10)=3。如果名为Pri的引用区域为B1:D4，B2及E1:E10，则AREAS(Pri)=3。3CHOOSE函数该函数利用索引从参数清单中选择需要的数值。其用法如下：CHOOSE(n, v1, v2, .)n用以指明待选参数序号。n必须为129之间的数字或者是包含数字129的公式或单元格引用。如果n为1，函数的值就为v1；如果为2，函数返回v2，以此类推。如果n小于1或大于列表中最后一个值的序号，函数返回错误值：#VALUE!。如果n为小数，则在使用前将被截尾取整。 v1，v2，为129个数值参数，可以是数字、单元格引用，或者已定义的名称、公式、函数或文本。说明：CHOOSE函数的数值参数不仅可以为单个数值，也可以为区域引用。例如，公式“SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10) ”相当于函数“SUM(B1:B10)”。CHOOSE函数先被计算，返回引用B1:B10。然后SUM函数用B1:B10 进行求和计算。即CHOOSE函数的结果是SUM函数的参数。例如，CHOOSE(5,一月,二月,三月,四月,五月,六月,七月)=五月；SUM(A1:CHOOSE(3,A10,A20,A30)=SUM(A1:A30)；如果B5包含3，则CHOOSE(B5, Nails,Screws,Nuts,Bolts)=Nuts；如果A10-3=3，则CHOOSE(A10-3,1st,2nd,3rd, Finished)=3rd。4INDIRECT函数该函数返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用该函数。其用法如下：INDIRECT(ref_text, A1)ref_text为对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用。如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!。A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果A1为TRUE或省略，ref_text被解释为A1样式的引用。否则ref_text被解释为R1C1样式的引用。例如，若单元格A1包含文本“B2”，且单元格B2包含数值1.333，则INDIRECT($A$1)=1.333；如果需要将单元格A1中的文本改为“C5”，而单元格C5中包含数值45，则INDIRECT($A$1)=45；如果B3包含文本“George”，而定义为George的单元格包含数值10，则INDIRECT($B$3)=10。注意：如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。否则，INDIRECT函数返回错误值 #REF!。如果在公式中对某个特定单元格进行了引用，然后插入或删除行或列，使公式中引用的单元格发生了移动，公式将被重新计算。如果需要使得无论单元格上方的行是否被删除或是单元格是否移动，都在公式保持相同的单元格引用，可用INDIRECT函数。例如，如果需要始终对单元格A10进行引用，可用函数：INDIRECT(A10)。这样不管工作表的单元格怎样变化，该函数始终引用A10单元格。5LOOKUP函数LOOKUP函数的功能是：从给定的向量（单行区域或单列区域）或数组中查询出需要的数值。LOOKUP函数有两种语法形式：向量和数组。向量形式的用法如下：LOOKUP(value, r1, r2)该函数在r1所在的行或列中查找值为value的单元格，然后返回r2中与r1同行或同列的单元格中的值。value是要查找的数值，可以为数字、文本、逻辑值或包含数值的名称或引用。r1为只包含一行或一列的区域，其值可以为文本、数字或逻辑值。r2为只包含一行或一列的区域，其大小必须与r1相同。注意： r1中的数值必须按升序排序：.，-2，-1，0，1，2，AZ，FALSE，TRUE；否则， LOOKUP函数不能返回正确的结果。文本不区分大小写。如果LOOKUP函数找不到value，则查找r1中小于或等于value的最大数值。如果value小于r1中的最小值，LOOKUP函数返回错误值#N/A。LOOKUP的数组形式如下：LOOKUP(value, array)这种形式在数组array中查找值为value的值，如果找到就返回该值在数组中的下标。如果LOOKUP找不到value，则使用数组中小于value的最大数值。说明：array中的数据必须按升序排列：.，-2，-1，0，1，2，.，AZ，FALSE，TRUE；否则，函数LOOKUP不能返回正确的结果。文本不区分大小写。请看下面的例子：LOOKUP(C,a,b,c,d;1,2,3,4)=3LOOKUP(bump,a,1;b,2;c,3)=26VLOOKUP函数查看数组最左边一列值，若找到要查找的值，则返回指定单元格的值。该函数用法如下：VLOOKUP(value, table, n, range_lookup)VLOOKUP函数在数据表（或单元格区域）table的第一列中查找值为value的数据，如果找到，就返回该表（或区域）中第n列且值与value 同行的单元格中的数据。当range_lookup为TRUE时，table第一列数据必须按升序排列，否则找不到正确的结果；当range_lookup为FALSE时，table的第一列数据不需要排列。table的第一列中的数值可以为文本、数字或逻辑值，不区分文本的大小写。 n为table_array 中待返回的匹配值的列序号。n=1时，返回 table 第一列中的数值；n=2时，返回 table_array 第二列中的数值；以此类推。说明：如果VLOOKUP函数找不到value，且range_lookup=TRUE，则返回小于等于value的最大值。如果value小于table第一列中的最小值，VLOOKUP函数返回错误值#N/A。如果VLOOKUP函数找不到value且range_lookup=FALSE，VLOOKUP函数返回错误值#N/A。图3.37是一个利用VLOOKUP函数查找各种蔬菜在不同地区的单价的例子。在如图3.37所示的工作表中：VLOOKUP(黄瓜,B3:F11,3,FALSE)=3.0，与说明的第3条相符。VLOOKUP(黄瓜,B3:F11,3,TRUE)=3.2，第4个参数为TRUE，找到小于“黄瓜”的最大数据（按汉语拼音字母序）为“大白菜”，所以返回“大白菜”行第3列的数据，下面的函数与此同理。VLOOKUP(黄瓜,B3:F11,4)=0.9。VLOOKUP(4.0,C3:F11,3)=3.9，在C3:F11区域中的C列中查找4.0，找到“韭菜”行数据，返回该区域的第3列即E10。VLOOKUP(1,C3:F11,2,true)=1.6。VLOOKUP(0.3,C3:F11,2,true)=#N/A。图3.37 利用VLOOKUP函数查找蔬菜价格7HLOOKUP函数在数组的第一行寻找特定值，并返回找到的单元格的值。HLOOKUP(value, table, n, range_lookup)该函数在数据清单（或区域）table的第一行中查找值为value的单元格，如找到，则返回该列第n行单元格的值。value为需要在数据表第一行中进行查找的数值，它可以是数值、引用或文字串。table是需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。它的第一行的数值可以为文本、数字或逻辑值如果range_lookup=TRUE，则table_array的第一行的数值必须按升序排列：-2，-1，0，1，2，AZ，FALSE，TRUE；否则，HLOOKUP函数将不能给出正确的数值。如果 range_lookup为FALSE，则table_array不必进行排序。说明：如果HLOOKUP函数找不到value，且range_lookup=TRUE，则使用小于等于value的最大值。如果HLOOKUP函数小于table第一行中的最小数值，HLOOKUP函数返回错误值#N/A!。图3.38是HLOOKUP函数的应用举例。该例是HLOOKUP函数的一种典型应用，从表头查找（表的首行）需要的信息。假设要查找重庆和西安的大白菜的单价，首先应确定查找数据的区域为B2:F11，其中第二行为首行，要比较的值应在该行（即HLOOKUP函数的参数value应在该行）；其次，确定返回值所在的行，大白菜在B2:F11区域的第6行，所以查找重庆大白菜价格的公式为“= HLOOKUP(重庆,$B$2:$F$11,6,FALSE)=6”查找成都大白菜的价格公式为“HLOOKUP(成都,$B$2:$F$11,6,FALSE)=0.9”。这两个公式中FALSE的意义请参考前面的说明理解。图3.38 用HLOOKUP函数查各地的蔬菜单价8MATCH函数查找在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。MATCH(value, array, type)value是在array中查找的数值。value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。array是可能包含要查找的数值的连续单元格区域，array可以为数组或数组引用。type为数字-1，0或1。表3-10给出了type的几种查找方式。表3-10 MATCH函数的查找方式取值函 数 功 能-1array 必须按降序排列，查找大于或等于 value的最小数值0array 不必排序，查找等于value的第一个数值1array 必须按升序排列，查找小于或等于value的最大数值说明：MATCH函数返回array中目标值的位置，而不是数值本身。例如，MATCH(b,a,b,c,0) 返回 2，即b在数组a,b,c 中的相应位置。查找文本值时，MATCH函数不区分大小写字母。如果MATCH函数查找不成功，则返回错误值#N/A。如果match_type=0且lookup_value为文本，lookup_value可以包含通配符、星号（*）和问号（?）。星号可以匹配任何字符序列，问号可以匹配单个字符。 图3.39是一个工资与税率的关系表，下面是MATCH函数的几种用法举例。图3.39 收入/税率简表注意：C2:C8包含的文本格式为百分数。在如图3.39所示的工作表中：MATC