Страницы: 1
RSS
подбор по номеру из наименования
 
добрый день,

Возник вопрос, нужна формула чтобы на листе 1 в столбце "Артикул" появились значения с листа 2 с столбца "Артикул" по соответствующему номеру в столбце "Наименование ТМЦ" столбцу  "Номер" на листе 1. таблица очень большая, из более чем 300 тысяч артикулов необходимо выбрать всего 1000

спасибо.
 
Вариант формулами.
Лист1!B4:B9
Код
=ИНДЕКС(Лист2!A:A;ПОИСКПОЗ(A:A;Лист2!C:C;0))
Лист2!C4:C9
Код
=ПСТР(B4;ПОИСК("0";B4);ДЛСТР(B4))
Учитывая количество строк, то лучше делать макросом.
 
Почитайте ТУТ
Или так, в Лист1!B4 и протянуть вниз:
Код
=ИНДЕКС(Лист2!$A$4:$B$9;ПОИСКПОЗ("*"&A4&"*";Лист2!$B$4:$B$9;0);1)
Изменено: Msi2102 - 19.05.2022 12:59:25
 
Вариант макросом
Код
Sub GetArt()
    Dim dic As Object
    Set dic = GetDic(Sheets("Лист2"))
    If dic.Count > 0 Then
        FillFromDic Sheets("Лист1"), dic
    End If
End Sub

Private Sub FillFromDic(sh As Worksheet, dic As Object)
    Dim ar1 As Variant
    Dim ar2 As Variant
    Dim yy As Long
    Dim rn As Range
    With sh
        yy = .Cells(.Rows.Count, 1).End(xlUp).Row
        ar1 = .Range(.Cells(1, 1), .Cells(yy, 1))
        Set rn = .Range(.Cells(1, 2), .Cells(yy, 2))
    End With
    ar2 = rn
    For yy = 4 To UBound(ar1, 1)
        If dic.Exists(ar1(yy, 1)) Then
            ar2(yy, 1) = dic.Item(ar1(yy, 1))
        Else
            ar2(yy, 1) = Empty
        End If
    Next
    rn = ar2
End Sub

Private Function GetDic(sh As Worksheet) As Object
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    
    Dim arr As Variant
    Dim yy As Long
    With sh
        yy = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Range(.Cells(1, 1), .Cells(yy, 2))
    End With
    
    Dim fullstr As String
    Dim substr As String
    Dim ii As Long
    For yy = 4 To UBound(arr, 1)
        fullstr = arr(yy, 2)
        ii = InStrRev(fullstr, " ")
        If ii > 0 Then
            If ii < Len(fullstr) Then
                substr = Mid(fullstr, ii + 1, Len(fullstr))
                dic.Item(substr) = arr(yy, 1)
            End If
        End If
    Next
    Set GetDic = dic
End Function
 
Может подойдёт и вариант PQ.(если количество знаков номере всегда восемь)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"Номер"},Table1,{"Наименование ТМЦ"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Артикул"}, {"NewColumn.Артикул"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.Артикул", "Артикул"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Номер"})
in
    #"Removed Columns"
Изменено: jakim - 19.05.2022 13:46:12
 
огромное спасибо, через ИНДЕКС и ПОИСКПОЗ как нужно
 
Еще в PQ:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Артикул", Int64.Type}, {"Наименование ТМЦ", type text}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Пользовательская", each ЧтоИскать),
    #"Развернутый элемент Пользовательская" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Пользовательская", {"Номер"}, {"Пользовательская.Номер"}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент Пользовательская", "Совпадения", each if Text.Contains([Наименование ТМЦ], [Пользовательская.Номер]) then [Артикул] else null),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Условный столбец добавлен", each [Совпадения] <> null and [Совпадения] <> ""),
    #"Переименованные столбцы" = Table.RenameColumns(#"Строки с примененным фильтром",{{"Совпадения", "Артикул_Совпадения"}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Переименованные столбцы",{"Артикул_Совпадения"})
in
    #"Удаленные столбцы"
Страницы: 1
Читают тему (гостей: 1)
Наверх