Методические указания

Материалы видиоучебника

Практическое занятие № 27

Создание и использование формул. Математические, тригонометриче­ские, статистические функции

Цель занятия:

сформировать умения и навыки по работе с функциями

Порядок выполнения задания

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

Задание 1.

Постановка задачи.

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

ЕСЛИ Объем продаж >= Нормированный объем продаж

И Объем расходов < Нормированный объем расходов;

ТО Комиссионные = Объем продаж * Премиальная ставка;

ИНАЧЕ Комиссионные = Объем продаж * Обычная ставка.

Решение 1. В ячейке D4 находится формула: =ЕСЛИ(И(В4>=$А$2;С4<$В$2);В4*$D$2;В4*$С$2).

Рис. Расчетная таблица задачи

	А	В	с	D
1	Нормированный	Нормированный	Обычная	Премиальная
	объем продаж	объем расходов	ставка	ставка
2	1000	300	15%	25%
3	Дата	Объем продаж	Объем расходов	Комиссионные
4	1.02.11	1 200	350	180
5	2.02.11	1 000	295	250

Решение 2. Комиссионные рассчитываются по тому же крите­рию, записанному с помощью функции ИЛИ:

ЕСЛИ Объем продаж < Нормированный объем продаж

ИЛИ Объем расходов >= Нормированный объем расходов;

ТО Комиссионные = Объем продаж * Обычная ставка;

ИНАЧЕ Комиссионные = Объем продаж * Премиальная ставка.

В ячейке D4 находится формула: =ЕСЛИ(ИЛИ(В4<$А$2;С4>=$В$2); B4*$C$2;B4*$D$2).

Задание2.

Использование команды Специальная вставка

Постановка задачи.

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

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

Фамилия И.О.	На начало месяца		В текущем месяце				На конец месяца
	Оста­ ток	Долг	Поступило	Оплатить	Доход института	Оста­ток		Долг
Бондарь А.Л.	500		2 000	800	=	=		=
Котова Р О.		600		900	=	=		=
Лазутин А Д.			800	800	=	=		=
Милевич Е.В.	1600			1 200	=	=		=
Новицкий В.Н.		800	2 000	800	=	=		=
Юрченко Л .П.		500	1 500	1 200	=	=		=
Яновская ОА.	100		800	1 200	=	=		=
Итог	∑	∑	∑	∑	∑	∑		∑

Рис. Расчетная таблица задачи

Таким образом, в начале каждого месяца студент имеет либо ос­таток денег, либо долг за прошлый период. Институт фиксирует поступления денег за текущий месяц на свой счет и устанавливает для каждого студента сумму средств на обучение в документе, форма которого представлена на рисунке.

Необходимо рассчитать следующие данные по приведенным вы­ражениям:

Доход института в текущем месяце =

ЕСЛИ Остаток на начало месяца + Поступило в текущем месяце >=

Долг на начало месяца + Оплатить в текущем месяце;

ТО Долг на начало месяца + Оплатить в текущем месяце;

ИНАЧЕ Остаток на начало месяца + Поступило в текущем месяце.

Остаток на конец месяца =

ЕСЛИ Остаток на начало месяца + Поступило в текущем месяце >=

Долг на начало месяца + Оплатить в текущем месяце;

ТО Остаток на начало месяца + Поступило в текущем месяце - Доход института в текущем месяце;

ИНАЧЕ О

Долг на конец месяца =

ЕСЛИ Остаток на начало месяца + Поступило в текущем месяце <

Долг на начало месяца + Оплатить в текущем месяце;

ТО Долг на начало месяца + Оплатить в текущем месяце - Доход института в текущем месяце;

ИНАЧЕ 0.

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

Решение.

1. На листе Январь создать расчетную таблицу и ввести формулы согласно таблице. Результат приведен на рисунке.

Таблица. Формулы для реализации задачи

Название столбца	Адрес ячейки	Формула
Доход в текущем месяце	F3	=ЕСЛИ(ВЗ+03>=СЗ+ЕЗ;СЗ+ЕЗ;ВЗ+03)
Остаток на конец месяца	G3	= ЕСЛИ (B3+D3>=C3+E3;B3+D3-F3;0)
Долг на конец месяца	НЗ	= ЕСЛИ (B3+D3<C3+E3;C3+E3-F3;0)

2. Создать копию листа январь, присвоив ей имя февраль.

3. На листе Февраль выделить диапазон ячеек где находятся остаток и долг на конец месяца по студентам (итоги не выделять, ), и скопировать в буфер обмена.

4. Активизировать ячейку b3, содержащую остаток на начало месяца для первого студента

5. Выполнить команду Правка/Специальная вкладка и в появившемся диалоговом окне специальная вставка установить опцию значение.

6. Очистить диапазон ячеек , где содержаться данные по поступлениям и оплате для текущего месяца. Значение остатка и долга на конец и начало месяца должны совпасть.

7. Согласно таблице 4.6 ввести новые данные в графы. Поступило в текущем месяце и Оплатить в текущем месяцце на листе Февраль и выполнить переход на март.

Фамилия И. О.	Поступило	Оплатить
Бондарь А.Л.	0	800
Котова Р.О.	2500	800
Лазутин А.Д.	700	700
Милевич Е.В.	0	1000
Новицкий Н.В.	600	1000
Юрченко Л.П.	0	1100
Яновская О.А.	2500	900

Использование функции СУММЕСЛИ

Задание 3.

Постановка задачи.

1. В табличном процессоре Excel создать расчетную таблицу со­гласно рисунку.