Bendroji formulė
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Santrauka
Norėdami suskaičiuoti ląsteles, kurios nėra lygios nė vienam iš daugelio dalykų, galite naudoti formulę, pagrįstą funkcijomis MATCH, ISNA ir SUMPRODUCT. Parodytame pavyzdyje formulė langelyje F5 yra:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kur „duomenys“ yra pavadintas diapazonas B5: B16, o „neįtraukti“ - pavadintas diapazonas D5: D7.
Paaiškinimas
Pirma, šiek tiek konteksto. Paprastai, jei turite tik keletą dalykų, kurių nenorite skaičiuoti, galite naudoti tokius COUNTIFS:
=COUNTIFS(range,"apple",range,"orange")
Bet tai nėra labai gerai, jei turite daugelio dalykų sąrašą, nes kiekvienam daiktui, kurio nenorite skaičiuoti, turite pridėti papildomą diapazono / kriterijų porą. Būtų daug lengviau sudaryti sąrašą ir kaip kriterijų dalį pateikti nuorodą į šį sąrašą. Būtent tai daro šio puslapio formulė.
Iš esmės ši formulė naudoja funkciją MATCH, kad rastų ląsteles, kurios nėra lygios „a“, „b“ ar „c“ su šia išraiška:
MATCH(data,exclude,0)
Atkreipkite dėmesį, kad paieškos vertė ir paieškos masyvas yra „pakeisti“, palyginti su įprasta konfigūracija - visas reikšmes iš nurodyto diapazono „duomenys“ pateikiame kaip paieškos reikšmes ir pateikiame visas reikšmes, kurias norime išskirti pavadintame diapazone „neįtraukti“. Kadangi MATCH suteikiame daugiau nei vieną paieškos reikšmę, gauname daugiau nei vieną rezultatą tokiame masyve:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Iš esmės „MATCH“ pateikia mums atitikimo reikšmių padėtį kaip skaičių ir grąžina # N / A visoms kitoms reikšmėms.
# N / A rezultatai yra tie, kurie mus domina, nes jie reiškia reikšmes, kurios nėra lygios „a“, „b“ ar „c“. Atitinkamai mes naudojame ISNA, kad priverstume šias reikšmes PATIKTI, o skaičius - NETIESA:
ISNA(MATCH(data,exclude,0)
Tada mes naudojame dvigubą neigiamą reikšmę, kad vertė TRUE būtų 1, o FALSE - nulis. Gautas masyvas SUMPRODUCT viduje atrodo taip:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Turint tik vieną masyvą apdoroti, SUMPRODUCT susumuoja ir pateikia galutinį rezultatą 4.
Pastaba: Naudojant SUMPRODUCT vietoj SUM, nereikės naudoti valdymo + Shift + Enter.
Skaičius minusas rungtynės
Kitas būdas skaičiuoti ląsteles, kurios nėra lygios nė vienam iš kelių dalykų, yra visų reikšmių skaičiavimas ir atitikmenų atimimas. Tai galite padaryti naudodami tokią formulę:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Čia COUNTA pateikia visų ne tuščių langelių skaičių. Funkcija COUNTIF, atsižvelgiant į pavadintą diapazoną „neįtraukti“, grąžins tris skaičius, po vieną kiekvienam sąrašo elementui. SUMPRODUCT susumuoja sumą ir šis skaičius atimamas iš visų ne tuščių langelių skaičiaus. Galutinis rezultatas yra langelių, kurių reikšmė „neįtraukti“ nesutampa.
Literal yra tipo logika
Šiame puslapyje pateikta formulė skaičiuojama su logika „lygu“. Jei jums reikia suskaičiuoti langelius, kuriuose nėra daug eilučių, kur yra reiškia, kad eilutė gali pasirodyti bet kurioje langelio vietoje, jums reikės sudėtingesnės formulės.
