Адрес ячейки ms excel имеет вид а 5 какая это ссылка
В Excel есть три типа ссылок: относительная, абсолютная и смешанная.
Как сделать: описание, пошаговая инструкция, примеры и иллюстрации.
Все ссылки в Excel по умолчанию относительные.
Абсолютные и смешанные ссылки можно сделать с помощью символа доллар ($), прописав ее в формуле.
Рассмотрим разницу на примере.
Относительная ссылка
Пример с относительными ссылками.
Нужно посчитать сумму налога от окладов сотрудников предприятия, которую необходимо перечислить в налоговый фонд.
В таблице напротив ФИО каждого сотрудника стоит его оклад, процент налога и сумма самого налога (См. Рис 1).
В данном примере, в каждой ячейке прописана формула, где оклад сотрудника умножается на ставку налога, и так по каждому сотруднику.
Обе ссылки являются относительной.
В чем недостаток такого варианта. Если процент налога меняется, нужно менять его в каждой ячейке, где он указан. Плюс сама по себе таблица становиться громоздкой, если речь идет о больших объемах информации.
Так же, если Вы протягиваете формулу, в которой используются относительные ссылки на ячейки, Вам нужно будет редактировать всю таблицу таким образом, чтобы формула ссылалась на верные ячейки с данными.
Абсолютная ссылка
Пример с абсолютной ссылкой.
Нужно посчитать сумму налога от окладов сотрудников предприятия, которую необходимо перечислить в налоговый фонд.
В данном примере ставка налога будет указана в отдельной ячейке (См. Рис. 2.)
Что бы ссылку на ячейку с налогом сделать абсолютной в формуле прописываем знак доллар ($).
Формула =E6*$E$2.
В данном случае абсолютной ссылкой является ссылка на ячейку *$E$2, и все формулы, в которых она указана, будут использовать значение, указанное в этой ячейки.
В нашем примере, если протянуть формулу вниз, по столбцу «Сумма налога», будет меняться только ссылка на ячейки с окладами сотрудников, а ссылка на процент не будет меняться.
Таким образом, если меняется ставка налога, Вы меняете значение только в одной ячейки.
Сама таблица при использовании абсолютных ссылок становиться меньше и удобнее для восприятия.
Ссылка указывает на ячейку или диапазон ячеек, которые содержат данные и используются в формуле. Ссылки позволяют:
использовать данные в одной формуле, которые находятся в разных частях таблицы.
использовать в нескольких формулах значение одной ячейки.
Имеются два вида ссылок:
- относительные- зависящие от положения формулы;
- абсолютные — не зависящие от положения формулы.
Относительные ссылки. Относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.
При копировании формулы вдоль столбца или строки относительная ссылка корректируется:
смещение на один столбец — изменение в ссылке одной буквы в имени столбца.
смещение на одну строку — изменение в ссылке номера строки на единицу.
Абсолютные ссылки. Абсолютная ссылка всегда ссылается на ячейку, расположенную в определенном (фиксированном) месте. Перед каждой буквой и цифрой ставится знак $, например $B$2. При изменении позиции ячейки, которая имеет формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и столбцов абсолютная ссылка не корректируется (рисунок 4).
.jpg)
Смешанные ссылки. Смешанная ссылка содержит абсолютно адресуемый столбец и относительно адресуемую строку ( $A1) или относительно адресуемый столбец и абсолютно адресуемую строку ( A$1). При изменении позиции ячейки, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется. При копировании формулы вдоль строк и столбцов относительная ссылка корректируется, а абсолютная ссылка нет (рисунок 5).
Применение абсолютной адресации
Абсолютная адресация нам может понадобиться, например, в том случае, когда мы копируем формулу, одна часть которой состоит из переменной, отображаемой в ряду чисел, а вторая имеет постоянное значение. То есть, данное число играет роль неизменного коэффициента, с которым нужно провести определенную операцию (умножение, деление и т.д.) всему ряду переменных чисел.
В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.
Способ 1: абсолютная ссылка
Безусловно, самым известным и часто применяемым способом создать абсолютную адресацию является применение абсолютных ссылок. Абсолютные ссылки имеют отличие не только функциональное, но и синтаксическое. Относительный адрес имеет такой синтаксис:
У фиксированного адреса перед значением координат устанавливается знак доллара:
Существует и более быстрый способ. Нужно установить курсор в ячейку, в которой находится адрес, и щелкнуть по функциональной клавише F4. После этого знак доллара моментально появится одновременно перед координатами по горизонтали и вертикали данного адреса.
Теперь давайте рассмотрим, как применяется на практике абсолютная адресация путем использования абсолютных ссылок.
Возьмем таблицу, в которой рассчитывается заработная плата работников. Расчет производится путем умножения величины их личного оклада на фиксированный коэффициент, который одинаков для всех сотрудников. Сам коэффициент расположен в отдельной ячейке листа. Перед нами стоит задача рассчитать заработную плату всех работников максимально быстрым способом.
-
Итак, в первую ячейку столбца «Заработная плата» вводим формулу умножения ставки соответствующего работника на коэффициент. В нашем случае эта формула имеет такой вид:
В Экселе также применяется, так называемая смешанная адресация. В этом случае в адресе элемента фиксируется либо столбец, либо строка. Достигается это таким образом, что знак доллара ставится только перед одним из координат адреса. Вот пример типичной смешанной ссылки:
Этот адрес тоже считается смешанным:
То есть, абсолютная адресация в смешанной ссылке используется только для одного из значений координат из двух.
Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.
-
Как видим, ранее мы сделали так, что все координаты второго множителя имеют абсолютную адресацию. Но давайте разберемся, обязательно ли в этом случае оба значения должны быть фиксированными? Как видим, при копировании происходит смещение по вертикали, а по горизонтали координаты остаются неизменными. Поэтому вполне можно применить абсолютную адресацию только к координатам строки, а координаты столбца оставить такими, каковыми они являются по умолчанию – относительными.
Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:
Способ 2: функция ДВССЫЛ
Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:
Функция имеет в наличии два аргумента, первый из которых имеет обязательный статус, а второй – нет.
Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.
Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.
-
Производим выделение первого элемента столбца «Заработная плата». Ставим знак «=». Как помним, первый множитель в указанной формуле вычисления зарплаты должен быть представлен относительным адресом. Поэтому просто кликаем на ячейку, содержащую соответствующее значение оклада (C4). Вслед за тем, как её адрес отобразился в элементе для вывода результата, жмем на кнопку «умножить» (*) на клавиатуре. Затем нам нужно перейти к использованию оператора ДВССЫЛ. Выполняем щелчок по иконке «Вставить функцию».
Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.
Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки - бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 - не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными:
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) - она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную - это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно "но".
Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь - даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки.
Если ввести в ячейку формулу:
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
Читайте также: