Консолидация данных по физическому расположению
Консолидация данных с использованием ссылок
На третьем шаге Мастера сводных таблиц нажмите кнопку Далее, чтобы подтвердить положение установленных по умолчанию полей (рис.17)
Связывание с ячейками и диапазонами ячеек рабочей книги
Обновление связанных изображений ячеек
Навигация
Консолидация данных по физическому расположению
Консолидация данных в Excel
37627
знаков
12
таблиц
27
изображений
1.1.2 Консолидация данных по физическому расположению
Консолидация по расположению ячеек возможна, если данные одного типа на всех листах расположены в одних и тех же позициях относительно исходных диапазонов. Сами диапазоны при этом могут располагаться по-разному. Данные в диапазоне назначения будут расположены так же, как и в диапазонах-источниках.
Пример 7.
Необходимо просуммировать данные об обороте товаров за первый и второй квартал 1996 года. Данные соответственно находятся на листе 1кв1996 ячейках D2:D5 файла Квартал1.xls и на листе 2кв1996 ячейках B3:B6 файла Квартал2.xls. В указанных диапазонах нет заголовков граф.
Чтобы осуществить консолидацию этого типа необходимо выполнить следующие действия:
1. Выделить диапазон назначения, руководствуясь табл. 1.
Учитывайте только область данных. Текстовые заголовки в консолидации не участвуют. Для нашего примера это диапазон A2:A5 листа назначения (рис.3). Поскольку указан диапазон ячеек, то консолидируется только четыре значения по количеству ячеек в диапазоне (см.табл.1).
2. Выбрать команду Данные, Консолидация. Появится диалоговое окно Консолидация (рис. 1).
3. В поле Ссылка указать исходный диапазон. Диапазон не должен включать заголовки столбцов или строк, если они имеются. Для нашего примера это:
=[Квартал1.xls]1кв1996!$D$2:$D$5
Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов (рис. 2).
4. Повторите шаги 3 и 4 для всех исходных диапазонов. Если исходные рабочие листы имеют однотипные имена, достаточно будет слегка редактировать строку в поле Ссылка. Для нашего примера это еще 1 диапазон:
=[Квартал2.xls]2кв1996!$В$3:$В$6
| ||||
| ||||
5. В списке Функция выбрать тип консолидации. По умолчанию используется функция сумма.
6. Сбросить оба флажка в группе Использовать в качестве имен, поскольку ячейки привязаны к своим позициям, а не к заголовкам.
7. Установить при необходимости флажок в опции Создать связи с исходными данными. Тогда результаты будут обновляться при изменении данных, а в области назначения будет создана структура.
8. Нажать кнопку ОК.
Результат консолидации представлен на рис.3.
Примечание. При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную.
Консолидация по расположению используется редко, поскольку необходимо, чтобы исходные диапазоны имели абсолютно одинаковую структуру, а этого сложно добиться, например, если данные поступают из различных отделений компании. В отделениях могут использоваться дополнительные поля, чтобы выделить какой-либо товар, регион или статью расхода. В этом случае предпочтительнее консолидация по заголовкам строк и столбцов.
1.1.3 Консолидация по заголовкам строк и столбцов
Этот метод консолидации позволяет консолидировать исходные данные, содержащие наряду с полями, общими для всех диапазонов, какие-либо уникальные поля. Порядок расположения полей не имеет значения. Сопоставление информации осуществляется по заголовкам строк и столбцов.
Таким образом, достигается большая гибкость.
Пример 8.
Необходимо составить сводную ведомость расходов двух различных филиалов компании (книги Филиал1.xls и Филиал2.xls). Названия статей и их порядок следования могут отличаться. Исходные диапазоны представлены на рис.5. Заголовки столбцов совпадают, заголовки строк нет.
|
Чтобы осуществить консолидацию по заголовкам строк и столбцов необходимо:
1. Выделить диапазон назначения. Если необходимо, чтобы поля были расположены в определенном порядке, следует включить в диапазон заголовки полей или строк. Заголовки должны быть написаны в точности так, как и на исходных листах. Если заголовки не вводить, то Excel создаст их автоматически (рис.6).
|
2. Выбрать команду Данные, Консолидация.
3. В поле Ссылка указать ссылку на исходный диапазон. Диапазон должен включать заголовки столбцов или строк. Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать или можно воспользоваться кнопкой Обзор и выбрать файл на диске:
='[Филиал1.xls]Лист1'!$А$1:$С$6
4. Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов.
5. Повторить шаги 3 и 4 для всех исходных диапазонов. Если исходные рабочие листы имеют однотипные имена, достаточно будет немного отредактировать строку в поле Ссылка.
='[Филиал2.xls]Лист1'!$А$1:$С$6
6. В списке Функция выбрать тип консолидации. Для рассматриваемого примера – функция Сумма.
7. Установить флажки в группе Использовать в качестве имен. Можно установить как один флажок, так и оба Подписи верхней строки, Значения левого столбца.(рис. 7).
|
8. Установить при необходимости флажок Создать связи с исходными данными. Тогда результаты будут обновляться при изменении данных, а в области назначения будет создана структура.
9. Нажать кнопку ОК.
Результат представлен на рис.8.
|
Похожие работы
нного фильтра------------------------------- 13 Удаление фильтра из списка------------------------------------------------------------------- 14 Контрольные вопросы и контрольное задание---------------- 15 Консолидация данных Методы консолидации данных В табличном редакторе Microsoft Excel предусмотрено несколько способов консолидации: · С помощью трехмерных ссылок, что ...
... рис. 18 ☺ Самостоятельно поработайте с диалоговым окном Формат ячеек и изучите возможности каждой из имеющихся закладок. Средства автоматического обобщения и анализа данных электронной таблицы Excel предоставляет разнообразные способы для автоматического обобщения и анализа данных: 1. Автоматические вычисления; 2. Средства для работы с базами данных и со списками, позволяющие ...
... сводной таблицы установить переключатель Обновлять внешние данные при выборе каждого элемента. Выделение элементов сводной таблицы При работе со сводными таблицами в Microsoft Excel 97 мы можем использовать новый режим выделения , который называется структурным выделением. Если этот режим включен , то при выделении элементов в ...
... элемента и вычисляемого поля. Использование общих и промежуточных итогов в сводной таблице Сводную таблицу можно настроить на выполнение вычислений с помощью итоговой функции (итоговая функция - это тип вычисления, производимого Microsoft Excel при объединении данных в сводной таблице или при вычислении итогов в списках и базах данных. Примерами итоговых функций являются «Сумма», «Кол-во ...
0 комментариев