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

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

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

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

Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
Жмем Enter, чтоб получить итог. Опосля того, как мы скопируем формулу на оставшиеся ячейки столбца, заместо результатов увидим последующую ошибку: #ДЕЛ/0! .

Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в следующих ячейках двинулись. Т.е. для ячейки E3 формула смотрится последующим образом: =D3/D14 . Но, как мы лицезреем, ячейка D14 – пустая, из-за чего программка и выдает ошибку, информирующую о том, что разделять на цифру недозволено.

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

Как в Excel указать ссылки на одну и ту же ячейку, но с разных листов (Функция ДВССЫЛ)

Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.

Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.

На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.

Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.

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

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

ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.

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

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так: =ДВССЫЛ(«D4») То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

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

=Лист1!B2 (Выручка за январь магазина 1)

=Лист2! В2 (Выручка за январь магазина 2) и т.д.

Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.

Если мы пропишем формулу ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2») и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее

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

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3» =ДВССЫЛ(A2&»!B3″) и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.

=ДВССЫЛ( A3 & «!B» & C1 ), где A3 — это текст «Лист1» , «!B» — это неизменный текст и С1 — это цифра 2 . Все это объединено с помощью знака & в общий текст «Лист1 !B 2 «. Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&»!B»&C$1) которую можно протянуть вправо и вниз.

Заметки по теме:

Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

SirExcel — безграничные возможности Excel

Как изменить относительные формулы на абсолютные формулы в Excel 2013 — манекены 2019

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

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

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

Например, на рабочем листе «Матушка гусиных предприятий — 2013» вы сталкиваетесь с этой ситуацией при создании и копировании формулы, которая вычисляет, какой процент каждого ежемесячного итога (в диапазоне ячеек B14: D14) равен ежеквартальный итог в ячейке E12.

Предположим, что вы хотите ввести эти формулы в строке 14 листа рабочих матов Goose Enterprises — 2013 Sales, начиная с ячейки B14. Формула в ячейке B14 для расчета процента от января-продажи к первому кварталу является очень простой:

= B12 / E12

Эта формула делит январскую сумму продаж в ячейке B12 на квартальную сумму в E12 (что может быть проще?). Посмотрите, однако, на то, что произойдет, если вы перетащили дескриптор заполнения в одну ячейку вправо, чтобы скопировать эту формулу в ячейку C14:

= C12 / F12

Настройка первой ссылки на ячейку от B12 до C12 — это просто что доктор заказал. Однако корректировка ссылки на вторую ячейку от E12 до F12 является катастрофой. Вы не только не подсчитаете, какой процент февральских продаж в ячейке C12 приходится на продажи в первом квартале в E12, но вы также получаете один из этих ужасных # DIV / 0! ошибки в ячейке C14.

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

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

Например, ячейка B14 содержит правильную формулу для копирования в диапазон ячеек C14: D14:

= B12 / $ E $ 12

Посмотрите на рабочий лист после того, как эта формула будет скопирована в диапазон C14: D14 с дескриптором заполнения и выбирается ячейка C14

Обратите внимание, что панель формул показывает, что эта ячейка содержит следующую формулу:. = C12 / $ E $ 12

= C12 / $ E $ 12

Поскольку E12 был изменен на $ E $ 12 в исходной формуле, все копии имеют такой же абсолютный (не изменяющийся ) Справка.

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

Дважды щелкните ячейку с помощью формулы или нажмите F2, чтобы отредактировать его.
Расположите точку вставки где-нибудь на ссылке, которую вы хотите преобразовать в абсолютную.
Нажмите F4.
Когда вы закончите редактирование, нажмите кнопку «Ввод» на панели формул, а затем скопируйте формулу в диапазон перепутанных ячеек с помощью дескриптора заполнения.

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

Если вы снова нажмете F4, в Excel появится другой тип смешанной ссылки, где часть столбца будет абсолютной, а часть строки будет относительной (как в $ E12).

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

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

Ознакомились ли это с формулами Excel, чтобы вы жаждали дополнительной информации и понимания популярной электронной таблицы Microsoft? Вы можете протестировать диск любого из курсов Для чайников .

Выберите свой курс (вас может заинтересует больше от Excel 2013 ), заполните краткую регистрацию, а затем дайте eLearning спину с помощью Try It! кнопка.

Вы будете правы на курсе для более надежных ноу-хау: полная версия также доступна в Excel 2013 .

Трюк №64. Как сделать, чтобы формула ссылалась на следующие строки при копировании по столбцам

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

Если создать ссылку на одну ячейку, например, А1, а затем скопировать ее вправо по столбцам, то в результате эта ссылка будет меняться: =В1, =С1, =D1 и т. д.

, но на самом деле вы хотите получить другой результат. Нужно, чтобы приращение формулы шло по строкам, а не по столбцам, то есть =А1, =А2, =А3 и т. д. К сожалению, в Excel такая возможность не предусмотрена. Но можно обойти это ограничение с помощью функции ДВССЫЛ (INDIRECT), в которую вложена функция АДРЕС (ADDRESS).

Наверное, лучший способ объяснить, как создать нужную функцию, — использовать пример с предсказуемыми результатами. В ячейках А1:А10 по порядку введите числа от 1 до 10.

Выделите ячейку D1 и введите в ней следующую формулу: =INDIRECT(ADDRESS(COLUMN()-3;1), в русской версии Excel =ДВССЫЛ(АДРЕС(СТОЛБЕЦ()-3;1)). Когда вы сделаете это, в ячейке D1 должно появиться число 1. Это происходит потому, что формула ссылается на ячейку А1.

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

Чтобы продемонстрировать, о чем идет речь, будем использовать необходимую всем функцию СУММ (SUM). Предположим, вы получили длинный список чисел, и необходимо просуммировать столбцы чисел в режиме промежуточной суммы: =SUM($A$1:$A$2). =SUM($A$1:$A$3). =SUM($A$1:$A$4), в русской версии Excel =СУММС$А$1:$А$2). =СУММ($А$1:$А$3). =СУММ($А$1:$А$4).

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

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

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

Заполните диапазон А1:А100 числами от 1 до 100 по порядку. В ячейку А1 введите 1, выделите ячейку А1 и, удерживая клавишу Ctrl, щелкните маркер заполнения и протащите «го вниз на 100 строк.

Выделите ячейку D1 и введите следующую формулу: =SUM(INDIRECT(ADDRESS(1;1)&»:»&ADDRESS(COLUMN()-2;1))), в русской версии Excel =СУММ(ДВССЫЛ(АДРЕС(1;1)&»:»&АДРЕС(СТОЛБЕЦ()-2;1))). В результате вы получите 3 как сумму ячеек А1:А2.

Переменная функция СТОЛБЕЦ (COLUMN) заставляет последнюю ссылку на ячейку увеличиваться на 1 каждый раз, когда вы копируете формулу в новый столбец.

Функция СТОЛБЕЦ (COLUMN) всегда возвращает номер столбца (а не букву) в ту ячейку, в которой находится, если только вы не ссылаетесь на другую ячейку. Иначе, можно было бы воспользоваться средством Excel Специальная вставка → Транспонировать (Paste Special → Transpose).

Введите формулу =SUM($A$1:$A2) в ячейку В1 (обратите внимание на относительную ссылку на строку и абсолютную ссылку на столбец в ссылке $А2) и скопируйте эту формулу вниз до ячейки В100. Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose)

Если необходимо, можно удалить формулы в диапазоне В2:В100

Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose). Если необходимо, можно удалить формулы в диапазоне В2:В100.

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

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

Предположим, у нас есть таблица с несколькими позициями товаров. Здесь указано количество некого товара и цена за одну единицу. Давайте для примера посчитаем, сколько стоит весь товар, находящийся в ассортименте условного магазина. Для этого нужно умножить количество товара на цену за единицу. Формула в Excel будет выглядеть так: «=ячейка_с_количеством*ячейка_с_ценой». Например, у нас это будут ячейки D2 и E2 соответственно: «=B2*C2». Введя и посчитав данную формулу мы получили формулу с абсолютной ссылкой.

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

Как создать абсолютную ссылку

Выше был рассмотрен очень распространённый пример относительных и абсолютных ссылок. Однако бывает так, что пользователю нужно сделать так, чтобы какая-то из-за ссылок в формуле всегда оставалась абсолютной. Даже при автоматической вставке. Для этого нужно поставить значок $ перед символом и номером ячейки. Пример такой формулы: «=A1/$B$1». В таком случае содержимое ячеек в столбце A всегда будет делиться на содержимое ячейки B1. Даже при автоматическом заполнении.

Про ошибки в относительных ссылках

Иногда при использовании относительных ссылок вместо подсчёта возникает ошибка, когда написано «#ДЕЛ/0!». Это случается, когда в одной из указанных в формуле ячейках нет никакого числа. Например, пропишем такую формулу: «=A1/B1». Столбец A заполним какими-то числами до 10-го номера, а в столбце B заполним только одну ячейку – первую.

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

Заполнить столбец B числами до 10-й ячейки;
Установить абсолютную ссылку для ячейки B1.

Смешанные ссылки

Абсолютной ссылке можно придать смешанный вид, просто правильно расставив символ $. Например:

$A1. Ссылка является абсолютной столбца A, но при этом может быть относительна номеров ячеек в этом столбце;
A$1. Ссылка является абсолютной номера ячейки, но может быть относительно столбца этого номера.

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

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

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

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

Кликаем на D2:
Для подсчета «Итого» необходимо умножить данные из столбцов B и C, т.е вписываем формулу =B2*C2.
Нажимаем Ввод (Enter), после чего формула будет вычислена и результат запишется в D2.
Значение из D2 можно растянуть на все строки. Делается это с помощью функции автозаполнения, которая представляет собой квадрат, расположенный справа внизу в выделенной ячейке. «Протягиваем» маркер с помощью мыши только до пятой строки. После нее данных нет, и в последующих ячейках будут нули.

Часто при создании такого рода адресаций появляются ошибки, поскольку пользователи забывают об относительности адреса ячейки. Используя в формуле какую-либо константу, ее просто добавляют без фиксирования символом $. Это приводит к таким ошибкам как «#ПУСТО!», «#ДЕЛ/0» и другим. Иногда бывает так, что нужно растянуть формулу с зафиксированной строкой или столбцом. В таких случаях смешанная адресация может сэкономить время.

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

В этом случае, проще всего будет показать на примере.

Допустим, у нас есть такая табличка:

Например, нам нужно узнать сумму всех товаров. Для этого цена*кол-во.

Для товара А формула примет следующий вид:

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

Ссылки, которые Excel автоматически подстраивает под новые ячейки — относительные.

В каких ситуациях они лучше всего?

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

Электронное приложение к уроку

Презентация «Организация вычислений в электронных таблицах»

Презентация «Организация вычислений в электронных таблицах» (Open Document Format)

Ссылки на ресурсы ЕК ЦОР:

демонстрация «Операции манипулирования с диапазонами ЭТ» (119389) http://school-collection.edu.ru/catalog/search/?text=119389&submit=%CD%E0%E9%F2%E8&interface=catalog
интерактивный задачник, раздел «Электронные таблицы. Запись формул» (119384) http://school-collection.edu.ru/catalog/search/?text=119384&submit=%CD%E0%E9%F2%E8&interface=catalog
интерактивное задание «Тренировочный тест N4» (119442) http://school-collection.edu.ru/catalog/search/?text=119442&submit=%CD%E0%E9%F2%E8&interface=catalog
интерактивное задание «Статистические функции в электронных таблицах» (119341) http://school-collection.edu.ru/catalog/search/?text=119341&submit=%CD%E0%E9%F2%E8&interface=catalog
демонстрация к лекции «Элементарные логические операции» (128620) http://school-collection.edu.ru/catalog/search/?text=128620&submit=%CD%E0%E9%F2%E8&interface=catalog
демонстрация к лекции «Вычисление логических выражений» (128658) http://school-collection.edu.ru/catalog/search/?text=128658&submit=%CD%E0%E9%F2%E8&interface=catalog
демонстрация к лекции «Условная функция» (119322) http://school-collection.edu.ru/catalog/search/?text=119322&submit=%CD%E0%E9%F2%E8&interface=catalog
интерактивный задачник, раздел «Логические формулы в электронных таблицах» (119424) http://school-collection.edu.ru/catalog/search/?text=119424&submit=%CD%E0%E9%F2%E8&interface=catalog

Cкачать материалы урока

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

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

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

Для начала пишем формулу в обычном виде в требуемой ячейке. В нашем случае она смотрится так: = D2/D13 .
Когда формула готова, не спешим жать кнопку Enter. Сейчас нам необходимо зафиксировать координаты ячейки D13. Для этого перед заглавием столбца и порядковым номером строчки печатаем знак “$”. Либо же можно просто опосля ввода адреса сходу надавить кнопку F4 на клавиатуре (курсор может находиться до, опосля либо снутри координат). В итоге формула обязана смотреться последующим образом: D2/$D$13 .
Сейчас можно надавить Enter, чтоб вывести итог в ячейку.
Остается лишь скопировать формулу при помощи маркера наполнения на нижние строчки. Сейчас, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, итог покажется и в остальных ячейках.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Более подробно об относительных ссылках в Excel Вы можете прочитать в данном уроке.

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

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

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

Вот что нам нужно сделать:

Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: =B2*C2.
Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.
Остается выполнить аналогичные расчеты в других ячейках столбца. Конечно же, если таблица небольшая, можно перейти в следующую ячейку и выполнить шаги 1-2, описанные выше. Но что делать, когда данных слишком много? Ведь на ручной ввод формул во все ячейки уйдет немало времени. На этот случай в Excel предусмотрена крайне полезная функция, позволяющая скопировать формулу в другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом, и когда появится небольшой черный крестик (маркер заполнения), зажав левую кнопку мыши тянем его вниз, тем самым копируя формулу в другие ячейки.
Отпустив кнопку мыши мы получим результаты во всех ячейках столбца, на которые растянули формулу.
Если мы перейдем, например, в ячейку D3, то увидим в строке формул следующее выражение: =B3*C3.Т.е. при копировании изменились координаты ячеек, участвующих в исходной формуле, которую мы записали в ячейку D2. Это результат того, что ссылки были относительными.

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

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

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

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная. Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащий исходное данное, не меняется. Для этого символ $ ставится перед наименованием столбца и номером строки. Пример 14.9. $B$5; $D$12 — полные абсолютные ссылки.

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

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

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

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

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

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

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

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

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

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

Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.

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

Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.