Excel как выделить защищенные ячейки

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

Как определить защищенные ячейки в Excel

Для примера возьмем таблицу, у которой защищены все значения кроме диапазона первой позиции B2:E2.

При попытке редактировать данные таблицы на защищенном листе отображается соответствующее сообщение:

Но лист может содержать несколько незащищенных ячеек. Чтобы быстро найти и определить незащищенные ячейки доступные для редактирования в таблице, сначала определим – защищенные. Для этого делаем следующее:

1. Создаем второй лист и на нем в ячейке A1 вводим такую формулу:
2. Теперь выделяем диапазон A1:E5 на этом же (втором) листе размером сопоставим с исходной таблицей так чтобы активной ячейкой осталась А1 (с формулой). И жмем клавишу F2.



3. Нажимаем комбинацию горячих клавиш CTRL+Enter и получаем результат:

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



Как автоматически выделить цветом защищенные ячейки

Внимание! Данный пример можно применить только в том случаи если лист еще не защищен, так как после активации защиты листа инструмент «Условное форматирование» – недоступен!

1. Выделяем диапазон всех ячеек c числовыми данными в исходной таблице B2:E5, которые следует проверить.
2. Выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».



3. В разделе данного окна «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматированных ячеек:».



4. В поле ввода вводим формулу:



5. Нажимаем на кнопку формат и переходим на вкладку «Заливка». В разделе «Цвет фона:» указываем – желтый. И жмем ОК на всех окнах.

Результат формулы автоматического выделения цветом защищенных ячеек:

Внимание! Перед использованием условного форматирования правильно выделяйте диапазон данных. Например, если Вы ошибочно выделили не диапазон таблицы с данными B2:E5, а всю таблицу A1:E5 тогда следует изменить формулу таким образом: =ЯЧЕЙКА(«защита»;A1)=1

Как определить и выделить цветом незащищенные ячейки

Если нужно наоборот выделить только те ячейки которые доступны для редактирования нужно в формуле изменить единицу на ноль: =ЯЧЕЙКА(«защита»;B2)=0.

При создании правила форматирования для ячеек таблицы мы использовали функцию ЯЧЕЙКА. В первом аргументе мы указываем нужный нам тип сведений о ячейке –»защита». Во втором аргументе мы указываем относительный адрес для проверки всех ячеек диапазона. Если ячейка защищаемая функция возвращает число 1 и тогда присваивается указанный нами формат.

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

Включаем защиту ячеек

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

Для включения защиты выполняем следующие действия:

1. Сначала нужно выделить все содержимое листа. Для этого щелкаем по прямоугольнику на пересечении координатных панелей. Также можно нажать комбинацию клавиш Ctrl+A (один раз, если выбрана ячейка за пределами заполненной таблицы, два раза – если выбрана ячейка внутри нее).
2. Правой кнопкой мыши щелкаем по любому месту выделенной области и в раскрывшемся списке выбираем “Формат ячеек”.
3. В открывшемся окне форматирования ячеек во вкладке “Защита” убираем галочку напротив опции “Защищаемая ячейка”, после чего жмем OK.
4. Теперь любым удобным способом (например, с помощью зажатой левой кнопки мыши) выделяем область ячеек, которые хотим защитить от изменений. В нашем случае – это столбец с формулами. После этого щелчком правой кнопкой мыши по выделенному диапазону вызываем контекстное меню и снова выбираем пункт “Формат ячеек”.
5. Перейдя во вкладку “Защита” ставим галочку напротив опции “Защищаемая ячейка” и жмем OK.
6. Теперь необходимо активировать защиту листа. После этого у нас будет возможность корректировать все ячейки листа, кроме тех, которые входят в выделенный диапазон. Для этого открываем меню “Файл”.
7. В правой части содержимого раздела “Сведения” нажимаем кнопку “Защитить книгу”. Откроется перечень команд, среди которых нужен вариант – “Защитить текущий лист”.
8. На экране отобразятся параметры защиты листа. Напротив опции “Защитить лист и содержимое защищаемых ячеек” обязательно должна быть установлена галочка. Остальные опции ниже выбираются согласно пожеланиям пользователя (в большинстве случаев, параметры остаются нетронутыми). Для защиты листа требуется ввести пароль в специально предназначенном для этого поле (нужен будет в дальнейшем для разблокировки), после чего можно щелкнуть OK.
9. В следующем небольшом окошке требуется повторить ранее введенный пароль и снова нажать кнопку OK. Это мера поможет обезопасить пользователя от собственных опечаток во время установки пароля.
10. Все готово. Теперь отредактировать содержимое ячеек, для которых мы включили защиту в параметрах форматирования, не получится. Остальные элементы листа можно менять на наше усмотрение.

Метод 2: применяем инструменты вкладки “Рецензирование”

Второй метод включения защиты ячеек предполагает использование инструментов вкладки “Рецензирование”. Вот как это делается:

1. Выполняем шаги 1-5, описанные в методе 1, т.е. снимаем защиту со всего листа и обратно устанавливаем только для выделенных ячеек.
2. В группе инструментов “Защита” вкладки “Рецензирование” жмем кнопку “Защитить лист”.
3. Появится уже знакомое окно с параметрами защиты листа. Дальше выполняем те же шаги, что и при реализации описанного выше метода.

Примечание: При сжатых размерах окна программы (по горизонтали) блок инструментов “Защита” представляет собой кнопку, нажатие которой раскроет список доступных команд.

Снимаем защиту

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

Для снятие блокировки необходимо ввести пароль:

1. Во вкладке “Рецензирование” в группе инструментов “Защита” жмем кнопку “Снять защиту с листа”.
2. Откроется небольшое окошко с одним полем, в котором следует ввести пароль, указанный при блокировке ячеек. Нажав кнопку OK мы снимем защиту.

Заключение

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

Подсветка незащищенных ячеек

Если вы когда-нибудь использовали защиту ячеек на листе (вкладка Рецензирование — Защитить лист или в старых версиях Excel — меню Сервис — Защита — Защитить лист), то, возможно, сталкивались с этой проблемой. Как известно, будет данная конкретная ячейка на листе защищена от изменений после включения защиты листа, или нет — определяется галочкой Защищаемая ячейка (Locked) в диалоговом окне Формат ячейки (Format Cells) на вкладке Защита (Protection):

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

Для добавления этих макросов в текущую книгу:

• нажмите сочетание клавиш ALT+F11, чтобы открыть редактор макросов Visual Basic
• вставьте новый пустой модуль в книгу, используя команду меню Insert — Module
• скопируйте и вставьте туда код приведенных ниже макросов

Public Fills
Sub Unprotected_Cells_Show()
    Application.ScreenUpdating = False
    ReDim Fills(1 To ActiveSheet.UsedRange.Rows.Count, 1 To ActiveSheet.UsedRange.Columns.Count)
    For Each cell In ActiveSheet.UsedRange
        If cell.Interior.ColorIndex = -4142 Then
            Fills(cell.Row, cell.Column) = 0
        Else
            Fills(cell.Row, cell.Column) = cell.Interior.Color
        End If
        If Not cell.Locked Then cell.Interior.ColorIndex = 3
    Next cell
    Application.ScreenUpdating = True
End Sub

Sub Unprotected_Cells_Hide()
    Application.ScreenUpdating = False
    For Each cell In ActiveSheet.UsedRange
        If Fills(cell.Row, cell.Column) = 0 Then
            cell.Interior.ColorIndex = -4142
        Else
            cell.Interior.Color = Fills(cell.Row, cell.Column)
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

Как легко догадаться, первый из этих двух макросов включает подсветку красным для незащищенных ячеек, а второй — выключает ее, восстанавливая исходный цвет заливки. Запустить эти макросы можно, нажав сочетание клавиш ALT+F8 или воспользовавшись кнопкой Макросы (Macros) на вкладке Разработчик (Developer).

Работа макроса на примере экранной формы выглядит примерно так:

И не надо ломать голову и проверять — где ты снял защиту с ячеек, а где она осталась.

Ссылки по теме

• Как защитить отдельные ячейки на листе, весь лист, зашифровать книгу Excel?
• Что такое макросы, как ими пользоваться?

Помогла ли Вам статья?

Защита листа

​Смотрите также​​ Защищаемая ячейка —​ цветом защищенных ячеек:​ таблицей так чтобы​ как устранить эту​ ее выделения также​ соответствующие флажки в​нажмите кнопку​ связанных ячеек на​Снять защиту листа​Просмотр скрытых сценариев, изменение​Примечание:​форматирование строк​Защищаемая ячейка​(либо нажмите клавиши​ определенных диапазонах на​Важно:​Примечание:​ выделить желтые столбцы​Внимание! Перед использованием условного​ активной ячейкой осталась​

​ проблему, см. в​ можно использовать клавиатуру.​ списке​Защитить лист​ листе​. Команду​ сценариев с установленным​ Пользователи не смогут сортировать​Использование любых команд форматирования​на вкладке​CTRL+1​ защищенном листе. Дополнительные​ ​ Мы стараемся как можно​ — установить флажок​ форматирования правильно выделяйте​ А1 (с формулой).​ публикации сообщества под​Чтобы выделить диапазон, выделите​Разрешить всем пользователям этого​или​Копирование и вставка данных​Снять защиту листа​ запретом на изменения​ диапазоны, содержащие заблокированные​ строк, включая изменение​

​Защита​​или​

• ​ сведения см. в​​Защита листа​​ оперативнее обеспечивать вас​ Защищаемая ячейка —​ диапазон данных. Например,​ И жмем клавишу​

• ​ названием Как предотвратить​ ячейку, а затем​ листа​Защитить книгу​ на защищенном листе​

• ​можно найти на​ и удаление этих​ ячейки на защищенном​ высоты строки или​в диалоговом окне​COMMAND1​

• ​ статье Блокировка и​не​ актуальными справочными материалами​ защитить лист​ если Вы ошибочно​ F2.​

• ​ одновременное выделение нескольких​ перетащите ее правый​.​. Введите пароль для​Видео: Защита книги или​ вкладке​

Выбор элементов листа для блокировки

​ сценариев. Пользователи могут​ листе, независимо от​ скрытие строк (вкладка​

• ​Формат ячеек​​на компьютере Mac),​ разблокировка определенных областей​является функцией безопасности.​ на вашем языке.​aidexin​ выделили не диапазон​Нажимаем комбинацию горячих клавиш​ ячеек в Excel?.​ нижний край. Для​

• ​Нажмите кнопку​​ листа или книги,​ листа паролем (Excel​Рецензирование​ изменять значения в​ настройки этого параметра.​Главная​. По умолчанию пользователям​ а затем откройте​

​ защищенного листа.​​ Она просто запрещает​ Эта страница переведена​: Спасибо большое, у​ таблицы с данными​ CTRL+Enter и получаем​При работе с Excel​ этого также можно​ОК​ а затем подтвердите​ 2013)​в группе​ изменяемых ячейках, если​использование автофильтра​, группа​ разрешено выделять защищенные​ вкладку​

Включение защиты листа

​Примечание:​ изменение заблокированных ячеек​ автоматически, поэтому ее​ меня все получилось.​ B2:E5, а всю​ результат:​ достаточно часто приходится​ использовать SHIFT+клавиши со​

Шаг 1. Разблокировка всех ячеек, которые необходимо изменять

1. ​.​ его в поле​Чтобы предотвратить случайное или​

2. ​Изменения​ ячейки не защищены,​

   ​Использование стрелок раскрывающегося списка​​Ячейки​ ячейки.​Защита​ Элементы ActiveX, элементы управления​ на листе.​

3. ​ текст может содержать​Но есть ли​ таблицу A1:E5 тогда​Там, где у нас​​ сталкиваться с защищенными​​ стрелками.​​Чтобы разблокировать ячейки, может​​Подтверждение пароля​​ преднамеренное изменение, перемещение​​.​ и добавлять новые​ для изменения фильтра​​, кнопка​​выделение незаблокированных ячеек​​и снимите флажок​​ форм, фигуры, диаграммы,​

   

Шаг 2. Защита листа

​Защита листа отличается от​ неточности и грамматические​ возможность как бы​ следует изменить формулу​ появились нули, там​ от редактирования ячейками.​Чтобы выделить несмежные ячейки​ потребоваться временно выключить​.​ или удаление важных​Если лист не​ сценарии.​ в диапазонах, если​Формат​Перемещение указателя на ячейки,​Защищаемая ячейка​

​ графические элементы SmartArt,​ защиты файла или​

1. ​ ошибки. Для нас​​ скопировать это защиту​​ таким образом: =ЯЧЕЙКА(«защита»;A1)=1​​ находятся незащищенные ячейки​​ Хорошо бы было​

   

2. ​ и диапазоны ячеек,​​ защиту. На вкладке​Примечание:​​ данных, вы можете​ защищен, на ленте​При желании можно ввести​ применяются автофильтры.​

   

   ​).​	​ для которых снят​
   ​.​	​ спарклайны, срезы, временные​ книги Excel паролем.​ важно, чтобы эта​​ на другие листы​​Если нужно наоборот выделить​​ в исходной таблице.​​ их экспонировать на​​ выберите их, удерживая​​Рецензирование​ Защита листа или книги​ заблокировать ячейки и​
   ​ выводится кнопка «Защитить​	​ пароль в поле​Примечание:​вставку столбцов​​ флажок​​Решите, какие действия пользователи​​ шкалы и некоторые​​ Дополнительные сведения см.​​ статья была вам​​ ( у меня​ только те ячейки​ В данном примере​ фоне ячеек доступных​ нажатой клавишу CTRL.​нажмите кнопку​ паролем необязательна. Если​
   ​ защитить их. Блокирование​	​ лист».​Пароль для отключения защиты​​ Пользователи не смогут применять​​Вставка столбцов.​​Защищаемая ячейка​​ должны выполнять на​ другие элементы блокируются​ ниже.​ полезна. Просим вас​ таких повторяющихся листов​ которые доступны для​
   ​ это диапазон B2:E2,​	​ для редактирования и​Выберите букву в верхней​Снять защиту листа​ вы не укажете​​ ячеек выполняется в​​Чтобы снять защиту листа,​​ листа​​ или удалять автофильтры​​вставку строк​​на вкладке​
   ​ листе (например, вставка​	​ сразу после добавления​Сведения о том, как​ уделить пару секунд​ 30). Каждый раз​​ редактирования нужно в​​ он доступен для​​ ввода данных, чтобы​​ части столбца, чтобы​​или​​ пароль, любой пользователь​
   ​ два этапа: назначение​	​ сделайте следующее:​
   ​и нажать кнопку​	​ на защищенном листе​
   ​Вставка строк.​	​Защита​ и удаление столбцов​
   ​ в таблицу. Однако​	​ заблокировать файл, чтобы​ ​ и сообщить, помогла​​ вручную защищать занимает​​ формуле изменить единицу​​ редактирования и ввода​​ не тратить время​​ выделить его целиком.​Снять защиту книги​ сможет отменить защиту​ ячейкам статуса​
   ​Выберите лист, защиту которого​	​ОК​ ​ независимо от настройки​​вставку гиперссылок​​в диалоговом окне​​ или строк, изменение​​ блокировка будет работать​​ другие пользователи не​ ли она вам,​ много время.​ на ноль: =ЯЧЕЙКА(«защита»;B2)=0.​
   ​ данных.​	​ на бессмысленные попытки.​ Можно также щелкнуть​​. Введите пароль.​​ листа и изменить​​Защищаемый объект​​ вы хотите снять.​ ​. В диалоговом окне​​ этого параметра.​Вставка новых гиперссылок (даже​Формат ячеек​ объектов, сортировка или​ только в том​
   ​ смогли открыть его,​	​ с помощью кнопок​Pelena​При создании правила форматирования​​ ​ Благодаря условному форматированию​​ любую ячейку в​Выделите ячейки, которые требуется​ данные в защищенных​и включение защиты​Выберите​
   ​Подтверждение пароля​	​использование отчетов сводной таблицы​ в незаблокированных ячейках).​. По умолчанию пользователям​ использование автофильтра). Кроме​ случае, если включена​
   ​ см. в статье​	​ внизу страницы. Для​ ​: Записать действия макрорекодером​ для ячеек таблицы​Внимание! Данный пример можно​ эта задача решается​ столбце и нажать​ разблокировать.​ ячейках. Если вы​ листа.​Файл​еще раз введите​Форматирование, изменение макета, обновление​удаление столбцов​ разрешено выделять незащищенные​ того, вы также​ ​ защита листа. Дополнительные​ Защита файла Excel.​ удобства также приводим​ и пробежаться по​ мы использовали функцию​ применить только в​ ​ быстро и в​
   ​ клавиши CTRL+ПРОБЕЛ.​	​Нажмите клавиши​ все же введете​Выделите ячейки, которые хотите​>​ пароль и нажмите​ данных либо иное​Удаление столбцов.​ ячейки, а также​ можете указать пароль,​ сведения о том,​

3. ​Чтобы предотвратить добавление, изменение,​ ссылку на оригинал​​ всем листам​ ЯЧЕЙКА. В первом​​ том случаи если​​ автоматическом режиме.​​Выберите номер строки, чтобы​​+1. После этого​​ пароль, запомните его​ заблокировать.​​Сведения​​ОК​

   ​ изменение отчетов сводной​​Примечание:​

   • ​ перемещаться между незащищенными​ чтобы заблокировать лист.​ как включить защиту,​ перемещение, копирование или​ (на английском языке).​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Макрос1()​ аргументе мы указываем​ лист еще не​Для примера возьмем таблицу,​ выделить ее целиком.​ снимите флажок​ или запишите и​В меню​>​

   • ​.​ таблицы, а также​​ Если​ ячейками на защищенном​ Пароль не позволит​

Как определить, защищен ли лист?

​ см. в следующем​​ скрытие и отображение​​Чтобы предотвратить умышленное или​Dim i&​​ нужный нам тип​​ защищен, так как​​ у которой защищены​​ Можно также щелкнуть​Защищаемый объект​​ сохраните в безопасном​​Формат​​Защита​​Важно:​

​ создание отчетов.​Удаление столбцов​ листе с помощью​ другим пользователям снять​

Отмена защиты листа Excel

​ разделе.​ листов в книге,​

1. ​ случайное изменение, перемещение​For i =​

2. ​ сведений о ячейке​​ после активации защиты​​ все значения кроме​​ любую ячейку в​​.​​ месте. В случае​​выберите пункт​​>​​ ​​изменение объектов​​защищена, а​​ клавиши TAB.​​ с листа защиту​​Защита листа выполняется в​

3. ​ см. статью Защита​ или удаление данных​ 1 To Sheets.Count​​ –»защита». Во втором​​ листа инструмент «Условное​​ диапазона первой позиции​​ строке и нажать​

См. также

​Если вам требуется заблокировать​ потери пароля вы​

​Ячейки​

​Снять защиту листа​

​Используйте надежные пароли, состоящие​Выполнять следующие действия:​

​Вставка столбцов​

​форматирование ячеек​

​ — его потребуется​ два этапа: сначала​ книги.​

​ на листе другими​With Sheets(i)​

​ аргументе мы указываем​ форматирование» – недоступен!​ B2:E2.​

support.office.com

Блокирование ячеек для защиты данных в Excel для Mac

​ клавиши SHIFT+ПРОБЕЛ.​ другие ячейки, на​ не сможете получить​или нажмите клавиши​или​ из букв в​Внесение изменений в графические​не защищена, пользователь​Изменение параметров в диалоговых​​ ввести для ее​​ нужно разблокировать ячейки,​Чтобы узнать, чем защита​

Блокирование ячеек

1. ​ пользователями, можно заблокировать​.Cells.Locked = False​

2. ​ относительный адрес для​​Выделяем диапазон всех ячеек​​При попытке редактировать данные​​Чтобы выделить несмежные строки​​ вкладке​​ доступ к защищенным​

3. ​+1.​​Рецензирование​​ верхнем и нижнем​ объекты, в том​​ может вставлять столбцы,​​ окнах​

   ​ отключения.​​ которые пользователи должны​ файла Excel отличается​​ ячейки на листе​​.Columns(«H:I»).Locked = True​

4. ​ проверки всех ячеек​ c числовыми данными​ таблицы на защищенном​​ или столбцы, выберите​Рецензирование​​ ячейкам листа.​​Откройте вкладку​

5. ​Изменения​​ регистре, цифр и​​ числе карты, встроенные​​ но их нельзя​​Формат ячеек​​Ниже приведены инструкции по​​ изменять, а затем​ от защиты книги​ Excel, а затем​.Protect​​ диапазона. Если ячейка​​ в исходной таблице​

   ​ листе отображается соответствующее​​ их номера, удерживая​нажмите кнопку​Если вы хотите запретить​Защита​>​ специальных знаков. В​ диаграммы, фигуры, надписи​ удалить.​или​ защите листа.​ защитить лист с​ или листа, прочтите​ защитить его паролем.​End With​ защищаемая функция возвращает​ B2:E5, которые следует​ сообщение:​

6. ​ нажатой клавишу CTRL.​Защитить лист​ выделение заблокированных ячеек,​​и убедитесь, что​​Снять защиту листа​

7. ​ ненадежных паролях используются​ и элементы управления,​удаление строк​Условное форматирование​​На вкладке​​ использованием пароля или​

8. ​ статью Защита и​​ Предположим, что у​​Next i​ число 1 и​ проверить.​Но лист может содержать​Чтобы выделить список или​или​ снимите флажок​ установлен флажок​​.​ не все эти​​ которые не были​

9. ​Удаление строк.​​. Если условное форматирование​​Рецензирование​

Разблокирование ячеек

1. ​ без него.​ безопасность в Excel.​ вас есть лист​​End Sub​​ тогда присваивается указанный​​Выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать​​ несколько незащищенных ячеек.​​ таблицу, щелкните любую​​Защитить книгу​

2. ​выделение заблокированных ячеек​Защищаемый объект​

3. ​Если лист защищен паролем,​​ символы. Пароль должен​ разблокированы перед установкой​​Примечание:​​ было применено до​

4. ​нажмите кнопку​В файле Excel щелкните​Вот какие элементы можно​​ отчета о работе​​_Boroda_​​ нами формат.​​ правило».​​ Чтобы быстро найти​​ содержащуюся в них​. Введите пароль для​.​.​

support.office.com

Выделение содержимого ячеек в Excel

​ введите его в​ состоять не менее​ защиты листа. Например,​

​ Если​​ установки защиты листа,​Защитить лист​ ярлычок листа, который​ заблокировать на незащищенном​

Выделение ячеек

1. ​ группы и вы​: А с паролем​aidexin​В разделе данного окна​ и определить незащищенные​

2. ​ ячейку и нажмите​ листа и подтвердите​Если вы хотите разрешить​Примечание:​ диалоговом окне​ чем из 8​ если на листе​

3. ​Удаление строк​ форматирование будет изменяться​.​ вы хотите защитить.​

Выделение строк и столбцов

1. ​ листе:​ хотите, чтобы ее​ так​: Добрый день друзья,​ «Выберите тип правила:»​ ячейки доступные для​ клавиши CTRL+A.​

2. ​ его в соответствующем​ выделение и заполнение​ По умолчанию всем ячейкам​Защита листа​ знаков, но лучше​ есть кнопка, запускающая​

3. ​защищена и​ при вводе значения,​В списке​Выделите ячейки, которые должны​

Выделение таблицы, списка или листа

1. ​Формулы​ участники могли добавлять​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub dsa()​ задача простая не​ выберите опцию «Использовать​

2. ​ редактирования в таблице,​Чтобы выделить весь лист,​ поле.​​ незаблокированных ячеек, установите​​ присвоен статус​и нажмите кнопку​

​ использовать парольную фразу​​ макрос, ее можно​Вставка строк​ удовлетворяющего определенному условию.​Разрешить всем пользователям этого​ изменять другие пользователи.​: Если вы не​ данные только в​For Each Sh​ могу никак найти.​ формулу для определения​ сначала определим –​

support.office.com

Как выделить защищенные и незащищенные ячейки в Excel

​ нажмите клавиши CTRL+A​В Excel можно выделять​ флажок​Защищаемый объект​OK​ длиной не менее​ нажать, чтобы запустить​не защищена, пользователь​форматирование столбцов​ листа​Совет:​ хотите, чтобы другие​ определенные ячейки и​ In ActiveWorkbook.Worksheets​ Как можно защитить​

Как определить защищенные ячейки в Excel

​ форматированных ячеек:».​ защищенные. Для этого​ или кнопку​ содержимое ячеек, строк​выделение незаблокированных ячеек​

​.​.​ 14 знаков.​ макрос, но нельзя​

​ может вставлять строки,​Использование любых команд форматирования​установите флажки для​ Чтобы выделить несколько несмежных​ пользователи видели формул,​ не могли изменять​With Sh​ только выделенные желтым​В поле ввода вводим​

1. ​ делаем следующее:​Выделить все​ или столбцов.​.​
2. ​Если вы хотите разблокировать​Защита и безопасность в​Очень важно запомнить свой​ удалить.​ но их нельзя​ столбцов, включая изменение​ элементов, изменение которых​ ячеек, нажмите и​ их можно скрыть​
3. ​ другие элементы. С​.Cells.Locked = False​ цветом столбцы. чтобы​

​ формулу:​Создаем второй лист и​в левом верхнем​Примечание:​Прежде чем нажимать кнопку​ определенные ячейки, выделите​ Excel​ пароль.​Внесение каких-либо изменений (например,​

​ удалить.​

Как автоматически выделить цветом защищенные ячейки

​ ширины столбца или​ должно быть доступно​ удерживайте клавишу CTRL​ от невидимым в​ помощью защиты листа​.Columns(«H:I»).Locked = True​ не могли изменить​Нажимаем на кнопку формат​

1. ​ на нем в​ углу.​ Если лист защищен, возможность​ОК​ их. Нажмите клавиши​
2. ​Защита файла Excel​Если вы забудете пароль,​
3. ​ форматирование) во встроенную​сортировку​ скрытие столбцов (вкладка​ пользователям.​ и щелкните их​
4. ​ ячейках или в​ можно сделать доступными​
5. ​.Protect Password:=»qq77″​Спасибо.​ и переходим на​ ячейке A1 вводим​Примечание:​ выделения ячеек и​, вы можете разрешить​

​+1. После этого​Защита книги​

​ корпорация Майкрософт не​ диаграмму. Диаграмма по-прежнему​Использование команд для сортировки​Главная​Параметр​ левой кнопкой мыши.​ строку формул. Дополнительные​ для редактирования только​End With​Pelena​

Как определить и выделить цветом незащищенные ячейки

​ вкладку «Заливка». В​ такую формулу:​ В некоторых случаях выделение​ их содержимого может​ пользователям выполнять другие​ снимите флажок​

​Блокировка и разблокировка определенных​ сможет его восстановить.​ будет обновляться при​ данных (вкладка​, группа​Возможность​Щелкните правой кнопкой мыши​ сведения просмотреть Отображение​ определенные части листа,​Next Sh​: Здравствуйте.​ разделе «Цвет фона:»​Теперь выделяем диапазон A1:E5​ одной ячейки может​ быть недоступна.​ действия с заблокированными​

exceltable.com

Защитить выделенные ячейки (Формулы/Formulas)

​Защищаемый объект​​ областей защищенного листа​Если лист защищен, команда​ изменениях ее исходных​Данные​Ячейки​выделение заблокированных ячеек​ в любом месте​
​ или скрытие формулы.​

​ после чего пользователи​​End Sub​
​Выделить весь лист​ указываем – желтый.​ на этом же​ привести к выбору​Чтобы выделить ячейку, щелкните​ ячейками, например выделение​.​Блокировка ячеек​Защитить лист​

​ данных.​​, группа​, кнопка​
​Перемещение указателя на ячейки,​ листа и выберите​Диапазоны​ не смогут изменять​aidexin​ — Формат ячейки​ И жмем ОК​ (втором) листе размером​ нескольких смежных ячеек.​

​ ее. Для перехода​​ и форматирование. В​На вкладке​Отображение и скрытие формул​
​на ленте изменяется​
​Добавление или редактирование примечаний.​
​Сортировка и фильтр​Формат​
​ для которых установлен​
​ команду​
​: вы можете позволить​
​ данные в других​
​: Все сделал, спасибо​
​ -- на вкладке​
​ на всех окнах.​

​ сопоставим с исходной​​ Советы о том,​ к ячейке и​
​ этом случае установите​
​Рецензирование​Защита элементов управления и​
​ на​
​изменение сценариев​
​).​
​).​
​ флажок​
​Формат ячеек​
​ пользователям работать в​

​ его областях.​​ большое за помощь…​ Защита снять флажок​

excelworld.ru

​Результат формулы автоматического выделения​

В этой статье я расскажу как защитить ячейки в Excel от изменений и редактирования. Защита ячеек может заключаться в том, что пользователи, открывшие ваш файл не смогут редактировать значения ячеек, а также видеть формулы.

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

Как защитить все ячейки в файле Excel

Если вы хотите защитить абсолютно все ячейки в вашем Excel файле от редактирования и изменений, проделайте следующие действия:

• Перейдите во вкладку «Рецензирование» на панели инструментов => в подразделе «Защита» кликните по иконке «Защитить лист«:

• Во всплывающем окне, убедитесь, что установлена галочка напротив пункта «Защитить лист и содержимое защищаемых ячеек«:

• Введите пароль в поле «Пароль для отключения защиты листа«, если хотите, чтобы снять защиту могли только те пользователи, которым вы сообщили пароль:

• Выберите из списка и отметьте «галочкой» те действия с ячейками листа, которые будут разрешены всем пользователям:

• Нажмите «ОК«

Если вы установили пароль, то система попросит вас повторно ввести его.

Теперь, всем пользователям, которые попытаются внести изменения или отредактировать значения ячеек будет отображаться такое сообщение:

Для того чтобы снять установленную защиту, перейдите во вкладку «Рецензирование«, и в разделе «Защита» кликните по иконке «Снять защиту с листа«. После этого система попросит вас ввести пароль для снятия защиты.

Как защитить отдельные ячейки в Excel от изменений

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

Для примера рассмотрим простую таблицу с данными о доходах и расходах. Наша задача защитить от изменений ячейки в диапазоне B1:B3.

Для того чтобы заблокировать отдельные ячейки проделаем следующие действия:

• Выделим абсолютно все ячейки на листе Excel (с помощью сочетания клавиш CTRL + A):

• Перейдем на вкладку «Главная» на панели инструментов => в разделе «Выравнивание» кликнем по иконке в правом нижнем углу:

• Во всплывающем окне перейдем на вкладку «Защита» и уберем галочку с пункта «Защищаемая ячейка«:

• Кликнем «ОК«

Таким образом, мы отключили настройку Excel для ячеек всего листа, при котором все ячейки готовы к защите и блокировке.

• Теперь, выделим ячейки, которые мы хотим защитить от редактирования (в нашем случае это диапазон ячеек B1:B3);
• Снова перейдем на вкладку «Главная» на панели инструментов и в подразделе «Выравнивание» кликнем по иконке в правом нижнем углу, как мы делали это раннее.
• Во всплывающем окне, на вкладке «Защита» поставим галочку в пункте «Защищаемая ячейка«:

• Перейдем во вкладку «Рецензирование» на панели инструментов и кликнем по иконке «Защитить лист«:

• Во всплывающем окне, убедимся, что установлена галочка напротив пункта «Защитить лист и содержимое защищаемых ячеек«:

• Введем пароль в поле «Пароль для отключения защиты листа«, чтобы снять защиту могли только те пользователи, которым мы сообщили пароль:

• Выберем из списка и отметим «галочкой» те действия с ячейками листа, которые будут разрешены всем пользователям:

• Нажмем «ОК«

После этого система попросит нас повторно ввести пароль. Теперь, при попытке редактирования ячеек B1:B3 из нашего примера, будет отображаться сообщение:

Как скрыть формулу заблокированной ячейки

Если ячейки, которые вы защитили от редактирования содержат формулы, вы также можете их скрыть.

Для этого проделайте следующие шаги:

• Выделите ячейки, которые вы хотите защитить и скрыть формулы;
• Перейдем на вкладку «Главная» на панели инструментов и в подразделе «Выравнивание» кликнем по иконке в правом нижнем углу, как мы делали это раннее;
• Во всплывающем окне, на вкладке «Защита» поставим галочки в пунктах «Защищаемая ячейка» и «Скрыть формулы«:

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

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

Защита ячеек производится в два этапа, для того чтобы защитить ячейки от изменений, необходимо:

Этап первый — выделить ячейки и сделать их защищаемыми;

Для этого достаточно после выделения ячеек нажать сочетание клавиш Ctrl+1 либо кликнуть правой кнопкой мыши в области выделенных ячеек, выбрать из контекстного меню пункт «Формат ячеек…», зайти на вкладку «Защита» и установить флажки в полях «Защищаемая ячейка» и/или «Скрыть формулы».

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

Этап второй — установить защиту листа.

Защите листа в различных версиях Excel посвящена отдельная публикация, здесь же следует напомнить, что установить защиту листа в Microsoft Excel 2003 можно в меню Сервис/Защита/Защитить лист, а в Excel 2007/2010/2013 лист защищается на вкладке «Рецензирование», в группе кнопок «Изменения» нажатием кнопки «Защитить лист». При необходимости можно отметить галочками разрешения для пользователей и ввести пароль.

Существует возможность быстрой защиты всех листов сразу. Защита ячейки/ячеек вступает в силу сразу после выполнения второго этапа.

Как защитить все ячейки листа? Как снять защиту со всех ячеек листа?

Для установки/снятия защиты сразу всех ячеек рабочего листа необходимо выделить все ячейки, кликнув на так называемую нулевую ячейку либо воспользоваться сочетанием горячих клавиш Ctrl+A. Нулевая ячейка — это прямоугольная область, которая находится на пересечении первой строки с первым столбцом (отмечена красной рамкой на изображении ниже).

После того как все ячейки выделены, необходимо вызвать диалоговое окно «Формат ячеек» любым удобным способом (например горячими клавишами Ctrl+1), перейти на вкладку «Защита» и установить (либо снять) флажки в полях «Защищаемая ячейка» и «Скрыть формулы». Опция «Скрыть формулы» позволяет скрывать не только формулы, скрывается любая информация, отображаемая в строке формул.

Выборочная защита ячеек в Excel

С помощью защиты ячеек, табличное пространство можно четко разграничить на области, которые изменять можно и которые изменять нельзя, сделать это достаточно просто, как мы уже убедились. Неудобство возникает если требуется выборочная защита ячеек, когда установить защиту необходимо например, только на ячейки с примечаниями или на ячейки с формулами. В Excel 2007 и выше это неудобство решается при помощи инструмента под названием «Выделение группы ячеек». Чтобы им воспользоваться, достаточно на вкладке «Главная», в группе кнопок «Редактирование» в меню кнопки «Найти и выделить» выбрать пункт «Выделение группы ячеек…» после чего выбрать необходимые опции и нажать кнопку «ОК» (подробнее о выделении группы ячеек). После того, как все нужные ячейки выделены, остается лишь сделать ячейки защищаемыми, после чего установить защиту на лист.

Установка и снятие защиты ячеек при помощи надстройки

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

Использование надстройки позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо с панели инструментов Excel;

2. выбирать по своему усмотрению один из четырех режимов обработки данных;

3. на выбор пользователя устанавливать либо снимать свойство «Защищаемая ячейка» и «Скрыть формулы» по семи условиям (на пустые ячейки, на ячейки с числовыми и текстовыми значениями, с формулами, с примечаниями, с заливками и рамками).

Видео по установке/снятию защиты ячеек

макрос (надстройка) для выборочной установки и снятия свойства «защищаемая ячейка»

Внимание! Описанная надстройка только изменяет формат ячеек, делая их защищаемыми, либо не защищаемыми. Для того чтобы защита ячеек вступила в силу, необходимо установить защиту листа!

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

Видео по выборочному созданию защищаемых ячеек