Excel公式 ：根据句子中的单词查找相应的数据

excelperfect

标签：Excel公式练习

本文所要解决的问题是，如何从一个句子中查找相应的单词，将使用该单词在表中查找并返回相应的数据。

如下图1所示，左侧的单元格区域中有一些描述性的句子，每个句子中都包含着一种代表资源类型的单词；右侧中的表列出了资源类型及对应的计费率。我们需要根据左侧的句子的描述从右侧表中提取其包含的资源类型的计费率。

图1

注：本文学习整理自chandoo.org。

如何编写公式？

先给出一个非常简洁的公式：

=LOOKUP(2^15,SEARCH($H$3:$H$8,B3),$I$3:$I$8)

分别在单元格B3中查找资源类型中的数据，得到“CM”在B3中的位置9，而其他没有找到返回#VALUE!，即SEARCH($H$3:$H$8,B3)的结果为数组：

{#VALUE!;#VALUE!;#VALUE!;9;#VALUE!;#VALUE!}

LOOKUP函数在该数组中查找2^15，返回仅有的数字9的位置4，结果返回I3:I8中的第4个位置处的值120。

下面是稍复杂一些的公式：

=VLOOKUP(INDEX($H$3:$H$8,MAX(IF(ISERROR(FIND($H$3:$H$8,B3)),-1,1)*(ROW($H$3:$H$8)-ROW($H$3)+1))),$H$3:$I$8,2,FALSE)

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

在上面的公式基础上稍作变化：

=INDEX($I$3:$I$8,SUM(NOT(ISERROR(SEARCH($H$3:$H$8,B3,1)))*ROW($H$3:$H$8))-2)

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

将上面的公式简化，得到公式：

=INDEX($I$3:$I$8,MATCH(0,FIND($H$3:$H$8,B3,1)*($J$3:$J$8),0))

这也是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

与上述公式的原理相同，可使用下面的数组公式：

=INDEX($I$3:$I$8,SMALL(IF(1*(ISNUMBER(FIND($H$3:$H$8,B3))),ROW($H$3:$H$8)-ROW($H$3)+1),1))

还可以使用SUMPRODUCT函数的公式：

=SUMPRODUCT(ISNUMBER(SEARCH($H$3:$H$8,B3))*($I$3:$I$8))

下面，该COUNTIF函数上场了，看下面这个逻辑简单但书写复杂的公式：

=IF(COUNTIF(B3,'*'&$H$3&'*')<>0,$I$3,IF(COUNTIF(B3,'*'&$H$4&'*')<>0,$I$4,IF(COUNTIF(B3,'*'&$H$5&'*')<>0,$I$5,IF(COUNTIF(B3,'*'&$H$6&'*')<>0,$I$6,IF(COUNTIF(B3,'*'&$H$7&'*')<>0,$I$7,IF(COUNTIF(B3,'*'&$H$8&'*')<>0,$I$8))))))

采用“穷举法”得到了结果。

下面的公式更简洁：

=INDEX($I$3:$I$8,MATCH(1,COUNTIF(B3,'*'&$H$3:$H$8&'*'),0))

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

使用SUMPRODUCT函数：

=SUMPRODUCT(COUNTIF(B3,'*'&$H$3:$H$8&'*'),$I$3:$I$8)

你还有什么简洁而优雅的公式吗？欢迎留言。

注：有兴趣的朋友可以到知识星球完美Excel社群下载本文配套示例工作簿。