求救各位excel大神D栏计算出各区分坪数所需缴纳之公共电费后，四捨五入进位，然而这样总是就会超过C栏的总电费，就是会多1元，如何透过函数让数据呈现如E栏，加总起来不超过C栏总电费感谢各位大神协助谢谢

4 个回答

  • 旧至新
  • 新至旧
  • 最高Like数

0

rc0603

iT邦研究生 2 级 ‧ 2025-02-21 17:42:14

这个问题是看要把差异数调整给哪一栏，例如调整到最后一栏
三栏算出
E1=347
E2=338
E3=(1358-347-338)=672

2

ccenjor

iT邦大师 8 级 ‧ 2025-02-21 19:40:08

我是以如果总和多1时，取最小的那个值减1。
E1:
=IF((SUM($D$2:$D$4)>$C$2)*(D2=SMALL($D$2:$D$4,1)),D2-1,D2)
向下複製到E2:E3。


  • 2

浅水员

iT邦大师 6 级 ‧
2025-02-22 01:42:13

请问为什么是调整最小的数字而不是最大的数字？
我的想法是调整最大的数字影响应该比较小

以前当学生在画圆饼图时
好像也是调整最大的项目让总和变成100%

修改

ccenjor

iT邦大师 8 级 ‧
2025-02-22 13:55:15

谢谢!这要看作者想法，其实要最大或最小或指定第几大第几小都可以，我这只是说明我的解法。

修改

0

ming9900

iT邦新手 3 级 ‧ 2025-02-23 14:39:11

以下是问deepseek 的回答，我没有印证，原post可以试试看.

以下为解决公共电费分摊四捨五入后总和溢价的完整方案：

问题核心

当直接对各分区电费（D栏）四捨五入到整数（E栏）时，总和可能因进位多出1元。需动态调整使 SUM(E栏) = C栏总电费。

解决思路

  1. 初步四捨五入
    先计算所有分区的基础四捨五入值。
  2. 识别溢价差异
    检查总和是否超出总电费（通常多1元）。
  3. 动态修正溢价
    若溢价存在，找到「四捨五入时小数部分最接近0.5」的项目，将其减1元。

具体步骤与公式

步骤1：计算基础分摊金额（D栏）

假设：

  • 总坪数在 A2
  • 分区坪数在 B2:B100
  • 总电费在 C2

每户分摊金额公式：

D2 = (B2 / $A$2) * $C$2

下拉填充至所有分区。

步骤2：四捨五入并动态修正（E栏）

使用组合公式直接输出修正后的结果：

=ROUND(D2, 0) - IFERROR(
   (SUMPRODUCT(ROUND(D$2:D$100, 0)) > $C$2) *
   (D2 - TRUNC(D2) = MAX(IF(ROUND(D$2:D$100, 0) - D$2:D$100 = 0.5, D$2:D$100 - TRUNC(D$2:D$100)))) *
   1,
   0
)

公式说明：

  1. ROUND(D2, 0)
    先对D栏四捨五入。
  2. SUMPRODUCT(ROUND(D$2:D$100, 0)) > $C$2
    检查四捨五入后总和是否超过总电费。
  3. MAX(IF(ROUND(D$2:D$100, 0) - D$2:D$100 = 0.5, ...))
    找到因四捨五入进位1元的项目（小数部分恰为0.5）。
  4. 对该项目减1元，消除溢价。

操作示意

A栏（总坪数） B栏（分区坪数） C栏（总电费） D栏（分摊金额） E栏（修正后结果）
1000 200 5000 1000 1000
1000 300 5000 1500 1500
1000 500 5000 2500 2499

说明：

  • 原始四捨五入总和：1000 + 1500 + 2500 = 5000（无需修正）。
  • 若D栏为 2500.5，四捨五入后总和变为 5001，则将最后一笔修正为 2499。

进阶验证

  1. 精确度测试
    将公式複製到所有分区后，检查 SUM(E栏) 是否严格等于 C栏。
  2. 极端值处理
    若多个分摊金额的小数均为0.5，公式会自动选择最后一个出现的项目进行修正（可调整 MAX 为 LARGE 控制优先级）。

替代方案（辅助列法）

若需更直观的操作，可新增辅助列标记修正项：

  1. F栏（标记溢价修正项）

    =IF(SUMPRODUCT(ROUND(D$2:D$100, 0)) > $C$2, 
   IF(D2 - TRUNC(D2) = MAX(IF(ROUND(D$2:D$100, 0) - D$2:D$100 = 0.5, D$2:D$100 - TRUNC(D$2:D$100)), 
   1, 0), 
   0
)
  2. E栏（最终结果）

    =ROUND(D2, 0) - F2

透过上述方法，可确保四捨五入后的总和严格等于公共电费总额，避免人工手动调整。

0

㊣浩瀚星空㊣

iT邦大神 1 级 ‧ 2025-02-24 15:56:45

我之前的做法不太一样。
一般小计的部份，我还是会保留原值及四捨五入值

在总计时，会个别统计
1.「小计原值加总」后四捨五入
2.「小计原值四捨五入值」

然后比对两值是否相等。
差异为多则扣除最大小计值。
差异为小则增加最小小计值

但在计算还是有争议的情况下。(我就不说是哪个行业，懂的人就懂)
则后来是小计还是会保留小数点2~4位数的显示。不直接整数化。

认真来说，原本四捨五入的做法，就只能做到最后统计才去处理。
如果个别先四捨五入再做加总处理。

一定会有出入的。如果小计区间多的话。
有时误差10元以上的情况，也是会发生的。

这就看待帐务人员该如何去整理这样的帐务了。该如何去处理