Абсолютные смешанные и относительные ссылки: Относительные, абсолютные и смешанные ссылки — урок. Информатика, 9 класс.

1.3. Относительные, абсолютные и смешанные ссылки

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

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

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

Как вам сделать абсолютную ссылку в Excel?

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

Абсолютная ссылка выглядит следующим образом: знак «$» стоит перед буквой и перед цифрой.

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

Полезный совет! Чтобы не вводить символ «$» вручную, при вводе используйте клавишу F4 как переключатель между всеми типами адресов.

Например, если нужно изменить ссылку с относительной на абсолютную:

1. Просто выделите ячейку, формулу в которой необходимо выполнить замену.
2. Перейдите в строку формул и поставьте там курсор непосредственно на адрес.
3. Нажмите на «F4». Вы заметите, так программа автоматически предлагает вам разные варианты и расставляет знак доллара.
4. Выбирайте подходящую вам ссылку, периодически нажимая F4 и жмите на «Enter».

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



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

По умолчанию (стандартно) все ссылки в Excel относительные. Они выглядят вот так: =А2 или =B2 (только цифра+буква):

Если мы решим скопировать эту формулу из строки 2 в строку 3 адреса в параметрах формулы изменяться автоматически:

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

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

1. Выделяем нужную нам ячейку.
2. Нажимаем Ctrl+C.
3. Выделяем ячейку, в которую необходимо вставить относительную формулу.
4. Нажимаем Ctrl+V.

Полезный совет! Если у вас однотипный расчет, можно воспользоваться простым «лайфхаком». Выделяем ячейку , после этого подводим курсор мышки к квадратику расположенному в правом нижнем углу. Появится черный крестик. После этого просто «протягиваем» формулу вниз. Система автоматически скопирует все значения. Данный инструмент называется маркером автозаполнения.

Или еще проще: диапазон ячеек, в которые нужно проставить формулы выделяем так чтобы активная ячейка была на формуле и нажимаем комбинацию горячих клавиш CTRL+D.

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

Как поставить смешанную ссылку в Excel?

Сначала разложим все по порядку. Смешанные ссылки могут быть всего 2-х видов:

Чтобы сделать ссылку относительной, используйте эффективные способы. Конечно, вы можете вручную проставить знаки «$» (переходите на английскую раскладку клавиатуры, после чего жмете SHIFT+4).

Сначала выделяете ячейку со ссылкой (или просто ссылку) и нажимаете на «F4». Система автоматически предложит вам выбор ячейки, останется только одобрительно нажать на «Enter».

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

Относительные, абсолютные и смешанные ссылки (9 класс) Информатика и ИКТ

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

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

Так, при копировании формулы из активной ячейки C1, содержащей относительные ссылки на ячейки A1 и B1, в ячейку D2 значения столбцов и строк в формуле изменятся на один шаг вправо и вниз. При копировании формулы из ячейки C1 в ячейку Е3 значения столбцов и строк в формуле изменятся на два шага вправо и вниз и т. д.

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

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

Смешанные ссылки.
В формуле можно использовать смешанные ссылки, в которых координата столбца относительная, а строки — абсолютная (например, А$1), или, наоборот, координата столбца абсолютная, а строки – относительная (например, $B1).

Контрольные вопросы:
Как изменяется при копировании в ячейку, расположенную в соседнем столбце и строке, формула, содержащая относительные ссылки? Абсолютные ссылки? Смешанные ссылки?

Содержание

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

Абсолютные и относительные ссылки в Excel используются при создании формул, которые ссылаются на другую ячейку или диапазон. Если в формуле Excel используется знак доллара ($) в ссылках на ячейку, то многих пользователей Excel это путает. Но этот знак имеет простое объяснение – он обозначает, следует ли изменять или нет ссылку, когда формула копируется в другие ячейки, и в свою очередь информирует пользователя, что данная ссылка является абсолютной ссылкой. В данной статье мы подробно рассмотрим, как использовать данную возможность, чем отличаются относительные ссылки от абсолютных ссылок, и что такое смешанные ссылки.

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

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

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

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

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

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

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

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

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

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

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

Относительная ссылка – это обычная ссылка, которая содержит в себе букву (столбец) и номер (строка) без знака $, например, D14, G5, A3 и т.п. Основная особенность относительных ссылок заключается в том, что при копировании (заполнении) ячеек в электронной таблице, формулы, которые в них находятся, меняют адрес ячеек относительно нового места. По умолчанию все ссылки в Excel являются относительными ссылками. В следующем примере показано, как работают относительные ссылки.

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

= A1*10

Если вы скопируете эту формулу в другую строку в том же столбце, например, в ячейку 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

Например, если у вас есть значение 10 в ячейке A1, и вы используете абсолютную ссылку на ячейку ($A$1), формула = $A$1+5 всегда будет возвращать число 15, независимо от того, в какие ячейки копируется формула. С другой стороны, если вы пишете ту же формулу с относительной ссылкой на ячейку (A1), а затем скопируете ее в другие ячейки в столбце, для каждой строки будет вычисляться другое значение. Следующее изображение демонстрирует разницу абсолютных и относительных ссылок в MS 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 (скидку). В данной формуле используются две смешанные ссылки:

$D3 — Абсолютный столбец и относительная строка

E$2 — Относительный столбец и абсолютная строка

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

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

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

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

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

Ссылки в Excel — абсолютные, относительные и смешанные. Ошибки при работе с относительными ссылками в Эксель

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

1. Относительные ссылки. Применяются для простейших расчетов. Копирование формулы влечет за собой изменение координат.
2. Абсолютные ссылки. При необходимости производства более сложных расчетов подойдет данный вариант. Для фиксации используют символ «$». Пример: $A$1.
3. Смешанные ссылки. Данный тип адресаций при проведении расчетов используется при необходимости в закреплении столбика или строчки по отдельности. Пример: $A1 или A$1.

Отличительные особенности разных видов ссылок

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

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

Это набор символов, определяющих местоположение ячейки. Ссылки в программе автоматически пишутся с относительной адресацией. К примеру: A1, A2, B1, B2. Перемещение в другую строку или столбец ведет к изменению символов в формуле. К примеру, исходная позиция A1. При перемещении по горизонтали изменяется буква на B1, C1, D1 и т.д. Таким же образом происходят изменения при смещении по вертикальной линии, только в данном случае меняется цифра – A2, A3, A4 и т.д. При необходимости дублирования однотипного расчета в соседнюю клетку проводится расчет по относительной ссылке. Для применения данной функции выполните несколько действий:

1. Как только данные будут вписаны в ячейку, наведите курсор и сделайте клик мышкой. Выделение зеленым прямоугольником говорит об активации ячейки и готовности к проведению дальнейших работ.
2. Нажатием комбинацией клавиш Ctrl + C проводим копирование содержимого в буфер обмена.
3. Активируем ячейку, в которую необходимо перенести данные или ранее записанную формулу.
4. Нажатием комбинации Ctrl + V переносим данные, сохраненные в буфере обмена системы.

Пример создания относительной ссылки в таблице к спортивному товару

Совет эксперта! Для проведения однотипных расчетов в таблице воспользуйтесь лайфхаком. Выделите ячейку с ранее введенной формулой. Наведя курсор на маленький квадратик, появившийся в правом углу снизу, и удерживая левую кнопку мыши, тянем до нижней строки или крайнего столбца, в зависимости от выполняемого действия. Отпустив клавишу на мышке, расчет будет произведен автоматически. Данный инструмент носит название – маркер автоматического заполнения.

Пример относительной ссылки

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

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

Порядок выполнения действий:

1. На примере видно, что для заполнения количества проданного товара и его цены, использованы колонки B и C. Соответственно, для записи формулы и получения ответа выбираем колонку D. Формула выглядит следующим образом: = B2*C

Обратите внимание! Чтобы облегчить процесс написания формулы, воспользуйтесь небольшой хитростью. Поставьте знак «=», кликните по количеству проданного товара, установите знак «*» и нажмите на цену продукции. Формула после знака равенства пропишется автоматически.

2. Чтобы получить окончательный ответ, нажмите на «Enter». Далее необходимо рассчитать итоговую сумму полученной прибыли с остальных видов продукции. Хорошо если количество строк не велико, тогда все манипуляции можно выполнить вручную. Для заполнения одновременно большого количества строк в Excel имеется одна полезная функция, дающая возможность переноса формулы в другие ячейки.
3. Наведите курсор на правый нижний угол прямоугольника с формулой или готовым результатом. Появление черного крестика служит сигналом, что курсор можно тянуть вниз. Таким образом производится автоматический расчет полученной прибыли на каждую продукцию в отдельности.
4. Отпустив зажатую кнопку мыши, получаем правильные результаты во всех строчках.

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

Кликнув по ячейке D3, можно увидеть, что координаты ячеек были автоматически изменены, и выглядят теперь следующим образом: =B3*C3. Из этого следует, что ссылки были относительными.

Возможные ошибки при работе с относительными ссылками

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

1. Создаем таблицу и заполняем: A – наименование продукции; B – количество проданного; C – стоимость; D – вырученная сумма. Допустим, в ассортименте всего 11 наименований продукции. Следовательно, с учетом описания столбцов, заполняется 12 строк и общая сумма прибыли – D
2. Кликаем по ячейке E2 и вписываем =D2/D13.
3. После нажатия кнопки «Enter» появляется коэффициент относительной доли продаж первого наименования.
4. Растягиваем столбец вниз и ждем результата. Однако система выдает ошибку «#ДЕЛ/0!»

Код ошибки как результат неправильно введенных данных

Причина ошибки в использовании относительной ссылки для проведения расчетов. В результате копирования формулы координаты изменяются. То есть для E3 формула будет выглядеть следующим образом =D3/D13. Потому как ячейка D13 не заполнена и теоретически имеет нулевое значение, то программа выдаст ошибку с информацией, что деление на нулевое значение невозможно.

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

Как вам сделать абсолютную ссылку в Excel

Благодаря использованию символа $ стала возможна фиксация координат ячейки. Каким образом это работает, рассмотрим далее. Так как программа по умолчанию использует относительную адресацию, то соответственно, чтобы сделать ее абсолютной, потребуется выполнить ряд действий. Разберем решение ошибки «Как найти коэффициент от продажи нескольких наименований товара», выполняя расчет при помощи абсолютной адресации:

1. Кликаем по E2 и вписываем координаты ссылки =D2/D13. Так как ссылка является относительной, то для фиксации данных необходимо установить символ.
2. Зафиксируйте координаты ячейки D Для выполнения этого действия перед буквой, указывающей на столбец и номером строки, установите знак «$».

Совет эксперта! Чтобы облегчить задачу со вводом, достаточно активировать ячейку к редактированию формулы и нажать несколько раз по клавише F4. До тех пор, пока не получите удовлетворительные значения. Правильная формула имеет следующий вид: =D2/$D$13.

3. Нажмите кнопку «Enter». В результате выполненных действий должен появится правильный результат.
4. Для копирования формулы протяните маркер до нижней строки.

Правильно введенные данные формулы для абсолютной ссылки

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

Как поставить смешанную ссылку в Excel

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

• К примеру, чтобы изменить положение строки, необходимо прописать знак $ перед буквенным обозначением.
• Напротив, если знак доллара будет прописан после буквенного обозначения, то показатели в строке останутся в неизменном состоянии.

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

1. Для получения точных расчетов вводим =D1/$D$3 и нажимаем «Enter». Программа выдает точный ответ.
2. Чтобы переместить формулу в последующие ячейки вниз по столбцу и получить точные результаты, протяните маркер до нижней ячейки.
3. В результате программа выдаст правильные расчеты.

Прописываем формулу для получения смешанной ссылки с учетом всех правил

Внимание! Если установить знак $ перед буквой, то Excel выдаст ошибку «#ДЕЛ/0!», что будет означать невозможность выполнения данной операции.

“СуперАбсолютная” адресация

В конце разберем еще один пример абсолютной ссылки – «СуперАбсолютная» адресация. В чем ее особенность и отличия. Возьмем примерное число 30 и впишем его в ячейку B2. Именно это число будет главным, с ним необходимо выполнить ряд действий, например возвести в степень.

1. Для правильного выполнения всех действий впишите в столбец C формулу следующего порядка: =$B$2^$D2. В столбец D вписываем значение степеней.

Пример создания «СуперАбсолютной» адресации путем возведения числа в степень

2. После нажатия кнопки «Enter» и активации формулы растягиваем маркер вниз по столбцу.
3. Получаем правильные результаты.

Получение окончательного результата после выполнения действий

Суть в том, что все производимые действия ссылались на одну закрепленную ячейку B2, поэтому:

• Копирование формулы из ячейки C3 в ячейку E3, F3 или h4 не приведет к изменению результата. Он останется неизменным – 900.
• При необходимости вставки нового столбца произойдет изменение координат ячейки с формулой, но результат также останется неизменным.

В этом и состоит особенность «СуперАбсолютной» ссылки: при необходимости перемещения полученный результат не будет изменяться. Однако существуют ситуации, когда вставка данных производится из сторонних источников. Таким образом происходит смещение столбцов в сторону, а данные устанавливаются по-старому в столбец B2. Что же происходит в этом случае? При смешении формула изменяется согласно произведенному действию, то есть она будет указывать уже не на B2, а на C2. Но так как вставка произведена в B2, то конечный результат будет неверным.

Справка! Чтобы иметь возможность вставлять макросы со сторонних источников, необходимо подключить настройки разработчика (по умолчанию они выключены). Для этого перейдите в Параметры, откройте настройку ленты и установите галочку в правом столбце напротив «Разработчик». После этого откроется доступ ко многим функциям, ранее скрытым от глаз обычного пользователя.

Напрашивается вопрос: можно ли произвести модификацию формулы из ячейки C2 таким образом, чтобы сбор исходного числа велся из ячейки B, несмотря на вставку новых столбцов с данными? Для того чтобы изменения в таблице не влияли на определение итогового показателя при установке данных из сторонних источников, необходимо выполнить следующие действия:

1. Вместо координат клетки B2 впишите следующие показатели: =ДВССЫЛ(“B2”).$E2.
2. Благодаря данной функции ссылка всегда указывает на квадрат с координатами B2, независимо от того будут добавляться или удаляться столбцы в таблице.

Необходимо понимать, что ячейка, не содержащая никаких данных, всегда показывает значение «0».

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

—

MS Office 2007: Microsoft Excel

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

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

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

• Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
• Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
• Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе — в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

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

На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2. Если ее скопировать маркером заполнения на ячейки D3 и D4, то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ, а в ячейке D4 соответственно =В4*С4.

Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2.

Если теперь скопировать эту формулу в ячейку D3, то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис.31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7.

В результате в ячейке D1 получим Комиссионные. В ячейку D2 введем формулу =В2*С2*$В$7. Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7. Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 — нет, т.е. мы получили правильный результат.

На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого — 2, а ширина — 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2.

При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.

Типы ссылок в MS Excel

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

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

Рассмотрим пример с уровнем дохода определенной группы лиц.

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

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

Как можно убедиться из рисунка выше для Иры расчет общего дохода осуществляется путем сложения ячеек B6 и С6, хотя изначально нами была введена другая формула.

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

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

Теперь рассмотри, тот же пример, однако, добавим абсолютных ссылок. Просто переведем полученный доход в доллары США.

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

Автозаполнение было произведено и по вертикали и по горизонтали, однако ссылка на ячейку B1 не изменилась. Для того, чтобы при введении формулы ссылку из относительной переделать в абсолютную необходимо поставить знак доллара перед обозначением строки и столбца (всего 2 знака доллара). Его также можно поставить просто нажав функциональную клавишу F4 на клавиатуре предварительно установив курсор на обозначение ячейки в формуле, либо вручную с помощью Shift+4 в английской раскладке клавиатуры.

Изучив относительные и абсолютные ссылки перейдем к изучению смешанных. Смешанная ссылка по сути является на половину абсолютной на половину относительной. Обратите внимание, что имеется ввиду ссылка на одну ячейку. В заданиях выше использовалась формула, в которой присутствовала относительная ссылка и абсолютная, однако это формула с двумя типами ссылок, а никак не относительная ссылка. Смешанная ссылка обозначается одним знаком доллара в зависимости от того, что необходимо зафиксировать, если она будет абсолютной по строке, значит знак доллара необходимо поставить перед цифрой, если по столбцу – перед буквой соответственно. Как и в случае с абсолютными ссылками знак доллара можно поставить просто нажав несколько раз функциональную клавишу F4, предварительно установив курсор на обозначение ячейки в строке формул.

Рассчитаем в качестве примера общий доход сотрудников в разных валютах.

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

В качестве, закрепляющего знания, урока можно порекомендовать простые задания:

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

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

По умолчанию ссылка на ячейку является относительной ссылкой, что означает, что ссылка указывается относительно местоположения ячейки. Если, например, вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически имеете в виду ячейку, которая находится на два столбца слева (C минус A) — в той же строке (2). При копировании формулы, содержащей относительную ссылку на ячейку, эта ссылка в формуле изменится.

В качестве примера, если вы скопируете формулу = B4 * C4 из ячейки D4 в D5, формула в D5 сдвинется вправо на один столбец и станет = B5 * C5 .Если вы хотите сохранить исходную ссылку на ячейку в этом примере при ее копировании, сделайте ссылку на ячейку абсолютной, поставив перед столбцами (B и C) и строкой (2) знак доллара ( $ ). Затем, когда вы копируете формулу = $ B $ 4 * $ C $ 4 из D4 в D5, формула остается точно такой же.

Реже вам может потребоваться смешать абсолютные и относительные ссылки на ячейки, поставив перед столбцом или значением строки знак доллара, который фиксирует либо столбец, либо строку (например, $ B4 или C $ 4).

Чтобы изменить тип ссылки на ячейку:

1. Выберите ячейку, содержащую формулу.

2. В строке формул

   , выберите ссылку, которую вы хотите изменить.

3. Нажмите F4 для переключения между ссылочными типами.

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

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

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

/ ru / excelformulas / сложные-формулы / содержание /

Введение

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

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

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

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D2 .
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = B2 * C2 .
3. Нажмите Введите на клавиатуре.Формула будет выглядеть так: , вычислено , и результат будет отображен в ячейке.
4. Найдите ручку заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D2 .
5. Щелкните, удерживайте и перетащите маркер заливки на ячейки, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3: D12 .
6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с относительными ссылками , и значения будут вычислены в каждой ячейке.

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

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

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

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

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D3 .
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = (B3 * C3) * $ E $ 1 .
3. Нажмите Введите на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке.
4. Найдите ручку заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D3 .
5. Щелкните, удерживайте и перетащите маркер заливки на ячейки, которые вы хотите заполнить, в нашем примере это ячейки D4: D13 .
6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут вычислены в каждой ячейке.

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

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

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

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

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

Для ссылки на ячейки на листах:

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

1. Найдите ячейку, на которую хотите сослаться, и запишите ее рабочий лист.В нашем примере мы хотим сослаться на ячейку E14 на листе Порядок меню .
2. Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Catering Invoice .
3. Появится выбранный рабочий лист .
4. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 .
5. Введите знак равенства (=) , лист имя , за которым следует восклицательный знак (! ) и адрес ячейки .В нашем примере мы введем = ‘Menu Order’! E14 .
6. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на листе «Порядок меню», оно будет автоматически обновлено на листе «Счет-фактура кейтеринга».

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

Вызов!

1. Откройте существующую книгу Excel.Если хотите, вы можете использовать файл-пример для этого урока.
2. Создайте формулу, которая использует относительную ссылку . Если вы используете этот пример, используйте дескриптор заполнения для заполнения формулы в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
3. Создайте формулу, которая использует абсолютную ссылку . Если вы используете этот пример, исправьте формулу в ячейке D4 , чтобы ссылаться только на налоговую ставку в ячейке E2 как на абсолютную ссылку , затем используйте дескриптор заполнения, чтобы заполнить формулу из ячеек D4 до D14 .
4. Попробуйте сослаться на ячейку на листах . Если вы используете этот пример, создайте ссылку на ячейку в ячейке B3 на листе Catering Invoice для ячейки E15 на листе Menu Order .

/ ru / excelformulas / functions / content /

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

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

/ ru / excel2013 / сложные-формулы / содержание /

Введение

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

Необязательно: Загрузите нашу рабочую тетрадь.

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D2 . Выбор ячейки D2
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы наберем = B2 * C2 . Ввод формулы
3. Нажмите Введите на клавиатуре. Формула будет выглядеть так: , вычислено , и результат будет отображен в ячейке.
4. Найдите метку заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заливки для ячейки D2 . Расположение маркера заливки
5. Щелкните, удерживайте и перетащите маркер заливки на ячейки, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3: D12 . Перетаскивая маркер заполнения над ячейками D3: D12
6. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками , и значения будут вычислены в каждой ячейке.Скопированные формулы и вычисленные значения

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

Проверка скопированных формул на точность

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

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

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

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D3 . Выбор ячейки D3
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = (B3 * C3) * $ E $ 1 . Ввод формулы
3. Нажмите Введите на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки.В нашем примере мы найдем маркер заливки для ячейки D3 . Расположение маркера заливки
5. Щелкните, удерживайте и перетащите маркер заливки на ячейки, которые вы хотите заполнить: ячейки D4: D13 в наш пример. Перетаскивание маркера заполнения
6. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут вычислены в каждой ячейке. Скопированные формулы и вычисленные значения

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

Проверка формул на точность

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

Результат неверной абсолютной ссылки

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

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

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

Для ссылки на ячейки на листах:

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

1. Найдите ячейку, на которую хотите сослаться, и запишите ее рабочий лист.В нашем примере мы хотим сослаться на ячейку E14 на листе Порядок меню . Ячейка E14
2. Перейдите к нужному листу . В нашем примере мы выберем рабочий лист Catering Invoice . Переход к Sheet2
3. Появится выбранный рабочий лист .
4. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 . Выбор ячейки B2
5. Введите знак равенства (=) , лист имя , за которым следует восклицательный знак (! ) и адрес ячейки .В нашем примере мы введем = ‘Menu Order’! E14 . Ссылка на ячейку на Sheet1
6. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на листе порядка меню, оно будет автоматически обновлено на листе счета-фактуры. Ссылочная ячейка

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

Если вы ввели имя рабочего листа неправильно, #REF! В ячейке появится ошибка . В нашем примере ниже мы неправильно ввели имя рабочего листа. Нажмите кнопку Ошибка и выберите нужный вариант в раскрывающемся меню, чтобы изменить или игнорировать ошибку .

Исправление ошибки ссылки на ячейку

Вызов!

1. Откройте существующую книгу Excel. Если хотите, можете воспользоваться нашим учебным пособием.
2. Создайте формулу, которая использует относительную ссылку .Если вы используете этот пример, используйте дескриптор заполнения для заполнения формулы в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
3. Создайте формулу, которая использует абсолютную ссылку . Если вы используете этот пример, исправьте формулу в ячейке D4 , чтобы ссылаться только на налоговую ставку в ячейке E2 как на абсолютную ссылку , затем используйте дескриптор заполнения, чтобы заполнить формулу из ячеек D4 до D14 .
4. Попробуйте сослаться на ячейку на листах . Если вы используете этот пример, создайте ссылку на ячейку в ячейке B3 на листе Catering Invoice для ячейки E15 на листе Menu Order .

/ ru / excel2013 / functions / content /

Абсолютная ссылка в Excel фиксирует ячейку в формуле

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

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

Взаимодействие с другими людьми

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

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

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

Также стоит отметить регулярность изменения ссылок в формулах. Данные в B3 относятся к A3, от B4 до A4 и так далее. Все зависит от того, где будет ссылаться первая введенная формула, а ее копии будут изменять ссылки относительно ее положения в диапазоне ячеек на листе.

Теперь вместо чисел мы используем абсолютные ссылки:

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

Взаимодействие с другими людьми

Использование абсолютных и относительных ссылок в Excel

Заполните пластину, как показано на рисунке:

Описание исходной таблицы. В ячейке A2 отображается фактический курс евро по отношению к доллару на сегодняшний день. В диапазоне ячеек B2: B4 указаны суммы в долларах. В диапазоне C2: C4 будет сумма в евро после конвертации валют.Завтра курс изменится, и задача планшета автоматически пересчитает диапазон C2: C4 в зависимости от изменения значения в ячейке A2 (то есть курса евро).

Чтобы решить эту проблему, нам нужно ввести формулу в C2: = B2 / A2 и скопировать ее во все ячейки в диапазоне C2: C4. Но здесь есть проблема. Из предыдущего примера мы знаем, что при копировании относительные ссылки автоматически меняют адреса относительно их положения. Таким образом возникает ошибка:

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

Как сделать абсолютную ссылку в Excel? Очень просто поставить символ $ (доллар) перед номером строки или столбца или перед ними обоими. Ниже рассмотрим все 3 варианта и определим их отличия.

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

1. В C2 введите другую формулу: = B2 / A $ 2. Чтобы изменить ссылки в Excel, дважды щелкните ячейку левой кнопкой мыши или нажмите клавишу F2 на клавиатуре.
2. Скопируйте его в другие ячейки диапазона C3: C4.

Описание новой формулы. Символ доллара ($) в адресе ссылок фиксирует адрес в новых скопированных формулах.

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

1. $ A $ 2 — адрес абсолютной ссылки с фиксацией на столбцах и строках как по вертикали, так и по горизонтали.
2. $ A2 — смешанная ссылка. При копировании столбец фиксируется, а строка изменяется.
3. A $ 2 — это смешанная ссылка. При копировании строка фиксируется, а столбец изменяется.

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

Примечание. В этом примере формула может содержать не только смешанную ссылку, но и абсолютный результат: = B2 / $ A $ 2.Результат будет таким же. Но на практике часто бывают случаи, когда без смешанных референсов ничего не обойтись.

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

Абсолютных, относительных и смешанных ссылок на ячейки в Excel

Рабочий лист в 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 и скопируйте для всех ячеек:

  = D2 * $ G $ 1  

Обратите внимание, что есть два знака доллара ($) в ссылке на ячейку, в которой указана комиссия — $ 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 * E $ 2

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

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

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

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

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

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

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

Однако более быстрый способ сделать это — использовать сочетание клавиш — F4.

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

Предположим, у вас есть ссылка = A1 в ячейке.

Вот что происходит, когда вы выбираете ссылку и нажимаете клавишу F4.

• Нажмите клавишу F4 один раз: Ссылка на ячейку изменится с A1 на $ A $ 1 (вместо «относительной» станет «абсолютная»).
• Дважды нажмите клавишу F4: Ссылка на ячейку изменится с A1 на A $ 1 (изменится на смешанную ссылку, где строка заблокирована).
• Трижды нажмите клавишу F4: Ссылка на ячейку изменится с A1 на $ A1 (изменится на смешанную ссылку, где столбец заблокирован).
• Нажмите клавишу F4 четыре раза: Ссылка на ячейку снова станет A1.

Вам также могут понравиться следующие руководства по Excel:

Смешанный справочник Excel — руководство для начинающих

Что такое смешанная ссылка?

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

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

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

Смешанная ссылка бывает этих двух распространенных форм:

1. = $ A1 ($ перед столбцом, удерживая константу столбца)

2. = A $ 1 ($ перед строкой, сохраняя постоянную строку)

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

С ними немного труднее начать по сравнению с абсолютными ссылками и относительными ссылками; тем не менее, они помогают сэкономить время и уменьшить количество ошибок.

Как сделать смешанную ссылку в Excel

Загрузите бесплатный файл практики

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

Вот шаги по созданию базовой смешанной ссылки на ячейку в Excel.

1. Выберите ячейку, в которой вы хотите создать смешанную ссылку.Ячейка B2 в этом примере.

2. Предполагая, что идея состоит в суммировании чисел, в формуле B2 введите « = » (знак равенства), затем выберите точку отсчета — ячейки A2 и B1.

3. Удерживать столбец и строку постоянными:

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

Результаты ниже:

г.Чтобы сохранить константу строки , добавьте вместо нее знак доллара (= A $ 2 + B1 ) , затем скопируйте формулы по всей таблице.

Результаты ниже:

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

Примеры смешанной ссылки

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

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

Ниже приведен пример применения смешанной справки:

Секретарю компании было предложено рассчитать годовой налог на прибыль для нескольких руководителей высшего звена компании. Ей был дан их годовой доход с 2017 по 2019 год и соответствующая ставка налога.

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

• В случае Джонатана это будет = 2598 * 17%
• В выражении Excel это будет = B3 * E3.

Возвращаемое значение — 441,66 доллара.

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

Для этого добавьте к вычислению $ = B3 * $ E3 :

Затем скопируйте формулу в остальные оставшиеся ячейки.

Возвращаемые значения:

Если бы смешанная ссылка не применялась в приведенном выше сценарии, а использовалась только относительная ссылка, расчеты были бы некорректными.

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

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

Сводка

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

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

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

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

Учите Excel бесплатно

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

Начать бесплатный курс

Агнес Ло

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

vba — изменить ссылку на ячейку между абсолютным и относительным

Я вижу, вы отредактировали вопрос, но, поскольку я уже работал над этим, я отправляю ответ.

Если вы не знаете, что содержит формула, и хотите изменить Относительный на Абсолютный и Абсолютный / Смешанный на Относительный , тогда попробуйте это

Допустим, у меня есть 4 диапазона в моем Selection , как показано ниже

Итак, я могу использовать RegEx , как предлагается здесь, чтобы извлечь отдельные адреса и найти, что это за формула, а затем внести изменения, как предлагает @cyboashu

  Const sPattern As String = _
"(['].*? ['!])? ([[A-Z0-9 _] + [!])? (\ $? [AZ] + \ $? (\ D) + (: \ $? [AZ] + \ $ ? (\ d) +)? | \ $? [AZ] +: \ $? [AZ] + | (\ $? [AZ] + \ $? (\ d) +)) "

Подвыборка ()
    Dim sMatches As Object, objRex As Object
    Dim rng As Range, aCell As Range
    Dim sFormula как строка
    Dim bAbsMix As Boolean, bRel As Boolean

    Установить rng = Выбор

    Установите objRex = CreateObject ("VBScript.RegExp")

    С помощью objRex
        .IgnoreCase = Истина
        .Global = Истина
    Конец с

    Для каждой ячейки In rng
        objRex.Pattern = "" ". *?" ""
        sFormula = aCell.Формула
        sFormula = objRex.Replace (sFormula, "")

        objRex.Pattern = "(([A-Z]) + (\ d) +)"
        objRex.Pattern = sPattern

        Если objRex.test (sFormula) Тогда
            Установите sMatches = objRex.Execute (sFormula)
            Если sMatches.Count> 0 Тогда
                Для каждого матча в sMatches
                    Если Len (Match) = Len (Replace (Match, "$", "")), то
                        bRel = Истина
                    Еще
                        bAbsMix = True
                    Конец, если
                Следующий матч
            Конец, если
        Конец, если

        Если bAbsMix = True Then '<~~ Это абсолютное / смешанное
            Отлаживать.Распечатать формулу & "in" & aCell.Address & "is Absolute / Mixed"
            aCell.Formula = Application.ConvertFormula (aCell.Formula, xlA1, xlA1, 4)
        Else '<~ Это относительное
            Debug.Print sFormula & "in" & aCell.Address & "is Relative"
            aCell.Formula = Application.ConvertFormula (aCell.Formula, xlA1, xlA1, 1)
        Конец, если

        bRel = False: bAbsMix = False
    Далее aCell
Конец подписки
  

В непосредственном окне

.