Matrixformules - richtlijnen en voorbeelden

Matrixformules - richtlijnen en voorbeelden

Opmerking: We willen u graag zo snel mogelijk de meest recente Help-inhoud in uw eigen taal bieden. Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor u is. Kunt u ons onder aan deze pagina laten weten of de informatie nuttig voor u was? Hier is het Engelstalige artikel ter referentie.

Een matrix formule is een formule waarmee meerdere berekeningen kunnen worden uitgevoerd op een of meer items in een matrix. U kunt een matrix beschouwen als een rij of kolom met waarden, of een combi natie van rijen en kolommen met waarden. Matrix formules kunnen meerdere resultaten opleveren, of één resultaat.

Vanaf de update van september 2018 voor Office 365kan elke formule die meerdere resultaten oplevert, automatisch worden overgelopen of in naburige cellen. Deze wijziging in gedrag is ook van invloed op verschillende nieuwe dynamische array-functies. Dynamische matrix formules, ongeacht of ze bestaande functies of de dynamische array functies gebruiken, hoeven alleen in één cel te worden ingevoerd en vervolgens te worden bevestigd door op Enterte drukken. Oudere matrix formules moeten eerst het hele uitvoer bereik selecteren en vervolgens de formule bevestigen met CTRL + SHIFT + ENTER. Ze worden vaak CSE -formules genoemd.

U kunt matrix formules gebruiken om complexe taken uit te voeren, zoals:

  • Snel voorbeeld sets maken.

  • Tel het aantal tekens in een bereik van cellen.

  • Alleen getallen opTellen die aan bepaalde voor waarden voldoen, zoals de laagste waarden in een bereik, of getallen die tussen een boven-en ondergrens vallen.

  • Som elke n-waarde in een bereik van waarden.

In de volgende voor beelden ziet u hoe u matrix formules met meerdere cellen en één cel kunt maken. Waar mogelijk hebben we voor beelden met een aantal dynamische array-functies en bestaande matrix formules die zijn ingevoerd als dynamische en verouderde matrix.

Voorbeelden downloaden

Down load een voor beeld van een werkmap met alle voor beelden van de matrix formules in dit artikel.

In deze oefening wordt beschreven hoe u matrixformules in meerdere cellen en in één cel kunt gebruiken om een set verkoopcijfers te berekenen. Voor de eerste set wordt met een formule in meerdere cellen een set met subtotalen berekend. Voor de tweede set wordt met een formule in één cel een eindtotaal berekend.

  • Een matrixformule in meerdere cellen

    Array met meerdere cellen in cel H10 = F10: F19 * G10: G19 voor het berekenen van het aantal door de prijs per eenheid verkochte auto's

  • Hier berekent u de totale verkoop van verhuis verwerkingen en sedans voor elke verkoper door = F19: F19 * G10: G19 in cel H10 in te voeren.

    Wanneer u op Enter drukt, ziet u de resultaten naar de cellen H10: H19. Zoals u ziet, is het overloop bereik gemarkeerd met een rand wanneer u een cel binnen het overloop bereik selecteert. Mogelijk ziet u ook dat de formules in de cellen H10: H19 grijs worden weer gegeven. Ze zijn alleen bedoeld voor verwijzingen, dus als u de formule wilt aanpassen, moet u cel H10 selecteren, waarin de hoofd formule zich bevindt.

  • Matrix formule met één cel

    Matrix formule met één cel om een eind totaal te berekenen met = som (F10: F19 * G10: G19)

    Typ of kopieer en plak = som (F10: F19 * G10: G19)in cel H20 van de voorbeeld werkmap en druk op Enter.

    In dit geval vermenigvuldigen de waarden in de matrix (het celbereik F10 tot en met G19) en gebruikt u vervolgens de functie som om de totalen samen toe te voegen. Het resultaat is een eind totaal van $1.590.000 in de verkoop.

    In dit voor beeld ziet u hoe krachtig dit type formule kan zijn. Stel dat u 1.000 rijen met gegevens hebt. U kunt een deel van of alle gegevens optellen door een matrix formule in één cel te maken in plaats van de formule omlaag te slepen in de 1.000 rijen. U ziet ook dat de formule met één cel in cel H20 volledig onafhankelijk is van de formule met meerdere cellen (de formule in de cellen H10 tot en met H19). Dit is een ander voor deel van het gebruik van matrix formules, flexibiliteit. U kunt de andere formules in kolom H wijzigen zonder dat dit van invloed is op de formule in H20. Het kan ook handig zijn om onafhankelijke totalen zoals deze te gebruiken, omdat het de nauw keurigheid van de resultaten kan valideren.

  • Dynamische matrix formules bieden ook de volgende voor delen:

    • Overeenstemming    Als u op een van de cellen in H10 omlaag klikt, ziet u dezelfde formule. Die consistentie kan bijdragen aan een betere nauw keurigheid.

    • Veiligheid    U kunt een onderdeel van een matrix formule in meerdere cellen niet overschrijven. Klik bijvoorbeeld op cel H11 en druk op DELETE. De uitvoer van de matrix wordt niet gewijzigd in Excel. Als u dit wilt wijzigen, moet u de cel in de linkerbovenhoek van de matrix of cel H10 selecteren.

    • Kleinere bestands grootten    U kunt vaak één matrix formule gebruiken in plaats van verschillende tussenliggende formules. In het voor beeld van de auto verkoop wordt bijvoorbeeld één matrix formule gebruikt om de resultaten in kolom E te berekenen. Als u standaard formules zoals = F10 * G10, F11 * G11, F12 * G12, enzovoort, hebt gebruikt, kunt u elf verschillende formules gebruiken om dezelfde resultaten te berekenen. Dat is geen grote deal, maar wat als het totaal van duizenden rijen niet is opgeteld? Dan kan het een groot verschil maken.

    • Rendement    Matrix functies kunnen een efficiënte manier zijn om complexe formules te maken. De matrix formule = SOM (F10: F19 * G10: G19) is hetzelfde als: = som (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Overlopen Dynamische matrix formules lopen automatisch over in het uitvoer bereik. Als uw bron gegevens zich in een Excel-tabel bevinden, worden de dynamische matrix formules automatisch aangepast wanneer u gegevens toevoegt of verwijdert.

    • #SPILL!-fout    Dynamische matrices waarin de #SPILL!-foutwordt geïntroduceerd, waarmee wordt aangegeven dat het beoogde overloop bereik om welke reden dan ook is geblokkeerd. Wanneer u het verloopt verhelpt, wordt de formule automatisch overgelopen.

Matrixconstanten zijn onderdeel van matrixformules. U maakt matrixconstanten door een lijst met items in te voeren en deze vervolgens handmatig tussen accolades ({ }) te plaatsen, bijvoorbeeld:

= {1; 2; 3; 4; 5} of = {"januari", "februari", "maart"}

Als u de items scheidt met behulp van komma's, maakt u een horizontale matrix (een rij). Als u de items scheidt met behulp van punt komma's, maakt u een verticale matrix (een kolom). Als u een tweedimensionale matrix wilt maken, beperkt u de items in elke rij met komma's en beperkt u elke rij met een punt komma.

In de volgende procedures wordt u gewaarschuwd bij het maken van horizontale, verticale en tweedimensionale constanten. We geven voor beelden weer met de functie volg orde voor het automatisch genereren van matrix constanten en hand matig ingevoerde matrix constanten.

  • Een horizontale constante maken

    Gebruik de werkmap uit de vorige voor beelden of maak een nieuwe werkmap. Selecteer een wille keurige lege cel en voer = volg orde (1, 5)in. Met de functie volg orde wordt een matrix van 1 rij met 5 opgebouwd op dezelfde wijze als = {1; 2; 3; 4; 5}. Het volgende resultaat wordt weer gegeven:

    Een horizontale matrix constante maken met = volg orde (1, 5) of = {1; 2; 3; 4; 5}

  • Een verticale constante maken

    Selecteer een lege cel met de onderliggende ruimte en voer = volg orde (5)of = {1; 2; 3; 4; 5 in}in. Het volgende resultaat wordt weer gegeven:

    Maak een verticale matrix constante met = volg orde (5) of = {1; 2; 3; 4; 5}

  • Een tweedimensionale constante maken

    Selecteer een lege cel met ruimte rechts en eronder en voer = volg orde (3, 4)in. U ziet het volgende resultaat:

    Een 3 rij maken met 4 kolom matrix constante met = volg orde (3, 4)

    U kunt ook het volgende invoeren: of = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}, maar u wilt de aandacht best Eden aan de plaats waar u punt komma's en komma waarden plaatst.

    Zoals u kunt zien, biedt de optie volg orde belang rijke voor delen boven het hand matig invoeren van de waarden van de matrix constanten. In eerste instantie wordt u tijd bespaard, maar u kunt er ook voor zorgen dat fouten niet hand matig worden ingevoerd. Het is ook gemakkelijker te lezen, vooral als de punt komma's moeilijk kunnen worden onderscheiden van de komma's als scheidings tekens.

Hier ziet u een voor beeld waarin matrix constanten worden gebruikt als onderdeel van een grotere formule. Ga in de voorbeeld werkmap naar de constante in een formule werkblad of maak een nieuw werk blad.

In cel D9 zijn de volgende ingevoerde = volg orde (1, 5, 3, 1), maar u kunt ook 3, 4, 5, 6 en 7 invoeren in de cellen A9: H9. Er is niets speciaal voor deze specifieke nummer selectie, we hebben gewoon iets anders dan 1-5 gekozen voor differentiatie.

Voer in cel E11 = som (D9: H9 * volg orde (1,5))of = som (D9: H9 * {1, 2, 3, 4, 5})in. De formules retour neren 85.

Gebruik matrix constanten in formules. In dit voor beeld hebben we = SUM (D9: H (*-volg orde (1, 5)) gebruikt.

Met de functie reeks wordt het equivalent van de matrix constante {1, 2, 3, 4, 5} samengesteld. Omdat in Excel bewerkingen worden uitgevoerd op expressies tussen haakjes, worden de volgende twee elementen die in de reeks worden afgespeeld, de celwaarden in D9: H9 en de operator voor vermenigvuldigen (*). Op dit punt vermenigvuldigt de formule de waarden in de opgeslagen matrix met de corresponderende waarden in de constante. Het is het equivalent van:

= Som (D9 * 1, E9 * 2, F9 * 3, G9 * 4, H9 * 5)of = som (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

Ten slotte worden de waarden door de functie SOM opgeteld en wordt 85 geretourneerd.

Als u wilt voor komen dat de opgeslagen matrix wordt gebruikt en de bewerking volledig in het geheugen blijft staan, kunt u deze vervangen door een andere matrix constante:

= Som (volg orde (1, 5, 3, 1) * Volg nummer (1, 5))of = som ({3; 4; 5; 6; 7} * {1; 2; 3; 4; 5})

Elementen die u kunt gebruiken in matrix constanten

  • Matrix constanten kunnen getallen, tekst, logische waarden (zoals waar en onWAAR) en fout waarden zoals #N/A. U kunt getallen gebruiken in de notaties integer, decimal en weten schappelijk. Als u tekst opneemt, moet u deze tussen aanhalings tekens plaatsen (' tekst ').

  • Matrixconstanten mogen geen andere matrices, formules of functies bevatten. Met andere woorden: ze mogen alleen tekst of getallen bevatten die van elkaar worden gescheiden met komma's of puntkomma's. Er verschijnt een waarschuwingsbericht wanneer u een formule als {1,2,A1:D4} of {1,2,SOM(Q2:Z8)} invoert. Daarnaast mogen numerieke waarden geen procenttekens, dollartekens, komma's of haakjes bevatten.

Een van de beste manieren om matrix constanten te gebruiken, is om deze te noemen. Benoemde constanten kunnen veel eenvoudiger worden gebruikt en ze kunnen een deel van de complexiteit van de matrix formules van anderen verbergen. Als u een matrix constante een naam wilt bieden en deze in een formule wilt gebruiken, gaat u als volgt te werk:

Ga naar formules _GT_ gedefinieerde namen > naam definiëren. Typ Kwartaal1 in het vak naam. Voer in het vak verwijst naar de volgende constante in (Vergeet de accolades hand matig te typen):

={"januari","februari","maart"}

Het dialoog venster ziet er nu als volgt uit:

Een benoemde matrix constante toevoegen uit formules > gedefinieerde namen > name Manager > New

Klik op OK, selecteer een rij met drie lege cellen en voer = Kwartaal1in.

Het volgende resultaat wordt weer gegeven:

Gebruik een benoemde matrix constante in een formule, bijvoorbeeld = Kwartaal1, waarbij Kwartaal1 is gedefinieerd als = {"januari", "februari", "maart"}

Als u wilt dat de resultaten verticaal overlopen in plaats van Horizon taal, kunt u =transponeren(Kwartaal1)gebruiken.

Als u een lijst met 12 maanden wilt weer geven, zoals bij het opbouwen van een financieel overzicht, kunt u één van het huidige jaar baseren met de functie reeks. Het mooie van deze functie is dat zelfs als alleen de maand wordt weer gegeven, er een geldige datum achter staat die u in andere berekeningen kunt gebruiken. U vindt deze voor beelden op de benoemde matrix constante en de werk bladen met een korte voor beeld van een gegevensset in de voorbeeld werkmap.

= TEKST (datum (jaar (vandaag ()), volg orde (1, 12), 1), "MMM")

Een combi natie van de functies tekst, datum, jaar, vandaag en volg orde gebruiken om een dynamische lijst van 12 maanden samen te stellen

Hiermee wordt de datum functie gebruikt om een datum te maken die is gebaseerd op het huidige jaar, wordt een matrix constante gemaakt van 1 tot 12 voor januari tot en met december en wordt met de functie tekst de weer gave-indeling geconverteerd naar ' MMM ' (Jan, feb, mrt, enzovoort). Als u de volledige naam van de maand wilt weer geven, zoals januari, gebruikt u ' mmmm '.

Wanneer u een benoemde constante als matrix formule gebruikt, moet u het gelijkteken invoeren, zoals in = Kwartaal1, niet alleen Kwartaal1. Als dat niet het geval is, wordt de matrix geïnterpreteerd als een teken reeks met tekst en werkt de formule niet zoals verwacht. Ten slotte kunt u combi Naties van functies, tekst en getallen gebruiken. Het hangt af van de manier waarop u wilt komen.

In de volgende voor beelden ziet u een paar van de manieren waarop u matrix constanten kunt gebruiken in matrix formules. In enkele voor beelden wordt de functie TRANSPONEREN gebruikt om rijen om te zetten in kolommen en omgekeerd.

  • Meerdere items in een matrix

    Enter = reeks (1, 12) * 2of = {1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12} * 2

    U kunt ook delen met (/), toevoegen met (+) en aftrekken met (-).

  • De items in een matrix tot de tweede macht verheffen

    Enter = volg orde (1, 12) ^ 2, of = {1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12} ^ 2

  • De vierkantswortel van gekwadrateerde items in een matrix zoeken

    Enter =wortel(volg orde (1, 12) ^ 2)of = wortel ({1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12} ^ 2)

  • Een eendimensionale rij transponeren

    Enter = transponeren (volg orde (1, 5))of = transponeren ({1; 2; 3; 4; 5})

    Hoewel u een horizontale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een kolom.

  • Een eendimensionale kolom transponeren

    Enter = transponeren (volg orde (5, 1))of = transponeren ({1; 2; 3; 4; 5})

    Hoewel u een verticale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een rij.

  • Een tweedimensionale constante transponeren

    Enter = transponeren (volg orde (3, 4))of = transponeren ({1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12})

    Met de functie TRANSPONEREN zet u elke rij om in een reeks kolommen.

In dit gedeelte worden voorbeelden gegeven van eenvoudige matrixformules.

  • Een matrix maken op basis van bestaande waarden

    In het volgende voor beeld wordt uitgelegd hoe u matrix formules kunt gebruiken om een nieuwe matrix te maken op basis van een bestaande matrix.

    Enter = volg orde (3, 6, 10, 10)of = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Zorg ervoor dat u {(accolade openen) typt voordat u 10 typt en} (accolade sluiten) nadat u 180 hebt getypt, omdat u een matrix met getallen maakt.

    Typ vervolgens = D9 #of = D9: I11 in een lege cel. Een matrix van drie x zes cellen wordt weer gegeven met dezelfde waarden die u ziet in D9: D11. Het teken # wordt de verwijsde bereik operatorgenoemd en is Excel's manier om te verwijzen naar het gehele matrix bereik, in plaats van het te typen.

    De geoverloopde bereik operator (#) gebruiken om te verwijzen naar een bestaande matrix

  • Een matrixconstante maken op basis van bestaande waarden

    U kunt de resultaten van een gemorsde matrix formule nemen en deze converteren naar de onderdelen. Selecteer cel D9 en druk op F2 om over te scha kelen naar de bewerkings modus. Druk vervolgens op F9 om de celverwijzingen te converteren naar waarden die in Excel worden omgezet in een matrix constante. Wanneer u op Enter drukt, moet de formule, = D9 #, nu = {10, 20, 30, 40, 50, 60; 70, 80, 90} zijn.

  • Tekens tellen in een celbereik

    In het volgende voor beeld ziet u hoe u het aantal tekens in een bereik van cellen kunt tellen. Dit geldt ook voor spaties.

    Het totale aantal tekens in een bereik tellen en andere matrices voor het werken met tekst reeksen

    = SUM (LEN (C9: C13))

    In dit geval retourneert de functie Len de lengte van elke teken reeks in elk van de cellen in het bereik. Met de functie som worden deze waarden bij elkaar opgeteld en wordt het resultaat weer gegeven (66). Als u het gemiddelde aantal tekens wilt opvragen, kunt u het volgende doen:

    = GEMIDDELDE (LEN (C9: C13))

  • Inhoud van de langste cel in het bereik C9: C13

    = INDEX (C9: C13, OVEREENKOMST (MAX (LEN (C9: C13)), LEN (C9: C13), 0), 1)

    Deze formule werkt alleen als een gegevens bereik één kolom met cellen bevat.

    Laten we eens kijken naar de formule, beginnend bij de binnenste elementen en naar buiten. De functie lengte geeft als resultaat de lengte van elk van de items in het celbereik D2: D6. Met de functie Max wordt de grootste waarde van de items berekend, die overeenkomt met de langste teken reeks in cel D3.

    Dit is waar alles wat ingewikkeld is. Met de functie vergelijken wordt de verschuiving (de relatieve positie) berekend van de cel die de langste teken reeks bevat. Hiervoor zijn drie argumenten nodig: een zoek waarde, een opzoek matrix en een overeenkomend type. MET de functie vergelijken wordt in de opzoek matrix gezocht naar de opgegeven zoek waarde. In dit geval is de zoek waarde de langste tekst reeks:

    MAX (LEN (C9: C13)

    en die tekenreeks bevindt zich in de volgende matrix:

    LEN (C9: C13)

    Het argument type overeenkomst in dit geval is 0. Het type overeenkomst kan een waarde van 1, 0 of-1 zijn.

    • 1-geeft als resultaat de grootste waarde die kleiner is dan of gelijk is aan de opzoek waarde.

    • 0-geeft als resultaat de eerste waarde die precies gelijk is aan de opzoek waarde

    • -1-geeft als resultaat de kleinste waarde die groter is dan of gelijk is aan de opgegeven opzoek waarde

    • Als u het type van een overeenkomst weglaat, wordt uitgegaan van 1.

    Ten slotte worden met de functie index de volgende argumenten gebruikt: een matrix en een rij-en kolom nummer in die matrix. Het celbereik C9: C13 levert de matrix, de functie vergelijken geeft het celadres aan en het laatste argument (1) geeft aan dat de waarde afkomstig is van de eerste kolom in de matrix.

    Als u de inhoud van de kleinste tekst reeks wilt ophalen, vervangt u MAX in het bovenstaande voor beeld met min.

  • De n kleinste waarden in een bereik zoeken

    In dit voor beeld ziet u hoe u de drie kleinste waarden in een bereik van cellen kunt vinden, waarbij een matrix met voorbeeld gegevens in de cellen B9: B18has is gemaakt met: = int (RANDARRAY(10, 1) * 100). Houd er rekening mee dat RANDARRAY een veranderlijke functie is, zodat u een nieuwe reeks wille keurige getallen krijgt telkens wanneer Excel wordt berekend.

    Excel-matrix formule om de ne kleinste waarde te vinden: = klein (B9 #, SEQUENCe)

    Enter = klein (B9 #, Sequence (D9), = klein (B9: b18, {1; 2; 3})

    In deze formule wordt een matrix constante gebruikt om de kleine functie drie keer te evalueren en de kleinste drie leden in de matrix in de cellen B9: b18 te retour neren, waarbij 3 een variabele waarde in cel D9 is. Als u meer waarden wilt zoeken, kunt u de waarde in de reeks functie verg Roten of meer argumenten toevoegen aan de constante. U kunt ook aanvullende functies gebruiken met deze formule, zoals som of gemiddelde. Bijvoorbeeld:

    = SOM (KLEIN (B9 #, VOLG ORDE (D9))

    = GEMIDDELDE (KLEIN (B9 #, VOLG ORDE (D9))

  • De n grootste waarden in een bereik zoeken

    Als u de grootste waarden in een bereik wilt zoeken, kunt u de kleine functie vervangen door de functie groot. Daarnaast gebruikt het volgende voor beeld de rij en indirecte functies.

    Enter = groot (B9 #, Row (indirect ("1:3")))of = groot (B9: b18, Row (indirect ("1:3")))

    Op dit moment kan het handig zijn om iets over de rij en indirecte functies te leren kennen. U kunt de functie rij gebruiken om een matrix van opeenvolgende gehele getallen te maken. Selecteer bijvoorbeeld een leeg en ENTER:

    = RIJ (1:10)

    Met de formule wordt een kolom met tien opeenvolgende gehele getallen gemaakt. Als u een mogelijk probleem wilt weer geven, voegt u een rij in boven het bereik dat de matrix formule bevat (boven rij 1). De celverwijzingen worden automatisch aangepast en de formule genereert nu gehele getallen van 2 tot 11. U kunt dit probleem oplossen door de functie INDIRECT toe te voegen aan de formule:

    = RIJ (INDIRECT ("1:10"))

    De functie INDIRECT gebruikt tekst reeksen als argumenten (Waarom wordt het bereik 1:10 tussen aanhalings tekens geplaatst). Tekst waarden worden niet automatisch aangepast wanneer u rijen invoegt of op een andere manier de matrix formule verplaatst. Hierdoor genereert de functie rij altijd de matrix met gehele getallen die u wilt. U kunt de volg orde eenvoudig gebruiken:

    = VOLG ORDE (10)

    Laten we eens kijken welke formule u eerder hebt gebruikt: = groot (B9 #, rij (INDIRECT ("1:3"))), beginnend bij de binnenste haakjes en werken naar buiten: de functie INDIRECT retourneert een reeks tekst waarden, in dit geval de waarden 1 t/m 3. Met de functie rij wordt een kolom matrix met drie cellen gegenereerd. De functie LARGE gebruikt de waarden in het cellen bereik B9: b18 en wordt drie maal geëvalueerd, één keer voor elke verwijzing die door de rij-functie wordt geretourneerd. Als u meer waarden wilt zoeken, voegt u een groter celbereik toe aan de functie INDIRECT. Ten slotte, net als bij de kleine voor beelden, kunt u deze formule gebruiken met andere functies, zoals som en gemiddelde.

  • Een bereik met foutwaarden optellen

    De functie som in Excel werkt niet wanneer u een bereik wilt optellen dat een fout waarde bevat, zoals #VALUE! of #N/A. In dit voor beeld ziet u hoe u de waarden optelt in een bereik met de naam gegevens die fouten bevatten:

    Gebruik matrices om fouten te verwerken. Bijvoorbeeld = SOM (als (ISFOUT (gegevens), "", gegevens) het bereik met de naam gegevens optelt, zelfs als het fouten bevat, zoals #VALUE! of #NA!.

  • =SOM(ALS(ISFOUT(Gegevens),"",Gegevens))

    Met deze formule wordt een nieuwe matrix gemaakt die de oorspronkelijke waarden bevat, minus eventuele foutwaarden. Met de functie ISFOUT wordt het celbereik (Gegevens) doorzocht op fouten, te beginnen bij de binnenste functies. Met de functie ALS wordt een bepaalde waarde opgehaald als wordt voldaan aan de opgegeven voorwaarde (WAAR) en een andere waarde als niet wordt voldaan aan de opgegeven voorwaarde (ONWAAR). In dit geval worden er lege tekenreeksen ("") geretourneerd voor alle foutwaarden omdat wordt voldaan aan de opgegeven voorwaarde en worden de overige waarden in het bereik (Gegevens) geretourneerd omdat deze niet aan de opgegeven voorwaarde voldoen. Dat wil zeggen: deze cellen bevatten geen foutwaarden. Vervolgens wordt met de functie SOM het totaal voor de gefilterde matrix berekend.

  • Het aantal foutwaarden in een bereik tellen

    Dit voor beeld is vergelijkbaar met de vorige formule, maar geeft als resultaat het aantal fout waarden in een bereik met de naam gegevens in plaats van deze te filteren:

    =SOM(ALS(ISFOUT(Gegevens),1,0))

    Met deze formule wordt een matrix gemaakt die de waarde 1 bevat voor de cellen met foutwaarden en de waarde 0 voor de cellen die geen fouten bevatten. U kunt de formule vereenvoudigen en hetzelfde resultaat bereiken door het derde argument bij de functie ALS te verwijderen:

    =SOM(ALS(ISFOUT(Gegevens),1))

    Als u het argument niet opgeeft, is het resultaat van de functie ALS ONWAAR als een cel geen foutwaarde bevat. U kunt de formule nog verder vereenvoudigen:

    =SOM(ALS(ISFOUT(Gegevens)*1))

    Deze versie werkt omdat WAAR*1=1 en ONWAAR*1=0.

Mogelijk moet u waarden optellen op basis van voor waarden.

U kunt matrices gebruiken om te berekenen op basis van bepaalde voor waarden. = SOM (als (Sales>0; verkoop)) worden alle waarden opgeteld die groter zijn dan 0 in een bereik genaamd verkoop.

Met deze matrix formule worden bijvoorbeeld alleen de positieve gehele getallen opgeteld in een bereik met de naam verkoop, die de cellen E9: E24 in het bovenstaande voor beeld vertegenwoordigt:

=SOM(ALS(Verkopen>0,Verkopen))

Met de functie als maakt u een matrix met de waarden positief en ONWAAR. Met de functie som worden de onjuiste waarden in hoofd zaak genegeerd omdat 0 + 0 = 0. Het celbereik dat u in deze formule gebruikt, kan bestaan uit een wille keurig aantal rijen en kolommen.

U kunt ook waarden optellen die aan meer dan één voor waarde voldoen. Met deze matrix formule worden bijvoorbeeld waarden berekend die groter zijn dan 0 en kleiner dan 2500:

= SUM ((Sales>0) * (Sales<2500) * (verkoop))

Denk eraan dat deze formule een fout oplevert als het bereik een of meer niet-numerieke cellen bevat.

U kunt ook matrix formules maken met een type of voor waarde. U kunt bijvoorbeeld waarden optellen die groter zijn dan 0 of kleiner dan 2500:

= SOM (als (Sales>0) + (Sales<2500); verkoop)

U kunt de functies en en en of niet rechtstreeks gebruiken in matrix formules, omdat deze functies één resultaat retour neren, waar of onWAAR, en matrix functies vereisen matrixen met resultaten. U kunt het probleem omzeilen door de logica te gebruiken die in de vorige formule is weer gegeven. Met andere woorden: u kunt wiskundige bewerkingen uitvoeren, zoals optellen of vermenigvuldigen met waarden die voldoen aan de voor waarde OR of AND.

In dit voorbeeld wordt gedemonstreerd hoe u nullen verwijdert uit een bereik wanneer u het gemiddelde van de waarden in dat bereik wilt berekenen. In deze formule wordt het gegevensbereik Verkopen gebruikt:

=GEMIDDELDE(ALS(Verkopen<>0,Verkopen))

Met de functie ALS wordt een matrix van waarden samengesteld die niet gelijk zijn aan 0, waarna deze waarden worden doorgegeven aan de functie GEMIDDELDE.

Deze matrix formule vergelijkt de waarden in twee bereiken met de namen MyData en som(1*(MijnGegevens en geeft als resultaat het aantal verschillen tussen de twee. Als de inhoud van de twee bereiken identiek is, geeft de formule 0 als resultaat. Als u deze formule wilt gebruiken, moeten de celbereiken even groot zijn en dezelfde dimensie hebben. Als MyData bijvoorbeeld een bereik van 3 rijen met 5 kolommen is, moet som(1*(mijngegevens ook 3 rijen met 5 kolommen hebben:

=SOM(ALS(MijnGegevens=JouwGegevens,0,1))

Met deze formule wordt een nieuwe matrix samengesteld die even groot is als de bereiken die u wilt vergelijken. Met de functie ALS wordt de matrix gevuld met de waarde 0 en de waarde 1 (0 voor niet-overeenkomende cellen en 1 voor identieke cellen). Vervolgens wordt met de functie SOM de som van de waarden in de matrix geretourneerd.

U kunt de formule als volgt vereenvoudigen:

=SOM(1*(MijnGegevens<>JouwGegevens))

Net als de formule waarmee foutwaarden in een bereik worden geteld, werkt ook deze formule omdat WAAR*1=1, en ONWAAR*1=0.

Met deze matrixformule wordt het rijnummer opgehaald van de maximumwaarde in het bereik met één kolom Gegevens:

=MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),""))

Met de functie ALS wordt een nieuwe matrix gemaakt die overeenkomt met het bereik Gegevens. Als een corresponderende cel de maximumwaarde in het bereik bevat, bevat de matrix het rijnummer van de cel. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als tweede argument en retourneert de kleinste waarde. Deze komt overeen met het rijnummer van de cel met de maximumwaarde in Gegevens. Als het bereik Gegevens identieke maximumwaarden bevat, wordt met de formule de rij met de eerste waarde opgehaald.

Als u het celadres van een maximumwaarde wilt ophalen, gebruikt u de volgende formule:

=ADRES(MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),"")),KOLOM(Gegevens))

U vindt dezelfde voor beelden in de voorbeeld werkmap op de verschillen tussen het werk blad gegevens sets.

In deze oefening wordt beschreven hoe u matrixformules in meerdere cellen en in één cel kunt gebruiken om een set verkoopcijfers te berekenen. Voor de eerste set wordt met een formule in meerdere cellen een set met subtotalen berekend. Voor de tweede set wordt met een formule in één cel een eindtotaal berekend.

  • Een matrixformule in meerdere cellen

Kopieer de hele tabel hieronder en plak deze in cel a1 in een leeg werk blad.

Verkoop cijfers Persoon

Auto Type

Nummer Verkocht

Eenheid Prijs

Totaal aantal Verkoop cijfers

Barnhill

Sedan

vijfde

33000

Coupé

Nr

37000

Ingle

Sedan

6

24000

Coupé

8

21000

Jordan

Sedan

Nr

29000

Coupé

1

31000

Pica

Sedan

9

24000

Coupé

vijfde

37000

Sanchez

Sedan

6

33000

Coupé

8

31000

Formule (eindtotaal)

Eindtotaal

'=SOM(C2:C11*D2:D11)

=SOM(C2:C11*D2:D11)

  1. Als u de totale verkoop van bijsnijders en sedans voor elke verkoper wilt zien, selecteert u de cellen E2: E11, voert u de formule = C2: C11, * D2: D11in en drukt u op CTRL + SHIFT + ENTER.

  2. Als u het eind totaal van alle verkopen wilt zien, selecteert u cel F11, voert u de formule = som (C2: C11, * D2: D11)in en drukt u op CTRL + SHIFT + ENTER.

Wanneer u op CTRL + SHIFT + ENTER drukt, wordt de formule met accolades ({}) omgeven en wordt een exemplaar van de formule ingevoegd in elke cel van het geselecteerde bereik. Dit gebeurt zeer snel, dus wat u ziet in kolom E is het totale verkoop bedrag voor elk type auto voor elke verkoper. Als u E2 selecteert en vervolgens E3, E4, enzovoort selecteert, ziet u dat dezelfde formule wordt weer gegeven: {= C2: C11, * D2: D11}.

De totalen in kolom E worden berekend met een matrixformule

  • Een matrixformule in één cel maken

Typ de volgende formule in cel D13 van de werkmap en druk op CTRL + SHIFT + ENTER:

=SOM(C2:C11*D2:D11)

In dit geval worden in Excel de waarden in de matrix (het celbereik C2 tot en met D11) vermenigvuldigd en wordt de functie somgebruikt om de totalen samen te voegen. Het resultaat is een eind totaal van $1.590.000 in de verkoop. In dit voor beeld ziet u hoe krachtig dit type formule kan zijn. Stel dat u 1.000 rijen met gegevens hebt. U kunt een deel van of alle gegevens optellen door een matrix formule in één cel te maken in plaats van de formule omlaag te slepen in de 1.000 rijen.

U ziet ook dat de formule met één cel in cel D13 volledig onafhankelijk is van de formule met meerdere cellen (de formule in de cellen E2 tot en met E11). Dit is een ander voor deel van het gebruik van matrix formules, flexibiliteit. U kunt de formules in kolom E wijzigen of die kolom helemaal verwijderen, zonder dat dit van invloed is op de formule in D13.

Matrixformules bieden bovendien de volgende voordelen:

  • Consistentie    Als u op een van de cellen vanaf E2 of lager klikt, ziet u steeds dezelfde formule. Dankzij deze consistentie kunt u een grotere nauwkeurigheid waarborgen.

  • Veiligheid    U kunt een onderdeel van een matrix formule met meerdere cellen niet overschrijven. Klik bijvoorbeeld op cel E3 en druk op Delete. U moet het hele bereik van cellen selecteren (E2 tot en met E11) en de formule voor de hele matrix wijzigen of de matrix ongewijzigd laten. Als extra veiligheids maatregel moet u op CTRL + SHIFT + ENTER drukken om eventuele wijzigingen in de formule te bevestigen.

  • Geringere bestandsgrootte    Vaak kunt u in plaats van meerdere tussenliggende formules één matrixformule gebruiken. Zo wordt in de werkmap één matrixformule gebruikt om de resultaten in kolom E te berekenen. Als u standaardformules (zoals =C2*D2, C3*D3, C4*D4) had gebruikt, had u 11 verschillende formules moeten opgeven om dezelfde resultaten te verkrijgen.

In het algemeen gebruikt matrix formules standaard syntaxis voor formules. Ze beginnen allemaal met een gelijkteken (=) en u kunt de meeste ingebouwde Excel-functies gebruiken in de matrix formules. Het belangrijkste verschil is dat wanneer u een matrix formule gebruikt, op CTRL + SHIFT + ENTER drukt om de formule in te voeren. Wanneer u dit doet, wordt de matrix formule omgeven door accolades, als u de accolades hand matig typt, wordt de formule omgezet in een teken reeks en werkt deze niet.

Matrix functies kunnen een efficiënte manier zijn om complexe formules te maken. De matrix formule = som (C2: C11, * D2: D11) is hetzelfde als: = som (C2 * D2, C3 * D3, C4 * D4, C5 * D5, C6 * D6, C7 * D7, C8 * D8, C9 * D9, C10 * D10, C11, * D11).

Belangrijk: Druk op CTRL + SHIFT + ENTER wanneer u een matrix formule wilt invoeren. Dit geldt voor formules met één cel en meerdere cellen.

Daarnaast moet u de volgende regels onthouden wanneer u met formules in meerdere cellen werkt:

  • Selecteer het bereik van cellen waarin de resultaten moeten worden opgenomen, voordat u de formule opgeeft. U hebt dit gedaan toen u de matrixformules in meerdere cellen hebt gemaakt, toen u de cellen E2 tot en met E11 selecteerde.

  • U kunt de inhoud van een afzonderlijke cel in een matrixformule niet wijzigen. Probeer dit uit door cel E3 in de werkmap te selecteren en op Delete te drukken. Er wordt een melding weergegeven met de boodschap dat u geen onderdeel van een matrix kunt wijzigen.

  • U kunt een matrixformule alleen in zijn geheel verplaatsen of verwijderen. Als u een matrixformule wilt verkleinen, moet u dus eerst de bestaande formule verwijderen en opnieuw beginnen.

  • Als u een matrix formule wilt verwijderen, selecteert u het hele formule bereik (bijvoorbeeld E2: E11) en drukt u vervolgens op Delete.

  • U kunt geen lege cellen invoegen in of verwijderen uit cellen uit een matrix formule met meerdere cellen.

Soms moet u een matrix formule uitvouwen. Selecteer de eerste cel in het bestaande matrix bereik en ga door totdat u het hele bereik hebt geselecteerd waarnaar u de formule wilt uitbreiden. Druk op F2 om de formule te bewerken en druk vervolgens op CTRL + SHIFT + ENTER om de formule te bevestigen wanneer u het formule bereik hebt aangepast. De sleutel is het hele bereik selecteren, te beginnen met de cel in de linkerbovenhoek van de matrix. De cel in de linkerbovenhoek is het item dat wordt bewerkt.

Matrixformules bieden grote voordelen, maar hebben ook een paar nadelen:

  • U kunt af en toe verg eten op CTRL + SHIFT + ENTER te drukken. Het kan gebeuren dat zelfs de ervaren Excel-gebruikers zijn. Houd deze toetscombinatie ingedrukt wanneer u een matrix formule invoert of bewerkt.

  • Andere gebruikers van uw werkmap kunnen uw formules mogelijk niet herkennen. In de praktijk worden matrix formules meestal niet uitgelegd in een werk blad. Als andere personen uw werkmappen moeten wijzigen, moet u dus geen matrix formules voor komen of ervoor zorgen dat deze personen weten wat de matrix formules zijn en hoe u deze kunt wijzigen.

  • Afhankelijk van de verwerkingssnelheid en het geheugen van uw computer kan het gebruik van grote matrixformules veel rekenkracht vergen en de prestaties nadelig beïnvloeden.

Matrixconstanten zijn onderdeel van matrixformules. U maakt matrixconstanten door een lijst met items in te voeren en deze vervolgens handmatig tussen accolades ({ }) te plaatsen, bijvoorbeeld:

={1,2,3,4,5}

U weet nu dat u op CTRL + SHIFT + ENTER moet drukken wanneer u matrix formules maakt. Omdat matrix constanten een onderdeel van matrix formules zijn, plaatst u de constanten hand matig met behulp van accolades. U gebruikt vervolgens CTRL + SHIFT + ENTER om de volledige formule in te voeren.

Als u de items met komma's van elkaar scheidt, maakt u een horizontale matrix (een rij). Als u de items met puntkomma's van elkaar scheidt, maakt u een verticale matrix (een kolom). Als u een tweedimensionale matrix wilt maken, scheidt u de items in elke rij met komma's en scheidt u de afzonderlijke rijen met puntkomma's.

Dit is een matrix in één rij: {1; 2; 3; 4}. Dit is een matrix in één kolom: {1; 2; 3; 4}. En dit is een matrix van twee rijen en vier kolommen: {1; 2; 3; 4; 5; 6; 7; 8}. In de matrix van twee rijen is de eerste rij 1, 2, 3 en 4, en de tweede rij is 5, 6, 7 en 8. De twee rijen worden tussen 4 en 5 gescheiden door één punt komma.

Net als matrixformules kunt u matrixconstanten gebruiken met de meeste ingebouwde functies van Excel. In de volgende gedeelten wordt uitgelegd hoe u de verschillende soorten constanten maakt en hoe u deze kunt gebruiken in combinatie met functies in Excel.

Aan de hand van de volgende procedures kunt u oefenen met het maken van horizontale, verticale en tweedimensionale constanten.

Een horizontale constante maken

  1. Selecteer in een leeg werk blad de cellen a1 tot en met E1.

  2. Voer in de formule balk de volgende formule in en druk op CTRL + SHIFT + ENTER:

    = {1; 2; 3; 4; 5}

    In dit geval moet u de accolades voor openen en sluiten ({}) typen en wordt de tweede set automatisch toegevoegd.

    Het volgende resultaat wordt weergegeven.

    Horizontale matrixconstante in formule

Een verticale constante maken

  1. Selecteer een kolom met vijf cellen in de werkmap.

  2. Voer in de formule balk de volgende formule in en druk op CTRL + SHIFT + ENTER:

    ={1;2;3;4;5}

    Het volgende resultaat wordt weergegeven.

    Verticale matrixconstante in een matrixformule

Een tweedimensionale constante maken

  1. Selecteer in de werkmap een blok cellen van vier kolommen breed en drie rijen hoog.

  2. Voer in de formule balk de volgende formule in en druk op CTRL + SHIFT + ENTER:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    U ziet het volgende resultaat:

    Tweedimensionale matrixconstante in matrixformule

Constanten gebruiken in formules

Hier volgt een eenvoudig voorbeeld met constanten:

  1. Maak in de voorbeeldwerkmap een nieuw werkblad.

  2. Typ 3 in cel A1 en typ vervolgens 4 in cel B1, 5 in cel C1, 6 in cel D1 en 7 in cel E1.

  3. Typ in cel A3 de volgende formule en druk op CTRL + SHIFT + ENTER:

    =SOM(A1:E1*{1,2,3,4,5})

    Zoals u ziet, wordt de constante in Excel tussen een extra stel accolades geplaatst omdat u deze als matrixformule hebt ingevoerd.

    Matrixformule met matrixconstante

    De waarde 85 wordt weergegeven in cel A3.

In het volgende gedeelte wordt uitgelegd hoe de formule werkt.

De formule die u zojuist hebt gebruikt, bestaat uit meerdere onderdelen.

Syntaxis van matrixformule met matrixformule

1. Functie

2. Opgeslagen matrix

3. Operator

4. Matrixconstante

Het laatste element tussen de haakjes is de matrix constante: {1; 2; 3; 4; 5}. Houd er rekening mee dat in Excel geen matrix constanten worden omgeven door accolades. u typt ze daad werkelijk. Vergeet ook niet dat nadat u een constante aan een matrix formule hebt toegevoegd, op CTRL + SHIFT + ENTER drukt om de formule in te voeren.

Omdat in Excel eerst de bewerkingen worden uitgevoerd voor expressies die tussen haakjes zijn geplaatst, komen vervolgens de waarden in de werkmap (A1:E1) en de operator aan de beurt. Op dit punt worden de waarden in de opgeslagen matrix door middel van de formule vermenigvuldigd met de overeenkomende waarden in de constante. Dat komt neer op het volgende:

=SOM(A1*1,B1*2,C1*3,D1*4,E1*5)

Tot slot worden de waarden met de functie SOM bij elkaar opgeteld en wordt de uitkomst 85 weergegeven in cel A3.

Als u de opgeslagen matrix niet wilt gebruiken en de bewerking helemaal in het geheugen wilt uitvoeren, vervangt u de opgeslagen matrix door een andere matrixconstante:

=SOM({3,4,5,6,7}*{1,2,3,4,5})

Als u dit wilt proberen, kopieert u de functie, selecteert u een lege cel in de werkmap, plakt u de formule in de formule balk en drukt u op CTRL + SHIFT + ENTER. U ziet hetzelfde resultaat als in de eerdere oefening die de matrix formule heeft gebruikt:

=SOM(A1:E1*{1,2,3,4,5})

Matrixconstanten kunnen getallen, tekst, logische waarden (zoals WAAR en ONWAAR) en foutwaarden (zoals #N/B) bevatten. U kunt getallen gebruiken in de volgende notaties: integer, decimaal en wetenschappelijk. Als u tekst opneemt, moet u deze tussen aanhalingstekens plaatsen (").

Matrixconstanten mogen geen andere matrices, formules of functies bevatten. Met andere woorden: ze mogen alleen tekst of getallen bevatten die van elkaar worden gescheiden met komma's of puntkomma's. Er verschijnt een waarschuwingsbericht wanneer u een formule als {1,2,A1:D4} of {1,2,SOM(Q2:Z8)} invoert. Daarnaast mogen numerieke waarden geen procenttekens, dollartekens, komma's of haakjes bevatten.

Een van de beste manieren om matrix constanten te gebruiken, is om deze een naam te gegeven. Benoemde constanten kunnen veel eenvoudiger worden gebruikt en ze kunnen een deel van de complexiteit van de matrix formules van anderen verbergen. Als u een matrix constante een naam wilt bieden en deze in een formule wilt gebruiken, gaat u als volgt te werk:

  1. Klik op het tabblad formules in de groep gedefinieerde namen op naam definiëren.
    Het dialoog venster naam definiëren wordt weer gegeven.

  2. Typ in het vak Naam de naam Kwartaal1.

  3. Typ in het vak Verwijst naar de volgende constante (denk eraan dat u de accolades handmatig moet typen):

    ={"januari","februari","maart"}

    Het dialoogvenster ziet er nu zo uit:

    Dialoogvenster Naam bewerken met formule

  4. Klik op OK en selecteer vervolgens een rij met drie lege cellen.

  5. Typ de volgende formule en druk op CTRL + SHIFT + ENTER.

    =Kwartaal1

    Het volgende resultaat wordt weergegeven.

    Benoemde matrix die als formule is ingevoerd

Wanneer u een benoemde constante gebruikt als matrixformule, moet u niet vergeten het gelijkteken op te geven. Als u dat niet doet, wordt de matrix beschouwd als tekenreeks en functioneert de formule niet zoals verwacht. Tot slot nog dit: u kunt combinaties van tekst en getallen gebruiken.

Wanneer de opgegeven matrixconstanten niet de verwachte resultaten opleveren, kan dat de volgende oorzaken hebben:

  • Sommige elementen worden mogelijk niet gescheiden door het juiste teken. Als u een komma of punt komma weglaat of als u deze op de verkeerde plek plaatst, wordt de matrix constante mogelijk niet correct gemaakt of wordt er een waarschuwing weer gegeven.

  • Mogelijk hebt u een celbereik geselecteerd dat niet overeenkomt met het aantal elementen in de constante. Als u bijvoorbeeld een kolom van zes cellen selecteert voor gebruik met een constante voor vijf cellen, verschijnt de fout #N/B in de lege cel. Omgekeerd geldt dat, als u te weinig cellen selecteert, de waarden worden weggelaten waarvoor geen corresponderende cel is.

In de volgende voor beelden ziet u een paar van de manieren waarop u matrix constanten kunt gebruiken in matrix formules. In enkele voor beelden wordt de functie TRANSPONEREN gebruikt om rijen om te zetten in kolommen en omgekeerd.

Elk item in een matrix vermenigvuldigen

  1. Maak een nieuw werkblad en selecteer een blok lege cellen met een breedte van vier kolommen en een hoogte van drie rijen.

  2. Typ de volgende formule en druk op CTRL + SHIFT + ENTER:

    = {1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12} * 2

De items in een matrix tot de tweede macht verheffen

  1. Selecteer een blok lege cellen met een breedte van vier kolommen en een hoogte van drie rijen.

  2. Typ de volgende matrix formule en druk op CTRL + SHIFT + ENTER:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    U kunt ook de volgende matrixformule invoeren, waarin de operator caret (^) wordt gebruikt:

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Een eendimensionale rij transponeren

  1. Selecteer een kolom van vijf lege cellen.

  2. Typ de volgende formule en druk op CTRL + SHIFT + ENTER:

    =TRANSPONEREN({1,2,3,4,5})

    Hoewel u een horizontale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een kolom.

Een eendimensionale kolom transponeren

  1. Selecteer een rij van vijf lege cellen.

  2. Voer de volgende formule in en druk op CTRL + SHIFT + ENTER:

    =TRANSPONEREN({1;2;3;4;5})

Hoewel u een verticale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een rij.

Een tweedimensionale constante transponeren

  1. Selecteer een blok cellen met een breedte van drie kolommen en een hoogte van vier rijen.

  2. Voer de volgende constante in en druk op CTRL + SHIFT + ENTER:

    =TRANSPONEREN({1,2,3,4;5,6,7,8;9,10,11,12})

    Met de functie TRANSPONEREN zet u elke rij om in een reeks kolommen.

In dit gedeelte worden voorbeelden gegeven van eenvoudige matrixformules.

Matrices en matrixconstanten maken op basis van bestaande waarden

In het volgende voorbeeld wordt uitgelegd hoe u matrixformules kunt gebruiken om koppelingen in te stellen tussen celbereiken in verschillende werkbladen. Daarnaast wordt gedemonstreerd hoe u op basis van dezelfde set waarden een matrixconstante kunt maken.

Een matrix maken op basis van bestaande waarden

  1. Selecteer de cellen C8:E10 op een werkblad in Excel en voer deze formule in:

    ={10,20,30;40,50,60;70,80,90}

    Zorg dat u eerst { (linkeraccolade) typt voordat u 10 typt en } (rechteraccolade) nadat u 90 typt, omdat u een matrix van getallen aan het maken bent.

  2. Druk op CTRL + SHIFT + ENTER, waarmee deze matrix met getallen wordt ingevoerd in het celbereik C8: E10 met behulp van een matrix formule. In het werk blad moet C8 tot en met E10 er als volgt uitzien:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Selecteer het celbereik C1 tot en met E3.

  4. Voer de volgende formule in de formule balk in en druk op CTRL + SHIFT + ENTER:

    =C8:E10

    In de cellen C1 tot en met E3 wordt een matrix van 3x3 met cellen weer gegeven met dezelfde waarden die u ziet in C8 tot en met E10.

Een matrixconstante maken op basis van bestaande waarden

  1. Als in cel C1: C3 is geselecteerd, drukt u op F2 om over te scha kelen naar de bewerkings modus.

  2. Druk op F9 om de celverwijzingen naar waarden te converteren. De waarden worden in Excel omgezet in een matrix constante. De formule moet nu = {10, 20, 30, 40, 50, 60; 70, 80, 90}zijn.

  3. Druk op CTRL + SHIFT + ENTER om de matrix constante in te voeren als een matrix formule.

Tekens tellen in een celbereik

In het volgende voorbeeld wordt gedemonstreerd hoe u het aantal tekens in een celbereik, spaties inbegrepen, kunt tellen.

  1. Kopieer deze hele tabel en plak deze in cel A1 in een werkblad.

    Gegevens

    Dit is een

    reeks cellen

    die samen

    één zin

    vormen.

    Totaal aantal tekens in A2:A6

    =SOM(LENGTE(A2:A6))

    Inhoud van langste cel(A2)

    =INDEX(A2:A6,VERGELIJKEN(MAX(LENGTE(A2:A6)),LENGTE(A2:A6),0),1)

  2. Selecteer cel A8 en druk op CTRL + SHIFT + ENTER om het totaal aantal tekens in de cellen a2 te zien: A6 (66).

  3. Selecteer cel A10 en druk op CTRL + SHIFT + ENTER om de inhoud te zien van het langste van de cellen A2: A6 (cel A3).

In cel A8 wordt de volgende formule gebruikt om het totaal aantal tekens (66) in de cellen A2 tot en met A6 te tellen.

=SOM(LENGTE(A2:A6))

In dit geval retourneert de functie Len de lengte van elke teken reeks in elk van de cellen in het bereik. Met de functie som worden deze waarden bij elkaar opgeteld en wordt het resultaat weer gegeven (66).

De n kleinste waarden in een bereik zoeken

In dit voorbeeld wordt gedemonstreerd hoe u de drie kleinste waarden kunt vinden in een bereik van cellen.

  1. Voer enkele wille keurige getallen in de cellen a1: A11.

  2. Selecteer de cellen C1 tot en met C3. Deze reeks cellen bevat de resultaten die door de matrix formule worden geretourneerd.

  3. Voer de volgende formule in en druk op CTRL + SHIFT + ENTER:

    = KLEINE (A1: A11; {1; 2; 3})

In deze formule wordt een matrix constante gebruikt om de kleine functie drie maal te evalueren en de kleinste (1), tweede kleinste (2) en derde kleinste (3) leden in de matrix die zich in de cellen a1: A10 bevindt, om meer waarden te zoeken, voegt u meer argumenten toe aan de grondtal. U kunt ook aanvullende functies gebruiken met deze formule, zoals som of gemiddelde. Bijvoorbeeld:

= SOM (KLEIN (A1: A10, {1; 2; 3})

= GEMIDDELDE (KLEIN (A1: A10, {1; 2; 3})

De n grootste waarden in een bereik zoeken

Als u de grootste waarden in een bereik wilt vinden, kunt u de functie KLEINSTE vervangen door de functie GROOTSTE. Daarnaast worden in het volgende voorbeeld ook de functies RIJ en INDIRECT gebruikt.

  1. Selecteer de cellen D1 tot en met D3.

  2. Voer deze formule in op de formule balk en druk op CTRL + SHIFT + ENTER:

    =GROOTSTE(A1:A10,ROW(INDIRECT("1:3")))

Op dit moment kan het handig zijn om iets over de rij en indirecte functies te leren kennen. U kunt de functie rij gebruiken om een matrix van opeenvolgende gehele getallen te maken. Selecteer bijvoorbeeld een lege kolom met tien cellen in uw Oefen werkmap, voer deze matrix formule in en druk op CTRL + SHIFT + ENTER:

=RIJ(1:10)

Met deze formule wordt een kolom met tien opeenvolgende gehele getallen gemaakt. Als u een potentieel probleem wilt zien, voegt u een rij in boven het bereik met de matrixformule (boven rij 1 dus). In Excel worden de rijverwijzingen aangepast, waarop de formule gehele getallen van 2 tot 11 genereert. U kunt dat probleem verhelpen door de functie INDIRECT aan de formule toe te voegen:

=RIJ(INDIRECT("1:10"))

In de functie INDIRECT worden tekenreeksen gebruikt als argumenten. (Daarom wordt het bereik 1:10 tussen dubbele aanhalingstekens geplaatst.) In Excel worden tekstwaarden niet aangepast wanneer u rijen invoegt of de matrixformule verplaatst. Op die manier kunt u ervoor zorgen dat de functie RIJ altijd de gewenste matrix van gehele getallen genereert.

Laten we eens kijken naar de formule die u eerder hebt gebruikt, = groot (A5: A14, Row (indirect ("1:3" ))), beginnend bij de binnenste haakjes en naar buiten werken: de functie indirect retourneert een reeks tekst waarden, in dit geval de waarden 1 t/m 3. Met de functie rij wordt de kolom matrix in drie cellen gegenereerd. De functie groot gebruikt de waarden in het celbereik A5: A14 en wordt drie maal geëvalueerd, één keer voor elke verwijzing die wordt geretourneerd door de functie Row . De waarden 3200, 2700 en 2000 worden geretourneerd naar de kolom matrix met drie cellen. Als u meer waarden wilt zoeken, voegt u een groter celbereik toe aan de functie indirect .

Net als met eerdere voor beelden kunt u deze formule gebruiken met andere functies, zoals som en gemiddelde.

De langste tekenreeks zoeken in een bereik van cellen

Ga terug naar het voor beeld van een eerdere tekst reeks, voer de volgende formule in een lege cel in en druk op CTRL + SHIFT + ENTER:

=INDEX(A2:A6,VERGELIJKEN(MAX(LENGTE(A2:A6)),LENGTE(A2:A6),0),1)

De tekst ' aantal cellen dat wordt weer gegeven.

Laten we eens kijken naar de formule, beginnend bij de binnenste elementen en naar buiten. De functie lengte geeft als resultaat de lengte van elk van de items in het celbereik a2: A6. Met de functie Max wordt de grootste waarde van de items berekend, die overeenkomt met de langste teken reeks in cel A3.

Hier wordt het wat ingewikkelder. Met de functie VERGELIJKEN wordt het verschil (de relatieve positie) bepaald ten opzichte van de cel met de langste tekstreeks. Hiervoor zijn drie argumenten nodig: een opzoekwaarde, een opzoekmatrix en een vergelijkingstype. Met de functie VERGELIJKEN wordt in de opzoekmatrix gezocht naar de opgegeven opzoekwaarde. In dit geval is de opzoekwaarde gelijk aan de langste tekenreeks:

(MAX (LEN (A2: A6))

en die tekenreeks bevindt zich in de volgende matrix:

LEN (A2: A6)

Het argument voor het vergelijkingstype is 0. Het vergelijkingstype kan bestaan uit de waarde 1, 0 of -1. Als u 1 opgeeft, wordt met VERGELIJKEN de grootste waarde opgehaald die kleiner is dan of gelijk is aan de opzoekwaarde. Als u 0 opgeeft, wordt met VERGELIJKEN de eerste waarde opgehaald die precies gelijk is aan de opzoekwaarde. Als u -1 opgeeft, wordt met VERGELIJKEN de kleinste waarde gevonden die groter is dan of gelijk is aan de opgegeven opzoekwaarde. Als u het vergelijkingstype weglaat, wordt aangenomen dat 1 wordt bedoeld.

Ten slotte worden met de functie index de volgende argumenten gebruikt: een matrix en een rij-en kolom nummer in die matrix. Het celbereik a2: A6 bevat de matrix, de functie vergelijken geeft het celadres aan en het laatste argument (1) geeft aan dat de waarde afkomstig is van de eerste kolom in de matrix.

In dit gedeelte worden voorbeelden gegeven van geavanceerde matrixformules.

Een bereik met foutwaarden optellen

De functie SOM in Excel werkt niet wanneer u een bereik probeert op te tellen dat een foutwaarde bevat, zoals #N/B. In dit voorbeeld wordt gedemonstreerd hoe u de waarden in het bereik Gegevens kunt optellen als dit bereik fouten bevat.

=SOM(ALS(ISFOUT(Gegevens),"",Gegevens))

Met deze formule wordt een nieuwe matrix gemaakt die de oorspronkelijke waarden bevat, minus eventuele foutwaarden. Met de functie ISFOUT wordt het celbereik (Gegevens) doorzocht op fouten, te beginnen bij de binnenste functies. Met de functie ALS wordt een bepaalde waarde opgehaald als wordt voldaan aan de opgegeven voorwaarde (WAAR) en een andere waarde als niet wordt voldaan aan de opgegeven voorwaarde (ONWAAR). In dit geval worden er lege tekenreeksen ("") geretourneerd voor alle foutwaarden omdat wordt voldaan aan de opgegeven voorwaarde en worden de overige waarden in het bereik (Gegevens) geretourneerd omdat deze niet aan de opgegeven voorwaarde voldoen. Dat wil zeggen: deze cellen bevatten geen foutwaarden. Vervolgens wordt met de functie SOM het totaal voor de gefilterde matrix berekend.

Het aantal foutwaarden in een bereik tellen

De formule in dit voorbeeld lijkt op die in het vorige voorbeeld, maar met deze formule wordt het aantal foutwaarden in het bereik Gegevens opgehaald. Ze worden dus niet eruit gefilterd:

=SOM(ALS(ISFOUT(Gegevens),1,0))

Met deze formule wordt een matrix gemaakt die de waarde 1 bevat voor de cellen met foutwaarden en de waarde 0 voor de cellen die geen fouten bevatten. U kunt de formule vereenvoudigen en hetzelfde resultaat bereiken door het derde argument bij de functie ALS te verwijderen:

=SOM(ALS(ISFOUT(Gegevens),1))

Als u het argument niet opgeeft, is het resultaat van de functie ALS ONWAAR als een cel geen foutwaarde bevat. U kunt de formule nog verder vereenvoudigen:

=SOM(ALS(ISFOUT(Gegevens)*1))

Deze versie werkt omdat WAAR*1=1 en ONWAAR*1=0.

Waarden optellen op basis van voorwaarden

Het is mogelijk dat u waarden bij elkaar moet optellen afhankelijk van bepaalde voorwaarden. Met deze matrixformule worden alleen de positieve gehele getallen in het bereik Verkopen opgeteld:

=SOM(ALS(Verkopen>0,Verkopen))

Met de functie ALS wordt een matrix van positieve waarden en foutieve waarden samengesteld. De functie SOM negeert in wezen de foutieve waarden omdat 0+0=0. Het celbereik dat u in deze formule gebruikt, kan bestaan uit een willekeurig aantal rijen en kolommen.

U kunt ook waarden optellen die aan meerdere voorwaarden voldoen. Met deze matrixformule worden bijvoorbeeld waarden berekend die groter zijn dan 0 en kleiner dan of gelijk aan 5:

=SOM((Verkopen>0)*(Verkopen<=5)*(Verkopen))

Denk eraan dat deze formule een fout oplevert als het bereik een of meer niet-numerieke cellen bevat.

U kunt ook matrixformules maken met een OF-voorwaarde. Zo kunt u waarden optellen die kleiner dan 5 en groter dan 15 zijn:

=SOM(ALS((Verkopen<5)+(Verkopen>15),Verkopen))

Met de functie ALS kunt u alle waarden zoeken die kleiner dan 5 en groter dan 15 zijn, en deze waarden vervolgens doorgeven naar de functie SOM.

U kunt de functies EN en OF niet rechtstreeks in matrixformules gebruiken omdat deze functies één resultaat opleveren (WAAR of ONWAAR), terwijl voor matrixfuncties juist matrices van resultaten nodig zijn. U kunt dit probleem omzeilen door de logica te gebruiken die in de voorgaande formule wordt toegepast. Met andere woorden: u voert rekenkundige bewerkingen uit op waarden die voldoen aan de voorwaarde OF of EN, zoals optellen of vermenigvuldigen.

Een gemiddelde berekenen waarbij nullen worden genegeerd

In dit voorbeeld wordt gedemonstreerd hoe u nullen verwijdert uit een bereik wanneer u het gemiddelde van de waarden in dat bereik wilt berekenen. In deze formule wordt het gegevensbereik Verkopen gebruikt:

=GEMIDDELDE(ALS(Verkopen<>0,Verkopen))

Met de functie ALS wordt een matrix van waarden samengesteld die niet gelijk zijn aan 0, waarna deze waarden worden doorgegeven aan de functie GEMIDDELDE.

Het aantal verschillen tussen twee celbereiken tellen

Met deze matrixformule worden de waarden in twee celbereiken, MijnGegevens en JouwGegevens, vergeleken en wordt het aantal verschillen tussen beide bereiken geretourneerd. Als de inhoud van beide bereiken identiek is, is het resultaat van de formule 0. U kunt deze formule alleen gebruiken als de celbereiken even groot zijn en dezelfde dimensie hebben (bijvoorbeeld als MijnGegevens een bereik heeft van 3 rijen en 5 kolommen, moet JouwGegevens ook 3 rijen en 5 kolommen bevatten):

=SOM(ALS(MijnGegevens=JouwGegevens,0,1))

Met deze formule wordt een nieuwe matrix samengesteld die even groot is als de bereiken die u wilt vergelijken. Met de functie ALS wordt de matrix gevuld met de waarde 0 en de waarde 1 (0 voor niet-overeenkomende cellen en 1 voor identieke cellen). Vervolgens wordt met de functie SOM de som van de waarden in de matrix geretourneerd.

U kunt de formule als volgt vereenvoudigen:

=SOM(1*(MijnGegevens<>JouwGegevens))

Net als de formule waarmee foutwaarden in een bereik worden geteld, werkt ook deze formule omdat WAAR*1=1, en ONWAAR*1=0.

De locatie van de maximumwaarde in een bereik zoeken

Met deze matrixformule wordt het rijnummer opgehaald van de maximumwaarde in het bereik met één kolom Gegevens:

=MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),""))

Met de functie ALS wordt een nieuwe matrix gemaakt die overeenkomt met het bereik Gegevens. Als een corresponderende cel de maximumwaarde in het bereik bevat, bevat de matrix het rijnummer van de cel. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als tweede argument en retourneert de kleinste waarde. Deze komt overeen met het rijnummer van de cel met de maximumwaarde in Gegevens. Als het bereik Gegevens identieke maximumwaarden bevat, wordt met de formule de rij met de eerste waarde opgehaald.

Als u het celadres van een maximumwaarde wilt ophalen, gebruikt u de volgende formule:

=ADRES(MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),"")),KOLOM(Gegevens))

Competentie

Onderdelen van dit artikel zijn gebaseerd op een aantal Power User-kolommen van Excel, geschreven door Colin' Wilcox, en zijn aangepast op basis van de hoofd stukken 14 en 15 van Excel 2002-formules, een boek dat is geschreven door John Walkenbach, een voormalige Excel-MVP.

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Zie ook

Dynamische matrices en gedrag van matrices op aangrenzende cellen

Dynamische matrix formules versus verouderde CSE-matrix formules

De functie FILTER

De functie RANDARRAY

De functie VOLGORDE

De functie SINGLE

De functie SORTEREN

De functie SORTBY

De functie UNIQUE

#SPILL!-fouten in Excel

Overzicht van formules

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×