如何在 Excel 中實現動態相關資料驗證列表

Question

網路上的資源顯示如何實現相關資料驗證列表，但我遇到的所有內容都是靜態的，不會自動更新。

我相信任何能夠回答這個問題的人都已經知道資料驗證清單如何在專業程度上發揮作用，但為了像我這樣的學習者的目的，我將給出一個簡短的描述（我建議進一步閱讀谷歌和YouTube影片).

資料驗證清單可讓您在工作簿中建立定義的資料結構。它們更適合用戶互動基礎。如果您希望工作簿使用者僅插入一組給定數據，例如甜甜圈、蛋糕、鬆餅和烤餅，您可以使用命名範圍（例如「零食」）建立一個列表，也可以建立一個表並將表命名為「零食」。其中給定名稱是一個單字（即給定名稱中不存在空格）

使用清單的缺點是，如果我們必須在清單中添加另一種名為羊角麵包的小吃，那麼我們將需要重新定義最初建立的清單。表格繞過了這種低效率，因為您可以透過從最後一行追加新行或在當前表格行後面寫入後續行並按 Enter 鍵來輕鬆地將新行新增至表中。

現在，對於數據驗證，我建議訪問下面的連結以獲取詳細信息，因為解釋會使這個問題變得太長。http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

因此，相關驗證列表是基於用戶從先前驗證列表中選擇的值的列表，例如，甜甜圈類型包括冰圈、巧克力釉面、果醬和蛋奶甜甜圈。鬆餅類型包括香蕉、藍莓和巧克力。蛋糕類型包括紅色絲絨蛋糕、胡蘿蔔蛋糕和椰子蛋糕。最後，鬆餅類型包括英國鬆餅、蘇格蘭鬆餅和小皮克餅。

因此，如果單元格具有主類別清單的下拉式選單，如何根據子類別建立動態依賴驗證清單。

透過動態相關驗證列表，我的意思是所有列列表都是如下所示的表格。

現在的問題是如何實現相鄰單元格，例如，如果單元格 C13 有零食表的驗證列表，如何根據用戶選擇的 C13 輸入使單元格 D13 成為驗證列表（即，如果選擇 C13 作為甜甜圈，則D13 的驗證清單的可能選擇將是Donuts 表的列資料）。

請務必注意，C13 中的原始驗證清單將使用 INDIRECT 函數創建，因為它是一個表而不是命名範圍。

Answer 1

只需 4 個簡單步驟即可輕鬆完成

步驟1 建立您的表：

第2步 命名表：選擇表並重新命名它們，使其與主表中的值相符表格1，例如。標題為「Cakes」的表 2 應命名為蛋糕，因為這是主表中的值表格1。
但如何呢？點擊表格的一角將其選中，在其顯示的位置輸入一個新標籤表2或類似的，然後按 Enter。如果它仍然顯示“Table2”等，請不要擔心
（筆記！這對於主表來說不是必需的，例如。表格1。

步驟3 選擇您想要第一個清單的位置，轉到資料標籤並進入資料驗證。選擇“list”並=INDIRECT("Table1")在來源視窗中輸入：

步驟4 為第二個清單選擇一個儲存格，該儲存格將依賴第一個清單。傳回資料驗證，選擇「清單」並輸入=INDIRECT(K2)其中「K2」是第一個清單的位置。

完畢

對於依賴第二個列表的第三個列表，請重複步驟 4 並改為參考第二個列表。祝你好運！

Answer 2

您必須相信我的話，因為這確實有效，但我為此目的編寫了一個（非常混亂的意大利麵條程式碼）巨集。不幸的是，程式碼一點也不優雅，但是嘿，它可以工作！正如他們所說......“讓它發揮作用，讓它正確，讓它快速”。

第 1 步：VBA 程式碼

你需要三樣東西：

1. Chip Pearson 的數組模組--- 實際的 VBA 程式碼即將結束。

2. Chip Pearson 的排序模組-- 實際的 VBA 程式碼也接近尾聲。

3. 我的宏組織得非常好

若要將這些貼到工作簿上的 VBA 模組中，請按Alt+ F11，在專案資源管理器中找到您的項目，右鍵點選「模組」資料夾並選擇「插入模組」。將上面 3 個連結中每個連結的程式碼貼到單獨的模組中。

當您使用 VBA 編輯器時，開啟「引用」對話方塊（然後是Alt+ ）並選取「Microsoft Scripting Runtime」。TEnter

第 2 步：建構數據

有了這些，您現在需要的是一張包含資料的工作表（稱為「資料」），就好像它是一個結構良好的資料庫一樣。我是這樣做的：

請注意，我又創建了兩張紙。一種稱為“驗證”，用於進行深入驗證邏輯，另一種稱為“主要”，用於對最終用戶重要的實際內容。

第 3 步：主表

讓我們轉到數據表並設定以下結構：

選擇單元格C2並為其命名Snack.Selected。若要命名儲存格，請選取它，然後按一下Alt M M D，然後在出現的對話方塊中鍵入名稱，如圖所示。現在，輸入一個值，例如“蛋糕”，以便下一步更有意義。

繼續並將單元格命名為C3“Type.Selected”，但暫時將其留空。

第 4 步：從資料表中的表中取得值

轉到您的驗證表並設定以下結構：

正如您在螢幕截圖中看到的，您應該選擇單元格B3:B20並鍵入以下公式，這就是我的巨集實際發揮作用的地方：

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

不要按 Enter，而是按 Ctrl+Shift+Enter，因為這是一個數組公式

如果它按照廣告宣傳的那樣工作，您應該會看到一份零食列表，後面跟著一些#N/A 錯誤。這些錯誤是預料之中的——它們只是意味著你的零食比你根據 的大小所預期的要少B3:B20。

公式中參數解釋：

1. 資料所在的工作表（「資料」）
2. 您想要從該工作表中獲得的欄位（“小吃”）
3. 刪除重複項（即分組）？ （真的）
4. 按字母順序排序？ （真的）

好吧，這似乎是大量的工作卻毫無價值，但最後的結局來了。在 cells 上C3:C20，您將放置以下公式：

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

這應該顯示蛋糕的可用類型，這是您在上一步中在主表中輸入的值，還記得嗎？

這是透過向公式添加兩個參數來實現的：

5. 要過濾的欄位（“小吃”）
6. 僅選擇等於 (Snack.Selected) 的值

因此，當您更改 Main 中的資料時，「類型」列會自動更新！

第 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和 kbd>AltA V V以建立資料驗證。選擇“清單”並將來源設定為=INDIRECT(Snack.Choices).請注意，周圍沒有引號Snack.Choices

在單元格上執行相同操作C3，但將來源設定為=INDIRECT(Type.Choices).

我們之所以使用和 ，是因為和INDIRECT()的值是對工作簿上範圍的間接（即文本）引用。Snack.ChoicesType.Choices

現在嘗試驗證選項以查看整個操作的情況。

如果您有任何疑問，請告訴我！