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関数の引数ダイアログボックスを使う場合は、範囲に $C3ドル:$C14ドル 、条件に C15 、平均対象範囲に $D3ドル:$D14ドル と入力します。
   範囲と平均対象範囲は絶対参照にして、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($C3ドル:$C14,ドル$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!$C3ドル:$G14ドルとなっています)
   統合の基準で「左端列」にチェックを入れます。
   [OK]ボタンをクリックします。
   
4. 計算結果が表示されました。
   計算式(数式)を使っていませんので、セルには結果の値が入力されます。