Решение статистических задач в excel
В первой части пособия изложены инструкции по реализации расчетов в среде MS EXCEL следующих статистических характеристик: среднего арифметического, дисперсии, среднего квадратического отклонения, цепных и базисных показателей динамики, средних динамического ряда, агрегатных индексов. Методики проиллюстрированы примерами.
Вторая часть содержит описание команд EXCEL, используемых для решения статистических задач практикума.
В последней части даны задания для самостоятельного решения.
К практикуму прилагается диск с дополнительными заданиями для самостоятельной работы и примерами расчетов статистических характеристик в MS EXCEL.
Раздел 1. Вычисление базовых статистических показателей.
Средняя арифметическая величина может вычисляться по простой и взвешенной формулам.
Простая средняя арифметическая
Для расчетов в Excel используется функция:
СРЗНАЧ (диапазон данных).
Рассмотрим методику расчета на следующем примере: определить среднюю зарплату в подразделении
1. Исходные значения признака хi надо записать в массив ячеек расположенных в столбце или строке (в примере в строке A2-L2);
2. Количественные данные следует определить как «Числовые».
1. В ячейку результата (например «Е3») установить функцию СРЗНАЧ (А2:L2).
Функция находит простое среднее арифметическое для заданных хi в диапазоне (А2:L2).
Взвешенная средняя арифметическая
Для расчета взвешенной средней арифметической эффективно использовать функции СУММ (диапазон данных) и СУММПРОИЗВ (диапазоны перемножаемых данных).
Взвешенная форма используется как правило для:
· непрерывных сгруппированных данных.
Взвешенная средняя арифметическая для
дискретных данных
Рассмотрим методику расчета на примере расчета среднего размера семей в группе:
1. Исходные значения признака хi и частоту fiнадо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (O13:T13) и (O14:T14).
2. Количественные данные следует определить как «Числовые».
1. В ячейку результата (например «R16») установить функцию СУММПРОИЗВ(O13:T13;O14:T14) реализует числитель
Функция выполняет попарное умножение элементов массива хi в ячейках (O13:T13)на элементы массива fi , в ячейках (O14:T14), а затем суммирует произведения.
СУММ (O14:T14) – знаменатель
Функция суммирует элементы массива fi , в ячейках O14:T14.
«/» - знак деления.
Взвешенная средняя арифметическая для
непрерывных сгруппированных данных
Рассмотрим методику расчета на примере расчета средних затрат на выпуск 1 тыс. руб. продукции.
1. Значение признака х задано интервалом. Для ввода интервала используйте 2 ячейки (в примере используются столбцы V и W). Левые границы интервала заданы в ячейках (V19:V23), правые (W19:W23). Частота fi записана в массив ячеек (Х19:Х23).
Ниже приведены условия задач и отчет в формате doc. Закачка полного решения(документы doc и xlsx в архиве zip) начнется автоматически через 10 секунд. Если закачка не началась, кликните по этой ссылке.
Видеурок по решению этих задач - внизу страницы.
Сгенерировать ряд значений переменной «Доход», которая является случайной величиной, распределенной по нормальному закону с параметрами М и , где М — среднее значение (математическое ожидание) случайной величины, — стандартное (среднеквадратическое) отклонение.
Использование инструмента « Генерация случайных чисел »
Данный инструмент предназначен для заполнения диапазона ячеек случайными числами, распределенными по какому-либо закону . С помощью данной процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей. Чтобы сгенерировать на рабочем листе ряд значений случайной величины, нужно выполнить команду Сервис—Анализ данных— Генерация случайных чисел— OK и заполнить открывшееся диалоговое окно.
Рис 1. Диалоговое окно «Генерация случайных чисел»
1) Число переменных — это количество столбцов, в которых будут выведены случайные величины в соответствии с заданным законом распределения;
2) Число случайных чисел — количество ячеек со значениями случайной величины в каждом столбце;
3) Распределение — из списка выбирается один из законов;
Нормальное распределение — наиболее распространенный закон, описывающий случайные явления, для которых характерно воздействие на конечный результат многих независимых случайных факторов, причем среди этих факторов нет особенно выделяющихся. Применяется для описания непрерывных величин. Характеризуется двумя параметрами: средним значением и стандартным отклонением.
4) Параметры — зависят от выбранного закона:
5) Случайное рассеивание — число, которое позволяет при повторной генерации получать те же числа, что и при первом запуске генератора;
6) Параметры вывода — указывается верхняя левая ячейка на текущем листе, начиная с которой будут выведены случайные числа, можно также указать вывод на новый лист или в новый файл.
В нашем случае, для генерации 36 значений одной переменной, распределенной по нормальному закону с параметрами: М=11; =1,95 , нужно заполнить диалог так, как показано на рис. 1. Тогда после нажатия ОК диапазон ячеек В4:В39 будет заполнен значениями случайной величины (см. рис. 2).
Вычислить, используя статистические функции, характеристики сгенерированного ряда: среднее значение, стандартное отклонение, медиана, асимметричность, минимум.
Чтобы найти характеристики сгенерированного ряда , нужно выполнить команду Сервис—Анализ данных— Описательная статистика— OK и заполнить открывшееся диалоговое окно.
Далее заполняем диалоговое окно . Вводим входной интервал , указываем параметры вывода . В результате получим данные , показанные на рисунке 3.
Для каждого значения случайной величины вычислить интегральную функцию распределения вероятности и процентранг.
Для вычисления плотности и интегральной функции используем функцию НОРМРАСП, так как в примере 1 была сгенерирована величина, распределенная по нормальному закону с параметрами: М=11; =1,95.
1) В ячейку В2 вводим формулу = НОРМРАСП(A2;$G$2;$G$3;0)и протягиваем ее маркером до ячейки В37. Результат вычисления плотности распределения вероятности приведен на рис. 4.
2) В ячейку С2 вводим формулу = НОРМРАСП(A2;$G$2;$G$3;1) и протягиваем ее маркером до ячейки С37. Результат вычисления интегральной функции распределения приведен на рис. 4.
3) В ячейку D 2 вводим формулу = ПРОЦЕНТРАНГ($A$2:$A$37;A2) и протягиваем ее маркером до ячейки D37. Результат вычисления интегральной функции распределения приведен на рис. 4.
Найти распределение частот для сгенерированного ряда. Количество интервалов разбиения ( n ) взять из таблицы:
1) найти максимальное ( S max ) и минимальное ( S min ) значение ряда (ячейки D 6 и D 7);
2) вычислить длину интервала (кармана) по формуле (ячейка D 5);
3) вычислить границу первого кармана по формуле L 1 = S min + (ячейка D 8);
4) протянуть формулу еще на 5 ячеек для получения границ остальных карманов, т.е. L 2 = L 1 + , … . Если все было сделано правильно, то верхняя граница последнего кармана должна быть равна максимальному значению ряда, т.е. L n = S max . Таким образом получено шесть карманов: в первый попадают значения ряда, соответствующие условию S i L 1 ; во второй — L 1 < S i L 2 ; …
Рис. 5. Распределение частот для ряда случайной величины
5) по функции ЧАСТОТА вычислить количество значений случайной величины, попадающих в каждый карман. Для этого:
- выделить диапазон ячеек, соответствующий количеству карманов: F 8: F 12;
- вставить функцию и заполнить аргументы, как показано на рисунке:
- не нажимая кнопку ОК, завершить ввод формулы комбинацией клавиш
Ctrl + Shift + Enter ;
- в результате формула в строке формул будет заключена в фигурные скобки, а выделенный диапазон ( F 8: F 12) будет заполнен значениями.
Статистический анализ в MS Excel. Дисперсионный анализ, регрессии, метод наименьших квадратов.
Двухфакторный дисперсионный анализ с повторениями в MS EXCEL
Пусть имеется случайная переменная Y, значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от 2-х факторов, значения которых мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …
Однофакторный дисперсионный анализ (one-way ANOVA) в MS EXCEL
Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от фактора, значения которого мы можем контролировать, т.е. задавать с требуемой точностью. Покажем …
Функция MS EXCEL ЛИНЕЙН()
Функция ЛИНЕЙН() специально создана для оценки параметров линейной регрессии, а также для вывода регрессионной статистики (коэффициента детерминации, стандартных ошибок, F -статистики и др.).
Использование Пакета анализа MS EXCEL для выполнения однофакторного дисперсионного анализа
Выполним однофакторный дисперсионный анализ с помощью надстройки MS EXCEL Пакет анализа .
Двухфакторный дисперсионный анализ без повторений в MS EXCEL
Решим задачу о сравнении средних значений нескольких выборок с использованием дисперсионного анализа в случае двух факторов без повторений (Two Factor ANOVA without Replication). Подход используемый для решения данной задачи имеет …
Использование Пакета анализа MS EXCEL для выполнения двухфакторного дисперсионного анализа (без повторений)
Выполним двухфакторный дисперсионный анализ без повторений с помощью надстройки MS EXCEL Пакет анализа .
МНК: Степенная зависимость в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью степенной функции.
МНК: Приближение полиномом в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).
Простая линейная регрессия в MS EXCEL
Регрессия позволяет прогнозировать зависимую переменную на основании значений фактора. В MS EXCEL имеется множество функций, которые возвращают не только наклон и сдвиг линии регрессии, характеризующей линейную взаимосвязь между факторами, но …
Множественная регрессия в MS EXCEL
Рассмотрим использование MS EXCEL для прогнозирования переменной Y на основании нескольких переменных Х, т.е. множественную регрессию.
Использование Пакета анализа MS EXCEL для выполнения двухфакторного дисперсионного анализа (с повторениями)
Выполним двухфакторный дисперсионный анализ с повторениями с помощью надстройки MS EXCEL Пакет анализа .
МНК: Метод Наименьших Квадратов в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + …
МНК: Экспоненциальная зависимость в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.
МНК: Приближение тригонометрическим полиномом в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью тригонометрического полинома.
Генерация данных для простой линейной регрессии в MS EXCEL
Сгенерируем массив данных для целей простой линейной регрессии. Линейный тренд зададим уравнением: Y = k * X + m .
Проверка значимости регрессии с помощью дисперсионного анализа (F-тест)
Проведем проверку значимости простой линейной регрессии с помощью процедуры F -тест.
Использование Пакета анализа MS EXCEL для построения простой линейной регрессионной модели
Проведем простой регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .
Подробный разбор задачи (примера) множественной регрессии в MS EXCEL
Рассмотрим пример построения модели множественной регрессии в случае 2-х регрессоров в MS EXCEL.
Использование Пакета анализа MS EXCEL для построения множественной линейной регрессионной модели
Проведем множественный регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .
МНК: Логарифмическая зависимость в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью логарифмической функции.
МНК: Квадратичная зависимость в MS EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax 2 +bx+с .
Количественные данные следует определить как «Числовые».
2. Для выполнения расчета необходимо закрыть имеющиеся открытые интервалы – первый – «до 500», последний - «от 700».
Формула вычисления левой границы первого интервала вводится в ячейку Z19 : «=АА19-(АА20-Z20)» (– из ячейки АА19 вычесть разницу между содержимым ячейки АА20 и Z20).
Правая граница последнего интервала в ячейку АА23 устанавливается формулой «=Z23+АА22-Z22» (от значения в ячейке Z23 откладывается размер предшествующего интервала «АА22-Z22»).
3. Рассчитывается среднее по каждой группе , как середина интервала. Для этого в ячейку первой группы (АС19) устанавливается функция СРЗНАЧ(Z19;AA19). После появления среднего значения первого интервала формула копируется в соседние ячейки. При этом автоматически смещаются координаты исходных данных в соответствии со смещением координат ячейки результата.
4. Рассчитывается величина средней взвешенной (в примере в ячейку АВ25)
СУММПРОИЗВ (АС19:АС23;АВ19:АВ23) реализует числитель
СУММ (АВ19:АВ23) – знаменатель;
«/» - знак деления.
Дисперсия. Среднее квадратическое
отклонение.
Дисперсия и среднее квадратическое отклонение (СКО) могут вычисляться по простой и взвешенной формулам.
Дисперсия, среднее квадратическое отклонение
по простой форме.
Для расчетов дисперсии по простой форме в Excel используется функция:
ДИСПР (диапазон данных).
СКО определяется как квадратный корень дисперсии, реализуемый оператором возведения в степень «^».
Рассмотрим методику расчета на примере расчета дисперсии и СКО зарплаты подразделения:
1. Исходные значения признака хi надо записать в массив ячеек расположенных в столбце или строке (в примере в строке (AF42:AQ42)).
Количественные данные следует определить как «Числовые».
2. В ячейку результата дисперсии (например «AF44») установить функцию ДИСПР(AF42:AQ42)
1. В ячейку результата СКО (например «AР44») установить функцию (ДИСПР(AF42:AQ42))^0,5.
Знак «^0,5» - означает возведение в степень 0,5 величины стоящей перед ним.
Дисперсия, среднее квадратическое
отклонение по взвешенной форме.
Для расчетов дисперсии по взвешенной форме в Excel используется функция:
СУММ (диапазон данных) и
СУММПРОИЗВ (диапазоны перемножаемых данных),
СКО определяется как квадратный корень дисперсии, реализуемый оператором возведения в степень «^».
Рассмотрим методику расчета на примере расчета дисперсии и СКО размера семьи группы
1. Исходные значения признака хi и частоту fi надо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (АТ46:AY46) и (АТ47:AY47).
Количественные данные следует определить как «Числовые».
1. Рассчитать среднее арифметическое взвешенное - в примере в ячейке AW49 установлена формула =СУММПРОИЗВ (AT46:AY46;AT47:AY47)/СУММ(AT47:AY47)
Одобрено на заседании кафедры «ЭАБУА», протокол №2 от 08.02.2006 г.
Рекомендовано Ученым советом ИЭУПС, протокол №1 от 15.02.2006 г.
Статистика. Практикум «Решение статистических задач в EXCEL» для студентов специальности 080105.65 (060400), 080109.65 (060500), 080502.65 (060800) «Бухгалтерский учет, анализ и аудит» / - СПб.: Изд-во СПбГУСЭ, 2008г.- 54с.
Составители: канд. тех. наук М.И. Лубочкина
Рецензент: канд. экон. наук, доцент Е.В. Ялунер
Ó Санкт-Петербургский государственный университет
сервиса и экономики
Раздел 1. Вычисление базовых статистических показателей. 5
Среднее арифметическое. 5
Простая средняя арифметическая. 5
Взвешенная средняя арифметическая. 6
Взвешенная средняя арифметическая для дискретных данных. 6
Взвешенная средняя арифметическая для непрерывных сгруппированных данных. 7
Дисперсия. Среднее квадратическое отклонение. 9
Дисперсия, среднее квадратическое отклонение
по простой форме. 9
Дисперсия, среднее квадратическое отклонение по взвешенной форме. 10
Показатели динамики. 11
Цепные показатели динамики. 11
Базисные показатели динамики. 12
Средние хронологические. 14
Средние хронологические с равными интервалами. 14
Средние хронологические с равно отстоящими моментами. 14
Средние хронологические с неравно отстоящими моментами. 15
Агрегатные индексы. 17
Раздел 2. Используемые в практикуме команды Excel. 20
Задание массивов ячеек исходных данных. 20
Задание данным формата. 20
Абсолютные и относительные координаты (ссылки). 21
Ввод функции в ячейку. 23
Копирование формул и функций. 24
Раздел 3. Задания для самостоятельного
решения. 26
Данный практикум разработан в соответствии с требованиями государственных стандартов к содержанию предмета «Статистика» для специальностей 060400, 060500, 060800, 061100.
Практикум соответствует рабочим программам Санкт-Петербургского государственного университета сервиса и экономики.
В практикуме изложена методика расчета наиболее используемых базовых статистических показателей средствами MS EXCEL.
Для выполнения практических работ студенты должны знать базовый курс «Общей теории статистики», курс по предмету «Информатика», владеть основными навыками работы в среде MS EXCEL.
Практикум планируется использовать для студентов дневной формы обучения во 2-ом семестре курса «Статистика», для ускоренной формы – в конце 1-го семестра.
Читайте также: