Как обозначаются абсолютные ссылки

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

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

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

Чтобы изменить тип ссылки на ячейку:

Выделите ячейку с формулой.

В строке формул строка формул выделите ссылку, которую нужно изменить.

Для переключения между типами ссылок нажмите клавишу F4.

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

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

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

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

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

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

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

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

При работе с формулами в программе Microsoft Excel пользователям приходится оперировать ссылками на другие ячейки, расположенные в документе. Но, не каждый пользователь знает, что эти ссылки бывают двух видов: абсолютные и относительные. Давайте выясним, чем они отличаются между собой, и как создать ссылку нужного вида.

Определение абсолютных и относительных ссылок

Что же представляют собой абсолютные и относительные ссылки в Экселе?

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

Пример относительной ссылки

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

Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.

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

Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.

Ошибка в относительной ссылке

Но, далеко не во всех случаях нам нужны именно относительные ссылки. Например, нам нужно в той же таблице рассчитать удельный вес стоимости каждого наименования товара от общей суммы. Это делается путем деления стоимости на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы его стоимость (D2) делим на общую сумму (D7). Получаем следующую формулу: «=D2/D7».

В случае, если мы попытаемся скопировать формулу в другие строки тем же способом, что и предыдущий раз, то получим совершенно неудовлетворяющий нас результат. Как видим, уже во второй строке таблицы формула имеет вид «=D3/D8», то есть сдвинулась не только ссылка на ячейку с суммой по строке, но и ссылка на ячейку, отвечающую за общий итог.

D8 – это совершенно пустая ячейка, поэтому формула и выдаёт ошибку. Соответственно, формула в строке ниже будет ссылаться на ячейку D9, и т.д. Нам же нужно, чтобы при копировании постоянно сохранялась ссылка на ячейку D7, где расположен итог общей суммы, а такое свойство имеют как раз абсолютные ссылки.

Создание абсолютной ссылки

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

С созданием относительных ссылок у пользователей проблем не будет, так как все ссылки в Microsoft Excel по умолчанию являются относительными. А вот, если нужно сделать абсолютную ссылку, придется применить один приём.

После того, как формула введена, просто ставим в ячейке, или в строке формул, перед координатами столбца и строки ячейки, на которую нужно сделать абсолютную ссылку, знак доллара. Можно также, сразу после ввода адреса нажать функциональную клавишу F7, и знаки доллара перед координатами строки и столбца отобразятся автоматически. Формула в самой верхней ячейке примет такой вид: «=D2/$D$7».

Копируем формулу вниз по столбцу. Как видим, на этот раз все получилось. В ячейках находятся корректные значения. Например, во второй строке таблицы формула выглядит, как «=D3/$D$7», то есть делитель поменялся, а делимое осталось неизменным.

Смешанные ссылки

Кроме типичных абсолютных и относительных ссылок, существуют так называемые смешанные ссылки. В них одна из составляющих изменяется, а вторая фиксированная. Например, у смешанной ссылки $D7 строчка изменяется, а столбец фиксированный. У ссылки D$7, наоборот, изменяется столбец, но строчка имеет абсолютное значение.

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

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

Помогла ли вам эта статья?

Еще статьи по данной теме:

Итог часы:минуты образован формулой =СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))

03:26
05:15
06:01
04:03
04:21
10:59
Задача: как сложить время в данном столбце с учетом, что Excel считает 24 часа за 1, а мне надо общее количество часов или количество дней, часов и минут.
Спасибо,
Валерий

Здравствуйте, Валерий. Попробуйте в той ячейке, в которую будет выводится общая сумма, установить формат «[ч]:мм». Просто откройте окно форматов, перейдите в раздел «Все форматы» и в поле «Тип» пропишите вышеуказанное значение. Затем жмите «OK».

Спасибо за отзыв, но это я пробовал. Не помогло. В сумее получаются нули.

Валерий, можете тогда приложить два скриншота:
1. На одном скриншоте должна быть выделена одна из ячеек слагаемых, чтобы я мог увидеть, какая формула содержится там.
2. На втором скриншоте выделите ячейку, в которой производится общее суммирование.
Отправьте каждый скриншот в отдельном сообщении.

Максим, в наименовании файлов ответ на Вашу просьбу

Ответ на второй вопрос

Валерий, к сожалению, в таком формате сложить не получится, так как в содержащих формулу ячейках, значения не воспринимаются, как время. Но есть, один вариант.

1. Выделите все ячейки столбца, в которых содержится формула «=СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))»
2. Кликните по выделению правой кнопкой мыши и выберите вариант «Копировать».
3. Тут же не снимая выделение опять кликайте правой кнопкой мыши по выделению. На этот раз в контекстном меню в параметрах вставки выберите «Значения». У разных версий Эксель этот пункт может выглядеть по-разному. У меня он выглядит, как на прикрепленном скриншоте.
4. После этого все данные в ячейках превратятся из формул в значения. После этого. чтобы сработало суммирование, нужно их всех перекликать, применив последовательное нажатие F2 и Enter. Но я вас советую просто удалить формулу в общей ячейке и вписать её заново. Так будет гораздо быстрее. И не забывайте в ячейке вывода общей суммы установить формат «[ч]:мм». Иначе корректно считать не будет.

Но данный способ содержит один недостаток, о котором вам нужно знать. Вы уберете форму, а это значит, что при изменении данных в связанных ячейках, данные в ячейках, в которых содержится время автоматически изменятся не будут, так как связь фактически будет разорвана. Но если таблица статическая и никаких изменений в тех ячейках, откуда тянет данные функция «=СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))» не предвидится, то и никаких негативных последствий не будет. А вот если данные в ячейке H1764 и др. будут постоянно изменятся, то тогда этот вариант не подойдет. Но вы можете поступить по другому. Справа от столбца с датами добавить ещё один столбец, и скопировав содержимое с формулами, вставить его, как значения, не в ту же колонку, а в соседний только что созданный столбец. Правда, опять же, данные автоматически обновляться не будут в этом столбце, но вы всегда сможете отследить изменения в соседнем столбце и скопировать из него данные, как значения в тот столбец, где будет производиться суммирование.

Респект, все получилось! Большое спасибо!

Здравствуйте, а почему вы пишете при задании абсолютной ссылки нажать F7? Она мне ничего не выдает, а вот F4 делает ссылку абсолютной, может у вас опечатка?

Задайте вопрос или оставьте свое мнение Отменить комментарий

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

Относительная ссылка – ссылка, в которой координаты ячейки меняются при копировании формулы в другую ячейку.

Абсолютная ссылка – ссылка, в которой координаты ячейки зафиксированы и не меняется при копировании.

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

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

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

Вот что нам нужно сделать:

1. Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: = B2*C2 .
2. Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.
3. Остается выполнить аналогичные расчеты в других ячейках столбца. Конечно же, если таблица небольшая, можно перейти в следующую ячейку и выполнить шаги 1-2, описанные выше. Но что делать, когда данных слишком много? Ведь на ручной ввод формул во все ячейки уйдет немало времени. На этот случай в Excel предусмотрена крайне полезная функция, позволяющая скопировать формулу в другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом, и когда появится небольшой черный крестик (маркер заполнения), зажав левую кнопку мыши тянем его вниз, тем самым копируя формулу в другие ячейки.
4. Отпустив кнопку мыши мы получим результаты во всех ячейках столбца, на которые растянули формулу.
5. Если мы перейдем, например, в ячейку D3, то увидим в строке формул следующее выражение: =B3*C3 .Т.е. при копировании изменились координаты ячеек, участвующих в исходной формуле, которую мы записали в ячейку D2. Это результат того, что ссылки были относительными.

Возможные ошибки при работе с относительными ссылками

Безусловно, благодаря относительным ссылкам существенно упрощаются многие расчеты в Эксель. Однако, они не всегда помогают решить поставленную задачу.

Допустим, нам нужно посчитать долю каждого наименования в общих продажах.

1. Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
2. Нажимаем Enter, чтобы получить результат. После того, как мы скопируем формулу на оставшиеся ячейки столбца, вместо результатов увидим следующую ошибку: #ДЕЛ/0! .

Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в последующих ячейках сдвинулись. Т.е. для ячейки E3 формула выглядит следующим образом: =D3/D14 . Но, как мы видим, ячейка D14 – пустая, из-за чего программа и выдает ошибку, информирующую о том, что делить на цифру нельзя.

Следовательно, мы должны написать формулу таким образом, чтобы координаты ячейки с итоговой суммой (D13) оставались неизменными при копировании. В этом нам помогут абсолютные ссылки.

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

Как мы уже выяснили ранее, абсолютные ссылки позволяют зафиксировать координаты ячейки. Давайте посмотрим, как это работает на нашем примере.

По умолчанию, все ссылки в формулах Эксель относительные, поэтому, чтобы сделать их абсолютными, выполняем следующие действия:

1. Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: = D2/D13 .
2. Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13 .
3. Теперь можно нажать Enter, чтобы вывести результат в ячейку.
4. Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.

Смешанные ссылки

Помимо ссылок, рассмотренных выше, в Excel также предусмотрены смешанные ссылки – когда при копировании формулы меняется одна из координат ячейки (столбец или номер строки).

• Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
• Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.

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

Примечание: вместо ручного ввода символов “$” можно задать тип ссылок (абсолютные, относительные, смешанные) с помощью функциональной клавиши F4. При это курсор должен находится в пределах координат ячейки, в отношении которой мы хотим выполнить данное действие.

Заключение

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