Примеры практических задач 3 страница
Е6=D6*ЕСЛИ(D6<=0;0;ЕСЛИ(D6>=5000;F$1;C$1)).
Замечание к условию задачи. Следует отметить, что с 2001г. в России с физических лиц прогрессивный подоходный налог (когда процент налога увеличивается с увеличением доходов) заменен на фиксированный в 13% независимо от доходов и исчисление налогов сильно упростилась. Однако, автор оставил этот и другие такого рода примеры в книге по двум причинам: с юридических лиц налоги берутся аналогичным образом (но по регрессивной шкале); сам механизм исчисления налогов имеет хождение не только для налогов и не только в денежном обращении и поэтому представляет самостоятельный интерес.
| C | D | E | F | ||
| 13% | Налог от: | 5000р | 20% | ||
| Деталей брака | Зарплата | Сумма налога | Сумма на руки | ||
| =B6*C$2–C6*C$3 | =D6*ЕСЛИ(D6<=0;0;ЕСЛИ(D6>=5000;F$1;C$1)) | =D6–E6 | |||
| =B7*C$2–C7*C$3 | =D7*ЕСЛИ(D7<=0;0;ЕСЛИ(D7>=5000; F$1;C$1)) | =D7–E7 | |||
| =B8*C$2–C8*C$3 | =D8*ЕСЛИ(D8<=0;0;ЕСЛИ(D8>=5000;F$1;C$1)) | =D8–E8 | |||
| =СУММ(C6:C8) | =СУММ(D6:D8) | =СУММ(E6:E8) | =СУММ(F6:F8) | Рис. 5.13б |
К оформлению. Для удобства последующего анализа данных сделаем так, чтобы работник, имеющий максимальный заработок, был выделен красным цветом, а минимальный – желтым. Для этого понадобится прибегнуть к условному форматированию клеток F6, F7, F8 вида:
Условие 1 значение равно =МАКС(F$6:F$8) – красный фон
Условие 2 значение равно =МИН(F$6:F$8) – желтый фон
| A | B | C | D | E | F | |
| Тарифная сетка | ||||||
| Разряд: | ||||||
| Тариф: | ||||||
| Премия: | ||||||
| З А Р П Л А Т А | ||||||
| Работник | Дней | Разряд | Зарплата | Премия | Начислено | |
| Петр | ||||||
| Иван | ||||||
| Олег | ||||||
| ВСЕГО | ||||||
| Рис. | 5.14а |
Пример 5.14. Повременная зарплата.Построить таблицу вычисления повременной (т.е. зависящей от длительности рабочего времени) заработной платы работников на некотором производстве. Последняя зависит от числа отработанных дней в месяце, разряда рабочего и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда
зарплата=дней*тариф_по_разряду.
Для розыска разрядного тарифа нам понадобится функция горизонтального поиска ГПР(). Например, для Петра должна быть использована формула D7= ГПР(C7;B$2:F$4;2;0)*B7. Премия также зависит от разрядаE7=ГПР(C7;B$2 :F$4;3;0). Всего работнику будет начислена сумма F7=E7+D7. В данном примере расчет начисленной суммы только для наглядности был разложен на отдельные компоненты. Ничто не мешает нам, при необходимости, соединить все формулы в одну, уменьшив размер таблицы F7=ГПР(C7;B$2:F$4;2;0)*B7+ ГПР(C7; B$2:F$4;3;0).
| A | B | C | D | E | F | |||
| Тарифная сетка | ||||||||
| Разряд: | ||||||||
| Тариф: | ||||||||
| Премия: | ||||||||
| З А Р П Л А Т А | ||||||||
| Работник | Дней | Разряд | Зарплата | Премия | Начислено | |||
| Петр | =ГПР(C7;B$2:F$4;2;0)*B7 | =ГПР(C7;B$2:F$4;3;0) | =E7+D7 | |||||
| Иван | =ГПР(C8;B$2:F$4;2;0)*B8 | =ГПР(C8;B$2:F$4;3;0) | =E8+D8 | |||||
| Олег | =ГПР(C9;B$2:F$4;2;0)*B9 | =ГПР(C8;B$2:F$4;3;0) |
| |||||
| ВСЕГО | =СУММ(B7:B9) | =СУММ(D7:D9) | =СУММ(E7:E9) | =СУММ(F7:F9) |
Пример 5.15. Расчет зарплаты с учетом квалификации работника (должностного класса) и коэффициента (КТУ) трудового участия. Здесь класс и КТУ одинаковым образом влияют на заработок, но только класс присваивается “навсегда” (до пересмотра), а КТУ изменяется в зависимости от качества исполнения конкретной работы.
Положим, заказчик оценил всю работу в 10000 руб. и выдал аванс в размере 4000 руб., который и был распределен между людьми произвольным образом (кто сколько попросил). Наша задача состоит в том, чтобы по завершении работы распределить остальную часть заработанного (6000 руб.).
Прежде всего необходимо определить влияние класса и КТУ на размеры выплат. Найдем сумму этих величин. Поскольку коэффициент доплат за класс работника имеет тот же смысл, что и КТУ, то эта сумма образуется сложением пар сомножимых, образованных из обеих величин. В нашем случае это
итого_КТУ=1*1,4+2*1,2+3*1=3,7 или D9=СУММПРОИЗВ(C6:C8;D6:D8).
| A | B | C | D | E | F | G | |||
| Всего: | Аванс: | Остаток: | |||||||
| Надбавки за класс | класс: | ||||||||
| коэфф.: | 1,4 | 1,2 | |||||||
| РАСПРЕДЕЛЕНИЕ ЗАРПЛАТЫ | |||||||||
| Имя | Класс | Коэфф. за класс | КТУ | Получен аванс | Заработано | Выдать остаток | |||
| Петр | 1,4 | ||||||||
| Иван | 1,2 | 1,5 | |||||||
| Олег | 0,5 | –849 | |||||||
| ИТОГО | 3,7 |
|
| C | D | E | F | G | |||
| Коэфф. за класс | КТУ | Получен аванс | Зара ботано | Выдать остаток | |||
| =ГПР(B6;C$2:E$3;2;0) | =B$1/D$9*C6*D6 | =F6–E6 | |||||
| =ГПР(B7;C$2:E$3;2;0) | 1,5 | =B$1/D$9*C7*D7 | =F7–E7 | ||||
| =ГПР(B8;C$2:E$3;2;0) | 0,5 | =B$1/D$9*C8*D8 | =F8–E8 | ||||
| =СУММПРОИЗВ (C6:C8;D6:D8) | =СУММ(E6:E8) | =СУММ(F6:F8) |
|
Теперь можно определить причитающуюся рабочему сумму
заработано=всего*коэфф._за_класс*КТУ/итого_КТУ для первого рабочего это F6=$B$1*C6*D6/D$9.
Поскольку работник уже получил аванс, ему предстоит выдать (а может быть и взыскать с него, если аванс не был “отработан”) сумму выдать_остаток=заработано–получен_аванс или G6=F6–E6.
Правильность наших расчетов подтверждает совпадение значений F9=B1 и G9=F1.
Пример 5.16. Распределение премии.Положим, отделу выделена недельная премия-помощь в размере 800 руб. Ее нужно поделить между сотрудниками следующим образом – малооплачиваемым (считаем таковыми тех, у кого недельный заработок менее пяти минимальных зарплат) делается доплата до 5 минимальных зарплат, а остаток делится между всеми пропорционально зарплате. Сформируем необходимые выражения.
1. Рассчитаем помощь малооплачиваемым. Сначала найдем ее в предположении, что выделенная сумма покрывает необходимые доплаты. Назовем ее максимально возможной помощью
помощь_максимальная=если(зарплата<5*мин._зарплата, то 5*мин._зарплата – зарплата, иначе 0).
| A | B | C | D | E | |
| Премия: | Мин.зарп: | ||||
| РАСПРЕДЕЛЕНИЕ ПРЕМИИ | |||||
| Имя | Зарп- лата | Помощь | Итого | ||
| максим. | фактич. | ||||
| Петр | |||||
| Ольга | |||||
| Иван | |||||
| Олег | |||||
| Всего | |||||
| Рис. | 5.16а |
Она будет таковой, если общая сумма помощи достаточна. Если денег выделено меньше, то помощь уменьшается до выделенного значения и делится пропорционально планируемым максимальным суммам помощи
помощь_фактическая=если(
общая_максимальная_помощь < премии,
то помощь_максимальная, иначе помощь_
максимальная*премия/общая_максимальная_помощь).
2. Остаток премии (если есть) делится уже между всеми работниками (включая и малооплачиваемых) пропорционально их зарплате
итого=если(общая_фактическая_помощь<премии, то
(помощь – общая_фактическая_помощь)/
общая_зарплата*зарплата) + помощь_фактическая.
Правильность расчетов подтверждает то, что B1=E9.
| A | B | C | D |
| ||||||||||||||||||||||||||||||
| Премия: | Мин.зарп: | |||||||||||||||||||||||||||||||||
| РАСПРЕДЕЛЕНИЕ ПРЕМИИ | ||||||||||||||||||||||||||||||||||
| Имя | Зарплата | Помощь | Итого | |||||||||||||||||||||||||||||||
| максим. | фактич. | |||||||||||||||||||||||||||||||||
| Петр | =ЕСЛИ(B5<5* E$1;5*E$1–B5;0) | =ЕСЛИ(C$9<=B$1; C5;C5*B$1/$C$9) | =ЕСЛИ(D$9<B$1; (B$1–D$9)/B$9*B5)+D5 | |||||||||||||||||||||||||||||||
| . . . | ||||||||||||||||||||||||||||||||||
| Олег | =ЕСЛИ(B8<5* E$1;5*E$1–B8;0) | =ЕСЛИ(C$9<=B$1; C8;C8*B$1/C$9) | =ЕСЛИ(D$9<B$1; (B$1–D$9)/B$9*B8)+D8 | |||||||||||||||||||||||||||||||
| Всего | =СУММ(B5:B8) | =СУММ(C5:C8) | =СУММ(D5:D8) | =СУММ(E5:E8) |
Рис. 5.16б
| A | B | C | D | E | F | |
| Сегодня: | 13.янв | |||||
| Товар закуплен | Цена закупки | Дата и курс конвертиров. | Цена закупки | Цена сегодня | ||
| 04.янв | 100$ | 04.янв | 21,1р | 2 110р | 2 280р | |
| 06.янв | 50$ | 06.янв | 22,4р | 1 120р | 1 140р | |
| 09.янв | 80$ | 08.янв | 22,3р | 1 784р | 1 824р | |
| 10.янв | 30$ | 08.янв | 22,3р | 669р | 684р | |
| 11.янв | 70$ | 11.янв | 22,6р | 1 582р | 1 596р | |
| 12.янв | 100$ | 12.янв | 22,8р | 2 280р | 2 280р | |
| Рис. 5.17б. Лист Товар |
| B | C | D | E | F | |
| =СЕГОДНЯ() | |||||
| Цена закупки | Дата и курс конвертирования | Цена закупки | Цена сегодня | ||
| 100$ | =ВПР(A3;Курс! A$2:B$240;1;1) | =ВПР(A3;Курс! A$2:B$240;2;1) | =D3*B3 | =ВПР(B$1;Курс! A$2:B$240;2;1)*B3 | |
| 50$ | =ВПР(A4;Курс! A$2:B$240;1;1) | =ВПР(A4;Курс! A$2:B$240;2;1) | =D4*B4 | =ВПР(B$1;Курс! A$2:B$240;2;1)*B4 | |
| 80$ | =ВПР(A5;Курс! A$2:B$240;1;1) | =ВПР(A5;Курс! A$2:B$240;2;1) | =D5*B5 | =ВПР(B$1;Курс! A$2:B$240;2;1)*B5 | |
| Рис. 5.17в. | Лист Товар |
Пример 5.17. Конвертирование валюты.Создать таблицу конвертирования цены товара. При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а расчет с внутренними покупателями – в рублях. Ввиду этого нужно иметь возможность конвертирования в рубли исходной (в момент поступления) и текущей (“на сегодня”) цены товара. Вся необходимая информация хранится в двух таблицах: таблице курса доллара и таблице расчета рублевого эквивалента товара. Для удобства пользователя этот материал мы разнесем на два листа рабочей книги – лист Курси лист Товар. Содержимое первого (на рисунке изображен только фрагмент листа) очевидно, – это последовательные значения дат и стоимости доллара.
На листе Товарв В1 предъявляется текущая дата (функция СЕГОДНЯ()), а также содержатся сведения о дате закупки товара и его закупочной цене (столбцы А и В). В столбце Е вычисляется рублевый эквивалент этой цены, но прежде формируются два столбца, нужные только для сведения оператора. В столбце С предъявляется ближайшая, найденная в листе Курс, дата (С3=ВПР(A3;Курс!A$2:B$240;1;1)), по курсу которой (столбец D) и производится конвертирование долларов в рубли. Делается это ради того, что бы пользователь мог контролировать правильность конвертирования. Так, например, если обнаруживается что дата конвертирования сильно отстает от даты закупки, можно предположить что курсовая таблица содержит не все данные и ее следует просмотреть и, возможно, дополнить. В столбце D показывается найденный курс доллара, по которому будет осуществляться конвертирование цены товара D3=ВПР(A3;Курс!A$2:B$240; 2;1). На основании его и находится закупочная цена товара в рублях E3=D3*B3. В столбце F определяется рублевый эквивалент цены товара “на сегодня” F3=ВПР(B$1; Курс!A$2 :B$240;2;1)*B3. В обоих выражениях используется функция ВПР() с четвертым аргументом равным 1, т.е. поиск даты в таблице курса доллара будет не точным, а интервальным, поскольку некоторых дат там нет (валютная биржа не работает в выходные дни – у нас 9 и 10 января) и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты (8 января), для которой она имеется. В качестве области поиска определена область листа Курс, содержащая два столбца А и В и число строк, равное числу рабочих дней в году (около 240).
Пример 5.18. Конвертирование в различные виды валют.Похожий пример, но покупка товара у зарубежных поставщиков может быть осуществлена не только в долларах, но также в немецких марках и в английских фунтах стерлингов, для чего в таблицу курсов валют включены соответствующие столбцы (на рисунке показана только часть листа Курс). В листе Товарвведен столбец Вид валюты, где может быть задан один из символов Д,М,Ф, обозначающих доллар, марку и фунт. В колонку С вводится цена в соответствующей валюте. Рублевая цена товара находится так же, как и ранее, но номер столбца, из которого извлекается курс, зависит от символа валюты, который ищется в строке “ДМФ” с помощью функции ПОИСК(B3; "ДМФ"). Если B3 равно Д, М или Ф, результатом применения функции будет число 1, 2 или 3. Поскольку курсы этих валют находятся соответственно во второй, третьей и четвертой колонках листа Курс, для поиска в курсовой таблице к результату нужно прибавить единицу (ПОИСК(…)+1). Тогда справедливо
D3=ВПР(A3;Курс!A$2:D$241;ПОИСК(СТРОЧН(B3);"ДМФ")+1;1)*C3.
Здесь к клетке D3 применена функция преобразования строчных букв в прописные СТРОЧН(). Это позволит вводить в колонку Вид валюты не только буквы Д,М,Ф, но и д,м,ф. Ввод любого другого символа (как, например, в строке 8) порождает сообщение об ошибке вида #ЗНАЧ!, которое пользователь легко обнаружит. Аналогично находится Цена сегодня.
E3=ВПР($B$1;Курс!A$2:D$241;ПОИСК(СТРОЧН(B3);"ДМФ")+1;1)*C3.
| A | B | C | D | E | |
| Сегодня: | 05.фев | ||||
| Товар закуплен | Вид валюты | Цена закупки | Цена сегодня | ||
| 04.янв | д | 2 110р | 2 280р | ||
| 06.янв | ф | 1 610р | 1 640р | ||
| 09.янв | ф | 2 560р | 2 624р | ||
| 10.янв | д | 669р | 684р | ||
| 11.янв | м | 1 267р | 1 274р | ||
| 12.янв | х | #ЗНАЧ! | #ЗНАЧ! | ||
| Рис. 5.18б. Лист Товар |
К оформлению. Существует способ гарантированно правильного ввода стабильных данных – это использование механизма Проверка из меню Данные. Условие проверки должно быть таким, как показано на рис. 5.18в (источником данных является список с буквами Д, Ф, М). А если установить в окне Проверка флаг Список допустимых значений, то возможен не только ввод “руками”, но выбор данных из списка-меню.
Пример 5.19. Турнирная таблица.Создать таблицу определения призеров по результатам чемпионата. Положим, в играх участвуют три команды. Для каждой игры фиксируется ее счет – число забитых и пропущенных голов (колонки “з” и “п”). В колонке “о” вычисляется число очков, полученных по результатам игры. Считается, что проигрыш приносит 0 очков, ничья – одно, выигрыш – два. Если игры еще не было, клетка остается пустой ("").
Формулы для вычисления очков по игре приведены ниже
D4=ЕСЛИ(B4>C4;2;ЕСЛИ(ЕПУСТО(B4);"";ЕСЛИ(B4=C4;1;0))),
G4=ЕСЛИ(E4>F4;2;ЕСЛИ(ЕПУСТО(E4);"";ЕСЛИ(E4=F4;1;0))),
J4=ЕСЛИ(H4>I4;2;ЕСЛИ(ЕПУСТО(H4);"";ЕСЛИ(H4=I4;1;0))),
M4=ЕСЛИ(K4>L4;2;ЕСЛИ(ЕПУСТО(K4);"";ЕСЛИ(K4=L4;1;0))).
По итогам чемпионата формируются сводные данные:
1. Число сыгранных игр. Здесь подсчитывается количество всех непустых клеток в диапазоне, где фиксируются игры, и делится на три (три клетки занимаются под каждую игру). Из числа непустых ячеек вычитается единица, чтобы не учитывалась заштрихованная клетка, в которой находится формула вычисления очков, находящаяся на пересечении строки и столбца для одной и той же команды (например, D4).
| A | B | C |
| E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| Спартак | Динамо | ЦСКА | Фили | Игр | Очков | Забито | Пропущено | Разность | Очки + разность | МЕСТО | ||||||||||||
| Команды | з | п | о | з | п | о | з | п | о | з | п | о | ||||||||||
| Спартак | –2 | 1,098 | ||||||||||||||||||||
| Динамо | 5,103 | |||||||||||||||||||||
| ЦСКА | 2,100 | |||||||||||||||||||||
| Фили | –1 | 2,099 | ||||||||||||||||||||
| Рис. 5.19 |
2. Число всех добытых командой очков в чемпионате. Подсчитывается сумма содержимого клеток, находящихся в столбцах, где в строке В3:M3 буква “о” (очки).
3.4. Аналогично подсчитывается число забитых и пропущенных голов.
5. Разность забитых и пропущенных голов.
6. Число очков с учетом соотношения забитых и пропущенных голов. Может случиться так, что некоторые команды будут иметь одинаковое число очков по результату чемпионата (у нас ЦСКА и Фили имеют по 2 очка). В этом случае место в чемпионате определяется по лучшей (большей) разности забитых и пропущенных голов. Чтобы учесть ее влияние, к сумме очков прибавляется некоторая величина, являющаяся функцией разности. Она должна быть такой, чтобы в итоге целая часть суммы очков не изменилась (малое положительное число). Для этого к разности очков сначала прибавляется 100 (чтобы избежать возможности уменьшения числа очков, если разность отрицательна), затем результат делится на 1000 (чтобы избежать изменения целой части результата) и все это прибавляется к сумме добытых командой очков. Таким образом, ЦСКА и Фили будут иметь уже не по 2 очка, а 2,1 и 2,099 очков соответственно, что отражает лучшее положение команды ЦСКА в турнире. В дальнейшем этот столбец, служащий для технических целей, может быть скрыт от пользователя, как, кстати, и столбцы “о”.
7. Занятое место в чемпионате определяется функцией РАНГ().
Соответствующие формулы для первой команды приведены ниже
Игрсыграно:N4=(СЧЁТЗ(B4:M4)–1)/3, Очки+Разность (голов): S4=O4+(R4+100)/1000,
Очков команды:O4=СУММЕСЛИ(B$3:$M$3;"=о";B4:M4), Место (в турнире): T4=РАНГ(S4;S$4:S$7),
Забито голов:P4=СУММЕСЛИ(B$3:$M$3;"=з";B4:M4), Разность забитых и пропущенных голов: R4=P4–Q4,
Пропущено голов: Q4=СУММЕСЛИ(B$3:$M$3;"=п";B4:M4).
| A | B | C | D | E | F | |
| Сумма: | ||||||
| Налог: | 0% | 12% | 15% | 20% | 30% | |
| Мин.зарп. | ||||||
| РАСЧЕТ НАЛОГОВ | ||||||
| Работник | Детей | Доход | Сумма обложения | Налог | Сумма на руки | |
| Петр | 0,0 | 1000,0 | ||||
| Иван | 3576,0 | 8344,0 | ||||
| Олег | 726,0 | 4114,0 | ||||
| ВСЕГО | 5400,0 | 13458,0 | ||||
| Рис. 5.20а |
Пример 5.20. Расчет зарплаты и налогов.Построить таблицу вычисления зарплаты с учетом увеличивающегося подоходного налога и числа детей. Положим, с месячного дохода до 2000 налог не удерживается, от 2000 удерживается в сумме 12%, от 4000 – 15%, от 6000 – 20%, от 10000 – 30%. Налог удерживается не со всей суммы заработка, а с величины, меньшей на одну минимальную зарплату, на каждого ребенка (иждивенца).