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

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

абсолютная и относительная ссылка

Визначення

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

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

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

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

    относительные абсолютные и смешанные ссылки

    Змішана

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

    АВЗ
    13,54,5
    270
    380

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

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

  • В3=$А3*В$1
  • С2=$А2*З$1
  • С3=$А3*З$1
  • абсолютные и относительные ссылки в формулах

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

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