Скрытие формул в Microsoft Excel

Как скрыть формулы в ячейках в Excel?

Разберем различные способы как скрыть от пользователей формулы в ячейках в Excel (с помощью отключения отображения строки формул и посредством защиты данных).

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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

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

Как скрыть или показать строку формул в Excel?

Самый простой способ скрыть отображение формул — это непосредственно убрать строку формул из интерфейса программы Excel.

Для реализации такого способа в панели вкладок переходим на Вид -> Отображение и снимаем галочку из Строка формул, после чего строка сразу исчезает:

Однако как очень просто убрать строку формул, так очень просто и вернуть ее обратно, повторив все те же самые действия и вернув галочку на место.

Теперь давайте поподробнее поговорим про более сложные и надежные варианты.

Как скрыть формулы от просмотра в Excel?

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

Вообще процесс можно разделить на 2 части:

  • Снимаем ограничения на редактирование ячеек из требуемого диапазона;
  • Включаем защиту листа, чтобы применились правила из 1 части.

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

Выделяем требуемый диапазон, нажимаем правую кнопку мыши и выбираем Формат ячеек -> Защита:

В открывшемся окне настроек мы видим 2 свойства ячеек, которые могут нам пригодиться:

  • Защищаемая ячейка. В этом случае при включении защиты листа данные ячейки не будут защищаться (т.е. другими словами мы задаем исключения из защиты);
  • Скрыть формулы. Аналогичный принцип только со свойством скрытия формул.

Нас сейчас интересует 2 свойство и для выделенных ячеек ставим галочку в поле Скрыть формулы и нажимаем ОК.

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

Поэтому переходим на панели вкладок на Рецензирование -> Защита -> Защитить лист, выбираем требуемые пункты защиты (по умолчанию можно оставить только 2 верхних пункта, если необходимо укажите дополнительные разрешения, а также устанавливаем пароль):

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

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

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

Спасибо за внимание!
Если у вас есть остались вопросы по теме статьи — пишите в комментариях.

Скрытие формул в Microsoft Excel

Иногда при создании документа с расчетами пользователю нужно спрятать формулы от чужих глаз. Прежде всего, такая необходимость вызвана нежеланием юзера, чтобы посторонний человек понял структуру документа. В программе Эксель имеется возможность скрыть формулы. Разберемся, как это можно сделать различными способами.

Способы спрятать формулу

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

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

Способ 1: скрытие содержимого

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

    Выделяем диапазон, содержимое которого нужно скрыть. Кликаем правой кнопкой мыши по выделенной области. Открывается контекстное меню. Выбираем пункт «Формат ячеек». Можно поступить несколько по-другому. После выделения диапазона просто набрать на клавиатуре сочетание клавиш Ctrl+1. Результат будет тот же.

После того, как окно закрыто, переходим во вкладку «Рецензирование». Жмем на кнопку «Защитить лист», расположенную в блоке инструментов «Изменения» на ленте.

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

  • Открывается ещё одно окно, в котором следует повторно набрать ранее введенный пароль. Это сделано для того, чтобы пользователь, вследствии введения неверного пароля (например, в измененной раскладке), не утратил доступ к изменению листа. Тут также после введения ключевого выражения следует нажать на кнопку «OK».
  • После этих действий формулы будут скрыты. В строке формул защищенного диапазона при их выделении ничего отображаться не будет.

    Способ 2: запрет выделения ячеек

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

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

    Далее, как и в предыдущем случае, жмем на кнопку «Защитить лист», расположенную на вкладке «Рецензирование».

    Аналогично с предыдущим способом открывается окно введения пароля. Но на этот раз нам нужно снять галочку с параметра «Выделение заблокированных ячеек». Тем самым мы запретим выполнение данной процедуры на выделенном диапазоне. После этого вводим пароль и жмем на кнопку «OK».

  • В следующем окошке, как и в прошлый раз, повторяем пароль и кликаем по кнопке «OK».
  • Теперь на выделенном ранее участке листа мы не просто не сможем просмотреть содержимое функций, находящихся в ячейках, но даже просто выделить их. При попытке произвести выделение будет появляться сообщение о том, что диапазон защищен от изменений.

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

    Добавьте сайт Lumpics.ru в закладки и мы еще пригодимся вам.
    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

    Поделиться статьей в социальных сетях:

    Еще статьи по данной теме:

    Добрый день! Как скрыть в программе Microsoft Excel запись #дело/0!, если внесены формулы, но заполняются данные постепенно.

    Здравствуйте, Ирина. Это можно сделать при помощи функции ЕСЛИОШИБКА и условного форматирования.
    I. Вначале используется функция ЕСЛИОШИБКА. В той формуле, где вылезают ошибки, после = введите название функции ЕСЛИОШИБКА. Затем откройте скобки. Дальше должна следовать непосредственна та формула. которая приводит к ошибке. После формулы ставьте точку с запятой, цифру 0 и закрывайте скобки. Должно получится что-то типа этого =ЕСЛИОШИБКА(H8/I8;0)
    Теперь если у вас будет ошибка в формуле, то вместо ошибки отобразится число 0, а если при расчете ошибки не предвидится, то в этом случае в ячейке будет отображаться реальный результат расчета. Если формула однотипная, то для того. чтобы вручную не прописывать в каждую ячейку, используйте маркер заполнения и копируйте формулу с его помощью.

    Читайте также  Вставка текста в ячейку с формулой в Microsoft Excel

    II. Если вас не удовлетворяет наличие нулей и вы хотите полностью скрыть ошибочные значения, то делается это при помощи условного форматирования. Если вы не в курсе. что это такое, то подробнее можно узнать в этой статье:
    Условное форматирование в Excel
    Сам же способ заключается в следующем.
    1).Создаете правило на диапазон ячеек, где могут появится ошибочные результаты. В окне создания правила выберите вариант «Форматировать только ячейки, которые содержат».
    2). В следующем окне в выпадающих списках выберите «Значение ячейки» и «равно». В поле введите «0». В общем, все так, как на скриншоте ниже. Далее жмите кнопку «Формат».
    3). В разделе «Число» выбирайте пункт «Все форматы». В поле «Тип» введите три точки с запятой ;;; Жмите «OK».
    4). Вернувшись в предыдущее окно тоже жмите «OK».

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

    Это все прекрасно, но яблочники, пользуясь своим Numbers спокойно видят формулы 🙁
    Как победить? Импортировать таблицу из Excell в Numbers и в ней все закрыть?

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

    Задайте вопрос или оставьте свое мнение Отменить комментарий

    Скрытие формул в Excel

    При работе с таблицами Excel, наверняка, многие пользователи могли заметить, что если в какой-то ячейке содержится формула, то в специальной строке формул (справа от кнопки “fx”) мы увидим именно ее.

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

    Метод 1. Включаем защиту листа

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

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

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

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

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

    Метод 2. Запрещаем выделение ячеек

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

    1. Выделяем требуемый диапазон ячеек, в отношении которых хотим выполнить запланированные действия.
    2. Идем в окно форматирования и во вкладке “Защита” проверяем, стоит ли галочка напротив опции “Защищаемая ячейка” (должна быть включена по умолчанию). Если нет, ставим ее и щелкаем OK.
    3. Во вкладке “Рецензирование” щелкаем по кнопке “Защитить лист”.
    4. Откроется уже знакомое окошко для выбора параметров защиты и ввода пароля. Убираем галочку напротив опции “выделение заблокированных ячеек”, задаем пароль и щелкаем OK.
    5. Подтверждаем пароль, повторно набрав его, после чего жмем OK.
    6. В результате выполненных действий у нас больше не будет возможности не только просматривать содержимое ячеек в строке формул, но и выделять их.

    Заключение

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

    Защита листа и ячеек в Excel

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

    Как поставить защиту в Excel на лист

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

    1. Выделите диапазон ячеек B2:B6 и вызовите окно «Формат ячеек» (CTRL+1). Перейдите на вкладку «Защита» и снимите галочку на против опции «Защищаемая ячейка». Нажмите ОК.
    2. Выберите инструмент «Рицензирование»-«Защитить лист».
    3. В появившемся диалоговом окне «Защита листа» установите галочки так как указано на рисунке. То есть 2 опции оставляем по умолчанию, которые разрешают всем пользователям выделять любые ячейки. А так же разрешаем их форматировать, поставив галочку напротив «форматирование ячеек». При необходимости укажите пароль на снятие защиты с листа.

    Теперь проверим. Попробуйте вводить данные в любую ячейку вне диапазона B2:B6. В результате получаем сообщение: «Ячейка защищена от изменений». Но если мы захотим отформатировать любую ячейку на листе (например, изменить цвет фона) – нам это удастся без ограничений. Так же без ограничений мы можем делать любые изменения в диапазоне B2:B6. Как вводить данные, так и форматировать их.

    Как видно на рисунке, в окне «Защита листа» содержится большое количество опций, которыми можно гибко настраивать ограничение доступа к данным листа.

    Как скрыть формулу в ячейке Excel

    Часто бывает так, что самое ценное на листе это формулы, которые могут быть достаточно сложными. Данный пример сохраняет формулы от случайного удаления, изменения или копирования. Но их можно просматривать. Если перейти в ячейку B7, то в строке формул мы увидим: «СУММ(B2:B6)» .

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

    1. Запретить выделять ячейки на листе.
    2. Включить скрытие содержимого ячейки.

    Рассмотрим, как реализовать второй способ:

    1. Если лист защищенный снимите защиту выбрав инструмент: «Рецензирование»-«Снять защиту листа».
    2. Перейдите на ячейку B7 и снова вызываем окно «Формат ячеек» (CTRL+1). На закладке «Защита» отмечаем опцию «Скрыть формулы».
    3. Включите защиту с такими самыми параметрами окна «Защита листа» как в предыдущем примере.

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

    Примечание. Закладка «Защита» доступна только при незащищенном листе.

    Как скрыть лист в Excel

    Допустим нам нужно скрыть закупочные цены и наценку в прайс-листе:

    1. Заполните «Лист1» так как показано на рисунке. Здесь у нас будут храниться закупочные цены.
    2. Скопируйте закупочный прайс на «Лист2», а в место цен в диапазоне B2:B4 проставьте формулы наценки 25%: =Лист1!B2*1,25.
    3. Щелкните правой кнопкой мышки по ярлычке листа «Лист1» и выберите опцию «Скрыть». Рядом же находится опция «Показать». Она будет активна, если книга содержит хотя бы 1 скрытый лист. Используйте ее, чтобы показать все скрытие листы в одном списке. Но существует способ, который позволяет даже скрыть лист в списке с помощью VBA-редактора (Alt+F11).
    4. Для блокировки опции «Показать» выберите инструмент «Рецензирование»-«Защитить книгу». В появившемся окне «Защита структуры и окон» поставьте галочку напротив опции «структуру».
    5. Выделите диапазон ячеек B2:B4, чтобы в формате ячеек установить параметр «Скрыть формулы» как описано выше. И включите защиту листа.
    Читайте также  Условное форматирование: инструмент Microsoft Excel для визуализации данных

    Внимание! Защита листа является наименее безопасной защитой в Excel. Получить пароль можно практически мгновенно с помощью программ для взлома. Например, таких как: Advanced Office Password Recovery – эта программа позволяет снять защиту листа Excel, макросов и т.п.

    Полезный совет! Чтобы посмотреть скрытые листы Excel и узнать их истинное количество в защищенной книге, нужно открыть режим редактирования макросов (Alt+F11). В левом окне «VBAProject» будут отображаться все листы с их именами.

    Но и здесь может быть закрыт доступ паролем. Для этого выбираем инструмент: «Tools»-«VBAProjectProperties»-«Protection» и в соответствующих полях вводим пароль. С другой стороны, если установленные пароли значит, книга скрывает часть данных от пользователя. А при большом желании пользователь рано или поздно найдет способ получить доступ этим к данным. Об этом следует помнить, когда Вы хотите показать только часть данных, а часть желаете скрыть! В данном случае следует правильно оценивать уровень секретности информации, которая предоставляется другим лицам. Ответственность за безопасность в первую очередь лежит на Вас.

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

    10 формул в Excel, которые облегчат вам жизнь

    Эти простые, но полезные функции могут пригодиться для самых разных задач.

    Чтобы применить любую из перечисленных функций, поставьте знак равенства в ячейке, в которой вы хотите видеть результат. Затем введите название формулы (например, МИН или МАКС), откройте круглые скобки и добавьте необходимые аргументы. Excel подскажет синтаксис, чтобы вы не допустили ошибку.

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

    Есть и альтернативный способ указать аргументы. Если после названия функции добавить пустые скобки и нажать на кнопку «Вставить функцию» (fx), появится окно ввода с дополнительными подсказками. Можете использовать его, если вам так удобнее.

    1. МАКС

    • Синтаксис: =МАКС(число1; [число2]; …).

    Формула «МАКС» отображает наибольшее из чисел в выбранных ячейках. Аргументами функции могут выступать как отдельные ячейки, так и диапазоны. Обязательно вводить только первый аргумент.

    2. МИН

    • Синтаксис: =МИН(число1; [число2]; …).

    Функция «МИН» противоположна предыдущей: отображает наименьшее число в выбранных ячейках. В остальном принцип действия такой же.

    Сейчас читают 🔥

    3. СРЗНАЧ

    • Синтаксис: =СРЗНАЧ(число1; [число2]; …).

    «СРЗНАЧ» отображает среднее арифметическое всех чисел в выбранных ячейках. Другими словами, функция складывает указанные пользователем значения, делит получившуюся сумму на их количество и выдаёт результат. Аргументами могут быть отдельные ячейки и диапазоны. Для работы функции нужно добавить хотя бы один аргумент.

    4. СУММ

    • Синтаксис: =СУММ(число1; [число2]; …).

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

    5. ЕСЛИ

    • Синтаксис: =ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]).

    Формула «ЕСЛИ» проверяет, выполняется ли заданное условие, и в зависимости от результата отображает одно из двух указанных пользователем значений. С её помощью удобно сравнивать данные.

    В качестве первого аргумента функции можно использовать любое логическое выражение. Вторым вносят значение, которое таблица отобразит, если это выражение окажется истинным. И третий (необязательный) аргумент — значение, которое появляется при ложном результате. Если его не указать, отобразится слово «ложь».

    6. СУММЕСЛИ

    • Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).

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

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

    7. СЧЁТ

    • Синтаксис: =СЧЁТ(значение1; [значение2]; …).

    Эта функция подсчитывает количество выбранных ячеек, которые содержат числа. Аргументами могут выступать отдельные клетки и диапазоны. Для работы функции необходим как минимум один аргумент. Будьте внимательны: «СЧЁТ» учитывает ячейки с датами.

    8. ДНИ

    • Синтаксис: =ДНИ(конечная дата; начальная дата).

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

    9. КОРРЕЛ

    • Синтаксис: =КОРРЕЛ(диапазон1; диапазон2).

    «КОРРЕЛ» определяет коэффициент корреляции между двумя диапазонами ячеек. Иными словами, функция подсчитывает статистическую взаимосвязь между разными данными: курсами доллара и рубля, расходами и прибылью и так далее. Чем больше изменения в одном диапазоне совпадают с изменениями в другом, тем корреляция выше. Максимальное возможное значение — +1, минимальное — −1.

    10. СЦЕП

    • Синтаксис: =СЦЕП(текст1; [текст2]; …).

    Эта функция объединяет текст из выбранных ячеек. Аргументами могут быть как отдельные клетки, так и диапазоны. Порядок текста в ячейке с результатом зависит от порядка аргументов. Если хотите, чтобы функция расставляла между текстовыми фрагментами пробелы, добавьте их в качестве аргументов, как на скриншоте выше.

    Защита листов и ячеек в MS Excel

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

    Установка защиты листов
    Чтобы защитить лист необходимо перейти на вкладку Рецензирование (Review) -группа Изменения (Changes)Защитить лист (Protect sheet) .
    в Excel 2003СервисЗащитаЗащитить лист.
    для версий Excel 2010 и выше так же можно щелкнуть правой кнопкой мыши на ярлыке нужного листа и выбрать Защитить лист (Protect sheet)
    После нажатия появится окно:

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

    • выделение заблокированных ячеек (Select locked cells) — разрешено выделять ячейки, для которых установлен атрибут Защищаемая ячейка (правая кнопка мыши на ячейке/диапазоне —Формат ячеек (Format cells) -вкладка Защита (Protection)Защищаемая ячейка (Locked) ). Если отметить этот пункт, то пункт выделение незаблокированных ячеек будет отмечен автоматически, т.к. если разрешено выделение заблокированных ячеек, то конечно, должно быть разрешено выделять и незаблокированные.
    • выделение незаблокированных ячеек (Select unlocked cells) — будет разрешено выделять только те ячейки, для которых атрибут Защищаемая ячейка не установлен. Применяется вместе с отключением пункта выделение заблокированных ячеек, чтобы запретить пользователю после установки защиты даже выделять запрещенные к изменению ячейки. Таким образом пользователь будет вынужден перемещаться только по тем ячейкам, которые ему можно изменять. Подробнее про применение свойства «Защищаемая ячейка» можно ознакомиться в этой статье: Как разрешить изменять только выбранные ячейки?
    • форматирование ячеек (Format cells) — будет разрешено изменять форматы ячеек: цвет заливки, цвет шрифта, размер шрифта, имя шрифта, границы, отступы и т.п.
    • форматирование столбцов (Format columns) — несмотря на вроде понятное название при установке разрешает изменять ширину столбцов. При этом, если пункт форматирование ячеек не установлен, то изменять цвет шрифта, заливки и т.п. будет запрещено
    • форматирование строк (Format rows) — так же как и в случае с пунктом форматирование столбцов при установке разрешает изменять высоту строк, но при этом невозможно изменять цвет шрифта, заливки и т.п., если пункт форматирование ячеек не установлен
    • вставку столбцов (Insert columns) — разрешает вставку целых столбцов (вставлять отдельные ячейки при этом запрещено)
    • вставку строк (Insert rows) — разрешает вставку целых строк (вставлять отдельные ячейки при этом запрещено)
    • вставку гиперссылок (Insert hyperlinks) — разрешает создание гиперссылок на листе (Что такое гиперссылка?). Правда, при этом создать гиперссылки можно будет исключительно в незаблокированных ячейках.
    • удаление столбцов (Delete columns) — разрешает удаление целых столбцов. При этом удаление столбцов допускается только в том случае, если столбец не содержит заблокированных ячеек. Если хоть одна ячейка в столбце с атрибутом «Защищаемая ячейка», то удаление столбца невозможно. Так же невозможно удалять отдельные ячейки внутри столбцов, даже если все ячейки не заблокированные
    • удаление строк (Delete rows) — разрешает удаление целых строк. При этом удаление строк допускается только в том случае, если строка не содержит заблокированных ячеек. Если в строке есть хоть одна ячейка с атрибутом «Защищаемая ячейка», то удаление строки невозможно. Так же невозможно удалять отдельные ячейки внутри строк, даже если все ячейки в строке не заблокированные
    • сортировку (Sort) — один из «хитрых» пунктов. Хоть сам пункт сортировки активен и доступен для вызова, сама сортировка при этом разрешена только в том случае, если все ячейки внутри сортируемого диапазона не заблокированные. Если внутри диапазона будет хоть одна заблокированная ячейка (с атрибутом «Защищаемая ячейка»), то сортировка будет невозможна
    • использование автофильтра (Use Autofilter) — тоже «хитрый» пункт. Как следует из описания допускается только использование автофильтра. Это означает, что если автофильтр уже установлен на листе, то после защиты его можно будет использовать для отбора данных. Однако если фильтр не был установлен до установки защиты на лист — то установить фильтр будет уже невозможно без снятия защиты
    • использование отчетов сводной таблицы (Use PivotTable reports) — при установке будет возможно использовать сводную таблицу для анализа данных: перемещать поля внутри сводной таблицы, отбирать и фильтровать данные. Однако невозможно при этом будет изменить источник данных, обновлять сводную, изменять функции полей, добавлять вычисляемые поля, убирать и добавлять промежуточные итоги, менять макет отчета, стили и т.п.
    • изменение объектов (Edit objects) — будет возможно добавлять, выделять и даже удалять объекты на листе, а так же изменять их размеры и большинство свойств (цвета границ, заливки, эффекты свечения и стилей и пр.). К объектам в данном случае относятся Фигуры (Shapes) , Рисунки (Pictures) , объекты SmartArt, Диаграммы (Charts)
    • изменение сценариев (Edit scenarios) — если до установки защиты были созданы сценарии (Данные (Data)Анализ Что-если (What-If Analysis)Диспетчер сценариев (Scenario manager) ), то после установки защиты их можно будет изменять.
    Читайте также  Построение параболы в Microsoft Excel

    После установки нужных параметров и нажатия ОК:

    • если пароль не был указан, то на лист будет установлена защита без пароля с указанными параметрами
    • если был указан пароль, то перед защитой появится еще одно окно, в котором будет предложено подтвердить пароль. Там единственное поле, в которое надо просто ввести тот же пароль, что и в первом окне. При установке пароля следует помнить, что регистр букв различается (А и а — будут считаться разными символами), а если указать пароль русскими буквами, то при открытии файла на ПК под управлением MAC OS возможны ошибки преобразования данных и снять защиту установленным паролем будет невозможно. Поэтому лучше применять символы английского алфавита, цифры и доп.символы( !@#$%^&* )

    Если после установки защиты пользователь должен иметь возможность выделять все ячейки на листе, но так же необходимо запретить ему доступ к просмотру формул , то перед установкой защиты в нужных ячейках необходимо проделать следующее: выделяем все необходимые ячейки -правая кнопка мыши —Формат ячеек (Format cells) -вкладка Защита (Protection) . Устанавливаем флажок на пункте Скрыть формулы (Hidden) (чаще всего используется вместе с установкой галочки на Защищаемая ячейка (Locked) ). После этого устанавливаем защиту.

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

    Если в файле присутствует группировка или структура (Данные (Data)Группировать (Group) ), то её использование будет невозможно на защищенном листе. Она будет доступна только в том виде, в котором была до установки защиты. Хотя здесь тоже есть лазейка, но уже только с применением Visual Basic for Applications(VBA — встроенный в MS Office язык программирования): Как оставить возможность работать с группировкой/структурой на защищенном листе?

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

    Снятие защиты с листа
    Чтобы снять защиту с листа необходимо перейти на вкладку Рецензирование (Review) -группа Изменения (Changes)Снять защиту листа (Unprotect sheet) . Если лист был защищен без пароля, то защита будет снята сразу. Если лист был защищен с указанием пароля, то появится окно с запросом пароля

    в это поле необходимо ввести пароль и нажать Ок. Если пароль неизвестен или был забыт, то стандартно снять защиту с листа будет уже невозможно.

    Насколько стойкая защита листов в Excel
    К сожалению или счастью защита листов в Excel совершенно не стойкая ко взлому. Защита с листа, если пароль не известен, снимается на раз-два даже при помощи VBA. В надстройке MulTEx есть специальная команда, которая поможет снять защиту с листа, если пароль был забыт или утерян: Снять защиту с листа(без пароля).
    Но стоит учитывать тот факт, что защита листов изначально не планировалась как средство защиты своих расчетных алгоритмов и интеллектуальной собственности. Защита листов(как и книг) задумывалась как защита «от дурака» — т.е. дабы случайно или по неумению данные не были испорчены или удалены.
    Плюс Microsoft все же совершенствует Excel и с выходом новых версий происходят изменения и в области защиты, что не может не радовать. Например, защита листов и книг начиная с версии Excel 2013 уже более стойкая(для тех кто в теме: алгоритм SHA-512 в 2013 и выше против SHA1 в ранних версиях). Это значит, что простым брутфорсом поломать такую защиту хоть и можно, но времени на это уйдет уже гораздо больше. Хотя для снятия защиты с листов в открытых форматах(.xlsx,.xlsm и им подобных) возможно и другими методами.

    Статья помогла? Поделись ссылкой с друзьями!