Excel(エクセル)関数でのワイルドカード「」「」の使い方:部分一致で指定する方法

前回は「ワイルドカードとは?」「表記の法則」とワイルドカードの基本的を解説してきました。

今日は実践編です。

Excel(エクセル)で関数とワイルドカード「*」「?」を組み合わせた使い方を書いていきます。

3つの関数でワイルドカードを使って

  1. SUMIF関数
  2. COUNTIF関数
  3. VLOOKUP関数

Excel(エクセル)関数でのワイルドカード「*」の使い方

 「*(半角アスタリスク)」は

なんでもいいから【任意の0文字以上の文字列】と置き換えられる記号
(字数に限らずすべての文字・スペースにも一致)


例)「ねこ〇〇」「ねこ」に一致させたいなら
「ねこ*」で検索すればOK


例)「✕✕1」「1」に一致させたいなら
「*1」で検索すればOK

SUMIF関数(部分一致したデータの合計値を求める)

まずはワイルドカード「*」×「SUMIF関数」の例です。 

例:クラス1組の合計得点を算出したい(A列の学生番号で先頭文字が「1-」となっているのが1組)
SUMIF関数で部分一致検索する例

クラスを判別するためキーとなるのが学生番号の先頭なのでSUMIFの【検索条件】に「1-」の部分一致を指定すればOK

ワイルドカードを使ってSUM関数を入力していく
SUM関数の公式:=SUM ( 範囲 , 検索条件 , 合計範囲)

範囲:$A$4:$C$13
関数コピーしたとき指定範囲をずらしたくないので絶対参照(※絶対参照とは)する



検索条件:”1-*”
「1-○○○」に一致させたいので「1-*」とする、また文字列扱いになるので両端を「”」で囲う



合計範囲:C$4:$C$13
関数コピーしたとき指定範囲をずらしたくないので絶対参照(※絶対参照とは)する


①を公式にあてはめると
=SUMIF($A$4:$C$13,"1-*",$C$4:$C$13)
アスタリスクを使って検索値を指定

これで1組の合計得点が算出されました
1組の合計得点が算出

以上です。意外と簡単ですよね!



ここから更に2組、3組の合計得点も出す場合は・・・
検索条件だけ「"2-*"」「"3-*"」と変更すればいいので、オートフィルでセルF5の関数をコピーして検索条件をそれぞれ変更する
SUMIF関数で部分一致検索する(数式コピー&検索値を変更する)

これで全クラスの合計得点が算出さる

あら、ちょっと待ってください。
2組の得点が異様に少ないと思ったら・・・よく見てみるとA列の学生番号がひとつ「22-023」と入力間違いしているようです

「2-*」の指定では「2-」か「2-○○」にしか一致しないので「〇2-〇〇」のパターンには一致しません。これが原因で合算対象から除外されてしまっていたんですね

学生番号を直したら今度こそ正しく表示されるようになりました!っていう補足の話でした
SUMIF関数で部分一致検索する(学生番号のミスを修正する)

このように部分一致で指定する際は、どのパターンに一致してどのパターンを除外するのか予め考慮して考える必要があります。意図しない一致を拾って本来出したいデータに誤りが生じないように気をつけましょう。

あなたにおすすめのExcel本(Amazonレビュー:

COUNTIF関数(部分一致したデータの個数を数える)

次は、ワイルドカード「*」×COUNTIF関数での例です。

例:クラス1組の合計学生人数をカウントしたい(A列の学生番号で先頭文字が「1-」となっているのが1組)
COUNTIF関数で部分一致検索する例

クラスを判別するためのキーとなるのが学生番号の先頭文字なのでCOUNTIFの「検索条件」に「1-」の部分一致を指定すればOK


ワイルドカードを使ってCOUNTIF関数を入力していく

COUNTIF関数の公式:=COUNTIF ( 範囲 , 検索条件 )

範囲:$A$4:$A$13
下段へ関数コピーしたときに指定範囲をずらしたくないため絶対参照にする(※絶対参照とは



検索条件:”1-*”
「1-○○○」に一致させたいので「1-*」とする、また文字列扱いになるので両端を「”」で囲う

①を公式にあてはめると=COUNTIF($A$4:$A$13,"1-*")
アスタリスクを使って検索条件を指定

1組の学生人数が算出される
COUNTIF関数で部分一致検索する(F10に学生人数が表示される)

以上です。これも意外と簡単!



ここからさらに2組、3組の学生人数も算出したい場合は・・・
つづけて2組、3組の人数を算出するには検索条件のみ変更すればよいので、オートフィル機能でセルF5の関数をコピーしそれぞれ検索条件を「”2-*”」「”3-*”」と変更する

COUNTIF関数で部分一致検索する(数式をコピー&検索条件を変更する)

これで全部のクラスの学生人数が表示されました
COUNTIF関数で部分一致検索する(すべての学生人数が表示された)

COUNTIFでのワイルドカードの使い方がわかりましたね。お気づきかもしれませんが、SUMIF関数と比べてもワイルドカードの使い方はそんなに変わりません。

VLOOKUP関数(部分一致したデータと同じ行にあるデータを取り出す)

最後はワイルドカード「*」×VLOOKUP関数です。

例:指定した漫画キャラの該当する漫画タイトルを一覧から取り出したい(A列に漫画キャラが1つのセルに羅列されている)
VLOOKUP関数で部分一致検索する例

セルE4に漫画キャラ名が入力するのでVLOOKUPの【検索値】にセルE4の部分一致を指定すればOK


VLOOKUP関数に入力する各引数を確認する

SUM関数の公式:=SUM ( 範囲 , 検索条件 , 合計範囲)

検索値"*"&E4&"*"
「*」は文字列扱いなのでセル番号に付ける場合は「"*"&E4&"*"」というように「&」と「”」を使ってつなぎ合わせる



範囲:$A$3:$C$9
関数をコピーしたときに範囲をずらしたくないので絶対参照にする



検索範囲:2
範囲のうち取り出したいデータは2列目



検索方法:0
完全一致で検索するので「0」もしくは「FALSE」と記述

①を公式にあてはめると=VLOOKUP("*"&E4&"*",$A$3:$C$9,2,0)
アスタリスクと&を使って検索値を指定


該当する漫画のタイトルが表示されました、これで完了

VLOOKUP関数で部分一致検索する(検索値が含まれる漫画のタイトルが表示された)

A列では「江戸川コナン 工藤新一・・・」羅列入力されていますがしっかり「コナン」という文字を部分一致として処理することができていますよね。

ちょっとしたポイント

検索範囲は「$A$3:$B$9」でもOK!
ただ、このあと連載雑誌も取り出して表を完成させる場合はC列も検索範囲に入れておくと【関数コピー + 列番号だけ変更するだけ】でいいのであとあと楽ですよ

引き続き、隣セルの連載雑誌も算出する場合は・・・

セルF4の関数をオートフィルで横へコピーして、関数内の列番号だけ「3」に変更する

④を反映させると
=VLOOKUP("*"&E4&"*",$A$3:$C$9,3,0)
隣セルに関数をコピーして検索範囲のみ変更

連載雑誌も表示されました
漫画の連載雑誌が表示された

(この後、オートフィルで関数を下段までコピーすれば表が完成する)
下段まで関数をオートフィル機能でコピーする

これでVLOOKUP関数でのワイルドカードの使い方ばっちりですね。

WordPress初心者さんにおすすめの本(Amazonレビュー:

Excel(エクセル)関数でのワイルドカード「?」の使い方

 「?(半角クエスチョンマーク)」は

なんでもいいから【任意の1文字】に置き換えられる記号

1文字なら「?」2文字なら「??」と置き換えたい文字数分?を重ねて指定することで何文字でも置き換えができる(スペースにも一致・ただし1文字以外には一致しない)


例)「ねこ〇」に一致させたいなら
「ねこ?」で検索すればOK


例)「✕✕✕1」に一致させたいなら
「???1」で検索すればOK

アスタリスクとは違い、0文字とは一致しないため「ねこ?」➛「ねこ」には一致しない点に注意しましょう!

SUMIF関数(「?」を使って部分一致指定する)

まずは、ワイルドカード「?」×SUMIF関数です。

例:学生番号を部分一致させてクラス別に得点を合算したい
SUMIF関数で?で部分一致検索する例

検索条件にアスタリスク「*」を使った場合は以下の式になりますが

=SUMIF($A$4:$C$13,“1-*”,$C$4:$C$13)

    

これを「?」を使って記述すると=SUMIF($A$4:$C$13,“1-???”,$C$4:$C$13) 
検索条件に?を使って指定する

これは「1-○○○」と任意の3文字を指定するものなので、A列データの末尾がすべて3ケタだから可能な指定方法です

例えば「1-0001」のように末尾が4ケタのデータが含まれる場合には「“1-???”」という指定の仕方はできないので注意しましょう!

COUNTIF関数(「?」を使って部分一致指定してみる)

ワイルドカード「?」× COUNTIF関数の例です。

例:学生番号を部分一致させて各クラスに学生人数をカウントしたい
COUNTIF関数でクエスチョンマークを使って部分一致検索する(例のイメージ)

検索条件をアスタリスク「*」で記述するとこのような式でした

=COUNTIF($A$4:$A$13,"1-*")

    

これを「?」を使って記述すると
=COUNTIF($A$4:$A$13,"1-???") 
COUNTIF関数でクエスチョンマークを使って部分一致検索する(クエスチョンマークを使って検索条件を指定する)

 この場合も「-」以下がすべて3文字だから使える方法なので注意しましょう

「-」以降につづく文字数がバラバラな場合は、無理して「?」を使わずに 「*(0文字以上、何文字でもOK)」を使って指定しましょう!

この記事を見ている人におすすめ


以上、Excel関数でのワイルドカードの使い方でした。

文中でも書きましたが、部分一致は複雑になる例も多いので意図しない一致を拾って出したいデータに誤りが生じないように気をつけましょう~!参考になると幸いです