從單元格內資料驗證清單中排除非空白空字串的非宏解決方案

Question 1

資料驗證下拉清單不會在這方面與您合作。它只想查看靜態列表，或確切地查看您告訴它的單元格，而不考慮其中的內容。

既然我們不能讓它做我們想要的事情，那麼讓我們妥協吧——我們將給它一個連續的範圍，只顯示我們想要看到的值（加上最後的空白，人們大多不會看）。

在我的範例中，包含資料驗證清單的值（和空白）的儲存格是 A1:A15。我將轉到另一列並輸入以下公式：

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")

我需要將其輸入為數組公式，所以當我輸入公式後，我將按 CTRL + SHIFT + ENTER。接下來，我將複製/貼上或填寫 15 行。結果將是 A1:A15 中的所有非空白值，然後是最後的所有空白。

現在，我將開啟「資料驗證」對話框，並將清單指向包含公式的儲存格（不是 A1:A15）。空白仍然在那裡，但在最後，所以沒有人需要看到它們，除非他們向下滾動到那裡看不到任何東西。

它並不完全乾淨，但它是動態的並且避免了巨集。

那麼這個公式做了什麼？

=INDEX($A$1:$A$15,

查看包含您的值和空白的原始範圍，並讓您透過數字告訴它您要查看哪個儲存格。

SMALL(

查看一組數字，從最小的開始計數，然後返回一個，

IF($A$1:$A$15<>"",

檢查範圍內的每個單元格是否符合空白字串以外的任何內容

ROW($A$1:$A$15)

如果存在則傳回行號，

,999),

如果不存在則傳回 999。如果您的行數超過 1000 行，則此處需要更大的數字，但這（希望）對於您的資料驗證選擇來說綽綽有餘。 SMALL 函數現在有一個行號列表，其中任何空白的實際行號都用 999 取代。

ROW(1:1)

是一種動態方式，從您輸入此公式的任何儲存格開始計數。這告訴小號，您想要包含公式的第一行中的第一個最小的數字，第二行中的第二小的數字，依此類推。

將SMALL行號傳回給INDEX，這將為您提供該行的值。這些 999 最後都變成了 #REF 錯誤，但我們悄悄地用IFERROR.

這個公式可能很難理解。如果您遇到問題，請嘗試在新工作表的A1:A15 中設定範例數據，在另一列中選擇一個單元格，然後將我的公式準確複製到公式欄中（不要忘記CTRL+SHIFT+ENTER）。稍微嘗試一下，您就應該掌握它的訣竅。

祝你好運！

Answer