Een Excel rangschikking formule is een formule die berekeningen uitvoert op de waarden in een of meer arrays in plaats van een enkele gegevenswaarde. In spreadsheetprogramma's is een array een bereik of reeks verwante gegevenswaarden die normaal worden opgeslagen in aangrenzende cellen in een werkblad. In deze handleiding wordt uitgelegd hoe u matrixformules gebruikt in alle moderne versies van Excel.
Deze zelfstudie is compatibel met de meeste versies van Excel, waaronder 2007, 2010, 2013, 2016 en Office 365.
Wat zijn matrixformules?
Matrixformules zijn vergelijkbaar met reguliere formules:
- Ze beginnen met een gelijkteken ( = )
- Gebruik dezelfde syntaxis als reguliere formules
- Gebruik dezelfde wiskundige operators
- Volg dezelfde volgorde van werken
In Excel worden matrixformules omringd door accolades { } - deze accolades kunnen niet alleen worden ingetypt; ze moeten aan een formule worden toegevoegd door op te drukken Ctrl , Verschuiving , en invoeren toetsen na het typen van de formule in een cel of cellen. Om deze reden wordt een matrixformule soms a genoemd CSE formule in Excel.
Een uitzondering op deze regel is wanneer accolades worden gebruikt om een array in te voeren als argument voor een functie die normaal gesproken slechts één waarde of celverwijzing bevat. Bijvoorbeeld in de onderstaande tutorial die gebruikt VLOOKUP en de KIEZEN functie om een formule voor links opzoeken te maken, wordt een array gemaakt voor de KIEZEN de functie van indexgetal argument door de accolades rond de ingevoerde array te typen.
Stappen voor het maken van een matrixformule
- Voer de formule in.
- Houd de toets ingedrukt Ctrl en Verschuiving toetsen op het toetsenbord.
- Druk op en laat los invoeren sleutel om de matrixformule te maken.
- Laat de … LosCtrl en Verschuiving sleutels.
Als dit correct wordt gedaan, wordt de formule omringd door accolades en bevat elke cel met de formule een ander resultaat.
Een matrixformule bewerken
Telkens wanneer een matrixformule wordt bewerkt, verdwijnen de accolades tussen de matrixformule. Om ze terug te krijgen, moet de matrixformule worden ingevoerd door op de. Te drukken Ctrl, Verschuiving, en invoeren toetsen opnieuw, net als toen de matrixformule voor het eerst werd gemaakt.
Typen arrayformules
Er zijn twee hoofdtypen matrixformules:
- Formules voor eencellige matrix die meerdere berekeningen uitvoeren in een cel met een enkel werkblad.
- Multi-cel array-formules die zich in meer dan één werkbladcel bevinden.
Formules voor een enkele matrix
Dit type matrixformule gebruikt een functie, zoals SOM, GEMIDDELDEof COUNT, om de uitvoer van een meercellige matrixformule te combineren tot een enkele waarde in een enkele cel. Een voorbeeld van een formule voor een array met een enkele cel zou zijn:
{= SOM (A1: A2 * B1: B2)}
Deze formule voegt het product van A1 B1 * en A2 * B2, en vervolgens retourneert het een enkel resultaat in een enkele cel in het werkblad. Een andere manier om de bovenstaande formule te schrijven is:
Multi-Cell Array-formules
Zoals hun naam doet vermoeden, bevinden deze matrixformules zich in meerdere cellen in het werkblad en retourneren ze een array als antwoord. Met andere woorden, dezelfde formule bevindt zich in twee of meer cellen en retourneert verschillende antwoorden in elke cel.
Elke kopie, of aanleg, van de matrixformule, voert dezelfde berekening uit in elke cel die het bewoont, maar elk exemplaar van de formule gebruikt verschillende gegevens in de berekeningen. Daarom produceert elk exemplaar verschillende resultaten. Een voorbeeld van een formule met meerdere celrijen ziet er als volgt uit:
{= A1: A2 * B1: B2}
Als het bovenstaande voorbeeld zich in bevindt cellen C1 en C2 in een werkblad, dan zijn de resultaten als volgt:
- De gegevens in A1 wordt vermenigvuldigd met de gegevens in B1en de resultaten worden opgeslagen in cel C1.
- De gegevens in A2 wordt vermenigvuldigd met de gegevens in B2en de resultaten worden opgeslagen in cel C2.
Arrays gebruiken om rijen en kolommen te transponeren
De TRANSPOSE functie wordt gebruikt om gegevens van een rij naar een kolom te kopiëren of omgekeerd. Deze functie is een van de weinige in Excel die altijd moet worden gebruikt als een matrixformule.
Array-formules en Excel-functies
Veel van de ingebouwde functies van Excel, zoals SOM, GEMIDDELDE, en COUNT, kan ook worden gebruikt in een matrixformule. Er zijn ook enkele functies, zoals de TRANSPOSE functie, die altijd moet worden ingevoerd als een array om te kunnen werken. Het nut van vele functies zoals INHOUDSOPGAVE en WEDSTRIJD of MAX en ALS kan worden uitgebreid door ze samen te gebruiken in een matrixformule.
Voorbeelden van matrixformules in Excel:
01 van 08Maak een eenvoudige enkele-celarrayformule
Formules met een eencellige matrix voeren normaal eerst een berekening met meerdere cellen uit en gebruiken vervolgens een functie zoals GEMIDDELDE of SUM om de uitvoer van de array te combineren in een enkel resultaat.
02 van 08Negeer foutwaarden bij het zoeken naar gegevens
Deze matrixformule gebruikt de GEMIDDELDE, ALS, en ISNUMBER functies om de gemiddelde waarde voor de bestaande gegevens te vinden terwijl foutwaarden zoals worden genegeerd # DIV / 0! en #NAAM?
03 van 08Tel Cells of Data
Gebruik de SOM enALS functies in een matrixformule om cellen met gegevens te tellen die aan een van de verschillende voorwaarden voldoen; deze techniek verschilt van het gebruik van Excel's COUNTIFS functie, die vereist dat aan alle gestelde voorwaarden wordt voldaan voordat de cel wordt geteld.
04 van 08Zoek het grootste positieve of negatieve nummer
Dit voorbeeld combineert de MAX functie enALS functie in een matrixformule die de grootste of maximale waarde voor een gegevensbereik vindt wanneer aan specifieke criteria is voldaan.
05 van 08Zoek het kleinste positieve of negatieve nummer
Vergelijkbaar met het bovenstaande voorbeeld, deze combineert de MIN functie en ALS functie in een matrixformule om de kleinste of minimale waarde voor een gegevensbereik te vinden wanneer aan specifieke criteria wordt voldaan.
06 van 08Vind de Midden- of Mediaewaarde
De MEDIAAN functie in Excel vindt de middelste waarde voor een lijst met gegevens. Door het te combineren met de ALS functie in een matrixformule, kan de middelste waarde voor verschillende groepen gerelateerde gegevens worden gevonden.
07 van 08Maak een opzoekformule met meerdere criteria
Deze matrixformule omvat het nesten van de WEDSTRIJD en INHOUDSOPGAVE functies om specifieke informatie in een database te vinden.
08 van 08Maak een formule voor links opzoeken
De VLOOKUP functioneert normaal alleen naar gegevens in kolommen rechts, maar door deze te combineren met de KIEZEN functie, kunt u een formule voor links opzoeken maken die kolommen met gegevens aan de linkerkant van de kolom zal doorzoeken Opzoekwaarde argument.
