Разрешение изменения диапазонов excel
Обновлено: 06.07.2024
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»
=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)
Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.
При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:
Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.
Способ 2. Динамический именованный диапазон
Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:
Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.
Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.
Ищем последнюю ячейку с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.
Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!
Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:
Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.
Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:
Формируем ссылку с помощью ИНДЕКС
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.
Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.
И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:
=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))
Создаем именованный диапазон
Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
Работая с контрагентами, иногда бывает удобнее высылать таблицу в Excel для заполнения заказа. Чтобы избежать изменения контрагентом в вашей таблице Excel формул и значений некоторых полей, вы можете защитить документ Excel паролем, но это может быть не всегда удобно, особенно когда вы хотите, чтобы Ваш контрагент мог вносить информацию в ваш документ Excel и высылать его Вам обратно.
В этих случаях наиболее правильным решением будет защитить от изменения определенный лист в документе, разрешив изменение некоторого диапазона. Рассмотрим на конкретном примере:
Допустим у нас имеется спецификация на атрибуты для организации детского праздника (Рис.1).
Мы хотим, чтобы наш контрагент имел возможность менять значения в диапазонах, выделенных желтым, но не имел возможности изменять наши цены, название продукции и т.д. Для этого мы переходим в Excel 2007 на вкладку «Рецензирование», и выбираем «Разрешить изменение диапазонов» (Рис. 2).
В открывшемся окне указываем диапазон, в который разрешено вносить изменения (Рис. 3).
После этого ставим защиту на лист Excel или на всю книгу (Рис. 4), указывая, какие действия можно выполнять с листом без пароля:
Все, Ваш контрагент сможет вносить изменения только в указанный Вам диапазон, а вы сможете менять полностью Ваш лист Excel, вводя пароль на доступ к таблице.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Сводка
В Microsoft Excel 2002 г. и в более поздних версиях Excel теперь можно использовать пароли для защиты определенных диапазонов в таблицах. Это изменение от предыдущих версий Excel, в котором один пароль применяется ко всему электронному таблицу, которая может иметь несколько защищенных диапазонов. Кроме того, если вы Windows 2000, вы можете применить пароли группового уровня и пароли на уровне пользователей к различным диапазонам.
Функции в Microsoft Excel, связанные с сокрытием данных и защитой электронных таблиц и книг с паролями, не предназначены для обеспечения безопасности данных или защиты конфиденциальной информации в Excel. Эти функции можно использовать для более четкого получения сведений, скрывая данные или формулы, которые могут запутать некоторых пользователей. Эти функции также помогают предотвратить случайные изменения данных другими пользователями.
Excel не шифрует данные, скрытые или заблокированные в книге. С достаточным временем пользователи могут получать и изменять все данные в книге, если у них есть к ней доступ. Чтобы предотвратить изменение данных и защитить конфиденциальную информацию, ограничите доступ к любым файлам Excel которые содержат такую информацию, храня их в местах, доступных только уполномоченным пользователям.
В этой статье описывается, как обеспечить правильное функционирование определенных сценариев совместной работы в средах совместной работы, не включавших пользователей, которые имеют вредоносные намерения. Невозможно включить мощное шифрование для файла с помощью защиты паролей. Чтобы защитить документ или файл от пользователя, у которого есть вредоносные намерения, можно ограничить разрешение с помощью управления правами на информацию (IRM).
Дополнительные сведения
Применение различных паролей
Чтобы применить разные пароли к двум диапазонам в таблице, выполните следующие действия:
Начните Excel, а затем откройте пустую книгу.
В меню Tools указать на защиту, а затем нажмите кнопку Разрешить пользователям изменять диапазоны.
В Microsoft Office Excel 2007 г. щелкните Разрешить пользователям изменять диапазоны в группе Изменений на вкладке Обзор.
В диалоговом окне Разрешить пользователям изменять диапазоны нажмите кнопку New.
В диалоговом окне New Range щелкните кнопку Диалог обрушения. Выберите диапазон B2:B6, а затем нажмите кнопку Диалог обрушения снова.
В поле Пароль Диапазона введите rangeone, нажмите кнопку ОК, а затем введите его снова в диалоговом окне Подтвердить пароль, а затем нажмите кнопку ОК.
Повторите шаги с 3 по 5, выбрав диапазон D2:D6 и набрав rangetwoas пароль для этого диапазона.
В диалоговом окне Разрешить пользователям изменять диапазоны нажмите кнопку Защита листа. В поле Пароль для unprotect sheet введите ranger и нажмите кнопку ОК. При запросе перепечатайте пароль и нажмите кнопку ОК.
Выберите ячейку B3 и запустите введите Dataone.
При введите D появится диалоговое окно Диапазон разблокировки.
Введите rangeone в введите пароль, чтобы изменить это поле ячейки, а затем нажмите кнопку ОК.
Теперь можно вводить данные в ячейке B3 и в любой другой ячейке в диапазоне B2:B6, но нельзя вводить данные ни в одной из ячеек D2:D6, не предоставив сначала правильный пароль для этого диапазона.
Диапазон, который вы защищаете с помощью пароля, не должен быть сделан из соседних клеток. Если вы хотите, чтобы диапазоны B2:B6 и D2:D6 делили пароль, вы можете выбрать B2:B6, как описано на шаге 4 ранее в этой статье, введите запятую в диалоговом окне New Range, а затем выберите диапазон D2:D6, прежде чем назначить пароль.
При применении различных паролей к отдельным диапазонам таким образом диапазон, который был разблокирован, остается разблокированным до закрытия книги. Если разблокировать другой диапазон, первый диапазон не будет повторно разблокирован. Кроме того, при сохранения книги не будет повторного блокировки диапазона.
Можно использовать существующие имена диапазонов для идентификации ячеек, которые должны быть защищены паролями, но если вы это сделаете, Excel преобразовывать все относительные ссылки в существующих определениях имен в абсолютные ссылки. Так как это может не дать вам предполагаемых результатов, для выбора ячеек, как описано выше в этой статье, лучше использовать кнопку Диалог обрушения.
Применение паролей группового уровня и паролей на уровне пользователей
Если используется Windows 2000 (но не другие версии Windows), можно назначить различные разрешения различным отдельным пользователям или группам пользователей. При этом разрешенные пользователи могут изменять защищенные диапазоны без необходимости вводить пароли, а другие пользователи могут изменять диапазоны до тех пор, пока они могут предоставить правильный пароль.
Чтобы применить защиту группового уровня к таблице, выполните следующие действия:
Запустите Excel, а затем откройте пустой лист.
В меню Tools указать на защиту, а затем нажмите кнопку Разрешить пользователям изменять диапазоны.
Если вы работаете Excel 2007 г., нажмите кнопку Разрешить пользователям изменять диапазоны в группе Изменений в меню Обзор.
В диалоговом окне Разрешить пользователям изменять диапазоны нажмите кнопку New.
В диалоговом окне New Range щелкните Диалог обрушения, выберите диапазон B2:B6, а затем снова нажмите диалоговое окно Collapse.
В поле пароль Range введите rangeone и дважды нажмите кнопку ОК. При запросе перенапечатыйте пароль.
Повторите шаги с 3 по 5, выбрав диапазон D2:D6 и введите rangetwo в качестве пароля для этого диапазона.
В диалоговом окне Разрешить пользователям изменять диапазоны щелкните Разрешения и нажмите кнопку Добавить в диалоговом окне Разрешения для Range2.
В диалоговом окне Выбор пользователей или групп введите Все, а затем нажмите кнопку ОК.
Щелкните ОК в диалоговом окне Разрешения для Range2.
В диалоговом окне Разрешить пользователям изменять диапазоны нажмите кнопку Защитить лист, введите ranger в поле Пароль, чтобы отклонить поле листа, а затем дважды нажмите кнопку ОК. При запросе перенапечатыйте пароль.
Выберите ячейку B3 и запустите введите Dataone. Пароль по-прежнему требуется. Щелкните Отмена в диалоговом окне Диапазон разблокировки.
Выберите ячейку D3 и введите Datatwo.
Пароль не требуется.
Необходимо использовать Windows 2000 для назначения разрешений группам или отдельным лицам, как описано ранее в этой статье, но после этого эти разрешения распознаются при редактировании таблиц на компьютерах с использованием Microsoft Windows NT. Windows NT не позволяет назначать или изменять разрешения.
Изменение паролей
Чтобы изменить пароль для диапазона, выполните следующие действия:
Запустите Excel, а затем откройте книгу.
В меню Tools указать на защиту и нажмите кнопку Unprotect Sheet.
В Excel 2007 г. щелкните Unprotect Sheet в группе Изменения на вкладке Обзор.
Если предложено ввести пароль таблицы, а затем нажмите кнопку ОК.
В меню Tools указать на защиту, а затем нажмите кнопку Разрешить пользователям изменять диапазоны.
В Excel 2007 г. щелкните Разрешить пользователям изменять диапазоны в группе Изменений на вкладке Обзор.
Щелкните диапазон в списке и нажмите кнопку Изменить.
Щелкните пароль.
Введите новый пароль в поле Новый пароль, а затем перепечатайте новый пароль в поле Подтвердить новый пароль.
Щелкните ОК, а затем щелкните ОК.
Чтобы изменить пароль для другого диапазона, повторите действия 3-6. В противном случае нажмите кнопку Защита листа.
Введите пароль листа в поле Пароль для unprotect sheet box.
Обратите внимание на эти аспекты применения паролей и разрешений группового уровня к определенным диапазонам:
Excel 2003 работает только в Microsoft Windows XP и в Microsoft Windows 2000.
Когда в Excel 2002 г. книга с защищенными диапазонами открывается на компьютере на Windows XP, на компьютере на Windows 2000 или на компьютере на Windows NT microsoft Windows NT, диапазон таблиц и защита групп будут такие же, как и в Excel 2003 г.
Когда книга с защищенными диапазонами открывается в Excel 2002 г. на компьютере microsoft Windows Millennium Edition или на компьютере microsoft Windows 98 на основе microsoft, диапазоны с разрешениями на уровне пользователей и группового уровня требуют пароля диапазона.
Дополнительные сведения
Дополнительные сведения о Microsoft Office, которые помогают в совместной работе, см. в Office функций, предназначенных для обеспечения совместной работы и не предназначенных для повышения безопасности.
По умолчанию при защите всех ячеек все ячейки заблокированы, поэтому ни одна из них не может быть редактируемой. Чтобы включить редактирование некоторых ячеек, при этом другие ячейки можно разблокировать. Перед защитой листа можно заблокировать только определенные ячейки и диапазоны и при желании позволить определенным пользователям изменять только определенные диапазоны защищенного листа.
Блокировка только определенных ячеек и диапазонов ячеек на защищенном листе
Если лист защищен, сделайте следующее:
На вкладке Рецензировка нажмите кнопку Отостановка листа (в группе Изменения).
Если будет предложено, введите пароль, чтобы отоблести защиты.
Выделите лист целиком, нажав кнопку Выделить все.
На вкладке Главная щелкните всплывающее кнопку запуска Формат шрифта ячейки. Вы также можете нажать клавиши CTRL+SHIFT+F или CTRL+1.
Во всплываемом окне Формат ячеек на вкладке Защита отоберем поле Блокировка и нажмите кнопку ОК.
При защите листа все ячейки будут разблокированы. Теперь вы можете выбрать конкретные ячейки, которые нужно защитить.
Выделите на листе только те ячейки, которые необходимо заблокировать.
Снова отключим всплывающее окно Формат ячеек (CTRL+SHIFT+F).
В этот раз на вкладке Защита выберите поле Заблокировано и нажмите кнопку ОК.
На вкладке Рецензирование нажмите кнопку Защитить лист.
В списке Разрешить всем пользователям этого таблицы выберите элементы, которые пользователи должны иметь возможность изменять.
Дополнительные сведения об элементах листа
Снятый флажок
Запрещаемые действия
выделение заблокированных ячеек
Перемещение указателя на ячейки, для которых установлен флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять защищенные ячейки.
выделение незаблокированных ячеек
Перемещение указателя на ячейки, для которых снят флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять незаблокированные ячейки, а также перемещаться между незаблокированными ячейками на защищенном листе с помощью клавиши TAB.
формат ячеек
Изменение параметров в диалоговых окнах Формат ячеек или Условное форматирование. Если условное форматирование было применено до установки защиты листа, форматирование будет изменяться при вводе значения, удовлетворяющего определенному условию.
форматирование столбцов
Использование любых команд форматирования столбцов, включая изменение ширины столбца или скрытие столбцов (вкладка Главная, группа Ячейки, кнопка Формат).
форматирование строк
Использование любых команд форматирования строк, включая изменение высоты строки или скрытие строк (вкладка Главная, группа Ячейки, кнопка Формат).
вставку столбцов
вставку строк
вставку гиперссылок
Вставка новых гиперссылок (даже в незаблокированных ячейках).
удаление столбцов
Если команда удаление столбцов защищена, а команда вставку столбцов не защищена, пользователь не сможет удалять столбцы, которые он вставит.
удаление строк
Если команда удаление строк защищена, а команда вставку строк не защищена, пользователь не сможет удалять строки, которые он вставит.
Использование команд для сортировки данных (вкладка Данные, группа Сортировка и фильтр).
Пользователи не смогут сортировать диапазоны, содержащие заблокированные ячейки на защищенном листе, независимо от настройки этого параметра.
использование автофильтра
Использование стрелок раскрывающегося списка для изменения фильтра в диапазонах, если применяются автофильтры.
Пользователи не смогут применить или удалить автофильтры на защищенном листе независимо от настройки этого параметра.
использование отчетов сводной таблицы
Форматирование, изменение макета, обновление или изменение отчетов сводной таблицы каким-либо иным образом, а также создание новых отчетов.
изменение объектов
Выполнять следующие действия:
Внесение изменений в графические объекты, в том числе карты, встроенные диаграммы, фигуры, текстовые поля и элементы управления, которые не были разблокированы перед установкой защиты листа. Например, если на листе есть кнопка, запускающая макрос, ее можно нажать, чтобы запустить макрос, но нельзя удалить.
Внесение каких-либо изменений (например, форматирование) во встроенную диаграмму. Диаграмма по-прежнему будет обновляться при изменениях ее исходных данных.
Добавление или изменение примечаний.
изменение сценариев
Просмотр скрытых сценариев, изменение сценариев с установленным запретом на изменения и удаление этих сценариев. Пользователи могут изменять значения в изменяемых ячейках, если ячейки не защищены, и добавлять новые сценарии.
Элементы листа диаграммы
Запрещаемые действия
Изменение элементов, являющихся частью диаграммы, таких как ряды данных, оси и легенды. При этом в диаграммах будут отображаться изменения, вносимые в исходные данные.
Изменение графических объектов, включая фигуры, текстовые поля и элементы управления (если объекты не были разблокированы до включения защиты листа диаграммы).
В поле Пароль для отключения защиты листа введите пароль для листа, нажмите кнопку ОК, а затем повторно введите пароль для подтверждения.
Пароль необязателен. Если не задать пароль, любой пользователь сможет снять защиту с листа и изменить защищенные элементы.
Выберите легко запоминающийся пароль, потому что в случае утраты пароля вы больше не сможете получить доступ к защищенным элементам листа.
Разблокировка диапазонов ячеек на защищенном листе для их изменения пользователями
Чтобы предоставить определенным пользователям разрешение изменять диапазоны на защищенном листе, на компьютере должна быть установлена операционная система Microsoft Windows XP или более поздней версии, а сам компьютер должен находиться в домене. Вместо использования разрешений, для которых требуется домен, можно также задать пароль для диапазона.
Выберите листы, которые нужно защитить.
На вкладке Рецензирование в группе Изменения нажмите кнопку Разрешить изменение диапазонов.
Эта команда доступна, только если лист не защищен.
Выполните одно из следующих действий:
Чтобы добавить новый редактируемый диапазон, нажмите кнопку Создать.
Чтобы изменить существующий редактируемый диапазон, выберите поле Диапазоны защищенного листа, разблокируемые паролем, затем нажмите кнопку Изменить.
Чтобы удалить редактируемый диапазон, выберите поле Диапазоны защищенного листа, разблокируемые паролем, затем нажмите кнопку Удалить.
В поле Название введите имя диапазона, который необходимо разблокировать.
В поле Содержит ячейки введите знак равенства (=), а затем ссылку на диапазон, который необходимо разблокировать.
Также можно нажать кнопку Свернуть диалоговое окно, выбрать на листе диапазон, а затем снова нажать кнопку Свернуть диалоговое окно, чтобы вернуться к диалоговому окну.
Для управления доступом с помощью пароля в поле Пароль диапазона введите пароль для доступа к диапазону.
При использовании разрешений на доступ задавать пароль необязательно. Использование пароля позволяет просматривать учетные данные всех полномочных пользователей, изменяющих диапазон.
Для установки разрешений на доступ выберите пункт Разрешения и нажмите кнопку Добавить.
В поле Введите имена объектов для выбора (примеры) введите имена пользователей, которым нужно разрешить изменять диапазоны.
Чтобы посмотреть, как должны вводиться имена пользователей, щелкните примеры. Чтобы проверить правильность имен, нажмите кнопку Проверить имена.
Чтобы указать тип разрешений для выбранного пользователя, в поле Разрешения установите или снимите флажок Разрешить или Запретить, а затем нажмите кнопку Применить.
При необходимости введите заданный пароль.
В диалоговом окне Разрешить изменение диапазонов нажмите кнопку Защитить лист.
В списке Разрешить всем пользователям этого листа выберите элементы, которые должны изменять пользователи.
Дополнительные сведения об элементах листа
Снятый флажок
Запрещаемые действия
выделение заблокированных ячеек
Перемещение указателя на ячейки, для которых установлен флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять защищенные ячейки.
выделение незаблокированных ячеек
Перемещение указателя на ячейки, для которых снят флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять незаблокированные ячейки, а также перемещаться между незаблокированными ячейками на защищенном листе с помощью клавиши TAB.
формат ячеек
Изменение параметров в диалоговых окнах Формат ячеек или Условное форматирование. Если условное форматирование было применено до установки защиты листа, форматирование будет изменяться при вводе значения, удовлетворяющего определенному условию.
форматирование столбцов
Использование любых команд форматирования столбцов, включая изменение ширины столбца или скрытие столбцов (вкладка Главная, группа Ячейки, кнопка Формат).
форматирование строк
Использование любых команд форматирования строк, включая изменение высоты строки или скрытие строк (вкладка Главная, группа Ячейки, кнопка Формат).
вставку столбцов
вставку строк
вставку гиперссылок
Вставка новых гиперссылок (даже в незаблокированных ячейках).
удаление столбцов
Если команда удаление столбцов защищена, а команда вставку столбцов не защищена, пользователь не сможет удалять столбцы, которые он вставит.
удаление строк
Если команда удаление строк защищена, а команда вставку строк не защищена, пользователь не сможет удалять строки, которые он вставит.
Использование команд для сортировки данных (вкладка Данные, группа Сортировка и фильтр).
Пользователи не смогут сортировать диапазоны, содержащие заблокированные ячейки на защищенном листе, независимо от настройки этого параметра.
использование автофильтра
Использование стрелок раскрывающегося списка для изменения фильтра в диапазонах, если применяются автофильтры.
Пользователи не смогут применить или удалить автофильтры на защищенном листе независимо от настройки этого параметра.
использование отчетов сводной таблицы
Форматирование, изменение макета, обновление или изменение отчетов сводной таблицы каким-либо иным образом, а также создание новых отчетов.
изменение объектов
Выполнять следующие действия:
Внесение изменений в графические объекты, в том числе карты, встроенные диаграммы, фигуры, текстовые поля и элементы управления, которые не были разблокированы перед установкой защиты листа. Например, если на листе есть кнопка, запускающая макрос, ее можно нажать, чтобы запустить макрос, но нельзя удалить.
Внесение каких-либо изменений (например, форматирование) во встроенную диаграмму. Диаграмма по-прежнему будет обновляться при изменениях ее исходных данных.
Добавление или изменение примечаний.
изменение сценариев
Просмотр скрытых сценариев, изменение сценариев с установленным запретом на изменения и удаление этих сценариев. Пользователи могут изменять значения в изменяемых ячейках, если ячейки не защищены, и добавлять новые сценарии.
Элементы листа диаграммы
Запрещаемые действия
Изменение элементов, являющихся частью диаграммы, таких как ряды данных, оси и легенды. При этом в диаграммах будут отображаться изменения, вносимые в исходные данные.
Изменение графических объектов, включая фигуры, текстовые поля и элементы управления (если объекты не были разблокированы до включения защиты листа диаграммы).
В поле Пароль для отключения защиты листа введите пароль, нажмите кнопку ОК, а затем повторно введите пароль для подтверждения.
Пароль необязателен. Если его не задать, любой пользователь сможет снять защиту с листа и изменить защищенные элементы.
Убедитесь, что вы выбрали пароль, который можете запомнить. Если вы потеряете пароль, вы не сможете получить доступ к защищенным элементам на этом сайте.
Если ячейка принадлежит к нескольким диапазонам, ее смогут редактировать пользователи, имеющие права на редактирование любого из этих диапазонов.
Если пользователь пытается редактировать несколько ячеек одновременно и имеет разрешение на изменение только некоторых из них, пользователю будет предложено изменить ячейки по одному.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей ("шапки" и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Читайте также: