I denne artikkelen lærer vi å finne tellingen med flere kriterier med ISNA -logikk i Excel.
Scenario:
I enkle ord, mens du jobber med lange umannert data. Vi må trekke ut tellingen som har flere kriterier uten logikk eller si verdier for å ekskludere ved hjelp av formelen i Excel.
Hvordan løse problemet?
For denne artikkelen trenger vi å bruke MATCH -funksjonen og SUMPRODUCT -funksjonen. Nå skal vi lage en formel av den nevnte funksjonen. Her vil vi gi en liste over blandede verdier. Vi må finne antall verdier som tilfredsstiller betingelser og bruke ISNA -funksjon for å ekskludere verdier.
Bruk formelen:
| = SUMPRODUCT ((område = kriterier) * (ISNA (MATCH (rng, cri_rng, 0))) |
område: første område som samsvarer med første kriterier
kriterier: kriterier for å matche med første område
rng: andre område der kriterier skal ekskluderes
cri_rng: verdier som skal ekskluderes fra andre område
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi en tabell, og vi må telle verdiene som samsvarer med visse ikke -logiske forhold. Forholdene er som følger.
- Må være en kvinnelig ansatt, slik at kjønnstilpasningen samsvarer med F.
- Må ikke inkludere ansatte fra gruppe A og B.
Nå vil vi bruke formelen nedenfor for å få tellingen av verdier i tabellen som tilfredsstiller de nevnte kriteriene.
Formel:
| = SUMPRODUCT ((Kjønn = H5) * (ISNA (MATCH (Grupper, I5: I6, 0))) |
Forklaring:
- MATCH (Groups, I5: I6,0) matcher alle gruppeverdiene med de gitte kriteriene og returnerer en rekke tall og #NA -verdier.
- MATCH -funksjonen returnerer indeks for samsvarende verdier og #NA! Feil for verdiene som ikke samsvarer.
- ISNA returnerer SANN verdi for det tilsvarende matchede #NA! Verdier og FALSK for de returnerte indeksverdiene.
- Kjønn = H5, dette returnerer alle de matchede verdiene som SANN og uten samsvar som FALSK.
- SUMPRODUCT -funksjonen returnerer antallet matchede TRUE -verdier som tilsvarer begge matrisene.
Her er matrisen til funksjonen gitt som navngitte områder som nevnt i de gulfargede cellene.
Navngitte områder brukt i formelen:
Kjønn (E4: E17)
ID (D4: D17)
Grupper (F4: F17)
Verdi for enkelt argument gitt som cellereferanse. Trykk Enter for å få resultatet.
Som du kan se i øyeblikksbildet ovenfor, blir tallet 3. Det er 3 ansatte som er kvinner og ikke tilhører hverken gruppe A eller gruppe B.
Nå kan vi se etter resultatet ved å filtrere innholdet, se gifen nedenfor. Bare filtrer tabellmatrisen og tell antall matchede verdier, eller bruk ovennevnte formel for å få tellingen.
Her er alle observasjonsnotatene om bruk av formelen.
Merknader:
- Formelen fungerer bare med tall og tekstverdier begge.
- Matrisen som er kjønn og grupper må ha samme lengde, ellers returnerer SUMPRODUCT -funksjonen feil.
- Operatører liker lik ( = ), mindre enn lik ( <= ), større enn ( > ) eller ikke er lik () kan utføres innenfor funksjon som bare brukes med tall.
Håper denne artikkelen om hvordan du finner tellingen med flere kriterier med ISNA -logikk i Excel er forklarende. Finn flere artikler om COUNTIF -funksjoner her. Hvis du likte bloggene våre, del den med fristarts på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post
Finn den siste dataraden med tall i Excel : I en rekke tekstverdier finner du den siste med data i excel.
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
TELLER med Dynamic Criteria Range : Tell celler som er avstarter på andre celleverdier i Excel.
TELLER To kriterier samsvarer : Tell celler som matcher to forskjellige kriterier på listen i excel.
TELLER MED ELLER For flere kriterier : Tell celler som har flere kriterier som samsvarer med OR -funksjonen.
COUNTIFS -funksjonen i Excel : Tell celler avhengig av andre celleverdier.
Hvordan bruke Countif i VBA i Microsoft Excel : Tell celler ved hjelp av Visual Basic for Applications -kode.
Hvordan bruke jokertegn i excel : Tell celler som matcher setninger ved hjelp av jokertegnene i excel
Populære artikler
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.