Занятие 5. MS Excel. Вложенные функции
Референсные значения
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Возраст
|
Уровень глюкозы, ммоль/л
|
|
|
|
Анализ действия препарата Х
на экспериментальную группу
из 10 человек
с клиническими симптомами диабета
|
|
от
|
до
|
|
|
|
|
< 14 лет
|
3,33
|
5,55
|
|
|
|
|
14-60 лет
|
3,89
|
5,83
|
|
|
|
|
|
|
|
|
|
|
>60 лет
|
4,44
|
6,38
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
№
|
Возраст
|
Обнаружение глюкозы плазмы
до приема
препарата Х
|
Обнаружение глюкозы плазмы после приема препарата Х
|
Снижение глюкозы на
%
|
Клинический показатель диабета
после приема пр. Х
|
Натощак
|
Ч/з 1 час после ГТТ
|
Ч/з 2 часа после ГТТ
|
Натощак
|
Ч/з 1 час после ГТТ
|
Ч/з 2 часа после ГТТ
|
|
Натощак
|
Ч/з 1 час после ГТТ
|
Ч/з 2 часа после ГТТ
|
Общий показатель
|
1
|
10
|
6
|
12
|
6
|
3
|
6
|
3
|
50,0
|
--
|
--
|
--
|
--
|
2
|
25
|
7,3
|
13,6
|
7,6
|
5,1
|
9,2
|
5,3
|
30,9
|
--
|
--
|
--
|
--
|
3
|
48
|
7,7
|
13
|
7,7
|
4,1
|
6,1
|
4,1
|
48,9
|
--
|
--
|
--
|
--
|
4
|
61
|
7,9
|
14,1
|
8
|
6,7
|
10,1
|
6,8
|
19,5
|
++
|
++
|
++
|
++
|
5
|
19
|
6,2
|
12,1
|
6,2
|
4,4
|
6,3
|
4,2
|
36,4
|
--
|
--
|
--
|
--
|
6
|
12
|
5,8
|
11,8
|
5,7
|
3,6
|
5,6
|
3,8
|
41,3
|
--
|
--
|
--
|
--
|
7
|
77
|
8,1
|
13,9
|
10
|
7,9
|
9
|
10
|
12,6
|
++
|
--
|
++
|
--
|
8
|
36
|
5,9
|
11,3
|
6,1
|
5,8
|
9,6
|
6
|
6,1
|
++
|
++
|
++
|
++
|
9
|
60
|
6,9
|
13,1
|
7
|
5,9
|
8,8
|
6,1
|
20,1
|
++
|
--
|
++
|
--
|
10
|
80
|
8,8
|
14,7
|
8,8
|
8
|
12,4
|
8,2
|
10,5
|
++
|
++
|
++
|
++
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Результаты эксперимента
|
Кол-во человек
|
|
|
|
|
|
|
|
|
|
Отсутствуют клинические признаки диабета
после применения препарата Х
|
7
|
|
|
|
|
|
|
|
|
|
Снижение уровня глюкозы на 50% и боллее
|
1
|
|
|
|
|
|
|
|
|
|
Снижение уровня глюкозы на 30-50%
|
4
|
|
|
|
|
|
|
|
|
|
Снижение уровня глюкозы на 10-30%
|
4
|
|
|
|
|
|
|
|
|
|
Нет или незначительное снижение уровня глюкозы
|
1
|
|
|
|
|
|
|
|
|
|
Введите исходные данные — возраст и показатели глюкозы в плазме крови до и после введения препарата Х.
Вычислите, на сколько процентов снизился показатель глюкозы, учитывая все данные.
Учитывая референсные значения и возраст пациента, определите данные в столбцах «Клинический показатель диабета после приема пр. Х».
Логическая схема совокупности референсных значений показана ниже. На основании этой схемы постройте логическое выражение, используя функции И, ИЛИ. И используйте его в функции ЕСЛИ для определения есть(++) или нет(--) Клинический показатель диабета.
Истина
Ложь
++
--
ЕСЛИ
|
ИЛИ
|
|
|
|
|
И
|
Возраст <= 14
|
|
УГ > 5,55
|
|
|
|
|
|
И
|
Возраст <=60
|
|
Возраст > 14
|
|
УГ > 5,83
|
|
|
|
|
|
И
|
Возраст > 60
|
|
УГ > 6,38
|
|
|
|
|
|
«Общий показатель» будет положительным, только если все три предыдущих показателя тоже положительные.
Подведите итог эксперимента — заполните поля результатов, внеся туда нужные формулы.
Используйте функции ЕСЛИ и СЧЕТЕСЛИ
Постройте кольцевую диаграмму, отражающую результаты эксперимента.
Занятие 6. MS Excel. Фильтрация данных.
Открыть файл Серия опыта.xls.
На Листе 1: вычислить «Долю аберрартных клеток (%)»
сделать две копии Листа1 и добавить один пустой лист.
переименовать Лист 1 в «ИСХОДНАЯ ТАБЛИЦА»
переименовать Лист 2 в «АВТОФИЛЬТР»
переименовать Лист 3 в «РАСШИРЕННЫЙ ФИЛЬТР»
переименовать Лист 4 в «РЕЗУЛЬТАТЫ ФИЛЬТРАЦИИ»
На Листе 2: выделить всю таблицу и оформить, используя Стиль «Светлый3».
Обратите внимание, что Автофильтр автоматически устанавливается при задании стиля. Кроме того, Автофильтр можно установить в панели
Главная > Редактирование > Сортировка и фильтр
В столбце «Количество исследованных клеток» и с помощью Автофильтра выбрать 150
Скопировать полученную таблицу на Лист 4, начиная с ячейки B2
Восстановить показ всей таблицы на Листе 2
В столбце «Количество клеток с аберрациями» выделить с помощью Автофильтра Числовой фильтр первые 5 с наибольшим значением
Скопировать полученную таблицу на Лист 4, ниже предыдущей таблицы
Восстановить показ всей таблицы на Листе 2
В столбце «Количество клеток с пробелами» с помощью Автофильтра определить все нулевые значения
Скопировать полученную таблицу на Лист 4
Восстановить показ всей таблицы на Листе 2
С помощью Автофильтра выбрать строки по следующим условиям:
«Количество исследованных клеток» - 100
«Количество клеток с аберрациями» больше 1, но меньше или равно 5
«Количество клеток с пробелами» не 0
Скопировать полученную таблицу на Лист 4
Восстановить показ всей таблицы на Листе 2.
На Листе 3: С помощью Расширенного фильтра выбрать данные по следующим условиям:
если «Количество исследованных клеток» - 100, то «Количество клеток с аберрациями» должно быть не меньше 10;
если «Количество исследованных клеток» - 150 то «Количество клеток с аберрациями» должно быть равно 2;
Постройте таблицу условий отбора на Листе 3 после основной таблицы.
Количество исследованных клеток
|
Количество клеток с аберрациями
|
100
|
>=10
|
150
|
2
|
Примените расширенный фильтр, указав в качестве диапазона условий построенную таблицу.
Данные > Сортировка и фильтр > Дополнительно
Занятие 7. MS Excel Статистикв в Excel
Тренд (читается трэнд, от англ. Trend - тенденция) — выраженная направленность тенденции изменений показателей любого временного ряда. Графики могут быть описаны различными уравнениями — линейными, логарифмическими, степенными и т. д. Фактический тип графика устанавливают на основе графического изображения данных временного ряда, путем осреднения показателей динамики ряда, на основе статистической проверки гипотезы о постоянстве параметров графика.
Корреля́ция — статистическая взаимосвязь двух или нескольких случайных величин (либо величин, которые можно с некоторой допустимой степенью точности считать таковыми). При этом изменения одной или нескольких из этих величин приводят к систематическому изменению другой или других величин. Математической мерой корреляции двух случайных величин служит коэффициент корреляции.
Коэффицие́нт корреля́ции или парный коэффицие́нт корреля́ции в теории вероятностей и статистике — это показатель характера изменения двух случайных величин.
Откройте файл «Анализ данных».
Вычислить коэффициент упитанности (по Фультону).
Ку=Р*100/L3
где Ку — коэффициент упитанности;
Р — масса тела, г;
L — длина тела, см.
Построить точечный график зависимости размера и веса пелагического омуля.
Измените параметры оси Y, задав минимальное значение – 300.
Уменьшите размер точек.
Добавьте подписи осей и диаграммы.
Добавьте линию тренда с наибольшей достоверностью аппроксимации R2.
Работа с диаграммами > Макет > Анализ > Линия тренда > Дополнительно
Включите показ на графике уравнения и величины достоверности аппроксимации.
В отдельной ячейке вычислите коэффициент корреляции, используя статистическую функцию:
КОРРЕЛ
Возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.
Синтаксис
КОРРЕЛ(массив1;массив2)
Массив1 — это интервал ячеек со значениями.
Массив2 — второй интервал ячеек со значениями.
В отдельной ячейке, в соответствии с приведенной ниже таблицей, определите характеристику силы связи размера и массы омуля. (Можно использовать или функцию ЕСЛИ или функцию ВПР)
-
Величина коэффициента корреляции
|
0,1-0,3
|
0,3-0,5
|
0,5-0,7
|
0,7-0,9
|
0,9-1,0
|
Характеристика силы связи
|
Слабая
|
Умеренная
|
Заметная
|
Высокая
|
Весьма высокая
|
сильная
средняя
|
