Связывание данных листов таблиц на одном рабочем листе

Можно совместно использовать данные, хранимые на различных листах и в различных книгах, с помощью связывания или внешних ссылок. Связывание особенно полезно, если не целесообразно хранить большие модели и системы расчетов в одной книге. Можно использовать либо прямое связывание, либо консолидацию данных.

При прямом связывании надо в ячейке листа, где должны располагаться данные из другого листа, поместить его абсолютный адрес с указанием названия используемого листа, например: Лист2!$B$65. Восклицательный знак в названии отделяет ссылку на лист от ссылки на ячейку. Такое использование данных связываемого листа может применяться и при различных вычислениях, при этом в формуле указывается полный адрес ячейки с указанием листа, где она располагается.

Например, в формуле =Лист2!$A$4*(B5+$C4) использован абсолютный адрес ячейки на листе 2.

Создание новой формулы начинается со знака равенства (=). В формуле

=СРЗНАЧ(Проба!B1:B21)

функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B21 на листе «Проба» в той же самой книге. Имя листа и ссылка на диапазон ячеек отделяются восклицательным знаком (!).

Можно не только создать ссылки между разными листами, но и организовать иерархию связанных книг. Например, группа отделов продаж может хранить данные в отдельных книгах; данные затем сводятся в книгу, обобщающую данные на районном уровне, которые затем сводятся в книгу, обобщающую данные на региональном уровне.

Если ячейки, на которые имеются ссылки, изменяются, Excel автоматически обновляет ссылки только для открытых книг, содержащих связи. Если книги связаны, необходимо проверять обновление зависимых книг после обновления или изменения данных в исходных книгах. Если зависимая книга открыта при изменении данных в исходной книге, то Excel обновляет зависимую книгу автоматически. Если зависимая книга не открыта, можно обновить связи вручную.

Excel отображает формулы со ссылками на другую книгу двумя способами, в зависимости от состояния исходной книги, которая предоставляет данные для формулы, – открыта она или закрыта. Когда она открыта, ссылка включает имя книги в квадратных скобках, за которым следует имя листа, восклицательный знак (!) и ячейки, влияющие на формулу. Например, если бы книга Бюджет.xls была открыта, то формула имела бы вид

=СУММ([Бюджет.xls]Годовой!C10:C25).

Можно использовать и адрес ячейки на листе, книга с которым (файл) не загружен, а находится на диске. В этом случае придется указать и полный адрес (путь) файла, и название листа, например:

=СУММ(‘С:\Отчет\[Бюджет.xls]Годовой”!С10:С25)

Здесь используется книга Бюджет.xls из папки Отчет на диске С:, из которой используются данные ячеек С10:С25, расположенные на листе Годовой. Исходная книга для этой формулы не открыта, поэтому ссылка включает полный путь, который заключен в одиночные кавычки. В кавычки заключается путь (имя книги или листа), если в нем есть символы, отличные от букв.

Консолидация данных

Под консолидацией данных понимают сведение расположенных в различных книгах, на разных листах или в разных местах одного листа данных в одном месте таблицы.

Консолидировать данные можно либо по расположению, если они во всех сводимых областях находятся на разных листах, но в таблицах с одинаковой структурой, либо по категориям (именам), если сводимые области отличаются по структуре и расположению, но имеют одинаковые категории.

Результат консолидации данных располагается в области местоназначения. Эта область может находиться как на любом рабочем листе, так в любой рабочей книге.

При консолидации по расположению данные во всех консолидируемых областях должны располагаться идентично. Алгоритм консолидации:

• На новом листе создается итоговая (консолидирующая) таблица, структура которой соответствует структуре сводимых ячеек. В созданной итоговой таблице установить курсор в левую ячейку области, где надо расположить значения консолидируемых ячеек;

• Задать команду Данные►Консолидация;

• В окне Консолидация в поле Функция указать функцию, которую требуется выполнить с консолидируемыми данными;

• Установить курсор в поле Ссылка и в каждой из сводимых таблиц поочередно мышью выделить диапазон данных, которые требуется консолидировать, после чего в окне Консолидация нажимать кнопку Добавить. В области консолидируемых данных не следует включать имена-категории;

• Результат консолидации может оставаться неизменным при изменении данных в исходных таблицах, а может и меняться, учитывая новые сведения. В последнем случае в окне Консолидация необходимо включить параметр Создавать связи с исходными данными;

• После добавления всех консолидируемых областей нажать кнопку Ок.

При консолидации по категориям (именам) данные на листах в сводимых областях могут располагаться различно, они могут располагаться даже на одном листе, но обязательно иметь одинаковые имена-категории.

• Установить курсор в верхнюю левую ячейку области, где необходимо расположить значения объединенных данных. В этой же области должны располагаться и категории объединенных данных;

• Задать команду Данные►Консолидация;

• В окне Консолидация указать функцию, выполняемую при консолидации. В наборе флажков Использовать в качестве имен установить флажки, соответствующие расположению в исходной области заголовков: Подписи верхней строки, Значения левого столбца или Подписи верхней строки и Значения левого столбца одновременно;

• Установить курсор в поле Ссылка и поочередно на каждом листе мышью определить подлежащие консолидации области данных вместе с именами-категориями, в окне Консолидация каждый раз нажимать кнопку Добавить;

• Если результат консолидации должен изменяться при изменении исходных данных, учитывая новые сведения, то в окне Консолидация включить параметр Создавать связи с исходными данными;

• После добавления всех консолидируемых областей нажать кнопку Ок.

Связи с исходными данными нельзя использовать в том случае, когда исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные данные и изменять исходные области, уже включенные в консолидацию. Если метки в одной из исходных областей не совпадают с метками в других областях, то при консолидации для них будут созданы отдельные столбцы или строки.

При включении параметра Создавать связи с исходными данными с полученной таблицей можно работать как со структурой. При этом кнопка «2» (второй уровень) раскрывает все входящие в результат внутренние данные. Кнопка «1» (первый уровень) восстанавливает вариант таблицы только с итоговыми данными. Кнопка «+» (плюс) раскрывает промежуточные данные для соответствующей метки (категории). Кнопка «-» (минус) убирает промежуточные данные для данной категории.

При ошибочно проведенной консолидации для удаления ее результатов:

• В итоговой таблице при консолидации без параметра Создавать связи с исходными данными необходимо удалить только неправильные данные. Для этого в окне Консолидация кнопкой Удалить очистить все ссылки в поле Список диапазонов;

• Если использовалась консолидация с параметром Создавать связи с исходными данными в итоговой таблице надо удалить все строки с неправильными данными, для чего кнопкой «2» (второй уровень) раскрыть все строки таблицы и в окне Консолидация кнопкой Удалить очистить все ссылки в поле Список