エクセル練習問題: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 合計 金額 124,800 128,800 117,600 140,000

問題1の解答例 topへ

• この問題のどこに注目するかで、さまざまな方法で計算することができます。

SUMIF関数を使う

• 計算するのに共通の条件がある場合はSUMIF関数がつかます。
• 合計するセルの左には「金額」と入力されているので、SUMIF関数で計算することができます。
  D15セルは =SUMIF(C3:C14,C15,D3:D14) としました。
  • SUMIF関数の構文 =SUMIF(検索範囲,検索条件,合計範囲)
    詳細な使い方は 条件付きで合計する関数(SUMIF関数)の使い方 をご覧ください。
• SUMIF関数の引数 ダイアログボックスを使う場合は下図のように設定します。
  

SUMIFS関数を使うこともできます。

• SUMIFS関数を使うこともできます、引数の順番がSUMIF関数とは異なりますのでご注意ください。
  D15セルは =SUMIFS(D3:D14,C3:C14,C15) となります。
  
  • SUMIFS関数の構文 =SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)
    詳細な使い方は 複数の条件で合計する関数(SUMIFS関数)の使い方:Excel関数 をご覧ください。
• SUMIFS関数の引数 ダイアログボックスを使う場合は下図のように設定します。
  

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

• 数量は奇数行、金額は偶数行に入力されているので、奇数行の合計と偶数行の合計を求めると考えることができます。
  合計する金額は偶数行に入力されているので、 偶数行はISEVEN関数で判定ができるので、
  D15セルは =SUMPRODUCT(ISEVEN(ROW(D3:D14))*(D3:D14)) となります。
  • 奇数である時に合計する場合はISODD関数を使い、
    =SUMPRODUCT(ISODD(ROW(D3:D14))*(D3:D14)) となります。

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

• この方法は3行おき、4行おきといった計算にも使える方法です。
• 上記と同じですが、行の奇数と偶数は 行番号を2で割って余りが 1のときは奇数、0のときは偶数 となる、と考えることができます。
  D15セルは偶数行に入力されて金額を合計するので、
  =SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*(D3:D14)) としました。
  • 奇数行を合計する場合は =SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*(D3:D14)) とします。

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

• ISEVEN関数とISODD関数を使って、奇数行/偶数行を判定して計算します。
• 奇数偶数で計算するのに、配列数式を使って求めることもできます。
  Excel for Microsoft365,Excel2021以降の動的配列数式が利用できるバージョンでは
  D15セルは偶数行の合計を計算するので
  =SUM(ISEVEN(ROW(D3:D14))*D3:D14)
  と入力します。
• Excel2019以前のバージョンは、[Shift]+[Ctrl]+[Enter]で数式を確定して、
  {=SUM(ISEVEN(ROW(D3:D14))*D3:D14)}
  と、配列数式にします。
  • 奇数行の合計は、Excel for Microsoft365,Excel2021以降の動的配列数式が利用できるバージョンでは
    =SUM(ISODD(ROW(D3:D14))*D3:D14) と入力します。
    Excel2019以前のバージョンは、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。

奇数行の合計・偶数行の合計で計算する(FILTER関数を使う)

• FILTER関数はExcel for Microsoft365、Excel2021で使用できる関数です。
  詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
• 行番号が奇数のデータをFilter関数で抽出して、Sum関数で合計しています。
• 金額は偶数行に入力されているので、MOD(ROW($D3ドル:$D14ドル),2)=0 で判定しています。
  =SUM(FILTER($D3ドル:$D14,ドルMOD(ROW($D3ドル:$D14ドル),2)=0))
  となります。
  • 奇数の行を合計する場合は
    =SUM(FILTER($D3ドル:$D14,ドルMOD(ROW($D3ドル:$D14ドル),2)=1))
    となります。

統合の機能を使う

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

問題2の解答例 topへ

SUMIF関数を使う

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

SUMPRODUCT,MOD,ROW関数を使って3行目ごとに合計する

1. D15セルは =SUMPRODUCT((MOD(ROW(D3:D14),3)=2)*(D3:D14)) としました。
   
   ↓
   計算の過程は、3行ごとに計算したいので、行番号を3で割るとあまりが 2 となる行を調べます。
   数式は (MOD(ROW(D3:D14),3)=2 となります。
   この部分は下図のように、{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE} と該当行はTRUEが返されます。
   FALSE=0 TRUE=1 として、各セルとの積を合計します。
   

3行ごとに合計する(配列数式を使う)

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

統合の機能を使う

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

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