Excel:一列的总和,但对总和之前的每个单元格应用函数 [英] Excel: sum of a column, but apply a function to each cell before sum
问题描述
我有一个基本的Excel电子表格,如下所示:
I have a basic excel spreadsheet like this:
A B C D etc..
10% 219.10 34.2 6.4 etc..
6% 45.10 36.2 7.4 etc..
4% 419.10 54.2 9.4 etc..
Totals: [SUM] [SUM] [SUM] ( -- update - need totals here -- )
我想对B,C和D列中的所有值求和.在执行sum()之前,每个单元格值都必须与A列中的%相乘
I would like to sum all the values in column B, C and D. Each cell value needs to be multiplied by the % in column A before doing the sum()
我可以手动创建一个新列以包含%计算,然后对该列求和,但是我希望在没有附加列的情况下进行操作.
I could manually create a new column to contain the % calculation, and sum that column up, but I'm hoping to do it without the additional column.
我发现了这我认为这是正确的,但是在实现它方面有些失落.
I found this which I think is on the right track but am a little lost at implementing it.
(计划将此计算结果写入到基于简单mysql数据库中的值的php脚本创建的excel电子表格中)
(planning to write this calculation to an excel spreadsheet created from a php script based on values in a simple mysql database)
推荐答案
编辑
对于更新后的问题,您所需要的只是(在B5中)
EDIT
For the updated question, all you need is (in B5)
=SUMPRODUCT(B2:B4 * $A2:$A4)
复制到右侧
对于E2,使用
For E2, use
=SUMPRODUCT(B2:D2 * $A2)
这表示B2 * A2 + C2 * A2 + D2 * A2.
This means B2*A2 + C2*A2 + D2*A2.
但是,您知道吗,从数学上讲,它与
But, did you know that mathematically speaking, it is the same as
B2*A2 + C2*A2 + D2*A2
= A2 * (B2+C2+D2)
下面是哪个?
=A2*SUM(B2:D2)
查看您的等,如果需要向右扩展,只需将A列锁定在SUMPRODUCT中,或使用SUM.以下示例最多可包含J
Looking at your etc, if you need to expand it rightwards, just keep column A locked in SUMPRODUCT, or use the SUM. Example below for up to J
=SUMPRODUCT(B2:J2 * $A2)
=A2*SUM(B2:J2)
然后抄下来.
这篇关于Excel:一列的总和,但对总和之前的每个单元格应用函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!