Абсолютний, відносний та змішаний тип посилань в Excel

Отже, щоб заощаджувати сили та час, ми майже завжди використовуємо не значення у формулах, а посилання на осередки.

Наприклад, щоб скласти число 1 (що знаходиться в осередку A1) і число 2 (що знаходиться в осередку B1) ми використовуємо формулу =A1+B1, замість просто складати самі значення. Це є посилання.

Але які трапляються посилання?

Всього в Excel є 3 типи посилань:

  • Абсолютні
  • Відносні
  • Змішані

Так-так, вони бувають різні. А тепер давайте докладніше поговоримо у чому між ними різниця.

Тож почнемо!

Відносний тип посилань

У цьому випадку найпростіше буде показати на прикладі.

Допустимо, у нас є така табличка:

Абсолютний, відносний та змішаний тип посилань в Excel

Наприклад, нам потрібно дізнатися про суму всіх товарів. Для цього ціна * кількість.

Для товару А формула набуде наступного вигляду:

Абсолютний, відносний та змішаний тип посилань в Excel

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

Абсолютний, відносний та змішаний тип посилань в Excel

Посилання, які Excel автоматично підлаштовує під нові осередки – відносні.

У яких ситуаціях вони найкраще?

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

Абсолютний тип посилань

Це тип посилань не змінюється, якщо ми копіюватимемо його в інші осередки.

Припустимо, у нас є така табличка, де праворуч (виділено жовтим) зазначена комісія магазину.

Абсолютний, відносний та змішаний тип посилань в Excel

Припустимо, нам потрібно дізнатися чистий прибуток, який отримає магазин під час продажу кожного товару. Формула набуде такого вигляду:

Абсолютний, відносний та змішаний тип посилань в Excel

Значення, яке не повинно змінюватися при копіюванні та вставці формули, міститься в “$”.

Що цей “$” робить?

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

При копіюванні перша частина формули (яка не міститься в «$» змінюватиметься і «адаптуватиметься»), а друга частина формули (яка міститься в «$» змінюватись не буде).

У яких ситуаціях вони найкраще?

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

Змішаний тип посилань

Тут буде трохи складніше, ніж раніше.

Змішані посилання поділяються на 2 типи:

  • При копіюванні формули змінюється рядок, а стовпець залишається статичним.
  • І навпаки.

Покажу одразу на прикладі.

Допустимо, у нас є така табличка і нам треба порахувати комісію.

Абсолютний, відносний та змішаний тип посилань в Excel

Формула для розрахунку 10% набуде такого вигляду:

= $ B4 * $ C4 * E $ 2

Абсолютний, відносний та змішаний тип посилань в Excel

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

Як це працює:

  • $B4*$C4 — у цьому випадку стовпчик, при копіюванні, не змінюватиметься. Змінюється лише рядок.
  • E$2 — а тут, при копіюванні, буде змінюватися стовпчик, а рядок залишиться колишнім.

Як змінювати тип посилань?

Напевно, ви вже здогадалися, зробити це досить просто.

Наприклад, є посилання B2, зробимо її абсолютним типом, додавши 2 долари: $B$2.

Якщо посилань мало, то змінити їх не буде складно. Але що робити якщо потрібно змінити тип посилань відразу на 100+ осередків? Як це швидко зробити?

За допомогою гарячих клавіш, звичайно.

Давайте одразу розглянемо приклад.

Допустимо, у вас є багато посилань і вам потрібно змінити їх тип.

Покрокова інструкція:

  • Виділіть діапазон комірок та натисніть один раз на F4. Тип зміниться абсолютний;
  • Якщо натиснути F4 двічі, то тип стане змішаним, а незмінною, при копіюванні змінною буде рядок;
  • Якщо натиснути F4 тричі, то тип стане змішаним, а незмінною, при копіюванні, змінною буде стовпчик;
  • Якщо натиснути F4 чотири рази, ми повернемося до відносного типу посилання.
nBook
Додати коментар