☰

Excel 2013 - データの検証 - 2 番目の条件に一致した場合に値を含むドロップダウン リストを作成する

tag-icon • tag-icon microsoft-excel vba
Excel 2013 - データの検証 - 2 番目の条件に一致した場合に値を含むドロップダウン リストを作成する

問題をできるだけ単純化します。Excel に 2 つのシートがあります。最初のシートでは、場所のリストを作成しています。列 B に場所を入力する前に、列 A で場所が位置するエリアを選択する必要があります。次の列 (C) には、自動的に作成される ID があります (最初の場所には ID-001、2 番目の場所には ID 002 など)。

2 番目のシートでは、資産のリストを作成しています。したがって、再び列 A でエリアを選択し、列 B に資産の名前を入力します。次に、列 C にドロップダウン リストを表示して、最初のテーブルから同じエリアにある ID のみを表示します。OFFSET 関数を使用しようとしましたが、ID のリストを同じエリアの ID だけに制限するための何らかの「select if」(countif や sumif など) が欠けています。

各エリアには多くの場所と資産が存在する可能性がありますが、すべての場所とすべての資産は 1 つの場所にのみマッピングできます。

皆さんの回答をお待ちしています。Excel ソリューションでも VBA ソリューションでもどちらでも結構です。

答え1

VLOOKUPで使用する一意の値を含むヘルパー列を作成することをお勧めします。たとえば、Area1、Area2、Area3、Area1（再び）などがあるとします。これらの値を増分値（例：01、02、03、...）と結合して、01Area1、01Area2、01Area3を作成します。02エリア1これで、2番目のシートの各リスト項目に何を呼び出すかがわかりました。エリア1A列の場合は検索が必要です増分数＆エリア1値による増分番号は次のように作成できます。カウント拡張範囲を使用する関数:

=COUNTIF($A$2:A2,A2)

範囲を拡張すると、数式はその行までの値のみをカウントします。この方法の唯一の欠点は、リスト項目を作成する必要があることです。他の場所

同様の使用法を説明する 2 つのリンクを以下に示します。

  1. https://www.spreadsheetweb.com/how-to-get-nth-match-with-vlookup/
  2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

答え2

Locations テーブルを並べ替えても問題ないと仮定すると、最も簡単なソリューションは、比較的短いデータ検証式といくつかの名前付き範囲だけです。ヘルパー列やヘルパー行は必要ありません。

次のように、 2 つのサンプル ワークシートとを設定しLocationsますAssets。

場所ワークシートのスクリーンショット  資産ワークシートのスクリーンショット

定義済みの名前を 2 つ追加します。

  1. Areas→=Locations!$A:$A
  2. IDs→=Locations!$C:$C

C2最後に、次の数式を使用してワークシートにセル内ドロップダウン リストのデータ検証を追加しAssets、セルを下方向に入力/コピーして貼り付けます。

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

この方法の欠点は次のとおりです。

  • 定義された名前を使用する必要があります
  • 場所テーブルは並べ替える必要があります
  • Locations シート内の他のテーブルは、AreaLocation テーブルのArea値と一致する値が含まれていないことが保証されない限り、列を安全に交差することはできません。

次の代替ソリューションは、他のテーブル以外の欠点を克服します。ただし、ドロップダウン リストの項目を保存するために Assets ワークシートのセルを使用します。

資産ワークシートには追加の列が設定されています。

資産ワークシートのスクリーンショット

今回のC2データ検証式は次のとおりです。

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

次の数式を入力してD2、下へ記入します。

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

複数セル配列 - この最後の数式をE2、エリア内の予想される場所の最大数まで右に進んでセルに入力します (示されている例では を使用しましたE2:I2)。

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)

関連情報