查找所有table中的最大值_18.3 查找函数

18.3.1 CHOOSE函数

CHOOSE函数用于根据序号从列表中选择对应的内容。

语法：CHOOSE(index_num,value1,[value2],...)

参数说明：

index_num：用于指定所选定的数值参数。index_num必须是介于1到254之间的数字，或是包含1到254之间的数字的公式或单元格引用。如果index_num为1，则返回value1；如果为2，则返回value2，以此类推。例如=CHOOSE(2,"王军","李温诗","朱静","王强")，返回“李温诗”。

value1,value2,...：1到254个数值参数，CHOOSE将根据index_num从中选择一个数值或一项要执行的操作。参数可以是数字、单元格引用、定义的名称、公式、函数或文本。

【示例-1】图18‑6展示是销售员的评定表，标准为销售额在30000以下为“一般”，30000~50000为“良好”，50000以上为“优秀”。在C2单元格中输入以下公式，复制至C9单元格，即可评定各销售员级别。

=CHOOSE(IF(B2<30000,1,IF(B2>50000,3,2)),"一般","良好","优秀")

图18-6 评定销售员级别

第一个IF函数用于以30000为界限，如果单元格中的值小于30000,那么将返回1，也就是得到CHOOSE函数的参数index_num为1，那么将返回“一般”的结果；如果单元格中的值大于等于30000,那么将执行第2个IF函数的判断，如果值大于50000，那么将返回3，即CHOOSE函数的参数index_num为3，那么将返回“优秀”。否则将返回2，即CHOOSE的结果返回“良好”。

18.3.2 VLOOKUP( )函数

VLOOKUP函数是使用频率非常高的查询函数之一,它用来查找表格中第一列的值，然后返回同区域中其他列的值。

语法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value：要在表格或区域的第一列中查询的值。

table_array：需要查询的单元格区域，该区域中的首列必须要包含查询值，否则公式将返回错误值#N/A。

col_index_num：指定返回查询区域中第几列的值。

range_lookup：查找方式，如果为0或FASLE，用精确匹配方式，如果为TRUE或被省略，则使用近似匹配方式。

注意事项：

1、 如果查询区域中包含多个符合条件的查询值，VLOOKUP函数只会返回第一个查找到的值。

2、 采用近似匹配时，要求查询区域的第一列按升序排序，该方式下如果没有找到准确的匹配值，函数则会返回小于查询值的最大值。若查询值小于第一列的最小值，则返回错误值#N/A,采用精确查找无需按升序排列。

【示例-1】图18‑7所展示的是学生的成绩表，左侧是成绩清单，右侧是查询学生成绩区域。在F2单元格中输入以下公式，即可求得相应学生的成绩。

=VLOOKUP(F1,A1:C8,3,FALSE)

图18-7 VLOOKUP函数的基本用法

上例中VLOOKUP函数的第四个参数为FALSE，即为精确查找，FALSE可用0代替，以下公式与上述公式等效。

=VLOOKUP(F1,A1:C8,3,0)

【示例-2】图18‑8所展示是某企业的销售员的提成比率表，右侧是提成率的分配表：销量额0~500（不含500）提成率为0%，500~1500（不含1500）提成率为1%，1500~2500（不含2500）提成率为3%，2500~3500（不含3500）提成率为5%，含3500或以上提成率为6%。

在C2单元格中输入以下公式，可求出各销售员对应的提成率

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

图18-8 利用VLOOKUP函数模糊查询提取提成率

上例中因在某区间值查找，所以VLOOKUP函数的第四个参数为TRUE，即为近似（模糊）查找，采用近似查询，第四个参数可以省略，以下公式与上述公式等效。

=VLOOKUP(B2,$E$2:$F$6,2)

VLOOKUP函数在查询时，常见错误有以下2种：

1、查询区域或查询值有空格或含有其他不可见字符，导致查询错误。

如图18‑9所示，查询值为D4单元格中的“张明”，但查询区域中的A5单元格中的“张明”中间有多个空格，用户可将光标置公式编辑栏中，即可观察到空格所在位置。

图18-9 多余空格导致VLOOKUP函数查询错误

在Excel中，空格或其他不可见字符经常会导致查询值与查询区域的值不匹配，需将其删除。常见批量删除空格的方式为选中含空格的单元格区域，再使用替换命令，在【查找内容】处输入空格或在单元格中选中空格样本后，复制再粘贴在【查找内容】处，然后在【替换为】处留空，表示删除。点击【全部替换】即可删除选中单元格区域所有空格，如图18‑10所示。

图18-10 利用替换功能删除空格

2、数据类型不匹配，导致VLOOKUP函数查询错误

图18‑11展示是学生成绩表，现需要在F2单元格中通过E2单元格中学号，查询对应的学号的学生姓名，因查询单元格E2内的数字为数值型数字，而A列中的学号为文本型数字，在VLOOKUP函数查找过程中，文本型数字和数值型数字为不同的字符。会造成查询错误，用户需要将其两者数据类型改成一致后方可正确的查询，如图17-11所示。

图18-11 数据类型不匹配导致VLOOKUP查询错误

18.3.3 HLOOKUP( )函数

HLOOKUP的工作原理与VLOOKUP相似，它用于查找表格中第一行的值，在指定的表格行中返回相应的值。

【示例-1】图18‑12展示的是某企业销售提成比率表，在右侧是提成比率规则表（水平排列）。在C2单元格中输入以下公式，复制到C7单元格，即可以计算出各销售员的提成率。

=HLOOKUP(B2,$E$2:$J$3,2,TRUE)

图18-12 HLOOKUP函数的基本用法

18.3.4 LOOKUP( )函数

LOOKUP函数用于在某一行或某一列中查找指定的值，然后返回另一行或另一列区域中相同位置的值。

LOOKUP函数具有向量和数组两种语法形式。

向量形式语法：LOOKUP(lookup_value,lookup_vector,[result_vector])

参数说明：

lookup_value：表示要查找的值。

lookup_vector：查找的区域或数组，该区域或数组必须要按升序排列，否则可能会返回错误结果。

result_vector：返回查找结果的区域或数组，该区域必须大小必须与lookup_vector保持一致。

注意事项：

1、 如果在查找区域中找不到该值，则该函数会与查询区域中小于查询值的最大值进行匹配，如果查询值小于查询区域中的最小值，则返回#N/A。此外如果查询区域中有多个符合条件记录，LOOKUP函数仅返回最后一条记录。

2、 该函数支持忽略空值、逻辑值和错误值来进行数据查询。

【示例-1】图18‑13所示，A列是姓名，B列是员工编号（已按升序排列），在右侧已知员工编号的情况，需查询相应员工编号的姓名，在E2单元格中输入以下公式，即可求出员工编号所对应的姓名。

=LOOKUP(D2,B2:B8,A2:A8)

图18-13 LOOKUP函数向量形式的基本用法

数组形式语法:LOOKUP(lookup_value,array)

参数说明：

lookup_value：表示要查找的值。

array：表示要查找数据的区域或数组。

注意事项：

1、 查询区域必须要按升序排列，否则可能会返回错误结果。

2、 当查询区域中列数大于行数时，将在第一行内查找lookup_value参数值，如果列数小于行数，则在第一列内查找lookup_value参数值。

【示例-7】图18‑14所示，在G2单元格中输入以下公式，可查找F2单元格中姓名所对应的员工编号。

=LOOKUP(F2,A2:C8)

图18-14 LOOKUP函数数组形式的基本用法

本例使用LOOKUP函数的数组形式，通过在A2:C8区域中的第一列中查找指定姓名，然后返回该区域所对应最后一列中的员工编号。

LOOKUP的查询区域必须要按升序排列，否则查询有可能出现错误。在实际应用中，无论查询的数据用户是否进行升序操作，LOOKUP函数始终视为查询区域为升序排列。

18.3.5 MATCH( )函数

MATCH函数用于在单元格区域中搜索查找值，然后返回要查找的值在单元格区域中的相对位置。

语法：MATCH(lookup_value,lookup_array,[match_type])

参数说明：

lookup_value：要查找的值

lookup_array：查找的数值的区域，区域为一行或一列。

match_type：表示查找方式，用于指定精确查找或模糊查找，取值为-1、0或1。表18‑1列出了MATCH函数在参数match_type取不同值时的返回值。

【示例-1】如图18‑15所示，A列为编号，在D2单元格中输入以下公式，可获取D1单元格中编号所处查询区域中的位置。

=MATCH(D1,A2:A9,0)

图18-15 MATCH函数的基本用法

18.3.6 INDEX( )函数

INDEX函数用于返回单元格区域或数组中行列交叉值。

INDEX函数具有数组和引用两种语法形式。

数组形式语法：INDEX(array,row_num,[column_num])

参数说明：

reference：单元格区域或数组，如果单元格区域或数组只包含一行或一列，则相对应的参数row_num或column_num为可选参数。

row_num：返回值所在的行号

column_num：返回值所在列号

【示例-1】图18‑16所展示是一不同地区、不同尺寸的运费价目表，D9单元格为待查尺寸大小输入值，D10单元格为待查地区代码输入值，在D11中输入以下公式，即可获取待查尺寸及地区所对应的运费。

=INDEX(C3:J7,D9,D10)

图18-16 INDEX函数数组形式的基本用法

引用形式语法：INDEX(reference,row_num,[column_num],[area_num])

参数说明：

reference：表示要返回值的一个或多个单元格区域，如果为引用输入一个不连续的区域，必须将其用括号括起来。

row_num：返回值所在的行号

column_num：返回值所在的列号

area_num：要从多个区域中选择的区域代码，第一个区域编码为1，第二个区域编号为2,以此类推。

【示例-2】图18‑17展示是2个季度的销售清单表，B10为季度输入单元格，B11为姓名输入单元格，在B12单元格中输入以下公式，可提取指定季度下某员工的销量。

=INDEX((A3:B8,D3:E8),MATCH(B11,A3:A8,0),2,B10)

(A3:B8,D3:E8)为第一季度和第二季度的两块数据区域，利用MATCH函数查找姓名所在行数，“2”表示返回数据区域的第2列，B10为季度的输入单元格，1表示指定第一个参数中第一个区域（A3:B8）,如果是2表示第一个参数中第二个区域（D3:E8）。

图18-17 不同季度中提取销量

18.3.7 INDEX与MATCH函数运用

【示例-1】图18‑18展示是各城市不同月份的销售金额表，在C12单元格需要根据C10单元格中指定的三月和C11单元格指定的城市名，查找出相应的金额。在C12单元格中输入以下公式，即可求出指定月份和城市的销售金额。

=INDEX(C3:H8,MATCH(C11,B3:B8,0),MATCH(C10,C2:H2,0))

INDEX函数的第二参数利用MATCH函数定位“深圳”在B3:B8的第4行（即MATCH(C11,B3:B8,0)的结果返回4），同时INDEX函数的第三参数也利用MATCH函数定位“三月”在C2:H2的第3列（即MATCH(C10,C2:H2,0)返回的结果为3），然后INDEX返回C3:H8区域中的第4行第3列，即为指定三月份深圳的销售金额57。

图18-18 提取指定月份城市的销售金额

【示例-2】图18‑19展示为不同分店1月至3月销售金额表，在G2单元格输入以下公式，可求出总金额最高的分店名称。

=INDEX(A2:A8,MATCH(MAX(E2:E8),E2:E8,0))

此公式先用MAX函数找到E2:E8最大值为19400，然后利用MATCH函数定位在E2:E8中第5行。INDEX在A2:A8区域中查找第5行，即返回总金额最高的店铺为“龙华店”。

图18-19 提取总金额最高的店铺

18.3.8 OFFSET函数

OFFSET函数用于以指定的引用为参照，通过给定偏移量得到新的引用。返回的引用可以是单个单元格或是单元格区域。同时可以指定要返回区域的行数和列数。

函数基本语法如下。

OFFSET(reference,rows,cols,[height],[width])

参数说明：

reference：作为偏移量参照的起始引用区域。

rows：相对于偏移量参照系的左上角单元格，上（下）偏移行数。行数为正数时，代表在起始引用的下方。行数为负数时，代表在起始引用的上方。如为0表示不偏移。

cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数为正数时，代表在起始引用的右边。列数为负数时，代表在起始引用的左边。如为0表示不偏移。

height：表示要返回的引用区域的行数。

width：表示要返回的引用区域的列数。

图18‑20展示了OFFSET的工作原理

图18-20 OFFSET函数的工作原理

图18‑21展示了OFFSET的常用参数的设置。

图18-21 OFFSET函数参数设置

H2单元格中公式“=OFFSET(B1,3,0)”返回结果为3，原理为以B1单元格为参照点，向下偏移第二个参数的3行（B2单元格为偏移的第1行），到达B4单元格，第三个参数为0，即不向右偏移，结果还是B4单元格，取B4单元格中的值为3。

H3单元格中公式“=OFFSET(B1,0,3)”返回结果为“2019年”，原理为以B1单元格为参照点，向下偏移第二个参数的0行（即保持为B1单元格），第三个参数为3，即向右偏移3列（C1单元格为偏移的第1列），达到E1单元格，取E1单元格中的值为“2019年”。

H4单元格中公式“=OFFSET(B1,3,3)”返回的结果为24，原理为以B1单元格为参照点，向下偏移第二个参数的3行，到达B4单元格，第三个参数为3，即向右偏移3列，到达E4单元格。取E4单元格中的值为24。

H5单元格中公式“=SUM(OFFSET(A2:A8,0,2))”返回的结果为77，原理为以A2:A8单元格区域为参照点，向下偏移第二个参数的0行（即不偏移），第三个参数为2，即向右整体偏移2列（即C2:C8单元格区域），然后利用SUM函数对C2:C8区域求和，结果为77。

H8单元格中公式“=SUM(OFFSET(A1,4,2,3,2))”返回的结果为93，原理为以A1单元格为参照点，向下偏移第二个参数的4行，第三个参数为2，即向右偏移2列，到达C5单元格，然后以C5单元格向下扩展3行2列，该区域为C5:D7,再利用SUM函数对C5:D7区域求和，结果为93。

【示例-1】图18‑22展示是某企业的出入库查询表，E3单元格中可选择入库数量或出库数据，右侧为相应查询的起始月份和终止月份，在F7单元格输入以下单元格，即可达到查询目的。

=SUM(OFFSET(A1,F3,MATCH(E3,B1:C1,0),G3-F3+1))

该公式先利用MATCH函数定位E3单元格中的“出库数量”在B1:C1区域的2列。再利用OFFSET函数，以A1单元格为参数点，向下偏移F3单元格中第4行（4行对应A5单元格中的2018年4月），向右偏移2列，到达C5单元格，然后向下扩展3行（即G3-F3+1，该表示为G3单元格中的终止月份减F3单元格中的起始月份，加1表示包含起始月份），最后利用SUM函数对扩展的区域C5:C7求和，返回结果为117。

图18-22 出入库查询表