Сводные таблицы и сводные диаграммы

Лабораторная работа № 4-5

Анализ данных в Excel 2010

Цель работы

Изучение возможностей обработки данных в электронных таблицах и изучения технологий объединения данных нескольких таблиц Excel

 

Задачи работы

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

 

Теоретические сведения

Работа со списками

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

Сортировка списков

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

Если в столбце, являющемся признаком сортировки, много повторяющейся информации, то возможна дополнительная сортировка по вторичному признаку. В этом случае нужно добавить уровень сортировки.

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

Команда Данные ► Промежуточные итоги выбирается, когда список отсортирован по заданным критериям. Эта команда добавляет строки промежуточных итогов для каждой группы элементов списка, а также создает общие итоги. При этом можно использовать различные функции для вычисления итогов (например, СУММ или СРЗНАЧ). При выводе промежуточных итогов Excel создает структуру списка. Чтобы вывести интересующий пользователя уровень детализации данных, нужно щелкнуть мышью на соответствующем символе структуры.

Чтобы удалить промежуточные итоги и структуру, нужно использовать кнопку «Убрать все» диалогового окна Промежуточные итоги. Для применения нескольких итоговых функций достаточно повторить команду Промежуточные итоги для новой итоговой функции и снять флажок «Заменить текущие итоги» диалогового окна Промежуточные итоги.

Также анализировать отфильтрованный список можно с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет следующий синтаксис:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон), где N может принимать следующие значения:

1 - вычисление среднего значения;

2 - счет чисел;

3 - счет значений;

4 - вычисление максимального значения;

5 - вычисление минимального значения;

6 – вычисление произведения;

7, 8 - вычисление стандартных отклонений;

9 - вычисление суммы;

10, 11 - вычисление дисперсии.

Диапазон задает область применения функции.

Применение фильтров

Фильтр - это средство для отбора записей в таблице по некоторому критерию. В Excel имеются два типа фильтров: автофильтр и расширенный фильтр. Автофильтр показывает записи, совпадающие с критериями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.

Автофильтр

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

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

Расширенный фильтр

Для применения расширенного фильтра требуется предварительная подготовка, состоящая из двух этапов:

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

· планирования места для размещения результатов фильтрации.

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

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

Подбор параметра

Средство MS Ехсе1 «Подбор параметра» позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. Это позволяет решать обратную задачу, когда требуется, меняя значение одного из исходных данных (параметров), получить заданное значение результата. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением. Подбор параметра выполняется с помощью команды Данные – Анализ «что-если» – Подбор параметра. В открывшемся диалоговом окне задаются:

• в поле ввода «Установить в ячейке» - ссылка на целевую ячейку;

• в поле ввода «Значение» — требуемое значение,

• в поле ввода «Изменяя значение ячейки» - ссылка на изменяемую ячейку.

После нажатия кнопки ОК или клавиши Enter результат подбора параметра будет показан в окне «Результат подбора параметра». Значение параметра сохранится в изменяемой ячейке. Если не нужно сохранить значение параметра, то следует нажать кнопку Отмена.

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

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

Таблицы подстановки

Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в одной или двух исходных (влияющих) ячейках. MS Excel позволяет создавать таблицы подстановки следующих типов:

· таблицы подстановки с одной переменной и с одной или несколькими формулами (рисунок 1);

· таблицы подстановки с двумя переменными.

Рисунок 1 Таблица подстановки

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

Ячейка ввода – это ячейка, в которую подставляются значения из таблицы данных. Формулы, используемые в таблицах подстановки, должны ссылаться на ячейки ввода.

Порядок действий:

1. Ввести список значений, которые следует подставлять в ячейки ввода, в отдельный столбец и/или в отдельную строку (ячейки D2:D12);

2. Ввести формулу в ячейку, расположенную выше столбца и левее строки с введенными значениями (ячейка Е1);

3. Выделить диапазон ячеек с формулой и значениями подстановки (ячейки D1:E12);

4. Активизировать диалоговое окно «Таблица подстановки» командой Анализ «что-если» - Таблица данных панели Данные.;

5. Указать ссылку на ячейку (ячейки) ввода в диалоговом окне «Таблица подстановки» (рисунок 2);

6. Инициировать вычисления, нажав кнопку OK.

Рисунок 2 Диалоговое окно Таблица подстановки

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

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

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

Создание сценариев

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

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

Для создания сценариев с помощью диспетчера следует ввести исходные данные, выделить нужные ячейки, выполнить команду на панели «Данные» Анализ «что-если» - Диспетчер сценариев и в появившемся диалоговом окне нажать кнопку Добавить. Откроется окно «Добавление сценария», в котором вводится имя создаваемого сценария и диапазон изменяемых ячеек (рисунок 3).

Рисунок 3 Диалоговое окно «Добавление сценария»

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

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

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

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

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

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

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

При выборе команды меню Данные - Консолидация открывается окно консолидации (рис. 4), в котором можно:

· выбрать итоговую функцию для обработки данных;

· определить исходные области консолидируемых данных;

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

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

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

Рисунок 4 Пример окна «Консолидация»

Консолидация может выполнять объединение данных таблиц одинаковой и различной структуры. В первом случае произойдет консолидация по расположению (см. рис. 5), а во втором – по категориям (см. рис. 6).

Рисунок 5 Пример консолидации данных по расположению

Рисунок 6 Пример консолидации данных по категориям

В диалоговом окне Консолидация поле Ссылка предназначено для ввода одного за другим диапазонов консолидации, которые кнопкой <Добавить> переносятся в поле Список Диапазонов. Назначение кнопки < Обзор>аналогичноее применению в другихпрограммах. Если от предыдущей консолидации в списке остались диапазоны, которые не соответствуют текущей задаче, их можно удалить.

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

Если флажок Создавать связи с исходными данными не включен, итоговые результаты консолидации выводятся в виде обычных числовых значений. В противном случае, они представляют собой сгруппированные списки, где кнопка «+» позволяет развернуть консолидируемые элементы (рис. 7), просмотреть через строку формул адреса исходных данных, а также тип примененной итоговой функции.

Рисунок 7 Консолидация с сохранением связей с исходными данными

Сводные таблицы и сводные диаграммы

Для всестороннего и эффективного анализа данных больших таблиц в Excel используются т.н. сводные таблицы (СТ). Главные достоинства СТ – это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности для группировки данных, а так же возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически.

Для построения и модификации СТ используется инструмент «Сводные таблицы», вызываемый с помощью пункта «Сводная таблица» на панели «Вставка».

Редактировать СТ можно, как в режиме просмотра, так и вернувшись, в режим создания таблицы. Изменению в таблице могут подвергаться структура (добавление новых полей, удаление существующих, изменение местонахождения поля), тип используемой функции и дополнительные вычисления.

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

Чтобы построить сводную диаграмму на основе существующей сводной таблицы, достаточно щелкнуть по сводной таблице и на панели «Работ со сводными таблицами» выбрать пункт «Сводная диаграмма».