Логические функции в электронных таблицах
методическая разработка по информатике и икт (10 класс)

Логические функции.

Функция ЕСЛИ.

Логические функции позволяют производить логический выбор и логические вычисления. Функция ЕСЛИ( ) используется для проверки условия и принятия решения. Функция ЕСЛИ (  ) присваивает одно значение, если заданное условие при вычислении выполняется (истина), и другое значение, если заданное условие при вычислении не выполняется (ложь).

Функция записывается в виде:

ЕСЛИ (логическое выражение; значение если истина; значение если ложь)

Логическое выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Например, условие ЕСЛИ (А5<2; «ДА»; «НЕТ») можно расшифровать так: если значение ячейки А5 меньше двух, то будет напечатано слово «ДА». Если значение ячейки А5 больше или равно двум, то будет напечатано слово «НЕТ».

Примечание. Для того, чтобы конструировать сложные проверки, можно строить вложенные функции. До семи функций ЕСЛИ может быть вложено друг в друга.

Пример 1.

Составьте таблицу, «Список группы», в котором необходимо назначить на стипендию студентов, имеющих количество пропусков меньше 18 часов.

1. Заполните столбцы таблицы «Ф.И.О.» и «Количество пропусков самостоятельно.
2. В ячейку D2 введите формулу проверки условия =ЕСЛИ(С2<18;«ДА»;»НЕТ»).
3. Измените исходные данные, чтобы убедиться в том, что формула записана правильно.

Пример 2.

Составьте таблицу, в которой будет определена сумма общей стоимости товара. Если сумма покупки (значение ячейки Е6) превышает 2000 руб., то покупатель получает скидку. Таким образом, в ячейку Е5 нужно поместить текст «ДА» или «НЕТ» в зависимости от стоимости покупки.

При решении задачи исходные данные занесите самостоятельно (с учетом реальных условий), т. е. в диапазон ячеек В2:D4 введите наименования каких-либо товаров, их цену и количество.

Запишите формулу подсчета стоимости в ячейки Е2:Е4 (=С2*D2).

Подсчитайте общую стоимость в ячейке Е5 (сумма Е2:Е4).

Запишите в ячейку Е6 формулу проверки условия

=ЕСЛИ(Е5<2000; «НЕТ»; «ДА»).

Измените исходные данные, чтобы убедиться в том, что формула записана правильно.

Пример 3.

Составить таблицу «Список группы». Назначить на стипендию студентов, у которых средний бал>3.

1. Заполните столбцы «Ф.И.О.», «Математика», «Физика» самостоятельно.
2. Рассчитайте средний балл, используя функцию Среднее значение.
3. В ячейку F2 введите формулу проверки условия. Используйте функцию ЕСЛИ.

Функции И( ) и ИЛИ( )

Функции И( ) и ИЛИ( ) могут проверять несколько критериев или условий для последующего использования в функции ЕСЛИ( ).

Функция И( ) присваивает значение ИСТИНА, если все аргументы имеют значение ИСТИНА и присваивает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Функция записывается в виде:

И(логическое значение 1; логическое значение 2; …)

Функция ИЛИ( ) присваивает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

Функция записывается в виде:

ИЛИ(логическое значение 1; логическое значение 2;…)

Статистические функции

Функции СЧЕТ( ) и СЧЕТЕСЛИ( )

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

Функция СЧЕТ( ) подсчитывает количество чисел в списке аргументов.

Функция записывается в виде:

СЧЕТ(значение 1; значение 2;…)

Функция СЧЕТЕСЛИ( ) подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.

Функция записывается в виде:

СЧЕТЕСЛИ(диапазон; условие)

Диапазон - это диапазон, в котором нужно подсчитать ячейки.

Условие – это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитать.

Пример 4.

По таблице определить, кто из абитуриентов зачислен в техникум. Для зачисления необходимо иметь по математике оценку 4 или 5 и сумма баллов за три экзамена не должна быть меньше проходного балла. Составить таблицу для 5 человек. Определить количество поступивших.

При решении задачи:

1. Занесите исходные данные самостоятельно в диапазон ячеек А3:D7.
2. Запишите в ячейку Е3 формулу подсчета суммы баллов.
3. Занесите в ячейку F3 формулу для проверки условия:

=ЕСЛИ(И(Е3>=$C$1; В3>3); «ДА»; «НЕТ»).

Это условие можно расшифровать так: ЕСЛИ сумма баллов (Е3) больше или равна проходному баллу (С1) и оценка за экзамен по математике (В3) больше 3, то условие выполняется и в ячейке F3 будет отображаться текст «ДА»; в противном случае  - «НЕТ».

Для проходного балла  в формуле используется абсолютный адрес $C$1, так как проходной балл является неизменным для всех абитуриентов.

В ячейку F8 запишите формулу: = СЧЕТЕСЛИ(F3:F7; «ДА»)  для подсчета количества поступивших.

Контрольное задание.

В таблице «Список группы» добавьте столбец количество пропусков. И стипендия будет назначена, если средний балл>3 И количество пропусков < 18 часов.

Упражнение.

Используя данные, о тестировании постройте нижеприведенную таблицу.

1. Заполните данные о тестировании самостоятельно (от 0 до 100 баллов в каждой ячейке).

2. Подсчитайте значение Итого, используя автосуммирование.
3. Для заполнения столбца Оценка используйте логическую функцию ЕСЛИ.

Оценки вычисляются по следующему правилу:

        Если 0 < Итого <=100 тогда Оценка = 2

                Иначе если 100 < Итого <= 150 тогда Оценка = 3

                        Иначе если 150 < Итого <= 180 тогда Оценка =4

                                Иначе Оценка = 5

Для этого в ячейку H2 введите формулу =ЕСЛИ(И(G2>0;G2<=100);2;ЕСЛИ(И(G2>100;G2<=150);3;ЕСЛИ(И(G2>150;G2<=180);4;5)))

4. Рассчитайте средний балл, использую функцию среднее значение.

Логические операторы.

Стандартные функции.

Задание 1.

Определить ежемесячную абонентскую плату.

Если оплата производится за 1-6 месяцев, то ежемесячно платят 350 рублей, если за 7-11 месяцев – 300 руб. в месяц, если за весь год вперед – 200 рублей в месяц.

Задание 2.

Для группы студентов по результатам сессии определить размер начисляемой стипендии.

Если сессия сдана на «отлично», то начисляется  стипендия в размере 200%, если есть двойки или тройки, то стипендия не начисляется. Все остальные (т.е. сдавшие на 4 или 5) получают стипендию 100%.

Для расчета стипендии необходимо использовать функцию ЕСЛИ и ИЛИ. Для того, чтобы стипендия была в размере 200% надо, чтобы минимальная (функция мин()) оценка в ячейках C3:G3 была равна 5. Если в ячейках C3:G3 мин = 2 ИЛИ 3, то стипендия = 0%. В остальных случаях стипендия равна 100%.

Задание 3.

Составить ведомость тестирования, содержащую сведения: фамилия, количество баллов, оценка.

Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке «2»; оценке «3» соответствует количество баллов от 12 до 15; оценке «4» - от 16 до 20; «5» - свыше 20 баллов.

Абсолютная адресация.

Постройте таблицу умножения чисел от 1 до 20 (используя абсолютные ссылки).

Подсказка находится в Строке формул.