リンクされたドロップダウン リスト

説明

Excel では、すばやく簡単に次のことができます ドロップダウンリストを作成する , しかし、依存ドロップダウンリストを作成してみたことがありますか?このようなリストを作成するにはいくつかの方法がありますので、それらを見てみましょう。

方法 1. INDIRECT 関数

この手法は、INDIRECT 関数のアプリケーションに基づいており、指定されたセルの内容を Excel が理解できる範囲アドレスに変換するという 1 つの単純な処理を実行できます。つまり、セルに「」というテキストが含まれている場合、 А1 "の場合、関数は結果としてセルへの参照を与えます。 А1 。セルに「」という単語が含まれている場合、 Auto " の場合、関数は次の名前を持つ名前付き範囲への参照を出力します。 Auto 等

たとえば、この車種リストを見てみましょう。 Toyota, Ford そして Nissan :

トヨタモデルのリスト全体を選択します（セルから） А2 リストの最後まで)、この範囲に名前を付けます Toyota メニューの中で 挿入 - 名前 - 割り当て (Insert - Name - Define) 。次に、リストに対して同じことを繰り返します Ford そして Nissan 、それに応じて範囲の名前を指定します Ford そして Nissan 。

次に、自動車ブランドを選択するための最初のドロップダウン リストを作成しましょう。空のセルを選択してメニューを開きます データ - チェック (Data - Validation) 、ドロップダウン リストから データ・タイプ 選択肢一つを選択してください リスト そしてフィールドで ソース - ブランド名のセルを強調表示します (この例では黄色のセル)。をクリックした後 ОК 最初のドロップダウン リストの準備ができました。

次に、最初のリストで選択したブランドのモデルを表示する 2 番目のドロップダウン リストを作成しましょう。前のケースと同様に、空のセルを選択してメニューを開きます データ - チェック - さらに遠く リスト 。現場で ソース 次の式を入力する必要があります。

=INDIRECT(F3)

どこ：

• F3 - 最初のドロップダウン リストが含まれるセルのアドレス - あなたのアドレスに置き換えます。

全て。をクリックした後 わかりました 2 番目のリストの内容は、最初のリストで選択した範囲の名前によって選択されます。

この方法の短所:

• OFFSET タイプの式で指定されたダイナミック レンジは、セカンダリ (依存) レンジとして機能することはできません。これらをプライマリ (独立) リストに使用できますが、セカンダリ リストは数式を使用せずに厳密に定義する必要があります。
• セカンダリ範囲名は、プライマリ ドロップダウン リスト項目と一致する必要があります。つまり、空白のあるテキストが含まれている場合は、下線などに置き換える必要があります。
• 多数の名前付き範囲を手動で作成する必要があります。

方法 2. 一致リスト (OFFSET および MATCH)

このメソッドには、次のタイプのメイクモデル一致のソートされたリストが必要です。

ブランドのプライマリ ドロップダウン リストを作成するには、上記で説明した通常の方法を使用できます。

• 範囲 D1:D3 の名前を指定します (例: ブランド ）
• タブで選択 データ (Data) チーム データ検証 (Data validation)
• ドロップダウン リストからチェック オプションを選択します リスト (List) そして品質として指定します 情報源 (Source) = ブランド または、単純にセル D1:D3 を選択します (セルがリストと同じシート上にある場合)。

ただし、モデルの依存リストの場合は、関数 OFFSET を使用して名前付き範囲を作成する必要があります。これは、特定のブランドのモデルのセルのみを動的に参照します。このために：

• Ctrl + F3 を押すか、 ボタンを使用します。 ネームマネージャー (Name manager) タブ上で 数式 (Formulas) 。 2003 年より前のバージョンでは、これはメニュー コマンドでした 挿入 - 名前 - 割り当て (Insert - Name - Define)
• 任意の名前を付けて新しい名前付き範囲を作成します (例: モデル ）そしてフィールドで リンク (Reference) ウィンドウの下部に、次の数式を手動で入力します。

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 ）

関数 OFFSET は、最初のセルに対して指定された行数と列数だけシフトされた、必要なサイズの範囲への参照を発行できます。よりわかりやすいバージョンでは、この関数の構文は次のとおりです。

=OFFSET( 開始セル ; シフトダウン ; シフト ; range_size_in_rows ; range_size_in_columns ）

それで：

• 開始セル - リストの最初のセルを取得します。つまり、 A1 ;
• シフトダウン - MATCH 関数をカウントします。これは、簡単に言えば、指定された範囲 (列) 内の選択されたブランド (G7) のセルのシリアル番号を出力します。 そして );
• シフト =1 なので、隣の列のモデルを参照したいと思います ( で );
• range_size_in_rows - COUNTIF 関数を使用して計算します。この関数は、必要な値のリスト (列 A) 内の出現数をカウントできます。 - 自動車ブランド ( G7 );
• range_size_in_columns =1 したがって、モデルを含む 1 つの列が必要です。

結果は次のようになります。

作成した数式に基づいてドロップダウン リストをセルに追加する必要があります。 G8 。このために：

• セルを選択してください G8
• タブで選択 データ (Data) チーム データ検証 (Data validation) またはメニュー内で データ - チェック (Data - Validation)
• ドロップダウン リストからチェック オプションを選択します リスト (List) そして次のように入力します 情報源 (Source) 符号は範囲の名前と同じです。つまり、 = モデル 。

トピックに関する記事:

• セル内のドロップダウン リスト
• セルにデータを入力するときのヒント