Формула Excel: Средняя заработная плата в неделю -

Общая формула

=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")

Резюме

Чтобы рассчитать среднюю заработную плату в неделю, за исключением недель, когда часы не регистрировались, и без уже рассчитанной общей заработной платы за неделю, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и СЧЁТЕСЛИ. В показанном примере формула J5 имеет следующий вид:

=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")

который возвращает среднюю заработную плату за неделю, за исключением недель, когда часы не регистрировались. Это формула массива, но ее необязательно вводить с помощью Ctrl + Shift + Enter, поскольку функция СУММПРОИЗВ изначально может обрабатывать большинство операций с массивами.

Объяснение

Сначала вы могли подумать, что эту проблему можно решить с помощью функции СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИ. Однако, поскольку общая заработная плата в неделю не является частью рабочего листа, мы не можем использовать эти функции, потому что они требуют диапазона.

Работая изнутри, мы сначала рассчитываем общую зарплату за все недели:

D5:I5*D6:I6 // total pay for all weeks

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

(87,63,48,0,12,0) // weekly pay amounts

Поскольку на листе 6 недель, массив содержит 6 значений. Этот массив возвращается непосредственно в функцию СУММПРОИЗВ:

SUMPRODUCT((348,252,192,0,48,0))

Затем функция СУММПРОИЗВ возвращает сумму элементов в массиве, 840. На этом этапе у нас есть:

=840/COUNTIF(D5:I5,">0")

Затем функция СЧЁТЕСЛИ возвращает количество значений больше нуля в диапазоне D5: I5. Поскольку 2 из 6 значений пусты, а Excel оценивает пустые ячейки как ноль, СЧЁТЕСЛИ возвращает 4.