I den här artikeln lär vi oss hur man letar upp exakta matchningar med funktionen SUMPRODUCT i Excel.
Scenario:
I enkla ord, medan vi arbetar med datatabeller, ibland måste vi leta upp värden med skiftlägeskänsliga bokstäver i Excel. Sökfunktioner som VLOOKUP eller MATCH -funktioner hittar inte exakt matchning eftersom dessa inte är skiftlägeskänsliga funktioner. Antag att arbeta med data där två namn är differentierade på grund av skiftlägeskänslighet, dvs Jerry & JERRY är två olika förnamn. Du kan utföra uppgifter som operationer över flera intervall med hjälp av formeln som förklaras nedan.
Hur löser man problemet?
För detta problem kommer vi att behöva använda SUMPRODUCT -funktionen & EXAKT -funktionen. Nu ska vi göra en formel av funktionen. Här får vi en tabell och vi måste hitta värden som motsvarar den exakta matchningen i tabellen i Excel. SUMPRODUCT -funktionen returnerar SUMMAN för motsvarande TRUE -värden (som 1) och ignorerar värden som motsvarar FALSE -värden (som 0) i den returnerade arrayen
Generisk formel:
| = SUMPRODUCT ( - (EXAKT (lookup_value, lookup_array)), (return_array)) |
lookup_value: value to lookup.
lookup_array: uppslagsmatris för uppslagsvärde.
return_array: array, returnerat värde som motsvarar lookup array.
-: Negation (-) char ändrar värden, TRUE eller 1s till FALSE eller 0s och FALSE eller 0s till TRUE eller 1s.
Exempel:
Allt detta kan vara förvirrande att förstå. Så, låt oss testa denna formel genom att köra den på exemplet som visas nedan. Här har vi data med kvantitet och pris på produkter som tas emot på datumen. Om någon produkt köps två gånger har den 2 namn. Här måste vi hitta antalet artiklar för alla väsentliga artiklar. Så för det har vi tilldelat 2 listor som den mottagna listan och nödvändiga saker i en kolumn för formeln. Nu kommer vi att använda nedanstående formel för att få mängden "mjölk" från tabellen.
Använd formeln:
| = SUMPRODUKT ( - (EXAKT (F5, B3: B11)), (C3: C11)) |
F6: slå upp värde i F6 -cell
B3: B11: Slå upp matris är Artiklar
C3: C11: returmatris är kvantitet
-: Negation (-) char ändrar värden, TRUE eller 1s till FALSE eller 0s och FALSE eller 0s till TRUE eller 1s.
Här ges intervallet som cellreferens. Tryck på Enter för att få mängden.
Som du kan se är 58 den kvantitet som motsvarar värdet "mjölk" i B5 -cellen, inte "mjölken" i B9 -cellen. Du måste leta efter värdet "Mjölk" om du behöver kvantiteten "54" i C9 -cell.
Du kan leta upp pris som motsvarar värdet "mjölk" bara genom att använda formeln nedan.
Använd formeln:
| = SUMPRODUKT ( - (EXAKT (F5, B3: B11)), (D3: D11)) |
F6: slå upp värde i F6 -cell
B3: B11: Slå upp matris är Artiklar
D3: D11: returmatris är Pris
Här har vi 58 är priset som motsvarar värdet "mjölk" i B5 -cellen inte "mjölken" i B9 -cellen. Du måste leta efter värdet "Mjölk" om du behöver priset "15,58" i D9 -cell.
Unika värden i uppslagningsarray
På samma sätt kan du hitta de unika värdena med hjälp av formeln. Här används uppslagsvärdet "ÄGG" som exempel.
I bilden som visas ovan fick vi värdena som justerade samma formel. Men problemet uppstår om det har ett unikt värde och du inte letar upp rätt uppslagsvärde. Som här med värdet "Bröd" i formeln för att leta upp i tabellen.
Formeln returnerar 0 som kvantitet och pris men det betyder inte att brödets kvantitet och pris är 0. Det är bara att formeln returnerar 0 som värde när värde du letar efter inte hittas. Så använd den här formeln bara för att leta upp den exakta matchningen.
Du kan också utföra exakta träffar med hjälp av INDEX och MATCH -funktionen i Excel. Läs mer om hur man gör skiftlägeskänslig sökning med hjälp av INDEX & MATCH -funktionen i Excel. Du kan också leta efter delmatchningarna med jokertecken i Excel.
Här är några observationsanteckningar som visas nedan.
Anmärkningar:
- Formeln fungerar bara för att leta upp den exakta matchningen.
- SUMPRODUCT-funktionen betraktar icke-numeriska värden som 0s.
- SUMPRODUCT -funktionen anser logiskt värde TRUE som 1 och Falskt som 0.
- Argumentmatrisen måste ha samma storlek, annars ger funktionen ett fel.
- SUMPRODUCT -funktionen returnerar värdet som motsvarar de TRUE -värdena i den returnerade arrayen efter att ha tagit enskilda produkter i motsvarande array.
- Operatörer gillar lika med ( = ), mindre än lika med ( <= ), större än ( > ) eller inte är lika med () kan utföras inom en tillämpad formel, endast med siffror.
Hoppas att den här artikeln om hur man letar upp exakt matchning med SUMPRODUCT -funktionen i Excel är förklarande. Hitta fler artiklar om räknar formler här. Om du gillade våra bloggar, dela den med dina fristarts på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.
Använd INDEX och MATCH för att leta upp värde : INDEX-MATCH-formeln används för att leta dynamiskt och exakt ett värde i en given tabell. Detta är ett alternativ till VLOOKUP -funktionen och det övervinner bristerna i VLOOKUP -funktionen.
Summa med OFFSET -grupper i rader och kolumner : OFFSET -funktionen kan användas för att summera grupp av celler dynamiskt. Dessa grupper kan vara var som helst i arket.
Så här hämtar du var tionde värde i ett intervall i Excel: Med hjälp av OFFSET -funktionen i Excel kan vi hämta värden från alternerande rader eller kolumner. Denna formel tar hjälp av ROW -funktionen för att alternera genom rader och COLUMN -funktionen för att alternera i kolumner.
Hur man använder OFFSET -funktionen i Excel : OFFSET -funktionen är en kraftfull Excel -funktion som underskattas av många användare. Men experter vet kraften i OFFSET -funktionen och hur kan vi använda den här funktionen för att utföra några magiska uppgifter i Excel -formeln. Här är grunderna för OFFSET -funktionen.
Hur man använder jokertecken i excel : Räkna celler som matchar fraser med jokertecken i excel
Populära artiklar
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark i Excel.
Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden i Excel. COUNTIF -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden med specifika villkor.