Методы консолидации данных

 

СОДЕРЖАНИЕ

 

 

 

ВВЕДЕНИЕ

 

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

MS Excel относится к классу  систем обработки числовой информации, называемых spreadsheet. Буквальный перевод термина “spreadsheet” c английского языка означает “расстеленный лист (бумаги)”. В компьютерном мире под этим термином подразумевают класс программных средств, именуемых у нас “электронными таблицами”.

Возможности EXCEL очень высоки. Обработка текста, управление базами данных - программа  настолько мощна, что во многих случаях  превосходит специализированные программы-редакторы или программы баз данных. Такое многообразие функций может поначалу запутать, чем заставить применять на практике. Но по мере приобретения опыта начинаешь по достоинству ценить то, что границ возможностей EXCEL тяжело достичь.

 

1. О КОНСОЛИДАЦИИ ДАННЫХ

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

Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается  в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

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

 

 

2. МЕТОДЫ КОНСОЛИДАЦИИ ДАННЫХ

В табличном редакторе Microsoft Excel предусмотрено несколько  способов консолидации:

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

 

 

1. КОНСОЛИДАЦИЯ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ТРЕХМЕРНЫХ ССЫЛОК

Для консолидации данных с использованием трехмерных ссылок необходимо проделать следующие шаги:

 

 

1. На листе консолидации скопируйте или задайте надписи для данных консолидации.
2. Укажите ячейку, в которую следует поместить данные консолидации.
3. Введите формулу. Она должна включать ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.
4. Повторите шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты обработки данных.

 

Использование трехмерных ссылок для объединения  данных

Если исходные листы  имеют различные шаблоны и подписи или если требуется создать собственный шаблон, или применить собственные формулы объединения данных, при консолидации следует использовать трехмерные ссылки. Трехмерные ссылки обновляются автоматически при изменении исходных данных.

 

Добавление  данных для объединения

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

 

Совет.

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

 

 

 

 

 

 

2.2 КОНСОЛИДАЦИЯ ДАННЫХ ПО РАСПОЛОЖЕНИЮ

Для консолидации данных по их расположению необходимо проделать  следующие шаги:

1. Укажите верхнюю левую ячейку области назначения консолидируемых данных.
2. Выберите команду "Консолидация" в меню "Данные".
3. Выберите из раскрывающегося списка "Функция" функцию, которую следует использовать для обработки данных.
4. Введите в поле "Ссылка" исходную область консолидируемых данных, а после чего нажмите кнопку "Добавить".
5. Повторите шаг 4 для всех консолидируемых исходных областей.
6. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок "Создавать связи с исходными данными".

 

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

 

Примечание:

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

 

 

 

 

2.3 КОНСОЛИДАЦИЯ ДАННЫХ ПО КАТЕГОРИЯМ

Для консолидации данных по категориям необходимо для начала указать верхнюю левую ячейку конечной области консолидируемых  данных а после этого выбрать команду "Консолидация" в меню "Данные" после чего необходимо следовать следующим пунктам:

1. Выберите из раскрывающегося списка "Функция" функцию, которую следует использовать для обработки данных.
2. Введите исходную область консолидируемых данных в поле "Ссылка". Убедитесь, что исходная область имеет заголовок. После этого нужно нажать кнопку "Добавить".
3. В наборе флажков "Использовать в качестве имен" установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.
4. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок "Создавать связи с исходными данными".

 

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

 

Примечание:

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

 

 

2.4 ЗАДАНИЕ ИСХОДНЫХ ОБЛАСТЕЙ КОНСОЛИДИРУЕМЫХ ДАННЫХ

Исходные области обрабатываемых данных задаются либо трехмерными формулами, либо в поле Ссылка диалогового окна Консолидация. Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.

При описании исходных областей следуйте следующим рекомендациям.

 

• Для облегчения работы с исходными областями задайте имя каждого диапазона и используйте в поле Ссылка только имена.
• Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны.
• Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в книге на листе «
• Бухгалтерия», введите Бухгалтерия!Бюджет.
• Если исходные области и область назначения находятся в разных книгах, используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «2006», находящейся в этой же папке, введите:
• '[2006.xls]Дальний Восток'!Продажи
• Если исходные области и область назначения находятся в разных книгах разных каталогов диска, используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет», введите:

 

'[C:\Бюджет\Отдел продаж.xls]Февраль'!Оборот

 

Примечание.

Если диапазонам назначены  уникальные, не присвоенные автоматически  имена, то в ссылке можно не указывать имена листов. Например '[2006.xls]'!Продажи или '[C:\Бюджет\Отдел продаж.xls]'!Оборот в предыдущих примерах.

 

Совет.

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

 

Изменение итоговой таблицы консолидации данных

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

 

конечная область

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

 

Примечание.

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

 

Добавление  области данных в итоговую таблицу

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

После всего этого  нажмите кнопку «Добавить». А чтобы  пересчитать итоговую таблицу, нажмите  кнопку «OK».

Чтобы сохранить новый  набор исходных областей без пересчета итоговой таблицы, нажмите кнопку «Закрыть».

 

 

 

Изменение области  данных в итоговой таблице

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

1. Укажите левую верхнюю ячейку существующей итоговой таблицы.
2. Выберите команду «Консолидация» в меню «Данные».
3. В списке «Список» диапазонов укажите исходную область, которую следует изменить.
4. Внесите изменения в выбранную область в поле «Ссылка».
5. Нажмите кнопку Добавить.
6. Если старая ссылка не нужна, укажите ее в списке Список диапазонов, а затем нажмите кнопку «Удалить».
7. Чтобы пересчитать итоговую таблицу, нажмите кнопку «OK».

 

Чтобы сохранить новый  набор исходных областей без пересчета  итоговой таблицы, нажмите кнопку «Закрыть».

 

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

Если на листе, содержащем область назначения, уже существует структура или этот лист использовался ранее для консолидации данных со связями, удалите структуру перед созданием новых связей. После этого укажите верхнюю левую ячейку существующей итоговой таблицы. Потом выберите команду «Консолидация» в меню «Данные». В появившемся окне установите флажок «Создавать связи с исходными данными».

 

 

 

 

 

2.5 ИТОГОВЫЕ ФУНКЦИИ ДЛЯ АНАЛИЗА ДАННЫХ

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

Функция	Результат
Сумма	Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям.
Количество	Количество данных. Эта  операция используется по умолчанию  для подведения итогов по нечисловым полям. Операция «Кол-во значений» работает так же, как и функция СЧЁТЗ.
Среднее	Среднее чисел.
Максимум	Максимальное число.
Минимум	Минимальное число.
Произведение	Произведение чисел.
Кол-во чисел	Количество данных, являющихся числами. Операция «Кол-во чисел» работает так же, как и функция СЧЁТ.
Несмещенное отклонение	Несмещенная оценка стандартного отклонения для генеральной совокупности, где выборка является подмножеством генеральной совокупности.
Смещенное отклонение	Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных.
Несмещенная дисперсия	Несмещенная оценка дисперсии  для генеральной совокупности, где  выборка является подмножеством генеральной совокупности.
Смещенная дисперсия	Смещенная оценка дисперсии  генеральной совокупности по выборке  данных.