Автоматизация учета материалов на складе с применением баз данных
ВВЕДЕНИЕ
С помощью программы MS
EXCEL необходимо создать
базу данных, которая бы упрощала работу экономистам, контролирующим цены на
материал на предприятии для изготовления деталей либо каких-то изделий.
Также эта база данных поможет и тем людям,
которые рассчитывают потребность в материалах на производственную программу. А
также для контроля за остатками материала на складах и их нормализации.
ИССЛЕДОВАТЕЛЬСКАЯ ЧАСТЬ
Создадим справочную таблицу «Сведения о деталях»
|
Сведения
о деталях
|
|
Склад
|
Деталь
|
Материал
|
Норма
расхода материала
|
Чис-тый
вес детали
|
Коэффициент
использования материала
|
Цена
матери-ала, руб
|
Стоимость
материала
|
|
Склад
металлов
|
Планка-1
|
Лист.сталь
|
0,45
|
0,35
|
0,78
|
23
|
10,35
|
|
Склад
металлов
|
Планка-2
|
Лист.сталь
|
1,2
|
0,98
|
0,82
|
23
|
27,6
|
|
Склад
металлов
|
Планка-3
|
Лист.сталь
|
0,6
|
0,48
|
0,80
|
23
|
13,8
|
|
Склад
металлов
|
Полоса
|
Лист.сталь
|
0,13
|
0,08
|
0,62
|
23
|
2,99
|
|
Склад
металлов
|
Труба-1
|
Труба
|
1,6
|
1,4
|
0,88
|
35
|
56
|
|
Склад
металлов
|
Вал
привода
|
Труба
|
5,9
|
5,4
|
0,92
|
35
|
206,5
|
|
Склад
металлов
|
Вал
главный
|
Труба
|
4
|
3,6
|
0,90
|
35
|
140
|
|
Склад
металлов
|
Венец
|
Бронза
|
3
|
2,1
|
0,70
|
350
|
1050
|
|
Склад
металлов
|
Втулка-1
|
Бронза
|
0,3
|
0,2
|
0,67
|
350
|
105
|
|
Склад
металлов
|
Шестерня
1
|
Отливка
СТ45Л
|
0,6
|
0,45
|
0,75
|
75
|
45
|
|
Склад
металлов
|
Шестерня
2
|
Отливка
СТ45Л
|
0,85
|
0,68
|
0,80
|
75
|
63,75
|
|
Склад
металлов
|
Шестерня
3
|
Отливка
СТ45Л
|
0,45
|
0,34
|
0,76
|
75
|
33,75
|
|
Склад
металлов
|
Втулка-2
|
Бронза
|
0,6
|
0,3
|
0,50
|
350
|
210
|
|
Склад
химии
|
Втулка
П1
|
Полиамид
|
0,61
|
0,52
|
0,85
|
210
|
128,1
|
|
Склад
химии
|
Крышка
|
Полистирол
|
0,54
|
0,49
|
0,91
|
105
|
56,7
|
|
Склад
химии
|
Труба-2
|
Полиэтилен
гран.
|
0,36
|
0,35
|
0,97
|
95
|
34,2
|
|
Склад
химии
|
Направляющая
|
Полиамид
|
0,26
|
0,24
|
0,92
|
210
|
54,6
|
|
Склад
покупных комплектующих изделий
|
Подшипник
|
|
1
|
|
|
132
|
132
|
|
Склад
покупных комплектующих изделий
|
Ремень
привод-ной
|
|
1
|
|
|
240
|
240
|
|
Склад
покупных комплектующих изделий
|
Двига-тель
М4
|
|
1
|
|
|
2560
|
2560
|
|
Склад
покупных комплектующих изделий
|
Датчик
|
|
1
|
|
|
425
|
425
|
|
Склад
покупных комплектующих изделий
|
Переключатель
|
|
1
|
|
|
236
|
236
|
|
Склад
покупных комплектующих изделий
|
Розетка
|
|
1
|
|
|
116
|
116
|
|
Склад
покупных комплектующих изделий
|
Вилка
|
|
1
|
|
|
85
|
85
|
У нас имеется 3 склада:
· Склад металлов
· Склад химии
· Склад покупных комплектующих изделий
Где изготавливаются следующие детали:
1. Планка-1
2. Планка-2
. Планка-3
. Полоса
. Труба-1
. Вал привода
. Вал главный
. Венец
. Втулка-1
. Втулка-2
. Шестерня 1
. Шестерня 2
. Шестерня 3
. Втулка П-1
. Крышка
. Труба-2
. Направляющая
А также закупаются следующие комплектующие:
1. Подшипник
2. Ремень приводной
. Двигатель М
. Датчик
. Переключатель
. Розетка
. Вилка
Для их изготовления используются разные типы
материалов, как показано в таблице: листовая сталь, труба, бронза, отливка
СТ45Л, полиамид, полистирол, полиэтилен гранулированный.
Для каждой детали установлены следующие
показатели: норма расхода и чистый вес. По этим показателям мы найдем
коэффициент использования, который находится по формуле: =ячейка чистого веса
делится на ячейку нормы расхода, а далее протягивается до конца таблицы.
Также в нашей таблице покажем цену 1кг материала
и таким образом найдем стоимость материала по формуле: =ячейку цены материала
умножим на ячейку норма расхода материала, и протягиваем до конца таблицы.
Для столбца «Склад» мы создадим список, таким
образом, мы сможем просмотреть какие именно детали и из какого материала
создаются на данном складе. А также аналогичный список мы создадим для столбца
«Материалы», что позволит нам просматривать для каких именно деталей
используется данный вид материала и в каком количестве, а также на какую сумму.
Также по данным из таблицы «Сведения о деталях»
построим диаграмму «Коэффициент использования материала».
При построении выделяем столбец «Коэффициент
использования», а по оси Х - столбец «Деталь». На этой диаграмме можно
посмотреть у какой из деталей коэффициент использования выше, а у какой самый
низкий.
Так как мы все таблицы располагаем на одном
листе, и 2 из них очень большие, то мы закрепим область ( выделим ячейку первой
таблицы «Норма расхода материала» и с помощью Окно\Закрепить область)
Теперь мы создадим еще одну вспомогательную
таблицу, которую назовем «Привод. Спецификация». В ней будут указаны:
· Деталь
· Применяемость для сборки привода
|
Привод.
Спецификация
|
|
Деталь
(1шт)
|
Применяемость
|
|
Вал
привода
|
1
|
|
Шестерня
1
|
1
|
|
Шестерня
2
|
1
|
|
Шестерня
3
|
1
|
|
Подшипник
|
2
|
|
Втулка-1
|
2
|
|
Двигатель
М4
|
1
|
|
Переключатель
|
1
|
|
Ремень
приводной
|
2
|
А теперь перейдем к основной таблице, которая
называется «Расчет потребности в материалах на производственную программу»
|
Расчет
потребности в материалах на производственную программу
|
|
1
квартал
|
2
квартал
|
3
квартал
|
4
квартал
|
Год
|
|
Деталь
(1шт)
|
Материал
|
Норма
расхода
|
План
производства, шт
|
Потреб
ность, кг
|
План
производства, шт
|
Потребность,
кг
|
План
производства, шт
|
Потребность,
кг
|
План
производства, шт
|
Потребность,
кг
|
План
производства, шт
|
Потребность,
кг
|
|
Привод
|
|
|
15000
|
|
|
|
|
|
|
|
|
|
|
Вал
привода
|
Труба
|
5,9
|
15000
|
88500
|
16500
|
97350
|
17325
|
102218
|
16805
|
99151
|
65630
|
387218
|
|
Шестерня
1
|
Отливка
СТ45Л
|
0,6
|
15000
|
9000
|
16500
|
9900
|
17325
|
10395
|
16805
|
10083
|
65630
|
39378
|
|
Шестерня
2
|
Отливка
СТ45Л
|
0,85
|
15000
|
12750
|
16500
|
14025
|
17325
|
14726
|
16805
|
14284
|
65630
|
55786
|
|
Шестерня
3
|
Отливка
СТ45Л
|
0,45
|
15000
|
6750
|
16500
|
7425
|
17325
|
7796
|
16805
|
7562
|
65630
|
29534
|
|
Подшипник
|
|
1
|
30000
|
30000
|
33000
|
33000
|
34650
|
34650
|
33611
|
33611
|
131261
|
131261
|
|
Втулка-1
|
Бронза
|
0,3
|
9000
|
33000
|
9900
|
34650
|
10395
|
33611
|
10083
|
131261
|
39378
|
|
Двигатель
М4
|
|
1
|
15000
|
15000
|
16500
|
16500
|
17325
|
17325
|
16805
|
16805
|
65630
|
65630
|
|
Переключатель
|
|
1
|
15000
|
15000
|
16500
|
16500
|
17325
|
17325
|
16805
|
16805
|
65630
|
65630
|
|
Ремень
приводной
|
|
1
|
30000
|
30000
|
33000
|
33000
|
34650
|
34650
|
33611
|
33611
|
131261
|
131261
|
|
Планка-1
|
Лист.сталь
|
0,45
|
35000
|
15750
|
38500
|
17325
|
40425
|
18191
|
39212
|
17646
|
153137
|
68912
|
|
Планка-2
|
Лист.сталь
|
1,2
|
40000
|
48000
|
44000
|
52800
|
46200
|
55440
|
44814
|
53777
|
175014
|
210017
|
|
Планка-3
|
Лист.сталь
|
0,6
|
45200
|
27120
|
49720
|
29832
|
52206
|
31324
|
50640
|
30384
|
197766
|
118659
|
|
Полоса
|
Лист.сталь
|
0,13
|
54000
|
7020
|
59400
|
7722
|
62370
|
8108
|
60499
|
7865
|
236269
|
30715
|
|
Труба-1
|
Труба
|
1,6
|
25000
|
40000
|
27500
|
44000
|
28875
|
46200
|
28009
|
44814
|
109384
|
175014
|
|
Вал
главный
|
Труба
|
4
|
1520
|
6080
|
1672
|
6688
|
1756
|
7022
|
1703
|
6812
|
6651
|
26602
|
|
Венец
|
Бронза
|
3
|
6352
|
19056
|
6987
|
20962
|
7337
|
22010
|
7116
|
21349
|
27792
|
83377
|
|
Втулка-2
|
Бронза
|
0,6
|
78000
|
46800
|
85800
|
51480
|
90090
|
54054
|
87387
|
52432
|
341277
|
204766
|
|
Втулка
П1
|
Полиамид
|
0,61
|
80000
|
48800
|
88000
|
53680
|
92400
|
56364
|
89628
|
54673
|
350028
|
213517
|
|
Крышка
|
Полистирол
|
0,54
|
100020
|
54011
|
110022
|
59412
|
115523
|
62382
|
112057
|
60511
|
437623
|
236316
|
|
Труба-2
|
Полиэтилен
гран.
|
0,36
|
52000
|
18720
|
57200
|
20592
|
60060
|
21622
|
58258
|
20973
|
227518
|
81907
|
|
Направляющая
|
Полиамид
|
0,26
|
64000
|
16640
|
70400
|
18304
|
73920
|
19219
|
71702
|
18643
|
280022
|
72806
|
|
Итого:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Бронза
|
|
3,9
|
114352
|
74856
|
125787
|
82342
|
132077
|
86459
|
128114
|
83865
|
500330
|
327521
|
|
Лист.сталь
|
|
2,38
|
174200
|
97890
|
191620
|
107679
|
201201
|
113063
|
195165
|
109671
|
762186
|
428303
|
|
Отливка
СТ45Л
|
|
1,9
|
45000
|
28500
|
49500
|
31350
|
51975
|
32918
|
50416
|
31930
|
196891
|
124697
|
|
Полиамид
|
|
0,87
|
144000
|
65440
|
158400
|
71984
|
166320
|
75583
|
161330
|
73316
|
630050
|
286323
|
|
Полистирол
|
|
0,54
|
100020
|
54011
|
110022
|
59412
|
115523
|
62382
|
112057
|
60511
|
437623
|
236316
|
|
Полиэтилен
гран.
|
|
0,36
|
52000
|
18720
|
57200
|
20592
|
60060
|
21622
|
58258
|
20973
|
227518
|
81907
|
|
Труба
|
|
11,5
|
41520
|
134580
|
45672
|
148038
|
47956
|
155440
|
46517
|
150777
|
181665
|
588835
|
В этой таблице мы покажем план производства по
кварталам и выведем годовую, а также ниже выведем итоговую табличку по
материалам, для которой также по столбцу «Материалы» создадим список. За счет
всего сделанного ранее, мы получим итоговые цифры по каждому материалу.
Первый столбец мы получим за счет ссылок на 1-ую
таблицу (например для первой строчки: = B9, тем самым нам программа выдаст: Вал
привода), таким же образом получим и второй столбец «Материалы» и третий
столбец «Норма расхода».
Так как мы собираем привод, то детали для его
сборки берем кратные количеству приводов, которые на собрать по каждому из
кварталов. Таким образом, например деталь считается следующим образом
=количество приводов умножается на количество деталей, которые указаны в
спецификации по каждому виду деталей.
Планы производства мы выставляем сами согласно
контрактам, а потребность в кг считается следующим образом: норма расхода
умножается на план производства.
По этой таблице построим диаграмму с областями,
где по оси Y мы прописываем по
очереди план производства по кварталам по всем материалам, в итоге на графике
мы наглядно увидим годовые показатели. По оси Х прописываем материалы.
база данных автоматизация
бухгалтерия логистическая
В заключение мы создадим следующую таблицу
«Нормирование складских запасов». В этой таблице у нас следующие столбцы:
· Склад
· Материал
· Комплектующие
И по кварталам:
· Наличие на начало квартала
· Планируемая закупка
Столбцы «Материал» и «Комплектующие» имеют
ссылку на предыдущую таблицу и ее итоговую часть. Наличие на 1 января мы
выставим сами, а планируемая закупка считается следующим образом для всех
строк: план производства (кг) минус наличие (кг) и плюс 20%. Таким образом, для
материалов мы берем итоговую цифру по каждому виду материалов из дополнительной
часть таблицы №3, а для комплектующих берем цифру, необходимую для сборки
привода. Для последующего нахождения наличия на начало квартала находится
следующим образом: наличие на начало предыдущего квартала плюс планируемая
закупка минус план производства. Все эти формулы имеют связь между этими 2-мя
таблицами с помощью необходимых ячеек.
|
Нормирование
складских запасов
|
|
|
|
1квартал
|
2
Квартал
|
3квартал
|
4квартал
|
|
Склад
|
Материал
|
Комплектующие
|
Наличие
на 1 января
|
Планируемая
закупка
|
Остаток
на 1апреля
|
Планируемая
закупка
|
Остаток
на 1июля
|
Планируемая
закупка
|
Остаток
на 1октября
|
Планируемая
закупка
|
|
Склад
металлов
|
Бронза
|
|
53000
|
26227
|
4371
|
93564
|
15594
|
85038
|
14173
|
83630
|
|
Склад
металлов
|
Лист.сталь
|
93440
|
15573
|
110527
|
18421
|
113570
|
18928
|
108891
|
|
Склад
металлов
|
Отливка
СТ45Л
|
|
2310
|
31428
|
5238
|
31334
|
5222
|
33234
|
5539
|
31669
|
|
Склад
химии
|
Полиамид
|
|
31200
|
41088
|
6848
|
78163
|
13027
|
75067
|
12511
|
72965
|
|
Склад
химии
|
Полистирол
|
|
18752
|
42311
|
7052
|
62832
|
10472
|
62293
|
10382
|
60155
|
|
Склад
химии
|
Полиэтилен
гран.
|
|
2410
|
19572
|
3262
|
20796
|
3466
|
21787
|
3631
|
20810
|
|
Склад
металлов
|
Труба
|
|
12354
|
146671
|
24445
|
148311
|
24719
|
156866
|
26144
|
149559
|
|
Склад
покупных комплектующих
|
|
Подшипник
|
0
|
36000
|
6000
|
32400
|
5400
|
35100
|
5850
|
33313
|
|
Склад
покупных комплектующих
|
|
Ремень
приводной
|
0
|
36000
|
6000
|
32400
|
5400
|
35100
|
5850
|
33313
|
|
Склад
покупных комплектующих
|
|
Двигатель
М4
|
0
|
18000
|
3000
|
16200
|
2700
|
17550
|
2925
|
16656
|
|
Склад
покупных комплектующих
|
|
Датчик
|
0
|
0
|
0
|
0
|
|
|
|
|
|
Склад
покупных комплектующих
|
|
Переключатель
|
0
|
18000
|
3000
|
16200
|
2700
|
17550
|
2925
|
16656
|
|
Склад
покупных комплектующих
|
|
Розетка
|
0
|
0
|
0
|
|
|
|
|
|
|
Склад
покупных комплектующих
|
|
Вилка
|
0
|
0
|
0
|
|
|
|
|
|
На основании этой таблицы постоим график
«Соотношение наличия на начало квартал и планируемой закупки» для 1 квартала,
аналогично можно построить графики для других кварталов.
ЗАКЛЮЧЕНИЕ
база данные таблица диаграмма материал
Такая база данных помогает упорядочить все
сведения о деталях: из чего они сделаны, какая норма расхода материала, какой
чистый вес детали получается, определить коэффициент использования. А самое
главное: при разработке какой-либо производственной задачи можно сразу
определить какой материал и в каком количестве нужен для производства
определенных видов деталей. Также на производстве производятся не только
детали, но и делаются различные сборки (станков, узлов, каких-либо частей и
т.д.), для чего нам необходимо знать, из чего состоит этот узел, и как он
повлияет на производственную программу. Также мы упростили задачу менеджеров по
логистике, которые должны определить сколько материала имеется на складе и
сколько необходимо закупать, возможность это все просматривать в реальном
времени и в перспективе.
ЛИТЕРАТУРА
1. Джошуа
С., Носситер А. Использование Microsoft Excel 2003 // «Диалектика»,
Киев-Москва, 2003 г
2. Симонович
С.В., Евсеев Г.А., Алексеев А.Г. Специальная информатика: Учебное пособие. -
М.: АСТ-Пресс: Инфорком-Пресс, 2000. - 480с.
. Методические
указания. Автоматизированные информационно-управляющие системы. ЧИ МГОУ
Чебоксары 2008.