Як зробити гіперпосилання на інший лист або файл Excel

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

Ви могли не знати, але існує різниця між посиланням на лист і зовсім інший файл Excel.

А також є кілька невеликих моментів, які необхідно знати, якщо ви користуєтеся посиланнями.

Але нічого складного там нема, не переживайте.

Коли ви дочитаєте статтю — ви розумітимете все, що стосується гіперпосилань в Excel.

Отже, почнемо!

Посилання на осередок

Це знають всі, хто коли-небудь працював у Excel.

Так виглядає посилання на комірку:

=A1

Як зробити гіперпосилання на інший лист або файл Excel

Якщо в першому осередку стовпчика А знаходилося значення «100», то при посиланні =A1, вставленої в осередок стовпчика В ви отримаєте те саме значення і В.

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

У такому разі, посилання виглядатиме так:

Аркуш!

Допустимо, нам потрібно зробити посилання на А2, яке знаходиться на аркуші з ім'ям Лист2:

= Лист2! A2

Посилання на діапазон виглядатиме так:

Аркуш!Діапазон:Діапазон

Ось приклад:

= Аркуш2!A2:C11

Так само ви можете використовувати осередки з інших листів у розрахунках, головне правильно вказуєте посилання.

Якщо аркуш має прогалини в назві – просто укласти назву в одинарні лапки.

Ось приклад:

='Обсяг продажів'!A2

Якщо ви використовуєте посилання і раптом змінили назву листа – не переживайте, Excel сам пропише нові назви посилання.

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

Отже, як створювати посилання найбільш безпечно і зручно?

Автоматичне створення посилання на інший аркуш у тій самій робочій книзі

Найоптимальніший, на мою думку, варіант — просто вказати діапазон осередків і Excel сам створить посилання на них.

Таким чином, ви виключите помилки, пов'язані, наприклад, з синтаксисом і так далі.

А як створити посилання на діапазон осередків в іншому аркуші?

Покрокова інструкція:

  1. Виберіть комірку;
  2. Поставте одно;
  3. Виберіть аркуш;
  4. Виділіть діапазон осередків (або осередок), на які потрібно послатися;
  5. Підтвердіть.

Готово!

А як використовувати такі посилання у розрахунках?

Ось приклад:

  • Прописуємо = сум;

Як зробити гіперпосилання на інший лист або файл Excel

  • Прописуємо потрібну назву листа;
  • А також діапазон;

Як зробити гіперпосилання на інший лист або файл Excel

  • Підтверджуємо.

Отже, припустимо, у вашій формулі є кілька аргументів. У першому ви використовували посилання на комірку з іншого аркуша. А тепер, у другому аргументі, хочете використати посилання на комірку поточного аркуша. Як це зробити?

Ви повинні зрозуміти, якщо ви не вказуєте посилання на будь-яку комірку, Excel автоматично розпізнає це як вказівку того, що потрібно використовувати поточний лист.

Великий плюс Excel у тому, що якщо ви скопіюєте формулу і помістите її на іншому аркуші, він автоматично пропише правильно цю формулу.

Це може бути складно, але розглянемо приклад.

Допустимо ми посилаємося на:

=SalesData!A1

Якщо ми скопіюємо і вставимо це в наступний осередок, то вийде:

=SalesData!A2

Формула відносна та Excel автоматично виправляє формулу на правильну.

Якщо ви хочете зробити так, що формула не змінювалася, пропишіть перед стовпчиком та номером рядка долар:

=SalesData!$A$1

Долар повідомляє Excel, що формула є абсолютною і коригувати її не потрібно.

Ну з цим все зрозуміло скажете ви, а як зробити посилання на інший файл?

Як зробити гіперпосилання на інший файл в Excel

Якщо ви посилаєтеся на інший файл Excel, вам потрібно знати відкритий це файл чи ні.

Розглянемо кілька прикладів.

Посилання на комірку в іншому файлі Excel

Отже, нам потрібно буде вказати ім'я файлу, листа та адресу осередку.

Ось приклад

='[Ім'я_файлу]Аркуш!Адреса_осередку

Допустимо у нас є таблиця Excel.xlsx, а в ньому є лист Продажу

Ось приклад використання функції:

=[Excel.xlsx]Продажи!A1

Якщо в назві є пробіли або символи (№; %: # і так далі), не забувайте використовувати одинарні лапки.

Приклад:

='[Excel.xlsx]Продажи!A1 ='[Excel.xlsx]Продажи'!A1 ='[Excel.xlsx]Продажи'!A1

Автоматично створюємо посилання на інший файл

Тут теж саме – краще користуватися автоматичними методами.

Це дозволить уникнути великої кількості помилок з необережності.

Як створити посилання на файл?

Покрокова інструкція:

  • Почніть вводити формулу (=сум або =sum в англ. версії Excel);
  • Вкажіть назву файлу;
  • Виберіть аркуш та діапазон;

Як зробити гіперпосилання на інший лист або файл Excel

  • Підтвердіть.

Готово! Посилання створено.

Якщо ви спробували створити таке посилання, ви помітили, що створюється абсолютний тип посилання. Якщо ви скопіюєте формулу, вона не зміниться.

Бувають, звичайно, ситуації, коли потрібно зробити так, щоб посилання змінювалося. У такому разі вам потрібно видалити знаки долара з формули.

Посилання на комірку у закритому файлі Excel

Отже, в попередньому прикладі файл був відкритий і нам достатньо було вказати його назву в посиланні. Таким чином, Excel одразу розумів, що нам потрібно. Але як бути, якщо файл не відкритий? Адже в такому разі ми не можемо вказати просто його назву.

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

Ось приклад:

='C:UsersExcelDesktop[Example File.xlsx]Письмо1'!$A$1

Що використовується у формулі:

  • Повний шлях до файлу Excel;
  • Назва файлу;
  • Назва листа;
  • І адреса осередку.

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

Зміна посилання після перенесення файлу в іншу директорію

Отже, ми розібралися, що коли файл в Excel закритий вам також потрібен шлях до файлу.

Але якщо ви кудись перемістите файл, на який посилаєтеся. Посилання “зламається”. Просто тому, що не зможе знайти файл за вказаним шляхом.

Якщо ви відкриєте файл заново, побачите наступне вікно, що пропонує вам автоматично оновити всі застарілі посилання:

Як зробити гіперпосилання на інший лист або файл Excel

Також буде відкрито наступне вікно:

Як зробити гіперпосилання на інший лист або файл Excel

Якщо Excel не оновить посилання, вам доведеться зробити це вручну. Натиснувши кнопку «Оновити посилання» та вказавши нове розташування файлу.

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

Посилання на іменований діапазон

Якщо вам часто потрібно використовувати діапазони осередків, наприклад, у формулах, ви можете створити іменований діапазон і замість вказівки самого діапазону осередків у кожній формулі просто писати його ім'я.

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

Як її створити?

Покрокова інструкція:

  • Виділіть діапазон осередків та клацніть на «Формули»;

Як зробити гіперпосилання на інший лист або файл Excel

  • Далі – “Присвоїти ім'я”;

Як зробити гіперпосилання на інший лист або файл Excel

  • Привласніть йому ім'я у вікні.

Як зробити гіперпосилання на інший лист або файл Excel

  • У параметрі “Область” вкажіть “Книга”;

Як зробити гіперпосилання на інший лист або файл Excel

  • Перевірте чи прописаний діапазон;

Як зробити гіперпосилання на інший лист або файл Excel

  • Підтвердіть.

Готово, тепер замість того, щоб у кожній формулі писати діапазон осередків, можете просто прописати його ім'я.

Ось приклад (діапазон називається SalesData):

=СУМ(SalesData)

Але якщо вам необхідно обчислювати що-небудь на інших аркушах або файлах, чи можна зробити так?

Звичайно ж да.

Просто робіть так само, як ми розглядали в попередніх розділах.

Посилання на іменований діапазон в іншому файлі Excel

Все дуже просто. Перш ніж вказувати іменований діапазон, вкажіть ім'я файлу (якщо він відкритий).

Ось приклад:

=СУМ(ExampleFile.xlsx!SalesData)

У назві файлу є пробіл? Укладіть його в лапки, приклад нижче:

=СУМ('Example File.xlsx'!SalesData)

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

=СУМ('[Example File.xlsx]Sheet1'!SalesData)

Отже, раніше я вже говорив – краще створювати іменований діапазон відразу на рівні файлу, але це все одно на ваш розсуд.

Якщо файл, у якому знаходиться іменований діапазон, закритий, то вкажіть шлях:

=СУММ('C:UsersExcelDesktopExample File.xlsx'!SalesData)

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

Як не заплутатися в іменованих діапазонах

Якщо ви часто створюєте іменовані діапазони – ви можете легко заплутатися в них. У такому разі працювати стане дуже складно. Що ж робити?

Розглянемо приклад.

Допустимо, у вас є іменований діапазон, ви хочете використовувати його у функції.

Як це зробити?

Покрокова інструкція:

  • Виділіть комірку та введіть формулу до того аргументу, де потрібно використовувати іменований діапазон;

Як зробити гіперпосилання на інший лист або файл Excel

  • Далі натисніть F3, відкриється вікно з усіма наявними іменованими діапазонами.

Як зробити гіперпосилання на інший лист або файл Excel

  • І оберіть той, який потрібно вставити.

Готово! Діапазон вставлено у функцію.

Так можна робити лише з тими іменованими діапазонами, які ви створили у поточному аркуші/файлі Excel. Якщо ви хочете послатися на діапазон з іншого файлу, вам доведеться зробити це вручну.

Ось і все, я розповів вам усі важливі моменти.

Сподіваюся, що вам допомогла ця стаття.

Читайте також
nBook - найцікавіше зі світу IT, Hi-Tech