Домашняя работа

лицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца»:

Интервальный_просмотр — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.

Например, вычислим значение для ячейки C4. В этом случае: Искомое_значение — соответствующее значение по шкале отношений, т.е. B4;

Таблица — диапазон ячеек с данными из таблицы переходов B38:C40 (исправьте относительные ссылки на абсолютные $B$38:$C$40 для дальнейшего копирования формулы); Номер_столбца — 2, т.е. из второго столбца таблицы переходов подставить найденное значение.

Скопируйте формулу в остальные ячейки.

Самостоятельно!

На основании найденных значений, и используя функцию СЧЕТЕСЛИ, отдельно постройте таблицу результатов измерения СРСХ в шкале порядков.

11

Задание 4. Элементы управления

Скорость химической реакции при постоянной температуре прямо пропорциональна произведению концентраций реагирующих веществ. Если в реакции типа А + В = С концентрацию веществ А и В обозначить через сА и сВ, то выражение для скорости будет иметь вид:

где k – константа скорости реакции, значение которой зависит от химических свойств реагирующих веществ и температуры.

В связи с тем, что концентрация исходных веществ в ходе реакции убывает, скорость реакции, согласно формуле (1), также уменьшается. Изменение концентрации вещества А равно:

Используя формулы (1) и (2), можно создать математическую модель изменения концентрации веществ в ходе химической реакции.

1.Откроем новую книгу Excel.

2.Разместим на Листе 1 два элемента управления «Счетчик», для чего откройте панель

Разработчик и выберите Вставить > Элемент управления, Счетчик.

3.Свяжем первый счетчик с ячейкой H2, для чего выделим счетчик правой кнопкой мыши и в открывшемся меню выберем команду Формат объекта, на вкладке Свойства в по-

ле LincedCell внесем H2.

4.Аналогичным образом свяжем второй счетчик с ячейкой H3.

5.В ячейку H6 будем вносить константу скорости реакции.

6.В ячейки А3–А12 занесем время протекания реакции в единицах времени (секунды, минуты и т. д.).

7.Ячейки В3 и С3 свяжем с ячейками H2, H3, внеся в них формулы = H2 и = H3 – это первоначальные концентрации вещества А и В соответственно.

8.Скорость реакции в начальный момент времени будет рассчитываться в ячейке D3, куда занесем формулу = H$6$*B3*C3 (обратите внимание на то, чтобы ссылка на ячейку H6 была абсолютной).

9.Концентрации веществ, получившихся через единицу времени, будут рассчитываться в ячейках E3 и F3, где расположим формулы = B3 – D3 и = C3 – D3.

10.Концентрации веществ в начале каждого следующего момента времени будут располагаться в ячейках В4–В12 и в ячейках С4–С12. Для этого внесем в ячейки В4 и С4 формулы = E3 и = F3. Затем размножим их вниз до строки номер 12.

11.Аналогичным образом размножим формулы для нахождения скорости реакции в каждый момент времени и концентраций реагирующих веществ, получившихся в результате реакции. Эти формулы находятся в ячейках D3, E3, F3.

12.Выделим ячейки D3–D12, введем команды главного меню Вставка, Диаграмма. Следуя указаниям Мастера диаграмм, построим график изменения скорости реакции с течением времени.

13.Выделим ячейки Е3–Е12 и построим график изменения концентрации вещества А с течением времени.

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

12

Математическая модель изменения концентрации веществ в ходе химической реакции

13

Введите исходные данные — возраст и показатели глюкозы в плазме крови до и после введения препарата Х.

Вычислите, на сколько процентов снизился показатель глюкозы, учитывая все данные.

Учитывая референсные значения и возраст пациента, определите данные в столбцах

«Клинический показатель диабета после приема пр. Х».

Логическая схема совокупности референсных значений показана ниже. На основании этой схемы постройте логическое выражение, используя функции И, ИЛИ. И используйте его в функции ЕСЛИ для определения есть(++) или нет(--) Клинический показатель диабета.

Возраст <=60 ИЛИ И Возраст > 14

УГ > 5,83

ИВозраст > 60

УГ > 6,38

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

Подведите итог эксперимента — заполните поля результатов, внеся туда нужные формулы.

Используйте функции ЕСЛИ и СЧЕТЕСЛИ

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

15

Задание 6. Фильтрация данных

Открыть файл Серия опыта.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 после основной таблицы.

Примените расширенный фильтр, указав в качестве диапазона условий построенную таблицу.

Данные > Сортировка и фильтр > Дополнительно

16

Задание 7. Статистика в Excel

Тренд (читается трэнд, от англ. Trend - тенденция) — выраженная направленность тенденции изменений показателей любого временного ряда. Графики могут быть описаны различными уравнениями — линейными, логарифмическими, степенными и т. д. Фактический тип графика устанавливают на основе графического изображения данных временного ряда, путем осреднения показателей динамики ряда, на основе статистической проверки гипотезы о постоянстве параметров графика.

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

Коэффициент корреляции или парный коэффициент корреляции в

теории вероятностей и статистике — это показатель характера изменения двух случайных величин.

1.Откройте файл «Анализ данных».

2.Вычислить коэффициент упитанности (по Фультону).

Ку=Р*100/L3

где Ку — коэффициент упитанности; Р — масса тела, г;

L— длина тела, см.

3.Построить точечный график зависимости размера и веса пелагического омуля.

4.Измените параметры оси Y, задав минимальное значение – 300.

5.Уменьшите размер точек.

6.Добавьте подписи осей и диаграммы.

7.Добавьте линию тренда с наибольшей достоверностью аппроксимации R2.

Работа с диаграммами > Макет > Анализ > Линия тренда > Дополнительно

8.Включите показ на графике уравнения и величины достоверности аппроксимации.

17

9.В отдельной ячейке вычислите коэффициент корреляции, используя статистическую функцию:

КОРРЕЛ

Возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.

Синтаксис КОРРЕЛ(массив1;массив2)

Массив1 — это интервал ячеек со значениями. Массив2 — второй интервал ячеек со значениями.

10. В отдельной ячейке, в соответствии с приведенной ниже таблицей, определите характеристику силы связи размера и массы омуля. (Можно использовать или функцию ЕСЛИ или функцию ВПР)

18

19

Задание 8. Оценка двигательной активности подростков

Цель данного исследования - оценка суточных энерготрат и уровня двигательной активности российских школьников 10-14 лет.

Предварительная работа заключалась в следующем:

-проанализированы виды занятий (активности) подростков и составлен перечень из 45 типичных видов активности;

-на основании анализа ранее опубликованных фундаментальных исследований определена энергетическая стоимость каждого из видов активности подростков в условных единицах - МЕТах (отношение энерготрат в процессе работы к энерготратам в покое);

-базируясь на энергетической стоимости конкретных видов двигательной активности, последние были разделены на пять категорий: очень легкая, легкая, средняя, тяжелая и очень тяжелая, которые получили следующую «ценность» в МЕТах: 1,5; 2,5; 4,0; 6,0 и 10 соответственно. МЕТы в данном случае выступают как коэффициенты, на которые умножается суммарное время, затраченное испытуемым на каждую из названных категорий двигательной активности в течение суток;

- была выбрана отправная точка конструирования нормативной шкалы для оценки уровня двигательной активности подростков по величине суточных энерготрат: подростки, ведущие активный образ жизни, имеют энерготраты не менее 40 ккал/ кг в день, а неактивные - менее 30 ккал/кг в день:

Результаты наблюдений среди 136 подростков собраны в таблице «Школьники.xls».

Задание:

1.Вычислить показатели суточных энерготрат на каждую из категорий активности. Для этого время, затраченное на каждую из категорий двигательной активности, умножить на соответствующий коэффициент (MET).

2.Вычислить суммарный показатель суточных энерготрат в ккал/кг.

3.Полученную величину сравнить с нормативной шкалой (табл. 1) и перевести данные эксперимента в шкалу рангов.

20