Matrixformules - richtlijnen en voorbeelden

Matrixformules - richtlijnen en voorbeelden

Een matrixformule is een formule die meerdere berekeningen kan uitvoeren op een of meer items in een matrix. U kunt een matrix beschouwen als een rij of kolom met waarden, of een combinatie van rijen en kolommen met waarden. Matrixformules kunnen meerdere resultaten of één resultaat opleveren.

Vanaf de update van september 2018 voor Office 365kunt u met elke willekeurige formule die meerdere resultaten oplevert, automatisch de gewenste resultaten weergeven of over de aangrenzende cellen. Deze wijziging in gedrag gaat ook vergezeld van enkele nieuwe dynamische matrixfuncties. Dynamische matrixformules, ongeacht of ze bestaan uit bestaande functies of de functies voor dynamische matrix, hoeven alleen maar in één cel te worden ingevoerd en zijn bevestigd door op Enterte drukken. Eerdere matrixformules vragen eerst het hele uitvoerbereik te selecteren en vervolgens met CTRL + SHIFT + ENTERom de formule te bevestigen. Dit worden vaak wel CSE -formules genoemd.

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

  • Snel voorbeeldgegevens sets maken.

  • Het aantal tekens in een bereik van cellen tellen.

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

  • Elke nde waarde in een bereik van waarden optellen

In de volgende voorbeelden ziet u hoe u matrixformules in meerdere cellen en in één cel kunt maken. Waar mogelijk hebben we voorbeelden opgenomen van enkele dynamische matrixfuncties, evenals de bestaande matrixformules die u hebt ingevoerd als dynamische en verouderde matrix.

Voorbeelden downloaden

Een voorbeeldwerkmap downloaden met alle voorbeelden van de matrixformule 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

    Matrix functie voor meerdere cellen in cel H10 = F10: F19 * G10: G19 om het aantal auto's te berekenen dat wordt verkocht per prijs per stuk

  • Hier wordt de totale verkoop van verwerkings-en sedans voor elke verkoper berekend door = F10: F19 * G10: G19 in cel H10 te typen.

    Wanneer u op Enterdrukt, ziet u de resultaten naar de cellen H10: H19. Het bereik van de overvloeiing wordt gemarkeerd met een rand wanneer u een willekeurige cel in het bereik van de overvloeiing selecteert. U kunt ook merken dat de formules in de cellen H10: H19 grijs worden weergegeven. En als u de formule wilt aanpassen, moet u eerst cel H10 selecteren, waar de hoofdformule zich bevindt.

  • Matrixformule in één cel

    Matrixformule in één cel om een eindtotaal te berekenen met = som (F10: F19 * G10: G19)

    Typ of kopieer en plak = Sum (F10: F19 * G10: G19)in cel H20 van de voorbeeldwerkmap en druk vervolgens op Enter.

    In dit geval worden in Excel de waarden in de matrix (het celbereik F10 tot en met G19) vermenigvuldigd en wordt de functie Som gebruikt om de totalen samen toe te voegen. Het resultaat is een eindtotaal van $1.590.000 in verkoop.

    In dit voorbeeld ziet u hoe krachtige dit type formule kan zijn. Stel dat u 1.000 rijen met gegevens hebt. U kunt een deel van de gegevens of alle gegevens optellen door een matrixformule in één cel te maken in plaats van de formule omlaag te slepen in de rijen van 1.000. U ziet er ook voor dat de formule voor één cel in cel H20 volledig onafhankelijk is van de formule met meerdere cellen (de formule in cellen H10 via H19). Dit is een extra voordeel van het gebruik van matrixformule: 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 als u op deze manier onafhankelijke totalen wilt hebben, aangezien dit helpt bij het valideren van de nauwkeurigheid van de resultaten.

  • Dynamische matrixformules bieden bovendien de volgende voordelen:

    • Consistentie    Als u op een van de cellen in H10 omlaag klikt, wordt dezelfde formule weergegeven. Dankzij deze consistentie kunt u een grotere nauwkeurigheid waarborgen.

    • Veiligheid    U kunt geen onderdeel van een matrixformule in meerdere cellen overschrijven. Klik bijvoorbeeld op cel H11 en druk op DELETE. De uitvoer van de matrix wordt niet gewijzigd. Als u dit wilt wijzigen, selecteert u de cel linksboven in de matrix of cel H10.

    • Kleinere bestandsgrootten    U kunt vaak één matrixformule gebruiken in plaats van diverse tussenliggende formules. Het voorbeeld van de auto voor de auto gebruikt bijvoorbeeld één matrixformule om de resultaten in kolom E te berekenen. Als u standaardformules zoals = F10 * G10, F11 * G11, F12 * G12, etc. hebt gebruikt, hebt u elf verschillende formules gebruikt om dezelfde resultaten te berekenen. Dat is geen grote aanbieding, maar wat als u duizenden rijen in totaal hebt Dan kan dit een groot verschil maken.

    • Efficientië    Matrixfuncties kunnen een efficiënte manier zijn om complexe formules te maken. De matrixformule = SUM (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).

    • Loopt    Dynamische matrixformules lopen automatisch in het uitvoerbereik. Als uw brongegevens zich in een Excel-tabel bevindt, wordt de grootte van de dynamische matrixformules automatisch aangepast aan het toevoegen of verwijderen van gegevens.

    • #SPILL! fout    Dynamische matrices geïntroduceerde de fout #SPILL!Hiermee wordt aangegeven dat het gewenste bereik voor overlopen om een of andere reden wordt geblokkeerd. Wanneer u de blokkering oplost, 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 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, beperkt u de items in elke rij met komma's en beperkt u de rijen met puntkomma's.

Aan de hand van de volgende procedures kunt u oefenen met het maken van horizontale, verticale en tweedimensionale constanten. U ziet voorbeelden met de functie reeks om automatisch matrixconstanten te genereren, en u kunt ook handmatig matrixconstanten opgeven.

  • Een horizontale constante maken

    Gebruik de werkmap uit de voorgaande column of maak een nieuwe werkmap. Selecteer een lege cel en voer = volgorde (1, 5)in. Met de functie volgorde bouwt u een rij van 1 rij met 5 kolommen op dezelfde wijze als = {1, 2, 3, 4, 5}. Het volgende resultaat wordt weergegeven:

    Een horizontale matrixconstante maken met = volgorde (1, 5) of = {1, 2, 3, 4, 5}

  • Een verticale constante maken

    Selecteer een lege cel met de ruimte eronder en voer = volgorde (5)in of = {1; 2; 3; 4; 5}. Het volgende resultaat wordt weergegeven:

    Een verticale matrixconstante maken met = volgorde (5) of = {1; 2; 3; 4; 5}

  • Een tweedimensionale constante maken

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

    Een rij met vier kolommen maken op basis van een matrix met = volgorde (3, 4)

    U kunt ook de volgende formule opgeven: of = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}, maar u wilt de aandacht vestigen op de plaats waar u puntkomma's of komma's kunt plaatsen.

    Zoals u kunt zien, biedt de optie volgorde grote voordelen voor het handmatig invoeren van de matrixconstante waarden. In de eerste plaats bespaart u tijd, maar het is ook mogelijk om fouten van handmatige invoer te beperken. Het is ook makkelijker leesbaar, met name als de puntkomma's moeilijk te onderscheiden zijn van de komma scheidingstekens.

Hier ziet u een voorbeeld waarin matrixconstanten worden gebruikt als onderdeel van een grotere formule. Ga in de voorbeeldwerkmap naar de constante in een formule werkblad of maak een nieuw werkblad.

In cel D9 is de ingevoerde = volgorde (1; 5; 3; 1), maar u kunt ook 3, 4, 5, 6 en 7 in de cellen A9: H9 invoeren. Er is niets speciaal voor de selectie van bepaalde nummers, we hebben gewoon iets anders dan 1-5 gekozen voor differentiatie.

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

Gebruik matrixconstanten in formules. In dit voorbeeld hebben we de formule = SOM (D9: H (de reeks (1; 5)) gebruikt

Met de functie reeks wordt het equivalent van de matrixconstante {1, 2, 3, 4, 5} samengesteld. Omdat in Excel bewerkingen worden uitgevoerd op expressies die eerst tussen haakjes worden geplaatst, zijn de volgende twee elementen die in het spel worden afgespeeld de celwaarden in D9: H9 en de vermenigvuldigingsoperator (*). 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. als (D9 * 1, alarmnummer * 2, F9 * 3, G9 * 4, H9 * 5)of = som (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

Tot slot worden de waarden met de functie som bij elkaar opgeteld en wordt 85 geretourneerd.

Als u de opgeslagen matrix niet wilt gebruiken en de bewerking helemaal in het geheugen wilt houden, kunt u deze vervangen door een andere matrixconstante:

= Som (volgorde (1; 5; 3; 1) * reeks (1; 5))of = som ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Elementen die u kunt gebruiken in matrixconstanten

  • Matrixconstanten kunnen getallen, tekst, logische waarden (zoals waar en ONWAAR) bevatten en foutwaarden zoals #N/A. U kunt getallen gebruiken in een gehele, decimaal en wetenschappelijke notatie. Als u tekst opneemt, moet u de tekst tussen de aanhalingstekens 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.

U kunt matrixconstanten op de beste manieren een naam geven. Benoemde constanten laten zich veel makkelijker hanteren en maken de complexiteit van matrixformules tot op zekere hoogte onzichtbaar voor anderen. U kunt een matrixconstante als volgt een naam geven en in een formule gebruiken:

Ga naar formules > gedefinieerde namen > gedefinieerdenaam. Typ in het vak naam de naam Kwartaal1. 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:

Een benoemde matrixconstante toevoegen uit formules > gedefinieerde namen > naam beheerder > nieuw

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

Het volgende resultaat wordt weergegeven:

Gebruik een benoemde matrixconstante in een formule, zoals = Kwartaal1, waarbij Kwartaal1 is gedefinieerd als = {"januari", "februari", "maart"}

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

Als u een lijst wilt weergeven van 12 maanden, zoals u kunt gebruiken bij het maken van een financieel overzicht, kunt u één van het huidige jaar baseren met de functie volgorde. De handige functie voor deze functie is dat er zelfs maar de maand wordt weergegeven, maar wel een geldige datum die u kunt gebruiken in andere berekeningen. U vindt deze voorbeelden van de benoemde matrixconstante en de werkbladen voor een korte voorbeeld weergave in de voorbeeldwerkmap.

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

Een combinatie van de functies tekst, datum, jaar, vandaag en volgorde gebruiken om een dynamische lijst van 12 maanden samen te stellen

Als u een datum maakt op basis van het huidige jaar, maakt reeks een matrixconstante van 1 tot en met 12 voor januari tot en met december, en de functie tekst zet vervolgens de weergave om in ' MMM ' (Jan, feb, mrt, enzovoort). Als u de naam van een volledige maand, zoals januari, wilt weergeven, gebruikt u ' mmmm '.

Wanneer u een benoemde constante als matrixformule gebruikt, moet u het gelijkteken typen, zoals in = Kwartaal1, en niet alleen Kwartaal1. Als u dat niet doet, wordt de matrix beschouwd als tekenreeks en functioneert de formule niet zoals verwacht. U kunt ook combinaties van functies, tekst en getallen gebruiken. Dat is alles, afhankelijk van de manier waarop u uw advertenties wilt bereiken.

Aan de hand van de volgende voorbeelden worden een paar manieren gedemonstreerd waarop u matrixconstanten kunt toepassen in matrixformules. Voorbeelden van het gebruik van de functie TRANSPONEREN om rijen naar kolommen te converteren en omgekeerd.

  • Meerdere items in een matrix

    Enter = volgorde (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 = volgorde (1, 12) ^ 2of = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} ^ 2

  • De vierkantswortel van kwadraat items in een matrix zoeken

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

  • Een eendimensionale rij transponeren

    Typ = transponeren (volgorde (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

    Typ = transponeren (volgorde (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 (volgorde (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 voorbeeld wordt uitgelegd hoe u met matrixformules een nieuwe matrix maakt op basis van een bestaande matrix.

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

    Zorg dat u eerst {(accolade openen) typt voordat u 10 typt en} (rechter accolade) nadat u 180 hebt getypt, omdat u een matrix van getallen aan het maken bent.

    Voer vervolgens = D9 #in of = D9: I11 in een lege cel. Er verschijnt een matrix van drie x zes cellen met dezelfde waarden die worden weergegeven in D9: D11. Het teken # wordt de verlopende bereikoperatorgenoemd, en de Excel'sste manier om te verwijzen naar het hele matrixbereik, in plaats van dat u dit hoeft te typen.

    De verlopende bereikoperator (#) gebruiken om te verwijzen naar een bestaande matrix

  • Een matrixconstante maken op basis van bestaande waarden

    U kunt de resultaten van een gemorseerde matrixformule omzetten en converteren naar de onderdelen. Selecteer cel D9 en druk op F2 om de bewerkingsmodus te activeren. Druk vervolgens op F9 om de celverwijzingen te converteren naar waarden die in Excel vervolgens worden omgezet in een matrixconstante. Wanneer u op Enterdrukt, wordt de formule = D9 #, nu = {10, 20, 30, 40, 50, 60; 70, 80, 90}.

  • Tekens tellen in een celbereik

    In het volgende voorbeeld ziet u hoe u het aantal tekens in een cellenbereik kunt tellen. Dit omvat spaties.

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

    = SOM (LENGTE (C9: C13))

    In dit geval wordt met de functie Len de lengte van elke tekenreeks in elk van de cellen in het bereik als resultaat gegeven. Met de functie som worden vervolgens deze waarden samen opgeteld en wordt het resultaat weergegeven (66). Als u het gemiddelde aantal tekens wilt berekenen, kunt u het volgende doen:

    = AVERAGE (C9: C13))

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

    = INDEX (C9: C13, VERGELIJKEN (MAX (LENGTE (C9: C13)), LENGTE (C9: C13), 0), 1)

    Deze formule werkt alleen wanneer een gegevensbereik één kolom met cellen bevat.

    Laten we de formule eens bekijken, te beginnen met de binnenste elementen. De functie lengte retourneert de lengte van elk van de items in het cellenbereik D2: D6. Met de functie Max wordt de grootste waarde tussen de items berekend, wat overeenkomt met de langste tekenreeks die zich in cel D3 bevindt.

    Hier wordt het wat ingewikkelder. Met de functie vergelijken wordt de verschuiving (de relatieve positie) berekend van de cel die de langste tekenreeks bevat. 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 (LENGTE (C9: C13)

    en die tekenreeks bevindt zich in de volgende matrix:

    LEN (C9: C13)

    De argumenten type overeenkomst in dit geval is 0. Het type overeenkomst kan de waarde 1, 0 of-1 zijn.

    • 1-retourneert de grootste waarde die kleiner is dan of gelijk is aan de opzoek val

    • 0-retourneert de eerste waarde exact gelijk aan de opzoekwaarde

    • -1-retourneert de kleinste waarde 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 de volgende argumenten gebruikt voor de functie index : een matrix, en ook een rij-en kolomnummer in die matrix. Het cellenbereik C9: C13 levert de matrix, de functie vergelijken levert het celadres en geeft het laatste argument (1) als resultaat dat de waarde afkomstig is van de eerste kolom in de matrix.

    Als u de inhoud van de kleinste tekenreeks wilt weergeven, vervangt u de waarde in het bovenstaande voorbeeld met min.

  • De n kleinste waarden in een bereik zoeken

    In dit voorbeeld ziet u hoe u de drie kleinste waarden kunt vinden in een bereik van cellen, waarbij een matrix met voorbeeldgegevens in de cellen B9: B18has is gemaakt met: = int (de rand array(10, 1) * 100). Houd er rekening mee dat de rand array een vluchtigste functie is, dus u krijgt een nieuwe set willekeurige getallen telkens wanneer Excel wordt berekend.

    Excel-matrixformule voor het vinden van de nde kleinste waarde: = klein (B9 #, SEQUENTIE (D9))

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

    In deze formule wordt een matrixconstante gebruikt om de kleine functie drie keer te evalueren en de kleinste drie leden in de matrix te retourneren die deel uitmaken van de cellen B9: b18, waarbij 3 een variabele waarde in cel D9 is. Als u meer waarden wilt zoeken, kunt u de waarde van de functie volgorde verhogen of meer argumenten aan de constante toevoegen. U kunt deze formule ook combineren met aanvullende functies, zoals SOM of GEMIDDELDE. Voorbeeld:

    = SOM (KLEINSTE (B9 #, VOLGORDE (D9))

    = GEMIDDELDE (KLEIN (B9 #, VOLGORDE (D9))

  • De n grootste waarden in een bereik zoeken

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

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

    Op dit punt kan het nuttig zijn iets meer te weten over de functies RIJ en INDIRECT. Met de functie RIJ kunt u een matrix van opeenvolgende gehele getallen samenstellen. Selecteer bijvoorbeeld een lege en voer de volgende handelingen uit:

    =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). De celverwijzingen worden automatisch aangepast, en de formule genereert nu gehele getallen van 2 tot en met 11. U kunt dat probleem verhelpen door de functie INDIRECT aan de formule toe te voegen:

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

    De functie INDIRECT gebruikt tekstreeksen als argumenten (dat wil zeggen het bereik 1:10 tussen aanhalingstekens). Tekstwaarden worden niet automatisch aangepast wanneer u rijen invoegt of op een andere manier de matrixformule verplaatst. Hierdoor wordt met de functie rij altijd de gewenste matrix van gehele getallen gegenereerd. U kunt de volgorde van net zo eenvoudig gebruiken:

    = VOLGORDE (10)

    Laten we eens kijken naar de formule die u eerder hebt gebruikt, bijvoorbeeld LARGE (B9 #, rij (INDIRECT ("1:3")))-vanaf de binnenste haakjes lopen en naar buiten werken: de functie INDIRECT retourneert een set tekstwaarden, in dit geval de waarden 1 tot en met 3. Met de functie rij wordt op de beurt een kolom matrix van drie cellen gegenereerd. Bij de functie grootste worden de waarden in de cellenbereik B9: b18 gebruikt en wordt de waarde drie keer geëvalueerd voor elke verwijzing die wordt geretourneerd door de functie ROW. Als u meer waarden wilt zoeken, voegt u een groter celbereik toe aan de functie INDIRECT. Ten slotte kunt u met de kleine voorbeelden deze formule gebruiken met andere functies, zoals som en gemiddelde.

  • Een bereik met foutwaarden optellen

    De functie som in Excel werkt niet wanneer u probeert een bereik op te tellen dat een foutwaarde bevat, zoals #VALUE! of #N/A. In dit voorbeeld ziet u hoe u de waarden optelt in een bereik met de naamgegevens die fouten bevatten:

    Gebruik matrices om met fouten af te handelen. Met = som (als (ISFOUT (gegevens), "", gegevens) wordt het bereik met de naamgegevens ook opgeteld indien het fouten bevat, zoals #VALUE! of #NA!.

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

    Met de formule wordt een nieuwe matrix gemaakt met de oorspronkelijke waarden min eventuele foutwaarden. Als u begint met de binnenste functies en naar buiten werken, zoekt de functie ISFOUT het celbereik (gegevens) voor fouten. De functie als retourneert een bepaalde waarde als de opgegeven voorwaarde waar is, resulteert in waar en een andere waarde als deze ONWAAR oplevert. In dit geval levert de functie lege tekenreeksen ("") voor alle foutwaarden op omdat deze resulteren in waar, en het resultaat van de resterende waarden uit het bereik (gegevens), wat betekent dat ze geen foutwaarden bevatten. Met de functie som wordt vervolgens het totaal voor de gefilterde matrix berekend.

  • Het aantal foutwaarden in een bereik tellen

    Dit voorbeeld is vergelijkbaar met de vorige formule maar het aantal foutwaarden in een bereik met de naamgegevens in plaats van ze 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.

Het is mogelijk dat u waarden bij elkaar moet optellen afhankelijk van bepaalde voorwaarden.

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

Met deze matrixformule wordt bijvoorbeeld alleen de positieve gehele getallen in het bereik verkoop opgeteld, namelijk de cellen alarmnummer: E24 in het voorbeeld hierboven:

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

Met de functie als wordt een matrix gemaakt met de waarden positief en ONWAAR. 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 die groter zijn dan 0 en kleiner dan 2500 , berekend:

= SOM ((verkoop>0) * (verkoop<2500) * (verkoop))

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. U kunt bijvoorbeeld waarden optellen die groter dan 0 of kleiner zijn dan 2500:

= SOM (als ((verkoop>0) + (verkoop<2500), verkopen))

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 voert u mathematische bewerkingen uit, zoals optellen of vermenigvuldigen met waarden die voldoen aan de of of en voorwaarde.

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.

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. Als u deze formule wilt gebruiken, moeten de celbereiken even groot zijn en dezelfde dimensie hebben. Als MyData bijvoorbeeld een bereik is van 3 rijen met 5 kolommen, moet gegevens jouw gegevens ook 3 rijen bij 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 * (mijn MyData<>gegevens jouw gegevens))

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 met de naamgegevens. Als een corresponderende cel de maximumwaarde in het bereik bevat, bevat de matrix het rijnummer. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als het tweede argument en retourneert de kleinste waarde die overeenkomt met het rijnummer van de maximumwaarde in gegevens. Als het bereik met de naamgegevens identieke waarden bevat, retourneert de formule de rij van de eerste waarde.

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 soortgelijke voorbeelden in de voorbeeldwerkmap op het werkblad verschillen tussen datasets .

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 onderstaande tabel en plak deze in cel a1 van een leeg werkblad.

Verkoopcijfers Persoon

Auto Type

Cijfer Verkocht

Stuk Prijs

Totaal aantal Verkoopcijfers

Barnhill

Sedan

5

33000

Coupé

4

37000

Ingle

Sedan

6

24000

Coupé

8

21000

Jordan

Sedan

3

29000

Coupé

1

31000

Pica

Sedan

9

24000

Coupé

5

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 ontkoppelingen 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. Voor het eindtotaal van de gehele verkoop selecteert u cel F11, voert u de formule = som (C2: C11 * D2: D11)in en drukt u op CTRL + SHIFT + ENTER.

Als u op CTRL + SHIFT + ENTERdrukt, wordt de formule in Excel tussen accolades ({}) geplaatst en wordt een exemplaar van de formule in elke cel van het geselecteerde bereik ingevoegd. Dit gaat heel snel, dus in kolom E ziet u het totale verkoopbedrag per type auto per verkoper. Als u E2 selecteert en vervolgens E3, E4, enzovoort, zie u dat steeds dezelfde formule wordt weergegeven: {=C2:C11*D2:D11}

De totalen in kolom E worden berekend met een matrixformule

  • Een matrixformule in één cel maken

Typ in cel D13 van de werkmap de volgende formule 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 toe te voegen. Het resultaat is een eindtotaal van $1.590.000 in verkoop. In dit voorbeeld ziet u hoe krachtige dit type formule kan zijn. Stel dat u 1.000 rijen met gegevens hebt. U kunt een deel van de gegevens of alle gegevens optellen door een matrixformule in één cel te maken in plaats van de formule omlaag te slepen in de rijen van 1.000.

U ziet er ook voor dat de formule voor één cel in cel D13 volledig onafhankelijk is van de formule met meerdere cellen (de formule in cel E2 tot en met E11). Dit is een extra voordeel van het gebruik van matrixformule: 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 de onderdelen van een matrixformule in meerdere cellen niet overschrijven. Klik bijvoorbeeld op cel E3 en druk op Delete. U moet het hele celbereik (E2 tot en met E11) selecteren en de formule voor de hele matrix wijzigen of de matrix zo laten. Als extra veiligheidsmaatregel drukt u op CTRL + SHIFT + ENTER om elke wijziging in de formule te bevestigen.

  • Kleinere bestandsgrootten    U kunt vaak één matrixformule gebruiken in plaats van diverse tussenliggende formules. De werkmap gebruikt bijvoorbeeld één matrixformule om de resultaten in kolom E te berekenen. Als u standaardformules hebt gebruikt (bijvoorbeeld = C2 * D2, C3 * D3, C4 * D4...), hebt u elf verschillende formules gebruikt om dezelfde resultaten te berekenen.

In het algemeen kunt u de syntaxis van matrixformules gebruiken. Allemaal beginnen met een gelijkteken (=) en u kunt de meeste ingebouwde Excel-functies gebruiken in de matrixformules. Het belangrijkste verschil is dat wanneer u een matrixformule gebruikt, op CTRL + SHIFT + ENTER om de formule in te voeren. Wanneer u dit doet, wordt de matrixformule met accolades weergegeven als u de accolades handmatig moet typen, wordt de formule geconverteerd naar een tekstreeks en werkt dit niet.

Matrixfuncties kunnen een efficiënte manier zijn om complexe formules te maken. De matrixformule =SOM(C2:C11*D2:D11) is dezelfde als deze: =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 matrixformule wilt invoeren. Dit geldt voor zowel formules in één cel als formules in 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 matrixformule wilt verwijderen, selecteert u het hele formule bereik (bijvoorbeeld E2: E11) en drukt u vervolgens op Delete.

  • U kunt geen lege cellen in een matrixformule in meerdere cellen invoegen of verwijderen.

Het kan voorkomen dat u een matrixformule moet uitbreiden. Selecteer de eerste cel in het bestaande matrixbereik 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, beginnend met de cel linksboven in de matrix. De cel linksboven is de cel die wordt bewerkt.

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

  • Het kan soms voorkomen dat u op CTRL + SHIFT + ENTERdrukt. Dit kan zelfs ervaren Excel-gebruikers overkomen. Denk eraan dat u deze toetscombinatie moet gebruiken wanneer u een matrixformule wilt invoeren of bewerken.

  • Het is mogelijk dat andere gebruikers van de werkmap uw formules niet begrijpen. In de praktijk is er meestal weinig uitleg over matrixformules beschikbaar in een werkblad. Als andere personen uw werkmappen willen wijzigen, dient u daarom geen matrixformules te vermijden of ervoor te zorgen dat deze personen de matrixformules kennen en hoe ze zo nodig kunnen 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 matrixformules maakt. Aangezien matrixconstanten onderdeel zijn van matrixformules, plaatst u de constanten handmatig tussen accolades door deze te typen. Vervolgens gebruikt u CTRL + SHIFT + ENTER om de gehele 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 met twee rijen, staat in de eerste rij 1, 2, 3 en 4 en in de tweede rij 5, 6, 7 en 8. Eén puntkomma, tussen 4 en 5, scheidt de twee rijen.

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 werkblad de cellen a1 tot en met E1.

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

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

    In dit geval moet u eerst de accolades ({}) typen, waarna de tweede set voor u wordt toegevoegd.

    De volgende informatie wordt weergegeven.

    Horizontale matrixconstante in formule

Een verticale constante maken

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

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

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

    De volgende informatie 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 formulebalk 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 matrixconstante: {1,2,3,4,5}. Houd er rekening mee dat in Excel niet automatisch accolades worden geplaatst rond matrixconstanten, deze moet u zelf typen. Wanneer u een constante hebt toegevoegd aan een matrixformule, moet u ook op CTRL + SHIFT + ENTER drukken 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 doen, kopieert u de functie, selecteert u een lege cel in de werkmap, plakt u de formule in de formulebalk en drukt u op CTRL + SHIFT + ENTER. U ziet hetzelfde resultaat als bij de eerdere oefening met de matrixformule:

=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 matrixconstanten te gebruiken is een naam geven. Benoemde constanten laten zich veel makkelijker hanteren en maken de complexiteit van matrixformules tot op zekere hoogte onzichtbaar voor anderen. U kunt een matrixconstante als volgt een naam geven en in een formule gebruiken:

  1. Ga naar het tabblad Formules en klik in de groep Gedefinieerde namen op Naam bepalen.
    Het dialoogvenster naam bepalen wordt weergegeven.

  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

    De volgende informatie 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:

  • Mogelijk worden niet alle elementen met het juiste teken van elkaar gescheiden. Als u een komma of puntkomma weglaat, of als u er een wilt toevoegen, is de matrixconstante mogelijk niet correct gemaakt, of ziet u mogelijk een waarschuwingsbericht.

  • 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.

Aan de hand van de volgende voorbeelden worden een paar manieren gedemonstreerd waarop u matrixconstanten kunt toepassen in matrixformules. Voorbeelden van het gebruik van de functie TRANSPONEREN om rijen naar kolommen te converteren 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 matrixformule 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 cellenbereik C8: E10 met behulp van een matrixformule. Op uw werkblad moeten C8 tot en met E10 er als volgt uitzien:

    10

    20

    dertig

    40

    50

    60

    70

    80

    90

  3. Selecteer het celbereik C1 tot en met E3.

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

    =C8:E10

    Een 3x3 matrix met cellen wordt weergegeven in de cellen C1 tot en met E3 met dezelfde waarden die u in C8 tot en met E10 ziet.

Een matrixconstante maken op basis van bestaande waarden

  1. Wanneer u de cellen C1: C3 hebt geselecteerd, drukt u op F2 om de bewerkingsmodus te activeren. 

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

  3. Druk op CTRL + SHIFT + ENTER om de matrixconstante als matrixformule in te voeren.

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 vervolgens op CTRL + SHIFT + ENTER om het totaal aantal tekens in de cellen A2: A6 (66) weer te geven.

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

De volgende formule wordt gebruikt in cel A8 het totale aantal tekens (66) in de cellen A2 tot en met A6 geteld.

=SOM(LENGTE(A2:A6))

In dit geval wordt met de functie LENGTE de lengte van elke tekenreeks in de afzonderlijke cellen in het bereik opgehaald. Met de functie som worden vervolgens deze waarden samen opgeteld en wordt het resultaat weergegeven (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 willekeurige getallen in de cellen a1: A11.

  2. Selecteer de cellen C1 tot en met C3. In deze cellen worden de resultaten opgeslagen die met de matrixformule worden opgehaald.

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

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

In deze formule wordt een matrixconstante gebruikt om de kleine functie drie keer te evalueren en de kleinste (1), de tweede kleinste (2) en de derde kleinste (3) leden weer te geven in de matrix in de cellen a1: A10, zodat u meer argumenten toevoegt aan de waarde voortdurend. U kunt deze formule ook combineren met aanvullende functies, zoals SOM of GEMIDDELDE. Voorbeeld:

= 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 formulebalk en druk op CTRL + SHIFT + ENTER:

    = GROOT (A1: A10; RIJ (INDIRECT ("1:3")))

Op dit punt kan het nuttig zijn iets meer te weten over de functies RIJ en INDIRECT. Met de functie RIJ kunt u een matrix van opeenvolgende gehele getallen samenstellen. Selecteer bijvoorbeeld een lege kolom van tien cellen in uw oefenwerkmap, voer deze matrixformule 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"))

De functie indirect gebruikt tekstreeksen als argumenten (Daarom is het bereik 1:10 tussen dubbele aanhalingstekens). Tekstwaarden worden niet automatisch aangepast wanneer u rijen invoegt of op een andere manier de matrixformule verplaatst. Hierdoor wordt met de functie rij altijd de gewenste matrix van gehele getallen gegenereerd.

Laten we eens kijken naar de formule die u eerder hebt gebruikt, namelijk large (A5: A14, rij (indirect ("1:3"))) — beginnend met de binnenste haakjes en naar buiten werken: de functie indirect retourneert een set tekstwaarden, in dit geval de waarden 1 tot en met 3. Met de functie rij wordt een cel in de kolom kolommen van drie cellen gegenereerd. De functie grootste gebruikt de waarden in het cellenbereik A5: A14 en de waarde wordt drie keer geëvalueerd voor elke verwijzing die wordt geretourneerd door de functie Row . De waarden 3200, 2700 en 2000 worden weergegeven in de kolom matrix met drie cellen. Als u meer waarden wilt zoeken, voegt u een groter celbereik toe aan de functie indirect .

Net zoals bij eerdere voorbeelden 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 voorbeeld van de eerdere tekstreeks, typ de volgende formule in een lege cel en druk op CTRL + SHIFT + ENTER:

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

De tekst "groep cellen die" wordt weergegeven.

Laten we de formule eens bekijken, te beginnen met de binnenste elementen. De functie lengte retourneert de lengte van elk van de items in het cellenbereik a2: A6. Met de functie Max wordt de grootste waarde tussen de items berekend, wat overeenkomt met de langste tekenreeks die zich in cel A3 bevindt.

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 (LENGTE (A2: A6))

en die tekenreeks bevindt zich in de volgende matrix:

LENGTE (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.

Bij de functie INDEX worden de volgende argumenten opgegeven: een matrix, en een rij- en kolomnummer binnen die matrix. Het cellenbereik a2: A6 biedt de matrix, de functie vergelijken bevat het celadres 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 de formule wordt een nieuwe matrix gemaakt met de oorspronkelijke waarden min eventuele foutwaarden. Als u begint met de binnenste functies en naar buiten werken, zoekt de functie ISFOUT het celbereik (gegevens) voor fouten. De functie als retourneert een bepaalde waarde als de opgegeven voorwaarde waar is, resulteert in waar en een andere waarde als deze ONWAAR oplevert. In dit geval levert de functie lege tekenreeksen ("") voor alle foutwaarden op omdat deze resulteren in waar, en het resultaat van de resterende waarden uit het bereik (gegevens), wat betekent dat ze geen foutwaarden bevatten. Met de functie som wordt vervolgens 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 * (mijn MyData<>gegevens jouw gegevens))

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 met de naamgegevens. Als een corresponderende cel de maximumwaarde in het bereik bevat, bevat de matrix het rijnummer. Anders bevat de matrix een lege tekenreeks (""). De functie min gebruikt de nieuwe matrix als het tweede argument en retourneert de kleinste waarde die overeenkomt met het rijnummer van de maximumwaarde in gegevens. Als het bereik met de naamgegevens identieke waarden bevat, retourneert de formule de rij van de eerste waarde.

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

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

Ter

Gedeelten van dit artikel zijn gebaseerd op een reeks columns van Excel voor Power gebruikers die zijn geschreven door Colin' Wilcox, en zijn afgestemd op basis van de hoofdstukken 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 matrixformules versus oude CSE-matrixformules

De functie FILTER

De functie ASELECT.MATRIX

De functie REEKS

De functie SORTEREN

De functie SORTEREN.OP

De functie UNIEK

#SPILL! -fouten in Excel

Impliciete intersectie operator: @

Overzicht van formules

Opmerking:  Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor je is. Wil je ons laten weten of deze informatie nuttig is? Hier is het Engelstalige artikel ter referentie.

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-ondersteuningsagenten.

×