This question's explanation and your answers so far.
表計算ソフトを用いて，天気に応じた売行きを予測する。表は，予測する日の天気（晴れ，曇り，雨）の確率，商品ごとの天気別の売上予測額を記入したワークシートである。セルE4に商品Aの当日の売上予測額を計算する式を入力し，それをセルE5～E6に複写して使う。このとき，セルE4に入力する適切な式はどれか。ここで，各商品の当日の売上予測額は，天気の確率と天気別の売上予測額の積を求めた後，合算した値とする。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 天気 | 晴れ | 曇り | 雨 | |
| 2 | 天気の確率 | 0.5 | 0.3 | 0.2 | |
| 3 | 商品名 | 晴れの日の売上予測額 | 曇りの日の売上予測額 | 雨の日の売上予測額 | 当日の売上予測額 |
| 4 | 商品A | 300,000 | 100,000 | 80,000 | |
| 5 | 商品B | 250,000 | 280,000 | 300,000 | |
| 6 | 商品C | 100,000 | 250,000 | 350,000 |
Since all references are relative, copying to E5 changes it to B3*B5+C3*C5+D3*D5. The references that should point to the weather probabilities shift to row 3 (the heading “Product name” row), so they cannot be multiplied by the probabilities and the correct expected value cannot be obtained. Incorrect.
正解 The formula in cell E4 represents the value obtained by taking, for each weather, the weather probability × the predicted sales amount for that weather, and then summing the results for all three types of weather. The probabilities are in row 2 (B2=0.5, C2=0.3, D2=0.2), and product A's sales by weather are in row 4 (B4, C4, D4), so the basic form of the formula is B2*B4+C2*C4+D2*D4. When this formula is copied downward from E4 to E5 and E6, the row numbers increase by one each time. The references to the predicted sales amounts (B4, C4, D4) should shift to the rows of product B and product C, so leaving them as relative references is fine; but the references to the weather probabilities should always point to row 2, so only the row number needs to be fixed. Therefore, by making the row an absolute reference — B$2, C$2, D$2 — even after copying, B$2 and the others keep pointing to row 2, and only the sales side shifts correctly to B5 and B6. Since the copying stays within the same column E, the column does not change, so fixing the column is unnecessary. Thus, イ, B$2*B4+C$2*C4+D$2*D4, is correct.
This formula shifts the probability side down (in $B2, the row number 2 is relative) while fixing the sales side (B$4). The side that should be fixed and the side that should shift are reversed: after copying, the probability side shifts to row 3, while the sales side always stays at row 4 (product A). Even in E5 and E6 it uses product A's sales, so products B and C cannot be calculated. Incorrect.
Since all references are absolute, even after copying it stays as $B$2*$B$4+… without any change. E5 and E6 also keep displaying product A's values, so the predicted sales amounts for the day for products B and C are not calculated. Incorrect.