Как сделать статистическую обработку данных в excel?

Применение описательной статистики в Microsoft Excel

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

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

Использование описательной статистики

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

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

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

  1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».

  • Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».
  • После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

    Применение инструмента «Описательная статистика»

    Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

      Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».

    Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».

    После выполнения данных действий непосредственно запускается окно «Описательная статистика».

    В поле «Входной интервал» указываем адрес диапазона, который будет подвергаться обработке этим инструментом. Причем указываем его вместе с шапкой таблицы. Для того, чтобы внести нужные нам координаты, устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем на листе соответствующую табличную область. Как видим, её координаты тут же отобразятся в поле. Так как мы захватили данные вместе с шапкой, то около параметра «Метки в первой строке» следует установить флажок. Тут же выбираем тип группирования, переставив переключатель в позицию «По столбцам» или «По строкам». В нашем случае подходит вариант «По столбцам», но в других случаях, возможно, придется выставить переключатель иначе.

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

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

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

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

    Читать еще:  Как сделать сглаживание графика в excel?

    После того, как все указанные данные внесены, жмем на кнопку «OK».

    После выполнения этих действий таблица с описательной статистикой выводится на отдельном листе, который был нами назван «Итоги». Как видим, данные представлены сумбурно, поэтому их следует отредактировать, расширив соответствующие колонки для более удобного просмотра.

  • После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.
  • Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

    Как видим, с помощью инструмента «Описательная статистика» можно сразу получить результат по целому ряду критериев, которые в ином случае рассчитывались с применением отдельно предназначенной для каждого расчета функцией, что заняло бы значительное время у пользователя. А так, все эти расчеты можно получить практически в один клик, использовав соответствующий инструмент — Пакета анализа.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Статистическая обработка данных с помощью Excel

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

    ,

    где x1, x2, . , xn – значения случайной величины X , n – число измерений. Оно широко используется в грубоориентировочных расчетах случайной величины, когда значение случайной величины заменяют ее средним.

    Таким образом, в данном примере = , т.е. в среднем каждый день в каждом из рассматриваемых магазинов бывает 700/7= 100 покупателей

    Важно также знать, как сильно значения изучаемой величины отличаются от ее среднего, или, иначе говоря, насколько широк разброс случайной величины. Рассеивание случайной величины вокруг ее среднего характеризует дисперсия D[X]. Чем больше дисперсия, тем «случайнее» случайная величина. Для приближенного значения дисперсии дискретной случайной величины X используют следующую формулу:

    .

    На практике часто используют и другую характеристику рассеивания – среднеквадратичное отклонение sx, вычисляемое по формуле . Величина sx также характеризует размах колебаний случайной величины X около среднего значения, но sx, в отличие от D[X], имеет ту же размерность, что и случайная величина X.

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

    Excel предусматривает также применение 18 статистических инструментов анализа, в том числе такие, как описательная статистика, гистограмма, генерация случайных чисел, корреляция, ковариация и ряд других. Эти инструменты позволяют автоматизировать анализ данных и статистических параметров. Доступ к ним можно получить, выбрав в меню Сервис команду Анализ данных. Затем в диалоговом окне Инструменты анализа следует выбрать нужный инструмент и задать входной и выходной интервалы, а также другие требуемые параметры. Например, инструмент анализа Описательная Статистика создает список одномерных статистических характеристик для данных во входном интервале. Инструмент Описательная Статистика генерирует, в частности, следующие выходные значения: дисперсию выборки, среднеквадратичное отклонение, медиану, моду и скос. Подробнее об этих функциях можно прочитать в соответствующих разделах встроенной Справки.

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

    Лабораторная работа № 8.

    Создание простых таблиц, автоматическое заполнение, автосуммирование. Форматирование в Excel

    Цель работы: научиться осуществлять перемещение по таблице, ввод/удаление данных в таблицу; изучить возможности форматирования и автозаполнения; производить автосуммирование.

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

    — создание, открытие, закрытие, сохранение книги;

    — добавление, переименование рабочего листа книги;

    — ввод чисел и формул в ячейку;

    — форматирование содержимого ячейки, блока ячеек;

    — автозаполнение числами, формулами;

    — относительные и абсолютные ссылки;

    — использование функций и формул в таблице;

    — работа с данными из разных книг;

    — скрытие и отображение блока столбцов (строк).

    Как сделать статистическую обработку данных в excel?

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

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

    Читать еще:  Как сделать продолжение таблицы в excel?

    1. Характеристика пакета Excel

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

    Каждая единица информации занимает свою собственную ячей­ку (клетку) в создаваемой рабочей таблице. В каждой рабочей таблице 256 столбцов (из которых в новой рабочей таблице на экране видны, как правило, только первые 10 или 11 (от А до J или К) и 65 536 строк (из которых обычно видны только первые 15-20). Каждая новая рабочая книга содержит три чистых листа рабочих таблиц.

    Вся помещаемая в электронную таблицу информация хранится в от­дельных клетках рабочей таблицы. Но ввести информацию можно только в текущую клетку. С помощью адреса в строке формул и табличного курсора Excel ука­зывает, какая из клеток рабочей таблицы является те­кущей. В основе системы адресации клеток рабочей таблицы лежит комбинация буквы (или букв) столбца и номера строки, например A 2, B 12.

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

    2 Использование специальных функций

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

    Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, . — это от 1 до 30 массивов для которых вычисляется среднее.

    Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана — это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

    Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

    Функция ДИСП позволяет оценить дисперсию по выборочным данным.

    Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

    Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным.

    Функция СКОС позволяет оценить асимметрию выборочного распределения.

    Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет формат КВАРТИЛЬ(массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

    Пример 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

    Задания для самостоятельной работы

    1. Наблюдение посещаемости четырех внеклассных мероприятий в экспериментальном (20 человек) и контрольном (30 человек) классах дали значения (соответственно): 18, 20, 20, 18 и 15, 23, 10, 28. Требуется найти среднее значение, стандартное отклонение, медиану и квартили этих данных.

    2. Найти среднее значение, медиану, стандартное отклонение и квартили результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

    3. Определите верхнюю и нижнюю квартиль, выборочную асимметрию и эксцесс для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

    4. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

    3. Использование инструмента Пакет анализа

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

    Для установки пакета Анализ данных в Excel сделайте следующее:

    — в меню Сервис выберите команду Надстройки;

    — в появившемся списке установите флажок Пакет анализа.

    Для использования статистического пакета анализа данных необходимо:

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

    Задание для самостоятельной работы

    1. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг./м 3 ): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

    Обработка статистических данных в Excel

    Курс повышения квалификации за 340 рублей!

    Эмоциональное выгорание педагогов. Профилактика и способы преодоления

    Выбранный для просмотра документ Обработка статистических данных часть1.docx

    Читать еще:  Как сделать чтобы первая строка в excel всегда была видна?

    Тема: Обработка статистических данных .

    Цель : научиться проводить сбор данных и проводить простейшую их статистическую обработку.

    После работы Вы должны знать ответы на следующие вопросы :

    Что такое ряд данных ?

    Что такое ранжированный ряд данных?

    Что такое Ч астота данных и как её определить?

    Что такое ОТНОСИТЕЛЬНАЯ ЧАСТОТА данных и как её определить?

    Что такое математическое ожидание и правило его вычисления?

    Что такое МОДА и правило её определения?

    Что такое МЕДИАНА и правило её определения?

    Проведите опрос своих одноклассников и запишите полученные данные в порядке их получения.

    Темы для опроса:

    Успеваемость по предмету (выберите какой-то один на своё усмотрение)

    Антропометрические данные (рост, вес, частота пульса и пр.)

    Количество времени, которое ученик тратит на подготовку домашнего задания.

    Количество времени, которое ученик проводит в социальных сетях.

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

    Количество времени, которое ученик отводит на сон.

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

    Количество опрошенных, т.е. количество измеренных показателей, обозначьте N .

    По результатам опроса составьте таблицу сбора данных (обозначим исследуемый параметр Х ).

    Составьте РАНЖИРОВАННЫЙ РЯД данных (расположите полученные данные в порядке возрастания)

    Составьте ряд частот .

    Для каждого показателя определите сколько раз он повторяется и составьте таблицу

    Значение измеряемого параметра )

    Сделайте проверку: сумма всех частот должна равняться количеству всех измерений N : N 1 + N 2 + … + N k = N

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

    Составьте ряд относительных частот

    ОТНОСИТЕЛЬНАЯ ЧАСТОТА определяется как отношение частоты параметра к общему количеству измерений:

    Рассчитайте СРЕДНЕЕ ЗНАЧЕНИЕ параметра ( X ср ):

    Среднее значение параметра рассчитывается как среднее арифметическое данных чисел.

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

    Определите МОДУ данного ряда. МОДА — самое популярное значение, т.е. измеренный показатель встречается чаще всего. Для определения МОДЫ используйте таблицу частот.

    Определите МЕДИАНУ данного ряда. МЕДИАНА — середина измерений. Для определения МЕДИАНЫ используйте ранжированный ряд.

    Домашнее задание: продумайте какие средства Excel можно использовать для оформления данной работы.

    Это будет тема вашей следующей практической работы.

    Выбранный для просмотра документ Обработка статистических данных часть2.docx

    Тема: Обработка статистических данных в Excel

    Цель : научиться проводить сбор данных и проводить простейшую их статистическую обработку в Excel

    После работы Вы должны знать ответы на следующие вопросы :

    Как производятся расчеты в Excel ?

    Формат функции НАИМЕНЬШИЙ .

    Формат функции СЧЕТ .

    Формат функции СЧЕТЕСЛИ .

    Формат функции СУММА .

    Формат функции СРЕДНЕЕ ЗНАЧЕНИЕ .

    Формат функции МОДА .

    Формат функции МЕДИАНА .

    Алгоритм построения диаграммы в Excel .

    Проведите опрос своих одноклассников и запишите полученные данные в порядке их получения.

    Темы для опроса:

    Успеваемость по предмету (выберите какой-то один на своё усмотрение)

    Антропометрические данные (рост, вес, частота пульса и пр.)

    Количество времени, которое ученик тратит на подготовку домашнего задания.

    Количество времени, которое ученик проводит в социальных сетях.

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

    Количество времени, которое ученик отводит на сон.

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

    Оформите полученные данные и проведите их обработку средствами Excel . Ниже приведен пример с комментариями.

    Пример:

    Добавляйте авторские материалы и получите призы от Инфоурок

    Еженедельный призовой фонд 100 000 Р

    Практикум по теме «Обработка статистических данных в Excel» 10-11 класс. Работа состоит из двух частей. В первой части учащиеся знакомятся со статистическими характеристиками, проводят сбор данных. А во второй осваивают встроенные функции Excel для их обработки.

    Работа подготовлена для распечатки в формате 2 страницы на 1 листе.

    • Степанова Жанна Владимировна
    • Написать
    • 914
    • 10.10.2017

    Номер материала: ДБ-743150

    Международные дистанционные олимпиады «Эрудит III»

    Доступно для всех учеников
    1-11 классов и дошкольников

    Рекордно низкий оргвзнос

    по разным предметам школьной программы (отдельные задания для дошкольников)

    Идёт приём заявок

    • 10.10.2017
    • 594
    • 10.10.2017
    • 212
    • 10.10.2017
    • 250
    • 10.10.2017
    • 644
    • 10.10.2017
    • 204
    • 10.10.2017
    • 1154
    • 10.10.2017
    • 871
    • 10.10.2017
    • 910

    Не нашли то что искали?

    Вам будут интересны эти курсы:

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

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

    Ссылка на основную публикацию
    Adblock
    detector