Консолидация - агрегирование (объединение) данных, представленных в исходных областях - источниках

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

Рис.2 Представление о консолидации данных

Существуют следующие варианты консолидации данных:

¶ с помощью формул, где используются ссылки;

¶ по расположению данных для одинаково организованных областей-источников (фиксированное расположение);

¶ по категориям для различающихся по своей структуре области данных;

¶ с помощью сводной таблицы;

¶ консолидация внешних данных.

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

¶ все области на одном листе — в ссылках указывается адрес блока ячеек, например D1:C8;

¶ области на разных листах — в ссылках указывается название листа, диапазон, например

¶ лист1!D1 :лист2!С8;

¶ области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.

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

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

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

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

Пример. На рабочих листах представлена информация областей-источников в виде структуры на рис.4. Число строк — переменное, состав предметов и виды занятий повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. Серым цветом показана консолидируемая область источников

Рис. 4. Пример областей-источников при консолидации данных по категориям

Условия консолидации задаются в диалоговом окне «Консолидация» (рис.3.53). В окне «Функция» выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка <Добавить>.

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

Рис. 5. Диалоговое окно «Консолидация» для задания условий консолидации

Ссылка может иметь любую из следующих форм:

ссылки на ячейки ссылки на лист и ячейки ссылки на книгу, лист и ячейки полный путь и все ссылки имя поименованной области

источники и назначение на одном листе источники и назначение на разных листах источники и назначение в разных книгах источники и назначение в различных местах диска область-источник поименована

При консолидации внешних данных в диалоговом окне «Консолидация» следует нажать кнопку <0бзор>, в диалоговом окне «Обзор» выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.

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

Внимание! Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными

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

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

СВОДНЫЕ ТАБЛИЦЫ.

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

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

¶ обобщения большого количества однотипных данных;

¶ группировать данные в сводных таблицах по категориям;

¶ реорганизации данных (с помощью перетаскивания;

¶ повышать удобочитаемость Сводной таблицы путем её форматирования;

¶ использовать данные из других программ;

¶ модифицировать Сводную таблицу для изменения представления данных;

¶ обновлять сводную таблицу с учетом текущих данных;

¶ создавать стратегические отчеты для Сводных таблиц.

 

Внешне сводные таблицы являются структурой, позволяющей размещать данные в трехмерном виде. Сводные таблицы создаются с помощью мастера Сводные таблицы (команда Данные – Сводная таблица).

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

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

2. Местонахождение исходных данных – список MS Excel, внешний источник данных, диапазоны консолидации, находящиеся в другой сводной таблице.

3. При задании структуры сводной таблицы определить:

¶ поля, находящиеся в строках и столбцах таблицы;

¶ поля, по которым подводятся итоги (с выбором необходимой операции);

¶ поля для страниц, что позволяет представить информацию в трехмерном виде.

4. Сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице, необходимо внести изменения в источник данных, а затем – обновить сводную таблицу (на панели инструментов Сводные таблицы находится кнопка Обновить данные).

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

6. Сводные таблицы допускают возможность группировки элементов полей по различным уровням иерархии путем объединения (выделение данных сводной таблицы осуществляется, например, с помощью мыши) в группы (группы можно переименовывать по желанию). Для этой цели на панели инструментов Сводные таблицы существуют две кнопки – Группировать и Разгруппировать. Можно скрывать и показывать детали в группе. Элементы самого высокого уровня группировки (обобщающие элементы) располагаются по верхней или по крайней левой границе сводной таблицы (отображение исходных данных в данном случае производится путем выделения группированной ячейки сводной таблицы и применения соответствующей команды отображения данных – кнопка Отобразить детали). Бывает следующая группировка сводной таблицы:

¶ группировка элементов по их именам;

¶ группировка чисел по диапазонам (для числовых данных, кнопка Группировать на панели инструментов Сводные таблицы);

¶ группировка по временным диапазонам (данные в формате Дата/Время).

7. Возможность построения диаграмм на основе сводных таблиц.

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

¶ сортировка элементов в сводной таблице;

¶ размещение страниц сводной таблицы на различных рабочих листах (кнопка – Отобразить страницы);

¶ управление общими и промежуточными итогами;

¶ использование различных итоговых функций для анализа данных и дополнительных вычислений;

¶ вставка вычисляемого поля в сводную таблицу;

¶ использование автоформата для форматирования сводной таблицы.

СРЕДСТВА КОНТРОЛЯ

Контроль рабочих листов

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

⇐ Предыдущая1234Следующая ⇒

Поделиться: