Коли користуєшся різними формулами Excel, то доводиться звертати увагу на посилання. Посилання в Excel, як відомо, бувають кількох видів: відносні, абсолютні, зовнішні, у вигляді імен діапазонів тощо.
Головне зрозуміти, що таке відносні та абсолютні посилання і навіщо вони використовуються в Excel. Зазвичай формула прописується один раз, а потім протягується (копіюється) на інші клітинки (наприклад, у підсумковому рядку або підсумковому стовпці таблиці). У цьому матеріалі розберемо адресацію в Excel.
Основні відомості про адресацію в Ексель
При копіюванні формули («протягуванні») можуть відбуватися 2 різні сценарії:
- Діапазон, на який веде посилання, «переміщується» слідом за клітинкою, що копіюється, тобто залишається на одному місці (рівні) щодо клітинки з формулою.
- Посилання веде на ту саму адресу незалежно від того, куди копіюється формула.
Недооцінка важливості виду посилання призводить до помилок у розрахунках та необхідності корекції формул. Все це як мінімум сповільнює роботу.
Розглянемо найпростіший приклад. Потрібно скласти два числа. Зробити це легко, прописавши у вільній клітинці (наприклад, внизу) знак «=» і потім через знак «+» натиснути на клітинки, що потрібно скласти. Якщо чисел багато, то підсумувати краще через функцію SUM, вказавши відразу весь діапазон підсумовування.
Як неважко помітити, замість чисел ми робимо посилання на клітинки, що складаються, або відразу на цілий діапазон. Посилання замість чисел – це основна відмінність Excel від калькулятора. Але щоб вони працювали правильно, непогано було б розрізняти абсолютні та відносні.
Відносне посилання Excel
Відносне посилання – це адреса клітинки, що містить вихідні дані, змінюється при копіюванні та переміщенні формули. Зміна адреси при копіюванні відбувається за правилом відносної орієнтації клітинки з вихідною формулою.
Розглянемо трохи складніший приклад, коли потрібно обчислити суму для кількох стовпців або, як іноді кажуть, підбити підсумки. Найчастіше формулу вводять в одну клітинку і потім протягують в потрібному напрямку.
Як бачимо з прикладу вище, при копіюванні формули діапазон підсумовування автоматично переміщається за формулою, дозволяючи за мінімум дій розрахувати підсумковий рядок. Подібний розрахунок можливий завдяки відносності посилань, які ніби прив’язані до клітинки з формулою, а не до адреси, на яку вказують.
Якщо формула копіюється вправо, то і діапазон, що обчислюється, зміщується туди ж. Якщо вниз або вгору, те ж саме станеться з діапазоном обчислення. За замовчуванням посилання в рамках однієї книги Excel робляться відносними та мають такий вигляд:
=A1, де літера означає назву стовпця, а число – номер рядка.
Проте бувають ситуації, коли посилання на адресацію клітинки у формулі, при обчисленнях в електронних таблицях, має залишатися незмінною, незалежно від того, куди копіюється формула.
Абсолютне посилання
Абсолютне посилання – це коли адреса клітинки, що містить вихідні дані, не змінюється та не модифікується під час копіювання та переміщенні формули.
У цьому випадку застосовується прийом, що називають заморожуванням адреси клітинок. Для вказівки абсолютної адресації, вводиться символ $ перед назвою стовпця та номером рядка (при введенні формули натисніть F4 або комбінацію клавіш Shift+4 в англійській розкладці).
Розглянемо приклад, коли необхідно розрахувати частку кожного значення до підсумку. Введемо формулу для розрахунку частки першого числа.
Тепер спробуємо «протягнути» (скопіювати) формулу вниз для розрахунку інших часток. Сума часток повинна вийти рівно 100%. Проте вже на другому значенні видно появу помилки.
Чисельник змістився вниз правильно, а ось адреса клітинки для знаменника мала залишитися незмінною. Щоб усе вийшло правильно, потрібно зафіксувати посилання на адресу клітинки з «Підсумком», щоб вона не змінювалась вслід за формулою.
Іншими словами, посилання на підсумкову суму потрібно зробити абсолютним, для цього перед назвою рядка і стовпця ставиться значок долара $ (Shift+4 в англійській розкладці). В результаті формула прийме вигляд:
=B2/$B$7
Таким же чином можна «закріпити» посилання на валютний курс, мито, податок та інше, що знаходиться в одному місці для багатьох клітинок, що на них посилаються.
Змішані посилання
Крім абсолютних та відносних посилань зустрічаються ще й так звані змішані, коли фіксується лише рядок чи стовпець. Цей тип посилань також можуть називати – часткова абсолютна адресація.
Зустрічається у випадках, коли при копіюванні чи переміщенні формули не змінюється номер рядка чи заголовок стовпця в адресі клітинки. При цьому символ долара $ у першому випадку ставиться перед номером рядка, а в другому перед заголовком стовпця.
Закріплений рядок ніяк не впливає на абсолютність стовпця, а абсолютний стовпець не залежить від відносності рядка. Для вибору правильного варіанта потрібно знати, куди копіюється клітинка з формулою і тоді не буде жодних проблем.
Строго кажучи, у прикладі, зображеному на скриншоті вище, до розрахунку вартості товару у валюті, посилання на курс долара можна зробити змішаним, оскільки фіксація необхідна лише за рядком. Але якщо формула копіюється і по вертикалі, і по горизонталі, тоді точно не обійтися без повного абсолютного посилання.
Як швидко встановити символ долара $
Нагадаємо, значок $ в адресі клітинки означає абсолютну адресу. Якщо ставити значок долара з клавіатури класичним методом, спочатку потрібно перейти в англійську розкладку, а потім натиснути Shift+4. Скажімо прямо, це довго та незручно.
Набагато швидше увійти в режим редагування формул за допомогою клавіші F2 (якщо ви набираєте формулу вручну, то вже знаходитесь у цьому режимі), встановити курсор на потрібну адресу клітинки та натиснути F4. Посилання почне змінювати свій режим абсолютності.
Після натискання F4 відносне посилання стане абсолютним (і по рядках, і по стовпцях). Якщо натиснути F4 ще раз, то абсолютним стане лише рядок, наступне натискання зробить абсолютним лише стовпець. Чергове натискання F4 зробить посилання знову відносним. І так по колу. Знову абсолютний, тільки рядок, тільки стовпець, відносний тощо. Послідовним натисканням F4 зупиняєтеся на потрібному варіанті.
Це дуже зручно, не потрібно перемикати розкладку та шукати знак $. Ця ж кнопка працює і в діалоговому вікні «Вставлення функції». Наприклад, при написанні формули з використанням COUNTIF або SUMMIF потрібно уважно дивитися на діапазони, що посилаються.
Якщо діапазон, на який потрібно послатися, знаходиться в тій же книзі (файлі Excel), нехай навіть і на іншому аркуші, Excel за замовчуванням встановлює відносні посилання (без символу «$»). А ось посилання на інші книги Excel за замовчуванням робить абсолютними (вставляє “$” де тільки можна). Але про інші аркуші та книги поговоримо далі.
Посилання на інші листи та книги
Excel вміє посилатися не тільки у межах одного аркуша, а й на інші аркуші та навіть інші файли Excel. Такий функціонал значно розширює діапазон обчислень. Якщо в формулах є посилання на інші файли, слід звернути увагу на наступне.
- При відкритті файлу із зовнішніми зв’язками, Excel настирливо нагадує про це діалоговим вікном, що вискакує. Це, звичайно, допомагає не забувати, що файл має зовнішні посилання, але краще, щоб їх взагалі не було.
- При зміні чи видаленні зовнішнього джерела миттєво змінюються і результати обчислень, що потрібно далеко не завжди.
- Якщо виникнуть помилки у розрахунках, то на виявлення їх джерела може знадобитися додатковий час, оскільки дані знаходяться в іншому файлі.
Тому, одразу після обчислень з використанням даних з інших книг, можна видалити зовнішні посилання: Дані → Запити та підключення → Редагувати зв’язки → Розірвати зв’язок.
Після видалення зв’язків, усі внутрішні формули залишаться незмінними, а формули із зовнішніми посиланнями перетворяться на значення.
Стиль посилань R1C1
Класична і всім відома система адресації клітинок на аркушах в Excel є поєднанням літери стовпця і номера рядка. Морський бій або шахи використовують ту саму ідею для позначення клітин дошки.
Однак існує менш відома система адресації, що має дещо незвичний вигляд, вона називається «Стиль посилань R1C1». У цій системі рядки та стовпці позначаються цифрами. Адреса клітинки B3 у такій системі буде мати вигляд R3C2 (R=row рядок, C=column=стовпець). Відносні, абсолютні та змішані посилання в такій системі можна реалізувати за допомогою конструкцій типу:
- RC – відносне посилання на поточну клітинку
- R2C2 – те саме, що $B$2 (абсолютне посилання)
- RC5 – посилання на клітинку з п’ятого стовпця у поточному рядку
- RC[-1] – посилання на клітинку з попереднього стовпця у поточному рядку
- RC[2] – посилання на клітинку, віддалену на два стовпці правіше в тому ж рядку
- R[2]C[-3] – посилання на клітинку, віддалену на два рядки нижче і на три стовпці ліворуч від поточної клітинки
- R5C[-2] – посилання на клітинку з п’ятого рядка, віддалену на два стовпці ліворуч від поточної клітинки
Нічого складного, просто трохи незвичайно. Такий стиль посилань іноді може бути корисним при пошуку помилок у формулі, при написанні коду макросу на VBA або під час роботи з великими таблицями.
Зазвичай цей режим може включитись сам при невдалому вивантаженні даних з деяких програм в Excel. Тому потрібно вміти повертати нормальний вигляд адреси клітинок. Для цього потрібно виконати такі дії: Файл → Параметри → Формули і далі потрібно зняти відмітку з пункту «Стиль посилання R1C1».
Ми розглянули можливі види адресації Ексель на прикладах. Якщо у вас залишилися питання, залишайте їх у коментарях та оцініть матеріал, для нас це теж важливо.
