問76
頻出度★★☆
ある商品の月別の販売数を基に売上に関する計算を行う。セル B1に商品の単価が，セル B3～B7に各月の商品の販売数が入力されている。セル C3に計算式 “B$1*合計(B$3：B3)/個数(B$3：B3)” を入力して，セル C4～C7に複写したとき，セル C5に表示される値は幾らか。
| A | B | C | |
|---|---|---|---|
| 1 | 単価 | 1,000 | |
| 2 | 月 | 販売数 | 計算結果 |
| 3 | 4月 | 10 | |
| 4 | 5月 | 8 | |
| 5 | 6月 | 0 | |
| 6 | 7月 | 4 | |
| 7 | 8月 | 5 |
令和元年度 秋期 ITパスポート試験 問76
テクノロジ系ソフトウェア › オフィスツール
This question's explanation and your answers so far.
解説
問 76
Incorrectア
6 is SUM(B$3:B5)÷COUNT(B$3:B5)＝18÷3＝6, that is, the average sales quantity itself — a value that forgot to multiply by the leading unit price B$1 (＝1,000). The formula includes B$1*, so multiplying by 1,000 is required.
- イ
正解 When the formula in C3, “B$1*SUM(B$3：B3)/COUNT(B$3：B3)”, is copied down to C5, the upper ends B$1 and B$3 whose rows are fixed with $ do not move, and only the lower end B3, a relative reference, extends downward according to the row number. Therefore the formula in C5 becomes “B$1*SUM(B$3：B5)/COUNT(B$3：B5)”. SUM(B$3:B5)=10+8+0=18 and COUNT(B$3:B5)=3 (the COUNT function also counts a cell whose value is 0 as one item). Hence C5＝1,000×18÷3＝1,000×6＝6,000, and the correct answer is イ. This formula computes “unit price×(cumulative sales quantity÷number of months)＝unit price×average sales quantity”, displaying 6,000, which is the average sales quantity of 6 up to June multiplied by the unit price of 1,000.
Incorrectウ
9,000 is the value obtained by mistaking COUNT(B$3:B5) for 2 and computing 1,000×18÷2＝9,000. COUNT counts any cell that contains a number, including one whose value is 0, as one item, so the count for B3 to B5 is 3. This error comes from excluding the 0 and counting only 2.
Incorrectエ
18,000 is 1,000×SUM(B$3:B5)＝1,000×18＝18,000, a value that ignores the ÷COUNT division. The formula includes /COUNT(B$3：B3), so it must be divided by the count.
ここがポイント
- Copying (relative and absolute references): rows/columns marked with $ stay fixed when copied, while references without $ shift in the copy direction. In B$3：B3 the upper end is fixed and the lower end is relative, so the range extends.
- Note that the COUNT function counts the number of cells that contain a numeric value, including cells whose value is 0 as one item.
用語