Для чего в формулах ms excel используют абсолютные ссылки: Для чего в формулах ms 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 абсолютных или относительных ссылок результат окажется неверным.

Как в Excel использовать абсолютную ссылку

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

То есть нам нужно создать абсолютную ссылку. Формула для расчетов представлена в строке ввода. Создайте абсолютную ссылку, установив курсор перед А1. Теперь нажмите клавишу F4. Перед А и 1 теперь должен появиться значок доллара $, что означает, что данные этой ячейки останутся неизменными. Эту формулу можно будет копировать в другие ячейки.

Абсолютная ссылка в формуле Excel — это, как уже показывает название — неизменная часть формулы. Вы можете с ее помощью «зафиксировать» формулы в ячейке, чтобы не допустить их изменения. Как создать абсолютную ссылку в электронной таблице Excel, объясняется в этой статье.

Создайте формулу Excel с абсолютными ссылками

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

Сначала мы выбираем ячейку Excel, которая должна содержать нашу абсолютную ссылку. В нашем случае ячейка A1, в которую мы вводим номер 2. В ячейки от A3-A5 до C3-C5 введите любые цифры. Затем нажмите на ячейку D3, перейдите в панель формул и введите знак равенства.

Затем щелкните ячейку таблицы A3, поместите знак плюса после A3 в формулу, щелкните ячейку D3, снова добавьте знак плюса и щелкните ячейку C3. За C3 введите знак умножения, а затем нажмите ячейку с нашей абсолютной ссылкой —  ячейку A1.

Так ячейка MS Excel становится абсолютной ссылкой

Ячейка Excel с абсолютной ссылкой

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

Сначала переместите курсор перед A1 в строке редактирования. Затем нажмите клавишу [F4]. А и 1 теперь представляют собой набор знаков внутри долларов. Это означает, что значение этой ячейки Excel всегда принимается без изменений.

Знак доллара позволяет легко увидеть на первый взгляд, что вы имеете дело с абсолютной ссылкой. Наконец, если вы перетащите формулу в ячейки таблицы D4 и D5, вы заметите, что соответствующие ссылки на ячейки изменяются — кроме ячейки A1, которая остается постоянной.

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

Читайте также:

Фото: компании-производители

Для какой цели в формулах Excel используют абсолютные ссылки?

Для того чтобы при копировании формулы ссылка на ячейку не изменялась

Для того чтобы при перемещении формулы ссылка на ячейку изменялась

Для того чтобы в ячейке отображались только положительные значения

Для присвоения числам в ячейке положительных значений

Какой вид примет содержащая абсолютную и относительную ссылку формула, записанная в ячейке C1, после ее копирования в ячейку С2?

=$A$1*B2

=$A$1*B1

=$A$2*B1

=$A$2*B2

Какая формула будет указана в ячейке D5 при копировании в нее формулы из ячейки D2?

=В2*С2/$В$8

=В5*С5/В11

=В5*С5/В8

=В5*С5/$В$8

Какую формулу необходимо записать в ячейке D2, чтобы потом ее можно было копировать на ячейки D3:D5?

=В2*С2/В8

=(В2*С2)/В8

=В2*С2/$В$8

=$В$2*$С$2/$В$8

Как копировать содержимое ячейки А2 в ячейки А3, А4, А5?

Выделить ячейки А2, А3, А4, А5 и нажать клавишу клавиатуры Enter

Выделить ячейки А2, А3, А4, А5 и нажать комбинацию клавиш клавиатуры Ctrl Enter

Перетащить при нажатой левой кнопке мыши маркер в правом нижнем углу ячейки А2 до ячейки А5

Щелкнуть левой кнопкой мыши по ячейке А2, затем щелкнуть правой кнопкой мыши по ячейке А5 и нажать комбинацию клавиш клавиатуры Shift Enter

Что произойдет, если протянуть за маркер вниз левой кнопкой мыши?

Все выделенные при этом ячейки заполнятся значением первой

Просто выделится несколько ячеек

Вокруг ячеек нарисуется граница

Такого маркера вообще не бывает

Маркер автозаполнения (черный крестик) появится, если курсор поставить:

в верхний левый угол ячейки

в нижний правый угол ячейки

на серый прямоугольник на пересечении заголовков строк и столбцов

по середине ячейки

Указатель мыши в MS Excel имеет вид + при

перемещение данных из ячеек

заполнении ячеек по закономерности (автозаполнение)

обычном режиме выбора ячейки

копировании данных из ячеек

Дан фрагмент электронной таблицы.

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

АА

1

АС

АВ

3

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

Да, обновление диаграммы произойдет автоматически

Да, но в диаграмме потребуется указать на измененные данные

Да, но потребуется выполнить команду «Обновить таблицу»

Нет

Диаграмма в Excel строится (укажите 2 правильных ответа)

по номерам строк в таблице

по выделенному диапазону в таблице

с помощью мастера диаграмм за несколько шагов

нет правильного ответа

«Легенда» диаграммы MS Exсel – это

таблица для построения диаграммы

руководство для построения диаграмм

порядок построения диаграммы (список действия)

условные обозначения рядов или категорий данных

Ссылки в Excel | statanaliz.info

Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др. 

Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

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

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

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

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

=A1,

где буква обозначает столбец, а число – строку.

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

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

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

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

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

=$A$1

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

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

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

=A$1

=$A1

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

Ссылки на другие листы и книги

Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

Все внутренние формулы останутся неизменными, а формулы с внешними ссылками превратятся в значения. Убрать ссылки Excel можно и по-другому. Для этих же целей можно использовать более топорный метод. Нужно просто скопировать диапазон с формулами и тупо вставить его сверху в виде значений (через специальную вставку). Правда, в значения превратятся уже все формулы, а не только внешние.

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Всего доброго и до новых встреч.

Поделиться в социальных сетях:

1.3. Относительные, абсолютные и смешанные ссылки

3. Относительные, абсолютные и смешанные ссылки

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

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

Так, при копировании формул из активной ячейки С1,
содержащей относительные ссылки на ячейки А1 и В1, в ячейку D2 значение столбцов и строк в формуле изменяется на
один шаг вправо и вниз. При копировании формулы из ячейки С1 в ячейку Е3
значение столбцов и строк в формуле изменяется на два шага вправо и вниз и т.д.
(табл.3).

Таблица 3. Относительные
ссылки

Абсолютные ссылки. Абсолютные ссылки в формулах используются для указания фиксированных
адресов ячеек. При перемещении или копировании формулы абсолютные ссылки не
изменяются. В абсолютных ссылках перед неизменяемыми обозначениями столбца и
строки, составляющими адрес ячейки, ставится знак доллара (например, $A$1).

Так, при копировании
формулы из активной ячейки С1, содержащей абсолютные ссылки на ячейки $A$1 и $B$1, значение
столбцов и строк в формуле не изменяется (табл. 4).

Таблица 4. Абсолютные
ссылки

Смешанные ссылки. В формуле можно использовать смешанные ссылки, в которых координата
столбца относительная, а строки – абсолютная (например, A$1), или , наоборот, координата столбца абсолютная, а
строки – относительная (например, $B1)
(табл. 5).

Таблица 5. Смешанные
ссылки

Абсолютные ссылки в Excel — Ссылочное и статейное продвижение

Абсолютная адресация в Microsoft Excel

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

Весьма важным моментом при работе в программной среде MS Excel является понятие адресации ячеек, так как от него, во многом, зависит результат выполнения формул. Для адресации ячеек электронных таблиц используют так называемые ссылки. Ссылки на ячейки могут быть трех типов: относительные (например, А1), абсолютные ($A$1) или смешанные (A$1 или $A1).

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

При использовании абсолютной адресации ссылка на ячейку или область ячеек всегда будет постоянной. То есть при любых вычислениях будет использоваться один и тот же адрес столбца и строки. Обозначается такая ссылка при помощи знака доллара — $. Например, $A$1 означает, что данный адрес при копировании формулы будет неизменным как по строкам, так и по столбцам. Хорошим примером использования данного типа адресации является задача о подсчете долей от какого-то числа. Когда формула осуществляет вычисления долей от общей суммы, ссылка на ту ячейку, которая содержит общую сумму (число), не должна изменяться при тиражировании или копировании самой формулы, то есть быть абсолютной по своему значению.

Абсолютную ссылку весьма удобно ставить в электронных таблицах с помощью клавиши «F4». Для этого нужно выделить адрес нужной ячейки в формуле и нажать «F4». Эта клавиша не является просто кнопкой для создания абсолютной адресации, а универсальным инструментом в табличном редакторе Microsoft Excel для преобразования ссылок из одного типа в другой. Так как по умолчанию используется относительная адресация ячеек, то использование клавиши F4 позволит перейти к абсолютным величинам.

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

какая ссылка называется абсолютной

что такое ссылка в одноклассниках

что такое ссылка в одноклассниках

Переключение между относительными, абсолютными и смешанными ссылками

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

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

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

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

1. Выберите ячейку, содержащую формулу.

2. В строке формул

   , выберите ссылку, которую вы хотите изменить.

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

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

Абсолютный справочник Excel — Руководство для начинающих

Что такое абсолютная ссылка?

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

Загрузите бесплатный файл практики

Используйте этот бесплатный файл Excel, чтобы практиковать абсолютные ссылки вместе с учебником.

Например, на изображении ниже все сотрудники получат одинаковую сумму бонусных выплат в размере 1500 долларов США. Сумма в 1500 долларов является постоянной в этой ситуации, и абсолютная ссылка может использоваться, чтобы помочь рассчитать общую выплату заработной платы (цифра, которая различается для каждого человека) плюс бонусные выплаты.

В Excel все ссылки по умолчанию являются относительными. Чтобы применить абсолютную ссылку в Excel, в формулу Excel необходимо добавить знак доллара ($), т. Е. = $ C $ 1, как показано в примере выше.

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

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

Краткое резюме

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

Вот шаги по созданию базовой абсолютной ссылки в Excel — $ A $ 1:

1. Выберите ячейку, в которой вы хотите создать абсолютную ссылку. Ячейка A1 в этом примере:

2. В формуле ячейки A1 введите «=» (знак равенства), а затем выберите точку отсчета — ячейку C1.

3. В той же формуле либо вручную добавьте два знака доллара (SHIFT + 4) перед координатами строки и столбца, либо нажмите клавишу F4 в качестве ярлыка.
4. Нажмите клавишу ВВОД, и теперь ячейка A1 имеет значение ячейки C1 в качестве фиксированной точки отсчета.

Примеры абсолютной ссылки

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

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

Давайте посмотрим на пример ниже:

Американская компания по производству канцелярских товаров нашла нового покупателя из Германии. Им нужно разработать предложение для нового партнера. Учитывая, что существуют различия в валютах, они хотели бы включить стоимость единицы в евро, чтобы получить полную картину затрат.Это означает, что все эти три единицы стоимости в долларах США необходимо умножить на обменный курс евро-доллар — стоимости единицы (долл. США) * обменный курс евро-доллар .

Есть три единицы стоимости и один обменный курс. Обменный курс евро (ячейка B3) должен оставаться постоянным при применении формул в столбце F для удельных затрат в евро.

Для начала узнаем стоимость единицы блокнота A в евро:

1. Введите: «= E2 * B3»

2. Вручную вставьте $ перед координатами строки и столбца или нажмите F4, чтобы создать абсолютную ссылку, затем нажмите ENTER.

3. Чтобы узнать другие удельные затраты в евро, перетащите вниз ячейку F2, чтобы применить формулы к F3 и F4.

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

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

Если бы в формулах не было абсолютной ссылки, Excel вернет 0 долларов в качестве стоимости единицы, когда мы перетаскиваем ячейку F2 вниз.Поскольку контрольная точка станет относительной и изменится в разных ячейках, а других значений в ячейках B4 и B5 нет.

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

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

Абсолютная ссылка Excel

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

Относительная ссылка Excel

Это относится к относительной точке отсчета, постоянно меняется, и в формуле отсутствует знак доллара $ (то есть, когда каждые цены за единицу и qty являются разностными переменными, в расчетах нет константы).

Подсказка

Нажмите клавишу F4 в качестве ярлыка для создания абсолютной ссылки на ячейку в Excel.

Сводка

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

Знак доллара $ используется для создания абсолютной ссылки. Без него это относительная ссылка, ни одна ячейка не «заблокирована».

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

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

Учите Excel бесплатно

Начните изучать формулы, функции и полезные советы прямо сегодня с этого бесплатного курса!

Начать бесплатный курс

Агнес Ло

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

Использование Excel для бюджета и расчетов

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

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

Что такое ссылки на ячейки в Excel?

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

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

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

Если вы не хотите рыскать по панели задач, чтобы найти пункт меню, есть простой ярлык, чтобы быстро и легко вставить его. На ПК просто нажмите F4 после ввода ссылки, чтобы переключиться с относительной на абсолютную.Если вы продолжите нажимать F4, программа будет циклически перебирать различные формы справочника, чтобы вы могли выбрать наиболее подходящий для своих целей. Чтобы получить ярлык абсолютной ссылки на ячейку на Mac, нажмите клавиши Command и T.

Использование абсолютной ссылки на ячейку

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

• Включите фиксированную стоимость единицы продукта в расчет бюджета.
• Используйте один и тот же рисунок или ссылку несколько раз в таблице Excel для разработки прогнозов роста.
• Часто используйте одни и те же значения для счетов-фактур или заказов на поставку.
• Абсолютная ссылка на ячейку также может пригодиться при работе с данными управления проектами.

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

Что такое абсолютная ссылка на ячейку?

Обновлено: 29 декабря 2017 г., компания Computer Hope

В Microsoft Excel и других программах для работы с электронными таблицами абсолютная ссылка на ячейку или ACR — это ячейка электронной таблицы, которая остается неизменной даже после копирования или перемещения.

Обзор абсолютной ссылки ячейки

Абсолютную ссылку на ячейку можно определить по формуле ячейки. Если формула содержит один или несколько знаков доллара ($), это абсолютная ссылка на ячейку или частичная абсолютная ссылка на ячейку. В приведенном ниже примере в строке формул отображается «= СУММ (D2: D5)», которое является относительной ссылкой на ячейку, а , а не — абсолютной ссылкой на ячейку.

Примеры абсолютной ссылки на ячейку

Абсолютный столбец и абсолютная ссылка на строку

 = 2 

австралийских долларов

Приведенная выше формула сообщает программе электронных таблиц, что нужно показать значение ячейки A1 в текущей ячейке.Например, в приведенной выше таблице Excel, если мы ввели эту формулу в ячейку B8, она всегда будет «100», поскольку это значение в ячейке A2.

Абсолютная ссылка на ячейку отличается от относительной (например, = A2), если мы скопировали формулу ячейки в B8 и поместили ее в B9, она останется «100». В относительной ссылке на ячейку будет использоваться следующая относительная ячейка для своего значения, которое будет A3 и будет иметь значение «101» в ячейке.

Относительный столбец и абсолютная ссылка на строку

 = 2 

австралийских долларов

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

Абсолютный столбец с относительной ссылкой на строку

 = 

долларов США

Вышеупомянутая формула является абсолютной ссылкой на столбец с относительной ссылкой на строку. Если формула ячейки была скопирована, столбец останется прежним (A), но строка находится относительно того места, куда она копируется.

Абсолютная ссылка на ячейку с относительной ссылкой на ячейку

 = СУММ ($ 2 + B2) 

Наконец, в приведенной выше формуле всегда используется значение из A2, но это значение добавляется к следующей относительной ячейке.Если бы формула ячейки была скопирована из C2 в C3, она стала бы «= СУММ ($ A $ 2 + B3)».

Подсказка

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

Зачем кому-то нужно использовать абсолютную ссылку на ячейку?

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

Абсолютное местоположение, Активная ячейка, Формула, Относительная ссылка на ячейку, Электронная таблица, Условия электронной таблицы

зачем использовать $ в формуле Excel

При написании формулы Excel $ в ссылках на ячейки сбивает с толку многих пользователей.Но объяснение очень простое. Знак доллара в ссылке на ячейку Excel служит только одной цели — он сообщает Excel, следует ли изменять ссылку или нет, когда формула копируется в другие ячейки. И это короткое руководство предоставляет полную информацию об этой замечательной функции.

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

Все вы, наверное, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя разными способами, например A1, $ A $ 1, $ A1 и A $ 1.

Знак доллара в ссылке на ячейку Excel влияет только на одно — он указывает Excel, как обрабатывать ссылку, когда формула перемещается или копируется в другие ячейки. Вкратце, использование знака $ перед координатами строки и столбца создает абсолютную ссылку на ячейку, которая не изменится.Без знака $ ссылка будет относительной и изменится.

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

Что такое ссылка на ячейку Excel?

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

Например, если вы введете простую формулу = A1 в ячейку C1, Excel извлечет значение из ячейки A1 в ячейку C1:

Как уже упоминалось, пока вы пишете формулу для одной ячейки , вы можете использовать любой ссылочный тип со знаком доллара ($) или без него, результат будет таким же:

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

Примечание. Помимо ссылочного стиля A1 , где столбцы определяются буквами, а строки — номерами, существует также ссылочный стиль R1C1 , в котором и строки, и столбцы идентифицируются номерами (R1C1 обозначает строку 1, столбец 1).

Поскольку A1 является эталонным стилем по умолчанию в Excel и используется большую часть времени, в этом руководстве мы обсудим только ссылки типа A1.Если кто-то в настоящее время использует стиль R1C1, вы можете отключить его, щелкнув Файл > Параметры > Формулы , а затем сняв флажок R1C1 reference style .

Относительная ссылка на ячейку в Excel (без знака $)

Относительная ссылка в Excel — это адрес ячейки без знака $ в координатах строки и столбца, например A1 .

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

Предположим, у вас есть следующая формула в ячейке B1:

= A1 * 10

Если вы скопируете эту формулу в другую строку в том же столбце, скажем, в ячейку B2, формула будет скорректирована для строки 2 (A2 * 10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца A на 10.

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

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

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

Использование относительной ссылки — Excel — пример формулы

Предположим, у вас есть столбец цен в долларах США (столбец B) на вашем листе, и вы хотите преобразовать их в евро. Зная обменный курс доллара США к евро (0,93 на момент написания), формула для строки 2 так же проста, как = B2 * 0,93 . Обратите внимание, что мы используем относительную ссылку на ячейку Excel без знака доллара.

При нажатии клавиши Enter вычисляется формула, и результат немедленно отображается в ячейке.

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

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

Вот и все! Формула копируется в другие ячейки с относительными ссылками, которые правильно настроены для каждой отдельной ячейки. Чтобы убедиться, что значение в каждой ячейке вычисляется правильно, выберите любую из ячеек и просмотрите формулу в строке формул. В этом примере я выбрал ячейку C4 и вижу, что ссылка на ячейку в формуле относится к строке 4, как и должно быть:

Абсолютная ссылка на ячейку Excel (со знаком $)

Абсолютная ссылка в Excel — это адрес ячейки со знаком доллара ($) в координатах строки или столбца, например $ A $ 1 .

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

Например, если у вас есть 10 в ячейке A1 и вы используете абсолютную ссылку на ячейку ( $ A $ 1 ), формула = $ A $ 1 + 5 всегда будет возвращать 15, независимо от того, в каких других ячейках эта формула копируется в.С другой стороны, если вы напишете ту же формулу с относительной ссылкой на ячейку ( A1 ), а затем скопируете ее в другие ячейки столбца, для каждой строки будет вычислено другое значение. Следующее изображение демонстрирует разницу:

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

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

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

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

Пример 1. Относительные и абсолютные ссылки на ячейки для вычисления чисел

В нашем предыдущем примере с ценами в долларах США и евро вы можете не захотеть жестко указывать обменный курс в формуле. Вместо этого вы можете ввести это число в какую-либо ячейку, например C1, и исправить ссылку на эту ячейку в формуле, используя знак доллара ($), как показано на следующем снимке экрана:

В этой формуле (B4 * $ C $ 1) существует два типа ссылок на ячейки:

• B4 — относительная ссылка на ячейку , которая настраивается для каждой строки, и
• $ C $ 1 — абсолютная ссылка на ячейку , которая никогда не изменяется независимо от того, куда копируется формула.

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

Пример 2. Относительные и абсолютные ссылки на ячейки для вычисления дат

Еще одно распространенное использование абсолютных и относительных ссылок на ячейки в одной формуле — это вычисление дат в Excel на основе сегодняшней даты.

Предположим, у вас есть список дат доставки в столбце B, и вы вводите текущую дату в C1 с помощью функции СЕГОДНЯ ().Вам нужно знать, сколько дней отправляется каждый предмет, и вы можете рассчитать это, используя следующую формулу: = B4- $ C $ 1

И снова мы используем в формуле два ссылочных типа:

• Относительный для ячейки с первой датой доставки (B4), поскольку вы хотите, чтобы эта ссылка на ячейку изменялась в зависимости от строки, в которой находится формула.
• Абсолютный для ячейки с сегодняшней датой ($ C $ 1), потому что вы хотите, чтобы эта ссылка на ячейку оставалась постоянной.

В заключение: всякий раз, когда вы хотите создать статическую ссылку на ячейку Excel, которая всегда ссылается на одну и ту же ячейку, обязательно включите в формулу знак доллара ($), чтобы создать абсолютную ссылку в Excel.

Ссылка на смешанные ячейки Excel

Смешанная ссылка на ячейку в Excel — это ссылка, в которой фиксирована буква столбца или номер строки. Например, $ A1 и A $ 1 — смешанные ссылки. Но что означает каждое из них? Все очень просто.

Как вы помните, абсолютная ссылка Excel содержит 2 знака доллара ($), которые блокируют как столбец, так и строку.В смешанной ссылке на ячейку только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от относительного положения строки или столбца:

• Абсолютный столбец и относительная строка , например $ A1. Когда формула с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы она никогда не изменялась. Относительная ссылка на строку без знака доллара зависит от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка , например A $ 1. В этом типе ссылки ссылка на строку не изменится, а ссылка на столбец изменится.

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

Использование смешанной ссылки в Excel — пример формулы

В этом примере мы снова будем использовать нашу таблицу конвертации валют. Но на этот раз мы не будем ограничиваться только конвертацией доллара в евро.Что мы собираемся сделать, так это преобразовать долларовые цены в ряд других валют, используя единую формулу !

Для начала введем коэффициенты конверсии в какую-нибудь строку, скажем, строку 2, как показано на скриншоте ниже. А затем вы пишете только одну формулу для верхней левой ячейки (C5 в этом примере), чтобы рассчитать цену в евро:

= 5 бат * 2

канадских долларов

Где B5 — это цена в долларах в той же строке, а 2 C $ — это обменный курс доллара США к евро.

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

Например, выделим ячейку D7 (в столбце GBP). Здесь мы видим формулу = $ B7 * D $ 2 , которая берет цену в долларах США в B7 и умножает ее на значение в D2, которое представляет собой обменный курс доллар-фунт стерлингов, именно то, что доктор прописал 🙂

А теперь давайте разберемся, как получается, что Excel точно знает, какую цену брать и на какой обменный курс ее умножать.Как вы уже догадались, трюк делают смешанные ссылки на ячейки ($ B5 * C $ 2).

• $ B5 — абсолютный столбец и относительная строка . Здесь вы добавляете знак доллара ($) только перед буквой столбца, чтобы привязать ссылку к столбцу A, поэтому Excel всегда использует исходные цены в долларах США для всех преобразований. Ссылка на строку (без знака $) не заблокирована, потому что вы хотите рассчитать цены для каждой строки отдельно.
• C $ 2 — относительный столбец и абсолютная строка .Поскольку все обменные курсы находятся в строке 2, вы блокируете ссылку на строку, помещая знак доллара ($) перед номером строки. И теперь, независимо от того, в какую строку вы копируете формулу, Excel всегда будет искать обменный курс в строке 2. И поскольку ссылка на столбец является относительной (без знака $), она будет скорректирована для столбца, для которого формула скопировал.

Как создать ссылку на весь столбец или строку в Excel

Когда вы работаете с листом Excel, который имеет переменное количество строк, вы можете обратиться ко всем ячейкам в определенном столбце.Чтобы сослаться на весь столбец, просто введите букву столбца дважды и двоеточие между ними, например A: A .

Ссылка на весь столбец

Как и ссылки на ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

• Абсолютная ссылка на столбец , , например, $ A: $ A
• Относительная ссылка на столбец, например A: A

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

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

Ссылка на всю строку

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

• Абсолютная ссылка на строку , как $ 1: $ 1
• Относительная ссылка на строку, например 1: 1

Теоретически вы также можете создать смешанную ссылку на весь столбец или смешанную целую ссылку — строку, как $ A: A или $ 1: 1, соответственно.Я говорю «теоретически», потому что не могу придумать никакого практического применения таких ссылок, хотя пример 4 доказывает, что формулы с такими ссылками работают именно так, как должны.

Пример 1. Ссылка на весь столбец Excel (абсолютная и относительная)

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

.

= СУММ ($ B: $ B) — используйте знак доллара ($), чтобы сделать абсолютной ссылкой на весь столбец , которая блокирует формулу для столбца B.

= СУММ (B: B) — напишите формулу без $, чтобы сделать относительной ссылкой на весь столбец , которая будет изменяться при копировании формулы в другие столбцы.

Наконечник. При написании формулы щелкните букву столбца, чтобы добавить в формулу ссылку на весь столбец.Как и в случае со ссылками на ячейки, Excel по умолчанию вставляет относительную ссылку (без знака $):

Таким же образом мы пишем формулу для расчета средней цены во всем столбце B:

= СРЕДНИЙ (B: B)

В этом примере мы используем относительную ссылку на весь столбец, поэтому наша формула корректируется правильно, когда мы копируем ее в другие столбцы:

Примечание. При использовании ссылки на весь столбец в формулах Excel никогда не вводите формулу в любом месте одного столбца.Например, может показаться хорошей идеей ввести формулу = СУММ (B: B) в одну из пустых самых нижних ячеек в столбце B, чтобы получить результат в конце того же столбца. Не делай этого! Это создаст так называемую круговую ссылку , и формула вернет 0.

Пример 2. Ссылка на всю строку в Excel (абсолютная и относительная)

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

= СРЕДНЕЕ (2 доллара США: 2 доллара США) — абсолютная ссылка целая строка привязана к определенной строке с помощью знака доллара ($).

= СРЕДНЕЕ (2: 2) — относительная ссылка на всю строку изменится при копировании формулы в другие строки.

В этом примере нам нужна относительная ссылка на всю строку, потому что у нас есть 3 строки данных, и мы хотим вычислить среднее значение в каждой строке, скопировав ту же формулу:

Пример 3.Как сослаться на весь столбец, исключая первые несколько строк

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа B5: B, которые включали бы все строки в столбце B, начиная со строки 5. Если вы попытаетесь добавить такую ​​ссылку, ваша формула, скорее всего, вернет ошибку #NAME.

Вместо этого вы можете указать максимальное количество строк , чтобы ваша ссылка включала все возможные строки в данном столбце.В Excel 2016, 2013, 2010 и 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии Excel имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти среднее значение для каждого столбца цен в приведенной ниже таблице (столбцы с B по D), вы вводите следующую формулу в ячейку F2, а затем копируете ее в ячейки G2 и h3:

= СРЕДНИЙ (B5: B1048576)

Если вы используете функцию СУММ, вы также можете вычесть строки, которые хотите исключить:

= СУММ (B: B) -SUM (B1: B4)

Пример 4.Использование смешанной ссылки на весь столбец в Excel

Как я упоминал несколькими абзацами ранее, вы также можете сделать смешанную ссылку на весь столбец или целую строку в Excel:

• Смешанная ссылка на столбец, например $ A: A
• Смешанная ссылка на строку, например $ 1: 1

Теперь посмотрим, что произойдет, если вы скопируете формулу с такими ссылками в другие ячейки. Предположим, вы ввели формулу = СУММ ($ B: B) в некоторую ячейку, в этом примере — F2. Когда вы копируете формулу в соседнюю правую ячейку (G2), она изменяется на = SUM ($ B: C) , потому что первая B фиксируется знаком $, а вторая — нет.В результате формула сложит все числа в столбцах B и C. Не уверен, имеет ли это какое-либо практическое значение, но вы можете узнать, как это работает:

Предупреждение! Не используйте слишком много ссылок на целые столбцы / строки на листе, потому что они могут замедлить работу Excel.

Как переключаться между абсолютными, относительными и смешанными ссылками (клавиша F4)

Когда вы пишете формулу Excel, знак $ можно, конечно, ввести вручную, чтобы изменить относительную ссылку на ячейку на абсолютную или смешанную.Или вы можете нажать клавишу F4, чтобы ускорить процесс. Чтобы ярлык F4 работал, вы должны находиться в режиме редактирования формулы:

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

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

Примечание. Если вы нажмете F4, не выбрав ссылку на ячейку, ссылка слева от указателя мыши будет выбрана автоматически и заменена на другой тип ссылки.

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

Вас также может заинтересовать

(Архивы) Microsoft Excel 2007: Ссылки на ячейки: относительные и абсолютные

Эта статья основана на устаревшем программном обеспечении.

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

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

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

ПРИМЕР: В показанном здесь примере функция в D2 вычисляет заработную плату Боба путем умножения его часов (C2) на его ставку оплаты (B2). Поскольку размер оплаты зависит от студента, мы можем использовать команду «Заполнить», чтобы скопировать функцию в другие строки электронной таблицы.Поскольку ссылки на ячейки являются относительными, функция будет настраиваться для вычисления значений в соответствующих строках. Другими словами, когда мы копируем функцию в итоговую сумму Анны, ее зарплата будет основываться на C3 * B3.

Абсолютные ссылки на ячейки

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

ПРИМЕР: В показанном здесь примере функция в C11 вычисляет заработную плату Боба путем умножения его часов (B11) на размер оплаты для всех студентов (C9).Обратите внимание на $ перед идентификаторами столбца и строки ($ C $ 9). Сделав ссылку на ставку оплаты абсолютной ссылкой, мы можем использовать команду «Заполнить», чтобы скопировать функцию в другие строки электронной таблицы. Такая же ставка оплаты будет умножена на количество часов, соответствующее каждому учащемуся. Другими словами, когда мы копируем функцию в общую сумму Анны, ее зарплата будет основываться на B12 * C $ 9.

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

Формула, диапазон ячеек или ссылка на ячейку могут иметь как относительные, так и абсолютные компоненты. Если добавить знак доллара ($) перед столбцом или строкой или перед обоими, эта ссылка станет абсолютной. При добавлении знаков доллара к ссылкам на ячейки только часть ссылки, следующая сразу за знаком доллара, является абсолютной. Чтобы ссылка на всю ячейку оставалась постоянной, поместите знак доллара перед столбцом и строкой.
ПРИМЕР: 12 австралийских долларов

Чтобы сделать ссылку на ячейку абсолютной:

1. В формуле перед частью ссылки (т.д., ссылка на строку или столбец), которую вы хотите сделать абсолютной, введите $.
   ИЛИ
   В штанге Formula ,
   1. Щелкните ссылку на ячейку, которую нужно изменить.
   2. Windows: нажмите [F4].
      Macintosh: нажмите [Cmd] + [T].
      Часть вашей ссылки на ячейку изменена.
      СОВЕТ: нажатие [F4] или [Cmd] + [T] приведет к переключению элементов (т. Е. Ссылок на строки и столбцы) ссылки на ячейку между относительным и абсолютным.Если вы не получили нужную ссылку в первый раз, продолжайте нажимать эту кнопку, пока желаемые части вашей ссылки не станут относительными и абсолютными.
      ПРИМЕР. При нажатии [F4] или [Cmd] + [T] ссылка на ячейку может циклически изменяться в следующей последовательности: A12, $ A $ 12, A $ 12, $ A12, A12.

Относительные, абсолютные и смешанные ссылки на ячейки в Excel — Учебники по Excel

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

Excel автоматически обновит каждую ссылку на листе, позволяя формулам динамически обновлять свое содержимое каждый раз, когда вы что-то меняете или обновляете (клавиша F9 ) рабочий лист.

В Excel можно использовать три типа ссылок на ячейки:

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

Первый тип ссылки — это относительная ссылка.Это наиболее распространенный тип справочников в Excel.

Записать можно так:

= B3

Это означает, что ячейка относится к значению внутри ячейки B3.

Пример 1

Давайте рассмотрим несколько примеров, чтобы проиллюстрировать, как работает этот справочник.

В столбце A есть целые и текстовые значения. В столбце B есть ссылки на эти ячейки. В этом примере это не видно, но когда вы используете сочетание клавиш для отображения формул — Ctrl + ` (клавиша, расположенная под клавишей ESC), вы можете видеть, что они действительно являются ссылками, а не значениями.

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

Если вы измените значения в ячейке A2 или A3 , Excel автоматически обновит ячейки B2 и B3 .

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

Пример 2

В этом примере ячейка A1 содержит число 2 , а ячейка B1 содержит формулу: = A1 + 2. Значение, на которое ссылается ячейка B1 , равно 2.

2 + 2 = 4

Это значение, которое вы видите в ячейке B1 .

Если вы измените значение в ячейке A1 на 5 , тогда значение в ячейке B1 автоматически изменится на 7 , потому что 5 + 2 = 7 .

Пример 3

Ячейка B1 относится к ячейке A1 , затем после копирования ячейки B1 в ячейку D2, ячейка начинает ссылаться на ячейку C2. Другими словами, ссылка на ячейку была перемещена на то же расстояние, что и скопированная ячейка.

Пример 4

Посмотрите на следующий пример. Здесь вы можете найти имена сотрудников компании.

Если вы хотите объединить имя с фамилией и поместить их в столбец D , вам не нужно вводить их вручную — вы можете объединить их, используя относительные ссылки.

В этом случае введите формулу = B2 & ”“ & C2 в ячейку D2 .Он объединит ячейку B2, пространство и ячейку C2. Теперь вы можете использовать Автозаполнение для заполнения оставшихся ячеек.

Вы можете отображать формулы вместо значений с помощью сочетания клавиш Ctrl + `.

Как видите, только формула в ячейке D2 относится к ячейкам B2, и C2. Ссылки в следующих ячейках были соответственно смещены.

Абсолютные ссылки на ячейки

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

В абсолютных ссылках используются два знака доллара ($): один перед буквой столбца и один перед номером строки.

= 4 млрд долларов

Пример 5

Я объясню это на простом примере.

Ячейка B1 относится к содержимому ячейки A1 ( = $ A $ 1 ). После того, как вы скопируете ячейку B1 в B3 , она все равно будет ссылаться на A1 вместо A3 , как это было с относительной ссылкой.

Пример 6

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

Посмотрите на строку формул. Он показывает, сколько налогов Джону нужно заплатить за январь ( = C3 * D7 ). Если вы хотите автоматически заполнить оставшиеся месяцы, вы заметите, что для февраля ссылка не указывает на ячейку D7 , вместо этого она указывает на ячейку D8 , а для марта — на D9.

Чтобы создать абсолютную ссылку , щелкните ячейку D3, , затем в строке формул щелкните текст D7. Теперь нажмите клавишу F4 и подтвердите ввод формулы, нажав Enter. Это изменит относительную ссылку на абсолютную ссылку .

Используйте AutoFill для расчета налогов за февраль и март и суммирования всех месяцев. Нажмите Ctrl + ` для отображения формул.

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

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

Ссылки на смешанные ячейки

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

Заблокировано в ряд:

= 1 австралийских долларов

Заблокировано для столбца:

= A1

долларов США

Если вы хотите создать смешанную ссылку, привязанную к строке — дважды нажмите клавишу F4 на строке формул:

Для смешанного задания, зафиксированного в столбце, трижды нажмите клавишу F4 .

Пример 7

В следующем примере показано практическое применение обоих типов смешанных ссылок на ячейки.

В этой таблице находится список товаров и их цены. Для каждого продукта предусмотрены три разные скидки.

Введите следующую формулу в ячейку C3 .

  = $ B3 - ($ B3 * C $ 2)  

Формула заблокирована для столбца B ( $ B3 ) и строки 2 ( C $ 2 ).

Если вы попытаетесь автоматически заполнить ячейку ниже (C4), Excel возьмет следующую строку для цены ( $ B4 ), но не изменит строку для ( C $ 2 ), поэтому всегда будет 10% скидка на эту колонку.

  = B4 - (B4 * C $ 2)  

Теперь давайте посмотрим, как это работает для ячейки справа (D3). Поскольку столбец B заблокирован ( $ B3 ), он не изменится.

Ячейка C $ 2 изменится на D $ 2 , потому что нет блокировки для столбца, только для строки.