14.4 查找与引用函数综合应用 - 下

一、使返回错误值以简化公式

例 提取一二三级科目名称

在下图所示的科目代码表中，A列为科目代码，B列为对应科目名称。A列科目代码中长度为4的为一级代码，长度为6的为二级代码，长度为8的为三级代码。要求根据A列代码分别提取一级、二级和三级科目名称到D:F列。

选中D2:F2单元格区域，直接输入以下数组公式，按< Ctrl+Shift+Enter >组合键。向下复制公式即可完成。

{=IFNA(VLOOKUP(LEFT(A2&" ",{4,6,8}),$A:$B,2,),"")}

“LEFT(A2, {4,6,8})”部分从A列科目代码中分别从左面第一个字符开始取4、6、8字符作为VLOOKUP函数的第一参数。VLOOKUP函数在D列、E列、F列分别查找4、6、8位科目代码并返回对应的B列科目名称。由于二级科目代码和三级科目代码前4位是一级科目代码，因此，在E列和F列也会返回对应一级科目代码对应的科目名称。

为使E列和F列不再返回对应的一级科目名称，可以使用以下公式，将VLOOKUP函数部分第一参数加4个空格，使VLOOKUP返回错误值。

VLOOKUP(LEFT(A2&" ",{4,6,8}),$A:$B,2,)

公式中“A2&" “”部分的结果为“1001 ”， 在D2单元格中, VLOOKUP函数的第一参数提出“1001 ”左侧的4个字符“1001”;在E2单元格中提取出“1001 “左侧6个字符“1001”;在F2单元格中则提取出“1001 “左侧的8个字符“1001 “。

在D2单元格中，VLOOKUP函数返回与科目代码“1001”对应的科目名称;而在E2和F2单元格中，查找含有空格的科目代码时则返回错误值。

公式在E列查询时，如果A列为4位的一级科目代码，公式将返回错误值。6位和8位的科目代码均返回二级科目代码对应的代码名称。

公式在F列查询时，只有A列代码为8位的三级代码时可以正常返回对应代码名称，A列4位和6位科目代码均返回错误值。

最后用IFNA函数将错误值屏蔽显示为空文本。

二、多条件提取不重复值

例 按产品号和型号不重复值统计金额

如下图所示，A~D列为某厂商产品不同日期销售金额明细，要求在F:H列根据不同产品号和型号统计销售总金额。

在F2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键，填充到F2:G13单元格区域。

{=IFERROR(OFFSET(C$1,SMALL(IF(MATCH($B$2:$B$13&"|"&$C$2:$C$13,$B$2:$B$13&"|"&$C$2:$C$13,0)=ROW($B$2:$B$13)-1,ROW($B$2:$B$13)-1),ROW(B1)),),"")}

以F2单元格公式为例，说明如下。

"MATCH($B$2:$B$13&"|"&$C$2:$C$13,$B$2:$B$13&"|"&$C$2:$C$13,0)=ROW($B$2:$B$13)-1”部分将产品号和型号两个条件用连接符“&”连接在一起，然后判断其自上到下出现的位置是否等于“ROW($B$2:$B$13)-1”。如果相等，公式返回TRUE，表示产品号和型号为自上到下第一次出现。返回值如下。

{TRUE;TRUE:FALSE;TRUE:TRUE:FALSE;TRUE;FALSE;TRUE:TRUE:TRUE:FALSE}

IF函数判断MATCH函数返回值为TRUE时记录其行号-1的返回值，否则返回FALSE。返回结果中的数字表示产品号和型号在B2：C13单元格区域中自上到下第一次出现时的位置。返回值如下。

{1;2;FALSE;4;5;FALSE;7;FALSE；9;1；11;FALSE }

SMALL函数返回符合条件的位置号1。OFFSET函数以B1单元格为引用基准,向下偏移1行0列返回第一个产品号。

当公式向右复制到G列时，OFFSET函数返回第一个型号。

当公式向下复制时，SMALL函数会逐个从小到大输出符合条件的位置号，OFFSET函数会在F列和G列分别返回符合条件的产品号和型号。

当公式向下复制行数超过以产品号和型号两个条件为基础判断的不重复值个数时公式将返回错误值#NUM!，用IFERROR函数将其屏蔽，显示为空文本。

在H2单元格中输入以下公式，复制到H2:H13单元格区域返回同时满足F列和G列产品号及型号条件的“总金额”合计。

=SUMPRODUCT(($B$2:$B$13=$F2)*($C$2:$C$13=$G2),$D$2:$D$13)

三、标记连续符合条件的单元格

例 标记所有连续5个大于10的数字

如下图中，A1:O1单元格区域为随机数字，要求在A3:O3单元格区域将A1:O1单元格区域中所有连续5个大于10的数字都标记出来。例如，E1单元格的数字为11,E1:I1单元格区域5个数字都大于10，因此E1单元要标记。D1单元格的数字为9，在包含D1单元格的所有连续5个单元格区域A1:E1、B1:F1、C1:G1、D1:H1中没有任何一组5个单元格数字都大于10，因此D1不需要标记。

在A3单元格中输入以下公式，并向右复制到03单元格。

=COUNT(0/(COUNTIF(OEESET(A1,,(-4,-3,-2,-1,0),1,5),">10")=5))

公式向右复制时A1会依次变成B1，C1，D1，··，O1。

返回值大于0的表示对应第一行的单元格包含在某组连续5个单元格数字都大于10的单元格区域中。

“OFFSET(A1,(4,-3,-2,-1,0),1,5)”部分以A1 作为引用基准，偏移0行，向左分别移4列、3列、2列、1列、0列，取1行5列，生成包含A1单元格在内的5个单元格区域引用。当OFFSET函数返回的引用超出工作表边缘时返回错误值#REF!。

COUNTIF函数判断生成的5个单元格区域引用中的数字是否大于10。如果生成的某个单元格区城引用中大于10的单元格数量等于5，表示OFFSET通数的第一参数单元格包含在该组连续5个单元格数字都大于 10的单元格区域中。

以F1 单元格为例，“COUNTIF(OFFSET(F1,(4,3,-2,1,0),1,5),">10” 部分返回值如下

{3,4,4,5,5}

表示B1:F1 单元格区域中大于 10的数字个数是3，C1:G1单元格区域中大于10的数字个数是4，D1:H1 单元格区域中大于 10的数字个数是4，E1:1 单元格区域中大于10的字个数是5，F1:J1 单元格区域中大于 10的数字个数是5。

“COUNTIF(OFFSET(F1,(-4,-3,-2,-1,0),1,5),">10")=5”部分表示COUNTIF函数返回的组元素等于5返回TRUE，否则返回FALSE。返回值如下。

（FALSE，FALSE，FALSE，TRUE，TRUE）

用0除以COUNTIF函数返回数组，返回值如下。

(#DIV/0!,#DIV/0!,#DIV/0!,0,0)

最后，COUNT函数返回数组中数字的个数为2，表示F1单元格包含在两组连续5个单元数字都大于10的单元格区域中。