Відносні, абсолютні та змішані посилання на комірки

Посилання на комірку в програмах для роботи з електронними таблицями, таких як Excel і Google Sheets, визначає місце розташування комірки на аркуші.

Комірка - це одна з коробчастих структур, що заповнюють аркуш, і ви можете знайти її за її посиланнями, наприклад, A1, F26 або W345. Посилання на комірку складається з літери стовпчика і номера рядка, які перетинаються в розташуванні комірки. При перерахуванні посилання на комірку буква стовпчика завжди з'являється першою.

Посилання на комірки відображаються у формулах, функціях, діаграмах та інших командах Excel.

Інформація в цій статті відноситься до версій Excel 2019, 2016, 2013, Excel для Mac, Excel Online і Google Sheets.

Використання посилання на комірку включає автоматичне оновлення

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

Якщо робоча книга налаштована на автоматичне оновлення при внесенні змін до робочої таблиці, ви можете виконати оновлення вручну, натиснувши клавішу F9 на клавіатурі.

Посилання на комірки з різних аркушів

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

Вам не потрібна нотація аркуша, якщо ви посилаєтеся на комірку в тому ж аркуші.

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

Щоб послатися на комірку на іншому аркуші, перед посиланням на комірку вкажіть «Sheet [число]» з знаком оклику після неї, а потім ім'я комірки. Тому, якщо ви хочете отримати інформацію з комірки A1 на аркуші 3, ви наберете "Sheet3! A1 ».

Запис, що посилається на іншу книгу в Excel, також містить назву книги в дужках. Щоб використовувати інформацію, що міститься в комірці B2 у Аркуші 2 Робочої книги 2, введіть "[Book2] Sheet2! B2 ».

Стільниковий діапазон

Хоча посилання часто відносяться до окремих комірок, таких як A1, вони також можуть відноситися до групи або діапазону комірок. Ви визначите діапазони комірок за початковою і кінцевою комірками. У випадку діапазонів, які займають декілька рядків і стовпчиків, ви будете використовувати посилання на комірки комірок у верхньому лівому і нижньому правому кутах діапазону.

Розділіть межі діапазону комірок двокрапкою (:), яке вказує Excel або Google Sheets увімкнути всі комірки між цими початковими і кінцевими точками. Таким чином, щоб перехопити всі між комірками A1 і D10, ви повинні ввести "A1: D10 ».

Щоб захопити весь рядок або стовпчик, ви, як і раніше, використовуєте позначення діапазону комірок, але використовуєте лише номери стовпчиків або літери рядків. Щоб включити всі в стовпчик A, діапазон буде "A: A ». Щоб використовувати рядок 8, ви наберете "8: 8 ». Для всього, що в стовпчиках з B по D, ви наберете "B: D ».

Відносні, абсолютні та змішані посилання на комірки

Три типи посилань, які можна використовувати в Excel і Google Sheets, легко визначаються за наявністю або відсутністю знаків долара ($) на посиланні на комірку. Знак долара говорить програмі використовувати це значення при кожному запуску формули.

• Відносні посилання на комірки не містять знаків долара (наприклад, A1).
• Посилання на змішані комірки мають знаки долара, прикріплені або до букви, або до номера на посиланні, але не до обох (тобто $ A1 або A $1).
• Абсолютні посилання на комірки мають знаки долара, прикріплені до кожної букви або номеру на посиланні (тобто $ A $1).

Зазвичай ви використовуєте абсолютне або змішане посилання на комірку, якщо налаштовуєте формулу. Наприклад, якщо у вас є число в комірці A1, більше чисел у стовпчику B, а стовпчик C містить суми A1 і кожне зі значень в B, ви будете використовувати «$ A $1» у формулі SUM, щоб при Ви заповнюєте автоматично, програма знає, що завжди використовувати число в A1 замість порожніх комірок під ним.

Копіювання формул і посилань на різні комірки

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

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

Іноді посилання на комірки повинні залишатися незмінними при копіюванні формул. Копіювання формул є ще одним основним використанням абсолютного посилання, такий як = $ A $2 + $ A $4. Значення в цих посиланнях не змінюються при їх копіюванні.

В інших випадках може знадобитися змінити частину посилання на комірку, наприклад літеру стовпчика, при цьому номер рядка залишається незмінним або навпаки при копіюванні формули. У цьому випадку ви будете використовувати посилання на змішану комірку, наприклад = $ A2 + A $4. До якої б частини посилання не був прикріплений знак долара, вона залишається незмінною, а інша частина змінюється при копіюванні.

Таким чином, для $ A2 під час копіювання літера стовпчика завжди A, але номери рядків змінюються на $ A3, $ A4, $ A5 тощо.

Рішення використовувати різні посилання на комірки при створенні формули ґрунтується на розташуванні даних, які будуть використовуватися в скопійованих формулах.

Перемикання між типами посилань на комірки

Найпростіший спосіб змінити посилання на комірки з відносних на абсолютні або змішані - це натиснути клавішу F4 на клавіатурі. Щоб змінити існуючі посилання на комірки, Excel повинен бути в режимі редагування, який ви вводите, двічі клацнувши по комірці покажчиком миші або натиснувши клавішу F2 на клавіатурі.

Щоб перетворити відносні посилання на комірки на абсолютні або змішані посилання на комірки:

• Натисніть «F4», щоб створити посилання на комірку повністю абсолютне, наприклад, $ A $6.
• Натисніть F4 вдруге, щоб створити змішане посилання, де номер рядка є абсолютним, наприклад, A $6.
• Натисніть «F4» втретє, щоб створити змішане посилання, в якому літера стовпчика є абсолютною, наприклад, $ A6.
• Натисніть F4 вчетверте, щоб знову зробити посилання на комірку відносним, наприклад, A6.