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

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

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

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

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

… а теперь копируем её

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

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

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

Как в excel закрепить (зафиксировать) ячейку в формуле

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

У нас есть данные по количеству проданной продукции и цена за 1 кг, необходимо автоматически посчитать выручку.

Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2

Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.

Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.

Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2* B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3* B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/ $B$7 , вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.

Примечание: в рассматриваемом примере мы указал два значка доллара $ B $ 7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7 , встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B $ 7 (зафиксирована строка 7) или $ B7 (зафиксирован только столбец B)

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

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.

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

Способ A: Сохраняйте постоянную ссылку на ячейку формулы с помощью клавиши F4 Способ B: Сохраняйте постоянную ссылку на ячейку формулы всего за несколько кликов

Сохраняйте постоянную ссылку на ячейку формулы с помощью клавиши F4

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

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

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

В этом случае я не хочу, чтобы ссылка на ячейку A1 изменялась при перемещении формулы, поэтому я помещаю курсор на A1 в формуле и затем нажимаю F4. Смотрите скриншот:

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

Сохраняйте постоянную ссылку на ячейку формулы всего за несколько кликов

Здесь очень рекомендую Kutools for Excel‘s Преобразовать ссылки утилита. Эта функция помогает легко преобразовать все ссылки на формулы в большом количестве в выбранном диапазоне или нескольких диапазонах в конкретный тип ссылки на формулу. Например, преобразовать относительное в абсолютное, абсолютное в относительное и т. Д. Загрузите Kutools для Excel прямо сейчас! (30-дневная бесплатная трасса)

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

1. После установки Kutools for Excel, нажмите Kutools > Еще >  Преобразовать ссылки для активации Преобразование ссылок на формулы функцию.

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

• Выберите диапазон или несколько диапазонов (удерживайте Ctrl клавиша для выбора нескольких диапазонов один за другим) вы хотите сделать ссылки постоянными;
• Выберите К абсолютному вариант;
• Нажмите OK кнопку.

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

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

• Вы здесь:  
• Главная
• Надстройки
• Надстройки Excel
• Как закрепить ячейки в формулах Excel?

Информация о материале

Категория: Надстройки Excel

Опубликовано: 19 февраля 2019

Адрес ячейки на листе рабочей книги Excel определяется двумя координатами, названием или номером столбца (в зависимости от выбранного стиля ссылок) и номером строки. Для закрепления ячеек в формулах используется символ $. Подстановка этого символа перед названием или номером столбца фиксирует столбец, перед номером строки – фиксирует строку, а перед каждой координатой ячейки – закрепляет ячейку. Речь в этой публикации пойдет о способах изменения типа ссылок в ячейках с формулами.

Как закрепить нужный диапазон в Excel?

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

1. Отметить можно только те строчки или колонки, которые находятся соответственно вверху и слева. Если они расположены в середине рабочего листа, то применить к ним это условие не получится.
2. Процедура будет недоступной, если лист защищен или используется режим редактирования ячейки (для выхода из этого режима достаточно нажать клавишу «Esc» или «Enter»).

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

Как закрепить столбец или ряд в Excel 2003?

Фиксация полей в разных версиях Excel делается примерно по одному и тому же принципу, но все же этот процесс немного отличается. Первый пример будет на основе версии 2003 года. Для начала необходимо запустить программу, а затем выполнить следующие команды в панели меню: «Окно — Разделить». Теперь рабочий лист будет разделен на 4 ровные части. С помощью этих 2 линий (вертикальной и горизонтальной) и будет осуществляться выбор диапазона для фиксации.

Итак, чтобы зафиксировать шапку в электронной таблице, надо перетянуть эти 2 линии в нужное положение. К примеру, можно указать диапазон из первых 2 строк и 10 колонок. Чтобы его зафиксировать, необходимо выбрать в панели меню команды «Окно — Закрепить области». После этого 2 линии больше нельзя будет передвигать. Вот таким несложным способом получилось в Exel зафиксировать шапку. Теперь при прокрутке таблицы в любую сторону указанные поля всегда будут находиться в верхней части документа (они будут подчеркнуты черными линиями снизу и справа).

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

Существует еще один, более простой, способ решения этой проблемы. В данном случае нужно всего лишь щелкнуть на ячейку, размещенную ниже и справа от того диапазона, который всегда должен отображаться в верхней части документа. А затем надо выбрать команду «Закрепить». Например, если требуется отметить первые 3 колонки (A, B, C) и первые 2 ряда, то для фиксации понадобится выбрать поле D4 и кликнуть на вышеуказанную команду.

Как зафиксировать диапазон в Excel 2007 и 2010?

Закрепление области в Excel происходит из меню «Вид» — необходимо выбрать меню «Закрепить область», и выбрать тип фиксируемого диапазона

Чтобы зафиксировать поля в версии 2007 и 2010, надо перейти на вкладку «Вид» и в группе «Окно» щелкнуть на кнопку «Закрепить области». Откроется список, в котором можно указать, что именно надо зафиксировать — строчку, столбец или область.

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

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

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

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

Как закрепить строку в excel, столбец и область

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

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

В экселе можно закрепить строку можно двумя способами. При помощи инструмента «Закрепить верхнюю строку» и «Закрепить области».

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

Чтобы закрепить какую либо строку и сделать ее неподвижной при прокрутке всего документа:

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

   Для закрепления строки, выделите строку, которая ниже закрепляемой

2. Перейдите во вкладку «Вид» вверху на панели инструментов и в области с общим названием «Окно» нажмите на «Закрепить области». Тут же находятся инструменты «Закрепить верхнюю строку» и «Закрепить первый столбец». После нажатия моя строка 1 с заголовком «РАСХОДЫ» и месяцами будет неподвижной при прокрутке и заполнении нижней части таблицы.

Инструмент для закрепления строки, столбца и области

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

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

Выделил 4-ю строку и нажал на «Закрепить области».

Чтобы закрепить три первые строки, выделите четвертую и нажмите на «Закрепить область»

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

Результат закрепления трех первых строк

Чтобы снять любое закрепление со строк, столбцов или области, нажмите на «Закрепить области» и в выпадающем списке вместо «Закрепить области» будет пункт меню «Снять закрепление областей».

Чтобы снять любое закрепление (строк, столбцов или области) нажмите на «Снять закрепление областей»

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

Я для закрепления столбца с названием проектов (это столбец B), выдели следующий за ним (это C) и нажал на пункт меню «Закрепить области». Результатом будет неподвижная область столбцов A и B при горизонтальной прокрутке. Закрепленная область в Excel выделяется серой линией.

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

Использование инструмента закрепить область в excel

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

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

Для такого вида закрепления используется тот же инструмент «Закрепить области», только отличается способ указания области для закрепления.

1. В моем примере мне для фиксации при прокрутке необходимо оставить неподвижной все что слева от столбца C и все что выше строки 4. Для этого выделите ячейку, которая будет первая ниже и правее этих областей. В моем случае это ячейка C4.
2. Во вкладке «ВИД» нажмите «Закрепить области» и в выпадающем меню одноименную ссылку «Закрепить области».
3. Результатом будет закрепленные столбцы и строки.

Результат закрепления области столбцов и строк

Как в Экселе закрепить строку и столбец одновременно?

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

Зафиксировать строку и столбец в Экселе можно следующим образом:

Выделить курсором мыши одну ячейку (не столбец, не строку), находящуюся под пересечением закрепляемых столбца и строки. Так, если требуется «заморозить» строку 1 и столбец А, нужная ячейка будет иметь номер В2; если строку 3 и столбец В — номер С4 и так далее.

В той же вкладке «Вид» (в меню «Закрепить области») кликнуть мышью по пункту «Закрепить области».

Можно убедиться: при перемещении по таблице на месте будут сохраняться как строки, так и столбцы.

СРОЧНО! ! !как в Эксель 2007 в формуле закрепить значение ячейки в формуле (=RC[-2]/R[-2]C[1]) (значение делителя)

В Эксель 2003 столбцы — цифровые, столбцы — буквенные. В формуле при необходимости можно было закрепить ячейку или привязаться к столбцу (строке) и при копировании формулы и смене значения в ячейке менялись расчёты (путём правильного становления знака доллара) . Кто работал с таблицами и расчётами —поймёт. А как в Эксель 2007???

Boris b

Точно так же. Абсолютная ссылка называется.
F4 нажмите — он сам доллары проставит.
Или измените формат таблицы на обычный (буквенно-цифровой) . Это в параметрах Екселя.
Параметры — формулы — работа с формулами. Снимите чекбокс стиль ячеек R1C1.

Полосатый жираф алик

RC — означает «двумя строками выше и одной колонкой правей текущей». При копировании такой формулы в ней ничего не меняется. И потому «на автомате» происходит смещение. А вот R20C3 обозначает абсолютный адрес ячейки в 20 строке и колонке «С».

39 полезных горячих клавиш в Excel

F4: повторение последней команды или действия, если это возможно

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

CTRL + ALT + F9: калькулирует все ячейки в файле

F11: создать диаграмму

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

ALT: отобразит подсказки клавиш на ленте

Нажмите клавишу Alt на клавиатуре и над пунктами меню в ленте инструментов Excel появятся буквы. Нажав эти буквы на клавиатуре Вы моментально активируете нужный вам пункт меню ленты.

ALT+: сумма выделенного диапазона с данными

Если у Вас есть диапазон данных, который вы хотите суммировать – нажмите сочетание клавиш Alt и + и в следующей после диапазона ячейке появится сумма выделенных Вами значений.

ALT + Enter: начать новую строку внутри ячейки

Это сочетание клавиш будет полезно тем, кто пишет много текста внутри одной ячейки. Для большей наглядности текста – его следует разбивать на абзацы или начинать с новых строк. Используйте сочетание клавиш Alt + Enter и Вы сможете создавать строки с текстом внутри одной ячейки.

CTRL + Page Up: перейти на следующий лист Excel

CTRL + Page Down: перейти на предыдущий лист Excel

CTRL + ‘: демонстрация формулы

CTRL + Backspace: демонстрация активной ячейки

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

CTRL + Shift + #: смена формата ячейки на формат даты “день, месяц, год”

CTRL + K: вставить гиперссылку

CTRL + Shift + $: сменить формат ячейки на денежный

CTRL + Shift + &: добавить внешние границы ячейки

CTRL + B: выделить жирным

CTRL + I: выделить курсивом

CTRL + U: сделать подчеркивание

CTRL + S: быстрое сохранение

Быстро сохранить изменения Вашего файла Excel можно с помощью этого сочетания.

CTRL + C: скопировать данные

CTRL + V: вставить данные

CTRL + X: вырезать данные

CTRL + Shift +

: назначить общий формат ячейки

Таким образом можно назначить общий формат выделенной ячейки.

CTRL + Shift + %: задать процентный формат ячейки

CTRL + Shift + ^: назначить экспоненциальный формат ячейки

CTRL + Shift + @: присвоить стиль даты со временем

CTRL + Shift + !: назначить числовой формат ячейки

Так Вы сможете присвоить числовой формат к любой ячейке.

CTRL + F12: открыть файл

CTRL + Пробел: выделить данные во всем столбце

CTRL + ]: выделить ячейки с формулами

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

Система выделит ячейки ссылающиеся в формулах на другие ячейки.

CTRL + ;: вставить текущую дату

CTRL + Shift + ;; вставить текущее время

CTRL + A: выделить все ячейки

CTRL + D: скопировать уравнение вниз

CTRL + F: Поиск

CTRL + H: поиск и замена

Shift + Пробел: выделить всю строку

CTRL + Shift + Стрелка вниз (вправо, влево, вверх): выделить ячейки со сдвигом вниз

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

CTRL + Стрелка вниз (вправо, влево, вверх): быстрое перемещение по данным

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

Я думаю, Вам пригодятся эти 39 самых полезных горячих клавиш в Excel.

Замена формулы на ее результат

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

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

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

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

Замена формул с помощью вычисляемых значений

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

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

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

Выбор диапазона, содержащего формулу массива

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите кнопку ” копировать “.

Нажмите кнопку вставить .

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

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

Нажмите клавишу F2, чтобы изменить значение в ячейке.

Нажмите клавишу F9 и нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение оно будет отображено как 1932,322 в строке формул

Обратите внимание, что 1932,322 является фактическим вычисленным значением, а 1932,32 — значением, которое отображается в ячейке в денежном формате

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

Замена части формулы значением, полученным при ее вычислении

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

При замене части формулы на ее значение ее часть не может быть восстановлена.

Щелкните ячейку, содержащую формулу.

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

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

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

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

Как в excel закрепить (зафиксировать) ячейку в формуле

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

У нас есть данные по количеству проданной продукции и цена за 1 кг, необходимо автоматически посчитать выручку.

Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2

Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.

Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.

Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2* B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3* B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/ $B$7 , вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.

Примечание: в рассматриваемом примере мы указал два значка доллара $ B $ 7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7 , встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B $ 7 (зафиксирована строка 7) или $ B7 (зафиксирован только столбец B)

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

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.

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

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

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

Как зафиксировать значение ячейки в Excel?

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

Rafail

Делается это так. Ставите курсор на нужную ячейку, затем заходите в меню «Главная», там «кликаете» значок «копировать» (изображение двух листочков с загнутым уголком), затем «кликаете» треугольник под кнопкой «вставить» (но не саму кнопку). Открывается небольшой список команд. Далее два варианта.

Первый: «кликаете» на команду «Вставить значения»,

Второй: «кликаете» на команду «Специальная вставка», и в открывшемся окне кликаете на команду «значения», и затем на «ОК».

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

Искатель приключений 2000

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

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

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

Выделите ячейки, которые хотите превратить в значения. Нажмите правую клавишу вашей мышки, и выскочит меню, нажмите «копировать». Снова нажмите правую кнопку мыши, снова выйдет меню, но теперь выберите пункт «специальная ставка». Перед вами появится таблица «Специальная ставка», поставьте точку напротив надписи «значения» и жмите ОК.

Darcy

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

Проповедник

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

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

Ovasiliev

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

1) Правой кнопкой клик по ячейке — вылетает контекстное меню.

2) Левой кнопкой клик — выбираем «копировать».

3) Правой кнопкой клик по ячейке — вылетает контекстное меню.

4) Левой кнопкой клик — выбираем из значков в меню значок с цифрами, или же можно раскрыть дальше подменю «Специальная вставка» и выбрать «Значение».

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

bolshoyvopros.ru

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

Полная фиксация ячейки

Полная фиксация ячейки — это когда закрепляется значение по вертикали и горизонтали (пример, $A$1), здесь значение никуда не может сдвинутся, так называемая абсолютная формула. Очень удобно такой вариант использовать, когда необходимо ссылаться на значение в ячейке, такие как курс валют, константа, уровень минимальной зарплаты, расход топлива, процент доплат, кофициент и т.п.

В примере у нас есть товар и его стоимость в рублях, а нам нужно узнать он стоит в вечнозеленых долларах. Поскольку, обменный курс у нас постоянная ячейка D1, в которой сам курс может меняться исходя из экономической ситуации страны. Сам диапазон вычисление находится от E4 до E7. Когда мы в ячейку Е4 пропишем формулу =D4/D1, то в результате копирования, ячейки поменяют адреса и сдвинутся ниже, пропуская, так необходимый нам обменный курс. А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;