Question

EXCEL高手速度来帮我一下

假如B1:B5 D1:D5里面有输入的RMB大小
B分别是 200，500，700，空 ,空
D分别是 200, 400,1000, 空, 空
我想把他们B D的总价减去 每项X0.05+5 全部总和 统计出来输出到一个框里 但有一个问题 就是空的那个区域如果不管就默认为5 因为他默认为零所以加出来得5
谁能帮我解决这个问题 列一个简单的函数 谢谢了

Answer 1

=SUM(B1:B5,D1:D5)-SUM(IF(B1:B5,B1:B5*0.05+5,0)+IF(D1:D5,D1:D5*0.05+5,0))
数组公式，按Ctrl+Shift+Enter结束公式输入。

Answer 2

水平太低，只能写个麻烦一点的
=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="",5,B1:B5)*0.05+5))-SUMPRODUCT((IF(d1:d5="",5,d1:d5)*0.05+5))
输入后，不能回车，需要按下ctrl+shift+回车，即看到公式的前后有{}才对

追问

这个=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="",5,B1:B5)*0.05+5))-SUMPRODUCT((IF(d1:d5="",5,d1:d5)*0.05+5))
不能显示 VALUE!

追答

你是按的ctrl+shift+回车吗？
公式前后自动加上了{}吗？

追问

加了的撒 按了就自动加的

追答

我测试你给的数据，得出的结果是2799
难道你说的空是个字？不是空单元格的意思？
那公式修改为
=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="空",5,B1:B5)*0.05+5))-SUMPRODUCT((IF(D1:D5="空",5,D1:D5)*0.05+5))
别忘记这是数组，按三键

追问

最后一位肯定不是9 我用笔算了一下得出来的是2895

追答

刚看到，原来我审错题了，你是当为空时当0算，我理解成当5算*0.05+5了
公式修改为
=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="空",0,B1:B5)*0.05+5))-SUMPRODUCT((IF(D1:D5="空",0,D1:D5)*0.05+5))
答案是2800，也不是你说的2895啊

=200+500+700+200+400+1000-200*0.05-5-500*0.05-5-700*0.05-5-5-5-200*0.05-5-400*0.05-5-1000*0.05-5-5-5
这样列有错吗？

追问

是+ 5 不是减

追答

你提问中说提每项乘0.05后+5
而它是被总数减去的。
N-(a*0.05+5)
不是等于
N-a*0.05-5吗？
按你的如果要求是-5
=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="空",0,B1:B5)*0.05-5))-SUMPRODUCT((IF(D1:D5="空",0,D1:D5)*0.05-5))
那结果是2900啊。你能不能列一下以你的提问为便，如果用手工算的算式？

追问

数值总和200+500+700+200+400+1000=3000
=200*0.05+5=15*2
=500*0.05+5=30
=700*0.05+5=40
=400*0.05+5=25
=1000*0.05+5=55
最后结果为3000-（30+30+40+25+55）=180=2820

追答

不是还有四个空值吗？四个空值不需要+5了？
那我这个笨人只能加长公式长度了
=SUM(B1:B5)+SUM(D1:D5)-SUMPRODUCT((IF(B1:B5="",0,B1:B5)*0.05+5))-SUMPRODUCT((IF(D1:D5="",0,D1:D5)*0.05+5))+COUNTIF(B1:B5,"")*5+COUNTIF(D1:D5,"")*5
与你一起坐等简单公式。

Answer 3

=IF(AND(B1,D1)，公式，)用这个就好使

Answer 4

你那个B4、B5、D4、D5中是空单元格吧，如果是，公式
=SUM(IF(B1:D5*{1,0,1},B1:D5*{1,0,1}*0.95+5))
同时按下CTRL+SHIFT+回车，输入数组公式，试试。
不过如果C1：C5中有文本，

追问

那些都是随机输出的数值 不会每次都是空

追答

我的意思是你所说的“空”，是真正的空单元格，还是一个“空”字样，如果是真正的空单元格，没有关系，还有C1：C5单元格中不能出现文本，可以出现数字。否则公式要改动。

Answer 5

=SUM(B1,D1)-B1*0.05-D1*0.05+5
假如要再F列得输出的结果，就在F1内输入上面的公式，选中F1往下拖动就可以出来F1-F5的数据来。不知道是不是楼主想要的结果，呵呵！

Answer 6

=SUM(B1:B5,D1:D5)*0.95+(COUNTA(B1:B5,D1:D5)+COUNTBLANK(B1:B5)+COUNTBLANK(B1:B5))*5