I denne artikel lærer vi, hvordan du finder nøjagtige matches ved hjælp af funktionen SUMPRODUCT i Excel.
Scenarie:
Med enkle ord, mens vi arbejder med datatabeller, er vi nogle gange nødt til at slå værdier op med store og små bogstaver i Excel. Opslagsfunktioner som VLOOKUP eller MATCH -funktioner finder ikke det nøjagtige match, da disse ikke er store og små bogstaver. Antag at arbejde med data, hvor 2 navne er differentieret på grundlag af sagfølsomhed, dvs. Jerry & JERRY er to forskellige fornavne. Du kan udføre opgaver som operationer over flere områder ved hjælp af formlen forklaret herunder.
Hvordan løser man problemet?
For dette problem skal vi bruge SUMPRODUCT -funktionen & EXAKT -funktionen. Nu laver vi en formel ud af funktionen. Her får vi en tabel, og vi skal finde værdier, der svarer til det nøjagtige match i tabellen i Excel. SUMPRODUCT -funktionen returnerer SUMMEN for de tilsvarende TRUE -værdier (som 1) og ignorerer værdier, der svarer til FALSE -værdier (som 0) i det returnerede array
Generisk formel:
| = SUMPRODUCT ( - (EXAKT (opslag_værdi, opslag_array)), (return_array)) |
lookup_value: værdi til opslag.
lookup_array: opslag array for opslag værdi.
return_array: array, returneret værdi svarende til opslag array.
-: Negation (-) char ændrer værdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
Eksempel:
Alt dette kan være forvirrende at forstå. Så lad os teste denne formel ved at køre den på eksemplet nedenfor. Her har vi data med mængde og pris på produkter modtaget på datoerne. Hvis et produkt er købt to gange, har det 2 navne. Her skal vi finde antallet af varer til alle væsentlige varer. Så til det har vi tildelt 2 lister som den modtagne liste og essentials krævet i en kolonne til formlen. Nu vil vi bruge nedenstående formel til at få mængden af "mælken" fra tabellen.
Brug formlen:
| = SUMPRODUCT ( - (PRÆCIS (F5, B3: B11)), (C3: C11)) |
F6: slå værdi op i F6 -celle
B3: B11: Slå op array er elementer
C3: C11: return array er mængde
-: Negation (-) char ændrer værdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
Her er området givet som cellereference. Tryk på Enter for at få mængden.
Som du kan se, er 58 den mængde, der svarer til værdien "mælk" i B5 -cellen, ikke "mælken" i B9 -cellen. Du bliver nødt til at lede efter værdien "Mælk", hvis du har brug for mængden "54" i C9 -celle.
Du kan slå prisen op, der svarer til værdien "mælk", bare ved at bruge formlen vist nedenfor.
Brug formlen:
| = SUMPRODUCT ( - (PRÆCIS (F5, B3: B11)), (D3: D11)) |
F6: slå værdi op i F6 -celle
B3: B11: Slå op array er elementer
D3: D11: return array er Pris
Her har vi 58 er prisen svarende til værdien "mælk" i B5 -celle ikke "mælk" i B9 -celle. Du bliver nødt til at kigge efter værdien "Mælk", hvis du har brug for prisen "15,58" i D9 -celle.
Unikke værdier i opslagarray
På samme måde kan du finde de unikke værdier ved hjælp af formlen. Her er opslagsværdien "ÆG" brugt som eksempel.
På billedet vist ovenfor fik vi værdierne til at justere den samme formel. Men problemet opstår, hvis det har en unik værdi, og du ikke leder efter den korrekte opslagsværdi. Som her ved at bruge værdien "Brød" i formlen til at slå op i tabellen.
Formlen returnerer 0 som mængde og pris, men det betyder ikke, at mængden og prisen på brød er 0. Det er bare, at formlen returnerer 0 som værdi, når den værdi, du leder efter, ikke findes. Så brug kun denne formel til at slå det nøjagtige match op.
Du kan også udføre nøjagtige opslag ved hjælp af INDEX og MATCH -funktionen i Excel. Lær mere om, hvordan du foretager sagfølsom opslag ved hjælp af INDEX & MATCH -funktionen i Excel. Du kan også slå op efter de delvise kampe ved hjælp af jokertegnene i Excel.
Her er nogle observationsnoter vist nedenfor.
Bemærkninger:
- Formlen fungerer kun med kun at slå det nøjagtige match op.
- SUMPRODUCT-funktionen betragter ikke-numeriske værdier som 0s.
- SUMPRODUCT -funktionen betragter logisk værdi TRUE som 1 og Falsk som 0.
- Argumentmatrixen skal have samme størrelse, ellers returnerer funktionen en fejl.
- SUMPRODUCT -funktionen returnerer værdien svarende til de SANDE værdier i det returnerede array efter at have taget individuelle produkter i det tilsvarende array.
- Operatører kan lide lig med ( = ), mindre end lig med ( <= ), bedre end ( > ) eller ikke er lig med () kan udføres inden for en anvendt formel, kun med tal.
Håber, at denne artikel om, hvordan man leder nøjagtig match ved hjælp af SUMPRODUCT -funktion i Excel, er forklarende. Find flere artikler om tælleformler her. Hvis du kunne lide vores blogs, kan du dele den med dine fristarts på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.
Brug INDEX og MATCH til at slå op på værdi : INDEX-MATCH-formlen bruges til at søge dynamisk og præcist en værdi i en given tabel. Dette er et alternativ til VLOOKUP -funktionen, og det overvinder manglerne i VLOOKUP -funktionen.
Sum ved OFFSET -grupper i rækker og kolonner : OFFSET -funktionen kan bruges til at summere gruppe af celler dynamisk. Disse grupper kan være overalt i arket.
Sådan hentes hver niende værdi i et område i Excel: Ved hjælp af OFFSET -funktionen i Excel kan vi hente værdier fra skiftende rækker eller kolonner. Denne formel tager hjælp af ROW -funktionen til at skifte mellem rækker og COLUMN -funktion til at skifte i kolonner.
Sådan bruges OFFSET -funktionen i Excel : OFFSET -funktionen er en kraftfuld Excel -funktion, der undervurderes af mange brugere. Men eksperter kender kraften i OFFSET -funktionen, og hvordan kan vi bruge denne funktion til at udføre nogle magiske opgaver i Excel -formlen. Her er det grundlæggende i OFFSET -funktionen.
Sådan bruges jokertegn i excel : Tæl celler, der matcher sætninger ved hjælp af jokertegnene i excel
Populære artikler
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark i Excel.
Sådan bruges funktionen COUNTIF i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier i Excel. COUNTIF -funktionen er vigtig for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier med specifikke betingelser.