如何用纯公式将数值（如100）拆分为12个无小数行且总和不变？

To split the value in cell A2 into 12 integer values (in cells B3:B14) without decimals, use this formula in cell B3 and drag it down to B14:

=IF(ROW()-2 <= MOD($A$2,12), INT($A$2/12)+1, INT($A$2/12))

How It Works: #

• INT($A$2/12): Calculates the base whole number when dividing the total by 12 (e.g., 100/12 = 8.333 → 8).
• MOD($A$2,12): Finds the remainder after division (e.g., 100 mod 12 = 4). This tells us how many cells need an extra 1 to account for the remainder.
• ROW()-2: Returns the position of the current cell in the 12-row sequence (B3 = position 1, B14 = position 12).
• The IF condition checks if the current position falls within the remainder count. If yes, it uses base +1; otherwise, it uses the base value.

Example: #

• If A2 = 100: Base value is 8, remainder is 4. Cells B3-B6 will be 9 (4 cells), B7-B14 will be 8 (8 cells). Total: 4×9 + 8×8 = 100.
• If A2 = 120 (no remainder): All cells B3-B14 will be 10, since MOD(120,12)=0.

This formula ensures all values are integers, sums back to the original total, and works entirely within Excel formulas (no scripts needed).

内容的提问来源于stack exchange，提问作者Minhas Mehmood