エクセル VLOOKUP と SUMIFS におけるワイルドカード
IMA® 支部の最近の Power Excel セミナーで、参加者から、長いセル内に埋め込まれた数値を VLOOKUP で照合する方法はないかと尋ねられました。 彼女は、小切手帳の小切手と銀行からの清算済み取引リストを照合しようとしていました。 難しいのは 4360>
彼女が検索する必要があったフィールドには、接頭辞として可変長の文字が含まれており、その後に小切手番号が続き、さらに多くの文字で終了していました。 ほとんどの人は、VLOOKUPがワイルドカードをサポートすることに気づいていません。 実際、MicrosoftのExcelチームは、人々が意図的に使用するよりも、偶然にVLOOKUPにワイルドカードが入ることの方が多いと考えています。 ほとんどの人は VLOOKUP がワイルドカードをサポートすることを知らないので、VLOOKUP を実行したときに、ワイルドカードを表すことを意図していない文字が検索値に含まれてしまう可能性の方が高いと思われます。 実際にアスタリスクやクエスチョンマークを探さなければならない場合はどうすればよいのでしょうか。 アスタリスクを探すには「~*」、クエスチョンマークを探すには「~? 4360>
この3つの文字を使用することは、数百のVLOOKUPがあり、値にすでにワイルドカード文字が含まれている場合には特に困難です。 ネストされたSUBSTITUTE関数を使用してその場で値を変換しようとすると、扱いにくくなります。 =VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2, “~”,”~~”), “*”,”~*”),”？”,”~？”) を想像してください。 Microsoft が、特定のワークシートまたはワークブックのワイルドカード動作をオフにするオプションを提供すれば、実際にはよりよいものになるでしょう。 ルックアップテーブルの最初の列のテキストの中にある「apple」という単語を見つけるには、「*apple*」を検索しなければならないでしょう。 しかし、図1のセルA2にはすでに「apple」が含まれているので、数式は=VLOOKUP(“*”&A2&”*”で始まっています。 この場合、連結文字（&）を使って、A2の値をアスタリスクで両端から包んでいることになります。 あるいは、=VLOOKUP(CONCATENATE( “*”,A2, “*”)を使って、「りんご」という文字をアスタリスクでくくることもできます。
数式 =VLOOKUP(“*”&A2&”*”,$D$2:$E$6,2,FALSE) はセル E5 から $956 を正しく検出します。
ワイルドカード検索からの結果を確認するときは特に注意しましょう。 apple」と「crabapple」の両方を検索していたとしたらどうでしょうか。 crabapple の検索は正しいでしょうが、VLOOKUP はルックアップテーブルの最初の一致を返すので、*apple* の VLOOKUP は、ルックアップテーブルで apple よりも先に見つかった場合、crabapple の値を返すことになります。
FINDING AND SUMMING ALL MATCHES
Excel セミナーでこの例の作業を行っていると、他の人から、複数の一致がある場合に一致する結果をすべて見つけて合計したい場合について質問されました。 以下に示すSUMIFS関数では、D列の対応する値に*apple*が含まれている場合、E列の値を加算するという式になっています。 この式も同様に機能します。
IMPROVING WILDCARD HANDLING
VLOOKUP がワイルドカードを処理できることに驚きましたか? 4360>
これらの質問のいずれかの答えが「はい」であれば、Microsoft が、デフォルトでワイルドカードを無視する VLOOKUP の新しい改良版を提供することは、おそらくあなたにとって意味のあることでしょう。 この新しい関数の最後に、ワイルドカードを使用するようにExcelに指示するためのオプションの引数を追加することができます。 この引数を省略した場合、Excelはワイルドカードを一切使用しません。 142*154」、「なぜ」、「e*Trade」などを簡単に検索でき、「Electrical Trade」のような値が偽の一致として表示される心配はありません。
FASTER VLOOKUPS ARE COMING
Microsoftはここ数か月、Office 365ビルドのInsider MonthlyチャネルでVLOOKUPの新しい高速バージョンをテストしています。 この関数は、特に同じテーブルに対して何千ものルックアップがある場合、古い VLOOKUP の 10 倍の速度になることが多いです。
新しいロジックは、2019 年末までにすべての Office 365 加入者にロールアウトされるはずです。 現在、VLOOKUP に 1 秒もかかっていない場合は、この変更に気づかないかもしれません。 しかし、計算に40秒かかるような巨大なワークシートがある場合、その再計算時間は約4秒以下にまで低下することが予想されます
。