如何在Excel中有条件地匹配

debugcn 发表于 Dev

vk087

我有两个数据集：数据A和数据B。

数据A

A   B   C   D   Start_Date  End_Date
N   C   P   1   23-05-2015  27-05-2015
N   C   K   1   30-05-2015  07-06-2015
N   C   Ke  1   09-06-2015  28-06-2015
N   C   Ch  1   14-07-2015  25-07-2015
N   C   Th  1   29-06-2015  13-07-2015
N   C   Po  2   23-05-2015  27-05-2015
N   C   Kan 2   30-05-2015  08-06-2015

数据B

X       D     Date              A   B   C
444     1   09-07-2015          
455     1   20-07-2015          
1542    1   28-06-2015          
2321    1   21-07-2015          
2744    1   01-07-2015          
7455    2   25-05-2015          
12454   2   02-06-2015          
18568   2   24-05-2015          
28329   2   03-06-2015          
28661   2   31-05-2015

值是数据丢失裸我需要使用条件折射率匹配/ VLOOKUP使得柱d（数据B）与沿匹配填充它们Date(Data-B)，使得Start Date<= Date <=End Date.

所需的输出：

X       D     Date              A   B   C
444     1   09-07-2015          N   C  Th
455     1   20-07-2015          N   C  Ch
1542    1   28-06-2015          N   C  Ke
2321    1   21-07-2015          N   C  Ch
2744    1   01-07-2015          N   C  Th
7455    2   25-05-2015          N   C  Po
12454   2   02-06-2015          N   C  Kan
18568   2   24-05-2015          N   C  Po
28329   2   03-06-2015          N   C  Kan
28661   2   31-05-2015          N   C  Kan

转发爱德

概念验证

为了实现上述目的，我使用了AGGREGATE函数。这是一个执行类似于数组的计算的普通公式。下面的公式将返回与您的条件匹配的第一行的结果。

=INDEX(A$2:A$8,AGGREGATE(15,6,ROW($D$2:$D$8)/(($J2=$D$2:$D$8)*($E$2:$E$8<=$K2)*($K2<=$F$2:$F$8)),1)-1)

这假定您的表Data-A从A1开始，并包含1行作为标题行。该公式可以放在数据B中A下的第一个单元格中，并根据需要向下和向右复制。

UPDATE公式说明

聚合函数在其括号内对某些子函数执行数组计算。大约有19个不同的子功能。子功能14和15都是数组计算。这是一个很好的功能，因为它既可以做规则公式，又可以进行类似于数组的计算。

由于我想要满足您条件的第一行，因此我选择将小函数或子函数15用于第一个参数。基本上，我告诉聚合函数生成一个列表并按升序对其进行排序。

第二个参数的值为6，该值指示聚合忽略数组中产生错误的任何结果。如果我们可以取得我们不希望出现错误的结果，这将非常方便。

现在我们进入公式的数组部分。你可以把等式的这一个部分，并在相邻列高亮显示相应的行，请输入一个CONTROL+ SHIFT+ ENTER（CSE）的公式。只要您在顶部的单元格中执行此操作，数组公式就会传播到所选单元格的其余部分，并向您显示数组的结果。另外，请检查公式栏，以查看{}是否出现在您的公式周围。您无法手动添加{}。

{=ROW($D$2:$D$8)/(($J2=$D$2:$D$8)*($E$2:$E$8<=$K2)*($K2<=$F$2:$F$8))}

这将确定当前行，然后将其除以我们条件的结果。您也可以按照上述相同的方式在以下单独的列中尝试以下条件作为CSE公式，以查看其结果。

($J2=$D$2:$D$8)
($E$2:$E$8<=$K2)
($K2<=$F$2:$F$8)

它们会在检查每一行时单独为您提供TRUE或FALSE。现在有趣的事情是，这适用于excel公式，当您对布尔值执行数学运算时，它将0视为false，将其他任何数字视为TRUE。它实际上会将TRUE转换为1。您还将注意到，每个逻辑检查都由*分隔。在这种情况下，*就像AND运算符，因为只有当所有结果都为真时，您才能得到1的答案。（+就像OR运算符一样）

现在，如果您还记得从前6条所说的，请忽略所有错误。因此，任何不符合我们逻辑检查的行都将导致被0除，因为并非所有逻辑检查的结果都为TRUE或1。所有结束错误检查的检查都会被忽略。因此，现在在执行此操作之后，将仅满足我们条件的行号列表保留在聚合数组中。

逻辑检查之后，下一个参数为.1。在这种情况下，我们告诉聚合返回列表中的第一个数字，它是满足我们条件的第一行。如果我们想要第三个数字，则应改为，3。

因此，aggregate返回所需结果的第一行号。当它与INDEX函数配对时，何时可以使用结果告诉我们要查找INDEX函数的哪一行。在这种情况下，我们说过要查看索引A $ 2：A $ 8。聚合函数告诉我们索引中要减少多少行。如果索引从第1行开始，则无需执行任何操作。但是，由于存在标题行，因此我们需要通过对标题行减去1来调整聚合函数的结果（实际上，您需要在数据开头上方减去行号）。这就是为什么在聚合函数之后看到-1的原因。

现在，如果您注意该范围的锁定，您会发现我没有将A锁定在A $ 2：A $ 8中。我这样做是为了可以将公式复制到右侧，并且地址A列会像我一样更新。这仅起作用，因为您将各列保持相同的顺序。如果顺序更改了，我将把索引从1D数组更改为2D数组，并使用MATCH函数排列列标题。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-06-22

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章