Excel - 递归 VLookup

Question

最近了解了Excel的Ctrl+Shift+Enter数组公式，目前还在学习中。谈到我的问题，

SheetA:
Product        Code
S1             19875
S2             19834
S1             13575
S1             35675
S2             47875   



SheetB:
Code           Indent
19875          40  
19834          15
13575          22
35675          25
47875          20

我需要对给定产品名称的所有缩进进行求和。

例如：我需要S1的总缩进，

• 在SheetA上Vlookup，获取代码19875
• 获取40 Next Vlookup的缩进
• 在SheetB上执行vlookup，在Sheet A上
• ，获取代码13575在SheetB上使用13575进行Vlookup，获取22
• Next Vlookup 的缩进在工作表 A 上，获取代码 35675
• 使用 35675 在工作表 B 上进行 Vlookup，获取
• 40+22+25 之和的 25 缩进，返回 87

我可以通过 VBA 实现此目的，但我想知道这是否可以在使用 CSE 的 excel 函数中实现/数组公式。

编辑：

我在 Sheet2 中没有与 Sheet1 相同顺序的值。它们是完全随机的。我的 SheetB 将是随机的，如下所示：

SheetB:
Code           Indent
19834          40  
19875          15
47875          22
13575          25
35675          20

Answer 1

{=SUM(NOT(ISNA(MATCH((($A$2:$A$6="S1")*(B2:B6)),Sheet2!$A$2:$A$6,FALSE)))*(Sheet2!$B$2:$B$6))}

MATCH 的第一个参数解析为

{19875;0;13575;35675;0}

MATCH 解析为

{1;#N/A;3;4;#N/A}

您必须确保 SheetB 中没有零。 NOT ISNA 将这些转换为 TRUE 和 FALSE 并解析为

{TRUE;FALSE;TRUE;TRUE;FALSE}

最终的 SUM 看起来像这样

=SUM({TRUE;FALSE;TRUE;TRUE;FALSE}*{40;15;22;25;20})

更新

当列表处于不同顺序时，我无法找出单数组解决方案。我对 OFFSET 和 TRANSPOSE 的尝试要么给出了错误的答案，要么导致 Excel 崩溃。如果您可以使用辅助列，则可以将此公式放在第一张表的第三列中

=VLOOKUP(B2,Sheet2!$A$2:$B$6,2,FALSE)

，然后使用此数组公式对它们进行求和

{=SUM(($A$2:$A$6=A2)*($C$2:$C$6))}

Answer 2

如果 SheetA 和 SheetB 上的代码列相同（顺序相同），则可以使用简单的 SUMIF 函数。同样，如果 INDENT 数据位于 SheetA 上，您也可以使用数据透视表来快速计算总和。

我猜你的工作簿的设计不允许这样做。在这种情况下，我没有任何简单的解决方案，但我会再看一下。

Answer 3

您可以使用以下内容：
//Ola.S

表1
上校：ABC
产品代码 目标

列 C：=SUMIF($E$4:$E$8;B4;$F$4:$F$8)

表 2
科尔：EF
代码意向

表3
可旋转（列：A 和 C）

Answer 4

在表C上
如果 A1 有 S1，则在单元格 B1 中

=SUM((IF((Product=A1),Code1)=TRANSPOSE(Code2))*TRANSPOSE(Amt))

输入数组
在哪里
Code1 = SheetA 上从 Row2 开始的代码的动态范围
Code2 = SheetB 上从 Row2 开始的代码的动态范围

Answer 5

不需要 TRANSPOSE 调用。

尝试数组公式

=SUM(SUMIF(SheetB!A2:A6,IF(SheetA!A2:A6="S1",SheetA!B2:B6),SheetB!B2:B6))

假设 SheetB!A2:A6 中没有单元格，其计算结果为 FALSE。如果代码列都是数字（可能是一个有问题的假设），则无需使用数组条目即可完成此操作：

=SUMPRODUCT(SUMIF(SheetB!A2:A6,SheetA!B2:B6/(SheetA!A2:A6="S1"),SheetB!B2:B6))

假设 SheetB!A2:A6 中没有单元格计算结果为 #DIV/0!。