MS Excel. Расчеты с условиями. Работа со списками
Цель работы:
- ознакомиться с возможностями Excel для выполнения расчетов с условиями;
- освоить вычисления с помощью условных функций и с помощью функции условного суммирования для массивов;
- освоить применение формулы массива для выполнения операций с диапазоном ячеек;
- освоить прием условного форматирования для автоматического выделения диапазона ячеек;
- освоить приемы работы с базами данных и списками.
Общие сведения
Расчеты с условиями
Для выполнения расчетов, требующих проверки условий, в Excel имеется ряд функций.
Функция ЕСЛИ()
Синтаксис: ЕСЛИ(<условие>;<выражение1>;<выражение2>)
Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.
<выражение1>и<выражение2> могут быть числами, формулами или текстами. Текст должен быть заключен в кавычки.
Выполнение: если условие истинно, значение ячейки определяет <выражение1>, в противном случае – <выражение2>.
Логическое выражение состоит из констант, адресов или имен ячеек, знаков операций отношений (<, >, =, <=, >=,<>) и логических операций И, ИЛИ, НЕ.
Логические операции в Excel используются как логические функции, при их вызове записывается знак операции, затем в круглых скобках перечисляются логические операнды, разделяемые точкой с запятой, например:ИЛИ(A4>2;A4<-2).
Пример записи формул, содержащих условную функцию:
Пусть в таблице хранится информация о зачислении слушателей на курсы:
| A | B | C | D | |
| Список слушателей | ||||
| ФИО | Баллы | Учебное заведение | Информация о зачислении | |
| Петров Н.Г | лицей №1 | не зачислен | ||
| Алексеев А.Л. | школа №12 | зачислен | ||
| Андреева Е.Н. | лицей №1 | зачислен | ||
| Тихонов К.П. | лицей №1 | зачислен | ||
| Ветрова В.В. | лицей №4 | зачислен |
Тогда при условии зачисления тех, кто набрал не менее 12 баллов, в ячейку D3 введена формула
=ЕСЛИ(B3>=12,"зачислен","не зачислен"),
а при условии зачисления тех, кто набрал не менее 12 баллов и учится в лицее №1, в ячейку D3 должна быть введена формула
=ЕСЛИ(И(B3>=12,C3="лицей №1"),"зачислен","не зачислен").
Функции СЧЁТЕСЛИ() и СУММЕСЛИ()
ФункцияСЧЁТЕСЛИ(). подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.
Синтаксис: СЧЁТЕСЛИ(интервал;критерий)
Критерий‑ критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать в заданном интервале. Например, критерий может быть записан следующим образом: 32, "32", ">32", "яблоки".
Пример:
Пусть имеется таблица, содержащая информацию о сборе фруктов:
| А | В | С | |
| Сбор фруктов | |||
| Фамилия | Вид | Вес, кг | |
| Иванов | яблоки | ||
| Круглов | апельсины | ||
| Пономарев | персики | ||
| Алексеев | яблоки |
Тогда функция СЧЁТЕСЛИ(A3:С6,"яблоки") возвращает значение 2 (количество сборщиков яблок), а СЧЁТЕСЛИ(A3:C6,">55") возвращает значение 3 (количество сборщиков, собравших более 55 кг фруктов). Точно такие же результаты дадут функции
СЧЁТЕСЛИ(B3:B6,"яблоки") и СЧЁТЕСЛИ(C3:C6,">55").
Обратите внимание на то, что условие, содержащее знаки операций отношений, должно заключаться в кавычки.
ФункцияСУММЕСЛИ() действует аналогично функции СЧЕТЕСЛИ(), но вычисляет сумму содержимого ячеек заданного диапазона.
Синтаксис: СУММЕСЛИ(интервал выбора; критерий; интервал суммирования).
При выполнении этой функции суммируются значения только тех ячеек из интервала суммирования, для которых соответствующие значения в интервале выбора удовлетворяют критерию.
Например, для приведенной выше таблицы с данными о сборе фруктов по формуле =СУММЕСЛИ(B3:B6;"яблоки",C3:C6) вычисляется вес всех собранных яблок.
Если аргумент интервал суммирования опущен, то суммируются значения ячеек из диапазона интервал выбора. Например, для той же таблицы значение функции СУММЕСЛИ(С3:С6,">50") равно 218.