Абсолютная адресация что это
Чем отличается абсолютная, относительная и смешанная адресация
Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они могут применяться в разных комбинациях и различными способами, что создает различные виды ссылок.
Если вы будете понимать разницу между абсолютными, относительными и смешанными ссылками, то вы на полпути к освоению мощи и универсальности формул и функций Excel. И вот о чем мы должны поговорить:
Применение абсолютной адресации.
Абсолютная – это адресация, при которой идёт указание на конкретную ячейку, адрес которой не изменяется.
Абсолютная адресация в Excel предназначена для того, чтобы можно было многократно использовать одни и те же значения в таблицах, не вводя из каждый раз. Или сделать какой-то расчет единожды и использовать его результаты многократно. При этом вы можете изменять вашу таблицу, добавляя в нее новые ячейки, строки и столбцы, либо перемещая уже существующие на новое место. Ваши формулы и ссылки не «поломаются».
Абсолютная адресация может быть полезна для самых различных расчетов, чтобы сделать их более эффективными. Вот несколько возможностей, о которых следует помнить:
Абсолютная адресация ячеек нам может быть нужна в том случае, когда мы копируем формулу, одна часть которой состоит из переменной величины (например, стоимость товара), а вторая имеет постоянное значение (скажем, размер скидки). То есть, у нас есть постоянная константа, с которой нужно многократно провести определенную операцию (умножение, деление, вычисление процента и т.д.).
Вводим выражение =B2*$F$2 в С2. Таким образом мы при помощи умножения найдём сумму скидки по первому товару.
Нажмите и, удерживая левую кнопку мыши, перетащите маркер автозаполнения насколько необходимо вниз. В нашем случае это диапазон С2:С8. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой. И в каждой будет вычислен результат.
Еще один способ применить абсолютную адресацию – дать ячейке имя. Для этого установите курсор на нужную клетку таблицы и в поле «Имя» впишите ее новое название. Далее вы можете использовать его в ваших расчётах. Важным достоинством этого подхода является и то, что теперь разбираться в ваших вычислениях стало намного проще.
Здесь важно помнить, что в имени можно использовать только буквы и цифры. Если очень хочется присвоить имя, состоящее из нескольких слов, поставьте между ними нижнее подчёркивание (_).
Вот как это может выглядеть на самом простом примере. Вместо «скидка» в любой формуле в этом файле будет использоваться содержимое F2.
Альтернативный и не такой удобный способ именования — это использовать меню. Выбираем Вставка, Имя, Присвоить. Появляется окно, в него вводим имя, нажимаем Добавить и ОК.
И, наконец, есть еще третий способ создать абсолютную адресацию ячейки.
Решение заключается в использовании функции ДВССЫЛ(), которая из текстового значения создает ссылку. Если записать такое выражение:
то оно всегда будет указывать на адрес A2 вне зависимости от любых дальнейших действий, вставки или удаления столбцов и т.д.
Небольшая сложность состоит в том, что если целевая ячейка A2 пуста, то ДВССЫЛ возвратит ноль, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО():
Ну а чем же отличается абсолютная адресация от относительной?
Применение относительной адресации.
Относительную адресацию применять очень просто, так как именно она используется в Excel по умолчанию. То есть, когда вы создаете ссылку, она всегда будет относительной. Относительная адресация означает, что при копировании или перемещении ссылки на какое-то количество строк или столбцов соответствующим образом будут изменяться и координаты относительно первоначального расположения.
То есть, новые координаты изменяются относительно текущего расположения ячейки с формулой.
Рассмотрим на примере.
=A1*B1 – данную формулу, записанную в С1, Excel читает следующим образом: содержимое клетки, находящейся на два столбца слева в той же строке, нужно перемножить с содержимым ячейки, находящейся на один столбец слева в той же строке.
Если это выражение скопировать из С1 в С2, то его понимание для Excel остается точно таким же. То есть, он возьмет ячейку, находящуюся на 2 столбца слева от текущих координат (теперь это будет A2), и перемножит ее с той, которая находится на один столбец слева (то есть, B2).
И формула в С2 примет вид =A2*B2
Если скопировать в С3, то она изменится на =A3*B3.
Когда по умолчанию в вашей формуле использована относительная адресация, то при копировании вниз по столбцу у входящих в нее адресов ячеек номер строки будет увеличиваться.
Ежели копировать формулу вправо, то буква столбца будет увеличиваться по алфавиту. А если влево, то буква столбца будет уменьшаться.
Относительная адресация нужна, если для однотипных данных в строке или столбце выполнить одинаковые вычисления. Например, для нескольких товаров количество умножить на цену.
В этом случае мы записываем формулу один раз (как правило, в верхнюю или самую левую позицию), а затем цепляем мышкой маркер автозаполнения и протаскиваем куда необходимо. Всё пространство заполняется автоматически.
Смешанная адресация.
Если нужно, чтобы при перемещениях адрес изменялся не полностью, применяют так называемую смешанную адресацию.
Или в адресе A$5 при копировании вниз и вверх по столбцу номер строки не будет изменяться, а при передвижении вправо и влево имя столбца будет меняться.
Предположим, нам нужно определить цену товара при различных уровнях наценки. Как это сделать максимально быстро? Вот тут нам и пригодится смешанная адресация.
Формула в С3 будет такая:
Это означает, что в первом сомножителе мы всегда адресуемся к столбцу B. Но номера строк могут меняться в зависимости от расположения формулы.
Во втором сомножителе нам всегда нужна строка 2, в которой записаны проценты надбавки. А вот столбец может изменяться.
Единожды записав эту формулу в B3, копируем ее по строке вправо до E5. И тут же, не снимая выделения, тащим мышкой маркер заполнения в E9. В результате формулу мы ввели единожды, а таблица вся заполнена!
Таким образом, грамотное применение адресации может сэкономить массу времени.
Как изменить тип адресации?
В следующих статьях мы подробно разберем, как наиболее правильно и рационально использовать разные типы адресации при создании ссылок и написании формул.
Типы ссылок EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация
history 18 ноября 2012 г.
Абсолютная адресация (абсолютные ссылки)
Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.
Другой пример.
Относительная адресация (относительные ссылки)
Теперь примеры.
Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.
Альтернативное решение
Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).
Относительная адресация при создании формул для Условного форматирования.
Смешанные ссылки
2. С помощью клавиши F4 (для ввода абсолютной ссылки):
Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,
а затем вернуть его в самую правую позицию (также мышкой),
3. С помощью клавиши F4 (для ввода относительной ссылки).
«СуперАбсолютная» адресация
Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?
Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :
Относительная и абсолютная адресация ячеек
При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Очевидно, что в зависимости от внутренней логики выражений в определенных случаях адреса должны оставаться неизменными, а других ситуациях для пользователя будет удобным и предпочтительным, чтобы их пересчитали с учётом относительного изменения местоположения. По этим причинам в электронных таблицах при написании формул наряду с понятием ссылки используется понятия относительной и абсолютной ссылок.
Определение. Ссылками называются адреса ячеек, содержимое которых используется в вычислениях.
Наличие ссылки в формуле означает, что результат вычисления этой формулы зависит от числа, находящегося в другой ячейке, указанной в ссылке. Иными словами, ячейка, содержащая формулу, является зависимой, при этом значение, отображаемое в ячейке с формулой, пересчитывается при любом изменении данных в ячейке, на которую указывает ссылка.
Ссылку на ячейку можно задать разными способами. В первом случае адрес ячейки можно ввести вручную. Другой способ состоит в щелчке на нужной ячейке или выборе диапазона, адрес которого требуется ввести (ячейка или диапазон при этом выделяются определенным образом).
Определение. Абсолютная ссылка — это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное. Для указания абсолютной адресации обычно используется символ $.
Различают два типа абсолютных ссылок: полная и частичная.
Пример: В$5, D$12, F$5 — частичная абсолютная ссылка, не меняется номер строки;
Определение. Относительная ссылка — это изменяющийся при копировании или перемещении формулы адрес ячейки, содержащий исходное данное. Изменение адреса происходит по правилу относительной ориентации клетки исходной формулой и клеток с операндами.
|
Рис.9.3.Пример: Правило относительной адресации поясним следующим образом. Пусть ячейка с адресом С2 (рис.9.3) содержит формулу-шаблон сложения двух чисел, находящихся в ячейках A1 и В3, т.е. =A1+В3. Эти ссылки являются относительными и отражают ситуацию взаимного расположения исходных данных в ячейках A1 и В3 и результата вычисления по формуле в ячейке С2. По правилу относительной ориентации ячеек ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно ячейки с адресом С2:
§ Ссылка A1 указывает на ячейку, которая смещена относительно С2 на одну ячейку вверх и на две влево;
§ Ссылка В3 указывает на ячейку, которая смещена относительно С2 на одну ячейку вниз и одну влево.
При выполнении операций копирования и перемещения формул следует иметь в виду, что формулы могут содержать три вида ссылок:
§ Двумерные ссылки — Относительные (обозначение А1+В3), Абсолютные ($A$1+$B$3), Смешанные ($A1+B$1);
Следует отметить, что:
§ Относительные ссылки используются в формулах в тех случаях, когда вычисления нужно произвести по одной и той же формуле, но с разными данными. В этом случае при копировании формулы происходит пересчет с другими данными, расположенными по направлению копирования относительно исходных данных.
§ Абсолютные ссылки закрепляют какие-то данные (например, постоянный множитель), и при копировании вычисления происходят с одними и теми же закрепленными данными.
§ Смешанные ссылки фиксируют столбец или строку при копировании.
Способы абсолютной адресации в Microsoft Excel
Как известно, в таблицах Excel существует два вида адресации: относительная и абсолютная. В первом случае ссылка изменяется по направлению копирования на относительную величину сдвига, а во втором — является фиксированной и при копировании остается неизменной. Но по умолчанию все адреса в Экселе являются абсолютными. В то же время, довольно часто присутствует необходимость использовать абсолютную (фиксированную) адресацию. Давайте узнаем, какими способами это можно осуществить.
Применение абсолютной адресации
Абсолютная адресация нам может понадобиться, например, в том случае, когда мы копируем формулу, одна часть которой состоит из переменной, отображаемой в ряду чисел, а вторая имеет постоянное значение. То есть, данное число играет роль неизменного коэффициента, с которым нужно провести определенную операцию (умножение, деление и т.д.) всему ряду переменных чисел.
В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.
Способ 1: абсолютная ссылка
Безусловно, самым известным и часто применяемым способом создать абсолютную адресацию является применение абсолютных ссылок. Абсолютные ссылки имеют отличие не только функциональное, но и синтаксическое. Относительный адрес имеет такой синтаксис:
У фиксированного адреса перед значением координат устанавливается знак доллара:
Знак доллара можно ввести вручную. Для этого нужно установить курсор перед первым значением координат адреса (по горизонтали), находящегося в ячейке или в строке формул. Далее, в англоязычной раскладке клавиатуры следует кликнуть по клавише «4» в верхнем регистре (с зажатой клавишей «Shift»). Именно там расположен символ доллара. Затем нужно ту же процедуру проделать и с координатами по вертикали.
Существует и более быстрый способ. Нужно установить курсор в ячейку, в которой находится адрес, и щелкнуть по функциональной клавише F4. После этого знак доллара моментально появится одновременно перед координатами по горизонтали и вертикали данного адреса.
Теперь давайте рассмотрим, как применяется на практике абсолютная адресация путем использования абсолютных ссылок.
Возьмем таблицу, в которой рассчитывается заработная плата работников. Расчет производится путем умножения величины их личного оклада на фиксированный коэффициент, который одинаков для всех сотрудников. Сам коэффициент расположен в отдельной ячейке листа. Перед нами стоит задача рассчитать заработную плату всех работников максимально быстрым способом.
Для копирования формулы применим такой инструмент, как маркер заполнения. Становимся курсором в нижний правый угол ячейки, где она содержится. При этом сам курсор должен преобразоваться в этот самый маркер заполнения в виде крестика. Зажимаем левую кнопку мыши и тянем курсор вниз до конца таблицы.
В Экселе также применяется, так называемая смешанная адресация. В этом случае в адресе элемента фиксируется либо столбец, либо строка. Достигается это таким образом, что знак доллара ставится только перед одним из координат адреса. Вот пример типичной смешанной ссылки:
Этот адрес тоже считается смешанным:
То есть, абсолютная адресация в смешанной ссылке используется только для одного из значений координат из двух.
Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.
Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:
Как видим, фиксированная адресация во втором множителе применяется только по отношению к координатам строки. Для вывода результата в ячейку щелкаем по кнопке Enter.
Способ 2: функция ДВССЫЛ
Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:
Функция имеет в наличии два аргумента, первый из которых имеет обязательный статус, а второй – нет.
Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.
Аргумент «a1» — необязательный и используется в редких случаях. Его применение необходимо только тогда, когда пользователь выбирает альтернативный вариант адресации, а не обычное использование координат по типу «A1» (столбцы имеют буквенное обозначение, а строки — цифровое). Альтернативный вариант подразумевает использование стиля «R1C1», в котором столбцы, как и строки, обозначаются цифрами. Переключиться в данный режим работы можно через окно параметров Excel. Тогда, применяя оператор ДВССЫЛ, в качестве аргумента «a1» следует указать значение «ЛОЖЬ». Если вы работает в обычном режиме отображения ссылок, как и большинство других пользователей, то в качестве аргумента «a1» можно указать значение «ИСТИНА». Впрочем, данное значение подразумевается по умолчанию, поэтому намного проще вообще в данном случае аргумент «a1» не указывать.
Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.
Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.
Так как мы работаем в стандартном режиме отображения координат, то поле «A1» оставляем незаполненным. Щелкаем по кнопке «OK».
Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.
Помимо этой статьи, на сайте еще 12516 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.