Тема 4. Должностные оклады и премии.

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

Определение количества сотрудников в каждом отделе и занимающих определенные должности И0В

ЕЗ Microsoft Excel - База данных •л

И] Файл Правка Вид Вставка Формат Сервис

л л я ^ dia ai^ai а

Данные Окно Справка

J -1 Ч, z - Ы Si I ш ¦

Arial Суг

-1 ж а- ч \шЩ\т ии i g % ооо ^ & \т т\ в - - А.в А1 fx Отдел А В С D Е F G Н I 1 Отдел Должность Фамилия Табельный номер Оклад Отдел Численность 2 Реапшации Менеджер Иванов 0123 1 050,00 р. Контроля 2 3 Реапшации Менеджер Петров 1234 700,00 р. Реализации 5 4 Реапшации Начальник Иваненко 2345 1 400,00 р Снабжения 3 5 Контроля Начальник Сидорова 3456 000,00 р. Итого 10 6 Снабжения Начальник Петренко 4567 700,00 р. 7 Снабжения Инженер Сидоренко 5678 600,00 р. Должность Численность 8 Контроля Аудитор Карпова 6789 310,ООр Аудитор 1 9 Реапшации Секретарь Лещова 7890 910,00р. Инженер 1 10 Снабжения Экспедитор Щукина 8901 600,00 р. Менеджер 3 11 Реапшации Менеджер Вьюнов 9012 400,00 р. Начальник 3 12 Итого 7 470,00р. Секретарь 1 13 Экспедитор 1 14 Итого 10 15 16 Всего должностных единиц 10| 17 И <4 > н[\ Количество сотрудников Сотрудники / l< пп I > I Рисунок 30 В файле База данных.хЫ добавьте новый лист, назовите его Количество сотрудников.

Скопируйте на него с листа Сотрудники столбцы Отдел, Должность, Фамилия, Табельный номер.

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

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

В ячейки Н2:Н4 поместите формулы для определения количества сотрудников в отделах, например для ячейки Н2 формула будет иметь вид =СЧЕТЕСЛИ($А$2:$А$11;А5) или =СЧЕТЕСЛИ($А$2:$А$11;"Контроля")

В ячейки Н8:Н13 поместите формулы для определения количества сотрудников, занимающих определенные должности, например для ячейки Н8 формула будет иметь вид

=СЧЕТЕСЛИ($В$2:$В$11;В8) или =СЧЕТЕСЛИ($В$2:$В$11;" Аудитор")

В ячейках Н5 и Н14 просуммируйте количество сотрудников по отделам и по должностям.

Выполните проверку рассчитываемых значений:

Результаты сложных и наиболее важных расчетов всегда нужно проверять на правильность.

Для нашей задачи возможен следующий метод контроля: если в списке работников нет ошибки, то значения в столбце оклады должны быть больше нуля, для этого в ячейку Н16 занесем формулу =СЧЕТЕСЛИ($Е$2:$Е$11;">0")

Если расчеты производились правильно, то численность сотрудников по должностям и отделам должна совпадать.

Изменение должностных окладов.

Предположим, финансовые возможности предприятия позволяют увеличить штатные оклады сотрудников на 7,7%. Рассчитаем новые ставки, воспользовавшись несколькими методами. При этом необходимо учитывать, что размер оклада должен выражаться целым числом, то есть не содержать копеек.

Скопируйте лист Количество сотрудников и переименуйте его в Оклады.

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

Скопируйте лист Количество сотрудников и переименуйте его в Оклады.

В ячейку F1 введите заголовок Новый оклад (Специальная вставка), в диапазон F2:F11 скопируйте значения старых окладов.

В ячейку С14 введите значение индекса увеличения оклада (1,077).

Скопируйте содержимое данной ячейки.

Выделите диапазон F2:F11, содержащий оклады.

Из контекстного меню выберите Специальная вставка.

В области Вставить появившегося окна активизируйте переключатель Значения, в области Операция - переключатель Умножить, Ок.

В результате все числа, указанные в ячейках F2:F11, будут умножены на значение 1,077, введенное в ячейку С14. Но при использовании данного метода значения увеличенных окладов выражены в рублях с копейками.

Применение формул

В ячейку G1 введите заголовок Новый оклад (формула).

Выделите диапазон G2:G11, введите формулу

=ОКРУГЛ (старый оклад* значение индекса увеличения оклада;0) Нажмите Ctrl+Enter.

Использование коэффициентов Размер оклада каждого сотрудника с помощью определенного коэффициента «привязывается» к окладу ведущего специалиста (например, ди-

ректора или начальника отдела).

В ячейку Н1 введите заголовок Новый оклад (коэффициенты), в ячейку I1 - Оклад (расчетный), в ячейку - Коэффициент.

В ячейку I2 занесите старый оклад начальника отдела Реализации - 1400,00 руб., в ячейку I3 - индекс увеличения оклада, в ячейку I4 формулу расчета нового оклада начальника ^КРУГЛ^^ШЗ)^).

Заполните диапазон J2:J11 коэффициентами, используемыми при перерасчете окладов, а в диапазон Н2:Н11 формулами расчета нового оклада сотрудников, например для ячейки Н2 ^КРУГЛ^Ш^^).

Расчет окладов всеми рассмотренными способами с числовыми данными приведен на рисунке:

? Microsoft Excel - База данных Введите вопрос

Файл Правка Вид Вставка Формат Сервис Данные Окно Справка : Arial Суг

Ж л ч щштш gj % ом tig 18 » ?• НЗ

f, =ОКРУГЛ(Щ1ИЧЗ;Р) D Табельный номер

Новый оклад (формула)

Новый оклад (коэффициенты)

Оклад (расчетный)

Оклад (старый)

Коэффициент

Отдел

Должность

Новый оклад (специальная вставка) Менеджер

1 050,ООр

1 131,ООр

1 131.ООр

I 400,ООр

1 1 30.85р. Менеджер

Петров

700,ООр

753,90р

754,ООр

1 010,ООр

Реализации Начальник

1 507,80р

1 508,ООр

1 508,ООр

1 508,ООр

1 400,ООр 800,ООр

861,60р

862,ООр

860,ООр

Контроля Начальник

Петренко

700,ООр

753,90р

754,ООр

754,ООр

0,50 0,43 0,22 0,65 0,66 0,44

Снабжения

Инженер

Сидоренко

600,ООр

646,20р

646,ООр

648,ООр

Снабжения

Аудитор

310,ООр

333,87р

334,ООр

332,ООр

Контроля

Лещоьа

910,ООр

980,07р.

980,ООр

980,ООр

Секретарь

Снабжения

Щукина

600,ООр

646,20р

646,ООр

995,ООр

Экспедитор

400,ООр

430,80р

431,ООр

664,ООр

Реализации Менеджер 7 470,ООр 14

Индекс увеличения оклада| 1,0771 > I

н < > н \ Количество сотрудников / сотрудники Доклады/ Готово Рисунок 31

Проверка данных

Обратите внимание на лист Сотрудники: в строке 10 указан сотрудник, который уже уволился, но ему начисляется заработная плата.

Вставьте новый лист, который назовите Проверка данных. На новом листе разместите:

столбцы с листа Количество сотрудников: Отдел, Должность, Фамилия, Табельный номер, Оклад;

столбцы с листа Сотрудники: Табельный номер, Фамилия, Отдел, Дата приема на работу, Дата увольнения.

Внесите ошибки в табельные номера.

Если работник уволен Формула, с помощью которой можно определить, числился ли сотрудник в списке работников на момент расчета премии, основана на функции ЕПУСТО, относящийся к категории Проверки свойств и значений.

ЕПУСТО(значение) - функция проверяет содержимое ячейки и, если ячейка ничего не содержит, возвращает логическое значение ИСТИНА, если в ячейке находится какая-либо информация, функция возвращает значение ЛОЖЬ.

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

Сравнение табельных номеров.

Воспользуемся функцией ЕСЛИ: =ЕСЛИ(Б2=а2;ИСТИНА;ЛОЖЬ)

Сравнение фамилий

У нас в одном столбце указана лишь фамилия, а в другом фамилия и инициалы. Поэтому воспользуемся текстовыми функциями:

сосчитаем количество символов в ячейке С2 (фамилия и инициалы) до первого пробела;

извлечь из ячейки С2 количество символов, расположенных слева от первого пробела.

Для определения символов, предшествующих первому пробелу, воспользуемся функцией НАЙТИ.

=НАЙТИ(" ";С2) - в ячейке С2 занесена фамилия с инициалами.

Далее применим функцию ЛЕВСИМВ: ЛЕВСИМВ(С2;НАЙТИ(" ";C2)-1)- получим фамилию без инициалов. Отнимается 1, т.к. функция НАЙТИ определяет положение пробела, следующего после фамилии.

Осталось сравнить фамилии, в итоге получится формула: =ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ(" ";С2)-1);ИСТИНА;ЛОЖЬ)

Соответствие всем условиям

Для проверки выполнения всех трех условий: сотрудник не уволен, совпадения табельных номеров и совпадения фамилий, воспользуемся функцией И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ, получим формулу: =H(L2;M2;N2) Результаты представлены на рисунке 32:

? Microsoft Excel - База данных

: Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос Ў _ fi> X

; J _; d л j I ^ ДI у a I а ^ J -1 ' I & s - йП11 & -о - т |

^ Arial Cyr J..9 -|ЖГ I jgl ^ | ¦Д % ИИ I iW W | Щ - & - А . |

Н1 т fx фамилия А В С D Е F G Н J К L М N О 1 Отдел Должность Фамилия Та б ел ьный номер Оклад Табель ный номер Фамилия Отдел Дата приема на работу Дата увольнения Проверка на увольнение Сравнение табельных номеров Сравнение фамилий Соответствие всем условиям 2 Реапшации Менеджер Иванов И.А. 0123 1 050,00р. 0123 Иванов Реализации 01.02.1995 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 3 Реализации Менеджер Пегроь Т.Р 1234 700,00р. 1233 Петроь Реализации 02.03.1996 ИСТИНА ЛОЖЬ ИСТИНА ЛОЖЬ 4 Реализации Начальник Иваненко А.О. 2345 1 400,00р. 2345 Иваненко Реализации 04.05.1997 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 5 Контроля Начальник Сидорова К Г 3456 В00,00р. 3456 Сидорова Контроля 03.04.1993 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 6 Снабжения Начальник Петренко P C. 4567 700,00р. 4567 Петренко Снабжения 05.06.1998 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 7 Снабжения Инженер Сидоренко С.Ю. 567! Б00,00р. 567! Сидоренко Снабжения 06.07.1992 ИСТИНА ИСТИНА ИСТИНА ИСТИНА В Контроля Аудитор Карпова Т.Н. 67S9 310,00р. 6789 Карпов Контроля 20.09.1999 ИСТИНА ИСТИНА ЛОЖЬ ЛОЖЬ — 9 Реализации Секретарь ЛещоваО.Н. 7S90 910,00р. 7890 Лешова Реализации 17.10.1996 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 10 Снабжения Экспедитор Щукина Р.И. 8901 В00,00р. 8901 Щукина Снабжения 19.12.1997 29.09.2001 ЛОЖЬ ИСТИНА ИСТИНА ЛОЖЬ 11 Реализации Менеджер Вьюнов О.Ю. 9012 400,00р. 9012 Вьюнов Реализации 18.11.1997 ИСТИНА ИСТИНА ИСТИНА ИСТИНА 12 1 -! Н > м \Проверка данных/ 1< ¦I > 1 Готово ЛЯ Ш Microsoft Excel - Безе донных Введите вопрос

^ 1 Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

J jd^JI^AI^aut -У а - - р ч ^ s - an; I iai -о - т | АЛ

Arial Суг

=JL

• " ;

% ООО

Ж Z Ч I = = = =ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАИТИ(" ";С2)-1);ИСТИНА;ЛОЖЬ) L

М Дата увольнения

Соответствие всем условиям

Сравнение фамилий

Проверка на увольнение

Дата приема на работу

Сравнение табельных номеров

01.02.1995

-ЕПУСТО(К2)

-ЕСЛИ(Р2=02;ИСТИНА;Л0ЖЬ)

-M(L2;M2;N2)

-ЕСЛИШ2-ЛЕВСИМВ(С2;НАИТИГ ";С2И);ИСТИНА;ЛОЖЫ 02.03.1996

=ЕПУСТО(КЗ)

=ЕСЛИ(РЗ=03;ИСТИНА;Л0ЖЬ)

=M(L3;M3;N3)

=ЕСЛИ(НЗ=ЛЕВСИМВ(СЗ;НАИТИГ ";СЗ)-1);ИСТИНА;ЛОЖЬ) = ЕПУСТО(К4)

=ЕСЛИ(Р4=е4;ИСТМНА;ЛОЖЬ)

С4)-1

ИСТИНА;ЛОЖЬ)

= M(L4;M4;N4)

= ЕСЛИ(Н4=ЛЕВСММВ(С:4;НАИТИ( -ЕПУСТО(К5)

-ЕСЛИ(Р5=05;ИСТИНА;Л0ЖЬ)

С5)-1

ИСТИНА;ЛОЖЬ)

-M(L5;M5;N5)

-ЕСЛИ(Н5-ЛЕВСИМВ(С5;НАИТИ( =ЕПУСТО(К6)

=ЕСЛИ(Р6=06;ИСТИНА;Л0ЖЬ)

С6)-1)

ИСТИНА;ЛОЖЬ)

=M(L6;M6;N6)

=ЕСЛИ(Н6=ЛЕВСИМВ(С6|НАИТИ( = ЕПУСТО(К7)

=ЕСЛИ(Р7=еГ;ИСТИНА;ЛОЖЬ)

С7)-1

МСТМНА;ЛОЖЬ)

= M(L7;M7;N7)

= ЕСЛИ(Н7=ЛЕВСММВ(С:7;НАИТИ( -ЕПУСТО(К8)

-ЕСЛИ(Р8=ОВ;ИСТИНА;ЛОЖЬ)

С8)-1

ИСТИНА;ЛОЖЬ)

-M(L8;M8;N8)

-ЕСЛИ(НЗ-ЛЕВСИМВ(С8;НАИТИ( =ЕПУСТО(К9)

=ЕСЛИ(Р9=09;ИСТИНА;Л0ЖЬ)

С9)-1);ИСТИНА;ЛОЖЬ)

=M(L9;M9;N9)

=ЕСЛИ(НЭ=ЛЕВСИМВ(С9|НАИТИ( 19.12.1997 29.09.2001 =ЕПУСТО(КЮ)

=ЕСЛИ(Р1 0=G1 Р;ИСТИНА;ЛОЖЬ)

= M(L10;M10;N10)

= ЕСЛИ(Н1 0=ЛЕВС:ММВ(С:1 0;НАИТМ(' ";С1 0)-1);МСТМНА;ЛРЖЬ) -ЕПУСТО(К11)

-ЕСЛИ(Р11-G11 ;ИСТИНА;ЛОЖЬ)

-M(L11 ;М11 ;N11

-ЕСЛИ(Н11-ЛЕВСИМВ(С11 ;НАИТИ(" ";С11)-1);ИСТИНА;ЛОЖЬ) I > I

< I t > \Проверка данных /

Готово Рисунок 32

Составление сложной формулы методом вложения Будем заменять ссылки на ячейку содержимым этой ячейки, т.е.

Для этого выделяется первая формула без знака = и копируется, затем курсор устанавливается на ячейку, ссылающуюся на эту формулу, и вместо адреса ячейки вставляется сама формула с помощью Ctrl+Insert и т. д.

В результате получим итоговую формулу: =И(ЕПУСТО(К2);ЕСЛИ(Б2=12;ИСТИНА; ЛОЖЬ); ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ(" ";С2)-1);ИСТИНА;ЛОЖЬ))

Промежуточные столбцы L, M и N можно удалить, а можно скрыть., для этого выделите скрываемые столбцы и выполните Фор- мат^Столбцы^Скрыть или из контекстного меню Скрыть.

Расчет премии за выслугу лет

Премия за выслугу лет зависит от стажа работника, ее величина определяется на основании данных таблицы: Стаж, годы Премия, % Менее 1 Не начисляется От 1 до 3 (3 не входит) 10 От 3 до 5 (5 не входит) 20 От 5 до 10 (10 не входит) 30 Свыше 10 40 Алгоритм вычисления премии.

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

Определить число отработанных сотрудником лет, разделив полученное на предыдущем этапе число дней на 365,25 - среднее число дней в году с учетом високосных лет.

Отбросить от полученного значения дробную часть.

Произвести начисление премии согласно таблице.

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

Определение полного количества лет работы на предприятии:

Для отбрасывания дробной части используем математическую функцию ОТБР, которая усекает число до целого, отбрасывая дробную часть числа, так что остается целое число.

В итоге для первого сотрудника имеем формулу: =ОТБР(($0$2- J2)/365,25), где $О$2 - ячейка, содержащая дату расчета премии, J2 - дата приема на работу 1 сотрудника.

Расчет суммы премии.

Расчет производится с использованием логических функций ЕСЛИ. Первая формула создается по принципу: если служащий проработал менее года (значение ячейки Q2 сравнивается со значением ячейки N4), то премия равна произведению значения оклада, указанного в ячейке Е2, на коэффициент, внесенный в ячейку О4.

=ЕСЛИ^2<1;04;ЕСШ^2<$т5;Е2*$0$5;ЕСЛИ^2<$Щб;Е2*$0$6; ЕСЛИ^2<$Щ7;Е2*$0$7;Е2*$0$8))))

Учет проверки условий (если сотрудник не уволен, табельные номера и фамилии совпадают, то начисляется премия, в противном случае выводится - Ошибка!)

=ЕСЛИ(Ь2?2;"Ошибка!")

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

в Microsoft Excel - База данных Введите вопрос

Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

ijEsaLAjiatai^aui ^ а - j I ч - ^ ч & ? - л зл щ ^ I * *

Arial Cyr

- | ж К Ч I =1 ш | Ivj N | ~СГ

f* =Е СЛ И (L2;S2; "Ошибка!")

D

Н Премия

Отдел

Оклад

Дата увольнения

Соответствие всем условиям

Дата приема на работу

Табель ный номер

Та б ел ьный номер

Иванов И.А.

0123

1 050.00р

0123

Реализации

01.02.1995

ИСТИНА

420 280

Петров Т.Р.

7 00,0 0 р

Петров

Дата | 31.12.2006 Контроля

ЗОО.ООр

Сидорова К Г.

420 320

420 320

Реализации

1 400,ООр

Иваненко А О.

700,ООр

Петренко

Петренко P.O. 600,ООр

Сидоренко

Снабжения

Сидоренко С.Ю. 310,ООр

Карпов

Контроля

Карпова Т.Н. 910,ООр

Лещова

Лещова Q.H. 600,ООр

Щукина

Щукина Р.И. 400,ООр

Вьюнов

Вьюнов О.Ю. > I

н ч > м [\ Премия /

Готово Рисунок 33

4. Формирование приказа о премии за выслугу лет

• создайте типовой бланк приказа в Word, оставив место для вставки таблицы, сформированной в Excel;

«__» 2006 г.

Приказ №

Выплатить премию за выслугу лет за 2006 год следующим сотрудникам:

перейдите в Excel и выделите диапазон ячеек и скопируйте его в буфер обмена;

перейдите в текстовый редактор, установите курсор в место вставки таблицы и выполните команду Правка^Специальная вставка;

в диалоговом окне Специальная вставка, в списке Как выделите Лист Microsoft Ехсв!(объект)

установите переключатель Связать.

В результате на странице текстового документа появится объект, связанный с электронной таблицей. Изменения, вносимые в электронной таблице, будут отражаться в документе Word.