Абсолютна і відносна посилання в excel. Як використовувати?

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

Відео: Формули в Excel 2007. Відносні і абсолютні посилання

визначення

Посилання в Microsoft Excel - це не те ж саме, що і посилання в інтернеті. Посиланням називається адреса комірки (наприклад: А1, В10). Вони діляться на два основних види - абсолютні і відносні посилання. В Excel адресація відбувається по осередках, тому при перенесенні і копіюванні формул нам часто потрібно зберегти або, навпаки, замінити дані.

Відносне посилання

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

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

• Нехай нам дано осередки з даними (стовпці) - М1, М2, М3, Н1, Н2, Н3. Під ними в осередку М4 ми пишемо формулу, результатом якої буде сума трьох чисел (М4 = М1 + М2 + М3). Начебто все просто. Тепер нам необхідно порахувати вміст комірки Н4. Вона також є сумою тих даних, які знаходяться над нею. Перетягуванням або просто копіюванням осередку М4, ми переносимо формулу в Н4. В результаті всі посилання в ній (М1, М2, М3) будуть замінені на Н1, Н2 і Н3, відповідно.

Відео: Введення формул в Excel: абсолютні і відносні посилання, частина 2

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

Абсолютне посилання

Як вже говорилося, абсолютні та відносні посилання в Excel служать різним цілям. Другий тип посилань, що розглядається нами сьогодні, покликаний зберегти адресацію при будь-яких змінах і переміщеннях формули.

• Припустимо, вам треба вирішити якусь фізичну задачу. У вас є якась константа, записана в осередку А1. Звичайно, можна було б кожен раз писати це число вручну, однак простіше записати її в одне певне місце. Також нам дано два стовпці даних (М1, М2, Р1, Р2). В осередках М3 і Р3 необхідно порахувати наступну формулу: М3 = (М1 + М2) * $ А $ 1. Тепер при перетягуванні і копіюванні в осередок Р3 вийде наступний результат Р3 = (Р1 + Р2) * $ А $ 1. Тобто адреса використовуваної клітинки не зрушиться.

Щоб адресу посилання зробити абсолютним, натисніть на формулу, виділіть необхідну "посилання" і натисніть F4. Або вручну надрукуйте символи "долара".

змішана

Це останній тип, зустрічається в формулах. Як зрозуміло з назви, це абсолютна і відносна посилання одночасно. Нескладно здогадатися, що на практиці вона позначається, як $ А1 або А $ 1. Таким чином, це дозволяє зберігати адресацію на стовпець або рядок, але продовжувати "ковзати" по ньому. Розглянемо приклад.

Отже, у нас є таблиця з деякими даними. У стовпці "А" у нас є якась величина, а в рядку "1", Коефіцієнт, на який необхідно помножити. Осередки В2, С3 міститимуть результати. Як бачите абсолютна і відносна посилання тут безсилі. У таких випадках необхідно використовувати змішаний тип.

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

• В3 = $ А3 * В $ 1
• С2 = $ А2 * С $ 1
• С3 = $ А3 * С $ 1

Відео: Курс Excel_Базовий - Урок №8. Абсолютні і відносні посилання

Як бачите, абсолютна і відносна посилання тут би не допомогли. Тому при складних розрахунках необхідно використовувати виключно змішаний тип. Це дозволить безболісно масштабувати таблицю, додавати в неї рядки і стовпці. Єдине, при роботі з такими типами адресації завжди треба бути дуже уважними, оскільки, припустившись помилки один раз в таблиці з використаної змішаної посиланням, потім знайти недолік буде дуже непросто.

На цьому все. Сподіваємося, тепер вам стало краще зрозуміло, що таке абсолютна і відносна посилання.