Üldine valem
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Kokkuvõte
Lahtrite loendamiseks, mis pole võrdsed ühegi paljude asjadega, võite kasutada valemit, mis põhineb funktsioonidel MATCH, ISNA ja SUMPRODUCT. Näidatud näites on lahtris F5 valem:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
kus "andmed" on nimega vahemik B5: B16 ja "välista" on nimega vahemik D5: D7.
Selgitus
Esiteks väike kontekst. Tavaliselt, kui teil on ainult paar asja, mida te ei soovi lugeda, võite kasutada COUNTIFS-i järgmiselt:
=COUNTIFS(range,"apple",range,"orange")
Kuid see ei muutu eriti hästi, kui teil on paljude asjade loend, sest iga asja jaoks, mida te ei loe, peate lisama täiendava vahemiku / kriteeriumide paari. Oleks palju lihtsam koostada loetelu ja lisada kriteeriumide osana viide sellele loendile. Täpselt seda teeb selle lehe valem.
Põhimõtteliselt kasutab see valem funktsiooni MATCH rakkude leidmiseks, mis pole võrdsed tähisega "a", "b" või "c" selle avaldisega:
MATCH(data,exclude,0)
Pange tähele, et otsingu väärtus ja otsingu massiiv on tavapärasest konfiguratsioonist "vastupidised" - pakume kõik nimevahemikus "data" olevad väärtused otsingu väärtustena ja esitame kõik väärtused, mida me soovime nimega vahemikus "välistada" välja jätta. Kuna anname MATCH-ile rohkem kui ühe otsingu väärtuse, saame sellise massiivi korral mitu tulemust:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Põhimõtteliselt annab MATCH meile väärtuste sobitamise numbritena ja tagastab kõigi muude väärtuste puhul # N / A.
# N / A tulemused on need, mis meid huvitavad, kuna need tähistavad väärtusi, mis pole võrdsed tähtedega "a", "b" või "c". Vastavalt sellele kasutame ISNA-d, et sundida neid väärtusi TÕELISEKS ja numbreid VÄÄRTama:
ISNA(MATCH(data,exclude,0)
Siis kasutame topeltnegatiivi, et sundida TRUE väärtusele 1 ja FALSE väärtusele null. Saadud massiiv SUMPRODUCT sees näeb välja selline:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Ainult ühe massiivi töötlemiseks summeerib ja tagastab SUMPRODUCT lõpptulemuse 4.
Märkus. SUMPRODUCTi kasutamine SUM asemel välistab vajaduse kasutada juhtklahvi + shift + enter.
Krahv miinus matš
Teine võimalus mitmete asjadega võrdsete lahtrite lugemiseks on kõigi väärtuste loendamine ja vastete lahutamine. Seda saate teha järgmise valemiga:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Siin tagastab COUNTA kõigi tühjade lahtrite arvu. Funktsioon COUNTIF, kui nimega vahemik on "välista", tagastab kolm loendit, ühe loendi iga üksuse jaoks. SUMPRODUCT summeerib summa ja see arv lahutatakse kõigi mittetühjade lahtrite arvust. Lõpptulemus on lahtrite arv, mis ei võrdu väärtuses jaotises "välista".
Literal sisaldab tüübiloogikat
Sellel lehel olev valem loetakse loogikaga "võrdub". Kui peate loendama lahtrid, mis ei sisalda paljusid stringi, sisaldab see tähendab, et string võib lahtris ilmuda ükskõik kus, vajate keerukamat valemit.
