в) общую сумму премии для каждого работника

2 этап. Моделирование таблицы.

Оформим таблицу средствами MS Excel’2000/2003. Для этого:

1. Перейдите на новый лист и назовите его «Расчет премии».

2. Установите параметры страницы: ориентация - страницы Альбомная, верхнее и нижнее поле – 1,5 см, левое и правое – 2 см.

3. Оформите заголовок и строку заголовков таблицы (рисунок 2). Для всего текста установить тип шрифта Times New Roman, для текста 1 строки установить размер шрифта – 14, для строки заголовков размер шрифта – 12, начертание – полужирный, выравнивание по центру, для остального текста размер шрифта – 11, выравнивание по левому краю. А так же задать границу ячейкам.

4. Для ячейки D1 установить формат – дата, тип –только название месяца. Ввести в ячейку номер месяца – 11.

5. В ячейку J1 ввести количество рабочих дней в ноябре -26.

6. Заполнить таблицу исходными данными.

7. Выполнить Автоподбор ширины столбцов и Автоподбор высоты строк.

8. Установите вид документа – Разметка страницы (Вид/ Разметка страницы).

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

10. Сохранить изменения.

Рисунок 2. Таблица «Расчет премии за ноябрь 2005 г»

3 этап. Выполнение расчетов.

Для того чтобы рассчитать премию для каждого работника необходимо выполнить промежуточные расчеты:

1. На второй странице рабочего листа «Расчет премии» внести условия расчетов премии (рисунок 3). Для ячеек С35, С36, С37, F35, F36 установить процентный формат.

Рисунок 3. Вид страницы 2 рабочего листа «Расчет премии».

2. В ячейке G5 рассчитаем план производства за месяц по плану, используя функцию СУММ: =СУММ(ячейки нормы в день раскройного цеха)*$J$1. Скопируйте формулу в соответствующие ячейки остальных цехов.

3. В ячейке H5 рассчитаем план производства за месяц по факту, используя функцию СУММ: =СУММ(ячейки нормы выработки по факту за месяц). Скопируйте формулу в соответствующие ячейки остальных цехов.

4. В ячейке I5 рассчитаем размер премии по плану, используя логические функции ЕСЛИ, И, по алгоритму, показанному на рисунке 4. Где

H$5 – размер плана производства за месяц,

G$5 - размер плана производства за месяц по факту,

D5 – размер оклада,

$C$35, $C$36, $C$37 – процент премии по плану от оклада.

5. Для ввода формулы необходимо:

­ вызвать Мастер функции, нажав в Строке формул кнопку или выполнить команду Вставка/Функция,

­ выбрать категорию – Логические, функцию – ЕСЛИ,

­ в диалоговом окне Аргументы функции заполнить поля, в поле Лог_выражение ввести лог_выражение1, в поле Значение_если_истина ввести значение_если_истина1, в поле Значение_если_ложь ввести слово - если и нажать Ок.

­ в строке формул выделить слово - если и нажать кнопку , в диалоговом окне Аргументы функции заполнить поля в соответствии с алгоритмом для вложенной функции ЕСЛИ 1 уровня,

­ аналогично ввести аргументы вложенной функции ЕСЛИ 2 уровня, для которой в поле Значение_если_ложь ввести значение_если_ложь3 и нажать Ок.

В результате в строке формул должна получиться следующая формула: =ЕСЛИ(H$5<G$5;0;ЕСЛИ(H$5=G$5;D5*$C$35;ЕСЛИ(И(H$5>G$5;H$5<=G$5*1,5);D5*$C$36;D5*$C$37))). Скопируйте формулу в остальные ячейки по раскройному цеху, по заготовочному и пошивочному, изменив адреса ячеек в логических выражениях.

Рисунок 4. Алгоритм расчета премии по плану.

6. В ячейке J5 рассчитаем размер премии по норме выработки:

=ЕСЛИ(ИЛИ(F5<E5*$J$1;F5=0);0;ЕСЛИ(F5=E5*$J$1;D5*$F$35;D5*$F$36)). Где

F5 – норма выработки пар по факту в месяц,

E5*$J$1 -норма выработки пар в день*количество рабочих дней,

$F$35, $F$36 - процент премии по выработке от оклада.

Скопируйте формулу в остальные ячейки.

7. В ячейке K5 рассчитаем общий размер премии: =СУММ(I5:J5).

8. Сохранить изменения.

9. Установим для ячеек, в которых рассчитан размер плана производства за месяц по факту, условное форматирование. То есть если размер плана производства за месяц по факту>= размера плана производства за месяц, то ячейку залить, например, желтым цветам и установить начертание шрифта – полужирное, а если размер плана производства за месяц по факту< размера плана производства за месяц, то установить начертание шрифта – полужирное и цвет шрифта, например, красный. Для этого:

­ выделить диапазон ячеек H5:H23,

­ выполнить команду меню Формат/Условное форматирование, в открывшемся диалоговом окне установить параметры для Условия 1 указанные на рисунке 5, нажать кнопку А также,

­ в дополнительном окне установить параметры для Условия 2 указанные на рисунке 5, нажать кнопку А также, установить параметры для Условия 3, нажать ОК.

10. Оцените результат. Измените размеры нормы выработки пар по факту в месяц. Какие произошли изменения?

11. Сохранить изменения.

Рисунок 5. Установка параметров условного форматирования.