In dit artikel leren we hoe u exacte overeenkomsten kunt opzoeken met behulp van de SOMPRODUCT-functie in Excel.
Scenario:
In eenvoudige bewoordingen, terwijl we met gegevenstabellen werken, moeten we soms waarden opzoeken met hoofdlettergevoelige letters in Excel. Opzoekfuncties zoals VERT.ZOEKEN of VERGELIJKEN-functies vinden de exacte overeenkomst niet omdat dit geen hoofdlettergevoelige functies zijn. Stel dat u werkt aan gegevens waarbij 2 namen worden onderscheiden op basis van hoofdlettergevoeligheid, d.w.z. Jerry & JERRY zijn twee verschillende voornamen. U kunt taken zoals bewerkingen over meerdere bereiken uitvoeren met behulp van de onderstaande formule.
Hoe het probleem op te lossen?
Voor dit probleem moeten we de SOMPRODUCT-functie & EXACT-functie gebruiken. Nu gaan we een formule maken van de functie. Hier krijgen we een tabel en moeten we waarden vinden die overeenkomen met de exacte overeenkomst in de tabel in Excel. De functie SOMPRODUCT retourneert de SOM van de bijbehorende TRUE-waarden (als 1) en negeert waarden die overeenkomen met FALSE-waarden (als 0) in de geretourneerde array
Generieke formule:
| = SOMPRODUCT ( -- ( EXACT ( lookup_value , lookup_array ) ) , (return_array ) ) |
lookup_value : waarde om op te zoeken.
lookup_array : opzoekarray voor opzoekwaarde.
return_array : array, geretourneerde waarde die overeenkomt met opzoekarray.
-- : Negatie (--) char verandert waarden, TRUEs of 1s naar FALSEs of 0s en FALSEs of 0s naar TRUEs of 1s.
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren. Hier hebben we gegevens over de hoeveelheid en prijs van producten die op de datums zijn ontvangen. Als een product twee keer wordt gekocht, heeft het 2 namen. Hier moeten we het aantal items vinden voor alle essentiële items. Dus daarvoor hebben we 2 lijsten toegewezen als de ontvangen lijst en essentiële benodigdheden in een kolom voor de formule. Nu zullen we de onderstaande formule gebruiken om de hoeveelheid "melk" uit de tabel te halen.
Gebruik de formule:
| = SOMPRODUCT ( -- ( EXACT ( F5 , B3:B11 ) ) , ( C3:C11 ) ) |
F6: waarde opzoeken in F6-cel
B3:B11: array opzoeken is Items
C3:C11: retourarray is Hoeveelheid
-- : Negatie (--) char verandert waarden, TRUEs of 1s naar FALSEs of 0s en FALSEs of 0s naar TRUEs of 1s.
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de hoeveelheid te krijgen.
Zoals u kunt zien, is 58 de hoeveelheid die overeenkomt met de waarde "melk" in B5-cel, niet de "Melk" in B9-cel. U zult moeten zoeken naar de waarde "Melk" als u de hoeveelheid "54" in cel C9 nodig heeft.
U kunt de prijs die overeenkomt met de waarde "melk" opzoeken door de onderstaande formule te gebruiken.
Gebruik de formule:
| = SOMPRODUCT ( -- ( EXACT ( F5 , B3:B11 ) ) , ( D3:D11 ) ) |
F6: zoek waarde op in F6-cel
B3:B11: array opzoeken is Items
D3:D11: retourarray is Prijs
Hier hebben we de 58 is de prijs die overeenkomt met de waarde "melk" in B5-cel, niet de "Melk" in B9-cel. U moet op zoek naar waarde "Melk" als u de prijs "15,58" in cel D9 nodig heeft.
Unieke waarden in opzoekarray
Op dezelfde manier kunt u de unieke waarden vinden met behulp van de formule. Hier wordt de opzoekwaarde "EIEREN" als voorbeeld gebruikt.
In de bovenstaande afbeelding hebben we de waarden die dezelfde formule aanpassen. Maar het probleem doet zich voor als het een unieke waarde heeft en u niet de juiste opzoekwaarde opzoekt. Zoals hier met de waarde "Brood" in formule om op te zoeken in tabel.
De formule retourneert 0 als hoeveelheid en Prijs, maar dit betekent niet dat de hoeveelheid en Prijs van Brood 0 is. De formule retourneert alleen 0 als waarde wanneer de waarde die u zoekt niet wordt gevonden. Gebruik deze formule dus alleen om de exacte overeenkomst op te zoeken.
U kunt ook exacte overeenkomsten opzoeken met behulp van de INDEX- en MATCH-functie in Excel. Meer informatie over hoe u hoofdlettergevoelige opzoekingen uitvoert met de functie INDEX & VERGELIJKEN in Excel. U kunt de gedeeltelijke overeenkomsten ook opzoeken met behulp van de jokertekens in Excel.
Hier zijn enkele observatienotities die hieronder worden weergegeven.
Opmerkingen:
- De formule werkt alleen met alleen om de exacte overeenkomst op te zoeken.
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde grootte hebben, anders retourneert de functie een fout.
- De functie SOMPRODUCT retourneert de waarde die overeenkomt met de TRUE-waarden in de geretourneerde array na het nemen van individuele producten in de bijbehorende array.
- Operators zoals is gelijk aan ( = ), kleiner dan gelijk aan ( <= ), groter dan ( > ) of niet gelijk aan ( ) kan worden uitgevoerd binnen een toegepaste formule, alleen met getallen.
Ik hoop dat dit artikel over het opzoeken van exacte overeenkomsten met de functie SOMPRODUCT in Excel verhelderend is. Vind hier meer artikelen over telformules. Als je onze blogs leuk vond, deel het dan met je fristarts op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
Gebruik INDEX en MATCH om waarde op te zoeken : De formule INDEX-MATCH wordt gebruikt om dynamisch en nauwkeurig een waarde in een bepaalde tabel op te zoeken. Dit is een alternatief voor de functie VERT.ZOEKEN en lost de tekortkomingen van de functie VERT.ZOEKEN op.
Som op OFFSET-groepen in rijen en kolommen : De OFFSET-functie kan worden gebruikt om groepen cellen dynamisch op te tellen. Deze groepen kunnen overal in het blad staan.
Hoe elke N-waarde in een bereik in Excel op te halen: Met behulp van de OFFSET-functie van Excel kunnen we waarden ophalen uit afwisselende rijen of kolommen. Deze formule maakt gebruik van de ROW-functie om door rijen te wisselen en de COLUMN-functie om in kolommen te wisselen.
Hoe de OFFSET-functie in Excel te gebruiken? : De OFFSET-functie is een krachtige Excel-functie die door veel gebruikers wordt onderschat. Maar experts kennen de kracht van de OFFSET-functie en hoe kunnen we deze functie gebruiken om enkele magische taken in de Excel-formule uit te voeren. Hier zijn de basisprincipes van de OFFSET-functie.
Jokertekens gebruiken in Excel : Tel cellen die overeenkomen met zinnen met behulp van de jokertekens in Excel
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarden op te zoeken uit verschillende bereiken en bladen in Excel.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden in Excel te tellen. AANTAL.ALS-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u om waarden samen te vatten met specifieke voorwaarden.