我想要做的是通过同一工作簿中具有相似结构的不同工作表进行一些计算（主工作表），因此当我从下拉菜单中选择某些内容时，它应该通过所有工作表计算总吨位，然后在主页（J4）上给我结果。
还有更多字段需要计算，但我只需要另一个想法或意见来继续。表格相似但不一致，组合数字/文本，实际上只是一些随机数据。
以下是我在 J4 - J7 列中使用的公式
J4 =IF($A$4=Calculation!$A$2&" ";SUM(tbl_CS1B[Menge '[t']]);IF($A$4=Calculation!$A$4&" ";SUM(tbl_CS2A[Menge '[t']]);IF($A$4=Calculation!$A$7&" ";SUM(tbl_CS3E[Menge '[t']]);IF($A$4=Calculation!$A$8&" ";SUM(tbl_CS3F[Menge '[t']]);IF($A$4=Calculation!$A$9&" ";SUM(tbl_CS3G[Menge '[t']]);IF($A$4=Calculation!$A$10&" ";SUM(tbl_CS3H[Menge '[t']]);IF($A$4=Calculation!$A$11&" ";SUM(tbl_CS3K[Menge '[t']]);IF($A$4=Calculation!$A$12&" ";SUM(tbl_CS3P[Menge '[t']]);IF($A$4=Calculation!$A$15&" ";SUM(tbl_CS7A[Menge '[t']]);IF($A$4=Calculation!$A$16&" ";SUM(tbl_CS7B[Menge '[t']]);IF($A$4=Calculation!$A$17&" ";SUM(tbl_CS7D[Menge '[t']]);IF($A$4=Calculation!$A$18&" ";SUM(tbl_CS7E[Menge '[t']]);""))))))))))))
J5=IF($A$4=Calculation!$A$2&" ";SUMPRODUCT(1/COUNTIF(tbl_CS1B[Charge];tbl_CS1B[Charge]));IF($A$4=Calculation!$A$4&" ";SUMPRODUCT(1/COUNTIF(tbl_CS2A[Charge];tbl_CS2A[Charge]));IF($A$4=Calculation!$A$7&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3E[Charge];tbl_CS3E[Charge]));IF($A$4=Calculation!$A$8&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3F[Charge];tbl_CS3F[Charge]));IF($A$4=Calculation!$A$9&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3G[CS3G Charge];tbl_CS3G[CS3G Charge]));IF($A$4=Calculation!$A$10&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3H[Charge];tbl_CS3H[Charge]));IF($A$4=Calculation!$A$11&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3K[Charge];tbl_CS3K[Charge]));IF($A$4=Calculation!$A$12&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3P[Charge];tbl_CS3P[Charge]));IF($A$4=Calculation!$A$15&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7A[Charge];tbl_CS7A[Charge]));IF($A$4=Calculation!$A$16&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7B[Charge];tbl_CS7B[Charge]));IF($A$4=Calculation!$A$17&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7D[Charge];tbl_CS7D[Charge]));IF($A$4=Calculation!$A$18&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7E[Charge];tbl_CS7E[Charge]));""))))))))))))
J6=IF($A$4=Calculation!$A$2&" ";SUMPRODUCT((tbl_CS1B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS1B[Rohstoff-Charge];tbl_CS1B[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$4&" ";SUMPRODUCT((tbl_CS2A[Rohware Charge]<>"")/COUNTIF(tbl_CS2A[Rohware Charge];tbl_CS2A[Rohware Charge]&""));IF($A$4=Calculation!$A$7&" ";SUMPRODUCT((tbl_CS3E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3E[Rohstoff-Charge];tbl_CS3E[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$8&" ";SUMPRODUCT((tbl_CS3F[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3F[Rohstoff-Charge];tbl_CS3F[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$9&" ";SUMPRODUCT((tbl_CS3G[Rohware Charge]<>"")/COUNTIF(tbl_CS3G[Rohware Charge];tbl_CS3G[Rohware Charge]&""));IF($A$4=Calculation!$A$10&" ";SUMPRODUCT((tbl_CS3H[Rohrware Charge]<>"")/COUNTIF(tbl_CS3H[Rohrware Charge];tbl_CS3H[Rohrware Charge]&""));IF($A$4=Calculation!$A$11&" ";SUMPRODUCT((tbl_CS3K[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3K[Rohstoff-Charge];tbl_CS3K[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$12&" ";SUMPRODUCT((tbl_CS3P[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3P[Rohstoff-Charge];tbl_CS3P[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$15&" ";SUMPRODUCT((tbl_CS7A[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7A[Rohstoff-Charge];tbl_CS7A[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$16&" ";SUMPRODUCT((tbl_CS7B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7B[Rohstoff-Charge];tbl_CS7B[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$17&" ";SUMPRODUCT((tbl_CS7D[Rohware Charge]<>"")/COUNTIF(tbl_CS7D[Rohware Charge];tbl_CS7D[Rohware Charge]&""));IF($A$4=Calculation!$A$18&" ";SUMPRODUCT((tbl_CS7E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7E[Rohstoff-Charge];tbl_CS7E[Rohstoff-Charge]&""));""))))))))))))
J7=IF($A$4=Calculation!$A$2&" ";COUNTA(tbl_CS1B[Faß]);IF($A$4=Calculation!$A$4&" ";COUNTA(tbl_CS2A[Faß]);IF($A$4=Calculation!$A$7&" ";COUNTA(tbl_CS3E[Faß]);IF($A$4=Calculation!$A$8&" ";COUNTA(tbl_CS3F[Faß]);IF($A$4=Calculation!$A$9&" ";COUNTA(tbl_CS3G[Faß]);IF($A$4=Calculation!$A$10&" ";COUNTA(tbl_CS3H[Faß]);IF($A$4=Calculation!$A$11&" ";COUNTA(tbl_CS3K[Faß]);IF($A$4=Calculation!$A$12&" ";COUNTA(tbl_CS3P[Faß]);IF($A$4=Calculation!$A$15&" ";COUNTA(tbl_CS7A[Faß]);IF($A$4=Calculation!$A$16&" ";COUNTA(tbl_CS7B[Faß]);IF($A$4=Calculation!$A$17&" ";COUNTA(tbl_CS7D[Faß]);IF($A$4=Calculation!$A$18&" ";COUNTA(tbl_CS7E[Faß]);""))))))))))))
因此，如您所见，它有效，但公式很长。有谁能帮忙提供任何其他解决方案或方法，通过整个工作簿来解决类似问题？
我的 WB 被格式化为 Power Query 中的表格，这为我提供了更多可能性，例如数据透视表等。但我希望将这些信息放在一页上，而不是 XY 页面上，例如一个材料对应一个数据透视表等等。
可以使用 INDIRECT 函数或其他组合来实现吗？
我正在使用 Office 2016。
答案1
您可以使用 MATCH 和 CHOOSE，而不是嵌套的 IF，因此 J4 中的公式将是：
=IFERROR(CHOOSE(MATCH(LEFT($A$4,LEN($A$4)-1),{Calculation!$A$2,Calculation!$A$4,Calculation!$A$7,Calculation!$A$8,Calculation!$A$9,Calculation!$A$10,Calculation!$A$11,Calculation!$A$12,Calculation!$A$15,Calculation!$A$16,Calculation!$A$17,Calculation!$A$18},0),SUM(tbl_CS1B[Menge '[t']]),SUM(tbl_CS2A[Menge '[t']]),SUM(tbl_CS3E[Menge '[t']]),SUM(tbl_CS3F[Menge '[t']]),SUM(tbl_CS3G[Menge '[t']]),SUM(tbl_CS3H[Menge '[t']]),SUM(tbl_CS3K[Menge '[t']]),SUM(tbl_CS3P[Menge '[t']]),SUM(tbl_CS7A[Menge '[t']]),SUM(tbl_CS7B[Menge '[t']]),SUM(tbl_CS7D[Menge '[t']]),SUM(tbl_CS7E[Menge '[t']])),"")
或甚至使用 INDIRECT 进一步减少它：
=IFERROR(SUM(INDIRECT(CHOOSE(MATCH(LEFT($A$4;LEN($A$4)-1);{Calculation!$A$2;Calculation!$A$4;Calculation!$A$7;Calculation!$A$8;Calculation!$A$9;Calculation!$A$10;Calculation!$A$11;Calculation!$A$12;Calculation!$A$15;Calculation!$A$16;Calculation!$A$17;Calculation!$A$18};0);tbl_CS1B;tbl_CS2A;tbl_CS3E;tbl_CS3F;tbl_CS3G;tbl_CS3H;tbl_CS3K;tbl_CS3P;tbl_CS7A;tbl_CS7B;tbl_CS7D;tbl_CS7E)&"[Menge '[t']]"));"")