✅ Відносна довідка в Excel (приклади) - Як використовувати відносну довідку

Відносна довідка в Excel

Відносна довідка в Excel (Зміст)

Відносна довідка в Excel
Як використовувати відносну посилання в Excel?

Відносна довідка в Excel

Якщо б попросити згадати про ту особливість Excel, яка справді робить досвід магічним під час роботи з формулами, можливо, це факт, що жорстке кодування значень у ваші формули не потрібно (насправді це навіть не рекомендується).

У Excel електронні таблиці складаються із стовпців та рядків, що складають "комірки". Кожній комірці відповідає точний стовпець і точний рядок. Щоб пояснити це на прикладі, комірка B2 посилається на другий стовпчик (B) та другий рядок (2). Відповідно, D16 вказує на четвертий стовпчик (D) та шістнадцятий ряд (16). Фактичний край Excel полягає у зручності використання посилань на комірки в інших клітинках під час створення формул.

Що таке відносна довідка в Excel?

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

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

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

Ця відносна довідка дуже проста у використанні. Давайте тепер подивимося, як використовувати відносну довідку в Excel за допомогою деяких прикладів.

Ви можете завантажити цей відносний довідковий шаблон Excel тут - Відносний довідковий шаблон Excel

Приклад №1

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

Тому ми застосовуємо формулу А1 + А2, яка дала б результат як 200 в А3.

Результат - 200.

Припустимо, у нас є аналогічний сценарій у наступному стовпчику (“B”). У комірці B1 і B2 є два числа, і ми хочемо мати суму в B3.

Ми можемо досягти цього двома різними способами:

Тут ми фізично пишемо формулу, щоб додати дві комірки B1 і B2 у B3, щоб отримати результат як 30.

Результат як 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 або просто скопіювати та вставити клітинку I2 у всі вибрані комірки.

Що слід пам’ятати про відносну довідку в Excel

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

Відносна довідка в Excel (приклади) - Як використовувати відносну довідку