Страницы: 1
RSS
Поиск в таблице по множеству критериев из другой таблицы и вывод результата
 
День добрый,форумчане!
Вчера возникла проблема, которую я не понял как решить, возможно мозг уже не соображал.
Суть: есть Таблица1, в которой все поля всегда забиты и всегда меняются (так как таблица обновляется). Каждую строку этой таблицы надо проверить данными из второй таблицы на максимальное совпадение условий, указанных в строках Таблица2, и вывести ответ о выполнении сравнения. Данные во второй таблице по факту вбиваются вручную и некоторые из них могут отсутствовать (все кроме критерий 1).
Что я пробовал: покопался по форуму и подобной темы не нашел (ну, нашел, но далее объясню в чем проблема), пробовал сделать объединение таблиц в PQ, но там столкнулся с проблемой пустых ячеек. Возможно это как-то решается через макросы, но в VBA я насквозь дуб-дубом.
Касательно того что нашел: так как проВПРить такое количество условий невозможно, я пробовал добавить условие ЕСЛИ в ВПР формулу, но не очень понял, как зациклить ЕСЛИ, чтоб при отсутствии критерия формула не выдавала ошибку.
Очень прошу помочь, пример приложен.
Изменено: multsib - 30.03.2020 17:13:26 (орфография просто капец)
 
Цитата
multsib написал:
надо проверить данными из второй таблицы на максимальное совпадение условий
Добрый день! Как понять "на максимальное совпадение" ?
 
Андрей_26, во второй таблице имеются строки типа приведенной ниже таблицы. И строка имеющая больше критериев относительно такой же но с меньшим количеством - приоритетней (тобеж поиск совпадений в Строке 4 первой таблицы должен захватить максимум критериев из второй таблицы, удовлетворяющим поиску)
Изменено: multsib - 29.03.2020 15:29:26 (добавил картинку)
 
multsib, может, ожидаемый результат покажите в отдельной таблице
 
Михаил Л, дополнил таблицу и цветом (слева от ячейки) обозначил какое условие выполнено для заполнения требуемой строки с результатом
 
multsib, не уверен что правильно, но посмотрите в файле.
Взято из приемов https://www.planetaexcel.ru/techniques/2/197/

желтые ячейки диапазон для условий
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, супер фильтр, почитаю и попробую использовать.
Но прямое решение моего вопроса, дабы дополнить загружаемую базу оно не решает.  
 
multsib, по вашему я чет не понял где что откуда берется и почему..я понял где условия но не понял что должно получиться ИМХО
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, сейчас сформулирую правильно, напишу.  
 
Цитата
multsib написал:
сейчас сформулирую
Код
let
   fn=(x)=> [
    a = Record.FieldValues(Source2{x}), //*получаем значения текущей строки
     b = Table.ColumnNames(Source2), //* получаем названия столбцов таблицы
    c = #table(2,List.Zip({b,a})), //* создаем таблицу
    e = Table.SelectRows(c, each [Column2] <> null), //* извлекаем значения, не равных нулю в определенном столбце
    d =  Table.SelectRows(e,  each [Column1] <> "критерий 4"), //* извлекаем значения, не равных определенному значению в определенном столбце
    f = Table.RenameColumns(Source,List.Zip({Table.ColumnNames(Source),b&{"цена","id"}})), //* одна из таблиц с переименованными столбцами
    g = Table.NestedJoin(#table(d[Column1],{d[Column2]}),d[Column1],f,d[Column1],"Source2",JoinKind.LeftOuter), //* соединение двух таблиц по динамическому списку столбцов соединения
    h = try Table.SelectRows(e,  each [Column1] = "критерий 4")[Column2]{0} otherwise null, //* извлекаем значение, равное определенному значению в определенном столбце с заменой возможной ошибки
    j = Text.Remove(h,{"<",">","="}), //* в тексте удаляем определенные значения
    i = Text.Remove(h,{"0".."9"}), //* в тексте удаляем определенные значения
   k = if i="<=" then Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [критерий 4] <= Number.From(j))}}) else
    if i=">=" then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [критерий 4] >= Number.From(j))}}) else
    if i="="  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [критерий 4] = Number.From(j))}}) else
    if i="<"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [критерий 4] < Number.From(j))}}) else
    if i=">"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [критерий 4] > Number.From(j))}}) else  g][k][Source2]{0}, //* несколько проверок на равенство значения, при найденном равенстве выполняется извлечение значений по соответствующему фильтру. При не найденном равенстве возвращается не фильтрованная таблица
  
    Source = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], "id"), //* таблица со столбцом индекса
    Source2 = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content], //* таблица
    Add = Table.AddColumn(Source2, "add", each fn(_)), //* добавляем столбец, обработанный функцией
    #"Added Custom" = Table.AddColumn(Add, "Count", each List.NonNullCount(Record.ToList(_))), //* добавляем столбец, со значением количества ненулевых значений в текущей строке
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "add", {"цена", "id"},{"цена", "id2"}), //* развернули пару столбцов
    #"Grouped Rows" = Table.Group(#"Expanded {0}", {"id2","цена"}, {{"b", each [a= Table.Sort(_,{{"Count", Order.Descending}}), b=a{0}][b]}}), //* сгруппировали с сортировкой в группе и извлечением первой записи после сортировки от макс к мин
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Grouped Rows", "b", {"критерий 1", "критерий 2", "критерий 3", "критерий 4"}), //* развернули запись
    Merge = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded {0}1", {{"цена", type text}}, "ru-RU"),{"критерий 1", "цена"},Combiner.CombineTextByDelimiter(" / ", QuoteStyle.None),"add")[[id2],[add]], //* соединили пару столбцов. В итоге оставили пару столбцов
    #"Merged Queries" = Table.Join(Source,{"id"},Merge,{"id2"},JoinKind.LeftOuter), //* соединение двух столбцов
    #"Sorted Rows" = Table.Sort(#"Merged Queries",{{"id", Order.Ascending}}), //* сортировка нарушенного порядка строк таблицы
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"id", "id2"}) //* удалили пару столбцов
in
    #"Removed Columns"
Изменено: Михаил Л - 30.03.2020 15:53:02 (комментарии)
 
Михаил Л, ничего себе. Спасибо за старания, я буду у компьютера попробую. А есть у Вас возможность разжевать что за что тут отвечает? И куда это надо прописать.
Просто если заменить формулу запроса в PQ, то на строке с фильтрацией, как я понимаю, выдаёт ошибку
Изменено: multsib - 30.03.2020 17:18:14 (Не догоняет, потому что тупой...)
 
multsib, комментарии позже напишу
Добавил комментарии выше
Изменено: Михаил Л - 30.03.2020 15:54:36
 
Цитата
Михаил Л написал:
Добавил комментарии выше
Михаил, это великолепный макрос!!!! А можно еще один тупой вопрос? Смотрите, если у меня база подгружается на один лист, а условия подгружаются на другой, а результат мне надо закинуть на третий лист, или если столбцов будет больше на 1-2, то что и где надо поменять, чтоб не запороть такой шедевр?
если надо могу скинуть пример с теми условиями, что сейчас описал.
 
Цитата
multsib написал:
у меня база подгружается на один лист, а условия подгружаются на другой, а результат мне надо закинуть на третий лист
Надо только выгрузить итоговою таблицу на третий лист, а остальное не влияет
Цитата
multsib написал:
столбцов будет больше на 1-2
Пробовали добавить?
Если еще столбец со знаками равенства(<>=), то не сработает. Знаки равенства только в столбце "критерий 4"
А так пример посмотреть бы
Изменено: Михаил Л - 30.03.2020 16:19:37
 
Цитата
Михаил Л написал:
А так пример посмотреть
Вот, что-то типа такого (количество строк в обеих таблицах может меняться):
Изменено: multsib - 30.03.2020 17:06:04 (Прошло больше 5-7 минут, обязан был докинуть пример. Слово надо держать)
 
Цитата
multsib написал:
что-то типа такого
Нет, запрос не будет работать. Проблема будет в столбце цена
 
Цитата
Михаил Л написал:
Проблема будет в столбце цена
тобеж надо по максимуму ужать условия?
тогда другой вопрос, если имена столбцов будут изменены, то это надо будет просто в запросе PQ по факту обращения их так-же изменить?
 
Цитата
multsib написал:
имена столбцов будут изменены
Не гарантирую что будет работать
В файле два варианта. На этом все)
 
Михаил Л, Вы и так очень помогли, огромное спасибо!  
 
Цитата
Михаил Л написал:
На этом все)
Михаииииил, пожалуйста, еще одна ПОСЛЕДНЯЯ просьба...
Можете ли сказать что дописать, так, чтоб в последнюю ячейку выгруженной таблица выводилось не "поставщик / цена" из "Таблицы1" а полное сцепка всех данных из строки с критерием из "Таблицы2", желательно, без знаков разделения и пробелов в случае отсутствия данных в ячейке критерия. это прям очень надо, так как появилось еще 1 условие, но оно подвязано на результате конкретной строки таблицы с критерием.

Прошу прощения за беспокойство, я помню про то, что разговор окончен.
Изменено: multsib - 31.03.2020 19:46:16 (Не тот файлик был прикреплён)
 
Цитата
multsib написал:
что разговор окончен
Не в том дело что мне не до этого. Надо сразу представлять себе конечный результат. У нас же получается так: надо сделать так. Теперь надо добавить следующее. А теперь надо так. А завтра еще что нибудь придумать.
Почему в файле нет ожидаемого результата?
 
Цитата
Михаил Л написал:
Надо сразу представлять себе конечный результат
у меня была конечная цель, и я её добьюсь, если появится возможность эти строки помечать как полную сцепку критериев
Цитата
Михаил Л написал:
Почему в файле нет ожидаемого результата?
прошу прощения, не тот файлик прикрепил (обновил там же)
 
Код
let
   fn=(x)=> [
    a = Record.ToList(x),
    b = Record.FieldNames(x),
    c = #table(2,List.Zip({b,a})),
    e = Table.SelectRows(c, each [Column2] <> null),
    d = Table.SelectRows(e,  each [Column1] <> "размер"),
    f = Table.RenameColumns(Source,List.Zip({Table.ColumnNames(Source),b&{"цена","id"}})),
    g = Table.NestedJoin(#table(d[Column1],{d[Column2]}),d[Column1],f,d[Column1],"Source2",JoinKind.LeftOuter),
    h = try Table.SelectRows(e,  each [Column1] = "размер")[Column2]{0} otherwise null,
    j = Text.Remove(h,{"<",">","="}),
    i = Text.Remove(h,{"0".."9"}),
    k =if i="<=" then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] <= Number.From(j))}}) else
       if i=">=" then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] >= Number.From(j))}}) else
       if i="="  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] =  Number.From(j))}}) else
       if i="<"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] <  Number.From(j))}}) else
       if i=">"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] >  Number.From(j))}}) else  g][k][Source2]{0},
  
    Source = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], "id"),
    Source2 = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Add = Table.AddColumn(Source2, "add", each fn(_)),
    #"Added Custom" = Table.AddColumn(Add, "Count", each List.NonNullCount(Record.ToList(_))),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "add", {"цена", "id"},{"цена", "id2"}),
    #"Grouped Rows" = Table.Group(#"Expanded {0}", {"id2","цена"}, {{"b", each [a= Table.Sort(_,{{"Count", Order.Descending}}), b=a{0}][b]}}),
    names=Table.ColumnNames(Source2),
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Grouped Rows", "b",names),
    Merge = Table.CombineColumns(#"Expanded {0}1",names,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"add")[[id2],[add]],
    #"Merged Queries" = Table.Join(Source,{"id"},Merge,{"id2"},JoinKind.LeftOuter),
    #"Sorted Rows" = Table.Sort(#"Merged Queries",{{"id", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"id", "id2"})
in
    #"Removed Columns"
по моему так
 
Михаил Л,спасибо... всё великолепно, но возникла проблема со знаками сравнения, прилагаю сам файлик с доработкой и скрины ошибки

Я так понял, это из-за того, что данные подгружаются в таблицу не вручную.
Изменено: multsib - 01.04.2020 21:20:22 (удалена ссылка на файл)
 
Код
// Таблица1 (4)
let
   fn=(x)=> [
    a = Record.ToList(x),
    b = Record.FieldNames(x),
    c = #table(2,List.Zip({b,a})),
    e = Table.SelectRows(c, each [Column2] <> null),
    d = Table.SelectRows(e,  each [Column1] <> "размер"),
    f = Table.RenameColumns(Source,List.Zip({Table.ColumnNames(Source),b&{"цена","id"}})),
    g = Table.NestedJoin(#table(d[Column1],{d[Column2]}),d[Column1],f,d[Column1],"Source2",JoinKind.LeftOuter),
    h = try Table.SelectRows(e,  each [Column1] = "размер")[Column2]{0} otherwise null,
    j = Text.Remove(h,{"<",">","="}),
    i = Text.Remove(h,{"0".."9"}),
    k =if i="<=" then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] <= Number.From(j))}}) else
       if i=">=" then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] >= Number.From(j))}}) else
       if i="="  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] =  Number.From(j))}}) else
       if i="<"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] <  Number.From(j))}}) else
       if i=">"  then  Table.TransformColumns(g,{{"Source2", each Table.SelectRows(_, each [размер] >  Number.From(j))}}) else  g][k][Source2]{0},
   
    Source = Table.TransformColumnTypes(Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], "id"),{{"размер", type number}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Add = Table.AddColumn(Source2, "add", each fn(_)),
    #"Added Custom" = Table.AddColumn(Add, "Count", each List.NonNullCount(Record.ToList(_))),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "add", {"цена", "id"},{"цена", "id2"}),
    #"Grouped Rows" = Table.Group(#"Expanded {0}", {"id2","цена"}, {{"b", each [a= Table.Sort(_,{{"Count", Order.Descending}}), b=a{0}][b]}}),
    names=Table.ColumnNames(Source2),
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Grouped Rows", "b",names),
    Merge = Table.CombineColumns(#"Expanded {0}1",names,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"add")[[id2],[add]],
    #"Merged Queries" = Table.Join(Source,{"id"},Merge,{"id2"},JoinKind.LeftOuter),
    #"Sorted Rows" = Table.Sort(#"Merged Queries",{{"id", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"id", "id2"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Removed Columns", each [поставщик] <> null and [поставщик] <> "")
in
    #"Строки с примененным фильтром"
Изменено: Михаил Л - 01.04.2020 21:10:14
Страницы: 1
Наверх