Vispārēja formula
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Kopsavilkums
Lai uzskaitītu šūnas, kas nav vienādas ar daudzām lietām, varat izmantot formulu, kuras pamatā ir funkcijas MATCH, ISNA un SUMPRODUCT. Parādītajā piemērā formula šūnā F5 ir:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kur "dati" ir nosauktais diapazons B5: B16 un "izslēgt" ir nosauktais diapazons D5: D7.
Paskaidrojums
Pirmkārt, nedaudz konteksta. Parasti, ja jums ir tikai pāris lietas, kuras nevēlaties skaitīt, varat izmantot šādus COUNTIFS:
=COUNTIFS(range,"apple",range,"orange")
Bet tas nav ļoti labi, ja jums ir saraksts ar daudzām lietām, jo katrai lietai, kuru nevēlaties skaitīt, jums jāpievieno papildu diapazons / kritēriju pāris. Būtu daudz vieglāk izveidot sarakstu un kā daļu no kritērijiem nodot atsauci uz šo sarakstu. Tieši to dara šīs lapas formula.
Būtībā šī formula izmanto funkciju MATCH, lai atrastu šūnas, kas nav vienādas ar "a", "b" vai "c" ar šo izteicienu:
MATCH(data,exclude,0)
Ņemiet vērā, ka uzmeklēšanas vērtība un uzmeklēšanas masīvs ir "apgriezti" no parastās konfigurācijas - visas vērtības no nosauktā diapazona "dati" sniedzam kā uzmeklēšanas vērtības un visas vērtības, kuras mēs vēlamies izslēgt, nosauktajā diapazonā "izslēgt". Tā kā mēs piešķiram MATCH vairāk nekā vienu uzmeklēšanas vērtību, mēs iegūstam vairāk nekā vienu rezultātu tādā masīvā:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Būtībā MATCH dod mums atbilstošu vērtību pozīciju kā skaitli un visām pārējām vērtībām atgriež # N / A.
# N / A rezultāti ir tie, kas mūs interesē, jo tie attēlo vērtības, kas nav vienādas ar "a", "b" vai "c". Attiecīgi mēs izmantojam ISNA, lai piespiestu šīs vērtības PATIESĪBĀ un skaitļus - PAMATOT:
ISNA(MATCH(data,exclude,0)
Tad mēs izmantojam dubultnegatīvu, lai piespiestu TRUE uz 1 un FALSE uz nulli. Rezultātā esošais masīvs SUMPRODUCT iekšpusē izskatās šādi:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Apstrādājot tikai vienu masīvu, SUMPRODUCT summē un atgriež gala rezultātu 4.
Piezīme. Izmantojot SUMPRODUCT, nevis SUM, nav nepieciešams izmantot vadības taustiņu + shift + enter.
Skaits mīnus mačs
Vēl viens veids, kā skaitīt šūnas, kas nav vienādas ar vairākām lietām, ir visu vērtību saskaitīšana un atbilstību atņemšana. To var izdarīt ar šādu formulu:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Šeit COUNTA atgriež visu tukšo šūnu skaitu. Funkcija COUNTIF, ņemot vērā nosaukto diapazonu "izslēgt", atgriezīs trīs skaitījumus, pa vienam katram saraksta vienumam. SUMPRODUCT summē kopsummu, un šis skaitlis tiek atņemts no visu tukšo šūnu skaita. Galīgais rezultāts ir šūnu skaits, kas nav vienādas ar vērtību "izslēgt".
Literal satur tipa loģiku
Šajā lapā esošā formula tiek skaitīta ar “vienāds ar” loģiku. Ja jums jāuzskaita šūnas, kurās nav daudz virkņu, kur ietverts, virkne var parādīties jebkur šūnā, jums būs nepieciešama sarežģītāka formula.
