Как известно, в таблицах Excel существует два типа адресации: относительная и абсолютная. В первом случае ссылка изменяется в направлении копии на относительную величину перемещения, а во втором она фиксируется и остается неизменной при копировании. Но по умолчанию все адреса в Excel абсолютны. При этом очень часто бывает необходимо использовать абсолютную (фиксированную) адресацию. Давайте узнаем, как это можно сделать.
Применение абсолютной адресации
Вам может потребоваться абсолютная адресация, например, если вы копируете формулу, часть которой состоит из переменной, отображаемой в виде ряда чисел, а вторая имеет постоянное значение. То есть это число играет роль постоянного коэффициента, с которым необходимо произвести определенную операцию (умножение, деление и т.д.) для всего ряда переменных чисел.
В Excel есть два способа установить фиксированную адресацию: сформировав абсолютную ссылку и используя функцию ДВССЫЛ. Давайте подробно рассмотрим каждый из этих методов.
Способ 1: абсолютная ссылка
Безусловно, наиболее известным и часто используемым способом создания абсолютной адресации является использование абсолютных ссылок. Абсолютные ссылки различаются не только функционально, но и синтаксически. Относительный адрес имеет следующий синтаксис:
= A1
При фиксированном адресе перед значением координаты ставится символ доллара:
= $ A $ 1
Знак доллара можно ввести вручную. Для этого поместите курсор перед первым значением координаты адреса (по горизонтали), найденным в ячейке или в строке формул. Кроме того, в английской раскладке клавиатуры нажмите клавишу «4» в верхнем регистре (удерживая клавишу «Shift»). Здесь находится знак доллара. Таким образом, вам нужно проделать ту же процедуру с вертикальными координатами.
Есть и более быстрый способ. Вам нужно поместить курсор в ячейку, где находится адрес, и нажать функциональную клавишу F4. Впоследствии знак доллара сразу же появится одновременно перед горизонтальной и вертикальной координатами этого адреса.
Теперь давайте посмотрим, как на практике применяется абсолютная адресация с использованием абсолютных ссылок.
Получите электронную таблицу, в которой рассчитывается заработная плата сотрудников. Расчет производится путем умножения вашей личной зарплаты на фиксированный коэффициент, одинаковый для всех сотрудников. Сам коэффициент находится в отдельной ячейке на листе. Перед нами стоит задача максимально быстро рассчитать заработную плату всех сотрудников.
- Затем в первую ячейку столбца «Заработная плата» вводим формулу умножения соответствующей ставки сотрудника на коэффициент. В нашем случае эта формула выглядит так:
= C4 * G3
- Чтобы рассчитать окончательный результат, нажмите клавишу Enter на клавиатуре. Итог отображается в ячейке, содержащей формулу.
- Посчитали зарплату первому сотруднику. Теперь нам нужно проделать то же самое со всеми остальными линиями. Конечно, операцию можно вручную записать в каждую ячейку столбца «Заработная плата», введя аналогичную формулу с поправкой на смещение, но наша задача — выполнить расчеты как можно быстрее, а ручной ввод займет много времени. А зачем тратить силы на ручной ввод, если формулу можно просто скопировать в другие ячейки?
Чтобы скопировать формулу, мы будем использовать такой инструмент, как маркер заливки. Мы становимся курсором в правом нижнем углу ячейки, где он содержится. В этом случае сам курсор нужно превратить в этот крестообразный маркер заливки. Удерживая левую кнопку мыши, перетащите курсор вниз до конца таблицы.
- Но, как видите, вместо правильного расчета заработной платы остальным сотрудникам мы получили только нули.
- Посмотрим, в чем причина такого результата. Для этого выберите вторую ячейку в столбце «Зарплата». Выражение, соответствующее ячейке, отображается в строке формул. Как видите, первый фактор (C5) соответствует ставке сотрудника, чью зарплату мы рассчитываем. Смещение координат относительно предыдущей ячейки произошло из-за свойства относительности. Однако в данном конкретном случае это то, что нам нужно. Благодаря этому первым фактором была точная ставка сотрудников, которая нам нужна. Но сдвиг координат произошел и со вторым фактором. И теперь его адрес относится не к коэффициенту (1,28), а к пустой ячейке внизу.
Это привело к неправильному расчету заработной платы для последующих сотрудников из списка.
- Чтобы разрешить ситуацию, нужно изменить адресацию второго фактора с относительной на фиксированную. Для этого вернитесь в первую ячейку столбца «Зарплата», выделив ее. Затем мы переходим к строке формул, где отображается нужное нам выражение. Выберите второй коэффициент (G3) с помощью курсора и нажмите функциональную клавишу на клавиатуре.
- Как видите, рядом с координатами второго фактора появился знак доллара и это, как мы помним, атрибут абсолютной адресации. Чтобы просмотреть результат на экране, нажмите клавишу Enter.
- Теперь, как и раньше, вызовем маркер заполнения, поместив курсор в правый нижний угол первого элемента столбца «Зарплата». Удерживая левую кнопку мыши, потяните ее вниз.
- Как видите, в данном случае расчет был произведен правильно, а размер заработной платы для всех сотрудников предприятия рассчитан правильно.
- Проверим, как копировалась формула. Для этого выберите второй элемент столбца «Зарплата». Давайте посмотрим на выражение, расположенное в строке формул. Как видите, координаты первого множителя (C5), который все еще является относительным, переместились на одну точку по вертикали от предыдущей ячейки. Но второй фактор ($ G $ 3), в котором мы исправили адресацию, остался без изменений.
Excel также использует так называемую смешанную адресацию. В этом случае в адресе элемента фиксируется столбец или строка. Это достигается тем, что знак доллара ставится только перед одной из координат адреса. Вот пример типичной смешанной ссылки:
= 1 австралийский доллар
Этот адрес также считается смешанным:
= $ A1
То есть абсолютная адресация в смешанной ссылке используется только для одного из двух значений координат.
Давайте посмотрим, как такую смешанную связь можно применить на практике на примере одной и той же таблицы заработной платы сотрудников фирмы.
- Как видите, ранее мы убедились, что все координаты второго фактора имеют абсолютную адресацию. Но давайте посмотрим, должны ли оба значения быть правильными в этом случае? Как видите, при копировании происходит вертикальный сдвиг, а горизонтальные координаты остаются неизменными. Следовательно, вполне возможно применить абсолютную адресацию только к координатам строк и оставить координаты столбцов такими, какими они являются по умолчанию — относительными.
Выберите первый элемент столбца «Зарплата» и выполните действия, описанные выше, в строке формул. Получаем следующую формулу:
= C4 * G $ 3
Как видите, фиксированная адресация во втором множителе применяется только по отношению к координатам линии. Чтобы просмотреть результат в ячейке, нажмите кнопку Enter.
- Затем, используя индикатор заполнения, скопируйте эту формулу в указанный ниже диапазон ячеек. Как видите, расчет заработной платы для всех сотрудников правильный.
- Давайте посмотрим, как скопированная формула появится во второй ячейке столбца, с которым мы работали. Как вы можете видеть в строке формул, после выбора этого элемента листа, несмотря на то, что только координаты строки имели абсолютную адресацию для второго фактора, координаты столбца не перемещались. Это связано с тем, что мы копировали не по горизонтали, а по вертикали. Если бы мы копировали по горизонтали, то в аналогичном случае, наоборот, нам пришлось бы выполнять фиксированную адресацию координат столбца, а для строк эта процедура была бы необязательной.
Способ 2: функция ДВССЫЛ
Второй способ организовать абсолютную адресацию в электронной таблице Excel — использовать оператор INDIRECT. Указанная функция принадлежит к группе встроенных операторов «Ссылки и массивы». Его задача — сформировать ссылку на заданную ячейку с выводом результата на элемент листа, где находится оператор. Это связывает ссылку с координатами даже сильнее, чем при использовании знака доллара. Поэтому иногда ссылки, в которых используется НЕПРЯМОЙ, принято называть «абсолютным супер». Этот оператор имеет следующий синтаксис:
= ДВССЫЛ (ссылка_ячейки; [a1])
Функция имеет два аргумента, первый из которых является обязательным, а второй — нет.
Ссылка на ячейку — это текстовая ссылка на элемент листа Excel. То есть это обычная ссылка, но заключенная в кавычки. Это то, что позволяет предоставлять свойства абсолютной адресации.
Аргумент «a1» является необязательным и используется редко. Его использование необходимо только тогда, когда пользователь выбирает альтернативный вариант адресации, а не обычное использование координат типа «A1» (столбцы обозначены буквами, а строки — числовыми). Альтернативный вариант — использовать стиль «R1C1», где столбцы, как и строки, обозначаются числами. Вы можете переключиться в этот режим работы через окно параметров Excel. Затем, используя оператор INDIRECT, укажите значение «FALSE» в качестве аргумента «a1». Если вы работаете в обычном режиме отображения ссылок, как и большинство других пользователей, вы можете указать «ИСТИНА» в качестве аргумента «a1». Однако это значение используется по умолчанию, поэтому в этом случае гораздо проще не указывать аргумент «a1.
Давайте посмотрим, как будет работать абсолютная адресация, организованная с помощью функции ДВССЫЛ, на примере нашей таблицы выплат.
- Выбираем первый элемент столбца «Зарплата». Ставим знак «=». Как вы помните, первый фактор в формуле, указанной для расчета зарплаты, должен быть представлен относительным адресом. Поэтому просто щелкните ячейку, содержащую соответствующее значение заработной платы (C4). После того, как его адрес отобразился в элементе отображения результата, нажмите на клавиатуре кнопку «умножить» (*). Поэтому нам нужно перейти к использованию оператора INDIRECT. Щелкаем по иконке «Вставить функцию».
- В открывшемся окне мастера перейдите в категорию «Ссылки и массивы». Среди представленного списка имен отметим имя «КОСВЕННО». Затем нажмите кнопку «ОК».
- Поле аргумента оператора INDIRECT включено. Он состоит из двух полей, соответствующих аргументам этой функции.
Установите курсор в поле «Ссылка на ячейку». Просто нажмите на элемент листа, который содержит коэффициент для расчета заработной платы (G3). Адрес сразу появится в поле окна темы. Если бы мы имели дело с обычной функцией, то адресную запись можно было бы считать завершенной, но мы используем функцию ДВССЫЛ. Как мы помним, содержащиеся в нем адреса должны быть в текстовой форме. Поэтому мы заключаем координаты, которые находятся в поле окна, в кавычки.
Поскольку мы работаем в стандартном режиме отображения координат, поле «А1» оставляем пустым. Щелкните кнопку «ОК».
- Приложение выполняет расчет и возвращает результат элементу листа, содержащему формулу.
- Теперь скопируем эту формулу во все остальные ячейки столбца «Зарплата» с помощью индикатора заполнения, как мы это делали ранее. Как видите, все результаты рассчитаны правильно.
- Посмотрим, как формула появится в одной из ячеек, куда она была скопирована. Выберите второй элемент столбца и посмотрите на строку формул. Как видите, первый фактор, являющийся относительной ссылкой, изменил свои координаты. При этом аргумент второго фактора, который представлен функцией ДВССЫЛ, остался неизменным. В этом случае использовалась методика фиксированной адресации.
Абсолютная адресация в электронных таблицах Excel может быть достигнута двумя способами: с помощью функции КОСВЕННО и с помощью абсолютных ссылок. В этом случае функция обеспечивает более жесткую привязку к адресу. Частично абсолютная адресация также может использоваться при использовании смешанных ссылок.