✅ Ссылка на ячейку в Excel (примеры) - Типы - Относительный, Абсолютный и Смешанный

Ссылка на ячейку в Excel

Ссылка на ячейку Excel (Оглавление)

Ссылка на ячейку в Excel
Типы ссылок на ячейки в Excel
- # 1 - Относительная ссылка на ячейку в Excel
- # 2- Абсолютная ссылка на ячейку в Excel
- # 3- Ссылка на смешанную ячейку в Excel

Ссылка на ячейку в Excel

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

В Excel электронные таблицы состоят из столбцов и строк, которые составляют «ячейки». Каждая ячейка соответствует точному столбцу и точной строке. Чтобы объяснить это на примере, ячейка B2 ссылается на второй столбец (B) и второй ряд (2). Соответственно, D16 указывает на четвертый столбец (D) и шестнадцатый ряд (16). Фактическим преимуществом Excel является удобство использования ссылок на ячейки в других ячейках при создании формул.

Типы ссылок на ячейки в Excel

У нас есть три разных типа ссылок на ячейки в Excel -

Относительная ссылка на ячейку в Excel
Абсолютная ссылка на ячейку в Excel
Справочник по смешанным ячейкам в Excel

Использование правильного типа Cell Reference в Excel в конкретном сценарии сэкономит много времени и усилий и значительно упростит работу.

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

Относительные ссылки на ячейки в Excel относятся к ячейке или диапазону ячеек в Excel. Каждый раз, когда значение вводится в формулу, например, SUMIFS, в Excel можно ввести «ссылку на ячейку» вместо жестко запрограммированного числа. Ссылка на ячейку может прийти в форме B2, где B соответствует букве столбца рассматриваемой ячейки, а 2 представляет номер строки. Всякий раз, когда Excel встречает ссылку на ячейку, он посещает определенную ячейку, извлекает ее значение и использует это значение в любой формуле, которую вы пишете. Когда эта ссылка на ячейку в Excel дублируется в другом месте, относительные ссылки на ячейки в Excel соответственно также автоматически изменяются.

Когда мы ссылаемся на такие ячейки, мы можем достичь этого с помощью любого из двух типов ссылок на ячейки в excel: абсолютных и относительных. Разграничение между этими двумя различными типами ссылок - это различное внутреннее поведение, когда вы перетаскиваете или копируете их и вставляете в разные ячейки. Относительные ссылки на ячейки могут изменяться и корректироваться по мере их копирования и вставки; Абсолютных ссылок наоборот нет. Для успешного достижения результатов в Excel важно правильно использовать относительные и абсолютные ссылки на ячейки.

Как эффективно использовать относительную ссылку на ячейку в Excel?

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

Вы можете скачать этот шаблон ссылки на ячейку Excel здесь - шаблон ссылки на ячейку Excel

Пример № 1

Давайте рассмотрим простой пример для объяснения механизма относительной ссылки на ячейку в Excel. Если мы хотим получить сумму двух чисел в двух разных ячейках - A1 и A2, а получить результат в третьей ячейке A3.

Таким образом, мы применяем формулу = A1 + A2

Что дало бы результат как 100 в A3.

Теперь предположим, что у нас есть аналогичный сценарий в следующем столбце (B). Ячейки B1 и B2 имеют два числа, и мы хотим иметь сумму в B3.

Мы можем достичь этого двумя разными способами:

Здесь мы физически пишем формулу, чтобы добавить две ячейки B1 и B2 в B3.

Результат как 30.

Или мы могли бы просто скопировать формулу из ячейки A3 и вставить в ячейку B3 (это сработало бы, если бы мы также перетащили формулу из A3 в B3).

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

И после этого мы переходим к следующей ячейке B3, щелкаем правой кнопкой мыши и выбираем «Формулы (f)».

Это означает, что ячейка A3 = A1 + A2. Когда мы копируем A3 и перемещаем одну ячейку вправо и вставляем ее в ячейку B3, формула автоматически адаптируется и изменяется на B3 = B1 + B2. Вместо этого применяется формула суммирования для ячеек B1 и B2.

Пример № 2

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

Цена продажи = Цена за единицу товара x Проданные единицы

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

Когда мы применяем формулу, мы получаем следующий результат для продукта 1:

Он успешно умножил цену за единицу на проданные единицы для продукта 1, т.е. ячейка G2 *, ячейка H2, т.е. 1826, 00 * 20, что дает нам результат 36520, 00.

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

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

Здесь мы нажимаем Ctrl + D. Таким образом, результат будет выглядеть следующим образом:

# 2- Абсолютная ссылка на ячейку в Excel

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

Давайте посмотрим на следующее:

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

= $ A $ 1 - это абсолютная ссылка на ячейку, столбец и строка заблокированы и не изменяются при копировании ячейки формулы. Таким образом, значение ячейки остается постоянным.

В = $ A1 столбец заблокирован, и строка может продолжать изменяться для этого конкретного столбца.

В = A $ 1 строка заблокирована, и столбец может продолжать изменяться для этой конкретной строки.

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

Используя знак доллара в формуле, если ссылка на ячейку делает ее абсолютной ссылкой на ячейку, поскольку знак доллара блокирует ячейку. Мы можем заблокировать строку или столбец, используя знак доллара. Если «$» перед алфавитом, то он блокирует столбец, а если «$» перед числом, то строка блокируется.

# 3- Ссылка на смешанную ячейку в Excel

Как эффективно использовать Абсолютную ссылку на ячейку в Excel и как использовать Смешанную ссылку на ячейку в Excel?

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

У нас есть данные о продажах для 4 менеджеров по продажам за разные месяцы, где продажи происходили несколько раз в месяц.

Наша цель - рассчитать сводную сводную информацию о продажах всех 4 менеджеров по продажам. Мы применим формулу SUMIFS, чтобы получить желаемый результат.

Результат будет как:

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

В «sum_range» мы имеем $ C $ 2: $ C $ 17. Перед буквами и цифрами стоит знак доллара. Таким образом, строки и столбцы для диапазона ячеек заблокированы. Это абсолютная ссылка на ячейку.
Далее у нас есть «attribute_range1». Здесь также у нас есть абсолютная ссылка на ячейку.
После этого у нас есть «критерий 1» - $ F2. Здесь мы видим, что при копировании ячейки формулы будет заблокирован только столбец, то есть только строка изменится, когда мы скопируем формулу в другую ячейку (перемещаясь вниз). Это ссылка на смешанную ячейку.
Затем у нас есть «attribute_range2», который также является абсолютной ссылкой на ячейку.
Последний сегмент формулы - «критерий2» - G $ 1. Здесь мы видим, что знак доллара присутствует перед числом, а не алфавитом. Таким образом, при копировании ячейки формулы блокируется только строка. Столбец может измениться, когда мы копируем ячейку формулы в другую ячейку (перемещение вправо). Это ссылка на смешанную ячейку.

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

Ссылка на смешанную ячейку относится только к определенной строке или столбцу, например, = $ A2 или = A $ 2 . Если мы хотим создать смешанную ссылку на ячейку, мы можем нажать клавишу F4 в формуле два-три раза, в соответствии с вашим требованием, то есть для ссылки на строку или столбец. При повторном нажатии F4 ссылка на ячейку изменится на относительную.

То, что нужно запомнить

При копировании формулы Excel относительные ссылки обычно являются желаемыми. Это причина, почему это поведение по умолчанию в Excel. Но иногда целью может быть применение абсолютной ссылки, а не относительной ссылки на ячейку в Excel. Абсолютная ссылка делает ссылку на ячейку фиксированной на абсолютный адрес ячейки, благодаря чему при копировании формулы она остается неизменной.
Абсолютно никакие знаки доллара не требуются с относительной ссылкой. Когда мы копируем формулу из одного места в другое, формула будет соответствующим образом адаптироваться. Таким образом, если мы введем = B1 + B2 в ячейку B3, а затем перетащим или скопируем и вставим ту же формулу в ячейку C3, ссылка на относительную ячейку автоматически настроит формулу на = C1 + C2 .
При относительной привязке указанные ячейки автоматически корректируются в формуле в соответствии с вашим движением: вправо, влево, вверх или вниз.
При относительной ссылке, если мы дадим ссылку на ячейку D10, а затем сместим одну ячейку вниз, она изменится на D11, если вместо этого мы сдвинем одну ячейку вверх, она изменится на D9 . Однако, если мы сместим одну ячейку вправо, ссылка изменится на E10, и вместо этого, если мы сдвинем одну ячейку влево, ссылка автоматически изменится на C10.
Однократное нажатие клавиши F4 изменит относительную ссылку на ячейку на абсолютную ссылку на ячейку в Excel.
Двойное нажатие клавиши F4 изменит ссылку на ячейку на смешанную ссылку, где строка заблокирована.
Нажатие F4 трижды изменит ссылку на ячейку на смешанную ссылку, где столбец заблокирован.
Нажатие F4 в четвертый раз изменит ссылку на ячейку обратно на относительную ссылку в Excel.

Ссылка на ячейку в Excel (примеры) - Типы - Относительный, Абсолютный и Смешанный