画像: simpson33、ゲッティイメージズ/iStockphoto
Microsoft Excelでデータを入力する際、オートコンプリート機能が役立ちます。この機能は便利で、データ入力のキー操作を減らし、タイプミスを防ぐことができるため、おそらく頻繁に利用されているでしょう。まさに、瞬時に使える便利な機能です。このオートコンプリート機能を使ってデータを検索できたら素晴らしいと思いませんか？実は、それができるのです。
Excelのコンボボックスでこの動作をVLOOKUP()関数と組み合わせる方法をご紹介します。このコントロールでは、オートコンプリート機能を使って文字による検索が可能になり、リンクされた関数は一致した文字列に対応する値を返します。
Windows 10 64ビットシステムでOffice 365（デスクトップ版）を使用しています。デモ用の.xlsmファイルをダウンロードするか、ご自身のデータで作業することもできます。Excelのブラウザ版とメニュー版では、この手法はサポートされていません。
Excelで基本的な検索を行う方法
文字を1つずつ入力し、最初に一致する値と対応する値を表示したいとします。Excelのオートコンプリート機能はデータ入力で機能します。既存の値列を検索することはできません。この機能は新しい値を入力する場合に使用します。
参照：Windows コンピュータと Mac をランサムウェアから保護するための 17 のヒント（無料 PDF）（TechRepublic）
幸いなことに、ExcelのActiveXコンボボックスコントロールは同様のオートコンプリート動作を提供します。文字を入力すると、コントロールは入力された文字と一致する最初の値と一致する値を表示します。これはソリューションの前半部分の簡単な修正ですが、一致するレコードについてさらに詳しく知りたいという場合、この動作が役立ちます。
たとえば、図 Aに示すような単純なデータ セット内の特定のカテゴリの単価を知りたいとします。カテゴリが文字 B で始まることはわかっていますが、正確なカテゴリ名を思い出せません。(この例は不自然ですが、ご容赦ください。) コンボ ボックスを使用すると、カテゴリを文字 B と一致させることは簡単です。単価を取得するために、コンボ ボックスを VLOOKUP() 関数にリンクします。文字を入力すると、VLOOKUP() 関数は現在一致しているカテゴリの単価を返します。たとえば、「B」と入力すると、コンボ ボックスは最初に「Baked Goods & Mixes」を一致させ、単価 2.50 ドルを返します。次に「E (Be)」と入力すると、コントロールは「Beverages」を返して、単価 39 ドルを返します。
図A
実際のデータを見ると、カテゴリ列で「Beverages（飲料）」が「Baked Goods & Mixes（焼き菓子＆ミックス）」よりも前に出現するため、Bは「Beverages（飲料）」を返すと予想されるかもしれません。この矛盾については今は気にしないでください。すぐに理解できます。簡単に言うと、コントロールのリストはアルファベット順に並べられますが、データはアルファベット順に並べられていません。では、コンボボックスを追加してみましょう。
Excelでコンボボックスを追加する方法
最初のステップは、コンボボックスを埋め込んで、カテゴリ値の一意のリストを入力することです。これにより、オートコンプリート機能を活用できるようになります。コンボボックスを追加する前に、次のようにカテゴリ値の一意のリストを作成しましょう。
- データ ソース C4:C49 を選択します。
- [データ] タブをクリックし、[並べ替えとフィルター] グループの [詳細設定] をクリックします。
- 表示されるダイアログで、「別の場所にコピー」オプションをクリックします。リストの範囲は、開始前に選択したため、正しいはずです。
- コピー先設定として F4 を入力します。
- 「一意のレコードのみ」オプション (図 B ) をチェックし、「OK」をクリックします。
図B
図Cは、元のデータソースの横に、この一意のリストを表示しています。作業中のシートでは、このリストは目立たない場所に配置するべきですが、今回の目的のためには、何が起こっているかを確認するのに便利です。先に進む前に、一意のカテゴリリストで簡単な並べ替えを実行してください。並べ替えの選択肢が拡張されるよう求められても、拡張しないでください。
図C
アルファベット順に並べられた一意のリストが用意できたら、データセットの上にコンボボックスを埋め込んでみましょう。（必要に応じて、データに関する行をいくつか挿入してください。）[開発] タブをクリックし、次の操作を行います。
- [コントロール] グループで、[挿入] ドロップダウンをクリックし、[ActiveX コントロール] セクションから [コンボ ボックス] を選択します。
- コントロールを C2 に埋め込み、必要に応じてサイズを変更します。
- コントロールを選択した状態で、[コントロール] グループの [プロパティ] をクリックします。
- LinkedCell 設定に C1 を入力し、ListFillRange 設定に F5:F20 を入力します（図D）。実際のシートでは、コントロールの下にあるリンクされたセルを非表示にしたいかもしれませんが、ここでは値がリアルタイムで変化する様子を確認したいと思います。
- プロパティ シートを閉じます。
- デザイン モードを終了するには、[コントロール] グループの [デザイン モード] オプションをクリックします。
図D
コントロールを選択して「B」と入力すると、コントロールのオートコンプリート動作により「Baked Goods & Mixes」が返されます。「C」と入力すると、「Candy」が返されます。「Cann」と入力すると、「Canned Fruit & Vegetables」が返されます。図Eに示すように、リンクされたセルC1にも一致した結果が表示されます。オートコンプリート機能は、カテゴリ列の実際のデータではなく、アルファベット順のリストを評価していることに注意してください。
図E
コントロールのオートコンプリート動作を使用してカテゴリを一致させることができますが、一致するカテゴリの単価はまだわかりません。これは次の作業です。
VLOOKUP()関数の使い方
コンボボックスがセルC1にリンクされていることがわかります。ここで、C1を参照するVLOOKUP()関数を追加します。この関数は、コンボボックスの更新値に対応する単価を返します。D2に次の関数を入力します。
=VLOOKUP($C$1,C5:D49,2,FALSE)
先に進む前に、VLOOKUP() について簡単に確認しておきましょう。この関数は以下の構文を使用します。
VLOOKUP(検索値, テーブル配列, 列インデックス, [範囲検索])
ここで、 lookupvalueは一致させる値です。この場合、それは C1 の値です。tablearray は lookupvalue と返したい対応する値を含むソース データです。columnindex は返される値が含まれる列を示します。rangelookup は、最初の最も近い値または完全一致のどちらが必要かを決定します。
すべてをまとめるために、例を評価してみましょう。コンボボックスを選択します。（コンボボックスで値が選択されていない場合、VLOOKUP() 関数は #N/A を返しますが、今は気にしないでください。）「C」と入力すると、列の最初の「C」の値が「Condiments」であっても、コンボボックスは「Candy」と一致します。コントロールのリストはアルファベット順になっていることに注意してください。図 Fに示すように、最初の「Candy Unit Price」の値は 62.50 ドルです。次に「E (Ce)」と入力すると、すべてが更新され、図 Gに示すように最初の「Cereal」の値（19.45 ドル）が表示されます。
図F
図G
この例が気に入っているのは、最初の数文字が同じカテゴリ値が非常に多いからです。
Excelでクイッククリアを使用する方法
現状では、新しい検索を開始するにはまず検索文字列をクリアする必要があるため、面倒です。これは面倒すぎるので、これを自動化する小さなコードを追加しましょう。
まず、ワークブックをマクロ有効ブック（xlsm）として保存します。次に、Alt+F11キーを押してVisual Basic Editor（VBE）を開きます。プロジェクトエクスプローラーを使用して適切なシート（デモワークブックのSheet2（データ））を選択します。リストAのシンプルなダブルクリック手順を入力します。
リストA
プライベートサブ ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
検索コンボをクリアします。
Me.ComboBox1.Value = “”
終了サブ
このウェブページからコードを直接コピーしないでください。VBEは評価できないウェブ文字についてエラーメッセージを表示します。代わりに、自分でコードを入力するか、このウェブページからWordまたはメモ帳にコードをコピーしてからVBEに入力してください。
コードはシンプルです。valueプロパティを空に設定すると、コントロールのテキスト要素がクリアされ、以前選択した値が削除されるため、すぐに再開できます。このダブルクリックのトリックは直感的ではないため、ファイルを他のユーザーと共有する場合は、その旨を相手に伝える必要があります。
Excelのエラーを抑制する方法
現在、コントロールのテキストボックスが空の場合、VLOOKUP()関数はエラーを返します。IFERROR()関数を以下のように使用すれば、このエラーの表示を簡単に抑制できます。
=IFERROR(VLOOKUP($C$1,C5:D49,2,FALSE),””)
コントロールをクリアすると、エラーが表示されるのではなく、VLOOKUP() 関数の結果が消えます。
Excelで簡単検索を使う方法
このシンプルなコントロールと関数を組み合わせることで、データセットから対応する値を返します。文字を追加すると関数が更新されます。完了したら、ダブルクリックして検索文字列をクリアします。
便利な反面、制限もあります。最初に一致した値で停止し、同じ列内の他の一致する値を参照することはできません。
今後数か月かけて、さらに複雑な検索コントロールを作成する予定ですので、どうぞお楽しみに。
Microsoft Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
