如何在 Excel 2010 中為不斷變化的值欄位建立下拉式清單？

Question

我有一個 Excel 電子表格，其中有一列多年，標題為：

Years
1993
1993
1994
1994
1994
...
2011
2011

年份存在重複值，並且隨著時間的推移將添加其他行。

我有另一個單元格需要顯示年份的下拉列表，但僅顯示唯一的年份。我嘗試過使用 Excel 2011 中的資料驗證功能，但它有兩個問題：

1. 它顯示重複的年份。
2. 我告訴它使用整個列，並且它包括下拉列表中的空白單元格。

如何取得僅顯示唯一值的年份下拉列表，同時在新增其他行時自動更新？

編輯: 多一點資訊。下拉清單在單獨的工作表中用於顯示計算數據，就像 Access 表單一樣。用戶可以選擇年份範圍，數據將相應更新。原始表只是所有資料的列表。

Answer 1

對於這種驗證，我使用 VBA + 一個骯髒的技巧：

首先，使用 Alt+F11 進入 VBA 編輯器然後，我將「動態清單驗證程式碼」(tm):) 放入對應的工作表中。

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

此程式碼使用資料->驗證->錯誤訊息->標題中輸入的公式產生的清單更新儲存格驗證清單。這樣，每個具有列表驗證的單元格都可以有自己的公式。

然後，我新增一個模組（插入->模組），然後將此程式碼放入新模組中：

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

此函數傳回範圍內的所有儲存格，不帶空格或重複。然後，在每個具有清單驗證的儲存格中，我在資料驗證的錯誤訊息標題中新增 GenDynList(range)。

Answer 2

凌亂的。沒有內建的方法可以自動更新。只創建一個包含您可能感興趣的所有可能年份的單獨列表，而不是嘗試將其限制為資料集中的年份，會更簡單。

Answer 3

從另一張工作表上的資料新增資料透視表。使用年份作為行，表的其餘部分不相關。根據需要對行進行排序和過濾（例如，明確過濾 OUT“[blank]”）。將行標籤所在的儲存格設定為資料驗證的目標。

使用擴充命名範圍來始終使用所有新標籤： http://www.ozgrid.com/Excel/DynamicRanges.htm

對其他欄位（例如國家/地區、州/省/自治區/直轄市）重複此動作。您的流程現在將是：新增資料、刷新資料透視表、使用更新的資料驗證。

進一步的想法：使用來源資料表將使更新資料透視表變得容易，因為它們將始終使用整個表。或者，使用擴展命名範圍作為資料來源，以避免在新增更多行時出現問題。

請注意，如果透過定義命名範圍來執行此操作，則可以使用不同工作表中的範圍作為資料驗證的來源。您不能使用明確引用另一張工作表的正常範圍。