Laske, jos useita ehtoja Excel ja Google Sheets

Lataa esimerkkityökirja

Lataa esimerkkityökirja

Thänen opetusohjelmansa osoittaa, kuinka COUNTIF- ja SUMPRODUCT -kaavojen avulla voidaan laskea useilla kriteereillä Microsoft Excelissä ja Google Sheetsissä.

COUNTIFS -toiminto

COUNTIFS -toiminnon avulla voit laskea arvot, jotka täyttävät useita ehtoja. Peruskaavan rakenne on:

1	= COUNTIFS (alue 1, ehto 1, alue 2, ehto 2)

Katsotaanpa esimerkkiä. Alla näet luettelon, joka sisältää englannin ja matematiikan opiskelijoiden arvosanat. Lasketaan kaikki opiskelijat, joiden testitulokset ovat yli 60 matematiikassa.

Tässä tapauksessa testaamme kahta kriteeriä:

1. Jos testitulokset ovat yli 60 (alue B2: B11)
2. Jos aihe on "matematiikka" (alue on C2: C11)

Alla näet, että 4 opiskelijaa teki yli 60 pistettä matematiikassa:

Voit testata lisäehtoja lisäämällä toisen alueen ja ehtoja. Jos esimerkiksi haluat laskea niiden oppilaiden määrän, jotka saivat 60–80 pistettä matematiikassa, käytä seuraavia kriteerejä:

1. Jos testitulokset ovat yli 60
2. Jos testitulokset ovat alle 80
3. Jos aihe on "matematiikka"

Tässä on korostetut kohdat, joissa näitä esimerkkejä testataan.

Tulos on tässä tapauksessa 3.

SUMPRODUCT -toiminto

COUNTIFS -toiminto on melko suoraviivainen, mutta voit suorittaa saman tehtävän edistyneemmän SUMPRODUCT -toiminnon avulla. SUMPRODUCT -toiminto avaa mahdollisuuksien maailman monimutkaisilla kaavoilla (katso SUMPRODUCT -opetusohjelma). Joten vaikka COUNTIFS -toiminto on helpoin tapa laskea useiden kriteerien perusteella, kannattaa ehkä tutustua tähän tehokkaaseen toimintoon. Sen syntaksi on:

1	= SUMPRODUCT (Alue 1 = Ehto 1*Alue 2 = Ehto 2)

Yllä olevan esimerkin avulla voit laskea kaikki opiskelijat, joiden testitulokset ovat yli 60 aineessa "Matematiikka", käyttämällä tätä kaavaa:

1	= SUMPRODUCT ((B3: B13> 60)*(C3: C13 = ”Math”))

Ensimmäinen ehto testaa, mitkä arvot tällä alueella ovat suurempia kuin 60, ja toinen ehto testaa, mitkä arvot tällä alueella ovat yhtä suuret kuin "Matematiikka". Tämä antaa meille joukon Boolen arvoja 1 (TOSI) tai 0 (EPÄTOSI):

<>

SUMPRODUCT -funktio laskee sitten kaikki taulukon kohteet yhteen ja antaa meille niiden opiskelijoiden määrän, jotka saivat matematiikassa noin 60 pistettä.

COUNTIFS ja SUMPRODUCT useille kriteereille Google Sheetsissä

Voit käyttää useita ehtoja käyttämällä samoja kaavarakenteita kuin yllä Google Sheetsissä.