В следующей статье вы узнаете, как узнать, какие внешние ссылки находятся в ваших таблицах Excel, и как их успешно адаптировать и редактировать после копирования.
Как внешние ссылки структурированы в Excel
Внешние ссылки всегда структурированы по одной и той же схеме. Сначала вы используете название книги в квадратных скобках. За ним следует имя рабочего листа. После этого следует восклицательный знак и ссылка на ячейку.
Если вы создаете внешнюю ссылку, которая ссылается на рабочий лист, имя которого содержит пробелы, то имя папки и листа заключаются в одинарные кавычки.
Если папка, на которую ссылается внешняя ссылка, не открыта, путь, по которому эта папка может быть найден, отображается перед именем рабочей папки.
По сути, Excel всегда создает внешние ссылки как абсолютные. Об этом можно судить по тому, что они содержат знаки доллара.
Как правильно копировать и адаптировать внешние ссылки таблиц Excel
При копировании формул Excel автоматически корректирует ссылки, если они не являются абсолютными. К сожалению, это не работает, если ссылки обращаются к нескольким другим таблицам и имя таблицы нужно изменить. Представьте себе следующую ситуацию:
Вы всегда хотите читать значения из одной и той же ячейки из разных таблиц с именами 01.xls, 02.xls и т. Д. Тогда ссылки имеют следующую структуру:
= ’C: \ My files \ [01.xls] Таблица 1’! A1
= ’C: \ Мои файлы \ [02.xls] Таблица 1’! A1
Если вы скопируете такую ссылку в свою таблицу, корректируется только ссылка на адрес ячейки в конце. Однако, если вы хотите легко и удобно поменять всю крышку, действуйте следующим образом.
Сначала создайте столбец, в который можно записать переменную часть имени файла. Затем вы составляете желаемую формулу, которую можете скопировать. Вот как это работает:
Создайте на листе столбец, в который будет вводиться переменная часть (в данном случае имя файла). Вы должны настроить этот столбец с форматом текстовых чисел. Затем введите переменные части имени файла в столбец, как показано на следующем рисунке:
Затем введите следующую формулу в ячейку B1:
= "= 'C: \ Мои документы \ [" & A1 & ". Xls] Table1'! $ A $ 1"
Скопируйте эту формулу в ячейки ниже. Результат в таблице показан на следующем рисунке:
Excel изначально интерпретирует результат формулы как текст. Чтобы иметь возможность производить вычисления по этим формулам, вам необходимо преобразовать их:
- Выделите все ячейки с скопированными формулами - в этом примере диапазон ячеек B1: B20.
- Используйте команду «Правка - Копировать», чтобы сохранить содержимое ячейки в буфере обмена.
- Оставьте ячейки выделенными и выберите команду «Специальная вставка» в меню «Правка».
- В этом поле активируйте опцию «Значения».
- Подтвердите это кнопкой «ОК».
Вместо формул результаты теперь находятся в ячейках. Снова оставьте ячейки выделенными и выберите команду «Текст в столбцах» в меню «Данные», чтобы запустить мастер преобразования текста.
Здесь вы напрямую подтверждаете стартовый экран кнопкой «Готово». Затем формулы сводятся в желаемую форму и показывают соответствующий результат.
Как найти внешние ссылки в таблицах Excel
Если вы хотите найти внешние ссылки в своих папках, вам не нужно делать это вручную. Позвольте Excel составить для вас обзор всех справочных материалов.
Это также полезно, когда в книге есть ссылка, но вы точно не знаете, к какому источнику данных она относится.
Чтобы получить список всех ссылок в вашей книге, сделайте следующее:
- Активируйте книгу, в которой вас интересуют ссылки.
- Excel 2010: щелкните меню ФАЙЛ. Затем выберите команду ПОДГОТОВИТЬ - ИЗМЕНИТЬ ССЫЛКИ К ФАЙЛАМ.
- Excel 2007: нажмите кнопку Office. Затем выберите команду ПОДГОТОВИТЬ - ИЗМЕНИТЬ ССЫЛКИ К ФАЙЛАМ.
- Excel 2003: вызовите команду РЕДАКТИРОВАТЬ - ССЫЛКИ.
Excel покажет вам список ссылок в вашей таблице.
В списке ссылок вы можете увидеть все связанные книги. Вы можете использовать кнопку ОТКРЫТЬ ИСТОЧНИК, чтобы открывать папки из этого списка после того, как вы щелкнете по соответствующему имени файла в списке.
