В работе с любыми большими объемами данных всегда существует потребность в сортировке и оценке. Одну из таких сортировок мы сегодня и рассмотрим. Мы научимся автоматически ранжировать клиентов. Если говорить более научно, то изучим процесс автоматизации abc-анализа простыми средствами Excel.

Во второй части статьи создадим полностью автоматизированную модель.

Приведу простой пример. Во время моей работы над StartUp‘ом одной водной компании необходимо было научиться разделять клиентов по количеству потребляемой ими питьевой воды. Было понятно, что у компании, производящей и продающей воду таких клиентов сотни и тысячи, а значит нужна автоматизация процесса разделения клиентов.

Можно было, конечно, повесить на девочек, принимающих звонки, задачу по проставлению статуса каждой компании, но компьютеру я доверяю больше, да и зачем утруждать кого-то, если оптимально решить эту задачу можно за 2 минуты раз и навсегда?

На этом примере и решим задачку.

Первый вариант автоматизированного ABC-анализа

Итак, существует компания, которая желает начать заказывать воду. Она звонит в CallCenter и осуществляет заказ. Девочка-оператор заносит данные: адрес, название компании, телефон, время доставки, количество заказанных бутылей… И вот здесь стоп.

После введения количества заказанных бутылок и нажатия клавиши Enter (Ввод), нужно научить Excel автоматически проставлять категорию клиента. Было решено ввести следующую систему градации:

• 1 категория: клиент заказывает  1 000 и более бутылей воды (19,8 литров) в месяц
• 2 категория: клиент заказывает от 250 до 999 бутылей (19,8 литров) включительно в месяц
• 3 категория: клиент заказывает от 100 до 249 бутылей (19,8 литров) включительно в месяц
• 4 категория: клиент заказывает от 10 до 99 бутылей (19,8 литров) включительно в месяц
• 5 категория: клиент заказывает от 1 до 9 бутылей (19,8 литров) включительно в месяц

У Вас критерии могут быть и обязательно будут совершенно иные, но мегаформула, которую мы напишем, будет универсальной и, уверен, подойдет Вам. Тем более, что дочитав эту статью и II часть  до конца и немного поразмыслив, Вы научитесь щелкать такие задачки, как семечки.

Вернемся к задаче. Данная постановка ее очень проста, поскольку предполагает линейное развитие событий: если «а» – сделать «1», или если «б» – сделать «2», или если «в» и так далее.

Вот это и запишем в виде единой мегаформулы. Для этого используем одни из самых популярных формул в Excel:
=ЕСЛИ()
=И()

Вопрос «с чего начинать ее составлять?» не актуален – в данном случае можно начинать либо с хвоста, либо с головы – как Вам удобнее. Мне удобнее с конца. Вот оттуда и начнем: запишем условие определения 5 категории клиентов:

=ЕСЛИ( И(С7>0; С7<10); 5; __ )

Внимание! Если Вы простым копированием переместите данную формулу в ячейку Excel – получите ошибку. Excel воспримет С7 не как указание на ячейку, а как простой текст. Правьте ручками!

Теперь разбор записи. Мы записали следующее: в случаях, когда С7 больше нуля, но меньше 10 ты должна вывести на экран цифру 5. В данном случае C7 – просто случайная ячейка, в которой мы, к примеру, обычно записываем количество заказанной воды. То есть, условие для сравнения и определения категории клиента. В Ваших таблицах это может быть любая другая ячейка, содержащая требуемую для решения Вашей же задачи информацию.

Кстати, обратите внимание, что в записи стоит С7<100. Это означает, что условие верно только в том случае, когда проверяемая величина меньше либо равна 9. Хотя с тем же успехом, я мог записать и С7>=99, что одно и то же.

Подчеркивание поставлено в форме на место третьего аргумента. Если Вы заглянете в инструкцию функции =ЕСЛИ(), то прочитаете там, что третий аргумент обозначает действие, которое необходимо выполнить в том случае, если результат проверки первого условия означает ЛОЖЬ, т.е., другими словами, если наш клиент заказывает более чем 9 бутылок воды.

Поэтому, для введения в формулу второго условия (4 категория клиентов), мы запишем вместо подчеркивания такую фразу:

=ЕСЛИ( И(С7>9; С7<100); 4; __ )

Сравните ее с первой – по структуре они идентичны. Разница только в цифрах, которые мы меняем в зависимости от нашей задачи.

Вводим ее в нашу основную формулу в то место, которое я обозначил подчеркиванием.

=ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>10; С7<100); 4; __ ) )

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

А теперь совсем просто. Поняв, как работает функция =ECЛИ, копируем первую запись и банально вставляем ее вместо третьего аргумента в исходной функции.

Схематично процесс построения мегаформулы изобразить это можно следующим образом:

1. =ЕСЛИ( И(С7>0; С7<10); 5; __ )
2. =ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>9; С7<100); 4; __ ) )
3. =ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>9; С7<100); 4; ЕСЛИ( И(С7>99; С7<250); 3; __ ) ) )
4. =ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>9; С7<100); 4; ЕСЛИ( И(С7>99; С7<250); 3; ЕСЛИ( И(С7>249; С7<1000); 2; __ ) ) ) )
5. =ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>10; С7<100); 4; ЕСЛИ( И(С7>99; С7<250); 3; ЕСЛИ( И(С7>249; С7<1000); 2; ЕСЛИ( И(С7>999); 1; __ ) ) ) ) )

Собственно, формула получается простым копированием первой своей части и изменением цифр условия.

Но в конце, после проставления всех условий разделения по категориям, у нас все равно остается «хвост» в виде последнего подчеркивания. Я в таких случаях обычно вписываю туда приказ для Excel отобразить предупреждение о том, что пользователь где-то допустил ошибку: “Err!”

Это логично: если не срабатывает ни одно из определений категорий, значит пользователь явно где-то ошибся. Может ввел не ту цифру, или вдруг текст ввел. Всяко бывает.

Тогда наша конечная формула будет выглядеть следующим образом:

=ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>10; С7<100); 4; ЕСЛИ( И(С7>99; С7<250); 3; ЕСЛИ( И(С7>249; С7<1000); 2; ЕСЛИ( И(С7>999); 1; “Err!”) ) ) ) )

Теперь при указании любого количества заказанной продукции в ячейке C7 Excel автоматически проставит категорию клиента. Как видите, все очень просто!

Остается только с помощью автозаполнения растянуть эту формулу на все строки нашей базы данных клиентов.

Автор: ChExOff