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.