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

Мы собираемся заблокировать только те формулы в столбце H.

Шаг 2. Здесь у нас есть две цели: заблокировать только формулы. Во-первых, мы должны разблокировать целые ячейки рабочего листа. Во-вторых, мы будем защищать только формулы. Итак, теперь выберите всю таблицу, как показано на рисунке выше. Теперь перейдите на вкладку «Главная» и в группе «Выравнивание» выберите маленькую стрелку, чтобы открыть диалоговое окно «Формат ячеек».

Шаг 3. На вкладке "Защита" снимите флажок "Заблокировано" и нажмите "ОК".

Шаг 4. Перейдите на вкладку "Главная". В группе «Редактирование» нажмите кнопку «Найти и выбрать» и выберите «Перейти к специальному».

Шаг 5. В диалоговом окне Перейти к специальному установите переключатель «Формулы». Это установит флажки для всех типов формул. Теперь нажмите ОК.

Шаг 6: теперь выбраны только те ячейки, которые будут содержать формулу. Теперь снова заблокирована только эта выбранная формула из диалогового окна «Формат ячеек», следуя предыдущим шагам. Теперь на вкладке "Обзор" на ленте выберите группу "Защитить лист от изменений".

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

Советы: В диалоговом окне «Защитить лист» вы можете использовать пароль в текстовом поле «Пароль для снятия защиты с листа ». Таким образом, когда кто-то попытается снять защиту с этого, он не сможет снять защиту без предоставленного вами пароля. Только вы можете снять защиту или разблокировать эту формулу. Так что будьте осторожны и запомните свой пароль, чтобы снять защиту с этой формулы.

Шаг 8: Нажмите на любую ячейку столбца H, содержащую формулу. Появится сообщение, подобное изображенному выше. Но вы можете редактировать другую ячейку. Это простой способ скрыть и заблокировать определенные ячейки, содержащие формулы в Excel.

Советы. Если вы хотите снова снять с него защиту, просто выберите вкладку «Обзор», затем нажмите «Снять защиту» и введите заданный пароль. Тогда он снова будет для вас незащищенным.Если вы хотите узнать больше о Советы и рекомендации по Excel. Ознакомьтесь с нашим последним Курсом Excel Ninja, который предлагает высококачественные видео с пожизненным доступом.

Теперь мы успешно заблокировали наши формулы в Excel!

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

Несмотря на то, что это делает создание формул в Excel удобным, у него есть и несколько недостатков.

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

Теперь вам повезет, если вы сможете обнаружить ошибку и исправить ее. Но если это не так, это может привести к ошибочным результатам.

И позвольте мне сказать вам, что ошибки в Excel обходятся компаниям в миллионы (прочитайте это или это).

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

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

Лучшим решением этой проблемы является блокировка только тех ячеек, в которых есть формулы.

Это руководство охватывает:

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

Прежде чем я покажу вам, как заблокировать формулы в Excel, вот что вы должны знать:

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

Вот шаги, чтобы заблокировать формулы в Excel (подробно объяснены позже):

1. Выберите все ячейки и разблокируйте их.
2. Выберите все ячейки с формулами (используя Перейти к специальному).
3. Заблокировать выбранные ячейки.
4. Защитите лист.

Теперь, когда я описал описанные выше шаги, давайте углубимся и посмотрим, как это сделать (и, что более важно, почему мы должны это делать):

Шаг 1. Выберите все ячейки и разблокируйте их

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

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

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

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

Вот шаги, чтобы разблокировать все ячейки:

Шаг 2. Выделите все ячейки с формулами

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

Для этого нам нужно сначала выделить все ячейки с формулами.

Вот шаги, чтобы выбрать все ячейки с формулами:

При этом будут выбраны все ячейки, в которых есть формулы.

Шаг 3. Заблокируйте ячейки формулами

Теперь, когда мы выбрали ячейки с формулами, нам нужно вернуться и заблокировать эти ячейки (включить свойство блокировки, которое мы отключили на шаге 1).

Как только мы это сделаем, защита рабочего листа заблокирует ячейки с формулами, но не другие ячейки.

Вот шаги, чтобы заблокировать ячейки с формулами:

Шаг 4. Защитите рабочий лист

Теперь, когда свойство «Заблокировано» включено для ячеек с формулами (а не для других ячеек), защита всего листа ограничит доступ только к ячейкам с формулами.

Вот шаги по защите рабочего листа:

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

Если пользователь попытается изменить ячейки, он/она получит подсказку, как показано ниже:

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

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

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

Хотя в большинстве случаев это не проблема, но если вы не хотите, чтобы формула была видна, ее нужно скрыть.

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

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

Примечание. Как упоминалось ранее, незаблокированная ячейка не может быть защищена. То же самое относится и к скрытию формул в Excel. Если ячейка не заблокирована, только установка флажка «Скрытый» ничего не даст. Чтобы действительно скрыть формулы в Excel, в ячейках должны быть установлены флажки Заблокировано и Скрытые, а затем они должны быть защищены.

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

БЕСПЛАТНАЯ КНИГА EXCEL

Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу

34 мысли о «Как заблокировать формулы в Excel (пошаговое руководство)»

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

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

Спасибо за отличный урок! следуйте инструкциям, и это сработает.

Отличное видео, спасибо!

Учебное пособие по Exel. Очень хорошо изложено, объяснено и проиллюстрировано. Настоятельно рекомендуется. Спасибо.

Я ввожу одну и ту же формулу в ячейки C1, C2 и C3. Например:

В понедельник ввожу в А1: 12
Ввожу в В2: 10
Получаю ответ 120 в ячейках С1, С2 и С3.
Идеально.
Во вторник у меня новый набор чисел для другого результата.
Но мне нужно сохранить ответ 120 с понедельника в ячейке C1.
Как я могу заблокировать эту ячейку, чтобы при вводе новых данных в ячейки A1 и B2 не менялся ответ предыдущего дня в ячейке C1 (120), а только вводились новые данные в ячейку C2 и С3?

Спасибо, очень круто и легко!

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

Как только вы заблокируете формулы, блокируется весь лист, и я не могу вводить цифры. Итак, у меня есть формула итога=СУММ(O6:T6). Мне нужно, чтобы люди вводили свои прогнозы в ячейки O6:T6. Когда я блокирую лист и формулы, я не могу поместить цифры в другие ячейки. Пожалуйста, посоветуйте

Привет, Джейшри! Тот же вопрос к тебе. Вы нашли способ решить эту проблему?

Это отлично сработало для меня, я следовал приведенным выше указаниям и заблокировал только ячейки с формулами. Если в ячейке есть формула, никто не должен вводить в эту ячейку какую-либо информацию. следуя приведенной выше логике, при вводе в O6: T6 заблокированная ячейка должна быть s6 с формулой. Магия заключалась в поиске только формул.

Дорогой, как мы можем исключить ячейку из общего количества; в этой ячейке также есть формула для расчета, например =300000-(50000+10000)

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

Есть ли способ заблокировать ячейки, но по-прежнему вставлять строки С ФОРМУЛОЙ АВТОЗАПОЛНЕНИЯ? У меня есть таблица бюджета со столбцами A-J и строками 3-33. Столбцы с полями ввода включают:
B — Средняя стоимость для строки 1, 2, 3…
C — Вариант «фиксированная» или «переменная» стоимость
D — Диапазон отклонения при переменной стоимости

Столбцы EJ включают:
E — максимальная стоимость для строки 1, 2, 3…
F — минимальная стоимость для строки 1, 2, 3…
G — строки разделены на « группы», чтобы указать категорию бюджета. Столбец G суммирует общие затраты по категории
H — рассчитывает % бюджетных пособий ИЛИ общие затраты
I, J — вычисляет указанный выше процент, используя расчеты максимальной/минимальной стоимости

Каждой группе/категории в настоящее время выделено четыре строки, но пользователям может потребоваться добавить строки, если в этой категории больше затрат. (Пример: в отношении расходов на жилье у меня может быть ряд из-за 1) ипотеки 2) электричества 3) коммунальных услуг 4) технического обслуживания 5) фонда проекта X 6) безопасности и т. д.). Столбцы E–J — это все формулы, которые не нужно трогать, но которые нужно «перенести вниз», если пользователь добавляет строку.

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

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

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

Понравилось видео — очень полезно.

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

Это «скрытая» функция?

Привет, это простой способ понять. Я также хотел бы знать, как заблокировать формулы на нескольких листах одновременно, которые находятся в одной рабочей книге. Как в одной трудовой книжке, лист 1, лист 2,….. лист 30.

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

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

Для перетаскивания или копирования ссылок на формулы и блокировки значения ячейки необходимо сначала преобразовать все ссылки на ячейки в формулах в абсолютные. Нажмите Kutools > Конвертировать ссылки. Утилита Kutools for Excel Преобразование ссылок на формулы помогает вам легко конвертировать все ссылки на формулы оптом в выбранном диапазоне, например конвертировать все относительные значения в абсолютные сразу в Excel. Смотрите скриншот ниже:

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 60 дней. Загрузите бесплатную пробную версию сейчас

• Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
• Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
• Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
• Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
• Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Перетащите или скопируйте формулу и заблокируйте значение ячейки с помощью клавиши F4

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

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

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

Перетащите или скопируйте формулу и заблокируйте значение ячейки с помощью Kutools for Excel

Утилита Convert Refers Kutools for Excel может не только сразу преобразовать все ссылки на ячейки одной ячейки в абсолютные, но и сразу преобразовать все ссылки на ячейки диапазона ячеек формулы в абсолютные. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>2. В диалоговом окне "Преобразование ссылок на формулы" выберите параметр "В абсолютные" и нажмите кнопку "ОК".

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

Если вы хотите получить бесплатную пробную версию (30 дней) этой утилиты, нажмите, чтобы загрузить ее, а затем перейдите к выполнению операции в соответствии с указанными выше шагами.

Точно скопируйте ячейку формулы с помощью Kutools for Excel

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

<р>2. В диалоговом окне «Точное копирование формулы» установите флажок «Копировать форматирование» и нажмите кнопку «ОК».

<р>3. Во втором диалоговом окне «Точное копирование формулы» выберите пустую ячейку для вставки скопированных ячеек формулы, а затем нажмите кнопку «ОК». Смотрите скриншот:

Затем выбранные ячейки формулы вставляются в новый диапазон без изменения какой-либо относительной ссылки на ячейку. Смотрите скриншот:

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

Уровень навыков: начинающий

Видеоруководство

Загрузить файл Excel

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

Блокировка ячеек и защита листа — пример цитаты.xlsx (23,1 КБ)

Защита вашей работы от нежелательных изменений

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

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

1. Блокировка/разблокировка ячеек.
2. Защита листа.

Вот как запретить пользователям изменять некоторые ячейки.

Шаг 1. Заблокируйте и разблокируйте определенные ячейки или диапазоны

Щелкните правой кнопкой мыши ячейку или диапазон, который вы хотите изменить, и выберите «Формат ячеек» в появившемся меню.

Откроется окно «Формат ячеек» (сочетание клавиш для этого окна — Ctrl + 1 .). Выберите вкладку с надписью Защита.

Затем убедитесь, что установлен флажок "Заблокировано".

Заблокировано — это параметр по умолчанию для всех ячеек в новом листе/книге.

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

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

В разделе "Бонусы" ниже я делюсь некоторыми советами, чтобы ускорить этот процесс.

Шаг 2. Защитите рабочий лист

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

Чтобы защитить лист, просто щелкните правой кнопкой мыши вкладку в нижней части листа и выберите в меню пункт "Защитить лист…".

Откроется окно защиты листа. Если вы хотите, чтобы ваш лист был защищен паролем, у вас есть возможность ввести пароль здесь. Добавление пароля не является обязательным. Нажмите "ОК".

Если вы решили ввести пароль, вам будет предложено подтвердить ввод после того, как вы нажмете OK.

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

Вы можете снять защиту с листа так же, как вы его защитили, щелкнув правой кнопкой мыши вкладку листа. Альтернативным способом защиты и снятия защиты с листов является использование кнопки "Защитить лист" на вкладке "Обзор" ленты.

Текст кнопки отображает противоположное текущему состоянию. Он говорит «Защитить лист», если лист не защищен, и «Снять защиту листа», если он защищен.

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

3 дополнительных совета по блокировке ячеек и защите листов

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

1. Запретить выделение заблокированных ячеек

Этот совет поможет вашим пользователям быстрее и проще вводить данные в таблицу.

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

Чтобы внести это изменение, просто снимите флажок "Выбрать заблокированные ячейки" в окне "Защитить лист".

После нажатия OK вы сможете выбирать только разблокированные ячейки.

2. Добавьте кнопку блокировки ячеек на панель быстрого доступа

Это позволяет быстро увидеть заблокированные настройки для ячейки или диапазона.

На вкладке "Главная" на ленте вы можете открыть раскрывающееся меню под кнопкой "Формат" и увидеть параметр "Блокировать ячейку".

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

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

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

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

Смешанное состояние блокировки

Если вы выберете диапазон, содержащий как заблокированные, так и разблокированные ячейки, вы увидите сплошной флажок для флажка "Заблокировано" в окне "Формат ячеек". Это означает смешанное состояние.

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

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

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

Возможно, вам интересно, где я нашел этот шаблон для цитаты. Я взял его из библиотеки шаблонов.Вы можете получить доступ к библиотеке, перейдя на вкладку «Файл», выбрав «Создать» и используя поисковое слово «цитата».

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

Заключение

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

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

Читайте также:

  
• Программа для фортепиано для компьютера
  
• Как убрать полужирный шрифт в Word
  
• Как вставить координаты в автокад из блокнота
  
• Autocad зависает при нажатии f8
  
• Программное обеспечение Fujifilm для компьютера