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

Автор: Алексей Батурин.

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

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

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

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

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

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

Ячейка зафиксирована

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

зафиксирована строка в Excel

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

Зафиксирован столбец

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

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

Ячейка полностью зафиксирована

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

Фиксируем строку в Excel

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

зафиксирован только столбец

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

фиксируем диапазон в Excel

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

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

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

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

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

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

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

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

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

Добрый день. Такой вопрос. Два файла ексель и мне нужно в файле1 сделать ссылку на одну ячейку с файла2, но проблема в том, что в файле2 постоянно добавляются строки над той ячейкой на которую ссылаюсь и тем самым номер строки меняется и ссылка сбивается. Как сделать в файле1 чтобы при добавлении строки в файле2 формула автоматически переходила на следующий строку?

Добрый день, спросите, лучше здесь http://www.planetaexcel.ru/forum/, не уверен, что так можно сделать. Скорее всего это можно сделать через сводную таблицу.

Цитировать

#34 Рус 09.11.2017 17:36

Добрый день. Такой вопрос. Два файла ексель и мне нужно в файле1 сделать ссылку на одну ячейку с файла2, но проблема в том, что в файле2 постоянно добавляются строки над той ячейкой на которую ссылаюсь и тем самым номер строки меняется и ссылка сбивается. Как сделать в файле1 чтобы при добавлении строки в файле2 формула автоматически переходила на следующий строку?

Цитировать

#33 Алексей Батурин 19.05.2017 08:34

Цитирую KonstantinPerm:

Доброе время суток. Подскажите, мне необходимо прибавить ячейки А1+А2. При этом А1 это постоянно изменяющиеся число, а А2 это сумма всех А1. Необходимо что бы при изменении А1в А2 появлялась сумма предыдущей А2+новая А1.
Заранее благодарю.

Константин, добрый день! Это надо макрос писать с глобальной переменной, которая будет помнить все значения в A1. Скорее всего стандартными средствами не решить. Напишите, вот сюда http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=list&FID=1
помогут!

Цитировать

#32 KonstantinPerm 19.05.2017 02:40

Доброе время суток. Подскажите, мне необходимо прибавить ячейки А1+А2. При этом А1 это постоянно изменяющиеся число, а А2 это сумма всех А1. Необходимо что бы при изменении А1в А2 появлялась сумма предыдущей А2+новая А1.
Заранее благодарю.

Цитировать

#31 Алексей Батурин 15.01.2016 18:09

Цитирую Богдан:

про фиксирование ячейки, столбца и строки понятно. Есть необходимость немного в другом.
Я прописываю, скажем, а1=а2+а3
затем добавляю колонку между первой и второй
формула в а1 автоматически преобразовывается в =а3+а4
Как заставить ее не изменяться, а запомнить, что мне нужно именно а2+а3

Богдан, здравствуйте.
Вашу задачу можно решить с помощью формулы =ИНДЕКС()
Для обозначенных вами ячеек а2+а3 необходимо сделать запись:
=ИНДЕКС(A2:A4;1;1)+ИНДЕКС(A2:A4;2;1)
Теперь при вставке строки во внутрь диапазона A2:A4, диапазон в формуле ИНДЕКС расширится, но ссылка будет ссылаться на указанные в формуле ИНДЕКС - номер строки и столбца, внутри заданного диапазона A2:A4.

Цитировать

#30 Богдан 15.01.2016 15:53

про фиксирование ячейки, столбца и строки понятно. Есть необходимость немного в другом.
Я прописываю, скажем, а1=а2+а3
затем добавляю колонку между первой и второй
формула в а1 автоматически преобразовывает ся в =а3+а4
Как заставить ее не изменяться, а запомнить, что мне нужно именно а2+а3

Цитировать

#29 -1 Алексей Батурин 23.12.2015 10:24

Цитирую Ирина:

В добавок к предыдущему вопросу,
сейчас прочитала первый комент, и вы советуете не использовать инструмент таблицы, и писать обычным диапазоном А$1$, а мне именно хочется, что бы в формуле было не имя ячейки/диапазона (сумм(А$1$:А$4$)), а именно прописана суть (что как раз достигается если оставить, например =СУММЕСЛИ(Планпродаж[Код клиентской группы];[@[Код клиентской группы]];Планпродаж[март]), что бы при протягивании на апрель, он мне так и оставлял Планпродаж[Код клиентской группы], а не переходил на Планпродаж[Столбец1].
Спасибо

Ирина, можно нужному диапазону присвоить "Имя", и в качестве ссылки подставлять "имя", тогда будет понятна суть.
Как присваивать имена диапазонов есть в этой статье: http://www.4analytics.ru/prognozirovanie/model-bootstrapping-prognoz-neregulyarnix-/-redkix-prodaj.html

Цитировать

#28 Алексей Батурин 23.12.2015 08:09

Цитирую Ирина:

Подскажите, пожалуйста, в формуле я хочу закрепить ячейку/диапазон из условно-форматированной таблицы, соответсвенно она прописывается не как A1 или RC, а например [@[Код клиентской группы]].
Пытаюсь сделать стандартным способом с помощью F4, но выдается ошибка. Я протягиваю формулу и она мне смещает диапазоны.
Заранее спасибо

Ирина, добрый день.
Сделайте обычную ссылку на таблицу формата A1 или RC и ее зафиксируйте.

Цитировать

#27 Ирина 23.12.2015 02:32

В добавок к предыдущему вопросу,
сейчас прочитала первый комент, и вы советуете не использовать инструмент таблицы, и писать обычным диапазоном А$1$, а мне именно хочется, что бы в формуле было не имя ячейки/диапазон а (сумм(А$1$:А$4$ )), а именно прописана суть (что как раз достигается если оставить, например =СУММЕСЛИ(Планп родаж[Код клиентской группы];[@[Код клиентской группы]];Планпр одаж[март]), что бы при протягивании на апрель, он мне так и оставлял Планпродаж[Код клиентской группы], а не переходил на Планпродаж[Столбец1].
Спасибо

Цитировать

1234

Обновить список комментариев
RSS лента комментариев этой записи