Одним из ключевых методов менеджмента и логистики является ABC-анализ. С его помощью можно классифицировать ресурсы предприятия, товары, клиентов и т.д. по степени важности. При этом по уровню важности каждой вышеперечисленной единице присваивается одна из трех категорий: A, B или C. Программа Excel имеет в своем багаже инструменты, которые позволяют облегчить проведение такого рода анализа. Давайте разберемся, как ими пользоваться, и что же собой представляет ABC-анализ.
Использование ABC-анализа
ABC-анализ является своего рода усовершенствованным и приспособленным к современным условиям вариантом принципа Парето. Согласно методике его проведения, все элементы анализа разбиваются на три категории по степени важности:
- Категория – элементы, имеющие в совокупности более удельного веса;
- Категория – элементы, совокупность которых составляет от до удельного веса;
- Категория – оставшиеся элементы, общая совокупность которых составляет и менее удельного веса.
Отдельные компании применяют более продвинутые методики и разбивают элементы не на 3, а на 4 или 5 групп, но мы будем опираться на классическую схему ABC-анализа.
Способ 1: анализ при помощи сортировки
В Экселе ABC-анализ выполняется при помощи сортировки. Все элементы отсортировываются от большего к меньшему. Затем подсчитывается накопительный удельный вес каждого элемента, на основании чего ему присваивается определенная категория. Давайте на конкретном примере выясним, как указанная методика применяется на практике.
У нас имеется таблица с перечнем товаров, которые предприятие реализует, и соответствующим количеством выручки от их продажи за определенный период времени. Внизу таблицы подбит итог выручки в целом по всем наименованиям товаров. Стоит задача, используя ABC-анализ, разбить эти товары на группы по их важности для предприятия.
- Выделяем таблицу с данными курсором, зажав левую кнопку мышки, исключая шапку и итоговую строку. Переходим во вкладку . Производим щелчок по кнопке , расположенной в блоке инструментов на ленте.
Можно также поступить по-другому. Выделяем указанный выше диапазон таблицы, затем перемещаемся во вкладку и выполняем щелчок по кнопке , расположенной в блоке инструментов на ленте. Активируется список, в котором выбираем в нем позицию .
- При применении любого из вышеуказанных действий запускается окно настройки сортировки. Смотрим, чтобы около параметра была установлена галочка. В случае её отсутствия, устанавливаем.
В поле указываем наименование той колонки, в которой содержатся данные по выручке.
В поле нужно указать, по какому конкретному критерию будет выполняться сортировка. Оставляем предустановленные настройки – .
В поле выставляем позицию .
После произведения указанных настроек нажимаем на кнопку в нижней части окна.
- После выполнения указанного действия все элементы были отсортированы по выручке от большего к меньшему.
- Теперь нам следует рассчитать удельный вес каждого из элементов для общего итога. Создаем для этих целей дополнительный столбец, который так и назовем . В первой ячейке данной колонки ставим знак , после чего указываем ссылку на ячейку, в которой находится сумма выручки от реализации соответствующего товара. Далее устанавливаем знак деления (). После этого указываем координаты ячейки, в которой содержится итоговая сумма реализации товаров по всему предприятию.
Учитывая тот факт, что указанную формулу мы будем копировать в другие ячейки столбца посредством маркера заполнения, то адрес ссылки на элемент, содержащий итоговую величину выручки по предприятию, нам нужно зафиксировать. Для этого делаем ссылку абсолютной. Выделяем координаты указанной ячейки в формуле и жмем на клавишу . Перед координатами, как мы видим, появился знак доллара, что свидетельствует о том, что ссылка стала абсолютной. При этом нужно учесть, что ссылка на величину выручки первого в списке товара () должна оставаться относительной.
Затем, чтобы произвести вычисления, жмем на кнопку .
- Как видим, удельный вес выручки от первого товара, указанного в списке, отобразился в целевой ячейке. Чтобы произвести копирование формулы в диапазон ниже, ставим курсор в правый нижний угол ячейки. Происходит его трансформация в маркер заполнения, имеющий вид небольшого крестика. Жмем левую кнопку мыши и перетягиваем маркер заполнения вниз до конца колонки.
- Как видим, весь столбец заполнен данными, характеризующими удельный вес выручки от реализации каждого товара. Но величина удельного веса отображается в числовом формате, а нам нужно трансформировать его в процентный. Для этого выделяем содержимое столбца . Затем перемещаемся во вкладку . На ленте в группе настроек имеется поле отображающее формат данных. По умолчанию, если вы не производили дополнительных манипуляций, там должен быть установлен формат . Щелкаем по пиктограмме в виде треугольника, расположенной справа от этого поля. В открывшемся списке форматов выбираем позицию .
- Как видим, все значения столбца были преобразованы в процентные величины. Как и положено, в строке указано . Удельный вес товаров ожидаемо располагается в столбце от большего к меньшему.
- Теперь нам следует создать столбец, в котором бы отображалась накопленная доля с нарастающим итогом. То есть, в каждой строке к индивидуальному удельному весу конкретного товара будет прибавляться удельный вес всех тех товаров, которые расположены в перечне выше. Для первого товара в списке () индивидуальный удельный вес и накопленная доля будут равными, а вот у всех последующих к индивидуальному показателю нужно будет прибавить накопленную долю предыдущего элемента списка.
Итак, в первой строке переносим в столбец показатель из колонки .
- Далее устанавливаем курсор во вторую ячейку столбца . Тут нам придется применить формулу. Ставим знак и складываем содержимое ячейки этой же строки и содержимое ячейки из строки выше. Все ссылки оставляем относительными, то есть, не производим с ними никаких манипуляций. После этого выполняем щелчок по кнопке для вывода итогового результата.
- Теперь нужно скопировать данную формулу в ячейки данного столбца, которые размещены ниже. Для этого применяем маркер заполнения, к которому мы уже прибегали при копировании формулы в столбце . При этом, строку захватывать не нужно, так как накопленный результат в будет отображаться на последнем товаре из списка. Как видим, все элементы нашего столбца после этого были заполнены.
- После этого создаем столбец . Нам нужно будет сгруппировать товары по категориям , и согласно указанной накопленной доле. Как мы помним, все элементы распределяются по группам по следующей схеме:
- – до ;
- – следующие ;
- – оставшиеся .
Таким образом, всем товарам, накопленная доля удельного веса которых входит в границу до , присваиваем категорию . Товарам с накопленным удельным весом от до присваиваем категорию . Оставшейся группе товаров со значением более накопленного удельного веса присваиваем категорию .
- Для наглядности можно произвести заливку указанных групп разными цветами. Но это уже по желанию.
Таким образом, мы разбили элементы на группы по уровню важности, используя при этом ABC-анализ. При использовании некоторых других методик, о чем говорилось уже выше, применяют разбиение на большее количество групп, но сам принцип разбиения при этом остается практически неизменным.
Способ 2: использование сложной формулы
Безусловно, применение сортировки – это наиболее распространенный способ проведения ABC-анализа в Экселе. Но в некоторых случаях требуется провести данный анализ без перестановки строк местами в исходной таблице. В этом случае на помощь придет сложная формула. Для примера будем использовать ту же исходную таблицу, что и в первом случае.
- Добавляем к исходной таблице, содержащей наименование товаров и выручку от продажи каждого из них, колонку . Как видим, в данном случае мы можем не добавлять столбцы с расчетом индивидуальных и накопительных долей.
- Производим выделение первой ячейки в столбце , после чего выполняем щелчок по кнопке , расположенной возле строки формул.
- Производится активация . Перемещаемся в категорию . Выбираем функцию . Делаем щелчок по кнопке .
- Активируется окно аргументов функции . Синтаксис её представлен следующим образом:
=ВЫБОР(Номер_индекса;Значение1;Значение2;…)
Задачей данной функции является вывод одного из указанных значений, в зависимости от номера индекса. Количество значений может достигать 254, но нам понадобится всего три наименования, которые соответствуют категориям ABC-анализа: , , . Можем сразу вводить в поле символ , в поле — , в поле — .
- А вот с аргументом придется основательно повозиться, встроив в него несколько дополнительных операторов. Устанавливаем курсор в поле . Далее жмем по пиктограмме, имеющей вид треугольника, слева от кнопки . Открывается список недавно используемых операторов. Нам нужна функция . Так как в списке её нет, то жмем по надписи .
- Снова производится запуск окна . Опять переходим в категорию . Находим там позицию , выделяем её и делаем щелчок по кнопке .
- Открывается окно аргументов оператора . Синтаксис его имеет следующий вид:
=ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)
Предназначение данной функции – это определение номера позиции указанного элемента. То есть, как раз то, что нам нужно для поля функции .
В поле сразу можно задать следующее выражение:
{0:0,8:0,95}
Оно должно быть именно в фигурных скобках, как формула массива. Не трудно догадаться, что эти числа (; ; ) обозначают границы накопленной доли между группами.
Поле не обязательное и в данном случае мы его заполнять не будем.
В поле устанавливаем курсор. Далее снова через описанную выше пиктограмму в виде треугольника перемещаемся в .
- На этот раз в производим перемещение в категорию . Выбираем наименование и жмем на кнопку .
- Запускается окно аргументов функции . Указанный оператор суммирует ячейки, отвечающие определенному условию. Его синтаксис такой:
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)
В поле вводим адрес колонки . Для этих целей устанавливаем курсор в поле, а затем, произведя зажим левой кнопки мыши, выделяем все ячейки соответствующего столбца, исключая значение . Как видим, адрес тут же отобразился в поле. Кроме того, нам нужно сделать данную ссылку абсолютной. Для этого производим её выделение и жмем на клавишу . Адрес выделился знаками доллара.
В поле нам нужно задать условие. Вписываем следующее выражение:
«>»&
Затем сразу же после него заносим адрес первой ячейки столбца . Делаем координаты по горизонтали в данном адресе абсолютными, дописав перед буквой знак доллара с клавиатуры. Координаты по вертикали оставляем относительными, то есть, перед цифрой никакого знака быть не должно.
После этого не жмем на кнопку , а кликаем по наименованию функции в строке формул.
- Затем мы возвращаемся в окно аргументов функции . Как видим, в поле появились данные заданные оператором . Но это ещё не все. Переходим в это поле и уже к имеющимся данным добавляем знак без кавычек. Затем вносим адрес первой ячейки столбца . И опять делаем координаты по горизонтали данной ссылки абсолютными, а по вертикали оставляем относительными.
Далее берем все содержимое поля в скобки, после чего ставим знак деления (). После этого снова через пиктограмму треугольника переходим к окну выбора функций.
- Как и в прошлый раз в запустившемся ищем нужный оператор в категории . На этот раз искомая функция называется . Выделяем её и жмем на кнопку .
- Открывается окно аргументов оператора . Его главное предназначение – это суммирование данных в ячейках. Синтаксис этого оператора довольно прост:
=СУММ(Число1;Число2;…)
Для наших целей понадобится только поле . Вводим в него координаты диапазона столбца , исключая ячейку, которая содержит итоги. Подобную операцию мы уже проводили в поле функции . Как и в тот раз, координаты диапазона делаем абсолютные, выделив их, и нажав на клавишу .
После этого жмем по клавише внизу окна.
- Как видим, комплекс введенных функций произвел вычисление и выдал результат в первую ячейку столбца . Первому товару была присвоена группа . Полная формула, примененная нами для данного вычисления, выглядит следующим образом:
=ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;»>»&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});»A»;»B»;»C»)
Но, конечно, в каждом конкретном случае координаты в данной формуле будут отличаться. Поэтому её нельзя считать универсальной. Но, используя то руководство, которое было приведено выше, можно вставить координаты любой таблицы и с успехом применять данный способ в любой ситуации.
- Впрочем, это ещё не все. Мы произвели расчет только для первой строки таблицы. Для того, чтобы полностью заполнить данными столбец , нужно скопировать эту формулу в диапазон ниже (исключая ячейку строки ) с помощью маркера заполнения, как мы уже делали не раз. После того, как данные будут внесены, ABC-анализ можно считать выполненным.
Как видим, результаты, полученные при помощи варианта с применением сложной формулы, ничуть не отличаются от тех результатов, которые мы проводили путем сортировки. Всем товарам присвоены те же самые категории, только при этом строки не изменили своего начального положения.
Программа Excel способна значительно облегчить проведение ABC-анализа для пользователя. Это достигается использованием такого инструмента, как сортировка. После этого производится подсчет индивидуального удельного веса, накопленной доли и, собственно, разбиение на группы. В тех случаях, когда изменение первоначального положения строк в таблице не допускается, можно применить метод с использованием сложной формулы.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.