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


スポンサードリンク

問題    topへ

問題1の解答例    topへ

AVERAGEIF関数を使う

  1. 平均するセルの左には「数量」と「金額」と入力されているので、AVERAGEIF関数で計算することができます。
    AVERAGEIF関数はExcel2007で追加された関数です。
    D15セルは =AVERAGEIF($C$3:$C$14,C15,$D$3:$D$14) としました。
    D16セルは =AVERAGEIF($C$3:$C$14,C16,$D$3:$D$14) としました。
    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)

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

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

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

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

  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]で数式を確定して、配列数式にします。

統合の機能を使う

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

問題2の解答例    topへ

AVERAGEIF関数を使う

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

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

  1. 3行目ごとの合計値を3行ごとの行数で割って求めることができます。
    D15セルは =SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*(D3:D14))/SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*1) としました。

3行ごとの平均値を求める(配列数式を使う)

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

統合の機能を使う

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

よねさんのExcelとWordの使い方エクセル練習問題:目次|1行おきに平均する