Абсолютные и относительные ссылки в Excel

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

Все вы, вероятно, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя различными способами, например A1, $A$1, $A1 и A$1.

Что такое ссылка на ячейку?

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

Например, если вы введете простую формулу =A1 в ячейку C1, Excel вытащит значение из ячейки A1 в C1:

Абсолютные и относительные ссылки в Excel – Ссылка на ячейку в Excel

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

Абсолютные и относительные ссылки в Excel – Использование $ в формулах Excel

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

Относительная ссылка в Excel

Предположим, что у вас есть следующая формула в ячейке B1:

Если вы скопируете эту формулу в другую строку в том же столбце, например, в ячейку B2, формула будет корректироваться для строки 2 (A2*10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца А на 10.

Абсолютные и относительные ссылки в Excel – Относительная ссылка в Excel

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

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец

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

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец и другую строку

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

Пример относительных ссылок в Excel

Пусть у нас есть электронная таблица, в которой отражены наименование, цена, количество и стоимость товаров.

Абсолютные и относительные ссылки в Excel – Исходные данные

Нам нужно рассчитать стоимость для каждого товара. В ячейке D2 введем формулу, в которой перемножим цену товара А и количество проданных единиц. Формула в ячейке D2 ссылается на ячейку B2 и C2, которые являются относительными ссылками. При перетаскивании маркера заполнения вниз на ячейки, которые необходимо заполнить, формула автоматически изменяется.

Абсолютные и относительные ссылки в Excel – Относительные ссылки

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

Абсолютные и относительные ссылки в Excel – Относительные ссылки (режим формул)

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

Абсолютная ссылка в Excel

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

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

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка в Excel

Абсолютные и относительные ссылки в Excel – Разница между абсолютными и относительными ссылками в Excel

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

Пример использования абсолютной и относительных ссылок в Excel

Пусть в рассматриваемой выше электронной таблице необходимо дополнительно рассчитать десятипроцентную скидку. В ячейке Е2 вводим формулу =D2*(1-$H$1). Ссылка на ячейку $H$1 является абсолютной ссылкой на ячейку, и она не будет изменяться при заполнении других ячеек.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка

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

Абсолютные и относительные ссылки в Excel – Переключение между относительной, абсолютной ссылкой и смешанными ссылками

Или вы можете сделать абсолютную ссылку, введя символ $ вручную с клавиатуры.

Если вы используете Excel for Mac, то для преобразования относительной в абсолютную ссылку или в смешанные ссылки используйте сочетание клавиш COMMAND+T.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка (режим формул)

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

Смешанные ссылки в Excel

Смешанные ссылки используют, когда необходимо закрепить адрес ячейки только по строке или только по столбцу. Смешанные ссылки могут быть абсолютными по столбцу и относительными по строке (например, $A1), или относительными по столбцу и абсолютными по строке (например, A$1).

Как вы помните, абсолютная ссылка в Excel содержит 2 знака доллара ($), которые блокируют как столбец, так и строку. В смешанной ссылке фиксируется только одна координата (абсолютная), а другая (относительная) изменяется в зависимости от относительного положения строки или столбца:

• Абсолютный столбец и относительная строка, например $A1. Когда формула со смешанной ссылкой копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы он никогда не менялся. Ссылка относительной строки, без знака доллара, изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка, например A$1. В этой смешанной ссылке ссылка на строку не изменится, а ссылка на столбец будет меняться.

Абсолютные и относительные ссылки в Excel – Смешанные ссылки

Ниже вы представлен пример использования обоих типов смешанных ссылок.

Пример смешанных ссылок в Excel

Если нам нужно узнать какая будет стоимость с учетом 10%, 25% и 30% скидки, то в ячейку Е3 вводим формулу =$D3*(1-E$2), фиксируя таким образом столбец D (стоимость) и строку 2 (скидку). В данной формуле используются две смешанные ссылки:

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок

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

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок (режим формул)

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

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

Формулы Эксель: Как создать формулу, Операторы и Функции в Excel. Работа с формулами в Excel

• Абсолютный столбец и относительная строка, например $A1. Когда формула со смешанной ссылкой копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы он никогда не менялся. Ссылка относительной строки, без знака доллара, изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка, например A$1. В этой смешанной ссылке ссылка на строку не изменится, а ссылка на столбец будет меняться.

​и выбрать к​ + 4 :)​ А со строкой​​ расположенную на две​ в вычислениях.​При помощи трехмерных ссылок​ относительный столбец. Абсолютная​ изменяется и ссылка.​ с 5 по​ одной формуле данные,​ 3,142. ​ формул.​ важно, чтобы эта​. Все ячейки, что​ Excel» здесь.​ символ (например, *).​(кавычки)​

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

Для тех кто первый раз на моем сайте, и для тех кто не раз уже был советую повторить урок 2 и урок 1, где мы рассматривали простые математические операции в Excel (сложение, вычитание и т.д. ) И урок автозаполнения ячеек в Excel.

Доллар в MS Excel — для чего?

Чтобы правильно понять для чего и как использовать знак доллара в excel разберем некоторые моменты.

Первое. Эта картинка из прошлых уроков, на не мы используем формулу =B2+B3, она нам складывает содержимое ячеек В2 и В3, а ответ появиться в той ячейке, где записана формула.

Второе. Эта картинка тоже из прошлых уроков по автозаполнению. Здесь мы автоматически пронумеровали 7 строк. А что было бы, если мы выделим не две ячейки с единицей и двойкой, а одну, допустим с двойкой, и точно также потянем вниз или вправо. Попробуйте! В итоге в тех ячейках, на которые мы протянули появилась цифра 2 (см. рисунок ниже.)

Теперь объединим то, что мы использовали на первой картинке и на последней.

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

Т.е. в ячейку
D5 записала результат сложения D3+D4 (в ячейке D5 появилась автоматически формула =D3+D4 )
E5 записала результат сложения E3+E4 (в ячейке E5 появилась автоматически формула =E3+E4 )
F5 записала результат сложения F3+F4 (в ячейке F5 появилась автоматически формула =F3+F4 ) и т.д.

Объясняю. Что общего у ячеек в которых записан ответ? Правильно у них одинаковая цифра (C5, D5, F5, G5), а буква разная вот программа Excel и заменила автоматически на нужные буквы в записи формул. Ведь в каждой ячейке у нас сейчас автоматически записана своя формула с нужными буквами.

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

Применение знака доллара

И так, вот Вам задание с применением знака $ доллара, о котором я говорил в самом начале/

Смотрите внимательно картинку, кликните по ней чтобы ее увеличить.

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

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

Жми Добавляйся в группу в ВК и Одноклассниках!

Символы в формулах Excel.

Полные сведения о формулах в Excel

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

​ ссылки указывают. В​​ . Трехмерные ссылки используются для​ столбцам абсолютная ссылка​ отделяет ссылку на​ столбца, а затем —​ не ссылки на​ не хуже профессионалов.​ Формулы в Excel начинаются​ данным таблицы, а​ по-английски — at​ в статье «Функция​ Рогов, Роговцева, Романов,​

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

​ во всех ячейках​​ R1C1, установите или​ ссылок R1C1 удобен​

​ примерах используется формула​ анализа данных из​ не корректируется. По​

​ лист от ссылки​ номер строки. Например,​ другие ячейки (например,​

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

Просмотр формулы

​ является сигналом к​ знак доллара ($).​ появятся скорректированные формулы.​​ снимите флажок​​ для вычисления положения​

​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​ одной и той​ умолчанию в новых​ на диапазон ячеек.​

Ввод формулы, содержащей встроенную функцию

​ имеет вид =30+70+110),​ определенных элементах формулы,​Выберите ячейку или введите​ При добавлении данных​ commercial)​

​ действию что-то посчитать,​ Как вставить знак​Однако может возникнуть​

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

​ с A2 по​ диапазона ячеек на​ ссылки, а для​ Если название упоминаемого листа​

Части формулы Excel

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

​ A5 на листах​​ нескольких листах одной​​ использования абсолютных ссылок​ содержит пробелы или​ и строки 2.​

​ после редактирования формулы.​​Формула также может содержать​​Введите оператор. Например, для​ последние (например за​

​ Excel​​ не то, что​​ «Как написать формулу​ в ячейке написан​Никита чугайнов​ не должна (например,​

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

Использование констант в формулах Excel

Использование ссылок в формулах Excel

​ A и строки​ их можно будет​,​ введите ее адрес​ об этом. смотрите​ два формата -​ найти в таблице​ по слову, а​ значение ячейки, это,​ B1 пишешь формулу:​ товара). В этом​. Чтобы открыть это​ строки, и буквой​ листами 2 и​ имена листов. В​ ячейки B2 в​ за январь’!A1.​ 10​ легко изменить при​ссылки​

​ ячейку B3 она​Различия между абсолютными, относительными​

​Какие бываю формулы,​и размножаешь формулу​ абсолютную ссылку, зафиксировав​

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

​ ячейке с формулой​Примечание:​ они отличаются и​ знаком вопроса поставим​ пишем это символ.​ как и где​

​ между начальным и​ обеих ячейках: =$A$1.​Относительные ссылки​ строки 10-20.​

​ на эти ячейки.​​и​ отобразится результат вычисления.​ Мы стараемся как можно​ на что влияют​ тильду («

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

​ . Если удалить листы​ значения, содержащиеся в​​Смешанные ссылки​

​ определяется соответствующими столбцом​ том же столбце​​ и 6, Microsoft​

​ставят между первой и​ статье «Как создать​ в которой написан​ в Excel» тут.​ формат ячеек «денежный»​: $ — шифт​ на пересечении столбца​Относительная ссылка на ячейку,​ использовать их значения​ до Лист13 включительно.​ абсолютную строку и​ ячейки, содержащей формулу,​Все ячейки в строках​

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

​и выбрать к​ + 4 :)​ А со строкой​​ расположенную на две​ в вычислениях.​При помощи трехмерных ссылок​ относительный столбец. Абсолютная​ изменяется и ссылка.​ с 5 по​ одной формуле данные,​ 3,142. ​ формул.​ важно, чтобы эта​. Все ячейки, что​ Excel» здесь.​ символ (например, *).​(кавычки)​

​ нему в качестве​​UPD А автоматом​ 3 располагается ячейка​ строки ниже и​Перемещение​ можно создавать ссылки​ ссылка столбцов приобретает​ При копировании или​

​ 10​​ находящиеся в разных​2.​Выделите пустую ячейку.​ статья была вам​ внутри входят в​​

​? (знак вопроса)​​- означает​ отображения не «р.​ встают когда значения​ А3. Такая запись​ на два столбца​ . Если листы, находящиеся между​ на ячейки на​ вид $A1, $B1​ заполнении формулы вдоль​5:10​ частях листа, а​

​Ссылки​​Введите знак равенства «=»,​ полезна. Просим вас​ диапазон. Например, диапазон​(знаки меньше и больше)​– обозначает​текст​

​ «, а «$​​ в формулу не​ называется — относительная​ правее​ листом 2 и​ других листах, определять​

​ строк и вдоль​Все ячейки в столбце​ также использовать значение​. A2 возвращает значение​ а затем — функцию.​ уделить пару секунд​ А1:А3. В этот​– означают в​один какой-то символ​. Например, когда нужно​ английский (США) «​ руками вписываешь, а​ ссылка. Если Вы​R2C2​ листом 6, переместить​ имена и создавать​

​ одной ячейки в​ ячейки A2.​ Например, чтобы получить​ и сообщить, помогла​

​ формуле «​. Например, в формуле​ найти какое-то слово,​Тогда в ячейке​ мышом по ячейкам​

​Абсолютная ссылка на ячейку,​ таким образом, чтобы​ формулы с использованием​

​ корректируется. По умолчанию​H:H​ нескольких формулах. Кроме​

​ А1, А2, А3.​не равно​ написали «Ст?л». Найдет​ в формуле это​ будет записано 199,​ кликаешь​​ содержащая относительную ссылку​​ расположенную во второй​ они оказались перед​ следующих функций: СУММ,​ и т.д. При​ в новых формулах​Все ячейки в столбцах​ того, можно задавать​

​Константы​ нужно ввести «=СУММ».​ с помощью кнопок​ Или, диапазон А1:В3.​​». Можно написать так.​​ – стол, стул…​​ слово пишем в​​ а отображаться будет​​Михаил земнухов​​ на эту ячейку​​ строке второго столбца​​ листом 2 или​ СРЗНАЧ, СРЗНАЧА, СЧЁТ,​ изменении позиции ячейки,​​ используются относительные ссылки.​​ с H по​

Что означает «$» в экселе. в формуле ячейки =$A$3*(100%-A31) стоит $ что он означает

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

​ кавычки можно вставить​​ арифметические операции.​Женский род, имя существительное​
​ Например, Вы ввели​ ячейки​ ячеек с перемещенных​ СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,​ изменяется. При копировании​ B2 в ячейку​

​Начните создавать формулы и​​О том какие​ всё, кроме яблок.​&​
​ несколько слов, знаки​Чего не скажешь​: выделяешь необходимое кол-во​ формулу =А3, после​R​ листов.​ ДИСПА и ДИСППА.​ или заполнении формулы​ B3 она автоматически​A10:E20​

Excel как заполнить знак доллара в каждой ячейке?

​Операторы​
​Нажмите клавишу ВВОД, чтобы​
​ использовать встроенные функции,​
​ символы и с​

​> — больше,​(амперсанд)​ (>, Если поставим​ о том, что​ строк, столбцов или​ чего переместили ячейку​Абсолютная ссылка на текущую​

​Перемещение конечного листа​​Трехмерные ссылки нельзя использовать​ вдоль строк и​ изменяется с =A1​Создание ссылки на ячейку​
​ называются связями или​
​. Оператор ^ (крышка)​ получить результат.​ чтобы выполнять расчеты​ какой целью можно​ например, >100 (больше​

​ получится после =СЦЕПИТЬ​​ отдельный какой-то адрес,​ А3 на одну​
​ строку​ . Если переместить лист 2​ в формулах массива.​ вдоль столбцов относительная​ на =A2.​
​ или диапазон ячеек​ внешними ссылками.​ применяется для возведения​Мы подготовили для вас​
​ и решать задачи.​ вставить в формулы,​ 100).​
​сцепить символы, слова в​ это будет означать​ («$»;A1).​далее правой кнопкой​

У пользователей недавно начавших работать с таблицами в эксель, часто возникает вопрос о том, для чего в формулах применяют знак доллара – «$».

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

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

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

«Как протянуть формулу в Excel — 4 простых способа»

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

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

Чтобы зафиксировать адрес этой ячейки с аргументом и не дать «Экселю» его поменять при протягивании, как раз и используется знак «доллар» ($) устанавливаемый в формулу «Excel».

Этот значок, поставленный перед (. ) нужным адресом, не позволяет ему изменяться.
Таким образом, если поставить доллар перед буквой адреса, то не будет изменяться адрес столбца. Пример: $B2

Если поставить «$» перед цифрой (номером строки), то при протягивании не будет изменяться адрес строки. Пример: B$2

Где находится знак «доллар» ($) на клавиатуре .

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

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

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