Excelで合計する時、いろんな条件を満たしているものだけを合計したいんだけど…
単純に合計する関数「SUM」や一つの条件を指定して合計する関数「SUMIF」は知ってるけど、複数の条件を指定してそれを満たすものだけを合計する方法はないの！？
より実践的な業務効率化を行おうと思うと、そんな悩みも出てきます。
過去、Excel2003以前のバージョンでは関数として複数条件を指定する方法はありませんでしたが、代わりに配列式というものを使用してそれを叶えたという歴史があります。
しかし、それ以降に関数でその悩みを解決できる関数「SUMIFS」が登場しました。
今回はその関数「SUMIFS」について紹介したいと思います。
関数「SUMIFS」とは？
その悩み、「SUMIFS」関数を使うと解決できますよ！
「SUMIFS」関数ってなんですか？
SUMIFS 関数 (数学/三角関数の 1 つ) は、複数の検索条件に一致するすべての引数を合計します。 たとえば、(1) ある 1 つの郵便番号区域に所在しており、(2) 利益が特定の金額を超える、国内の販売店の数を合計するときなどに、SUMIFS 関数を使います。
マイクロソフトOfficeサポートホームページ
マイクロソフトの公式HPには上記のように説明されています。
この「SUMIFS」関数は「SUMIF」関数の進化版とも言える関数です。
「SUMIF」関数が条件を1つしか登録できないのに対し、SUMIFS関数は複数の条件を登録し、それらをすべて満たすものを合計することができます。
ただし、注意点としては、「それらのどれかを満たす」という条件は指定できないことに注意する必要があります。
AかBのどちらかを満たすものを合計するためには工夫が必要になります。
関数「SUMIFS」の考え方・使い方
関数「SUMIFS」の構文
ここからは関数「SUMIFS」の考え方や使い方について説明します。
関数「SUMIFS」の構文は以下のようになります。
注意する点としては、一つの条件を指定して合計する関数「SUMIF」と合計範囲の設定位置が異なる点です。
SUMIFの場合は引数の一番最後に合計範囲を指定していましたが、SUMIFSになると一番最初に合計範囲を指定します。
ちなみに、関数「SUMIF」の場合は下図のようになります。
色分けも同じにしているので、参考にしてください。
関数「SUMIFS」の条件・範囲の考え方
合計範囲の列数が増える場合、考え方が複雑になるため、合計範囲が1列の場合と2列の場合のそれぞれで考え方を説明したいと思います。
合計範囲1列の場合
想定として、下図のようなデータが入力されているとします。
合計範囲の理屈を理解できるように、あえて横並びにデータがなっていない場合を想定しています。
合計数値をする範囲は「J6:J9」です。
条件を指定する範囲は一つ目と二つ目をそれぞれ範囲①、②に設定します。
条件1に「りんご」を指定した場合、下図のように赤で塗りつぶされた部分が検索で見つかった部分になります。
条件を指定している範囲で表に①～④の番号を振ったとすると、④番が条件に一致していることが分かります。
同じように合計する範囲でも表に①～④の番号を振ると、④番の位置には「4」と入力されています。
次に条件2に「いちご」を指定した場合、下図のように緑で塗りつぶされた部分が検索で見つかった部分になります。
条件1の時と同じように条件を指定する範囲に①～④の番号を振ると、④番が条件に一致していることが分かります。
同じように合計する範囲でも表に①～④の番号を振ると、④番の位置には「4」と入力されています。
このように条件1、条件2それぞれでどの位置の数値が条件に一致しているかが分かった後、条件1、条件2共に満たす番号を探します。
今回はどちらも④番が条件に一致していることがわかるため、合計値としては「4」となります。
合計範囲2列の場合
次に、更に複雑になる合計範囲が2列になった場合について説明します。
こちらも想定として使用する表は同じものを使用します。
合計範囲としては（J6:K9）とします。
今回の検索範囲は下図のように①、②の範囲とします。
条件1に「りんご」を指定した場合、下図のように赤で塗りつぶされた部分が検索で見つかった部分になります。
条件を指定している範囲で表に①～⑧の番号を振ったとすると、②番と⑦番と⑧番が条件に一致していることが分かります。
同じように合計する範囲でも表に①～⑧の番号を振ると、 ②番と⑦番と⑧番 の位置には「5」と「4」と「6」と入力されています。
条件2に「みかん」を指定した場合、下図のように緑で塗りつぶされた部分が検索で見つかった部分になります。
条件を指定している範囲で表に①～⑧の番号を振ったとすると、②番が条件に一致していることが分かります。
同じように合計する範囲でも表に①～⑧の番号を振ると、 ②番と の位置には「5」と入力されています。
このように条件1、条件2それぞれでどの位置の数値が条件に一致しているかが分かった後、条件1、条件2共に満たす番号を探します。
今回は②番がどちらとも条件に一致していることがわかるため、合計値としては「5」となります。
関数の使い方（操作方法）
ここからは関数を利用する場合の操作方法などを説明します。
方法としては「関数の挿入」と「直接入力」の2種類紹介します。
関数の挿入
まず、合計数値を入力したいセルを選択し、上部のfx（関数の挿入）ボタンをクリックする。
別ウィンドウ（関数の挿入）が立ち上がるので、関数の分類を「数学/三角」に変更し、関数名のリストの中から「SUMIF」を探し、選択した状態でOKボタンをクリックする。
ウィンドウが関数の引数へと変わるので、合計対象範囲の上矢印のボタンをクリックする。
ウィンドウが小さくなり、セルを選択できる状態になるため、ドラッグ＆ドロップで合計値を算出する範囲を選択する。
選択が終わったらENTERキーを押す。
ウィンドウが関数の引数へと戻るので、条件範囲1の上矢印のボタンをクリックする。
ウィンドウが小さくなり、セルを選択できる状態になるため、ドラッグ＆ドロップで合計値を算出する範囲を選択する。
選択が終わったらENTERキーを押す。
ENTERキーを押し関数の引数ウィンドウに戻ったら、次は条件1の蘭に合計するための条件を指定する。
条件にはワイルドカードを利用できるため、今回は「1で終わる」を指定するために「*1」と入力しました。
他にも「1*（1で始まる）」や「*1*（1を含む）」も指定できます。
条件1の設定が終わったら、次は条件範囲2の上矢印をクリックします。
先ほどの条件1の設定の時と同じように、ドラッグ＆ドロップで選択する。
選択が終わったらENTERキーを押す。
ENTERキーを押し関数の引数ウィンドウに戻ったら、次は条件2の蘭に合計するための条件を指定する。
今回は10より大きい場合を設定するため、「>10」と入力しました。
他にも「<（より小さい）」や「>=（以上）」「<=（以下）」「<>（等しくない）」も使えます。
条件2を入力し終わったら、OKボタンをクリックし、関数を確定させます。
そうすると条件に当てはまる合計値が表示されます。
検索条件を更に増やしたい場合は、条件2の設定後、設定欄が下にスクロールできるので、そこで追加設定をしてください。
直接入力
まず、合計値を表示したいセルに「=SUMIFS(」と入力します。
この際、SUMIFSの入力は大文字でも小文字でもどちらでも構いません。
(まで入力したら、ドラッグ＆ドロップで合計したい範囲を選択します。
合計したい範囲を選択し終わったら、半角で「,（カンマ）」を入力する。
カンマの入力が終わったら、ひとつめの条件を指定したい範囲をドラッグ＆ドロップで選択する。
条件を指定したい範囲を選択し終わったら、再度半角で「,（カンマ）」を入力し、ひとつめの検索条件を入力する。
検索条件の設定の際、検索条件を「””（ダブルクォーテーション）」で挟むことを忘れないようにしてください。
ひとつめの条件を入力し終わったら、再度半角で「,（カンマ）」を入力する。
カンマの入力が終わったら、ふたつめの条件を指定したい範囲をドラッグ＆ドロップで選択する。
条件を指定したい範囲を選択し終わったら、再度半角で「,（カンマ）」を入力し、ふたつめの検索条件を入力する。
こちらも検索条件の設定の際、検索条件を「””（ダブルクォーテーション）」で挟むことを忘れないようにしてください。
検索条件まで指定し終わったら、「)」を入力してEnterキーを押します。
すると、検索条件を満たす数値の合計値が表示されます。
検索条件を更に複数指定する場合は、検索範囲2の設定～検索条件2の設定までの動作を繰り返し実施してください。
まとめ
複数条件一致の場合のみを合計することができるSUMIF関数、SUM関数からすると複雑になっていますが、実用性も向上していますので、是非覚えて活用してみてください。
コメント