1行おき、3行ごとの平均を求める：エクセル練習問題

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

• 問題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関数を使う

1. 平均するセルの左には「数量」と「金額」と入力されているので、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セルへコピーできるようにします。
   
2. ちなみに、0を除外したいときはAVERAGEIFS関数を使って条件を加えます。
   D7:D8セルを0に変更しています。
   =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関数で奇数行の平均値・偶数行の平均値を計算する

• かなり多くの関数を使っています。

1. 数量は奇数行、金額は偶数行に入力されているので、奇数行の合計と偶数行の合計を求め、行数で割ると平均値を求めることができます。
   計算する「金額」は偶数行なので、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）

1. 上記と同じですが、行の奇数と偶数は　行番号を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)
     となります。
     

配列数式を使って奇数行の平均値・偶数行の平均値を計算する

• 奇数偶数で計算するのに、配列数式を使って求めることもできます。
• Excel for Microsoft365,Excel2021以降のバージョンで動的配列数式が利用できる場合は
  偶数行の平均はD15セルに　=AVERAGE(IF((MOD(ROW(D3:D14),2)=0),D3:D14))
  奇数行の平均は =AVERAGE(IF((MOD(ROW(D3:D14),2)=1),D3:D14))
  と入力します。
  Excel2019以前のバージョンは
  [Ctrl]+[Shift]+[Enter]で数式を確定して、↓のように配列数式にします。
  {=AVERAGE(IF((MOD(ROW(D3:D14),2)=0),D3:D14))}
  {=AVERAGE(IF((MOD(ROW(D3:D14),2)=1),D3:D14))}
  
  

統合の機能を使う

• ただし、この統合を使った場合は表中の元データが変更されても自動で再計算されませんので、再度統合を実行する必要があります。

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

問題2の解答例　　　　topへ

AVERAGEIF関数を使う

1. 合計するセルの左には「金額」と入力されているので、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行目ごとの平均値を求める

1. 3行目ごとの合計値を3行ごとの行数で割って求めることができます。
   計算する値は 5,8,11,14行目のです。3で割ると余りが2になります。
   (MOD(ROW(D3:D14),3)=2)　という条件を満たせばよいことになります。
   
2. D15セルは　
   =SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*(D3:D14))
   /SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*1)
   　としました。
   

配列数式を使って3行ごとの平均値を求める

• 3行おきに計算するのに、配列数式を使って求めることもできます。
• Excel for Microsoft365,Excel2021以降のバージョンで動的配列数式が利用できる場合は
  D15セルに　=AVERAGE(IF((MOD(ROW(D3:D14),3)=2),D3:D14))
  と入力します。
• Excel2019以前のバージョンは
  [Ctrl]+[Shift]+[Enter]で数式を確定して、↓のように配列数式にします。
  {=AVERAGE(IF((MOD(ROW(D3:D14),3)=2),D3:D14))}
  
  

統合の機能を使う

• ただし、この統合を使った場合は表中の元データが変更されても自動で再計算されませんので、再度統合を実行する必要があります。

1. 計算式を使わずに求めることもできます。統合の機能を利用します。
   C15セルを選択します。
   ポイントはC15セルに合計する行と同じく、　金額　と入力されていることと、このセルを選択していることが必要です。
   
2. 「データ」タブの[統合]を実行します。
   
3. 集計の方法が「平均」になっているのを確認します。
   統合元の範囲で　C3:G14セルを指定します。　
   [追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。(この画面ではSheet2で操作しているので　Sheet2!$C$3:$G$14となっています)
   統合の基準で「左端列」にチェックを入れます。
   [OK]ボタンをクリックします。
   
4. 計算結果が表示されました。
   計算式(数式)を使っていませんので、セルには結果の値が入力されます。