在满足多个条件的同时，基于查找ID汇总值 [英] Aggregate values based on lookup IDs while meeting multiple criteria
问题描述
我获得了根据第1页"的销售数据以下.每行包含季度，产品，区域ID和销售数据.每个区域可以有多个ID，并且我有一个查找表(第2页)，该表指示每个ID属于哪个区域.
I am given sales data as per "sheet 1" below. Each row contains the quarter, the product, the region ID, and the sales figure. Each region can have multiple IDs, and I have a lookup table (sheet 2) which denote which region each ID belongs to.
我的目标是进入工作表3.本质上，我正在尝试编写一个公式，该公式将引用A列中的产品名称，A1单元格中的四分之一(用户将输入)，并汇总下的相关销售数字B列中的每个区域.
My goal is to get to sheet 3. Essentially I am trying to write a formula that will reference the product name in column A, the quarter in cell A1 (which the user will input), and aggregate the relevant sales figures under each region in column B.
我尝试过嵌套INDEX&如下所示，SUMPRODUCT中的SUMIFS中的MATCH函数，但是我得到了#VALUE！错误:
I have tried nesting an INDEX & MATCH function within a SUMIFS within a SUMPRODUCT as per below, but I am getting a #VALUE! error:
= SUMPRODUCT(SUMIFS(INDEX(sheet1！$ D:$ D $，MATCH(1，($ A4 = sheet1！$ B:$ B)*)($ A $ 1 = Sheet1！$ A:$A)，0)，0)，sheet1 $ C:$ C，sheet2！$ A $ 2:$ A $ 8)*(sheet2！$ B $ 2:$ B $ 8 = $ B4))
有人知道我的公式有什么问题吗，或者是否有解决此问题的更好方法?
Does anyone know what is wrong with my formula, or if there is a better approach to this problem?
第1张(原始数据)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 季度 | 产品 | ID | 2021年销售 |
| 2 | 第一季度 | A | 1 | 39 |
| 3 | 第一季度 | A | 3 | 41 |
| 4 | 第一季度 | A | 7 | 20 |
| 5 | 第一季度 | A | 14 | 7 |
| 6 | 第一季度 | A | 25 | 2 |
| 7 | 第一季度 | A | 27 | 2 |
| 8 | 第一季度 | A | 44 | 45 |
| 9 | 第一季度 | B | 1 | 28 |
| 10 | 第一季度 | B | 3 | 34 |
| 11 | 第一季度 | B | 7 | 29 |
| 12 | 第一季度 | B | 14 | 48 |
| 13 | 第一季度 | B | 25 | 5 |
| 14 | 第一季度 | B | 27 | 15 |
| 15 | 第一季度 | B | 44 | 32 |
| 16 | 第二季度 | A | 1 | 19 |
| 17 | 第二季度 | A | 3 | 28 |
| 等等…… |
第2张(区域ID查找表)
Sheet 2 (region ID lookup table)
| A | B | |
|---|---|---|
| 1 | ID | 地区 |
| 2 | 1 | 东部 |
| 3 | 3 | 东部 |
| 4 | 7 | 中部 |
| 5 | 14 | 中部 |
| 6 | 25 | 中部 |
| 7 | 27 | 西 |
| 8 | 44 | 西 |
第3页(报告)
| A | B | C | |
|---|---|---|---|
| 1 | 第一季度 | ||
| 2 | |||
| 3 | 产品 | 地区 | 销售 |
| 4 | A | 东部 | 29 |
| 5 | A | 中部 | 42 |
| 6 | A | 西 | 31 |
推荐答案
一些返工:
=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!C:C,IF(Sheet2!$B$2:$B$8=$B4,Sheet2!$A$2:$A$8),Sheet1!A:A,$A$1,Sheet1!B:B,A4))
根据版本一，退出编辑模式时，可能需要使用Ctrl-Shift-Enter而不是Enter进行确认.
Depending on version one may need to confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
使用动态数组公式FILTER，我们可以将FIFTER()替换为IF()部分:
With the dynamic array formula FILTER we can replace the IF() part with FILTER():
=SUMPRODUCT(SUMIFS(Sheet1!D:D,Sheet1!C:C,FILTER(Sheet2!$A$2:$A$8,Sheet2!$B$2:$B$8=$B4),Sheet1!A:A,$A$1,Sheet1!B:B,A4))
它将保存一些迭代.
这篇关于在满足多个条件的同时，基于查找ID汇总值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
