Создание относительной и абсолютной адресации электронных книг в MS Excel

Обновлено: 07.07.2024

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

Например, A1 будет относиться к первой строке (обозначенной как 1) и первому столбцу (обозначенной как A). Точно так же B3 будет третьей строкой и вторым столбцом.

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

Теперь в Excel можно использовать три типа ссылок на ячейки:

Относительные ссылки на ячейки
Абсолютные ссылки на ячейки
Смешанные ссылки на ячейки

Понимание этих различных типов ссылок на ячейки поможет вам работать с формулами и сэкономить время (особенно при копировании и вставке формул).

Это руководство охватывает:

Что такое относительные ссылки на ячейки в Excel?

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

Предположим, у меня есть набор данных, показанный ниже:

Чтобы рассчитать общую сумму для каждого товара, нам нужно умножить цену каждого товара на количество этого товара.

Для первого элемента формула в ячейке D2 будет следующей: B2*C2 (как показано ниже):

Теперь вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3:D8). Когда вы это сделаете, вы заметите, что ссылка на ячейку автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 станет B3*C3, а формула в ячейке D4 станет B4*C4.

Эти ссылки на ячейки, которые корректируются при копировании ячейки, называются относительными ссылками на ячейки в Excel.

Когда использовать относительные ссылки на ячейки в Excel?

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

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

Что такое абсолютные ссылки на ячейки в Excel?

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

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

Комиссия составляет 20% и указана в ячейке G1.

Чтобы получить сумму комиссии за каждую продажу товара, используйте следующую формулу в ячейке E2 и скопируйте ее во все ячейки:

Обратите внимание, что в ссылке на ячейку с комиссией есть два знака доллара ($) — $G$2.

Что означает знак доллара ($)?

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

Например, в приведенном выше случае, когда я копирую формулу из ячейки E2 в E3, она меняется с =D2*$G$1 на =D3*$G$1.

Обратите внимание, что хотя D2 меняется на D3, $G$1 не меняется.

Поскольку мы добавили символ доллара перед буквами "G" и "1" в G1, это не позволит изменить ссылку на ячейку при ее копировании.

Следовательно, это делает ссылку на ячейку абсолютной.

Когда использовать абсолютные ссылки на ячейки в Excel?

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

Хотя вы также можете жестко закодировать это значение в формуле (например, использовать 20% вместо $G$2), наличие его в ячейке, а затем использование ссылки на ячейку позволяет изменить его в будущем.

Например, если ваша структура комиссионных изменилась и теперь вы платите 25% вместо 20%, вы можете просто изменить значение в ячейке G2, и все формулы автоматически обновятся.

Что такое смешанные ссылки на ячейки в Excel?

Смешанные ссылки на ячейки немного сложнее, чем абсолютные и относительные ссылки на ячейки.

Могут быть два типа смешанных ссылок на ячейки:

Строка блокируется, а столбец изменяется при копировании формулы.
Столбец блокируется, а строка изменяется при копировании формулы.

Давайте посмотрим, как это работает, на примере.

Ниже приведен набор данных, в котором вам нужно рассчитать три уровня комиссии на основе процентного значения в ячейках E2, F2 и G2.

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

Введите приведенную ниже формулу в ячейку E4 и скопируйте ее во все ячейки.

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

Давайте проанализируем каждую ссылку на ячейку и поймем, как она работает:

$B4 (и $C4) — в этой ссылке знак доллара стоит прямо перед обозначением столбца, но не перед номером строки. Это означает, что когда вы копируете формулу в ячейки справа, ссылка останется такой же, как фиксированный столбец. Например, если вы скопируете формулу из E4 в F4, эта ссылка не изменится. Однако при копировании номер строки изменится, поскольку он не заблокирован.
E$2 — в этой ссылке знак доллара стоит прямо перед номером строки, а в обозначении столбца знак доллара отсутствует. Это означает, что при копировании формулы вниз по ячейкам ссылка не изменится, поскольку номер строки заблокирован. Однако если вы скопируете формулу вправо, алфавит столбца изменится, поскольку он не заблокирован.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

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

Например, A1 — это относительная ссылка на ячейку, и она станет абсолютной, если вы сделаете ее $A$1.

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

Однако это можно сделать быстрее, нажав клавишу F4.

Когда вы выбираете ссылку на ячейку (в строке формул или в ячейке в режиме редактирования) и нажимаете F4, ссылка меняется.

поиск меню

Урок 4. Относительные и абсолютные ссылки на ячейки

Введение

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

Необязательно: загрузите файл примера для этого урока.

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

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

По умолчанию все ссылки на ячейки являются относительными. При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

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

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

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.

В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.

При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными, абсолютными и смешанными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В нашем примере мы будем использовать ставку налога с продаж в размере 7,5 % в ячейке E1 для расчета налога с продаж для всех товаров в столбце D. Нам потребуется использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

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

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

Использование ссылок на ячейки с несколькими листами

Большинство программ для работы с электронными таблицами позволяют ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на конкретное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.

Обратите внимание: если имя рабочего листа содержит пробел, вам нужно будет заключить имя в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.

Чтобы ссылаться на ячейки на листах:

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

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

поиск меню

Урок 15. Относительные и абсолютные ссылки на ячейки

Введение

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

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

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

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

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

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В приведенном ниже примере мы собираемся использовать ячейку E2 (которая содержит налоговую ставку 7,5%) для расчета налога с продаж для каждого элемента в столбце D. Чтобы убедиться, что ссылка на налоговую ставку остается постоянной — даже когда формула копируется и заполняется другими ячейками — нам нужно сделать ячейку $E$2 абсолютной ссылкой.

Обязательно добавляйте знак доллара ($) всякий раз, когда делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Из-за этого Excel интерпретировал ее как относительную ссылку, что приводило к неверному результату при копировании в другие ячейки.

Использование ссылок на ячейки с несколькими листами

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

Обратите внимание, что если имя рабочего листа содержит пробел, его необходимо заключить в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.

Чтобы ссылаться на ячейки на листах:

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

Оглавление

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

Формулы относительных ссылок являются наиболее простым и широко используемым типом формул. В этом типе ссылок мы не используем «$» перед ссылками. Здесь, когда мы используем автозаполнение для автоматического ввода формулы в диапазоне, Excel распознает шаблон в формуле и заполняет формулу в соответствии с этим шаблоном.

Давайте разберемся с относительными ссылками на примере

В приведенном ниже примере нам нужно рассчитать таблицу умножения в Excel.

Итак, для расчета первого элемента формула будет выглядеть так:

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

Итак, как это происходит?

На самом деле, когда вы вводите первую формулу как =A2*C2, Excel распознает шаблон, т. е. четыре ячейки слева от текущей ячейки, умноженные на 2 ячейки слева от текущей ячейки. Теперь, когда вы перетаскиваете эту формулу в конец, Excel заполняет формулу в соответствии с предыдущим шаблоном.

Это называется относительными ссылками, поскольку шаблон зависит от текущей ячейки.

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

Формулы абсолютной ссылки обычно используются реже, чем формулы относительной ссылки. Здесь мы не хотим, чтобы Excel заполнил формулу, используя какой-либо шаблон, и, следовательно, мы храним определенные строки, столбцы или ячейки в качестве постоянной ссылки (которую не следует изменять). Это делается с помощью знака «$» перед ссылкой.

< tbody>

$A1	Это позволяет изменять ссылку на строку, но ссылка на столбец будет постоянной.
A$1	Это позволяет изменять ссылку на столбец, но ссылка на строку будет постоянной.
$A$1	Ссылки как на строки, так и на столбцы являются постоянными.

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

Давайте разберемся с абсолютными ссылками на примере

На изображении ниже мы должны создать массив таблицы умножения.

Теперь очевидно, что в первой ячейке формула будет

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

Итак, почему это происходит?

Как я и предсказывал, Excel распознает шаблон в формулах, а затем заполняет тот же шаблон в других ячейках, когда вы перетаскиваете формулу. В этом случае происходит то же самое, когда вы пишете формулу для первой ячейки (т.е. B5), ваша формула:

Excel распознает это как шаблон: одна ячейка поверх текущей ячейки, умноженная на одну ячейку слева от текущей ячейки.

Но это не то, что вы хотите, чтобы Excel делал, поэтому вы будете использовать абсолютные ссылки, чтобы заблокировать ссылки.

На изображении выше я использовал формулу, состоящую из абсолютных ссылок, для расчета результатов. Формула:

Теперь, если вы перетащите эту формулу, вы увидите, что она всегда дает правильный результат.

При перетаскивании этой формулы (=B$4*$A5) . Первая часть, то есть B$4, содержит постоянную ссылку на строку, но ссылка на столбец может измениться, т. е. это означает, что она всегда будет указывать на ячейки в желтой строке. Вторая часть, т.е. ($A5), содержит ссылку на столбец постоянную, но ссылка на строку может измениться, это гарантирует, что вторая часть в формуле всегда указывает на ячейки в зеленой строке. И, следовательно, он дает правильный результат.

Это называется абсолютной ссылкой.

Ярлык для установки абсолютных ссылок в Excel

Нажатие клавиши F4 во время написания формулы делает ссылки абсолютными. Многократное нажатие клавиши F4 приводит к циклическому перебору всех возможных типов абсолютных ссылок для текущей формулы.

Итак, речь шла об относительных и абсолютных ссылках Excel.

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Читайте также: