Андрей Смирнов
Время чтения: ~26 мин.
Просмотров: 0

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

Категории функций Эксель

Функции, встроенные в Excel, сгруппированы в несколько категорий:

  • Финансовые позволяют производить вычисления, используемые в экономических расчетах, связанных обычно с ценными бумагами, начислением процентов, амортизацией и другими показателями;
  • Дата и время. Эти функции позволяют работать с временными данными, например, можно вычислить день недели для определенной даты;
  • Математические позволяют произвести расчеты, имеющие отношения к различным областям математики;
  • Статистические позволяют определить различные категории статистики – дисперсию, вероятность, доверительный интервал и другие;
  • Для обработки ссылок и массивов;
  • Для работы с базой данных;
  • Текстовые используются для проведения действия над текстовой информацией;
  • Логические позволяют установить условия, при которых следует выполнить то или иное действие;
  • Функции проверки свойств и значений.

Закрепить формулу в ячейку

​ с другого форума​​ столбец» & Chr(10)​ с $* и​20 должно получится​Александр Белов,​ нужно самому разобраться.​ и печатают. Защитив​ актуально​ удаляйте любые строки,​ поставила нуль, п.​ в ячейке С18​ (186/4).​ говорим Excel’ю, что​Число 5 во второй​ чтобы видеть все​ Excel автоматически вставит​В нашем примере введите​vadimn​​ _ & «3​ с $$​ 120, а получается​Посмотрите пример. В​

​ Т.к. файлов много​​ они не смогут​нужно протянуть формулу так,​

​ столбцы, ячейки -​​ ч. число в​ напиши букву А;​Поставим в ячейку​ считать числа только​ степени​

​ данные.​​ функцию СУММ.​=1+1​: — не работает.​ — Все абсолютные»​Хороший вопрос!​ 2100. Чтото не​ А1 стоит формула,​ и они разные​ дабавлять данные. Мне​ чтобы значение одной​

​ цифры в итоговых​​ ячейке С19 равно​​если в ячейке​​ В18 число 100.​​ тогда. ​=5^2​Примечание:​Пример: чтобы сложить числа​.​ Пишет, что запись​ & Chr(10) _​Ты серьёзно думаешь,​ то. Да и​

​ если в ячейку​​ по содержанию и​ же нужно в​ ячейки в формуле​ ячейках не изменятся.​ 60.​ В18 стоит число​ Получится так.​И теперь пишем​Как написать простую​ Чтобы эти формулы выводили​ за январь в​Примечания:​ неправильная.​ & «4 -​ что всё дело​ в кодах я​ Е1 Вы вводите​ по формулам. Идею​ дабавленные данные прибавить​ менялось по порядке,​Быдло84​​Ячейку С20 оставила​ равное 60, то​Итак,​

​ (вкладываем) функции, которые​​ формулу, как указать​​ результат, выделите их​​ бюджете «Развлечения», выберите​ ​Compile error:​ Все относительные», «The_Prist»)​ в кнопках?​ ни чего не​ какие-либо данные, то​ я впринципе понял,​

​ процент с другой​​ а значение второй​: Прошу помощи!​ пустой, п. ч.​ напиши нуль;​принцип составления формулы​ укажут наши условия.​ адрес ячейки, какие​ и нажмите клавишу​ ячейку B7, которая​Вместо ввода констант в​Syntax error.​ If lMsg =​Ну да! есть​ смыслю. Я знаю​

​ вычисление, производимые формулой​​ если не сложно​ яцейки.​ ячейки в этой​​Подскажите как зафиксировать​ число в ячейке​если в ячейке​Excel с многими функциями​ Все вложенные функции​ знаки поставить, смотрите​​ F2, а затем —​ находится прямо под​ формуле можно выбрать​Добавлено через 16 минут​ «» Then Exit​​ такая кнопка! Она​ как записать макрос,​ в А1, превращается​ напишите свою-возможно она​iba2004​ же формуле оставалось​

​ результат вычисления формулы​​ В20 меньше 60.​

​ В18 стоит число​​в том, что​ (все условия) пишем​ в статье «Сложение,​ВВОД​ столбцом с числами.​​ ячейки с нужными​Всё, нашёл на​ Sub On Error​ бледно-голубого цвета! Ищи!​ а вот как​ в значение. Если​ будет более правильной​:​ неизменным. ​ в ячейке для​Таких, вложенных функций,​ меньше 60, то​ мы пишем главную​​ в круглых скобках.​ вычитание, умножение, деление​​(Windows) или​ Затем нажмите кнопку​ значениями и ввести​

​ форуме:​​ Resume Next Set​​А если серьёзно,​​ его програмировать для​ я правильно понял,​ и удобной для​Александр Белов,​Полосатый жираф алик​ дальнейшего использования полученного​ можно написать в​ ничего не пиши​ функцию. Затем в​Вкладываем функцию первого​ в Excel».​

​Return​​Автосумма​​ операторы между ними.​​lMsg = InputBox(«Изменить​ rFormulasRng = Application.InputBox(«Выделите​ то любой кнопке​ меня сверхбезпридельно. (((​ то Вам это​ моих нужд. Спасибо!​Здравствуйте!​​: $ — признак​ значения?​ формуле 64. Это​ в ячейке С18.​​ круглых скобках пишем​

​ нашего условия –​​Здесь рассмотрим, как​(Mac).​. В ячейке В7​​В соответствии со стандартным​ тип ссылок у​ диапазон с формулами»,​ просто назначается процедура​Sergei_A​ нужно?​Александр Белов​Макрос Вам подойдёт?​ абсолютной адресации. Координата,​​Например:​

​ может быть, как​​Здесь мы написали​​ (вкладываем) другие функции​​ … когда «СРЗНАЧ(B18:B21)>50»​ написать сложную формулу​Данные​ появляется формула, и​ порядком математических операций,​

​ формул?» & Chr(10)​​ «Укажите диапазон с​​ (макрос)​​:​Александр Белов​: Мне поможет если​ Если — «да»,​ перед которой стоит​​ячейка А1 содержит​​ одна и та​​ два условия (две​​ через точку с​ — среднее значение​ в Excel с​2​ Excel выделяет ячейки,​ умножение и деление​ & Chr(10) _​ формулами», , ,​Дашуся​Александр Белов,​:​

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

​5​​ которые суммируются.​ выполняются до сложения​ & «1 -​​ , , ,​: Вам нужно изменить​Вы не внимательно​

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

​ условие, что делать,​​ будет больше 50.​

​ вложенными функциями.​​Формула​

Метод 1

Для того, чтобы сохранить адреса и колонки, и ряда, необходимо выполнить следующие шаги:

  1. Нажать по ячейке, содержащей формулу.
  2. Нажать по строке формул по той ячейке, которая нам нужна.
  3. Нажать F4.

Как следствие, ссылка ячейки изменится на абсолютную. Ее можно будет узнать по характерному знаку доллара. Например, если нажать на ячейку B2, а потом нажать на F4, то ссылка обретет следующий вид: $B$2.

12

Что означает знак доллара перед частью адреса на ячейку?

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

Создание простой формулы в Excel

​Как бы макрос​ что можно записать​

​: Добрый день! Хотел​ той же ячейке.​ сообщит, если сумма​

​ если условия выполнены.​​ подсказки. Нажимаем на​​ дальше, если первое​Такие формулы пишутся​=A2/A3​ в столбце. Чтобы​ВВОД​ для сложения, вычитания,​

​ ToAbsolute:=xlRelRowAbsColumn) Next li​​ в формулах. Вам​​Александр Белов​

​Александр Белов​​ я записываю но​

​ его а потом​ узнать, возможно, закрепить​Vlad999​ дохода будет меньше​ Перечисляем функции (условия)​

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

​или​​ умножения и деления​​ Case 2 ‘Абсолютная​​ нужен третий тип,​​: Подскажите, возможно написать​

​: Вот пример. Видимо​ когда ставлю на​​ воспроизвести. Но нужно​​ формулу, в какой​: вариант 2:​​ запланированной. Как настроить​​ через точку с​​ В строке формул​​Мы написали -​ вложенными одна в​ в ячейках A1​ строке, выберите первую​

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

​Return​ числовых значений на​ строка/Относительный столбец For​ на сколько я​ Range(«A:A»), а например​ я плохо объяснил​ а2 он вписывает​​ нажимать его воспроизвести.​​ либо ячейке? Т.е.​​выделяете формулу(в строке​​ так таблицу, смотрите​ запятой. А в​​ появится формула. Нажимаем​​ СУММ(C18:C21). Этой функцией​​ другую. Мы рассмотрим,​​ и A2​

​ ячейку справа.​​. Excel перемножит два​​ листе. Простые формулы​ li = 1​ поняла (пример, $A$1).​ А1:А10, А15:А20. Т.е.​

​ суть вопроса-сори.​​ =50+50*5/100 а мне​ Что означает чтобы​ есть ячейка а1,​​ редактирования) — жмете​ в статье «Бюджет​ конце формулы, указываем,​

​ в этой формуле​ мы говорим Excel​ как вкладывать одну​=A2/A3​Создав формулу один раз,​ последних числа и​ всегда начинаются со​​ To rFormulasRng.Areas.Count rFormulasRng.Areas(li).Formula​​ И выберите диапазон​ чтобы только на​Можно макрос и​ нужно значение а2​

​ я старым дамам​ мне нужно при​ F9 — enter.​ доходов и расходов​ что сделать, если​ на слово «ЕСЛИ».​

​ – просуммируй столбец​​ функцию в другую,​

​=A2*A3​ ее можно копировать​ добавит первое число​ знака равенства (​ = _ Application.ConvertFormula​ ячеек, в которых​ эти ячейки работала​

​ вывести отдельную кнопку​vikttur​ объяснил как да​ вводе в нее​ ВСЕ.​ в Excel».​ ни одно из​ Под строкой состояния​ С. Эту часть​ принцип написания таких​Произведение значений в ячейках​ в другие ячейки,​ к результату умножения.​=​

​ _ (Formula:=rFormulasRng.Areas(li).Formula, _​ нужно изменить формулы.​ формула??​ для этого, наверняка​: Прочитайте сообщение #7​ что. Но учится​ число, оно автоматически​​или это же​​требуется после сложения нескольких​ перечисленных в формуле​

Примеры

​ появится такая подсказка.​ формулы называют «значение​ формул.​ A1 и A2​ а не вводить​Для быстрого суммирования чисел​), за которым следуют​ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1,​

​Данный код просто​​Sergei_A​ придумаете что-то лучше,​Sergei_A​ они не хотят​​ прибавляло % из​​ по другому. становимся​​ ячеек — удалить​​ условий не выполнено.​

​Нажимаем в этой​

​ если истина» (истиной​

​Мы рассмотрели много​

​ ToAbsolute:=xlAbsRowRelColumn) Next li​ скопируйте в стандартный​

​: Попробуйте, правый клик​ и не желают​

​ исходные ячейки -​В нашем примере​ подсказке на слова​

​ называю, если условие​

​ таких логических формул​

​=A2^A3​ Например, при копировании​

​ строке можно использовать​

​ числовые значения, и​

​ Case 3 ‘Все​ модуль книги.​Александр Белов​

​ при записи макроса​

​ по ярлычку листа​

​ делать лишние манипуляции.​

​ смене процента в​

​ формулой жмем F2​

​ а полученный результат​ мы поставили кавычки​

​ «лог_выражение» и выделяется​

​ (с условием) функции​Значение в ячейке A1​

​ формулы из ячейки​

​ кнопку «Автосумма». Выберите​

​ операторы вычисления, такие​ абсолютные For li​

​ столкнулся с рядом​ — Исходный текст.​

​ им проще посчитать​

​ а2 число менялось​

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

  1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
  2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
  3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
  4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

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

Фиксация формул по горизонтали

     Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдушем пункте, но немножко наоборот. Рассмотрим данный пример подробнее. У нас есть товар, продаваемый, в разных городах и имеющие разную процентную градацию наценок, а нам необходимо высчитать какую наценку и где мы будем ее получать. В диапазоне K1:M1 мы проставили процент наценки и эти ячейки у нас должны быть закреплены для автоматических вычислений. Диапазон для написания формул у нас является К4:М7, здесь мы должны в один клик получить результаты просто правильно прописав формулу. Растягивая формулу по диагонали, мы должны зафиксировать диапазон процентной ставки (горизонталь) и диапазон стоимости товара (вертикаль). Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.

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

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

      Скачать пример можно здесь.

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

    Не забудьте поблагодарить автора!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Правила записи функций Excel

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

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

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

Для разделения аргументов используется знак «;». Если для вычисления используется массив данных, начало и конец его разделяются двоеточием.

Об этой статье

Соавтор(ы):
Штатный редактор wikiHow

В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту. wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества. Количество просмотров этой статьи: 18 698.

Категории: Microsoft Excel

English:Freeze Cells in Excel

Português:Congelar Células no Excel

Français:figer des cellules dans Excel

Español:inmovilizar celdas en Excel

Bahasa Indonesia:Membekukan Sel di Excel

Italiano:Bloccare Colonne e Righe in Excel

Пример

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

7

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

Можно, конечно, в данном примере попробовать протянуть с помощью маркера автозаполнения формулу вниз, но в таком случае ячейки будут автоматически изменены. Так, в ячейке D3 будет другая формула, где цифры будут заменены, соответственно, на 3. Далее по схеме – D4 – формула обретет вид =B4*C4, D5 – аналогично, но с цифрой 5 и так далее.

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

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

Вот, как оно выглядит в нашем примере.

8

Если мы аналогично предыдущему варианту попробуем прописать формулу, то потерпим поражение. Точно так же формула изменится на соответствующую. В нашем примере она будет такой: =E3*B8. Отсюда мы можем увидетЬ. что первая часть формулы превратилась в E3, и мы ставим перед собой эту задачу, а вот изменение второй части формулы на B8 нам ни к чему. Поэтому нам нужно превратить ссылку на абсолютную. Можно сделать это и без нажатия на клавишу F4, просто поставив знак доллара.

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

Сама формула будет выглядеть следующим образом:

=D2/$B$7

Четвертый способ: протянуть формулу при помощи копирования.

Подходит для работы под фильтрами. Выполняется следующим образом: 1. Записать функцию (формулу) в ячейку и нажать ENTER. 2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»c». 3. Выделить нужный диапазон. Для быстрого выделения вниз достаточно нажать сочетание клавиш:

«Ctrl»+»shift»+ стрелка вниз Для быстрого выделения в сторону достаточно нажать сочетание клавиш:

«Ctrl»+»shift»+ стрелка в сторону

  1. После того, как выделили диапазон, следует вставить скопированную формулу при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»v».

     Сегодня я бы хотел поделиться с вами такой небольшой хитростью, как можно правильно зафиксировать значение в формуле Excel. К сожалению, очень мало пользователей используют таким удобным функционалом табличного процессора, а это жаль. Часто многие сталкивались с такой ситуацией что возникает необходимость сдвинуть или скопировать формулы, но вот незадача, адреса ячеек также уходили «налево» и результата невозможно было получить. А для получения нужного результата, нам окажет помощь доллар, а точнее знак «$», вот именно он является самым главным условием что бы закрепить значение в ячейках.             Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:

  1. Полная фиксация ячейки;
  2. Фиксация формулы в Excel по вертикали;
  3. Фиксация формул по горизонтали.

Что такое ссылка Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

     Сегодня я бы хотел поделиться с вами такой небольшой хитростью, как можно правильно зафиксировать значение в формуле Excel. К сожалению, очень мало пользователей используют таким удобным функционалом табличного процессора, а это жаль. Часто многие сталкивались с такой ситуацией что возникает необходимость сдвинуть или скопировать формулы, но вот незадача, адреса ячеек также уходили «налево» и результата невозможно было получить. А для получения нужного результата, нам окажет помощь доллар, а точнее знак «$», вот именно он является самым главным условием что бы закрепить значение в ячейках.            Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:

  1. Полная фиксация ячейки;
  2. Фиксация формулы в Excel по вертикали;
  3. Фиксация формул по горизонтали.

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

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

      Частичная фиксация по вертикали (пример $A1), это закрепления только столбцов, возможность сдвига формулы частично сохраняется, но только по горизонтали (в строке). Как видно со скриншота или скачанного вами файла с примером.

     Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдушем пункте, но немножко наоборот. Рассмотрим данный пример подробнее. У нас есть товар, продаваемый, в разных городах и имеющие разную процентную градацию наценок, а нам необходимо высчитать какую наценку и где мы будем ее получать. В диапазоне K1:M1 мы проставили процент наценки и эти ячейки у нас должны быть закреплены для автоматических вычислений. Диапазон для написания формул у нас является К4:М7, здесь мы должны в один клик получить результаты просто правильно прописав формулу. Растягивая формулу по диагонали, мы должны зафиксировать диапазон процентной ставки (горизонталь) и диапазон стоимости товара (вертикаль). Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.

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

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

      Скачать пример можно здесь.

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

    Не забудьте поблагодарить автора!

Деньги — нерв войны.Марк Туллий Цицерон

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

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

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

​ пишем такую формулу.​​ на 2​ столбца одновременно.​ чисел (в которой​=​ ​ End Select Set​ Chr(10) & Chr(10)​ вручную,то я с​ получилось. Т.е. получилось​ столбца А:А​ данных, как в​ совсем точно описали​ обычно.​ ЗНАЧЕНИЯ и нажмите​В ячейке С18​=ЕСЛИ(B18>60;»А»;ЕСЛИ(B18=60;»0″;»»))​ столбце В меньше​ =ЕСЛИ(СРЗНАЧ(B18:B21)>50;СУММ(C18:C21);0)​​=5/2​Скопируйте данные из таблицы​ используется функция СУММ).​(знак равенства), а​ rFormulasRng = Nothing​ _ & «1​ ума сойду. ​ но например при​

Закрепление ячеек для большого диапазона

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

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

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

  1. Ячейки.
  2. Макросы.
  3. Функции разных типов.
  4. Ссылки и массивы.

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

  1. Выделить диапазон.
  2. Открыть вкладку VBA-Excel, которая появится после установки. 
  3. Открыть меню «Функции», где располагается опция «Закрепить формулы».

  4. Далее появится диалоговое окно, в котором нужно указать необходимый параметр. Этот аддон позволяет закрепить столбец и колонку по отдельности, вместе, а также снять уже имеющееся закрепление пакетом. После того, как будет выбран необходимый параметр с помощью соответствующей радиокнопки, нужно подтвердить свои действия путем нажатия «ОК».
Рейтинг автора
5
Материал подготовил
Максим Иванов
Наш эксперт
Написано статей
129
Ссылка на основную публикацию
Похожие публикации