Тема 8.1. Информационные технологии анализа электронных табличных данных

Для анализа табличных данных в MS Excel можно использовать средства формирования сводной информации. Сводная информация может быть получена: объединением данных с помощью промежуточных итогов; методом консолидации; формированием сводных таблиц.

Простейший способ получения итогов – с помощью команды Итоги меню ДАННЫЕ. Для выполнения этой команды необходимо:

· представить данные в виде списка;

· командой Сортировка меню ДАННЫЕ упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;

· установить указатель на ячейку списка;

· выполнить команду Итоги меню ДАННЫЕ;

· в диалоговом окне Промежуточные итоги задать нужные параметры.

После выполнения команды Итоги меню ДАННЫЕ создается структура, в которой данные (таблица) структурированы, т.е. разбиты на несколько уровней. С помощью уровней структуры можно управлять выводом данных соответствующего уровня из таблицы на экран, указывая, выводить данные или скрывать.

Для удаления промежуточных итогов необходимо в диалоговом окне команды Итоги выбрать кнопку Убрать все. При удалении промежуточных итогов из списка удаляется и структура таблицы.

Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В MS Excel предусмотрено несколько способов консолидации данных:

· консолидация данных с помощью трехмерных ссылок;

· консолидация данных по расположению;

· консолидация данных по категориям.

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

· на листе консолидации (итоговом листе) создать (или скопировать) надписи для данных консолидации;

· указать ячейку на листе консолидации, куда следует поместить результат консолидации;

· ввести формулу, которая должна содержать ссылки на консолидируемые исходные области листов, данные которых будут участвовать в консолидации;

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

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

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

· выполнить команду Консолидация меню ДАННЫЕ;

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

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

Рис. 24 Окно команды Консолидация из меню Данные

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

Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.

Сводная таблица может быть создана: на основе данных любой таблицы или области таблицы рабочего листа; на основе данных нескольких таблиц, полученных в результате консолидации данных; на основе данных сводной таблицы; на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.

Для создания сводной таблицы необходимо выделить источник данных и запустить программу Мастер сводных таблиц. Перед построением сводной таблицы из источника данных обязательно должны быть убраны промежуточные итоги и наложенные фильтры. Запустить программу Мастер сводных таблиц можно путем выбора команды Сводная таблица меню ДАННЫЕ или нажатием кнопки Мастер сводных таблиц инструментальной панели Сводные таблицы.

Изменение исходных данных не приводит к автоматическому обновлению сводной таблицы. Чтобы обновить сводную таблицу, можно:

· Выполнить команду Обновить данные меню ДАННЫЕ.

· Выполнить команду Обновить данные контекстного меню.

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

Рис. 25 Окно для форматирования

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

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

Мастер сводных таблиц автоматически группирует элементы внутреннего поля для каждого заголовка внешнего поля. Создание групп элементов производится с помощью команды Группа и структура - Группировать меню ДАННЫЕ или при нажатии кнопки Группировать на панели инструментов Сводные таблицы.

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

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