Excel でテーブル ヘッダーからドロップダウンを作成するのは不可能に思えますが、このトリックで問題は解決します。

完璧な Excel テーブルを作成しましたが、そのヘッダーをドロップダウン メニューで使用しようとした瞬間に、すべてが壊れてしまいます。 Excel のデータ検証はテーブルにうるさいことで有名ですが、賢い回避策があります。したがって、メニューのハードコーディングをやめて、代わりにこの動的同期を使用してください。

という名前の書式設定された Excel テーブルがあると想像してください。 T_在庫。ルックアップ領域には、2 つのドロップダウン メニューが必要です。1 つは製品を選択するためのセル H2 で、もう 1 つは属性 (コストやサプライヤーなど) を選択するためのセル I1 です。両方を選択すると、セル I2 は正しい結果を返すはずです。

これから紹介する方法を使用しないと、セル I1 に属性を手動で入力する可能性があります。ただし、これにより検索の自動化が即座に停止され、スプレッドシートがあまり専門的ではなくなり、1 回のタイプミスでプロセスが中断されてしまいます。

もう 1 つのオプションは、データ検証ルールで固定範囲 $A$1:$E$1 を指すことです。この方法の問題は、テーブルに新しい列を追加しても、参照される範囲が自動的に拡張されないことです。

つまり、テーブルが大きくなるたびに自動的に更新されるメニューが必要になります。

テーブルがデータ検証を中断する理由

Excel テーブルは構造化参照 (T_Inventory(#Headers) など) を使用します。これは、データが変更されたときに自動的に調整されるため、優れています。問題は、データ検証が特定の範囲のセルまたは定義された名前を予期しているにもかかわらず、テーブルが使用する括弧で囲まれた構造化参照構文をネイティブに理解していないことです。

これを回避するには、テーブルとデータ検証ツールの間のブリッジとして名前付き範囲を使用します。

解決策: 動的検索を設定する

この検索エンジンを構築するには 3 つの手順が必要です。ただし、ステップ 2 では、単一の ヒーロー トリックが明らかになります。ドロップダウン メニューを真にダイナミックで破壊不可能にする賢いブリッジです。

ステップ 1: 製品の名前付き範囲を作成する (セル H2)

データ検証は構造化参照を嫌いますが、名前付き範囲を好みます。これは、製品リストをルックアップ領域に取得する最もクリーンな方法です。

まず、Product 列のすべてのセルを選択します。

カーソルが黒い下向き矢印に変わるまで、Product ヘッダー セルの上端にマウスを置き、1 回クリックします。これにより、ヘッダーを選択せず​​に、その列のデータが選択されます。

次に、名前ボックスに覚えやすい名前を入力します (例: 製品一覧) を入力して Enter を押します。

この名前付き範囲はテーブルの一部であるため、新しい行を追加すると自動的に拡張されます。

次に、セル H2 (製品のドロップダウン リストが表示される場所) を選択し、[データ]タブで[データ検証]をクリックします。

最後に、[許可]フィールドで[リスト]を選択し、等号 (=) に続いて作成した名前を入力し、[OK]をクリックします。

ここで、セル H2 を選択すると、製品のドロップダウン リストが表示されます。

通常、データ検証ではテーブル ヘッダーのドアが閉ざされますが、動的な名前付き範囲を作成することでその制限を回避できます。これを行うには、まず、[数式]タブで、[名前マネージャー]をクリックします。次に、「新規」をクリックします。

「名前」フィールドに次のように入力します。 ヘッダーリスト。次に、[参照先]フィールドの内容をすべて削除し、空になったフィールドにカーソルを置いて、テーブル内のすべてのヘッダーを選択します。フィールドにテーブル ヘッダーへの構造化された参照 (セルの直接参照ではなく) が含まれていることを確認します。

=T_Inventory(#Headers)

次に、「OK」および「閉じる」をクリックしてダイアログボックスを閉じます。

ここで、セル I1 (属性ドロップダウンが表示される場所) を選択し、手順 1 で行ったのと同様に、データ検証ダイアログの[許可]フィールドで[リスト]を選択します。ただし、今回は、[ソース]フィールドに等号 (=) と、その後に作成した名前を入力します。

[OK]をクリックすると、セル I1 のドロップダウン メニューにテーブル ヘッダーが含まれていることがわかります。

他の方法とは異なり、このリンクは完全に動的です。列ヘッダーまたはテーブル全体の名前を変更しても、名前マネージャーは自動的に更新され、ドロップダウン メニューが壊れることはありません。

ステップ 3: エンジンの式 (セル I2)

セル H2 で製品が選択され、セル I1 で属性が選択されている場合、すべてをまとめるためにもう 1 つの数式が必要です。この場合、INDEX と MATCH を使用する必要があります。

=INDEX(T_Inventory,MATCH(H2,T_Inventory(Product),0),MATCH(I1,T_Inventory(#Headers),0))

数式内の列名 ((Product)) がテーブルの最初の列名と正確に一致していることを確認してください。

式の仕組みは次のとおりです。

• 
  INDEX(T_在庫...): これはデータセット全体のマップです。
  


• 
  一致(H2...): これにより、製品の特定の行が検索されます。
  


• 
  一致(I1...): これにより、属性の特定の列が検索されます。
  

次に、ルックアップを試してみましょう。別の製品と属性を選択し、セル I2 の更新で結果を確認します。ただし、セル I1 で「コスト」を選択すると、セル I2 の結果は通貨ではなく、書式設定されていない単純な数値になることがわかります。

これは、セル I2 がカメレオンであり、列全体で異なるタイプのデータを切り替えているためです。しかし、心配しないでください。これを修正する簡単な方法があります。

フォーマットの問題を修正する

「コスト」を選択したときにセル I2 の結果が通貨として表示されるようにするには、簡単な条件付き書式ルールを入力する必要があります。

セルを選択し、[ホーム]タブで[条件付き書式]>[新しいルール]をクリックします。

ここで、[数式を使用して書式設定するセルを決定する]をクリックし、ダイアログの下部にあるフィールドに次のように入力します。

=$I$1="Cost"

次に、[書式]をクリックし、[数値]タブで[会計]または[通貨]を選択し、正しい記号が選択されていることを確認します。次に、[OK]を 2 回クリックして両方のダイアログ ボックスを閉じます。

ここで、セル I1 に「コスト」という単語が含まれる場合、セル I2 の結果が選択した通貨に自動的に変わります。

他の数値形式にも同じ方法を使用します。たとえば、日付の場合、数式は =$I$1="Date" となり、数値形式の選択は "Date" になります。テキスト列と標準の数値列には特別な書式設定は必要ありません。

最終テスト: 列を追加する

この設定の本当の威力は、変化をどのように処理するかにあります。セル F1 (表の右側の最初のセル) に、次のように新しい列ヘッダーを入力します。 再入荷しました新しいテーブル列を作成します。次に、セル I1 のドロップダウンを展開すると、新しい列ヘッダーがすでに存在していることがわかります。

---

データ検証ドロップダウン リストは、実際には思っているよりも便利です。これらを使用してテーブル ヘッダー セレクターを作成するだけでなく、選択内容に基づいて更新される動的なグラフを作成することもできます。自動化をさらに進めたい場合は、あるメニューのオプションが別のメニューで選択した内容に基づいて変化する、依存するドロップダウン リストを作成できます。