Лабораторная работа №5 / Лабораторная работа№5
.pdf21
Аргументы ряда функций могут принимать только логические значения
ИСТИНА или ЛОЖЬ. Логическое выражение возвращает значение ИСТИНА
или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например:
=ЕСЛИ(А1=ИСТИНА;"Повышение";"Понижение")&" цены"
В качестве аргумента функции можно указать имя диапазона. Например, если диапазону ячеек А1:А5 присвоено имя "Дебет" (Вставка – Имя - Присвоить), то для вычисления суммы чисел в ячейках с А1 по А5 можно использовать формулу:
=СУММ(Дебет)
В одной функции можно использовать аргументы различных типов:
=СРЗНАЧ(Дебет;С5;2*8)
Логические функции Excel
Функция ЕСЛИ Функции И, ИЛИ, НЕ. Вложенные функции ЕСЛИ Функции
ИСТИНА и ЛОЖЬ Функция ЕПУСТО.
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Ниже представлен список операторов сравнения Excel:
= Равно;
> Больше;
< Меньше;
>= Больше или равно;
<= Меньше или равно;
<> Не равно;
22
Результатом логического выражения является логическое значение ИСТИНА
(1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ
Функция ЕСЛИ (IF) имеет следующий синтаксис:
=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ ложь)
Следующая формула возвращает значение 10, если значение в ячейке А1
больше 3, а в противном случае - 20:
=ЕСЛИ(А1>3;10;20)
В качестве аргументов функции ЕСЛИ можно использовать другие функции. В
функции ЕСЛИ можно использовать текстовые аргументы. Например:
=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")
Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например:
=ЕСЛИ(СУММ(А1:А3)=30;А10;"")
Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое
значение. Например:
=ЕСЛИ(А1="Динамо";10;290)
Эта формула возвращает значение 10, если ячейка А1 содержит строку
"Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.
Функции И, ИЛИ, НЕ
Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми
23
операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:
=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)
Функция НЕ имеет только один аргумент и следующий синтаксис:
=НЕ(логическое_значение)
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями,
массивами или ссылками на ячейки, содержащие логические значения.
Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:
=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")
Не смотря на то, что функция ИЛИ имеет те же аргументы, что и И,
результаты получаютсясовершенно |
различными.Так, если в |
предыдущей |
|||
формуле |
заменить функцию |
И на |
ИЛИ, то ученик будет проходить, если |
||
выполняется хотя |
бы одно |
из условий (средний балл более 4 или пропуски |
|||
занятий |
менее 3). |
Таким образом, |
функция ИЛИ возвращает |
логическое |
|
значение ИСТИНА, если хотя бы одно из логических выражений истинно, а
функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны. Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно
24
использовать вложенные функции ЕСЛИ. Например, в следующей формуле
используются три функции ЕСЛИ:
=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=6
0;А1<80);"Иногда";"Никогда")))
Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку
"Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1
находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7
уровней вложения функций ЕСЛИ.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА()
=ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:
=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")
В противном случае формула возвратит "Стоп.
25
Ход работы
1.Изучить теоретический материал, описанный выше в данных методических указаниях.
2.Запустить редактор электронных таблиц MS Excel и создать новый документ.
Рисунок 1 – Запуск MS Excel.
3.Создать таблицу, показанную ниже и используя статистические функции Excel
рассчитать указанные параметры.
Рисунок 2 – Показатели физического развития школьников.
26
Рисунок 3 – Вставка функции.
При этом следует воспользоваться такими статистическими функциями:
СРЗНАЧ;
МЕДИАНА;
МОДА;
СРОТКЛ;
ДИСПР;
СТАНДОТКЛОН.
Данные функции доступны в разделе «Статистические». См. рисунок 4.
Рисунок 4 – Перечень статистических функций.
27
Рисунок 5 демонстрирует использование функции СРЗНАЧ:
Рисунок 5 – Применение функции СРЗНАЧ.
Рисунок 6 – Выбор диапазона аргументов функции.
28
Аргументы функции выбираются путѐм выделения требуемого диапазона
левой клавишей мыши. Для расчѐта остальных параметров воспользоваться
функциями:
СЧЁТ;
МАКС;
МИН;
СЧЁТЕСЛИ.
4.Используя описанный выше критерий Стьюдента, определить, существует ли зависимость нейролиновой кислоты в эритроцитах при болезнях крови. Ниже приведена таблица концентрации данной кислоты в крови больных и здоровых людей. Задать уровень значимости 0,05.
Рисунок 7 – Проверка гипотезы о зависимости концентрации нейролиновой кислоты в крови больных и здоровых людей.
По результатам расчѐтов сделать выводы.
29
5.Используя описанный выше критерий Фишера, определить влияние магнитных полей низкой частоты на размер опухоли карциномы Герена на четвѐртый день заболевания.
Рисунок 8 – Проверка гипотезы о влиянии магнитных полей НЧ на размер опухоли Герена.
По результатам расчѐтов сделать выводы.
6.Используя алгоритм построения таблиц истинности, описанный выше,
построить таблицу истинности вручную и с использованием логических функций Excel для логической функции:
F A & B C
При этом использовать логические функции:
И;
ИЛИ.
30
Рисунок 9 – Таблица истинности логической функции.
7.Рассчитать идеальную массу тела, а также индекс массы тела, и на основании расчѐтных данных поставить диагноз. Идеальная масса тела по Куперу определяется формулой:
|
Рост 3,5 |
|
|
|
||
m |
|
|
108 |
0,453 |
для женщин; |
|
|
|
|||||
|
|
2,54 |
|
|
|
|
|
Рост 4,0 |
|
|
|
||
|
|
|
||||
m |
|
|
128 |
0,453 |
для мужчин. |
|
|
|
|||||
|
|
2,54 |
|
|
|
|
|
|
|
|
|||
Индекс массы тела (индекс Кетле) рассчитывается по формуле:
ИМТ
Масса
Рост2
Интерпретация результатов:
|
|
|
|
кг |
|
|||||||
ИМТ 18,5 |
24,9 |
|
|
|
|
|
|
норма; |
||||
|
|
|
2 |
|
||||||||
|
|
|
|
м |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
кг |
|
||||||||
ИМТ 25,0 |
29,9 |
|
|
|
|
наличие лишнег о веса; |
||||||
|
|
2 |
||||||||||
|
|
|
|
м |
|
|
|
|
||||
|
|
|
|
кг |
|
|||||||
ИМТ 30,0 |
34,9 |
|
|
|
|
|
ожирение 1 й степени; |
|||||
м2 |
||||||||||||
|
|
|
|
|
|
|||||||
|
|
|
|
кг |
|
|||||||
ИМТ 35,0 |
39,9 |
|
|
|
|
|
|
ожирение 2 й степени; |
||||
|
м2 |
|||||||||||
|
|
|
|
|
|
|||||||
|
кг |
|
|
|
|
|
|
|||||
ИМТ 40,0 |
|
|
|
|
|
|
||||||
|
|
ожирение 3 й степени. |
||||||||||
2 |
||||||||||||
|
м |
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
||||||