排序無法將資料驗證規則與 Excel 中的資料一起移動

Question

在 Excel 中對表格或範圍進行排序時，儲存格值和基本格式（如粗體、斜體、顏色等）會移動，但以下內容不會移動：

• 邊框
• 資料驗證規則

我的電子表格具有每個單元格的資料驗證功能，因此當我執行排序操作時，它會移動值，但不會進行驗證。

這是排序之前的範例：

排序後（“水果”名稱上的 AZ。）

有沒有辦法讓 Excel 在執行排序操作時將資料驗證規則以及值和格式一起移動？

編輯：

只是為了澄清一下，我在這裡有兩個獨立的焦點：

1. 主要問題：如果使用者決定單擊“排序”按鈕或“過濾器”列標題上的“排序”選項，這將破壞我的（複雜！）資料驗證規則。
2. 第二個擔憂：這將是好的是否可以使用內建的 Excel 按鈕對我的工作表進行排序（這些是對資料進行排序的最明顯的方法）。僅用於進行排序的單獨巨集似乎有點老套。

編輯：

我最終的解決方案只是透過保護工作簿並停用所有工作表的排序來完全停用排序。您可以按“排序”按鈕，但它們什麼都不做。

作為額外的保護措施，我添加了VBA，每次打開工作簿時都會重新應用“保護工作簿”，並顯示一個包含使用說明和警告的工作表（例如“不要對此進行排序！”）。用戶可以取消- 隨時保護工作簿，所以這並不是萬無一失的，但我相信這是我能做的最好的事情。

Answer 1

您可以製定處理 pH 值的一般規則並將其應用於整個值列。您只需新增一個條件來檢查 Key1 以查看該值是否為 pH 值；如果是，檢查標準；如果不是，則傳回 TRUE，這樣任何事情都是允許的。

例如，以下是套用於C2:C6表中（值 1 列）的自訂資料驗證規則：

=IF(B2="pH",AND(C2>=0,C2<=14,C2=INT(C2)),TRUE)

這將限制 pH 值 0<=pH<=14 並且僅限整數。由於該規則應用於整列，因此排序不會影響資料有效性。

如果使用條件格式套用邊框，則可以以類似的方式處理邊框。只需使用適當的公式規則將其應用於列中的所有數據，例如

=B2="pH"

Answer 2

看看舊程式碼，我似乎刪除並重新建立了驗證器：

var cellValues = activeSheet.Range[leftColumn + startingRow, Type.Missing];
cellValues.Validation.Delete();

如果您想以程式設計方式在 Excel 中執行任何操作，請錄製巨集 > 手動執行操作 > 停止巨集錄製 > 按 Alt + F11 並查看新建立的模組中的程式碼。這是學習 VBA 程式設計的快速方法。祝你好運！

Answer 3

這不完全是你問題的答案，但我覺得應該要提及。

將資料輸入電子表格的方式有點不常見，如果沒有特定原因，您也可以透過稍微更改電子表格的設計來解決問題。輸入資料（如您的資料）的最直接方法是每個鍵一列（顏色、pH、直徑等），每個水果一行。這樣您就可以為整個列定義規則和樣式，而不必擔心排序。

如果您需要使用鍵和值存取數據，則使用資料透視表和資料透視表查找 (GETPIVOTDATA) 或行/列查找 (VLOOKUP / HLOOKUP) 可能會有所幫助。

在您的情況下，鍵似乎定義得很好（否則是採用鍵值方法的原因），考慮到 Excel 允許數千列，您也不會用完鍵。為每個鍵使用單獨的列也將起到一種輸入形式的作用。您不會忘記輸入水果的值，因為該值的儲存格將為空。

如果您有多個樣本（並非所有蘋果都具有相同的直徑等），您將能夠將電子表格中的每一行視為單一樣本。

這種方法還將有助於進一步處理數據，例如建立圖表和數據透視表。