Door een matrixformule in Excel te gebruiken, kunnen we een opzoekformule maken die meerdere criteria gebruikt om informatie te vinden in een database of een gegevenstabel. De matrixformule omvat het nesten van de MATCH-functie binnen de INDEX-functie.
Deze zelfstudie bevat een stapsgewijs voorbeeld van het maken van een opzoekformule die meerdere criteria gebruikt om een leverancier van te vindentitanium Widgets in een voorbeelddatabase. Volg mee om te leren hoe eenvoudig het is om uw eigen aangepaste opzoekformules te maken.
Bereid uw Excel-werkboek voor met gegevens
Als u de stappen in deze zelfstudie wilt volgen, moet u beginnen met het invoeren van de voorbeeldgegevens in de volgende cellen, zoals weergegeven in de bovenstaande afbeelding. Rijen 3 en 4 worden leeg gelaten om rekening te houden met de matrixformule die tijdens deze zelfstudie is gemaakt.
- Voer het bovenste bereik van gegevens in cellen in D1 naar F2
- Voer het tweede bereik in cellen in D5 naar F11
De zelfstudie bevat niet de opmaak die te zien is in de afbeelding, maar dit heeft geen invloed op de werking van de opzoekformule. Informatie over opmaakopties vergelijkbaar met die hierboven zijn beschikbaar in deze eenvoudige Excel-opmaak zelfstudie.
02 van 08Maak een INDEX-functie in Excel
De INHOUDSOPGAVE functie is een van de weinige in Excel met meerdere formulieren. De functie heeft een Array-formulier en een Referentieformulier. Het matrixformulier retourneert de werkelijke gegevens uit een database of gegevenstabel, terwijl het referentieformulier u de celverwijzing of locatie van de gegevens in de tabel geeft.
In deze zelfstudie gebruiken we het matrixformulier omdat we de naam van de leverancier voor titanium-widgets willen weten in plaats van de celverwijzing naar deze leverancier in onze database. Volg deze stappen om uw INDEX-functie te maken:
- Klik op cel F3 om het de actieve cel te maken - dit is waar we de geneste functie zullen invoeren.
- Klik op de formules tab van het lintmenu.
- Kiezen Lookup en referentie uit de lintbalk om de functie te openen.
- Klik op INHOUDSOPGAVE in de lijst om het Selecteer Argumenten dialoog venster.
- Kies de array, row_num, col_num optie in het dialoogvenster.
- Klik OK om het dialoogvenster INDEX-functie te openen; hierdoor wordt de Formula Builder in Excel geopend.
- In de Formula Builder, Klik op de reeks lijn.
- Markeer cellen D6 naar F11, door te klikken en te slepen, in het werkblad om het bereik in de builder in te voeren.
In oudere versies van Excel de Formula Builder zal worden vervangen door de Functieargumenten venster. Voer dezelfde stappen uit als in deze zelfstudie in het venster.
03 van 08Start de geneste MATCH-functie
Bij het nesten van een functie in een andere is het niet mogelijk om de formulebouwer van de tweede of geneste functie te openen of de nodige argumenten in te voeren. De geneste functie moet worden ingetypt als een van de gesorteerde in van de eerste functie.
Bij het handmatig invoeren van functies worden de argumenten van de functie van elkaar gescheiden door een komma.
De eerste stap bij het invoeren van de geneste MATCH-functie is het invoeren van de Opzoekwaarde argument. De Lookup_value is de locatie of celverwijzing voor de zoekterm die we in de database willen matchen.
Normaal accepteert de Lookup_value slechts één zoekcriterium of term. Om naar meerdere criteria te zoeken, moeten we de Lookup_value verlengen; thij wordt gedaan door twee of meer celverwijzingen samen te voegen of samen te voegen met behulp van het ampersand-symbool - &.
- In de Formula Builder, Klik op de rij_getal lijn.
- Typ de functienaam WEDSTRIJD gevolgd door een open ronde beugel.
- Klik op cel D3 om die celverwijzing in te voeren in het dialoogvenster.
- Typ een ampersand na de celverwijzing D3 om een tweede celverwijzing toe te voegen.
- Klik op cel E3 om deze tweede celverwijzing in te voeren.
- Typ een komma achter de celverwijzing E3 om de invoer van de MATCH-functie's te voltooien Opzoekwaarde argument.
In de laatste stap van de zelfstudie, onze Lookup_values wordt ingevoerd in de cellen D3 en E3 van het werkblad.
04 van 08Voltooi de Geneste MATCH-functie
Deze stap omvat het toevoegen van de opzoekmatrix argument voor de geneste WEDSTRIJD functie. De Lookup_array is het celbereik dat door de MATCH-functie wordt gezocht om het argument Lookup_value te vinden dat is toegevoegd in de vorige stap van de zelfstudie.
Omdat we twee zoekvelden hebben geïdentificeerd in het Lookup_array-argument, moeten we hetzelfde doen voor de Lookup_array. De MATCH-functie doorzoekt slechts één array voor elke opgegeven term, dus om meerdere arrays in te voeren, gebruiken we opnieuw het ampersand om de arrays samen aaneen te schakelen.
Deze stappen moeten worden ingevoerd na de komma die is ingevoerd in de vorige stap op de rij_getal regel in de INHOUDSOPGAVE functie.
- Klik op de rij_getal regel na de komma om het invoegpunt aan het einde van het huidige item te plaatsen.
- Markeer cellen D6 naar D11 in het werkblad om het bereik in te voeren - dit is de eerste array waarmee de functie zoekt.
- Typ een ampersand na de celverwijzingen D6: D11 omdat we willen dat de functie twee matrices doorzoekt.
- Markeer cellen E6 naar E11 in het werkblad om het bereik in te voeren - dit is de tweede array waarmee de functie zoekt.
- Typ een komma achter de celverwijzing E3 om de invoer van de te voltooien WEDSTRIJD de functie van opzoekmatrix argument.
- Verlaat de INHOUDSOPGAVE functiedialoogvenster open voor de volgende stap in de zelfstudie.
Voeg het MATCH Type-argument toe
Het derde en laatste argument van de MATCH-functie is de match_type argument; het vertelt Excel hoe het te matchen Opzoekwaarde met waarden in de Lookup_array - de beschikbare keuzes zijn 1, 0 of -1.
Dit argument is optioneel. Als deze wordt weggelaten, gebruikt de functie de standaardwaarde 1.
- Als het Match_type = 1 of is weggelaten: MATCH vindt de grootste waarde die kleiner is dan of gelijk aan de zoekwaarde. De Lookup_array-gegevens moeten in oplopende volgorde worden gesorteerd.
- Als het Match_type = 0: MATCH vindt de eerste waarde die exact gelijk is aan de Lookup_value. De Lookup_array-gegevens kunnen in willekeurige volgorde worden gesorteerd.
- Als het Match_type = -1: MATCH vindt de kleinste waarde die groter is dan of gelijk is aan de Lookup_value. De Lookup_array-gegevens moeten in aflopende volgorde worden gesorteerd.
Deze stappen moeten worden ingevoerd na de komma die is ingevoerd in de vorige stap op de rij_getal regel in de INHOUDSOPGAVE functie.
- De komma volgen op de rij_getal regel, typ een nul - 0 - omdat we willen dat de geneste functie exact overeenkomt met de termen die we in cellen hebben ingevoerd D3 en E3.
- Typ een sluitende ronde haak - ) - om het WEDSTRIJD functie.
- Verlaat de Formula Builder open voor de volgende stap in de tutorial.
De INDEX-functie beëindigen
Nu dat het WEDSTRIJD Als de functie is voltooid, gaan we naar de derde regel van de Formula Builder en voeren we het laatste argument in voor de INHOUDSOPGAVE functie. Dit derde en laatste argument is het kolom_getal argument dat Excel het kolomnummer in het bereik vertelt D6 naar F11; dit is waar het de informatie zal vinden die we door de functie willen teruggeven. In dit geval een leverancier voor titanium widgets.
- Klik op de kolom_getal lijn.
- Voer het nummer drie in - 3 - op deze regel omdat we naar gegevens zoeken in de derde kolom van het bereik D6 naar F11.
- Nogmaals, verlaat de Formula Builder open voor de volgende stap in de tutorial.
De matrixformule maken
Voordat u de. Sluit Formula Builder, we moeten onze geneste functie omzetten in een matrixformule; dit is wat onze functie toestaat om naar meerdere termen in de gegevenstabel te zoeken. In deze zelfstudie willen we twee termen matchen: widgets van kolom 1 en Titanium uit kolom 2.
Een matrixformule maken in Excel wordt gedaan door op de. Te drukken CTRL, VERSCHUIVING, en ENTER toetsen op het toetsenbord tegelijkertijd. Eenmaal ingedrukt, wordt de functie omringd door accolades, wat aangeeft dat de functie nu een array is.
- Sluit de Formula Builder door op te klikken Gedaan knop.
- Selecteer vervolgens cel F4 en druk op de invoeren toets om de formule te bekijken.
- Als u de formule naar een array wilt converteren, drukt u tegelijkertijd op CTRL + SHIFT + ENTER op je toetsenbord.
- Indien correct gedaan a # N / A fout verschijnt in cel F3 - de cel waarin we de functie hebben ingevoerd.
- De # N / A fout verschijnt in cel F3 omdat cellen D3 en E3 zijn leeg. D3 en E3 zijn de cellen waar we de functie hebben verteld om de te vinden Lookup_values. Zodra gegevens aan deze twee cellen zijn toegevoegd, wordt de fout vervangen door informatie uit de database.
De zoekcriteria toevoegen
Ten slotte voegen we de zoektermen toe aan ons werkblad. Zoals vermeld in de vorige stap, willen we de voorwaarden matchen widgets van kolom 1 en Titanium uit kolom 2. Als, en alleen als, onze formule een overeenkomst voor beide termen vindt in de juiste kolommen in de database, wordt de waarde uit de derde kolom geretourneerd.
- Klik op cel D3.
- Type widgets en druk op de invoeren toets op het toetsenbord.
- Klik op cel E3.
- Type Titanium en druk op de invoeren toets op het toetsenbord.
- De naam van de leverancier Widgets Inc. zou moeten verschijnen in cel F3 - de locatie van de functie omdat het de enige vermelde leverancier is die Titanium Widgets verkoopt.
- Wanneer u op cel F3 klikt, verschijnt de volledige functie in de formulebalk boven het werkblad.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
In ons voorbeeld was er maar één leverancier voor titanium-widgets. Als er meer dan één leverancier is, wordt de leverancier die als eerste in de database wordt vermeld door de functie geretourneerd.
