排序无法将数据验证规则与 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 允许数千列，您也不会用完键。为每个键使用单独的列也将起到一种输入表单的作用。您不会忘记输入水果的值，因为该值的单元格将为空。

如果您有多个样本（并非所有苹果都有相同的直径等），您将能够将电子表格中的每一行视为单个样本。

这种方法还将促进数据的进一步处理，例如创建图表和数据透视表。