根据多个条件和查找表对一组值求和
我得到了以下销售表，其中提供了每个员工的销售额，但我没有他们的名字，而是他们的 ID，并且每个 ID 可能有超过 1 行。
为了将 ID 映射回姓名，我有一个包含每个员工的姓名和 ID 的查找表。需要记住的一件事是，任何给定名称都可能分配有多个 ID，如下例所示：
Sales 表：
| 年份 | ID | North | South | West | East |
|---|---|---|---|---|---|
| 2020 | A | 58 | 30 | 74 | 72 |
| 2020 | A | 85 | 40 | 90 | 79 |
| 2020 | B | 9 | 82 | 20 | 5 |
| 2020 | B | 77 | 13 | 49 | 21 |
| 2020 | C | 85 | 55 | 37 | 11 |
| 2020 | C | 29 | 70 | 21 | 22 |
| 2021 | A | 61 | 37 | 21 | 42 |
| 2021 | A | 22 | 39 | 2 | 34 |
| 2021 | B | 62 | 55 | 9 | 72 |
| 2021 | B | 59 | 11 | 2 | 37 |
| 2021 | C | 41 | 22 | 64 | 47 |
| 2021 | C | 83 | 18 | 56 | 83 |
ID 表：
| ID | 姓名 |
|---|---|
| A | Allison |
| B | Brandon |
| C | Brandon |
我正在尝试按给定年份汇总每个员工的销售额，并按他们的姓名（而不是 ID）汇总他们的所有交易，以便我的结果看起来如下所示：
结果：
| Report | |
|---|---|
| 2021 | |
| Allison | 258 |
| Brandon | 721 |
我希望用户能够选择年份，并且报告会自动总结每个人的销售额年份和他们的名字。同样，Brandon 被分配了 ID B 和 C，因此报告应该能够获取 B 和 C 下的所有 2021 年销售额。
我发布了类似的 问题，其中不包括将名称与多个 ID 绑定所增加的复杂性。在该线程中，我得到了一个包含以下公式的解决方案：
=SUMPRODUCT($C$2:$F$13*($B$2:$B$13=INDEX($I$2:$I$4,MATCH(N3,$J$2:$J$4,0)))*($A$2:$A$13=$N$2))
虽然此公式适用于仅与一个 ID 相关联的名称，但我相信 INDEX 和 MATCH 组件一旦在 ID 表上遇到重复名称，就会失败。
我目前使用的是 Excel 2016，因此任何解决方案都需要至少与该版本兼容。预先感谢您对此的任何指导。
I am given the following sales table which provide the sales that each employee made, but instead of their name I have their ID and each ID may have more than 1 row.
To map the ID back to the name, I have a look up table with each employee's name and ID. One thing to keep in mind is that any given name could potentially have more than one ID assigned to it, as described in the example below:
Sales Table:
| Year | ID | North | South | West | East |
|---|---|---|---|---|---|
| 2020 | A | 58 | 30 | 74 | 72 |
| 2020 | A | 85 | 40 | 90 | 79 |
| 2020 | B | 9 | 82 | 20 | 5 |
| 2020 | B | 77 | 13 | 49 | 21 |
| 2020 | C | 85 | 55 | 37 | 11 |
| 2020 | C | 29 | 70 | 21 | 22 |
| 2021 | A | 61 | 37 | 21 | 42 |
| 2021 | A | 22 | 39 | 2 | 34 |
| 2021 | B | 62 | 55 | 9 | 72 |
| 2021 | B | 59 | 11 | 2 | 37 |
| 2021 | C | 41 | 22 | 64 | 47 |
| 2021 | C | 83 | 18 | 56 | 83 |
ID table:
| ID | Name |
|---|---|
| A | Allison |
| B | Brandon |
| C | Brandon |
I am trying to sum up each employee's sales by a given year, and aggregate all their transactions by their name (rather than ID), so that my result looks like the following:
Result:
| Report | |
|---|---|
| 2021 | |
| Allison | 258 |
| Brandon | 721 |
I want the user to be able to select the year, and the report would automatically sum up each person's sales by the year and their name. Again, Brandon was assigned ID B and C, so the report should be able to obtain all 2021 sales under B and C.
I posted a similar question which did not include the added complexity of having a name tied to more than one ID. In that thread, I was provided a solution with the following formula:
=SUMPRODUCT($C$2:$F$13*($B$2:$B$13=INDEX($I$2:$I$4,MATCH(N3,$J$2:$J$4,0)))*($A$2:$A$13=$N$2))
While this formula works on names that only have one ID tied to it, I believe the INDEX and MATCH component falls through once it encounters a duplicate name on the ID table.
I am currently using Excel 2016, so any solution would need to be compatible with that version at least. Thanks in advance for any guidance on this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试这个公式解决方案可以在您的 Excel 2016
中使用
L4，复制下来的公式：Try this formula solution can work in your Excel 2016
In
L4, formula copied down :