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

Итоги

Анализ данных

Для анализа данных можно использовать команды Итоги и Сводные таблицы меню Данные.

Команда Итоги позволяет получать сводные данные по числовым параметрам: сумму, минимум, максимум, среднее значение и другие статистические данные. Предварительно необходимо определить по какому параметру требуется группировать итоги и отсортировать данные по этому параметру. В примере на рис. 6.29, Листинг 6.24 в качестве такого параметра выбран вид продукции. Затем необходимо выделить базу данных и ввести команду Данные, Итоги. На экране появится окно диалога Промежуточные итоги (рис. 6.29). Выберем в списке “При каждом изменении в:” параметр Вид, в списке “Операция” выберем вид операции Сумма. В списке “Добавить итоги по:” установим флажки для параметров Стоимость и Количество. Установим флажки “Заменить текущие итоги” и “Итоги под данными”.

Если флажок “Итоги под данными” не установлен, то итоговые данные выводятся срзу же после шапки таблицы, в ином случае итоговые данные будут размещены в конце таблицы.

Флажок “Конец страницы между группами” позволяет разбить данные по видам и напечатать их на отдельных листах. При малом объеме данных это делать не целесообразно.

Кнопка Убрать все удаляет все итоги и переводит базу данных в исходное состояние

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

Сводные таблицы

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

На первом шаге предлагается выбрать источник данных и вид отчета. В качестве источника данных может быть база данных Excel или внешняя база данных, например, база данных Microsoft Access, Visual FoxPro, dBase-Word.

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

На третьем шаге можно указать место для сводной таблицы (на текущем листе или на новой странице), создать макет сводной таблицы и настроить некоторые параметры (рис. 6.30.). При выборе команды Макет открывается одноименное окно диалога (рис. 6.31). На макете обозначены четыре зоны: Страницы, Строка, Столбец, Данные. В эти зоны необходимо поместить кнопки требуемыех полей базы данных, расположенных справа от макета. Заполнение зон Страницы, Строка, Столбец, Данные осуществляется претаскиванием кнопок базы данных мышью. Поле Страница выполняет роль фильтра и позволяет выполнить группировку данных по некоторому полю базы данных, например, по дате поступления, отправителю или виду. В зону Строки помещают данные, которые должны быть помещены в строки сводной та блицы. А в зону столбец помещают имена полей, которые должны размещаться в столбцах базы данных. В зону Данные помещаюся как правило числовые поля.

Рассмотрим для примера фрагмент базы данных, приведенный на листинге 6.24. Поместим в зону Страница кнопку Дата, в зону Строка – кнопку Вид, в зону Столбец – кнопку Откуда и в зону Данные – кнопку Количество (рис. 6.31). При установке в зону Данные кнопки Количество по умолчанию предлагается выполнить

суммирование значений данных по соответствующему полю и соответственно имя кнопки изменяется. Можно выбрать другой вид операции. Для этого щелкните дважды мышкой по кнопке Сумма по полю Количество. Для завершения операции щелкните по кнопке ОК – программа возвращается в окно шага 3 (рис.6.31). Для завершения формирования сводной таблицы щелкните по кнопке Готово. Результат приведен на Листинге 6.25. В списке Все содержится список дат поступления товаров. Список Откуда содержит список источников поступления. Эти списки снабжены флажками, которые позволяют управлять выводом информации на экран. Например, открыв список Откуда, можно снять флажки у Вологды, Мурманска, Тюмени. Тогда на экране останутся данные только по Братску. В списке дата по умолчанию флажки установлены у всех элементов списка. Можно установить флажок только для одной, интересующей Вас даты, тогда на экране будет выведен список товаров, поступивших в указанную дату.

Контрольные вопросы

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

1. Консолидация по расположению ячеек – в этом случае состав и порядок расположения данных во всех диапазонах одинаков;

2. Консолидация по категориям – в этом случае консолидация проводится на основании одинаковых подписей строк и столбцов диапазонов.

Введем команду Данные, Консолидация – на экране появится окно диалога Консолидация (рис. 6.32). Список “Функция” позволяет выбрать операцию над консолидируемыми данными. При консолидации доступны все функции статистических итогов (сумма, минимум, максимум и. д.). Строка ввода “Ссылка” позволяет указать или выбрать диапазон консолидируемых данных. “Список диапазо нов” хранит списки диапазонов консолидируе мых данных. Кнопка Добавить позволяет добавлять данные в список диапазонов через строку “ссылка”.

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

Кнопка Обзор служит для выбора данных в других книгах

Пример 6.9. Пусть требуется найти среднее значение для каждой строки данных в столбцах А и В. Результаты поместить в столбец С.

Алгоритм работы:

- выделите диапазон ячеек, равный высоте столбца А, без учета заголовков;

Листинг 6. 26
А		В		С
				1,5
				2,5
				3,5
				4,5
				5,5
				6,5
				7,5
				8,5

- введите команду Данные, Консолидация;

- активизируйте строку ввода “Ссылка”, выделите первый диапазон в столбце А и щелкните по кнопке Добавить;

- активизируйте строку ввода “Ссылка”, выделите второй диапазон в столбце В и щелкните по кнопке Добавить;

- выберите функцию “Среднее” в списке Функция;

- щелкните по кнопке ОК.

Состояние окна диалога Консолидация после ввода данных и выбора функции приведено на рис. 6.32, а результат – на листинге 6.26.

Дата добавления: 2014-01-06