Excel の SUMIFS 関数でもワイルドカードは使えます。商品名やコードの一部が一致するデータだけを拾って合計したいときは、*、?、~ を使うと対応しやすくなります。
この記事では、「含む」「で始まる」「で終わる」「1文字だけ違う」 というよくある条件を、Windows 版 Excel の実例で順番に確認します。画面と同じ状態で試せるサンプルファイルも用意しています。
サンプルファイルをダウンロードする
記事内の画面と同じ内容で試したい場合は、サンプルブックを使うと分かりやすくなります。含む、始まる、終わる、1文字、記号 の各シートを用意しています。
ExcelでSUMIFS関数にワイルドカードを使う基本形
まずは基本形です。商品名を見ながら売上を合計するような場面では、合計したい列 を最初に、条件を調べる列 をそのあとに指定します。
=SUMIFS(C5:C14,B5:B14,"*りんご*")
この式では、C5:C14 が合計範囲、B5:B14 が条件範囲 です。"*りんご*" にしているので、商品名の中に りんご を含む行だけが合計されます。
| 記号 | 意味 | 例 |
|---|---|---|
* | 0文字以上の任意の文字 | *りんご* |
? | 任意の1文字 | A-?01 |
~ | * や ? を文字として扱う | *~** |
*りんご* のように入力できます。SUMIFSで特定の文字を含むデータの合計を求める方法
商品名の中に特定の文字が 含まれていれば合計したい ときは、文字列の前後に * を付けます。
=SUMIFS(C5:C14,B5:B14,"*りんご*")
この例では、青りんご、赤りんご、りんごジュース の 3 件が対象になり、合計は 450 になります。
SUMIFSで特定の文字で始まるデータの合計を求める方法
前方一致 にしたいときは、文字列の後ろだけに * を付けます。
=SUMIFS(C5:C14,B5:B14,"東*")
この式では、商品名が 東 で始まる行だけが対象です。サンプルでは 東京店 が該当するので、合計は 200 になります。
SUMIFSで特定の文字で終わるデータの合計を求める方法
後方一致 にしたいときは、文字列の前だけに * を付けます。
=SUMIFS(C5:C14,B5:B14,"*店")
この例では、末尾が 店 の商品名が対象です。サンプルでは 東京店 と 大阪店 が当てはまるので、合計は 360 になります。
SUMIFSで1文字だけ違うデータをまとめて合計する方法
? は 任意の1文字 を表します。コードの一部だけが違うデータをまとめたいときに便利です。
=SUMIFS(C5:C14,B5:B14,"A-?01")
サンプルでは A-X01 と A-Y01 が当てはまり、合計は 200 になります。2文字以上違うデータまでは拾わないので、条件を絞りたいときに使いやすいです。
? は1文字分だけ置き換わります。SUMIFSでアスタリスクや疑問符そのものを検索したいとき
商品名やコードの中に * や ? が文字として入っている場合は、そのまま書くとワイルドカードとして解釈されます。記号そのものを探したいときは、前に ~ を付けます。
=SUMIFS(C5:C14,B5:B14,"*~**")
=SUMIFS(C5:C14,B5:B14,"*~?*")
=SUMIFS(C5:C14,B5:B14,"*~~*")
サンプルでは、見積*特価 が 70、型番?候補 が 60、波形~試験 が 40 になります。
~ を付けると、記号を文字として検索できます。SUMIFSでワイルドカードがうまく効かないときの確認ポイント
- 合計範囲と条件範囲の大きさがそろっているか
行数や列数がずれると、正しく集計できません。 - 全角と半角が混ざっていないか
見た目が似ていても、別の文字として扱われることがあります。 - 前後に空白が入っていないか
貼り付けたデータでは、見えない空白が条件ずれの原因になることがあります。 - 数値と文字列が混ざっていないか
型番やコードは、文字列としてそろえたほうが条件を合わせやすいです。
特に初級者の方がつまずきやすいのは、合計範囲と条件範囲の指定ミス です。まずは「どの列を条件にして、どの列を合計したいのか」を分けて確認してみてください。
関連記事
- 件数を数えたい場合: ExcelでCOUNTIFS関数にワイルドカードを使う方法
- 日付の期間条件で合計したい場合: ExcelでSUMIFS関数を使って日付が期間内の合計を求める方法
- 別シートのデータを合計したい場合: SUMIFSを別シートにまたがって使う方法
- 前方一致や後方一致の考え方を先に確認したい場合: COUNTIF関数で特定の文字「で始まる/で終わる/を含む」セルを数える
ExcelでSUMIFS関数にワイルドカードを使う方法まとめ
- 含む:
"*文字列*" - で始まる:
"文字列*" - で終わる:
"*文字列" - 1文字だけ違う:
"?"を使う - 記号そのものを探す:
"~"を付ける
SUMIFS 関数で部分一致の合計をしたいときは、まず * の使い方から覚えると進めやすくなります。さらに細かく条件を絞りたいときは ?、記号そのものを探したいときは ~ を使ってみてください。
合計ではなく件数を数えたいとき は SUMIFS ではなく COUNTIFS を使います。「特定の文字を含む」セルをワイルドカードで数える詳細は Excelで文字を含むセルをCOUNTIFSで数える方法 で * と ? の罠 5 つを実機検証しています。
コメント