Vispārēja formula
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Kopsavilkums
Lai vienā sarakstā uzskaitītu vērtības, kuru trūkst citā sarakstā, varat izmantot formulu, kuras pamatā ir funkcijas COUNTIF un SUMPRODUCT.
Parādītajā piemērā formula H6 ir:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Kas atgriež 1, jo vērtība "Osborne" neparādās B6: B11.
Paskaidrojums
Funkcijas COUNTIF pārbauda diapazona vērtības atbilstoši kritērijiem. Bieži vien tiek piegādāts tikai viens kritērijs, taču šajā gadījumā mēs piegādājam vairāk nekā vienu kritēriju.
Par diapazonā, mēs dodam COUNTIF nosaukts diapazons List1 (B6: B11), kā arī kritērijus, mēs piedāvājam nosaukto diapazonu List2 (F6: F8).
Tā kā mēs piešķiram COUNTIF vairāk nekā vienu kritēriju, rezultātu masīvā mēs iegūstam vairākus rezultātus: (2; 1; 0)
Mēs vēlamies saskaitīt tikai tās vērtības, kuru trūkst, kurām pēc definīcijas ir nulle, tāpēc mēs pārvēršam šīs vērtības par TRUE un FALSE ar teikumu "= 0", kas dod: (FALSE; FALSE; TRUE)
Tad ar dubultnegatīvu operatoru (-) mēs piespiežam TRUE FALSE vērtības līdz 1s un 0s, kas rada: (0; 0; 1)
Visbeidzot, mēs izmantojam SUMPRODUCT, lai summētu masīvā esošos vienumus un atgrieztu trūkstošo vērtību kopējo skaitu.
Alternatīva ar MATCH
Ja vēlaties vairāk burtisku formulu, varat izmantot tālāk norādīto formulu, kuras pamatā ir MATCH, kas burtiski uzskaita vērtības, kuras "trūkst", izmantojot funkciju ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
