SUMIFS関数 複数の条件を指定して数値を合計する

25-Dec-2020

 

複数の条件を指定して、その全ての条件を満たした数値を合計する関数。それが今回ご紹介させていただきます、SUMIFS関数でございます。

たとえば売上表で、売上金額を月別で集計したり、取引先別で集計したり、はたまたその両方に当てはまる集計を算出したりと、大量のデータを扱う方なら必須の関数でございます。

 



 




 

SUMIFS関数とは

下の図は某府の、ある職種の入札予定価格をデータにまとめています。

入札結果

 

SUMIFS関数でできることは、例えば上の図の一覧の中から、赤文字箇所、細別が「区画線」となっているものだけの「予定価格」の合計を算出したい。だとか、色が多くて見にくいですが、下図のように、「区画線」の月別での合計値も算出できます。

入札結果

データ量が多ければ多いほど、使用用途が広がる関数と言えます。

 




 

 

SUMIFS関数使い方

まずは下図、赤囲い「細別」ごとの予定価格合計をSUMIFS関数を使って算出します。

入札結果

 

SUMIFS関数
=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,・・・)

 



 

① 合計対象範囲の選択

合計を表示させたいセルに「=SUMIFS(」と入力し、合計対象範囲、ここでは「予定価格」の列(G2:G32)を選択し、[F4]キーを押して絶対参照にします。(表示が(G2:G32)から($G$2:$G$32)変わる)

SUMIFS 合計対象範囲

 



 

② 条件範囲の選択

=SUMIFS($G$2:$G$32」の続きに「,」を入力し、条件範囲、ここでは「細別」ごとに集計を出したいので、「細別」の列(E2:E32)を選択し、[F4]キーを押して絶対参照にします。(表示が(E2:E32)から($E$2:$E$32)に変わる)

SUMIFS 条件範囲

 


③ 条件の選択

=SUMIFS($G$2:$G$32,$E$2:$E$32」の続きに「,」を入力し、条件、ここでは「細別」の各項目の集計を出したいので、「細別」の各項目を指定します。下図のように「細別」の各項目を入力したセルをクリックし「)」で閉じます。「細別」の各項目の指定は、セルを指定する代わりに「”区 画 線”」など、細別に記載されている文字列を を前後につけて入力しても可能です。

SUMIFS 条件

 



 

④ 「細別」ごとの集計結果

表内の「区画線」の合計が算出されました。

区画線合計

 

①~③で入力した数式を下にコピペします。

区画線合計

 

上図赤矢印緑▮をダブルクリックします。

コピー完了

 

下に同じ数式がコピペされ、各細別ごとの合計が表示されました。ちなみに「鋼矢板」は、エクセル画面のスクショの都合上でカットした範囲にあったのですが、細別ごとの合計を算出するまで消えていることに気づいてませんでした。

合計数一致

 

念のため合計を比較すると、一致していることが分かります。

 

 




 

 

SUMIFS関数使い方 複数の条件の場合

「細別」ごとに集計を出しましたが、月ごとではどうでしょうか。
細別かつ月ごとの集計を下の表に算出していきます。

細別・月別集計表

 

まずは月ごとの集計を算出するために、各行の「公告月」をシート上に表示させます。「公告日」はあらかじめ表示されていますので、「MONTH関数」で「公告日」から「月」のみを抽出します。

MONTH関数

 

「公告月」を表示させました。

月表示完了

 



 

① 合計対象範囲・条件範囲1・条件1の選択

合計を表示させたいセルに「=SUMIFS(」と入力し、合計対象範囲、ここでは「予定価格」の列(G2:G32)を選択し、[F4]キーを押して絶対参照にします。(表示が(G2:G32)から($G$2:$G$32)変わる)

=SUMIFS($G$2:$G$32」の続きに「,」を入力し、条件範囲、ここでは「細別」ごとに集計を出したいので、「細別」の列(E2:E32)を選択し、[F4]キーを押して絶対参照にします。(表示が(E2:E32)から($E$2:$E$32)に変わる)

=SUMIFS($G$2:$G$32,$E$2:$E$32」の続きに「,」を入力し、条件、ここでは「細別」の各項目の集計を出したいので、「細別」の各項目を指定します。下図のように「細別」の各項目を入力したセルをクリック)で閉じます

以上、先ほどと同じ。

SUMIFS_条件範囲1_条件1

 



 

② 条件範囲2・条件2の選択

引き続き「条件範囲2」を追加します。
現在セル「L2」には数式「=SUMIFS($G$2:$G$32,$E$2:$E$32,K2)」が入力されています。K2の後ろに「,」を入力し、「条件範囲2」となる「公告月」の列を選択します。

条件範囲2

「公告月」の列(I2:I32)を選択し、[F4]キーを押して絶対参照にします。(表示が(I2:I32)から($I$2:$I$32)に変わる
条件範囲2」を指定したら「,」を入力し、セル「L1」をクリックし、[F4]キーを押して絶対参照にし「)で閉じます。。

条件2

 

これで、「06月度」「区画線」の「予定価格」が算出されました。

6月度区画線合計

 

6月度集計合計

 

上図赤矢印緑▮をダブルクリックします。

6月度集計合計

 

「6月度」の集計合計が算出されました。

 



 

③ 数式のコピー&ペースト

「6月度」に使用した数式を「7月度」にコピペします。

7月度にコピペ

上図赤矢印根元の緑▮赤矢印の方向へドラッグします。

7月度にコピペ

 

7月度にコピペされました。

が、参照セルの確認、再指定が必要となります。

L1は移動していない+K2がL2に移動している

 

「07月」の「区画線」のセル「M2」では、「区画線」と表示されているセル「K2」に設定されていないといけないのが、セル「L2」に移動してしまっています。「07月」と表示されているセルに設定されていないといけないのが「06月」のセルに残ったままになっています。これらを訂正します。

L1をM1に移動+L2をK2に移動する

 

訂正し、下のセルにコピペします。

7月度集計合計

 

「7月度」の集計合計が算出されました。同様に「12月度」まで繰り返します。

細別・月別集計表

 

上図のとおり、「細別」「公告月」別の集計表が完成しました。

用途によって、使い方の幅が広がる関数です。機会がございましたらお試し下さい。

 

 




コメントを残す