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

IT

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

Advertisements

Sum関数のおさらい

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

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

Sumif関数の使い方

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

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

比較演算子による計算

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

合計範囲について

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

Sumifs関数の使い方

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

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

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

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

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

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント