Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Функция поиска и сопоставления в Power Query, аналог ВПР в Excel’е
 
#1
15.03.2021 15:13:27
Добрый день!

Уважаемые пользователи,  помогите пожалуйста разобраться с задачей.
Как сделать функцию поиска для Power Query, аналог ВПР в Excel’е, чтобы сопоставить две таблицы и найти необходимые значения?

В приложенном файле есть две таблицы:
1.      Таблица_для_заполнения
2.      Таблица_источник

Нужно для каждой фамилии и даты в «Таблице_для_заполнения» найти и сопоставить значение, соответствующее значению выручки в «Таблице_источнике». Если такой даты во второй таблице нет, то нужно найти и сопоставить значение, соответствующее ближайшей дате в прошлом.

Заранее благодарен.
 
 
 
#2
15.03.2021 15:49:11
Возьмем, для примера, Продавца с фамилией Гоголь, для него в таблице подстановки имеются 2 даты 10.12.2014 и 12.12.2014, а в таблице поиска есть "ближайшая продажа в прошлом" от 08.12.2014.
Внимание вопрос, что подставлять к дате 12.12.2014?
Изменено: PooHkrd - 15.03.2021 15:50:11
Вот горшок пустой, он предмет простой...
 
 
 
#3
15.03.2021 16:26:56
Добрый день!

Учитывая условия задачи - брать ближайшую дату в прошлом, то по продавцу с фамилией Гоголь должна быть следующая ситуация:
Изменено: Георгий Шкуро - 15.03.2021 16:29:30 (Некорректная загрузка рисунка)
 
 
 
#4
15.03.2021 16:47:12
Странная логика. Пробуйте.
Код
let
    to = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_для_заполнения"]}[Content],{{"Дата", type date}}),
    from = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_источник"]}[Content],{{"Дата", type date}}),
    combine = Table.AddColumn(from, "ДатаИсточник", each [Дата], Date.Type) & Table.AddColumn(to, "tab", each 1, Int64.Type),
    sorted = Table.Sort(combine,{{"Продавец", Order.Ascending}, {"Дата", Order.Ascending}, {"tab", Order.Ascending}}),
    filledDown = Table.FillDown(sorted,{"Выручка", "ДатаИсточник"}),
    filtered = Table.SelectRows(filledDown, each ([tab] = 1)),
    removed = Table.RemoveColumns(filtered,{"tab"})
in
    removed
Изменено: PooHkrd - 16.09.2021 17:15:48
Вот горшок пустой, он предмет простой...
 
 
 
#5
15.03.2021 19:06:06
PooHkrd, изящное решение ))))

"&"   -  выступает аналогом функции Table.Combine?
 
 
 
#6
16.03.2021 00:09:47
Blood81, мопед не мой, алгоритмом со мной любезно поделился Андрей VG. Вот использую по маленьку.. На так-то, если по думать, то интервальных поиск ВПР в упорядоченном массиве действует примерно такой же.
Амперсанд, да, это замена комбайна.
Вот горшок пустой, он предмет простой...
 
 
 
#7
22.03.2021 12:50:04

Добрый день!

Коллеги, большое спасибо за предложенное решение, действительно - простое и элегантное!

Правда, преподаватель не оценил сие отличное решение, указал на то, что задача должна была быть решена с использованием пользовательской функции. А вот, что брать в качестве аргументов, как-то указано не было, очевидно решение отдано на откуп фантазии студентов.

Еще раз спасибо!
 
 
 
#8
22.03.2021 14:34:47
Георгий Шкуро, если вопрос в решении поставленной задачи, то странный препод, пускай сравнит по скорости мой вариант и с применением "пользовательской функции". Тогда поспорим. Данный алгоритм - самая быстрая реализация интервального поиска из мне известных. Если же решение должно было быть реализовано конкретным способом, то это другой вопрос.
Но при любых раскладах я за эффективность кода, каким бы непривычным не выглядело бы решение.
Хотя, можно и функцией, ну, если это принципиально!
Код
let
    to = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_для_заполнения"]}[Content],{{"Дата", type date}}),
    from = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_источник"]}[Content],{{"Дата", type date}}),
    fx = (from, to) =>
    let 
        combine = Table.AddColumn(from, "ДатаИсточник", each [Дата], Date.Type) & Table.AddColumn(to, "tab", each 1, Int64.Type),
        sorted = Table.Sort(combine,{{"Продавец", Order.Ascending}, {"Дата", Order.Ascending}, {"tab", Order.Ascending}}),
        filledDown = Table.FillDown(sorted,{"Выручка", "ДатаИсточник"}),
        filtered = Table.SelectRows(filledDown, each ([tab] = 1)),
        removed = Table.RemoveColumns(filtered,{"tab"})
    in
        removed,
    out = fx(from, to)
in
    out
Изменено: PooHkrd - 22.03.2021 14:40:53
Вот горшок пустой, он предмет простой...
 
 
 
#9
22.03.2021 15:17:37
Цитата
PooHkrd написал:
пускай сравнит по скорости
да вся беда, что не будет он ничего сравнивать, сказали пользовательской функцией, значит пользовательской функцией - как несколько лет назад приходили за решением задачки - "вычислить таблицу умножения с использованием ровно четырех функций Excel"- и там ваялось СТЕПЕНЬ(ПРОИЗВЕД(СТРОКА();СТОЛБЕЦ());1)
так что видимо нужно какую-нибудь такую ахинею:
Код
(f,d,t,c)=> Record.SelectFields(Table.Last(Table.SelectRows(t, each [Продавец]=f and [Дата]<=d)),c)
Изменено: buchlotnik - 22.03.2021 15:17:51
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#10
28.08.2023 16:54:47
Все работает!  Огромное спасибо всем!!

Хорошего дня!
 
 
 
Страницы: 1
Наверх