Hoe zoekfuncties in excel gebruiken?

voorbeeldbestanden

1. ADRES-functie

De functie ADRES retourneert een geldige celverwijzing op basis van het kolom- en rijadres. In eenvoudige bewoordingen kunt u het adres van een cel maken met behulp van het rijnummer en kolomnummer.

Syntaxis

ADRES(regelnummer,kolomnummer,abs_nummer,A1,bladtekst)

Argumenten

• row_num: een getal om het rijnummer op te geven.
• column_num: een getal om het kolomnummer op te geven.
• [abs_num]: Referentietype.
• [A1]: referentiestijl.
• [sheet_text]: een tekstwaarde als bladnaam.

Opmerkingen

• Standaard retourneert de functie ADDRESS een absolute verwijzing in het resultaat.

Voorbeeld

In het onderstaande voorbeeld hebben we verschillende argumenten gebruikt om alle soorten resultaten te krijgen.

Met referentiestijl R1C1 :

• Relatieve referentie.
• Relatieve rijreferentie en absolute kolomreferentie.
• Absolute rij- en relatieve kolomreferentie.
• Absolute referentie.

Met referentiestijl A1:

• Relatieve referentie.
• Relatieve rijreferentie en absolute kolomreferentie.
• Absolute rij- en relatieve kolomreferentie.
• Absolute referentie.

2. ZONES-functie

De functie GEBIEDEN retourneert een getal dat het aantal bereiken vertegenwoordigt in de referentie die u hebt opgegeven. Simpel gezegd telt het feitelijk de verschillende werkbladgebieden waarnaar u in de functie verwijst.

syntaxis

VELDEN (referentie)

Argumenten

• verwijzing: een verwijzing naar een cel of celbereik.

Opmerkingen

• De verwijzing kan een cel, een celbereik of een benoemd bereik zijn.
• Als u naar meerdere celverwijzingen wilt verwijzen, moet u al deze verwijzingen tussen meerdere sets haakjes plaatsen en komma’s gebruiken om elke verwijzing van de andere te scheiden.

Voorbeeld

In het onderstaande voorbeeld hebben we de functie zones gebruikt om de numerieke referentie in een benoemd bereik te verkrijgen.

Zoals je kunt zien zijn er drie kolommen in het bereik en het resultaat leverde 3 op.

3. KIES de functie

De functie SELECT retourneert een waarde uit de lijst met waarden op basis van het opgegeven positienummer. Simpel gezegd: het zoekt naar een waarde in een lijst op basis van zijn positie en retourneert deze in het resultaat.

Syntaxis

SELECT(aantal_index,waarde1,waarde2,…)

Argumenten

• index_num: nummer dat wordt gebruikt om de positie van de waarde in de lijst te specificeren.
• waarde1: Een celbereik of invoerwaarde waaruit u kunt kiezen.
• [waarde2]: een celbereik of invoerwaarde waaruit u kunt kiezen.

Opmerkingen

• U kunt naar een cel verwijzen of u kunt ook waarden rechtstreeks in de functie invoegen.

Voorbeeld

In het onderstaande voorbeeld hebben we de functie KIEZEN met een vervolgkeuzelijst gebruikt om vier verschillende dingen te berekenen (som, gemiddelde, max en mix). Daarom gebruikten we de onderstaande formule om de vier dingen te berekenen:

=KIES(VLOOKUP(K2,Q1:R4,2,FALSE),SOM(O2:O9),GEMIDDELD(O2:O9),MAX(O2:O9),MIN(O2:O9))

We hebben een kleine tabel met de namen van de vier berekeningen die we willen, en een serienummer voor elke berekening in de overeenkomstige cel.

Daarna hebben we een vervolgkeuzelijst voor alle vier de berekeningen. Om nu het indexnummer in de kiesfunctie uit deze kleine tabel te halen, hebben we een opzoekformule die het serienummer retourneert volgens de waarde die is geselecteerd in de vervolgkeuzelijst.

En in plaats van waarden gebruikten we vier formules voor vier verschillende berekeningen.

4. KOLOM-functie

De functie KOLOM retourneert het kolomnummer voor de opgegeven celverwijzing. Zoals u weet bestaat elke celverwijzing uit een kolomnummer en een rijnummer. Het neemt dus het kolomnummer en retourneert dit in het resultaat.

Syntaxis

KOLOM([referentie])

Argumenten

• referentie: een celverwijzing waarvan u het kolomnummer wilt weten.

Opmerkingen

• Je kunt niet naar meerdere referenties verwijzen.
• Als u naar een array verwijst, retourneert de kolomfunctie ook de kolomnummers in een array.
• Als u verwijst naar een bereik van meerdere cellen, wordt het kolomnummer van de meest linkse cel geretourneerd. Als u bijvoorbeeld naar het bereik A1:C10 verwijst, wordt het kolomnummer van cel A1 geretourneerd.
• Als u het opgeven van een verwijzing overslaat, wordt het kolomnummer van de huidige cel geretourneerd.

Voorbeeld

In het onderstaande voorbeeld hebben we KOLOM gebruikt om het kolomnummer van cel A1 te verkrijgen.

Zoals ik al zei: als je de celverwijzing negeert, wordt het kolomnummer van de huidige cel geretourneerd. In het onderstaande voorbeeld hebben we COLUMN gebruikt om een koptekst met serienummers te maken.

5. KOLOMMEN-functie

De functie KOLOMMEN retourneert het aantal kolommen waarnaar wordt verwezen in de gegeven verwijzing. Simpel gezegd telt het het aantal kolommen in het opgegeven bereik en retourneert dat aantal.

Syntaxis

KOLOMMEN(tabel)

Argumenten

• tabel: Een tabel of celbereik waaruit u het aantal kolommen wilt halen.

Opmerkingen

• U kunt ook een benoemd bereik gebruiken.
• De functie KOLOMMEN houdt zich niet bezig met waarden in cellen, maar retourneert eenvoudigweg het aantal kolommen in een verwijzing.

Voorbeeld

In het onderstaande voorbeeld hebben we COLUMN gebruikt om het aantal kolommen in het bereik A1:F1 te verkrijgen.

6. FORMULATEX-functie

De functie FORMULATEXT retourneert de formule voor de cel waarnaar wordt verwezen. En als er geen formule in de cel waarnaar wordt verwezen, een waarde of een spatie staat, wordt er een #N/A geretourneerd.

Syntaxis

TEKSTFORMULE (referentie)

Argumenten

• verwijzing: de celverwijzing waaruit u de formule als tekst wilt ophalen.

Opmerkingen

• Als u naar een andere werkmap verwijst, moet die werkmap geopend zijn, anders wordt de formule niet weergegeven.
• Als u verwijst naar een bereik van meer dan één cel, wordt de formule voor de cel linksboven van het opgegeven bereik geretourneerd.
• Er wordt een foutwaarde “#N/A” geretourneerd als de cel die u als referentie gebruikt geen formules bevat, een formule heeft met meer dan 8192 tekens, een cel beveiligd is of een externe werkmap niet geopend is.
• Als u in een cirkelverwijzing naar twee cellen verwijst, worden de resultaten van beide cellen geretourneerd.

Voorbeeld

In het onderstaande voorbeeld hebben we formuletekst met verschillende verwijzingstypen gebruikt. Wanneer u naar een cel verwijst die geen formule heeft, retourneert deze de foutwaarde “#N/B”.

7. HSEARCH-functie

De functie HLOOKUP zoekt naar een waarde in de bovenste rij van een tabel en retourneert de waarde in dezelfde kolom van de overeenkomende waarde met behulp van het indexnummer. Simpel gezegd: het zoekt horizontaal.

Syntaxis

HLOOKUP(opzoekwaarde, tabelmatrix, rijindexnummer, [bereikopzoeken])

Argumenten

• lookup_value: De waarde waarnaar u wilt zoeken.
• table_array: De gegevenstabel of array waaruit u de waarde wilt vinden.
• row_index_num: Een numerieke waarde die een aantal rijen vertegenwoordigt vanaf de bovenste rij waaruit u de waarde wilt halen. Als u bijvoorbeeld 2 opgeeft en uw opzoekwaarde staat in A10 in de gegevenstabel, wordt de waarde in cel B10 geretourneerd.
• [range_lookup]: een logische waarde om het opzoektype op te geven. Als u een zoekopdracht met exacte overeenkomsten wilt uitvoeren, gebruikt u FALSE en als u niet-exacte overeenkomsten wilt zoeken, gebruikt u TRUE (standaard).

Opmerkingen

• U kunt jokertekens gebruiken.
• U kunt exacte match en fuzzy match doen.
• Zorg er bij het uitvoeren van een fuzzy match voor dat u de gegevens in oplopende volgorde van links naar rechts sorteert. Als de gegevens niet in oplopende volgorde staan, zou dit een onnauwkeurig resultaat opleveren.
• Als range_lookup waar is of wordt weggelaten, wordt er een niet-exacte overeenkomst uitgevoerd, maar wordt een exacte overeenkomst geretourneerd als de opzoekwaarde binnen het opzoekbereik bestaat.
• Als range_lookup waar is of weggelaten, en de opzoekwaarde ligt niet in het opzoekbereik, wordt de dichtstbijzijnde waarde geretourneerd die kleiner is dan de opzoekwaarde.
• Als range_lookup false is, hoeft het gegevensbereik niet te worden gesorteerd.

Voorbeeld

In het onderstaande voorbeeld hebben we de functie HLOOKUP met MATCH gebruikt om een dynamische formule te maken, en vervolgens een vervolgkeuzelijst gebruikt om de zoekwaarde van de cel te wijzigen.

De veldnaam uit cel C7 wordt gebruikt als zoekwaarde. Bereik B1: F5 als tabelarray en voor row_index_num hebben we de matchfunctie gebruikt om het rijnummer te verkrijgen.

Telkens wanneer u de waarde in cel C9 wijzigt, wordt het rijnummer van de tabel geretourneerd. U hoeft uw formule niet steeds opnieuw te wijzigen. Wijzig gewoon de waarden met de vervolgkeuzelijst en u krijgt er een waarde voor.

8. HYPERLINK-functie

De functie HYPERLINK retourneert een tekenreeks waaraan een hyperlink is gekoppeld. In eenvoudige bewoordingen, zoals de HYPERLINK-optie in Excel, helpt de HYPERLINK-functie u bij het maken van een hyperlink.

Syntaxis

HYPERLINK(link_locatie,[vriendelijke_naam])

Argumenten

• Link_Location: De locatie waarvoor u een HYPERLINK wilt toevoegen. Het kan verder worden onderverdeeld in twee termen.
  1. link: Dit kan het adres zijn van een cel of een celbereik in hetzelfde werkblad of in een ander werkblad of werkmap. We kunnen ook een bladwijzer uit een Word-document koppelen.
  2. locatie: Dit kan een link zijn naar een harde schijf, een server die het UNC-pad gebruikt, of een URL van internet of intranet. (In Excel online kunt u het webadres alleen gebruiken voor de HYPERLINK-functie). U kunt een koppeling naar de functie invoegen door deze als tekst tussen aanhalingstekens in te voegen of door als tekst naar een cel te verwijzen die de koppeling bevat. Zorg ervoor dat u ‘HTTPS://’ vóór een webadres gebruikt.
• [vriendelijke_naam]: dit is een optioneel onderdeel van deze functie. Het fungeert als het gezicht van de verbindingslink.
  1. U kunt elk type tekst, nummer of beide gebruiken.
  2. U verwijst ook naar een cel die de beschrijvende_naam bevat.
  3. Als u dit negeert, gebruikt de functie het linkadres om weer te geven.
  4. Als vriendelijke_naam een fout retourneert, geeft de functie een fout weer.

Opmerkingen

• Een bestand koppelen dat is opgeslagen op een webadres: U kunt een bestand gebruiken dat is opgeslagen op een webadres. Hierdoor kunnen we het bestand efficiënt delen.
• Een bestand koppelen dat op een harde schijf is opgeslagen: u kunt deze functie ook gebruiken als u offline werkt. U kunt een bestand dat op uw harde schijf is opgeslagen, koppelen en via één Excel-werkblad openen. U hoeft niet naar elke map te gaan om ze te openen.
• Link Word-documentbestand: dit is ook een geweldige functie van de HYPERLINK-functie. U kunt een Word-documentbestand of een specifieke locatie in een Word-documentbestand koppelen met behulp van een bladwijzer.
• Een bestand koppelen zonder een beschrijvende naam te gebruiken: Als u de daadwerkelijke link naar het bestand of de locatie aan de gebruiker wilt weergeven. In deze situatie hoeft u alleen maar de beschrijvende naamdeclaratie in de functie HYPERLINK te negeren.

9. INDIRECTE functie

De functie INDIRECT retourneert een geldige verwijzing uit een tekstreeks die een celverwijzing vertegenwoordigt. In eenvoudige bewoordingen kunt u naar een celbereik verwijzen door het celadres als tekstwaarde te gebruiken.

Syntaxis

INDIRECT(ref_tekst, [a1])

Argumenten

• ref_text: tekst die het adres van een cel, het adres van een celbereik, een benoemd bereik of een tabelnaam vertegenwoordigt. Bijvoorbeeld A1, B10:B20 of MyRange.
• [a1]: een getal of Booleaanse waarde die het celverwijzingstype vertegenwoordigt dat u opgeeft in ref_text. Als u bijvoorbeeld referentiestijl A1 wilt gebruiken, gebruikt u TRUE of 1, en als u referentiestijl R1C1 wilt gebruiken, gebruikt u FALSE of 0 voor referentiestijl R1C. En als u het celverwijzingstype niet opgeeft, wordt standaard de A1-stijl gebruikt.

Opmerkingen

• Wanneer u naar een andere werkmap hebt verwezen, moet die werkmap worden geopend.
• Als u een rij of kolom invoegt in het bereik waarnaar u verwijst, zal INDIRECT die referentie niet bijwerken.
• Als u tekst rechtstreeks in de functie wilt invoegen, moet u deze tussen dubbele aanhalingstekens plaatsen of kunt u ook verwijzen naar een cel met de tekst die u als referentie wilt gebruiken.

Voorbeeld

1. Verwijzing naar een ander werkblad

U kunt ook naar een ander werkblad verwijzen met INDIRECT en u moet daar de werkbladnaam invoegen. In het onderstaande voorbeeld hebben we de indirecte functie gebruikt om naar een ander werkblad te verwijzen en hebben we de bladnaam in cel A2 en de celverwijzing in cel B2.

In cel C2 hebben we de volgende formule gebruikt om de tekst te combineren.

=INDIRECT(“’”&A2&”’!”&B2)

Deze combinatie creëert tekst die door de INDIRECT-functie wordt gebruikt om naar cel A1 in blad1 te verwijzen en het beste is dat wanneer u de werkbladnaam of het celadres wijzigt, de verwijzing automatisch verandert.

Cel A1 in “Blad1” heeft de waarde “Ja” en daarom retourneert indirect de waarde “Ja”.

2. Verwijzing naar een andere werkmap

U kunt ook naar een andere werkmap verwijzen, op dezelfde manier als we deden voor een ander werkblad. Het enige dat u hoeft te doen, is eenvoudigweg een werkmapnaam toevoegen aan uw tekst die u als referentie gebruikt.

In het bovenstaande voorbeeld hebben we de volgende formule gebruikt om de waarde van cel A1 van werkmap “Boek1” te verkrijgen.

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

Omdat we de werkmapnaam hebben in cel “A2”, de werkbladnaam in cel “B2” en de celnaam in cel “C2”. We combineren ze om te gebruiken als invoertekst in een indirecte functie.

Opmerking: Zorg er bij het combineren van een celverwijzing als tekst voor dat u de juiste referentiestructuur volgt.

3. Benoemde bereiken gebruiken

Ja, u kunt ook naar een benoemd bereik verwijzen met behulp van de indirecte functie. Het is makkelijk. Nadat u een benoemd bereik hebt gemaakt, moet u dat benoemde bereik als tekst invoeren in INDIRECT.

In het bovenstaande voorbeeld hebben we een vervolgkeuzelijst in cel E1 die een lijst met benoemde bereiken bevat, en in cel E2 hebben we die naam gebruikt. Omdat het bereik B2:B5 de naam “Aantal” heeft en het bereik C2:C5 de naam “Bedrag”.

Wanneer u een hoeveelheid in de vervolgkeuzelijst selecteert, verwijst de indirecte functie onmiddellijk naar het genoemde bereik. En wanneer u het bedrag in het vervolgkeuzemenu selecteert, krijgt u de som van het celbereik C2:C5.

11. ZOEKfunctie

De functie SEARCH retourneert een waarde (die u zoekt) uit een rij, kolom of tabel . Simpel gezegd: u kunt naar een waarde zoeken en SEARCH retourneert die waarde als deze in die rij, kolom of tabel staat.

Syntaxis

ZOEKEN(waarde, zoekbereik, [resultaatbereik])

Er zijn twee soorten ZOEK-functies.

• vectorvorm
• Tabelvorm

Argumenten

• waarde: de waarde waarnaar u wilt zoeken in een kolom of rij.
• lookup_range: De kolom of rij waaruit u de waarde wilt opzoeken.
• [result_range]: de kolom of rij waaruit u een waarde wilt retourneren. Dit is een optioneel argument.

Opmerkingen

• In plaats van het array-formulier te gebruiken, is het beter om VLOOKUP of HLOOKUP te gebruiken.

12.MATCH-functie

De functie MATCH retourneert het indexnummer van een arraywaarde. Simpel gezegd: de functie MATCH zoekt naar een waarde in de lijst en retourneert het positienummer van die waarde in de lijst.

Syntaxis

MATCH (lookup_value, lookup_array, [match_type])

Argumenten

• lookup_value : De waarde waarvan u de positie wilt verkrijgen uit een lijst met waarden.
• lookup_array : Celbereik of array bevat waarden.
• [match_type] : het getal (-1, 0 en 1) om op te geven hoe Excel naar de waarde in de zoeklijst zoekt.
  1. Als u 1 gebruikt, wordt de grootste waarde geretourneerd die gelijk is aan of kleiner is dan de zoekwaarde. De waarden in de lijst moeten in oplopende volgorde worden gesorteerd.
  2. Als u -1 gebruikt, wordt de kleinste waarde geretourneerd die gelijk is aan of groter is dan de zoekwaarde. De waarden in de lijst moeten in oplopende volgorde worden gesorteerd.
  3. Als u 0 gebruikt, wordt de exacte overeenkomst van de lijst geretourneerd.

Opmerkingen

• U kunt jokertekens gebruiken.
• Als er geen overeenkomende waarde in de lijst staat, retourneert #N/A.
• De matchfunctie is niet hoofdlettergevoelig.

Voorbeeld

In het onderstaande voorbeeld hebben we 1 als zoektype gebruikt en zoeken we naar de waarde 5.

Zoals ik al zei: als je 1 gebruikt in het zoektype, retourneert dit de grootste waarde die gelijk is aan of kleiner is dan de zoekwaarde. In de hele lijst staan 3 waarden kleiner dan 5 en 4 is de hoogste.

Dat is de reden waarom het in het resultaat 3 retourneerde, wat de positie is van de waarde 4.

13. Shift-functie

De OFFSET-functie retourneert een verwijzing naar een bereik dat zich een specifiek aantal rijen en kolommen verwijderd van een cel of celbereik bevindt. In eenvoudige bewoordingen kunt u naar een cel of een celbereik verwijzen met behulp van rijen en kolommen, beginnend bij een startcel.

Syntaxis

OFFSET(referentie, rijen, kolommen, [hoogte], [breedte])

Argumenten

• referentie : de referentie van waaruit u de offset wilt starten. Het kan een cel zijn of een reeks aangrenzende cellen.
• rijen : het aantal rijen dat OFFSET vertelt om omhoog of omlaag te gaan ten opzichte van de referentie. Om naar beneden te gaan heb je een positief getal nodig en om omhoog te gaan heb je een negatief getal nodig.
• cols : Het aantal kolommen vertelt OFFSET dat deze vanaf de referentie naar links of rechts moet bewegen. Om naar rechts te gaan heb je een positief getal nodig en om naar links te gaan heb je een negatief getal nodig.
• [hoogte] : een getal om aan te geven welke regels in de referentie moeten worden opgenomen.
• [breedte]: een getal om aan te geven welke kolommen in de referentie moeten worden opgenomen.

Opmerkingen

• OFFSET is een “vluchtige” functie, die elke keer dat er een verandering in een spreadsheet plaatsvindt, opnieuw wordt berekend.
• Het toont de #REF! foutwaarde als de offset zich buiten de rand van het werkblad bevindt.
• Als hoogte of breedte wordt weggelaten, worden de referentiehoogte en -breedte gebruikt.

Voorbeeld

In het onderstaande voorbeeld hebben we SOM met OFFSET gebruikt om een dynamisch bereik te creëren dat de waarden van alle maanden voor een bepaald product optelt.

14. LINE-functie

De functie RIJ retourneert het rijnummer van de cel waarnaar wordt verwezen. In eenvoudige woorden: met de RIJ-functie kunt u het rijnummer van een cel verkrijgen en als u niet naar een cel verwijst, retourneert deze het rijnummer van de cel waarin u deze invoegt.

Syntaxis

LIJN([referentie])

Argumenten

• verwijzing: een celverwijzing of celbereik waarvan u het rijnummer wilt controleren.

Opmerkingen

• Het omvat alle soorten bladen (grafiekblad, werkblad of macroblad).
• U kunt naar folio’s verwijzen, zelfs als deze zichtbaar, verborgen of zeer verborgen zijn.
• Als u geen waarde in de functie opgeeft, krijgt u het bladnummer van het blad waarop u de functie hebt toegepast.
• Als u een ongeldige bladnaam opgeeft, wordt een #N/A geretourneerd.
• Als u een ongeldige bladreferentie opgeeft, wordt er een #REF! geretourneerd.

Voorbeeld

In het onderstaande voorbeeld hebben we de rijfunctie gebruikt om het rijnummer te controleren van dezelfde cel waarin we de functie hebben gebruikt.

In het onderstaande voorbeeld hebben we naar een andere cel verwezen om het rijnummer van die cel te achterhalen.

U kunt de rijfunctie gebruiken om een lijst met serienummers in uw spreadsheet te maken. Het enige wat u hoeft te doen is simpelweg rijfuncties in een cel in te voeren en deze naar de cel te slepen waar u serienummers wilt toevoegen.

15. LIJNEN-functie

De functie RIJEN retourneert het aantal rijen in het bereik waarnaar wordt verwezen. In eenvoudige woorden: met de RIJEN-functie kunt u het aantal rijen tellen in het bereik waarnaar u verwijst.

Syntaxis

LIJNEN (tabel)

Argumenten

• array: Een celverwijzing of array om het aantal rijen te controleren.

Opmerkingen

• U kunt ook een benoemd bereik gebruiken.
• Het gaat niet om waarden in cellen, het retourneert eenvoudigweg het aantal rijen in een verwijzing.

Voorbeeld

In het onderstaande voorbeeld hebben we verwezen naar een verticaal bereik van 10 cellen en het resultaat leverde 10 op omdat het bereik 10 rijen heeft.

16. TRANSPOSE-functie

De TRANSPOSE-functie verandert de oriëntatie van een bereik. In eenvoudige woorden: met deze functie kunt u gegevens van een rij naar een kolom en van een kolom naar een rij wijzigen.

Syntaxis

TRANSPONEREN (tabel)

Argumenten

• array: Een array of bereik dat u wilt transponeren.

Opmerkingen

• U moet TRANSPOSE toepassen als een arrayfunctie, waarbij u hetzelfde aantal cellen in uw bronbereik gebruikt door op Ctrl+Shift+Enter te drukken.
• Als u cellen selecteert die kleiner zijn dan het bronbereik, worden de gegevens alleen voor die cellen getransponeerd.

Voorbeeld

Hier moeten we de gegevens van het bereik B2:D4 omzetten naar het bereik G2 tot I4:

Hiervoor moeten we eerst naar cel G2 gaan en het celbereik tot I4 selecteren.

Voer vervolgens (=TRANSPOSE(B2:D4)) in cel G2 in en druk op Ctrl+Shift+Enter.

TRANSPOSE converteert gegevens van rijen naar kolommen, en de formule die we hebben toegepast is een matrixformule, je kunt er geen enkele cel van wijzigen.