Inventor – надстройка для Excel: ABC-XYZ и другие методы анализа

Написано admin в 22 декабря, 2012. Опубликовано в Системы и ПО для бизнеса

Разрабочик: Язакупщик.РФ

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

Рис. 1. Дополнительная панель инструментов 

i-1

или вкладку в Excel 2007-2013:

i-2

С помощью Inventor вы сможете быстро и удобно:

– провести АВС – анализ 4 возможными методами + возможность визуализации цветом,

– провести XYZ – анализ с возможностью оценки границ групп на графике кривой XYZ,

– оценить динамику продаж на графике и подобрать формулу для прогноза продаж,

– рассчитать страховой запас,

– автоматизировать процесс управления запасами в Excel,

– обрабатывать псевдочисла и тект,

– обрабатывать ячейки и диапазоны,

– и еще более 50 функций, полный список можно найти в инструкции.

Работа не завершится аварийно

В отличие от обычных макросов, для большинства команд Inventor доступна отмена последнего действия, так же Inventor умеет обрабатывать ошибки, а это значит, что работа Excel не завершится аварийно, и вы не потеряете ценную информацию. Кроме того, при длительных по времени операциях Inventor выводит индикатор выполнения задачи, чтобы вы могли попить чайку или перекурить.

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

АВС анализ

Думаю, сегодня крайне сложно найти закупщика, который бы не знал об АВС-XYZ анализе. Я решил автоматизировать его, и вот что у меня вышло (см. рис. 2).

Рис. 2. Автоматизация анализа

i-3

В данной форме нужно задать:

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

– Выбрать один или несколько методов анализа. При выборе нескольких методов результаты будут размещены в несколько столбцов.

– В эмпирическом методе и методе суммы границы задаются пользователем, а в методах касательных рассчитываются программой. Можно дополнительно выделить группы АА и СС при большом количестве объектов и дефиците управленческого ресурса. Подробное толкование методов анализа можно получить в инструкции к программе.

– При необходимости можно выбрать графический метод анализа, который раскрашивает исходные данные в соответствии с той группой, к которой они относятся в результате АВС-анализа. Метод может быть выбран как отдельно, так и в совокупности с любым сочетанием основных методов. Данная функция рассчитана на стандартную палитру цветов, подробности смотрите в инструкции по установке, которая идет в одном архиве с файлом надстройки.

– Вы можете сохранить текущие настройки для того, чтобы в следующий раз не задавать их вновь.

В итоге получится картина, показанная на рисунке 3.

Рис. 3. Настройки АВС

i-4

Если какой-то из параметров задан неверно, то Inventor обязательно уведомит вас об этом.

Благодаря высокоскоростной обработке данных в массивах, Inventor выполняет АВС анализ на 1 млн. ячеек (50 тыс. строк * 20 столбцов) менее чем за 1 секунду на один метод. Ошеломительная скорость (я не встречал программы, которые выполнят то же самое хотя бы за 10 секунд)! Благодаря этому программа не будет выдавать ошибку даже при очень больших объемах исходных данных. При графическом анализе скорость ниже, но это связано не с обработкой данных, а с изменением цвета ячеек, так что не беспокойтесь за свои данные.

XYZ анализ

Форма XYZ анализа выглядит следующим образом (рис. 4).

Рис. 4. Форма XYZ анализа

i-5

В данной форме нужно задать:

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

– Нажав кнопку “обновить”, вы увидите распределение коэффициента вариации на кривой XYZ. Благодаря этому вы сможете более точно оценить границы групп и задать их слева от графика. Визуально оценить границы (пусть и не всегда) можно при помощи касательных, как и в АВС-анализе, подробное толкование метода касательных можно получить в инструкции к программе. Обратите внимание на настройку максимального коэффициента вариации для графика, это нужно для того, чтобы он не искажался при выбросах (например, если одна или несколько позиций имеют запредельный коэффициент). Справа от настройки максимального Квар. вы увидите, сколько позиций попало на график при текущем ограничении вывода. Если цифра близка к 100%, то вы смело можете принимать решение о границах групп на основании текущего графика. Я дополнительно выделил группы XX и ZZ, для очень стабильных и очень нестабильных позиций.

– При выборе опции “Квар” вместе с группой XYZ выводится коэффициент вариации.

– Вы можете сохранить текущие настройки для того, чтобы в следующий раз не задавать их вновь.

В итоге получится картина, показанная на рисунке 5.

Рис. 5. Настройки XYZ

i-6

Если какой-то из параметров задан неверно, то Inventor обязательно уведомит вас об этом.

Благодаря высокоскоростной обработке данных в массивах, Inventor выполняет XYZ – анализ на 1 млн. ячеек (50 тыс. строк * 20 столбцов) менее чем за 3 секунды. Благодаря этому программа не будет выдавать ошибку даже при очень больших объемах исходных данных.

Если отбросить ненужную скромность, то я могу с уверенностью сказать, что данный модуль ABC-XYZ анализа лучший из всех, что мне приходилось видеть.

Прогноз продаж

О различные методы прогнозирования на практике сломано немало копий. Всему причина исходные данные, которые неадекватно отражают историю продаж из-за дефицитов, пересортов, акций и т.д. (сами продолжите?!). Нет абсолютно никакого смысла применять высшую математику на кривых данных (да и на нормальных данных нужно применять те методы, которые вы в состоянии понять и осмыслить), и наоборот, даже простые, на первый взгляд, методы могут дать хорошие результаты на адекватной статистике продаж (и заниматься, прежде всего, нужно нормализацией этой статистики). Гуру в данной области Джон Шрайбфедер предлагает отличные формулы для прогнозирования, их я и использовал в данной утилите. Форма прогноза показана на рисунке 6.

Рис. 6. Форма прогноза продаж

i-7

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

Текущий месяц в статистике за прошлые года выделяется цветом на графике для удобства визуального восприятия информации. Прогнозное значение так же выделяется цветом. При нажатии ОК формула прогноза вводится в ячейку справа от статистики продаж (эту формулу можно копировать и изменять на рабочем листе). При помощи кнопок вверх/вниз и т.д. вы можете перемещаться по рабочему листу и строить прогнозы на разные периоды или по разным объектам. Иногда просто удобно визуально оценить массив данных из множества строк, при помощи этой формы можно построчно это сделать. Уверен, что вы по достоинству оцените данный инструмент.

Страховой запас

Существует множество формул расчета страхового запаса. Предлагаемая мною формула учитывает все факторы, которые на него влияют. Кроме того, результат измеряется в днях продаж, а не в количестве, что очень удобно при управлении запасами. Подробнее ознакомиться с формулой и ее применением в управлении запасами можно в инструкции к программе. Форма для расчета страхового запаса показана на рисунке 7.

Рис. 7. Форма расчета страхового запаса

i-8

В ней необходимо задать все составляющие расчета страхового запаса, а именно: уровень сервиса, точность прогноза, цикл заказ-поставка и заказ-заказ, а так же возможные ожидаемые отклонения от них. Можно указать как фиксированные значения, так и задать ссылку на ячейку. При нажатии ОК в соответствующую ячейку будет введена формула страхового запаса, которую можно корректировать и копировать на рабочем листе. Обратите внимание, на следующие особенности:

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

– Ошибка прогноза измеряется в %, но в формулу вводится значение без %. Например, если ошибка составляет 20%, то в формуле на рабочем листе нужно использовать аргумент 20, а не 0,2 (20%). Если вам интересно, почему мне пришлось пойти на это математическое недоразумение, то я могу объяснить: формула генерируется в коде VBA, внутри него аргументы формулы разделяются “,” а не “;”. Поэтому, если используется аргумент с запятой, например “0,2”, то в формуле он будет интерпретирован как два аргумента: “0” и “2”, в результате чего возникает ошибка #ЗНАЧ!. Я использовал самый простой вариант обхода этой ошибки из тех, что удалось придумать. Если вы будете вводить формулу при помощи фиксированных значений в форме, то можете не беспокоиться об этом, в противном случае, просто помните об этой особенности.

Конвертация псевдочисел

Я часто сталкиваюсь с ситуацией, когда данные импортированные или скопированные из учетной системы воспринимаются Excel как текст. Превратить их в настоящие числа непростая задача (умножение на 1 или прибавление 0 не помогает, форматирование – тем более), а когда речь идет о миллионах ячеек, то задача становится крайне непростой. Специально для решения этой задачи я разработал данную утилиту, при помощи которой можно превращать в числа миллионы ячеек с псевдочислами (рис. 8).

Рис. 8. Утилита для конвертации псевдочисел

i-9

Благодаря высокоскоростной обработке информации в массиве на 1 млн. ячеек уходит всего несколько секунд.  Помните о том, что в данном случае формулы заменяются на значения, а пустые ячейки остаются пустыми (не заполняются нулями). Данная утилита хорошо подходит для первичной обработки больших массивов чисел. Если у вас в массиве есть формулы и вы хотите их сохранить или пустые ячейки должны заполняться нулями, то обратитесь к утилите “Операции с текстом”, она обладает меньшей скоростью обработки данных, но при этом имеет свои преимущества.

Отмена выделения диапазона

Одна из самых изящных функций Inventor. Вам часто приходится выделять десяток несмежных диапазонов для оперативного суммирования (в панели состояния) или форматирования? И сколько раз на последнем диапазоне вы нажимали не на ту ячейку и с нецензурной бранью начинали выделять все заново? Если я вызвал у вас улыбку, значит, вы понимаете, о чем речь и влюбитесь в данную функцию.

При нажатии кнопки отменяется выбор последнего выбранного вами диапазона (если их выделено более одного). Повторное нажатие отменяет выделение диапазона, выделенного предпоследним и т.д. пока не останется всего 1 выделенный диапазон. Вы вряд ли будете отменять выделение нескольких последних диапазонов, но отменять выделение последнего будете постоянно.

Сортировка по спецсвойствам

У вас не возникала потребность отсортировать массив данных по цвету шрифта или его типу? Вот вам простой пример: бывают случаи, когда из учетной системы можно получить данные только в иерархии. Удалить иерархию можно, а вот что делать с лишними строками, которые показывают суммирующие показатели по своей группе? Тут на помощь приходит Inventor, вы можете вывести дополнительный столбец с характеристиками спецсвойств и отсортировать массив по нему (рис. 9).

Рис. 9. Дополнительный столбец

i-10

i-11

Очень быстро, удобно и незаменимо!

Кроме макросов Inventor имеет в своем наборе более 30 функций, которые можно использовать в формулах на рабочем листе Excel. Ознакомиться с ними подробно вы можете в инструкции к программе, сейчас же мы рассмотрим лишь несколько из них.

ВПРН

ВПР Номер – функция, дополняющая стандартную функцию Excel ВПР. Но в отличие от него может вести поиск в любом столбце и возвращает не первое найденное значение, а N-ое заданное пользователем (см. рис. 10, 11).

Рис. 10. Ведение поиска

i-12

Аргументы данной функции интуитивно понятны для пользователей стандартного ВПР при ее вводе через мастер функций.

СуммЦветЗаливки, СуммЦветШрифта, СчетЦветЗаливки, СчетЦветШрифта

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

Рис. 11. Подсчет ячеек заданного диапазона

i-13

Синтаксис всех функций одинаков: нужно задать диапазон и ячейку с нужным цветом заливки/шрифта.
Protected content.

Для просмотра и скачивания полной версии необходима регистрация
В Мой Мир

Теги:, , , , , , , ,

Trackback с вашего сайта.

Комментарии (3)

  • admin
    12 февраля, 2014 в 5:29 пп |

    Ссылка обновлена

  • Аноним
    25 августа, 2014 в 9:07 дп |

    Вылетает при попытке рассчитать страховой запас((

  • AlibabaMyMovies
    6 июня, 2018 в 2:08 пп |

    Премного благодарен!
    Немного подправил пост, думаю, так будет корректнее. Возможно, стоит заменить и заголовок, на мой взгляд вполне себе корректно. Прикладная задача для ABC-анализа строится на основе отраслевой классификации. Хотя всегда готов рассмотреть другие доводы, если они будут весомее, изменим и заголовок.
    В свой черед представляю http://romantic-lovers.org.ua/movies/

Оставить комментарий

Войти

Новости

Полезное

Изменить "Кол-во"
Ежедневный курс иностранной валюты ЦБ РФ на 26.02.2021
Валюта Кол-во Рубль RUB
Продажа (руб.)
Доллар США USD
Евро EUR
Фунт стерлингов Соединенного королевства GBP
Use data from the cache