Робочий лист у Excel складається з клітинок. На ці клітинки можна посилатися, вказавши значення рядка та значення стовпця.
Наприклад, A1 буде посилатися на перший рядок (зазначений як 1) та перший стовпець (зазначений як A). Аналогічно, B3 буде третім рядком і другим стовпцем.
Сила Excel полягає в тому, що ви можете використовувати ці посилання на клітинки в інших осередках під час створення формул.
Тепер у Excel є три типи посилань на клітинки:
- Посилання на відносні клітинки
- Абсолютні посилання на клітинки
- Посилання на змішані клітинки
Розуміння цих різних типів посилань на клітинки допоможе вам працювати з формулами та заощадить час (особливо при формулах для копіювання).
Що таке відносні посилання на клітинки в Excel?
Дозвольте мені взяти простий приклад для пояснення концепції відносних посилань на клітинки в Excel.
Припустимо, у мене є набір даних, показаний нижче:
Щоб обчислити загальну суму по кожному товару, нам потрібно помножити ціну кожного товару на кількість цього товару.
Для першого пункту формула у комірці D2 буде B2* C2 (як показано нижче):
Тепер, замість того, щоб вводити формулу для всіх клітинок по черзі, ви можете просто скопіювати комірку D2 і вставити її у всі інші клітинки (D3: D8). Коли ви це зробите, ви помітите, що посилання на клітинку автоматично налаштовується відповідно до відповідного рядка. Наприклад, формула у комірці D3 перетворюється на B3*C3, а формула в D4 - на B4*C4.
Викликаються ці посилання на клітинки, які налаштовуються під час копіювання комірки відносні посилання на клітинки в Excel.
Коли використовувати відносні посилання на клітинки в Excel?
Відносні посилання на клітинки корисні, коли вам потрібно створити формулу для діапазону клітинок, а формула має посилатися на відносну посилання на клітинку.
У таких випадках можна створити формулу для однієї комірки та скопіювати та вставити її у всі клітинки.
Що таке абсолютні посилання на клітинки в Excel?
На відміну від відносних посилань на клітинки, абсолютні посилання на клітинки не змінюються під час копіювання формули в інші клітинки.
Наприклад, припустимо, що у вас є набір даних, як показано нижче, де потрібно розрахувати комісію за загальний обсяг продажів кожного товару.
Комісія становить 20% і вказана в комірці G1.
Щоб отримати суму комісії за кожний продаж товару, скористайтеся такою формулою в комірці E2 і скопіюйте для всіх клітинок:
= D2*$ G $ 1
Зауважте, що у довідці комірки є два знаки долара ($), які мають комісію - $G$2.
Що робить знак долара ($)?
Символ долара, доданий перед номером рядка та стовпця, робить його абсолютним (тобто зупиняє зміну номера рядка та стовпця при копіюванні в інші клітинки).
Наприклад, у наведеному вище випадку, коли я копіюю формулу з комірки E2 на E3, вона змінюється з = D2*$ G $ 1 на = D3*$ G $ 1.
Зауважте, що хоча D2 змінюється на D3, $ G $ 1 не змінюється.
Оскільки ми додали символ долара перед "G" та "1" у G1, це не дозволить змінити посилання на клітинку під час копіювання.
Отже, це робить посилання на клітинку абсолютною.
Коли використовувати абсолютні посилання на клітинки в Excel?
Абсолютні посилання на клітинки корисні, коли ви не хочете, щоб посилання на клітинку змінювалося під час копіювання формул. Це може бути у випадку, якщо у вас є фіксоване значення, яке потрібно використати у формулі (наприклад, ставка податку, ставка комісії, кількість місяців тощо).
Хоча ви також можете жорстко закодувати це значення у формулі (тобто використати 20% замість $ G $ 2), його розміщення в комірці, а потім використання посилання на комірку дозволяє змінити його в майбутньому.
Наприклад, якщо ваша структура комісії змінюється, і ви зараз виплачуєте 25% замість 20%, ви можете просто змінити значення в комірці G2, і всі формули автоматично оновляться.
Що таке посилання на змішані клітинки в Excel?
Змішані посилання на клітинки трохи складніше, ніж абсолютні та відносні посилання на клітинки.
Змішані посилання на клітинки можуть бути двох типів:
- Рядок блокується, коли стовпець змінюється під час копіювання формули.
- Стовпець блокується, а рядок змінюється під час копіювання формули.
Давайте подивимось, як це працює, на прикладі.
Нижче наведено набір даних, де вам потрібно розрахувати три рівні комісії на основі відсоткового значення у комірці E2, F2 та G2.
Тепер ви можете використовувати потужність змішаного посилання для розрахунку всіх цих комісій лише за однією формулою.
Введіть формулу нижче в клітинку E4 і скопіюйте для всіх клітинок.
= $ B4*$ C4*E $ 2
У наведеній вище формулі використовуються обидва види змішаних посилань на клітинки (одна, де рядок заблокований, а інший, де стовпець заблоковано).
Давайте проаналізуємо кожне посилання на клітинку і зрозуміємо, як це працює:
- $ B4 (і $ C4) - У цьому посиланні знак долара знаходиться прямо перед позначенням у стовпці, але не перед номером рядка. Це означає, що під час копіювання формули у клітинки праворуч посилання залишатиметься незмінним із фіксованим стовпцем. Наприклад, якщо скопіювати формулу з E4 до F4, це посилання не зміниться. Однак, коли ви копіюєте його, номер рядка зміниться, оскільки він не заблокований.
- 2 долари США - У цьому посиланні знак долара знаходиться прямо перед номером рядка, а позначення стовпця не має знаку долара. Це означає, що під час копіювання формули вниз по клітинках посилання не зміниться, оскільки номер рядка заблоковано. Однак, якщо скопіювати формулу праворуч, алфавіт стовпця зміниться, оскільки він не заблокований.
Як змінити посилання з відносного на абсолютне (або змішане)?
Щоб змінити посилання з відносного на абсолютне, перед символом стовпця та номером рядка потрібно додати знак долара.
Наприклад, A1 є відносним посиланням на клітинку, і воно стане абсолютним, якщо ви зробите це $ A $ 1.
Якщо вам потрібно змінити лише кілька посилань, вам може бути легко змінити ці посилання вручну. Таким чином, ви можете перейти до рядка формул і відредагувати формулу (або вибрати клітинку, натиснути F2, а потім змінити її).
Однак швидше це зробити за допомогою комбінації клавіш - F4.
Коли ви вибираєте посилання на клітинку (у рядку формул або в клітинці в режимі редагування) і натискаєте F4, вона змінює посилання.
Припустимо, у вас є посилання = A1 у комірці.
Ось що відбувається, коли ви вибираєте посилання та натискаєте клавішу F4.
- Натисніть клавішу F4 один раз: Посилання на клітинку змінюється з A1 на $ A $ 1 (стає «абсолютним» від «відносного»).
- Натисніть клавішу F4 двічі: Посилання на клітинку змінюється з A1 на A $ 1 (змінюється на змішане посилання, де рядок заблоковано).
- Натисніть клавішу F4 тричі: Посилання на клітинку змінюється з A1 на $ A1 (змінюється на змішане посилання, де стовпець заблоковано).
- Натисніть клавішу F4 чотири рази: Посилання на клітинку знову стає A1.
