Часть 5. ABC-анализ с использованием фильтров

В практической работе на компьютере часто требуется готовить, сортировать и фильтровать различные списки. Сортировка (упорядочивание по возрастанию или убыванию) осуществляется через пункт меню Д анныеàС о ртировка… Предварительно всю группу сортируемых ячеек/столбцов следует выделить мышью (разберите самостоятельно).

 

Здесь рассмотрим использование фильтров Excel для оперативного получения оценочных данных на примере задачи ABC-анализа. Суть ABC-анализа состоит в разбиении всех товаров (клиентов) по важности на три группы (A, B и C) по некоторому показателю – объему продаж, прибыли и т.д.

ABC-анализ строится на законе Парето, который гласит, что, как правило, 80% всего объема продаж (прибыли) фирмы достигается за счет не более, чем 20% товаров (клиентов), и, наоборот, 80% всех товаров (клиентов) дают не более 20% оборота.

В связи с этим все товары (клиентов) можно разбить на 3 группы:

· A – товары (клиенты), дающие 80% прибыли. В группу А попадают те, которые в основном определяют обороты фирмы;

· C – 80% товаров (клиентов) с наименьшей важностью, доля участия которых в обороте фирмы невелика;

· В – все остальные товары (клиенты), занимающие промежуточное положение между группами A и С, если группы А и С не пересекаются.

 

Пусть имеются данные по отгрузке некоторых товаров за месяц, приведенные на рис. 23. Необходимо определить суммы (и %) отгрузки, которые дают 80% товаров с наименьшей важностью (группа С) и 20% товаров с наибольшей важностью.

Определим сумму отгрузки, которую дают 80% товаров с наименьшей важностью (группа С). Для этого проделайте следующие действия:

 

Рис. 23

 

1. В ячейку В31 запишите формулу =СУММ(B3:B29) – общая сумма отгрузки будет равна 9346500 руб.

2. Установите фильтр на данные, т.е. выделите диапазон ячеек А2:В29 и выберите Д анныеà Ф ильтрà А втофильтр. В строке 2 появятся кнопки списков (как на рис. 24).

3. В списке Сумма (руб) выберите пункт (Первые 10…) как на рис. 24. В появившемся окне установите условие:

 

 

4. После нажатия ОК Вы получите 80% списка товаров, дающих минимальные объемы – рис. 24. Выделите отфильтрованный диапазон А5:А29 – после выделения в строке состояния (внизу) Вы увидите Сумму выделенной области – рис. 24.

 

Как видно, 80% товаров наименьшей важности дает отгрузку 3036500 руб., что составляет около 32.5% от общего объема продаж 9346500 руб.

Аналогично оцените объем продаж 20% наибольшей важности, применив следующее условие:

 

 

Вы должны получить сумму 5570000 руб., что составляет почти 60% от объема продаж и подтверждает правило Парето.

Снимите фильтр, еще раз выбрав пункт Д анныеà Ф ильтрà А втофильтр.

 

Рис. 24

Отобранные с помощью фильтра строки (ячейки) можно удалять, изменять формат отображения (цвет, шрифт и пр.) – при этом остальные, не отобранные строки (ячейки) остаются без изменения. Это удобно для выборочного изменения, удаления строк (ячеек) по условию.

 

Часть 6. Сводные таблицы

Электронные таблицы могут содержать большие группы данных, требующие некоторого обобщения и анализа. Для этих целей применяются сводные таблицы, которые позволяют обобщить данные и просмотреть срезы данных по некоторым критериям:

 

 

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

 

Рассмотрим простой пример, показывающий правила создания и манипулирования сводными таблицами.

Подготовьте исходные данные для построения сводной таблицы – группу ячеек таблицы Excel – как на рис. 25. Здесь приведены данные об объемах продаж телевизоров 3-х производителей в 3-х регионах за три месяца 2009 года. Важно отметить, что все столбцы имеют заголовки – наименования, которые будут активно использоваться в сводной таблице. Кроме того, данные в столбцах представлены в виде повторяющихся списков.

 

Рис. 25

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

 

Сводная таблица может иметь вид, как на рис. 26 слева. Как видно, в ней столбец Объемы продаж просуммирован по столбцам Регион и Товар по периодам/месяцам. Поля Период, Регион и Товар становятся измерениями куба и представляют собой раскрывающиеся списки, позволяют выбирать и просматривать срезы данных. Поле Объемы продаж составляет данные куба.

Справа от сводной таблицы (рис. 26) выведен список полей. Под сводной таблицей здесь выведена Панель инструментов Сводные таблицы.

Теперь создадим сводную таблицу из уже подготовленных исходных данных (рис. 25), используя Мастер сводных таблиц.

 

Рис. 26

 

Проделайте следующие действия:

1. Выделите диапазон ячеек (исходные данные) для сводной таблицы – A1:D28. Выберите пункт меню Д анныеàСвод н ая таблица…. Будет запущен Мастер.

2. В первом окне запрашиваются исходные данные для сводной таблицы – выберите пункт " в списке или базе данных Microsoft Office Excel " и нажмите кнопку Далее>.

3. Во втором окне можно скорректировать диапазон ячеек с исходными данными. Можно указать данные из другого файла. Нажмите Далее>.

4. В следующем окне (выберите опцию " новый лист ") непосредственно создается сводная таблица (рис. 27) – на макет просто перетаскиваются названия полей из списка полей сводной таблицы. Поочередно перетащите каждое название на макет, как показано на рис. 27 – сводная таблица готова на отдельном рабочем листе (рис. 26).

 

Рис. 27

 

Поработайте с созданной сводной таблицей. В частности, просмотрите срезы данных по месяцам, раскрыв поле со списком Период и т.д.

 

Возможно перетаскивание названий полей как на макет, так и с макета на список полей сводной таблицы. Таким образом, сводная таблица всегда может быть реорганизована.

Если вы случайно закрыли Панель инструментов Сводные таблицы, то для её отображения щёлкните правой кнопкой мыши по Панели инструментов Excel и в раскрывшемся списке выберите нужную панель.

 

Другим удобным инструментом компоновки макета сводной таблицы является сам Мастер. Есть два способа вызова Мастера:

· кнопкой Сводная таблица на Панели инструментов Сводные таблицы;

· правым щелчком мыши по сводной таблице вызвать меню и в нем пункт Мастер.

В обоих случаях будет вызвано окно Мастера – рис. 28. Здесь по кнопке Ма к ет будет вызвано окно компоновки макета сводной таблицы – рис. 29. Этот способ представляется более удобным для реорганизации сложных сводных таблиц с множеством полей.

По кн. Параметры можно изменять различные параметры сводной таблицы.

 

Рис. 28

 

Рис. 29

 

Мощным средством визуализации данных, анализа и выявления закономерностей являются диаграммы. Недаром говорят – "графика открывает данные". Поэтому для полученной сводной таблицы построим трехмерную диаграмму. Выполните следующие действия:

1. Щелкните по сводной таблице для ее выделения. На панели инструментов Сводные таблицы (рис. 26) нажмите кнопку Мастер диаграмм. Будет построена гистограмма с накоплением.

2. Измените тип гистограммы – еще раз нажмите кнопку Мастер диаграмм в появившемся окне выберите вид гистограмму – трехмерная гистограмма и нажмите Далее.

3. В следующем окне Мастера диаграмм нажмите Далее.

4. В следующем, последнем окне укажите, куда вы хотите поместить диаграмму – выберите радиокнопку отдельном. Нажмите кнопку Готово. Гистограмма будет преобразована в трехмерный вид, как на рис. 30. Списки на диаграмме позволяют выбирать и просматривать срезы данных так же, как на сводной таблице.

 

Рис. 30