Excelのリスト機能を活用する5つの方法の記事では、Excelに組み込まれている5つの基本的なリスト機能を紹介しました。これらの機能を活用するために特別な操作は必要ありませんが、場合によってはより高度な機能が必要になることもあります。この記事では、検証リストとルックアップ関数を使用して動的なリストを生成する、2つの高度なリスト機能を紹介します。どちらの手法が優れているというわけではありません。ニーズに合わせて選択してください。
Excel 2016（デスクトップ版）を使用していますが、どちらの手法も以前のバージョンでも動作します。参考までに、デモ用の.xlsxファイルをダウンロードできます。VLOOKUP()関数は以前のメニューバージョンでも使用できますが、どちらの手法も動的に動作するためにTableオブジェクトに依存しており、メニューバージョンではTableオブジェクトがサポートされていません。ブラウザ版では検証リストとTableオブジェクトがサポートされていますが、Tableに新しいデータを挿入するのは面倒です。このバージョンでは、新しいレコードを入力する際に、新しいレコードへのタブ折り返し動作が無視されます。
参照: Microsoft Office で絶対にやってはいけない 30 のこと (TechRepublic の無料 PDF)
動的検証リスト
検証リストを使用すると、ユーザーが入力できる値を特定の値に制限できます。これらのリストは、データ入力を容易にし、エラーを防ぐために役立ちます。ユーザーが間違った値を選択するのを完全に防ぐことはできませんが、少なくとも正しいスペルであることは保証されます。馬鹿げているように聞こえるかもしれませんが、フィルタリングを行う場合や複雑な関数を使用してデータを分析する場合など、スペルミスのある値は誤った結果を返します。
最も単純な形式では、検証リストは動的ではありませんが、この機能をTableオブジェクトと組み合わせることで状況は変わります。データセットとリストソースの両方がTableオブジェクトである場合、Excelは作業中にすべてを更新します。
図Aは、 2つのTableオブジェクトを含むシンプルなシートを示しています。列BからEにはデータセットがあり、一意の棚コード値のリストがあります。列Eに検証コントロールを追加し、各レコードに適切な棚コードを入力できるようにします。
図A
これら 2 つの Table オブジェクトを使用して、動的な検証リストを構築します。
これら2つのデータセットは、デモファイル内のTableオブジェクトです。独自のデータを扱う場合は、次のようにデータ範囲をTableに変換できます。
- データ セット内の任意の場所をクリックします。
- [挿入]タブをクリックします。
- [テーブル] グループで、[テーブル] をクリックします。
- 表示されるダイアログで、[テーブルにヘッダーを含める] オプションをオンまたはオフにして (図 B )、[OK] をクリックします。
図B
テーブルにヘッダーがあるかどうかを指定します。
検証リストを作成する前に、もう1つ手順があります。検証リストのソースとしてTableオブジェクトを指定することはできませんが、次のようにTableに範囲名を付ければ指定できます。
- H2:H7 (リスト ソース) を選択します。
- [数式]タブをクリックします。
- [定義された名前] グループで、[選択範囲から作成] オプションをクリックします。
- 表示されるダイアログで、必要に応じて [一番上の行] をチェックすると、Excel はヘッダー テキストを使用して範囲に Shelf_Code_List という名前を付けます (図 C )。
図C
リストに範囲名を付けます。
これで、次のように検証リストを作成する準備が整いました。
- E2:E15 を選択します。これらは、検証コントロールを使用して入力する既存のセルです。（テーブルにまだデータが含まれていない場合は、1 つのセルを選択します。）
- [データ]タブをクリックします。
- [データ ツール] グループで、[データの入力規則] ドロップダウンから [データの入力規則] を選択します。
- [許可] ドロップダウンから [リスト] を選択します。
- ソースコントロールに「=Shelf_Code_List 」と入力します（図Dを参照）。テーブルオブジェクトではなく、名前付き範囲を参照します（同じ範囲ですが）。
図D
リスト ソースに指定した名前付き範囲を参照します。
検証リストが完成したら、E列のセルに適切な棚コードの値を入力し始めることができます。また、図Eに示すように、ドロップダウンから適切なコードを選択して手動で入力することもできます。値を入力し終わったら、オートコンプリート機能を使って1～2文字入力するだけで、自動的に値が補完されます。
図E
ドロップダウンリストから値を選択します。
なぜテーブルオブジェクトが必要なのか疑問に思うかもしれません。テーブルに新しいレコードを追加すると、Excelは自動的に検証コントロールを拡張します（図F）。リストソースを更新すると、図Gに示すように検証コントロールも自動的に更新されます。データセットをテーブルに変換できない場合、同様の動的な結果を得るにははるかに多くの作業が必要になりますが、不可能ではありません。テーブルを使用しない手法の詳細については、「Excelのデータ検証機能を使用してデータ入力ミスを防ぐ方法」をご覧ください。
図F
検証リストは新しいレコードに使用できます。
図G
ソースリストを更新します。
このような単純な例では、アルファベット順のリストは必要ありません。アルファベット順のリストが必要な場合は、図Hに示すように、リストソースを並べ替えるだけです。
図H
リスト ソースを並べ替えて検証リストを並べ替えます。
動的検索機能
ルックアップテーブルを使えば、棚コードを選択する手間を完全に省くことができます。欠点は、数千行に及ぶ複雑なワークブックでは処理が少し遅くなることです。しかし、ほとんどの人にとっては問題なく動作します。ExcelのVLOOKUP()関数の仕組みをよく理解している必要があります。VLOOKUP()関数の計算式でよくある問題のトラブルシューティング方法を説明した短いチュートリアルをご用意しました。
開始する前に、ルックアップ値を少し更新する必要があります。図Iに示すように、棚コード列の左側に新しい「商品」列があります。これは、データセットの値と一致する一意の値を持つ列です。右側の値（棚コード）は、一致が見つかった場合に関数が返す値です。例えば、列Cの商品値が「Apples」の場合、列Eの関数は「APL」を返します。
図I
ルックアップ テーブルには少なくとも 2 つの列が必要です。
続行するには、図 Jに示すように、E3 に次の関数を入力し、残りのデータ セットにコピーします。
=VLOOKUP(C:C,$G$3:$H$8,2,FALSE)
図J
VLOOKUP() 関数を入力します。
図Kに示すように、新しいレコードを入力すると、Excelは数式を新しいレコードまで拡張します。これが発生しない場合は、次のオプションを確認してください。
- [ファイル]タブをクリックし、[オプション]を選択します。
- 左側のペインで「詳細設定」を選択します。
- [編集オプション] セクションで、[データ範囲の形式と数式の拡張] オプションがオンになっていることを確認します (図 L )。
- [OK]をクリックします。
図K
Excel は数式を拡張します。
図L
数式を拡張します。
現状では、ルックアップソリューションは動的ではないため、ルックアップテーブル（列GとH）に新しい果物を追加しても、列Eの関数は新しい行を含むように更新されません。試してみて、何が起こるか見てみましょう。図Mに示すように、ルックアップテーブルに「Coconut; CNT」を追加した後、Coconutを参照する新しいレコードを入力しても、期待どおりに更新されません。
図M
この関数は、行 9 の新しいルックアップ項目を参照しません。
ここでちょっとしたテーブルマジックが役に立ちます。新しい項目を追加する前に、ルックアップテーブル (G2:H8) をテーブルオブジェクトに変換するだけです。そして、新しい項目を追加します。図 Nに示すように、関数はルックアップテーブルオブジェクトの新しい行を参照するように自動的に更新されます。元の関数はH8を参照していましたが、データ範囲をテーブルに変換すると、関数はテーブルの最終行である9行目を参照するようになります。列Eの関数を更新する必要はありませんでした。果物データセットをテーブルオブジェクトに変換することもできますが、この手法を動的に動作させるためには必ずしも必要ではありません。
図N
ルックアップ テーブル データ セットを Table オブジェクトに変換します。
テーブルオブジェクトがヒーローです
どちらの高度なリストソリューションも、Tableオブジェクトのおかげで動的に操作できます。このオブジェクトがなければ、これらの高度なリストソリューションをこれほど柔軟にするには、はるかに多くの作業が必要になります。ExcelのTableオブジェクトについて詳しくは、「ExcelのTableオブジェクトを使用する10の理由」をご覧ください。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
また読んでください…
- Excel 2016 を自分好みにカスタマイズできる 10 個のデフォルト設定 (TechRepublic)
- Outlook のタスクにカスタム優先度フィールドを追加する方法 (TechRepublic)
- Office Q&A: Word 文書でテキストを繰り返す 2 つの簡単な方法 (TechRepublic)
- Excelのエラー：Microsoftのスプレッドシートが健康に危険をもたらす可能性（ZDNet）
- Excel スキルを磨く 10 のパワーヒント (TechRepublic の無料 PDF)
