SUMPRODUCT INDEX MATCH [英] SUMPRODUCT INDEX MATCH

问题描述

使用以下数据：

    A       B
1   CUMULATIVE PERCENTAGE OF ITEMS PRODUCED PER MONTH ("COMPLETION_TABLE")
2   Type     Month 1   Month 2
3   KITTENS  0         10
4   FISH     0         20
5   BANANAS  2         5
6   APPLES   0         0
7   PEARS    0         5
8   KITTENS  0         5
9           
10          
11  PRICES TABLE ("PRICES_TABLE")
12  Type     Value  
13  APPLES   1000   
14  BANANAS  5000   
15  PEARS    3000   
16  FISH     4000   
17  KITTENS  2000   

我试图使用SUMPRODUCT函数来计算每个月的百分比变化，并将该值用作价格表的倍数来提供所有类型的每个月的总价格。

I'm attempting to use the SUMPRODUCT function to calculate the percentage change in each month and use that value as a multiple of the prices table to provide a total price per month across all types that have been produced.

我可以计算运动：

= SUMPRODUCT（（COMPLETION_TABLE [Month 2] COMPLETION_TABLE [Month 1]））

...但是，我需要根据该类型的价格计算单个运动值的一部分，并将所得产品合计。我一直在使用各种INDEX / MATCH组合，没有多少运气。

... but I then need to calculate the portion of the individual movement values against the price for that type and sum the resulting products together. I have been using various INDEX / MATCH combinations without much luck.

例如：BANANAS应该 =（5-2）* 5000 。

As an example: BANANAS which should =(5-2)*5000.

以扩展数组的形式编写我想做的

Written as expanded arrays I would like to do

({10;20;5;0;5;5}-{0;0;2;0;0;0})*{2000;4000;5000;1000;3000;2000}. 

推荐答案

使用 SUMPRODUCT 意味着你想要一个单一的数字结果。您可以使用 SUMIF 作为 SUMPRODUCT 中的伪查找来获取价格，例如

Use of SUMPRODUCT implies you want a single figure result. You can use SUMIF as a "pseudo lookup" within SUMPRODUCT to get the prices, e.g.

=SUMPRODUCT(C3:C8-B3:B8,SUMIF(A13:A17,A3:A8,B13:B17))

这将为您的例子获得140,000的结果

That would get you a result of 140,000 for your example

这篇关于SUMPRODUCT INDEX MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！