Щоб зробити точні обчислення в Excel, важливо розуміти, як працюють різні типи посилань на клітинки.
A1 проти R1C1 Посилання
Робочі листи Excel містять багато клітинок, і (за замовчуванням) кожна клітинка ідентифікується своєю колонка листа а потім номер рядка. Це відоме як посилання у стилі А1. Приклади: A1, B4, C6
Стиль посилання А1
За бажанням можна перейти до Довідковий режим R1C1 посилання на рядок клітинки & номер стовпця. Замість того, щоб посилатися на клітинку A1, ви б зверталися до R1C1 (рядок 1, стовпець 1). Клітинку С4 буде називати R4C3.
Стиль посилання R1C1
Посилання у стилі R1C1 надзвичайно рідкісне в Excel. Якщо у вас немає вагомих причин, ви, ймовірно, повинні дотримуватися стандартного режиму посилання у стилі А1. Однак, якщо ви використовуєте VBA, ви, ймовірно, зустрінете цей стиль посилання.
Перейдіть до стилю посилання R1C1
Щоб змінити стиль посилання, перейдіть до Файл> Параметр> Формула. Поставте прапорець біля Стиль посилання R1C1.
Іменовані діапазони
Однією з найбільш недостатньо використовуваних функцій Excel є функція Іменовані діапазони. Замість того, щоб посилатися на клітинку (або групу клітинок) за її розташуванням (наприклад, B3 або R3C2), ви можете назвати цей діапазон і просто посилатися на назву діапазону у ваших формулах.
Щоб назвати діапазон:
- Виберіть клітинку або клітинки, які потрібно назвати
- Натисніть усередині вікна імені діапазону
- Введіть бажане ім'я
- Натисніть Enter
Тепер ви можете посилатися на клітинку A1, ввівши натомість = range_name1. Це дуже корисно при роботі з великими книгами з кількома аркушами.
Діапазон клітин
При використанні вбудованих функцій Excel вам може знадобитися посилатися на діапазони клітинок. Діапазони клітинок виглядають так: "A1: B3". Це посилання відноситься до всіх клітин між A1 і B3: клітини A1, A2, A3, B1, B2, B3.
Щоб вибрати діапазон клітинок під час введення формули:
- Введіть діапазон (розділіть початковий і кінцевий діапазон крапкою з комою)
- Клацніть мишею на першому посиланні на клітинку, утримуйте кнопку миші та перетягніть потрібний діапазон.
- Утримуйте клавішу Shift і переміщайтесь за допомогою клавіш зі стрілками, щоб вибрати свій діапазон
Абсолютні (заморожені) та відносні посилання
При введенні посилань на клітинки у формулах можна використовувати відносні або абсолютні (заморожені) посилання. Відносні посилання будуть переміщуватися пропорційно, коли ви скопіюєте формулу в нову клітинку. Абсолютні посилання залишаться незмінними. Розглянемо деякі приклади:
Відносна довідка
Відносне посилання в Excel виглядає так
= A1
Коли ви копіюєте та вставляєте формулу з відносними посиланнями, відносні посилання рухатимуться пропорційно. Якщо ви не вкажете інше, ваші посилання на клітинки будуть відносними (незамороженими) за замовчуванням.
Приклад: Якщо скопіювати "= A1" вниз на один рядок, посилання зміниться на "= A2".
Абсолютні (заморожені) посилання на клітинки
Якщо ви не хочете, щоб посилання на клітинку переміщувалися під час копіювання формули, ви можете "заморозити" посилання на клітинку, додавши знаки долара ($ s) перед посиланням, яке потрібно заморозити. Тепер, коли ви копіюєте та вставляєте формулу, посилання на клітинку залишається незмінним. Ви можете заморозити посилання на рядок, посилання на стовпець або обидва.
A1: Ніщо не замерзло
$ A1: Стовпець заморожено, але рядок не застиг
A $ 1: Рядок заморожено, але стовпець не заморожено
$ A $ 1: Рядок і стовпець заморожені
Абсолютна посилання
Додавання знаків долара ($ s) вручну у ваші формули не дуже практично. Натомість під час створення формули використовуйте клавішу F4 для переключення між абсолютними/відносними посиланнями на клітинки.
Приклад абсолютного посилання на клітинку
Коли насправді потрібно заморозити посилання на клітинку? Один із поширених прикладів - коли у вас є клітинки введення, на які часто посилаються. У наведеному нижче прикладі ми хочемо розрахувати податок з продажів для кожної кількості пунктів меню. Податок з продажів є незмінним для всіх позицій, тому ми будемо неодноразово посилатися на комірку податку з продажів.
Щоб знайти загальний податок з продажів, введіть формулу ‘= (B3*C3)*$ C $ 1’ у стовпець D і скопіюйте формулу вниз.
Змішана довідка
Можливо, ви чули про змішані посилання на клітинки. Змішане посилання - це коли посилання на рядок або стовпець заблоковано (але не обидва).
Змішана довідка
Пам’ятайте, що за допомогою клавіші “F4” ви можете переходити між цими відносними абсолютними посиланнями на клітинки.
Посилання на клітинки - Вставка та видалення рядків/стовпців
Вам може бути цікаво, що відбувається з посиланнями на клітинки, коли ви вставляєте або видаляєте рядки/стовпці?
Посилання на клітинку буде автоматично оновлюватися, посилаючись на вихідну клітинку. Це так, незалежно від того, заморожена посилання на клітинку.
3D -посилання
Іноді вам може знадобитися працювати з кількома аркушами з однаковими зразками даних. Excel дозволяє звертатися до кількох аркушів одночасно без необхідності вводити кожен аркуш вручну. Ви можете посилатися на діапазон аркушів, подібний до того, як би посилатися на діапазон клітинок. Приклад "Аркуш1: Аркуш5! А1" посилатиметься на клітинки А1 на всіх аркушах від Листа1 до Листа5.
Пройдемося по прикладу:
Ви хочете скласти загальну суму проданих одиниць для кожного товару у всіх магазинах. Кожен магазин має власний аркуш, і всі аркуші мають однаковий формат. Ви можете створити формулу, подібну до цієї:
Це не надто складно лише з чотирма аркушами, але що, якби у вас було 40 аркушів? Ви дійсно хочете вручну додати кожне посилання на клітинку?
Замість цього, ви можете використовувати 3D -посилання для посилання на кілька аркушів одночасно (подібно до того, як ви можете посилатися на діапазон клітинок).
Будь обережний! Порядок ваших аркушів має значення. Якщо ви перемістите інший аркуш між аркушами, на які посилається (StoreA і StoreD), цей аркуш буде включений. І навпаки, якщо ви перемістите аркуш за межі діапазону аркушів (до StoreA або після StoreD), він більше не буде включений.
Довідка про круглі клітинки
Посилання на кругову клітинку - це коли клітина посилається на себе. Наприклад, якщо результат комірки B1 використовується як вхідний сигнал для комірки B1, то створюється кругове посилання. Клітинка не потребує прямого посилання на себе. Можуть бути проміжні кроки.
Приклад:
У цьому випадку формула для комірки В2 - «А2+А3+В2». Оскільки ви знаходитесь у клітинці В2, ви не можете використовувати В2 у рівнянні. Це спричинить кругове посилання, і значення у комірці “B2” автоматично встановиться на “0”.
Зазвичай кругові посилання є результатом помилки користувача, але в деяких випадках ви можете скористатися циркулярним посиланням. Основним прикладом використання кругового посилання є обчислення значень ітеративно. Для цього вам потрібно перейти до Файл> Параметри> Формули та Увімкнено ітеративний розрахунок:
Зовнішні посилання
Іноді при обчисленні даних вам може знадобитися посилатися на дані поза робочою книгою. Це називається зовнішнім посиланням (посиланням).
Щоб вибрати зовнішнє посилання під час створення формули, перейдіть до зовнішньої книги та виберіть посилання, як зазвичай.
Для переходу до іншої книги можна скористатися комбінацією клавіш CTRL + TAB або перейти до Перегляд> Переключити Windows.
Вибравши комірку, ви побачите зовнішнє посилання, яке виглядає так:
Зверніть увагу, що назва робочої книги укладено в дужки [].
Після закриття книги, на яку посилається, у довідці буде показано розташування файлу:
Коли ви знову відкриєте книгу, що містить зовнішнє посилання, вам буде запропоновано ввімкнути автоматичне оновлення посилань. Якщо ви це зробите, Excel відкриє еталонне значення з поточним значенням книги. Навіть якщо він закритий! Будь обережний! Ви можете, а можете і не хотіти цього.
Іменовані діапазони та зовнішні посилання
Що відбувається з вашим зовнішнім посиланням на клітинку, коли рядки / стовпці додаються або видаляються з довідкової книги? Якщо обидві книги відкриті, посилання на клітинки оновлюються автоматично. Однак, якщо обидві книги не відкриті, посилання на клітинки не оновлюватимуться і більше не будуть дійсними. Це викликає велику стурбованість при посиланні на зовнішні книги. Через це виникає багато помилок.
Якщо ви маєте посилання на зовнішні книги, вам слід назвати посилання на клітинку з іменованим діапазоном (див. попередній розділ для отримання додаткової інформації). Тепер ваша формула буде посилатися на іменований діапазон незалежно від того, які зміни відбуваються у зовнішній книзі.
