シート 1 に ID と説明のリストがあります。このデータは、ユーザーが操作できる 2 番目のシート (シート 2) で使用されます。
シート 1 のリストには、繰り返される ID がありますが、説明が異なります。シート 2 のデータ検証を使用して、ID 番号 4 のすべての繰り返しを 1 つに結合し、列 A で選択を行った後、ユーザーが希望する説明 (ドロップダウン ボックスを使用) を選択できるようにするにはどうすればよいでしょうか。
このためにマクロは使用せず、組み込み関数のみを使用するようにします。
どのような助けでも大歓迎です!
答え1
Sheet1 の繰り返しを結合します。ルックアップ式は最初に見つかった値のみを返すため、同じ ID が複数ある場合は、代わりに一意の ID を持つルックアップを作成する必要があります。それをヘルパー テーブルで準備し、元のテーブルを変更したくない、または変更できない場合は、データ検証にヘルパー テーブルを使用します。
識別子 (理由があってそう呼ばれています) が一意ではないというのは、非常に誤解を招きます。
答え2
使用している Excel のバージョンについては言及されていません。質問を理解した上で、可能な解決策を提案します。ただし、IFERROR および COUNTBLANK という関数も使用されており、これらは Excel 2007 より前では使用できないと思われます。したがって、Excel 2003 を使用している場合は、別のアプローチが必要になる可能性があります。
これはヘルパー列とヘルパーシートも使用するため、あまり理想的なソリューションではない可能性があります。
この例では、サンプル データは、このスクリーンショットに示すように、Sheet1!A2:B10 にあります。
まず、ID から一意のリストを取得するために、列 D にヘルパー列を作成する必要があります。
D2 に次の数式を入力し、数式バー内でCTRL+ SHIFT+を押してENTER配列数式を作成します。また、これを正しく機能させるには、行 (できれば最初の行) をヘッダーとして残す必要があります。
数式は、配列数式であることを示すために中括弧で囲む必要があります。
マスター列の一意の値の予想数に基づいて、目的の行まで下にドラッグします。最終的に下部に空白が返されるようになったら、そこで停止できます。これにより、ID の一意のマスター リストが作成されます。
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
ここで、検証リストにデータを入力するには、このリストの正確な開始と終了を知る必要があります。
ここで、G3 に次の数式を入力します。
=INDEX(Sheet1!D2:D10,1)
G4 に次の配列数式を入力します。
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
これにはCTRL+ SHIFT+を忘れないでください。ENTER
次に、名前マネージャに行き、MyListという新しい名前を作成します。
そこに次の式を入れてください
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
ここでは、INDEXを使用して値ではなくセル参照を返します。
Sheet2 という名前の新しいシートを挿入します。
次に、列 A2 以下に検証リストを作成し、=MyListそこに配置します。
これで、すべての一意の値が ID の最初のドロップダウン リストに表示されるようになりました。
これでパート1は完了です。
次のタスクは、説明列から抽出されたデータに基づいて 2 番目の検証リストにデータを入力することです。
Sheet3 という 3 番目のヘルパー シートを挿入します。このシートは単に非表示にすることができます。
この例では、Sheet3!B2:H10 からデータを取得しました。マスター リストの ID の重複の最大数に基づいて列の数を決定します。また、正しく動作させるために、最初の列 A を残しておきます。
B2 に次の数式を入力し、数式バー内からCTRL+ SHIFT+ を押して配列数式を作成します。ENTER
意図したとおりに、横方向と下方向にドラッグします。
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
名前マネージャーに戻り、「Trimmed」という新しい名前を作成し、そこに次の数式を入力します。
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
これにより、リストが適切にトリミングされるように、空白を除いたリストが生成されます。
次に、Sheet2 のセル B2 以下に検証リストを作成し、=Trimmedそこに入力します。
これで、Sheet1!A2:A10 のデータが変更されると、一意の値のリストが MyList に自動的に反映され、名前 Trimmed によって、Sheet1 の説明列から目的の説明のみが取得されます。
全体の仕組みを理解するには、以下の Screen to GIF をご覧ください。
