Excel OFFSET を使用して動的なドロップダウン リストを作成する方法 (3 つの方法)

エクセル 巨大なデータセットを扱う場合、最も便利なツールです。通常、ドロップダウン リストの作成に慣れています 動的なドロップダウン リストを作成する必要があることがよくあります エクセルで 私たちの生活を楽にするために。 OFFSET関数を適用することで簡単に行うことができます .この記事では、動的なドロップダウン リストを作成する方法を紹介します。 エクセルで オフセットで 機能 .

これは、 動的ドロップダウン リスト の作成方法を説明するために使用するデータセットです。 エクセルで OFFSET 関数で .スポーツのイベントがあります 受賞者のリスト . ダイナミック ドロップダウン リストを作成して、勝者をそれぞれのイベントに分類します。 .

Excel OFFSET を使用して動的なドロップダウン リストを作成する 3 つの方法

1. OFFSET および COUNTA 関数を使用して Excel で動的ドロップダウン リストを作成する

ここでは、動的ドロップダウン リストの作成方法を説明します。 エクセル オフセットの使用 と COUNTA 機能。 動的ドロップダウン リストを作成する必要があります 範囲 C4:C11 . 勝者を選びます 受賞者のリストから .

手順:

➤ 範囲 C4:C11 を選択します .次にデータに移動します タブ>> データ ツール>> データ検証>> データ検証 .

➤ データ検証 ダイアログボックスがポップアップします。 リストを選択 ドロップダウンから そのダイアログ ボックスで .

➤ ソースで ボックスに、次の式を書き留めます。

=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)

式の内訳

➥ カウント($E$4:$E$100) ➜ 空でないセルの数を返します 範囲 E4:E100

出力 ➜ {4}

➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ 指定された参照の行と列に基づいて範囲を返します。

➥ オフセット($E$4,0,0,4,1)

出力 ➜ {“アレックス”;“モーガン”;“フォークナー”;“エリオット”}

説明: リファレンス E4 です . 行以来 0 です と列 0 です 最終的に 高さ 4 の セル、セル E4:E7 の値を取得します .

➤ [OK] を選択します .

エクセル ドロップダウンを作成します ボックス 各セル内 範囲の C4:C11 .

ドロップダウン ボックスのオプションに注目してください。 勝者のリストのものとまったく同じです .ここで、これが 動的ドロップダウン ボックス かどうかを確認します。 そうでなくても、勝者が イベント撮影の ジェームズです . ジェームス以来 受賞者リストにありません 、彼の名前を追加して、何が起こるか見てみましょう。

James の名前を追加するとすぐに 受賞者のリスト 、エクセル ドロップダウン オプションのオプションを自動的に更新しました . これらのドロップダウン リスト ダイナミック 自然の中で .
➤ 残りの勝者を選択します .

注意 :範囲 COUNTA関数で選択しました E4:E100 です .だからこそ Excel は ドロップダウン オプション を更新します セルを追加または更新する限り E4:E100 の範囲内 .

続きを読む: Excel で VBA を使用して動的データ検証リストを作成する方法

2. OFFSET および COUNTIF 関数を使用して Excel で動的ドロップダウン リストを作成する方法

動的ドロップダウン リストを作成することもできます エクセル オフセットの使用 と COUNTIF 関数。

手順:

➤ データ検証を持参 method-1 のようなダイアログ ボックス . ソースで ボックスに、次の式を書き留めます

=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))

式の内訳

➥ COUNTIF($E$4:$E$100,”<>”) ➜ 空でないセルの数を返します 範囲 E4:E100

出力 ➜ {4}

➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,”<>”)) ➜ 指定された参照の行と列に基づいて範囲を返します。

➥ オフセット($E$4,0,0,4,1)

出力 ➜ {“アレックス”;“モーガン”;“フォークナー”;“エリオット”}

説明: リファレンス E4 です . 行以来 0 です と列 0 です 最終的に 高さ 4 の セル、セル E4:E7 の値を取得します

➤ [OK] を選択します .

➤ エクセル ドロップダウンを作成します ボックス 各セル内 範囲の C4:C11 .

これが 動的ドロップダウン ボックス かどうかを確認するには そうでなくても、勝者が イベント撮影の ジェームズです . ジェームス以来 受賞者リストにありません 、彼の名前を追加して、何が起こるか見てみましょう。

James の名前を追加するとすぐに 受賞者のリスト 、エクセル ドロップダウン オプションのオプションを自動的に更新しました . これらのドロップダウン リスト ダイナミック 自然の中で .
➤ 残りの勝者を選択します .

注意 :範囲 COUNTIF関数で選択しました E4:E100 です .だからこそ Excel は ドロップダウン オプション を更新します セルを追加または更新する限り E4:E100 の範囲内 .

3.関数の組み合わせを使用してネストされたドロップダウン リストを作成する方法

このセクションでは、よりスマートで高度な動的ドロップダウン リストの作成について詳しく説明します。 、ネストされた 1。 オフセットを使用します 、COUNTA 、MATCH 一緒に機能します。私たちが何をしようとしているのか説明しましょう。
これはデータセットです 特定の製品の情報を表すこのメソッドの..基本的に、2 つのドロップダウン リストを作成します。 セル F3 で と F4 . F3 で選択したオプションに応じて 、エクセル F4 のオプションを更新します .一歩一歩やってみましょう。

STEP-1:F3 でドロップダウン リストを作成する

➤ データ検証を持参する method-1 のようなダイアログ ボックス . ソースで ボックス、セル参照を行います 、テーブル ヘッダーです (セル B3:D3 ).

エクセル ドロップダウン リストを作成します F3 で .

STEP-2:F4 で動的ドロップダウン リストを作成する

ここで、別のドロップダウン リストを作成します F4 で . ドロップダウン リストのオプション F4 の ドロップダウン リストで選択した内容によって異なります F3 の .そうするために、
➤ データ検証を持参 method-1 のようなダイアログ ボックス . ソースで ボックスに、次の式を書き留めます

=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)

式の内訳

➥ マッチ($F$3,$B$3:$D$3,0) ➜ セル値 F3 の相対位置を返します 範囲 B3:D3 から

出力:{1} .

➥ オフセット($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ 範囲を返します 行に基づく と列 特定の参照の . 高さ 10 です .だからアウトプット 10 の配列になります セル値 リファレンスから .

出力:{"サム";"カラン";"ヤンク";"ロチェスター";0;0;0;0;0;0}

➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ セルの数 を返します 選択した範囲で空でないもの .

➥ COUNTA{“サム”;“カラン”;“ヤンク”;“ロチェスター”;0;0;0;0;0;0}

出力:{4}

➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH ($F$3,$B $3) :$D$3,0)-1,10,1)),1) ➔ 範囲を返します 行に基づく と 列 特定の参照の

➥ OFFSET($B$3,1,1-1,COUNTA{"サム";"カラン";"ヤンク";"ロチェスター";0;0;0;0;0;0}),1)

➥ オフセット($B$3,1,0,4,1)

出力:{“サム”;“カラン”;“ヤンク”;“ロチェスター”}

説明: リファレンス B3 です . 行以来 1 です と列 0 です 最終的に 高さ 4 の セル、セル B4:B7 の値を取得します .

➤ [OK] を選択します .

エクセル 動的ドロップダウン リストを作成します F4 で . F3 で何を選択するかによってオプションが変わります .たとえば、名前を選択すると F3 ドロップダウン リストで 、ドロップダウン リスト F4 で 利用可能な名前が名前列に表示されます .

同様に、商品を選択すると F3 ドロップダウン リストで 、ドロップダウン リスト F4 で 商品列に利用可能な商品が表示されます .

名前を追加または更新すると 、商品 、またはブランド 、エクセル ドロップダウン リスト を更新します F4 で .たとえば、新しい名前 Rock を追加しました 名前列 とエクセル ドロップダウン リストに名前を追加しました .

続きを読む: Excel で動的なトップ 10 リストを作成する方法 (8 つの方法)

練習用ワークブック

ご覧のとおり、動的ドロップダウン リストを作成します。 エクセル OFFSET 関数で は本当にトリッキーです。ですから、どんどん練習することをお勧めします。練習用シートを添付しました。

結論

この記事では、 3 を説明しました。 動的ドロップダウン リストを作成するメソッド エクセル OFFSET 関数で .この記事がお役に立てば幸いです。最後に、コメントがあれば、コメント ボックスに残してください。

関連記事

• Excel で表から動的リストを作成 (3 つの簡単な方法)
• 基準に基づいて Excel で動的リストを作成する方法 (単一および複数の基準)