こんにちは。すごい改善のAitoです。

今日はSUMIFS関数においてOR条件で集計する方法をご紹介します。

まずは次のような例を考えてみましょう。

次のようなデータがあるとします。

この中から各担当について2016年においてステータスが1~3に当てはまるものを集計します。これはあくまで例なのでお仕事で使われてるものに適宜場面を置き換えて考えてみてください。

さて、どんな式を組めばよいでしょうか。ここではSUMIFS関数を用いて集計してみることにします。その前に作業列を追加します。必要なのは年ですが、年だけのデータがないので日付からYEAR関数を使って年を抜き出します。

E2に次の式を入力してオートフィルで下まで入力します。

=YEAR(B2)

次にUNIQUE関数などを使って担当者の一覧を作成します。今回であればこんな感じですね。集計する際に必要になる条件も併せて書いておきました。

ここから集計を始めていきます。SUMIFSの引数の確認をします。

Excelは入力するとヒントを表示してくれるので親切ですね！最初に合計対象範囲を入力するので、C列を列選択します。

次に条件範囲1,条件1,条件範囲2,条件2…と続いていくので、それぞれにデータを入力していきます。全部入力すると次のようになります。

式は次の通りです。

=SUMIFS(C:C,A:A,G4,E:E,$H$1,D:D,{1,2,3})

C:Cは売り上げを集計指定し、A:A,G4で担当者を指定、E:E,$H$1で年の指定、D:D,{1,2,3}でステータスの指定をしています。これらの条件で合計してくれるのがSUMIFS関数です。複数条件で集計するときのポイントは複数条件を中括弧{}で囲むことです。ここに複数条件を入れるとスピルされ、それぞれに対応する集計結果が出ます。今回はこの三つ全部の合計を出したいので、この結果をSUM関数で囲んであげます。

式は次の通りです。

=SUM(SUMIFS(C:C,A:A,G4,E:E,$H$1,D:D,{1,2,3}))

すると合計が出て一つにまとまりましたね。今回は0なのでちょっとわかりにくいですが。

これを下までコピーすれば完成です。

簡単ですよね！

でもここで察しのいい方ならすでにお気づきかと思いますが、そう、「集計ステータス変わったらどうすんねん(怒)」

せっかく上に集計ステータスを書いてあるのにここが変わったら全部書き直しです。これではとても非効率で、よくない仕組みです。基本的に値のベタ打ちはやめたほうがいいですね。ではどうするか、ここで{1,2,3}と同じ挙動ができないか考えてみます。

もう一度データに目を通すと……カンマ区切りになってる！ということでTEXTSPLIT関数を使ってみることにしましょう。ここは範囲で指定できるなら範囲指定でも構いません。

SUM関数で囲む前の関数を改良します。

【改良前】

= SUMIFS(C:C,A:A,G4,E:E,$H$1,D:D,{1,2,3})

【改良後】

= SUMIFS(C:C,A:A,G4,E:E,$H$1,D:D,TEXTSPLIT($H$2,","))

少し複雑に見えるかもしれませんが、変わったのは中括弧{}の部分だけです。数値をベタ打ちしていたところがTEXTSPLITでカンマ区切りで自動的に入るようになったと思えば大丈夫です。

結果は期待通りの動作になっていますので、最後に再びSUM関数で囲ってあげます。

=SUM(SUMIFS(C:C,A:A,G4,E:E,$H$1,D:D,TEXTSPLIT($H$2,",")))

後は桁区切りなり何なりと自由にいじることができます。TEXTSPLITを使う手法であれば、条件の数が変わっても一つのセル(ここでは集計ステータスの1,2,3のところ)を変えるだけで式に自動反映されますのでより作業が楽になります。

今回はちょっと例が限定的すぎたかもしれませんが、この記事を見てくださっているあなたのお仕事においてうまく用例変換で当てはめて少しでもお仕事が楽になれば幸いです。

最後まで読んでくださりありがとうございました。また別の記事でお会いしましょう！