Basisfuncties in Excel werken prima voor eenvoudige berekeningen, maar worden al snel ingewikkeld bij complexe data-analyses. Je krijgt dan geneste formules die moeilijk te lezen zijn, meerdere hulpkolommen die je spreadsheet onoverzichtelijk maken en formules die kunnen mislukken wanneer je gegevens veranderen. Hier komen matrixformules in Excel om de hoek kijken.
Met matrixformules kunt u berekeningen uitvoeren op volledige gegevensbereiken in één formule. Daarom kun je Razendsnelle zoekopdrachten uitvoeren, filter en sorteer met één krachtige expressie, in plaats van dat u aparte formules voor elke rij of kolom moet schrijven. Excel is niet nieuw, maar sommige mensen blijven vasthouden aan de oude werkwijze wanneer deze functies hun werk eenvoudiger en efficiënter kunnen maken.
Snelle links
5. XZOEKEN
Presteert elke keer beter dan VLOOKUP.
XLOOKUP is de opzoekfunctie die er vanaf het begin al had moeten zijn. In tegenstelling tot VLOOKUP, waarbij je kolommen moet tellen en alleen naar rechts zoekt, werkt XLOOKUP in elke richting en gebruikt het daadwerkelijke kolomverwijzingen. De syntaxis is als volgt:
=XLZOEKEN(opzoekwaarde, opzoekmatrix, retourmatrix, [indien_niet_gevonden], [zoekmodus], [zoekmodus])
Dit is wat elke parameter betekent:
- opzoekwaarde: De specifieke waarde die u zoekt. Dit kan een onderdeelnummer, productcode of een andere identificatie in uw dataset zijn.
- lookup_array: Het bereik waarin Excel zoekt opzoekwaarde Van jou. Dit is meestal één kolom of rij met je zoekcriteria.
- retour_array: Het bereik met de waarden die u wilt ophalen. Dit kan een enkele kolom, meerdere kolommen of zelfs een hele tabelsectie zijn.
- if_not_found (optioneel): Aangepaste tekst of waarde om weer te geven wanneer er geen match is gevonden. Dit voorkomt vervelende #N/A-fouten en maakt het mogelijk om in plaats daarvan "Niet gevonden" of "Onderdeelnummer controleren" weer te geven.
- match_mode (optioneel): Bepaalt het type overeenkomst. Gebruik 0 voor een exacte overeenkomst (standaard), -1 voor de volgende exacte of kleinere overeenkomst, 1 voor de volgende exacte of grotere overeenkomst en 2 voor een jokerovereenkomst.
- zoekmodus (optioneel): Geeft de zoekrichting aan. Gebruik 1 voor een zoekopdracht van begin tot eind (standaard), -1 voor een zoekopdracht van eind tot eind en 2 voor een binaire zoekopdracht op gesorteerde gegevens.
Laten we een voorbeeld nemen van een spreadsheet voor mechanische inventarisatie. De volgende formule zoekt naar het onderdeelnummer "BRG-002" binnen een reeks onderdeel-ID's en retourneert de bijbehorende gegevens. Als het onderdeel niet aanwezig is, wordt "Onderdeel niet gevonden" weergegeven in plaats van een foutmelding.
=XLOOKUP("BRG-002", A:A, A:H, "Onderdeel niet gevonden")
Met XLOOKUP kunt u gegevens uit verschillende kolommen halen zonder de omslachtige kolomberekeningen die u in VLOOKUP tegenkomt, waardoor het een van de belangrijkste functies is. Excel-functies om snel gegevens te vinden.
4. SUMPRODUCT
Energiecentrale voor voorwaardelijke berekeningen
SOMPRODUCT telt niet alleen getallen op, maar vermenigvuldigt ook matrices en telt de resultaten op. Dit maakt het handig voor complexe voorwaardelijke berekeningen die meerdere hulpkolommen vereisen.
De formule is als volgt:
=SUMPRODUCT(array1, [array2], [array3], ...)
hier, array1 Dit is het eerste bereik van waarden dat vermenigvuldigd moet worden. Meestal is dit uw primaire gegevenskolom, zoals hoeveelheden of kosten. array2 Het is een optioneel tweede bereik voor vermenigvuldiging, dat vaak criteria of voorwaardelijke logica bevat die gebruikmaakt van vergelijkingsoperatoren.
Ze worden nuttiger wanneer we logische operatoren binnen arrays gebruiken. Wanneer we bijvoorbeeld voorwaarden zoals (leverancier="Siemens") typen, converteert Excel de resultaten WAAR/ONWAAR naar 1/0, wat berekeningen mogelijk maakt.
De volgende formule berekent bijvoorbeeld de totale voorraadwaarde voor onderdelen die alleen door Siemens worden geleverd. De formule vermenigvuldigt de hoeveelheden met de eenheidskosten, maar alleen voor rijen waar de leverancier aan de criteria voldoet.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Op soortgelijke wijze vindt u met de volgende formule de totale kosten van een voorraad lagers in voorraad:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Er zijn twee voorwaarden tegelijk van toepassing: de categorie moet 'Lagers' zijn en de voorraadniveaus moeten 15 eenheden of hoger zijn, zodat we lagercategorieën kunnen identificeren die voldoende voorraaddekking hebben.
In tegenstelling tot traditionele SUM-functies met meerdere criteria, heeft SUMPRODUCT geen complexe geneste structuren nodig, omdat het meerdere voorwaarden in één leesbare formule verwerkt. SOM-functies in Excel, Net als SOM.ALS en SOMMEN.ALS zijn ze uitstekend geschikt voor eenvoudige voorwaardelijke optellingen, maar de functie SOMPRODUCT is ideaal als u waarden moet vermenigvuldigen voordat u ze optelt of als u complexere logische bewerkingen moet uitvoeren.
3. FILTER
Maakt dynamische gegevensextractie eenvoudig
FILTER extraheert rijen uit uw dataset op basis van de door u opgegeven voorwaarden. In tegenstelling tot handmatig filteren genereert deze functie dynamische resultaten die automatisch worden bijgewerkt wanneer de brongegevens veranderen. De FILTER-syntaxis is als volgt:
=FILTER(matrix, inclusief, [indien_leeg])
Dit is wat elke invoer regelt:
- array (bereik): Het volledige gegevensbereik dat u wilt filteren. Dit omvat alle kolommen die u in uw resultaten wilt opnemen, niet alleen de criteriakolom.
- erbij betrekken: Logische voorwaarde die aangeeft welke rijen moeten worden geretourneerd. Gebruikt vergelijkingsoperatoren om TRUE/FALSE-arrays voor elke rij te maken.
- if_empty (optioneel): Geeft een aangepast bericht weer wanneer geen enkele rij aan uw criteria voldoet. Voorkomt #CALC!-fouten en geeft een betekenisvolle tekst weer, zoals 'Geen overeenkomende resultaten gevonden'.
De functie werkt door uw voorwaarde te evalueren aan de hand van elke rij in het bereik. Wanneer de voorwaarde TRUE retourneert, verschijnt die hele rij in de gefilterde resultaten. Hier is een voorbeeld uit een spreadsheet voor mechanische inventarisatie:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Deze formule extraheert alle rijen waarin de resource 'Timken' is en de categorie 'Lagers'. De asterisk (*) creëert een EN-voorwaarde door de logische arrays met elkaar te vermenigvuldigen.
Wanneer u nieuwe gegevens aan uw bronbereik toevoegt, De FILTER-functie gebruiken in Excel Het is logischer dan handmatig sorteren en tijdelijke tabellen, omdat gefilterde resultaten automatisch worden bijgewerkt. Dit maakt het handig voor het maken van live dashboards en rapporten.
2. .
Unieke waarden extraheren zonder duplicaten
UNIQUE haalt unieke waarden uit uw gegevensbereik en voorkomt automatisch duplicaten. Deze functie is belangrijk als u vervolgkeuzelijsten wilt maken, gegevenscategorieën wilt analyseren en samenvattingsrapporten wilt maken. De formule is:
=UNIEK(array, [per_kolom], [exact_eenmaal])
Dit is hoe elke invoer werkt:
- array (bereik): Het bereik met de gegevens waaruit u duplicaten wilt verwijderen. Dit kan één kolom, meerdere kolommen of een hele sectie van de tabel zijn.
- by_col (optioneel): FALSE vergelijkt rijen om de uniciteit te bepalen (standaard), terwijl TRUE kolommen vergelijkt. In de meeste scenario's wordt echter de standaardrijvergelijking gebruikt.
- exact_eenmaal (optioneel): FALSE retourneert alle unieke waarden, inclusief de waarden die meerdere keren voorkomen (standaard), en TRUE retourneert alleen waarden die precies één keer in de gegevensset voorkomen.
De functie UNIQUE evalueert elke rij of waarde in uw array en retourneert alleen de eerste keer dat elk uniek element voorkomt. De volgorde komt overeen met de oorspronkelijke gegevensreeks. Hier is een voorbeeld:
=UNIEK(G2:G22)
Deze formule extraheert alle unieke leveranciersnamen uit kolom G van Leverancier en creëert een schone, dubbele lijst. Ik gebruik deze om dropdownlijsten of samenvattingsrapporten voor leveranciers te maken.
U kunt het ook over de hele tabel gebruiken, zoals hieronder weergegeven:
=UNIEK(A2:F100)
Retourneert unieke combinaties in alle kolommen (A tot en met F) en geeft afzonderlijke voorraadrecords weer. Als twee onderdelen in elke kolom identieke waarden hebben, wordt er slechts één in de resultaten weergegeven.
Bij het werken met grote datasets elimineert UNIQUE het omslachtige proces van het handmatig verwijderen van duplicaten. Dynamische resultaten worden bijgewerkt zodra er nieuwe data binnenkomt. En omdat UNIQUE spillovermatrices creëert, elimineert deze aanpak het gedoe van het aanpassen van de grootte van tabellen door automatisch te schalen om alle unieke waarden te accommoderen. Ik gebruik het om overzichtelijke referentielijsten te onderhouden en betrouwbare datavalidatiebereiken op te bouwen.
1. SORTEREN en SORTEREN OP
Organiseer uw gegevens zonder de originele gegevens in gevaar te brengen
De functies SORT en SORTBY ordenen gegevens dynamisch, terwijl de bron intact blijft. SORT verwerkt basissortering op kolompositie, terwijl SORTBY sorteert op basis van waarden in verschillende kolommen, wat u meer flexibiliteit biedt voor complexe sortering.
SORT gebruikt deze structuur:
=SORTEREN(matrix, [sort_index], [sort_order], [by_col])
Dit is wat elke parameter regelt:
- matrix: Het gegevensbereik dat u wilt sorteren: omvat alle kolommen die in de gesorteerde resultaten moeten worden weergegeven.
- sort_index (optioneel): Het kolomnummer binnen de matrix waarop gesorteerd moet worden. Gebruik 1 voor de eerste kolom, 2 voor de tweede kolom, enzovoort (standaard is 1).
- sorteervolgorde (optioneel): Gebruik 1 voor oplopende volgorde (standaard) en -1 voor aflopende volgorde.
- by_col (optioneel): FALSE om op rijen te sorteren (standaard), TRUE om op kolommen te sorteren. In de meeste scenario's wordt rijsortering gebruikt.
De SORT.OP-functie heeft de volgende vorm:
=SORTEREN(matrix, op_matrix1, [sorteervolgorde1], [op_matrix2], [sorteervolgorde2], ...)
De transacties omvatten:
- matrix: Het bereik van de gegevens die u wilt sorteren. Dit bereik is vergelijkbaar met de functie SORTEREN en bevat alle kolommen die u in de resultaten wilt opnemen.
- by_array1: Het bereik met de waarden die de sorteervolgorde bepalen, kan elke kolom zijn, zelfs buiten het bereik van de hoofdmatrix.
- sort_order1 (optioneel): 1 voor oplopende volgorde (standaard), -1 voor aflopende volgorde.
- by_array2, sort_order2 (optioneel): Aanvullende sorteercriteria voor sorteren op meerdere niveaus.
Kijken we naar een voorbeeld uit een spreadsheet voor mechanische inventarisatie, dan zien we dat deze functies realistische sorteerscenario's aankunnen:
=SORTEREN(A2:H22, 4, -1)
Hiermee wordt de gehele voorraad in aflopende volgorde gesorteerd op voorraadniveau, waarbij artikelen met de hoogste voorraad als eerste worden weergegeven. De formule sorteert op kolom 4 (voorraadniveau), waarbij alle relaties tussen de rijen behouden blijven.
Ik gebruik de functie SORTEREN.OP. In plaats van SORTEREN kunt u het gebruiken om meer controle te krijgen over sorteercriteria en meerdere sorteerniveaus. De volgende formule sorteert bijvoorbeeld eerst alfabetisch op categorie en vervolgens op voorraadniveau, van hoog naar laag binnen elke categorie.
=SORTEREN OP(A2:H22, C2:C22, 1, D2:D22, -1)
Georganiseerde spreadsheets, slimmere resultaten
Matrixformules elimineren de rommel van hulpkolommen en geneste functies die spreadsheets lastig te onderhouden maken. U krijgt enkelvoudige formules die meerdere bewerkingen verwerken, waardoor werkmappen overzichtelijker en professioneler worden.
Een belangrijk voordeel zijn dynamische functies, waarbij resultaten automatisch worden bijgewerkt wanneer de brongegevens veranderen. Dit elimineert handmatige updates of kapotte formulereeksen, waardoor uw spreadsheets betrouwbaarder zijn voor doorlopende analyses.
De matrixfunctiebibliotheek van Excel breidt zich steeds verder uit dan deze basistools. Wanneer ik gegevens uit meerdere bronnen moet combineren, gebruik ik de functies VSTACK en HSTACK om bereiken te combineren. Samen creëren deze functies krachtige workflows voor gegevensverwerking die met traditionele formules onmogelijk zouden zijn.
