Задания_excel
.doc
Задание. Выполнить вариант задания.
-
Таблицу создать для 10-12 записей.
-
В формулах правильно расставить виды адресов.
-
Установить защиту на блоки ячеек с формулами.
-
Представить результаты расчета графически, используя деловую графику.
Вариант 1. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость для начисления зарплаты. В формулах использовать логические функции.
Исходные данные (первые столбцы таблицы): Фамилия, Имя, Должность, Тарифный коэффициент, Стаж работы.
Расчетные данные (столбцы таблицы с формулами): Начислено, Надбавки, Налог, К выдаче (На руки), Подпись.
Правила расчета:
-
Тарифный коэффициент зависит от должности и находится в пределах от 1 до5.
-
Известна зарплата для тарифного коэффициента 1(например, 1000руб.).
-
Надбавка начисляется за стаж работы по правилу:
-
при стаже до года надбавки нет,
-
при стаже от 1 года до 3 лет – 10%,
-
при стаже свыше 3 лет – 20%,
-
-
Величина налога 13%
Вариант 2. Создать в EXCEL для бригады строителей (10-12 чел.) ведомость для начисления зарплаты. В формулах использовать логические функции.
Исходные данные (первые столбцы таблицы): Фамилия, Имя, Рабочие дни, Сверхурочные часы.
Расчетные данные (столбцы таблицы с формулами): Начислено, Налог, К выдаче (На руки), Подпись.
Правила расчета:
-
Известна ставка рабочего дня и цена сверхурочного часа (Ваши цифры),
-
Ставку налога принять 12% и 20% в зависимости от заработанной суммы (Ваше условие).
Вариант 3. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость для начисления зарплаты.
Исходные данные (первые столбцы таблицы): Фамилия, Имя, Должность, Тарифный коэффициент, % удержаний.
Расчетные данные (столбцы таблицы с формулами): Начислено, Удержано, Налог, К выдаче (На руки), Подпись.
Правила расчета:
-
Тарифный коэффициент зависит от должности и находится в пределах от 1 до10.
-
Известна зарплата для тарифного коэффициента 1(например, 1000руб.).
-
Величина налога 13%
Предусмотреть итог по всем видам начислений.
Вариант 4. Создать в EXCEL ведомость для начисления зарплаты коллектива розничных торговцев (10-12 чел.) В формулах использовать логические функции.
Исходные данные (первые столбцы таблицы): Фамилия, Имя, Выручка, Стаж работы.
Расчетные данные (столбцы таблицы с формулами): Начислено, Надбавки, Налог, К выдаче (На руки), Подпись.
Правила расчета:
Начисленная сумма равна 10% от выручки. Величина налога 13%. Надбавка начисляется за стаж работы по правилу:
-
при стаже до 2 месяцев надбавки нет,
-
при стаже от 2месяцев до1 года– 10% ,
-
при стаже свыше 1 года – 20%,
Вариант 5. Создать в EXCEL таблицу расчета квартплаты 12-ти квартирного жилого дома.
Исходные справочные данные (отдельная таблица) Цена 1кв.м.площади, тариф за холодную воду, тариф за горячую воду.
Исходные данные (первые столбцы таблицы): Номер квартиры, Кол-во жильцов, Площадь.
Расчетные данные (столбцы таблицы с формулами):, Квартплата за жилье, Начислено за горячую воду, Начислено за холодную воду, Всего к оплате.
Предусмотреть итог по всем видам начислений.
Вариант 6. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость выплат зарплаты с учетом удержаний алиментов. В формулах использовать логические функции.
Исходные данные (первые столбцы таблицы): Фамилия, Имя, Начислено, Количество детей.
Расчетные данные (столбцы таблицы с формулами): Удержано, Налог, К выдаче (На руки), Подпись.
Правила расчета:
Величина налога 12%. Удержания производятся по правилу:
-
при наличии одного ребенка – 25%,
-
при наличии двух детей – 33%,
-
при наличии трех детей – 50%,
Вариант 7. Создать в EXCEL таблицу расчета части квартплаты многоквартирного жилого дома. В формулах использовать логические функции.
Исходные справочные данные (отдельная таблица) Цена 1кв.м. площади с учетом этажности, тариф за газ.
Исходные данные (первые столбцы таблицы): Номер квартиры, Кол-во жильцов, Площадь, Этаж.
Расчетные данные (столбцы таблицы с формулами): Квартплата за жилье, Начислено за газ, Всего к оплате.
Предусмотреть итог по всем видам начислений.
Вариант 8. Создать в EXCEL таблицу расчета повременной оплаты телефона. В формулах использовать логические функции.
Исходные справочные данные (отдельная таблица) Тарифы с учетом типов льгот.(Ваши варианты).
Исходные данные (первые столбцы таблицы): Номер телефона, Фамилия, Тип льготы, Продолжительность разговоров в часах.
Расчетные данные (столбцы таблицы с формулами): Начислено, Льгота, К оплате.
Предусмотреть итог по всем видам начислений.
Вариант 9. Создать в EXCEL ведомость оплаты за отгруженный оптовикам товар (10-12 операций). В формулах использовать логические функции.
Исходные данные (первые столбцы таблицы): Дата, Покупатель, Количество товара (ед.изм.), Цена за ед.изм..
Расчетные данные (столбцы таблицы с формулами): Стоимость, Оптовая скидка, К оплате, Получено, Подпись.
-
При покупке товара от10 упаковок до 20 упаковок скидка – 2%,
-
При покупке товара от21 упаковок до 40 упаковок скидка – 6%,
-
При покупке товара свыше 41 упаковки скидка – 10%.
Предусмотреть итог по всем видам начислений.
Вариант 10. Определение коэффициента трудового участия КТУ.
На таком производстве, где невозможно оценить индивидуальное количество и качество труда часто, принято по итогам месяца или недели оценивать их при помощи КТУ, которое в дальнейшем влияет на оплату. Чтобы исключить необъективность оценок, все члены бригады анонимно заполняют анкету, где выставляют КТУ (в диапазоне от 0 до 1) для каждого из своих коллег. Необходимо как-то обработать эти данные и сформировать обобщенные значения КТУ. В таблице в столбцах В:Е зафиксированы оценки для каждого из четырех членов бригады, в F они просуммированы, в G вычисляется собственно КТУ по формуле:
<КТУ работника>=<Всего КТУ работника>/<Сумма КТУ>.
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
ФИО работника |
Оценки |
Всего КТУ работника |
КТУ работника |
Результирующее КТУ |
|||
2 |
Петров |
0,9 |
0,7 |
0,7 |
0,9 |
3,2 |
0,277 |
0,98 |
3 |
Ковалев |
0,6 |
0,5 |
0,6 |
0,4 |
2,15 |
0,186 |
0,66 |
4 |
Кузнецов |
0,8 |
0,8 |
0,5 |
0,8 |
2,95 |
0,255 |
0,91 |
5 |
Никитин |
0,9 |
0,9 |
0,4 |
1,0 |
3,25 |
0,281 |
1,00 |
6 |
Сумма КТУ |
11,55 |
|
|
Эти значения (в таблице столбец G) уже можно использовать для оценки результатов труда, однако более удобно, если они находятся в том же диапазоне, что и исходные оценки, т.е. в диапазоне от 0 до 1 (столбец Н).
<Результирующее КТУ>=<КТУ работника>/МАКС(КТУ работников).
Рабочие формулы для первого работника приведены ниже:
F2=СУММ(В2:Е2), F6=СУММ(F2:F5), G2=F2/$F$6, H2=G2/МАКС(G2:G5).
Вариант 11. Расчет сдельной зарплаты: ЕСЛИ().
Построить таблицу вычисления сдельной (т.е. пропорциональной количеству произведенной продукции) зарплаты, а также налога и суммы на руки. Зарплата работника определяется числом обработанных деталей, умноженным на стоимость обработки. Если работник допустил брак и испортил деталь, ее стоимость вычитается из заработка.
<зарплата>=<обработано деталей>*<стоимость работы>–<деталей брака>*<стоимость детали>.
Таким образом, Зарплата первого работника будет определяться формулой: D6=B6*C2-C6*C3. Полагаем, что налог исчисляется в зависимости от зарплаты: если она выше 20000 – налог составляет 20%, если нет – 13%. Иными словами: <сумма налога>=<зарплата>*ЕСЛИ(<зарплата> больше 20000, то 13%, иначе 20%).
|
A |
B |
C |
D |
E |
F |
1 |
Налог до 20000р. |
13% |
Налог после 20000р. |
20% |
||
2 |
Стоимость работы |
1000 |
|
|
|
|
3 |
Стоимость детали |
200 |
|
|
|
|
4 |
Расчет зарплаты |
|||||
5 |
ФИО работника |
Обработано деталей |
Деталей брака |
Зарплата |
Сумма налога |
Сумма на руки |
6 |
Иванов |
75 |
2 |
|
|
|
7 |
Петров |
12 |
|
|
|
|
8 |
Кузнецов |
62 |
4 |
|
|
|
9 |
Всего |
|
|
|
|
|
Может случиться, что если брака окажется много, зарплата станет отрицательной (работник должен компенсировать нанесенный ущерб). В этом случае, конечно, никакой налог не удерживается и, следовательно, в формуле для налога следует предусмотреть выявление отрицательности заработка. С учетом вышесказанного уточним значение Е6:
Е6=D6*ЕСЛИ(D6<=0;0;ЕСЛИ(D6>=20000;F1;С1)).
Для удобства последующего анализа данных сделаем так, чтобы работник, имеющий максимальный заработок, был выделен красным цветом, а минимальный – желтым. Для этого понадобится прибегнуть к условному форматированию ячеек F6, F7, F8 вида:
Условие 1:
[значение] =МАКС(F6:F8) – красный фон;
Условие 2:
[значение] =MИH(F6:F8) – желтый фон.
Представить результаты расчета графически, используя деловую графику, (2-3 типа, круговую диаграмму обязательно).
Вариант 12. Повременная зарплата: ГПР().
Построить таблицу вычисления повременной (т.е. зависящей от длительности рабочего времени) заработной платы работников на некотором производстве. Последняя зависит от числа отработанных дней в месяце, разряда рабочего и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда
<зарплата>=<дней>*<тариф по разряду>.
|
A |
B |
C |
D |
E |
F |
1 |
Тарифная сетка |
|||||
2 |
Разряд |
1 |
2 |
3 |
4 |
5 |
3 |
Тариф |
300 |
400 |
500 |
700 |
900 |
4 |
Премия |
1000 |
1200 |
1350 |
1500 |
1600 |
5 |
ЗАРПЛАТА |
|||||
6 |
Работник |
Дни |
Разряд |
Зарплата |
Премия |
Начислено |
7 |
Иванов |
20 |
2 |
|
|
|
8 |
Петров |
10 |
3 |
|
|
|
9 |
Сидоров |
15 |
5 |
|
|
|
10 |
ВСЕГО |
45 |
|
|
|
|
Для розыска разрядного тарифа нам понадобится функция горизонтального поиска ГПР(). Например, для Иванова должна быть использована формула D7=ГПР(С7;В2:F4;2;0)*В7. Премия также зависит от разряда Е7=ГПР(С7;В2:F4;3;0). Всего работнику будет начислена сумма F7=E7+D7.
Вариант 13. Расчет зарплаты: СУММПРОИЗВ(), ГПР().
Расчет зарплаты с учетом квалификации работника (должностного класса) и коэффициента (КТУ) трудового участия. Здесь класс и КТУ одинаковым образом влияют на заработок, но только класс присваивается "навсегда" (до пересмотра), а КТУ изменяется в зависимости от качества исполнения конкретной работы.
Положим, заказчик оценил всю работу в 10000 руб. и выдал аванс в размере 4000 руб., который и был распределен между людьми произвольным образом (кто сколько попросил). Наша задача состоит в том, чтобы по завершении работы распределить остальную часть заработанного (6000 руб.).
Прежде всего необходимо определить влияние класса и КТУ на размеры выплат. Найдем сумму этих величин. Поскольку коэффициент доплат за класс работника имеет тот же смысл, что и КТУ, то эта сумма образуется сложением пар сомножимых, образованных из обеих величин. В нашем случае это
<итогоКТУ>=1*1,4+2*1,2+3*1=3,7 или D9=СУММПРОИЗВ(С6:С8;D6:D8).
|
A |
B |
C |
D |
E |
F |
G |
1 |
Всего |
10000 |
Аванс |
4000 |
Остаток |
6000 |
|
2 |
Надбавки за класс: |
Класс |
1 |
2 |
3 |
|
|
3 |
Коэфф. |
1,4 |
1,2 |
1 |
|
|
|
4 |
Распределение заработанной платы |
||||||
5 |
ФИО |
Класс |
Коэфф.за класс |
КТУ |
Получен аванс |
Заработано |
Выдать остаток |
6 |
Петров |
1 |
1,4 |
1 |
1000 |
3784 |
2784 |
7 |
Иванов |
2 |
1,2 |
1,5 |
800 |
486 |
4065 |
8 |
Кузнецов |
3 |
1 |
0,5 |
2200 |
1351 |
-849 |
9 |
Итого |
|
|
3,7 |
4000 |
10000 |
6000 |
Теперь можно определить причитающуюся рабочему сумму:
<заработано>=<всего>/<итого КТУ>*<коэфф. за класс>*<КТУ>
для первого рабочего это F6=$B$1/$D$9*C6*D6.
|
C |
D |
E |
F |
G |
10 |
Коэффициент за класс |
КТУ |
Получен аванс |
Заработано |
Остаток |
11 |
=ГПР(B6;C2:E3;2;0) |
1 |
100 |
=B1/D9*C6*D6 |
=F6-E6 |
12 |
|
|
|
|
|
13 |
|
|
|
|
|
14 |
|
|
|
|
|
15 |
|
=СУММПРОИЗВ (C6:C8;D6:D8) |
=СУММ (Е6:Е8) |
=СУММ (F6:F8) |
=СУММ (G6:G8) |
Поскольку работник уже получил аванс, ему предстоит выдать (а может быть и взыскать с него, если аванс не был "отработан") сумму
<остаток>=<заработано>–<получен аванс>
или G6=F6-E6.
Правильность наших расчетов подтверждает совпадение значений F9=B1 и G9=F1.
Вариант 14. Распределение премии: СУММ(),ЕСЛИ().
Положим, отделу выделена недельная премия-помощь в размере 8000 руб. Ее нужно поделить между сотрудниками следующим образом – малооплачиваемым (считаем таковыми тех, у кого недельный заработок менее пяти минимальных зарплат) делается доплата до 5 минимальных зарплат, а остаток делится между всеми пропорционально зарплате. Сформируем необходимые выражения.
-
Рассчитаем помощь малооплачиваемым. Сначала найдем ее в предположении, что выделенная сумма покрывает необходимые доплаты. Назовем ее максимально возможной помощью.
Помощь максимальная рассчитывается по следующему алгоритму:
Если зарплата < 5*мин. зарплата
5*мин. зарплата – зарплата
Иначе
0
Все_Если
|
A |
B |
C |
D |
E |
1 |
Премия |
8000 |
|
Мин.зарплата |
1000 |
2 |
РАСПРЕДЕЛЕНИЕ ПРЕМИИ |
||||
3 |
ФИО |
Зарплата |
Помощь |
Итого |
|
4 |
Максим. |
Фактич. |
|||
5 |
Петров |
4000 |
1000 |
1000 |
1200 |
6 |
Иванов |
12000 |
0 |
0 |
600 |
7 |
Кузнецов |
3000 |
2000 |
2000 |
2150 |
8 |
Орлов |
1000 |
4000 |
4000 |
4050 |
9 |
Всего |
20000 |
7000 |
7000 |
8000 |
|
A |
B |
C |
D |
E |
1 |
Премия |
8000 |
|
Мин.зарплата |
1000 |
2 |
РАСПРЕДЕЛЕНИЕ ПРЕМИИ |
||||
3 |
ФИО |
Зарплата |
Помощь |
Итого |
|
4 |
Максим. |
Фактич. |
|||
5 |
Петров |
4000 |
=ЕСЛИ(B5<5*E1;5*E1–B5;0) |
=ЕСЛИ(С9<=В1; С5; С5*B1/C9) |
=ЕСЛИ(D9<В1; (B1-D9)/ B9*B5)+D5 |
6 |
Иванов |
|
|
|
|
7 |
Кузнецов |
|
|
|
|
8 |
Орлов |
|
|
|
|
9 |
Всего |
=СУММ(B5:B8) |
=СУММ(C5:C8) |
=СУММ(D5:D8) |
=СУММ(E5:E8) |
Она будет таковой, если общая сумма помощи достаточна. Если денег выделено меньше, то помощь уменьшается до выделенного значения и делится пропорционально планируемым максимальным суммам помощи.