Сводные таблицы Excel 2007

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

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

 

Математические функции Excel 2007

 

Здесь рассмотрены наиболее часто используемые математические функции Excel (краткая справка). Дополнительную информацию о функциях можно найти в окне диалога мастера функций, а также в справочной системе Excel. Кроме того, множество математических функций включено в надстройку "Пакет анализа".

Ввод формул

Чтобы ввести формулу введите сначала знак =, а затем саму формулу. При вводе формул в качестве аргументов обычно используются адреса ячеек. Адреса ячеек могут быть относительными (например, А4) и абсолютными (например, $А$4).

При копировании формул относительные адреса ячеек модифицируются, т.е. преобразуются так, чтобы соответствовать новому расположению формулы. Например, в ячейку D1 введена формула: =А1+В1-С1. При копировании формулы из ячейки D1 в ячейки D2 и D3 формула преобразуются соответственно вид: =А2+В2-С2 и =А3+В3-С3.

При копировании формул абсолютные адреса ячеек не меняются.

Например, в ячейку D2 введена формула: =(А2+В2)*$C$1. При копировании формулы из ячейки D2 в ячейки D3 и D4 формула преобразуются соответственно в вид: =(А3+В3)*$C$1 и ==(А4+В4)*$C$1.

Основные статистические характеристики

Выборка – группа элементов, выбранная для исследования из всей совокупности элементов. Задача выборочного метода состоит в том, чтобы сделать правильные выводы относительно всего собрания объектов, их совокупности. Например, врач делает заключения о составе крови пациента на основе анализа ее нескольких капель.

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

Среднее значение (Хс, М) – центра выборки, вокруг которого группируются элементы выборки.

Медиана –элемент выборки, число элементов выборки со значениями больше которого и меньше которого – равно.

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

Важной характеристикой выборки является мера разброса элементов выборки от среднего значения. Такой мерой является среднее квадратическое отклонение или стандартное отклонение.

Стандартное отклонение (среднее квадратическое отклонение) – параметр, характеризующий степень разброса элементов выборки от среднего значения. Стандартное отклонение обычно обозначается буквой “σ “ (сигма).

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

Нормальное распределение – совокупность объектов, в которой крайние значения некоторого признака – наименьшее или наибольшее – появляются редко; чем ближе значение признака к среднему арифметическому, тем чаще оно встречается. Например, распределение пациентов по их чувствительности к воздействию любого фармакологического агента часто приближается к нормальному распределению.

Коэффициент корреляции (r) – параметр, характеризующий степень линейной взаимосвязи между двумя выборками. Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость)до 1 (строгая прямая пропорциональная зависимость). При значении 0 линейной зависимости между двумя выборками нет.

Случайное событие – событие, которое может произойти или не произойти без видимой закономерности.

Случайная величина – величина, принимающая различные значения без видимой закономерности, т.е. случайным образом.

Вероятность (p) – параметр, характеризующий частоту появления случайного события. Вероятность изменяется от 0 до 1, причем вероятность р=0 означает, что случайное событие никогда не происходит (невозможное событие), вероятность р=1 означает, что случайное событие происходит всегда (достоверное событие).

Уровень значимости – максимальное значение вероятности появления события, при котором событие считается практический невозможным. В медицине наибольшее распространение получил уровень значимости равный 0,05. Поэтому если вероятность, с которой интересующее событие может произойти случайным образом р < 0,05, то принято считать это событие маловероятным, и если оно все же произошло, то это не было случайным.

Критерий Стьюдента – наиболее часто используется для проверки гипотезы: «Среднее двух выборок относятся к одной и той же совокупности». Критерий позволяет найти вероятность того, что оба средних относятся к одной и той же совокупности. Если это вероятность р ниже уровня значимости (р < 0,05), то принято считать, что выборки относятся к двум разным совокупностям.

Регрессия – линейный регрессионный анализ заключается в подборе графика и соответствующего уравнения для набора наблюдений. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Например, на степень заболеваемости человека влияют несколько факторов, включая возраст, вес и иммунный статус. Регрессия пропорционально распределяет меру заболеваемости по этим трем факторам на основе данных наблюдаемой заболеваемости. Результаты регрессии впоследствии могут быть использованы для предсказания уровня заболеваемости новой, неисследованной группы людей.

Демонстрационный пример.

Рассмотрим две группы больных тахикардией, одна из которых (контрольная) получала традиционное лечение, другая (исследуемая) получала лечение по новой методике. Ниже приведены частоты сердечных сокращений (ЧСС) для каждой группы (ударов в минуту). А) Определить среднее значение в контрольной группе. В) Определить стандартное отклонения в контрольной группе.

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

162 135

156 126

144 115

137 140

125 121

145 112

151 130

Решение А).

Для определения среднего значения в контрольной группе необходимо установить табличный курсор в свободную ячейку. На панели инструментов нажать кнопку Вставка функций (fx). В появившемся диалоговом окне выбрать категорию Статистические и функцию СРЗНАЧ, после чего нажать кнопку ОК. Затем указателем мыши ввести диапазон данных для определения среднего значения. Нажать кнопку ОК. В выбранной ячейке появится среднее значение выборки – 145,714.

Решение В).

Для определения стандартного отклонения в контрольной группе необходимо установить табличный курсор в свободную ячейку. На панели инструментов нажать кнопку Вставка функций (fx). В появившемся диалоговом окне выбрать категорию Статистические и функцию СТАНДОТКЛОН, после чего нажать кнопку ОК. Затем указателем мыши ввести диапазон данных для определения стандартного отклонения, после чего нажать кнопку ОК. В выбранной ячейке появится стандартное отклонение выборки – 12, 298.

Демонстрационный пример.

Следующей задачей статистического анализа в рассматриваемом примере является сравнение данных исследуемой группы с контрольной. Сопоставляя средние значения ЧСС контрольной группы больных (145,7) и исследуемой (125,6), можно видеть, что они отличаются. Можно ли по этим данным сделать вывод о большей эффективности нового препарата?

Для решения задач такого типа используются так называемые критерии различия, в частности, t-критерий Стьюдента.

Для оценки достоверности отличий по критерию Стьюдента принимается нулевая гипотеза, что средние выборок равны между собой. Затем вычисляется значение вероятности того, что изучаемые события (ЧСС больных в обеих выборках) произошли случайным образом. Для этого табличный курсор устанавливается в свободную ячейку. На панели инструментов необходимо нажать кнопку Вставка функций (fx). В появившемся диалоговом окне Мастер функции выбрать категорию Статистические и функцию ТТЕСТ, после чего нажать на кнопку ОК. В появившемся диалоговом окне ТТЕСТ указателем мыши ввести диапазон данных контрольной группы в поле Массив 1. В поле Массив 2 ввести диапазон данных исследуемой группы. В поле хвосты всегда вводится цифра «2» (без кавычек), в поле Тип с клавиатуры введем цифру «3».Нажать на кнопку ОК. В выбранной ячейке появится значение вероятности – 0,006295.

Поскольку величина вероятности случайного появления анализируемых выборок (0,006295) меньше уровня значимости (р=0,05), то нулевая гипотеза отвергается. Следовательно, различия между выборками не случайные и средние выборок считаются достоверно отличающимися друг от друга. Поэтому на основании применения критерия Стьюдента можно сделать вывод о большей эффективности нового препарата (р<0,05).

Демонстрационный пример.

Имеются результаты наблюдений частоты сердечных сокращений (ударов в минуту) и частоты дыхания (вдохов в минуту) у группы больных с определенной патологией:

ЧСС ЧД

120 20

84 15

105 18

92 16

113 19

90 16

80 15

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

Решение.

Для выявления степени взаимосвязи, прежде всего, необходимо ввести данные в рабочую таблицу. Для вычисления значения коэффициента корреляции между выборками, табличный курсор нужно установить в свободную ячейку. На панели инструментов необходимо нажать кнопку Вставка функции(fх). В появившемсядиалоговом окне Мастер функции выбрать категорию Статистические и функцию КОРРЕЛ, после чего нажать кнопку ОК. Указателем мыши ввести диапазон данных ЧСС в поле Массив1. В поле Массив2 ввести диапазон данных ЧД. Нажать кнопку ОК. В выбранной ячейке появится значение коэффициента корреляции – 0,995493. Значение коэффициента корреляции больше чем 0,95. Значит, можно говорить о том, что при исследуемой патологии имеется высокая степень прямой линейной взаимосвязи между частотой сердечных сокращений (r=0,995493) у больных.

Примечание: r=-1 – строгая обратная линейнаязависимость;

r=0 – линейнойзависимости между двумя выборками нет;

r>\0,95\- то принято считать, что между параметрами существует практически линейная зависимость (прямая при положительном r и обратная при отрицательном r;

0,6< r<0,8 – говорят о наличии линейной связи между параметрами;

r<0,4 – обычно считают, что линейную взаимосвязь между параметрами выявить не удалось.

Литература:

1. Гельман В.Я. Медицинская информатика.СПб. “Пи­тер”. 2001

2. Герасевич В.. Компьютер для врача. СПб. 2002

3. Симонович С. В. Информатика. Базовый курс. СПб. “Пи­тер”. 2003

4. Глушаков С.В., Сурядный А.С. Персональный компьютер. М. Фолио. 2004

5. Богумирский Б. Энциклопедия Windows 98., 2-е изд., СПб. “Питер”. 2001

6. Шилина А.Л. Экспресс-курс Microsoft Excel XP. М.:АСТ Мн Харвест, 2007

7. Хэлворсон М., Янг М. Эффективная работа с Microsoft Office 2000. СПб. “Питер”. 2001

8. Андаспаева А.А. Новичку о работе на персональном компьютере Windows - 95, 98

Алматы. 2001 г.

Контроль

Вопросы:

1. Как запустить табличный процессор MS Excel?

2. Что такое рабочий лист и рабочая книга?

3. Что такое текущая ячейка? Ее предназначение.

4. Как объединить, разбить и отформатировать ячейки?

5. Как включить в документ графические объекты?

6. Как изменить высоту строк и ширину столбцов?

7. Как построить, отредактировать и дополнить диаграммы?

8. Как работают с Мастером диаграмм (типы, исходные данные, параметры и размещение диаграмм; форматирующие области диаграмм - вид, шрифт, свойства)?

9. Как исправить типичные ошибки в формулах и функциях?

10. Какие стандартные функции имеет Excel?