I denne artikkelen lærer vi hvordan du finner eksakte treff ved hjelp av SUMPRODUCT -funksjonen i Excel.
Scenario:
Med enkle ord, mens vi jobber med datatabeller, må vi noen ganger slå opp verdier med store bokstaver i Excel. Oppslagsfunksjoner som VLOOKUP eller MATCH -funksjoner finner ikke den eksakte matchen, siden dette ikke er store og små bokstaver. Anta å jobbe med data der to navn er differensiert på grunnlag av saksfølsomhet, dvs. Jerry & JERRY er to forskjellige fornavn. Du kan utføre oppgaver som operasjoner over flere områder ved å bruke formelen forklart nedenfor.
Hvordan løse problemet?
For dette problemet må vi bruke SUMPRODUCT -funksjonen og EXACT -funksjonen. Nå skal vi lage en formel av funksjonen. Her får vi en tabell, og vi må finne verdier som tilsvarer den eksakte matchen i tabellen i Excel. SUMPRODUCT -funksjonen returnerer SUMMEN for tilsvarende TRUE -verdier (som 1) og ignorerer verdier som tilsvarer FALSE -verdier (som 0) i det returnerte arrayet
Generisk formel:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: verdi til oppslag.
lookup_array: oppslagsmatrise for oppslagsverdi.
return_array: array, returnert verdi som tilsvarer oppslag array.
-: Negasjon (-) char endrer verdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor. Her har vi data om mengde og pris på produkter mottatt på datoene. Hvis et produkt er kjøpt to ganger, har det 2 navn. Her må vi finne antall varer for alle viktige ting. Så for det har vi tilordnet 2 lister som mottatt liste og viktige ting som kreves i en kolonne for formelen. Nå vil vi bruke formelen nedenfor for å få Mengden av "melken" fra tabellen.
Bruk formelen:
| = SUMPRODUCT ( - (EXAKT (F5, B3: B11)), (C3: C11)) |
F6: slå opp verdien i F6 -cellen
B3: B11: slå opp matrise er elementer
C3: C11: returmatrisen er Antall
-: Negasjon (-) char endrer verdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
Her er området gitt som cellereferanse. Trykk Enter for å få Antall.
Som du kan se, er 58 mengden som tilsvarer verdien "melk" i B5 -celle, ikke "melk" i B9 -celle. Du må se etter verdien "melk" hvis du trenger mengden "54" i C9 -celle.
Du kan slå opp prisen som tilsvarer verdien "melk" bare ved å bruke formelen vist nedenfor.
Bruk formelen:
| = SUMPRODUCT ( - (EXAKT (F5, B3: B11)), (D3: D11)) |
F6: slå opp verdien i F6 -cellen
B3: B11: slå opp matrise er elementer
D3: D11: returmatrisen er Pris
Her har vi 58 er prisen som tilsvarer verdien "melk" i B5 -celle, ikke "melk" i B9 -celle. Du må lete etter verdien "melk" hvis du trenger prisen "15,58" i D9 -celle.
Unike verdier i oppslagsserien
På samme måte kan du finne de unike verdiene ved å bruke formelen. Her er oppslagsverdien "EGG" brukt som eksempel.
På bildet vist ovenfor fikk vi verdiene til å justere den samme formelen. Men problemet oppstår hvis det har en unik verdi, og du ikke ser opp den riktige oppslagsverdien. Som her ved å bruke verdien "Brød" i formelen for å slå opp i tabellen.
Formelen returnerer 0 som mengde og pris, men dette betyr ikke at mengden og prisen på brød er 0. Det er bare at formelen returnerer 0 som verdi når verdien du leter etter ikke blir funnet. Så bruk denne formelen bare for å slå opp den eksakte matchen.
Du kan også utføre nøyaktige oppslag ved å bruke INDEX og MATCH -funksjonen i Excel. Lær mer om hvordan du gjør saksfølsom oppslag ved hjelp av INDEX & MATCH -funksjonen i Excel. Du kan også slå opp for de delvise treffene ved hjelp av jokertegnene i Excel.
Her er noen observasjonsnotater vist nedenfor.
Merknader:
- Formelen fungerer bare med bare for å slå opp den eksakte matchen.
- SUMPRODUCT-funksjonen anser ikke-numeriske verdier som 0s.
- SUMPRODUCT -funksjonen anser logisk verdi TRUE som 1 og Falsk som 0.
- Argumentmatrisen må ha samme størrelse, ellers returnerer funksjonen en feil.
- SUMPRODUCT -funksjonen returnerer verdien som tilsvarer de SANNE verdiene i den returnerte matrisen etter å ha tatt individuelle produkter i den tilsvarende matrisen.
- Operatører liker lik ( = ), mindre enn lik ( <= ), større enn ( > ) eller ikke er lik () kan utføres innenfor en anvendt formel, bare med tall.
Håper denne artikkelen om hvordan du søker nøyaktig samsvar med SUMPRODUCT -funksjonen i Excel er forklarende. Finn flere artikler om telleformler 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
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
Bruk INDEX og MATCH til å slå opp verdi : INDEX-MATCH-formelen brukes til å slå dynamisk og presist opp en verdi i en gitt tabell. Dette er et alternativ til VLOOKUP -funksjonen, og den overvinner manglene i VLOOKUP -funksjonen.
Sum ved OFFSET -grupper i rader og kolonner : OFFSET -funksjonen kan brukes til å summere gruppe celler dynamisk. Disse gruppene kan være hvor som helst i arket.
Hvordan hente hver niende verdi i et område i Excel: Ved å bruke OFFSET -funksjonen i Excel kan vi hente verdier fra alternerende rader eller kolonner. Denne formelen tar hjelp av ROW -funksjonen til å veksle mellom rader og COLUMN -funksjonen til å veksle i kolonner.
Hvordan bruke OFFSET -funksjonen i Excel : OFFSET -funksjonen er en kraftig Excel -funksjon som er undervurdert av mange brukere. Men eksperter kjenner kraften til OFFSET -funksjonen og hvordan kan vi bruke denne funksjonen til å utføre noen magiske oppgaver i Excel -formelen. Her er det grunnleggende om OFFSET -funksjonen.
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 til excel som brukes til å slå opp verdi fra forskjellige områder og ark i Excel.
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 i Excel. 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 med spesifikke forhold.