☰

返回多个 vlookup 的总和

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
返回多个 vlookup 的总和

好的，我有一个问题，我想用一个公式解决它。

我有一组表格：

我的桌子

苹果的 NFPI（水果数量 x 百分比强度）是多少？

我需要扫描表 3 的 G 列和表 1 的 B 列，以查看哪些行中包含某种 Apple。

然后我需要找出每次出现苹果时水果的总数：

(Number of trees * Tree fruit) + ground fruit

并在表 2 中找到适当百分比强度的水果总数，并将所有出现的次数加起来

因此它看起来像：

(1 * 3) + 2 = 5 Red Apples. NFPI of 5 fruits at 97% intensity = 1.67  
(2 * 2) + 0 = 4 Green Apples. NFPI of 4 fruits at 98% intensity = 2  
(1 * 2) + 1 = 3 Yellow Apples. NFPI of 3 fruits at 97% intensity = 1  
**total NFPI = (1.67 + 2 + 1) = 4.67**

我试图在一个公式中完成所有这些操作。我尝试使用的公式将 vlookup 合并到数组公式中，但它总是给出错误的答案。这是我尝试过的公式：

{=SUM(IF(ISERROR(MATCH(G12:G16,B3:B5,0))=FALSE,VLOOKUP(H12:H16,F4:K8,(I12:I16*J12:J16)+K12:K16+1),0))}

我不知道为什么它不起作用，也不知道如何让它起作用。我想也许 SUMPRODUCT 公式可以帮到我，但我也想不通。我知道我可以找到每个苹果条目的 NFPI，并将其输入到该列旁边的另一列中Ground Fruit，然后只需在该列底部放置一个 SUM 公式即可将其全部加起来，但如果可能的话，我会尝试不这样做来求和。

任何帮助，将不胜感激！

答案1

编辑：@ScottCraner 最近的一个回答使用了“取消引用”INDEX()公式，他的回答让我决定再次尝试解决这个问题。我之前尝试过（未成功）的相同方法在第二次尝试时完美地奏效了。我将在下面描述解决方案。

关于取消引用的 INDEX() 公式的背景：

Sean，您尝试使用数组公式来完成需要做的事情，真是令人钦佩。您遇到的问题与 Excel 处理数组的方式有关。有些公式可以使用数组作为参数，有些则不能。

我对此进行了一些深入研究，并了解到一些关于在 ExcelINDEX()公式中使用数组的非常奇怪、神秘的事情，这些事情我以前并不知道。要了解这个公式的工作原理，让我们从最后开始。

您的公式要做的最后一件事是将二维数组（即表 2）中的三个（不连续的）值相加。

INDEX(array,row_num,col_num)可以从二维数组返回单个值，也可以返回整列或整行。看起来應該能够返回值列表。那么让我们测试一下。

此公式（在理想情况下）将返回您在表 2 中寻找的总和：

=SUM(INDEX(G4:K8,{3,2,3},{5,4,3}))

这应该添加第 3 行、第 5 列加上第 2 行、第 4 列以及第 3 行、第 3 列的元素。但是它没有，它仅返回 1.67，这是引用的第一个元素。

在线搜索可以找到参考资料（包括一个在 StackOverflow 上）INDEX()将返回一个数组，但前提是你取消引用公式（这是“奇怪”的部分）。“神秘”的部分是如何做到这一点。这是“取消引用”的公式：

=SUM(INDEX(G4:K8,N(IF(1,{3,2,3})),N(IF(1,{5,4,3}))))

此公式给出正确答案：4.67。

在公式中，IF()将 1 视为True，因此它返回数字数组，N()如果它们是数字，则返回数字数组。至于为什么需要 IF() 和 N() 才能使公式正常工作，谁也说不准。在斯科特公式，他还必须将他的数组（这是一个范围引用）乘以 1。

但是，现在我们有了可以给出正确答案的公式。希望我们所要做的就是使用其他数据将数组常量替换为计算数组。

新信息从这里开始。

对于上述公式中的 row_num ，我们需要与所选水果品种相关的 F4:F8 中百分比强度的位置。首先，我们将获取表 3{3,2,3}中苹果的位置数组：G12:G16

=MATCH(B3:B5,G12:G16,0)

这是一个数组公式，必须用 来输入CTRLShiftEnter，而不能仅仅用Enter。

此公式在表 3 的 G 列中查找表 1 中的苹果品种列表，并返回它们的位置数组。

如果您在公式栏中选择公式并按 F9，您将看到价值公式的值为数组{1,3,4}，即表 3 的 G 列中苹果的位置。

现在我们需要与这些职位相关的 PI。此INDEX()公式查找 H 列并使用上述数组作为 row_num。此处，row_num 必须“取消引用”：

=INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

此公式返回数组{0.97,0.98,0.97}，即苹果的 PI。到目前为止一切顺利。接下来，我们将该数组用作公式中的查找值，MATCH()该公式查找 F4:F8，即表 2 的 PI 索引：

=MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0)

此公式返回数组{3,2,3}，这些是最终公式所需的 row_num。

接下来我们需要 col_num {5,4,3}，即每种苹果品种的水果总数。我们将从表 3 中获取此数据，但首先我们需要计算所有水果品种的水果总数。此（计算）数组是这些总数的列表：

(I12:I16*J12:J16)+K12:K16

为了获取苹果品种的水果总数，我们将在中使用该数组INDEX()，并使用与之前相同的（取消引用的）row_num：

=INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

此公式返回数组{5,4,3}，这些是最终公式所需的 col_num。

综上所述，NFPI 列表如下：

=INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

此公式返回数组{1.67;2;1}。这些是苹果的 NFPI，现在我们只需将它们加起来。

但还不是时候，首先要解决一个小问题。表 3 中可以找到所有三种苹果品种，但橙子品种却不是这样。上面的公式返回的数组中没有#N/A小橙子品种。这不会造成任何问题，直到需要将值相加时。

因此，在求和之前，我们先#N/A's用公式将转换为 0。IFERROR()这是最终公式：

=SUM(IFERROR(INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(C3:C5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(C3:C5,G12:G16,0))))),0))

此公式对于苹果返回 4.67，对于橙子返回 5.75。

Sean，希望这仍然有用。抱歉这么久才回复。

答案2

编辑请参阅本页上我的其他答案。事实证明，INDEX()CAN 从数组中返回 (不连续) 值的列表。

这是完成您要做的事情的另一种方法，尽管公式结果是非常长的。

以下公式查找表 2 中的三个 NFPI，并将它们相加。

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B4,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B4,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B5,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B5,G$12:G$16,0)),G$3:K$3,0))

怎么运行的： 总和中的每个项都是一个INDEX()函数，通过指定 row_num 和 column_num 返回表 2 中的一个元素。对于第一个项，首先使用在表 3 中INDEX()查找B3（红苹果）的函数找到 row_num，然后返回相关的 % 强度：

INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0))

然后在 a 中使用此 PIMATCH()返回表 2 的正确行：

MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0)

首先找到正确的水果数量，然后找到 column_num。我在您的表 3 中添加了一个额外的列，用于计算 中的水果总数L12:L16。如果这不可行，您可以通过替换L$12:L$16来“即时”计算水果数量(I$12:I$16)*(J$12:J$16)+(K$12:K$16)：

INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0))

至于 row_num，它用于MATCH()返回表 2 的正确行：

MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0)

现在已经知道了“红苹果”的行和列，总和中的第一个项是：

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))

青苹果和黄苹果的其余两个项是相同的公式，但是用B4和B5替换B3。

我希望这会有所帮助并祝你好运。

答案3

根据 OP 提供的信息和示例公式，我找到了提取水果名称、水果总数和 NFPI 的解决方案。

在此处输入图片描述

单元格 A12 中的公式用于提取水果类型：

=IFERROR(VLOOKUP(A2,$D$2:$H$6,1,0),"")

D12 中的公式生成苹果表：

{=VLOOKUP(A2,$D$2:$H$6,{1,2,3,4,5},FALSE)}

注意：完成公式进入然后将公式拖至 H 列并按F2然后完成这个公式Ctrl+Shift+Enter然后将其拖下来。您将获得带有其他值的苹果表。

单元格 B12 中的公式用于查找水果总价值（如 OP 所建议的示例公式）：

`=IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0)`

对于 NFPI，单元格 C12 中的公式为：

=(E12*IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0))

注意： 这是我根据在 OP 中找到的信息创建的暂定公式。（如果你告诉我你是如何得出 1.67 的，那么我只需改变方程式）。

我相信这会对你有帮助。

相关内容