Решение оптимизационной задачи, связанной со штатным расписанием организации

 

Глава 2. Практическое применение пакета Microsoft Office (на примере решения задачи по распределению премии сотрудникам организации)

2.1. Решение задачи  по распределению премии сотрудникам  при помощи Microsoft Office Excel 2007

Нам были даны условия задачи, которые представлены ниже (Рис.2.1.).

Рис.2.1.

 

Необходимо распределить премию в размере 770 тыс. рублей. Известно, что зарплата равна произведению коэффициента А на минимальный оклад, равный 5 тыс. рублям. Премия рассчитывается путем умножения зарплаты на коэффициент В.  Как видно из рисунка, коэффициент А известен.

Найти решение при следующих  ограничениях: коэффициент В  уборщицы должен находится в пределах от 0,4 до 0,7, младшего продавца и водителя – от 0,55 до 0,7, старшего продавца – от 0,7 до 0,93, менеджера и товароведа – от 0,93 до 1, директора – от 1,2 до 2,2, заместителя директора – от 1 до 1,2.

Эту задачу можно решить в  среде табличного процессора  Excel с помощью программы Поиск решения. Запуск программы Поиск решения осуществляется с помощью команды Поиск решения меню Данные. Эта команда доступна только в том случае, если в диалоговом окне Надстройки помечен пункт Пакет анализа.

Для решения задачи необходимо ввести формулы в лист Microsoft Excel. Известно, что зарплата равна произведению коэффициента А на минимальный оклад. Премия рассчитывается путем умножения зарплаты на коэффициент В. Суммарная премия равна произведению премии одного сотрудника на количество работников. Сумма премий рассчитывается путем сложения суммарной премии всех категорий работников. Таблица имеет следующий вид (Рис.2.2.):

Рис.2.2.

 

Далее вызываем команду Поиск решения. В появившемся диалоговом окне Поиск решения (Рис.2.3.) в качестве целевой ячейки необходимо установить адрес $I$2, где находится итоговое значение суммы премии сотрудников по магазину.

Установим значение целевой ячейки равное числу 770.

Изменяемыми ячейками, то есть ячейками результата, являются ячейки $D$2:$D$8.

Затем устанавливаются ограничения, указанные в задании. В качестве ограничений - допустимый диапазон варьирования изменяемых ячеек.

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

 

 

Рис.2.3.

Рис.2.4.

 

В левой части  окна указывается адрес изменяемой ячейки, например адрес $D$2, содержащий коэффициент В уборщицы. Затем указывается нужный знак, например, <=,  в правой части вводится предельно допустимое значение, по условию задачи равное числу 0,7. Таким же образом устанавливаются условие $D$2>=0,4. Аналогично заполняются остальные ограничения.

Путем нажатия  кнопки Выполнить получается решение задачи, показанное на Рис.2.4.

 

 

 

 

 

Рис.2.4.

 

Таким образом, при помощи Microsoft Office Excel 2007 решена задача по распределению премии сотрудникам.

 

2.2. Создание базы данных в Microsoft Office Access 2007

Создание любой базы данных (БД) начинается с создания файла  БД и присвоении ему имени. Для этого открываем программу Access. Нажимаем кнопку Кнопка «Office»,затем – Создать, далее – указываем имя в диалоговом окне справа, а так же место хранения создаваемой БД и нажимаем кнопку Создать (Рис.2.5.).

Рис.2.5.

 

После проведенных операций нам открывается таблица в режиме Режим таблицы. Необходимо перейти в Режим Конструктор. При переходе высвечивается диалоговое окно в котором нужно указать имя таблицы (в нашем случае – Штатное расписание 1)

Создаем две таблицы БД в режиме конструктора. Структура  таблиц следующая (таблица 1 – Рис.2.6., таблица 2 – Рис.2.7.):

В обеих таблицах создается  ключевое поле – Должность. Свидетельствует  о том, что поле является ключевым знак ключ в левой колонке, то в  какой строке он стоит показывает какое именно поле является ключевым (Рис. 2.6., 2.7.).

При этом обратите внимание на значение индексированного поля (Рис.2.8.), это говорит о том, что позиции  в данном столбце в Режиме Таблицы  не должны повторяться.

Рис.2.6.

 

Рис.2.7.

Рис.2.8.

 

В режиме Таблица заполняем таблицы Штатное расписание 1 и Штатное расписание 2 данными (на Рис.2.9., Рис.2.10. дан фрагмент).

Рис.2.9.

 Рис.2.10.

 

 

Так как в столбце подразделение  повторяющиеся значения упростим процедуру  ввода. Для этого в режиме конструктора создадим новую таблицу, содержащую только одно поле Список подразделений. Присвоим таблице имя Список подразделений. Ключевое поле задавать не следует. Откроем ее и заполним следующими данными:

1) Административный;

2) Вспомогательный;

3) Производственный;

4) Основной.

Откроем таблицу Штатное расписание 2 в режиме конструктора. Выделите поле Подразделение и во вкладке Подстановка укажите тип поля Поле со списком. В открывшемся свойстве поля со списком укажите тип источника строк – Таблица или Запрос, Источник строк – Список подразделений.

В этом случае не будет необходимости  вводить с клавиатуры  названия подразделений, можно будет их выбирать из  раскрывающегося списка (Рис.2.11.).

Рис.2.11.

 

Необходимо создать межтабличные связи. На панели инструментов выбираем Работа с базами данных/Схема данных (Рис.2.12.).

Рис.2.12.

 

 

В диалоговом окне Добавление таблицы поочередно с помощью  кнопки Добавить добавляем таблицы Штатное расписание1 и Штатное расписание 2. Закрываем диалоговое окно.

Перетаскиваем поле Должность  из таблицы Штатное расписание 1 на список таблицы Штатное расписание 2. Появится окно связи. На правой панели окна Связи выбираем поле Должность таблицы Штатное расписание 2, включаемые в связь. Поставим галочку в окошке Обеспечение целостности  данных. Программа Обеспечение целостности  данных будет следить за работой пользователя, и пресекать его некорректные действия (Рис.2.13).

Нажмите кнопку Создать и закройте окно схемы данных. Схема данных представлена на Рис.2.14. Закройте окно Схема данных.

Рис.2.13.

Рис.2.14.

Создание запроса на выборку. Создадим запрос о наличии в организации старшего продавца получающего более 30 000 рублей. Для этого:

1. На панели инструментов  БД откроем Создание/Конструктор запросов.

2. В окне Добавление таблицы выберем поочередно таблицы Штатное расписание 1 и Штатное расписание 2.

3. В списке полей таблиц  Штатное расписание 1 и Штатное  расписание 2 выберем поля, включаемые в результирующую таблицу: Должность, Зарплата, Подразделение, Фамилия. Выбор производится двойными щелчками на именах полей.

4. Зададим условие отбора для поля Зарплата. В строку Условие отбора введем: >=30 000.

6. Закроем бланк запроса. При закрытии запроса введем его имя – Запрос на выборку.

В режиме конструктора запрос на выборку имеет вид представленный на рисунке, располагаемый ниже (Рис.2.15.).

Рис.2.15.

 

Итоговый вид запроса  на выборку (Рис.2.16.) представлен ниже.

Рис.2.16.

 

Создание запроса с  параметром.

1. На панели инструментов  БД откроем Создание/Конструктор запросов.

2. В открывшемся окне  создаем запрос на выборку на основе таблиц Штатное расписание 1 и Штатное расписание 2. Войдут следующие поля: Должность, Зарплата, Премия одного сотрудника, Фамилия ИО, Адрес фактического проживания.

3. В строке Условие отбора поля Должность введем: «Старший продавец №1».

4. В строке Условие отбора поля Зарплата введем: [Введите максимальную зарплату] ИЛИ >=29 000.Текст в квадратных скобках – это текст, обращенный к пользователю.

5. Закройте запрос и  присвоим ему имя Запрос с параметром.

В режиме конструктора запрос с параметром имеет вид представленный на рисунке, располагаемый ниже (Рис.2.17.).

Рис.2.17.

 

Итоговый вид запроса  с параметром (Рис.2.18.) представлен  ниже.

Рис.2.18

 

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

В организации четыре подразделения: административный, основной, вспомогательный, производственный. Соответственно, каждая должность принадлежит к определенному подразделению.

1. Создадим запрос в режиме Конструктора на основе таблиц Штатное расписание 1 и Штатное расписание 2. В бланк запроса введите поля: Подразделение, Должность, Зарплата, Премия одного сотрудника, Фамилия ИО.

2. Для поля Подразделение введем сортировку по возрастанию.

3. На панели инструментов  выберем пункт Сумма. Эта команда необходима для создания в нижней части бланка строки Групповые операции. На основе этой команды создаются итоговые вычисления. Все поля, отобранные в этом запросе, получают в строке значение Группировка.

4. Для полей Подразделение, Должность, Фамилия ИО оставляем в строке Групповые операции значение Группировка. Для поля Зарплата, Премия одного сотрудника выбираем итоговую функцию Sum – для определения стоимости всего изделия как суммы всех его комплектующих.

5. Закроем окно запроса и присвоим ему имя – Итоговый запрос.

В режиме конструктора итоговый запрос имеет вид представленный на рисунке, располагаемый ниже (Рис.2.19.).

Рис.2.19.

 

Итоговый вид итогового  запроса (Рис.2.20.) представлен ниже.

Рис.2.20.

 

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

Первый шаг – создание запроса для отчета.

Создание запроса производится в режиме конструктора на основе таблиц Штатное расписание 1 и Штатное  расписание 2. Используемые поля – Подразделение, Должность, Зарплата, Фамилия ИО, Дата рождения. В строке Условие отбора вводится: [Введите подразделение]. Присвоить имя – Список по подразделениям.

Вид в режиме конструктора (Рис.2.21.):

 

 

 

 

 

 

Рис.2.21.

 

 

При выполнении запроса по подразделению Административный результат  будет следующим (Рис.2.22.):

Рис.2.22.

 

Шаг второй – построение отчета на основе запроса Список по подразделениям с помощью мастера.

Источником построения будет  служить запрос Список по подразделениям. Далее в окне Создания отчетов  выбираем все доступные поля, добавляем  группировку по полю Подразделение, задавать сортировку в данном случае не будем. Нажав кнопку Итоги в появившемся диалоговом окне помечаем галочкой функцию Sum по полю Зарплата. Затем выбираем макет Ступенчатый, ориентацию – Книжная, стиль – Поток.

При выборе вспомогательного подразделения отчет будет иметь вид, показанный на Рис.2.23.:

Рис.2.23.

 

Улучшить вид отчета можно  в режиме конструктора.

Для удобства ввода данных в СУБД Access предусмотрена возможность создания пользовательских форм. Формы можно создавать с помощью мастера или конструктора. Для этого:

На панели инструментов выбираем Создание/Другие формы/Мастер форм.

В появившемся окне Создание форм в строке Таблицы и запросы  находим Таблица: Штатное расписание 1. Из нее переносим все поля. Затем  в строке Таблицы и запросы  выбираем Таблица: Штатное расписание 2. Из таблицы переносим поля Подразделение, Фамилия ИО, Дата рождения, Адрес  фактического проживания. 

Нажимаем Далее.

Выбираем форму в один столбец, стиль – стандартный.

Нажимаем Готово.

Пользовательская форма  имеет следующий вид (Рис.2.24).:

Рис.2.24.

 

Отредактировать пользовательскую форму можно в режиме конструктора.

Кнопочные формы  создают для удобства работы с  БД.

Пошаговые действия:

1. На панели инструментов  выбираем Работа с базами данных/Диспетчер кнопочных форм. На вопрос о создании кнопочной формы следует ответить Да.

2. В диалоговом окне  Диспетчер кнопочных форм нажимаем кнопку Изменить… (Рис.2.25.).

 

 

Рис.2.25.

 

3. В диалоговом окне изменение страницы кнопочной формы следует нажать кнопку Создать… (Рис.2.26.).

Рис.2.26.

 

4. В новом окне –  Изменение элемента кнопочной  формы ввести:

А) Текст – Ввод и редактирование.

Б) Команда – Открыть  форму для изменения.

В) Форма – Пользовательская форма

При этом в полях Команда  и Форма нужно не вводить текст, а выбрать из списка предложенных (Рис.2.27.).

 

Рис.2.27.

Нажать кнопку OK.

5. После проведенных действий  диалоговое окно выглядит следующим  образом (Рис.2.28.):

Рис.2.28.

 

 

6. Нажимаем кнопку Закрыть, закрываем диспетчер кнопочных форм.

Кнопочная форма имеет  следующий вид (Рис.2.29.):

Рис.2.29.

При нажатии кнопки Ввод и  редактирование можно увидеть следующие (Рис.2.30):

Рис.2.30.

 

2.3. Презентации работы  с помощью Microsoft Office Power Point 2007

 

Выводы по главе

В ходе решения оптимизационной  задачи, связанной со штатным расписанием  организации, проведены действия:

1) при помощи Microsoft Office Excel 2007 и команды Поиск решения решена задача по распределению премии сотрудникам организации в соответствии с данными коэффициентами;

2) составлена база данных  по штатному расписанию сотрудников  организации при помощи Microsoft Office Access 2007. Для удобства так же сделаны запросы, для наглядного представления отчет, пользовательская форма и кнопочная форма;