У меня есть два диапазона, RangeA и RangeB. Я хотел бы по существу сделать следующее:
=SUMPRODUCT(RangeA, 1/RangeB) / TotalItems
Чтобы получить средний процент. Однако оба диапазона могут иметь пустые ячейки, разбросанные по ним, и я хочу включить соответствующие ячейки из RangeA и RangeB только тогда, когда они не являются пустыми.
Я пробовал варианты на следующем:
=SUMPRODUCT((RangeA<>"")*RangeA, 1/((RangeB<>"")*RangeB)) / TotalItems
Но все, что я пробовал с SUMPRODUCT до сих пор, дает мне "# DIV/0!"msgstr "ошибка, потому что это просто приводит к преобразованию пробелов в нули (обратите внимание, что использование RangeB<>0 приводит к тому же результату).
Я мог бы легко использовать следующую пользовательскую функцию:
Function AvgPerc(Range1 As Range, Range2 As Range) As Single
Dim ind As Integer, inVal As Single, outVal As Single, _
Perc As Double, TotPerc As Double, valCount As Integer
For ind = 1 To Range1.Rows.Count
If IsNumeric(Range1.Item(ind, 1)) And IsNumeric(Range2.Item(ind, 1)) Then
inVal = Range1.Item(ind, 1)
outVal = Range2.Item(ind, 1)
If inVal <> 0 And outVal <> 0 Then
Perc = CDbl(outVal) / CDbl(inVal)
TotPerc = TotPerc + Perc
valCount = valCount + 1
End If
End If
Next ind
If valCount = 0 Then
AvgPerc = 0
Else
AvgPerc = TotPerc / valCount
End If
End Function
Однако для этого необходимо, чтобы рабочая книга была сохранена как функция с поддержкой макросов, и будут люди, использующие эту книгу, у которых не включены макросы, и я не хочу просить их включить макросы для чего-то простого: это (в дополнение к тому факту, что это может вызвать замешательство у людей, которые начнут использовать книгу позже, когда я не смогу объяснить, что происходит).
В конечном счете, я думаю, что смогу использовать именованные диапазоны, чтобы сделать это без макроса, но я не уверен, как удалить "# DIV/0!""результаты из именованного диапазона, и мне действительно нужно продублировать все поведение в пользовательской функции выше.
Пример данных:
Обратите внимание, что в идеале я бы мог включить пустые строки внизу каждого столбца, чтобы люди могли добавить больше данных позже.
