Тема 11. Основные средства анализа данных, содержащихся в таблицах

Формирование итогов и сводных таблиц. Группировка строк и столбцов. Консолидация таблиц и их частей. Процедура транспонирования данных.

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

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

Список состоит из трех структурных элементов:

1) Заглавная строка – это первая строка списка, состоящая из заголовков столбцов. Заголовки столбцов – это метки (названия) соответствующих полей.

2) Запись – совокупность компонентов, составляющих описание конкретного элемента (строка таблицы).

3) Поля – отдельные компоненты данных в записи (ячейки в столбце).

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

Правила формирования списка таковы:

1) Рабочий лист должен содержат только один список, т.к. некоторые операции, например, фильтрование, могут работать только с одним списком.

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

3) Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка).

4) Метки столбцов могут содержать до 255 символов.

5) Не следует отделять заглавную строку от записей пустыми строками или строкой, содержащей линию из символа «дефис».

6) Список должен быть составлен так, чтобы столбец содержал во всех строках однотипные значения.

7) При вводе значения поля нельзя вставлять ведущие пробелы, это может привести к проблемам при поиске и сортировке.

8) В списках можно использовать формулы

Списки могут обрабатываться, как обычные таблицы. Т.е. для редактирования данных могут применяться все рассмотренные ранее возможности. Однако значительно упростить работу с записями списка позволяет команда Форма меню ДАННЫЕ. Использование формы данных позволяет:

добавить записи в список;

организовать поиск записей в списке;

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

удалять записи из списка.

Excel содержит средства формирования сводной информации для проведения анализа данных.

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

Сводная таблица может быть создана:

на основе данных любой таблицы или области таблицы рабочего листа;

на основе данных нескольких таблиц, полученных в результате консолидации данных;

на основе данных сводной таблицы;

на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.

Подбор параметра позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат.

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

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

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