Относительные и абсолютные ссылки — как создавать и изменять
В руководстве объясняется, что такое адрес ячейки, как правильно писать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое.
Ссылки на ячейки в Excel, как бы просто это ни звучало, многих пользователей сбивает с толку. Как определяется адрес ячейки? Что такое абсолютные и относительные ссылки и когда вы должны использовать каждую из них? Как сделать перекрестные ссылки между разными листами и файлами? В этом руководстве вы найдете ответы на эти и многие другие вопросы
Что такое ссылка на ячейку?
Электронная таблица Excel состоит из ячеек. На каждый из них можно сослаться, указав значение строки и значение столбца. Потому что это необходимо? Чтобы получить записанное в него значение, а затем использовать его в вычислениях.
Ссылка на ячейку представляет собой комбинацию буквы столбца и номера строки, которая идентифицирует ее на листе. Короче говоря, это ваш адрес. Он сообщает программе, где искать значение, которое вы хотите использовать в вычислениях.
Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.
При использовании в формуле ссылки помогают Excel находить значения для использования.
Например, если вы введете более простое выражение =A1 в ячейку C1, Excel продублирует данные из A1 в C1:
Чтобы сложить числа в ячейках A1 и A2, используйте: =A1 + A2
Что такое ссылка на диапазон?
В Microsoft Excel диапазон — это блок из двух или более ячеек. Ссылка на диапазон представлена адресами его верхней левой и нижней правой ячеек, разделенных двоеточием.
Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.
Как создать ссылку?
Чтобы зарегистрировать ссылку на ячейку на том же листе, нужно сделать следующее:
- Выберите, где вы хотите ввести формулу.
- Введите знак равенства (=).
- Выполните одно из следующих:
- Введите координаты непосредственно в ячейку или в строку формул, или
- Нажмите на ячейку, к которой вы хотите получить доступ.
- Введите остальную часть формулы и нажмите Enter для завершения.
Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюс, щелкните A2 и нажмите Enter:
Чтобы связать диапазон, выберите область в электронной таблице.
Например, чтобы добавить значения в A1, A2 и A3, введите знак равенства, за которым следует имя функции SUM, а затем открывающая скобка, выберите ячейки с A1 по A3, введите закрывающую скобку и нажмите Enter:
Чтобы получить доступ ко всей строке или ко всему столбцу, щелкните номер строки или букву столбца соответственно.
Например, чтобы суммировать все ячейки в строке 1, начните вводить функцию СУММ, а затем щелкните заголовок первой строки, чтобы включить в расчет ссылку на строку:
Как изменить ссылку?
Чтобы изменить адрес ячейки в существующей формуле Excel, выполните следующие действия:
- Выберите ячейку, содержащую формулу, и нажмите F2, чтобы войти в режим редактирования, или дважды щелкните саму ячейку. Это выделит каждую ячейку или диапазон, на которые ссылается формула, другим цветом.
- Чтобы изменить адрес, выполните одно из следующих действий:
- Выберите адрес в формуле и вручную введите вместо него новый.
- После выбора ссылки с помощью мыши укажите другой адрес или диапазон на листе.
- Чтобы включить больше или меньше ячеек в ссылку на диапазон, перетащите правый нижний угол:
- Нажмите Ввод.
Как сделать перекрестную ссылку?
Чтобы сослаться на ячейки в другом листе или файле Excel, вам нужно указать не только ячейку назначения, но также лист и книгу, где они расположены. Это можно сделать с помощью так называемой внешней ссылки.
Чтобы сослаться на данные, находящиеся на другом листе, введите имя этого целевого листа, а затем восклицательный знак (!) перед адресом ячейки или диапазона.
Например, вот как вы можете создать ссылку на адрес A1 на Листе2 в той же книге Excel:
=Лист2!A1
Если имя рабочего листа содержит пробелы или неалфавитные символы, его необходимо заключить в одинарные кавычки, например:
=’Новый лист’!A1
Чтобы избежать возможных опечаток и ошибок, вы можете настроить автоматическое создание внешней ссылки в Excel. Вот как:
- Начните печатать в ячейке. Введите знак «=».
- Щелкните вкладку листа, на который вы хотите создать ссылку, затем выберите ячейку или диапазон ячеек на этом листе.
- Закончите ввод и нажмите Enter.
Как сослаться на другую книгу?
Чтобы сослаться на ячейку или диапазон ячеек в другом файле Excel, необходимо заключить имя книги в квадратные скобки, за которым следует имя листа, восклицательный знак и адрес ячейки или диапазона.
Например:
=[Книга1.xlsx]Лист1!A1
Если имя файла или листа содержит небуквенные символы, обязательно заключите путь в одинарные кавычки, например
='[Новый файл.xlsx]Лист1′!A1
Как и в случае со ссылкой на другой лист, вам не нужно вводить все вручную. Более быстрый способ — начать вводить формулу, затем переключиться на другую книгу и выбрать ячейку или диапазон в этой книге. Нажмите Ввод.
Итак, мы научились создавать простейшие ссылки. Теперь давайте посмотрим, что они из себя представляют.
В Excel существует три типа ссылок на ячейки: относительные, абсолютные и смешанные. Вы можете использовать любой из них в своих расчетах. Но если вы собираетесь скопировать написанное выражение в другое место на листе, вам нужно быть осторожным. Важно использовать правильный тип адреса, поскольку относительные и абсолютные ссылки ведут себя по-разному при перемещении и копировании.
Относительная ссылка на ячейку.
Относительная ссылка является самой простой и включает координаты строк и столбцов, например A1 или A1:D10. По умолчанию все адреса ячеек в Excel являются относительными.
Пример:
=А2
Это простое выражение указывает программе отображать значение, записанное в первом столбце (A) и во второй строке (2). Используя снимок экрана чуть ниже, если бы эта формула была помещена в ячейку D1, она отображала бы число «8», поскольку это значение находится по адресу A2.
При перемещении или копировании относительные ссылки изменяются в зависимости от относительного положения строк и столбцов. Другими словами, насколько новая локация отличается от исходной.
Поэтому, если вы хотите повторить одни и те же вычисления для одного и того же типа данных по вертикали или по горизонтали, вы должны использовать относительные ссылки.
Например, чтобы добавить числа в A2 и B2, введите это в C2: =A2+B2. Копирование из строки 2 в строку 3 изменит выражение на =A3+B3.
Относительные ссылки полезны и удобны, потому что если у вас есть данные того же типа, с которыми вам нужно выполнять одни и те же операции, вы можете создать формулу один раз, а затем просто скопировать ее для всех данных.
Например, очень удобно перемножать количество и цену различных товаров в таблице, чтобы найти их стоимость.
Создайте расчет цены и количества для одного товара и скопируйте его для всех остальных. Здесь на помощь приходят относительные ссылки.
Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3:D8). Когда вы сделаете это, вы заметите, что адрес автоматически корректируется, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в ячейке D4 — B4*C4.
Абсолютная ссылка на ячейку.
Абсолютная ссылка в Excel имеет знак доллара ($) в координатах строки или столбца, например $A$1 или $A$1:$B$20.
Знак доллара, добавленный перед любой из координат, делает адрес абсолютным (то есть предотвращает изменение номера строки и столбца).
Он остается неизменным при копировании расчета в другие ячейки. Это особенно полезно, когда вы хотите выполнить несколько вычислений значения по определенному адресу или когда вам нужно скопировать формулу без изменения ссылок.
Это может быть в том случае, когда у вас есть фиксированное значение, которое необходимо повторно использовать (например, ставка налога, ставка комиссии, количество месяцев, сумма скидки и т д)
Например, чтобы умножить числа в столбце B на скидку F2, введите следующую формулу в строке 2, а затем скопируйте ее, перетащив маркер заполнения:
=B2*$F$2
Относительная ссылка (B2) будет меняться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ссылка ($F$2) всегда будет иметь один и тот же адрес:
Конечно, вы можете жестко закодировать 10% скидку в своем выражении, и это решит проблему копирования. Но если впоследствии вам понадобится изменить процент скидки, вам придется искать и корректировать все формулы. И убедитесь, что вы случайно не пропустите несколько. Поэтому такие константы принято писать отдельно и использовать абсолютные ссылки на них.
Таким образом, относительная ссылка на ячейку отличается от абсолютной тем, что копирование или перемещение формулы изменяет ее.
Абсолютные ссылки всегда указывают на конкретный адрес, где бы они ни находились.
Смешанная ссылка.
Смешанные ссылки немного сложнее, чем абсолютные и относительные.
Смешанные ссылки могут быть двух типов:
- Строка заблокирована, а столбец изменен на копии.
- Столбец фиксируется, а строка изменяется.
Смешанная ссылка содержит относительную и абсолютную координату, например $A1 или A$1.
Как вы, возможно, помните, абсолютная ссылка содержит 2 знака доллара ($), фиксирующие как столбец, так и строку. В смешанном фиксирована только одна координата (абсолютная), а другая (относительная) будет меняться в зависимости от нового местоположения:
- Абсолютный столбец и относительная строка, например $A1. Когда выражение с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца устанавливает ссылку на указанный столбец, чтобы он никогда не менялся. Относительная ссылка на строку без знака доллара изменится в зависимости от того, в какую строку копируется формула.
- Относительный столбец и абсолютная строка, например A$1. Здесь адресация к строке будет зафиксирована, а к столбцу изменится.
Может быть много ситуаций, когда нужно зафиксировать только одну координату: столбец или строку.
Например, чтобы умножить столбец цен (столбец B) на 3 разных бренда (C2, D2 и E2), вы должны поместить следующую формулу в C3, а затем скопировать ее вправо, а затем вниз:
=$B3*(1+C$2)
Теперь вы можете использовать возможности смешанной ссылки для расчета всех этих цен по единой формуле.
В первом множителе фиксируем столбик. Поэтому при копировании прямо по строке адрес $B3 не изменится: ведь строка по-прежнему третья.
Но во втором множителе мы ставим знак доллара перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2, в итоге в D3 получим:
=$B3*(1+D$2)
А при копировании вниз все будет наоборот: $B3 изменится на $B4, $B5 и т.д., а D$2 не изменится, так как строка «заморожена». В итоге в С4 получаем:
=$B4*(1+C$2)
Самый приятный момент в том, что формулу мы записываем один раз, а потом просто копируем на всю таблицу. Мы экономим много времени.
А если ваши отметки вдруг изменятся, просто поменяйте цифры в C2:E2 и проблема решится практически моментально.
Как изменить ссылку с относительной на абсолютную (или смешанную)?
Чтобы перейти от относительного к абсолютному и наоборот, вы можете добавить или удалить знак $ вручную. Или вы можете использовать функциональную клавишу F4:
- Дважды щелкните ячейку, содержащую формулу.
- Выберите ссылку, которую хотите изменить.
- Нажмите F4 для переключения между четырьмя типами ссылок.
Повторное нажатие F4 будет циклически переключать их в следующем порядке:
Если выбрать относительную ссылку без знака $, например A1, последовательное нажатие F4 приведет к переключению между абсолютной ссылкой с двумя знаками доллара $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем обратно к A1 снова.
Примечание. Если вы нажмете F4, не выделив ничего определенного, ячейка слева от указателя мыши будет автоматически выделена и там будет изменен тип ссылки.
Имя как разновидность абсолютной ссылки.
Отдельная ячейка или диапазон также могут быть идентифицированы по имени. Для этого просто выберите ячейку, введите имя в поле «Имя» и нажмите клавишу «Ввод.
В нашем примере поместите курсор на F2, а затем дайте этому адресу имя, как показано на рисунке выше. В этом случае вы можете использовать только буквы, цифры и знаки подчеркивания, которые могут заменить пробел. Знаки препинания и специальные символы не допускаются.
Вы можете использовать его в расчетах рабочей тетради.
=B2*скидка
Естественно, это своего рода абсолютная ссылка, так как за каждым именем жестко закреплены координаты определенной ячейки или диапазона.
Формула становится более понятной и читаемой.
Ссылка на столбец.
Как и в случае с отдельными ячейками, на весь столбец можно ссылаться абсолютно или относительно, например:
- Абсолютная ссылка на столбец — $A:$A
- Родственник — А:А
Когда вы используете знак доллара ($) в абсолютной ссылке на столбец, его адрес не изменится при копировании в другое место.
Относительная ссылка на столбец изменится при копировании или перемещении формулы по горизонтали и останется неизменной при копировании в другие ячейки в том же столбце (по вертикали).
Теперь давайте посмотрим на это на примере.
Допустим, у вас есть несколько чисел в столбце B, и вы хотите узнать их общее и среднее значение. Проблема в том, что каждую неделю в таблицу добавляются новые данные, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек — не лучший вариант. Вместо этого вы можете запросить весь столбец B:
=SUM($D:$D) — используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, который соединяет формулу со столбцом B.
=СУММ(D:D) — напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, который изменится при копировании.
Наконечник. При вводе формулы щелкните букву заголовка (например, D), чтобы добавить ссылку сразу на весь столбец. Как и в случае с ячейками, программа по умолчанию вставляет относительную ссылку (без знака $):
Примечание. При использовании полной ссылки на столбец никогда не вводите формулу в тот же столбец, на который она ссылается. Например, может показаться хорошей идеей ввести =СУММ(D:D) в одну из самых нижних пустых ячеек в том же столбце D, чтобы получить окончательный результат внизу таблицы. Не делай этого! Это создаст так называемую циклическую ссылку, и вы получите результат 0.
Ссылка на строку.
Чтобы сослаться сразу на всю строку, используйте тот же подход, что и со столбцами, за исключением того, что вместо букв столбцов введите номера строк:
- Абсолютная ссылка на строку — $1:$1
- Относительный — 1:1
Пример 2: Ссылка на всю строку (абсолютную и относительную)
Если данные на вашем листе расположены горизонтально, а не вертикально, вы можете запросить всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:
=AVERAGE($3:$3) — абсолютная ссылка на всю строку задается с помощью знака доллара ($).
=AVERAGE(3:3): относительная ссылка на строку изменится при копировании вниз.
В этом примере нам нужна относительная ссылка. Ведь у нас есть 6 строк данных и мы хотим рассчитать среднюю стоимость каждого товара отдельно. Запишем в B12 расчет средней цены яблок и скопируем его:
Для бананов (B13) расчет уже будет таким: СРЕДНИЙ (4:4). Как видите, номер строки изменился автоматически.
Ссылка на столбец, исключая первые несколько строк.
Это очень актуальная тема, потому что очень часто первые несколько строк рабочего листа содержат вводное предложение, заголовок таблицы или пояснительную информацию, которую вы не хотите включать в свои расчеты. К сожалению, Excel не поддерживает ссылки D3:D, которые включают все данные в столбце D, начиная только со строки 3. Если вы попытаетесь добавить такую конструкцию, ваша формула, скорее всего, вернет #NAME?.
Вместо этого вы можете указать максимальную строку для вашей ссылки, чтобы включить все возможные адреса в данном столбце. В Excel 2019–2007 максимальное количество строк — 1 048 576 и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.
Итак, чтобы найти сумму продаж в таблице ниже (столбец «Стоимость»), вы можете использовать выражение:
=СУММ(D3:D1048576)
Кроме того, вы можете вычесть данные, которые хотите исключить из общего количества:
=СУММ(Д:Д)-СУММ(Д1:Д2)
Но первый вариант предпочтительнее, так как SUM(D:D) занимает больше времени и требует больше вычислительных ресурсов, чем SUM(D3:D1048576).
Смешанная ссылка на весь столбец.
Как я упоминал выше, вы также можете создать смешанную ссылку на весь столбец или на всю строку:
- Перемешать по столбцу, например, $A:A
- Смешанный в строке, например $ 1: 1
Теперь посмотрим, что будет, если скопировать формулу с этими адресами в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какую-то ячейку, в данном примере F3. Когда вы копируете формулу вправо (в G3), она меняется на =SUM($B:C), потому что первая B имеет знак $ и остается фиксированной, а вторая B является нормальной и, следовательно, изменяется.
В результате Excel сложит все числа в столбцах B и C. Ну а двигаясь дальше вправо, можно найти сумму трех столбцов.
Реклама! Не используйте слишком много ссылок на целые столбцы или строки на листе, так как это может значительно замедлить работу Excel.
Надеюсь, теперь вы полностью понимаете, что такое относительные и абсолютные ссылки на ячейки, и формула со знаками $ больше не является загадкой.