Excel-matrixformules zijn super krachtig. U kunt duizenden formules vervangen door een enkele formule zodra u de Ctrl + Shift + Enter-truc leert. Tegenwoordig voert een enkele matrixformule 86.000 berekeningen uit.
Triskaidekafobie is de angst van vrijdag de 13e. Dit onderwerp zal niets genezen, maar het zal je een absoluut geweldige formule laten zien die 110.268 formules vervangt. In het echte leven hoef ik nooit te tellen hoeveel Friday the 13ths er in mijn leven hebben plaatsgevonden, maar de kracht en de schoonheid van deze formule illustreert de kracht van Excel.
Stel dat je een vriend hebt die rond vrijdag de 13e bijgelovig is. U wilt illustreren hoeveel Friday the 13ths uw vriend heeft meegemaakt.
Stel het onderstaande eenvoudige werkblad op, met geboortedatum in B1 en =TODAY()in B2. Vervolgens evalueert een wilde formule in B6 elke dag dat je vriend nog leefde om erachter te komen hoeveel van die dagen vrijdag waren en op de 13e van de maand vielen. Voor mij is het nummer 86. Niets om bang voor te zijn.
Trouwens, 17-2-1965 is echt mijn verjaardag. Maar ik wil niet dat je me een verjaardagskaart stuurt. In plaats daarvan wil ik dat je me voor mijn verjaardag laat uitleggen hoe die geweldige formule werkt, stap voor stap.
Heeft u ooit de INDIRECTE functie gebruikt? Als u erom vraagt =INDIRECT("C3"), gaat Excel naar C3 en retourneert alles wat zich in die cel bevindt. Maar INDIRECT is krachtiger als u de celverwijzing on-the-fly berekent. Je zou een prijzenrad kunnen opzetten waarbij iemand een letter tussen A en C kiest en vervolgens een cijfer tussen 1 en 3. Wanneer je de twee antwoorden samenvoegt, heb je een celadres, en alles wat zich op dat celadres bevindt, is de prijs . Het lijkt erop dat ik een fotoboek heb gewonnen in plaats van het resortverblijf.
Weet u hoe Excel datums opslaat? Wanneer Excel u 17-2-1965 laat zien, slaat het 23790 op in de cel, omdat 17-2-1965 de 23790e dag van de 20e eeuw was. De kern van de formule is een aaneenschakeling die de startdatum en een dubbele punt en de einddatum verbindt. Excel gebruikt niet de opgemaakte datum. In plaats daarvan gebruikt het het serienummer achter de schermen. Dus B3&":"&B4wordt 23790: 42167. Geloof het of niet, dat is een geldige celverwijzing. Als je alles in rij 3 tot en met 5 wilt optellen, zou je kunnen gebruiken =SUM(3:5). Dus als u 23790: 42167 doorgeeft aan de INDIRECTE functie, wijst deze naar alle rijen.
Het volgende dat de killer-formule doet, is vragen naar de ROW(23790:42167). Normaal passeer je een enkele cel: =ROW(D17)is 17. Maar in dit geval passeer je duizenden cellen. Wanneer u ROW(23790:42167)de formule vraagt en deze voltooit met Ctrl + Shift + Enter, retourneert Excel feitelijk elk getal van 23790, 23791, 23792, enzovoort tot 42167.
Deze stap is de geweldige stap. In deze stap gaan we van twee cijfers en "pop-out" een reeks van 18378 nummers. Nu moeten we iets doen met die reeks antwoorden. Cel B9 van de vorige figuur telt hoeveel antwoorden we krijgen, wat saai is, maar het bewijst dat ROW(23790:42167)18378 antwoorden worden geretourneerd.
Laten we de oorspronkelijke vraag drastisch vereenvoudigen, zodat u kunt zien wat er gebeurt. In dit geval vinden we het aantal vrijdagen in juli 2015. De onderstaande formule in B7 geeft het juiste antwoord in B6.
De kern van de formule is ROW(INDIRECT(B3&":"&B4)). Hiermee worden de 31 datums in juli 2015 geretourneerd. Maar de formule geeft die 31 datums vervolgens door aan de WEEKDAY(,2)functie. Deze functie retourneert een 1 voor maandag, 5 voor vrijdag, enzovoort. De grote vraag is dus hoeveel van die 31 datums een 5 retourneren wanneer ze aan de WEEKDAY(,2)functie worden doorgegeven .
U kunt de formule in slow motion zien berekenen met de opdracht Formule evalueren op het tabblad Formule van het lint.
Dit is nadat INDIRECT de datums heeft geconverteerd naar een rijverwijzing.
In de volgende stap staat Excel op het punt 31 nummers door te geven aan de WEEKDAY-functie. Nu, in de killer-formule, zou het 18.378 nummers doorgeven in plaats van 31.
Hier zijn de resultaten van de 31 WEEKDAY-functies. Onthoud dat we willen tellen hoeveel er 5 zijn.
Als u controleert of de vorige array 5 is, wordt een hele reeks True / False-waarden geretourneerd. Er zijn 5 True-waarden, één voor elke vrijdag.
Ik kan je niet laten zien wat er daarna gebeurt, maar ik kan het wel uitleggen. Excel kan een aantal True en False-waarden niet SOMMEN. Het is tegen de regels. Maar als u die True en False-waarden met 1 vermenigvuldigt of als u de dubbel-negatieve of de N () -functie gebruikt, converteert u de True-waarden naar 1 en de False-waarden naar 0. Stuur die naar SUM of SUMPRODUCT, en u zult de telling van de True-waarden ophalen.
Hier is een soortgelijk voorbeeld om te tellen hoeveel maanden een dag 13 bevatten. Dit is triviaal om over na te denken: elke maand heeft een 13e, dus het antwoord voor een heel jaar is beter 12. Excel doet de wiskunde, genereert 365 datums, stuurt ze allemaal naar de functie DAY () en zoekt uit hoeveel op de 13e van de maand. Het antwoord is, zoals verwacht, 12.
De volgende afbeelding is een werkblad dat alle logica bevat van de one killer-formule die aan het begin van dit onderwerp wordt getoond. Ik heb een rij gemaakt voor elke dag dat ik leef. In kolom B krijg ik de DAG () van die datum. In kolom C krijg ik de WEEKDAY () van de datum. Is B in kolom D gelijk aan 13? Is in kolom E C = 5? Ik vermenigvuldig dan D * E om de Waar / Onwaar naar 1/0 te converteren.
Ik heb veel van de rijen verstopt, maar ik laat je drie willekeurige dagen in het midden zien die toevallig zowel een vrijdag als de 13e zijn.
Het totaal in F18381 is dezelfde 86 die mijn oorspronkelijke formule heeft geretourneerd. Een geweldig teken. Maar dit werkblad heeft 110.268 formules. Mijn originele killer-formule bevat alle logica van deze 110.268 formules in één formule.
Wacht. Ik wil duidelijk maken. Er is niets magisch in de originele formule dat slim wordt en de logica verkort. Die originele formule doet echt 110.268 stappen, waarschijnlijk zelfs meer, omdat de originele formule de ROW () -matrix twee keer moet berekenen.
Zoek een manier om dit ROW(INDIRECT(Date:Date))in het echt te gebruiken en stuur het naar mij in een e-mail (pub at dot com). Ik stuur een prijs naar de eerste 100 mensen die moeten antwoorden. Waarschijnlijk geen resortverblijf. Waarschijnlijker een Big Mac. Maar zo gaat het met prijzen. Veel Big Macs en niet veel resortverblijven.
Ik zag deze formule voor het eerst gepost op het prikbord in 2003 door Ekim. Er werd krediet gegeven aan Harlan Grove. De formule verscheen ook in Bob Umlas 'boek This Is not Excel, It's Magic. Mike Delaney, Meni Porat en Tim Sheets stelden allemaal de min / min-truc voor. SOMPRODUCT werd voorgesteld door Audrey Lynn en Steven White. Bedankt iedereen.
Bekijk video
- Er is een geheime klasse van formules die matrixformules worden genoemd.
- Een matrixformule kan duizenden tussenliggende berekeningen uitvoeren.
- Ze vereisen vaak dat u op Ctrl + Shift + Enter drukt, maar niet altijd.
- Het beste boek over matrixformules is Ctrl + Shift + Enter van Mike Girvin.
- Met INDIRECT kunt u aaneenschakeling gebruiken om iets op te bouwen dat op een celverwijzing lijkt.
- Datums zijn mooi opgemaakt maar worden sinds 1 januari 1900 als aantal dagen bewaard.
- Het samenvoegen van twee datums verwijst naar een reeks rijen in Excel.
- Als u om de
ROW(INDIRECT(Date1:Date2))wil vraagt, verschijnt er een reeks van vele opeenvolgende nummers - Gebruik de WEEKDAY-functie om erachter te komen of een datum vrijdag is.
- Hoeveel vrijdagen zijn er in juli?
- Gebruik de tool Formule evalueren om een formule in slow motion te zien berekenen
- Hoeveel 13e komen dit jaar voor?
- Hoeveel Friday the 13ths zijn er tussen twee datums gebeurd?
- Controleer elke datum om te zien of de WEEKDAG vrijdag is
- Controleer elke datum om te zien of de DAG 13 is
- Vermenigvuldig die resultaten met SOMPRODUCT
- Gebruik - om True / False naar 1/0 te converteren
Videotranscriptie
Leer Excel van podcast, aflevering 2026 - Mijn favoriete formule in heel Excel!
Podcast dit hele boek, klik op de "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
Oké, het was het 30e onderwerp in het boek, we zaten een beetje aan het einde van het formulegedeelte, of midden in het formulegedeelte, en ik zei dat ik mijn favoriete formule aller tijden moet opnemen. Dit is gewoon een geweldige formule, of je nu het nummer vrijdag de 13e moet tellen of niet, het opent een wereld in een heel geheim gebied van Excel genaamd Array Formulas! Voer een startdatum in, voer een einddatum in en deze formule berekent het aantal vrijdag de 13e dat plaatsvond tussen die twee datums. Het voert eigenlijk elke dag vijf berekeningen uit tussen die twee datums, 91895 berekeningen + SUM, 91896 berekeningen die plaatsvinden binnen deze ene kleine formule, oké. Nu, tegen het einde van deze aflevering, zul je zo geïntrigeerd zijn door matrixformules. Ik wil erop wijzen,mijn vriend Mike Girvin heeft het beste boek over matrixformules genaamd "Ctrl + Shift" Enter ", dit is een recente druk met de blauwe omslag, vroeger een gele en groene omslag. Welk boek u ook krijgt, het is een geweldig boek, met dezelfde inhoud in zowel het gele als het groene.
Oké, laten we beginnen met de binnenkant hiervan, met een formule die je misschien nog niet hebt gehoord, genaamd INDIRECT. INDIRECT stelt ons in staat om een stukje tekst samen te voegen of op een of andere manier een stukje tekst op te bouwen dat eruitziet als een celverwijzing. Oké dus, laten we zeggen dat we hier een prijzenrad hebben, en ik heb je net gevraagd om te kiezen tussen A, B en C. Oké, dus je kiest dit en kiest C, en dan dit en kiest 3, oké, en je prijs is een resortverblijf, want dat is wat opgeslagen is in C3. En de formule hier is aaneengeschakeld, wat dan ook sinds C5 en wat er ook in C6 is, met behulp van de & en dat vervolgens door te geven aan de INDIRECT. Dus = INDIRECT (C5 & C6), in dit geval is C3, dat moet een uitgebalanceerde referentie zijn. INDIRECT zegt: "Hé, we gaan naar C3 en geven daar het antwoord op, oké?" In Lotus 1-2-3 heette dit de functie @@,in Excel hernoemden ze het naar INDIRECT. Oké, dus je hebt in de INDIRECTE, nu hier is het verbazingwekkende dat daarbinnen gebeurt.
We hebben twee datums, hoe slaat Excel datums op, 17-2-1965, dat is eigenlijk alleen maar opmaak. Als we gaan kijken naar het werkelijke aantal daarachter, is het 23790, wat betekent dat het 23790 dagen is sinds 1/1/1900 en 42167 dagen sinds 1/1/1980. Op een Mac is het sinds 01-01-1904, dus de datums zullen ongeveer 3000 afwijken. Oké, dat is hoe Excel het opslaat, het toont het ons echter, dankzij dit getalnotatie als een datum, maar als we B3 en a: en B4 zouden samenvoegen, zou het ons eigenlijk de nummers geven die achter de schermen zijn opgeslagen. So = B3 & ”:” & B4, en als we dat zouden doorgeven aan INDIRECT, dan wijst het in feite naar alle rijen van 23790 tot 42167.
Dus daar is het INDIRECTE van B6, ik vroeg om de RIJ daarvan, dat geeft me een heleboel antwoorden, en om erachter te komen hoeveel antwoorden ik heb gebruikt tellen, oké. En om dit te laten werken, als ik gewoon op Enter druk, werkt het niet, ik moet Ctrl en Shift ingedrukt houden en op Enter drukken, en kijk, dat voegt de () toe rond de formule hierboven. Het vertelt Excel om naar de superformulemodus, de matrixformulemodus te gaan en alle berekeningen uit te voeren voor alles wat uit die array is gekomen, 18378, oké. Dus dat is een geweldige truc, indirect van date1: date2, geef dat door aan de ROW-functie, en hier is een klein voorbeeld.
Dus we willen gewoon weten hoeveel vrijdagen er in juli plaatsvonden, hier is een startdatum, hier is een einddatum, en voor elk van die rijen ga ik om de WEEKDAG vragen. WEEKDAY vertelt ons welke dag van de week het is, en hier in het, 2 argument zal vrijdag de waarde 5 hebben. Dus ik zoek het antwoord, en we gaan deze formule kiezen, ga naar Formules, en Formule evalueren, en Formule evalueren is een geweldige manier om te zien hoe een formule in slow motion wordt berekend. Dus er is de B3, de 1 juli, en je ziet dat verandert in een nummer, en dan voegen we ons bij de dubbele punt, daar is de B4, die zal veranderen in een nummer, en nu krijgen we de tekst, 42186: 42216. Op dit punt geven we dat door aan de RIJ, en die simpele kleine uitdrukking zal hier in 31 waarden veranderen.
Nu, in het voorbeeld waarin ik alles had van 1965 tot 2015, zou het 86.000 waarden tevoorschijn halen, toch, en je wilt dat niet doen en de formule evalueren, want het zou een beetje krankzinnig zijn, oké? Maar je kunt zien wat hier gebeurt met de 31, en nu geef ik die 31 dagen door aan de WEEKDAY-functie, en we krijgen 3-4-5. Dus 3 betekent dat het een woensdag was, en 4 betekent dat het een donderdag was, en 5 betekent dat het een vrijdag was. Neem al die 31 waarden en kijk of ze = 5 zijn, wat een vrijdag is, en we krijgen een heleboel ONWAAR en WAAR, dus woensdag, donderdag, vrijdag en dan 7 cellen later zouden de volgende WAAR zijn, geweldig!
Oké, dus in dit geval hebben we 5 WAAR en 26 ONWAAR.Om die bij elkaar op te tellen, moet ik de ONWAAR omzetten in 0 en de WAAR in 1, en een heel gebruikelijke manier om dat te doen is door de - . Oké, helaas liet het daar het antwoord niet zien, waar we een hele reeks enen en nullen zagen, maar dat is eigenlijk wat er gebeurt, en dan telt SUMPRODUCT het op en brengt ons naar de 5. Hier beneden, als we willen zoek uit hoeveel van de 13e van de maand er dit jaar waren, vanaf deze startdatum tot deze einddatum, een vergelijkbaar proces. Hoewel we 365 zullen hebben, geef dat door aan de DAG-functie en kijk hoeveel er 13 zijn, oké. Voor het voorbeeld van de 92000 rij, weet je, we krijgen de dag, we krijgen de weekdag, controleren of de, DAG = 13, controleren of de WEEKDAY = FALSE, dit * dit vermenigvuldigen,en alleen in de gevallen waarin het vrijdag de 13e is, wordt dat WAAR. Het SUMPRODUCT zegt dan "Tel die allemaal op", en zo krijgen we de 86, letterlijk 91895 berekeningen + SUM, 91896 binnen deze ene formule, het is waanzinnig krachtig! Koop Mike's boek, het is een geweldig boek, het zal een hele wereld van Excel-formules voor je openen, en eigenlijk zou je gewoon beide boeken moeten kopen. Koop mijn boek, koop Mike's boek, en je hebt een geweldige collectie waar je de rest van het jaar mee door kunt.het zal een hele wereld van Excel-formules voor je openen, en eigenlijk zou je gewoon beide boeken moeten kopen. Koop mijn boek, koop Mike's boek, en je hebt een geweldige collectie waar je de rest van het jaar mee door kunt.het zal een hele wereld van Excel-formules voor je openen, en eigenlijk zou je gewoon beide boeken moeten kopen. Koop mijn boek, koop Mike's boek, en je hebt een geweldige collectie waar je de rest van het jaar mee door kunt.
Alright, so recap: there's a secret class of formulas called array formulas, and array formula can do thousands of intermediate calculations. They usually require you to press Ctrl+Shift+Enter, but not always, and the best book on array formulas is Mike Girvin’s “Ctrl+Shift+Enter” book. Alright, so INDIRECT lets you use concatenation to build something that looks like a cell reference, and then INDIRECT goes to that cell reference. Concatenating two dates with a colon will point to a range of rows in Excel, and then asking for the ROW of the INDIRECT of date1:date2 will pop out an array of many consecutive numbers, maybe 31, maybe 365, or maybe 85000. Check each date to see if the WEEKDAY=Friday, check each day to see if the DAY=13, multiply those two arrays of TRUEs and FALSEs using the SUMPRODUCT. In many cases we'll use -- to convert the TRUE/FALSE to 1’s and 0’s to allow the SUMPRODUCT to work. It's an awesome formula, I didn't create it, I found it on the message board, as I worked through it, I'm like “Wow, this is really cool!”
Oké, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2026.xlsx
