C$1;$C$2)–СУММЕСЛИ($D$5:F$5;"Налог";$D6:F6)
Формула для G6 получена простым копированием в нее клетки Е6.
Здесь сумма всех доходов СУММЕСЛИ($D$5:D$5;"Доход";$D6:D6)умножается на процент налога$C$1, если она меньше $B$1(меньше 20000), или на $C$2, если больше. Затем из результата вычитается сумма уже удержанных ранее налоговСУММЕСЛИ($D$5:D$5;"Налог"; $D6:D6).
Кроме того, нарастающим итогом вычисляются суммы доходов и налогов:
Сводный доход: B6=СУММЕСЛИ($D$5:$Y$5;"Доход";D6:Y6).
Сводный налог: C6=СУММЕСЛИ($D$5:$Y$5;"Налог";D6:Y6).
Эти сводные данные мы, против обыкновения, разместили не в конце (т.е. в правой части) таблицы, а в ее начале, что удобнее для пользователя, поскольку ему не придется перелистывать громоздкую таблицу по горизонтали, чтобы их увидеть (в декабре таблица достигнет столбца Y).
Пример 5.23. Расчет стипендии. Создать таблицу начисления стипендии студентам ВУЗа и получения сводных данных о результатах сессии. Пусть в сессию студентами сдавались три экзамена и два зачета. В качестве результата экзамена вводится его оценка (кроме двойки). Сданный зачет отмечается значком “+”. Стипендия назначается всем студентам, полностью сдавшим сессию, в размере, определяемом средним баллом. Для выполнения расчетов имеется таблица коэффициентов, связывающая ее размер с величиной минимальной зарплаты (область K4:L8). Конечно, студенты, не сдавшие сессию, стипендии не получают и, кроме того, в столбце Задолжники они помечаются словом ”1зд”(если задолженность одна), или словом “отч” (более одной задолженности), как планируемые к отчислению из института за академическую неуспеваемость.
Рассмотрим, какие формулы необходимы для первого студента в списке (Петра). Средний балл (G4) определяется как сумма всех баллов, полученных на экзаменах, деленная на их число ((B4+C4+D4)/3). Однако, если студент не сдал какой-либо из экзаменов или зачетов, т.е.
История<3 ИЛИ Физика<3 ИЛИ Химия<3 ИЛИ Право¹зачет ИЛИ Этика¹зачет,
средний балл принимается равным 0. Таким образом, критерий несдачи сессии Петром будет таков ИЛИ(В4<3;C4<3;D4<3;E4<>"+";F4<>"+"). Его можно несколько упростить, если воспользоваться косвенным признаком, а именно тем фактом, что для несданных предметов клетки оценок остаются пустыми. Тогда для экзаменов произведение всех оценок равно нулю (B4*C4*D4=0), а для зачетов последовательность отметок не равна "++" (т.е. E4&F4<>"++"). Для формирования анализируемой строки все клетки, содержащие зачеты, сцепляются в одно слово (E4&F4). Окончательно, формула для Н4 будет иметь вид
G4=ЕСЛИ(ИЛИ(B4*C4*D4=0;E4&F4<>"++");0;(B4+C4+D4)/3).
В качестве признака наличия академических задолженностей можно использовать и еще более простой критерий – длина строки (ДЛСТР()), образованной сцеплением оценок, меньше числа предметов
G4=ЕСЛИ(ДЛСТР(B4&C4&D4&E4&F4)<5;0;(B4+C4+D4)/3).
| A | B | C | D | E | F | G | H | I | J | K | L | |
| СТИПЕНДИЯ | Мин. | зарп. | ||||||||||
| Студент | Экзамены | Зачеты | Ср. балл | Стипендия | Задолжн. | 100р | ||||||
| История | Физика | Химия | Право | Этика | Ср. балл |  Коэфф.
| ||||||
| Петр | + | + | 5,0 | 200р | ||||||||
| Иван | + | + | 4,7 | 180р | 3,5 | 1,1 | ||||||
| Саша | + | + | 4,0 | 150р | 1,5 | |||||||
| Олег | + | + | 3,7 | 110р | 4,5 | 1,8 | ||||||
| Вера | + | + | 3,0 | 100р | ||||||||
| Игорь | + | + | 1зд | |||||||||
| Лена | + | + | отч | |||||||||
| Ср.балл | 3,9 | 4,2 | 4,4 | Сумма | ||||||||
| Сдало | 740р | Рис. | 5.23а |
Величина начисляемой стипендии (H4) определяется с помощью функции вертикального поиска значения G4 в области K4:L8. Найденный коэффициент умножается на величину минимальной зарплаты L2. Таким образом H4=ЕСЛИ(G4=0;0;ВПР(G4;K$4:L$8;2;1)*L$2). Поскольку в таблице коэффициентов отсутствует строка, где средний балл равен нулю, этот факт пришлось учитывать непосредственно в формуле H4.
Неуспевающие студенты выявляются следующим образом. Сначала определяются студенты, имеющие менее четырех оценок (к отчислению), затем – все остальные, не сдавшие сессию (т.е. с одной задолженностью).
I4=ЕСЛИ(ДЛСТР(B4&C4&D4&E4&F4)<4;"отч";ЕСЛИ(H4=0;" 1зд ";"")).
Кроме собственно стипендии в таблице вычисляются некоторые простые сводные характеристики: средний балл по всем экзаменам для студентов, имеющих оценки (B11=СРЗНАЧ(B4:B10) и т.д.), число студентов, получивших положительные оценки по каждому предмету (B12=СЧЁТ(B4:B10)), число студентов, получивших зачет по каждому из предметов (E11=СЧЁТЗ(B4:B10)), общую сумму всех назначенных стипендий (H12=СУММ(H4: H10)). По итогам сессии обычно требуется сформировать и более сложные показатели, например такие (рис. 5.23б). Рассмотрим как они получаются.
Число всех студентов успешно сдавших сессию: D14=СЧЁТЕСЛИ(G$4:G10;">0").
Число студентов сдавших сессию только на отлично (отличников): D15=СЧЁТЕСЛИ(G$4:G10;5).
Количество студентов сдавших сессию без троек (хорошистов):
D16 {=СЧЁТ(ЕСЛИ(B$4:B10>3;ЕСЛИ(C$4:C10>3;ЕСЛИ(D$4:D10>3;ЕСЛИ(G$4:G10>0;ЕСЛИ(G$4:G10<5;1))))))}.
Здесь подсчитываются только студенты не имевшие троек на всех экзаменах и средним баллом больше нуля и меньше пяти (последнее позволяет исключить из подсчета отличников, поскольку только для них средний балл =5). Такая формула может быть только формулой для массивов, т.е. ее ввод завершается нажатием клавиш Ctrl+Shift+Enter (выражение обрамляется фигурными скобками). Если воспользоваться тем фактом, что результатом операции сравнения будет число 1 или 0 (отношение истинно/ложно), формулу можно упростить следующим образом
D16 {=СЧЁТ(ЕСЛИ((B4:B10>3)*(C4:C10>3)*(D4:D10>3)*(G4:G10>0)*(G4:G10<5);1))}.
Количество студентов сдавших сессию с тройками (троечников):
D17 {=СЧЁТ(ЕСЛИ((G$4:G10>0)*((B$4:B10=3)+(C$4:C10=3)+(D$4:D10=3));1))}.
Здесь подсчитываются студенты, имеющие средний балл больше нуля и хотя бы одну тройку на экзамене.
Число студентов не сдавших сессию, т.е. средний балл которых равен нулю (двоечники): D18=СЧЁТЕСЛИ(G$4:G10;0).
Количество студентов, подготавливаемых к отчислению (имеющих минимум две задолженности):
D19 {=СЧЁТ(ЕСЛИ(((B$4:B10=0)+(C$4:C10=0)+(D$4:D10=0)+(E$4:E10<>"+")+(F$4:F10<>"+"))>1;1))}.
Подсчитываются студенты, имеющие больше одного (>1) “хвоста”.
Последний вычисляемый параметр – средний балл студентов, сдавших сессию (для них средний балл >0)
D20 {=СРЗНАЧ(ЕСЛИ(G$4:G10>0;G$4:G10))}.
Пример 5.24. Оплата труда в торговле.Создать таблицу назначения еженедельных премий продавцам-менеджерам фирмы в зависимости от суммы (в тыс. руб.) проданного ими товара. Премиальный фонд (20% от общей выручки – клетка E3) назначается только менеджерам, занявшим по сумме продаж первые три места. В абсолютном исчислении эта сумма равна E4=E3*B14(здесь 20% от 73 =14,6 тыс. руб.). Эта сумма делится между участниками в следующей пропорции: с коэффициентом 1 – за 1 место, с коэффициентом 0,7 – за второе, с коэффициентом 0,4 – за третье. По очевидным причинам таких людей может оказаться больше трех, если некоторые из “призеров” имели одинаковую выручку. В таблице должны фигурировать: имя продавца, процент выручки данного работника от общей суммы, призовое (если есть) место продавца, индивидуальный коэффициент премирования и премия (если есть). Сначала в таблице вычисляется общая сумма выручки B14=СУММ( B8:B13). Затем для каждого продавца находится процент его личной выручки C8=B8/B$14.Теперь следует выявить призовые места работников, используя функцию РАНГ( ): D8=РАНГ(B8;B$8:B$13;0).В столбце Коэффициенты отражаются индивидуальные коэффициенты премирования в зависимости от занятого места. Функция ВЫБОР( ) извлекает из таблицы коэффициентов премирования нужные значения.
E8=ВЫБОР(ЕСЛИ(D8<=3;D8;4);B$3;B$4;B$5;0).
Если занятое место имеет значения 1, 2, 3 (т.е. £3), его коэффициент разыскивается в группе ячеек B3, B4, B5. Если нет, искомое значение принимается равным четырем. Этому значению в группе извлечения соответствует цифра ноль (последний аргумент в функции).
Собственно премия определяется очевидным образом
премия=премиальный_фонд/сумма_коэффициентов*индивидуальный_коэффициент. Или F8=E$4/E$14*E8.
Как видим, сумма премий (F14) в точности равна премиальному фонду (E3), т.е. расчеты выполнены верно.
К оформлению.
1. Удобно, если имена лучших работников будут выделены. Например, занявшие первое место – красным фоном, второе место – синим, третье – зеленым. Для этого выполним условное форматирование блока А8:А13 следующего вида:
Условие 1 значение равно 1 – установить красный фон
Условие 2 значение равно 2 – установить синий фон
Условие 3 значение равно 3 – установить зеленый фон
2. Заметим, что столбец индивидуальных коэффициентов работников нужен только по техническим причинам (ради вычисления суммы всех коэффициентов) и по завершении построения таблицы его можно скрыть, оставив лишь значимые данные. Такой облик таблицы не отвлекает пользователя от восприятия важной информации и в то же время не ограничивает его в действиях – он может редактировать, удалять и создавать новые строки, не вникая больше в суть формул в скрытых столбцах.
Следует отметить, что при рассмотренном способе определения призовых мест может оказаться, например, 4 первых места и ни одного второго или третьего (если четыре человека имели одну и ту же максимальную выручку) или, как в нашем случае, 2 первых и 2 третьих места и ни одного второго (два человека имели одинаковую максимальную выручку и еще два – одинаковую выручку поменьше) и т.п. Если такой механизм выявления призеров вас устраивает, все в порядке. Если же вы хотите, чтобы в таблице обязательно присутствовали все три первых места без пропусков, то придется поступить более сложным способом. На рис. 5.24б приведен фрагмент такой таблицы. Для экономии места строки с первой по шестую опущены (они идентичны рис. 5.24а). Здесь при определении призового места нами введены три дополнительных столбца D, E, F. В столбце D выявляем лиц, занявших 1 место. Они отмечаются текстом вида “1м”. Для остальных работников отображается их выручка
| A | B | C | D | E | F | |
| Премия продавцам: | ||||||
| К-ты премирования | Премиальный фонд | |||||
| за 1 место | Процент: | 20% | ||||
| за 2 место | 0,7 | Сумма: | 14,6т | |||
| за 3 место | 0,4 | |||||
| П Р О Д А Ж И | ||||||
| Продавец | Выручка | Процент | Место | Коэфф. | Премия | |
| Петр | 12т | 16,4% | 0,4 | 2,1т | ||
| Иван | 9т | 12,3% | ||||
| Елена | 12т | 16,4% | 0,4 | 2,1т | ||
| Саша | 20т | 27,4% | 5,2т | |||
| Федор | ||||||
| Толя | 20т | 27,4% | 5,2т | |||
| ВСЕГО | 73т | 100,0% | 2,8 | 14,6т | ||
| Рис. | 5.24а |
=ЕСЛИ(максимальная выручка в магазине=выручка данного работника, то выводим “1м”, иначе сама выручка)
или D10=ЕСЛИ(МАКС(B$10:B$15)=B10;"1м";B10).
В столбце E аналогичным образом выявляем лиц, занявших 1 и 2 места, а F – 1-3 места. Однако в качестве анализируемой области используем не исходный столбец Выручка, а предшествующий столбец, содержащий не только значения выручки, то текстовые строки, которые теперь замещают числовые значения
E10=ЕСЛИ(МАКС(D$10:D$15)=D10;"2м";D10)
F10=ЕСЛИ(МАКС(E$10:E$15)=E10;"3м";E10).
Функция МАКС( ) находит максимальное число в области D10:D15. Имеющиеся здесь текстовые значения вида “1м” воспринимаются ею как нули. Теперь мы превратим текстовые значения в числовые (“1м” – в цифру 1, “2м” – в 2, “3м” – в 3). Это можно сделать с помощью функции ЕСЛИ( )
G10=ЕСЛИ(F10="1м";1;
ЕСЛИ(F10="2м";2;ЕСЛИ(F10="3м";3;0))).
| A | B | C | D | E | F | G | H | I | |
| . . . | |||||||||
| Продавец | Выручка | 1м | 1-2м | 1-3м | Место | Коэфф. | Премия | ||
| Петр | 2м | 2м | 0,7 | 2,7 | |||||
| Иван | 3м | 0,4 | 1,5 | ||||||
| Елена | 2м | 2м | 0,7 | 2,7 | |||||
| Саша | 1м | 1м | 1м | 3,8 | |||||
| Федор | |||||||||
| Толя | 1м | 1м | 1м | 3,8 | |||||
| ВСЕГО | 3,8 | 14,6 | |||||||
| Рис.6 | .24б |
Здесь, если место не призовое, результатом будет 0. Возможно и более короткое решение путем непосредственного преобразования первого символа в слове на число G10=ЗНАЧЕН(ЛЕВСИМВ(F10;1)).
Далее находим, соответствующий занятому месту, коэффициент премирования
H10=ВЫБОР(ЕСЛИ(G10<1;4;G10);B$3;B$4;B$5;0).
Если занятое место <1 (в нашем случае 0), коэффициент принимается равным нулю.
Видим, что сумма премиальных выплат совпадает с выделенным фондом, т.е. расчеты произведены правильно, однако сами премии оказались иными, нежели на рис. 5.24а.
Далее технические столбцы D, E, F можно скрыть.
Пример 5.25. Расчет размеров помощи.Создать таблицу расчетов по выделению материальной помощи работникам предприятия. Положим, она назначается по двум причинам:
1. При наличии более 2-х и более детей – в размере одной минимальной зарплаты на каждого ребенка.
2. Инвалидам и участникам войны (УчВ) в возрасте свыше 60-и лет – в размере двух минимальных зарплат.
Для Иванова это:
1. ЕСЛИ(C6>2;C6*F$1;0).
2. ЕСЛИ(И(ГОД(B$1)–B6>60;ИЛИ(D6="И";E6="У"));2*F$1;0).
В любом случае размер помощи не может превышать собственную зарплату работника(=МИН(F6;<расчетная помощь>)).Таким образом, полная формула будет выглядеть так
G5=МИН(F6;(ЕСЛИ(C6>2;C6*F$1)+ЕСЛИ(И(ГОД(B$1)–B6>60;ИЛИ(D6="И";E6="У"));2*F$1))).
По вертикали определяется средний возраст работников предприятия
=число_лет_текущей_даты – средний_возраст_работников или B9=ГОД(B$1)–СРЗНАЧ(B6:B8).
| A | B | C | D | E | F | G | H | |
| Дата: | Ноя.99 | Миним. | зарп. | |||||
| НАЗНАЧЕНИЕ ПОМОЩИ | ||||||||
| ФИО | Год рожд. | Детей | Л ь г о т ы | Зарп- лата | По- мощь | Всего | ||
| Инвалид | УчВ | |||||||
| Иванов | И | |||||||
| Петров | ||||||||
| Кулик | И | У | ||||||
| Итого | 56,7лет | |||||||
| Рис. 5.25 |
Для этой ячейки назначен пользовательский формат вида:0,0"лет".Кроме того, вычисляются число инвалидов (D9=СЧЁТЕСЛИ(D6:D8;"И") и участников войны (E9=СЧЁТЕСЛИ(E6:E8;"У"). Если бы нам понадобилось вычислить число лиц имеющих обе льготы (т.е. инвалидность и участие в войне), то пришлось бы прибегнуть к формулам, работающим с массивами: {=СЧЁТ(ЕСЛИ(D6:D8= "И";ЕСЛИ(E6:E8="У";1)))}или проще{=СЧЁТ( ЕСЛИ((D6:D8="И")* (E6:E8="У");1))}.
Замечание. Здесь средний возраст определен весьма приблизительно, поскольку для работников отсутствуют сведения о месяце и дне рождения, т.е. как бы считается, что они родились 1 января. Для нахождения статистических данных, вроде среднего возраста всех членов коллектива, это несущественно, но при вычислении, например, трудового стажа, влияющего на оплату труда, так поступать нельзя. К этому вопросу мы вернемся позже.
Пример 5.26. Оплата проката автомобилей.Создать таблицу расчетов с клиентами за наем автомобилей, цена часа проката которых известна. В момент взятия в прокат автомобиля работник пункта проката выставляет текущие и ожидаемые дату и время возврата (столбцы Взято с и Оплачено по). Для столбцов С, D и G назначен пользовательский формат даты вида Д.МММ чч:мм. Исходя из стоимости часа проката, в таблице вычисляются время проката в часах и его стоимость. Для первой строки E6=(D6–C6)*24 и F6=B6*E6. В момент возврата автомобиля оператор выставляет время возврата (столбец G). В колонке Н и I вычисляется разница в часах и деньгах между оплаченной и фактической датами возврата автомобиля Н6=(G6–D6)*24 и I6=B6*H6.Если клиент вернул его раньше оплаченного времени, ему возвращается часть денег (коэффициент возврата находится в клетке C1). Если позже – с него берется недостающая сумма с коэффициентом доплаты (находится в С2) за задержку автомобиля в прокате J6=ЕСЛИ(I6>0;I6*C$2;I6*C$1).Так, за прокат автомобиля ВАЗ клиенту будут возвращены деньги.
| A | B | C | D | E | F | |||||||
| К-т возв | рата | 0,50 | ||||||||||
| К-т допл | аты | 1,30 | ||||||||||
| П Р О К А Т | А В Т | О М О | Б И Л Е Й | |||||||||
| Тип | Цена | Взято | Оплачено | Опла | чено | Дата | Раз | ница | Доплата | |||
| авто | часа | с: | по: | часов | сумма | возврата | часы | сумма | /возврат | |||
| Волга | 2.Ноя 10:00 | 12.Ноя 00:00 | 230,0 | 18.Ноя 05:00 | ||||||||
| БМВ | 13.Ноя 01:30 | 21.Ноя 10:00 | 200,5 | 21.Ноя 10:00 | ||||||||
| Рено | 4.Ноя 09:06 | 14.Ноя 00:00 | 230,9 | 16.Ноя 08:00 | ||||||||
| Ауди | 1.Ноя 01:00 | 5.Ноя 10:00 | 105,0 | 7.Ноя 00:30 | 38,5 |
| ||||||
| ВАЗ | 6.Ноя 06:30 | 19.Ноя 12:00 | 317,5 | 14.Ноя 00:00 | –132 | –13200 | –6600 |
Пример 5.27. Зарплата с учетом разряда и трудового стажа.Создать таблицу расчета выплат заработка по результатам месяца в зависимости от разряда и стажа работников. Для этого имеется тарифная сетка, содержащая коэффициенты премирования сотрудника в зависимости от его разряда и стажа работы на производстве (в левой верхней части листа на рис. 5.27б). Стаж изображается в форме цифры стажа и слова “лет” (например “10 лет”) с помощью формата вида: 0” лет”, а разряд (например “3 разр.”)– с помощью формата 0” разр.” О каждом сотруднике известны: дата поступления его на предприятие (Дп), разряд, зарплата. На основе этих данных необходимо определить его премию (доплату за стаж и разряд) на произвольно заданный день расчета (Др). Кроме того, следует найти средний трудовой стаж всех работников.
Задача определения числа прошедших лет и/или месяцев является очень актуальной. Во многих случаях стаж работы влияет на оплату труда (например, выслуга лет у военнослужащих, размер пенсии и т.п.). Ее решение может быть различным в зависимости от целей расчета. Если требуется найти, например, средний стаж работников предприятия, то достаточно просуммировать стаж (в днях) каждого из них и поделить на 364 и число работников. Не так в случае определения стажа отдельного работника, если это влияет на оплату труда. Если поступить аналогичным образом, может оказаться, что будет получена неточная цифра, ведь число дней в году может составлять и 365. Кроме того, перерасчет зарплаты производится не немедленно по достижении нужной даты, а начиная только со следующего месяца – по прошествии установленного числа полных месяцев стажа. Пусть, например, сотрудник зачислен на работу 12.6.94 и через пять лет (т.е. после 12.6.99) ему положена прибавка зарплаты. По существующему законодательству она последует только в следующем месяце – в июле 1999 г.
| 11.97 | 12.97 | 1.98 | 2.98 | 3.98 | 4.98 | 5.98 | 6.98 | 7.98 | 8.98 | 9.98 | 10.98 | 11.98 | 12.98 | 1.99 | 2.99 | |||
|
Теперь получим формулы расчета искомого промежутка времени. Определим его как разность между числом месяцев, прошедших от 1.1.1900 г. (от начала отсчета всех дат в Excel) до начала месяца Дп, и числом месяцев от 1.1.1900 г. до начала месяца Др и вычтем отсюда еще один месяц (месяц в котором находится Дп).
Выполним сначала эти расчеты применительно к нашим данным.
1. Число полных месяцев от 1.1.1900 до месяца Дп:
Год поступления*12 + Месяц поступления – 1.
В нашем случае это
ГОД(15.11.1997)*12+МЕСЯЦ(15.11.1997)-1=1997*12+11-1=23974 месяца.
2. Число полных месяцев от 1.1.1900 до месяца Др, исключая один месяц, в котором находится Дп:
Год расчета*12+ Месяц расчета –1–1 или
ГОД(25.1.1999)*12+МЕСЯЦ(25.1.1999)-1-1=1999*12+1-1-1=23987 месяцев.
3. Число полных месяцев от Дп до Др:
Месяцев до даты расчета – Месяцев до даты поступления или 23987–23974=13 месяцев.
Таким образом, можем записать
(ГОД(Др)*12+МЕСЯЦ(Др)–1–1)–(ГОД(Дп)*12+МЕСЯЦ(Дп)–1)=
ГОД(Др)*12+МЕСЯЦ(Др)–1–ГОД(Дп)*12–МЕСЯЦ(Дп)=
12*(ГОД(Др)–ГОД(Дп))+МЕСЯЦ(Др)–МЕСЯЦ(Дп)–1.
Если подсчет стажа ведется в годах, полученный результат нужно поделить на 12. Воспользуемся полученным выше выражением. Так, для первого работника стаж в годах (Е9) составит
E9=(12*(ГОД(G$2)–ГОД(B9))+МЕСЯЦ(G$2)–МЕСЯЦ(B9)–1)/12.
Теперь найдем коэффициент премирования в таблице тарифных ставок. Этот коэффициент находится на пересечении колонки с соответствующим разрядом (функция ПОИСКПОЗ(C9;A$1:F$1;0)) и стажем (функция ПОИСКПОЗ(E9;A$1:A$6;1)). Адрес клетки пересечения определяется функцией ИНДЕКС(A$1:F$6; номер_строки; номер_ столбца). Или окончательно
F9=ИНДЕКС(A$1:F$6;ПОИСКПОЗ(E9;