У Microsoft Excel доволі часто виникає потреба звертатися до даних, що містяться в інших аркушах або навіть у різних файлах. Хоча на перший погляд це може здатися складним, насправді все набагато простіше, варто лише розібратися з принципом роботи.
У цій статті ми детально розглянемо процес створення посилань на клітинки в межах одного файлу Excel, а також навчимося посилатися на дані з інших файлів. Крім того, ми розглянемо способи посилання на діапазони комірок у функціях, як спростити роботу за допомогою визначених імен та як використовувати функцію VLOOKUP для створення динамічних посилань.
Як створити посилання на інший аркуш у поточному файлі Excel
Стандартне посилання на клітинку складається з літери стовпця та номера рядка. Наприклад, посилання “B3” вказує на клітинку, розташовану на перетині стовпця B та рядка 3.
Для посилання на клітинки, які знаходяться на інших аркушах, потрібно перед звичайним посиланням вказати назву відповідного аркуша. Наприклад, щоб посилатися на клітинку B3 на аркуші під назвою “Січень”, необхідно використовувати наступну формулу:
=Січень!B3
Знак оклику (!) використовується для відокремлення імені аркуша від адреси клітинки.
Якщо назва аркуша містить пробіли, її потрібно взяти в одинарні лапки. Наприклад:
='Січневі Продажі'!B3
Ви можете вводити ці посилання безпосередньо в клітинку, але простіше і надійніше дозволити Excel автоматично створювати посилання за вас. Для цього введіть знак рівності (=), перейдіть на потрібний аркуш, а потім клікніть на клітинку, на яку потрібно створити посилання. Excel автоматично створить посилання у рядку формул.
Натисніть Enter для завершення формули.
Як створити посилання на інший файл Excel
Створення посилань на клітинки з інших файлів Excel відбувається за аналогічним принципом. Перед початком введення формули переконайтеся, що необхідний файл Excel відкритий.
Введіть знак рівності (=), перейдіть до потрібного файлу, а потім клікніть на клітинку, на яку потрібно посилатися. Після цього натисніть Enter.
Згенероване посилання міститиме назву іншої книги, укладену в квадратні дужки, потім назву аркуша і адресу клітинки. Наприклад:
=[Chicago.xlsx]Січень!B3
Якщо назва файлу або аркуша містить пробіли, то посилання на файл (включаючи квадратні дужки) необхідно взяти в одинарні лапки. Наприклад:
='[New York.xlsx]Січень'!B3
У цьому прикладі можна побачити знаки долара ($) в адресі клітинки. Це означає абсолютне посилання на клітинку. Детальніше про абсолютні посилання ви можете дізнатися в відповідних матеріалах.
При створенні посилань на клітинки та діапазони в інших файлах Excel, посилання за замовчуванням стають абсолютними. Якщо потрібно, це можна змінити на відносне посилання.
Якщо ви подивитеся на формулу, коли книга, на яку посилаєтеся, закрита, вона міститиме повний шлях до цього файлу.
Хоча створення посилань на інші книги не є складним, вони більш вразливі до проблем. Користувачі, які створюють, перейменовують папки або переміщують файли, можуть порушити ці посилання і викликати помилки. Тому рекомендується зберігати дані в одній робочій книзі, якщо це можливо, оскільки це більш надійно.
Як посилатися на діапазон клітинок у функції
Посилання на окремі клітинки може бути корисним, але часто виникає потреба використовувати діапазони комірок у функціях. Наприклад, можна використовувати функцію SUM для підсумовування значень з діапазону клітинок, розташованих на іншому аркуші або в іншій книзі.
Щоб створити таке посилання, введіть функцію як зазвичай, а потім перейдіть на потрібний аркуш та виділіть необхідний діапазон клітинок, як і в попередніх прикладах.
У наступному прикладі, функція SUM підсумовує значення з діапазону B2:B6 на робочому аркуші під назвою “Продажі”.
=SUM(Продажі!B2:B6)
Використання визначених імен для спрощення посилань
В Excel можна присвоювати імена клітинкам або діапазонам. Це значно зручніше, ніж використовувати адреси клітинок, особливо коли ви часто використовуєте ці посилання. Присвоєння імен посилання дозволяє зробити вашу електронну таблицю більш зрозумілою, особливо якщо ви маєте справу з великою кількістю посилань.
Перевагою визначених імен є те, що вони унікальні для всіх аркушів у файлі Excel.
Наприклад, ви можете назвати клітинку “ChicagoTotal”. В цьому випадку, посилання на цю клітинку виглядатиме так:
=ChicagoTotal
Це набагато більш зрозуміла альтернатива стандартному посиланню:
=Продажі!B2
Створити визначене ім’я досить легко. Спочатку потрібно виділити клітинку або діапазон, якому ви хочете надати ім’я.
Потім клацніть на поле імені у верхньому лівому куті, введіть потрібне ім’я та натисніть Enter.
При створенні визначених імен не можна використовувати пробіли. У цьому прикладі слова об’єднано в назві та розділено великою літерою. Також можна використовувати такі символи, як дефіс (-) або підкреслення (_).
В Excel є менеджер імен, який полегшує відстеження цих імен. Щоб скористатися ним, натисніть “Формули” > “Менеджер імен”. У вікні менеджера можна переглянути список всіх визначених імен у книзі, їхні діапазони та поточні значення.
У верхній частині вікна менеджера імен є кнопки для редагування та видалення цих імен.
Форматування даних у вигляді таблиці
При роботі з великими списками даних, функція Excel “Форматувати як таблицю” може значно спростити спосіб звернення до цих даних. Розглянемо простий приклад таблиці:
Цю таблицю можна відформатувати як таблицю Excel.
Щоб це зробити, клацніть на будь-яку клітинку в списку, перейдіть на вкладку “Головна”, натисніть кнопку “Форматувати як таблицю” і виберіть потрібний стиль.
Переконайтеся, що діапазон клітинок вказано правильно і що таблиця має заголовки.
На вкладці “Конструктор” можна присвоїти таблиці зрозуміле ім’я.
Після цього, якщо потрібно підсумувати продажі в Чикаго, можна звернутися до таблиці за її назвою, а потім скористатися квадратними дужками ([), щоб побачити список стовпців таблиці.
Вибравши необхідний стовпець і закривши квадратні дужки, ми отримаємо формулу, подібну до наступної:
=SUM(Продажі[Чикаго])
Як бачите, використання таблиць значно полегшує створення посилань на дані для функцій агрегації, таких як SUM та AVERAGE, у порівнянні зі звичайними посиланнями на аркуші. Чим більша таблиця і чим більше аркушів у робочій книзі, тим більше переваг ви отримаєте від цього підходу.
Використання функції VLOOKUP для створення динамічних посилань
Посилання, які використовувалися у попередніх прикладах, були прив’язані до конкретних клітинок або діапазонів. Це часто є достатнім для більшості потреб. Проте, що робити, якщо клітинка, на яку ви посилаєтеся, може змінитися через вставлення нових рядків, або якщо її положення залежить від інших параметрів?