Bendroji formulė
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Santrauka
Norėdami suskaičiuoti vieno sąrašo vertes, kurių nėra kitame sąraše, galite naudoti formulę, pagrįstą funkcijomis COUNTIF ir SUMPRODUCT.
Parodytame pavyzdyje formulė H6 yra:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Kuris grąžina 1, nes reikšmė „Osborne“ nerodoma B6: B11.
Paaiškinimas
Funkcijos COUNTIF tikrina diapazono reikšmes pagal kriterijus. Dažnai pateikiamas tik vienas kriterijus, tačiau šiuo atveju pateikiame daugiau nei vieną kriterijų.
Diapazonui suteikiame COUNTIF pavadintą diapazonų sąrašą1 (B6: B11), o pagal kriterijus pateikiame pavadintą diapazonų sąrašą2 (F6: F8).
Kadangi COUNTIF pateikiame daugiau nei vieną kriterijų, gauname daugiau nei vieną rezultato masyvo rezultatą, kuris atrodo taip: (2; 1; 0)
Norime suskaičiuoti tik tas vertes, kurių trūksta, o pagal apibrėžimą jų skaičius yra lygus nuliui, todėl šias reikšmes paverčiame TRUE ir FALSE teiginiu „= 0“, kuris duoda: (FALSE; FALSE; TRUE)
Tada verčiame TRUE FALSE reikšmes į 1s ir 0s su dvigubai neigiamuoju operatoriumi (-), kuris sukuria: (0; 0; 1)
Galiausiai mes naudojame SUMPRODUCT, kad susumuotume elementus iš masyvo ir grąžintume bendrą trūkstamų verčių skaičių.
Alternatyva su MATCH
Jei norite daugiau pažodinių formulių, galite naudoti toliau pateiktą formulę, pagrįstą MATCH, kuri pažodžiui suskaičiuoja reikšmes, kurių „trūksta“ naudojant ISNA funkciją:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
