Разрешение изменения диапазонов excel
Для данных на листе от изменений в Excel существует такая команда как Защитить лист (Protect sheet) . Найти её можно:
Все возможности защиты и видеоурок по защите листов в Excel можно посмотреть на этой странице: Защита листов и ячеек в MS Excel
Но при выполнении этой команды защищаются ВСЕ ячейки листа. Но бывают ситуации, когда защитить необходимо все ячейки, кроме А1 , С2 и D3 , чтобы изменения можно было делать только в этих ячейках, а значения остальных изменить было невозможно. Очень востребовано это в различного вида заполняемых шаблонах, в которых заполнять можно только определенные ячейки, а все остальные запретить к редактированию. Сделать это достаточно просто. Выделяем ячейки, которые необходимо разрешить изменять( А1 , С2 и D3 ); затем Ctrl+1(или правая кнопка мыши-Формат ячеек (Format cells) )-вкладка Защита (Protection) . Снимаем галочку с пункта Защищаемая ячейка (Locked) . Теперь устанавливаем защиту на лист.
Если необходимо сделать обратное - защитить лишь несколько ячеек, а для всех остальных оставить возможность изменять их, то последовательность будет несколько иной:
- Выделяем ВСЕ ячейки листа (это можно сделать так:щелкаете левой кнопкой мыши на пересечении заголовков строки и столбцов):
- Формат ячеек (Format cells) -вкладка Защита (Protection). Снимаем галочку с пункта Защищаемая ячейка (Locked)
- выделяем нужные ячейки (если ячейки не "в одной кучке", а по отдельности, то выделить их можно по одной, зажав клавишу Ctrl)
- Формат ячеек (Format cells) -вкладка Защита (Protection) . Ставим галочку Защищаемая ячейка (Locked)
После этого устанавливаете защиту на лист(как см. в самом начале статьи) и вуаля! Изменять можно только те ячейки, у которых снята галка с "Защищаемая ячейка" (Locked) .
При этом, если при защите листа снять галочку с пункта выделение заблокированных ячеек (Select locked cells) - выделять можно будет только те ячейки, которые разрешены для редактирования. Так же перемещение по ячейкам стрелками, TAB-ом и после нажатия Enter будет происходить исключительно по незащищенным ячейкам. Это может быть полезно, чтобы пользователю не пришлось самому угадывать в каких ячейках можно изменять значения, а в каких нет.
Так же на вкладке Защита (Protection) есть пункт Скрыть формулы (Hidden) . Если его установить вместе с установкой атрибута Защищаемая ячейка, то после установки защиты в защищенных ячейках невозможно будет увидеть формулы - только результаты их вычислений. Полезно, если хотите оставить возможность вводить какие-то параметры, а расчеты формулами оставить "за кадром".
По умолчанию при защите всех ячеек все ячейки заблокированы, поэтому ни одна из них не может быть редактируемой. Чтобы включить редактирование некоторых ячеек, при этом другие ячейки можно разблокировать. Перед защитой листа можно заблокировать только определенные ячейки и диапазоны и при желании позволить определенным пользователям изменять только определенные диапазоны защищенного листа.
Блокировка только определенных ячеек и диапазонов ячеек на защищенном листе
Если лист защищен, сделайте следующее:
На вкладке Рецензировка нажмите кнопку Отостановка листа (в группе Изменения).
Если будет предложено, введите пароль, чтобы отоблести защиты.
Выделите лист целиком, нажав кнопку Выделить все.
На вкладке Главная щелкните всплывающее кнопку запуска Формат шрифта ячейки. Вы также можете нажать клавиши CTRL+SHIFT+F или CTRL+1.
Во всплываемом окне Формат ячеек на вкладке Защита отоберем поле Блокировка и нажмите кнопку ОК.
При защите листа все ячейки будут разблокированы. Теперь вы можете выбрать конкретные ячейки, которые нужно защитить.
Выделите на листе только те ячейки, которые необходимо заблокировать.
Снова отключим всплывающее окно Формат ячеек (CTRL+SHIFT+F).
В этот раз на вкладке Защита выберите поле Заблокировано и нажмите кнопку ОК.
На вкладке Рецензирование нажмите кнопку Защитить лист.
В списке Разрешить всем пользователям этого таблицы выберите элементы, которые пользователи должны иметь возможность изменять.
Дополнительные сведения об элементах листа
Снятый флажок
Запрещаемые действия
выделение заблокированных ячеек
Перемещение указателя на ячейки, для которых установлен флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять защищенные ячейки.
выделение незаблокированных ячеек
Перемещение указателя на ячейки, для которых снят флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять незаблокированные ячейки, а также перемещаться между незаблокированными ячейками на защищенном листе с помощью клавиши TAB.
формат ячеек
Изменение параметров в диалоговых окнах Формат ячеек или Условное форматирование. Если условное форматирование было применено до установки защиты листа, форматирование будет изменяться при вводе значения, удовлетворяющего определенному условию.
форматирование столбцов
Использование любых команд форматирования столбцов, включая изменение ширины столбца или скрытие столбцов (вкладка Главная, группа Ячейки, кнопка Формат).
форматирование строк
Использование любых команд форматирования строк, включая изменение высоты строки или скрытие строк (вкладка Главная, группа Ячейки, кнопка Формат).
вставку столбцов
вставку строк
вставку гиперссылок
Вставка новых гиперссылок (даже в незаблокированных ячейках).
удаление столбцов
Если команда удаление столбцов защищена, а команда вставку столбцов не защищена, пользователь не сможет удалять столбцы, которые он вставит.
удаление строк
Если команда удаление строк защищена, а команда вставку строк не защищена, пользователь не сможет удалять строки, которые он вставит.
Использование команд для сортировки данных (вкладка Данные, группа Сортировка и фильтр).
Пользователи не смогут сортировать диапазоны, содержащие заблокированные ячейки на защищенном листе, независимо от настройки этого параметра.
использование автофильтра
Использование стрелок раскрывающегося списка для изменения фильтра в диапазонах, если применяются автофильтры.
Пользователи не смогут применить или удалить автофильтры на защищенном листе независимо от настройки этого параметра.
использование отчетов сводной таблицы
Форматирование, изменение макета, обновление или изменение отчетов сводной таблицы каким-либо иным образом, а также создание новых отчетов.
изменение объектов
Выполнять следующие действия:
Внесение изменений в графические объекты, в том числе карты, встроенные диаграммы, фигуры, текстовые поля и элементы управления, которые не были разблокированы перед установкой защиты листа. Например, если на листе есть кнопка, запускающая макрос, ее можно нажать, чтобы запустить макрос, но нельзя удалить.
Внесение каких-либо изменений (например, форматирование) во встроенную диаграмму. Диаграмма по-прежнему будет обновляться при изменениях ее исходных данных.
Добавление или изменение примечаний.
изменение сценариев
Просмотр скрытых сценариев, изменение сценариев с установленным запретом на изменения и удаление этих сценариев. Пользователи могут изменять значения в изменяемых ячейках, если ячейки не защищены, и добавлять новые сценарии.
Элементы листа диаграммы
Запрещаемые действия
Изменение элементов, являющихся частью диаграммы, таких как ряды данных, оси и легенды. При этом в диаграммах будут отображаться изменения, вносимые в исходные данные.
Изменение графических объектов, включая фигуры, текстовые поля и элементы управления (если объекты не были разблокированы до включения защиты листа диаграммы).
В поле Пароль для отключения защиты листа введите пароль для листа, нажмите кнопку ОК, а затем повторно введите пароль для подтверждения.
Пароль необязателен. Если не задать пароль, любой пользователь сможет снять защиту с листа и изменить защищенные элементы.
Выберите легко запоминающийся пароль, потому что в случае утраты пароля вы больше не сможете получить доступ к защищенным элементам листа.
Разблокировка диапазонов ячеек на защищенном листе для их изменения пользователями
Чтобы предоставить определенным пользователям разрешение изменять диапазоны на защищенном листе, на компьютере должна быть установлена операционная система Microsoft Windows XP или более поздней версии, а сам компьютер должен находиться в домене. Вместо использования разрешений, для которых требуется домен, можно также задать пароль для диапазона.
Выберите листы, которые нужно защитить.
На вкладке Рецензирование в группе Изменения нажмите кнопку Разрешить изменение диапазонов.
Эта команда доступна, только если лист не защищен.
Выполните одно из следующих действий:
Чтобы добавить новый редактируемый диапазон, нажмите кнопку Создать.
Чтобы изменить существующий редактируемый диапазон, выберите поле Диапазоны защищенного листа, разблокируемые паролем, затем нажмите кнопку Изменить.
Чтобы удалить редактируемый диапазон, выберите поле Диапазоны защищенного листа, разблокируемые паролем, затем нажмите кнопку Удалить.
В поле Название введите имя диапазона, который необходимо разблокировать.
В поле Содержит ячейки введите знак равенства (=), а затем ссылку на диапазон, который необходимо разблокировать.
Также можно нажать кнопку Свернуть диалоговое окно, выбрать на листе диапазон, а затем снова нажать кнопку Свернуть диалоговое окно, чтобы вернуться к диалоговому окну.
Для управления доступом с помощью пароля в поле Пароль диапазона введите пароль для доступа к диапазону.
При использовании разрешений на доступ задавать пароль необязательно. Использование пароля позволяет просматривать учетные данные всех полномочных пользователей, изменяющих диапазон.
Для установки разрешений на доступ выберите пункт Разрешения и нажмите кнопку Добавить.
В поле Введите имена объектов для выбора (примеры) введите имена пользователей, которым нужно разрешить изменять диапазоны.
Чтобы посмотреть, как должны вводиться имена пользователей, щелкните примеры. Чтобы проверить правильность имен, нажмите кнопку Проверить имена.
Чтобы указать тип разрешений для выбранного пользователя, в поле Разрешения установите или снимите флажок Разрешить или Запретить, а затем нажмите кнопку Применить.
При необходимости введите заданный пароль.
В диалоговом окне Разрешить изменение диапазонов нажмите кнопку Защитить лист.
В списке Разрешить всем пользователям этого листа выберите элементы, которые должны изменять пользователи.
Дополнительные сведения об элементах листа
Снятый флажок
Запрещаемые действия
выделение заблокированных ячеек
Перемещение указателя на ячейки, для которых установлен флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять защищенные ячейки.
выделение незаблокированных ячеек
Перемещение указателя на ячейки, для которых снят флажок Защищаемая ячейка на вкладке Защита в диалоговом окне Формат ячеек. По умолчанию пользователям разрешено выделять незаблокированные ячейки, а также перемещаться между незаблокированными ячейками на защищенном листе с помощью клавиши TAB.
формат ячеек
Изменение параметров в диалоговых окнах Формат ячеек или Условное форматирование. Если условное форматирование было применено до установки защиты листа, форматирование будет изменяться при вводе значения, удовлетворяющего определенному условию.
форматирование столбцов
Использование любых команд форматирования столбцов, включая изменение ширины столбца или скрытие столбцов (вкладка Главная, группа Ячейки, кнопка Формат).
форматирование строк
Использование любых команд форматирования строк, включая изменение высоты строки или скрытие строк (вкладка Главная, группа Ячейки, кнопка Формат).
вставку столбцов
вставку строк
вставку гиперссылок
Вставка новых гиперссылок (даже в незаблокированных ячейках).
удаление столбцов
Если команда удаление столбцов защищена, а команда вставку столбцов не защищена, пользователь не сможет удалять столбцы, которые он вставит.
удаление строк
Если команда удаление строк защищена, а команда вставку строк не защищена, пользователь не сможет удалять строки, которые он вставит.
Использование команд для сортировки данных (вкладка Данные, группа Сортировка и фильтр).
Пользователи не смогут сортировать диапазоны, содержащие заблокированные ячейки на защищенном листе, независимо от настройки этого параметра.
использование автофильтра
Использование стрелок раскрывающегося списка для изменения фильтра в диапазонах, если применяются автофильтры.
Пользователи не смогут применить или удалить автофильтры на защищенном листе независимо от настройки этого параметра.
использование отчетов сводной таблицы
Форматирование, изменение макета, обновление или изменение отчетов сводной таблицы каким-либо иным образом, а также создание новых отчетов.
изменение объектов
Выполнять следующие действия:
Внесение изменений в графические объекты, в том числе карты, встроенные диаграммы, фигуры, текстовые поля и элементы управления, которые не были разблокированы перед установкой защиты листа. Например, если на листе есть кнопка, запускающая макрос, ее можно нажать, чтобы запустить макрос, но нельзя удалить.
Внесение каких-либо изменений (например, форматирование) во встроенную диаграмму. Диаграмма по-прежнему будет обновляться при изменениях ее исходных данных.
Добавление или изменение примечаний.
изменение сценариев
Просмотр скрытых сценариев, изменение сценариев с установленным запретом на изменения и удаление этих сценариев. Пользователи могут изменять значения в изменяемых ячейках, если ячейки не защищены, и добавлять новые сценарии.
Элементы листа диаграммы
Запрещаемые действия
Изменение элементов, являющихся частью диаграммы, таких как ряды данных, оси и легенды. При этом в диаграммах будут отображаться изменения, вносимые в исходные данные.
Изменение графических объектов, включая фигуры, текстовые поля и элементы управления (если объекты не были разблокированы до включения защиты листа диаграммы).
В поле Пароль для отключения защиты листа введите пароль, нажмите кнопку ОК, а затем повторно введите пароль для подтверждения.
Пароль необязателен. Если его не задать, любой пользователь сможет снять защиту с листа и изменить защищенные элементы.
Убедитесь, что вы выбрали пароль, который можете запомнить. Если вы потеряете пароль, вы не сможете получить доступ к защищенным элементам на этом сайте.
Если ячейка принадлежит к нескольким диапазонам, ее смогут редактировать пользователи, имеющие права на редактирование любого из этих диапазонов.
Если пользователь пытается редактировать несколько ячеек одновременно и имеет разрешение на изменение только некоторых из них, пользователю будет предложено изменить ячейки по одному.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В Microsoft Excel 2002 и более поздних версиях Excel теперь можно использовать пароли для защиты определенных диапазонов на листах. Это изменение предыдущих версий Excel, в котором один пароль применяется ко всему листу, который может иметь несколько защищенных диапазонов. Кроме того, если вы используете Windows 2000, вы можете применять пароли на уровне группы и пароли на уровне пользователя к разным диапазонам.
Функции в Microsoft Excel, связанные с скрытием данных и защитой листов и книг с помощью паролей, не предназначены для защиты данных или защиты конфиденциальной информации в Excel. Эти функции можно использовать для более четкого представления информации путем скрытия данных или формул, которые могут запутать некоторых пользователей. Эти функции также помогают предотвратить случайное изменение данных другими пользователями.
Excel не шифрует данные, скрытые или заблокированные в книге. При наличии достаточного времени пользователи могут получать и изменять все данные в книге, если у них есть к ним доступ. Чтобы предотвратить изменение данных и защитить конфиденциальную информацию, ограничьтесь доступом к любым Excel, содержащим такую информацию, храня их в расположениях, доступных только для авторизованных пользователей.
В этой статье описывается, как обеспечить правильную работу определенных сценариев совместной работы в средах совместной работы, не включающих пользователей с вредоносными намерениями. Невозможно включить надежное шифрование для файла с помощью защиты паролем. Чтобы защитить документ или файл от пользователя, у которого есть вредоносные намерения, можно ограничить разрешения с помощью службы управления правами на доступ к данным (IRM).
Дополнительная информация
Применение разных паролей
Чтобы применить разные пароли к двум диапазонам на листе, выполните следующие действия.
Запустите Excel, а затем откройте пустую книгу.
В меню "Сервис " наведите указатель мыши на пункт "Защита" и выберите команду "Разрешить пользователям изменять диапазоны".
В Microsoft Office Excel 2007 нажмите кнопку "Разрешить пользователям изменять диапазоны" в группе "Изменения" на вкладке "Рецензия".
В диалоговом окне " Разрешить пользователям изменять диапазоны" нажмите кнопку " Создать".
В диалоговом окне "Создать диапазон" нажмите кнопку " Свернуть диалоговое окно ". Выберите диапазон B2:B6 и снова нажмите кнопку "Свернуть диалоговое окно".
В поле "Пароль диапазона" введите rangeone, нажмите кнопку " ОК", затем снова введите его в диалоговом окне "Подтверждение пароля", а затем нажмите кнопку "ОК".
Повторите шаги 3–5, выбрав диапазон D2:D6 и введя rangetwoas пароль для этого диапазона.
В диалоговом окне " Разрешить пользователям изменять диапазоны" нажмите кнопку " Защитить лист". В поле "Пароль для отмены защиты листа " введите ranger и нажмите кнопку " ОК". При появлении запроса введите пароль повторно и нажмите кнопку "ОК".
Выделите ячейку B3, а затем начните ввести Dataone.
При вводе D появится диалоговое окно " Диапазон разблокировки".
Введите диапазон в поле ввода пароля, чтобы изменить это поле ячейки, и нажмите кнопку " ОК".
Теперь вы можете вводить данные в ячейку B3 и любую другую ячейку в диапазоне B2:B6, но нельзя ввести данные ни в одной из ячеек D2:D6, не указав правильный пароль для этого диапазона.
Диапазон, который вы защищаете паролем, не обязательно должен быть сделан из смежных ячеек. Если вы хотите, чтобы диапазоны B2:B6 и D2:D6 совместно использовали пароль, можно выбрать B2:B6, как описано на шаге 4 выше в этой статье, ввести запятую в диалоговом окне "Создать диапазон", а затем выбрать диапазон D2:D6 перед назначением пароля.
При таком применении разных паролей к отдельным диапазонам диапазон, который был разблокирован, остается разблокированным до закрытия книги. При разблокировке другого диапазона первый диапазон не блокирован повторно. Аналогичным образом при сохранении книги диапазон не будет блокировщиком.
Можно использовать существующие имена диапазонов для идентификации ячеек, которые должны быть защищены с помощью паролей, но в этом случае Excel любые относительные ссылки в существующих определениях имен в абсолютные ссылки. Так как это может не дать нужных результатов, для выбора ячеек лучше использовать кнопку "Свернуть диалоговое окно", как описано выше в этой статье.
Применение паролей на уровне группы и паролей на уровне пользователя
Если вы используете Windows 2000 (но не другие версии Windows), можно назначить различные разрешения различным отдельным пользователям или группам пользователей. При этом разрешенные пользователи могут изменять защищенные диапазоны без необходимости ввода паролей, а другие пользователи по-прежнему могут изменять диапазоны, если они могут ввести правильный пароль.
Чтобы применить защиту на уровне группы к листу, выполните следующие действия.
Запустите Excel, а затем откройте пустой лист.
В меню "Сервис " наведите указатель мыши на пункт "Защита" и выберите команду "Разрешить пользователям изменять диапазоны".
Если вы используете Excel 2007, в меню "Рецензия" щелкните "Разрешить пользователям изменять диапазоны" в группе " Изменения".
В диалоговом окне " Разрешить пользователям изменять диапазоны" нажмите кнопку " Создать".
В диалоговом окне "Новый диапазон" нажмите кнопку " Свернуть диалоговое окно", выберите диапазон B2:B6 и снова нажмите кнопку " Свернуть диалоговое окно ".
В поле "Пароль диапазона " введите rangeone и дважды нажмите кнопку "ОК ". При появлении запроса введите пароль повторно.
Повторите шаги 3–5, выбрав диапазон D2:D6 и введя rangetwo в качестве пароля для этого диапазона.
В диалоговом окне "Разрешить пользователям изменять диапазоны" щелкните "Разрешения" и выберите "Добавить" в диалоговом окне "Разрешения для Диапазона2".
В диалоговом окне "Выбор пользователей или групп" введите "Все" и нажмите кнопку " ОК".
В диалоговом окне "Пользователи для изменения диапазонов" нажмите кнопку "Защитить лист", введите "Ranger" в поле " Пароль", чтобы снять защиту листа, а затем дважды нажмите кнопку "ОК". При появлении запроса введите пароль повторно.
Выделите ячейку D3 и введите Datatwo.
Пароль не требуется.
Чтобы назначить разрешения группам или отдельным пользователям, как описано выше в этой статье, необходимо использовать Windows 2000, но после этого эти разрешения распознаются при редактировании листов на компьютерах, использующих Microsoft Windows NT. Windows NT не позволяет назначать или изменять разрешения.
Если вы применяли разрешения группы или разрешения пользователя, а затем открываете книгу в Excel 2002 на компьютере microsoft Windows Edition или microsoft Windows 98, разрешения группы или разрешения пользователя игнорируются, но распознаются разные пароли для разных диапазонов.
Изменение паролей
Чтобы изменить пароль для диапазона, выполните следующие действия.
Запустите Excel и откройте книгу.
В меню "Сервис" наведите указатель мыши на пункт "Защита" и выберите пункт "Снять защиту листа".
В Excel 2007 нажмите кнопку "Снять защиту листа" в группе "Изменения" на вкладке "Рецензия".
При появлении запроса введите пароль листа и нажмите кнопку " ОК".
В меню "Сервис " наведите указатель мыши на пункт "Защита" и выберите команду "Разрешить пользователям изменять диапазоны".
В Excel 2007 нажмите кнопку "Разрешить пользователям изменять диапазоны" в группе "Изменения" на вкладке "Рецензия".
Щелкните диапазон в списке и нажмите кнопку " Изменить".
Щелкните "Пароль".
Введите новый пароль в поле " Новый пароль", а затем введите новый пароль в поле "Подтверждение нового пароля ".
Щелкните ОК, а затем щелкните ОК.
Чтобы изменить пароль для другого диапазона, повторите шаги с 3 по 6. В противном случае нажмите кнопку "Защитить лист".
Введите пароль листа в поле "Пароль", чтобы снять защиту листа .
Обратите внимание на следующие аспекты применения паролей и разрешений на уровне группы к определенным диапазонам:
Excel 2003 работает только в Microsoft Windows XP и в Microsoft Windows 2000.
Когда книга с защищенными диапазонами открывается в Excel 2002 на компьютере Windows XP, на компьютере на базе Windows 2000 или на компьютере на базе Microsoft Windows NT, диапазон листа и защита групп совпадают с защитой Excel 2003.
При открытии книги с защищенными диапазонами в Excel 2002 на компьютере microsoft Windows Edition или на компьютере с Microsoft Windows 98 диапазоны с разрешениями на уровне пользователя и на уровне группы требуют пароля диапазона.
Дополнительная информация
Дополнительные сведения о функциях Microsoft Office, которые помогают обеспечить совместную работу, см. в описании Office, предназначенных для обеспечения совместной работы и не предназначенных для повышения безопасности.
Очень часто на своих тренингах и в форумах я слышу вопрос: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы? А другие ячейки или листы были недоступны для изменения или просмотра? Или скрыть отдельные столбцы с глаз пользователя? Часть подобного функционала предоставляется стандартными средствами Excel, а другая(например, доступность просмотра только конкретных листов) достигается только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями, их плюсы и минусы.
Что важно: не следует указывать здесь пароль, который совпадает хотя бы с одним из паролей для отдельных диапазонов. Думаю, понятно почему: чтобы защиту не могли снять те, кому этого не положено делать.
Теперь остается сообщить сотрудникам отделов их пароли: производственный - 2222 , коммерческий – 1111 .
При первой попытке изменить данные в ячейках C8:N11;C13:N14 - будет запрошен пароль на изменение ячеек созданного диапазона "коммерческий" ( 1111 ):
Если пользователю известен пароль для диапазона – его необходимо будет ввести лишь один раз. В дальнейшем для ввода данных в ячейки этого диапазона вводить пароль не придется до тех пор, пока файл не будет закрыт. После повторного открытия файла пароль необходимо будет указать заново.
Однако, если сотрудник другого отдела попытается изменить ячейки производственного отдела и пароль ему неизвестен – изменить данные этих ячеек не получится.
Также ни сотрудники коммерческого отдела, ни сотрудники производственного отдела не смогут изменить данные столбцов А и В(№ и наименование статьи), заголовки таблицы(строки с 1-ой по 7-ю) и строки с итоговыми формулами (12, 15 и т.д. – закрашенные зеленым). Они смогут изменять только те ячейки, которые перечислены в назначенных каждому отделу диапазонах. Внести данные в другие ячейки(не перечисленные в разрешенных диапазонах) можно будет исключительно сняв общий доступ с книги, а после этого защиту с листа –Рецензирование (Review) -группа Изменения (Changes) -Снять защиту листа (Unprotect sheet) . Но снять общую защиту сможет только тот, кто её создавал и кому известен "главный" пароль. Как правило это администратор или некий "смотрящий" файла и другие пользователи этот пароль не знают.
Плюс подобного метода в том, что такая защита может быть установлена для книги в общем доступе(подробнее про книги с общим доступом можно прочитать в статье - Ведение журнала сделанных в книге изменений).
Что необходимо учитывать для книг с общим доступом: создавать диапазоны для пользователей и устанавливать защиту на лист необходимо ДО назначения книге общего доступа, т.к. после того, как книге будет назначен общий доступ изменять параметры защиты листов и книги запрещено. При этом запрещены как установка защиты так и её снятие.
Минус данного метода в том, что нет дружественного интерфейса снятия защиты. Например, при попытке изменить какие-то ячейки одного из назначенных диапазонов нет никакой информации о том, что это за диапазон(коммерческий или производственный). Что в свою очередь может запутать пользователя. Так же данным методом невозможно скрыть листы, либо отдельные строки и столбцы. Можно лишь запретить изменение ячеек.
Разграничение прав доступа при помощи VBA
Самый большой минус всех методов ниже: они не будут работать при отключенных макросах (Что такое макрос и где его искать?). Во всех приложенных к статье файлах это предусмотрено и если макросы будут отключены, то пользователь увидит лист, предлагающий включить макросы. Подробнее см. в статье: Как запустить файл с включенными макросами?
Плюс подобного подхода - мы практически не ограничены в правилах: можем скрывать от отдельных пользователей любые листы, строки и столбцы, защищать отдельные ячейки и т.п. Я ниже приведу несколько вариантов реализации защиты кодами, а вам останется лишь выбрать тот, который больше подходит под задачу. Первые три больше демонстрационные, чтобы показать что можно сделать. А вот последний пример - Практический пример с использованием администратора - наиболее приближен к задачам, применяемым в работе и наиболее удобен для распространения среди пользователей.
Проект VBA во всех файлах открыт для просмотра и изменений . Однако перед распространением решений в реальности лучше его закрыть от просмотра и изменений - Как защитить проект VBA паролем.
Важно: приведенные ниже решения могут работать некорректно в книгах с общим доступом. А те решения, в которых устанавливается защита на листы вообще не будут работать, т.к. для книг с общим доступом невозможно изменять параметры защиты листов и книг.
В форме необходимо выбрать пользователя и указать пароль, соответствующий этому пользователю. Важно: Пароли и список доступных листов можно редактировать на очень скрытом листе "Users". Для каждого пользователя можно указать несколько листов. Указывать имена листов необходимо в точности такие же, какие они на самом деле. Это значит, что и регистр букв и каждый пробел должен быть учтен. Для разделения записей с несколькими листами используется точка-с-запятой(Лист1;Лист2;Лист3).
На листе "Main" перечислены имена пользователей, пароли для них и доступные для просмотра листы. Данная информация указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Важно: файл может работать нестабильно в книгах с общим доступом.
Tips_Macro_Sheets_for_Users.xls (84,5 KiB, 9 912 скачиваний)
Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:
- Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
=СУММ( A1:C10 ; F1:K10 )
Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется - достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой - а что, если таких формул 10? 30?
Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде. - Именованный диапазон не просто так называется именованным. Если взять пример выше - то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя - то оно как было ДиапазонСумм , так им и останется.
- При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:
Данный метод доступен лишь в версиях Excel 2007 и выше
MsgBox Range("ДиапазонСумм").Address MsgBox [ДиапазонСумм].Address
Обращение к именованному диапазону в формулах/функциях
- =СУММ( ДиапазонСумм )
- =ВПР("Критерий"; ДиапазонСумм ;2;0)
Читать подробнее про функцию ВПР
Если при указании диапазона в формуле выделить именованный диапазон, то его имя автоматически подставится в формулу вместо фактического адреса ячеек:
Ограничения, накладываемые на создание имен
- В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
- Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
- Нельзя в качестве имени использовать зарезервированные в Excel константы - R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
- Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 - это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен - то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется - можно просто добавить нижнее подчеркивание к имени: _A1
- Длина имени не может превышать 255 символов
Создание именованного диапазона
Способ первый
обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter:
Способ второй
Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:
- Excel 2007: Имя диапазона (Range Name)
- Excel 2010: Присвоить имя (Define Name)
либо:
Жмем Ctrl + F3
либо:
- 2007-2016 Excel : вкладка Формулы (Formulas) -Диспетчер имен (Name Manager) -Создать (New) (либо на той же вкладке сразу - Присвоить имя (Define Name) )
- 2003 Excel : Вставка -Имя -Присвоить
Появляется окно создания имени
Имя (Name) - указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
Область (Scope) - указывается область действия создаваемого диапазона - Книга , либо Лист1 :
- Лист1 (Sheet1) - созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
- Книга (Workbook) - созданный диапазон можно будет использовать из любого листа данной книги
Примечание (Comment) - здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
Диапазон (Refers to) - при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.
Изменение диапазона
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.
Удаление диапазона
Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Удалить (Delete. ) .
Так же можно создавать списки с автоматическим определением его размера. Например, если значения в списке периодически пополняются или удаляются и чтобы каждый раз не переопределять границы таких диапазонов. Такие диапазоны называют динамическими.
Читайте также: