А что у вас в файле означает, например "с 01.05.21" - с 01 мая и до скончания века?

В разделе "правила" - "   5.1.Наши модераторы: Юрий М, vikttur".
Щёлкаете на любого из них и внизу слева будет "Сообщение"

Читаем правила форума - "2.7. Если вам нужен не совет по самостоятельному решению задачи, а чтобы все сделали за вас - добро пожаловать в ветку Работа. Там, скорее всего, найдутся специалисты, которые разберутся с вашей проблемой быстро, качественно и за разумную плату."

Попросите модераторов перенести тему в указанный раздел

Тогда поищите в интернете одну надстройку Excel. Она позволяет преобразовывать такие таблицы в плоские

Данные из 1С "Управление торговлей" получены с помощью стандартного отчета "товары на складах".Вы именно формируете данный отчет? Если Вы, то:
1. Наверняка в карточке товара есть поле (реквизит), в котором указана кратность упаковки. Т.е. её можно вывести как допполе рядом с наименованием товара.
Это нужно, чтобы все ваши коробки пересчитать в штуки. Почему в штуки? Потому как продажи у вас могут быть в штуках. Можно конечно и с помощью формул кратность получить, если в наименовании товара знак " * " всегда означает, что после него идет количество в коробке. Если в наименовании знак " * " может быть просто так, то получить кратность будет уже проблематично.
2. Данный отчет, если нерадивые программисты 1С вам не закрыли доступ к его настройке, можно сформировать в плоском виде.по дням периода. В таком виде легче будет подсчитывать потери.

Это не причем. Это нам нужно, чтобы привести в единый вид. Например, "2323328" и "89.23.233.28" это одно и тоже? Какой из них правильный? От того, какой код правильный, зависит, какой будет формула для приведения всех кодов в единообразный вид. А после приведения кодов, всё остальное делается на раз-два.

Как вариант, добавить к каждой дате вспомогательный столбец, где в каждой ячейке вставить имеющуюся у вас в модуле функцию "GetColorIndex". А затем с помощью СУММПРОИЗВ легко подсчитать количество ячеек с тем или иным номером цвета

Лучше всего, если есть один из уникальных показателей. Например, код всегда должен быть вида "89.##.###.##"?

Во-первых, не надо использовать

вы же уже присвоили переменной ws это.

Во-вторых, не понятно зачем вы в цикле присваиваете ячейке B1 значение i

Я не вижу где это используется

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

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

Martishka,вы своим файлом подняли для меня интересную тему. Я часто занимаюсь АВС анализом и до сегодняшнего момента использовал одну формулу, которую можно спокойно найти в интернете.

Но, получив ваш файл, понял, что она работает некорректно в том случае, если много повторяющихся по оцениваемому параметру данных внутри категорий (в вашем файле "Итого по артикулу"), особенно если разброс в числах невелик.

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

Так что пользуйтесь на здоровье и удачи в делах.

1С-ник не нужен. Это встроенная в отчеты и обработки возможность. И PooHkrd, вам правильную ссылку на инструкцию дал.

А почему вы из 1С в плоском виде не выгружаете? Она же позволяет это делать.

Человек хочет получить АВС-анализ не по общему количеству "Итого по артикулу", а внутри каждой категории

Как раз сцепляется результат, а не текст формул. Если бы сцеплялся текст формул,то в ячейке D27 было бы следующее:

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

ДВССЫЛ не работает с закрытыми книгами

Нужен пример с данными

Я ошибся, снимаю шляпу. Мой вариант не прокатит

БМВ, есть ошибки. Например, сеть1-сеть1 - количество 37, а не 23

Неправильно. Должно быть количество клиентов совершивших покупки, а у вас количество покупок

Есть ошибки. Например, сеть1-сеть1 - количество 37, а не 23

Можно ещё добавить спомогательные столбцы на лист "Данные", в которых указан признак того, что в той или иной сети совершена покупка. На листе"Финальная таблица" указать столбцы для каждой сети из этих вспомогательных таблиц именно так, как я указал и чеерз формулу "СУММПРОИЗВ" подсчитаь количество пересечений. Единственное "НО!" - при изменении вспомогательных столбцов придётся каждый раз их названия корректировать на листе "финальная таблица"

Т.к. у Вас в формулах не используются формулы массивов, то без макроса никак. Хотя есть и другой вариант - написать ТЗ программисту 1С,чтобы создал такой отчет или попытаться поискать похоже на профильных 1С сайтах

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

Нашел решение. Правда, пришлось прибегнуть к добавлению двух вспомогательных столбцов. Итак, суть решения проблемы в следующем:
1. Дабы не усложнять формулу, добавляем два столбца. Один из них считает количество дней наличия товара на складе, второй же - количество дней наличия на складе аналога. В случае, если аналога у товара нет, или аналога в таблице нет (например в 1С товар с нулевым остатком не отображается), то в соответствующую ячейку данного столбца ставится символ "-".
2. Теперь задача упрощается. Вот итоговая формула (см. файл) - {=ЕСЛИ(S2="-";R2;R2+S2-СУММПРОИЗВ(ЕСЛИ(ДВССЫЛ("C"&ПОИСКПОЗ(B2;A:A)&":"&"Q"&ПОИСКПОЗ(B2;A:A))>0;1;0);ЕСЛИ(C2:Q2>0;1;0)))}
Сначала проверяется есть ли у товара аналог и присутствует ли он в таблице, если "нет", то берется количество дней наличия товара на складе. Если есть аналог и он присутствует в таблице, то ищется диапазон ячеек, относящихся к аналогу, затем с помощью функции "ДВССЫЛ" он преобразовывается в ссылку, с помощью функции "СУММПРОИЗВ" вычисляется количество совпадений указанных диапазонов. Это количество отнимается от количества дней наличия аналога,тем самым мы получаем уникальное количество дней наличия на складе аналога. Остается только прибавить это число к количеству дней наличия товара на складе.


Буду признателен, если кто-то сможет упростить формулу.

Или берутся планы магазинов и их потребность в товаре, затем считается агрегирующий показатель необходимости и на основе его в два этапа распределяется товар. Например: есть два магазина - у магазина 1 план составляет 60 рублей, а потребность 3 штуки, у магазина 2 план составляет 40 рублей, потребность 5 штук. Соответственно, у магазина 1 доля плана 60%, а доля потребности 37,5%; у магазина 2 доля плана составляет 40%, а доля потребности - 62,5%. Итого агрегирующий показатель потребности у магазина 1 - 60+37,5=97,5, а у магазина 2 – 40+62,5=102,5. Таким образом, у магазина 1 доля распределения равна 97,5/(97,5+102,5)=48,75%, а у магазина 2 – 102,5//(97,5+102,5)=51,25%.
Вот теперь всё сделано "по справедливости". Остается распределить остаток склада в два этапа: первый этап - распределение по этим долям, второй - распределение остатка после первого этапа, например, в порядке убывания плана. Всё это легко сделать формулами. И не забудьте - если Вы распределяете штуки, а они находятся в коробках, то нужно учесть кратность упаковки,т.е. использовать функцию ОКРУГЛТ или ОКРВВЕРХ.МАТ, ОКРВНИЗ.МАТ

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

Здравствуйте коллеги!
Поступила мне интересная задачка, которую нужно решить без макроса.
Суть её в следующем - есть пул товаров с остатками по дням. Необходимо определить количество дней наличия товара на складе с учетом наличия аналога. При этом аналог может располагаться не в следующей за товаром строке, а в любой строке таблицы. В прилагаемом файле есть товары 1,2,3. У товара 1 аналог - товар 3. Видно,что количество дней наличия на складе товара 1 с учетом аналога равно 14.
Можно ли подсчитать это количество дней только формулой или всё-таки придется макросом?

P.S. Попробуйте на старом комьютере в Excel 2003 в 1000 строк и 10 столбцов вставьте формулу с использованием функции "РАБДЕНЬ" с учётом списка праздников за 3 года и посмотрите как быстро будет обрабатываться файл.

{quote}{login=The_Prist}{date=15.01.2011 03:24}{thema=Re: Re: }{post}{quote} Каким образом тормозит? {/post}{/quote}  
пердоставил бы, да коммерческая тайна. Скажу только, что в файле Excel собирается и обрабатывается информация из 60-ти файлов, в каждом по 6 листов, около 20000 строк, 30 столбцов с различными данными, расчёты проводятся по числам и датам. И всё это выполняется на старом одноядерном процессоре c 512 Mb в Excele 2003. Только не надо говорить, чтобы купили компьютер помощнее, это не я решаю.