XYZ анализ -разработка макроса в MSExcel

Обсуждение вопросов по использованию Excel, Access и других программ оптимизирующих работу закупщика
Аватар пользователя
inkerman

Сообщение inkerman » 05 фев 2008 15:41

Спасибо!
А на счет XYZ, думаю имеет смысл обсудить как наиболее удобно давать данные для макроса.
Я вижу 2 варианта минимум:
1) сформировать табличку где в первой строке будут названия периодов а в первом столбце названия объектов. Тогда все циферки будут ниже строки 1 и правее столбца А. нажимаем кнопочку, все работает.
2) Не делаем специальную подготовку данных, просто в рабочем листе выделяем данные в виде таблички и нажимаем на кнопочку, все работает а данные записываются в ближайший свободный столбец справа от выделенной области
По идее удобно было бы не специально готовить данные для макроса (способ 1) а делать вторым способом. Как думаете?

Реклама
sudakov
Коллега
Коллега
Сообщений: 11
Зарегистрирован: 31 янв 2008 03:00

ХУЗ

Сообщение sudakov » 05 фев 2008 16:10

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

Аватар пользователя
inkerman

Сообщение inkerman » 06 фев 2008 10:45

Насчет выделения - сделаю, это можно.
А вот поправки на сезонность уже нет. Тут 2 варианта:
а) если коэф вариации большой - это признак сезонности, если очень большой, это продажи спорадического характера.
б) самим корректировать с учетом сезонности и уже выделять табличку скорректированных данных.

Я полагаю метод б) лучше, т.к. метод а) совсем неоднозначен в своей трактовке.
Просто как программе пояснить каким способом корректировать сезонность? Для этого нужно бы знать прогнозные значения следующего периода. Другими словами я не знаю как вообще сделать эту корректировку, она в каждом конкретном случае не то что своя, она может принципиально отличаться. От фирмы к фирме и даже от товара к товару. Универсального метода тут придумать не могу, чтоб работал всегда.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Сообщение Роман Бодряков » 06 фев 2008 11:18

Если коэфвариации большой или очень большой, то может не быть никакой однозначной связи с сезонностью. Поставь ручные границы и пусть пользователь сам думает, что туда ставить. Отлавливать сезонность XYZ - неправильно. Гипотеза по гипотетическому предположению.

Вариант б - практичнее. Разве что, автоматизировать назначение границ. Принцип в основу можно положить следующий. Если у меня все нестабильно, то из всего это-го X- более стабильно, Y - менее и Z- совсем нестабильно. Ставить рекомендованно-фиксированные границы здесь не прокатит. Для розницы 30% - очень много, для опта из Китая слишком мало.

Дополнительно вставь две группы: XX- подозрительно стабильные и ZZ - подозрительно нестабильные. Туда может попасть один и тот же товар,если по нему одна продажа с нолями и без нолей в пустых ячейках. Т.е. статистически недостоверный.

sudakov
Коллега
Коллега
Сообщений: 11
Зарегистрирован: 31 янв 2008 03:00

XYZ

Сообщение sudakov » 07 фев 2008 01:15

Я работаю в сфере быт. техники, те группы товаров, что я веду - сезонные. Первое время убирал сезонность, надоело. Поэтому при ХУЗ анализе, ставил нестнадартные значения. Так что соглашусь с Романом, необходима возможность корректировки критериев.

Аватар пользователя
stanley

Re: XYZ

Сообщение stanley » 11 фев 2008 12:29

sudakov писал(а):Я работаю в сфере быт. техники, те группы товаров, что я веду - сезонные. Первое время убирал сезонность, надоело. Поэтому при ХУЗ анализе, ставил нестнадартные значения. Так что соглашусь с Романом, необходима возможность корректировки критериев.


хм. я чего-то в жизни не понимаю... а отчего надоело? это же делается ОДИН раз для одного продукта максимум. а на практике как правило для целой категории единый профиль сезонности. такое ощущение, что это каждый раз вручную делается :)

Аватар пользователя
d__4997

Сообщение d__4997 » 11 фев 2008 12:52

Согласен , со Стенли. Сезонность проще всего задавать один раз при вводе данной позиции в каталог товаров и дальше не ХУЗ по нему считать, а расчет аналитики сезонности проводить. Я даже считаю, что коэффициенты после расчета должны в систему ручками вноситься ( доп. контроль) а не самой системой.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Сообщение Роман Бодряков » 11 фев 2008 15:25

Коллеги,
При любом раскладе, сезонность выявлять/вычленять надо отдельно от XYZ анализа, а не в процессе.

Стас, единый профиль не панацея, он может больше проблем создать, чем пользы принести. У меня сейчас, в одном из проектов, есть суммовые продажи всей группы, которые с основными товарами этой группы имеют корреляцию 25% максимум, при это между собой у них все коррелирует. Хотя, сам я такое впервые встречаю, но супротив факта не попрешь.

Аватар пользователя
stanley

Сообщение stanley » 12 фев 2008 13:26

Roman писал(а):Коллеги,
При любом раскладе, сезонность выявлять/вычленять надо отдельно от XYZ анализа, а не в процессе.

я в очередной раз начинаю беситься от ваших анализов :)
хуз надо делать _после_ декомпозиции тренд/сезон, а не вместе и не вместо. сяду я все-таки после логфорума писать статью со своим взглядом на проблему
Стас, единый профиль не панацея, он может больше проблем создать, чем пользы принести. У меня сейчас, в одном из проектов, есть суммовые продажи всей группы, которые с основными товарами этой группы имеют корреляцию 25% максимум, при это между собой у них все коррелирует. Хотя, сам я такое впервые встречаю, но супротив факта не попрешь.

не понял. кто с кем между собой?
спорим, что внутри группы частные корреляции сплошь и рядом отрицательные? :lol:
по поводу же сезонности: мое твердое убеждение, что профиль сезонности - неотъемлемое свойство товара. точнее, свойство данного товара в данных сложившихся рыночных условиях. и пока рынок не "поплыл", профиль этот будет оставаться постоянным. как говорит, один мой общий знакомый, "это оказывается большим упрощением жизни закупщика" - не так уж много у нас констант в работе.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Сообщение Роман Бодряков » 12 фев 2008 15:27

А что ты статьей хочешь сказать? Попробуй сначала в форуме свою мысль до людей донести. Если человек, на твой взгляд, заблуждается.
1. Пойми его позицию,
2. Помоги ему осознать ошибочность его убеждений.

XYZ анализ можно делать и до и после и вместо. Весь вопрос какие результаты хочешь получить (во всех трех случаях они будут разные). И как будешь их использовать.
Проведение анализа по данным очишенным от сезонных колебаний - это только ЧАСТНЫЙ СЛУЧАЙ использования XYZ анализа.

А то, ишь напугать решил. Сколько тебя знаю все пугаешь и пугаешь. :-) Статью напишу. НАПИШИ! Если ее напечатают, то ее читать будут? Умничать легко. Когда умничают, то остальных дураками выставляют. А свое мнение авторитарно высказывают. И очень злятся если с ним кто-то не согласен. Но ты же не такой!  :D

Пиши статью, я ее на форуме размещу, будем на нее ссылаться и больше никогда тебе возражать не будем. Честно, честно.
Последний раз редактировалось Роман Бодряков 12 фев 2008 22:50, всего редактировалось 1 раз.

Аватар пользователя
inkerman

Сообщение inkerman » 12 фев 2008 17:37

Роман, вам замечание за неуважительный тон беседы. В подобной манере с вами вести разговор неприятно никому. Впредь, используйте обращение на "Вы" или на "вы", все таки stanley к вам обращался именно так, а вы перешли на "ты".
Кроме того вы и мне тоже отвечали, "тыкая" мне. Это не есть приятно.
Если у вас будут возражения, создавайте отдельную тему, возможно там обсудим.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Сообщение Роман Бодряков » 12 фев 2008 22:08

Inkerman. Давай борьбу за поднятие твоей самооценки будем проводить в другой ветке, заводи и обсуждай мое невоспитанное поведение.
Форум территория свободная и если участник скрывает свое имя, значит подразумевается, что он не старше и не Выше по должности. Местоимение "ты" уместно.

Стас, возможно я увлекся подначивая тебя. Прости пожалуйста.
Но будь ласков, подробно с примерами, изложи хотя бы суть своего подхода. А то тут под "беситься от ваших анализов " народ понимает что ты со мной на ВЫ первый раз за последние пять лет заговорил и что есть какие - то мои "неправильные" анализы. Проясни, чем твой подход отличается от нашего?

Аватар пользователя
stanley

Сообщение stanley » 19 фев 2008 11:22

Roman писал(а):А что ты статьей хочешь сказать? Попробуй сначала в форуме свою мысль до людей донести. Если человек, на твой взгляд, заблуждается.
1. Пойми его позицию,
2. Помоги ему осознать ошибочность его убеждений.

да я уже и здесь, и на логисте раз 10 объяснял свою позицию. естественно, в рамках форума трудно в двух словах рассказать развернуто, вот и получаю, что сказанное в двух словах понимают лишь те, кто и так уже знает, о чем речь. один из вариантов sapienti sat, так сказать. если же ставить себе цель еще и просветительскую, нужно именно статью с картинками и примерами делать, нет других вариантов.
XYZ анализ можно делать и до и после и вместо. Весь вопрос какие результаты хочешь получить (во всех трех случаях они будут разные). И как будешь их использовать.
Проведение анализа по данным очишенным от сезонных колебаний - это только ЧАСТНЫЙ СЛУЧАЙ использования XYZ анализа.

соглашусь, пожалуй. будем считать, что я максимализьмом погрешил в запале. поясню (не для тебя, мы это все в москве обсосали): меня бесит не столько сам факт использования этих сегментирующих преобразований из непрерывного спектра в дискретный, сколько попытки применять их и к месту, и не к месту. сам посуди - фраза "сделал анализ, а что дальше?" стала уже общим местом. так и хочется в моей хамской манере спросить "а накойхрен время тратил?". потому что вопрос "зачем, с какой целью" должен определить используемый инструмент. для одних задач эксель подходит, для других лучше изначально брать кувалду потяжелее, иначе замучаешься этим молотком махать.
А то, ишь напугать решил. Сколько тебя знаю все пугаешь и пугаешь. :-) Статью напишу. НАПИШИ! Если ее напечатают, то ее читать будут? Умничать легко. Когда умничают, то остальных дураками выставляют. А свое мнение авторитарно высказывают. И очень злятся если с ним кто-то не согласен. Но ты же не такой!  :D

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

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

ps. для горячих финских парней: смайлики отсутствуют, но подразумеваются, расставляйте по вкусу

Аватар пользователя
stanley

Сообщение stanley » 19 фев 2008 11:24

inkerman писал(а):Роман, вам замечание за неуважительный тон беседы. В подобной манере с вами вести разговор неприятно никому. Впредь, используйте обращение на "Вы" или на "вы", все таки stanley к вам обращался именно так, а вы перешли на "ты".
Кроме того вы и мне тоже отвечали, "тыкая" мне. Это не есть приятно.
Если у вас будут возражения, создавайте отдельную тему, возможно там обсудим.


Вам все же следует проконсультироваться, чем отличается обращение "вы" от "Вы"
:twisted:

Аватар пользователя
inkerman

Сообщение inkerman » 20 фев 2008 16:12

Вот сделал XYZ анализ.
Кто желает - пробуйте
http://slil.ru/25868670/670c218c.4848c770/XYZ_анализ_L.xls
Гарантированно работает под 2003 и 2007 Офисом

Как всегда мнения, отзывы и пожелания либо тут, либо на почту arcrimea@yandex.ru


Update: файл с неправильными надписями обновил
Последний раз редактировалось inkerman 05 июн 2008 20:24, всего редактировалось 2 раз.

Аватар пользователя
Дядя_Ежик

Сообщение Дядя_Ежик » 20 фев 2008 17:48

inkerman писал(а):Вот сделал XYZ анализ.
Кто желает - пробуйте
http://slil.ru/25497365/6a0a5d79.47bccb80/XYZ_анализ_lock.rar
Гарантированно работает под 2003 и 2007 Офисом

Как всегда мнения, отзывы и пожелания либо тут, либо на почту arcrimea@yandex.ru

А почему в меню он встраивается вытесняя предыдущий ABCDE и группы у него не XYZ, как было бы логично ожидать, а как раз ABCDE? :roll: :roll: :roll:

Аватар пользователя
inkerman

Сообщение inkerman » 20 фев 2008 19:07

Ой :) Косяк :'(
Следил за корректностью вычислений (были свои гемморои), а вышло от как, названия групп забыл поменять.
Уже исправил

Насчёт вытеснения - Дядя Ёжик, напишите поподробнее. У меня похожего не наблюдалось.
В качестве демонстрации:
Изображение

Аватар пользователя
inkerman

Сообщение inkerman » 12 мар 2008 13:21

В чем проблема? Там есть инструкция на вкладке. Вроде подробно написано как и что делать. Подробнее напишите в чем заключаются ваши проблемы....

Aleksander
Коллега
Коллега
Сообщений: 7
Зарегистрирован: 25 мар 2008 03:00

Сообщение Aleksander » 31 мар 2008 17:48

Может, есть смысл добавить возможности для макроса АВС-анализа наподобие XYZ? То есть, для проведения АВС=то данные за период анализа мы формируем, а для XYZ можем выделить область из нескольких периодов и на ней сделать анализ. Почему бы не добавить к макросу АВС такую же возможность (в столбцах - последовательно данные, пусть, по Vреализации, по нескольким периодам, а макрос все периоды сложит вместе, получит сумму за укрупнённый период, а потом уже всё подсчитает-поделит?). Согласен, что всё сложить не долго и вручную в Excel, но так зато получаем единый формат данных для двух видов анализа. И можем, если потребуется, сделать на одном массиве два анализа сразу.

Aleksander
Коллега
Коллега
Сообщений: 7
Зарегистрирован: 25 мар 2008 03:00

Сообщение Aleksander » 31 мар 2008 18:00

Да, я и забыл. У Вас в макросе стоит отображение наименований столбцов в Excel в виде R1C1. Я-то на это внимание не обратил, зато сколько шуму-то было 8O. Это хорошо ещё, что значок Excel`я не изменился :lol: , пока меня не было на месте :D

Аватар пользователя
inkerman

Сообщение inkerman » 01 апр 2008 21:42

Сменить стиль R1C1 на обычный, можно в настройках на вкладке общие
Excel 2003: Сервис -> Параметры ->Общие
Excel 2007: Кнопка Excel -> Параметры Excel -> формулы -> Работа с формулами

Касательно выделения для АВС, это будет очень неудобно когда захочется провести только АВС, без XYZ.
Поэтому, имхо, на данный момент наиболее удобно получается.

aRTi
Профессионал
Профессионал
Сообщений: 227
Зарегистрирован: 21 дек 2007 03:00

Сообщение aRTi » 02 апр 2008 11:47

Спасибо огромное!!! Макрос очень удобный - не требуется дополнительной подготовки данных для анализа. Все группы получились y, z. х - вообще нет (на годовом периоде). Защиту лучше менять не на низкую, а на среднюю... А потом при проверке понимаю, что xyz можно сделать 3-мя формулами в экселе, (хотя без %% по каждой группе) :)) Огромный "+" макроса для меня - можно пробовать разные периоды, не меняя формул.

aRTi
Профессионал
Профессионал
Сообщений: 227
Зарегистрирован: 21 дек 2007 03:00

Сообщение aRTi » 02 апр 2008 12:43

Aleksander писал(а):... для XYZ можем выделить область из нескольких периодов и на ней сделать анализ. Почему бы не добавить к макросу АВС такую же возможность (в столбцах - последовательно данные, пусть, по Vреализации, по нескольким периодам, а макрос все периоды сложит вместе, получит сумму за укрупнённый период, а потом уже всё подсчитает-поделит?)...

Поддерживаю!!! Будет еще лучше.
И еще в ABC: если значение первой строки 0 (полученный функцией суммесли при отсутствии данных), то макрос ABC не работает. (Чтобы работал, первой строкой выбираю 1-ю положительную).
А во всем остальном ABC тоже очень удобный.
Последний раз редактировалось aRTi 02 апр 2008 13:23, всего редактировалось 1 раз.

Aleksander
Коллега
Коллега
Сообщений: 7
Зарегистрирован: 25 мар 2008 03:00

Сообщение Aleksander » 02 апр 2008 13:04

inkerman писал(а):Касательно выделения для АВС, это будет очень неудобно когда захочется провести только АВС, без XYZ.
Поэтому, имхо, на данный момент наиболее удобно получается.


Так я же не говорю, что надо убрать существующий алгоритм работы с АВС (кстати, работает всё отлично!), а просто к имеющемуся добавить и другую возможность (ну, что-то вроде преключателя: если данные сформированы, то пользуемся тем что уже есть, а если не сформированы - то переключаем, что бы с дополнительной обработкой работало). Так же удобнее.

Аватар пользователя
inkerman

Сообщение inkerman » 02 апр 2008 13:23

На данный момент сделать и авс и xyz можно так: просуммировать, затем выделить сами данные без столбца с суммой и провести xyz, а затем по столбцу с суммой провести abc.
Но раз сразу 2 человека посчитали что так проще, я подумаю над возможностью подобного усовершенствования. Правда что это будет скоро - не обещаю.

to aRTi
А вы выделите первую ячейку не первую положительную, а вообще первую, начиная с которой следует провести анализ. В вашем случае это будет ячейка с 0. А так конечно, ячейка с 0 просто не участвует в расчётах, т.к. находится выше чем первая выделенная.

aRTi
Профессионал
Профессионал
Сообщений: 227
Зарегистрирован: 21 дек 2007 03:00

Сообщение aRTi » 02 апр 2008 13:29

Если я выделяю ячейку с 0 (т.е. она же 1-я), то макрос не работает.
Вылезает сообщение: "Ячейка не должна быть пустой, а с числом".

Аватар пользователя
inkerman

Сообщение inkerman » 02 апр 2008 20:39

Хм. это уже не порядочек
Я доделал немножко АВС, сделал возможность сразу по нескольким методам строить анализ, так удобнее сравнивать. кроме того, убрал вышеназванный косячок и сделал метод двойной касательной гораздо быстрее на больших массивах.
ссылка на макрос размещена в ветке форума "ABC анализ в Excel'e (расширенный макрос)"
Последний раз редактировалось inkerman 08 июн 2008 14:11, всего редактировалось 1 раз.

Аватар пользователя
inkerman

Сообщение inkerman » 03 апр 2008 14:29

Добрый день!
В ветке "АВС анализ в Excel (макрос)" выложил новую версию, учитывающую пожелания Aleksander и aRTi


Вернуться в «Программы для закупщиков»

Кто сейчас на форуме

Количество пользователей, которые сейчас просматривают этот форум: CommonCrawl [Bot] и 0 гостей