在EXCEL中使用多个表的条件SUM [英] Conditional SUM using multiple tables in EXCEL
问题描述
我有一个要根据两个参考表的值填充的表.
I have a table that I'm trying to populate based on the values of two reference tables.
我有各种不同的项目类型1"，类型2"等，每个项目运行4个月，并且根据其生命周期的不同而花费不同的金额.这些成本显示在Ref Table 1中.
I have various different projects 'Type 1', 'Type 2' etc. that each run for 4 months and cost different amounts depending on when in their life cycle they are. These costings are shown in Ref Table 1.
参考表1
Month | a | b | c | d
---------------------------------
Type 1 | 1 | 2 | 3 | 4
Type 2 | 10 | 20 | 30 | 40
Type 3 | 100 | 200 | 300 | 400
Ref Table 2显示了我接下来3个月的项目进度表.从1月开始有2个新的，一个是Type 1，另一个是Type 2. 2月，我将有4个项目，前两个进入第二个月，两个新的开始，但这一次是Type 1和Type 3.
Ref Table 2 shows my schedule of projects for the next 3 months. With 2 new ones starting in Jan, one being a Type 1 and the other being a Type 2. In Feb, I'll have 4 projects, the first two entering their second month and two new ones start, but this time a Type 1 and a Type 3.
参考表2
Date | Jan | Feb | Mar
--------------------------
Type 1 | a | b | c
Type 1 | | a | b
Type 2 | a | b | c
Type 2 | | | a
Type 3 | | a | b
我想创建一个表，该表计算每月每种项目类型花费的总费用.结果示例显示在下面的Results表中.
I'd like to create a table which calculates the total costs spent per project type each month. Example results are shown below in Results table.
结果
Date | Jan | Feb | Mar
-------------------------------
Type 1 | 1 | 3 | 5
Type 2 | 10 | 20 | 40
Type 3 | 0 | 100 | 200
我尝试使用数组公式来做到这一点:
I tried doing it with an array formula:
Res!b2 = {sum(if((Res!A2 = Ref2!A2:A6) * (Res!A2 = Ref1!A2:A4) * (Ref2!B2:D6 = Ref1!B1:D1), Ref!B2:E4))}
但是它不起作用，我相信是由于第三个条件，试图将一个向量与另一个向量而不是单个值进行比较.
However it doesn't work and I believe that it's because of the third condition trying to compare a vector with another vector rather than a single value.
有人知道我该怎么做吗?很高兴使用数组，索引，匹配，向量，查找，但不使用VBA.
Does anyone have any idea how I can do this? Happy to use arrays, index, match, vector, lookups but NOT VBA.
谢谢
推荐答案
假定结果表标题中的月份与参考表2的顺序相同(按照您的示例)，然后在Res！B2中尝试使用此公式
Assuming that months in results table headers are in the same order as Ref table 2 (as per your example) then try this formula in Res!B2
=SUM(SUMIF(Ref1!$B$1:$E$1,IF(Ref2!$A$2:$A$6=Res!$A2,Ref2!B$2:B$6),INDEX(Ref1!$B$2:$E$4,MATCH(Res!$A2,Ref1!$A$2:$A$4,0),0)))
与CTRL+SHIFT+ENTER确认并向下复制并
与您在结果表中获得的结果相同，这给了我
That gives me the same results as you get in your results table
如果月份的顺序可能不同，那么您也可以添加一些内容进行检查-我假设结果表行标签中的类型可能与Ref表1的顺序不同，但如果它们始终相同也可以按照您的示例进行排序，那么末尾的INDEX/MATCH部分可以简化为单个范围
If the months might be in different orders then you can add something to check that too - I assumed that the types in results table row labels might be in a different order to Ref table 1, but if they are always in the same order too (as per your example) then the INDEX/MATCH part at the end can be simplified to a single range
这篇关于在EXCEL中使用多个表的条件SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
