Поводом послужила тема и предложенное там решение =СУММПРОИЗВ(--(""&A1=""&'1'!A$2:A$112);'1'!B2:B112) к нему претензий нет, но вспомнилось что не раз встречал упоминания о том, что СУММПРОИЗВ быстра, и в частности использование разных аргументов быстрее. Решил проверить. сделал стенд из файла той темы на 100112 строк источника и 1000 строк расчета и вот результат
=SUMPRODUCT(--(""&A1=""&'1'!A$2:A$100112);'1'!$B$2:$B$100112) - 11,60938 =SUMPRODUCT((""&A1=""&'1'!A$2:A$100112)*'1'!$B$2:$B$100112) - 11,82031 что подтверждает выше сказанное и барабанная дробь
buchlotnik, Михаил, он не предельно прост. Взял файл из темы по ссылке и размножил. Сюда не поместится, а писать несколько строк по генерации просто лениво.
Если поискать, найдется тема (давняя, в закрытом старом форуме где-то), где было сравнение двух функций. Были и тесты. В теме присутствовали KL (MVP), Serge_007, vikttur. Больше участников не помню... По результатам обсуждения выяснили, что СУММПРОИЗВ с вариантом --условие1;--условие;диапазон (без перемножения) предпочтительнее и экономия времени достигалась до 30%.
vikttur, Ну то что быстрее и мой результат подтверждает, может не на столько, но можно допускать что с тех пор 2016 стал оптимальнее считать, но меня больше результат массивной удивил. прикольно другое, решил не то чтоб перепроверить а просто изменить структуру данных, все 100000 одинаковы и все подходят под одинаковое условие 1000 раз
Sub tests()
Sheet1.UsedRange.ClearContents
Sheet2.UsedRange.ClearContents
Sheet1.Range("a1:a100000").Formula = "=2&randbetween(111111111111111,111111111111199)"
Sheet1.Range("B1:B100000").Formula = "=randbetween(1,100)"
Sheet2.Range("a1:a1000").Formula = "=2&randbetween(111111111111111,111111111111199)"
Application.Calculation = xlCalculationManual
DoEvents
Debug.Print "start"
With Sheet2.Range("b1:b1000")
.Formula = "=SUMPRODUCT(--(""""&A1=""""&Sheet1!$A$1:$A$100000),Sheet1!$B$1:$B$100000)"
t = Timer
.Calculate
Debug.Print .Cells(1).Formula, Timer - t
DoEvents
End With
With Sheet2.Range("c1:c1000")
.Formula = "=SUMPRODUCT((""""&A1=""""&Sheet1!$A$1:$A$100000)*Sheet1!$B$1:$B$100000)"
t = Timer
.Calculate
Debug.Print .Cells(1).Formula, Timer - t
DoEvents
End With
With Sheet2.Range("d1")
.FormulaArray = "=SUM((""""&A1=""""&Sheet1!$A$1:$A$100000)*Sheet1!$B$1:$B$100000)"
.AutoFill Destination:=Range("D1:D1000")
t = Timer
Range("D1:D1000").Calculate
Debug.Print .Cells(1).FormulaArray, Timer - t
End With
Application.Calculation = xlCalculationAutomatic
End Sub
In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an array-entered SUM.
VB
Copy {=SUM($D$2:$D$10301*$E$2:$E$10301)} =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301) =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301) These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the calculation time that the other two formulas need.
sokol92, Владимир, спс. ну судя по всему оптимизировали. Конечно интересно посмотреть результаты при разных структурных данных, но разница не на столько велика чтоб делать ставку на тот или иной метод. Удобнее - чуть более длинный вариант. Его и можно пользовать, разве что не утверждать что это самое быстрое решение.