英文:
Matching criteria in multiple tables in Excel
问题
我需要你在以下问题上的帮助。
我无法将来自不同表的条件组合在一起。
例如，我有10个“J”人员。我想根据第一个表中的部门容量将这些人员分配到第二个表中。您可以在“Result！”列中看到我想要实现的结果。
Table 1（表格1）：
| 部门 | 容量 |
|---|---|
| A | 70 |
| B | 50 |
| C | 30 |
| D | 120 |
Table 2（表格2）：
| 部门 | 姓名 | 结果！ |
|---|---|---|
| A | 人员“J” | 7 |
| B | 人员“K” | 5 |
| C | 人员“J” | 3 |
我需要根据部门容量分配人员数量。
例如，人员“J”只在“A”和“C”部门工作。
这些部门的总容量为100（A=70％，C=30％）。J的总数量为10。A = 10 x 70% = 7，B = 10 x 30% = 3。
人员“K”只在“B”部门工作。
该部门的总容量为50（B=100％）。K的总数量为5。所以B = 5 x 100% = 5。
提前感谢！
英文:
I need your help on the following issue.
I can't combine criteria from different tables.
For example, I have 10 "J" personnel. I want to distribute these personnel in the second table according to the department volume which is in the first table. You can see the result I want to achieve in the "Result!" column.
| Name | Qty |
|---|---|
| Personnel "J" | 10 |
| Personnel "K" | 5 |
Table 1:
| Department | Volume |
|---|---|
| A | 70 |
| B | 50 |
| C | 30 |
| D | 120 |
Table 2:
| Department | Name | Result! |
|---|---|---|
| A | Personnel "J" | 7 |
| B | Personnel "K" | 5 |
| C | Personnel "J" | 3 |
I need to distribute personnel quantity according to the department volume.
For example personnel "J" only works in "A" and "C" departments.
Total volume of these departments are 100. (A=%70, C=%30). Total J quantity is 10. A = 10 x 70% = 7, B = 10 x 30% = 3.
Personnel "K" only works in "B" department.
Total volume of this department is 50. (B=%100).Total K quantity is 5. so B = 5 x 100% = 5
Thank you in advance!
答案1
得分: 2
=LET(sn,Table0[Name],sq,Table0[Qty],ld,Table1[Department],lv,Table1[Volume],dd,[Department],dn,[Name],ddVal,[@Department],dnVal,[@Name],sqSum,SUM(FILTER(sq,sn=dnVal)),ddd,FILTER(dd,dn=dnVal),dvd,XLOOKUP(ddd,ld,lv,0),dvVal,XLOOKUP(ddVal,ddd,dvd),dvSum,SUM(dvd),dq,sqSum*dvVal/dvSum,dq)
英文:
Lookup Data in Two Excel Tables
=LET(sn,Table0[Name],sq,Table0[Qty],ld,Table1[Department],lv,Table1[Volume],
dd,[Department],dn,[Name],ddVal,[@Department],dnVal,[@Name],
sqSum,SUM(FILTER(sq,sn=dnVal)),
ddd,FILTER(dd,dn=dnVal),dvd,XLOOKUP(ddd,ld,lv,0),
dvVal,XLOOKUP(ddVal,ddd,dvd),dvSum,SUM(dvd),
dq,sqSum*dvVal/dvSum,
dq)
答案2
得分: 1
我已解决了这个问题：
=SUMIF(Table1[Department];"A";Table1[Volume])/
SUMPRODUCT(SUMIFS(Table1[Volume];Table1[Department];[Department]);
--([Staff]="Personnel J"))*SUMIF(Table0[Staff];"Personnel J";Table0[Total])
英文:
I've solved the problem:
=SUMIF(Table1[Department];"A";Table1[Volume])/
SUMPRODUCT(SUMIFS(Table1[Volume];Table1[Department];[Department]);
--([Staff]="Personnel J"))*SUMIF(Table0[Staff];"Personnel J";Table0[Total])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论