✅ Посилання на клітинку в 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

Використання правильного типу довідки стільникових даних у excel у конкретному сценарії заощадить багато часу та зусиль та полегшить роботу.

№1 - Відносна посилання на клітинку в Excel

Відносні посилання на клітинку в excel стосуються клітини або діапазону комірок у excel. Кожного разу, коли значення вводиться у формулу, таку як SUMIFS, можна вводити в Excel "посилання на комірки" як заміну жорстко кодованого числа. Посилання на комірку може надходити у формі B2, де B відповідає букві стовпця відповідної комірки, а 2 являє собою номер рядка. Щоразу, коли Excel наштовхується на посилання на клітинку, він відвідує конкретну клітинку, витягує її значення та використовує це значення в тій чи іншій формулі, яку ви пишете. Коли ця посилання на клітинку у excel дублюється в іншому місці, відносні посилання комірок у excel відповідно також змінюються автоматично.

Коли ми посилаємо клітинки так, ми можемо досягти цього за допомогою будь-якого з двох типів посилань комірок у відмінності: абсолютної та відносної. Розмежування між цими двома різними типами посилань полягає в різній властивій поведінці, коли ви перетягуєте або копіюєте та вставляєте їх у різні комірки. Відносні посилання комірок можуть змінювати себе та коригувати під час їх копіювання та вставки; абсолютних посилань навпаки немає. Щоб успішно досягти результатів в Excel, дуже важливо правильно використовувати відносні та абсолютні посилання на комірки.

Як ефективно використовувати відносну клітинку в Excel?

Щоб мати можливість всебічно зрозуміти універсальність та зручність використання цієї дивовижної особливості Excel, нам потрібно буде переглянути кілька практичних прикладів, щоб зрозуміти її справжню цінність.

Ви можете завантажити цей шаблон посилань на Excel Шаблон тут - Шаблон Excel Excel

Приклад №1

Розглянемо простий приклад для пояснення механіки відносних клітинних посилань в Excel. Якщо ми хочемо мати суму двох чисел у двох різних комірках - А1 та А2, і отримаємо результат у третій комірці А3.

Тож застосовуємо формулу = 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

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

Ціна продажу = Ціна одиниці х одиниць, що продаються

Щоб мати змогу знайти Продажну ціну, нам потрібно тепер помножити одиницю ціни на одиниці, продані для кожного товару. Отже, тепер перейдемо до застосування цієї формули для першої комірки у Продажній ціні, тобто до 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, рядок блокується, і стовпець може змінюватися для цього конкретного рядка.

На відміну від відносної довідки, яка може змінюватися під час переміщення до різних комірок, абсолютна посилання не змінюється. Тут потрібно лише заблокувати конкретну клітинку.

Використовуючи знак формули долара у формулі, wrt посилання на клітинку робить її абсолютною посиланням на клітинку, оскільки знак долара блокує комірку. Ми можемо блокувати або рядок, або стовпець, використовуючи знак долара. Якщо "$" стоїть перед алфавітом, він блокує стовпець, а якщо "$" - перед числом, то рядок блокується.

# 3 - Змішана посилання на клітинку в Excel

Як ефективно використовувати посилання на абсолютну клітинку в Excel, а також як користуватися змішаною посиланням на комірки в excel?

Щоб отримати всебічне розуміння абсолютних та змішаних посилань на комірки в excel, давайте розглянемо наступний приклад.

У нас є дані про продажі для 4 менеджерів з продажу за різні місяці, де продажі відбувалися кілька разів на місяць.

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

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

Давайте поспостерігаємо за формулою, щоб побачити, що сталося.

У "sum_range" ми маємо $ C $ 2: $ C $ 17. Перед абетками та цифрами стоїть знак долара. Таким чином, і рядки, і стовпці для діапазону комірок блокуються. Це абсолютна посилання на клітинку.
Далі ми маємо “критерії_поширення1”. Тут також ми маємо абсолютну клітинку.
Після цього у нас є "критерії1" - $ F2. Тут ми бачимо, що під час копіювання комірки формули буде заблокований лише стовпець, тобто лише рядок зміниться, коли ми скопіюємо формулу в іншу комірку (рухаючись вниз). Це змішана посилання на клітини.
Далі, у нас є "kriteri__range2", який також є абсолютним посиланням на клітинку.
Заключний сегмент формули - "критерії2" - G $ 1. Тут ми спостерігаємо, що знак долара присутній перед цифрою, а не алфавітом. Таким чином, лише рядок блокується, коли ми копіюємо комірку формули. Стовпець може змінюватися, коли ми копіюємо комірку формули в іншу комірку (рухається праворуч). Це змішана посилання на клітини.

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

Посилання на змішану комірку стосується лише певного рядка або стовпця, наприклад = $ A2 або = A $ 2 . Якщо ми хочемо створити змішану посилання на клітинку, ми можемо натиснути клавішу F4 за формулою два-три рази відповідно до вашої вимоги, тобто для позначення рядка або стовпця. Повторне натискання на F4 призведе до зміни посилання на клітинку до відносної.

Що потрібно пам’ятати

Під час копіювання формули Excel, відносне посилання - це, як правило, бажане. Це причина, чому це поведінка Excel за замовчуванням. Але іноді ціллю може бути застосування абсолютної посилання, а не відносна посилання на клітинку в excel. Absolute Reference - це посилання на клітинку, закріплене за абсолютною адресою комірки, завдяки чому, коли формула скопіюється, вона залишається незмінною.
Абсолютно не потрібні знаки долара при відносному посиланні. Коли ми копіюємо формулу з одного місця в інше, формула буде відповідно адаптуватися. Отже, якщо ми введемо = B1 + B2 у комірку B3, а потім перетягнемо чи скопіюємо та вставимо ту саму формулу в комірку C3, відносна посилання комірки автоматично регулює формулу = = C1 + C2 .
При відносному посиланні згадані комірки автоматично регулюються у формулі відповідно до вашого руху: вправо, вліво, вгору або вниз.
При відносному посиланні, якби ми давали посилання на комірку D10, а потім зміщували одну клітинку вниз, вона змінилася б на D11, якщо замість цього ми змістимо одну комірку вгору, вона змінилася б на D9 . Якщо ж ми змістимо одну клітинку вправо, посилання зміниться на E10, і замість цього, якщо ми змістимо одну комірку вліво, посилання автоматично підкоригується на C10.
Натискання клавіші F4 один раз змінить відносну клітинку посилання на абсолютну посилання на комірку у excel.
Натискання клавіші F4 двічі змінить посилання комірки на змішане посилання, де рядок заблоковано.
Натискання F4 тричі змінить посилання комірки на змішане посилання, де стовпець заблокований.
Натиснення F4 в четвертий раз змінить посилання на клітинку до відносної посилання в excel.

Посилання на клітинку в Excel (приклади) - Типи - відносні, абсолютні та змішані