主页 / coding / 问题 / 79226115
Accepted
Michi
Asked: 2024-11-26 17:44:36 +0800 CST

FILTER 函数中两个数组的 SUMIF

  • 772
0 一个 乙 碳 德 埃 F 格 赫 我 J 钾 大号
1 品牌A 产品 01 600 品牌A 产品 01 370 品牌C
2 品牌A 产品 02 100 品牌C 产品 02 650
3 品牌A 产品 02 300 品牌C 产品 01 390 产品 02 200 产品 02 650
4 品牌B 产品 01 400 产品 02 800 产品 01 390
5 品牌B 产品 03 500 产品 04 900
6 品牌C 产品 02 200 产品 01 700
7 品牌C 产品 02 800
8 品牌C 产品 04 900
9 品牌C 产品 01 700 产品 02 350
10 品牌D 产品 03 250 产品 04 900
11 品牌D 产品 03 460 产品 01 310
12 品牌D 产品 04 690

在上表中我有两个不同的列表：

列表AColumn A:C
列表BColumn E:G

在Range I3:J6和中我使用以下公式Range K3:L4根据标准过滤这两个列表：Cell I1

Cell I3 =CHOOSECOLS(FILTER(A1:C12,A1:A12=I1),2,3) 
Cell K3 =CHOOSECOLS(FILTER(E1:G12,E1:E12=I1),2,3)

一切运行良好。

现在我的目标是 中的列表Range I9:J11。
该列表唯一地显示 中的筛选列表 A 中的所有产品，Range I3:J6并从 中的筛选列表 B 中减去每个产品的相应值Range K3:L4。

我知道我可以通过将UNIQUE函数和函数应用于和SUMIF中的现有结果来做到这一点。Range I3:J6Range K3:L4

但是，我更希望有一个不需要Range I3:J6和 的解决方案Range K3:L4。相反，应该显示
中的列表而不显示“辅助范围”。Range I9:J11

您知道如何实现这个目标吗？

笔记：

  1. List B永远不会包含不存在的值List A。无论是品牌还是产品。

  2. List B每个品牌也可能包含多次相同的产品。它并不总是唯一的。例如，可能会出现Product 02每次出现 x 次的情况Brand C。

excel

4 个回答

  1. Best Answer

    GROUPBY()可能是更简单的方法。尝试-

    =LET(x,VSTACK(HSTACK(E1:F3,G1:G3*(-1)),A1:C12),GROUPBY(CHOOSECOLS(x,2),CHOOSECOLS(x,3),SUM,0,0,,CHOOSECOLS(x,1)=I1))

    您也可以尝试-

    =LET(p,UNIQUE(VSTACK(FILTER(B:B,A:A=I1),FILTER(F:F,E:E=I1))),
HSTACK(p,MAP(p,LAMBDA(x,SUMIFS(C:C,B:B,x,A:A,I1)-SUMIFS(G:G,F:F,x,E:E,I1)))))

    在此处输入图片描述

    • 3

  2. 此公式解决了以下任务：

    =LET(
  r,VSTACK(
    FILTER(B1:C12,A1:A12=I1,{"",0}),
    HSTACK(FILTER(F1:F3,E1:E3=I1,""),-FILTER(G1:G3,E1:E3=I1,0))
  ),
  _c1,TAKE(r,,1),_c2,TAKE(r,,-1),u,UNIQUE(_c1),
  HSTACK(u,MAP(u,LAMBDA(v,SUM(_c2*(_c1=v)))))
)

    在此处输入图片描述

    这个想法是对第二个范围的值取反，然后将两个范围的值合并并乘以总和。

    可以使用新的 GROUPBY 函数简化公式，但我仍然无法使用。

    • 2

  3. 再次，只是有点令人费解：

    在此处输入图片描述

    公式为I3：

    =LET(x,VSTACK,GROUPBY(x(B.:.B,F.:.F),x(C.:.C,-G.:.G),SUM,,0,,x(A.:.A,E.:.E)=I1))
    • 2

  4. E6 中的公式

    =LET(filt1,FILTER(A1:C12,A1:A12=I1),
filt2,FILTER(E1:G3,E1:E3=I1),
sumx1,UNIQUE(CHOOSECOLS(filt1,2)),
sumpr1,BYROW(sumx1,LAMBDA(a,SUMPRODUCT(IF(CHOOSECOLS(filt1,2)=a,CHOOSECOLS(filt1,3),0)))),
sumpr2,BYROW(sumx1,LAMBDA(a,SUMPRODUCT(IF(CHOOSECOLS(filt2,2)=a,CHOOSECOLS(filt2,3),0)))),
HSTACK(sumx1,sumpr1-sumpr2))

    第一部分基于您的试验。第二部分SUMPRODUCT对唯一名称的值进行求和Product。

    在此处输入图片描述

    • 0

相关问题