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.