Лекция по Excel №2 «Использование встроенных функций Excel при решении предметных задач»
1. Что такое условие?. 2
2. Функция ЕСЛИ.. 3
3. Логические функции И, ИЛИ, НЕ.. 4
4. Вложенные логические функции. 5
5. Статистические функции СЧЕТЕСЛИ, СУММЕСЛИ.. 6
6. Функции ВПР, ГПР. 8
Что такое условие?
Рассматриваемые в этой лекции функции чаще всего используются для решения задач с нелинейным алгоритмом.
Задачи с линейным алгоритмом являются самыми простыми. В них действия выполняются последовательно друг за другом без инструкций передачи управления. Задачам с таким алгоритмом соответствует следующая блок-схема:
 |
К задачам с нелинейным алгоритмом относятся задачи: с условными разветвлениями, с циклическими повторениями, рекурсивные алгоритмы.
 |
В задачах с условными разветвлениями, вычисления могут вестись по двум разным сценариям в зависимости от какого-то условия.
С условными разветвлениями мы часто встречаемся в повседневной жизни:
§ Малыш, если ты съешь кашу, то мы пойдем гулять, иначе – нет;
§ Если ты закончишь четверть без троек, то я куплю тебе велосипед (компьютер, машину…), иначе – нет;
§ Если ваш рейтинг выше 85%, то вы автоматически получаете по предмету «Информатика» оценку «пять»;
§ и т.п.
Условие – это логическое выражение, имеющие (как правило) знаки сравнения и возвращающее результат логического типа (ИСТИНА, ЛОЖЬ).
В условиях EXCEL используются следующие знаки сравнения:
| = | Равно |
| <> | неравно |
| > | Больше |
| < | меньше |
| >= | Больше либо равно |
| <= | Меньше либо равно |
! Основным отличием логического выражения то обычного математического является то, что условие при вычислении всегда возвращает только два варианта ответа: либо ИСТИНА, либо ЛОЖЬ.
Сравним обычное математическое выражение X+1 и условие X>0:
Возьмем для Х несколько разных значений:
| X | X+1 | X>0 |
| истина | ||
| истина | ||
| - 5 | - 4 | ложь |
| ложь |
Таким образом, при вычислении математического выражения для разных Х получаем разные результаты, а условие при любых Х возвращает только один из двух вариантов ответов: либо ИСТИНА, либо ЛОЖЬ, что и позволяет однозначно выбрать либо один сценарий вычислений, либо – другой.
*Для общего сведения:
В Excel используется общепринятое правило обработки условий (такое же как, например, во многих языках программирования, таких как Pascal, C, C++, VBA и др.):
Полученный при вычислении условия 0 интерпретируется как ЛОЖЬ, все что не 0 ‑ интерпретируется как ИСТИНА.
Условие, как правило, состоит из трех частей: две сравниваемые величины и какой-либо знак сравнения. В условиях EXCEL могут сравниваться:
· значения ячеек с числовыми константами: A2>5
· значения ячеек: С1<>C2
· значения ячеек с текстовыми или логическими величинами: А2= “студент”, С4=ИСТИНА
· результаты вычисления функций: ОСТАТ(А2;2)=0, СРЗНАЧ(А1:А5)>3,5 (функция ОСТАТ возвращает целочисленный остаток от деления своего первого аргумента на второй. Например, ОСТАТ(5;2) возвратит 1. Чаще всего эта функция используется для анализа четности/нечетности, кратности/некратности числа).
Все текстовые строки в EXCEL заключаются в двойные английские кавычки
Функция ЕСЛИ
Используется для выбора одного из двух вариантов действий.
Функция ЕСЛИ проверяет истинность какого-либо условия, и при истинности условия возвращает результат вычисления одного выражения, а при ложности – другого.
Синтаксис:
ЕСЛИ(условие; выражение_если_истина; выражение_если_ложь)
Например,
=ЕСЛИ (А6<25; 0;1)
Эта формула возвратит ноль, если значение ячейки А6 меньше 25, иначе формула возвратит 1.
Пример:
Вычислить значение кусочной функции в заданной точке: 
Вышеприведенная запись читается следующим образом: если х>0, то значение y(x) равно sin(x), иначе y(x)=x^2. То есть, на той части числовой прямой, где х>0, функция строится как синусоида, а где х<=0 – как парабола. График такой кусочной функции выглядит следующим образом:
Решение:
Легче всего построить функцию ЕСЛИ, пользуясь мастером по построению функций: Вставка/Функция/Категория: Логические.
В качестве аргументов функции ЕСЛИ могут использоваться:
§ Простые математические выражения: =ЕСЛИ (А1<-3; A1+2;A1^3)
§ Другие функции: =ЕСЛИ(СУММ (А1:А10)>50;ПРОИЗВЕД (А1:А10);СРЗНАЧ(А1:А10)) – если сумма значений ячеек А1:А10 больше 50, то функция ЕСЛИ возвратит произведение ячеек А1:А10, иначе среднее арифметическое ячеек А1:А10.
§ Текстовые константы: =ЕСЛИ (А2<3; “экзамен не сдал”; “сдал”) – если значение ячейки А2 меньше трех, выдается сообщение “экзамен не сдал”, иначе “сдал”.
§ Пустые строки: =ЕСЛИ (С8<>0;1/C8; “”) – формула вычисляет значение выражения 1/х, только в том случае, если х не равен 0, иначе возвращает пустую строку. Значение для х находится в ячейке С8.