Hoe gebruik je VERT.ZOEKEN in Excel-tabellen en vermenigvuldig je het resultaat daarna?
In dagelijkse zakelijke of Data-analyse scenario’s komt het vaak voor dat u informatie moet ophalen uit gestructureerde tabellen en vervolgens verdere berekeningen moet uitvoeren op basis van zoekresultaten. U kunt bijvoorbeeld een regeltabel hebben waarin verschillende winstmarges worden gedefinieerd afhankelijk van het verkoopvolume van producten, en een andere tabel waarin de werkelijke verkoopvolumes per product zijn vastgelegd. De uitdaging bestaat erin om efficiënt de toepasselijke winstmarge te bepalen voor elk product op basis van het werkelijke verkoopvolume, en vervolgens de winst te berekenen door de werkelijke verkoopwaarde te vermenigvuldigen met de juiste winstmarge. Deze aanpak is vooral nuttig wanneer prijsstrategieën of bonussen afhangen van variabele drempels of regels, wat essentieel is voor taken op het gebied van verkoop, financiën of voorraadbeheer.
Opzoeken en daarna vermenigvuldigen op basis van criteria
Opzoeken en daarna vermenigvuldigen op basis van criteria
Voor deze taak kunt u de functie VERT.ZOEKEN in Excel combineren met VERGELIJKEN, of kiezen voor de INDEX-functie om vergelijkbare resultaten te behalen. Beide oplossingen hebben hun eigen toepassingsgebied en subtiele verschillen, zodat u de methode kunt selecteren die het beste aansluit bij uw gegevensstructuur en voorkeuren. Ze helpen u allebei om efficiënt de relevante winstmarge op te halen op basis van de combinatie van product en verkoopvolume, en vervolgens het werkelijke winstbedrag te berekenen.
Selecteer de cel direct rechts van het werkelijke verkoopbedrag van uw product en voer vervolgens de volgende formule in om de bijbehorende winstmarge op te halen:
=VLOOKUP(B3,$A$14:$E$16,MATCH(C3,$A$13:$E$13),0)Nadat u de formule hebt ingevoerd, drukt u op Enter om de winstmarge weer te geven die zowel bij het product als bij het verkoopvolume past. Gebruik de vulgreep (het kleine vierkantje rechtsonder in de cel) om de formule naar beneden te slepen naar alle relevante rijen in uw lijst, zodat automatisch de winstmarges voor alle producten worden berekend.
In deze formule:
- B3: Productnaam of -identificatie om op te zoeken (criterium 1)
- C3: Werkelijke verkoopwaarde of -categorie (criterium 2)
- $A$14:$E$16: Bereik met de regeltabel (producten en bijbehorende winstmarges)
- $A$13:$E$13: Koptekstrij met verkoopbereiken of -categorieën
Zorg ervoor dat de opzoekbereiken ($A$14:$E$16 en $A$13:$E$13) als absolute verwijzingen zijn ingesteld door het gebruik van $. Dit voorkomt fouten wanneer u de formules naar andere rijen kopieert. Als uw kopteksten of productnamen extra spaties of inconsistente opmaak bevatten, standaardiseer uw gegevens dan eerst met de functie INPASSEN of OPSCHONEN voordat u de formules toepast. Let goed op de gegevenstypen: onovereenkomsten tussen categorische en numerieke waarden in uw criteriumcellen (B3, C3) en de koptekstrij of -kolommen kunnen leiden tot opzoekfouten of onbedoelde resultaten.
Zodra de winstmarges zijn ontvangen, berekent u de winst per product door de betreffende werkelijke verkoop te vermenigvuldigen met de verkregen winstmarge. Voer in de volgende aangrenzende kolom deze formule in:
=D3*C3Hier bevat D3 de hierboven berekende winstmarge en C3 het werkelijke verkoopbedrag. Druk op Enter en gebruik de vulgreep om de formule naar beneden te slepen voor alle andere producten, zodat eenvoudig de winst van elk product wordt berekend.
Tip: Als u werkt met een grote dataset of meer flexibiliteit nodig heeft, kunt u ook de combinatie INDEX en VERGELIJKEN gebruiken voor uw opzoeking. Dit is vooral handig wanneer uw opzoektabel niet is gestructureerd met de opzoekkolom links — zoals VERT.ZOEKEN vereist — of wanneer u kolommen en rijen liever afzonderlijk wilt refereren. Voer bijvoorbeeld de volgende formule in om de winstmarge te bepalen op basis van zowel product als verkoop:
=INDEX($B$14:$E$16,MATCH(B3,$A$14:$A$16,0),MATCH(C3,$B$13:$E$13))In deze formule:
- $B$14:$E$16: Het bereik dat uitsluitend de winstmarges bevat.
- MATCH(B3;$A$14:$A$16,0): Zoekt het rijnummer dat overeenkomt met het doelproduct.
- MATCH(C3;$B$13:$E$13): Zoekt het kolomnummer dat overeenkomt met het verkoopbedrag of de verkoopcategorie.
Nadat u de formule hebt ingevoerd, drukt u op Enter en gebruikt u de vulgreep om deze indien nodig toe te passen op de rest van uw tabel. INDEX en VERGELIJKEN zijn over het algemeen minder gevoelig voor fouten wanneer de structuur van uw opzoektabel complexer is of in de toekomst verandert, waardoor ze meer veelzijdigheid bieden.
Als u #N/B- of #REF!-fouten tegenkomt, controleer dan of uw opzoekargumenten correct zijn gespeld, of de bereiken overeenkomen met de werkelijke lay-out van uw gegevens en of de waarden van de verkoopcategorieën consistent zijn tussen de tabellen. Houd uw criteriumwaarden het beste gestandaardiseerd en vermijd verborgen spaties of onverwachte opmaak in uw datasets.
U kunt ook alternatieve formules overwegen, zoals X.ZOEKEN of SOMPRODUCT, vooral als u nieuwere versies van Excel gebruikt of geavanceerdere matrix- of multicriteria-opzoeking nodig heeft. X.ZOEKEN biedt bijvoorbeeld een eenvoudigere syntaxis en intuïtievere foutafhandeling dan VERT.ZOEKEN.
Nadat u uw winstberekeningen hebt afgerond, is het raadzaam om enkele resultaten handmatig te controleren, zodat u zeker weet dat de opzoekregels en berekeningen naar verwachting worden toegepast. Zo detecteert u snel eventuele inconsistenties veroorzaakt door de tabelstructuur of invoerfouten. Bovendien draagt het up-to-date houden van de bereiken in uw opzoektabel bij gegevenswijzigingen bij aan langdurige nauwkeurigheid.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins