Вложенные логические функции ЕСЛИ

Указания к выполнению аудиторной контрольной работы

Аудиторная контрольная работа выполняется в табличном процессоре Excel. В работе необходимо будет решить одну из перечисленных далее задач. Исходные данные для заполнения таблицы подобрать самостоятельно (не менее 10 строк).

Тема 1: Использование логических и статистических функций MS EXCEL

Теория

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

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

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

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

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

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

И (логическое_значение1; Возвращает значение ИСТИНА, если все аргументы

Логическое_значение2; …) имеют значение ИСТИНА; возвращает значение

(AND) ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ; например, И(2+2=4; 2+3=5) равняется ИСТИНА, если ячейка B4 содержит число между 1 и 100, то И(1<B4; B4<100) равняется ИСТИНА

ИЛИ(логическое_значение1; Возвращает значение ИСТИНА, если хотя бы один

Логическое_значение2; …) аргументов имеет значение ИСТИНА ивозвращает

(OR) значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ; например, ИЛИ(2+2=4; 2+3=6) возвращает ИСТИНА; если ячейка B4 содержит число меньше 1 или больше 100, то ИЛИ(1<B4; B4>100) возвращает ИСТИНА.

НЕ (логическое_зачение) (NOT) Меняет на противоположное логическоезначение своего аргумента; например НЕ (2+2=5) возвращает ИСТИНА; если ячейка B4 содержит число меньше 1 или больше 100, то НЕ(ИЛИ(1<B4; B4>100)) возвращает ЛОЖЬ

Вложенные логические функции ЕСЛИ

Формат записи:

=ЕСЛИ(усл.1; выражение В; ЕСЛИ(усл.2; выражение С; ЕСЛИ(…))).

Число вложенных функций в принципе может быть любое, но общая длина строки не должна быть слишком большой.

Пример 1. Найти работников, у которых имеются одновременно задолжен-ности по обоим видам кредита, и удержать от начисленной им суммы 20% в счет погашения кредитов. С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной им суммы. Работникам, не имеющим задолженности по кредиту, проставить в графе “Удержано”- Б/К. В нашем примере логическая функция будет иметь следующий вид:

=ЕСЛИ(И(С3>0;D3>0);B3*0,2;ЕСЛИ(И(С3=0;D3=0);”б/к”;B3*0,1))

Данная логическая функция означает, что если одновременно задолженности по потребительскому кредиту на жилищное строительство больше нуля, то необходимо удержать 20% с начисленной суммы, если обе задолженности одновременно равны нулю, то необходимо вывести “б/к”, в противном случае необходимо удержать 10% от начисленной суммы.

Пример 2. Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике – 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
	Проходной	балл:
	Фамилия	Математика	Русский	Иностранный	Сумма	Зачисление
	Антонов					ДА
	Воробьев					НЕТ
	Синичкин					ДА
	Воронина					НЕТ
7	Снегирев					НЕТ
	Соколова					ДА

ВАРИАНТ 1

Задача: Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см? Возраст не должен превышать 13 лет.

Режим значений

			Максимальный возраст
			Минимальный рост
№	Фамилия	Возраст	Рост	Результат	Отклонение от нормы роста, %
…
Итого

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Баскетбольная команда»;

2. используя логические функции Excel, составить расчет зачисления детей в секцию, если туда принимают детей только с ростом не менее 160 см, возраст которых не должен превышать 13 лет.

3. используя статистические функции Excel, определите максимальный возраст участников команды; минимальный рост участников команды; средний рост участников команды и какое количество человек будет в команде, если их возраст не должен превышать 13 лет.

4. используя Мастер диаграмм Excel, построить круговую диаграмму, отражающую отклонение от нормы роста в %.

ВАРИАНТ 2

Задача: Каждому пушному зверьку в возрасте от 1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны.

Выяс­нить сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.

			Один стакан молока, л	0,2
Номер	Возраст	Вес	Дополнительный стакан	Л в день	Л в месяц
	1,5	2,5
	1,2	2,2
	1,6	2,6
	1,8	2,7
	2,5	3,5
		3,7
	1,1	2,2
	2,2
	2,8	3,6
		2,8
Итого

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Звероферма»;

2. используя формулы Excel, составить расчет, сколько литров молока надо в месяц для кормления зверьков;

3. используя логические функции Excel, составить расчет, нужен или не нужен дополнительный стакан молока, если его вес меньше 3 кг;

4. используя логические функции Excel, составить расчет, сколько литров в день молока надо, если один стакан молока 0,2 л;

5. используя статистические функции Excel, определите максимальный возраст зверьков; минимальный вес зверьков;

6. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, сколько литров молока в месяц необходимо для кормления «Зверофермы».

ВАРИАНТ 3

Задача: Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.

Режим значений

			Снижение дневного концентрата, кг.	0,125
Номер	Возраст	Вес	Дополнительный концентрат	в день	в месяц
	6,8	6,7
	7,4	6,9
	6,8	6,6
	7,3	6,9
	8,1	7,5
	7,6	7,1
		6,8
	8,3	7,3
	7,8	7,2
	6,5	6,4
Итого

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Звероферма»;

2. используя формулы Excel, составить расчет, на сколько килограммов в месяц снизится потребление витаминного концентрата;

3. используя логические функции Excel, составить расчет, нужен или не нужен дополнительный концентрат, если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг;

4. используя логические функции Excel, составить расчет, сколько кг составит снижение дневного концентра, если норма составляет 0,125 кг;

5. используя статистические функции Excel, определите минимальный возраст зверьков; максимальный вес зверьков;

6. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, на сколько килограммов в месяц снизится потребление витаминного концентрата.

ВАРИАНТ 4

Задача: Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: 0,6 рубля за 1 кВт/ч за первые 200 кВт/ч; 0,9 рубля за 1 кВт/ч, если потребление свыше 200 кВт/ч, но не превышает 500 кВт/ч; 1,2 рубля за 1 кВт/ч, если потребление свыше 500 кВт/ч. Услугами компании пользуются не менее 10 клиентов. Подсчитать плату для каждого клиента. Подсчитать суммарную плату всех клиентов; определить, сколько клиентов потребляют свыше 500 кВт/ч.

Режим значений

	Стоимость 1кВт/ч	свыше 200 кВт/ч
	от 200 до 500 кВт/ч
	свыше 500 кВт/ч
Клиент	Потребляет, кВт/ч	Плата
…
	Суммарная плата
	Клиентов потреб.>500 кВт/ч

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Оплата электроэнергии»;

2. используя формулы Excel, составить расчет, суммарной платы всех клиентов за электроэнергию; используя формулы Excel, составить расчет, суммарного расхода электрэнергии;

3. используя логические функции Excel, составить расчет взимания платы за электроэнергию;

4. используя статистические функции Excel, составить расчет, какое количество клиентов потребляет свыше 500 кВт/ч, максимальное количество электроэнергии, потребляемое клиентами;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую взимания платы за расход электроэнергии по клиентам.

ВАРИАНТ 5

Задача: Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 ме­сяцев, то – в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую ин­формацию: наименование товара, количество товара, срок хранения, стоимость товара, цена товара до уценки, цена товара после уценки.

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Уценка товара»;

2. используя формулы Excel, составить расчет, какова общая цена товара на складе до уценки и после уценки;

3. используя логические функции Excel, составить расчет цены после уценки, если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 ме­сяцев, то – в 1,5 раза;

4. используя статистические функции Excel, составить расчет, максимального срока хранения продуктов на складе; минимальную цену продуктов на складе до уценки;

5. используя Мастер диаграмм Excel, построить объемную круговую диаграмму, отражающую цену после уценки продуктов на складе.

ВАРИАНТ 6

Задача: В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают помидоры. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет K килограммов. Сбор 1 кг помидоров стоит Х рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза до­роже. Сколько денег в день получит каждый рабочий за собранный урожай?

Режим значений

		Дневная норма
		1 кг помидоров(руб.)
№ п/п	Рабочий	Собрал	Деньги за день
…

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Сбор помидоров»;

2. используя формулы Excel, составить расчет, сколько всего собрали сезонные рабочие помидор, сколько всего денег будет получено рабочими за день;

3. используя логические функции Excel, составить расчет, сколько денег получит каждый рабочий за день;

4. используя статистические функции Excel, определите, максимальное количество собранных томатов; минимальное количество собранных томатов;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую сколько денег получит каждый рабочий за день.

ВАРИАНТ 7

Задача: Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке «2»; оценке «3» соответствует количество баллов от 12 до 15; оценке «4» -16 до 20; оценке «5» - свыше 20 баллов. Составить ведомость тестирования, содержащую сведения фамилия, количество баллов, оценка.

Режим значений

№ п/п	Фамилия, Имя, Отчество	Количество баллов	Оценка
…

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Ведомость тестирования»;

2. используя формулы Excel, составить расчет, общее количество баллов;

3. используя логические функции Excel, составить расчет, полученных оценок за тест;

4. используя статистические функции Excel, определите, максимальную оценку за тест; минимальную оценку за тест;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, полученные оценки за тест.

ВАРИАНТ 8

Задача: Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: K рублей за 1 КВт/ч и Х рублей за каждый КВт/ч сверх нормы, которая составляет 50 КВт/ч. Услугами компании пользуются не менее 10 клиентов. Подсчитать плату для каждого клиента.

Режим значений

		Стоимость 1 кВт (руб.)
		Норма, кВт/ч
		Стоимость 1 кВт сверх нормы
Жилец	Потратил	Сумма к оплате
…
Итого

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Плата за электроэнергию»;

2. используя формулы Excel, составить расчет, общую сумму к оплате;

3. используя логические функции Excel, составить расчет, платы за электроэнергию для каждого жильца;

4. используя статистические функции Excel, определите, максимальную сумму к оплате; минимальную сумму к оплате;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, сумму к оплате по жильцам.

ВАРИАНТ 9

Задача: 10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену при­сваивается звание мастера, если он набрал в сумме не менее K очков. Сколько спортсменов получило звание мастера?

Режим значений

	Виды спорта	Звание мастера	К очков
Номер спортсмена						Всего очков
…
Звание получили

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Ведомость спортсменов-многоборцев»;

2. используя формулы Excel, составить расчет, сколько всего очков было получено каждым спортсменом;

3. используя статистические функции Excel, определите, сколько спортсменов получили звание мастера спорта; максимальное количество очков по каждому виду спорта; минимальное количество очков по каждому виду спорта, полученные спортсменами;

4. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, сколько всего очков было получено каждым спортсменом.

ВАРИАНТ 10

Задача: 10 учеников проходили тестирование по 5 темам какого-либо предмета. Вычислить суммарный (по всем темам) средний балл, полученный учениками. Сколько учеников имеют суммарный балл ниже среднего?

Режим значений

№	Фамилия И.О.	Тема 1	Тема 2	Тема 3	Тема 4	Тема 5	Ср.балл	Результат
…
					Суммарный ср. балл		Количество учеников

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Ведомость тестирования»;

2. используя формулы Excel, составить расчет, суммарный средний балл тестируемых учеников;

3. используя логические функции Excel, составить расчет, результатов тестирования учеников, имеющих средний балл «Выше среднего» или «Ниже среднего»;

4. используя статистические функции Excel, определите, средний балл каждого тестируемого за пройденные темы; количество учеников, у которых результат тестирования «Ниже среднего»;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, средний балл каждого тестируемого ученика.

ВАРИАНТ 11

Задача: Билет на пригородном поезде стоит 5 монет, если расстояние до станции не больше 20 км; 13 монет, если расстояние больше 20 км, но не превышает 75 км; 20 монет, если расстояние больше 75 км. Составить таблицу, содержащую следующие сведения: пункт назначения, расстояние, стоимость билета. Выяснить сколько станций находится в радиусе 50 км от города.

Режим значений

Цена		монет
	монет
	монет
Пункт назначения	Расстояние	Стоимость билета
…
	Станций в 50 км

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Стоимость билета на пригородный поезд»;

2. используя формулы Excel, составить расчет, общей стоимости билета по всем пунктам назначения;

3. используя логические функции Excel, составить расчет, стоимости билета;

4. используя статистические функции Excel, определите, сколько станций находится в радиусе 50 км от города; максимальную стоимость билетов до станций;

5. используя Мастер диаграмм Excel, построить круговую диаграмму, отражающую, стоимость билета в зависимости от пункта назначения.

ВАРИАНТ 12

Задача: Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каждую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц.

Режим значений

		Норма в месяц, мин
		Абонентская плата		монет
		Мин. сверх нормы		монеты
Жилец	Потратил, мин	Начислено
…
	Итого за месяц

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Плата за услуги телефонной связью»;

2. используя формулы Excel, составить расчет, сколько всего за месяц составляет плата за услуги телефонной компании;

3. используя логические функции Excel, составить расчет, сколько будет начислено каждому из жильцов за пользование телефонной связью;

4. используя статистические функции Excel, составить расчет, сколько в среднем жильцы потратили минут за услуги телефонной связью; определите, какое минимальное и максимальное количество минут потратили жильцы за услуги телефонной компании;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, оплату услуг телефонной связи для 10 жильцов за один месяц.

ВАРИАНТ 13

Задача: Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти наименований товаров или стоимость покупки превышает K рублей. Составить ведомость, учитывающую скидки: покупатель, количество наименований купленных; товаров, стоимости покупки, стоимость покупки с учетом скидки. Выяснить сколько покупателей сделали покупки, стоимость которых превышает K рублей.

Режим значений

			Кол-во тов. необх. для скидки
			Стоимость для скидки
№	Покупатель	Кол-во купл. товаров	Цена, руб.	Стоимость, руб.	Стоим. со скидкой
…
		Покупателей потративших больше К руб.

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Ведомость для учета скидок»;

2. используя формулы Excel, составить расчет, общей суммы потраченной покупателями за покупки; стоимости покупок из расчета количества и цены купленных товаров;

3. используя логические функции Excel, составить расчет, стоимости товаров со скидкой;

4. используя статистические функции Excel, определите, какое количество покупателей потратило больше К рублей на покупки; максимальную стоимость товаров;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, стоимости товаров и стоимости товаров со скидкой по покупателям.

ВАРИАНТ 14

Задача: Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: K рублей за 1 кВт/ч за первые 500 кВт/ч; K2рублей за 1 кВт/ч, если потребление свыше 500 кВт/ч, но не превышает 1000 кВт/ч; K3рублей за 1 кВт/ч, если потребление свыше 1000 кВт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента и суммарную плату. Сколько клиентов потребляет более 1000 кВт/ч.

Режим значений

	Стоимость 1кВт/ч	свыше 500кВт/ч	К
	от 500 до 1000кВт/ч	К2
	свыше 1000кВт/ч	К3
Клиент	Потребляет, кВт/ч	Плата
…
	Суммарная плата
	Клиентов потреб.>1000кВт/ч

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Плата за электроэнергию»;

2. используя формулы Excel, составить расчет, суммарной платы за потребляемую электроэнергию;

3. используя логические функции Excel, составить расчет, платы за электроэнергию для каждого из клиентов;

4. используя статистические функции Excel, определите, сколько клиентов потребляет свыше 1000 кВт/ч; максимальную плату за потребляемую электроэнергию;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, отражающую, плату за потребляемую электроэнергию по клиентам.

ВАРИАНТ 15

Задача: При температуре воздуха зимой до -20°С потребление угля тепловой станцией составляет K тонн в день При температуре воздуха от -30° С до -20° С дневное потребление увеличивается на 5 тонн, если температура воздуха ниже -30° С, то потребление увеличивается еще на 7 тонн. Составить таблицу потребления угля тепловой станцией за неделю. Сколько дней температура воздуха была ниже -30° С?

Режим значений

		Потреб. При t>-20	К
День	Температура	Потребление угля, т
…
	За неделю,т
	Темп.<-30, дней

Задание:

Проанализировав постановку задачи и исходные данные, автоматизировать расчет, для этого:

1. спроектировать в Excel электронную таблицу «Потребление угля тепловой станцией за неделю»;

2. используя формулы Excel, составить расчет, суммарный расход потребляемого угля за неделю тепловой станцией;

3. используя логические функции Excel, составить расчет, потребления угля в тоннах за каждый день;

4. используя статистические функции Excel, определите, какое количество дней температура воздухабыла ниже 30° С; среднесуточную температуру воздуха за неделю;

5. используя Мастер диаграмм Excel, построить столбчатую диаграмму, потребления угля по дням тепловой станцией.

Дата добавления: 2015-07-14