Выделить незащищенные ячейки excel
Данные в Excel можно защищать от постороннего вмешательства. Это важно, потому что иногда вы тратите много времени и сил на создание сводной таблицы или объемного массива, а другой человек случайно или намеренно изменяет либо вовсе удаляет все ваши труды.
Рассмотрим способы защиты документа Excel и его отдельных элементов.
Защита ячейки Excel от изменения
Как поставить защиту на ячейку в Excel? По умолчанию все ячейки в Excel защищаемые. Это легко проверить: кликаем на любую ячейку правой кнопкой, выбираем ФОРМАТ ЯЧЕЕК – ЗАЩИТА. Видим, что галочка на пункте ЗАЩИЩАЕМАЯ ЯЧЕЙКА проставлена. Но это еще не значит, что они уже защищены от изменений.
Зачем нам эта информация? Дело в том, что в Excel нет такой функции, которая позволяет защитить отдельную ячейку. Можно выбрать защиту листа, и тогда все ячейки на нем будут защищены от редактирования и другого вмешательства. С одной стороны это удобно, но что делать, если нам нужно защитить не все ячейки, а лишь некоторые?
Рассмотрим пример. Имеем простую таблицу с данными. Такую таблицу нам нужно разослать в филиалы, чтобы магазины заполнили столбец ПРОДАННОЕ КОЛИЧЕСТВО и отправили обратно. Во избежание внесения каких-то изменений в другие ячейки, защитим их.
Для начала освободим от защиты те ячейки, куда сотрудники филиалов будут вносить изменения. Выделяем D4:D11, правой кнопкой вызываем меню, выбираем ФОРМАТ ЯЧЕЕК и убираем галочку с пункта ЗАЩИЩАЕМАЯ ЯЧЕЙКА.
Теперь выбираем вкладку РЕЦЕНЗИРОВАНИЕ – ЗАЩИТИТЬ ЛИСТ. Появляется такое окно, где будут проставлены 2 галочки. Первую из них мы убираем, чтобы исключить любое вмешательство сотрудников филиалов, кроме заполнения столбца ПРОДАННОЕ КОЛИЧЕСТВО. Придумываем пароль и нажимаем ОК.
Внимание! Не забудьте свой пароль!
Теперь в диапазон D4:D11 посторонние лица смогут только вписать какое-то значение. Т.к. мы ограничили все остальные действия, никто не сможет даже изменить цвет фона. Все средства форматирования на верхней панели инструментов не активные. Т.е. они не работают.
Защита книги Excel от редактирования
Если на одном компьютере работает несколько человек, то целесообразно защищать свои документы от редактирования третьими лицами. Можно ставить защиту не только на отдельные листы, но и на всю книгу.
Когда книга будет защищена, посторонние смогут открывать документ, видеть написанные данные, но переименовать листы, вставить новый, поменять их расположение и т.п. Попробуем.
Прежнее форматирование сохраняем. Т.е. у нас по-прежнему можно вносить изменения только в столбец ПРОДАННОЕ КОЛИЧЕСТВО. Чтобы защитить книгу полностью, на вкладке РЕЦЕНЗИРОВАНИЕ выбираем ЗАЩИТИТЬ КНИГУ. Оставляем галочки напротив пункта СТРУКТУРУ и придумываем пароль.
Теперь, если мы попробуем переименовать лист, у нас это не получится. Все команды серого цвета: они не работают.
Снимается защита с листа и книги теми же кнопками. При снятии система будет требовать тот же пароль.
При работе с шаблоном отчета на большую часть ячеек установлена защита листа с паролем. Таким образом пользователь не нарушит формулы и целостность логической структуры отчета. Незащищенные только те ячейки, в которые необходимо вводить данные показателей. Но по ошибке автора шаблона небыли выделены цветом ячейки для ввода данных отчета при его проектировании. Пользователь изначально не знает, а только лишь догадывается, в какие ячейки можно вводить или изменять данные. Напишем свой макрос для поиска ячеек со снятой защитой.
Выделение ячеек со снятой защитой макросом
Таблица шаблона отчета выглядит так:
По умолчанию в Excel нет инструмента для автоматического выделения незащищенных ячеек. А проверять установлена ли защита для каждой ячейки или нет – это потребует много сил и времени. По умолчанию каждая ячейка защищена при включении защиты листа. А чтобы снять защиту с ячейки нужно выбрать: «ГЛАВНАЯ»-«Формат»-«Формат ячеек». После перейти на закладку «Защита» и снять галочку на против опции «Защищаемая ячейка». Затем если включить защиту листа, выбрав инструмент: «РИЦЕНЗИРОВАНИЕ»-«Изменение»-«Защитить лист», то в незащищенную ячейку все равно можно вводить и редактировать значения данных. Поэтому лучшим решением будет – написать свой VBA макрос, который сам найдет и выделит каждую ячейку со снятой защитой в таблице Excel.
Для написания своего кода макроса открываем редактор Visual Basic (ALT+F11):
Sub SeeLock()
Dim i As Long
Dim diapaz1 As Range
Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range( "A1" ), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Count
If diapaz1(i).Locked = False Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1(i)
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
diapaz2. Select
MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub
Затем в этом же редакторе создаем новый модуль выбрав инструмент: «Insert»-«Module». И в созданный модуль вводим код представленный ниже на листинге:
Теперь если мы хотим найти и выделить все незащищенные от редактирования ячейки в таблице отчета на листе с включенной защитой выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«SeeLock»-«Выполнить». После запуска макроса выделиться несмежный диапазон всех незащищенных ячеек в таблице отчета.
Основной принцип действия макроса основывается на тем, что с помощью цикла каждая ячейка проверяется установлено ли для ее свойства Locked (галочка – «защищаемая ячейка») значение False. Если да, значит ячейка незащищенная от редактирования, когда будет включена защита рабочего листа Excel. В таком случае ячейка со снятой защитой дополняет несмежный диапазон в переменной diapaz2.
-
Если нужно сделать так чтобы этот макрос наоборот выделял только защищенные ячейки, тогда достаточно лишь изменить значение False на True.
If diapaz1(i).Locked = True Then
Set diapaz1 = Selection
Примечание. При использовании второго полезного совета следует учитывать тот факт, что при таком коде нельзя выделять любые диапазоны листа перед запуском макроса. Иначе Excel будет очень медленно работать в процессе выполнения VBA кода.
Такой макрос будет полезен и для проектанта шаблонов Excel, чтобы быстро определять для каких ячеек уже снята защита, а для каких нужно еще снять, чтобы они были доступны для редактирования и ввода данных.
При работе с Excel достаточно часто приходится сталкиваться с защищенными от редактирования ячейками. Хорошо бы было их экспонировать на фоне ячеек доступных для редактирования и ввода данных, чтобы не тратить время на бессмысленные попытки. Благодаря условному форматированию эта задача решается быстро и в автоматическом режиме.
Как определить защищенные ячейки в Excel
Для примера возьмем таблицу, у которой защищены все значения кроме диапазона первой позиции B2:E2.
Но лист может содержать несколько незащищенных ячеек. Чтобы быстро найти и определить незащищенные ячейки доступные для редактирования в таблице, сначала определим – защищенные. Для этого делаем следующее:
- Создаем второй лист и на нем в ячейке A1 вводим такую формулу:
- Теперь выделяем диапазон A1:E5 на этом же (втором) листе размером сопоставим с исходной таблицей так чтобы активной ячейкой осталась А1 (с формулой). И жмем клавишу F2.
- Нажимаем комбинацию горячих клавиш CTRL+Enter и получаем результат:
Там, где у нас появились нули, там находятся незащищенные ячейки в исходной таблице. В данном примере это диапазон B2:E2, он доступен для редактирования и ввода данных.
Как автоматически выделить цветом защищенные ячейки
Внимание! Данный пример можно применить только в том случаи если лист еще не защищен, так как после активации защиты листа инструмент «Условное форматирование» – недоступен!
- Выделяем диапазон всех ячеек c числовыми данными в исходной таблице B2:E5, которые следует проверить.
- Выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
- В разделе данного окна «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматированных ячеек:».
- В поле ввода вводим формулу:
- Нажимаем на кнопку формат и переходим на вкладку «Заливка». В разделе «Цвет фона:» указываем – желтый. И жмем ОК на всех окнах.
Результат формулы автоматического выделения цветом защищенных ячеек:
Внимание! Перед использованием условного форматирования правильно выделяйте диапазон данных. Например, если Вы ошибочно выделили не диапазон таблицы с данными B2:E5, а всю таблицу A1:E5 тогда следует изменить формулу таким образом: =ЯЧЕЙКА("защита";A1)=1
Как определить и выделить цветом незащищенные ячейки
Если нужно наоборот выделить только те ячейки которые доступны для редактирования нужно в формуле изменить единицу на ноль: =ЯЧЕЙКА("защита";B2)=0.
При создании правила форматирования для ячеек таблицы мы использовали функцию ЯЧЕЙКА. В первом аргументе мы указываем нужный нам тип сведений о ячейке –"защита". Во втором аргументе мы указываем относительный адрес для проверки всех ячеек диапазона. Если ячейка защищаемая функция возвращает число 1 и тогда присваивается указанный нами формат.
Если вы когда-нибудь использовали защиту ячеек на листе (вкладка Рецензирование - Защитить лист или в старых версиях Excel - меню Сервис - Защита - Защитить лист), то, возможно, сталкивались с этой проблемой. Как известно, будет данная конкретная ячейка на листе защищена от изменений после включения защиты листа, или нет - определяется галочкой Защищаемая ячейка (Locked) в диалоговом окне Формат ячейки (Format Cells) на вкладке Защита (Protection) :
В случае применения защиты листа к большой и сложной таблице или экранной форме, где должно быть много областей ввода, не всегда понятно - у каких ячеек на листе эта галочка уже выключена, а у каких еще осталась включена? Опубликованные ниже макросы как раз и позволяют включить/выключить подсветку цветом для незащищенных ячеек на текущем листе, чтобы их было наглядно видно.
Для добавления этих макросов в текущую книгу:
- нажмите сочетание клавиш ALT+F11, чтобы открыть редактор макросов Visual Basic
- вставьте новый пустой модуль в книгу, используя команду меню Insert - Module
- скопируйте и вставьте туда код приведенных ниже макросов
Как легко догадаться, первый из этих двух макросов включает подсветку красным для незащищенных ячеек, а второй - выключает ее, восстанавливая исходный цвет заливки. Запустить эти макросы можно, нажав сочетание клавиш ALT+F8 или воспользовавшись кнопкой Макросы (Macros) на вкладке Разработчик (Developer) .
Работа макроса на примере экранной формы выглядит примерно так:
И не надо ломать голову и проверять - где ты снял защиту с ячеек, а где она осталась.
Если вы когда-нибудь использовали защиту ячеек на листе (вкладка Рецензирование - Защитить лист или в старых версиях Excel - меню Сервис - Защита - Защитить лист), то, возможно, сталкивались с этой проблемой. Как известно, будет данная конкретная ячейка на листе защищена от изменений после включения защиты листа, или нет - определяется галочкой Защищаемая ячейка (Locked) в диалоговом окне Формат ячейки (Format Cells) на вкладке Защита (Protection) :
В случае применения защиты листа к большой и сложной таблице или экранной форме, где должно быть много областей ввода, не всегда понятно - у каких ячеек на листе эта галочка уже выключена, а у каких еще осталась включена? Опубликованные ниже макросы как раз и позволяют включить/выключить подсветку цветом для незащищенных ячеек на текущем листе, чтобы их было наглядно видно.
Для добавления этих макросов в текущую книгу:
- нажмите сочетание клавиш ALT+F11, чтобы открыть редактор макросов Visual Basic
- вставьте новый пустой модуль в книгу, используя команду меню Insert - Module
- скопируйте и вставьте туда код приведенных ниже макросов
Как легко догадаться, первый из этих двух макросов включает подсветку красным для незащищенных ячеек, а второй - выключает ее, восстанавливая исходный цвет заливки. Запустить эти макросы можно, нажав сочетание клавиш ALT+F8 или воспользовавшись кнопкой Макросы (Macros) на вкладке Разработчик (Developer) .
Работа макроса на примере экранной формы выглядит примерно так:
И не надо ломать голову и проверять - где ты снял защиту с ячеек, а где она осталась.
Читайте также: