Страницы: 1
RSS
VBA. Аналог функции SumIf для умной таблицы
 
Доброго времени суток!
Помогите, пожалуйста.
Не могу найти аналог функции SumIf для умной таблицы. Может вообще не предусмотрено в VBA  для Data Tables?
 
mbk61, а что не так с обычным Sumif? есть еще DSUM
Изменено: Mershik - 26.09.2020 20:32:16
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
mbk61 написал:
Может вообще не предусмотрено в VBA  для Data Tables?
навивает мысль что вы хотите работать по названию полей, так?
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
навивает мысль что вы хотите работать по названию полей, так?
Привет, Михаил.
Выскажу догадку, что ТС хочет иметь в VBA такой же набор высокоуровневых функций и структур данных, чтобы использовать в коде такие же подходы как и в случае работы с формулами на листе Excel. В общем случае обращение к столбцу данных таблицы Workbooks("BookName.extension").Worksheets("SheetName").ListObjects("TableName").ListColumns("ColumnName").DataBodyRange.Value возвращает двумерный массив данных столбца. А уже с этими данными можно работать в том числе и функциями, определяемыми в Application.WorksheetFunction. В частности Application.WorksheetFunction.SumIf
 
Андрей VG, Андрей VG,  Утречко доброе.
Это справедливо для функций которые работают с массивом для SUMIF ,как пример нужно скармливать диапазон.
Ну альтернативой может быть функция написанная в приdычном для листа виде и обернутая в Evaluate
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
в приdычном для листа виде и обернутая в Evaluate
Вот только смысл? Надеяться что это будет быстрее и быстрее в каком контексте: создания кода или времени выполнения? Или всё же лучше декомпозировать задачу и написать код на VBA? Что то мне подсказывает, что это может быть быстрее в плане выполнения :)
 
Цитата
Андрей VG написал:
Вот только смысл?
Вот тут согласен.
Цитата
Андрей VG написал:
Что то мне подсказывает, что это может быть быстрее в плане выполнения
а вот тут зависит от многих факторов. Как правило функции быстрее.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
функции быстрее.
Тест. Создаём умную таблицу "Таблица1" со столбцом key с формулой =СЛУЧМЕЖДУ(1;200) и value с формулой =СЛЧИС() размножаем на 1000000 строк. Превращаем в значения. Для оценки скорости выполнения на VBA, такой код
Код
Public Sub sumIfByVBA()
    Dim pLo As ListObject, resultColumn As ListColumn
    Dim pDict As New Scripting.Dictionary
    Dim keys, values, i As Long, t As Single
    t = Timer
    Set pLo = ActiveSheet.ListObjects("Таблица1")
    keys = pLo.ListColumns("key").DataBodyRange.Value
    values = pLo.ListColumns("value").DataBodyRange.Value
    For i = 1 To UBound(keys)
        pDict(keys(i, 1)) = pDict(keys(i, 1)) + values(i, 1)
    Next
    For i = 1 To UBound(keys)
        values(i, 1) = pDict(keys(i, 1))
    Next
    Set resultColumn = pLo.ListColumns.Add
    resultColumn.DataBodyRange.Value = values
    MsgBox Timer - t
End Sub

У меня выполнился за 2,2 секунды. Для оценки формульного решения - новый столбец с формулой =СУММЕСЛИ([key];[@key];[value]) После 30 секунд остановил вычисление и прибил столбец :)   Тестировалось по Excel 2016 32bit.
P. S. Да, летом Microsoft хвалилось, что в Excel 365 подобные расчёты они стали оценивать - не выполняется ли для единого блока, и если да, то формула работает как для единого массива, но в 2016 для каждой ячейки полный набор вычислений и счастья медленного выполнения. :)
 
WorksheetFunction в коде работает медленнее, чем на листе, об этом где-то в справке читал.
 
Андрей, ну тест не показательный. Точнее он показывает не то что функция листа медленная, а то что ко всему с умом нужно подходить. Естественно миллион раз пробежаться по миллиону строк - это "несколько" дольше, чем тоже сделать два раза , даже с погрешностью на поиск в словаре.
Ну у меня на весь столбец за 5,5 секунд рассчитал пример с ключом от 1 до 200 , но стоило симулировать работу функции, а именно изменить на от 1 до 10000000, так вот , крутит , греет процессор.
Так что тут не функция медленная, а применение ее опасно для окружающей среды. Собственно с этим часто сталкиваются при использовании выводе списка уникальных, для примера в 10-100 строк все "летает", а потом вопрос "у меня 100 000 строк, считает ооочень медленно!!!" А чего удивительного, только на COUNTIF квадратное увеличение операций.

UPD. Пока писал , дочитал за 104сек
Изменено: БМВ - 27.09.2020 10:18:08
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
квадратное увеличение операций.
Да, проблема в этом. Нужен другой алгоритм, да и скорее всего другой процесс работы. Я так и не понимаю, зачем нужно показывать сумму по ключу для каждого его вхождения в таблице.
Цитата
БМВ написал:
даже с погрешностью на поиск в словаре.
А вот это место, пожалуйста, поподробнее. Как, на каком наборе данных было установлено, что поиск в словаре даёт погрешность?
 
Цитата
Андрей VG написал:
А вот это место, пожалуйста, поподробнее.
Андрей, я о том что размер словаря имеет значение для скорости, но естественно не меняет итоговый результат. То есть
Цитата
БМВ написал:
миллион раз пробежаться по миллиону строк - это "несколько" дольше, чем тоже сделать два раза , даже с погрешностью на поиск в словаре.
читаем как
миллион раз пробежаться по миллиону строк - это "несколько" дольше, чем тоже сделать два раза  + время на поиск в словаре.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх