Проведение АВС-анализа в Microsoft Excel

Пусть в качестве объекта анализа выбран товар. В качестве классификационного признака выбрано количество сделок с товаром. Тогда алгоритм проведения АВС-анализа в Excel следующий. 

1. Создайте в Excel таблицу, как показано на рис. 5.1. 

Рис. 5.1. Таблица Excel для ABC-анализа

2. Внесите в столбец «Наименование товара» наименования товаров.

3. Внесите в столбец «Количество сделок за период» количество сделок по соответствующему наименованию товара за выбранный период.

4. Внесите в столбец «№ по порядку» номера товаров по порядку. Для этого в первую строку столбца следует внести цифру 1. Затем при нажатой левой кнопке мыши необходимо выделить столбец (до последней значащей строки). Не снимая выделения, в меню «Правка» - «Заполнить» выберите пункт «Прогрессия». В открывшемся окне «Прогрессия» (рис. 5.2) укажите в поле «Предельное значение:» заведомо избыточное количество строк, например 999, нажмите на «ОК». Столбец пронумерован.

Рис. 5.2. Окно «Прогрессия»

5. В строке «Итого» столбца С («Количество сделок за период») вычислите общее количество сделок. Для этого установите табличный курсор в требуемую ячейку и щелкните на инструменте Σ (автосумма) на панели «Стандартная», а затем на «Ввод». В ячейке отобразится сумма столбца. Если стать на ячейку, в строке формул отобразится формула =СУММ (С2 : Сххх) , где Сххх — нижняя граница диапазона суммирования. Следует убедиться, что диапазон суммирования (С2:Сххх) указан верно.

6. В первую строку столбца «Доля в общем кол-ве сделок» внесите формулу вида: =С2/$С$ххх, где ячейка Сххх — это ячейка, содержащая общее количество сделок по всем товарам за выбранный период.

7. Скопируйте формулу =С2/$С$ххх в расположенные ниже ячейки. Для этого установите табличный курсор на ячейку «С2», затем поместите курсор мыши на небольшой черный квадрат, расположенный в правом нижнем углу табличного курсора (курсор мыши примет вид «+»), и дважды щелкните на нем мышью.

8. Необходимо упорядочить доли в общем объеме сделок по убыванию. Для этого выделите всю таблицу, включая подписи столбцов, но без итоговых сумм. Затем в меню «Данные» выберите пункт «Сортировка». Откроется окно «Сортировка диапазона» (рис. 5.3). В этом окне необходимо установить переключатель • «Идентифицировать поля по подписям...», а в поле «Сортировать по...» — «Доля в общем кол-ве сделок». Затем установить переключатель • «по убыванию» и нажать «ОК». Осуществится сортировка.

Рис. 5.3. Окно «Сортировка диапазона»

9. Введите в первую ячейку столбца «Доля в общем кол-ве сделок нарастающим итогом» формулу =D2. В следующую за ней ячейку — формулу = D3+Е2 и скопируйте ее до последней значащей строки.

10. Разбейте товар на группы (А, В, С), указав соответствующую букву в столбце «Группа». Разбиение на группы проще проводить вручную, но можно и автоматизировать, используя формулы. Например, воспользовавшись формулой:

=ЕСЛИ (Е2<=0,7;"А";ЕСЛИ(Е2<=0,9;"В";"С")).

11. В столбце «Доля в ассортименте нарастающим итогом» рассчитайте долю объектов анализа нарастающим итогом. Один из вариантов расчета: в ячейку «G2» занесите формулу =1/СЧЁТ3(F2:Fxx); в ячейку «G3» занесите формулу =G2+1/CЧЁТ3 (F$2:F$xx) и скопируйте ее в ячейки, расположенные ниже. Формула СЧЁТ3 считает непустые ячейки. При этом не важно, цифры или буквы в них содержатся.

Пример таблицы Excel с осуществленным АВС-анализом представлен на рис. 5.4.

Рис. 5.4. Таблица Excel с ABC-анализом

Внимание!

Для выделения таблицы удобно воспользоваться комбинациями «горячих» клавиш:

CTRL+SHIFT+HOME — расширить выделенную область до начала листа;

CTRL+SHIFT+END — расширить выделенную область до правой нижней ячейки листа;

CTRL+SHIFT+* — выделить текущую область.

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

Рис. 5.5. Таблица с итогами АВС-анализа

Источник: Логистика управления запасами с помощью Excel / авт.-сост. В. И. Копыл. — Минск: Харвест, 2007.— С. 21-24 (64 с.)