Консолидация данных с использованием трехмерных ссылок
Трехмерные ссылки – это ссылки на данные, расположенные на разных листах.
Для решения задачи консолидации этим способом надо выполнить следующие действия:
1) на листе консолидации скопировать или задать надписи для данных консолидации;
2) указать ячейку, в которую следует поместить данные консолидации, и ввести формулу для расчета. Она должна включать ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация;
3) повторить шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты обработки данных.
Консолидация данных по расположению
Производится, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Для консолидации этого вида надо выполнить следующие действия:
1) указать верхнюю левую ячейку области назначения - выбранного для хранения консолидируемых данных диапазона ячеек, который может находиться на том же листе, что и исходные данные, или на другом;
2) выбрать команду Консолидация в меню Данные и из раскрывающегося списка Функция выбрать функцию, которую следует использовать для обработки данных;
3) ввести в поле Ссылка исходную область консолидируемых данных;
4) нажать кнопку Добавить.
Определение консолидируемых областей можно выполнять многократно.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными. Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже участвующие в консолидации.
|
|
|
При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную.
Консолидация данных по категориям
Консолидация данных по категориям производится, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Этот способ целесообразно использовать для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.
Осуществляется также, как и консолидация данных по расположению за добавлением следующего: в наборе флажков Использовать в качестве имен установить флажки, соответствующие расположению в исходной области заголовков.
Однако следует убедиться в следующем:
1) заголовки строк или столбцов были включены в исходные области;
2) в диалоговом окне Консолидация установлены флажки В верхней строке, или В левом столбце, или оба флага одновременно;
|
|
|
3) заголовки категорий одинаковы во всех областях (включая регистр букв). Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации;
4) категории, которые не нужно включать в таблицу консолидации, имеют заголовки, которые появляются только в одной исходной области.
Изменение итоговой таблицы консолидации данных
После создания итоговой таблицы с помощью команды Консолидация можно добавлять, удалять или изменять исходные области данных. Кроме этого, можно создать связи итоговой таблицы с исходными данными, с тем, чтобы данные области назначения автоматически обновлялись при изменении данных в исходных областях.
1. Добавление области данных в итоговую таблицу
Если при консолидации не создавались связи с исходными данными, можно добавить новые исходные области и пересчитать данные в области назначения. Для этого:
1) укажите верхнюю левую ячейку существующей итоговой таблицы;
2) выберите команду Консолидация в меню Данные;
3) укажите поле Ссылка;
4) если лист, содержащий новый источник данных, является текущим, выделите на нем исходную область;
|
|
|
5) нажмите кнопку Добавить;
6) чтобы пересчитать итоговую таблицу, нажмите кнопку OK.
Чтобы сохранить новый набор исходных областей без пересчета итоговой таблицы, нажмите кнопку Закрыть.
Если были созданы связи с исходными областями, удалите итоговую таблицу, а затем удалите структуру, если она существует на листе.
2. Изменение области данных в итоговой таблице
Если при консолидации не создавались связи с исходными данными, можно изменить исходные области и пересчитать данные в области назначения. Для этого:
1) укажите левую верхнюю ячейку существующей итоговой таблицы;
2) выберите команду Консолидация в меню Данные;
3) в списке Список диапазонов укажите исходную область, которую следует изменить;
4) внесите изменения в выбранную область в поле Ссылка;
5) нажмите кнопку Добавить;
6) если старая ссылка не нужна, укажите ее в списке Список диапазонов, а затем нажмите кнопку Удалить;
7) чтобы пересчитать итоговую таблицу, нажмите кнопку OK.
Чтобы сохранить новый набор исходных областей без пересчета итоговой таблицы, нажмите кнопку Закрыть.
Если связи с исходными областями были созданы, удалите итоговую таблицу, а затем удалите структуру.
3. Удаление области данных из итоговой таблицы
|
|
|
1) укажите верхнюю левую ячейку существующей итоговой таблицы;
2) выберите команду Консолидация в меню Данные;
3) в списке Список диапазонов укажите исходную область, которую следует удалить;
4) нажмите кнопку Удалить;
5) чтобы пересчитать итоговую таблицу, нажмите кнопку OK.
Чтобы сохранить новый набор исходных областей без пересчета итоговой таблицы, нажмите кнопку Закрыть.
4. Создание связей итоговой таблицы с исходными данными
Если на листе, содержащем область назначения, уже существует структура или этот лист использовался ранее для консолидации данных со связями, удалите структуру перед созданием новых связей:
1) укажите верхнюю левую ячейку существующей итоговой таблицы;
2) выберите команду Консолидация в меню Данные;
3) установите флажок Создавать связи с исходными данными.
Дата добавления: 2018-04-05; просмотров: 375; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!