いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)SUMIF関数でワイルドカードを使って合計を求める

 ExcelのSUMIF関数では条件の指定にワイルドカードが使えます。あいまい検索などとも呼ばれ、「~を含む文字列」とか「~で終わる文字列」といった条件に対応する数値の合計を求めることができます。使い方によっては複雑な集計も簡単にできるので、やり方を紹介します。

~を含むという条件(部分一致)の例

 次の画像内の左側の表に市町村名と人口が記録されています。
 E3セルにSUMIF関数とワイルドカードを使った式を入力し「山」の字を含む市町村の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"*山*",C3:C12)

 「*」という文字がワイルドカードで、「0文字以上の任意の文字」を意味しています。
 これはその位置(この場合は「山」の前後)に文字が何文字あってもいいし、なくてもいいということなので、「山」という1文字でも条件に該当しますし、「山」で始まる文字列や「山」で終わる文字列、中間に「山」を含む文字列、いずれも条件に該当します。
 このデータの場合「山県市」「亀山市」「大阪狭山市」「山添村」が集計の対象となり、合計値は24+50+58+3=135となります。



 次の画像も実行結果は同じですが、条件とする文字(山)をE3セルから参照しています。
 このような場合は式の記し方に注意が必要です。

 F3セル

=SUMIF(B3:B12,"*"&E3&"*",C3:C12)

 第2引数(検索条件)を「"*E3*"」などとしてしまってはダメで、セル参照の「E3」は「" "」の中に入れず、さらに&でつなぐ必要があります。
 これは後で紹介するもう一つのワイルドカード「?」を使う場合も同様です。

~で始まるという条件(前方一致)の例

 次の画像では、「山」の字で始まる市町村の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"山*",C3:C12)

 部分一致の例と異なり、「山」の字の後ろにだけ「*」のワイルドカードが付いています。
 これにより「『山』で始まる文字列」という条件になります。
 該当するのは「山県市」「山添村」の2つで、合計値は24+3=27となります。

~で終わるという条件(後方一致)の例

 次の画像では「村」の字で終わる市町村、つまり村の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"*村",C3:C12)

 「村」の字の前にだけ「*」のワイルドカードが付いています。
 これで「『村』で終わる文字列」という条件になります。
 該当するのは「豊根村」「山添村」の2つで、合計値は1+3=4となります。

文字数を指定する例

 次の画像では、2文字の市(最後の「市」以外に2文字)の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"??市",C3:C12)

 ここでは「?」というワイルドカードを使っています。これは「任意の1文字」を意味します。
 その位置に1つだけ文字がなければならないので、「"??市"」とすることで2文字の市だけが条件に該当します。
 該当するのは「山県市」「亀山市」「舞鶴市」の3つで、合計値は24+50+78=152となります。



 さらに次の画像では「2文字以上の市」の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"*??市",C3:C12)

 「*」と「?」の両方のワイルドカードを使っています。「市」の前に「?」が2つあるので確実に2文字なければならず、さらに「*」も含まれているのでそれ以上に何文字あってもかまいません。結果的に2文字以上の市という条件になります。
 ちなみに「*」の位置はどこでもよく、「"?*?市"」や「"??*市"」としても条件としては同じです。
 なお、否定の演算子「<>」(下記参照)を使えば「~文字未満」という条件を作ることも可能です。ただし条件の意味を誤りやすいので注意が必要です。

「~を含まない」や「~以外」の条件の例

 次の画像では「山」の字を含まない市町村の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"<>*山*",C3:C12)

 最初の例でみたように「"*山*"」とすることで「『山』を含む」という条件になりますが、否定の演算子「<>」を併せて使うことで「『山』を含まない」という条件になります。



 同じく「<>」を応用して、次の画像では「市」の字以外で終わる市町村、つまり町村の人口の合計を求めています。

 E3セル

=SUMIF(B3:B12,"<>*市",C3:C12)

 「"*市"」だけだと「『市』で終わるもの」という意味なので、「<>」を加えることにより「『市』で終わるもの以外」つまりこのデータでは町村が条件に該当することとなります。
 「<>」をうまく使えばSUMIFを何個も足し算するような面倒を避けることができ、とても便利です。


 ただし、条件が複雑になると「<>」を使ったときに意味を間違えやすいので注意が必要です。
 例えば2つ上の例でみたように「"*??市"」は「(最後の「市」以外に)2文字以上の市」を意味します。これに「<>」をつけると「"<>*??市"」となりますが、その意味は「2文字未満の市」ではなく「全体から2文字未満の市を除いたもの」つまり「町村または2文字未満の市」となります。
 条件によっては1つのワイルドカード文字列で表現するのが困難になるので、その場合はSUMIFS関数を使って複数のワイルドカードを検索するのが適当です。