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($G$2:$G$32」の続きに「,」を入力し、条件範囲、ここでは「細別」ごとに集計を出したいので、「細別」の列（E2:E32）を選択し、［F4］キーを押して絶対参照にします。（表示が(E2:E32)から($E$2:$E$32)に変わる）
③ 条件の選択
「=SUMIFS($G$2:$G$32,$E$2:$E$32」の続きに「,」を入力し、条件、ここでは「細別」の各項目の集計を出したいので、「細別」の各項目を指定します。下図のように「細別」の各項目を入力したセルをクリックし「)」で閉じます。「細別」の各項目の指定は、セルを指定する代わりに「”区 画 線”」など、細別に記載されている文字列を “ を前後につけて入力しても可能です。
④ 「細別」ごとの集計結果
表内の「区画線」の合計が算出されました。
①～③で入力した数式を下にコピペします。
上図赤矢印の緑▮をダブルクリックします。
下に同じ数式がコピペされ、各細別ごとの合計が表示されました。ちなみに「鋼矢板」は、エクセル画面のスクショの都合上でカットした範囲にあったのですが、細別ごとの合計を算出するまで消えていることに気づいてませんでした。
念のため合計を比較すると、一致していることが分かります。
SUMIFS関数使い方 複数の条件の場合
「細別」ごとに集計を出しましたが、月ごとではどうでしょうか。
細別かつ月ごとの集計を下の表に算出していきます。
まずは月ごとの集計を算出するために、各行の「公告月」をシート上に表示させます。「公告日」はあらかじめ表示されていますので、「MONTH関数」で「公告日」から「月」のみを抽出します。
「公告月」を表示させました。
① 合計対象範囲・条件範囲１・条件１の選択
合計を表示させたいセルに「=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」の続きに「,」を入力し、条件、ここでは「細別」の各項目の集計を出したいので、「細別」の各項目を指定します。下図のように「細別」の各項目を入力したセルをクリックし「)」で閉じます。
以上、先ほどと同じ。
② 条件範囲２・条件２の選択
引き続き「条件範囲２」を追加します。
現在セル「L2」には数式「=SUMIFS($G$2:$G$32,$E$2:$E$32,K2)」が入力されています。K2の後ろに「,」を入力し、「条件範囲2」となる「公告月」の列を選択します。
「公告月」の列（I2:I32）を選択し、［F4］キーを押して絶対参照にします。（表示が(I2:I32)から($I$2:$I$32)に変わる）
「条件範囲2」を指定したら「,」を入力し、セル「L1」をクリックし、［F4］キーを押して絶対参照にし「)」で閉じます。。
これで、「06月度」「区画線」の「予定価格」が算出されました。
上図赤矢印の緑▮をダブルクリックします。
「６月度」の集計合計が算出されました。
③ 数式のコピー＆ペースト
「６月度」に使用した数式を「７月度」にコピペします。
上図赤矢印根元の緑▮を赤矢印の方向へドラッグします。
７月度にコピペされました。
が、参照セルの確認、再指定が必要となります。
「07月」の「区画線」のセル「M2」では、「区画線」と表示されているセル「K2」に設定されていないといけないのが、セル「L2」に移動してしまっています。「07月」と表示されているセルに設定されていないといけないのが「06月」のセルに残ったままになっています。これらを訂正します。
訂正し、下のセルにコピペします。
「７月度」の集計合計が算出されました。同様に「12月度」まで繰り返します。
上図のとおり、「細別」「公告月」別の集計表が完成しました。
用途によって、使い方の幅が広がる関数です。機会がございましたらお試し下さい。