SUMIFS関数の「〇〇以外」集計は、実質この4つでほぼ網羅できる（と思う）
えくせるサイトウ
「この値だけ除いて集計したい」
そう思ったことはありませんか。
SUMIFS 関数は、複数の条件に合うデータだけを合計することができます。
でも、たとえば、こんな場面に出会うかもしれません。
- 特定の値をひとつ除きたいとき
- 複数の値をまとめて除きたいとき
- ある文字列を含むものを除きたいとき
- 空白セルを除きたいとき
そんな “以外” の条件をつけたいとき、どう書けばいいか迷ったことはありませんか？、
このページでは、SUMIFS 関数で「◯◯以外」を条件に指定する方法を、
ひとつずつ整理していきます。
もし SUMIFS 関数そのものの使い方にまだ慣れていなければ、
先にこちらの記事を見ておくのがおすすめです。
基本の構文を確認してから読み進めると、より理解しやすくなります。
1. 不等号 <> を使って「以外」を指定する
「この値だけ除いて合計したい」
そんなときにまず思い出してほしいのが、不等号 <> を使う方法です。
「不等」は（等しくないこと）「等しくない」関係を表します。
たとえば、A列 に「A」という値があるとします。
それ以外の行だけを集計したい場合には、SUMIFS 関数をこのように書きます。
<code>=SUMIFS(B2:B5, A2:A5, "<>A")</code>
Code language: HTML, XML (xml)<>A という条件によって、「Aと等しくない」が選ばれます。
つまり「A以外のすべて」が合計の対象になるということです。
SUMIFS COUNTIFS AVERAGEIFS などの他の条件付き集計関数でも同様の考え方が使える
単一条件なら SUMIF、COUNTIF でも対応できます
こちらの記事でも、類似例を紹介しています。
ご自身の課題に合わせて、お好きな方を読んでみてください。
2. 複数の値を除外する場合
ひとつの値ではなく、いくつかの値を同時に除外したいこともありますよね。
その場合は、SUMIFS 関数を使って、同じ条件範囲を複数回指定するのがポイントです。
たとえば、A列 に「△△△」と「◯◯◯」という値があるとします。
この2つを除いた行だけを合計したいとき、式はこうなります。
<code>=SUMIFS(B1:B10, A1:A10, "<>△△△", A1:A10, "<>◯◯◯")</code>Code language: HTML, XML (xml)ここで大事なのは、「AND条件」になるということです。
つまり「△△△と等しくない」「◯◯◯と等しくない」、
両方の条件を同時に満たす行だけが合計されます。
「どちらかを除きたい」というような OR 条件にしたいときには、別の工夫が必要になります。
その場合は、関数を分けて合計するか、フィルター関数などを使って対応する方法もあります。
3. ワイルドカードを使って文字列を除外する
値そのものではなく、「特定の文字を含むものを除きたい」というときもありますよね。
そんな場面では、ワイルドカード * を使った指定が便利です。
たとえば、「報告」という文字が含まれるデータを除外したいとき。
このように書くことで、A列 に「A」を含む行を合計から外すことができます。
<code>=SUMIFS(B2:B20, A2:A20, "<>*A*")</code>Code language: HTML, XML (xml)*A* は「前後に何か文字があってもいい」という意味を持ちます。
そのうえで <> をつけることで、「報告を含まないもの」が条件になります。
文字列を含むかどうかで条件をつけたいときには、
この * と <> の組み合わせを覚えておくと安心です。
データ分析の豆知識を簡潔説明 -ワイルドカードの基本と曖昧検索に、ワイルドカードの詳細説明があります
4. 空白以外を集計したい場合
「空白を除いて集計したい」
そんな場面もよくありますよね。
この場合も、不等号 <> を使って条件を指定することができます。
たとえば、A列 に空白のセルがあり、そこを除いた行だけを合計したいとき。
次のように書きます。
<code>=SUMIFS(B2:B10, A2:A10, "<>")</code>Code language: HTML, XML (xml)<> だけを指定すると、「空でないセルすべて」が条件になります。
つまり、何かしらの値が入力されている行だけが合計されるということですね。
文字列でも数値でも、「空白以外」であればこの方法で集計できます。
とてもよく使う書き方なので、早めに慣れておくと安心です。
まとめ
「◯◯以外」の条件をつけたいとき、
いちばん大切なのは、何を“除きたい”のかをはっきりさせることです。
- 特定の値をひとつ除きたいときは、不等号
<>を使う - 複数の値を除きたいときは、同じ条件範囲を
SUMIFSの中で繰り返して書く - ある文字列を含むものを除きたいときは、ワイルドカード
*と<>を組み合わせる - 空白セルを除きたいときは、条件を
"<>"にする
こうした書き方は、SUMIFS に限らずSUMIF COUNTIF AVERAGEIF などでも同じように使えます。
「以外」をうまく使いこなせるようになると
集計の自由度は大きく広がります。
ぜひ、手を動かしながら試してみてください。
