У Microsoft Excel звичайним завданням є посилання на клітинки на інших аркушах або навіть у різних файлах Excel. Спочатку це може здатися дещо складним і заплутаним, але коли ви зрозумієте, як це працює, це не так вже й складно.
У цій статті ми розглянемо, як посилатися на інший аркуш у тому самому файлі Excel і як посилатися на інший файл Excel. Ми також розповімо про те, як посилатися на діапазон комірок у функції, як спростити роботу за допомогою визначених імен і як використовувати VLOOKUP для динамічних посилань.
Як посилатися на інший аркуш у тому самому файлі Excel
Основне посилання на клітинку записується у вигляді літери стовпця, за якою слідує номер рядка.
Отже, посилання на клітинку B3 відноситься до клітинки на перетині стовпця B і рядка 3.
При посиланні на клітинки на інших аркушах перед цим посиланням на клітинку стоїть ім’я іншого аркуша. Наприклад, нижче наведено посилання на клітинку B3 на аркуші з назвою «Січень».
=January!B3
Знак оклику (!) відокремлює назву аркуша від адреси комірки.
Якщо назва аркуша містить пробіли, ви повинні взяти ім’я в одинарні лапки в посилання.
='January Sales'!B3
Щоб створити ці посилання, ви можете ввести їх безпосередньо в клітинку. Однак легше та надійніше дозволити Excel написати довідку за вас.
Введіть знак рівності (=) у клітинку, натисніть вкладку Аркуш, а потім клацніть клітинку, на яку потрібно створити перехресне посилання.
Коли ви це робите, Excel записує посилання для вас у рядку формул.
Натисніть Enter, щоб завершити формулу.
Як посилатися на інший файл Excel
Ви можете посилатися на клітинки іншої книги, використовуючи той самий метод. Просто переконайтеся, що у вас відкритий інший файл Excel, перш ніж почати вводити формулу.
Введіть знак рівності (=), перейдіть до іншого файлу, а потім клацніть клітинку у цьому файлі, на яку потрібно посилатися. Коли закінчите, натисніть Enter.
Заповнене перехресне посилання містить іншу назву книги, узяту в квадратні дужки, а потім назву аркуша та номер комірки.
=[Chicago.xlsx]January!B3
Якщо ім’я файлу або аркуша містить пробіли, вам потрібно взяти посилання на файл (включаючи квадратні дужки) в одинарні лапки.
='[New York.xlsx]January'!B3
У цьому прикладі ви можете побачити знаки долара ($) серед адреси осередку. Це абсолютне посилання на клітинку (дізнайтеся більше про абсолютні посилання на клітинку).
При посиланні на клітинки та діапазони в різних файлах Excel посилання стають абсолютними за замовчуванням. Ви можете змінити це на відносне посилання, якщо потрібно.
Якщо ви подивитеся на формулу, коли книга, на яку посилається, закрита, вона міститиме повний шлях до цього файлу.
Хоча створення посилань на інші книги є простим, вони більш сприйнятливі до проблем. Користувачі, які створюють або перейменовують папки та переміщують файли, можуть порушити ці посилання та викликати помилки.
Зберігати дані в одній робочій книзі, якщо це можливо, надійніше.
Як зробити перехресне посилання на діапазон комірок у функції
Посилання на одну клітинку досить корисно. Але ви можете написати функцію (наприклад, SUM), яка посилається на діапазон комірок на іншому аркуші або книзі.
Запустіть функцію, як зазвичай, а потім клацніть на аркуші й діапазоні комірок — так само, як у попередніх прикладах.
У наступному прикладі функція SUM підсумовує значення з діапазону B2:B6 на робочому аркуші з назвою Продажі.
=SUM(Sales!B2:B6)
Як використовувати визначені імена для простих перехресних посилань
У Excel ви можете призначити ім’я комірці або діапазону комірок. Це є більш значущим, ніж адреса клітинки чи діапазону, коли ви озираєтеся на них. Якщо ви використовуєте багато посилань у своїй електронній таблиці, назвати ці посилання може значно легше побачити, що ви зробили.
Навіть краще, це ім’я є унікальним для всіх аркушів у цьому файлі Excel.
Наприклад, ми можемо назвати клітинку “ChicagoTotal”, а потім перехресне посилання буде виглядати так:
=ChicagoTotal
Це більш значуща альтернатива стандартному довіднику:
=Sales!B2
Створити певну назву легко. Почніть з виділення клітинки або діапазону клітинок, яким потрібно назвати.
Клацніть поле імені у верхньому лівому куті, введіть ім’я, яке потрібно призначити, а потім натисніть Enter.
При створенні визначених імен не можна використовувати пробіли. Тому в цьому прикладі слова були об’єднані в назві та розділені великою літерою. Ви також можете розділити слова такими символами, як дефіс (-) або підкреслення (_).
У Excel також є менеджер імен, який полегшує моніторинг цих імен у майбутньому. Натисніть Формули > Менеджер імен. У вікні Менеджера імен ви можете побачити список усіх визначених імен у книзі, де вони знаходяться та які значення в даний момент зберігаються.
Потім ви можете використовувати кнопки вгорі, щоб відредагувати та видалити ці визначені імена.
Як відформатувати дані у вигляді таблиці
Під час роботи з великим списком пов’язаних даних використання функції Excel «Формат як таблиця» може спростити спосіб посилання на дані в ньому.
Візьміть наступну просту таблицю.
Це можна відформатувати як таблицю.
Клацніть клітинку в списку, перейдіть на вкладку «Головна», натисніть кнопку «Форматувати як таблицю», а потім виберіть стиль.
Переконайтеся, що діапазон комірок правильний і що ваша таблиця має заголовки.
Потім ви можете призначити значуще ім’я своїй таблиці на вкладці «Дизайн».
Потім, якщо нам потрібно було підвести підсумки продажів Чикаго, ми могли б посилатися на таблицю за її назвою (з будь-якого аркуша), а потім квадратною дужкою ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Ви можете побачити, як таблиці можуть полегшити посилання на дані для функцій агрегації, таких як SUM і AVERAGE, ніж стандартні посилання на аркуші.
Ця таблиця невелика для демонстрації. Чим більше таблиця і більше аркушів у робочій книжці, тим більше переваг ви побачите.
Як використовувати функцію VLOOKUP для динамічних посилань
Посилання, використані в прикладах до цього часу, були закріплені за певною клітинкою або діапазоном комірок. Це чудово і часто достатньо для ваших потреб.
Однак що робити, якщо клітинка, на яку ви посилаєтеся, може змінитися, коли вставляються нові рядки, або деякі