This question's explanation and your answers so far.
A さんは，製品 N の売上の動向を分析するため，昨年と今年の売上高の推移を基に，表計算ソフトを用いて図1のワークシートを作成した。 図1の D 列の移動合計欄には，当該月を含む過去12か月の売上の合計を，E 列の売上累計欄には今年の売上高の累計を計算する式が入力されている。また，製品 N の売上の動向を視覚的に確認するため，図2の売上高の推移グラフを作成した。 図1のセルD3に入力されている計算式として，適切なものはどれか。ここで，セルD2には計算式“B14+C2-B2”が入力されており，セルD3の計算式はセルD4～D13に複写して利用する。
※ Our check while writing this explanation could not fully confirm the published answer; scoring follows IPA's official key.
B3+C3 merely adds February sales of the previous year and February sales of the current year, so it is no more than two months' worth of value. The range of the most recent 12 months that a moving total requires is expressed nowhere, and it does not reference D2, the moving total on the row above, so it is not an update that carries the accumulated value forward. Even when copied to D4 through D13, each row only adds B and C on the same row, which does not match at all the meaning of the moving total that column D should express.
正解 The correct answer is イ, “D2+C3-B3”. Column D in Figure 1 is the moving total, that is, a column listing the total sales of the most recent 12 months including that month. Column B holds each month's sales for the previous year and column C holds each month's sales for the current year; rows 2 to 13 correspond to January through December, and row 14 is the annual total. A moving total does not have to be recalculated by adding up 12 months every time. You can update it by taking the moving total on the row above, adding the current year's sales for that month, and subtracting the sales of the same month in the previous year, which is the month 12 months earlier. This is the key point of the question. Only D2, the first row, has no moving total above it, so it uses a special first-term formula that takes the previous year's annual total as its base, adds January of the current year, and subtracts January of the previous year. From D3 on, however, the row above already holds a moving total, so D3 only has to add C3 (February of the current year) to the moving total D2 on the row above and subtract B3 (February of the previous year) — that is, D2+C3-B3. In addition, the condition says this formula is copied to D4 through D13 for use, so every reference must be a relative reference. Choice イ uses relative references throughout, so copying it to D4 automatically gives D3+C4-B4 and D5 gives D4+C5-B5, shifting one row at a time, and on every row it keeps the same meaning: the moving total of the row above + the current year's month − the previous year's same month. Only イ is correct in meaning and also survives being copied, so イ is the appropriate choice.
This is the trap of reusing the first-term formula in D2 as it is for D3, making B14 an absolute reference so that the base does not shift when copied; it is the most tempting wrong answer. But B14 is the annual total of the previous year's sales, and it is nothing more than the base used only to build D2, the starting point of the moving total. What D3 and later rows should take as their base is D2, the moving total on the row above, not B14. In fact, copying this formula to D4 gives $B$14+C4-B4, so every row simply adds one month of the current year to the previous year's annual total and subtracts one month of the same month a year earlier; the month-by-month replacement does not accumulate, so the result is not a total of the most recent 12 months. The property that an absolute reference keeps pointing at the same cell even after copying backfires here.
Looking only at row D3, SUM(B4〜B13)+SUM(C2〜C3) covers 10 months from March to December of the previous year plus 2 months from January to February of the current year, so it does indeed pick up 12 months in total. However, it cannot satisfy the condition that the formula in D3 is copied to D4 through D13 for use. Because the ranges are specified with relative references, copying it to D4 shifts the whole range down one row to SUM(B5〜B14)+SUM(C3〜C4), pulling in B14 on row 14, which is not a month but the annual total. A formula that is correct for one row but does not survive copying is inappropriate; if you write it as an update formula that inherits the result of the row above, the meaning is preserved no matter which row it is copied to.