Сводные таблицы 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?
Тесты:См. Сборник тестовых заданий по «Информатике», тема «Табличный процессор MS Excel».
Задания:
| № | Практические навыки | Критерий оценки |
| Создайте файл под названием своей группы и сохраните его в корневом каталоге Student | ||
| Введите данные согласно таблице№1 на листе1. Произведите обрамление таблицы. | ||
| Определите, сколько знаков входит в ширину ячейки по умолчанию. | ||
| Проведите сортировку по убыванию для полей «Вес» и «Рост». | ||
| Используя «Автофильтр»: ü А) по столбцу «Вес», отобразите данные больше 59; ü Б) по столбцу «Рост», отобразите данные меньше или равно 172. ü В) по столбцу «Фамилия», отобразите имена, начинающиеся с буквы «А». | ||
| Вставьте 3 столбца и 3 строки в таблицу. Назовите способы вставки столбца и строки. | ||
| Введите цифры начиная с единицы в созданную ячейку таблицы. Определите, сколько знаков входит в ширину ячейки по умолчанию. | ||
| Введите в другую ячейку текст, превышающий ширину ячейки по умолчанию. Сделайте выводы. | ||
| Расширьте ширину текстовой ячейки так, чтобы был виден введенный в нее текст. | ||
| Пустые ячейки заполните любыми числами. Выделите диапазон чисел. | ||
| Уничтожьте, а затем восстановите информацию. | ||
| Переместите, а затем скопируйте содержание 3 строк тремя способами: ü в пределах одного рабочего листа; ü на второй рабочий лист; ü в новую рабочую книгу. | ||
| Удалите вставленные 3 строки. Покажите преподавателю разные способы удаления (триаду). | ||
| Постройте полигон и гистограмму (Фамилия – Рост). | ||
| Введите данные согласно таблице №2 на лист2, используя «Мастер функции» вычислите сред. значение, минимум, максимум. | ||
| Выполните команды указанные в таблице №3 и заполните таблицу. F Примечание:вычислить значения в ячейках С2:L12, используя соответствующую функцию по имени поля. | ||
| Заполните таблицу №4. F Примечание:используя массивы Х и У из таблицы №3, вычислите значения в столбце Результат. | ||
| Добавьте новый лист и переименуйте этот лист на «Студент»; | ||
| Заполните таблицу №5. Дано К и Х , вычислить Y1, Y2 , Y; | ||
| Вычислите сумму, максимум, минимум; средние арифметическое значение; | ||
| Вставьте электронную таблицу № 6 на лист1 «Узнай свой возраст» и заполните ее (как указано таблице №5). F Примечание: В этой задаче Вы узнаете Ваш возраст в днях, в неделях и в годах. | ||
| Введите данные о текущей дате и о дате рождения в формате Дата в 3 ячейку строки «текущая дата». | ||
| Введите формулу, в пустую ячейку «Мой возраст в днях»: = абсолютный адрес ячейки, содержащий текущую дату – абсолютный адрес ячейки, содержащий дату рождения (или задайте имя ячейке содержащий текущую дату) | ||
| Введите формулу, в пустую ячейку «Мой возраст в неделях»: = (относительный адрес ячейки, содержащий текущую дату – относительный адрес ячейки, содержащий дату рождения)/7 | ||
| Введите формулу, в пустую ячейку «Мой возраст в годах»: = (относительный адрес ячейки, содержащий текущую дату – относительный адрес ячейки, содержащий дату рождения)/365 | ||
| Перейдите на 2-лист, переименуйте лист на лист под названием «Моя группа» | ||
| Вставьте таблицу №7 на лист 2 и заполните её. F Примечание: В этой задаче Вы узнаете возраст ваших сокурсников в днях, неделях и годах | ||
| С помощью объекта Smart Art постройте пирамиду Вашей группы | ||
| Создайте книгу Excel состоящий из 6 листов, сохраните книгу под названием «Анализ плотности населения» | ||
| Переименуйте листы согласно заголовкам: «Европа», «Америка», «Азия», «Австралия», «Африка», «Всего по континентам» | ||
| Введите данные на листе Европа согласно таблице№7 и скопируйте таблицу №8 на листы: «Европа», «Америка», «Азия», «Австралия», «Африка» | ||
| Прочитав текст скопируйте данные по континентам таблицы | ||
| Использовать: Специальная вставка Ширины столбцов | ||
| Использовать: Специальная вставка Все | ||
| Перейдите на лист под заголовком «Всего по кинтинентам», установите курсор на ячейке А1 и выполните команду: Данные Группа: работа с данными Консолидация | ||
| В появившемся диалоговом окне выберите ФункцияСумма, и выберите нужные ссылки с помощью кнопки Добавить | ||
| Отметьте подписи верхней строки и значения левого столбца | ||
| Рассчитайте данные, используя «Мастер функции» максимальное, минимальное значение плотности населения и территорию по всему миру | ||
| Результат сравните с таблицей №9, поставьте защиту и сохраните документ. | ||
| Итого |
Приложение:
Таблица №1
| Статистический расчёт | |||
| № | Фамилия | Вес | Рост |
| Азанбаева Г. | |||
| Богедаев А. | |||
| Кокебаев Б. | |||
| Илипов М. | |||
| Камиева А. | |||
| Умиржанов К | |||
| Акимбаев Р. | |||
| Кенесбаева А. | |||
| Нурымбетов М. | |||
| Жумашев Ж. |
Таблица №2
| Параметры | |||
| Ср. значение | Минимум | Максимум | |
| Вес | |||
| Рост |
Таблица №3
| X | Y | [X] | expX | LnY | Log2Y | lgY | X2 | Y3 | Случайное число | Случайное число между Х и | Корень чисел |
| -1 | |||||||||||
| -0,8 | 1,2 | ||||||||||
| -0,6 | 1,4 | ||||||||||
| -0,4 | 1,6 | ||||||||||
| -0,2 | 1,8 | ||||||||||
| 0,2 | 2,2 | ||||||||||
| 0,4 | 2,4 | ||||||||||
| 0,6 | 2,6 | ||||||||||
| 0,8 | 2,8 | ||||||||||
Таблица №4.
| Операция | Результат | |
| Сумма чисел в Х и У | ||
| Произведение чисел Х | ||
| Произведение чисел в У | ||
| Сумма чисел в Х для Х>0 | ||
| Сумма чисел в У для У>=2 | ||
| Сумма чисел в У, с Х>=0 | ||
| Число Пи | ||
| Градус числа Пи | ||
| Радианы градуса 180 | ||
| Остаток от деления числа 16 на 3 |
Таблица №5.
| К | Х | Y1=X^4+2K | Y2=3X^2-5K+Y1 | Y=(Y1/Y2) |
| минимум | ||||
| максимум | ||||
| сумма | ||||
| срзнач |
Таблица №6
| Мой возраст | ||
| Текущая дата | ||
| Дата рождения | ||
| Мой возраст в днях | ||
| Мой возраст в неделях | ||
| Мой возраст в годах |
Результат
| Мой возраст | ||
| Текущая дата | 01.10.2009 | |
| Дата рождения | 01.05.1981 | |
| Мой возраст в днях | ||
| Мой возраст в неделях | 1482,86 | |
| Мой возраст в годах | 28,44 |
Таблица №7
| № | Ф.И.О | Дата рождения | Возраст в днях | Возраст в неделях | Возраст в годах |
2-задание «Территория и население по континентам»
Используя набор данных «Территория и население по континентам», составить таблицу и выяснить минималную и максимальную плотность населения в 1970 году и в 1989 году, суммарную площадь всех континентов.
Территория Австралии - 8,5 млн. кв.км. Плотность населения в Африке в 1989 году была 21 человек на кв. км. Население Европы в 1989г. составило 701 млн.человек. Территория Южной Америки -17,8 млн.кв.км. Население Северной и Центральной Америки в 1989г. составило 422 млн. человек. Плотность населения в Северной и Центральной Америке в 1970г. была 13 человек на кв. км. Территория всего мира -135,8 млн. кв. км. Плотность населения в Австралии в 1989 г. была 3 человека на кв.км. Население Южной Америки в 1989г. составило 291 млн.человек. Территория Африки -30,3 млн. кв.км. Население Австралии в 1989г. составило 26 млн. человек. Плотность населения во всем мире в 1970г. была 27 человек на кв.км. Территория Азии -44,4 млн. Население всего мира в 1989 г. составило 5201 млн. человек. Территория Северной и Центральной Америки -24,3 млн. кв.км. Население Азии в 1970г. составило 2161 млн. человек. Плотность населения в Европе в 1989г. была 67 человек на кв.км. Плотность населения в Азии в 1970 г. была 49 человек на кв.км. Население Африки в 1970г. составило 361 млн.человек. Население Австралии в 1970г. составило 19 млн.чел Население Южной Америки в 1970г. составило 190 млн. человек. Плотность населения в Африке в 1970г. была 12 человек на кв.км. Население Северной и Центральной Америки в 1970г. составило 320 млн. человек. Плотность населения в Южной Америке в 1970г. была 11 человек после кв.км. Население Африки в 1989 г. составило 628 млн.человек. Плотность населения в Австралии в 1970г. была 2 человека на кв.км. Население Европы в 1970г. составило 642 млн. человек. Плотность населения во всем мире в 1989 г. была 38 человек на кв. км. Территория Европы – 10,5 млн. кв.км. Плотность населения в Северной и Центральной Америке в 1989г. была 17 человек после кв.км. Плотность населения в Европе в 1970 г. была 61 человек на кв. км. Население Азии в 1989 г. составило 3133 млн. человек. Плотность населения в Южной Америке в 1989г. была 16 человек на кв.км. Население всего мира в 1970 г. составило 3693 млн. человек. Плотность населения в Азии в 1989 г. была 71 человек на кв.км
Таблица №8
| Часть континента | Территория | Плотность населения 1970г | Население 1970г | Плотность населения 1989 год | Население 1989 год |
| млн.кв.км | чел. на кв км. | млн.чел. | чел. на кв км. | млн.чел. | |
Таблица №9
| Континент | Территория | Плотность населения 1970г | Население 1970г | Плотность населения 1989 год | Население 1989 год |
| Австралия | 8,5 | ||||
| Азия | 44,4 | ||||
| Южная Америка | 17,8 | ||||
| Северная и Центральная Америка | 24,3 | ||||
| Африка | 30,3 | ||||
| Европа | 10,5 | ||||
| Минимум | |||||
| Максимум | |||||
| Территория всего мира | 135,8 |