- Home »
- エクセル練習問題：目次 »
- 1行おきに平均する
- 離れたセル同士、飛び飛びのセルの平均を出す方法を考えてみます。
基本はAVERAGE関数で平均を計算するのですが、ポイントは飛び飛びのセルのデータをどのように取り出すかになります。
共通の行見出しがあれば、AverageIf関数、行位置が特定できるのならFilter関数・・・といったことが考えられます。
- なお、0を除外した平均値を求める方法も追加しています。
更新:2024/8/27;作成:2014/11/21
- 問題1：下図のようなデータを渡されました。 問題1の解答
D15:D16セルの数量と金額の平均値を計算しなさい。
|
B |
C |
D |
| 2 |
品名 |
数量/金額 |
結果 |
| 3 |
みかん |
数量 |
631 |
| 4 |
|
金額 |
433,933 |
| 5 |
バナナ |
数量 |
696 |
| 6 |
|
金額 |
311,319 |
| 7 |
ぶどう |
数量 |
296 |
| 8 |
|
金額 |
270,128 |
| 9 |
パイナップル |
数量 |
169 |
| 10 |
|
金額 |
119,913 |
| 11 |
マンゴー |
数量 |
973 |
| 12 |
|
金額 |
423,365 |
| 13 |
桃 |
数量 |
129 |
| 14 |
|
金額 |
75,764 |
| 15 |
平均 |
金額 |
|
- 問題2：下表の金額の平均値を計算しなさい。 問題2の解答
下表の青の行(金額)を平均して、15行目に結果を表示します。
|
B |
C |
D |
E |
F |
G |
| 2 |
|
日付 |
4月10日 |
4月11日 |
4月12日 |
4月13日 |
| 3 |
山田 |
時間 |
8:00 |
5:00 |
7:00 |
6:00 |
| 4 |
|
数量 |
40 |
30 |
37 |
44 |
| 5 |
|
金額 |
32,000 |
24,000 |
29,600 |
35,200 |
| 6 |
今野 |
時間 |
5:00 |
8:00 |
2:00 |
6:00 |
| 7 |
|
数量 |
50 |
66 |
22 |
45 |
| 8 |
|
金額 |
40,000 |
52,800 |
17,600 |
36,000 |
| 9 |
植田 |
時間 |
5:00 |
5:00 |
4:00 |
5:00 |
| 10 |
|
数量 |
42 |
40 |
37 |
42 |
| 11 |
|
金額 |
33,600 |
32,000 |
29,600 |
33,600 |
| 12 |
海江田 |
時間 |
4:00 |
3:00 |
8:00 |
8:00 |
| 13 |
|
数量 |
24 |
25 |
51 |
44 |
| 14 |
|
金額 |
19,200 |
20,000 |
40,800 |
35,200 |
| 15 |
平均 |
金額 |
|
|
|
|
- この問題のどこに注目するかで、さまざまな方法で計算することができます。
AVERAGEIF関数を使う
- 平均するセルの左には「数量」と「金額」と入力されているので、AVERAGEIF関数で計算することができます。
AVERAGEIF関数はExcel2007で追加された関数です。
D15セルは =AVERAGEIF(C3:C14,C15,D3:D14) となります。
- =AVERAGEIF(C3:C14,"金額",D3:D14) でもOKです。
D15セルの数式はAVERAGEIF関数の引数ダイアログボックスを使う場合は、範囲に $C$3:$C$14 、条件に C15 、平均対象範囲に $D$3:$D$14 と入力します。
範囲と平均対象範囲は絶対参照にして、D16セルへコピーできるようにします。
- ちなみに、0を除外したいときはAVERAGEIFS関数を使って条件を加えます。
=AVERAGEIFS(D3:D14,C3:C14,C15,D3:D14,"<>0")
FILTER関数を使って奇数行の合計・偶数行の平均値を計算する
- Filter関数で2行目のデータを取り出して、AVERAGE関数で平均を計算します。
簡単な数式になっています。
2行目は MOD(ROW(D3:D14),2)=0 と、行番号を2で割って余りが0 としています。
Filter関数で FILTER(D3:D14,MOD(ROW(D3:D14),2)=0) とすると、2行目の金額を取り出すことができます。
あとはこの金額をAVERAGE関数で平均を計算するだけです。
D15セルに =AVERAGE(FILTER(D3:D14,MOD(ROW(D3:D14),2)=0)) と入力します。
- ちなみに、0を除外したいときは"0でない"という条件を加えます。
=AVERAGE(FILTER(D3:D14,(MOD(ROW(D3:D14),2)=0)*(D3:D14<>0)))
SUMPRODUCT関数で奇数行の平均値・偶数行の平均値を計算する
- 数量は奇数行、金額は偶数行に入力されているので、奇数行の合計と偶数行の合計を求め、行数で割ると平均値を求めることができます。
計算する「金額」は偶数行なので、ISEVEN関数で判定ができます。
偶数行の合計は =SUMPRODUCT((ISEVEN(ROW(D3:D14)))*(D3:D14)) となります。
偶数行の数は SUMPRODUCT((ISEVEN(ROW(D3:D14)))*1) で求めることができます。
D15セルは
=SUMPRODUCT((ISEVEN(ROW(D3:D14)))*(D3:D14))
/SUMPRODUCT((ISEVEN(ROW(D3:D14)))*1)
となります。
- ちなみに、行番号が奇数である時にはISODD関数を使います。
奇数行の平均値は
=SUMPRODUCT((ISODD(ROW(D3:D14)))*(D3:D14))
/SUMPRODUCT((ISODD(ROW(D3:D14)))*1)
で求めることができます。
SUMPRODUCT関数で奇数行の平均値・偶数行の平均値を計算する（その2）
- 上記と同じですが、行の奇数と偶数は 行番号を2で割って余りが 1のときは奇数、0のときは偶数 となる、と考えることができます。
D15セルは偶数行の平均を求めるので、
=SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*(D3:D14))
/SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*1)
としました。
- ちなみに、奇数行の平均は
=SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*(D3:D14))
/SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*1)
となります。
配列数式を使って奇数行の平均値・偶数行の平均値を計算する
- 奇数偶数で計算するのに、配列数式を使って求めることもできます。
偶数行の平均はD15セルに =AVERAGE(IF((MOD(ROW(D3:D14),2)=0),D3:D14)) と入力して、[Ctrl]+[Shift]+[Enter]で数式を確定して、配列数式にします。
- 奇数行の平均は =AVERAGE(IF((MOD(ROW(D3:D14),2)=1),D3:D14)) と入力して、[Ctrl]+[Shift]+[Enter]で数式を確定して、配列数式にします。
統合の機能を使う
- ただし、この統合を使った場合は表中の元データが変更されても自動で再計算されませんので、再度統合を実行する必要があります。
- 計算式を使わずに求めることもできます。統合の機能を利用します。
C15セルを選択します。
- 「データ」タブの[総合]を実行します。
- 集計の方法で「平均」を選択します。
統合元の範囲で C3:D14セルを指定します。 (この画面ではSheet2で操作しているので Sheet2!C3:D14となっています)
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
- 計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。
AVERAGEIF関数を使う
- 合計するセルの左には「金額」と入力されているので、SUMIF関数で計算することができます。
D15セルは =AVERAGEIF($C$3:$C$14,$C15,D3:D14) としました。
D15セルの数式を右方向へオートフィルしてコピーすれば完成です。
FILTER関数を使って3行目ごとの平均値を計算する
- 3行目ごとの金額は行番号を3で割って余りが2となるところになります。
数式 (MOD(ROW(D3:D14),3)=2) という条件を満たせばよいことになります。
Filter関数でこれらの金額を取り出して、Average関数で平均を計算します。
D15セルは =AVERAGE(FILTER(D3:D14,MOD(ROW(D3:D14),3)=2)) と入力します。
D15:G15セルを選択して、[Ctrl]+[R]で右のセルへコピーします。
SUMPRODUCT関数を使って3行目ごとの平均値を求める
- 3行目ごとの合計値を3行ごとの行数で割って求めることができます。
計算する値は 5,8,11,14行目のです。3で割ると余りが2になります。
(MOD(ROW(D3:D14),3)=2) という条件を満たせばよいことになります。
- D15セルは
=SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*(D3:D14))
/SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*1)
としました。
配列数式を使って3行ごとの平均値を求める
- 3行おきに計算するのに、配列数式を使って求めることもできます。
D15セルに =AVERAGE(IF((MOD(ROW(D3:D14),3)=2),D3:D14)) と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。
統合の機能を使う
- ただし、この統合を使った場合は表中の元データが変更されても自動で再計算されませんので、再度統合を実行する必要があります。
- 計算式を使わずに求めることもできます。統合の機能を利用します。
C15セルを選択します。
ポイントはC15セルに合計する行と同じく、 金額 と入力されていることと、このセルを選択していることが必要です。
- 「データ」タブの[統合]を実行します。
- 集計の方法が「平均」になっているのを確認します。
統合元の範囲で C3:G14セルを指定します。
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。(この画面ではSheet2で操作しているので Sheet2!$C$3:$G$14となっています)
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
- 計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。
スポンサードリンク
Home｜エクセル練習問題：目次｜1行おき、3行ごとの平均を求める
PageViewCounter
Since2006/2/27