- Home »
- エクセル練習問題：目次 »
- 1行おきに合計する
- 問題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 |
合計 |
金額 |
124,800 |
128,800 |
117,600 |
140,000 |
- この問題のどこに注目するかで、さまざまな方法で計算することができます。
解答例は下図のようになります。
- 合計するセルの左には「数量」と「金額」と入力されているので、SUMIF関数で計算することができます。
D15セルは =SUMIF(C3:C14,C15,D3:D14) としました。
D16セルは =SUMIF(C3:C14,C16,D3:D14) としました。
- SUMIF関数の引数 ダイアログボックスを使う場合は下図のように設定します。
- D15セルに数式を入力して、D16セルにコピーする場合は、
D15セルには =SUMIF($C$3:$C$14,C15,$D$3:$D$14) と入力して、D16セルにコピーします。
D16セルは =SUMIF($C$3:$C$14,C16,$D$3:$D$14) となります。
- SUMIFS関数を使うこともできます、引数の順番がSUMIF関数とは異なりますのでご注意ください。
D15セルは =SUMIFS(D3:D14,C3:C14,C15) となります。
D16セルは =SUMIFS(D3:D14,C3:C14,C16) となります。
- SUMIFS関数の引数 ダイアログボックスを使う場合は下図のように設定します。
- 数量は奇数行、金額は偶数行に入力されているので、奇数行の合計と偶数行の合計を求めると考えることができます。
ISODD関数で行番号が奇数である時に合計すると考えると、
D15セルは =SUMPRODUCT(ISODD(ROW(D3:D14))*(D3:D14)) となります。
偶数はISEVEN関数で判定ができるので、
D16セルは =SUMPRODUCT(ISEVEN(ROW(D3:D14))*(D3:D14)) となります。
- 上記と同じですが、行の奇数と偶数は 行番号を2で割って余りが 1のときは奇数、0のときは偶数 となる、と考えることができます。
D15セルは =SUMPRODUCT((MOD(ROW(D3:D14),2)=1)*(D3:D14)) としました。
D15セルは =SUMPRODUCT((MOD(ROW(D3:D14),2)=0)*(D3:D14)) としました。
- 奇数偶数で計算するのに、配列数式を使って求めることもできます。
D15セルに =SUM(IF(MOD(ROW(D3:D14),2)=1,D3:D14,"")) と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。
D16セルは =SUM(IF(MOD(ROW(D3:D14),2)=0,D3:D14,"")) と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。
- FILTER関数はMicrosoft365で使用できる関数です。
詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
- 行番号が奇数のデータをFilter関数で抽出して、Sum関数で合計しています。
奇数の行を合計します。奇数は MOD(ROW($D$3:$D$14),2)=1 で判定しています。
=SUM(FILTER($D$3:$D$14,MOD(ROW($D$3:$D$14),2)=1))
- 偶数行を合計します。奇数は MOD(ROW($D$3:$D$14),2)=0 で判定しています。
=SUM(FILTER($D$3:$D$14,MOD(ROW($D$3:$D$14),2)=0))
となります。
統合の機能を使う
- 計算式を使わずに求めることもできます。統合の機能を利用します。
C15:C16セルを選択します。
- 「データ」タブの[総合]を実行します。(下図はExcel2010の画像です)
- 集計の方法が「合計」になっているのを確認します。
統合元の範囲で C3:D14セルを指定します。 (この画面ではSheet4で操作しているので Sheet4!C3:D14となっています)
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
- 計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。
- ただし、D3:D14の値を修正しても自動で再計算されませんので、もう一度 「統合」を実行する必要があります。
SUMIF関数を使う
- 合計するセルの左には「金額」と入力されているので、SUMIF関数で計算することができます。
D15セルは =SUMIF($C$3:$C$14,"金額",D3:D14) としました。
D15セルの数式を右方向へオートフィルしてコピーすれば完成です。
SUMPRODUCT,MOD,ROW関数を使って3行目ごとに合計する
- 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 として、各セルとの積を合計します。 SUM(D3*0,D4*0,D5*1,D6*0,D7*0,D8*1,D9*0,D10*0,D11*1,D12*0,D13*0,D14*1) と計算されます。
3行ごとに合計する(配列数式を使う)
- 3行おきに計算するのに、配列数式を使って求めることもできます。
D15セルに =SUM((MOD(ROW(D3:D14),3)=2)*(D3:D14)) と入力して、[Shift]+[Ctrl]+[Enter]で数式を確定して、配列数式にします。
- 計算式を使わずに求めることもできます。統合の機能を利用します。
C15セルを選択します。ポイントはC15セルに合計する行と同じく、 金額 と入力されていることと、このセルを選択していることが必要です。
- 「データ」タブの[総合]を実行します。(下図はExcel2016の画像です)
- 集計の方法が「合計」になっているのを確認します。
統合元の範囲で C3:G14セルを指定します。
[追加]ボタンをクリックして、統合元に選択して指定した範囲を登録します。(この画面ではSheet3で操作しているので Sheet3!$C$3:$G$14となっています)
統合の基準で「左端列」にチェックを入れます。
[OK]ボタンをクリックします。
- 計算結果が表示されました。
計算式(数式)を使っていませんので、セルには結果の値が入力されているのが分かります。
- ただし、この統合を使った場合は表中の元データが変更されても自動で再計算されませんので、再度「統合」を実行する必要があります。
スポンサードリンク
Home｜エクセル練習問題：目次｜1行おきに合計する
PageViewCounter
Since2006/2/27