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

MS Excel предоставляет широкие возможности для проведени? анализа данных, находящихся в списке. К средствам анализа от­носятся:

□ обработка списка с помощью различных формул и функций;

□ построение диаграмм и использование карт MS Excel;

П проверка данных рабочих листов и рабочих книг на наличие ошибок;

О структуризация рабочих листов;

□ автоматическое подведение итогов (включая мастер частич­ных сумм);

□ консолидация данных;

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

□ специальные средства анализа выборочных записей и дан­ных— подбор параметра, поиск решения, сценарии, пакет анализа и др.

Структуризация рабочих листов

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

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

Рабочий лист может содержать только одну структуру, хотя ее можно разделить на несколько частей (рис. 8.17).

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

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

Рис. 8.17. Пример структуризации данных на рабочем листе

Рис. 8.18. Скрытие низших уровней в структуре данных

Таблица 8.1. Символы структуры

Символ структуры	Назначение
а	Кнопка для показа детальных данных
в	Кнопка для скрытия детальных данных
	2 3		Номера последовательных уровней для строк
	Номера последовательных уровней для столбцов
| 1 2| 3|
	[		Все детальные строки одного уровня
	Все детальные столбцы одного уровня
|| -|

Для автоматического создания структуры следует:

П удостовериться, что в итоговых формулах содержатся ссылки на детальные данные, расположенные в одном направлении относительно итоговых;

□ выделить нужный диапазон ячеек — для структуризации час­ти рабочего листа или выбрать одну ячейку — для структури­зации всего рабочего листа;

□ выполнить команду Данные | Группа и структура | Создание структуры.

При структуризации рабочего листа "вручную" необходимо:

□ выделить нужные ячейки строк и столбцов, которые подлежат объединению в структуру (за исключением ячейки с итоговой формулой);

□ выполнить команду Данные | Группа и структура | Группи­ровать.

В случае ошибочных действий или для разгруппировки дан­ных выбрать команду Данные | Группа и структура | Раз­группировать;

П указать вид организации документа и создать структуру, вы­полнив команду Данные | Группа и структура | Настройка.

Для возврата рабочего листа в исходное состояние следует вос­пользоваться командой Данные | Группа и структура | Удалить структуру.

Для структурированных данных имеется возможность создавать диаграммы с заданных уровней структуры.

Автоматическое подведение итогов

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

□ простые промежуточные;

□ сложные промежуточные;

□ связанные с вычислением частичных сумм (используется мас­тер частичных сумм).

Промежуточные итоги формируются с помощью команды Дан­ные | Итоги. MS Excel автоматически создает необходимые ма­тематические выражения, вставляет строки промежуточных и общих итогов, а также структурирует данные. Такую информа­цию легко обрабатывать — форматировать ячейки, создавать диаграммы и т. д.

С помощью операции Итоги можно:

□ указать способ группировки данных;

□ вывести промежуточные и общие итоги для одной группы в списке;

□ вывести промежуточные и общие итоги для нескольких групп в списке;

П выполнить расчеты над данными.

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

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

2. Провести сортировку по необходимому полю или полям (команда Данные | Сортировка).

3. Подвести итоги (команда Данные | Итоги).

(Совет ^

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

Чтобы убрать итоги, необходимо установить указатель в список с итогами и выполнить команду Данные | Итоги | кнопка Убрать все.

При создании итогов при необходимости можно:

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

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

□ подвести итоги по отфильтрованным данным (сначала— от­фильтровать, затем — отсортировать по необходимому полю (полям) и, наконец, — подвести итоги;

□ использовать мастер суммирования (команда Сервис | Мас­тер | Частичная сумма) позволяет просуммировать только те данные в списке (рис. 8.19), которые удовлетворяют заданно­му критерию (например, вычислить сумму продаж для това­ров, цена которых больше некоторого значения).

ПРИМЕР

Дан список со следующими полями (рис. 8.20): № пп, Продавец, Товар, Номер партии, Цена, Количество, Итого, Дата прода­жи, Покупатель. Определить количество товаров, проданных конкретным продавцом за конкретную дату.

Решение

1. Выделите список (или — установите в список указатель ячей­ки) и проведите сортировку (команда Данные | Сортировка) сначала— по полю Продавец, затем— по полю Дата прода­жи (рис. 8.21).

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

Северный Розница 413р. - 413р.

| Восточный Опт 166р.

■ Северный Розница 538р. 538р.

Северный Опт 230р. _:+

Дата добавления: 2015-04-29