Использование логических функций

Электронные таблицы Excel

Задание 1.

Заработная плата за январь.

№ п/п	ФИО	Начисления	Всего начислено	Налог	К выдаче
Оклад	Районный коэффициент	Премия
			15%	35%		13%
	Васечкин С.И
	Репин М.В.
	Анечкин Р.Д.
	Сидоров С.Н.
	Петухов А.Д.
Итого:
Средний оклад
Максимальный оклад
Минимальный оклад

Построить таблицу «Заработная плата за январь», выполнить расчеты, используя функции СУММ, МАКС, МИН, СРЗНАЧ. Постройте диаграмму, используя столбцы «ФИО» и «К выдаче» (для выделения несмежных столбцов удерживайте кнопку Ctrl)

Использование логических функций.

Функции И и ИЛИ позволяют создавать сложные высказывания, с помощью которых можно проверить выполнение (или невыполнение) сразу нескольких условий.

Задание 2. Предположим что в бюро трудоустройства, где ведутся списки желающих получить работу, поступил запрос. Требования работодателя – образование высшее, возраст не более 25 лет.

Фамилия	пол	Образование	год рождения	кандидат
Беликов М.И.	м	в
Бочкарева А.П.	ж	в
Дерюгин С.С.	м	с/с
Иванов П.П.	м	с
Иванова С.В.	ж	с
Бялко О.О.	ж	в
Виноградова Т.Н.	ж	с/с
Иванов П.П.	м	в

Для отбора из этого списка кандидатов, соответствующих требованиям работодателя, 23можно использовать логическую функцию И. Её действие заключается в том, что она присваивает значение Истина, если все аргументы имеют значение Истина. Если хотя бы один из аргументов имеет значение ЛОЖЬ, результатом будет значение ЛОЖЬ.

Задание 3.

В то же бюро по трудоустройству поступил запрос на специалиста с высшим или средним специальным образованием. Для решения данной задачи следует использовать функцию ИЛИ. Эта функция вводит значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА. Значение ЛОЖЬ вводится, если все аргументы имеют значение ЛОЖЬ.

Задание 4.

Работодателю требуются женщины с высшим образованием и мужчины со средним специальным. Применяются 2 функции ИЛИ и И. Аргументами функции ИЛИ являются вложенные функции И, каждая из которых проверяет одновременное выполнение двух условий: пола и образования.

=ИЛИ(И(пол="м";образование="с/с");И(пол="ж";образование="в"))

Задание 5.

Кандидаты с высшим образованием – инженеры, со средним или средним специальным образованием – техники.

=ЕСЛИ (образование="в"; инженер; техник).

Задание 6.

Книжный магазин
Наименование	Цена	Скидка
Гарри Поттер и Философский камень
Гарри Поттер и Тайная комната
Гарри Поттер и Узник Азкабана
Гарри Поттер и Огненная чаша
Гарри Поттер и Орден Феникса
Гарри Поттер и Принц Полукровка	378,9
Гарри Поттер и Дары смерти	293,5
Минимальная цена
Маскимальная цена
Средняя цена

Рассчитать скидку. Если цена меньше 300, скидка 5%, если выше 300, скидка 7%.

Задание 7.

№	Фамилия	Зарплата
	Деникин Л.И.
	Петров С.С.
	Колпаков И.К.
	Колчак К.И.
	Старцев Р.У.
	Ведин П.В.
	Борисов З.С.
	Леонидов Б. С.
Итого:
Минимальное значение
Максимальное значение
Среднее значение

Добавьте колонки: премия, итого, материальная помощь, к выдаче.

Если зарплата превышает 10000, то премия 500, иначе 200.

Если зарплата превышает 8000, то материальная помощь 0, если ниже то 4000

Задание 8.

Таблица рассчетов
№	Фамилия	Категория	Месяцы	за квартал	надбавка
Январь	Февраль	Март
	Иванов
	Петров
	Козлов
	Львов
	Тигров
	Кошечкин
	Хомяков
	Курочкин
Итого	х
Минимальное значение	х
Максимальное значение	х
Среднее значение	х

Надбавка определяется к сумме за квартал в зависимости от категории.

При категории 1 равна 50%, при категории 2 и 3 категории – 30 %.

Задание 9.

Итоги летней сессии
Фио	Группа	Экзамены
Математика	Русский язык	История
Иванов И. И.	ФТ-47
Петров П. Р.	АП-29
Сидорова Ж.Г.	ФП-39
Васильева Л. Е.	ФТ-47
Елисеева Л.В.	АП-29
Каменский Р.Т.	ФП-39
Яковлев Г. М.	ФТ-47
Букин Р.Г.	АП-29

Добавить колонку стипендия, и начислить стипендию только тем студентам, у которых нет троек по всем предметам. (если, или).

Задание 10.

Анализ заработной платы сотрудников
Месяц	Январь
Отдел	ФИО	Оклад	Премия		Средний оклад
Первый	Иванов	4 000р.			Максимальный оклад
Первый	Петров	4 000р.			Минимальный оклад
Первый	Сидров	4 000р.			Количество выданных премий
Второй	Яковлев	3 000р.
Второй	Александров	4 444р.			Отдел	Первый	Второй
Второй	Сергеев	6 000р.			Количество сотрудников
Второй	Ильин	8 000р.	1 600р.		Фонд заработной платы по отделам
Второй	Павлов	9 000р.	1 800р.
Второй	Николаев	5 000р.	1 000р.

Определить средний, максимальный и минимальный оклад сотрудников (Функции МАКС, МИН, СРЗНАЧ). Определить скольким сотрудникам была выписана премия (функция СЧЕТЗ). Определить количество сотрудников и объем заработной платы по каждому отделу. (функции СЧЕТЕСЛИ, СУММЕСЛИ)

СЧЕТЗ – подсчитывает количество непустых ячеек

СЧЕТЕСЛИ – подсчитывает количество непустых ячеек, удовлетворяющих определенному условию.

СУММЕСЛИ – суммирует ячейки, указанные заданным условием.

Задание 11.

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

№	Фамилия	№ зачетной	Оценка	Подпись экзаменатора
п/п	книжки
	Иванов		отлично
	Петров		хорошо
	Сидров		удовл
	Яковлев		хорошо
	Александров		отлично
	Сергеев		отлично
	Ильин		удовл
	Павлов		неявка
	Николаев		отлично
		отлично
		хорошо
		удовл
		неудовл
		неявка

Постройте круговую диаграмму, которая характеризовала бы распределение оценок в группе.

Задание 12.

Провести анализ товарооборота. Для анализа использовать таблицу приложения 1.

Используя функцию СУММЕСЛИ, определить, на какую сумму было продано товаров каждым сотрудником.

Используя функции СЧЕТЗ и СРЗНАЧ определить общее количество сотрудников и средний товарооборот.

Анализ товарооборота по сотрудникам
Сотрудники	Итого
Яколев Я.Я.
Михайлов М.М.
Алешин А.А.
Петров П.П
Иванов И.И
Количество сотрудников
Средний товарооборот по сотрудникам

Постройте круговую диаграмму, которая продемонстрирует распределение товарооборота между сотрудниками.

Задание 13.

Провести анализ товарооборота. Для анализа использовать таблицу приложения 1.

Используя функцию СУММЕСЛИ, определить, какое количество товаров было продано по каждому виду товара.

Используя функцию МАКС и МИН, определить самый дорогой и самый дешевый товар.

Анализ товарооборота по товарам
Товар	Количество
Холодильник
Утюг
Пылесос
Миксер
Обогреватель
Кондиционер
Телевизор
Электроплита
Стиральная машина
Люстра
Самый дорогой товар
Самый дешевый товар

Постройте круговую диаграмму, которая продемонстрирует долю проданного товара.

Задание 14.

Провести анализ товарооборота. Для анализа использовать таблицу приложения 1

Используя функцию СУММЕСЛИ определить, на какую общую сумму было продано товаров каждой фирме.

Используя функции СЧЕТЗ и СРЗНАЧ определить общее количество фирм и средний товарооборот с каждой фирмой.

Анализ товарооборота по фирмам
Фирмы	Итого
Радуга
Омега
Спутник
Каскад
Рекорд
Корунд
Альфа
Гамма
Вета
Горизонт
Количество фирм
Средний товарооборот по фирмам

Постройте круговую диаграмму, которая продемонстрирует распределение товарооборота между фирмами.

Задание 15.

С целью увеличения товарооборота на оптовой базе установлены торговые скидки. Если определенный вид товара покупается на сумму более чем 10 тыс. рублей, назначается скидка.

Рассчитать сумму продаж с учетом скидки, используя функцию ЕСЛИ.

Применить денежный формат Рубли.

Расчет суммы продажи с учетом скидки
Скидка	10%
Артикул товара	Стоимость	Количество	Сумма продажи с учетом скидки
Т-246	1 000р.
К -905	600р.
Л-583	777р.
Т-586	888р.
Д-895	333р.
Д-987	495р.
М-З56	89р.
З-900	10р.
А-500	500р.

Постройте гистограмму, характеризующую долю каждого товара в общем объеме продаж.

Задание 16.

Прогрессивный налог облагает доходы предприятий свыше 100 тыс. рублей 25% -ой ставкой налога. Если доход меньше или равен 100 тыс. руб., то налог на доход равняется 15%. Рассчитать сумму налога.

Применить денежный формат Рубли.

Прогрессивный налог
Налогооблагаемый доход	Ставка налога
100 000	15%
свыше 100 000	25%
Название фирмы	Облагаемый доход	Сумма налога
Альфа	123 000р.
Вета	35 400р.
Гамма	576 000р.
Дельта	19 400р.
Омега	375 000р.
Сигма	87 000р.

Постройте сравнительную гистограмму налогов.

Задание 17.

В торговой фирме перед Новым Годом устроена праздничная распродажа. Рассчитать сумму продаж с учетом скидки, назначаемой в период распродажи.

В функции ЕСЛИ для описания условий использовать логическую функцию И.

Применить денежный формат Рубли.

Расчет суммы продажи с учетом скидки
Скидка	20%	Праздничная распродажа	21 дек	31 дек
Артикул товара	Стоимость	Количество	Дата продажи	Сумма	Скидка	Сумма продажи с учетом скидки
Д-895	333р.		17 ноя
К -905	600р.		14 ноя
К -905	10р.		31 дек
Л-583	777р.		15 ноя
Л-583	777р.		24 дек
Т-246	1 000р.		12 ноя
Т-246	1 000р.		21 дек
Т-586	888р.		16 ноя
У- 546	89р.		13 ноя
У- 546	89р.		22 дек

Задание 18.

В торговой фирме установлены дни распродаж - последние числа месяца. Рассчитать сумму продаж с учетом скидки, назначаемой в дни распродажи.

Для описания условий в функции, ЕСЛИ использовать логическую функцию ИЛИ.

Применить денежный формат Рубли.

Расчет суммы продажи с учетом скидки
Скидка	20%	Даты распродажи	31 окт	30 ноя
Артикул товара	Стоимость	Количество	Дата продажи	Сумма	Скидка	Сумма продажи с учетом скидки
А-500	500р.		15 ноя
Д-987	495р.		12 ноя
З-900	600р.		14 ноя
З-900	600р.		31 окт
М-З55	777р.		31 окт
М-З55	777р.		13 ноя
Т-246	1 000р.		12 ноя
Т-246	1 000р.		30 ноя
Т-587	333р.		30 ноя

Задание 19.

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

Финансовая сводка за неделю
дни недели	доход	расход	финансовый результат
понедельник	2 300,00р.	2 400,00р.
вторник	4 500,90р.	3 200,78р.
среда	2 900,00р.	1 600,00р.
четверг	3 800,75р.	2 700,70р.
пятница	7 900,19р.	8 100,68р.
суббота	8 100,78р.	9 500,90р.
воскресенье	5 907,80р.	4 300,18р.
среднее значение
общий финансовый результат

Финансовый результат = Доход-Расход

Для ячеек доход, расход, финансовый результат задайте формат «Денежный» с выделением отрицательных чисел красным цветом.

Рисунок 4. Формат ячеек

Постройте диаграмму линейчатого типа.

Скопируйте таблицу на 2 лист, произведите фильтрацию значений дохода превышающих 3500 руб.(Данные/Фильтр/Автофильтр).

Практическая работа.

Задание 20.

В задании использовать базу данных приложения 2.

К 8 марта руководство предприятия решило выплатить премии всем женщинам

Графу: Пол заполнить связыванием.

Рассчитать премию, используя функцию ЕСЛИ.

Премия к 8 марта
Премия	500р.
ФИО	Пол	Премия
Алексеева И.П.
Андреев Е.А.
Веренина Г.А.
Гордеев Ф.Ю.
Гришин А.А.
Дмитриева Е.К.
Дробова И.Г.
Замянский В.Т.
Зудин Р.Л.
Киреев В.А.
Котов Г.Г
Кудин И.И.
Кузнецов В.В.
Кумарина Г.В.
Лебедева К.Б.
Легков Д.Н.
Линева Р.Б.
Михеев О.А.
Моисеенко В.Г.
Пименов Г.Б.
Попова О.А.
Привалов Н.Е
Прянишников Е.А.
Пустовалов Н.В.
Родионов И.А.
Савостьянов А.П.
Симоянова Е.В.
Стрельников Н.В.
Ухтомская А.П.
Филина Г.Б.
Ягодкина Л.А.

Для установки связи скопируйте таблицу из приложения 2 на второй лист. Таблицу «Премия к 8 марта» на 1 лист. На 2 листе скопируйте первое значение графы «пол», далее перейдите на 1 лист, в ячейку «Пол» и нажмите Правка/Специальная вставка – Вставить связь. В ячейке появилась формула (необходимо подкорректировать ее, удалив знаки $). Далее скопируйте эту формулу остальным (можно растянуть, используя маркер автозаполнения).

Задание 21.

В задании использовать базу данных приложения 2.

К 23 февраля руководство предприятия решило выплатить премии всем участникам военных операций

Графу: Участник ВОВ и др. заполнить связыванием.

Рассчитать премию, используя функцию ЕСЛИ.

Премия к 23 февраля
Премия	1 000р.
ФИО	Участник ВОВ, Афганистан, Чечня	Премия
Алексеева И.П.
Андреев Е.А.
Веренина Г.А.
Гордеев Ф.Ю.
Гришин А.А.
Дмитриева Е.К.
Дробова И.Г.
Замянский В.Т.
Зудин Р.Л.
Киреев В.А.
Котов Г.Г
Кудин И.И.
Кузнецов В.В.
Кумарина Г.В.
Лебедева К.Б.
Легков Д.Н.
Линева Р.Б.
Михеев О.А.
Моисеенко В.Г.
Пименов Г.Б.
Попова О.А.
Привалов Н.Е
Прянишников Е.А.
Пустовалов Н.В.
Родионов И.А.
Савостьянов А.П.
Симоянова Е.В.
Стрельников Н.В.
Ухтомская А.П.
Филина Г.Б.
Ягодкина Л.А.

Задание 22.

В задании использовать базу данных приложения 2.

В связи с началом нового учебного года руководство предприятия приняло решение всем штатным сотрудникам выплатить социальную надбавку на детей в объеме 1 тыс руб. на 1 ребенка.

Графы: Принадлежность к штату, Количество детей и Оклад заполнить связыванием.

Итого= Окдад+Выплаты на детей

Применить денежный формат Рубли.

	Расчет выплат за сентябрь месяц
Надбавка на детей
ФИО	Принадлеж-ность к штату	Кол-во детей на иждивении родителей	Выплаты на детей	Оклад	Итого
Алексеева И.П.
Андреев Е.А.
Веренина Г.А.
Гордеев Ф.Ю.
Гришин А.А.
Дмитриева Е.К.
Дробова И.Г.
Замянский В.Т.
Зудин Р.Л.
Киреев В.А.
Котов Г.Г
Кудин И.И.
Кузнецов В.В.
Кумарина Г.В.
Лебедева К.Б.
Легков Д.Н.
Линева Р.Б.
Михеев О.А.
Моисеенко В.Г.
Пименов Г.Б.
Попова О.А.
Привалов Н.Е
Прянишников Е.А.
Пустовалов Н.В.
Родионов И.А.
Савостьянов А.П.
Симоянова Е.В.
Стрельников Н.В.
Ухтомская А.П.
Филина Г.Б.
Ягодкина Л.А.
Яковлева Д.Л.

Отобразите распределение заработной платы в виде гистограммы.

Задание 23.

В задании использовать базу данных приложения 2.

В конце года руководство предприятия приняло решение выделить персональные надбавки в объеме месячного оклада сотрудникам проработавшим ровно 10 лет или 20 лет на данном предприятии.

Графы: Год приема на работу и Оклад заполнить связыванием.

Итого= Оклад+ Персональная надбавка.

Расчет выплат за декабрь месяц 2003 года
ФИО	Оклад	Год приема на работу	Персональная надбавка	Итого
Алексеева И.П.
Андреев Е.А.
Веренина Г.А.
Гордеев Ф.Ю.
Гришин А.А.
Дмитриева Е.К.
Дробова И.Г.
Замянский В.Т.
Зудин Р.Л.
Киреев В.А.
Котов Г.Г
Кудин И.И.
Кузнецов В.В.
Кумарина Г.В.
Лебедева К.Б.
Легков Д.Н.
Линева Р.Б.
Михеев О.А.
Моисеенко В.Г.
Пименов Г.Б.
Попова О.А.
Привалов Н.Е
Прянишников Е.А.
Пустовалов Н.В.
Родионов И.А.
Савостьянов А.П.
Симоянова Е.В.
Стрельников Н.В.
Ухтомская А.П.
Филина Г.Б.
Ягодкина Л.А.
Яковлева Д.Л.

Отобразите распределение заработной платы в виде гистограммы.

Задание 24.

С целью уменьшения текучести кадров администрация фирмы решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. 10% надбавка к окладу выплачивается работнику, если он проработал на предприятии не менее пяти лет. Если работник проработал на предприятии свыше 10 лет, то надбавка –20%. Провести расчет оклада с учетом надбавки

Использовать вложенную функцию ЕСЛИ. Применить денежный формат Рубли.

Расчет оклада
№ п/п	Ф.И.О.	Оклад	Непрерывный стаж работы на одном предприятии	Надбавка за рабочий стаж	Оклад с учетом надбавки за рабочий стаж
	Иванов	3 000р.
	Петров	2 000р.
	Сидров	3 000р.
	Яковлев	5 000р.
	Сергеев	4 000р.
	Павлов	5 000р.
	Николаев	2 500р.

Отобразите распределение заработной платы в виде гистограммы.

Задание 25.

С целью уменьшения текучести кадров администрация фирмы решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. 10% надбавка к окладу выплачивается работнику, если он проработал на предприятии не менее пяти лет. Если работник проработал на предприятии свыше 10 лет, то надбавка — 20%. Если работник проработал на предприятии свыше 20 лет, то надбавка — 30%. Провести расчет оклада с учетом надбавки за стаж. При расчетах использовать вложенную функцию ЕСЛИ.

ЗАРПЛАТА
ФИО	Оклад	Стаж	Разряд	Надбавка	Выплата
Петров	3000р.
Сидоров	2000р.
Яковлев	3000р.
Сергеев	5000р.
Павлов	4000р.
Николаев	5000р.
Иванов	7000р.
Макс. зарплата

Отобразите распределение заработной платы (выплаты) в виде гистограммы.

Задание 26*.

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

1. При наличии более 2-х детей — в размере одной минимальной зарплаты на каждого ребенка.

2. Инвалидам и участникам войны (УчВ) в возрасте свыше 60-и лет — в размере двух минимальных зарплат.

Ф.И.О.	Год рождения	детей	Льготы	Зарплата
			Инвалид	УчВ
Иванов			И
Петров
Перцов
Огурцов			И	у
Давыденко
Бондаренко			И
Кукушкина

Для расчета премии использовать функцию МИН.

Задание 39.

Создать и отформатировать таблицу. В последней строке таблицы вывести наивысший % экономии.

Ведомость учета горючего

№	Фамилия водителя	Норма расхода	Факт. расход	Экономия	Перерасход
литр	%	литр	%
	Андреев А.А.
	Степанов С.П.
	Машковский И.Т.
	Дмитриев В.М.			.
	Вострикова Н.А.
	Комеч А.И.
	Мозжечкова Г.М.
	Максимальный % экономии

Отобразите в виде объемной диаграммы сравнительную характеристику нормы и фактического расхода горючего каждым водителем.