SUMIF/SUMIFS関数でワイルドカードを使い部分一致の条件を設定する方法！完全一致との違いとエスケープも！

ExcelのSUMIF関数で「〜を含む」といった部分一致の条件で合計したいときはないでしょうか。

けど、そんな中で悩むことは、

・ワイルドカードの使い方がわからない
・「ノートPC」と指定すると「ノートPC用ケース」が集計から漏れてしまう
・前方一致と部分一致の違いがわからない

ですよね。

今回はそんなお悩みを解決する

・SUMIF/SUMIFS関数でワイルドカードを使い部分一致の条件を設定する方法

についてまとめます！

完成イメージ

商品名を「ノート」と入れるだけで、「ノート」を含む商品の合計が出せるようにします。
今回は
・SUMIF関数にワイルドカード（*）を使い、部分一致で条件を設定する方法（=SUMIF(A2:A16,”*ノート*”,E2:E16)）
をゴールにします。
完全一致では漏れてしまう商品も、ワイルドカードを使えばまとめて集計できます。

なお、前方一致・後方一致の使い分けや、「*」そのものを検索するときのエスケープ（~）、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

上の画像が完成形です。商品名に「ノート」を含む商品だけが合計され、条件の書き方を変えれば合計する範囲も自由に切り替わります。

STEP1. 元データを用意する

まずは集計したい商品売上の表を用意します。
1行目が見出し、2行目以降がデータです。
金額列（E列）は手入力ではなく =C2*D2 の数式にしておきましょう。
数式にしておくと、あとから数量や単価を変更しても金額が自動で変わります。

STEP2. 完全一致の限界を知る

まずはワイルドカードを使わず、商品名を完全一致で指定してみます。
合計を表示したいセルに、次の数式を入力します。

=SUMIF(A2:A16, "ノートPC", E2:E16)

この数式は「商品名が完全に『ノートPC』の行」だけを合計します。
そのため「ノートPC用ケース」や「ノートPC用バッテリー」は集計から漏れてしまうのです。
合計は 384,000 になりました。

STEP3. ワイルドカードで部分一致にする

次に、検索条件にワイルドカードを付けて部分一致にします。
「*」は「0文字以上の任意の文字」を表します。

=SUMIF(A2:A16, "*ノート*", E2:E16)

前後に「*」を付けることで、「ノート」を含む商品がすべて対象になります。
「ノートPC用ケース」「ノートPC用バッテリー」「軽量ノートPC」まで合計され、結果は 688,800 になりました。
カンタンですね。

STEP4. 前方一致・後方一致を使い分ける

「*」の位置を変えると、一致のしかたを変えられます。
先頭だけに付ければ前方一致、末尾だけに付ければ後方一致です。

=SUMIF(A2:A16, "ノート*", E2:E16)   ← 前方一致
=SUMIF(A2:A16, "*ケース", E2:E16)   ← 後方一致

「ノート*」は「ノート」から始まる商品、「*ケース」は「ケース」で終わる商品だけを合計します。
前方一致は 492,800、後方一致は 45,600 になりました。
目的に合わせて使い分けましょう。

STEP5. 完成を確認する

4つの条件を並べて、結果を見比べてみましょう。
「ノート」を含む商品の合計は 688,800 になりました。
条件の書き方を変えるだけで、合計する範囲を自由に切り替えられます。

一致のしかたとワイルドカードの比較

ワイルドカードの位置で、一致する範囲が変わります。
今回のサンプルデータでの結果もあわせて確認してみましょう。

指定方法	書き方の例	一致する商品	合計
完全一致	“ノートPC”	ノートPC のみ	384,000
前方一致	“ノート*”	ノートで始まる商品	492,800
後方一致	“*ケース”	ケースで終わる商品	45,600
部分一致	“ノート”	ノートを含む商品	688,800
1文字一致	“ノ?トPC”	?＝任意の1文字	384,000

POINT

ワイルドカードは半角で入力します。
* は0文字以上、? は1文字を表します。
部分一致は “*キーワード*” と覚えましょう。

「*」や「?」そのものを検索したいとき（エスケープ）

商品名に「*」や「?」が含まれていて、それをそのまま検索したい場合があります。
このときは、記号の前に「~（チルダ）」を付けます。

検索したい文字	書き方
アスタリスク（*）そのもの	*“~”**
疑問符（?）そのもの	“~?”
チルダ（~）そのもの	“~~”

~ は「次の1文字はワイルドカードではなく普通の文字」とExcelに伝える記号です。
たとえば =SUMIF(A2:A16, “~*”, E2:E16) と書くと、「*」を含む商品だけを合計できます。

VBAで部分一致の合計をまとめて設定する

同じパターンの集計を何度も作るときは、VBAで数式を一括入力するのもおすすめです。
ボタン1つで複数条件の合計を並べられます。

Sub SumIfWildcard()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Range("I20").Formula = "=SUMIF(A2:A16,""ノート*"",E2:E16)"
    ws.Range("I21").Formula = "=SUMIF(A2:A16,""*ケース"",E2:E16)"
    ws.Range("I22").Formula = "=SUMIF(A2:A16,""*ノート*"",E2:E16)"
End Sub

タカヒロ

ワイルドカードは半角で入れるのがポイントです。
全角の「＊」だとただの文字として扱われてしまうので、入力モードに気をつけてくださいね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように集計できないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

症状	原因	対処
ワイルドカードが効かない	Excelのオプションで「ワイルドカードを使用する」がオフになっている	ファイル→オプション→詳細設定→「Lotus 1-2-3との互換性」を確認する
条件に反応しない	全角の「＊」を使っている	ワイルドカードは半角の「*」を使う
数値で部分一致できない	ワイルドカードは文字列にのみ有効	数値は範囲指定や >= などの比較演算子で条件を書く
商品名が一致しない	セルの前後に余分な空白がある	空白を削除する／=TRIM() で整えてから集計する
英字の大文字小文字で悩む	SUMIFは大文字小文字を区別しない	区別したい場合は =SUMPRODUCT(EXACT()) などを組み合わせる

関数の説明

今回使う2つの関数の概要です。
どちらも条件付き合計の基本になる関数なので、違いを押さえておきましょう。

SUMIF関数（サムイフ）

書式	=SUMIF(範囲, 検索条件, [合計範囲])
できること	1つの条件に一致するセルの合計を求める関数です。
戻り値	条件に一致した合計値（数値）
こんなときに	「ノート」を含む商品のように、1つの条件で絞り込んで合計したいとき。
注意	条件の「*」「?」はワイルドカードとして働きます。合計するので合計範囲は数値列にします。

=SUMIF(A2:A16, "*ノート*", E2:E16)

SUMIFS関数（サムイフズ）

書式	=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], …)
できること	複数の条件をすべて満たすセルの合計を求める関数です。
戻り値	すべての条件に一致した合計値（数値）
こんなときに	「カテゴリがアクセサリ」かつ「商品名にノートを含む」など、2つ以上の条件で絞りたいとき。
注意	引数の順番がSUMIFと逆で、合計範囲が先頭です。ワイルドカードはSUMIFSでも同じように使えます。

=SUMIFS(E2:E16, A2:A16, "*ノート*", B2:B16, "アクセサリ")

完成

「ノート」を含む商品の合計は 688,800 になりました。
完全一致では 384,000 だったので、ケースやバッテリー、軽量ノートPCまで含まれています。
思ったとおりに動きましたか？
ワイルドカードを使えば、商品名の一部だけで柔軟に集計できるので、集計作業がぐっとラクになります。