Как закрепить ячейку в формуле в Excel
Обновлено: 03.07.2024
Как заблокировать формулы в Excel? Защитить формулу в Excel? Смущенный? Вот блог, чтобы узнать, как заблокировать формулы Excel.
Представьте, что ваш начальник хочет, чтобы вы защитили книгу, но она также хочет иметь возможность изменить несколько ячеек после того, как вы закончите. Итак, прежде чем защитить паролем книгу (или рабочий лист), вы разблокировали некоторые ячейки. Теперь, когда ваш босс закончил, вы можете запирать камеры.
Подумайте, можете ли вы защитить свои ячейки, содержащие формулы?
Ответ: Да!
Самый распространенный способ предотвратить несанкционированное вмешательство в ваши формулы Excel – защитить лист.
Как заблокировать формулы в Excel (пошаговое руководство)
Вот шаги, чтобы заблокировать формулы в Excel (подробно объяснены позже):
Шаг 1. Выберите ячейку с формулами, которые вы хотите заблокировать, и нажмите Ctrl + 1
Шаг 2. В диалоговом окне форматирования ячеек выберите вкладку "Защита".
Шаг 3. Установите флажок «Заблокировано» в Excel
Шаг 4. Нажмите "ОК" и "Применить"
Давайте посмотрим, как заблокировать формулы в Excel, выполнив следующие действия!
Шаг 1. Создайте таблицу, как показано на рисунке выше. В этой таблице показаны оценки учащихся по двум предметам: математике и географии. В ячейках столбца H мы использовали формулу, которая вычисляет суммарные оценки каждого учащегося по этим двум предметам. Скажем, в ячейке H2 мы использовали приведенную ниже формулу:
Мы собираемся заблокировать только те формулы в столбце H.
Шаг 2. Здесь у нас есть две цели: заблокировать только формулы. Во-первых, мы должны разблокировать целые ячейки рабочего листа. Во-вторых, мы будем защищать только формулы. Итак, теперь выберите всю таблицу, как показано на рисунке выше. Теперь перейдите на вкладку «Главная» и в группе «Выравнивание» выберите маленькую стрелку, чтобы открыть диалоговое окно «Формат ячеек».
Шаг 3. На вкладке "Защита" снимите флажок "Заблокировано" и нажмите "ОК".
Шаг 4. Перейдите на вкладку "Главная". В группе «Редактирование» нажмите кнопку «Найти и выбрать» и выберите «Перейти к специальному».
В диалоговом окне
Шаг 5. В диалоговом окне Перейти к специальному установите переключатель «Формулы». Это установит флажки для всех типов формул. Теперь нажмите ОК.
Шаг 6: теперь выбраны только те ячейки, которые будут содержать формулу. Теперь снова заблокирована только эта выбранная формула из диалогового окна «Формат ячеек», следуя предыдущим шагам. Теперь на вкладке "Обзор" на ленте выберите группу "Защитить лист от изменений".
Шаг 7. Появится диалоговое окно Защитить лист. Отметьте «Выбрать заблокированную ячейку» и «Выбрать незаблокированную ячейку». Теперь нажмите ОК.
Советы: В диалоговом окне «Защитить лист» вы можете использовать пароль в текстовом поле «Пароль для снятия защиты с листа ». Таким образом, когда кто-то попытается снять защиту с этого, он не сможет снять защиту без предоставленного вами пароля. Только вы можете снять защиту или разблокировать эту формулу. Так что будьте осторожны и запомните свой пароль, чтобы снять защиту с этой формулы.
Шаг 8: Нажмите на любую ячейку столбца H, содержащую формулу. Появится сообщение, подобное изображенному выше. Но вы можете редактировать другую ячейку. Это простой способ скрыть и заблокировать определенные ячейки, содержащие формулы в Excel.
Советы. Если вы хотите снова снять с него защиту, просто выберите вкладку «Обзор», затем нажмите «Снять защиту» и введите заданный пароль. Тогда он снова будет для вас незащищенным.Если вы хотите узнать больше о Советы и рекомендации по Excel. Ознакомьтесь с нашим последним Курсом Excel Ninja, который предлагает высококачественные видео с пожизненным доступом.
Теперь мы успешно заблокировали наши формулы в Excel!
Работа в Excel — это действительно отличный опыт. С помощью Excel можно выполнять множество математических операций. Практически все операции имеют свою уникальную формулу, и все ячейки имеют свои определенные формулы. Представьте, что вы работаете с электронной таблицей Excel и по ошибке удаляете ячейку или по ошибке нажимаете кнопку. Это может привести к серьезной ошибке в вашей работе, если вы не определите ее заранее. Это подчеркивает причины, по которым вам необходимо блокировать и защищать свои ячейки.
Теперь давайте взглянем на простые формулы, которые заблокируют ваши ячейки, чтобы они не смещались, не корректировались и не становились ошибочными, когда вы по ошибке нажимаете.
Ваши ячейки могут корректироваться, когда вы вставляете данную формулу в другие места документа или в другие ячейки. Вы можете ограничить это, добавив $ перед строкой или значением столбца. Процесс добавления знака $ перед строками или столбцами называется привязкой и совместим со всеми версиями Excel.
Предположим, вы хотите заблокировать ячейку A1.
При разблокировке у нас может быть формула:
=СУММ(A2: B2)
Вы можете добавить $, чтобы заблокировать ячейку A1. При блокировке у нас будет:
=СУММ($A$2:B2)
Теперь вы можете привязать строку или столбец. В приведенной выше формуле мы привязали и строку, и столбец, но вы можете привязать их по отдельности, добавив $ к единице.
=SUM($A1: B2) имеет привязанную строку, так как $ добавлен перед A.
Столбец=SUM(A$1:B2) имеет привязанный столбец, так как $ добавляется после A
Обратите внимание, что $ очень эффективно блокирует ячейки, когда они вводятся вручную. Ваша электронная таблица должна использовать стандартную нотацию строки/столбца A1. Вы можете быстро переключаться между блокировкой столбца, строки или всего отсека ячейки, нажимая клавишу F4. Нажимая клавишу F4, вы будете переключаться между четырьмя возможными вариантами блокировки: A1, $A1, $A$1 и A$1.
Теперь рассмотрим запись: A1:B2.
Если вы поместите курсор в конец массива и нажмете F4, все значения вашего массива будут немедленно заблокированы — A1B2, $A$1:$B$2, A$1: B$2, $A1:$B2.< /p>
В целом, вы не сможете защитить свою камеру, если не сможете ее заблокировать. Следуйте приведенным выше инструкциям, и вы сможете заблокировать и защитить свои ячейки. Спасибо за прочтение!
Вы можете легко изменить формулу в строке формул или непосредственно в ячейке.
Несмотря на то, что это делает создание формул в Excel удобным, у него есть и несколько недостатков.
Вы просматриваете рабочий лист, полный формул, и случайно нажимаете клавишу удаления, клавишу возврата или какую-либо другую цифровую/алфавитную клавишу.
Теперь вам повезет, если вы сможете обнаружить ошибку и исправить ее. Но если это не так, это может привести к ошибочным результатам.
И позвольте мне сказать вам, что ошибки в Excel обходятся компаниям в миллионы (прочитайте это или это).
Вероятность таких ошибок многократно возрастает, когда вы делитесь файлом с коллегами, руководителями или клиентами.
Один из способов предотвратить это — заблокировать рабочий лист и все ячейки. Однако это помешает пользователю вносить какие-либо изменения в рабочий лист. Например, если вы отправляете книгу своему руководителю на проверку, вы можете разрешить ему добавлять свои комментарии или изменять некоторые ячейки.
Лучшим решением этой проблемы является блокировка только тех ячеек, в которых есть формулы.
Это руководство охватывает:
Как заблокировать формулы в Excel
Прежде чем я покажу вам, как заблокировать формулы в Excel, вот что вы должны знать:
По умолчанию все ячейки в Excel заблокированы. Только когда вы защищаете заблокированные ячейки, вы действительно можете запретить пользователю вносить изменения. Это также означает, что если ячейка не заблокирована и вы ее защитите, пользователь сможет вносить изменения.
Вот шаги, чтобы заблокировать формулы в Excel (подробно объяснены позже):
- Выберите все ячейки и разблокируйте их.
- Выберите все ячейки с формулами (используя Перейти к специальному).
- Заблокировать выбранные ячейки.
- Защитите лист.
Теперь, когда я описал описанные выше шаги, давайте углубимся и посмотрим, как это сделать (и, что более важно, почему мы должны это делать):
Шаг 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, если хотите защитить ячейки от редактирования.
Заблокировать все ячейки
По умолчанию все ячейки заблокированы. Однако блокировка ячеек не действует, пока вы не защитите лист.
<р>1. Выберите все ячейки.
Все ячейки заблокированы. Чтобы снять защиту листа, щелкните правой кнопкой мыши вкладку листа и выберите «Снять защиту листа». Пароль для загружаемого файла Excel — «easy».
Заблокировать определенные ячейки
Чтобы заблокировать определенные ячейки в Excel, сначала разблокируйте все ячейки. Затем заблокируйте определенные ячейки. Наконец, защитите лист.
<р>1. Выберите все ячейки.
Опять же, блокировка ячеек не действует, пока вы не защитите рабочий лист.
Ячейки A1 и A2 теперь заблокированы. Для редактирования этих ячеек необходимо снять защиту с листа. Пароль для загружаемого файла Excel — «easy». Вы по-прежнему можете редактировать все остальные ячейки.
Заблокировать ячейки с формулами
Чтобы заблокировать все ячейки, содержащие формулы, сначала разблокируйте все ячейки. Затем заблокируйте все ячейки с формулами. Наконец, защитите лист.
<р>1. Выберите все ячейки.
Excel выделяет все ячейки с формулами.
Примечание. Если вы также установите флажок "Скрытый", пользователи не смогут видеть формулу в строке формул при выборе ячейки A2, B2, C2 или D2.
Опять же, блокировка ячеек не действует, пока вы не защитите рабочий лист.
Все ячейки формул теперь заблокированы. Для редактирования этих ячеек необходимо снять защиту с листа. Пароль для загружаемого файла Excel — «easy». Вы по-прежнему можете редактировать все остальные ячейки.
Читайте также: