设计一个范围内的求和函数公式？

lm3312

袁勇 发表于 2023-3-21 15:35

感谢帮助.....

lm3312

jisijie630 发表于 2023-3-21 18:48
D2：

=IF(A2,SUM(C2:C99)-SUM(D3:D99),"")

有循环错误

lm3312

一杯绿茶来了 发表于 2023-3-21 20:31
=IF(A2,LOOKUP(1,SUBTOTAL({2,9},OFFSET(A2,,{0,2},ROW($1:$10)))),"")
下拉

感谢帮助....

象山海鲜

lm3312 发表于 2023-3-23 11:09
您好，您给的这个公式不错，实用性很强，但请再次修改并满足一个条件
原条件：
1、自动汇总C列本行单元 ...

怎么判断再D5和D7要计算呢？
规则是什么或者说判断依据是什么？
请说明规则并举例。

lm3312

象山海鲜 发表于 2023-3-23 11:31
怎么判断再D5和D7要计算呢？
规则是什么或者说判断依据是什么？
请说明规则并举例。

如果当前处于D2单元格的公式：不管D5是什么内容，只要D5有任意内容，则汇总结果为SUM(C2：C4)，否则汇总结果为SUM(C2:C65536)。

lm3312

问了一下GPT，仍没有解决，而且GPT设计的IF()函数总是多了一个参数，这个错误还老是改不过来。

1. 【提问】
   
2. 2003版EXCEL工作表中，在D列设计一个对C列单元格的求和公式，公式适合D列任意一个单元格，比如，当前公式所在的单元格是D2，则汇总条件是：如果D列的下一个单元格有内容，比如D5单元格有内容，则汇总结果为SUM(D2:D4)，否则，汇总结果为SUM(D2:D65536)
   
3. 
   
4. 【回答】
   
5. 可以使用以下公式来实现您的需求：
   
6. 
   
7. =SUM(D2:INDIRECT(IF(ISBLANK(D3),"$D$65536",ADDRESS(MATCH(TRUE,NOT(ISBLANK(D3:$D$65536))),4))))
   
8. 
   
9. 这个公式使用了INDIRECT函数和ADDRESS函数来动态计算汇总范围的结束单元格。具体来说，这个公式会检查D列下一个单元格是否为空。如果不为空，它会使用MATCH函数和ADDRESS函数来获取D列下一个非空单元格的地址，并将其作为汇总范围的结束单元格。如果为空，它会将结束单元格设置为D列的最后一行（65536行）。
   
10. 需要注意的是，这个公式是针对Excel 2003版本的，因为该版本的行数最大为65536行。如果您使用的是Excel 2007及以后的版本，行数已经扩展到1048576行，因此需要将公式中的行数修改为1048576。
    
11. 
    
12. 【提问】
    
13. 这个公式有错误，虽然对汇总的行范围作出了判断，但是汇总数据在C列，你的公式没有涉及C列的内容
    
14. 
    
15. 【回答】
    
16. 非常抱歉，我理解错误了您的需求。以下是一个新的公式，可以实现您的要求：
    
17. 
    
18. =SUM(OFFSET(C2,0,0,IF(ISBLANK(D3),65536-ROW(D2)+1,MATCH(TRUE,NOT(ISBLANK(D3:$D$1048576))),0)-ROW(C2),1))
    
19. 
    
20. 这个公式使用了OFFSET函数和IF函数来动态计算要求和的单元格范围。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MATCH函数来获取D列下一个非空单元格的行号，然后使用IF函数计算要汇总的行数。如果为空，则使用ROW函数和常量65536来计算要汇总的行数。最后，使用OFFSET函数来获取要求和的单元格范围。
    
21. 需要注意的是，这个公式是针对Excel 2003及以后的版本，因为该版本的行数最大为1048576行。如果您使用的是Excel 2003版本，需要将公式中的行数修改为65536。同时，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    
22. 
    
23. 【提问】
    
24. 这个公式错误，IF()函数多了一个参数，请重新设计
    
25. 
    
26. 【回答】
    
27. 非常抱歉，我的回答有误。感谢您的指正。以下是一个修正后的公式，可以实现您的需求：
    
28. 
    
29. =SUM(C2:INDEX(C:C,IF(ISBLANK(D3),65536,MIN(ROW(D:D)-ROW(D2)-1))+1))
    
30. 
    
31. 这个公式使用了INDEX函数和MIN函数来动态计算求和范围的结束单元格。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MIN函数和ROW函数计算D列下一个非空单元格的行号，然后使用INDEX函数获取C列中该行的单元格。如果为空，则使用常量65536作为结束行号。最后，将求和范围设置为C2到动态计算的结束单元格。
    
32. 需要注意的是，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    
33. 
    
34. 【提问】
    
35. 这个公式仍有错误，显示“#REF!”的错误结果
    
36. 
    
37. 【回答】
    
38. 非常抱歉，我的回答还有误。感谢您的指正。以下是一个修正后的公式，可以实现您的需求：
    
39. 
    
40. =SUM(C2:INDEX(C2:C1048576,IF(ISBLANK(D3),1048576-ROW(D2)+1,MATCH(TRUE,NOT(ISBLANK(D3:$D$1048576))),0)-1))
    
41. 
    
42. 这个公式使用了INDEX函数和MATCH函数来动态计算求和范围的结束行号。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MATCH函数和NOT函数来获取D列下一个非空单元格的行号，然后使用IF函数计算要汇总的行数。如果为空，则使用ROW函数和常量1048576来计算要汇总的行数。最后，使用INDEX函数来获取求和范围。需要注意的是，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    
43. 
    
44. 【提问】
    
45. 这个公式跟前面的一个犯了同样的错误，IF()函数多了一个参数
    
46. 
    
47. 【回答】
    
48. 非常抱歉，我的回答还有误。感谢您的指正。以下是一个修正后的公式，可以实现您的需求：
    
49. 
    
50. =SUM(C2:INDEX(C:C,IF(ISBLANK(D3),ROWS(C:C)-ROW(C2)+1,MATCH(TRUE,NOT(ISBLANK(D3:$D$1048576))),0)+ROW(C2)-1))
    
51. 
    
52. 这个公式使用了INDEX函数和MATCH函数来动态计算求和范围的结束行号。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MATCH函数和NOT函数来获取D列下一个非空单元格的行号，然后使用IF函数计算要汇总的行数。如果为空，则使用ROWS函数和ROW函数计算要汇总的行数。最后，使用INDEX函数来获取求和范围。需要注意的是，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    
53. 
    
54. 【提问】
    
55. 仍然错误，这个公式中的IF()函数多了一个参数
    
56. 
    
57. 【回答】
    
58. 非常抱歉，我的回答还有误。感谢您的指正。以下是一个修正后的公式，可以实现您的需求：
    
59. 
    
60. =SUM(C2:INDEX(C:C,IF(ISBLANK(D3),ROWS(C:C)-ROW(C2)+1,MATCH(TRUE,NOT(ISBLANK(D3:$D$1048576))),ROWS(C:C)-ROW(C2)+1)))
    
61. 
    
62. 这个公式使用了INDEX函数和MATCH函数来动态计算求和范围的结束行号。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MATCH函数和NOT函数来获取D列下一个非空单元格的行号，然后使用IF函数计算要汇总的行数。如果为空，则使用ROWS函数和ROW函数计算要汇总的行数。最后，使用INDEX函数来获取求和范围。需要注意的是，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    
63. 
    
64. 【提问】
    
65. 还是同样错误，这个公式中的IF()函数多了一个参数
    
66. 
    
67. 【回答】
    
68. 非常抱歉，我的回答还有误。感谢您的指正。以下是一个修正后的公式，可以实现您的需求：
    
69. 
    
70. =SUM(C2:INDEX(C:C,IF(ISBLANK(D3),ROWS(C:C)-ROW(C2)+1,MATCH(TRUE,NOT(ISBLANK(D3:$D$1048576))),ROWS(C:C)-ROW(C2)+2)))
    
71. 
    
72. 这个公式使用了INDEX函数和MATCH函数来动态计算求和范围的结束行号。具体来说，这个公式会检查D列下一个单元格是否为空，如果不为空，则使用MATCH函数和NOT函数来获取D列下一个非空单元格的行号，然后使用IF函数计算要汇总的行数。如果为空，则使用ROWS函数和ROW函数计算要汇总的行数，并且需要将结果加1，以便包含当前行。最后，使用INDEX函数来获取求和范围。需要注意的是，这个公式假设要求和的单元格范围是C列中当前单元格以下的所有单元格。如果要求和的单元格范围不同，请根据实际情况修改公式中的单元格引用。
    

复制代码

橒♂蝣

lm3312 发表于 2023-3-23 11:18
公式不可用

看版本哈，我是21的

lm3312

橒♂蝣 发表于 2023-3-24 14:46
看版本哈，我是21的

哦，那只有等以后再用了，我还没装过21版的。