Посилання в Excel – абсолютні, відносні та змішані. Помилки при роботі з відносними посиланнями в Excel

Для полегшення розрахунків за формулами в автоматичному режимі використовуються посилання на клітинки. Залежно від типу письма вони поділяються на три основні види:

1. Відносні посилання. Використовується для простих розрахунків. Копіювання формули тягне за собою зміну координат.
2. Абсолютні посилання. Якщо вам необхідно провести більш складні розрахунки, цей варіант підійде. Для фіксації використовуйте символ «$». Приклад: $A$1.
3. змішані посилання. Цей тип адресації використовується в розрахунках, коли необхідно окремо зафіксувати стовпець або рядок. Приклад: $A1 або A$1.

При необхідності копіювання даних введеної формули використовуються посилання з абсолютною та змішаною адресацією. У статті на прикладах буде розказано, як здійснюються розрахунки з використанням різних типів посилань.

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

Це набір символів, які визначають розташування комірки. Посилання в програмі записуються автоматично з відносною адресацією. Наприклад: A1, A2, B1, B2. Перехід до іншого рядка чи стовпця змінює символи у формулі. Наприклад, вихідна позиція А1. Переміщення по горизонталі змінює букву на B1, C1, D1 і т. д. Так само зміни відбуваються при русі по вертикальній лінії, тільки в цьому випадку змінюється цифра – A2, A3, A4 і т. д. Якщо потрібно дублювати обчислення того самого типу в сусідній комірці, обчислення виконується за допомогою відносного посилання. Щоб скористатися цією функцією, виконайте кілька кроків:

1. Як тільки дані будуть введені в клітинку, перемістіть курсор і клацніть мишкою. Виділення зеленим прямокутником свідчить про активацію осередку і готовність до подальшої роботи.
2. Натиснувши комбінацію клавіш Ctrl + C, копіюємо вміст в буфер обміну.
3. Активуємо комірку, в яку потрібно перенести дані або раніше написану формулу.
4. Натисканням комбінації Ctrl + V ми переносимо дані, збережені в системному буфері обміну.

Порада експерта! Щоб провести однотипні розрахунки в таблиці, скористайтеся лайфхаком. Виділіть клітинку, що містить введену раніше формулу. Навівши курсор на маленький квадратик, що з'явився в нижньому правому куті, і, утримуючи ліву кнопку миші, перетягніть в нижній рядок або крайній стовпець, в залежності від виконуваної дії. Якщо відпустити кнопку миші, розрахунок буде виконано автоматично. Цей інструмент називається маркером автозаповнення.

Приклад відносного посилання

Щоб було зрозуміліше, розглянемо приклад розрахунку за формулою з відносною прив'язкою. Припустимо, власнику спортивного магазину після року роботи потрібно порахувати прибуток від продажів.

Порядок дій:

1. З прикладу видно, що графи B і C використовувалися для заповнення кількості проданого товару та його ціни. Відповідно, щоб записати формулу і отримати відповідь, вибираємо стовпець D. Формула виглядає так: = B2 *C

Звернути увагу! Щоб полегшити процес написання формули, скористайтеся маленькою хитрістю. Поставте знак «=», натисніть на кількість проданого товару, встановіть знак «*» і натисніть на ціну товару. Формула після знака рівності буде записана автоматично.

2. Щоб отримати остаточну відповідь, натисніть «Enter». Далі потрібно підрахувати загальну суму прибутку, отриманого від інших видів продукції. Ну а якщо кількість ліній невелика, то всі маніпуляції можна виконати вручну. Для одночасного заповнення великої кількості рядків в Excel є одна корисна функція, яка дає можливість переносити формулу в інші комірки.
3. Наведіть курсор на правий нижній кут прямокутника з формулою або готовим результатом. Поява чорного хрестика служить сигналом про те, що курсор можна перетягнути вниз. Таким чином, здійснюється автоматичний розрахунок прибутку, отриманого по кожному продукту окремо.
4. Відпускаючи натиснуту кнопку миші, отримуємо правильні результати у всіх рядках.

Клацнувши комірку D3, ви побачите, що координати комірки були автоматично змінені та тепер виглядають так: =B3 *C3. З цього випливає, що зв'язки були відносними.

Можливі помилки при роботі з відносними посиланнями

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

1. Створіть таблицю та заповніть: А – назву товару; B – продана кількість; C – вартість; D — отримана сума. Припустимо, що в асортименті всього 11 найменувань. Отже, з урахуванням опису граф заповнюється 12 рядків і загальна сума прибутку Д
2. Натисніть клітинку E2 і введіть =D2/D13.
3. Після натискання кнопки «Enter» з’являється коефіцієнт відносної частки продажів першої позиції.
4. Протягніть стовпчик вниз і дочекайтеся результату. Однак система видає помилку «#DIV/0!»

Причиною помилки є використання відносного посилання для розрахунків. У результаті копіювання формули змінюються координати. Тобто для Е3 формула матиме такий вигляд =D3/D13. Оскільки клітинка D13 не заповнена і теоретично має нульове значення, програма видасть помилку з інформацією про те, що ділення на нуль неможливо.

Важливо! Щоб виправити помилку, необхідно записати формулу так, щоб координати D13 були фіксованими. Відносна адресація не має такої функції. Для цього існує інший тип посилань – абсолютні. 

Як зробити абсолютне посилання в Excel

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

1. Натисніть на E2 і введіть координати посилання =D2/D13. Оскільки зв’язок відносний, для фіксації даних потрібно встановити символ.
2. Зафіксуйте координати комірки D. Щоб виконати цю дію, перед літерою, яка позначає стовпець, і номером рядка поставте знак «$».

Порада експерта! Для полегшення завдання введення досить активувати осередок для редагування формули і кілька разів натиснути клавішу F4. Поки ви не отримаєте задовільні значення. Правильна формула така: =D2/$D$13.

3. Натисніть кнопку «Enter». В результаті виконаних дій повинен з'явитися правильний результат.
4. Перетягніть маркер у нижній рядок, щоб скопіювати формулу.

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

Як поставити змішане посилання в Excel

Для формульних розрахунків використовуються не тільки відносні та абсолютні посилання, а й змішані. Їх відмінною рисою є те, що вони фіксують одну з координат.

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

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

1. Для точних розрахунків введіть =D1/$D$3 і натисніть «Enter». Програма дає точну відповідь.
2. Щоб перемістити формулу в наступні комірки вниз по стовпцю та отримати точні результати, перетягніть ручку до нижньої комірки.
3. В результаті програма видасть правильні розрахунки.

Увага! Якщо поставити перед буквою знак $, то Excel видасть помилку «#DIV/0!», що означатиме, що цю операцію виконати неможливо.

«Суперабсолютна» адресація

На завершення розглянемо ще один приклад абсолютного посилання – «СуперАбсолютну» адресацію. У чому його особливості та відмінності. Візьміть приблизне число 30 і введіть його в клітинку B2. Саме це число буде головним, з ним необхідно виконати ряд дій, наприклад, підняти його в ступінь.

1. Для коректного виконання всіх дій в графу С введіть наступну формулу: =$B$2^$D2. У графу D вводимо значення градусів.

2. Після натискання кнопки «Enter» і активації формули розтягуємо маркер вниз по колонці.
3. Ми отримуємо правильні результати.

Суть в тому, що всі виконувані дії стосувалися однієї фіксованої комірки В2, тому:

• Копіювання формули з клітинки C3 до клітинки E3, F3 або H3 не змінить результат. Залишиться без змін – 900.
• Якщо потрібно вставити новий стовпець, координати комірки з формулою зміняться, але результат також залишиться незмінним.

У цьому особливість ланки «СуперАбсолют»: при необхідності рухатися результат не зміниться. Однак бувають ситуації, коли дані вставляються зі сторонніх джерел. Таким чином, стовпці зсуваються в сторону, а дані задаються по-старому в стовпці В2. Що відбувається в цьому випадку? При змішуванні формула змінюється відповідно до виконуваної дії, тобто вказуватиме вже не на B2, а на C2. Але оскільки вставка була зроблена в B2, кінцевий результат буде неправильним.

Довідка! Щоб мати можливість вставляти макроси зі сторонніх джерел, потрібно ввімкнути параметри розробника (за замовчуванням вони вимкнені). Для цього зайдіть в «Параметри», відкрийте налаштування стрічки і поставте галочку в правій колонці навпроти «Розробник». Після цього відкриється доступ до багатьох функцій, які раніше були приховані від очей звичайного користувача.

У зв’язку з цим виникає запитання: чи можна змінити формулу з клітинки C2 так, щоб оригінальне число збиралося з клітинки B, незважаючи на вставлення нових стовпців даних? Щоб переконатися, що зміни в таблиці не вплинули на визначення підсумку під час встановлення даних зі сторонніх джерел, необхідно виконати наступні кроки:

1. Замість координат комірки В2 введіть такі показники: =INDIRECT(“B2”). В результаті після переміщення рецептурний склад буде виглядати так: =INDIRECT(“B2”)^$E2.
2. Завдяки цій функції посилання завжди вказує на квадрат з координатами B2, незалежно від того, додано чи видалено стовпці в таблиці.

Потрібно розуміти, що комірка, яка не містить даних, завжди показує значення «0».

Висновок

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