Нажатие какой клавиши меняет относительный адрес в формуле на абсолютный

Изменение типа ссылки: относительная, абсолютная, смешанная

По умолчанию ссылка на ячейку является относительной ссылкой, которая означает, что ссылка относительна к расположению ячейки. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая находится на два столбца слева (C минус A) в одной строке (2). При копировании формулы, содержаной относительную ссылку на ячейку, эта ссылка в формуле изменится.

Например, при копировании формулы =B4*C4 из ячейки D4 в D5 формула в ячейке D5 корректируется на один столбец вправо и становится =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, необходимо сделать ссылку на ячейку абсолютной, предшествуя столбцам (B и C) и строке (2) знаком доллара($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается той же.

В меньшей степени может потребоваться смешанные абсолютные и относительные ссылки на ячейки, предшествуя столбецу или значению строки знаком доллара, что исправит столбец или строку (например, $B 4 или C$4).

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

1. Выделите ячейку с формулой.
2. В строке формул строка формул выделите ссылку, которую нужно изменить.
3. Для переключения между типами ссылок нажмите клавишу F4. В приведенной ниже таблице по сумме обновляется тип ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки справа.

Копируемая формула

Первоначальная ссылка

Новая ссылка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Новая ссылка

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

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

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

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

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

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

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

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

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 сам проставит знаки «$».

Автопересчет

Если в ячейках, адреса которых указаны в формулах, вносить изменения, то программа MS Excel автоматически произведет перерасчет по формуле с учетом новых значений. В тех случаях, когда таблица велика, то при ее редактировании каждый раз будет включаться перерасчет, что займет достаточно много времени. Поэтому, с целью сокращения времени отладки такой таблицы, на время ее отладки автоматический перерасчет можно исключить. Это можно сделать, выполнив следующую последовательность операций с пунктами основного меню: СЕРВИС – ПАРАМЕТРЫ. В раскрывшемся окне ПАРАМЕТРЫ, на вкладке ВЫЧИСЛЕНИЯ, необходимо включить переключатель ВРУЧНУЮ. После этого перерасчет значений возможен только при нажатии клавиши F9.

Ошибки в формулах

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

Таблица 19.1. Сообщения об ошибках в формулах

Попытка деления на ноль.

Отсутствие данных (возможно ячейка пуста).

Ссылка на несуществующее имя.

Использован недопустимый числовой аргумент.

Неправильно указан адрес ячейки.

Тип значения не совпадает с типом данных, допустимых для данного аргумента.

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

Часто приходится многократно выполнять расчеты по одной и той же формуле, но при различных значениях аргументов. Например, вычисление функции вида у = ах 2 для ряда значений аргумента х. Если подходить формально, следуя логике вычислений по формулам принятой в MS Excel, то в каждой ячейке, куда будет помещаться результат вычисления, необходимо создавать формулу с указанием адреса ячейки, содержащей значение х и адреса ячейки, содержащей значение коэффициента а. Такой подход возможен, но он требует больших затрат времени и энергии.

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

Для реализации такого способа вычислений по формулам в MS Excel предусмотрены два вида адресации:

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

= С1*А1*А1

Предполагается все последующие вычисляемые значения функции у по приведенной формуле последовательно помещать в ячейках интервала В2:В10. Следовательно, копии формулы следует поочередно создавать во всех ячейках интервала В2:В10. Если копия формулы помещается в ячейку В2, то адрес коэффициента С1 должен остаться неизменным, а адрес аргумента должен соответственно измениться. Копия должна принять следующий вид = С1*А2*А2. В ячейке В10 формула принимает вид = С1*А10*А10. Как видно, здесь аргумент синхронно меняет свой адрес относительно своего адреса, указанного в оригинале формулы. В то же время адрес коэффициента С1 должен оставаться неизменным, то есть абсолютным. Иными словами новый адрес аргумента меняется на столько, на сколько ячейка с копией формулы отдалена от исходной по вертикали и по горизонтали.

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

Признаком абсолютной адресации является наличие знака $ перед значением координаты в адресе ячейки. Если в формуле адрес ячейки представлен как $А2, то это означает, что адрес столбца А является абсолютным и остается неизменным, а адрес строки воспринимается как относительный и может изменяться. В случае обозначения адреса в виде $А$5 – адрес ячейки А5 воспринимается как абсолютный и остается неизменным во всех случаях. Адресация вида А$9 говорит о том, что адрес столбца относительный, а адрес строки абсолютный. Сочетание адресации может быть различной.

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

Порядок создания абсолютного адреса следующий:

• выделить в формуле адрес, подлежащий редактированию, таким же способом, как выделяется фрагмент текста в текстовом редактореMSWord, то есть установить указатель мыши на первый символ адреса и при нажатой ее левой клавиши перемещать мышь до последнего символа выделяемого адреса, после чего отпустить клавишу (выделенный фрагмент окрасится темным цветом);
• нажать на клавиатуре клавишуF4, после чего адрес примет вид $В$6, то есть будет установлена абсолютная адресация и для столбца В и для строки 6. Вторичное нажатие клавиши F4 приведет к изменению адреса к виду В$6, последующее ее нажатие – к виду $В6. Нажимая, таким образом, клавишу F4, можно устанавливать желаемую абсолютную адресацию.