Отже, щоб заощаджувати сили та час, ми майже завжди використовуємо не значення у формулах, а посилання на осередки.
Наприклад, щоб скласти число 1 (що знаходиться в осередку A1) і число 2 (що знаходиться в осередку B1) ми використовуємо формулу =A1+B1, замість просто складати самі значення. Це є посилання.
Але які трапляються посилання?
Всього в Excel є 3 типи посилань:
- Абсолютні
- Відносні
- Змішані
Так-так, вони бувають різні. А тепер давайте докладніше поговоримо у чому між ними різниця.
Тож почнемо!
Відносний тип посилань
У цьому випадку найпростіше буде показати на прикладі.
Допустимо, у нас є така табличка:
Наприклад, нам потрібно дізнатися про суму всіх товарів. Для цього ціна * кількість.
Для товару А формула набуде наступного вигляду:
А тепер замість того, щоб прописувати формулу для кожного товару, ми просто копіюємо її і вставляємо в усі наступні осередки. Excel автоматично змінює посилання у формулі на потрібні.
Посилання, які Excel автоматично підлаштовує під нові осередки – відносні.
У яких ситуаціях вони найкраще?
Ці посилання краще застосовувати тоді, коли потрібно скопіювати функцію багато разів. Оскільки у випадку відносного типу посилань вони підлаштовуються автоматично, це вигідне використання, яке заощаджує ваш час.
Абсолютний тип посилань
Це тип посилань не змінюється, якщо ми копіюватимемо його в інші осередки.
Припустимо, у нас є така табличка, де праворуч (виділено жовтим) зазначена комісія магазину.
Припустимо, нам потрібно дізнатися чистий прибуток, який отримає магазин під час продажу кожного товару. Формула набуде такого вигляду:
Значення, яке не повинно змінюватися при копіюванні та вставці формули, міститься в “$”.
Що цей “$” робить?
Він вказує програмі на те, що ми не хочемо, щоб цей осередок при копіюванні змінювався. Тобто робить цю частину формули абсолютною.
При копіюванні перша частина формули (яка не міститься в «$» змінюватиметься і «адаптуватиметься»), а друга частина формули (яка міститься в «$» змінюватись не буде).
У яких ситуаціях вони найкраще?
У таких, коли у вашій формулі є якесь значення, яке є актуальним для всіх осередків. Наприклад, як у нашому випадку, розглянутому вище. Це може бути корисним у абсолютно різних ситуаціях. При цьому ви не хочете прописувати це значення вручну у формулу, тому що комісія магазину, наприклад, може будь-якої миті змінитись. Тоді, якщо ви вказали значення комісії у формулі вручну, вам потрібно буде міняти її у всіх формулах. У нашому ж способі, ви просто зміните його 1 раз у комірці та все.
Змішаний тип посилань
Тут буде трохи складніше, ніж раніше.
Змішані посилання поділяються на 2 типи:
- При копіюванні формули змінюється рядок, а стовпець залишається статичним.
- І навпаки.
Покажу одразу на прикладі.
Допустимо, у нас є така табличка і нам треба порахувати комісію.
Формула для розрахунку 10% набуде такого вигляду:
= $ B4 * $ C4 * E $ 2
У цій формулі ми використовуємо відразу обидва варіанти змішаного типу посилань (ви можете зрозуміти це за знаком долара, один блокує рядки, а другий стовпчики).
Як це працює:
- $B4*$C4 — у цьому випадку стовпчик, при копіюванні, не змінюватиметься. Змінюється лише рядок.
- E$2 — а тут, при копіюванні, буде змінюватися стовпчик, а рядок залишиться колишнім.
Як змінювати тип посилань?
Напевно, ви вже здогадалися, зробити це досить просто.
Наприклад, є посилання B2, зробимо її абсолютним типом, додавши 2 долари: $B$2.
Якщо посилань мало, то змінити їх не буде складно. Але що робити якщо потрібно змінити тип посилань відразу на 100+ осередків? Як це швидко зробити?
За допомогою гарячих клавіш, звичайно.
Давайте одразу розглянемо приклад.
Допустимо, у вас є багато посилань і вам потрібно змінити їх тип.
Покрокова інструкція:
- Виділіть діапазон комірок та натисніть один раз на F4. Тип зміниться абсолютний;
- Якщо натиснути F4 двічі, то тип стане змішаним, а незмінною, при копіюванні змінною буде рядок;
- Якщо натиснути F4 тричі, то тип стане змішаним, а незмінною, при копіюванні, змінною буде стовпчик;
- Якщо натиснути F4 чотири рази, ми повернемося до відносного типу посилання.
