1行おきに平均する:エクセル練習問題

問題　　　　topへ

問題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	平均	数量
16		金額

問題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	平均	金額

問題1の解答例　　　　 topへ

この問題のどこに注目するかで、さまざまな方法で計算することができます。
解答例は下図のようになります。

AVERAGEIF関数を使う

平均するセルの左には「数量」と「金額」と入力されているので、AVERAGEIF関数で計算することができます。
AVERAGEIF関数はExcel2007で追加された関数です。
D15セルは　=AVERAGEIF($C$3:$C$14,C15,$D$3:$D$14)　として、フィルハンドルを下へドラッグして数式をコピーします。

=AVERAGEIF($C$3:$C$14,"数量",$D$3:$D$14)　でもOKです。

D16セルは　=AVERAGEIF($C$3:$C$14,C16,$D$3:$D$14)　となります。

=AVERAGEIF($C$3:$C$14,"金額",$D$3:$D$14)　でもOKです。

	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	平均	数量	=AVERAGEIF($C$3:$C$14,C15,$D$3:$D$14)
16		金額	=AVERAGEIF($C$3:$C$14,C16,$D$3:$D$14)

D15セルの数式はAVERAGEIF関数の引数ダイアログボックスを使う場合は、範囲に　$C$3:$C$14　、条件に　C15　、平均対象範囲に $D$3:$D$14　と入力します。
範囲と平均対象範囲は絶対参照にして、D16セルへコピーできるようにします。

奇数行の合計・偶数行の平均値を計算する(SUMPRODUCT,ISODD,ISEVEN関数を使う)

数量は奇数行、金額は偶数行に入力されているので、奇数行の合計と偶数行の合計を求め、行数で割ると平均値を求めることができます。
ISODD関数で行番号が奇数である時に合計すると考えると、=SUMPRODUCT((ISODD(ROW(D3:D14)))*(D3:D14))　となります。
奇数行の数は SUMPRODUCT((ISODD(ROW(D3:D14)))*1) で求めることができます。
よって、平均値は　=SUMPRODUCT((ISODD(ROW(D3:D14)))*(D3:D14))/SUMPRODUCT((ISODD(ROW(D3:D14)))*1)　で求めることができます。
同様に、偶数はISEVEN関数で判定ができるので、　D16セルは　=SUMPRODUCT((ISEVEN(ROW(D3:D14)))*(D3:D14))/SUMPRODUCT((ISEVEN(ROW(D3:D14)))*1)　となります。

奇数行の合計・偶数行の合計で計算する(SUMPRODUCT,MOD,ROW関数を使う)

上記と同じですが、行の奇数と偶数は　行番号を2で割って余りが　1のときは奇数、0のときは偶数　となる、と考えることができます。
D15セルは　=SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*(D3:D14))/SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*1)　としました。
D15セルは　=SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*(D3:D14))/SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*1)　としました。

奇数行の合計・偶数行の平均値を計算する(配列数式を使う)

奇数偶数で計算するのに、配列数式を使って求めることもできます。
D15セルに　=AVERAGE(IF((MOD(ROW(D3:D14),2)=1),D3:D14))　と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。
D16セルは　=AVERAGE(IF((MOD(ROW(D3:D14),2)=0),D3:D14))　と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。

統合の機能を使う

計算式を使わずに求めることもできます。統合の機能を利用します。
C15:C16セルを選択します。
「データ」タブの[総合]を実行します。(下図はExcel2010の画像です)
集計の方法で「平均」を選択します。
統合元の範囲で　C3:D14セルを指定します。　(この画面ではSheet2で操作しているので　Sheet2!C3:D14となっています)
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。

問題2の解答例　　　　 topへ

AVERAGEIF関数を使う

合計するセルの左には「金額」と入力されているので、SUMIF関数で計算することができます。
D15セルは　=AVERAGEIF($C$3:$C$14,$C15,D3:D14)　としました。
D15セルの数式を右方向へオートフィルしてコピーすれば完成です。

SUMPRODUCT,MOD,ROW関数を使って3行目ごとの平均値を求める

3行目ごとの合計値を3行ごとの行数で割って求めることができます。
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セルに合計する行と同じく、　金額　と入力されていることと、このセルを選択していることが必要です。
「データ」タブの[統合]を実行します。(下図はExcel2016の画像です)
集計の方法が「平均」になっているのを確認します。
統合元の範囲で　C3:G14セルを指定します。　
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。(この画面ではSheet1で操作しているので　Sheet1!$C$3:$G$14となっています)
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。