Yleinen kaava
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Yhteenveto
Voit laskea keskimääräisen viikkopalkan, lukuun ottamatta viikkoja, joihin ei ole kirjattu tunteja, ja ilman jo laskettua viikkopalkkaa, voit käyttää kaavaa, joka perustuu SUMPRODUCT- ja COUNTIF-funktioihin. Esitetyssä esimerkissä kaava kohdassa J5 on:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
joka palauttaa keskimääräisen viikkopalkan lukuun ottamatta viikkoja, joihin ei kirjattu tunteja. Tämä on matriisikaava, mutta sitä ei tarvitse syöttää ohjaimella + shift + enter, koska SUMPRODUCT-toiminto pystyy käsittelemään luonnollisesti useimpia matriisitoimintoja.
Selitys
Voit ensin ajatella, että tämä ongelma voidaan ratkaista AVERAGEIF- tai AVERAGEIFS-toiminnolla. Koska viikkopalkka ei kuitenkaan ole osa laskentataulukkoa, emme voi käyttää näitä toimintoja, koska ne edellyttävät aluetta.
Työskentelemällä sisältä ulospäin, laskemme ensin kaikkien viikkojen kokonaispalkan:
D5:I5*D6:I6 // total pay for all weeks
Tämä on matriisitoiminto, joka kertoo tunnit hinnoilla viikkopalkkojen laskemiseksi. Tuloksena on tällainen taulukko:
(87,63,48,0,12,0) // weekly pay amounts
Koska laskentataulukossa on 6 viikkoa, taulukko sisältää 6 arvoa. Tämä taulukko palautetaan suoraan SUMPRODUCT-toimintoon:
SUMPRODUCT((348,252,192,0,48,0))
SUMPRODUCT-funktio palauttaa sitten matriisin 840 kohteiden summan. Tässä vaiheessa meillä on:
=840/COUNTIF(D5:I5,">0")
Seuraavaksi COUNTIF-funktio palauttaa nollaa suurempien arvojen määrän alueella D5: I5. Koska 2 kuudesta arvosta on tyhjä ja Excel arvioi tyhjät solut nollaksi, COUNTIF palauttaa 4.
=840/4 =210
Lopputulos on 840 jaettuna 4: llä, mikä on 210
