Условная функция и логические выражения

Условная функция. Общий вид условной функции сле­дующий:

ЕСЛИ(<условие>, <выражение1>, <выражение2>)

Эта функция принадлежит категории функций логические

Условие — это логическое выражение, которое может прини­мать значение ИСТИНА или ЛОЖЬ. <выражение 1> и выраже­ние 2> могут быть числами, формулами или текстами.

Условная функция, записанная в ячейку таблицы, выполняет­ся так: если условие истинно, то значение данной ячейки опреде­лит <выражение 1>,в противном случае — <выражение 2>.

Логические выражения. Логические выражения строятся с помощью операций отношения (<, >, <=(меньше или равно), >=(больше или рано), =, <>(не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются ло­гические величины ИСТИНА или ЛОЖЬ.

Существуют особенности записи логических операций в таб­личных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечис­ляются логические операнды.

Пример 1. Разработать таблицу, содержащую следую-щие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному язы­кам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике — 4 или 5, то аби­туриент зачислен в учебное заведение, в противном случае — нет.

Решение. Исходной информацией являются фамилии, оцен­ки за экзамены и проходной балл. Сумма баллов и информация о зачислении вычисляются с помощью формул.

Первоначально следует подготовить таблицу в следующем виде:

	А	В	С	D	Е	F
	Проходной	балл:
	Фамилия	Математика	Русский	Иностранный	Сумма	Зачислен

В ячейке С1 будет храниться значение проходного балла — 13. Формула в ячейке ЕЗ вычисляет сумму баллов за три эк­замена: ВЗ + СЗ + D3. Формула в ячейке F3 задается с помощью условной функции:

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

Условие, записанное с помощью логической операции И, можно расшифровать так: сумма баллов (ЕЗ) >= проходному баллу (С1) И оценка за экзамен по математике (ВЗ) > 3. Если условие выполняется, то в клетке F3 будет отображаться текст — ДА, в противном случае — НЕТ.

Для проходного балла в формуле используется абсолютный адрес $С$1, так как проходной балл является одинаковым и неизменным для всех абитуриентов. После заполнения 3-ей строки формулами, можно произвести копирование соответст­вующих формул в нижние строки. Формулы в столбцах Е и F после копирования будут выглядеть так:

		D	Е	F
			Сумма	Зачислен
			B3+C3+D3	ЕСЛИ(И(ЕЗ>=$С$1;ВЗ>3);«ДА»;«НЕТ»)
			B4+C4+D4	ЕСЛИ(И(Е4>=$С$1;В4>3);«ДА»;«НЕТ»)
			B5+C5+D5	ЕСЛИ(И(Е5>=$С$1;В5>3);«ДА»;«НЕТ»)

После ввода исходных данных получим таблицу в режиме отражения значений:

	А	В	С	D	Е	F
	Проходной	балл:
	Фамилия	Математика	Русский	Иностранный	Сумма	Зачислен
	Антонов					ДА
	Воробьев					НЕТ
	Синичкин					ДА
	Воронина					НЕТ
	Снегирев					НЕТ
	Соколова					ДА

 

Функции для анализа списка

 

Функции СЧЁТЕСЛИ и СУММЕСЛИ предназначены для работы со списками. В них можно задавать простые условия сравнения

Функция СЧЁТЕСЛИ имеет синтаксис

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

Аргумент диапазон задает диапазон, в котором подсчитывается количество значений, удовлетворяющих критерию. Критерий задается вторым аргументом условие, представляющим собой текстовое значение. Эта функция относится к категории статистических функций.

Пример

1. Необходимо подсчитать количество сотрудников с именем Иван. Для решения задачи понадобится формула =СЧЁТЕСЛИ(B2:B5;”Иван”). Ответ: 2.

2. Определить число сотрудников, у которых оклад не превышает 4000р. Задачу можно решить с помощью формулы =СЧЁТЕСЛИ(D2:D5;”<=4500”). Ответ: 3.

 

Функция СУММЕСЛИ имеет синтаксис

=СУММЕСЛИ(диапазон; условие; диапазон_суммирования).

Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических функций.

Пример

Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Иван. Воспользуемся функцией =СУММЕСЛИ(B2:B5; ”Иван”; D2:D5). Первый аргумент функции задает диапазон B2:B5, в котором осуществляется проверка условия (второй аргумент). Если условие выполняется, то значение из диапазона В2:В5 (третий аргумент) добавляется к итоговой сумме. Ответ: 7000.

 

Функции баз данных

 

Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех Д-функций один и то же синтаксис:

=Дфункция(база_данных;поле;критерий)

Аргумент база_данных задает весь список, а не отдельный столбец. Второй аргумент поле определяет столбец в котором производятся вычисления (суммирование, усреднение, и т.д.). В качестве второго аргумента можно использовать имя поля, задаваемое в виде текстового значения (название, заключенное в кавычки) или порядковый номер столбца в списке. Если формула задается с помощью мастера функций, при заполнении второго аргумента достаточно указать ячейку рабочего листа, в которой хранится имя соответствующего поля. Аргумент критерий задает диапазон критериев (условий).

 

Функции баз данных.

ДСРЗНАЧ. Вычисляет среднее значение в столбце списка или базы данных среди значений, удовлетворяющих заданным условиям.

БСЧЁТ. Подсчитывает количество ячеек, содержащих числа в столбце списка или базы данных среди всех записей, удовлетворяющих заданным условиям.

БСЧЁТА. Подсчитывает все непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям.

БИЗВЛЕЧЬ. Извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. Если такой ячейки не обнаружено, возвращается значение #ЗНАЧ!. Если заданным условиям удовлетворяют несколько ячеек, то возвращается ошибочное значение #ЧИСЛО!

ДМАКС. Возвращает наибольшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям.

ДМИН. Возвращает наименьшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям.

БДПРОИЗВЕД. Перемножает значения в столбце списка или базы данных, которые удовлетворяет заданным условиям.

БДСУММ. Суммирует числа в столбце списка или базы данных, которые удовлетворяет заданным условиям.

 

Пример. Подсчитать максимальный оклад у сотрудников с именем Иван.

 

 

Решение:

Используем одну из функций

=ДМАКС(A1:D5;4;F1:F2)

=ДМАКС(A1:D5;D1;F1:F2)

=ДМАКС(A1:D5;"Оклад";F1:F2)

 

Задание условий

 

Все условия задаются в некотором диапазоне критериев (условия). Диапазон включает заголовки столбцов и ячейки с условиями.