Формула СУММПРОИЗВ

[staniiislav]

Приветы Всем!
Хорошего дня и классного настроения!

Сразу перейду к сути вопроса:
есть простая формула:

=СУММПРОИЗВ(($A$2:$A$10=$N4)*($B$2:$B$10=O$3)*$C$2:$C$10+($D$2:$D$10=$N4)*($E$2:$E$10=O$3)*$F$2:$F$10+($G$2:$G$10=$N4)*($H$2:$H$10=O$3)*$I$2:$I$10+($J$2:$J$10=$N4)*($K$2:$K$10=O$3)*$L$2:$L$10)

Которая считает по столбцам C,F,I,L сумму часов, по условиям A,D,G,J определенного номера и B,E,H,K определенного кода (в примере можно увидеть, где что находится)


Пытаюсь формулу которая выше переделать чтобы она была меньше (и возможно быстрее работала, т.к. таких формул может быть примерно 15000)

=СУММПРОИЗВ((ОСТАТ(СТОЛБЕЦ($A$1:$L$1);3)=0)*$A$2:$L$10)

считает все часы, как в эту формулу еще условия по номеру и коду добавить, подскажите пожалуйста?

[Hugo121]

Хм... Для 96:

=SUMPRODUCT((A2:J8=N4)*(B2:K8=O3)*C2:L8)

вернее баксов нужно добавить, чтоб тянулась вниз:

=SUMPRODUCT(($A$2:$J$8=$N4)*($B$2:$K$8=$O$3)*$C$2:$L$8)

[staniiislav]

спасибо за ответ! немного я не учел, эти формулы будут в самой таблице где производится поиск, и получается тогда циклические ссылки, нужно что-то другое придумывать (((

[staniiislav]

Сообщение от Hugo121

Хм... Для 96:

как Вы думаете, если UDF написать, может быстрее работать или нет (чем СУММПРОИЗВ)?

[Hugo121]

Я думаю всё зависит от задачи.
Например в примере - 4 раза просматривается диапазон (12 или 3 поддиапазона каждый раз, но это не важно).
Если будет 15000 - значит будет 15000 раз просматривать эти диапазоны.
Можно написать "массивную" UDF которая сделает всё дело за один проход по данным диапазона, и выгрузит результат в 15000 ячеек.
Но конечно если ей для работы нужно 30000 параметров - то это реализовать будет трудновато... Хотя опять же зависит от задачи - может это тоже будут всего лишь 2 массива...

[staniiislav]

ну вот набросал вот такую функцию:

Option Explicit

Function Sum(Korpus As Long, Kod As Long) 'As Variant
    Dim a(), i&, j&
    Sum = 0
    a = Range("E48:FC647").Value ' строк 600
    For i = 1 To UBound(a) ' столбцов по моему 160
        For j = 1 To UBound(a, 2) Step 5
            If a(i, j) = Korpus And a(i, j + 1) = Kod Then Sum = Sum + a(i, j + 2)
        Next j
    Next i
        Application.Volatile  
End Function

но все равно циклические ссылки есть, т.к. функция находится внутри диапазона где производится поиск.
Функции из оригинально файла, для примера нужно немного переделать

[staniiislav]

такая удф не катит ((( безумно долго работает (проверил на 1/10)

[Hugo121]

Я предлагаю принципиально другое - ОДНА UDF получает на вход диапазон поиска и 2 диапазона параметров, и формирует массив результатов 2х2. Вводить как формулу массива сразу в весь диапазон G4:P5.
Реализовывать конкретно лениво... т.к. непродуктивно, а код будет непростой.
Но возможны ситуации, когда такое писать оправданно. Я делал

Механизм может быть такой - сперва циклом по анализируемому диапазону собираем все суммы в словаре, затем циклом по массиву результатов из словаря заполняем его. Критерий - № &"|"& Код.

[staniiislav]

пока не пойму как это можно сделать буду думать

[Hugo121]

На примере - взяли диапазон в массив, пошли циклом сверху вниз и с шагом вправо - собираем суммы по всем встреченным парам номер|код в словаре.
Затем создали массив для результата (можно исходя из массивов параметров) и перебирая параметры (цикл в цикле) его заполняем из словаря.
В конце функция=массив - получаем результат на листе.