Как зафиксировать формулу в excel

Как зафиксировать ссылку в Excel? | Что важно знать о

Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?

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

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

Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:

и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:

это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка '4' и столбец 'F'. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.

Если еще раз нажмете клавишу «F4», то ссылка станет $F4:

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

Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6:

Если зафиксирована только строка (R), то ссылка будет R4C[-1]

Если зафиксирован только столбец (С), то ссылка будет иметь вид RC6

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

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

Присоединяйтесь к нам!

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

Novo Forecast Lite - автоматический расчет прогноза в Excel.
4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

Добавить комментарий

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

Главная » Табличный редактор Excel » Как зафиксировать формулу в MS Excel

При работе с формулами MS Excel, особенно если таблица сложная, а формул много, весьма легко ошибиться. Одна из самых распространенных (и чаще всего фатальных) ошибок связана с копированием формул в другие ячейки. К примеру, создали мы заведомо рабочую формулу, и забыли о ней, работая с другими данными. Спустя время нам вновь понадобились старые расчеты, мы копируем ячейку со “старой” формулой и вставляем её на несколько ячеек “поближе”.

Создаем самую обычную формулу в Excel

И не замечаем, что “старая” формула вдруг стала “новой” – сместилось не только ячейка в которой выводился результат формулы, но и, на то же число ячеек, сместились исходные данные! Хорошо если “новые” ячейки не заполнены – тогда, увидев вместо результата “0”, мы поймем ошибку. А если заполнены, причем похожими данными? Так можно и доходы с расходами перепутать и долго оптом искать концы – формула-то работала правильно!

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

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

Фиксируем формулу в ячейке Excel

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

Обратите внимание:

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

Значок «доллара» позволяет зафиксировать ячейку в Excel

На практике эта особенность означает, что формулу можно зафиксировать только частично:

Если $ стоит только перед буквами – формула будет зафиксирована по горизонтали (по строкам).
Если $ стоит только перед числами – формула будет зафиксирована по вертикали (по столбцам)

Особенности фиксации формул в MS Excel

Конечно вам может показаться весьма утомительной процедура расстановки “долларов” на листе. Это действительно так, поэтому разработчики программы предусмотрели автоматизацию этой процедуры. Попробуйте при вводе формулы ячейку нажимать на клавиатуре кнопку F4. Вы заметите любопытную особенность:

Одно нажатие F4 при вводе формулы ставит значки доллара ко всем составляющим адреса ячейки (то есть для ячейки D4 это будет $D$4)
Два нажатия F4 при вводе формулы ставит значки доллара ТОЛЬКО перед цифрами адреса ячейки (для ячейки D4 это будет D$4)
Три нажатия F4 при вводе формулы ставит значки доллара ТОЛЬКО перед буквами составляющим адреса ячейки (для ячейки D4 это будет $D4)
Четыре нажатия F4 отменяют расстановку “долларов” и снимают фиксацию.

Теги: excel, продвинутое использование

Также вас может заинтересовать:

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

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

Формулы Excel легко создавать и редактировать в Excel.

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

Хотя это упрощает создание формул в Excel, у него также есть несколько недостатков.

Подумайте об этом.

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

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

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

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

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

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

В этом руководстве рассматриваются:

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

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

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

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

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

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

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

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

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

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

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

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

Выберите все ячейки на листе (используйте сочетание клавиш Control + A).
Используйте сочетание клавиш Control + 1 (удерживайте клавишу Control, а затем нажмите 1). Это откроет диалоговое окно форматирования ячеек.
В диалоговом окне форматирования ячеек выберите вкладку Защита.
Снимите флажок «Заблокировано».
Нажмите ОК.

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

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

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

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

Выберите все ячейки на листе (используйте Control + A).
Перейдите на главную страницу и в группе «Редактирование» нажмите «Найти и выбрать».
В раскрывающемся списке выберите Перейти к специальному.
В диалоговом окне "Перейти к специальному" выберите "Формулы".
Нажмите OK.

Это выберет все ячейки, в которых есть формулы.

Шаг 3: Блокировка ячеек с формулами

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

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

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

Выделив ячейки с формулами, нажмите Control + 1 (удерживайте клавишу Control, а затем нажмите 1).
В диалоговом окне форматирования ячеек выберите вкладку Защита.
Установите флажок «Заблокировано».
Нажмите ОК.

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

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

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

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

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

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

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

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

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

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

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

Выберите все ячейки на листе (используйте Control + A).
Перейдите на главную страницу и в группе «Редактирование» нажмите «Найти и выбрать».
В раскрывающемся списке выберите Перейти к специальному.
В диалоговом окне «Перейти к специальному» выберите «Формулы».
Нажмите OK. Это выделит все ячейки, в которых есть формулы.
Нажмите Control + 1 (удерживайте клавишу Control, а затем нажмите 1). Это откроет диалоговое окно форматирования ячеек.
В диалоговом окне «Формат ячеек» перейдите на вкладку «Защита».
Установите флажок Скрытый.
Нажмите OK.

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

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

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

Блокировка строк/столбцов с помощью областей фиксации Excel.
Как копировать и вставлять формулы в Excel без изменения ссылок на ячейки.
Показывать формулы в Excel вместо значений.
Как преобразовать формулы в значения в Excel.
Показать столбцы в Excel
Как заблокировать высоту строки и ширину столбца в Excel

7 шагов для блокировки формул в Excel (пошаговое руководство)

Как заблокировать формулы в Excel? Защитить формулу в Excel? Смущенный? Вот блог, чтобы узнать, как заблокировать формулы Excel.

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

Подумайте, возможно ли защитить ваши конкретные ячейки, содержащие формулы?

Ответ: Да!

Самый распространенный способ предотвратить вмешательство людей в ваши формулы Excel — защитить лист.

Формула Excel Vlookup – Справочник

Надоело загружать электронные книги с большим объемом текста или копией вставки?

Если Да , вам понравится это руководство по формулам Excel Vlookup — VLOOKUP, HLOOKUP, MATCH & INDEX.

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

Шаг 1: Выберите ячейку с формулами, которые вы хотите заблокировать, и нажмите Ctrl + 1
Шаг 2: В диалоговом окне форматирования ячеек выберите вкладку защиты.
Шаг 3: Установите флажок «Заблокировано» в Excel
Шаг 4: Нажмите «ОК» и «Применить»

Давайте посмотрим, как заблокировать формулы в Excel, выполнив следующие действия!

Шаг 1: Создайте таблицу, как показано на рисунке выше. В этой таблице показаны оценки учащихся по двум предметам: математике и географии. В ячейках столбца H мы использовали формулу, которая вычисляет суммарные оценки каждого учащегося по этим двум предметам. Скажем, в ячейке h3 мы использовали приведенную ниже формулу:

=B2+C2

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

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

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

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

15 Руководство по очистке данных Excel

Надоело загружать электронные книги с большим объемом текста или копией вставки? Если Да , вам понравится это руководство (43 страницы)

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

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

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

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

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

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

Blender уроки

Категории