Программа Microsoft Excel: абсолютные и относительные ссылки

Какие бывают ссылки в Excel

Здравствуйте, дорогие читатели. Я решил выделить эту тему в отдельный пост, потому что многие не знают какие бывают ссылки в Эксель и не представляют, чем они отличаются друг от друга. Ежедневно я получаю вопросы от читателей, которые неправильно применяют ссылки на ячейки. Из-за этого, использование формул может давать ошибочные результаты. Уделите этому тексту 5 минут, чтобы потом не было неожиданных результатов вычислений.

Ссылки в Эксель

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

  1. Абсолютные ссылки. Они однозначно указывают адрес ячейки, на которую ссылаются. При копировании, этот адрес не изменяется. Так обычно выглядит абсолютная ссылка в Excel: =$A$1 . В этой ссылке перед именами столбцов и строк стоит знак «$», который указывает программе не изменять эти координаты при копировании.
  2. Относительные ссылки. Такие ссылки запоминают свое положение относительно источника и при копировании так же, изменяют свои координаты. Например, в ячейке А1 записано =В2 . Значит, мы ссылаемся на ячейку, которая на одну строку ниже нашей и на один столбец правее. Теперь, если скопировать эту формулу в клетку С3 , ссылка в ней будет такая: =D4 , т.е. сохранит своё относительное положение к источнику.

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

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

  • Смешанные ссылки. В таком виде ссылки, лишь одна из двух координат изменяется. Например, если в ячейке А1 записано =А$2 , то при копировании ссылка на столбец А будет относительной, а на строку 2 – абсолютной. Теперь скопируем эту формулу в ячейку С3 и увидим формулу: =C$2 . То есть, координата столбца изменила своё значение, а строки – нет.
  • Я в практике одинаково часто использую все три типа ссылок, поскольку они позволяют решать разные задачи при копировании формул. При этом, все они используются постоянно.

    Как изменить тип ссылки в Эксель

    Изменение типа ссылки – очень простая задача, которую можно выполнить двумя путями. Как вы уже поняли, чтобы координаты не меняли своего значения при копировании, нужно устанавливать перед ними знак «$». И делается это так:

    1. Вручную – дважды кликните на ячейке со ссылкой для редактирования содержимого. Проставьте «$» перед теми координатами, которые нужно «заморозить» и нажмите Enter .
    2. Автоматическим перебором — установите курсор на ссылке и нажимайте F4 , пока не получите нужный вид ссылки. Каждое нажатие клавиши устанавливает в данной ссылке новый тип ссылки. Нажатие клавиши циклически изменяет варианты ссылок по кругу: Относительная — Абсолютная — Изменяются столбцы — Изменяются строки — Относительная… Я пользуюсь этим способом, и он ни разу не подводил.

    Внешние ссылки в Эксель

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

    1. Ссылка на ячейку на том же листе по умолчанию ставится относительной и выглядит, как набор координат. Например: =А1
    2. Ссылка на ячейку на другом листе активной книги, по умолчанию тоже относительная, но содержит имя листа, на котором расположена искомая ячейка. Имя листа и адрес ячейки разделяются восклицательным знаком. Например, =Лист1!А1 .
    3. Ссылка на ячейку в другом файле по умолчанию абсолютная и записывается, как комбинация: [Имя_рабочей_книги]Имя_листа!Адрес_ячейки . Например: =[Книга1.xlsx]Лист1!$А$1 . И здесь нужно сделать несколько уточнений:
      1. Если целевая рабочая книга закрыта, ссылка изменяет вид: Адрес_рабочей_книги[Имя_рабочей_книги]Имя_листа!Адрес_ячейки .
      2. Если имя листа или книги содержит пробелы, ссылка заключается в одинарные кавычки по такой схеме: ’[Книга 1.xlsx]Лист 1’!$А$1 .

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

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

    Спасибо за прочтение, до встречи на страницах блога OfficeЛЕГКО!

    Добавить комментарий Отменить ответ

    2 комментариев

    Пожалуй, статья не является полной (по крайней мере, на свой вопрос я в ней ответ не нашёл).
    При пользовании EXCEL встретился термин «ПРОСТАЯ ССЫЛКА»! Не пойму, что он означает.
    В частности, указание на необходимость /возможность использования «простой ссылки» даётся при неправильном (по меркам EXCEL, естественно) написании ОГРАНИЧЕНИЙ, используемых при применении опции «ПОИСК РЕШЕНИЯ» (буквально появляется следующее сообщение: «Ограничение должно быть числом, ПРОСТОЙ ССЫЛКОЙ или формулой с числовыми значениями»).
    Исходя из специфики решаемой задачи в данном случае интерес может представлять только ПРОСТАЯ ССЫЛКА.
    Буду признателен автору, если он пояснит значение этого термина (разумеется, если знает это).

    Заранее спасибо. С уважением, Виктор (Московская область)

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

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

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

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

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

    Простейшая формула в MS Excel состоит из относительных ссылок

    Разумеется это очень удобно в том случае, если мы имеем таблицу, где каждая строка в определенном столбце имеет один и тот же принцип вычисления результата. Например, в таблице из 20 строк нам нужно вычислить сумму ячеек А1 и В1, а результат поместить в ячейку С1. Нет ничего проще — мы помещаем в ячейку С1 формулу =А1+В1 и насаждаемся результатом.

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

    А как вычислить результат аналогичного сложения для следующей строки? Поместить в С2 формулу А2+В2 — вариант отличный, но не слишком ли это жирно — пройти по всем строкам таблицы и в каждой записать нужную формулу? К счастью, делать этого не нужно — за счет того, что ссылки в Excel по умолчанию относительны, нам достаточно просто скопировать исходную формулу (=А1+В1) на следующую строку, и программа сама подставит на нужные места изменившиеся за счет смещения относительные данные ячеек (формула изменит вид на =А2+В2).

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

    Читайте также  Подсчет суммы в строке таблицы в Microsoft Excel

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

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

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

    Такая формула обычно имеет вид: =(А1+В1)*Коэффициент

    К примеру, коэффициент имеет вид 2,6, тогда формула будет выглядеть как =(А1+В1)*2,6 На первый взгляд этот самый коэффициент можно просто вписать в формулу вручную (как я указал выше). Но что если завтра коэффициент изменится? Гораздо проще поместить его в ячейку таблицы (например D1) и привести формулу к виду =(А1+В1)*D1. Однако как мы помним, по умолчанию все ссылки в MS Excel относительны, а это значит что при копировании этой формулы на строку ниже, она приобретет вид =(А2+В2)*D2.

    А вот и минус относительных ссылок — смещается вся формула и даже те фрагменты, которые по идее смещать не надо

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

    Можно решить проблему с относительными ссылками введя дополнительную колонку с одинаковыми значениями

    Добиться этого просто: к каждому знаку «постоянного» элемента нужно всего лишь добавить специальный символ «$». Иными словами, формула =(А1+В1)*$D$1 будет работать совсем не так как раньше: если «относительные» ячейки А1 и В1 будут изменяться при копировании формулы в другое место по обычным правилам относительных ссылок, то $D$1 всегда будет ссылаться на одну и ту же ячейку, как бы мы не перемещали исходную формулу.

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

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

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

    Мы с вами знаем, что в умелых руках Excel — очень мощный инструмент. При произведении расчетов с помощью этого табличного редактора мы можем оперировать как содержимым ячеек так и их адресами (ссылками). И вот эти самые ссылки бывают двух типов — абсолютные и относительные. В чем же отличие этих двух способов адресации? Давайте рассмотрим на примере — очередном уроке Excel.

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

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

    В итоге получим в ячейке E2 число 410 — это и есть стоимость 10 единиц молока.

    Но, предположим, что цена молока повысилась до 43 рублей или количество изменилось с 10 до 9. Тогда нам придется вносить исправления не только в ячейках C2 и D2, но и в формуле в ячейке E2. Иначе в E2 так и останется 410, что неверно. Это не очень удобно.

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

    И теперь эта ячейка станет зависимой от ячеек C2 и D2. Любое изменение содержимого ячеек C2 и D2 тут же скажется на ячейке E2, ее содержимое будет пересчитано. И нам не нужно будет заниматься этим вручную.

    Осталось только скопировать содержимое ячейки E2 в нижерасположенные ячейки и таблица будет готова. Обращаю внимание — в ячейках E2, E3 и E4 будут храниться формулы.

    Ячейка Содержимое
    E2 =C2*D2
    E3 =C3*D3
    E4 =C4*D4

    А таблица примет вид:

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

    При копировании мы не меняли вручную адреса ячеек в формулах — они менялись автоматически. Это и есть относительная адресация. При копировании формулы =C2*D2 из ячейки E2 в ячейку E3, в ячейку E3 будет помещена формула =C3*D3, т. е. адреса ячеек в формуле будут преобразованы относительно ячейки E3. Именно относительный способ адресации используется в Excel по умолчанию. Это удобно, но иногда является преградой.

    Давайте добавим в нашу таблицу еще один столбец, в котором общая стоимость будет выражена в долларах. Курс доллара мы поместим в ячейку G2. Таблица будет выглядеть так:

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

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

    В итоге получим следующее:

    Абсолютная адресация в Excel

    На дробную часть можно не обращать внимание — наша цель не в этом.

    Все замечательно получилось. Далее попробуем скопировать содержимое ячейки F2 в F3 чтобы получить общую стоимость хлеба в долларах. И получим ошибку #ДЕЛ/0! которая говорит, что мы пытаемся делить на ноль:

    А если мы посмотрим содержимое ячейки F3 то увидим формулу =E3/G3. А так как в ячейке E3 у нас 391, а в G3 — пусто, то Excel и выдал нам такую ошибку. Ведь он пытался использовать относительную адресацию. Давайте исправлять ситуацию.

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

    Итак, поменяем содержимое ячейки F2 на

    Здесь мы адресуем ячейку E2 относительно, а G2 — абсолютно. В итоге при копировании в ячейку F3 ячейка E2 изменится на E3, а вот G2 так и останется — $G$2. Это нам и нужно. Скопируем содержимое ячейки F2 в F3 и F4 и увидим, что все работает правильно. Этого мы и добивались.

    Итоговая таблица Excel

    Обратите внимание, что так как мы заполняем ячейки ниже F2, то у ячейки G2 меняется только номер строки, а столбец остается прежним. Поэтому мы можем поставить символ $ только перед номером строки — замораживать столбец не обязательно, он и так не меняется при копировании. В итоге в ячейках F2, F3, F4 должны быть записаны формулы:

    Ячейка Содержимое
    F2 =E2/G$2
    F3 =E3/G$2
    F4 =E4/G$2

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

    Автор: Александр Чернышов

    Оцените статью, это очень поможет развитию сайта.

    Excel: Ссылки относительные и абсолютные

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

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

    Ссылка в Excel — адрес ячейки или связного диапазона ячеек.

    Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.

    Читайте также  Составление сметы в Microsoft Excel

    Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

    Ссылки в Excel бывают 3-х типов:

    • Относительные ссылки (пример: A1);
    • Абсолютные ссылки (пример: $A$1);
    • Смешанные ссылки (пример: $A1 или A$1).

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

    «Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

    Пример.

    В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.

    При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7 формула изменяется (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).

    Теперь формула в ячейке А7 ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).

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

    1. Если формулу не предполагается копировать в другие ячейки.
    2. Если формулу необходимо скопировать в идентичные ячейки.

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

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

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

    1. Необходимости применения в формулах констант.
    2. Необходимости фиксации диапазона для проведения расчетов.

    Пример.

    В диапазоне А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию каждого сотрудника и поместим в диапазоне В1:В5.

    Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.

    Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Так как в ячейках диапазона С2:С5 нет значений, то в диапазоне В2 : В5 получаем нули.
    Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.

    • выделите ячейку В1
    • в Строке формул поставьте знак «$» перед буквой столбца и адресом строки $С$1. Более быстрый способ — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед или после 1) и нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
    • нажмите ENTER

    Формула приняла вид « =А1*$С$1».
    Маркером заполнения протяните полученную формулу вниз.

    Теперь диапазон В2: В5 заполнен значениями премий сотрудников.

    Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знаки «$».

    Понравилось? Поделись с друзьями

    Семинары. Вебинары. Конференции

    Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

    Ссылки в Excel

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

    Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

    Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

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

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

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

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

    где буква обозначает столбец, а число – строку.

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

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

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

    Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

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

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

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

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

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

    Как быстро установить символ доллара $

    Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4 . Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

    Читайте также  Программа Microsoft Excel: закрепление строки на листе

    » src=»https://statanaliz.info/wp-content/uploads/2015/01/ssylki-06_F4.gif» alt=»Последовательное нажатия » w />

    Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
    Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

    Ссылки на другие листы и книги

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

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

    Стиль ссылок

    И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

    Абсолютная и относительная ссылка в Excel. Как использовать?

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

    Определение

    Ссылки в Microsoft Excel — это не то же самое, что и ссылки в интернете. Ссылкой называется адрес ячейки (например: А1, В10). Они делятся на два основных вида — абсолютные и относительные ссылки. В Excel адресация происходит по ячейкам, поэтому при переносе и копировании формул нам частенько необходимо сохранить либо, наоборот, заменить данные.

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

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

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

    • Пусть нам даны ячейки с данными (столбцы) — М1, М2, М3, Н1, Н2, Н3. Под ними в ячейке М4 мы пишем формулу, результатом которой будет сумма трёх чисел (М4=М1+М2+М3). Вроде всё просто. Теперь нам необходимо посчитать содержимое ячейки Н4. Она также является суммой тех данных, которые находятся над ней. Перетягиванием или просто копированием ячейки М4, мы переносим формулу в Н4. В результате все ссылки в ней (М1, М2, М3) будут заменены на Н1, Н2 и Н3, соответственно.

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

    Абсолютная ссылка

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

    • Допустим, вам надо решить какую-нибудь физическую задачку. У вас имеется некая константа, записанная в ячейке А1. Конечно, можно было бы каждый раз писать это число вручную, однако проще записать её в одно определённое место. Также нам даны два столбца данных (М1,М2,Р1,Р2). В ячейках М3 и Р3 необходимо посчитать следующую формулу: М3=(М1+М2)*$А$1. Теперь при перетаскивании и копировании в ячейку Р3 получится следующий результат Р3=(Р1+Р2)*$А$1. То есть адрес используемой ячейки не сдвинется.

    Чтобы адрес ссылки сделать абсолютным, нажмите на формулу, выделите необходимую «ссылку» и нажмите F4. Либо вручную напечатайте символы «доллара».

    Смешанная

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

    А В С
    1 3,5 4,5
    2 70
    3 80

    Итак, у нас имеется таблица с некоторыми данными. В столбце «А» у нас имеется некая величина, а в строке «1», коэффициент, на который необходимо умножить. Ячейки В2, С3 будут содержать результаты. Как видите абсолютная и относительная ссылка тут бессильны. В таких случаях необходимо использовать смешанный тип.

    Запишем формулу в первую ячейку В2=А2*В1. Но чтобы перетащить её вправо, нужно закрепить столбец «А», а чтобы вниз, закрепить строку «1». Поэтому правильным вариантом написания формулы станет В2=$А2*В$1. Таким образом, при перетаскивании формулы мы получим ряд значений в каждой из оставшихся ячеек:

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

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