Как сделать относительную адресацию в excel?

Относительная, абсолютная и смешанная адресация в Excel

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

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

Она используется по умолчанию. Результатом такого обращения является то, что при копировании формулы, в которой задействованы адреса, из одной ячейки в другую, параметры будут изменяться. Запись адреса осуществляется путем прямого указания на название столбца и номер строки – например, «A1» или «R1C1» при выборе.

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

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

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

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

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

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

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

В примере представлена абсолютная ссылка на ячейку – «$C$10» и «$C$11».

Для указания абсолютной ссылки на столбец символ «$» записывается только перед столбцом, а именно «$C10». Аналогично для абсолютной ссылки на строку – только перед номером строки — «C$10».

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

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

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

Например, есть два столбца со значениями: массив B2:B5 и C2:C5. В столбце D внесем сумму чисел из первого и второго столбцов по формуле $B2 + C2 (и аналогично для прочих строк диапазона).

Если между столбцами A и B добавить новый столбец, то формула модифицируется $C2 + D2.

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

Если в обновленном поле нет числового значения, то в формулу по умолчанию передается ноль.

Для того чтобы избежать ошибок при выводе решения (например, деление на ноль), можно добавить функции проверки ЕПУСТО() в связке с ЕСЛИ().

Еще один вариант – использование функции СМЕЩ(), в которой помимо основной ячейки задается расстояние для строк и столбцов в отстоящем диапазоне.

Типы ссылок MS EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация

В формулах EXCEL можно сослаться на другую ячейку используя ее адрес. Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул, задания правил Условного форматирования и при формировании условий Проверки данных.

В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке В1 содержится формула =А1+5 , то означает, что в ячейку В1 будет помещено значение ячейки А1 находящейся на пересечении столбца А и строки 1, к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек А2, А3, . А11. Однако, формула =СУММ($А$2:$А$11) также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница?

Читать еще:  Как сделать ссылку на неизменяемую ячейку в excel?

Абсолютная адресация (абсолютные ссылки)

Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде $А$2:$А$11 . Абсолютная ссылка позволяет при копировании формулы однозначно зафиксировать адрес диапазона или адрес ячейки. Рассмотрим пример.

Пусть в ячейке В2 введена формула =СУММ( $А$2:$А$11 ) , а в ячейке С2 формула =СУММ(А2:А11). Скопировав формулы вниз, например с помощью Маркера заполнения, во всех ячейках столбца В получим одну и ту же формулу =СУММ( $А$2:$А$11 ) , т.е. ссылка на диапазон ячеек при копировании не изменилась. А в столбце С получим другой результат: в ячейке С3 будет формула =СУММ(A3:A12) , в ячейке С4 будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была модифицирована.

Другой пример.

Пусть в диапазоне А1:А5 имеются числа (например, зарплата сотрудников отдела), а в С1 – процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне В1:В5. Для этого введем в ячейку В1 формулу =А1*С1 . Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в В2:В5 нули (при условии, что в диапазоне С2:С5 нет никаких значений). В ячейке В5 будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).

Чтобы выйти из ситуации — откорректируем формулу в ячейке В1.

  • выделите ячейкуВ1;
  • войдите в режим правки ячейки (нажмите клавишу F2) или поставьте курсор в Строку формул;
  • поставьте курсор на ссылку С1 (можно перед С, перед или после 1);
  • нажмите один раз клавишу F4. Ссылка С1 выделится и превратится в $C$1 (при повторных нажатиях клавиши F4 ссылка будет принимать последовательно вид C$1, $C1,C1, $C$1, …). Ссылка вида $C$1 называется абсолютной, C$1, $C1 – смешанными, а С1относительной.

Такм образом, введем в В1 формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $.

Нажмем ENTER и протянем ее вниз. Теперь в В5 будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).

Относительная адресация (относительные ссылки)

Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1. Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1? После протягивания ее вниз Маркером заполнения, в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.

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

Теперь примеры.

Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А: значения из той же строки и значения из строки выше.

Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3: =СУММ(A2:A3) и т.д.

Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже. Другим вариантом решения этой задачи является использование Именованной формулы. Для этого:

  • выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах). Теперь B2 – активная ячейка;
  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
  • в поле Имя введите, например Сумма2ячеек;
  • убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
  • Нажмите ОК.

Теперь в B2 введем формулу =Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1). Если формулу ввести в ячейку B5, то она будет суммировать ячейки A4:A5, если ввести в D10, то – ячейки С9:С10.

Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).

Относительная адресация при создании формул для Условного форматирования.

Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2). Построим такую таблицу:

Читать еще:  Как сделать несколько вертикальных осей в excel?

  • выделите диапазон таблицы B2:F11, так, чтобы активной ячейкой была B2 (важно выделить диапазон начиная с B2, а не с F11. Во втором случае, активной ячейкой будет F11);
  • вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило/ использовать формулу для …);
  • введите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50) ;
  • выберите Формат;
  • нажмите ОК

Важно отметить, что, если бы, при создании правила, активной ячейкой была F11, то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11.

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

Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец B, а строка может изменяться в зависимости при копировании формулы.

Предположим, у нас есть столбец с ценами в диапазоне B3:B6 (см. файл примера , лист пример3). В столбцах С, D, Е содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах F, G, H посчитать годовые продажи в рублях, т.е. перемножить столбцы С, D, Е на столбец B. Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку F вводим: =$В3*C3 . Потом протягиваем формулу маркером заполнения вниз до F6,

а затем весь столбец таблицы протягиваем вправо на столбцы G и H.

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

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

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

Абсолютная ссылка – ссылка, в которой координаты ячейки зафиксированы и не меняется при копировании.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
  • Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.

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

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

Заключение

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

информатика экзамен / 17 Адресация ячеек в Excel Относительные и абсолютные

17 Адресация ячеек в Excel Относительные и абсолютные

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

Большинство ссылок в формулах записываются в относительной форме — например, С3 (столбец)(строка)

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

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

В большинстве случаев это очень удобно, но иногда этого не требуется. Поясним это на следующем примере.

Необходимо вычислить стоимость каждой модели принтеров на складе. Т.к. курс $ периодически изменяется, ячейка B11 будет использоваться для хранения текущего значения. При изменении кура достаточно внести новое значение в ячейку С11 и стоимость будет автоматически пересчитана.

Вставим необходимые для расчета формулы В ячейку F14 =В14*E14 В ячейку G14 =F14*B11

Выделим ячейки F14 и G14

При помощи автозаполнения скопируем в нижележащие строки

Обратите внимание на возникшие ошибки в столбце G

Перейдем в режим отображения формул при помощи меню Сервис — Параметры

При копировании формулы из 14 в 15 строку Excel изменил адрес ячейки с B11 на B12, что в нашем случае делать не следовало.

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

Дважды щелкните мышью по ячейки G14 (перейдите в режим редактирования)

Нажмите клавишу . Теперь в формуле участвует абсолютный адрес $B$11

Введите формулу, нажав

Скопируйте в нижележащие ячейки

Отмените режим отображения формул (Сервис — Параметры)

Некоторые ссылки в формулах записываются в абсолютной форме — например, $С$3

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

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

Изменение типа ссылки

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

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

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

Использование имен для абсолютной адресации

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

Например назначив ячейки B11 имени курс можно ввести следующую формулу

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

Для того, чтобы назначить имя ячейки необходимо

Выполнить команду меню Вставка — Имя — Присвоить

Введите имя в стоке имя ячейки, например курс

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