Статистическая обработка результатов

Программа Excel позволяет рассчитывать некоторые статистические параметры.

При повторных измерениях на одном и том же приборе одной и той же величины (например, измерениях оптической плотности одного и того же образца) для оценки точности измерительного устройства применяется величина стандартного отклонения (синонимы: среднеквадратичное отклонение, среднеквадратичная ошибка, standard deviation). Оно рассчитывается по формуле:

Ст. откл. =

В Microsoft Excel, однако, примененa иная форма математического выражения этого показателя (которое получается из предыдущего путем алгебраических преобразований):

Для того, чтобы рассчитать стандартное отклонение также используем «Мастер функций»:

· в статистических функциях выбираем «СТАНДОТКЛОН», щелкнуть «ОК»,

· указать интервал значение (номера ячеек значений) и нажать на «ОК».

· в ячейке появится значение стандартного отклонения.

Дисперсия(синоним: «разброс данных») отражает степень отклонения результатов измерений от среднего значения. Дисперсия равна квадрату стандартного отклонения (см. выше). В Microsoft Excel дисперсия рассчитывается по формуле:

. Для расчета дисперсии нужно:

• выделить ячейку,
• открыть «Мастер функций»,
• в статистических функциях выбрать «ДИСП»,
• задать интервал значений и нажать «ОК».

В выбранной ячейке появится значение дисперсии. -

Среднеквадратичная ошибка среднего (синонимы: стандартная ошибка, «погрешность», standard error) вычисляется по формуле:

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

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

Для того чтобы рассчитать этот параметр, нужно:

· определить квадратичное отклонение: .

v выделить «КВАДРОТКЛ»,

v щелкнуть «ОК».

· отметить номера ячеек (диапазон значений), по которым рассчитываем разброс;

· нажать «ОК».

· в ячейке появится значение квадратичного отклонения.

· выбрать новую ячейку, где будет рассчитано значение разброса.

· в строке формул для этой ячейке записать {=КОРЕНЬ («номер ячейки, где стоит значение квадратичного отклонения»/ n×(n-1)}.

§ Пояснение: Пусть, например, количество измерений 10, а квадратичное отклонение записано в ячейке В3, тогда запись в строке формул выглядит как: =КОРЕНЬ (В3/10*(10-1)).

· нажать на «ВВОД».

· в ячейке должно появится искомое значение стандартной ошибки по выборке.

Решение уравнений в программе Excel

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

Например, нужно решить уравнение с одним неизвестным. Это уравнение может быть квадратным, содержать экспоненты, тригонометрические и степенные функции.

Рассмотрим конкретные примеры.

v ПРИМЕР 1. Пусть нужно найти решение уравнения . Для этого:

Ø откроем новый файл Excel,

Рис. 1. Окно Microsoft Excel

	- Создать; после нажатие кнопки открывается новый документ
	- Открыть; после нажатие кнопки открывается папка с документами, там можно выбрать нужный документ
	- Сохранить; после нажатие кнопки документ сохраняется
	- Печать; после нажатие кнопки начинается печать документа
	- Предварительный просмотр; позволяет увидеть расположение текста, таблиц и рисунков на листе вашего документа, и как будет выглядеть документ после печать
	- Правописание; после нажатие кнопки запускается проверка орфографии текста документа
	- Вырезать; после нажатие кнопки выделенный фрагмент текста или таблицы перемещается в буфер обмена (область памяти доступная любой программе)
	- Копировать; после нажатие кнопки выделенный фрагмент текста или таблицы копируется в буфер обмена
	- Вставить; после нажатие кнопки выделенный фрагмент текста или таблицы вставляется из буфера обмена
	- Отменить; после нажатие кнопки последнее выполненное действие отменяется
	- Автосумма; после нажатие кнопки выделенный столбец или строка таблицы суммируются
	- Вставка функций; открывается «Мастер функций»
	- «Мастер диаграмм»; открывается окно «Мастера диаграмм»
	- Рисование; открывается панель рисования

Рис. 2. Кнопки панели инструментов.

Ø

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

Ø в ячейке рядом (например, ячейка В1) запишем левую часть уравнения в виде: =2*А1*А1*А1*А1+ А1*А1*А1- А1.

Ø выделим ячейку В1,

Ø в строке меню щелкнем «мышью» на «Сервис»,

Ø на открывшемся подменю выберем «Подбор параметра».

Ø откроется окно «Подбор параметра», в нем нужно заполнить все поля.

§ в поле «Установить в ячейке» запишем номер ячейки, где записана левая часть уравнения (в нашем примере это ячейка В1);

§ в поле «Значение»запишем правую часть нашего уравнения , которая равна 12 (пишем число «12»);

§ в поле «Изменяя значение ячейки» запишем номер ячейки, выбранной для x (в нашем примере ячейка А1).

· После того как все поля заполнены, щелкнем на «ОК».

· Откроется окно «Результат подбора параметра», где сообщается о результате подбора (решение уравнения). В ячейке А1 появится значение x. В рассматриваемом уравнении значение x получилось равным 1,5.

v ПРИМЕР 2. Пусть нужно решить систему, состоящую из двух уравнений (с двумя неизвестными):

· откроем новый файл Excel,

· выберем ячейки для x и для y (например, ячейка А1 и ячейка В1)

· запишем там какие-нибудь цифры или числа, чтобы ячейки не были пустыми (например, 1 и 3).

· в ячейке рядом (например, ячейка С1) запишем левую часть первого уравнения в виде: =А1*А1*А1- EXP(В1- А1*А1)+ В1*В1*(2.7- (А1/ (В1-1))).

· В ячейке D1запишем левую часть второго уравнения: =B1*B1-A1/(A1*A1-B1*B1)

· Выделим ячейку С1, в строке меню мышью войдем в подменю «Сервис»,

· на вкладке выбрать «Поиск решения». Откроется окно «Поиск решения».

· в этом окне нужно заполнить поля: «Установить целевую ячейку»- в нашем примере это ячейка С1(правая часть первого уравнения), «Равной»- отметим «значение», и напишем в поле рядом левую часть первого уравнения, т. е. 1,8. В поле «Изменяя ячейки» запишем номера ячеек для x и y (их можно выделить так: щелкнуть на стрелочку справа, и выделить курсором нужные ячейки) должна появиться такая надпись: $A$1:$B$1.

· внесем ограничения. Нашим ограничением будет второе уравнение системы.

· рядом с полем «Ограничения» щелкнуть на кнопку «Добавить». Откроется окно«Добавление ограничения». В поле «Ссылка на ячейку» напишем D1 , в поле рядом по стрелочке выберем символ равенства, в «Ограничении» напишем правую часть второго уравнения нашей системы, т. е. 1. Если все поля в окне заполнены, то нажимаем «ОК».

· в окне «Поиск решения» щелкнем на «Выполнить».

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

· в ячейках для А1 и В1 появятся значения X и Y. В рассматриваемом случае X = 0,161 и Y=-0,888.

· Примечание: Необходимо строго следить за правильностью записи уравнения в ячейку, не допуская пропуска знаков и скобок. Количество открытых скобок должно быть равно количеству закрытых. Кроме этого иногда вместо запятой в десятичном числе (например, 23,8) нужно писать точку. Это зависит от настроек программы Excel.

Построение графиков

Например, необходимо построить спектр поглощения вещества с помощьюMicrosoft Excel. Для этого нужно:

· войти в программу Excel

· в столбец «А» занести длины волн, при которых измерялась оптическая плотность образца,

· в столбец «В» занести измеренную величину оптической плотности данного соединения.

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

· если Вы измеряли оптическую плотность при длинах волн от 400 до 750 нм, с фиксированным шагом (например, через каждые 2 нм), для ускорения введение значений длин волн нужно:

v в столбец «А», под названием, в строке «2», впечатать число 400,

v в строке «3» число 402;

v выделить левой кнопкой мышки эти ячейки с числами,

v появится рамочка, потяните ее за правый краешек вниз и получите столбец с числами от 400 до 750, с шагом 2 нм.

· Величину оптической плотности при каждой длине волны нужно вносить вручную.

· после введения данных получатся 2 столбца чисел. Обязательно сохраните файл с данными (кнопка «Сохранить» на панели инструментов или в строке меню под кнопкой «ФАЙЛ»). Необходимо присвоить файлу имя и выбрать папку, в которую нужно сохранить файл (по умолчанию файл сохраняется в папке «Мои документы»).

· для построения графика (спектра поглощения образца) нужно:

v левой кнопкой мышки выделить эти столбцы с данными

v в левой верхней части экрана найти кнопку-иконку «МАСТЕР ДИАГРАММ» (название появляется, когда вы подводите к кнопке курсор) и нажать на нее.

v откроется окно «МАСТЕР ДИАГРАММ». Там нужно выбрать «СТАНДАРТНЫЕ», в них – «Тип»: «ТОЧЕЧНАЯ», а «Вид» (вы видите примерное изображение своего графика) – например, точечную диаграмму со сглаженными линиями без маркеров. После того как вы выбрали тип и внешний вид графика нажмите внизу окна «МАСТЕР ДИАГРАММ» кнопку «Далее».

v в окне «Диапазон данных» вы увидите как выглядит график, нажимаете кнопку «Далее».

v в поле «Заголовки» вы можете вписать название диаграммы, например, «Спектр поглощения (название соединения)», названия оси «X» и оси «Y».

v в поле «Оси» можно убрать ось «X» или ось «Y», удалив галочку рядом с надписью оси.

v в поле «Линии сетки» можно убрать или добавить основные или промежуточные линии параллельные осям.

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

v в «Подписи данных»ставим точку рядом с «Нет».

v нажмите внизу окна «МАСТЕР ДИАГРАММ» кнопку «Далее».

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

· нажать «ГОТОВО».

· увидев построенный график,. нажмите на «Сохранить».

В этом графике можно форматировать оси, область построения диаграммы, делать дополнительные надписи.

· Для форматирования осинужно

v подвести курсор к оси и, дважды щелкнув левой кнопкой мышки, выделить ее.

v открывается окно «Формат оси».

§ В подменю «Вид» можно выбрать: тип линии оси, ее цвет и толщину (внизу окна представлен образец линии оси), направление основных и промежуточных делений на оси, размещение меток этих делений.

§ В подменю «Шкала» можно изменить минимальное, максимальное значение; выбрать цену основных и промежуточных делений, которые программа выбирает автоматически. Чтобы ваши изменения сохранились нужно убрать галочки в окошках рядом с надписями «максимальное значение», «минимальное значение», «цена основных делений», «цена промежуточных делений» и точкой пересечения с другой осью.

§ В подменю «Шрифт» выбираем шрифт и начертание меток делений рядом с осью.

§ В подменю «Число» производится выбор числового формата, автоматически он – общий. После всех изменений внизу окна «Формат оси» нажать на «ОК».

· Для форматирования области построения диаграммынужно:

v выделить область построения диаграммы, щелкнув на нее мышкой. Выделяется «прямоугольник», который можно уменьшать или увеличивать, потянув за уголок или за одну из сторон выделенной области построения.

v дважды щелкнув по ней мышкой, мы открываем окно «Формат области построения».

Ø В подменю «Вид» можно изменить тип, цвет и толщину линии рамки. Лучше сделать рамку невидимой (поставить точку напротив надписи «невидимая»). Можно менять цвет и фактуру области построения в разделе «Заливка». Если после форматирования вашего графика вы будите распечатывать его на принтере или копировать в текстовый документ Word, то лучше выбрать прозрачную или белую заливку. В случае если график необходимо вставить в Power Point (программу презентации) заливка графика может быть любого цвета и фактуры. После форматирования области построения нажмите на «ОК».

· Надписина графике можно сделать с помощью команды «Надпись» (в строке меню выбрать «ВСТАВКА», щелкнуть мышкой, и на вкладке найти «Надпись») или функции «РИСОВАНИЕ». В последнем случае:

v Найдите в панели инструментов кнопку «РИСОВАНИЕ» (на ней нарисованы маленькие геометрические фигуры) (рис. 2) и нажмите на нее.

v Внизу листа появится панель рисования.

v Чтобы сделать дополнительную надпись на графике нужно:

Ø выделить график (щелкнуть на него мышкой),

Ø на панели рисования нажать кнопку «Надпись»

Ø подвести курсор, который выглядит как вертикальная линия, к тому месту на графике, где будет надпись.

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

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

После построения графика можно его форматировать (менять некоторые его параметры). Для этого:

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

· Откроется окно с вкладками:

v «Формат рядов данных» - где можно форматировать построенный график, менять цвет линий, их толщину;

v «Тип диаграммы» - позволяет менять тип и вид построенного графика;

v «Добавить линию тренда»- откроется окно «Линия тренда» и во вкладке «Тип» можно задать аппроксимацию построенной кривой. Аппроксимация может быть линейной, экспоненциальной, логарифмической, степенной и полиномиальной. Линейная аппроксимация позволяет получить усредненную прямую, которая строится по всем точкам кривой. На вкладке «Параметры» можно выбрать название аппроксимирующей кривой (автоматическое) и отметить «Показывать уравнение на диаграмме». На графике появится аппроксимирующая функция и уравнение, которое описывает эту функцию. Чтобы получить более сглаженную линию построенной кривой нужно выбрать полиномиальную аппроксимацию и задать ее «Степень». Нажать «ОК» и «Сохранить».

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

Построенный график можно распечатать на принтере из программы Excel. Чтобы увидеть, как будет выглядеть график при печати, используйте кнопку «Предварительный просмотр» (листок с лупой) на панели инструментов или в разделе под кнопкой «ФАЙЛ».

Для распечатки графика нужно включить принтер, заправить его бумагой, и нажать кнопку «Печать» на панели инструментов или выбрать команду «Печать» в разделе под кнопкой «ФАЙЛ». Печать можно запустить и нажав сочетание клавиш Сtrl+P.

Построенный график можно копировать в Word или Power Point. Для этого выделить график, щелкнув на него мышкой, нажать «Копировать». Открыть документ Word или слайд Power Point (см. ниже) и щелкнуть на «Вставить» (рис. 2), график появится на листе документа.

Построение диаграммы

Поиск по сайту: