Штирлиц — Сеть печатных салонов в Перми

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

Относительная ссылка в эксель: Относительные, абсолютные и смешанные ссылки на ячейки в 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
  1. Чтобы получить окончательный ответ, нажмите на «Enter». Далее необходимо рассчитать итоговую сумму полученной прибыли с остальных видов продукции. Хорошо если количество строк не велико, тогда все манипуляции можно выполнить вручную. Для заполнения одновременно большого количества строк в Excel имеется одна полезная функция, дающая возможность переноса формулы в другие ячейки.
  2. Наведите курсор на правый нижний угол прямоугольника с формулой или готовым результатом. Появление черного крестика служит сигналом, что курсор можно тянуть вниз. Таким образом производится автоматический расчет полученной прибыли на каждую продукцию в отдельности.
  3. Отпустив зажатую кнопку мыши, получаем правильные результаты во всех строчках.
Чтобы использовать маркер автоматического заполнения, потяните за квадратик, расположенный в правом нижнем углу

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В конце разберем еще один пример абсолютной ссылки – «СуперАбсолютная» адресация. $D2. В столбец D вписываем значение степеней.Пример создания «СуперАбсолютной» адресации путем возведения числа в степень

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

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

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

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

  • Благодаря данной функции ссылка всегда указывает на квадрат с координатами B2, независимо от того будут добавляться или удаляться столбцы в таблице.

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

    Заключение

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

    Сообщение Ссылки в Excel – абсолютные, относительные и смешанные. Ошибки при работе с относительными ссылками в Эксель появились сначала на Информационные технологии.

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

    Хитрости »

    19 Февраль 2021       Дмитрий       7178 просмотров

    Основные понятия (26)
    Сводные таблицы и анализ данных (10)
    Графики и диаграммы (5)
    Работа с VB проектом (12)
    Power BI и Power Query (22)    		 Условное форматирование (5)
    Списки и диапазоны (5)
    Макросы(VBA процедуры) (68)
    Разное (43)
    Баги и глюки Excel (4)

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

    Если Вы еще не знаете что такое макрос и как его записывать и воспроизводить, то рекомендуется сначала ознакомиться со статьей: Что такое макрос и где его искать?
    К примеру, возьмем таблицу такого вида:

    В конце таблицы нам необходимо добавить столбец «Стоимость», прописав в нем нехитрую формулу перемножения количества на цену:
    =F2*G2
    Перед записью макроса выделяем ячейку h2. При обычной записи макроса наши шаги такие:
    1. Выделили I1
    2. Записали в неё заголовок «Стоимость»
    3. Перешли в I2
    4. Записали формулу: =F2*G2
    5. Распространили формулу до конца таблицы (через автозаполнение или путем копирования ячейки с формулой и вставки в остальные ячейки)
    Макрос работает отлично. Пока количество строк не изменится. Если при записи макроса в таблице было 319 строк, а потом добавилось еще 20, то записанный макрос создаст формулу только в первых 319 строках. Все дело в том, что при обычной записи макрос использует абсолютную адресацию ячеек. Т.е. в нем каждый наш шаг обозначает выделение ячеек с конкретно указанным адресом (I1, I2, I319 и т.д.):

    Как выйти из такой ситуации? Все не слишком сложно. В группе кнопок код на вкладке Разработчик есть кнопка «Относительные ссылки». Если нажать её до записи макроса(или во время), то ссылки на ячейки будут уже запоминаться не как конкретный адрес, а как смещение относительно последней выделенной ячейки.
    Например, запишем два простых макроса, которые будут делать одно и то же действие – перемещение вниз таблицы и выделение ячеек от нижней до верхней. Только первый макрос будет записан обычным способом, а перед записью второго мы нажмем кнопку «Относительные ссылки». Наши действия будут следующими (одинаковыми для обоих макросов):
    1. До записи макроса выделяем ячейку I2
    2. Начинаем запись макроса
    3. Выделяем ячейку h3
    4. Комбинацией клавиш Ctrl+↓(стрелка вниз) перемещаемся вниз таблицы
    5. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
    6. Комбинацией клавиш Ctrl+Shift+↑(стрелка вверх) выделяем столбец I от последней ячейки до первой
    7. Завершаем запись макроса
    Теперь можно посмотреть на код обоих макросов:

    Отличия очевидны: в первом используется обращение к ячейкам по их конкретным адресам. Во втором же все действия происходят относительно последней выделенной ячейки(на Range(«A1») не обращаем внимания – это из другой оперы и если их удалить ничего не изменится).
    Из этого можно сделать вывод, что для создания гибких универсальных макросов с использованием относительных ссылок необходимо как можно меньше использовать мышку и максимально стараться применять горячие клавиши. Попробую пояснить почему: когда мы применяем то же автозаполнение (наведение курсора мыши на нижний правый угол ячейки и протягивание вниз или двойной щелчок левой кнопкой мыши) – оно применяется к конкретно определенному количеству ячеек. Т.е. даже относительные ссылки не помогут заполнить его формулами, как того требует наша изначальная задача. Но если использовать горячие клавиши перемещения и выделения (Ctrl+стрелка и Ctrl+Shift+стрелка), то мы можем создать макрос, которому уже будет не важно сколько строк в нашей таблице. Чтобы в этом убедиться, запишем макрос из начала статьи, но уже с использованием относительных ссылок и исключительно клавиш для перемещения. Наши действия:
    1. Перед записью макроса выделяем ячейку h2
    2. Начали запись макроса
    3. Нажимаем кнопку Относительные ссылки(если она еще не нажата)
    4. Выделяем I1
    5. Записываем в неё заголовок «Стоимость»
    6. Переходим в I2
    7. Записываем в I2 формулу: =F2*G2
    8. Комбинацией клавиш Ctrl+C(или при помощи контекстного меню мыши) копируем ячейку с формулой
    9. Стрелкой вправо перемещаемся в ячейку h3
    10. Комбинацией клавиш Ctrl+↓(стрелка вниз) перемещаемся вниз таблицы
    11. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
    12. Комбинацией клавиш Ctrl+Shift+↑(стрелка вверх) выделяем столбец I от последней ячейки до первой
    13. Комбинацией клавиш Ctrl+V вставляем скопированную формулу
    14. Нажимаем Esc для сброса буфера обмена
    15. Запись макроса можно завершить
    Если теперь попробовать применить такой макрос к таблице, у которой строк больше или меньше, чем было при записи макроса – все пройдет идеально. Макрос создаст столбец и запишет в нем формулу только на нужное количество строк.
    Более того. Если наша таблица находится уже в другом листе и даже начинается не с первой ячейки, а где-то в середине:

    Нам достаточно будет выделить ячейку заголовка последнего столбца(K5) и запустить наш макрос. Он без проблем добавит столбец с формулой в нужном месте и на все строки. Макрос же без использования относительных ссылок в такой ситуации спасует по полной: он создаст формулы начиная с ячейки I2 и до заголовка, только испортив таблицу и не сделав ничего полезного.
    Так же хочу дополнить, что Относительные ссылки играют роль исключительно во время записи макроса. Во время воспроизведения совершенно не важно включены они или нет. Плюс можно(а иногда и нужно) комбинировать во время записи макросов режим относительных ссылок с обычным режимом. Например, когда столбцов в таблице у нас всегда одинаковое количество и таблица всегда в одном месте, и столбец мы добавляем всегда в столбец I. Но формулы при этом надо протягивать на разное количество строк. Тогда можно начать запись макроса обычным режимом, а после того, как записали название столбца — включить режим относительных ссылок, чтобы определение последней ячейки таблицы не зависело от количества строк в этой таблице.

    Статья помогла? Поделись ссылкой с друзьями!

        Видеоуроки
    Поиск по меткам
    Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

    гиперссылка на файл по относительному пути

    Эндрю Поулсом
    MrExcel MVP