SumProduct по наборам ячеек (не непрерывный)

Question

SumProduct по наборам ячеек (не непрерывный)

У меня есть общий набор данных, который является для 4 различных группировок. Одно из значений является средним временем, другой количество. Для Общего количества я должен умножить их и затем разделиться на общее количество количества. В настоящее время я использую:

=SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218)

Я использовал бы SumProduct, если я могу для создания его более читаемым. Я пытался сделать:

=SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218)

Но поскольку можно сказать моей регистрацией здесь, которая не работала. Существует ли способ сделать SumProduct как, я хочу?

5

excel worksheet-function

задан Lance Roberts 6 July 2011 в 08:34

2 ответа

Было бы полезно создать промежуточную таблицу, в которой суммированы данные, которые вы используете для вычисления суммарного произведения. Это также упростило бы выполнение расчетов.

0

ответ дан 18 December 2019 в 14:44

Другие вопросы по тегам:

excel worksheet-function

Похожие вопросы:

score 5 · Accepted Answer

Это могло бы быть возможно с мастерским excel-fu, но даже если это возможно, оно вряд ли будет более читабельным, чем ваше исходное решение. Проблема в том, что даже по прошествии 20 с лишним лет Excel все еще работает с прерывистыми диапазонами. Присвоение им имен не сработает, формулы массива не будут работать, и, как вы видите, с помощью SUMPRODUCT, они обычно не работают в функциях массива кортежей. Лучше всего придумать настраиваемую функцию.

ОБНОВЛЕНИЕ

Ваш вопрос заставил меня задуматься о том, как обрабатывать прерывистые диапазоны. Это не то, с чем мне приходилось часто сталкиваться в прошлом. У меня не было времени дать лучший ответ, когда вы задали вопрос, но теперь, когда у меня есть несколько минут, я создал специальную функцию, которая будет делать то, что вы хотите:

Function gvSUMPRODUCT(ParamArray rng() As Variant)

    Dim sumProd As Integer
    Dim valuesIndex As Integer
    Dim values() As Double

    For Each r In rng()
        For Each c In r.Cells
            On Error GoTo VBAIsSuchAPainInTheAssSometimes
                valuesIndex = UBound(values) + 1
            On Error GoTo 0
            ReDim Preserve values(valuesIndex)
            values(valuesIndex) = c.Value
        Next c
    Next r
    If valuesIndex Mod 2 = 1 Then
        For i = 0 To (valuesIndex - 1) / 2
            sumProd = sumProd + values(i) * values(i + (valuesIndex + 1) / 2)
        Next i
        gvSUMPRODUCT = sumProd
        Exit Function
    Else
        gvSUMPRODUCT = CVErr(xlErrValue)
        Exit Function
    End If

VBAIsSuchAPainInTheAssSometimes:
    valuesIndex = 0
    Resume Next

End Function

Некоторые примечания:

Excel перечисляет диапазоны по столбцам, а затем по строкам, поэтому, если у вас есть непрерывный диапазон, в котором данные организованы по столбцам, вам нужно выбрать отдельные диапазоны: gvSUMPRODUCT (A1: A10, B1: B10), а не gvSUMPRODUCT (A1: B10).
Функция работает путем попарного умножения первой половины ячеек на вторую и последующего суммирования этих произведений: gvSUMPRODUCT (A1, C3, L2, B2, G5, F4) = A1 * B2 + C3 * G5 + L2 * F4. Т.е. порядок имеет значение.
Вы можете расширить функцию, включив в нее умножение по n, выполнив что-то вроде gvNSUMPRODUCT (n, range).
Если имеется нечетное количество ячеек (не диапазонов), возвращается ошибка #VALUE.