У наступній статті ви дізнаєтесь, як дізнатися, які зовнішні посилання є у ваших таблицях Excel, і як успішно адаптувати та редагувати їх після копіювання.
Як структуруються зовнішні посилання в Excel
Зовнішні посилання завжди структуровані за однією схемою. Спочатку ви використовуєте назву книги у квадратних дужках. Після цього йде назва робочого аркуша. Після цього слідує знак оклику, після якого йде посилання на клітинку.
Якщо ви створюєте зовнішнє посилання, яке посилається на аркуш, ім’я якого містить пробіли, то назва папки та аркуша укладаються в одинарні лапки.
Якщо папка, на яку посилається зовнішнє посилання, не відкрита, шлях до цієї папки відображається перед назвою робочої папки.
В принципі, Excel завжди створює зовнішні посилання як абсолютні посилання. Ви можете зрозуміти, що вони містять знаки долара.
Як правильно скопіювати та адаптувати зовнішні посилання таблиць Excel
При копіюванні формул Excel автоматично коригує посилання, якщо вони не є абсолютними посиланнями. На жаль, це не спрацьовує, якщо посилання мають доступ до кількох інших таблиць, а назву таблиці потрібно адаптувати. Уявіть собі таку ситуацію:
Ви завжди хочете читати значення з однієї комірки з різних таблиць з іменами 01.xls, 02.xls тощо. Тоді посилання мають таку структуру:
= ’C: \ Мої файли \ [01.xls] Таблиця 1’! A1
= ’C: \ Мої файли \ [02.xls] Таблиця 1’! A1
Якщо ви копіюєте таке посилання у свою таблицю, змінюється лише посилання на адресу комірки в кінці. Однак, якщо ви хочете легко і зручно змінити всю обкладинку, працюйте за наступним методом.
Спочатку створіть стовпець, у якому можна записати змінну частину імені файлу. Після цього ви збираєте потрібну формулу, яку можна скопіювати. Це працює так:
Створіть на робочому аркуші стовпець, у якому буде введено змінну частину (у цьому випадку ім’я файлу). Ви повинні налаштувати цей стовпець із форматом текстового номера. Потім введіть змінні частини імені файлу у стовпці, як показано на наступному малюнку:
Потім введіть у комірку В1 таку формулу:
= "= 'C: \ Мої документи \ [" & A1 & ". Xls] Таблиця1'! $ A $ 1"
Скопіюйте цю формулу в комірки нижче. Результат у таблиці показаний на наступному малюнку:
Excel спочатку інтерпретує результат формули як текст. Щоб мати можливість обчислювати за допомогою цих формул, їх потрібно перетворити:
- Виділіть усі комірки з скопійованими формулами - у цьому прикладі діапазон комірок B1: B20.
- Використовуйте команду "Редагувати - Копіювати", щоб зберегти вміст комірки у буфері обміну.
- Залиште клітинки виділеними і виберіть команду "Вставити спеціально" в меню "Редагувати".
- У цьому вікні активуйте параметр "Значення".
- Підтвердьте це кнопкою "OK".
Замість формул результати тепер у клітинках. Знову залиште виділені клітинки та виберіть команду "Текст у стовпцях" у меню "Дані", щоб запустити майстер перетворення тексту.
Тут ви безпосередньо підтверджуєте початковий екран кнопкою "Готово". Потім формули складаються у потрібну форму і показують відповідний результат.
Як знайти зовнішні посилання в таблицях Excel
Якщо ви хочете знайти зовнішні посилання у своїх папках, вам не потрібно це робити вручну. Нехай Excel створить для вас огляд усіх посилань.
Це також корисно, якщо в робочій книзі є посилання, але ви не знаєте, на яке саме джерело даних йдеться.
Щоб отримати список усіх посилань у вашій книзі, виконайте наведені нижче дії.
- Активуйте робочу книгу, у якій вас цікавлять посилання.
- Excel 2010: Натисніть меню ФАЙЛ. Потім виберіть команду ПІДГОТОВИТИ - ЗМІНИТИ ПОСИЛАННЯ ДО ФАЙЛІВ.
- Excel 2007: Натисніть кнопку Office. Потім виберіть команду ПІДГОТОВИТИ - ЗМІНИТИ ПОСИЛАННЯ ДО ФАЙЛІВ.
- Excel 2003: Викличте команду EDIT - LINKS.
Excel покаже вам список посилань у вашій таблиці.
У списку посилань можна побачити всі пов’язані книги. Ви можете натиснути кнопку ВІДКРИТИЙ ДЖЕРЕЛ, щоб відкрити папки з цього списку після того, як ви натиснете відповідну назву файлу у списку.
