Типы ссылок EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация. Примеры и описание

Типы ссылок MS EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация. В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1*5). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул, задания правил Условного форматирования и при формировании условий Проверки данных.

Относительные ссылки

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

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

Выделенный столбец в Эксель

Вот что нам нужно сделать:

  1. Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: =B2*C2.Формула умножения в ячейке таблицы Excel
  2. Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.Результат умножения в ячейке таблицы Эксель
  3. Остается выполнить аналогичные расчеты в других ячейках столбца. Конечно же, если таблица небольшая, можно перейти в следующую ячейку и выполнить шаги 1-2, описанные выше. Но что делать, когда данных слишком много? Ведь на ручной ввод формул во все ячейки уйдет немало времени. На этот случай в Excel предусмотрена крайне полезная функция, позволяющая скопировать формулу в другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом, и когда появится небольшой черный крестик (маркер заполнения), зажав левую кнопку мыши тянем его вниз, тем самым копируя формулу в другие ячейки.Копирование формулы в другие ячейки в Эксель
  4. Отпустив кнопку мыши мы получим результаты во всех ячейках столбца, на которые растянули формулу.Результат копирования формулы в другие ячейки Эксель
  5. Если мы перейдем, например, в ячейку D3, то увидим в строке формул следующее выражение: =B3*C3.Умножение ячеек в таблице ExcelТ.е. при копировании изменились координаты ячеек, участвующих в исходной формуле, которую мы записали в ячейку D2. Это результат того, что ссылки были относительными.

Определение абсолютных и относительных ссылок

Что же представляют собой абсолютные и относительные ссылки в Экселе?

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

Виды ссылок в Excel и как их сделать в документе

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

Ссылки для электронной книги

Ссылки облегчают работу в документе и помогают сохранить данные. Существует несколько видов ссылок: одни фиксируют ячейки, другие меняются, когда вы редактируете формулу. Рассмотрим их подробнее.
1. Абсолютная ссылка представляет собой адрес нужной ячейки. Ее особенностью считается то, что перед обозначениями обязательно ставится значок «$». Пример того, как должен выглядеть такой линк.

№ 1.png

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

Без знака «=» ссылка, оформленная в Excel, считается недействительной и при редактировании она уже не будет неизменным значением. Поэтому не забывайте о знаке «=».

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

№ 2.png

3. Смешанные ссылки создаются, когда мы меняем одну из координат. Добавляем «$» перед координатой. При копировании координата с отсутствующим дополнительным значком будет изменяться. На примере это выглядит следующим образом.

№ 3.png

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

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

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

1-absoliutnye-i-otnositelnye-ssylki-v-excel

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

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

2-absoliutnye-i-otnositelnye-ssylki-v-excel

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

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

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

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

Протягивание

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

=A1,

где буква обозначает столбец, а число – строку.

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

Что такое ссылка на диапазон?

В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Относительная адресация (относительные ссылки)

Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1 . Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1 ? После протягивания ее вниз Маркером заполнения , в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.

Теперь примеры.

Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.

Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3 : =СУММ(A2:A3) и т.д.

Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже.

Альтернативное решение

Другим вариантом решения этой задачи является использование Именованной формулы . Для этого:

  • выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах ). Теперь B2 – активная ячейка;
  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите, например Сумма2ячеек ;
  • убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
  • Нажмите ОК.

Теперь в B2 введем формулу = Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1 ). Если формулу ввести в ячейку B5 , то она будет суммировать ячейки A4:A5 , если ввести в D10 , то – ячейки С9:С10 .

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

Относительная адресация при создании формул для Условного форматирования.

Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2 ). Построим такую таблицу:

Создадим правило для Условного форматирования :

  • выделите диапазон таблицы B 2: F 11 , так, чтобы активной ячейкой была B 2 (важно выделить диапазон начиная с B 2 , а не с F 11 . Во втором случае, активной ячейкой будет F 11 );
  • вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ использовать формулу для … );
  • введите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50) ;
  • выберите Формат ;
  • нажмите ОК

Важно отметить, что, если бы, при создании правила, активной ячейкой была F11 , то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11 .

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

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

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

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

  1. Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: =D2/D13.Формула деления в ячейке таблицы Excel
  2. Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13.Абсолютная ссылка в формуле Эксель
  3. Теперь можно нажать Enter, чтобы вывести результат в ячейку.Абсолютная ссылка в формуле Excel
  4. Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.Копирование формулы в другие ячейки в Excel

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Изменение стиля ссылок

Всего доброго и до новых встреч.

Поделиться в социальных сетях:

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

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

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

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

=B2*скидка

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

Формула же при этом становится более понятной и читаемой.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...