День добрый, уважаемые гуру Excel. Каждый день сталкиваюсь с рутинной задачей работы подбора замен номеров. Есть исходная таблица со всеми возможными артикулами без дубликатов. Есть вторая таблица в которой есть замены. Как сделать общую таблицу с 2 колонками АРТИКУЛ и ЗАМЕНА, во второй колоне вывести, без дубликатов, все возможные варианты замен.
Порядка 100 000 артикулов в файле, периодически добавляются новые артикула и добавляются варианты замен. Пытался через формулу в Excel {=ОБЪЕДИНИТЬ("/";;ЕСЛИ(ТАБЛИЦА1[@АРТИКУЛ]=ТАБЛИЦА2[ЗАМЕНА];ТАБЛИЦА2[ЗАМЕНА];""))} она работает, но ограниченно, находит только четкий артикул без "/", но если использовать символы "*"&"/"&[@АРТИКУЛ] "*"&"/"&[@АРТИКУЛ]&"/"&"*" [@АРТИКУЛ]&"/"&"*" но результата нет, да и перекрестный поиск данная формула не делает.
Помогите решить задачу, т.к. перебрал все возможные варианты нет результата.
Оно пропускается т.к. его нет в 1 колонке с артикулами. Но его я вставил т.к. при поиске допустим есть A005 и есть A00555 это 2 разных числа, поэтому я думаю нужно использовать критерии
Его нет в исходном столбце АРТИКУЛ, у A00555 нет замен, просто есть артикула A001 и A0011 это разные артикула и при поиске A001 не должен находиться A0011, поэтому A00555 был вставлен как пример при поиске A005
Aleksei Vidin написал: Его нет в исходном столбце АРТИКУЛ, у A00555 нет замен
И не должно - так как он - ЗАМЕНА! При поиске A005 A00555 находиться не должен, но, так как он - ЗАМЕНА, он ДОЛЖЕН присутствовать в списке всех вариантов замен! Вам так не кажется?
Заготовка, можно сделать UDF или макросом заполнить/сгенерить таблицу. Только сортировки нет.
Код
Sub Perebor() 'коллекция в словаре
Dim a, i&, t, Dic As Object
Dim el, elel, col
a = Range("C2", Cells(Rows.Count, "A").End(xlUp)).Value
Set Dic = CreateObject("Scripting.Dictionary")
On Error Resume Next
With Dic
.CompareMode = 1
For i = 1 To UBound(a)
For Each t In Split(a(i, 3), "/")
For Each el In Split(a(i, 3), "/")
If Not .exists(t) Then .Add t, New Collection
.Item(t).Add el, el
Next
Next
Next
End With
For Each el In Dic.keys
For Each col In Dic.Item(el)
For Each elel In Dic.Item(col)
Dic.Item(el).Add elel, elel
Next
Next
Next
On Error GoTo 0
For Each el In Dic.keys
Debug.Print "Артикул " & el
For Each col In Dic.Item(el)
If col <> el Then Debug.Print "Замена " & col
Next
Next
End Sub
Вообще-то происходит, но смотреть нужно в окно отладки, я ведь написал что это заготовка. И код лучше располагать в стандартном модуле, хотя работает и из модуля листа. P.S. Я взял один из древних своих кодов из загашника, и чуть его дополнил, поэтому он конечно довольно топорный, но работает. Чтоб реально его применять - нужно бы сперва разобраться как/где вообще на практике это нужно?
используется для формирования заказа товаров, функция впр вытягивает из колонки замены соответствующие артикулам указанных в заказе и вставляет их в колонку замены в заказе и сейчас вручную просматриваются замены на наличие на складе, но планируется функцией перебирать замены указанные через "/" и выводить актуальную замену с необходимым колвом на складе.
Да просто можно вместо вывода в окно отладки сделать вывод на чистый лист. В два столбца как в примере, или в кучу столбцов правее сколько есть на замену. И далее с этого листа уже можете тянуть ВПРой. Можно сделать типа ВПР уже к готовому списку, чтоб заполнял диапазон. Можно сделать UDF, т.е. применять как функцию с двумя или тремя параметрами. Вы пока так и не описали что именно нужно ПРАКТИЧЕСКИ.
P.S. Да и сортировку можно приладить, но она будет воровать время, а если там 100000 строк то будет заметно. Но если применять в UDF и сортировать нужно только то, что конкретно выводится в одну ячейку, то можно и посортировать.
Hugo написал: Вы пока так и не описали что именно нужно
Объясняю практически: 1) Есть склад с запчастями у каждой запчасти есть артикул, наличие выгружается ексель файлом 2) Есть файл ексель список замен артикулов порядка 100 000 3) Есть заказ от клиента со списком необходимых запчастей.
Сейчас перебор актуальных замен артикулов происходит в ручном режиме через Ctrl+F, каждый артикул ищется и проверяется с наличием на складе. Может быть заказ быть на A001 на 10 шт. и в наличии есть A001 5 шт. и 5 шт. есть A008. Так вот если A008 не найти то он так и останется лежать на складе.
Поэтому и необходимо всего 2 колонки. 1 это все производившиеся когда либо артикулы без дублей отсортированы по алфавиту 2 все возможные варианты замен через "/" 3 это из 3 таблицы с заказом функцией впр найти ячейку с заменой и в ней перебрать все артикула и которые есть на складе вывести в таблицу заказа
Ну тогда наверное вариант 2: Можно сделать типа ВПР уже к готовому списку, чтоб заполнял диапазон. Или сразу идти дальше и искать наличие по складу первой детали, если мало то ищем вторую по замене и т.д.
P.S. Изменил в файле код чтоб он заполнял вторую таблицу.
Или вот так - в том файле пишите в E2 и E3 A001 A005 выделяете их, выполняете код
Код
Option Explicit
Sub Perebor2() 'коллекция в словаре
Dim a, i&, t, Dic As Object
Dim el, elel, col, s$, c As Range
a = Range("C2", Cells(Rows.Count, "A").End(xlUp)).Value
Set Dic = CreateObject("Scripting.Dictionary")
On Error Resume Next
With Dic
.CompareMode = 1
For i = 1 To UBound(a)
For Each t In Split(a(i, 3), "/")
For Each el In Split(a(i, 3), "/")
If Not .exists(t) Then .Add t, New Collection
.Item(t).Add el, el
Next
Next
Next
End With
For Each el In Dic.keys
For Each col In Dic.Item(el)
For Each elel In Dic.Item(col)
Dic.Item(el).Add elel, elel
Next
Next
Next
On Error GoTo 0
For Each c In Selection.Cells
s = "": el = Trim(c)
If Dic.exists(el) Then
For Each col In Dic.Item(el)
If col <> el Then s = s & "/" & col
Next
Else
s = "/Нет замен"
End If
c.Next = Mid(s, 2)
Next
End Sub
Hugo написал: Изменил в файле код чтоб он заполнял вторую таблицу.
Что вот так просто то, что делали вручную кучу времени, можно вот так за один проход сделать. Это гениально просто. Как я вам благодарен. Я не понимаю неужели за столько времени никто не сталкивался с такой задачей, ведь работа с замена нужна всегда. В интернете перебрал массу вариантов.
Я там дописал код который заполняет замены только для нужных выделенных кодов. Коды должны быть рядом в столбце, любом. Кстати если код оставить в модуле листа, то можно доработать (а может и так взлетит) для использования/заполнения диапазона в любом другом листе или даже файле. Т.е. есть база замен с этим кодом, и его применяете к любому другому клиентскому файлу с кодами, к которым нужно подобрать замены.
P.S. Проверил, работает и в другом файле вот прямо как есть. Но чуть изменил код выше чтоб обрабатывало случаи когда вписан код для которого в базе нет информации по замене.
Hugo написал: Проверил, работает и в другом файле вот прямо как есть.
Вообще все четко работает. 1000кратное спасибо. Теперь осталась вторая задача, поиск по наличию слова в ячейке, если есть запрос артикула A001 из ячейки с заменами A002/A009/A010/...A00N сВПРить (формулой не макросом) каждое значение и если оно есть на складе (остаток больше 0) вывести замены которые есть на складе A002/A010. З.xlsm(26.91 КБ)
P.S. На простом примере все работает отлично, но загрузив все 100000 замен в данный файл уже 5 часов Excel не отвечает, не видно что сейчас происходи или реально завис или вычисляет долго, но по загрузке ПК видно что как будто идут вычисления.
Уважаемые форумчане, помогите пожалуйста добить вопрос. Hugo предложил отличный макрос который выполняет обработку, но при его внедрении столкнулся с проблемами, при обработке более 1000 строк макрос уходит в зависание. Вставлял функцию DoEvents нет реакции, хотел внедрить макрос визуализации работы при помощи прогресс-бара, чтобы понять на какой стадии находится вычисления, результата нет все зависает.
Ошибка в данных, для этого кода нужны данные как в примере - в третьем столбце должны быть артикулы из первого. Ну или нужно менять как-то код. По зависанию не знаю, у меня не завис.
Hugo написал: в третьем столбце должны быть артикулы из первого.
Вы сказали, можно в любом месте справа выводить результат, поэтому 3 столбец убрал, вывожу справа от исходного столбца. Но попробовал вставить в пример в 3 столбец, также выделяю, запускаю макрос и все равно теряется вот этот артикул A000000471167. Должен получиться A0000004711 A0000004712/A000000471267/A000000471167
Ну при маленьком кол-ве данных обрабатывает но стоит загрузить порядка 50000 уходит в зависание. Пример прикрепить не могу т.к. по размеру не пропускает