Правила расчета: (например)

· При покупке товара от10 упаковок до 20 упаковок скидка – 2%,

· При покупке товара от21 упаковок до 40 упаковок скидка – 6%,

· При покупке товара свыше 41 упаковки скидка – 10%.

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

 

Вариант 10. Определение коэффициента трудового участия (КТУ), Сумм(), Макс()

На таком производстве, где невозможно оценить индивидуальное количество и качество труда часто, принято по итогам месяца/недели оценивать их при помощи КТУ, которое в дальнейшем влияет на оплату. Чтобы исключить необъективность оценок, все члены бригады анонимно заполняет анкету, где выставляет КТУ (например, в диапазоне от 0 до 1) для каждого из своих коллег. Необходимо как-то обработать эти данные и сформировать обобщенные значения КТУ. На рисунке в колонках В:Е зафиксированы оценки для каждого из четырех членов бригады, в F они просуммированы, в G - вычисляется собственно КТУ

<КТУ работника>=<Всего КТУ работника>/<Сумма КТУ бригады>.

	А	В | С | D | E	F	G	Н
	Работники	Оценки	Всего КТУ	КТУ	КТУ
	Петр	0,90	0,70	0,70	0,90	3,2	0,277	0,98
	Иван	0,60	0,55	0,60	0,40	2,15	0,186	0,66
	Олег	0,80	0,80	0,55	0,80	2,95	0,255	0,91
	Вера	0,90	0,90	0,45	1,00	3,25	0,281	1,00
	Сумма 11,55 11,55

 

Эти значения (в таблице колонка G) уже можно использовать для оценки результатов труда, однако более удобно, если они находятся в том же диапазоне, что и исходные оценки, т.е. в диапазоне от 0 до 1 (столбец Н)

<КТУ работника>=<КТУ работника>/МАКС(КТУ всех работников). Рабочие формулы для первого работника приведены ниже:

F2=СУММ(В2:Е2), F6=СУММ(F2:F5), G2=F2/F$6,

Вариант 11. Расчет сдельной зарплаты, ЕСЛИ().

Построить таблицу вычисления сдельной (т.е. пропорциональной количеству произведенной продукции) зарплаты, а также налога и суммы на руки. Зарплата работника определяется числом обработанных деталей, умноженным на стоимость обработки. Если работник допустил брак и испортил деталь, ее стоимость вычитается из заработка

<зарплата>=<обработано деталей>*<стоимость работы>-<деталей брака>*<стоимость детали>.

Таким образом, Зарплата Ивана будет определяться формулой; D6=B6*C$2-C6*C$3. Полагаем, что налог исчисляется в зависимости от зарплаты: если она выше 5000 - налог составляет 20%, если нет - 13%. Иными словами

<сумма налога>=<зарплата>*ЕСЛИ(<зарплата> больше 5000, то 13%, иначе 20%).

Или Е6=06*ЕСЛИ(06>=5000;Р$1;С$1).

Сумма на руки вычисляется как F6=D6-E6. Итог по вертикали F9=СУММ(F6:F8).

	А	В	С	D | Е	F
	Налог до: 5000р	13%	Налог от: SOOOp	20%
	Стоимость работы:
	Стоимость детали:
	РАСЧЕТ ЗАРПЛАТЫ
	Работник	Обработано деталей	Деталей брака	Зарплата	Сумма налога	Сумма на руки
	Петр				1120,0	4480,0
	Иван				124,8	L 835,2
	Олег				540,8	3619,2
	ВСЕГО				2144,0	8934,4

Может случиться, что если брака окажется много, зарплата станет отрицательной (работник должен компенсировать нанесенный ущерб). В этом случае, конечно, никакой налог не удерживается и, следовательно, в формуле для налога следует предусмотреть выявление отрицательности заработка. С учетом вышесказанного уточним значение Е6

Е6=D6*ЕСЛИ(D6<=0;0;ЕСЛИ(D6>=5000;F$1;С$1)).

 

	С	D I E	F
	13%	Налог от:5000p	20%
	Деталей брака	Зарплата	Сумма налога	Сумма на руки
		=В6*С$2 -С6*С$3	=06*ЕСЛИ(06<=0;0;ЕСЛИ (D6>=5000;F$1;C$1))	=D6-E6
		=В7*С$2 -С7*С$3	=07*ЕСЛИ(07<=0;0;ЕСЛИ (D7>=5000;F$1;C$1))	=D7-E7
		=В8*С$2 -С8*С$3	=08*ЕСЛИ(08<=0;0;ЕСЛИ (D8>=5000;F$1;C$1))	=D8-E8
	=СУММ |=СУММ (С6:С8)|(D6:D8)	=СУММ (Е6:Е8)	=СУММ (F6:F8)

 

Для удобства последующего анализа данных сделаем так, чтобы работник, имеющий максимальный заработок, был выделен красным цветом, а минимальный - желтым. Для этого понадобится прибегнуть к условному форматированию клеток F6, F7, F8 вида:

Условие 1

[значение] [равно | |=МАКС(Р$6:Р$8)| - красный фон

Условие 2

[значение] [равно] |=MИH(F$6:F$8) | - желтый фон

Представить результаты расчета графически, используя деловую графику, (2-3 типа, круговую диаграмму обязательно).

Упорядочить список ведомости

· По алфавитному списку,

· По сумме зарплаты

 

Вариант 12. Повременная зарплата. Функция ГПР().

Построить таблицу вычисления повременной (т.е. зависящей от длительности рабочего времени) заработной платы работников на не­котором производстве. Последняя зависит от числа отработанных дней в месяце, разряда рабочего и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда

<зарплата>=<дней>*<тариф по разряду>.

	А	В	С | D | E	F
	Tapифная	сетка
	Разряд:
	Тариф:
	Премия:
	ЗАРПЛАТА
	Работник	Дней	Разряд	Зарплата	Премия	Начислено
	Петр
	Иван
	Олег
	ВСЕГО

 

Для розыска разрядного тарифа нам понадобится функция горизонтального поиска ГПР(). Например, для Петра должна быть использована формула Д7=ГПР(С7;В$2:F$4;2;0)*В7. Премия также зависит от разряда Е7=ГПР(С7;В$2:F$4;3;0). Всего работнику будет начислена сумма F7=E7+D7.

	А	В I С | D | е i f
	Тарифная сетка Та
	Разряд:
	Тариф:
	Премия:
	ЗАРПЛАТА
	Работник	Дней	Разряд	Зарплата	Премия	Начислено
	Петр			=ГПР(С7;В$2:F$4;2;0)*B7	=ГПР(С7;В$2:F$4;3;0)	=E7+D7
	Иван			=ГПР(С8;В$2:F$4;2;0)*B8	=ГПР(С8;В$2:F$4;3;0)	=E8+D8
	Олег			=ГПР(С9;В$2:F$4;2;0)*B9	=ГПР(С8;В$2:F$4;3;0)	=E9+D9
	ВСЕГО	=СУММ (В7:В9)		=СУММ =СУММ (D7:D9)| (E7:E9)	=СУММ (F7:F9)

 

В данном примере расчет начисленной суммы только для наглядности был разложен на отдельные компоненты.

 

Вариант 13. Расчет зарплаты. СУММПРОИЗВ(),ГПР()

Расчет зарплаты с учетом квалификации работника (должностного класса) и коэффициента (КТУ) трудового участия. Здесь класс и КТУ одинаковым образом влияют на заработок, но только класс присваивается "навсегда" (до пересмотра), а КТУ изменяется в зависимости от качества исполнения конкретной работы.

Положим, заказчик оценил всю работу в 10000 руб. и выдал аванс в размере 4000 руб., который и был распределен между людьми произ­вольным образом (кто сколько попросил). Наша задача состоит в том, чтобы по завершении работы распределить остальную часть заработанного (6000 руб.).

Прежде всего необходимо определить влияние класса и КТУ на размеры выплат. Найдем сумму этих величин. Поскольку коэффициент доплат за класс работника имеет тот же смысл, что и КТУ, то эта сумма образуется сложением пар сомножимых, образованных из обеих величин. В нашем случае это

<итого КТУ>=1*1,4+2*1,2+3*1 =3,7 или D9=СУММПРОИЗВ(С6:С8;D6:D8).

	А | В	С | D	Е	F	G
	Всего:;10000	Аванс: 4000	Остаток: 6000
	Надбавки за класс	класс: 1: 2: 3
	коэфф.: 1,4; 1,2; 1 1
	РАСПРЕДЕЛЕНИЕ ЗАРПЛАТЫ
	Ф.И.О	Класс	Коэфф. за класс	КТУ	Получен аванс	Заработано	Выдать остаток
	Петр		1,4
	Иван		1,2	1,5
	Олег			0,5			-849
	ИТОГО	3,7

 

Теперь можно определить причитающуюся рабочему сумму:

<заработано>=<всего>/<итого КТУ>*<коэфф. за класс>*<КТУ> для первого рабочего это F6=$B$1/D$9*C6*D6.

I С D | Е | F | G
5 Коэфф. за класс	КТУ	Получен аванс	Заработано	Выдать остаток
	=ГПР(В6; С$2:Е$3;2;0)			=B$1/D$9 *C6*D6	=F6-E6
	=ГПР(В7; С$2:Е$3;2;0)	1,5		=B$1/D$9 *C7*D7	=F7-E7
	=ГПР(В8; С$2:Е$3;2;0)	0,5		=B$1/D$9 *C8*D8	=F8-E8
		=СУММПРОИЗВ (C6:C8;D6:D8)	=СУММ (Е6:Е8)	=СУММ (F6:F8)	=СУММ (G6:G8)

 

Поскольку работник уже получил аванс, ему предстоит выдать (а может быть и взыскать с него, если аванс не был "отработан") сумму

<выдать остаток>=<заработано>-<получен аванс>

или G6=F6-E6.

Правильность наших расчетов подтверждает совпадение значений F9=B1 и G9=F1.

 

Вариант 14. Распределение премии. СУММ(),ЕСЛИ().

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

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

<помощь максимальная>=

если(<зарплата> меньше 5*<мин. зарплата>, то 5*<мин. зарплата> - <зарплата>, иначе 0).

	А	В	С	D	Е
	Премия:			Мин.зарп:
		РАСПРЕДЕЛЕНИЕ ПРЕМИИ
		Зарплата	Помощь	Итого
	Ф.И.О.	максим	фактич.
5 6 7 8	Петр
Ольга
Иван
Олег
	Всего
	А	В	С	D	Е
	Премия:			Мин.зарп:
	РАСПРЕДЕЛЕНИЕ ПРЕМИИ
	Ф.И.О.	Зарп лата	Помощь	Итого
	максим.	фактич.
	Петр		=ЕСЛИ(В5<5 *Е$1;5*Е$1 -В5;0)	=ЕСЛИ(С$9<=В$1;С5; С5*В$1/$С$9)	=ЕСЛИ(0$9<В$1; (B$1-D$9)/ B$9*B5)+D5
			...
	Олег		=ЕСЛИ(В8<5 *Е$1;5*Е$1 -В8;0)	=ЕСЛИ(С$9<=В$1;С8 С8*В$1/С$9)	=ЕСЛИ(0$9<В$1; (B$1-D$9)/ B$9*B8)+D8
	Всего	=СУММ| =СУММ (В5:В8)| (С5:С8)	=СУММ (D5:D8)	=СУММ (Е5:Е8)

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

<помощь фактическая>=

если(<общая максимальная помощь> меньше <премии>, то <помощь максимальная>,

иначе <помощь максимальная> *<премия>/<общая максимальная помощь>).

2. Остаток премии (если есть) делится уже между всеми работниками (включая и малооплачиваемых) пропорционально их зарплате

<итого>=если(<общая фактическая помощь> меньше <премии>, то (<помощь> - <общая фактическая помощь>)/ <общая зарплата>*<зарплата>) + <помощь фактическая>. Правильность расчетов подтверждает то, что В1=Е9.

 

Вариант 15. Динамическое исчисление налогов.СУММЕСЛИ().

Определение налогов в конце года ставит задачу выплаты работником сразу большой суммы, возможно даже значительно большей, чем зарплата за последний месяц/месяцы. Кроме того, в случае увольнения работника в конце года вообще проблематично получить с него этот самый налог. В виду сказанного, на предприятиях ежемесячный налог обычно исчисляется от нарастающей суммы дохода с вычетом уплаченных ранее налогов. На рисунке изображена таблица расчетов налогов к марту месяцу.

В рассматриваемом примере для упрощения выкладок налоговая сетка имеет всего две строчки - для дохода до и свыше 20000. Для yпрощения же считаем, что налог со всей суммы от 20000 составляет именно 20%. Наша задача - научиться определять налог в каждом из 12 месяцев, таким образом, чтобы учесть как тарифную сетку, так и уже уплаченные в предыдущих месяцах налоги. Для этого следует вы полнить вычисления:

<налог в текущем месяце>=

<сумма всех доходов к дате расчета>*<процент налога>-<сумма ранее уплаченных налогов>

	А	В 1 С	D | Е | F | G	н I
	Доход до 20000: 12%;
2_	Свыше 20000: 20%:
Динамическое исчисление налогов
4 5	Ф.И.О.	Сводный	Январь	Февраль	Март
Доход! Налог	Доход Налог	Доход Налог	Доход Налог
	Иван	24000: 4800	8000: 960	9000: 1080	7000: 2760
	Петр	1800 216	8000 96	1000; 120
	Анна	4000: 480	400: 48	600: 72	3000: 360
	Елена	14300 1716	300! 36	8000 960	6000 720
	Всего	44100: 7212	9500: 1140	18600: 2232	16000: 3840

 

Или конкретнее:

<налог>=<сумма всех клеток, имеющих в заголовке слово Доход>

*<размер совкупного налога>

-<сумма всех клеток, имеющих в заголовке слово Налог>

Технику вычислений налога для первого сотрудника (Ивана) в марте иллюстрирует таблица ниже.

Месяц	Доход	Полный налог за истекший период	Внесенный ранее налог	Налог текущего месяца
Январь		12% от 8000=960
Февраль		12% от 17000=2040
Март		20% от 24000=4800

 

Ниже показаны клеточные формулы налога для первого сотрудника в январе и феврале.

Е6=СУММЕСЛИ($D$5:D$5;"Доход";$D6:D6)*

ЕСЛИ(СУММЕСЛИ($D$5:D$5;"Доход";$D6:D6)<=$В$1;

$С$1;$С$2)-СУММЕСЛИ($D$5:D$5;"Налог";$D6:D6), G6=СУММЕСЛИ($D$5:F$5;"Доход";$D6:F6)*

ЕСЛИ(СУММЕСЛИ($D$5:F$5;"Доход";$D6:F6)<=$В$1;

$С$1;$С$2)-СУММЕСЛИ($D$5:F$5;"Налог";$D6:F6)

Формула для G6 получена простым копированием в нее клетки Е6. Здесь сумма всех доходов СУММЕСЛИ($D$5:D$5;"Доход";$D6:D6) умножается на процент налога $С$1, если она меньше $В$1 (меньше 20000), или на $С$2, если больше. Затем из результата вычитается сумма уже удержанных ранее налогов СУММЕСЛИ($D$5:D$5;"Налог"; $D6:D6).

Кроме того, нарастающим итогом вычисляются суммы доходов и налогов:

Сводный доход: В6=СУММЕСЛИ($D$5:$Y$5;"Доход";D6:Y6).

Сводный налог: С6=СУММЕСЛИ($D$5:$Y$5;"Налог";D6:Y6).

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

Вариант 16. Расчет стипендии. ДЛСТР(),СЧЕТ()

Создать таблицу начисления стипендии студентам ВУЗа. Пусть в сессию ими сдавались три экзамена и два зачета. В качестве результата экзамена вводится его оценка (кроме двойки). Результат сданного заче­та отмечается значком "+". Стипендия назначается всем студентам, полностью сдавшим сессию, в размере, определяемом средним баллом. Для расчета стипендии в зависимости от среднего балла имеется таблица коэффициентов, связывающая ее размер с величиной минимальной зарплаты (область J4:K8).

 

	А | В | С | D |Е | F | G	Н I I I J | К
	СТИПЕНДИЯ Мин. зарпл.: 100р
2_ 3	Студент	Экзамены	Зачеты	Ср. балл	Стипен-дия		Ср. балл	Коэфф.
Матем.	Физика	Химия	право	Этика
	Петр				+	-ь	3,67	110р
	Иван					+		0р	3,5	1,1
	Саша				+	+ '		0р		1,5
	Вера				+	+	4,67	180р	4,5	1,8
	Ср.балл	4,25	4,25	3,67
	Сдало						290р

 

Рассмотрим, какие формулы необходимы для первого студента в списке (Петра). Средний балл (G4) определяется как сумма всех баллов, полученных на экзаменах, деленная на их число ((B4+C4+D4)/3). Однако, если студент не сдал какой-либо из экзаменов или зачетов, т.е.

Математика<3 ИЛИ Физика<3 ИЛИ Химия<З

ИЛИ Право¹зачет ИЛИ Этика¹зачет,

средний балл принимается равным 0. Таким образом, критерий несдачи сессии Петром будет таков ИЛИ(В4<3;С4<3;D4<3;Е4<>"+";F4<>"+").

Его можно несколько упростить, если воспользоваться косвенным признаком, а именно тем фактом, что для несданных предметов клетки оценок остаются пустыми. Тогда для экзаменов произведение всех оценок равно нулю (B4*C4*D4=0), а для зачетов последовательность отметок не равна "++" (т.е. E4&F4<>"++"). Для формирования анализируемой строки все клетки, содержащие зачеты, сцепляются в одно слово (E4&F4). Окончательно, формула для Н4 будет иметь вид

G4=ЕСЛИ(ИЛИ(В4*С4*D4=0;Е4&Р4<>"++");0;(В4+С4+D4)/3).

В качестве признака наличия академических задолженностей можно использовать и еще более простой критерий - длина строки, образованной сцеплением оценок, меньше числа предметов

G4=ЕСЛИ(ДЛСТР(В4&С4&D4&Е4&F4)<5;0;(В4+С4+D4)/3).

Величина начисляемой стипендии (Н4) определяется с помощью функции вертикального поиска значения G4 в области J4:K8. Найденный коэффициент умножается на величину минимальной зарплаты K1.

Таким образом Н4=ЕСЛИ(G4=0;0;ВПР(G4;J$4:К$8;2;1)*К$1).

Кроме собственно стипендии в таблице вычисляются некоторые сводные характеристики: средний балл по всем экзаменам для студентов, имеющих оценки (В8=СРЗНАЧ(В4:В7) и т.д.), число студентов, получивших положительные оценки по каждому предмету (В9=СЧЁТ(В4:В7)), число студентов, получивших зачет по каждому из предметов (Е9=СЧЁТЗ(В4:В7)), общая сумма всех назначенных стипендий (Н9= СУММ(14:I7)).

 

Вариант 17. Оплата труда в торговле. НАИБОЛЬШИЙ(),МАКС(),ЕСЛИ().

Создать таблицу назначения премий продавцам-менеджерам фирмы в зависимости от суммы (в тыс. руб.) проданного ими товара. Премия (20% от общей выручки) назначается только менеджерам, занявшим по сумме продаж первые три места. В абсолютном исчислении эта сумма равна D1=C1*C14 (здесь 20% от 55 = 11 тыс. руб.). Премиальный фонд (D1) делится между участниками в следующей пропорциии: с коэффициентом 1 - за 1 место, с коэффициентом 0,7 - за второе, с коэффициентом 0,4 - за третье. По очевидным причинам таких людей может оказаться больше трех, если некоторые из "призеров" имели одинаковую выручку.

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

Сначала в таблице вычисляется общая сумма выручки С14=СУММ (С8:С13). Затем для каждого продавца находится процент его личной выручки В8=С8/С$14. Теперь следует выявить призовые места работников

<место>= если(<выручка> максимальна, то 1 место, иначе если(<выручка> вторая по значению, то 2 место, иначе если(<выручка> третья по значению, то 3 место, иначе

место не назначается))).

Или для первого работника

D8= ЕСЛИ(МАКС(С$8:С$13)=С8;1;

ЕСЛИ(НАИБОЛЬШИЙ(С$8:С$13;2)=С8;2;

ЕСЛИ(НАИБОЛЬШИЙ(С$8:С$13;3)=С8;3;"")))-

 

	А |	в	c	D	G | Н
	Премия продавцам::	20%: 11т!
	К -ты премирования рования
	за 1 место за 2 место за 3 место	0,7 0,4
4 5
	ПРОДАЖИ
	Продавец	Процент	Сумма	Место	Коэфф.	Премия
	Петр	21,8%	12т		0,7	3,08т
	Иван	16,4%	9т		0,4	1,76т
	Елена	7,3%	4т			0,00т
	Саша	38,2%	21т			4,40т
	Федор	0%	От			0,00т
	Толя	16,4%	9т		0,4	1,76т
	ВСЕГО	55т		2,5 11,00т

 

Для работников, не занявших призовых мест, в столбце Места выводятся пустые кавычки (т.е. ничего). В столбце Коэффициенты отражаются индивидуальные коэффициенты премирования в зависимости от занятого места

Е8=ЕСЛИ(D8=1;В$3;ЕСЛИ(D8=2;В$4;ЕСЛИ(D8=3;В$5;0))).

Собственно премия определяется очевидным образом

<премия>=<сумма премирования>/<сумма коэффициентов>*индивидуальный коэффициент.

Или F8=D$1/E$14*E8.

Как видим, сумма премий (HI4) в точности равна премиальному фонду (D1), т.е. расчеты выполнены верно.

К оформлению.

1. Удобно, если имена лучших работников будут выделены. Например, занявшие первое место - красным фоном, второе место - синим, третье - зеленым. Для этого выполним условное форматирование блока А8:А13 следующего вида:

Условие 1

Значение равно 1 - установить красный фон

Условие 2

Значение равно 2 - установить синий фон

Условие 1

Значение равно 3 - установить зеленый фон

2. Заметим, что столбец индивидуальных коэффициентов работников нужен только по техническим причинам (ради вычисления суммы всех коэффициентов) и по завершении построения таблицы его можно скрыть, оставив лишь значимые данные. Такой облик таблицы не отвлекает пользователя от восприятия важной информации и в то же время не ограничивает его в действиях - он может редактировать, удалять и создавать новые строки, не вникая больше в суть формул в скрытых столбцах.

 

Вариант 18. Расчет размеров помощи. ГОД(),И(),ИЛИ(),ЕСЛИ()

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

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

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

1. ЕСЛИ(С6>2;С6*Р$1;0).

2. ЕСЛИ(И(ГОД(В$1)-В6>60;ИЛИ(D6="И";Е6="У"));2*F$1;0).

В любом случае размер помощи не может превышать собственную зарплату работника (=МИН(Р6;<расчетная помощь>)). Таким образом, полная формула будет выглядеть так С5=МИН(Р6;(ЕСЛИ(С6>2;С6*F$1)+

ЕСЛИ(И(ГОД(В$1)-В6>60;ИЛИ(Р6="И";Е6="У));2*F$1))).

	А	В	С	D |	Е	F	G	Н
	Дата:	Ноя.99	НА	Миним. зарп.
2 3		ЗНАЧЕНИЕ ПОМОЩИ
4 5	Ф.И.О.	Год рожд.	Детей	Льготы	Зарплата	Помощь	Всего
инвалид	УчВ
	Иванов			И
	Петров
	Кулик			И	У
	Итого	56,7лет

 

По вертикали определяется средний возраст работников предприятия

=<число лет текущей даты> - <средний возраст работников> или В9=ГОД(В$1)-СРЗНАЧ(В6:В8).

Для этой ячейки назначен пользовательский формат вида: 0,0"лет". Кроме того, вычисляются число инвалидов (D9=СЧЁТЕСЛИ(D6:D8; "И")| и участников войны (Е9=СЧЁТЕСЛИ(Е6:Е8;"У").

Замечание. Здесь средний возраст определен весьма приблизительно, поскольку для работников отсутствуют сведения о месяце и дне рожде­ния, т.е. как бы считается, что они родились 1 января. Для нахождения статистических данных, вроде среднего возраста всех членов коллектива, это несущественно, но при вычислении, например, трудового стажа,! влияющего на оплату труда, так поступать нельзя.

 

Вариант 19. Оплата проката автомобилей.ДАТА,ВРЕМЯ,ЕСЛИ().

Создать таблицу расчетов с клиентами за наем автомобилей, цена часа проката которых известна. В момент взятия в прокат автомобиле работник пункта проката выставляет текущие и ожидаемые дату и время возврата (столбцы Взято с и Оплачено по). Для столбцов С, D и G назначен пользовательский формат даты вида Д.МММ чч:мм.

Исходя из стоимости часа проката, в таблице вычисляются время проката в часах и его стоимость. Для первой строки E6=(D6-C6)*24 F6=B6*E6. В момент возврата автомобиля оператор выставляет время возврата (столбец G). В колонке Н и I вычисляется разница в часах и в деньгах между оплаченной и фактической датами возврата автомобиля H6=(G6-D6)*24 и I6=В6*Н6.

 

	А | В	С	D I Е | F
	К-т возврата	0,50
	К-т доплаты	1,30
	ПРОКАТ АВТОМОБИЛЕЙ
4 Гип 5 авто	Цена часа	Взято с:	Оплачено по:	Оплачено
часов	сумма
	Волга		2.Ноя 10:00	12.Ноя 00:00	230,0
	БМВ		13.Ноя 01:30	21.Ноя 10:00	200,5
	Рено		4.Ноя 09:06	14.Ноя 00:00	230,9
9 Ауди		1. Ноя 01:00	б.Ноя 10:00	105,0
	ВАЗ		б.Ноя 06:30	19.Ноя 12:00	317,5

Продолжение таблицы

	G I Н | I	J
4 5	Дата возврата	Разница	Доплата /возврат
часы	сумма
	18.Ноя 05:00
	21. Ноя 10:00
	16.Ноя 08:00
	7.Ноя 00:30	38,5		12512,5
	14.Ноя 00:00	-132	-13200	-6600

Если клиент вернул его раньше оплаченного времени, ему возвращается часть денег (коэффициент возврата находится в клетке С1). Еcли позже - с него берется недостающая сумма с коэффициентом доплаты (находится в С2) за задержку автомобиля в прокате

J6=ЕСЛИ(I6>0;I6*С$2;I6*С$1).

Так, за прокат автомобиля ВАЗ клиенту будут возвращены деньги.