Программа 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
- Создать; после нажатие кнопки открывается новый документ
- Открыть; после нажатие кнопки открывается папка с документами, там можно выбрать нужный документ
- Сохранить; после нажатие кнопки документ сохраняется
- Печать; после нажатие кнопки начинается печать документа
- Предварительный просмотр; позволяет увидеть расположение текста, таблиц и рисунков на листе вашего документа, и как будет выглядеть документ после печать
- Правописание; после нажатие кнопки запускается проверка орфографии текста документа
- Вырезать; после нажатие кнопки выделенный фрагмент текста или таблицы перемещается в буфер обмена (область памяти доступная любой программе)
- Копировать; после нажатие кнопки выделенный фрагмент текста или таблицы копируется в буфер обмена
- Вставить; после нажатие кнопки выделенный фрагмент текста или таблицы вставляется из буфера обмена
- Отменить; после нажатие кнопки последнее выполненное действие отменяется
- Автосумма; после нажатие кнопки выделенный столбец или строка таблицы суммируются
Ø запишем там произвольно цифру или число, чтобы ячейка не была пустой.
Ø в ячейке рядом (например, ячейка В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), график появится на листе документа.
Построение диаграммы
Если требуется сравнивать значения полученных в эксперименте величин друг с другом, удобно представлять их в виде столбчатых диаграмм.
Пример: Изучалось влияние аспирина на агрегацию тромбоцитов. Было установлено, что аспирин снижает агрегацию на 45% (+/- 5% среднеквадратичная ошибка измерения) по сравнению с контрольной пробой (степень агрегации тромбоцитов в которой условно принята за 100%. Построим диаграмму, используя программу Excel. Для этого:
· Открываем новый файл.
· В столбец «А» вносим значение агрегации в контроле 100 %, в столбец «В» пишем действие аспирина – 45%, в столбец «С» - пишем разброс 5%.
· Сохраняем файл.
· Выделяем столбцы «А», «В» левой кнопкой мышки.
· На панели инструментов (находится сверху окна Microsoft Excel) нажать кнопку «МАСТЕР ДИАГРАММ».
· В подменю открывшегося окна «Стандартные» нужно выбрать «Тип»– «гистограмма»и один из видов диаграмм (двух- или трехмерную). Нажать кнопку «Далее» внизу окна.
· В следующем окне можно изменять диапазон данных диаграммы. В нашем случае данные размешены в столбцах, поэтому отмечаем «Столбцы». Нажать кнопку «Далее».
· Открывается следующее окно, где можно менять параметры диаграммы:
v вкладка «Подписи данных» - отметить «нет»,
v вкладка «Заголовки»- вписать название диаграммы и подписать ось «Y»,
v вкладка «Оси» - отмечено, что в нашей гистограмме есть и ось «X», и ось «Y»,
v вкладка «Линии сетки» - можно добавить или удалить основные или промежуточные линии сетки;
v вкладка «Легенда» - можно добавить или удалить легенду;
v вкладка «Таблица данных» - можно поместить таблицу с данными, по которым строится гистограмма.
· После изменения параметров диаграммы нажать кнопку «Далее».
· Появится окно «Размещение диаграммы», в котором можно выбрать, поместить ли гистограмму на отдельном листе, или на имеющимся, вместе с таблицей ваших результатов. Затем нажать кнопку «Готово».
· Появляется поле с построенной гистограммой, в которой можно также менять некоторые параметры.
v Если дважды щелкнуть на столбик левой кнопкой мышки, то открывается окно «Формат ряда данных» с разными вкладками.
Ø На вкладке «Вид» можно форматировать границу и заливку столбика диаграммы.
Ø Вкладка «Y-погрешности» позволяет указать погрешности на столбике; можно выбрать тип планки погрешности, величину погрешности. Величина погрешности (разброс) в рассматриваемом случае задана в столбце «С». Для того, чтобы показать разброс на графике нужно в «Величине погрешности» отметить «ПОЛЬЗОВАТЕЛЬСКАЯ» и указать ячейку, где размещено значение разброса, или в окошке написать его значение, т. е. цифру 5.
Ø На вкладке «Параметры», меняя значения в графе «перекрытие» и «ширина зазора», можно подобрать ширину и расстояние между столбиками на диаграмме. Подобрав все параметры для «столбика» нажать «ОК».
· Сохранить файл кнопкой «Сохранить».
После окончания работы в программе Excel нужно ее закрыть или выйти из программы. Для этого:
v щелкните мышкой на «Файл» (в строке меню),
v выберите «Закрыть» или щелкните на крестик (X), расположенный в верхнем правом углу окна Excel.
v Если вы не сохраняли файл или изменяли его без сохранения, то появится окно с запросом на сохранение изменений в документе Нажмите «ДА» и файл будет сохранен.