Yleinen kaava
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Yhteenveto
Voit laskea yhdestä luettelosta arvot, jotka puuttuvat toisesta luettelosta, käyttämällä kaavaa, joka perustuu COUNTIF- ja SUMPRODUCT-funktioihin.
Esitetyssä esimerkissä kaava H6: ssa on:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Joka palauttaa arvon 1, koska arvo "Osborne" ei näy kohdassa B6: B11.
Selitys
COUNTIF-funktiot tarkistaa alueen arvot ehtojen perusteella. Usein vain yksi kriteeri toimitetaan, mutta tässä tapauksessa toimitamme useamman kuin yhden kriteerin.
Alueelle annamme COUNTIF: lle nimetty alue- luettelo1 (B6: B11) ja ehtojen osalta annamme nimetyn alueen luettelon2 (F6: F8).
Koska annamme COUNTIF: lle useamman kuin yhden ehdon, saamme useamman kuin yhden tuloksen tulosryhmässä, joka näyttää tältä: (2; 1; 0)
Haluamme laskea vain puuttuvat arvot, joilla on määritelmän mukaan nolla lukumäärä, joten muunnamme nämä arvot TOSI ja EPÄTOSI lausekkeella "= 0", joka tuottaa: (EPÄTOSI; EPÄTOSI; TOSI)
Sitten pakotamme TOSI EPÄTOSI -arvot 1: een ja 0: een kaksois-negatiivisella operaattorilla (-), joka tuottaa: (0; 0; 1)
Lopuksi käytämme SUMPRODUCT-sovellusta taulukon kohteiden lisäämiseen ja puuttuvien arvojen palauttamiseen.
Vaihtoehto MATCH: n kanssa
Jos haluat enemmän kirjaimellisia kaavoja, voit käyttää alla olevaa kaavaa, joka perustuu MATCH: iin, joka kirjaimellisesti laskee puuttuvat arvot ISNA-funktion avulla:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
