☰

Excel 2013 - 資料驗證 - 如果符合第二個條件，則建立包含值的下拉列表

tag-icon • tag-icon microsoft-excel vba
Excel 2013 - 資料驗證 - 如果符合第二個條件，則建立包含值的下拉列表

我會盡可能簡化我的問題。我有兩張 Excel 工作表。在第一張表中，我正在建立位置清單。我需要在 A 列中選擇該位置所在的區域，然後才能在 B 列中輸入位置。 ID 002，等等）。

在我的第二張表中，我正在建立一個資產清單。因此，我再次在 A 列中選擇一個區域，並在 B 列中輸入資產名稱。 。我嘗試使用 OFFSET 函數，但缺少的一點是某種“select if”（如 countif 或 sumif）來將 ID 列表限制為具有相同區域的 ID。

每個區域可以有多個位置和資產，但每個位置和每個資產只能對應到一個位置。

期待你們的答案！無論是 Excel 解決方案還是 VBA 解決方案都很棒。

答案1

我建議建立一個輔助列，其中包含要在 VLOOKUP 中使用的唯一值。例如;假設您有 Area1、Area2、Area3 和 Area1（再次），然後繼續。將此值與增量值（例如 01、02、03...）合併並建立 01Area1、01Area2、01Area3 和02區域1。現在您知道如何呼叫第二張表中的每個清單項目。如果您選擇區域1在A列中，那麼你需要搜索增量數&區域1。按值遞增的數字可以透過以下方式創建計數值使用擴展範圍的函數：

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

透過擴展範圍，該公式僅計算直到其自己行的值。此方法的唯一障礙是您需要在其中建立清單項別處

這裡有兩個描述類似用途的連結：

  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和Assets，如下所示：

位置工作表的螢幕截圖  資產工作表的螢幕截圖

新增兩個定義的名稱：

  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 表必須排序
  • 位置表中的其他表無法安全地與該Area列相交，除非保證它們不包含與位置表的Area值相符的值。

以下替代解決方案克服了除其他表之外的缺點。但是，它確實使用資產工作表中的儲存格來儲存下拉清單的項目。

資產工作表設定有附加列：

資產工作表的螢幕截圖

這次的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()
  )
)

相關內容