Excel(エクセル)で条件に合う値のみを合計する方法/Sumif、Sumifs関数の使い方

IT

Excelを実務で使うと、「この条件の数値だけを足し算したい」という場面が多く出てきます。そうした時のために覚えておきたいのがSumif/Sumifs関数です。

スポンサーリンク

Sum関数のおさらい

Sumif関数の話をする前に、Sum関数の解説をします。Sum関数は「指定した範囲の値を合計する」関数です。

範囲の値の合計
=sum(範囲)
A1~A3の値を合計したいので、=sum(A1:A3)(A1~A3の値合計と記述します。
Enterで式を確定すると、値が合計されました。
Sum関数は範囲を合計するだけのシンプルな関数なので、A列の文字がAの場合のみ、B列の数値を合計したいといった場面では文字と数字を並び変えるなどする必要があります。しかしデータが膨大になるとこれは現実的ではありません。

Sumif関数の使い方

こうした場面で使用するのがSumif関数です。Sum + if(もし~なら)でSumif、となります。

範囲内の条件を満たす値の合計
=sumif(検索範囲,検索条件,計算する範囲)
まず=sumif(と入力すると、SUMIF(範囲,検索条件,[合計範囲])とガイドが出ます。左ふたつが検索条件、最後の合計範囲が計算する範囲です。
ガイドに従い、まずは「文字列が●の場合のみ」にあたる検索範囲を入力します。この場合=sumif(A1:A6となります(マウスで検索範囲を選択してもOKです)。
次に検索条件を入力orマウスで選択します。今回は文字列がAの場合のみ合算したいので、=sumif(A1:A6,”A”となります。文字列は””で囲み、範囲と条件の間は,で区切ります。
最後に条件に合う場合に合計したい範囲を入力orマウスで選択します。今回はB1:B6を計算したいので、=sumif(A1:A6,“A”,B1:B6)(A1~A6Aと一致する場合B1~B6対応する数値を合計という式が完成しました。
Enterで式を確定すると、文字列がAの行の値のみが合計されていることが確認できました。

比較演算子による計算

もし検索条件を「数値が~以上」にしたければ、適当なセルに>=50と入力し、数値自身を検索範囲にして判別するか、直接”>=50″を検索条件にします(比較演算子を使う場合も””で囲ってください)。今回はセルを参照し、=SUMIF(B1:B6,A7,B1:B6)(B1~B6の中でA7(50以上)の値のみを合計)という式になります。
50以上の値のみが合計されました。50より大きい数値のみの場合は>、以下は

合計範囲について

実は、最後の合計範囲に関しては、B1:B6でもB1:B2でもB1でも同じ結果になります。開始位置さえわかれば、検索範囲のふたつめが○→開始位置から見てふたつめを加算、検索範囲のよっつめが○→開始位置から見てよっつめを加算……というように自動で対応します。
また、計算範囲にSumif関数自身が含まれた場合は無視されます。

Sumifs関数の使い方

より実戦的な例を取り上げてみましょう。次の表はあるスポーツクラブの会員表(抜粋)です。

このデータから、年齢分布および性別ごとの来店数をカウントしたいと思います。
Sumif関数を使えば、男性および女性の来店数の合計を求めることは可能です。しかし複数の検索条件を用いるには、Sumifs関数を使う必要がありそうです。
範囲内の(複数の)条件を満たす値の合計
=sumif(計算する範囲,検索範囲1,検索条件1(,検索範囲2,検索条件2…))
=sumifs(と入力すると、Sumif関数同様SUMIFS(合計対象範囲,条件範囲1,条件,…)とガイドが出ます。Sumif関数とちがい、Sumifs関数では計算する範囲を先に書きます。
なので、まずは来店数にあたるセルを入力し、=sumifs(D2:D8とします(範囲をマウスで選択してもOK)。
,で区切り、ひとつめの検索条件である性別があるセルを指定します。入力orマウスで指定して、=sumifs(D2:D8,B2:B8とします。
一致条件である”男性”を入力し、=sumifs(D2:D8,B2:B8,”男性”とします。
次にふたつめの条件である年齢の検索範囲を指定します。入力orマウスで指定して、=sumifs(D2:D8,B2:B8,”男性”,C2:C8とします。
20歳までの来店数をカウントしたいので、21未満を意味する”Enterを押します。
=SUMIFS(D2:D8,B2:B8,“男性”,C2:C8,“<21”)(D2~D8(来店数)の中でB2~B8(性別)男性、かつC2~C8(年齢)21未満場合のみ対応する値を合計という式が完成しました。ちゃんと20歳以下の男性の来店数のみが表示されていることが確認できました。
この式はコピペして使いまわしたいので、行番号を絶対参照にしておきます。
式をF2にコピーして男性を女性に書き変え、20歳以下の女性の来店数もカウントします。
F3になると、さらに条件がひとつ増えます。年齢を2回参照し、それぞれ”=21″(21以上)の条件を課します。
条件を増やしても、クリアする男性の来店数のみがカウントされることが確認できました。
女性側ではちゃんと足し算も実行されていることが確認できました。
同様に41歳~の男性も作成すると、年配の男性が多く来店していることがわかりました。

Sumifs関数で単体条件の検索は可能か

可能です。Sumifs関数はひとつだけの条件を指定し、Sumif関数的に使うことができます。なので特にこだわりがなければ、Sumif関数は覚えずにSumifs関数のみを使っても支障ないです。

Sumif関数、Sumifs関数の使い方については以上です。

Excel:四則演算、足し算、引き算、掛け算、割り算
Excelの四則演算関連まとめです。 基本的な処理だけであれば、+、-、*、/でそれぞれ足し算、引き算、掛け算、割り算となります。たとえば1+1は=1+1、A1セルの値×10は=A1*10、A1~A3の合計を3で割るなら=Sum(A1:A...
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント