【Excel】SUMIFS関数の条件にワイルドカードを使う!Excelの部分一致集計テクニックと注意点

Excelで特定の条件に一致するデータの合計を計算したい場合、SUMIFS関数が便利です。しかし、完全に一致するデータだけでなく、一部の文字だけが一致するデータ(部分一致)を集計したい場面も多いでしょう。SUMIFS関数では、ワイルドカード文字を使うことで、この部分一致による集計を柔軟に行えます。この記事では、SUMIFS関数でワイルドカードを使う方法とその具体的なテクニック、さらに注意すべき点について解説します。

SUMIFS関数でワイルドカードを活用すれば、より高度なデータ集計が可能になります。例えば、「〇〇を含む商品名」や「△△で終わる取引先名」といった条件での集計が容易になります。この記事を読むことで、SUMIFS関数での部分一致集計の基本から応用までを理解し、日々の業務でのデータ分析の精度を高めることができるようになります。

SUMIFS関数でワイルドカードが使える仕組み

SUMIFS関数は、複数の条件を指定して、それらの条件をすべて満たすセルの合計値を計算する関数です。通常、条件には特定の文字列や数値が指定されます。しかし、SUMIFS関数では、条件としてアスタリスク(*)や疑問符(?)といったワイルドカード文字を使用できます。これにより、あいまいな条件での集計が可能になります。

ワイルドカード文字は、文字列の検索や置換、そしてSUMIFS関数のような条件付き集計関数で、柔軟な条件指定を実現するために用意されています。これらの文字を条件に含めることで、完全一致ではなく「部分一致」や「特定の文字数一致」といった条件でデータを絞り込めるようになります。

SUMIFS関数で使えるワイルドカードの種類と意味

SUMIFS関数で利用できるワイルドカード文字は主に2種類あります。それぞれの文字が持つ意味を理解することが、正しく部分一致集計を行うための第一歩です。

アスタリスク(*)の使い方

アスタリスク(*)は、任意の文字列(0文字以上)を表します。これは最も汎用性の高いワイルドカードです。例えば、「*りんご*」という条件を指定した場合、「ふじりんご」、「りんごジュース」、「紅玉りんご」など、「りんご」という文字列が含まれるすべてのセルが条件に一致します。条件のどこにアスタリスクを配置するかで、検索範囲が変わります。

疑問符(?)の使い方

疑問符(?)は、任意の1文字を表します。アスタリスクよりも限定的な条件を指定したい場合に利用します。例えば、「や?ま」という条件を指定した場合、「やま」や「やまな」といった文字列は一致しませんが、「やまと」や「やまと」のように、2文字目に任意の1文字が入る文字列に一致します。単語の特定の位置に任意の1文字がある場合に有効です。

チルダ(~)でワイルドカード文字自体を検索する

もし、条件としてアスタリスク(*)や疑問符(?)そのものを使いたい場合は、これらの文字の前にチルダ(~)を付けます。例えば、「~*」と指定すると、アスタリスク文字そのものを含むセルが対象となります。これは、ワイルドカードとしてではなく、文字として扱いたい場合に必要です。同様に、「~?」とすれば疑問符文字そのものを含むセルを検索できます。

SUMIFS関数でワイルドカードを使った部分一致集計の具体例

ここでは、SUMIFS関数でワイルドカードを活用した具体的な集計例をいくつか紹介します。実際のデータを使って、どのように条件を設定すれば良いかを理解しましょう。

例1:「〇〇を含む」データ集計

商品リストがあり、「りんご」という言葉を含む商品の売上合計を計算したい場合を考えます。商品名がA列、売上がB列にあると仮定します。この場合、条件として「*りんご*」を使用します。

1. SUMIFS関数を入力する
   合計を計算したいセルに「=SUMIFS(」と入力します。
2. 合計対象範囲を指定する
   売上データがあるB列(例: B2:B100)を指定します。
3. 条件範囲を指定する
   商品名が入力されているA列(例: A2:A100)を指定します。
4. 条件を指定する
   「*りんご*」と入力します。これは、A列のセルに「りんご」という文字列が含まれていれば一致するという意味になります。
5. 関数を閉じる
   「)」で関数を閉じます。完成した数式は「=SUMIFS(B2:B100, A2:A100, “*りんご*”)」のようになります。

例2:「〇〇で始まる」データ集計

取引先リストがあり、「株式会社」で始まる取引先の売掛金合計を計算したい場合です。取引先名がC列、売掛金がD列にあるとします。この場合、条件として「株式会社*」を使用します。

1. SUMIFS関数を入力する
   合計を計算したいセルに「=SUMIFS(」と入力します。
2. 合計対象範囲を指定する
   売掛金データがあるD列(例: D2:D100)を指定します。
3. 条件範囲を指定する
   取引先名が入力されているC列(例: C2:C100)を指定します。
4. 条件を指定する
   「株式会社*」と入力します。これは、C列のセルが「株式会社」で始まっていれば一致するという意味になります。
5. 関数を閉じる
   「)」で関数を閉じます。完成した数式は「=SUMIFS(D2:D100, C2:C100, “株式会社*”)」のようになります。

例3:「〇〇で終わる」データ集計

製品コードリストがあり、「-A」で終わる製品の在庫合計を計算したい場合です。製品コードがE列、在庫数がF列にあるとします。この場合、条件として「*-A」を使用します。

1. SUMIFS関数を入力する
   合計を計算したいセルに「=SUMIFS(」と入力します。
2. 合計対象範囲を指定する
   在庫数データがあるF列(例: F2:F100)を指定します。
3. 条件範囲を指定する
   製品コードが入力されているE列(例: E2:E100)を指定します。
4. 条件を指定する
   「*-A」と入力します。これは、E列のセルが「-A」で終わっていれば一致するという意味になります。
5. 関数を閉じる
   「)」で関数を閉じます。完成した数式は「=SUMIFS(F2:F100, E2:E100, “*-A”)」のようになります。

例4:「特定の文字数」のデータ集計

郵便番号リストがあり、最初の3桁が「100」で、かつ全体の桁数が5桁の郵便番号の件数を数えたい場合です。郵便番号がG列にあるとします。この場合、条件として「100??」を使用します。件数を数える場合はSUMIFS関数で合計対象範囲に1を指定します。

1. SUMIFS関数を入力する
   件数を計算したいセルに「=SUMIFS(」と入力します。
2. 合計対象範囲を指定する
   ここでは件数を数えるため、1(または任意の値)を指定するか、G列と同じ範囲(例: G2:G100)を指定します。
3. 条件範囲1を指定する
   郵便番号が入力されているG列(例: G2:G100)を指定します。
4. 条件1を指定する
   「100??」と入力します。これは、G列のセルが「100」で始まり、その後に任意の2文字が続く場合に一致するという意味です。
5. 関数を閉じる
   「)」で関数を閉じます。完成した数式は「=SUMIFS(G2:G100, G2:G100, “100??”)」のようになります。

SUMIFS関数でワイルドカードを使う際の注意点とトラブルシューティング

SUMIFS関数でワイルドカードを使う際には、いくつか注意すべき点があります。これらの点に留意することで、意図した通りの集計結果を得られる可能性が高まります。

注意点1:条件にワイルドカード文字そのものを含めたい場合

もし、検索したい文字列にアスタリスク(*)や疑問符(?)そのものが含まれている場合、Excelはそれらをワイルドカードとして解釈してしまいます。例えば、「商品名に*」という商品を集計したい場合、条件を「*」とだけ指定すると、すべての文字列に一致してしまいます。このような場合は、チルダ(~)を前に付けて、文字そのものであることを明示する必要があります。例えば、「~*」のように指定します。

注意点2:半角・全角の区別

Excelの文字列比較は、基本的に半角と全角を区別します。ワイルドカードを使用した条件指定も例外ではありません。「*りんご*」と指定しても、「*リンゴ*」とは一致しません。集計したいデータに半角と全角が混在している可能性がある場合は、事前にデータを統一するか、複数の条件をOR条件で指定するなどの工夫が必要です。SUMIFS関数はAND条件のみのため、OR条件は別途工夫が必要です。

注意点3:空白セルとワイルドカード

ワイルドカード「*」は0文字以上の文字列を表すため、空白セルにも一致します。もし、空白セルを除外したい場合は、別途条件を追加する必要があります。例えば、商品名がA列にある場合、「*りんご*」だけでなく、「A2:A100<>“”」のような条件も追加して、空白ではないセルのみを対象とします。

注意点4:ワイルドカードが機能しない場合の確認事項

ワイルドカードを設定したのに期待通りの結果にならない場合、まず数式が正しく入力されているかを確認しましょう。条件全体がダブルクォーテーション(“”)で囲まれているか、ワイルドカード文字の前にチルダ(~)が必要ないかなどをチェックします。また、条件範囲のデータ形式が文字列になっているかどうかも確認してください。数値として扱われていると、ワイルドカードが正しく機能しないことがあります。

注意点5:SUMIFS関数とワイルドカードの組み合わせ制限

SUMIFS関数は複数の条件を指定できますが、すべての条件がAND条件として扱われます。OR条件(いずれかの条件に一致すれば良い)でワイルドカードを使いたい場合は、SUMIFS関数を複数使用し、それらをSUM関数で合計するなどの工夫が必要になります。例えば、「りんご」または「みかん」を含む商品の合計を計算したい場合は、2つのSUMIFS関数をSUM関数で足し合わせます。

SUMIFS関数と他の部分一致関数との比較

Excelには、部分一致を扱うための関数が他にも存在します。SUMIFS関数でワイルドカードを使う方法と、これらの関数との違いを理解しておくと、より適切な関数を選択できます。

機能	SUMIFS関数(ワイルドカード使用)	SEARCH関数/FIND関数	COUNTIF関数(ワイルドカード使用)
目的	条件に一致する値の合計	文字列内の位置を検索	条件に一致するセルの個数
部分一致	可能(*、?)	可能(*、?)	可能(*、?)
複数条件	可能(AND条件のみ)	不可(単独では)	可能(AND条件のみ)
集計対象	数値	位置(数値)	個数(数値)
使い分け	条件付きで合計したい場合	文字列の存在確認や位置特定	条件付きで個数を数えたい場合

SUMIFS関数は、あくまで「条件に一致する値の合計」を目的としています。文字列の検索そのものが目的ならSEARCH関数やFIND関数、条件に一致するセルの「個数」を知りたいならCOUNTIF関数(またはSUMIFS関数で合計対象範囲に1を指定)を使用します。SUMIFS関数でワイルドカードを使うのは、これらの関数と組み合わせて、より複雑な集計を行う際の一要素として捉えると良いでしょう。

まとめ

SUMIFS関数にワイルドカード(*、?)を組み合わせることで、Excelでの部分一致集計が非常に柔軟に行えるようになります。これにより、「〇〇を含む」「〇〇で始まる」「〇〇で終わる」といった多様な条件でデータを集計し、より深い分析が可能になります。ワイルドカード文字そのものを条件に含めたい場合はチルダ(~)を使うこと、半角・全角の区別や空白セルの扱いに注意することが、正確な集計結果を得るための鍵となります。これらのテクニックを習得し、SUMIFS関数でのデータ集計能力を向上させましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】