В этой статье речь пойдёт о том, как провести анализ данных с помощью сводной таблицы. Для тренировки Вы можете использовать таблицу, доступную по этой ссылке (простая таблица.xlsx).
Первое, что может понадобиться проанализировать с помощью сводной таблицы - это подвести промежуточные итоги. В нашем примере это может быть необходимость подсчитать объемы продаж по всем магазинам на каждую дату.
Для этого необходимо кликнуть на любом заголовке строки сводной таблицы (в нашем примере - это поля Дата, Точка продажи и Марка телефона ), и в открывшихся вкладках Работа со сводными таблицами перейти на вкладку Параметры . На ней необходимо нажать кнопку Параметры поля в группе Активное поле .
В открывшемся окне первой закладкой будет закладка .
Отсутствие такой закладки означает, что Вы не выбрали заголовок строки, то есть курсор установлен на ячейке с числовым значением.
На закладке Промежуточные итоги и фильтры Вы можете выбрать условие для выведения промежуточных итогов. Предлагаются следующие условия:
Установив автоматическое подведение промежуточных итогов, мы получим следующую таблицу, которая содержит промежуточные итоги для каждого условия:
Если настройка промежуточных итогов с помощью команды Параметры поля , не даёт видимых результатов, проверьте настройки отображения промежуточных итогов с помощью команды Промежуточные итоги группы Макет вкладки Конструктор .
Допустим, нам необходимо вывести промежуточные итоги только для дат, скрыв промежуточные итоги для точек продаж. Для этого щелкните на любое поле таблицы с названием магазина и вызовите контекстное меню. В нём нужно убрать галочку с условия Промежуточный итог: точка продажи . Как мы видим, промежуточные итоги остались только для дат:
Часто бывает необходимо отсортировать данные сводной таблицы, для лучшего их восприятия. Для этого достаточно выбрать поле, по которому нужно провести сортировку, перейти на вкладку Общие , в группе Редактирование нажать на кнопку Сортировка и фильтр и установить нужные Вам условия сортировки.
Очень полезной функцией для анализа информации в сводной таблице является возможность группировки данных. Например, нам нужно сгруппировать наши продажи по неделям месяца. Для этого нужно выделить даты, которые входят в первую неделю (15.05-21.05):
Обратите внимание, что для удобства выделения мы свернули данные по отдельным магазинам, воспользовавшись кнопкой + в левой части ячейки с названием магазина.
Далее нужно выполнить команду Группировка по выделенному группы Группировать вкладки Параметры . В таблице появится новый столбец, в котором поле Группа1 будет объединять выбранные нами поля.
Останется только переименовать название группы путём простого редактирования ячейки:
Для отмены группировки достаточно воспользоваться командой Разгруппировать из этой же группы, предварительно выбрав поле, которое подлежит разгруппировке. Обратите внимание, что нельзя разгруппировать поле, которое мы включили в условие построения сводной таблицы - например поле Точка продажи или Дата .
Рассмотрим ещё один способ вывода данных, который поможет нам проанализировать информацию из нашей таблицы. Например, нам нужно узнать объемы выручки не в денежном выражении, а в виде процента от общего объема выручки за весь период продаж.
Для этого нужно выделить любую ячейку в столбце Выручка нашей сводной таблицы. После этого нужно выполнить команду Параметры поля в группе Активное поле вкладки Параметры .
В открывшемся диалоговом окне необходимо перейти на вкладку Дополнительные вычисления и из выпадающего меню выбрать пункт Доля от суммы по столбцу . После нажатия кнопки Ок, наша таблица будет иметь следующий вид:
Если данные в таблице будут выводиться не в виде процентов, проверьте настройки числового формата ячеек (это можно сделать сразу же в диалоговом окне Параметры поля , нажав на кнопку Числовой формат , или вызвав соответствующее окно из контекстного меню).
Для анализа списков данных (таблиц данных) в Excel 2007, которые имеют множество строк или записей, часто используются такие средства как сводные таблицы. Сводные таблицы значительно упрощают просмотр, обработку и обобщение данных в списках Excel 2007.
Для того чтобы программа Excel работала с введенными данными как с таблицей данных, а не с простым набором данных, необходимо ее отформатировать как таблицу. Для этого щелкните на кнопке Таблица, расположенной во вкладке Вставка. В диалоговом окне Создание таблицы (рис. 1) укажите предполагаемый диапазон, в котором будет размещаться таблица, и установите флажок Таблица с заголовками.
Рис. 1.
К данному диапазону будет применено форматирование, т.е. применен экспресс-стиль таблицы, заданный по умолчанию, при этом активизируются контекстные инструменты под общим названием "Работа с таблицами", которые входят в контекстную вкладку Конструктор. Для форматирования таблицы можно также применить средство "Форматировать как таблицу" на вкладке "Главная".
Рис. 2.
Для построения отчета по этой таблице целесообразно применить мощное средство "Сводная таблица". Для применения этого средства к спискам данных или к таблицам данных необходимо активизировать одну из ячеек таблицы данных, например ячейку таблицы "Остатки товаров на складе". Затем щелкнуть кнопку "Сводная таблица", которая находится на вкладке "Вставка" в группе "Таблица" (рисунок 3).
Рис. 3.
В открывшемся окне диалога "Создание сводной таблицы" требуется выбрать (выделить) таблицу или диапазон и указать, куда следует поместить отчет (желательно на новый лист), затем щелкнуть ОК. Откроется специальный мастер сводных таблиц (рисунок 4).
Рис. 4.
В левой части рабочего листа отображается изображение отчета (Сводная Таблица1), а в правой части листа расположены инструменты для создания сводной таблицы: четыре пустых областей и список полей. Для построения отчета надо в правой части перетащить требуемые поля в соответствующие области сводной таблицы: "Фильтр отчета", "Название столбцов", "Название строк" и "Значения".
Например, если выбрать поля: № склада, Наименование, Цена (грн.) и перетащить их в соответствующие области: "Название столбцов", "Название строк" и "Значения", то в правой части они будут отображаться в этих областях. При этом в левой части рабочего листа будет построена сводная таблица или отчет (рис. 5).
Рис. 5.
Следует отметить, что в области "Значения" выполняются какие-либо математические вычисления, например, суммирование (Сумма по полю Цена). Чтобы изменить тип вычислений, надо в области "Значения" щелкнуть левой кнопкой мыши по полю "Сумма по полю Цена" и в открывшемся меню выбрать команду "Параметры полей значений", затем в окне диалога "Параметры поля значений" выбрать требуемую функцию и щелкнуть на кнопке ОК.
Для изменения структуры сводной таблицы надо в правой части листа перетащить поля в другую область сводной таблицы или удалить. Необходимо отметить, что для удаления поля надо перетащить его за пределы таблицы.
Мощными средствами анализа данных Excel 2007 являются:
Средства анализ "что – если" помещены на вкладке "Данные" в группе "Работа с данными", а "Поиск решений" на вкладке "Данные" в группе "Analysis".
Подбор параметров обеспечивает решение задачи подбора параметра для функции одного аргумента. Диспетчер сценариев предназначен для создания сценариев (сначала добавляются несколько сценариев для различных случаев), а затем выполняется просмотр сценариев с целью прогнозирования процесса и создание отчета по сценарию.
Программа "Поиск решений" предназначена для решения сложных систем уравнений, линейных и нелинейных задач оптимизации. В основе надстройка Solver лежат итерационные методы.
Программа Excel – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных» . Давайте выясним, как его можно включить.
Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных» , нужно активировать группу инструментов «Пакет анализа» , выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.
После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.
Теперь мы можем запустить любой из инструментов группы «Анализ данных» .
Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.
Как видим, хотя блок инструментов «Пакет анализа» и не активирован по умолчанию, процесс его включения довольно прост. В то же время, без знания четкого алгоритма действий вряд ли у пользователя получится быстро активировать эту очень полезную статистическую функцию.
ЗАДАНИЕ № 1
Статистический анализ данных в программе MS Excel
Цель работы : научиться обрабатывать статистические данные с помощью встроенных функций MS Excel ; изучить возможности Пакета анализа и его инструменты: «Генерация случайных чисел» , «Гистограмма» , «Описательная статистика» на примере обработки измерений скорости движения.
В соответствии с методическими указаниями к лабораторной работе «Измерение скорости движения автомобилей» (по дисциплине «Изыскание и проектирование автомобильных дорог») обработать экспериментальные данные измерений методами математической статистики в программе Excel. Для чего:
1. Вычислить статистические характеристики, используя встроенные функции: - минимальное значение скорости движения Vмин;
Максимальное значение скорости движения Vмакс; - среднее значение скорости движения Vср;
Стандартное отклонение S;
Стандартное отклонение среднего Sср;
Коэффициент Стьюдента (для определения доверительного интервала) t; - доверительный интервал для Р = 0.95.
2. Получить статистические характеристики, используя инструмент « Описательная статистика » из дополнительного пакета «Анализ данных ».
3. Построить гистограмму распределения скорости движения.
4. Построить кумулятивную кривую (кривую накопленной частости).
5. Построить теоретическую кривую распределения скорости движения.
Для получения достаточного количества исходных данных (результатов измерений скорости) использовать имитационный эксперимент с помощью инструмента «Генерация случайных чисел » дополнения «Анализ данных ».
При выполнении п.п. 3 и 4 подобрать интервал скоростей («карман» – в терминологии Excel), позволяющий получить наиболее симметричную гистограмму, демонстрирующую нормальный закон распределения.
Образец выполнения приведен в прилагаемом файле ОсновыПК1-Студент.xls.
Методические указания
Предположим, что мы проделали серию из 10 опытов, измеряя некоторую величину Х. Таблица 1. Примерный вид листа «Обработка эксперимента»
Записи в колонках D и Е – это подсказки, которые помогут разобраться с тем, какие характеристики мы будем рассчитывать. Колонка F у Вас должна быть пока пустой, в нее будут помещены наши формулы.
Обработку результатов начнем с расчета числа опытов n .
Для определения числа значений используется специальная функция, которая называется СЧЕТ . Для ввода формулы с функциями используется Мастер функций , который запускается командой «Вставка функции» через меню «Вставка» – «Функция» или кнопкой на панели инструментов с обозначением f x .
Щелкнем мышкой по ячейке F6 , где должен находиться результат и запустим Мастер функций.
Первый шаг работы (рисунок 1) служит для выбора нужной функции.
Для обработки данных эксперимента используются статистические функции. Поэтому, прежде всего в списке категорий выбираем категорию «Статистические ». Во втором окне появляется список статистических функций.
Список функций упорядочен по алфавиту, что позволяет без труда найти нужную нам функцию СЧЕТ («Подсчитывает количество чисел в списке аргументов»).
Выделив щелчком эту функцию, нажимаем кнопку Ok и переходим к шагу 2.
Второй шаг (рисунок 2) служит для задания аргументов функции.
Функции СЧЕТ надо указать, какие числа ей надо пересчитывать, или в каких ячейках находятся эти числа. Следующие два этапа обработки серии опытов проводятся аналогично.
В ячейке F7 c помощью функции СРЗНАЧ рассчитывается среднее значение выборки, в ячейке F8 – стандартное отклонение выборки, с помощью функции СТАНДОТКЛОН. .
Аргументами этих функций служит все тот же диапазон ячеек.
Для расчета доверительного интервала необходимо определить коэффициент Стьюдента. Он зависит от вероятности ошибки (при обычно задаваемой надежности 95% вероятность ошибки составляет 5%), и от числа степеней свободы n-1 ).
Для нахождения коэффициента Стьюдента используется статистическая функция Excel СТЬЮДРАСПОБР (“Стьюдента распределение обратное“). Особенностью этой функции является то, что первый аргумент, число 5% (или 0,05) вводится в соответствующее окно с клавиатуры. Для второго указываем адрес ячейки, где находится значение n , затем дописываем в окне “-1”. Получаем запись “F6-1 ”.
Для нахождения доверительного интервала используется обычная формула умножения. Конечно, вместо букв там должны стоять адреса ячеек, где находятся коэффициент Стьюдента и стандартное отклонение среднего. Как правило, значение доверительного интервала округляется до одной значащей цифры, такой же порядок окружения должен быть и у среднего. Поэтому окончательный результат можно записать так: с 95%-ной надежностью Х = 14,80±0,05 . В заключение посчитаем относительную ошибку определения Х: = ДИ / Х ср (формула: “=F11/F7 ”). Значение относительной ошибки обычно выражают в процентах, у нас 0,3%.
Для выполнения заданий 2 и 3 используется надстройка «Пакет анализа» (из меню Сервис .Анализ данных Гистограмма).
Для установки надстройки вызвать меню Сервис Надстройки и из предлагаемого списка доступных к установке надстроек выбрать «Пакет анализа» (см. Установка надстроек
Excel на компьютере.doc).
