ПРАКТИЧЕСКАЯ РАБОТА Логические функции в электронных таблицах.

ПРАКТИЧЕСКАЯ РАБОТА

Использование логических функций в  электронных таблицах

Цель:

§       знакомство с логическими функциями;

§       практически выполнять расчеты в таблице MS Word.

Теоретические сведения

Общий вид условной функции  следующий:

ЕСЛИ (<условие >; <выражение1>; <выражение2>)

Условие – это логическое выражение, которое может принимать значение истина или ложь.

<выражение1> и <выражение2> могут быть числами, формулами или текстами.

 Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение1>, в противном случае – <выражение2>. 

Логические выражения  строятся с помощью операций отношения (<, >,  <=(меньше или равно), >=(больше или равно), =, <>(не равно)) и логических операций (логическое И, ИЛИ, НЕ).

      В табличных процессорах сначала записывается имя логической функции, потом в скобках имя лог. операции (и, или, не), а затем в круглых скобках перечисляются логические операнды, разделенные «;»:

ЕСЛИ(И(E3>=$C$1;B3>3);”ДА”;”НЕТ”)

                                                        Функция If                     2 Логических выражения           Знач.        Знач.

                                          операнды                     «Истина»      «Ложь

 

 Пример1. Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма балов за три экзамена и информация о зачислении: если сумма балов равна или больше проходному баллу и оценка за экзамен  по математике – 4 или 5, то абитуриент зачислен в учебное заведение, а если меньше то нет.

Решение. На лист1 подготовить таблицу в следующем виде:

   

В ячейке С1 будет храниться значение проходного балла – 13. Записав формулу в ячейке Е3 будем вычислять сумму баллов за три экзамена: SUM(В3:D3). А формула в ячейке F3 задаётся с помощью условной функции: 

ЕСЛИ(И(E3>=$C$1;B3>3);”ДА”;”НЕТ”)

Условие записанное с помощью логической операции И, можно расшифровать так: сумма баллов (Е3) >= проходному баллу (С1) и оценка за экзамен по математике (В3) >3.

Если условие не выполняется, то в клетке F3 будет отображаться текст ДА, в противном случае – НЕТ.  Для проходного балла в формуле используется абсолютный адрес $C$1, так как проходной балл является одинаковым и неизменным для всех абитуриентов.  

	А	В	С	D	E	F
1	Проходной	балл	13
2	Фамилия	Математика	Русский	Иностранный	Сумма	Зачисление
3	Антонов	4	5	5	14	ДА
4	Воробьев	3	5	5	13	НЕТ
5	Синичкин	5	5	3	13	ДА

 Переименовать Лист1 на Зачисление. Файл сохранить как Логические функции.  

Функции подсчета количества значений, удовлетворяющих некоторому  условию

 Функция СЧЕТЕСЛИ используется для подсчета количества значений,

удовлетворяющих некоторому условию.   Эта функция имеет следующий формат:

=СЧЕТЕСЛИ(диапазон; критерий)

=COUNTIF(диапазон; критерий)

Критерий отбора – число (н-р, 3), или текст (“шкаф”), или условие (“>0”). Текст

и условия указываются в двойных кавычках.

Пример. Известны результаты экзамена для учеников класса. Определить:

количество 2  и количество 4  и 5.  Диапазон просмотра В2:В6,  критерий

подсчета – значение равно 2 или значение больше 3.

 

Для подсчета количества числовых значений диапазона используется функция:

=СЧЕТ(диапазон)

=COUNT(диапазон)

Задание 1. Записать в тетрадь конспект теоретической части.

Задание 2. Рассчитать количество комиссионных на основе использования логических функций.

 

Технология работы:

1. Лист 2 переименуйте на Комиссионные.
2. В столбец A ввести фамилии продавцов в соответствии с образцом (см. рис.).
3. Правило 1. Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, а если не меньше 20000, то 20%.
4. Правило 2. Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, если больше 20000, но меньше 30000, то 20%, а если больше 30000, то 30%.
5. Для расчетов комиссионных по первому правилу в ячейку С2 введите формулу  =IF(В2<20000;В2*0,1;B2*0,2). Затем скопируйте эту формулу в диапазон С2:С6 или распространите ее на столбец C.
6. Для расчетов комиссионных по второму правилу в ячейку D2 введите формулу

=IF(В2<20000;В2*0,1;IF(AND(В2>=20000;В2<30000);В2*0,2; IF(В2>=30000;В2*0,3))). Скопируйте эту формулу в ячейки D2:D6.

7. Формула для расчетов комиссионных по второму правилу довольно сложна и состоит из нескольких вложенных функций IF. Того же результата можно достичь не за счет не суперпозиции этих функций, а их сложения. В ячейку Е2 введите следующую формулу: =IF(В2<20000;В2*0,1;0)+IF(AND(В2>=20000;В2<30000);В2*0,2;0)+IF(В2>30000;В2*0,3;0).

Она будет давать тот же результат, что и формула в столбце D. Так же скопируйте эту формулу в ячейки Е2:Е6.

8. В заключение отберем тех менеджеров, которые по результатам продаж добились лучших результатов. С этой целью в ячейку F2 введите формулу =IF(В2=МАX(F$2:F$6);"Лучший";""), а затем скопируйте ее в диапазон F2:F6.
9. Оформим таблицу в соответствии с образцом. Выделите всю таблицу Формат – Ячейки – Граница. Щелкните Внешние и Внутренние. Тип линии – Двойная. Вновь щелкните по кнопке Внешние границы.
10. Выделите 1 столбец. Формат – Ячейки – Вид – Фон. Выберите цвет заливки, но не слишком насыщенный, иначе не будет видно текст, он будет сливаться с заливкой. Аналогично измените заливку 1 строки таблицы.
11. Измените начертание текста на полужирный в 1 столбце и 1 строке.

Задание 3. Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по информатике, математике, физики и химии. Сумма баллов за четыре экзамена информация о зачислении: если сумма баллов больше или равна проходному баллу (16) и оценка по информатике 5 или 4, то абитуриент зачислен в учебное заведение в противном случае – нет.

Задание 4.

2. Составить таблицу, содержащую следующие данные о  студентах: фамилия, возраст и рост. Кто из студентов может заниматься в баскетбольной секции, если туда принимают студентов с ростом не менее 170см? Возраст не должен превышать 18 лет.

Задание 5.  Выполнить задания файла Логические функции.

Вопросы и ответы:

1. Какие  функции относятся к статистическим?

К статистическим относятся следующие функции: СРЗНАЧ(), МИН(), МАКС()

2. В чем заключается принцип относительной адресации.

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

3. В чем заключается принцип абсолютной адресации.

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

4. Что значит протабулировать  функцию на заданном отрезке.

Значит, найти значение функции при каждом аргументе на заданном отрезке. 

5. Формат логических функций. Примеры.

6. Логические операции в логических функциях. Примеры