Аппроксимация данных в MS Excel. Линия тренда.

Тема № 1

Вычисление основных статистических показателей в MS Excel.

Аппроксимация данных в MS Excel. Линия тренда.

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

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

Пример.

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

 

Группа 1 (беродуал)	Группа 2 (сальбутамол)

 

Необходимо провести статистический анализ этих данных.

Решение.

1. Для проведения статистического анализа необходимо ввести данные в рабочую таблицу. Введите в ячейку А1 Группа 1 (беродуал), затем в ячейки А2:А8 – соответствующие значения частот сердечных сокращений. Аналогично в ячейки В1:В8 введите значения второй группы.

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

· установите табличный курсор в ячейку А9;

· вызовите Мастер функций, нажав кнопку на панели инструментов, или, воспользовавшись командой ВставкаðФункция;

· в появившемся диалоговом окне Мастер функций выберите категорию Статистические и функцию СРЗНАЧ, после чего нажмите кнопку Ok.

· в диалоговом окне СРЗНАЧ в поле Число 1 введите диапазон данных группы 1 А2:А8;

· нажмите кнопку Ok.

В ячейке А9 появится среднее значение выборки – 81, 143.

3. Для определения стандартного отклонения в группе 1

· установите табличный курсор в ячейке А10;

· вызовите Мастер функций, выберите категорию Статистические и функцию СТАНДОТКЛОН, нажмите Ok

· в диалоговом окне СТАНДОТКЛОН в поле Число 1 введите диапазон А2:А8, нажмите кнопку Ok.

В ячейке А10 появится стандартное отклонение выборки – 2,41.

G Существует правило, согласно которому при отсутствии артефактов данные должны лежать в диапазоне М ± 3σ (в примере 81, 143±7,23).

4. Для получения медианы заданной выборки в ячейке А11 воспользуйтесь функцией МЕДИАНА.

В ячейке А11 появится медиана выборки – 81.

5. Для оценки дисперсии по выборочным данным в ячейке А12 выберите функцию ДИСП.

Дисперсия выборки – 5,81.

G Форму эмпирического распределения позволяют оценить специальные функции ЭКСЦЕСС (вычисляет оценку эксцесса по выборочным данным) и СКОС (позволяет оценить асимметрию выборочного распределения).

6. Получите в ячейке А13 ЭКСЦЕСС = -0,503.

7. Получите в ячейке А14 СКОС = 0,367.

Самостоятельно определите основные статистические характеристики для группы 2.

 

Упражнения.

1. Частоты сердечных сокращений контрольной и исследуемой групп приведены в таблице:

Контроль	Исследование

Проведите статистический анализ данных

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

Аппроксимация данных в MS Excel. Линия тренда.

Одной из распространенных задач в науке, технике, экономике является аппроксимация экспериментальных данных, алгебраических данных аналитическими выражениями. Возможность подобрать параметры уравнения таким образом, чтобы его решение совпало с данными эксперимента, зачастую является доказательством (или опровержением) теории. Рассмотрим следующую математическую задачу. Известные значения некоторой функции f образуют таблицу: Таблица x x1 x2 ... xn f(x) y1 y2 ... yn Необходимо построить аналитическую зависимость y = f (x), наиболее близко описывающую результаты эксперимента. Построим функцию y = f (x, a0, a1,..., ak) таким образом, чтобы сумма квадратов отклонений измеренных значений yi от расчетных f (xi,a0, a1,..., ak) была наименьшей Математически эта задача равносильна следующей: найти значение параметров a0, a1, a2,...,ak, при которых функция принимала бы минимальное значение. Эта задача сводится к решению системы уравнений: Если параметры ai входят в зависимость y = f (x,ao, a1, …, ak) линейно, то мы получим систему линейных уравнений: Решив систему (8.3), найдем параметры ao, a1,..., ak и получим зависимость y = f (x, ao, a1,..., ak).