Автоматизация MS Excel. ABC-анализ. Мегаформулы. Часть II
В первой части статьи мы составили мегаформулу для автоматического ранжирования клиентов в базе данных.
Да, это удобно. Однако, стоит взглянуть на эту мегаформулу несколько под другим углом: в первой части статьи 1мы соорудили слишком тяжеловесную, слишком неповоротливую конструкцию.
Все дело в том, что Вы сами через неделю (не говоря уже о стороннем пользователе этой мегаформулы) будете с трудом вспоминать, что, собственно, тут пытались сделать. Придется копаться в коде, заново разбирать всю конструкцию, если, к примеру, требуется чуть-чуть изменить условия.
Необходимо помнить правило автоматизации в Excel: все условия выносим «за скобки»!
Подробнее об этом можно прочитать в статье: “Автоматизация вычислений в Excel: управление постоянными и переменными“
И сделать это очень просто. Нужно вырезать из формулы все абсолютные цифры и, заменив их на переменные, вынести на отдельный лист! Только и всего!
Выглядеть это будет следующим образом.
Промежуточный рабочий файл Excel
Скачать файл для работы с примером
Вначале подготавливаем рабочее поле – создаем таблицу с условиями.
Поля «От» и «До» будут служить ограничителями по критериям.
«Или равно» – дополнительный параметр, если он требуется. Обычно я предусматриваю наличие подобных полей при автоматизации вычислений. На всякий случай.
«Показать» – результат, выводимый программой при попадании тестируемого значения в рамки критерия.
«Значение» и «Результат» – технические поля для быстрой проверки результата создания формулы и выявления ошибок.
Как помните, в конце первой части у нас получилась мегаформула для ABC-анализа:
=ЕСЛИ( И(С7>0; С7<10); 5; ЕСЛИ( И(С7>10; С7<100); 4; ЕСЛИ( И(С7>99; С7<250); 3; ЕСЛИ( И(С7>249; С7<1000); 2; ЕСЛИ( И(С7>999); 1; «Err!») ) ) ) )
Сейчас наша задача выдернуть все абсолютные значения и вставить вместо них переменные.
Для этого, запишем условия ранжирования в подготовленной таблице:
Работать с нашей формулой будем в ячейке G3. Именно в этой ячейке мы будем наблюдать результат работы формулы.
Скопируем нашу формулу в эту ячейку и заменим в ней все C7 на D3.
Вторым шагом, заменим все цифры условий (0-9, 10-99, 100-249, 250-999, >1 000) на соответствующие графы новой таблицы и увеличим число проверочных условий до 10 строк:
=ЕСЛИ(И(D2>=C5;D2<D5);G5;ЕСЛИ(И(D2>=C6;D2<D6);G6;ЕСЛИ(И(D2>=C7;D2<D7);G7;ЕСЛИ(И(D2>=C8;D2<D8);G8;ЕСЛИ(И(D2>=C9;D2<D9);G9;ЕСЛИ(И(D2>=C10;D2<D10);G10;ЕСЛИ(И(D2>=C11;D2<D11);G11;ЕСЛИ(И(D2>=C12;D2<D12);G12;ЕСЛИ(И(D2>=C13;D2<D13);G13;ЕСЛИ(И(D2>=C14;D2<D14);G14;”Err!”))))))))))
А теперь добавим проверку на дополнительные условия в столбцах E и F. Это элементарно осуществить с помощью функции ИЛИ():
=ЕСЛИ(ИЛИ(И(D2>=C5;D2<D5);D2=E5;D2=F5);G5;ЕСЛИ(ИЛИ(И(D2>=C6;D2<D6);D2=E6;D2=F6);G6;ЕСЛИ(ИЛИ(И(D2>=C7;D2<D7);D2=E7;D2=F7);G7;ЕСЛИ(ИЛИ(И(D2>=C8;D2<D8);D2=E8;D2=F8);G8;ЕСЛИ(ИЛИ(И(D2>=C9;D2<D9);D2=E9;D2=F9);G9;ЕСЛИ(ИЛИ(И(D2>=C10;D2<D10);D2=E10;D2=F10);G10;ЕСЛИ(ИЛИ(И(D2>=C11;D2<D11);D2=E11;D2=F11);G11;ЕСЛИ(ИЛИ(И(D2>=C12;D2<D12);D2=E12;D2=F12);G12;ЕСЛИ(ИЛИ(И(D2>=C13;D2<D13);D2=E13;D2=F13);G13;ЕСЛИ(ИЛИ(И(D2>=C14;D2<D14);D2=E14;D2=F14);G14;”Err!”))))))))))
В ячейке D3 будем вводить тестовые значения.
Введя несколько разных значений и проверив правильность работы формулы ее можно переносить в нужный документ.
Что имеем теперь?
Вынеся «за скобки» все постоянные значения мы получили возможность управлять критериями категорий наших клиентов так, как требуется в любой момент времени. Нам более не нужно копаться в коде формулы – она работает правильно.
Финальный рабочий файл Excel
Скачать файл для работы с примером
Достаточно перейти на страницу, содержащую таблицу значений критериев и в нужных местах заменить значения на новые – мегаформула ABC-анализа автоматически заменит свои значения во всей клиентской базе на новые и по-новому ее ранжирует!
Добавление встроенных фильтров дополнительно упростит работу с базой данных:
По мере набора опыта в подобных построениях и при необходимости Вы научитесь выстраивать много более сложные схемы.
Например, трех-, четырех- и пятиуровневые системы фильтров на основе мегаформул, когда конечная выборка значений для мегаформулы зависит от вложенных в одну четырех мегаформул, каждая из которых опирается на десятки критериев ранжирования, выбора, условий и равенств.
Воистину, Excel – великий инструмент!
Данный метод подходит, если ты уже знаешь значение по каждой группе. А если задача как раз в том, чтобы выделить эти группы автоматически.
Например, мне нужно сделать ABC-анализ услуг.
В этом анализе я задаю следующее значение групп:
A – те услуги, стоимость которых в сумме составляет 70% от общей стоимости всего ассортимента услуг.
B – 25% соответственно
C – 5% соответственно
При этом важно, чтобы можно было заменить %-ое соотношение на другое.
Трудность возникает в том, что ровно разделить услуги по заданным процентам не получается (т.к. 75% дают 3 улсуги + половина стоимости 4ой, итого 3,5 услуги), поэтому возможно ли сделать изменение процентного соотношения – масимально приближенного к заданным изначально параметрам при осуществлении анализа? То есть эту 4ую услуг отнести либо к А либо к В группе?