СОДЕРЖАНИЕ
Часть 1. Постановка задачи……………………………………………………..3
1. Описание
входной информации…………………………………………..4
2. Описание условно-постоянной
информации…………………………….5
1. Описание результирующей
информации………………………………...6
2. Описание алгоритма решения задачи…………………………………….7
3. Решение задачи средствами MS Excel……………………………………7
Приложение………………………………………………………………………20
Список использованной литературы…………………………………………...22
1. Условие задачи
Для планового отдела
необходимо рассчитать нормативную трудоемкость годовой производственной
программы по профессиям и разрядам. Для этого следует рассчитать два
показателя:
- нормативную
трудоемкость годовой производственной программы по изделиям;
- нормативную
трудоемкость годовой производственной программы по профессиям и разрядам.
Первый показатель
рассчитывается путем умножения годовой производственной программы изделий на
нормативную трудоемкость единицы изделия
разрезе профессий и разрядов, а второй равняется сумме нормативной
трудоемкости производственной программы по изделиям.
Входная информация: код изделия, код профессии, код разряда, трудоемкость по
профессии и разряду, годовая производственная программа.
Результирующая информация: код изделия, трудоемкость годовой программы по
изделию, трудоемкость годовой программы по профессии и разряду.
2. Постановка задачи
2.1.
Наименование
задачи: определение нормативной
трудоемкости годовой производственной программы по профессиям и разрядам.
2.2.
Место
решения задачи: плановый отдел ООО «Альянс».
2.3. Цель решения задачи: расчет нормативной трудоемкости годовой
производственной программы по профессиям и разрядам.
2.4.
Периодичность
решения задачи: ежегодно до 26 числа последнего месяца.
2.5. Для кого предназначено решение
задачи: для планового отдела, бухгалтерии, руководства организации.
2.6. Источники получения исходных
документов: бухгалтерия.
2.7. Информационная модель задачи:
Рис.
1. Информационная модель задачи.
2.8. Экономическая сущность задачи.
Анализ
полученной информации, необходимый для принятия решений по формированию кадровой
политики, т.е. на основании ведомости, полученной в результате рения данной
задачи, принимаются управленческие решения, касающиеся годовой производственной
программы.
3. Описание входной информации.
3.1. В качестве входной
информации используется документ «Сводная ведомость». На основании
этого документа создаётся следующий машинный документ.
Сводная ведомость
|
Код
изделия
|
Наименование изделия
|
Единица измерения
|
Код
профес-сии
|
Наименование профессии
|
Нормативная трудоемкость ед. изделия
|
Кол-во изделий в год
|
|
I
|
NI
|
EI
|
P
|
NP
|
Тj
|
|
.
Описание структуры первичного
документа
«Сводная
ведомость»
|
Имя
реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ
сортировки
|
Способ
ввода
реквизита
|
|
целые
|
дробные
|
|
Код
изделия
|
I
|
С
|
3
|
|
1
|
вручную
|
|
Наименование изделия
|
NI
|
С
|
9
|
|
|
автоматически из справочника
|
|
Единица измерения
|
EI
|
C
|
2
|
|
|
вручную
|
|
Код
профессии
|
P
|
С
|
2
|
|
2
|
вручную
|
|
Наименование профессии
|
NP
|
С
|
7
|
|
|
автоматически из справочника
|
|
Нормат. трудоемкость на ед.
изд.
|
|
Ч
|
1
|
2
|
|
вручную
|
|
Кол-во изделий в год
|
|
Ч
|
3
|
|
|
вручную
|
3.2. Количество
документов за период: ежегодно 1 шт.
3.3. Контроль
правильности заполнения и ввода документа:
-
контролировать
реквизит «Код изделия» (I) на соответствие списку значений (диапазон
от 100 до 107)
- контролировать реквизит «Код профессии» (P) на соответствие списку значений (диапазон от 10 до
17).
4. Описание
условно-постоянной информации
Для решения данной задачи
будем использовать два справочника:
·
«Справочник
изделий» - для расшифровки кодов изделий и соответствующей им
нормативной трудоемкости.
·
«Справочник
профессий» - для расшифровки кодов профессий и разрядов.
4.1. Описание структуры документа «Справочник
изделий»:
|
Имя
реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ
сортировки
|
|
целые
|
дробные
|
|
Код изделия
|
I
|
С
|
3
|
|
1
|
|
Наименование
изделия
|
NI
|
C
|
9
|
|
|
|
Единица
измерения
|
EI
|
C
|
2
|
|
|
|
Нормативная трудоемкость на ед.изд./ч.
|
|
Ч
|
1
|
2
|
|
4.2. Описание структуры документа «Справочник
профессий»:
|
Имя
реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ
сортировки
|
|
целые
|
дробные
|
|
код профессии
|
P
|
С
|
2
|
|
1
|
|
наименование профессии
|
NP
|
C
|
7
|
|
|
|
разряд
|
R
|
С
|
1
|
|
|
5. Описание выходной
(результирующей) информации
В
результате решения задачи следует получить документ: «Нормативная трудоемкость годовой производственной программы».
5.1. Описание структуры полей выходного
документа «Нормативная трудоемкость годовой
производственной программы»:
|
Имя
реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ
сортировки
|
|
целые
|
дробные
|
|
Код
изделия
|
I
|
С
|
3
|
|
1
|
|
Единица измерения
|
EI
|
С
|
2
|
|
|
|
Наименование изделия
|
NI
|
C
|
15
|
|
|
|
Код профессии
|
Р
|
С
|
2
|
|
|
|
Наименование профессии
|
NI
|
С
|
10
|
|
|
|
Разряд
|
R
|
C
|
1
|
|
|
|
Количество изделий в год
|
|
Ч
|
3
|
|
|
|
нормативная трудоемкость на единицу изделия, ч.
|
|
Ч
|
1
|
2
|
|
|
нормативная трудоемкость годовой программы
|
|
Ч
|
4
|
|
|
5.2. Количество документов за период: ежегодно 1 шт.
5.3. Количество строк в документе (в среднем): 10
5.4. Контроль правильности документа:
- контролировать реквизит «Код изделия»
(I) на
соответствие списку значений (см. документ «Справочник изделий»);
- контролировать реквизит «Код профессии» (P) на соответствие списку значений
(см. документ «Справочник профессий»).
6. Описание алгоритма
решения задачи.
Для получения нормативной трудоемкости годовой производственной программы необходимо
рассчитать следующие показатели:
TGi = Тi ´ KGi
где TGi - нормативная
трудоемкость годовой
производственной программы по изделиям,
- нормативная
трудоемкость на единицу изделия I;
- количество изделий
в год.
TGp =∑ TGi
где TGp - нормативная
трудоемкость годовой
производственной программы по профессиям
и разрядам;
Решение задачи средствами MS Excel
Для того чтобы выполнить
решение данной задачи в среде Microsoft Excel
необходимо выполнить следующие пункты:
1. Вызовем Excel
·
Нажмем кнопку «Пуск» далее «Программы», выберем Microsoft Excel.
2. Переименуем «Лист 1» в «Справочник изделий»:
·
установим курсор мыши на ярлык «Лист 1» и нажмем правую кнопку мыши;
·
выберем в контекстном меню команду «Переименовать» и нажмем левую кнопку мыши;
·
наберем на клавиатуре «Справочник изделий»;
·
нажмем клавишу «Enter».
3. Введем заголовок таблицы «Справочник изделий»
·
сделаем ячейку А1 активной;
·
наберем на клавиатуре «Справочник изделий»;
нажмем на строку формул.
4. Отформатируем заголовок
·
выделим ячейки А1-D1;
·
на панели инструментов «Форматирование» нажмем кнопку «Объединение ячеек».
5. Отформатируем ячейки А2-D2 под ввод длинных заголовков:
· выделим ячейки А2-D2;
· выполняем команду «Ячейки...» в меню «Формат»;
· выберем закладку «Выравнивание»;
· в группе опций «Отображение» установите флажок опции «Переносить по словам».
Рис. 2. Задание
переноса слов при вводе в ячейку
длинных
предложений
6.
Введем в
ячейки А2:D2 информацию, представленную на рис. 3.
Рис. 3. Имена
полей таблицы «Справочник изделий»
7. Организуем контроль вводимых данных в
колонку «Код изделия»:
·
выделим ячейки А3:А10 /«Данные»/ «Проверка»/ в поле «Тип данных» нажмем кнопку 
/ выберем «Целое число» (Рис. 4)
Рис. 4. Выбор типа данных
·
зададим
в поле «Минимум»: 100 / в поле
«Максимум»: 107 (рис. 5).
Рис. 5. Задание интервала допустимых значений целых чисел
·
выберем
закладку «Сообщение для ввода»
·
введем в
поля «Заголовок» и «Сообщение» информацию, приведенную на рис. 6, 7.
Рис. 6. Задание сообщения для ввода
Рис. 7. Вид экрана с сообщением для ввода
·
выберем
закладку «Сообщение об ошибке»
·
в поле «Вид» выберем «Останов» (рис. 8). В
случае ввода ошибочных данных на экран монитора выводится сообщение (рис. 9).
Рис. 8. Сообщение об ошибке «Останов»
Рис. 9. Вид сообщения «Останов»
8. Отформатируем ячейки В3:В10 для ввода текстовых символов:
·
выделим
ячейки ВЗ:В10 /«Формат» /«Ячейки» /
«Число»/ «Текстовый»/ «ОК».
9. Введите информацию, приведенную на
рис. 10.
10.
Присвоить имя группе ячеек (рис.11):
выделим ячейки А3:D10 /
«Вставка» / «Имя» / «Присвоить» / «Добавить» / «ОК»
Рис. 10. Вид таблицы «Справочник
изделий»
Рис 11. Вид окна Присвоение имени
11.
Аналогично
создаем «Справочник профессий» (рис.12).
Рис.
12. Вид таблицы «Справочник профессий»
12.
Переименуйте
«Лист 3» в «Сводная ведомость».
13.
Создадим
таблицу «Сводная ведомость».
14.
Организуем
подобно предыдущему примеру проверку ввода данных в графу «Код изделия» и «Код
профессии» с выдачей сообщения об ошибке.
15.
Для автоматического заполнения граф
«Наименование изделия» и «Наименование профессии» используем функцию ВПР.
·
Заполним графу «Наименование изделия» в соответствии с кодом изделия:
·
сделаем ячейку В3 активной /«Функция»/ «Вставка» / в поле
«Категория:» - «Ссылки и массивы» / в
поле «Функция:» - «ВПР»/ «ОК»;
(рис. 13);
Рис. 13.
Вид первого окна мастера функций
·
введем информацию в поле «Искомое значение», щелкнув по ячейке A3 (это значение, которое должно быть
найдено в первом столбце массива);
·
введем информацию в поле «Таблица» (таблица с информацией, из которой возвращаются значения);
·
воспользуемся командой «Имя:» из меню «Вставка»;
·
использовать команду «Вставить...»;
·
выделить «Имя: Код изделия»/ «ОК»;
Рис. 14. Ввод имени массива в качестве
аргумента формулы
·
введем информацию в поле «Номер _столбца» — 2 (номер переносимого
столбца в исходной таблице)
·
введем информацию в поле «Интервальный просмотр» — 0
(Если этот аргумент имеет значение ИСТИНА(1) или опущен, то
возвращается приблизительно соответствующее значение; если - значение ЛОЖЬ (0),
то функция ВПР ищет точное соответствие) / ОК
Рис.
15. Вид окна мастера функций ВПР
16.
Скопируем формулу в ячейки В4-В10:
·
сделаем ячейку ВЗ активной;
·
установим курсор на маркер в правом нижнем углу;
·
сделаем двойной щелчок левой кнопкой мыши.
17.
Аналогично
заполняем графу «Наименование профессии» в соответствии с кодом профессии (рис.
16).
Рис.
16. Вид таблицы «Сводная ведомость» в
формулах
18.
Остальные графы заполняем вручную, как
показано на рис. 17.
Рис. 17. Вид таблицы
«Сводная ведомость»
19.
Переименуем
«Лист 4» в «Нормативная трудоемкость годовой производственной программы по
изделиям»
20.
Для автоматического заполнения граф «Разряд»,
«Нормативная трудоемкость на единицу изделий»,
«Количество изделий в год» также применяли функцию ВПР:
·
В
ячейку Е3 введем формулу =ВПР(C3;Код_профессии;3;0);
·
В
ячейку F3 введем формулу
=ВПР(A3;СВОДВЕД! $A$3:$F$10;5;0), затем скопируем
щелчком мыши в F4-F10;
·
В
ячейку G3 введем формулу
=ВПР(A3;СВОДВЕД!$A$3:$F$10;6;0) , далее копируем в G4-G10;
·
Аналогично
заполняем графы «Наименование изделий» и «Наименование профессий»
21.
Высчитываем
значения нормативной трудоемкости годовой производственной программы: ячейку Н3
заносим формулу = F3*G3 далее копируем ее в оставшиеся ячейки.
22.
Таким
образом, получаем искомые значения, представленные
на рис. 18.
Рис. 18. Вид таблицы «Нормативная
трудоемкость изготовления изделий»
23. Создадим ведомость «Нормативная
трудоемкость годовой производственной программы по изделиям»:
·
установим
курсор в поле таблицы «Нормативная трудоемкость годовой производственной
программы по изделиям»;
·
«Данные»
/ «Сводная таблица...»/в окне «Мастер сводных таблиц» — шаг 1 из 3 / «Далее»;
Рис. 19.
«Мастер сводных таблиц» — шаг 1 из 3
·
в
окне «Мастер сводных таблиц» — шаг 2 из 3 /«Далее»;
Рис. 20.
. «Мастер сводных таблиц» — шаг
2 из 3
·
в
окне «Мастер сводных таблиц и диаграмм» — шаг 3 из 3 / «Макет».
Рис. 21.
«Мастер сводных таблиц» — шаг 3 из 3
Чтобы вставить поле в сводную
таблицу, его необходимо перетащить в одну из четырех областей: «Страница»,
«Столбец», «Строка» и «Данные»:
·
перенесем
в поле «Страница» (необязательное) надпись «Код изделия»;
·
перенесем
в поле «Строка» надпись «Наименование изделия»;
·
перенесем
в поле «Данные» надпись «Сумма по» / ОК (рис. 21).
в окне
«Мастер сводных таблиц и диаграмм» — шаг 1 из 3 /«Новый лист» / «Готово».
Рис. 22. Создание макета сводной таблицы
- Получаем сводную таблицу «Нормативная
трудоемкость годовой производственной программы по изделиям» (рис.23):
Рис.
23. Сводная таблица «Нормативная
трудоемкость
годовой
производственной программы по изделиям».
24.
Аналогично
создаем ведомость «Нормативная трудоемкость годовой производственной программы
по профессиям и разрядам» (рис24,25)
Рис.24. Сводная таблица «Нормативная
трудоемкость годовой производственной программы по профессиям и разрядам»
25.
С помощью
сводной таблицы можно фильтровать и группировать данные. Активизировав команду
«Все», можно выбрать необходимые значения для отображения (рис.25):
Рис.
25. Фильтрация данных по полю «Разряд»
Приложение
Справочник изделий
|
Код изделия
|
Наименование
изделия
|
Единица
измерения
|
Нормативная
трудоемкость ед. изд., ч
|
|
100
|
шайба
|
шт
|
0,5
|
|
101
|
гайка
|
шт
|
0,30
|
|
102
|
втулка
|
шт
|
1,1
|
|
103
|
вал
|
шт
|
1,5
|
|
104
|
шуруп
|
шт
|
0,32
|
|
105
|
гвоздь
|
шт
|
0,30
|
|
106
|
болт
|
шт
|
0,28
|
|
107
|
двигатель
|
шт
|
2,5
|
Справочник профессий
|
Код профессии
|
Наименование
професии
|
Разряд
|
|
10
|
Токарь
|
1
|
|
11
|
Токарь
|
2
|
|
12
|
Токарь
|
3
|
|
13
|
Слесарь
|
1
|
|
14
|
Слесарь
|
2
|
|
15
|
Сборщик
|
1
|
|
16
|
Сборщик
|
3
|
|
17
|
Сварщик
|
2
|
Сводная ведомость
|
Код изделия
|
Наименование
изделия
|
Код профессии
|
Наимен.
профессии
|
Нормативная
трудоемкость ед.изделия
|
Кол-во изделий
в год, тыс.шт
|
|
100
|
шайба
|
10
|
Токарь
|
0,5
|
999
|
|
101
|
гайка
|
11
|
Токарь
|
0,30
|
879
|
|
102
|
втулка
|
12
|
Токарь
|
1,1
|
798
|
|
103
|
вал
|
13
|
Слесарь
|
1,5
|
800
|
|
104
|
шуруп
|
14
|
Слесарь
|
0,32
|
910
|
|
105
|
гвоздь
|
15
|
Сборщик
|
0,30
|
950
|
|
106
|
болт
|
16
|
Сборщик
|
0,28
|
890
|
|
107
|
двигатель
|
17
|
Сварщик
|
2,5
|
560
|
Ведомость «Нормативная трудоемкость годовой производственной программы по
изделиям»
|
Наименование
изделия
|
Итог
|
|
Болт
|
249,2
|
|
Вал
|
1200
|
|
Втулка
|
877,8
|
|
Гайка
|
263,7
|
|
Гвоздь
|
285
|
|
Двигатель
|
1400
|
|
Шайба
|
499,5
|
|
Шуруп
|
291,2
|
|
Общий
итог
|
5066,4
|
Ведомость «Нормативная трудоемкость годовой производственной программы по
профессиям и разрядам»
|
Наимен.
профессии
|
Разряд
|
Итог
|
|
Сборщик
|
1
|
285
|
|
|
3
|
249,2
|
|
Сборщик
Итог
|
|
534,2
|
|
Сварщик
|
2
|
1400
|
|
Сварщик
Итог
|
|
1400
|
|
Слесарь
|
1
|
1200
|
|
|
2
|
291,2
|
|
Слесарь
Итог
|
|
1491,2
|
|
Токарь
|
1
|
499,5
|
|
|
2
|
263,7
|
|
|
3
|
877,8
|
|
Токарь
Итог
|
|
1641
|
|
Общий
итог
|
|
5066,4
|
ФОРМА
ДОКУМЕНТА
«Нормативная трудоемкость годовой
производственной программы
по
профессиям и разрядам»
Наименование
организации
|
Код
изделия
|
Наименование
изделия
|
Кол-во
изделий в год
|
Нормативная
трудоёмкость
|
|
|
|
|
|
" " 200 г.
|
Код
профессии
|
Наименование
профессии
|
Разряд
|
Нормативная
трудоёмкость годовой производственной программы
|
|
|
|
|
|
Список использованной литературы
1.
Романов
А. Н., Одинцов Б. Е. Информационные системы в экономике: Учебное пособие. - М.:
Вузовский учебник, 2006. - 300 с.
2.
Информационные
системы в экономике: Методические указания по выполнению контрольной работы для
самостоятельной работы студентов III курса. ВЗФЭИ. – М.: Вузовский учебник, 2007. – 80 с.