This question's explanation and your answers so far.
セル A1 ～ A10 に表のような数値が入力されており，セル B1 ～ B10 に計算式が入力されている。このときのセル B1 ～ B10 の計算結果とセル C1 ～ C10 の計算結果がそれぞれ同じ数値となるようにするために，最初にセル C10 に計算式を入力し，次にセル C10 をセル C1 ～ C9 に複写したい。セル C10 に入力する計算式として，正しいものはどれか。
| A | B | C | |
|---|---|---|---|
| 1 | 231 | A1 | |
| 2 | 156 | B1+A2 | |
| 3 | 132 | B2+A3 | |
| 4 | 73 | B3+A4 | |
| 5 | 49 | B4+A5 | |
| 6 | 32 | B5+A6 | |
| 7 | 21 | B6+A7 | |
| 8 | 15 | B7+A8 | |
| 9 | 13 | B8+A9 | |
| 10 | 9 | B9+A10 |
正解 Column B is building a running total. Since B1=A1, B2=B1+A2, ... B10=B9+A10, the value of B_n ends up equal to SUM(A1~A_n) (B1=231, B2=387, ... B10=731). So column C only has to produce SUM(A1~A_n) on each row as well. The key here is the spreadsheet rule that when a formula is copied, a relative reference shifts its row, while an absolute reference marked with $ does not. If you enter SUM(A$1~A10) of choice ア into C10, the start point A$1 has an absolute row, so it stays A1 wherever it is copied, while the end point A10 is relative, so copying from C10 to C_n (a shift of n-10 rows) turns it into A_n. The formula in C_n therefore becomes SUM(A1~A_n), giving C1=231, C2=387, ... C10=731, matching the results of column B row for row. A running total has a fixed start point and an end point on its own row, and ア expresses exactly that shape by fixing only the row of the start point with $.
SUM(A$1~A$10)-SUM($A$1~A10) subtracts SUM(A1~A_n) from the grand total 731, so the result is SUM(A_n+1~A10), a running total that runs in the opposite direction, namely the sum of the rows below. In C10 it gives 731-731=0, which does not match the 731 in B10, and in C1 it gives 731-231=500, which does not match the 231 in B1. The direction of the subtraction is reversed, so it does not produce the top-down running total that is wanted.
SUM(A$1~A$10)-SUM($A$10~A10) subtracts the range from A_n to A10 from the grand total 731, so it yields SUM(A1~A_n-1), a running total that stops one row short. In C10 it gives 731-9=722 (which is the value of B9), and in C1 it gives 731-731=0, so it is always off by one row from column B. In C1 there is nothing left to add at all, so it becomes 0, which does not match B1=231.
SUM(A$1~A$10)-SUM($A$1~A10)+A10 takes the reverse running total of choice イ and adds the value of its own row back in, so it amounts to SUM(A_n~A10). In C10 it gives 731-731+9=9, far from the 731 in B10, and in C1 it gives 731-231+231=731, which does not match the 231 in B1. The direction of the running total is still downward, so adding +A10 still does not make it agree with column B.