День добрый,форумчане! Вчера возникла проблема, которую я не понял как решить, возможно мозг уже не соображал. Суть: есть Таблица1, в которой все поля всегда забиты и всегда меняются (так как таблица обновляется). Каждую строку этой таблицы надо проверить данными из второй таблицы на максимальное совпадение условий, указанных в строках Таблица2, и вывести ответ о выполнении сравнения. Данные во второй таблице по факту вбиваются вручную и некоторые из них могут отсутствовать (все кроме критерий 1). Что я пробовал: покопался по форуму и подобной темы не нашел (ну, нашел, но далее объясню в чем проблема), пробовал сделать объединение таблиц в PQ, но там столкнулся с проблемой пустых ячеек. Возможно это как-то решается через макросы, но в VBA я насквозь дуб-дубом. Касательно того что нашел: так как проВПРить такое количество условий невозможно, я пробовал добавить условие ЕСЛИ в ВПР формулу, но не очень понял, как зациклить ЕСЛИ, чтоб при отсутствии критерия формула не выдавала ошибку. Очень прошу помочь, пример приложен.
Андрей_26, во второй таблице имеются строки типа приведенной ниже таблицы. И строка имеющая больше критериев относительно такой же но с меньшим количеством - приоритетней (тобеж поиск совпадений в Строке 4 первой таблицы должен захватить максимум критериев из второй таблицы, удовлетворяющим поиску)
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, то на строке с фильтрацией, как я понимаю, выдаёт ошибку
Михаил, это великолепный макрос!!!! А можно еще один тупой вопрос? Смотрите, если у меня база подгружается на один лист, а условия подгружаются на другой, а результат мне надо закинуть на третий лист, или если столбцов будет больше на 1-2, то что и где надо поменять, чтоб не запороть такой шедевр? если надо могу скинуть пример с теми условиями, что сейчас описал.
Пробовали добавить? Если еще столбец со знаками равенства(<>=), то не сработает. Знаки равенства только в столбце "критерий 4" А так пример посмотреть бы
тобеж надо по максимуму ужать условия? тогда другой вопрос, если имена столбцов будут изменены, то это надо будет просто в запросе PQ по факту обращения их так-же изменить?
Михаииииил, пожалуйста, еще одна ПОСЛЕДНЯЯ просьба... Можете ли сказать что дописать, так, чтоб в последнюю ячейку выгруженной таблица выводилось не "поставщик / цена" из "Таблицы1" а полное сцепка всех данных из строки с критерием из "Таблицы2", желательно, без знаков разделения и пробелов в случае отсутствия данных в ячейке критерия. это прям очень надо, так как появилось еще 1 условие, но оно подвязано на результате конкретной строки таблицы с критерием.
Прошу прощения за беспокойство, я помню про то, что разговор окончен.
Не в том дело что мне не до этого. Надо сразу представлять себе конечный результат. У нас же получается так: надо сделать так. Теперь надо добавить следующее. А теперь надо так. А завтра еще что нибудь придумать. Почему в файле нет ожидаемого результата?
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"
// Таблица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
#"Строки с примененным фильтром"