Лабораторный практикум по информатике
.pdfРис. 42 Распределение мероприятий, запланированных по состоявшимся контактам
352
1.Задайте высоту строк с 1 по 58 равной 9, ширину столбцов с A по AA – 4.
Задайте заливку ячеек в диапазоне A1 по AA1 черным цветом, в ячейке A1 задайте текст «Пример ABC-анализа продаж реального торгового предприятия за 2007 г.»
2.В ячейке A3 задайте подпись для таблицы «Матрица ABC- распределения товарных групп» (шрифт – 8, жирный).
3.Используя команду объединения ячеек, создайте шапку таблицы «Матрица ABC-распределения товарных групп» в диапазоне ячеек A4 – G6 (рис. Б46). Границы ячеек шапки таблицы в виде тонкой линии, заливка ячеек шапки таблицы – серым цветом. Текст в таблице оформлен шрифтом Arial Cyr, размер шрифта – 6.
4.Заполните таблицу «Матрица ABC-распределения товарных групп» в соответствии с рис. Б46. Измените форматирование границ ячеек таблицы – внешние границы в виде тонкой линии, внутренние границы – пунктир. Будьте внимательны, форматирование таблицы должно строго соответствовать рис. Б45.
5.В ячейке I3 задайте подпись для таблицы «Общая статистика ABC-распределения».
6.В диапазоне ячеек I4 - P14, используя команды объединения и форматирования ячеек, заполните таблицу «Общая статистика ABC- распределения» (рис. Б47).
7.В ячейке R3 задайте подпись для графика «Гистограмма ABC- распределения».
8.Объедините ячейки в диапазоне R4 – AA14, измените формат образовавшейся области: границы области в виде тонкой линии. Для построения графика «Гистограмма ABC-распределения» воспользуйтесь Мастером диаграмм (рис. 48). Тип диаграммы – обычная гистограмма. Гистограмма содержит два ряда, имена которых Сумма продаж и Кол-во групп. В качестве подписей данных по оси X – столбец ABC группа, значения Y для ряда Сумма продаж – Сумма продаж.доля и для ряда Кол-во групп – Кол-во групп.доля. Будьте внимательны при вводе источников данных, не выделяйте столбец целиком – диапазон должен быть составным, для его формирования необходимо выделять каждую ячейку столбца в отдельности, удерживая клавишу CTRL. На графике должны присутствовать все основные линии сетки и легенда.
356
|
№ п/п |
Группа |
Продажи за 2007 г. |
ABC-анализ |
|||
n |
|
% |
товаров |
всего |
доля |
н.итог |
группа |
1 |
|
2 |
3 |
4 |
5 |
6 |
7 |
1 |
|
2,08% |
Товарная группа 1 |
9 930 954 |
29,19% |
29,19% |
A |
2 |
|
4,17% |
Товарная группа 2 |
5 333 496 |
15,68% |
44,87% |
A |
3 |
|
6,25% |
Товарная группа 7 |
2 229 750 |
6,55% |
51,43% |
B |
4 |
|
8,33% |
Товарная группа 18 |
1 738 947 |
5,11% |
56,54% |
B |
5 |
|
10,42% |
Товарная группа 5 |
1 671 191 |
4,91% |
61,45% |
B |
6 |
|
12,50% |
Товарная группа 4 |
1 570 030 |
4,62% |
66,06% |
B |
7 |
|
14,58% |
Товарная группа 13 |
1 204 160 |
3,54% |
69,60% |
B |
8 |
|
16,67% |
Товарная группа 11 |
1 082 733 |
3,18% |
72,79% |
B |
9 |
|
18,75% |
Товарная группа 20 |
658 712 |
1,94% |
74,72% |
B |
10 |
|
20,83% |
Товарная группа 22 |
649 660 |
1,91% |
76,63% |
B |
11 |
|
22,92% |
Товарная группа 15 |
629 438 |
1,85% |
78,48% |
B |
12 |
|
25,00% |
Товарная группа 14 |
627 046 |
1,84% |
80,33% |
C |
13 |
|
27,08% |
Товарная группа 21 |
578 260 |
1,70% |
82,03% |
C |
14 |
|
29,17% |
Товарная группа 37 |
567 752 |
1,67% |
83,70% |
C |
15 |
|
31,25% |
Товарная группа 25 |
480 911 |
1,41% |
85,11% |
C |
16 |
|
33,33% |
Товарная группа 38 |
454 066 |
1,33% |
86,44% |
C |
17 |
|
35,42% |
Товарная группа 17 |
443 370 |
1,30% |
87,75% |
C |
18 |
|
37,50% |
Товарная группа 29 |
417 569 |
1,23% |
88,97% |
C |
19 |
|
39,58% |
Товарная группа 26 |
381 785 |
1,12% |
90,10% |
C |
20 |
|
41,67% |
Товарная группа 28 |
371 450 |
1,09% |
91,19% |
C |
21 |
|
43,75% |
Товарная группа 58 |
340 250 |
1,00% |
92,19% |
C |
22 |
|
45,83% |
Товарная группа 42 |
318 329 |
0,94% |
93,12% |
C |
23 |
|
47,92% |
Товарная группа 41 |
276 068 |
0,81% |
93,94% |
C |
24 |
|
50,00% |
Товарная группа 33 |
176 752 |
0,52% |
94,46% |
C |
25 |
|
52,08% |
Товарная группа 56 |
162 840 |
0,48% |
94,93% |
C |
26 |
|
54,17% |
Товарная группа 54 |
161 318 |
0,47% |
95,41% |
C |
27 |
|
56,25% |
Товарная группа 72 |
147 630 |
0,43% |
95,84% |
C |
28 |
|
58,33% |
Товарная группа 39 |
136 335 |
0,40% |
96,24% |
C |
29 |
|
60,42% |
Товарная группа 61 |
131 323 |
0,39% |
96,63% |
C |
30 |
|
62,50% |
Товарная группа 30 |
109 629 |
0,32% |
96,95% |
C |
31 |
|
64,58% |
Товарная группа 67 |
85 414 |
0,25% |
97,20% |
C |
32 |
|
66,67% |
Товарная группа 79 |
85 288 |
0,25% |
97,45% |
C |
33 |
|
68,75% |
Товарная группа 23 |
83 781 |
0,25% |
97,70% |
C |
34 |
|
70,83% |
Товарная группа 76 |
83 575 |
0,25% |
97,95% |
C |
35 |
|
72,92% |
Товарная группа 74 |
80 020 |
0,24% |
98,18% |
C |
36 |
|
75,00% |
Товарная группа 77 |
72 780 |
0,21% |
98,39% |
C |
37 |
|
77,08% |
Товарная группа 66 |
70 253 |
0,21% |
98,60% |
C |
38 |
|
79,17% |
Товарная группа 87 |
56 240 |
0,17% |
98,77% |
C |
39 |
|
81,25% |
Товарная группа 82 |
53 035 |
0,16% |
98,92% |
C |
40 |
|
83,33% |
Товарная группа 27 |
48 741 |
0,14% |
99,07% |
C |
41 |
|
85,42% |
Товарная группа 99 |
46 965 |
0,14% |
99,20% |
C |
42 |
|
87,50% |
Товарная группа 64 |
43 095 |
0,13% |
99,33% |
C |
43 |
|
89,58% |
Товарная группа 129 |
40 442 |
0,12% |
99,45% |
C |
44 |
|
91,67% |
Товарная группа 103 |
40 270 |
0,12% |
99,57% |
C |
45 |
|
93,75% |
Товарная группа 83 |
39 949 |
0,12% |
99,69% |
C |
46 |
|
95,83% |
Товарная группа 95 |
38 740 |
0,11% |
99,80% |
C |
47 |
|
97,92% |
Товарная группа 36 |
35 190 |
0,10% |
99,90% |
C |
48 |
|
100,0% |
Товарная группа 106 |
33 212 |
0,10% |
100,0% |
C |
ИТОГО |
|
34 018 744 |
100% |
- |
|
Рис. 46 Заполнение таблицы «Матрица ABC-распределения товарных групп»
Измените размеры диаграммы так, чтобы она располагалась поверх диапазона ячеек R4 – AA14.
357
|
ABC |
|
|
Доля |
|
|
Количество тов.групп |
|
|
Сумма продаж |
|
||||||||
|
|
|
|
|
|
|
|
||||||||||||
|
группа |
|
|
продаж |
|
|
всего |
|
доля |
|
н.итог |
|
|
всего |
|
доля |
|
н.итог |
|
|
1 |
|
|
2 |
|
|
3 |
|
4 |
|
5 |
|
|
6 |
|
7 |
|
8 |
|
|
|
|
50% |
|
|
|
|
|
|
|
|
15 |
|
|
|
|
|
||
|
A |
|
2 |
|
4,2% |
|
4,2% |
|
264 |
|
44,9% |
|
44,9% |
|
|||||
|
|
общ.суммы |
|
|
|
|
|
|
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
450 |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
30% |
|
|
|
|
|
|
|
|
11 |
|
|
|
|
|
||
|
B |
|
9 |
|
18,8% |
|
22,9% |
|
434 |
|
33,6% |
|
78,5% |
|
|||||
|
|
общ.суммы |
|
|
|
|
|
|
|||||||||||
|
|
|
|
|
|
|
|
|
|
|
621 |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
C |
20% |
|
37 |
|
77,1% |
|
100,0% |
7 319 |
|
21,5% |
|
100,0% |
||||||
|
|
|
|
общ.суммы |
|
|
|
|
|
|
|
673 |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
34 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
ИТОГО |
|
|
3 сегмента |
|
|
48 |
|
100,0% |
|
- |
|
|
018 |
|
100,0% |
|
- |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
744 |
|
|
|
|
|
Рис. 47 Оформление таблицы «Общая статистика ABC-распределения»
При выделении диапазона удерживайте клавишу
CTRL
Рис. 48 Создание гистограммы ABC-распределения
358
Измените форматирование полученной гистограммы: рамка области гистограммы должна быть невидимой, переместите легенду в верхний правый угол, растяните область построения диаграммы на всю область гистограммы, измените формат осей гистограммы и формат легенды – шрифт – 6, измените шкалу оси абсцисс – число категорий между делениями – 2. Измените форматирование рядов данных – заливку и ширину зазора.
ЗначениеXXдля рядовAAииBB соответственнответственно
Значение Y для рядов A и B соответственно
Значение Y для ряда Кривая Лоренца
Значение X для рядов Кривая Лоренца и Диагонали, значение Y для ряда Диагональ
Рис. 49 Создание графика «Кривая Лоренца»
359
9. В ячейке I16 задайте подпись «Кривая Лоренца», а в ячейке O16 задайте подпись «Индекс Джини G=», а в ячейке P16 задайте рассчитанное значение индекса Джини** – 72%.
10. Объедините ячейки в диапазоне I17 – P41, задайте границы выделенной области в виде тонкой линии.
11.Создайте график «Кривая Лоренца», используя Мастер диаграмм (рис. 49).
Тип графика – Точечный. Последовательно создайте четыре ряда с именами «Кривая Лоренца-Парето», «Диагональ», «A» и «B». Выбор источников данных для графиков представлен на рис. 49. На графике должна присутствовать легенда и все основные линии сетки. Измените формат диаграммы – рамка диаграммы должна быть невидимой - и размеры диаграммы так, чтобы она полностью занимала область I17 – P41. Измените формат осей графика – шрифт подписей – 6, формат числа – Процентный, число знаков после запятой – 0. Измените формат рядов данных так, чтобы они соответствовали рис. 59 (если Вы не можете изменить формат точек A
иB на графике воспользуйтесь областью легенды – дважды щелкните на условном изображении ряда и измените его формат – формат на графике изменится автоматически). Измените положение легенды на графике и растяните область построения гистограммы на весь график.
12.В ячейке R16 разместите подпись «Динамика индекса Джини в 2005-2007 гг.»
13.В диапазоне ячеек R17 – AA41 разместите график «Динамика индекса Джини в 2005-2007 гг.», который должен быть построен с использованием Мастера диаграмм (рис. 51). Для формирования этого графика в диапазоне ячеек R41 - AA41 разместите таблицу, заполненную в соответствии с рис. Рис 50.
Значения индекса |
|
|
|
Джини: |
2005 г. |
70,40% 2006 г. 70,82% 2007 г. |
72,34% |
Рис. 50 Заполнение вспомогательной таблицы для графика «Динамика индекса Джини в 2005-2007 гг.»
14. Добавьте линейную линию тренда на график «Динамика индекса Джини в 2005-2007 гг.».
** Индекс Джини - это статистический показатель, с помощью которого можно описывать равномерность изменения одной величины относительно изменения другой. Основным применением индекса Джини является оценка неравномерности распределения изучаемого признака. Индекс Джини численно равен площади под кривой Лоренца
360