Страницы: 1
RSS
Извлечь с другого листа данные по трем условиям, ускорить вычисления
 
Добрый день.
Имеется файл с очень большим количеством строк и столбцов. На один лист необходимо вытаскивать данные по трем условиям с другого листа. Условия прописаны в трех разных ячейках, ищутся по трем разным столбцам. В данный момент это происходит индексом и тремя поисками позиции, формула массивная. Работает отлично, проблем нет, но ОЧЕНЬ медленно. Настолько, что все другие файлы начинают тормозить при открытии этого монстра. Есть ли способ заменить массивную формулу на что-то более быстрое или как то "ускорить" ее? Только переезд на VBA? Может написать пользовательскую функцию? Столбец сцепка не подходит, ибо файл поиска меняется каждый день на свежий.
 
Валерий Кишин, никто ничего не скажет если не увидим ваши данные, то что вы хотите получить на выходе и не опишите условия
Не бойтесь совершенства. Вам его не достичь.
 
в массивной формуле уменьшите диапазон (ссылайте не на весь столбец, а скажем 1000 строк - все зависит от размера исходных данных)
Изменено: mechanix 85 - 29.12.2021 11:11:24
 
Цитата
Валерий Кишин написал: Может написать пользовательскую функцию
... которая будет тормозить, как и формулы )

Цитата
переезд на VBA
Ну, UDF - это тоже VBA ) Но, да - обрабатывать процедурой Sub или макросом, реагирующим на изменение ячеек (последнее для случая, когда нужно искать одно значение при изменении одного из трех входных параметров).
Сортированные данные обрабатываются быстрее
 
Цитата
Валерий Кишин написал:
Настолько, что все другие файлы начинают тормозить при открытии этого монстра.
Это на самом деле так? Формула тормозит вычисления в других файлах? Возможно, Ваш файл перегружен лишним, как и другие файлы. Попробуйте создать чистый файл и открыть - будет тормозить? :) Это так, к слову :) Поверьте тормознутый файл  
Изменено: _Igor_61 - 29.12.2021 13:51:52
 
Цитата
написал:
Может написать пользовательскую функцию?
Код
Function ЯБЛОЧНАЯМАРИЯ(Продукт As Variant, Цена As Variant, Цвет As Variant, ДиапазонПродукт As Range, ДиапазонЦена As Range, ДиапазонЦвет As Range, ДиапазонВладелец As Range) As Variant
    Dim r As Range
    Set r = ДиапазонВладелец.Cells(1, 1)
    With r.Parent
        Dim arrVlad As Variant
        Dim arrProd As Variant
        Dim arrCena As Variant
        Dim arrCvet As Variant
        arrVlad = .Range(r, .Cells(.Rows.Count, r.Column).End(xlUp))
        Set r = Nothing
        Dim vlad As Variant
        If IsArray(arrVlad) Then
            arrProd = ДиапазонПродукт.Cells(1, 1).Resize(UBound(arrVlad, 1))
            arrCena = ДиапазонЦена.Cells(1, 1).Resize(UBound(arrVlad, 1))
            arrCvet = ДиапазонЦвет.Cells(1, 1).Resize(UBound(arrVlad, 1))
            
            Dim y As Long
            For y = 1 To UBound(arrVlad, 1)
                If arrProd(y, 1) = Продукт Then
                If arrCena(y, 1) = Цена Then
                If arrCvet(y, 1) = Цвет Then
                    vlad = arrVlad(y, 1)
                    Exit For
                End If
                End If
                End If
            Next
        End If
        
    End With
    ЯБЛОЧНАЯМАРИЯ = vlad
End Function
 
Цитата
написал:
файл поиска меняется каждый день на свежий
В т.ч. и столбцы? Поясните про "свежесть"
 
Как вариант PQ
Код
//GetValue
(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Продукт] = GetValue("var_1") and 
                                                                [Цена] = GetValue("var_2") and 
                                                                [Цвет] = GetValue("var_3") then 
                                                                [Вледелец] else "n/a"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> "n/a")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Custom", "Вледелец"}})
in
    #"Renamed Columns"
Страницы: 1
Наверх