Решение задачи средствами MS Excel

 

Количество документов за период: ежемесячно 1 шт.

4.4. Количество строк в документе: 30.

4.5. Контроль правильности  документа: логический контроль  полученных сумм.

 

 

 

 

 

 

 

5. Описание алгоритма  решения задачи

 

Для получения нормативной трудоемкости производства деталей необходимо рассчитать следующие показатели:

 

1) На квартальную программу:

,

где - нормативная трудоемкость на квартальную программу,

- норма штучно-калькуляционная  на деталь I;

- плановое количество деталей на квартал.

2) На месячную программу:

,

где - нормативная трудоемкость на месячную программу,

- норма штучно-калькуляционная  на деталь I;

- плановое количество деталей на месяц.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Контрольный пример

 

План производства

Код детали	Номер цеха	Код оборудования	Плановое количество деталей за квартал	Плановое количество деталей за месяц	Код  профессии	Разряд работы	Норма штучно-калькуляционная  на деталь	Нормативная трудоемкость квартальной программы предприятия  по деталям	Нормативная трудоемкость месячной программы предприятия  по деталям
10	1	55	1521	507	1	4	14,00	21294,00	7098,00
12	6	99	1260	420	2	5	16,00	20160,00	6720,00
14	4	65	1119	373	3	6	17,00	19023,00	6341,00
16	4	84	1461	487	2	5	19,00	27759,00	9253,00
18	2	78	1500	500	2	5	19,00	28500,00	9500,00
20	1	55	2451	817	2	5	21,00	51471,00	17157,00
22	6	99	1779	593	3	6	23,00	40917,00	13639,00
24	3	54	1650	550	1	4	21,00	34650,00	11550,00
26	5	84	1980	660	1	4	16,00	31680,00	10560,00
28	3	54	1731	577	2	5	17,00	29427,00	9809,00

 

Справочник деталей

 

Код детали	Норма штучно-калькуляционная  на деталь
10	14,00
12	16,00
14	17,00
16	19,00
18	19,00
20	21,00
22	23,00
24	21,00
26	16,00
28	17,00

 

 

 

Справочник профессий

 

 

Код профессии	Разряд работы
1	4
2	5
3	6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ведомость Нормативная  трудоемкость квартальной программы  предприятия

Код детали	Плановое количество деталей за квартал	Нормативная трудоемкость квартальной программы предприятия  по деталям
10	1521	21294
10 Итог		21294
12	1260	20160
12 Итог		20160
14	1119	19023
14 Итог		19023
16	1461	27759
16 Итог		27759
18	1500	28500
18 Итог		28500
20	2451	51471
20 Итог		51471
22	1779	40917
22 Итог		40917
24	1650	34650
24 Итог		34650
26	1980	31680
26 Итог		31680
28	1731	29427
28 Итог		29427
Общий итог		304881

 

 

 

 

 

 

 

 

 

 

Ведомость Нормативная  трудоемкость месячной программы предприятия

 

Код детали	Плановое количество деталей за месяц	Нормативная трудоемкость месячной программы предприятия  по деталям
10	507	7098
10 Итог		7098
12	420	6720
12 Итог		6720
14	373	6341
14 Итог		6341
16	487	9253
16 Итог		9253
18	500	9500
18 Итог		9500
20	817	17157
20 Итог		17157
22	593	13639
22 Итог		13639
24	550	11550
24 Итог		11550
26	660	10560
26 Итог		10560
28	577	9809
28 Итог		9809
Общий итог		101627

 

 

Часть 2. Решение задачи средствами MS Excel

 

1. Вызовем Excel:
• нажмем кнопку «Пуск»;
• выберем в главном меню команду «Программы»;
• выберем MS Excel.
2. Переименуем «Лист1» в «Справочник деталей»:
• установим курсор мыши на ярлык «Лист1»(нижняя часть экрана) и нажмем правую кнопку мыши;
• наберем на клавиатуре «Справочник деталей»;
• нажмем клавишу Enter.
3. Введем заголовок таблицы «Справочник деталей»:
• сделаем ячейку А1 активной (установим курсор мыши на пересечении столбца А и строки 1 и нажмем левую кнопку мыши);
• наберем на клавиатуре «Справочник деталей»;
• нажмем Enter.
4. Введем в ячейки: А2- Код детали, В2- Норма штучно-калькуляционная на деталь.
5. Организуем контроль вводимых данных в колонку «Код детали», ограничив значения интервалом 10-99:
• выделим ячейки А3-А30;
• выполним команду «Проверка…» меню «Данные»;
• в поле «Тип данных» выберем «Целое число»;
• зайдем в поле «Минимум»: 10;
• зайдем в поле «Максимум»: 99;
• выберем закладку «Сообщение для ввода»;
• введем в поле «Заголовок» - Ограничения для ввода кода, в поле «Сообщение» - Код детали может принимать значения 10-99;
• выберем закладку «Сообщение об ошибке»;
• в поле «Вид» выберем «Останов». В случае ввода ошибочных данных на экран монитора выводится сообщение: Код детали может принимать значения 10-99.
6. Заполним Справочник деталей (рис. 2.1.):

Рис. 2.1. Справочник деталей

7. Присвоим имя группе ячеек:
• выделим ячейки А3-B30;
• выберем команду «Имя» в меню «Вставка»;
• выберем команду «Присвоить»;
• в окне «Присвоение имени» нажмем кнопку «Добавить»;
• нажмем кнопку «ОК».
8. Переименуем «Лист2» в «Справочник профессий»:
• установим курсор мыши на ярлык «Лист2»(нижняя часть экрана) и нажмем правую кнопку мыши;
• наберем на клавиатуре «Справочник профессий»;
• нажмем клавишу Enter.
9. Введем заголовок таблицы «Справочник профессий»:
• сделаем ячейку А1 активной (установим курсор мыши на пересечении столбца А и строки 1 и нажмем левую кнопку мыши);
• наберем на клавиатуре «Справочник профессий»;
• нажмем Enter.
10. Введем в ячейки: А2- Код профессии, В2- Разряд работы.
11. Организуем контроль вводимых данных в колонку «Код профессии», ограничив значения интервалом 1-6:
• выделим ячейки В3-В30;
• выполним команду «Проверка…» меню «Данные»;
• в поле «Тип данных» выберем «Целое число»;
• зайдем в поле «Минимум»: 1;
• зайдем в поле «Максимум»: 6;
• выберем закладку «Сообщение для ввода»;
• введем в поле «Заголовок»- Ограничения для ввода кода, в поле «Сообщение»- Код профессии может принимать значения 1-6;
• выберем закладку «Сообщение об ошибке»;
• в поле «Вид» выберем «Останов. В случае ввода ошибочных данных на экран монитора выводится сообщение: Код профессии может принимать значения 1-6.
12. Заполним Справочник профессий (рис. 2.2.): 

Рис. 2.2. Справочник профессий

13. Присвоим имя группе ячеек:
• выделим ячейки А3-B30;
• выберем команду «Имя» в меню «Вставка»;
• выберем команду «Присвоить»;
• в окне «Присвоение имени» нажмем кнопку «Добавить»;
• нажмем кнопку «ОК».
14. Переименуем «Лист3» в «План производства»:
• установим курсор мыши на ярлык «Лист4»(нижняя часть экрана) и нажмем правую кнопку мыши;
• наберем на клавиатуре «План производства»;
• нажмем клавишу Enter.
15. Введем заголовок таблицы «План производства»:
• сделаем ячейку А1 активной (установим курсор мыши на пересечении столбца А и строки 1 и нажмем левую кнопку мыши);
• наберем на клавиатуре «План производства»;
• нажмем Enter.
16. Введем в ячейки: А2-Код детали , В2- Номер цеха, С2-Код оборудования , D2- Плановое количество деталей за квартал, Е2- Плановое количество деталей за месяц, F2-Код профессии , G2- Разряд работы, H2-Норма штучно-калькуляционная на деталь.
17. Организуем контроль вводимых данных в колонку «Код детали», ограничив значения интервалом 10-99:
• выделим ячейки А3-А30;
• выполним команду «Проверка…» меню «Данные»;
• в поле «Тип данных» выберем «Целое число»;
• зайдем в поле «Минимум»: 10;
• зайдем в поле «Максимум»: 99;
• выберем закладку «Сообщение для ввода»;
• введем в поле «Заголовок»- Ограничения для ввода кода, в поле «Сообщение»- Код детали может принимать значения 10-99.
• выберем закладку «Сообщение об ошибке»;
• в поле «Вид» выберем «Останов». В случае ввода ошибочных данных на экран монитора выводится сообщение: Код детали может принимать значения 10-99 .
18. Организуем контроль вводимых данных в колонку «Код профессии», ограничив значения интервалом 1-6:
• выделим ячейки В3-В30;
• выполним команду «Проверка…» меню «Данные»;
• в поле «Тип данных» выберем «Целое число»;
• зайдем в поле «Минимум»: 1;
• зайдем в поле «Максимум»: 6;
• выберем закладку «Сообщение для ввода»;
• введем в поле «Заголовок»- Ограничения для ввода кода, в поле «Сообщение»- Код профессии может принимать значения 1-6.
• выберем закладку «Сообщение об ошибке»;
• в поле «Вид» выберем «Останов». В случае ввода ошибочных данных на экран монитора выводится сообщение: Код профессии может принимать значения 1-6.
19. Введем исходные данные (рис. 2.4.).

Рис. 2.4. Исходная таблица

20. Заполним графу «Норма штучно-калькуляционная на деталь» в соответствии с кодом детали:
• сделаем ячейку Н3 активной;
• воспользуемся командой «Функция…» меню «Вставка»;
• в поле «Категория» выберем «Ссылки и массивы»;
• в поле «Выберите функцию» выберем «ВПР» (рис. 2.5.);
• нажмем «ОК»;

Рис. 2.5. Вид первого окна мастера функций

• введем информацию в поле «Искомое значение», щелкнув по ячейке А3;
• введем информацию в поле «Таблица»;
• воспользуемся командой «Имя» из меню «Вставка»;
• используем команду «Вставить»;
• выделим «Имя: Код детали»;
• нажмем кнопку «ОК»;
• введем информацию в поле «Номер_столбца» - 2;
• введем информацию в поле «Интервальный просмотр» - 0 (рис. 2.6.);

Рис. 2.6. Вид второго окна мастера  функций

• нажмем кнопку «ОК».

21. Скопируем формулу в ячейки Н4-Н12 (рис. 2.7.):

• сделаем ячейку Н3 активной;
• установим курсор на маркер в правом нижнем углу;
• сделаем двойной щелчок левой кнопкой мыши.

Рис. 2.7. Копирование формул

22. Заполним графу «Разряд работы» в соответствии с кодом профессии:
• сделаем ячейку G3 активной;
• воспользуемся командой «Функция…» меню «Вставка»;
• в поле «Категория» выберем «Ссылки и массивы»;
• в поле «Выберите функцию» выберем «ВПР»;
• нажмем «ОК»;
• введем информацию в поле «Искомое значение», щелкнув по ячейке F3;
• введем информацию в поле «Таблица»;
• воспользуемся командой «Имя» из меню «Вставка»;
• используем команду «Вставить»;
• выделим «Имя: Код профессии»;
• нажмем кнопку «ОК»;
• введем информацию в поле «Номер_столбца» - 2;
• введем информацию в поле «Интервальный просмотр» - 0 (рис. 2.8.);