☰

Excel で動的な従属データ検証リストを実装するにはどうすればいいですか?

tag-icon • tag-icon microsoft-excel data-validation
Excel で動的な従属データ検証リストを実装するにはどうすればいいですか?

Web 上のソースでは、依存データ検証リストを実装する方法が示されていますが、私が目にしたものはすべて静的であり、自動的に更新されません。

この質問に答えられる人は、データ検証リストがどのように機能するかをすでに専門的に知っていると思いますが、私のような学習者のために簡単に説明します (Google や YouTube のビデオでさらに読むことをお勧めします)。

データ検証リストを使用すると、ワークブックに定義済みのデータ構造を作成できます。これらは、ユーザー インタラクション ベースでの使用を意図しています。ワークブック ユーザーに、ドーナツ、ケーキ、マフィン、クランペットなどの特定のデータ セットのみを挿入させたい場合、「スナック」などの名前付き範囲を使用してリストを作成するか、テーブルを作成してテーブルに「スナック」という名前を付けることができます。指定された名前は 1 つの単語です (つまり、指定された名前にスペースはありません)。

リストを使用することの欠点は、クロワッサンという別のスナックをリストに追加する必要がある場合、最初に作成したリストを再定義する必要があることです。テーブルを使用すると、最後の行から追加するか、現在のテーブル行の次の行に入力して Enter キーを押すだけで、テーブルに新しい行を簡単に追加できるため、この非効率性が回避されます。

さて、データの検証ですが、説明すると質問が長くなりすぎるので、詳細な説明については以下のリンクにアクセスすることをお勧めします。http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

したがって、従属検証リストは、以前の検証リストからユーザーが選択した値に基づくリストです。たとえば、ドーナツの種類には、アイスリング、チョコレートグレーズ、ジャム、カスタードドーナツが含まれます。マフィンの種類には、バナナ、ブルーベリー、チョコレートが含まれます。ケーキの種類には、レッドベルベット、キャロット、ココナッツが含まれます。最後に、クランペットの種類には、イングリッシュ、スコティッシュ、パイクレットが含まれます。

したがって、セルにメイン カテゴリ リストのドロップダウンがある場合、サブカテゴリに基づいて動的な従属検証リストを作成するにはどうすればよいでしょうか。

動的従属検証リストとは、以下に示すようにすべての列リストがテーブルであることを意味します。

テーブルとしてのデータリスト

ここでの質問は、隣接するセルをどのように実装するかということです。たとえば、セル C13 にスナック テーブルの検証リストがある場合、ユーザーが選択した C13 の入力に基づいてセル D13 を検証リストにするにはどうすればよいでしょうか (つまり、C13 がドーナツとして選択されている場合、D13 の検証リストの可能な選択はドーナツ テーブルの列データになります)。

C13 の元の検証リストはテーブルであり、名前付き範囲ではないため、INDIRECT 関数を使用して作成されたことに注意することが重要です。

答え1

4つの簡単なステップで簡単に

ステップ1 テーブルを作成します。

ここに画像の説明を入力してください

ステップ2 テーブルに名前を付ける: テーブルを選択し、プライマリテーブルの値と一致するように名前を変更します。表1たとえば、ヘッダーが「ケーキ」のテーブル2は次のように命名されます。ケーキ、これはプライマリテーブルの値であるため表1.
でも、どうやって？テーブルの角をクリックして選択し、新しいラベルを入力します。表2または同様のものを入力して Enter キーを押します。「Table2」などと表示された場合でも心配しないでください。
(注記！これはプライマリテーブル (例: Table1) には必要ありません。

ここに画像の説明を入力してください

ステップ3 最初のリストを配置する場所を選択し、データ タブに移動してデータ検証に進みます。「リスト」を選択し、=INDIRECT("Table1")ソース ウィンドウに次のように入力します。

ここに画像の説明を入力してください

ステップ4 最初のリストに依存する 2 番目のリストのセルを選択します。データ検証に戻り、「リスト」を選択して入力します。=INDIRECT(K2)「K2」は最初のリストの場所です。

ここに画像の説明を入力してください

終わり

2 番目のリストに依存する 3 番目のリストについては、手順 4 を繰り返し、代わりに 2 番目のリストを参照します。頑張ってください!

答え2

これが機能するという事実については、私の言葉を信じるしかありませんが、私はこの目的のために (非常に乱雑なスパゲッティ コードの) マクロを作成しました。残念ながら、コードはまったくエレガントではありませんが、とにかく機能します。よく言われるように...「機能するように、正しく、速くする」のです。

ステップ1: VBAコード

必要なものは3つあります:

  1. チップ・ピアソンのアレイモジュール--- 実際の VBA コードは最後のほうにあります。

  2. チップ・ピアソンのソートモジュール-- 実際の VBA コードも最後のほうにあります。

  3. 私のひどく整理されたマクロ

これらをワークブックの VBA モジュールに貼り付けるには、Alt+を押しF11、プロジェクト エクスプローラーでプロジェクトを見つけ、モジュール フォルダーを右クリックして [モジュールの挿入] を選択します。上記の 3 つのリンクのそれぞれからコードを個別のモジュールに貼り付けます。

ここに画像の説明を入力してください

VBA エディターで、[参照] ダイアログ ボックス ( Alt+ T)を開きEnter、[Microsoft Scripting Runtime] をオンにします。

ここに画像の説明を入力してください

ステップ2: データを構造化する

ここまで準備ができたら、次に必要なのは、きちんと構造化されたデータベースのように、データ (「データ」など) を含むシートです。次のようにします。

ここに画像の説明を入力してください

さらに 2 つのシートを作成したことに注意してください。1 つはドリルダウン検証ロジックが実行される場所用の「検証」、もう 1 つはエンド ユーザーにとって重要な実際のコンテンツ用の「メイン」です。

ステップ3: メインシート

データシートに移動して、次の構造を設定しましょう。

ここに画像の説明を入力してください

セルを選択しC2て名前を付けますSnack.Selected。セルに名前を付けるには、セルを選択して を押しAlt M M D、表示されるダイアログに画像のように名前を入力します。ここでは、次の手順がわかりやすいように「Cake」などの値を入力します。

セルに「Type.Selected」という名前を付けますC3が、今のところは空のままにしておきます。

ステップ4: データシートのテーブルから値を取得する

検証シートに移動して、次の構造を設定します。

ここに画像の説明を入力してください

スクリーンショットからわかるように、セルを選択しB3:B20て次の数式を入力する必要があります。ここで、マクロが実際に機能します。

=MultiLookup("Data","Snack",TRUE,TRUE)

Enterキーを押す代わりにCtrl+Shift+Enterキーを押します。これは配列数式

宣伝どおりに動作する場合は、スナックのリストが表示され、その後に #N/A エラーが表示されます。これらのエラーは想定内のもので、 のサイズに基づいて予想していたよりもスナックの数が少ないことを意味しますB3:B20。

数式のパラメータの説明:

  1. データが保存されているシート（「データ」）
  2. そのシートから必要なフィールド（「スナック」）
  3. 重複を削除しますか (つまりグループ化しますか)? (TRUE)
  4. アルファベット順に並べられていますか? (TRUE)

そうですね、あまり価値がないのに大変な作業のように思えますが、ここでグランドフィナーレです。セルにC3:C20次の数式を入力します。

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

これにより、利用可能なケーキの種類が表示されます。これは、前の手順でメイン シートに入力した値です。覚えていますか?

これは、数式に 2 つの引数を追加することで機能します。

  1. フィルタリングするフィールド ("スナック")
  2. (Snack.Selected) に等しい値のみを選択します。

したがって、Main でデータを変更すると、Types 列が自動的に更新されます。

ステップ5: 検証の完了

最後に、 とSnack.ChoicesのType.Choices名前を作成してデータ検証を完了しましょう。検証でセルにそれぞれ と という名前を付けてください。この小さなボックスに入力してセルに名前を付けることもできますB1。C1

ここに画像の説明を入力してください

セルにB1次の数式を入力する必要があります。

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

ここに画像の説明を入力してください

これは、スナックの選択肢が配置されている Validation!$B$3:$B$6 へのテキスト参照を作成します。この数式を右側にコピーすれば完了です。

メイン シートに戻り、Snack と Type の検証ルールでこれらのセルを参照してみましょう。

セルを選択しC2、キーボード > AltA V Vでデータの入力規則を作成します。「リスト」を選択し、ソースを に設定します=INDIRECT(Snack.Choices)。引用符がないことに注意してください。Snack.Choices

ここに画像の説明を入力してください

セルに対しても同じ操作を行いますC3が、ソースを に設定します=INDIRECT(Type.Choices)。

およびINDIRECT()の値はワークブックの範囲への間接的な (つまりテキストの) 参照であるため、を使用します。Snack.ChoicesType.Choices

次に、検証オプションを試して、全体の動作を確認してみましょう。

ご質問があればお知らせください。

関連情報