☰

获取表中的所有（唯一）匹配项并在 SUMIF/S 函数中将其用作条件

tag-icon • tag-icon microsoft-excel
获取表中的所有（唯一）匹配项并在 SUMIF/S 函数中将其用作条件 
-> PAYMENT METHODS TABLE   
NAME SCORE
A    4
B    5
C    6

-> BOOKINGS TABLE
ID DATE   CLIENT   PAYED WITH   PRICE
1  10APR  JHON     A            500
2  10JUN  PATRICK  B            1000
3  10JUL  JHON     A            300
4  15JUL  JHON     C            500

我有两个这样的表，基本上我需要的是能够通过几个过滤器（日期、客户、价格）过滤预订表，然后从所有匹配项中获取“付款方式”名称并在“付款方式表”中加总其各自的分数。这意味着我需要获取一个“付款方式名称”数组（来自匹配的预订），并将其用作过滤器（可能在 SUMIF 函数中）。

我尝试使用这个：

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],INDEX(BOOKINGS_TABLE[PAYED_WITH],MATCH(1,(BOOKINGS_TABLE[CLIENT]:BOOKINGS_TABLE[CLIENT]="JOHN")*(BOOKINGS_TABLE[DATE]:BOOKINGS_TABLE[DATE]>="10/06/2020"),0)),PAYMENT_METHODS_TABLE[SCORE]))

使其成为数组公式 CTRL+SHIFT+ENTER 但它不起作用，MATCH 函数仅返回第一个“付款方式名称”，而不是全部

我想要实现的公式（使用与上述相同的标准）应该是这样的：

=SUM(SUMIF(PAYMENT_METHODS_TABLE[NAME],{"B","C"},PAYMENT_METHODS_TABLE[SCORE]))

答案1

要检查该行是否可见，我们使用 SUBTOTAL：

SUBTOTAL(3,OFFSET(BT[[#Headers],[ID]],ROW(BT[ID])-MIN(ROW(BT[ID]))+1,,1,1))

这将迭代表中的行并返回1是否可见，0如果可见则返回。

然后我们使用LOOKUP：

 LOOKUP(BT[PAYED WITH],PMT[NAME],PMT[SCORE])

返回正确的值。

然后我们将它们相乘并放入 SUMPRODUCT 中计算总和：

=SUMPRODUCT((SUBTOTAL(3,OFFSET(BT[[#Headers],[ID]],ROW(BT[ID])-MIN(ROW(BT[ID]))+1,,1,1))>0)*(LOOKUP(BT[PAYED WITH],PMT[NAME],PMT[SCORE])))

在此处输入图片描述

然后您可以按照您想要的任何方式过滤第一个表，并且总和将会改变：

[

相关内容