Страницы: 1
RSS
Запрос между листами с сортировкой по дате и 10 максимальным значениям
 
Всем добрый день!

Уже давно ищу информацию по своему вопросу, но никак не могу найти ответ. Либо плохо ищу, т.к. с написанием макросов и работой в PQ только начинаю знакомиться...

В общем, что имею:
1. Основная таблица "Сводка", находящаяся на первом листе/книге, в которой ежедневно ведется отчет по покупкам клиентов, с указанием даты, названием клиента, объемом закупа за текущую дату.
2. Таблица "5 лучших", находящаяся на втором листе, либо вообще в отдельной книге, в которую должны копироваться данные из таблицы "Сводка" таким образом, чтобы в нее попадали только 5 самых больших сумм покупок клиентов за текущую дату, ну и в столбце "Наименование клиент" указывалось его название.

Таким образом в таблицу "Сводка" данные заносятся постоянно и каждый день, соответственно дата будет меняться от старой к новой ежедневно.
Нужно, чтобы в таблице "5 лучших", при внесении изменений и новый записей в таблицу "Сводка", данные заполнялись автоматически. Либо, если книга с таблицей будет закрыта, то при ее открытии данные обновлялись по запросу/автоматически, с сортировкой сумм от максимального к минимальному.

Причем, запись значений в таблицу "5 лучших" может происходить таким образом, что если в диапазон значений будет попадать равная сумма по двум клиентам, то в таблицу "5 лучших" должно записываться оба результата (из-за чего в какие-то даты может быть не 5, а например 6, 7 и т.д. значений, но при этом сумма покупок каждого должна входить в диапазон 5 максимальных сумм покупок за день). Таким образом, название клиента за текущий день уникальное, а их сумма покупок за день может быть не уникальным.

Структура обеих таблиц на разных листах (количество и название столбцов) одинаковая, нужно, чтобы во вторую таблицу попадала только "нужная" информация. То есть, при отборе 5 максимальных значений, под строкой с наименьшим из 5 значений, не было пустых строк и т.п.

Понятно, что отбор в таблице "5 лучших" можно делать вручную с помощью настраиваемой сортировки, но все же может есть возможность автоматизировать этот процесс? Буду признателен за помощь.

таблица "222" - "Сводка", таблица "333" - "5 лучших"
 
Если что, то таблица "222" - "Сводка", таблица "333" - "5 лучших"
 
Код
Sub Start()
    Dim sh1 As Worksheet: Set sh1 = Workbooks("222.xlsx").Worksheets(1)
    Dim sh2 As Worksheet: Set sh2 = Workbooks("333.xlsx").Worksheets(1)
    
    Dim dic1 As Object
    Set dic1 = GetMax5(sh1)
    
    ProcDic sh1, sh2, dic1
End Sub
    
Function GetMax5(sh As Worksheet) As Object
    Dim y1 As Long
    Dim y2 As Long
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    With sh
        y1 = 2
        Do
            y2 = y1 + WorksheetFunction.CountIfs(.Columns(1), .Cells(y1, 1).Value) - 1
            dic(.Cells(y1, 1).Value) = WorksheetFunction.Large(.Range(.Cells(y1, 3), .Cells(y2, 3)), 5)
            y1 = y2 + 1
            If IsEmpty(.Cells(y1, 1)) Then Exit Do
        Loop
    End With
    
    Set GetMax5 = dic
End Function
Sub ProcDic(sh1 As Worksheet, sh2 As Worksheet, dic As Object)
    
    Dim y1 As Long
    Dim y2 As Long
    Dim a As Variant
    With sh1
        y1 = .Cells(.Rows.Count, 1).End(xlUp).Row
        a = .Range(.Cells(2, 1), .Cells(y1, 3))
    End With
    
    With sh2
        y2 = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(2, 1), .Cells(y2, 3)).ClearContents
    
        For y1 = 1 To UBound(a, 1)
            If a(y1, 3) >= dic(a(y1, 1)) Then
                y2 = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Cells(y2, 1).Value = a(y1, 1)
                .Cells(y2, 2).Value = a(y1, 2)
                .Cells(y2, 3).Value = a(y1, 3)
            End If
        Next
    End With
End Sub
 
МатросНаЗебре,спасибо огромное за столь быстрый ответ! Если я правильно понял, это код для макроса?
К сожалению на данный момент я в макросах не разбираюсь от слова "совсем", думал, что нужные мне операции можно как-то выполнить с помощью формул, построения массива или через Power Query, но будет чем заняться!)
 
Цитата
Bettor54 написал:
можно как-то выполнить с помощью формул,
=E1+(C2>=НАИБОЛЬШИЙ((A2=$A$2:$A$34)*($C$2:$C$34);5)) - формула массива. Вставить в 222.xlsx в E2, потом F2 Ctrl+Shift+Enter, протянуть вниз.
=ЕСЛИОШИБКА(ИНДЕКС([222.xlsx]Лист1!$A$1:$C$34;ПОИСКПОЗ(СТРОКА()-1;[222.xlsx]Лист1!$E:$E;0);СТОЛБЕЦ());"") вставить в 333.xlsx в A2 и скопировать формулу в столбцы A:C.
 
Цитата
Bettor54 написал:
или через Power Query,
Скрытый текст
невнимательно прочитал задачу, вот так надо:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(from, {"Дата"}, {{"temp", each Table.FirstN(Table.Sort(Table.Group(_,{"Сумма покупок"},{"temp2", each _, type table}),{"Сумма покупок", Order.Descending}),5), type table}}),
    to = Table.Combine(Table.Combine(group[temp])[temp2])
in
    to
Изменено: buchlotnik - 11.12.2019 10:31:57
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
МатросНаЗебре написал:
=E1+(C2>=НАИБОЛЬШИЙ((A2=$A$2:$A$34)*($C$2:$C$34);5))
Цитата
МатросНаЗебре написал:
=E1+(C2>=НАИБОЛЬШИЙ((A2=$A$2:$A$34)*($C$2:$C$34);5)) - формула массива. Вставить в 222.xlsx в E2, потом F2 Ctrl+Shift+Enter, протянуть вниз.=ЕСЛИОШИБКА(ИНДЕКС([222.xlsx]Лист1!$A$1:$C$34;ПОИСКПОЗ(СТРОКА()-1;[222.xlsx]Лист1!$E:$E;0);СТОЛБЕЦ());"") вставить в 333.xlsx в A2 и скопировать формулу в столбцы A:C.
Спасибо! буду пробовать!
 
Цитата
buchlotnik написал:
let    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],    group = Table.Group(from, {"Дата"}, {{"temp", each Table.FirstN(Table.Sort(Table.Group(_,{"Сумма покупок"},{"temp2", each _, type table}),{"Сумма покупок", Order.Descending}),5), type table}}),    to = Table.Combine(Table.Combine(group[temp])[temp2])in    to
Спасибо огромнейшее! Это тоже то, что нужно! Буду пробовать так же и ваш способ, т.к. мои таблицы чуть посложнее, чем то, что в примере, но общая суть та же.
 
МатросНаЗебре, по Вашему способу все тоже отлично работает, только один момент - на выходе, в столбце "Сумма покупок", значения идут не от большего к меньшему, а в разнобой. По большому счету это не столь критично, так как Ваш способ и формула значительно упрощает мою изначальную задачу, но все же может я что-то не так записал...
 
Список формируется в порядке, в котором данные представлены в файле 333.xlsx.
 
Цитата
Цитата
МатросНаЗебре написал:
Список формируется в порядке, в котором данные представлены в файле 333.xlsx.
С этим моментом разобрался, спасибо!
Страницы: 1
Наверх