Сортировка не позволяет перемещать правила проверки данных вместе с данными в Excel

Question

При сортировке таблицы или диапазона в Excel значения ячеек и базовое форматирование, такое как полужирный шрифт, курсив, цвета и т. д., перемещаются, но следующие элементы НЕ перемещаются:

• Границы
• Правила проверки данных

В моей электронной таблице реализована проверка данных по ячейкам, поэтому при выполнении операции сортировки значения перемещаются, но их проверка не сохраняется.

Вот пример перед сортировкой:

И после сортировки (по названию «Фрукты» от AZ).

Есть ли способ заставить Excel перемещать правила проверки данных вместе со значениями и форматами, когда Excel выполняет операцию сортировки?

Редактировать:

Чтобы прояснить ситуацию, у меня есть два отдельных опасения:

1. Основная проблема: если пользователь решит нажать кнопку «Сортировать» или параметр «Сортировать» в заголовке столбца «Фильтр», это нарушит мои (сложные!) правила проверки данных.
2. Второстепенная проблема: это было быхорошийесли бы мой рабочий лист можно было сортировать с помощью встроенных кнопок Excel для сортировки (это самые очевидные способы сортировки данных). Отдельный макрос только для сортировки кажется немного хакерским.

Редактировать:

Моим окончательным решением было просто отключить сортировку полностью, защитив книгу и отключив сортировку для всех листов. Вы можете нажать кнопки «Сортировка», но они ничего не сделают.

В качестве дополнительной меры безопасности я добавил VBA, который повторно применяет функцию Protect Workbook каждый раз при открытии книги и выводит рабочий лист с инструкциями по использованию и предупреждениями (например, «Не сортируйте это!»). Конечный пользователь может снять защиту с книги в любое время, так что это не является надежной защитой, но я считаю, что это лучшее, что я могу сделать.

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 допускает тысячи столбцов, у вас также не закончатся ключи. Использование отдельного столбца для каждого ключа также будет работать как своего рода форма ввода. Вы не забудете ввести значение для фрукта, поскольку ячейка для этого значения будет пустой.

Если у вас есть несколько образцов (не все яблоки имеют одинаковый диаметр и т. д.), вы сможете обрабатывать каждую строку в электронной таблице как один образец.

Такой подход также облегчит дальнейшую обработку данных, такую ​​как создание диаграмм и сводных таблиц.