Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Абсолютные и относительные адреса ячеек

Абсолютные и относительные адреса ячеек

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

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

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

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

Пример 14.9. $B$5; $D$12 — полные абсолютные ссылки.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.

Пример В$5, D$12 — частичная абсолютная ссылка, не меняется номер строки; $B5, $D12 — частичная абсолютная ссылка, не меняется наименование столбца.

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

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

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

Рассмотрим правило относительной ориентации ячейки на примере.

Пример 2.6 В ячейке В4 находилась формула = В2+В3. При копировании ее в ячейку С4 формула приобретает вид =С2+С3

При копировании в ячейку В7 формула приобретает вид =В5+В6.

Общее правило: если формула копируется на N строк вниз, то Excel добавляет ко всем используемым номерам строк число N. Если формула копируется на M столбцов правее, то все используемые в ней буквенные обозначения столбцов смещаются на М позиций вправо.

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

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

Все сказанное выше относится и к адресам диапазонов. Однако следует помнить, что диапазон задается адресами угловых ячеек. При простановке знаков $ при копировании необходимо знак $ ставить при координатах обоих угловых ячеек.

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

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

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

Относительная и абсолютная адресация ячеек

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

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

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

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

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

Различают два типа абсолютных ссылок: полная и частичная.

§ Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащий исходное данное, не меняется. Для этого символ $ ставится перед наименованием и столбца, и строки.

Пример: $B$5; $D$12; $D$5 — полные абсолютные ссылки.

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

Пример: В$5, D$12, F$5 — частичная абсолютная ссылка, не меняется номер строки;

$B5, $D12, $H5 — частичная абсолютная ссылка, не меняется наименование столбца.

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

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

§ Ссылка A1 указывает на ячейку, которая смещена относительно С2 на одну ячейку вверх и на две влево;

Читать еще:  Какой браузер лучше установить

§ Ссылка В3 указывает на ячейку, которая смещена относительно С2 на одну ячейку вниз и одну влево.

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

§ Двумерные ссылки — Относительные (обозначение А1+В3), Абсолютные ($A$1+$B$3), Смешанные ($A1+B$1);

§ Трехмерные ссылки, связанные с местоположением ячейки на листе, (например, Лист2!А3 — ячейка, Лист2:Лист6!А3:А5 – диапазон ячеек).

Следует отметить, что:

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

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

§ Смешанные ссылки фиксируют столбец или строку при копировании.

Относительная и абсолютная адресация ячеек;

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

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

Адрес ячейки называют также ссылкой; в этом случае используют термины Относительная ссылка и Абсолютная ссылка.

Адрес можно сделать абсолютным двумя способами:

1. Поместить символ доллара ($)в строке формул перед именем столбца и номером ячейки, например, $A$5, введя его непосредственно с клавиатуры, или установить курсор в строке формул на адресе ячейки и нажать клавишу F4.

2. Присвоить имя ячейке с помощью кнопки Присвоить имя из группы Определенные имена на вкладке Формулы.

Упражнение. Необходимо на листе «Итог» осуществить пересчет сумм НДС, которые были рассчитаны в условных единицах, в рублевый эквивалент в соответствии с установленным валютным курсом, для чего:

· Перейти на лист «Итог».

· В ячейку Е2 ввести наименование новой графы – «НДС в рублях».

· В ячейку А14 ввести – «Курс доллара», в ячейку В14 – 30,5.

· Установить курсор в ячейку Е3, ввести знак «=», затем щелкнуть левой кнопкой мыши в ячейке D3, ввести знак «*», щелкнуть левой кнопкой мыши в ячейке В14 и нажать функциональную клавишу F4. Нажать Enter. В строке формул будет отражена формула =D3*$B$14.

· Скопировать полученную формулу в ячейки Е4:Е11.

· Выделить блок ячеек Е3:Е11и нажать правую кнопку мыши для вызова контекстного меню.

· Выбрать пункт меню Формат ячеек.

· На вкладке Число в окне Числовые форматы выбрать Денежный, в окне Число десятичных знаков – 2, в окне Обозначение – «р.». Нажать ОК.

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

· Расширить столбец Е до появления значений ячеек.

· Оформить внешний вид полученной таблицы в соответствии с рисунком 12, используя кнопки Границы и Цвет заливки из группы Шрифт вкладки Главная.

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

Абсолютные и относительные ссылки в 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:

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

Абсолютные и относительные адреса ячеек

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

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

В случае относительной адресации адреса ячеек, используемые в формулах, определены относительно места расположения формулы . При копировании формулы в новое положение таблицы адреса используемых в формуле ячеек меняются соответственно новому месту положения формулы. Например, в таблице на рисунке справа формулу в ячейке `»C»1` табличный процессор воспринимает так: умножить значение ячейки, расположенной на две ячейки левее на значение ячейки, расположенной на одну ячейку левее данной формулы. Тогда при копировании формулы в ячейку `»C»2` табличный процессор умножит значение ячейки `»A»2` на значение ячейки `»B»2`. Преимущество относительной адресации состоит в том, что при копировании ячейки в новое положение ссылки в копируемой формуле меняются автоматически. Однако на практике бывает так, что адрес ячейки, используемой в формуле, не должен меняться при копировании. В этом случае используется абсолютная адресация . В абсолютных адресах перед неизменяемым значением адреса ячейки ставится знак `$`, например `$»B»$2` – это абсолютный адрес ячейки `»B»2`. Поясним преимущества относительного адреса и использование абсолютного адреса на следующем примере.

Турфирма «Кругосвет» предоставляет путевки в Грецию, на Мальту и в Италию по ценам, указанным в долларах США. Составьте формулу для расчёта цен путёвок в европейской валюте ЕВРО согласно курсу, указанному в таблице.

Составим формулу расчёта цены путёвки в Грецию в ячейке `»C»4` так, чтобы, скопировав её в ячейки `»C»5` и `»C»6`, можно было автоматически вычислить значения цены на путёвки на Мальту и в Италию. Положение ячейки `»B»1` относительно ячеек `»C»4`, `»C»5` и `»C»6` различное, значит необходимо, чтобы в формуле `»C»4` адрес ячейки `»B»1` был абсолютным. Положение ячеек `»B»4`, `»B»5` и `»B»6` относительно `»C»4`, `»C»5`, `»C»6` одинаковое, а значит, адрес ячейки `»B»4` в формуле должен быть относительным. Тогда выражение `=»B»4^(**)$»B»$1` является формулой расчёта цены путёвки в Грецию. Использование относительного адреса `»B»4` и абсолютного адреса `$»B»$1` позволяет скопировать формулу в ячейки `»C»5` и `»C»6` и автоматически вычислить цены путёвок в остальные страны.

В формулах возможно использование смешанной адресации, при которой один из компонентов адреса абсолютный, а другой — относительный. Например, в адресе `»B»$2` компонент по столбцу относительный, а компонент по строке абсолютный.

В ячейке `»B»1` электронной таблицы находится формула `=»E»1+`$`»E»2`. Какой вид приобретёт формула после того, как содержимое ячейки `»B»1` скопируют в ячейку `»C»1`?

Так как относительное положение ячейки по строкам не изменилось, и в формуле используется относительная адресация по строкам, то строковая компонента нового адреса остаётся прежней и имеет вид `=»X»1+»Y»2`, где адрес по столбцам `»X»` и `»Y»` необходимо определить. Адрес столбца ячейки `»E»1` относительный, и так как ячейка `»B»1` копируется в `»C»1` со смещением в один столбец вправо, то новый адрес столбца ячейки `»E»1` будет `»F»`. Адрес столбца ячейки `$»E»2` абсолютный, значит, он остаётся неизменным и равным `»E»`. Итак, получаем, что формула в новой ячейке принимает вид `=»F»1+`$`»E»2`.

В ячейке `»C»2` записана формула `=$»B»$3+»D»2`. Какой вид она приобретёт после того, как содержимое ячейки `»C»2` скопируют в ячейку `»B»1`?

Адрес первой ячейки `$»B»$3` абсолютный, значит, он не изменится. Адрес ячейки `»D»2` относительный по строке и по столбцу. Ячейка `»D»2` располагается в той же строке, что и ячейка `»C»2`, но в другом столбце `»D»` со смещением вправо на один столбец. Значит, адрес столбца ячейки `»D»2` в ячейке `»B»1` станет `»C»`, а по строке станет `1`, и полный вид формулы будет выглядеть `=$»B»$3+»C»1`.

Ссылка на основную публикацию
Adblock
detector