|
|
Обработка экспериментальных данных в программе Excel.
В предыдущей статье («Ошибки экспериментальных данных») мы рассмотрели виды ошибок, которые сопровождают любые измерения в ходе исследовательского эксперимента и пути их учета при обработке данных. При этом мы отметили громоздкий характер математического аппарата, используемого при такой обработке. В этой статье мы покажем, как решаются сложности математической обработки данных эксперимента при помощи программы Microsoft Excel. Если текстовый процессор Microsoft Word уже достаточно широко используется многими учителями, то вести расчеты мы часто еще предпочитаем по старинке, если не столбиком, то с помощью калькулятора. Вместе с тем Excel не только удобен для составления различных таблиц, графиков, но и служит мощным средством для проведения очень сложных вычислений. Будем считать, что у читателя статьи уже есть некоторые навыки работы с компьютером и перейдем к рассмотрению особенностей и возможностей данной программы. Лучше всего, если Вы включите компьютер, и по мере чтения статьи будете выполнять описанные в ней примеры вычислений. Простейшие приемы работы.Запуск программы можно осуществить стандартным путем: через главное меню, открываемое кнопкой Пуск, переходим в раздел Программы, затем в раздел Microsoft Office, где и находим нужный ярлык. Рабочее окно программы во многом напоминает окно Word`a. Строка заголовка, в которой написано «Книга1» (документы Excel называются книгами), строка меню, панели инструментов, на которых, правда, наряду со знакомыми кнопками, есть и незнакомые. А вот ниже появляется что-то новенькое. Под панелями инструментов находится два поля, одно из которых, в котором написано А1, называется полем адреса, а другое, длинное, строкой формул. И, наконец, основная рабочая область окна представляет собой не страничку, как в Word’e, а таблицу, состоящую из множества ячеек. Заголовки колонок таблицы обозначены буквами английского алфавита, а строчки – числами. Сочетание буквы и числа и дает адрес любой ячейки, и один из них мы видим в адресном поле. А именно, там отражен адрес активной ячейки, той, которая выделена в таблице жирной черной рамкой. Сделать ячейку активной можно, щелкнув по ней мышкой, при этом в адресном поле появится новый адрес. В нижней части окна Excel мы увидим ярлычки с надписями «Лист 1», «Лист 2» и т.д. Как уже говорилось, документы Excel называются книгами, а книги состоят из листов. Хотя обычно в новой книге всего три листа, мы всегда можем удалить лист или добавить новый. Если на ярлычке листа щелкнуть правой кнопкой мышки, откроется контекстное меню, из которого можно узнать о других действиях над листами. В частности можно их переименовывать. Для удобства мы можем переименовать, например, Лист 1 в «Расчеты», и рассмотрим на нем ввод данных и примеры простейших вычислений. Что может находиться в ячейках таблицы Excel? Три основных объекта – это текст, число и формула. Попробуем ввести текст в ячейку В3. Для этого щелкнем по ячейке В3, затем наберем текст «Примеры вычислений». Обратите внимание, что при этом тот же текст появляется и в строке формул. Мы можем в любой момент щелкнуть по строке формул и продолжить набор – это будет происходить и в ячейке. Таким образом, строка формул показывает содержимое ячейки и может использоваться для ввода и коррекции этого содержимого. Ввод текста в ячейку заканчивается нажатием клавиши “Enter”. Обратите внимание, что текст вышел за границу ячейки В3, и кажется, что часть его находится и в ячейке С3. То, что это только кажется, мы определим, щелкнув по ячейке С3 – строка формул покажет, что в этой ячейке пусто. |
|
|
Ввод текста помогает оформлять заголовки таблиц, записывать определенные пояснения. Допустим, нам надо рассчитать объем раствора по его массе 10 г и плотности 1,25 г/мл, используя простейшую формулу V=m/d. Введем в ячейки В5, С5, D5 заголовки столбцов будущей таблицы, обозначения величин m, d и V, и приступим к вводу чисел. В ячейку В6 введем численное значение массы 10. Заканчиваем ввод, нажимая Enter, и убеждаемся, что тест в ячейке, как правило, смещен к правой границе, а число к левой. Это удобно, так как позволяет замечать ошибки ввода. В ячейку С6 введем дробное число 1,25. Здесь надо учесть, что в зависимости от настройки конкретного компьютера для разделения целой и дробной части числа может использоваться или запятая, или точка. При неправильном вводе наши символы будут восприниматься как текст, или даже как дата (янв.25). Наконец, в ячейке D6 введем формулу, по которой Excel будет проводить вычисления. Ввод формулы начинается со знака равенства (=). Затем надо показать программе, где находится первое число в нашей формуле, масса раствора, дать адрес этой ячейки - В6. Конечно, можно набрать этот адрес с клавиатуры, надо только учитывать, что В – это символ английского алфавита. Поэтому, гораздо проще просто щелкнуть по нужной ячейке и ее адрес будет введен автоматически (=В6). Далее надо ввести знак арифметического действия. Эти знаки удобно вводить с правой части клавиатуры, напоминающей клавиатуру калькулятора. Здесь есть клавиши со знаком сложения (+), вычитания (-), умножения (*) и деления (/). И, наконец, надо показать компьютеру, где находится делитель – щелкаем мышкой по ячейке С6 и получаем окончательный вид формулы (=В6/С6). Нажимаем Enter, и, если все было набрано правильно, получаем в ячейке D6 результат (8). Таким образом, формулы возвращают в ячейку результат вычислений, число. Но если щелкнуть по ячейке и посмотреть на строку формул, мы увидим, что на самом деле находится в ней. Иногда формула может возвращать и сообщение об ошибке. Щелкнем по ячейке В6 и введем вместо числа 10 символы «10 г». В ячейке D6 тут же окажется сообщение #ЗНАЧ!, которое говорит о неверном значении в одной из ячеек. Действительно, запись «10 г» воспринимается уже как текст. Чтобы исправить ошибку надо снова вместо «10 г» ввести число 10. (Для исправления неверных действий можно использовать и кнопку «Отменить» на панели инструментов). Щелкнем теперь по ячейке С6 и нажмем клавишу “Del”. Этим мы удалим содержимое ячейки, и в соседней ячейке тут же получим сообщение #ДЕЛ/0! (ошибка деления на 0). Действительно, на ноль делить нельзя и ошибку надо исправить. Итак, мы научились вводить числа и формулы, а значит и проводить простейшие вычисления в Excel. Но как упростить эту процедуру, если таких вычислений много? Здесь помогают приемы копирования, и автоматического заполнения ячеек методом «протягивания». Пусть у нас 10 порций раствора массой 10 г, и в ячейки В6, В7 …, В16 надо ввести 10, 10, … и т.д. Щелкнем по ячейке В6, где число 10 уже введено. В черной рамке выделенной ячейки, внизу справа, есть маленький черный квадратик. При наведении на него указателя мышки, последний меняет форму. Если в этот момент «взяться» (нажать левую кнопку мыши) и потянуть вниз, до ячейки В16, то все десять ячеек окажутся автоматически заполнены нужным числом. Не труднее заполнить и 100 ячеек! А если массы растворов отличаются на некоторую постоянную величину, например 10, 12,5, 15 г и т.д.? В этом случае достаточно ввести два значения: число 10 в ячейку В6 и число 12,5 в ячейку В7. Теперь надо выделить эти две ячейки. Для этого щелкаем по первой ячейке и, не отпуская кнопки, ведем до второй. Теперь обе ячейки обведены жирной рамкой. Снова беремся за черный квадратик и тянем вниз. Получаем ряд значений от 10 до 35. Поскольку предполагается, что раствор у нас один и тот же, оставим колонку С в покое и попробуем методом протягивания скопировать формулу, которая у нас набрана в ячейке D6. Проделываем уже описанную операцию: выделяем ячейку, беремся, протягиваем… и получаем во всех ячейках, кроме первой, ошибку! Разберемся, почему это произошло, для чего щелкнем по ячейке D7 и посмотрим на строку формул. В ячейке D6 было написано «=В6/С6», а в ячейке D7 уже «=В7/С7»! То есть, при копировании формул Excel автоматически меняет адреса ячеек, откуда он берет данные для расчетов. И это совершенно правильно, когда речь идет о массе раствора. Но плотность раствора у нас постоянная, как показать программе, что адрес этой ячейки менять не надо? Для этого мы должны познакомиться с такими понятиями, как относительный и абсолютный адрес. Те адреса, которые мы использовали, называются относительными и меняются при копировании. Адрес в абсолютной форме сопровождается знаками доллара и выглядит так: $C$6. Вот эту поправку нам и надо внести в формулу в ячейке D6. Исправлять записи в ячейках удобнее в строке формул. Щелкнем сначала по ячейке D6, (формула появится в строке формул), затем в нужном месте строки формул – там появится курсор. Конечно знаки доллара можно ввести с клавиатуры, но проще, установив курсор на адресе С6, нажать на клавиатуре клавишу F4. Понажимайте ее несколько раз и посмотрите, как будет меняться адрес. Он может быть полностью абсолютным, абсолютным по строчке, по колонке, и полностью относительным. Добейтесь нужного вида и нажмите Enter. Формула исправлена, теперь ее снова можно протянуть до ячейки D16. Если все сделано правильно, вы получите ряд значений от 8 до 28 мл. Итак, если Вы не только прочитали, но и проделали все, о чем шла речь выше, Вы научились многому. Вы умеете вводить текст, числа и формулы, вносить исправления, устранять ошибки, копировать и заполнять ячейки рядами данных. Не мешает сохранить результаты своей работы. Процедуры сохранения файла и его открытия полностью совпадают с работой в Worde и не должны вызвать у Вас затруднений. Формулы с функциями.Но в наших расчетах использовались только простейшие арифметические действия. Для более сложных расчетов нужно научиться использовать функции. Этим мы займемся на втором листе нашей книги. Для перехода на нужный лист достаточно щелкнуть по его ярлычку. Начнем работу с краткого повторения пройденного: дадим листу 2 имя «Ошибки», в ячейку А3 введем текст «Данные эксперимента», в ячейки А5 и В5 - заголовки новой таблицы «№» и «Х». Предполагается что мы проделали серию из 10 опытов, измеряя некоторую величину Х (здесь не важно, что это, длина побега или объем раствора). Номера опытов от 1 до 10 легко ввести протягиванием, а вот численные значения Х надо последовательно ввести (табл.1). Таблица 1. Примерный вид листа «Ошибки»
Записи в колонках D и Е – это подсказки, которые помогут разобраться с тем, какие характеристики мы будем рассчитывать. Колонка F у Вас должна быть пока пустой, в нее будем помещать наши формулы. Обработку результатов начнем с расчета числа опытов n. Казалось бы это очевидное число, но в ходе работы, какой-то результат мы можем отбросить, или провести еще пару опытов. Желательно, чтобы нам не пришлось при этом переделывать все формулы. Для определения числа значений используется специальная функция, которая называется СЧЕТ. Для ввода формулы с функциями используется Мастер функций, который запускается командой «Вставка функции» через меню «Вставка» – «Функция» или кнопкой на панели инструментов с обозначением fx. Щелкнем мышкой по ячейке F6, где должен находиться результат и запустим Мастер функций.
Первый шаг работы (рисунок 1) служит для выбора нужной функции. Все функции разделены, в зависимости от своего назначения на несколько категорий (математические, логические и др.). Для обработки данных эксперимента используются в основном статистические функции. Поэтому, прежде всего в списке категорий выбираем категорию «Статистические». Во втором окне появляется список статистических функций. Если щелкнуть по любой из них, внизу появляется краткое описание функции. Специальной ссылкой можно вызвать систему помощи Excel, в которой данная функция будет разобрана подробно, с примерами. Список функций упорядочен по алфавиту, что позволяет без труда нужную нам функцию СЧЕТ («Подсчитывает количество чисел в списке аргументов»). Выделив щелчком эту функцию, нажимаем кнопку Ok и переходим к шагу 2.
Второй шаг (рисунок 2) служит для задания аргументов функции. Функции СЧЕТ надо указать, какие числа ей надо пересчитывать, или в каких ячейках находятся эти числа. Диапазон ячеек указывается адресами первой и последней ячейки, записанными через двоеточие, в нашем случае данные находятся в ячейках В6:В15. Как и в других случаях эти адреса лучше не вводить, а показать мышкой. Для этого устанавливаем указатель мышки на первую ячейку, нажимаем левую кнопку и ведем до последней. Обратите внимание, что окно аргументов можно перемещать, если оно заслоняет нужную часть экрана. Кроме того, рядом с полем для ввода есть маленькая кнопка с красной стрелочкой. При щелчке по ней окно аргументов сворачивается до узкой полоски. Когда мы показываем в основном окне диапазон ячеек, в окне аргументов появляется запись диапазона адресов, а рядом с ним – значения чисел из первых ячеек. Предварительное значение функции тоже показывается после ввода ее аргументов. Это помогает избегать ошибок. Помогает работе с мастером функций и подсказка под полем для ввода аргументов, в которой разъясняется их смысл и возможные значения. Заканчивается работа с мастером функций нажатием кнопки “Ok” или клавиши “Enter”. Если все сделано правильно, в ячейке F6 появится нужное значение “10”. Следующие два этапа обработки серии опытов проводятся аналогично. В ячейке F7 c помощью функции СРЗНАЧ рассчитывается среднее значение выборки, в ячейке F8 – стандартное отклонение выборки, с помощью функции СТАНДОТКЛОН. . Будьте аккуратны при выборе функций – среди них есть очень похожие по названию. Аргументами этих функций служит все тот же диапазон ячеек. Следующая формула сложная, частично она набирается как обычная формула, начиная с символа ”=”. Указав, где находится делимое S и набрав знак операции (=F8/), вызываем мастер функций. Функция КОРЕНЬ – математическая, поэтому на первом шаге выбираем категорию математических функций. Аргументом этой функции служит число опытов, которое мы рассчитали в ячейке F6. Окончательный вид формулы “=F8/ КОРЕНЬ(F6)”. Для расчета доверительного интервала необходимо определить коэффициент Стьюдента. Он зависит от вероятности ошибки (при обычно задаваемой надежности 95% вероятность ошибки составляет 5%), и от числа степеней свободы n-1). Для нахождения коэффициента Стьюдента используется статистическая функция Excel СТЬЮДРАСПОБР (“Стьюдента распределение обратное“). Особенностью этой функции является то, что первый аргумент, число 5% (или 0,05) вводится в соответствующее окно с клавиатуры. Для второго указываем адрес ячейки, где находится значение n, затем дописываем в окне “-1”. Получаем запись “F6-1”. Для нахождения доверительного интервала используется обычная формула умножения. Конечно, вместо букв там должны стоять адреса ячеек, где находятся коэффициент Стьюдента и стандартное отклонение среднего. Как правило, значение доверительного интервала округляется до одной значащей цифры, такой же порядок окружения должен быть и у среднего. Поэтому окончательный результат можно записать так: с 95%-ной надежностью Х = 14,80±0,05. В заключение посчитаем относительную ошибку определения Х: d = ДИ / Хср (формула: “=F11/F7”). Значение относительной ошибки обычно выражают в процентах, у нас 0,3%. Если Вы впервые работаете в Excel, описанная процедура обработки данных эксперимента может показаться очень сложной. Но на практике, вводить формулы, с помощью мастера функций, ничуть не сложнее, чем обычные арифметические. К тому же, один раз подготовив лист Excel для обработки данных, можно скопировать его, и ввести результаты новой серии опытов в колонку В. Результаты будут тут же рассчитаны автоматически. Изучение зависимостей.Часто в исследованиях изучается зависимость некоторой величины от другой. Характер этих зависимостей стремятся выразить математическими формулами, коэффициенты которой могут иметь определенный физический смысл. Наиболее употребительна и проста в обработке линейная зависимость, которую можно выразить уравнением прямой у = kx + b. При этом коэффициент k показывает степень влияния х на у, а b – некоторое начальное значение у. Поскольку значения, полученные в ходе эксперимента, всегда включают некоторую ошибку, экспериментальные точки не лежат строго на прямой. Как же провести по этим разбросанным точкам наилучшую линию. Для этого используется статистический метод «наименьших квадратов» предлагающий достаточно сложные функции для нахождения коэффициентов k и b, а также для оценки их достоверности. В Excel эта задача решается при помощи статистических функций НАКЛОН (наклон прямой относительно оси Х, коэффициент k) и ОТРЕЗОК (отрезок отсекаемый прямой на оси Y, коэффициент b). Кроме того, Excel позволяет построить график зависимости, саму прямую, которая называется линией тренда, а также вывести уравнение прямой на график. Для знакомства с этим возможностями перейдем на Лист 3 нашей книги, назовем его «Зависимость» и введем необходимые исходные данные (таблица 2). Таблица 2. Примерный вид листа «Зависимость»
В колонках В и С вводятся данные эксперимента по измерению величин Х и У, записи в колонке Е играют роль подсказок, колонка F заполняется по мере обработки. Начнем с ячейки F3. Ввод формул проводится с помощью мастера функций так, как это описывалось ранее. Маленькое отличие заключается в том, что у функций НАКЛОН и ОТРЕЗОК два аргумента: диапазон ячеек со значениями Y и диапазон ячеек со значениями Х. Щелкаем мышкой сначала по полю для ввода первого аргумента, показываем нужный диапазон (С3:С13). Затем щелкаем по второму поля и повторяем ввод (В3:В13). Также рассчитывается и значение функции ОТРЕЗОК в ячейке F4. Для оценки достоверности можно использовать квадрат коэффициента корреляции Пирсона (R2). Если он равен 1, то имеет место полная корреляция с моделью, т.е. точки лежат строго на прямой. В противоположном случае, если коэффициент равен 0, то уравнение линейной зависимости полностью неудачно. Для его нахождения используется статистическая функция КВПИРСОН. Таким образом, данные нашего эксперимента с достоверностью 0,98 описываются уравнением у = 1,42х+0,905. Рассмотрим теперь второй метод обработки и представления результатов эксперимента в виде графика. Для построения графиков и диаграмм в Excel’e используется Мастер диаграмм, который можно запустить, используя меню Вставка – Диаграмма, или кнопки на панели инструментов с условным изображением диаграммы. Предварительно щелкнем мышкой по любой свободной ячейке нашего листа. Рисунок 3. На первом шаге (рисунок 3) выбирается тип и вид диаграммы. Для построения графика зависимости одной величины от другой используются точечные диаграммы, причем лучше (из-за разброса точек) выбирать вид «Точки не соединенные линиями». Заканчиваем выбор, щелкая по кнопке «Далее». На втором шаге необходимо указать, где у нас находится независимая величина Х и зависящая от нее Y (рисунок 4). Для этого щелкаем по ярлычку вкладки «Ряд» и затем по кнопке «Добавить». Рисунок 4.
Открываются поля для указания Х и Y. Ввод значений адресов в эти поля не отличаются от работы с Мастером функций (только при вводе Y предварительно сотрите условное значение “={1}”. Если Вы правильно выполните эту часть работы, на поле вверху уже появится примерный вид графика. Следующие два шага имеют отношение к оформлению и размещению графика. На первый раз можно, ничего не меняя, просто нажимать кнопки «Далее» и «Готово». Полученный черновой вариант графика всегда можно редактировать, изменять или удалять его отдельные элементы. Обычно для этого щелкают по нужному элементу графика правой (!) кнопкой мышки. При этом открывается контекстное меню, в котором выбирают подходящую команду. Если правой кнопкой мышки щелкнуть по одной из точек графика, то в контекстном меню можно увидеть команду «Добавить линию тренда». Это и есть необходимая нам линия. Добавляется она тоже в два шага. На первом выбирается тип (линейный), на втором – параметры. На вкладке Параметры нам важно поставить галочки против слов: «показывать уравнение» и «поместить величину достоверности». Если из теоретических предпосылок понятно, что прямая должна проходить через начало координат (при нулевой концентрации скорость реакции, очевидно, равна нулю) поставим галочку и в данном пункте. Примерный вид графика после добавления линии тренда представлен на рисунке 5. Выведенное уравнение прямой и величины достоверности совпадает с рассчитанными ранее. Рисунок 5. Итак, мы рассмотрели важнейшие приемы работы в Microsoft Excel, необходимые для качественной обработки данных эксперимента. Разумеется эти приемы не исчерпывают всех возможностей Excel, и могут развиваться в ходе работы. Автор статьи с удовольствием ответит на все вопросы, связанные с работой в данной программе. Желаю успеха! |
|
© Можаев Г.М. |
|||
