Formuła Excela: Średnia pensja tygodniowo -

Formuła ogólna

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

Podsumowanie

Aby obliczyć średnią pensję tygodniową, z wyłączeniem tygodni, w których nie zarejestrowano żadnych godzin i bez obliczonej już całkowitej pensji tygodniowej, możesz użyć formuły opartej na funkcjach SUMPRODUCT i LICZ.JEŻELI. W przedstawionym przykładzie formuła w J5 to:

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

który zwraca średnią płacę tygodniową, z wyłączeniem tygodni, w których nie zarejestrowano żadnych godzin. To jest formuła tablicowa, ale nie jest konieczne wprowadzanie za pomocą control + shift + enter, ponieważ funkcja SUMPRODUCT może natywnie obsługiwać większość operacji tablicowych.

Wyjaśnienie

Na początku możesz pomyśleć, że ten problem można rozwiązać za pomocą funkcji AVERAGEIF lub AVERAGEIFS. Jednak ponieważ całkowita tygodniowa pensja nie jest częścią arkusza roboczego, nie możemy korzystać z tych funkcji, ponieważ wymagają one zakresu.

Pracując od wewnątrz, najpierw obliczamy łączną pensję za wszystkie tygodnie:

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

Jest to operacja tablicowa, która mnoży godziny przez stawki, aby obliczyć tygodniowe kwoty płac. Rezultatem jest tablica taka:

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

Ponieważ arkusz zawiera 6 tygodni, tablica zawiera 6 wartości. Ta tablica jest zwracana bezpośrednio do funkcji SUMPRODUCT:

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

Funkcja SUMPRODUCT zwraca następnie sumę elementów w tablicy, 840. W tym momencie mamy:

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

Następnie funkcja LICZ.JEŻELI zwraca liczbę wartości większych od zera w zakresie D5: I5. Ponieważ 2 z 6 wartości są puste, a program Excel ocenia puste komórki jako zero, LICZ.JEŻELI zwraca 4.