Относительные и абсолютные ссылки — как создавать и редактировать
В руководстве объясняется, что такое адрес ячейки, как правильно писать абсолютные и относительные ссылки в 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, чтобы войти в режим редактирования, или дважды щелкните саму ячейку. Это выделит каждую ячейку или диапазон, к которому относится формула, другим цветом.
- Чтобы изменить адрес, выполните одно из следующих действий:
- Выберите адрес в формуле и введите новый вручную.
- После выбора ссылки используйте мышь, чтобы вместо этого указать другой адрес или диапазон на листе.
- Чтобы включить больше или меньше ячеек в ссылку на диапазон, перетащите правый нижний угол:
Нажмите Enter.
Как сделать перекрестную ссылку?
Чтобы ссылаться на ячейки в другом листе или файле Excel, вам необходимо указать не только целевую ячейку, но также лист и книгу, в которых они находятся. Это можно сделать с помощью так называемой внешней ссылки.
Чтобы обратиться к данным на другом листе, введите имя этого целевого листа с восклицательным знаком (!) Перед адресом ячейки или диапазона.
Например, вот как вы можете создать ссылку на адрес A1 на листе Sheet2 в той же книге Excel:
= Лист2! A1
Если имя рабочего листа содержит пробелы или неалфавитные символы, оно должно быть заключено в одинарные кавычки, например:
= ‘Новый лист’! A1
Чтобы избежать возможных опечаток и ошибок, вы можете заставить Excel автоматически сгенерировать для вас внешнюю ссылку. Вот как:
- Начните печатать в ячейке. Обратите внимание на знак «=».
- Щелкните вкладку листа, на которую нужно создать ссылку, затем выберите ячейку или диапазон ячеек на этом листе.
- Завершите ввод и нажмите Enter.
Как сослаться на другую книгу?
Чтобы сослаться на ячейку или диапазон ячеек в другом файле Excel, необходимо заключить имя книги в квадратные скобки, за которыми следует имя листа, восклицательный знак и адрес ячейки или диапазона.
Например:
= [Book1.xlsx] Sheet1! A1
Если имя файла или листа содержит неалфавитные символы, обязательно заключите путь в одинарные кавычки, например
= ‘[Новый файл.xlsx] Sheet1’! A1
Как и в случае ссылки на другой лист, вам не нужно вводить все вручную. Более быстрый способ — начать писать формулу, затем перейти к другой книге и выбрать в ней ячейку или диапазон. Нажмите Ввод.
Итак, мы научились делать самые простые соединения. А теперь давайте посмотрим, что они из себя представляют.
В Excel есть три типа ссылок на ячейки: относительные, абсолютные и смешанные. Вы можете использовать любой из них в своих расчетах. Но если вы собираетесь скопировать зарегистрированное выражение в другое место на листе, то здесь нужно быть осторожным. Важно использовать правильный тип адреса, потому что относительные и абсолютные ссылки ведут себя по-разному при передаче и копировании.
Относительная ссылка на ячейку.
Относительная ссылка является самой простой и включает координаты строки и столбца, например A1 или A1: D10. По умолчанию все адреса ячеек в Excel являются относительными.
Пример:
= LA2
Это более простое выражение указывает программе отображать значение, записанное в первом столбце (A) и во второй строке (2). Используя снимок экрана чуть ниже, если бы эта формула была введена в ячейку D1, она отобразила бы число «8», поскольку это значение находится в ячейке A2.
При перемещении или копировании относительные ссылки изменяются в зависимости от относительного положения строк и столбцов. Другими словами, насколько новая позиция изменилась по сравнению с исходной.
Итак, если вы хотите повторить один и тот же расчет для одного и того же типа данных по вертикали или горизонтали, вам необходимо использовать относительные ссылки.
Например, чтобы сложить числа в A2 и B2, вы должны ввести их в C2: = A2 + B2. При копировании из строки 2 в строку 3 выражение меняется на = A3 + B3.
Относительные ссылки полезны и удобны, потому что, если у вас есть данные одного типа, с которыми вам нужно выполнять те же операции, вы можете создать формулу один раз, а затем просто скопировать ее для всех данных.
Например, очень удобно умножить количество и цену различных товаров в таблице таким образом, чтобы найти их стоимость.
Создайте расчет, чтобы умножить цену на количество для одного товара и скопировать его для всех остальных. Здесь в игру вступают относительные ссылки.
Вместо того, чтобы вводить формулу для всех ячеек по очереди, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что URL-адрес автоматически настроен для ссылки на соответствующую строку. Например, формула в ячейке 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 не изменится, так как строка «заморожена». Следовательно, в C4 получаем:
= $ B4 * (1 + C $ 2)
Самым приятным моментом является то, что формулу мы пишем только один раз, а потом просто копируем на всю таблицу. Мы экономим много времени.
А если ваши поля внезапно изменятся, просто измените числа в C2: E2, и проблема будет решена практически мгновенно.
Как изменить ссылку с относительной на абсолютную (или смешанную)?
Чтобы переключаться между относительным и абсолютным, вы можете вручную добавить или удалить знак $. В качестве альтернативы вы можете использовать функциональную клавишу F4:
- Дважды щелкните ячейку, содержащую формулу.
- Выберите ссылку, которую хотите отредактировать.
- Нажмите F4 для переключения между четырьмя типами справочников.
При повторном нажатии F4 выполняется прокрутка в следующем порядке:
Если вы выберете беззнаковую относительную ссылку, такую как A1, многократное нажатие F4 приведет к переходу от абсолютной ссылки с двумя символами доллара $ A $ 1 к абсолютной строке A $ 1, к абсолютному столбцу $ A1, а затем обратно к A1.
Примечание. Если вы нажмете F4, не выбрав ничего конкретного, ячейка слева от указателя мыши будет автоматически выбрана, и тип ссылки там изменится.
Имя как разновидность абсолютной ссылки.
Отдельную ячейку или диапазон также можно идентифицировать по имени. Для этого просто выберите ячейку, введите имя в поле «Имя» и нажмите клавишу Enter.
В нашем примере установите курсор на F2, а затем дайте этому адресу имя, как показано на рисунке выше. В этом случае вы можете использовать только буквы, цифры и знак подчеркивания, которые можно использовать для замены пробела. Знаки препинания и служебные символы не допускаются.
Вы можете использовать его в своих расчетах в рабочей книге.
= B2 * скидка
Конечно, это своего рода абсолютная ссылка, поскольку каждое имя кодируется координатами определенной ячейки или диапазона.
При этом формула становится более понятной и читаемой.
Ссылка на столбец.
Как и в случае с отдельными ячейками, вы можете ссылаться на весь столбец как в абсолютном, так и в относительном выражении, например:
- Абсолютная ссылка на столбец: $ A: $ A
- Родственник — LA: LA
Когда вы используете знак доллара ($) в абсолютной ссылке на столбец, его адрес не изменится при копировании в другое место.
Относительная ссылка на столбец изменится при копировании или перемещении формулы по горизонтали и останется неизменной при копировании в другие ячейки в том же (вертикальном) столбце).
Теперь посмотрим на это на примере.
Предположим, у нас есть некоторые числа в столбце B, и мы хотим узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому написание обычной формулы SUM () или AVERAGE () для фиксированного диапазона ячеек — не лучший вариант. Вместо этого вы можете обратиться ко всему столбцу B:
= СУММ ($ D: $ D) — используйте знак доллара ($), чтобы создать абсолютную ссылку на уровне столбца, которая связывает формулу со столбцом B.
= СУММ (D: D) — напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, который изменится после копирования.
Совет. При написании формулы щелкните букву заголовка (например, D), чтобы одновременно добавить ссылку на весь столбец. Как и в случае с ячейками, по умолчанию программа вставляет относительную ссылку (без знака $):
Примечание. При ссылке на весь столбец никогда не вводите формулу в том же столбце, на который указывает ссылка. Например, может показаться хорошей идеей ввести = СУММ (D: D) в одну из нижних пустых ячеек в том же столбце D, чтобы получить окончательный результат в конце таблицы. Не делай этого! Это создаст так называемую круговую ссылку, и вы получите результат 0.
Ссылка на строку.
Чтобы ссылаться на всю строку одновременно, вы используете тот же подход, что и для столбцов, за исключением того, что вы вводите номера строк вместо букв столбцов:
- Абсолютная ссылка на строку — $ 1: $ 1
- Относительная — 1: 1
Пример 2. Ссылка на всю строку (абсолютная и относительная)
Если данные на листе расположены по горизонтали, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:
= СРЕДНЕЕ ($ 3: $ 3) — абсолютная ссылка на всю строку фиксируется знаком доллара ($).
= СРЕДНЕЕ (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)
В качестве альтернативы вы можете вычесть данные, которые хотите исключить, из общей суммы:
= СУММ (G: D) -SUM (D1: D2)
Но первый вариант предпочтительнее, потому что СУММ (D: D) занимает больше времени и требует больше вычислительных ресурсов, чем СУММ (D3: D1048576).
Смешанная ссылка на весь столбец.
Как упоминалось выше, вы также можете создать смешанную ссылку для всего столбца или всей строки:
- Смешано по столбцу, например $ A: A
- Смешано по строкам как $ 1: 1
Теперь посмотрим, что произойдет, если вы скопируете формулу с этими адресами в другие ячейки. Предположим, вы вводите формулу = СУММ ($ B: B) в ячейку, в этом примере F3. Когда вы копируете формулу вправо (в G3), она изменяется на = SUM ($ B: C), потому что первый B равен $ и остается неподвижным, а второй B является регулярным и поэтому изменяется.
В результате Excel просуммирует все числа в столбцах B и C. Что ж, двигаясь дальше вправо, вы можете найти сумму трех столбцов.
Предупреждение! Не используйте слишком много целочисленных ссылок на столбцы или строки на листе, так как это может значительно замедлить работу Excel.
Надеюсь, теперь вы полностью понимаете, что такое относительные и абсолютные ссылки на ячейки, и формула знака $ больше не является загадкой.
Спасибо за чтение и надеюсь увидеть вас в нашем блоге!