Categories: Excel

SUMIFで指定の文字列を含む・含まないデータの合計を計算する

Excelは操作も簡単で万人向けの表計算ソフトのため、業種を問わずとても多くの会社で使われています。

そして、よほど規模が大きくなければ、本格的なデータベースではなくてもExcelでデータ管理することができます。

一方、表計算という特性上、集計や分析を行う機会も多いため、用途に応じた関数の使い方を習得する必要があります。今回のテーマ、ある条件に合致したデータだけを合計計算する方法も覚えておくと、Excelの活用の幅が広がります。

[2年]を含むデータのみ合計計算

この記事では、SUMIF関数を使って指定の文字列を含む・含まないデータの合計を計算する数式を紹介します。

文字列を扱うにあたってはワイルドカードの知識が必須となります。当記事はワイルドカードについても丁寧に説明していますので、文量が多くなっています。すでに概要をご存じの方は、ワイルドカードの章は読み飛ばしてください。

SUMIF関数で使うワイルドカードについて

ExcelのSUMIF関数は文字通り、条件に合致するなら合計計算をするという動きをします。

工夫次第で様々な集計が可能になるのですが、この記事では、SUMIF関数で指定の文字列を含む・含まないデータの合計計算をする使い方と実例を紹介します。

なお、文字列を扱う場合には、ワイルドカードの知識が必須となりますので、まずはワイルドカードについて説明します。

ワイルドカードとは（*or?)

ワイルドカードとは、Excel関数だけではなくプログラミング言語でも広く使われている概念です。ワイルドカードは記号で表し次の2種類があります。

*（アスタリスク）
?（クエスチョンマーク）

それぞれについて説明します。少し難しく感じるかもしれませんが、当記事のテーマである指定の文字列を含む・含まないデータの合計計算をするうえで必須となる知識です。

ワイルドカードの基本

ワイルドカードは、「ここに何らかの文字が入る」という場面で使います。（部分一致）

基本例

例えば、「カリフォルニア」というアメリカの州名をアルファベットで書いてみてください。

もしスペルが分からず、Ca・・・までしか書けなければ、ワイルドカードで下記のように表現することができます。

Ca*

上記の記述で、「頭のCaは確定でそのあとに何らかのスペルが続く」、という意味になります。

もし、頭文字はCかKか分からないけど、末尾は・・niaということを確信していれば下記のように表現します。

*nia

さらに、頭文字も末尾も分からないけど、間に・・fo・・が入ることを確信していれば下記のように表現します。

*fo*

以上のように、ワイルドカードは記述した箇所に入る文字は特定できないないけど、何らかの文字が入る。ということを指示するときに使うのです。

もし上記の例からワイルドカートを除けば、それぞれ「Ca」「nia」「fo」の完全一致するデータのみを指定したことになります。

*と?の違い

文字数の指定有無

ワイルドカードには、「*（アスタリスク）」と「?（クエスチョンマーク）」の2種類ありますが、どちらも基本的な役割は一緒で、前記の説明通りです。

違いは、「*」は文字数を指定しない場合、「?」は文字数を指定する場合に使います。

前記のCaliforniaのスペルで説明すると、「Ca*」はCaliforniaも含みますが、「Ca?」はCaliforniaを含みません。

「Ca?」という記述は、Caのあとに1文字続く文字列のみが対象になってしまうからです。

「?」の使いどころとしては、「総務部や総務課は対象にしたいけど、総務経理課は対象から除きたい」場合に、「総務?」というように指定すれば、総務部と総務課のみを対象にすることができます。

ワイルドカードの実例

実例

下記のイメージ図は、ある小学校の各学年のクラス毎に忘れ物をした児童の数を一覧化したものです。

この表から、2学年全クラスの人数を抽出したい場合、ワイルドカードをどのように使えばよいでしょうか？考えてみてください。

2学年全体の人数を抽出するには・・

この問題は、いくつかの回答が想定されます。下記のようになっていれば正解ですが、あえて複雑な記述は避け、客観的に分かりやすいように心がけましょう。

正解例：[2年*][2*][2年??][2年?組]

なお、わたしなら[2年*]で記述します。[2*]でも良さげではありますが、一見して何の2なのかが分かり辛いです。「?」に関しては、意図しないデータを混在させないためあえて文字数を指定するときに使うのですが、今例のように、定義化（〇年△組）されたデータであれば、そのリスクはありません。

SUMIF関数（指定の文字列を含む・含まない）

前置きが長くなりました。ワイルドカードを理解したうえで、指定の文字列を含む・含まないデータの合計計算をするためのSUMIF関数について説明します。

SUMIF関数の数式（指定の文字列を含む・含まない）

数式

指定の文字を含む
=SUMIF(範囲,”指定の文字列”,合計範囲)
例：=SUMIF(B2:B11,”2年*”,C2:C11)

指定の文字を含まない
=SUMIF(範囲,”<>指定の文字列”,合計範囲)
例：=SUMIF(B2:B11,”<>2年*”,C2:C11)

上記のようにそれぞれの条件判定するセル範囲、指定の文字列、合計するセル範囲をカンマ区切りで記述することで、指定の文字列を含む・含まないなどの条件に合致するデータのみ合計計算することができます。

指定の文字列の箇所には、前章で説明したワイルドカードを使って定義する場合がほとんどです（完全一致以外）。

なお、指定の文字を含まない条件にする場合は、指定の文字列の前に記号「<>」を付与するだけです。（<>は等しくないという意味）