Защитить лист vs. Защищаемая ячейка в Excel – Защита по цвету ячейки!⁠⁠

Защита листа в Excel – это всем хорошо известная функция, которая позволяет блокировать внесение изменений в рабочий лист («Рецензирование» -> «Защитить лист»). Я уверен, ты с ней уже знаком.

В этом же посте я хотел поделиться возможностью такой защиты листа, при которой конечный пользователь файла может вносить изменения в ячейки, помеченные определенным цветом. Такой вариант защиты рабочего листа может быть очень полезным в самых разнообразных ситуациях. Один из первых примеров – это формуляры на рабочих листах Excel.

Итак, вот как можно настроить защиту листа для определенных цветных ячеек. В качестве примера предположим, что конечный пользователь нашего файла должен дозаполнить эту таблицу умножения, внеся значение в пустые ячейки, выделенные оранжевым цветом:

Для этого, в настройках форматирования каждой из этих оранжевых ячеек нужно отключить настройку защиты ячейки «Защищаемая ячейка» (то есть даже если весь лист будет защищен, ячейки всё также будут изменяемыми).

Конечно, с одной стороны, можно в случае нашего маленького примера вручную выбрать все оранжевые ячейки и изменить их настройку защиты – но это не то, что нам нужно, ведь, во-первых, в реальном примере из жизни количество требуемых к заполнению ячеек может быть очень большим, а во-вторых, повышается риск совершения ошибки человеческого фактора – можно банально проглядеть одну из нужных ячеек. Именно поэтому мы будем использовать автоматизированный подход.

Выбираем область, в которой находятся нужные нам ячейки – в нашем примере это область таблицы умножения:

И открываем окно поиска значений с помощью комбинации клавиш CTRL+F:

Нажав на «Параметры», раскрываем расширенные настройки поиска:

Теперь, если у тебя вместо «Формат не задан» написано «Образец» (1), то нужно нажать на «Очистить формат поиска»:

После этого щелкаем по кнопке «Формат» (5) и в открывшемся окне, не изменяя других настроек, указываем те настройки, которые нам нужны. В нашем примере нам нужны лишь оранжевые ячейки, так что захожу в раздел «Заливка» (6) и выбираю нужный мне цвет (7), после чего подтверждаю ввод (8):

Отлично, требуемый формат задали, так что идём дальше, и теперь нажимаем на «Найти все» (9). В нижней части окна Excel выводит все найденные ячейки, соответствующие заданному формату (10):

Одна из строк здесь при этом уже выбрана, так что всё, что остаётся сделать – это использовать комбинацию клавиш CTRL+А для выбора всех найденных ячеек, после чего окно поиска можно закрыть. В итоге мы выбрали все оранжевые ячейки в нужном диапазоне:

Сейчас все нужные оранжевые ячейки были успешно выбраны, так что теперь заходим в окно форматирования ячеек (CTRL+1) и в открывшемся окне во вкладке «Защита» убираем галочку в поле «Защищаемая ячейка» (11):

Подтверждаем и теперь защищаем рабочий лист (Вкладка «Рецензирование» -> «Защитить лист»):

Всё, всё готово! Теперь пользователь этого файла может вносить изменения лишь в разрешенные нами ячейки.

Важный при этом бонус, который делает процесс заполнения ячеек для пользователя очень эффективным и приятным – это возможность использования клавиши Таб. Я наглядно показал, как классно работает клавиша Таб с подобной защитой листа вот в этом видео:

В нём я также в принципе пошагово и еще более углубленно разобрал тему сегодняшнего поста, поэтому также предлагаю тебе посмотреть это видео.

Надеюсь, совет будет для тебя полезным 😊