Обобщаем с умом: внешние ссылки и консолидация в Excel
Здравствуйте уважаемые читатели. Сколько раз вы сталкиваетесь с задачей консолидации или объединения данных из разных листов и книг в единую таблицу? Я часто думаю. Консолидация в Excel требует времени, кропотливой и утомительной работы, но в этой статье я покажу вам, как ее упростить. Есть два подхода к сбору информации из разных источников: ссылки на внешние ячейки или инструмент консолидации».
Внешние ссылки в Excel
Думаю, каждый из вас знает, что внешняя ссылка в Excel — это ссылка на ячейку в другой книге. И самый распространенный способ собрать данные из нескольких источников в один — это просто внешние ссылки.
Если полученная таблица достаточно проста, вы можете использовать этот метод, иначе легко запутаться и утомительно вручную добавлять несколько сотен ссылок на ячейки, высока вероятность ошибки. Однако для журнальных столиков это действительно выход.
Есть много способов сослаться на ячейку с другого листа или книги. Говорю самое простое:
- Открыть все файлы — Хранилище и исходники
- Щелкните ячейку, в которую вы хотите вставить ссылку, и введите знак равенства (как при создании формул).
- Выберите файл с исходным значением и щелкните это значение. Ссылка на это значение появится в строке формул.
- Если вам нужно добавить к этому значению другие данные из других источников, добавьте «+» и повторяйте абзацы 3-4, пока оно не будет полностью заполнено.
- Когда все ссылки на ячейки будут сделаны, нажмите Enter, итоговый результат будет рассчитан.
Внешняя ссылка имеет специфический вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:
Когда вы закрываете исходные файлы, URL-адрес исходного файла добавляется к ссылке слева:
По умолчанию все внешние ссылки являются абсолютными, т.е они не меняются при копировании. При необходимости сделайте их относительными, нажимая F4, пока не получите нужный тип ссылки.
Не перемещайте исходные файлы, так как это приведет к разрыву ссылок, и внешние ссылки больше не будут работать.
Если и сводный документ, и исходный документ открыты на одном компьютере, изменения в исходной таблице немедленно «проталкиваются» в сводную таблицу. Если хотя бы одна из этих книг будет закрыта, потребуется принудительное обновление. Кроме того, при повторном открытии файла сводки Excel предложит обновить данные. Сделайте это, если вам нужно обновить свою информацию.
Управление внешними ссылками
Чтобы управлять ссылками на источники в других книгах, щелкните ленту «Данные — Подключения — Изменить ссылки». В открывшемся окне будет отображен весь список связанных файлов и командных кнопок:
- Обновление: принудительное обновление всех ссылок
- Изменить: укажите новую книгу Excel, содержащую подключенные данные
- Открыть: открывает исходный документ
- Разорвать ссылку — удаляет внешние ссылки из этой ссылки и заменяет их значением
- Статус — обновляет статус доступности файлов данных
- Запрос на обновление ссылки: сообщает программе, следует ли обновлять данные при открытии файла
Если у вас есть большой файл с несколькими листами, поиск внешних ссылок может занять много времени. Итак, как быстро найти внешние ссылки в документе Excel? Подскажу рецепт. Все эти ссылки имеют один общий элемент: открывающую и закрывающую скобки, которые содержат имя исходного файла. В остальных случаях такие скобки используются редко. Нажмите Ctrl + F, чтобы открыть окно поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». Результаты поиска обязательно будут содержать все внешние ссылки.
Использование внешних ссылок — это самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных много, этот способ не применим. Итак, воспользуемся инструментом «Консолидация».
Консолидация данных в одну таблицу
Этот инструмент позволяет легко консолидировать информацию в единую таблицу данных, это хороший способ автоматизировать процесс.
Рассмотрим его работу на примере. У нас есть 3 файла с данными о продажах трех подразделений компании. Необходимо собрать общие показатели по организации и сделать это быстро, потому что информация нужна «на вчерашний день».
Выполняем. Открываем все исходные файлы и хранилище. Активируем «Арку». Ищем Данные — Работа с данными — Консолидация на ленте. Откроется окно подключения к внешним данным.
Теперь выполним пошагово:
- В поле «Функция» выберите «Сумма» (или что-то еще, если необходимо)
- В поле «Ссылка» укажите ссылку на данные в первом исходном файле (вместе с заголовком). Лучше указывать столбцы полностью, например A: D. Поэтому вам не нужно будет переопределять диапазон при добавлении новых данных.
- Нажмите «Добавить», ссылка отобразится в поле «Список диапазонов»
- Повторяйте шаги 2–3, пока не добавите ссылки на все исходные файлы
- Установите флажки «Субтитры в верхней строке» и «Значения в левом столбце», чтобы Excel собирал заголовки и категории в строках самостоятельно
- Установите флажок «Создавать ссылки на исходные данные», тогда консолидация будет производиться с использованием ссылок на ячейки. В противном случае будут просто введены итоги.
- Нажмите ОК, чтобы завершить настройку консолидации.
В результате в файле «Код» появится сводный список таблиц из всех связанных файлов. Если вы установили флажок «Создавать ссылки на исходные данные», итоговая таблица будет структурирована, вы можете развернуть каждый элемент и просмотреть подробную информацию по каждому отделу отдельно
Если исходные таблицы изменились, вы можете перефразировать хранилище. Для этого снова откройте окно «Консолидация» и нажмите OK.
Это простой инструмент, решающий одну из самых неприятных проблем в Excel. Я предпочитаю использовать его, ручное связывание не для меня. Я рекомендую вам тоже его использовать. Однако лучшим решением было бы использовать сводную таблицу (когда это возможно) или создать свою собственную программу VBA с подробными настройками информации.
На этом этапе я оставляю вас экспериментировать с консолидацией и начинаю писать сообщение о защите данных в Excel. Читайте, эта информация сейчас актуальна как никогда. Увидимся!
