excel - 如果在列表中找到前缀,如何查找?
问题描述
你好。如果在“con no”(A 列)中找到任何“PrefiX”(G 列),我如何在 B 列得出“公司名称”(H 列)的返回值。
所需的结果样本,如 B 列所示。
样品: 6200 11113 = DD CN 1234 = BB
谢谢
解决方案
=INDEX($H:$H,AGGREGATE(15,6,ROW($G$1:$G$7)/(--(FIND($G$1:$G$7,$A2)=1)*--(LEN($G$1:$G$7)>0)),1),1)
将其分解,从 H 列(公司名称)中INDEX
检索第N项。为了找到N的值,我们使用函数AGGREGATE
AGGREGATE
是一个奇怪的函数——它让我们可以使用MAX
or之类的东西LARGE
,SUM
而忽略任何错误值。在这种情况下,我们将它用于SMALL
(第一个参数,15
),同时忽略错误值(第二个参数,6
)。我们想要最小的值,所以第四个参数是1
。(如果我们想要第二小的,那就是2
,依此类推)
=INDEX($H:$H,AGGREGATE(15,6, <SOMETHING> ,1),1)
所以,我们现在需要的只是一个要比较的值列表!为了让事情稍微简单一点,我将在这里为您分解这段代码:
ROW($G$1:$G$7) / (--(FIND($G$1:$G$7,$A2)=1) * --(LEN($G$1:$G$7)>0))
这有3个部分。第一个,ROW($G$1:$G$7)
是我们想要检索的实际值——这些将是与您的值匹配的每个前缀的行号。然而,就其本身而言,它将是所有的行号。由于我们正在跳过错误,我们希望任何与前缀不匹配的行都抛出错误。最简单的方法是除以零
在开始时--(FIND($G$1:$G$7,$A2)=1)
,--(LEN($G$1:$G$7)>0)
我们有一个双重否定。True
这是将and转换False
为1
and的快速方法0
。只有当两个测试都是True
时,我们才不会除以0
,如下表所示:
A | B | A*B
1 | 1 | 1
1 | 0 | 0
0 | 1 | 0
0 | 0 | 0
首先从第二个测试开始(它更容易),我们有LEN($G$1:$G$7)>0
- 基本上“不要看空白单元格”。
另一个测试 ( FIND($G$1:$G$7,$A2)=1
) 将搜索 Con No 中的 Prefix,并返回找到的位置(#VALUE!
如果不是,则返回错误)。然后我们检查“这是在位置 1” - 换句话说,“这是在 Con No 的开头,而不是在中间”。我们不想说Con NoCNQ6060
是AA 公司的一部分而不是BB 公司的一部分!
因此,如果前缀位于 Con No 的开头,并且它不是空白的(因为在每个数字和字母之前、之后和之间都有无限量的 Nothing),那么我们将它添加到我们的列表中行。然后我们取最小的行(即最接近顶部 -如果您想要最接近底部,请更改AGGREGATE(15
为!),并使用它来获取公司名称AGGREGATE(14
推荐阅读
- java - 如何存储和检索数据
- glsl - glsl 中是否有快速数组块复制功能或类似功能?
- sql - 在 Oracle 中透视结果
- file-upload - 空手道在帖子上膨胀二进制文件
- r - 从引导 R 返回两个值
- function - 供应链的 Solidity 合约
- magento2 - Magento 2.2.5 按价格排序:从低到高,从高到低
- json - 如果另一个键等于某个值,则 Json 模式检查键是否存在(草案 4)
- pandas - Calculate new columns in a Pandas DataFrame using row number
- mysql - mySql-从文件导入数据时出错