Что такое смешанная ссылка в excel

Что такое смешанная ссылка в excel

������� ���������� ��������� MS Office 2007: Microsoft Excel

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

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

В формулах используется три типа ссылок на ячейки и диапазоны.

• Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
• Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
• Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5 ). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4 ). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4 ). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1 , то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе — в А$1 , третье — в $А1 , а четвертое вернет ей первоначальный вид — А1 . Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Различие между разными типами ссылок проявляется при копировании формул.

На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2 . Если ее скопировать маркером заполнения на ячейки D3 и D4 , то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ , а в ячейке D4 соответственно =В4*С4 .

Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2 .

Если теперь скопировать эту формулу в ячейку D3 , то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис.31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7 .

В результате в ячейке D1 получим Комиссионные . В ячейку D2 введем формулу =В2*С2*$В$7 . Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7 . Обратите внимание на то, что ссылка на ячейку В7 является абсолютной . Скопировав ячейку D2 в D3 , получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 — нет, т.е. мы получили правильный результат.

На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого — 2, а ширина — 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ( $В ), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ( $2 ). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2 .

При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.

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

По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы указываете адрес ячейки в том же ряду (2), но отстоящей на два столбца влево (C минус A). Формула с относительной ссылкой изменяется при копировании из одной ячейки в другую. Например, вы можете скопировать формулу =A2+B2 из ячейки C2 в C3, при этом формула в ячейке C3 сдвинется вниз на один ряд и превратится в =A3+B3.

Если необходимо сохранить исходный вид ссылки на ячейку при копировании, ее можно зафиксировать, поставив перед названиями столбца и строки знак доллара ($). Например, при копировании формулы =$A$2+$B$2 из C2 в D2 формула не изменяется. Такие ссылки называются абсолютными.

В некоторых случаях ссылку можно сделать «смешанной», поставив знак доллара перед указателем столбца или строки для «блокировки» этих элементов (например, $A2 или B$3). Чтобы изменить тип ссылки на ячейку, выполните следующее.

1. Выделите ячейку со ссылкой на ячейку, которую нужно изменить.
2. В строка формул

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

Текущая ссылка (описание):

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

$A$1 (абсолютная ссылка)

A$1 (относительный столбец и абсолютная строка)

C$1 (смешанная ссылка)

$A1 (абсолютный столбец и относительная строка)

$A3 (смешанная ссылка)

A1 (относительный столбец и относительная строка)

C3 (относительная ссылка)

Абсолютные относительные и смешанные ссылки в Excel с примерами

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

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

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

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

Абсолютная ссылка выглядит следующим образом: знак «$» стоит перед буквой и перед цифрой.

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

Полезный совет! Чтобы не вводить символ «$» вручную, при вводе используйте клавишу F4 как переключатель между всеми типами адресов.

Например, если нужно изменить ссылку с относительной на абсолютную:

1. Просто выделите ячейку, формулу в которой необходимо выполнить замену.
2. Перейдите в строку формул и поставьте там курсор непосредственно на адрес.
3. Нажмите на «F4». Вы заметите, так программа автоматически предлагает вам разные варианты и расставляет знак доллара.
4. Выбирайте подходящую вам ссылку, периодически нажимая F4 и жмите на «Enter».

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

Относительная ссылка на ячейку в Excel

По умолчанию (стандартно) все ссылки в Excel относительные. Они выглядят вот так: =А2 или =B2 (только цифра+буква):

Если мы решим скопировать эту формулу из строки 2 в строку 3 адреса в параметрах формулы изменяться автоматически:

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

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

1. Выделяем нужную нам ячейку.
2. Нажимаем Ctrl+C.
3. Выделяем ячейку, в которую необходимо вставить относительную формулу.
4. Нажимаем Ctrl+V.

Полезный совет! Если у вас однотипный расчет, можно воспользоваться простым «лайфхаком». Выделяем ячейку , после этого подводим курсор мышки к квадратику расположенному в правом нижнем углу . Появится черный крестик. После этого просто «протягиваем» формулу вниз. Система автоматически скопирует все значения. Данный инструмент называется маркером автозаполнения.

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

Часто пользователям необходимо изменить только ссылку на строку или столбец, а часть формулу оставить неизменной. Сделать это просто, ведь в Excel существует такое понятие как «смешанная ссылка».

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

Сначала разложим все по порядку. Смешанные ссылки могут быть всего 2-х видов:

Тип ссылки	Описание
В$1	При копировании формулы относительно вертикали не изменяется адрес (номер) строки.
$B1	При копировании формулы относительно горизонтали не меняется адрес (латинская буква в заголовке: А, В, С, D…) столбца.

Чтобы сделать ссылку относительной, используйте эффективные способы. Конечно, вы можете вручную проставить знаки «$» (переходите на английскую раскладку клавиатуры, после чего жмете SHIFT+4).

Сначала выделяете ячейку со ссылкой (или просто ссылку) и нажимаете на «F4». Система автоматически предложит вам выбор ячейки, останется только одобрительно нажать на «Enter».

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Excel: Ссылки относительные и абсолютные

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

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

Ссылка в Excel — адрес ячейки или связного диапазона ячеек.

Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.

Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Ссылки в Excel бывают 3-х типов:

• Относительные ссылки (пример: A1);
• Абсолютные ссылки (пример: $A$1);
• Смешанные ссылки (пример: $A1 или A$1).

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

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

Пример.

В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.

При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7 формула изменяется (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).

Теперь формула в ячейке А7 ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).

Относительные ссылки целесообразно использовать в формулах в двух случаях:

1. Если формулу не предполагается копировать в другие ячейки.
2. Если формулу необходимо скопировать в идентичные ячейки.

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

Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка. Для этого перед символами ссылки устанавливаются символы «$» (формат записи $А$1).

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

1. Необходимости применения в формулах констант.
2. Необходимости фиксации диапазона для проведения расчетов.

Пример.

В диапазоне А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию каждого сотрудника и поместим в диапазоне В1:В5.

Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.

Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Так как в ячейках диапазона С2:С5 нет значений, то в диапазоне В2 : В5 получаем нули.
Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.

• выделите ячейку В1
• в Строке формул поставьте знак «$» перед буквой столбца и адресом строки $С$1. Более быстрый способ — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед или после 1) и нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
• нажмите ENTER

Формула приняла вид « =А1*$С$1».
Маркером заполнения протяните полученную формулу вниз.

Теперь диапазон В2: В5 заполнен значениями премий сотрудников.

Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знаки «$».