Excel Array-formler är superkraftiga. Du kan ersätta tusentals formler med en enda formel när du lär dig Ctrl + Shift + Enter-tricket. Idag gör en enda matrisformel 86 000 beräkningar.
Triskaidekaphobia är rädslan för fredagen den 13: e. Detta ämne botar ingenting, men det kommer att visa dig en helt fantastisk formel som ersätter 110 268 formler. I verkliga livet behöver jag aldrig räkna hur många fredagar 13-talet har inträffat under min livstid, men kraften och skönheten i denna formel illustrerar kraften i Excel.
Säg att du har en vän som är vidskeplig omkring fredagen den 13: e. Du vill illustrera hur många fredag 13: e din vän har upplevt.
Ställ in det enkla kalkylbladet nedan, med födelsedatum i B1 och =TODAY()i B2. Sedan utvärderar en vild formel i B6 varje dag att din vän har levt för att ta reda på hur många av dessa dagar som var fredag och föll den 13: e i månaden. För mig är siffran 86. Inget att vara rädd för.
Förresten, 17/2/1965 är verkligen min födelsedag. Men jag vill inte att du skickar ett födelsedagskort till mig. Istället för min födelsedag vill jag att du ska låta mig förklara hur den fantastiska formeln fungerar, ett litet steg i taget.
Har du någonsin använt INDIRECT-funktionen? När du ber om =INDIRECT("C3")kommer Excel att gå till C3 och returnera vad som helst i den cellen. Men INDIRECT är kraftfullare när du beräknar cellreferensen direkt. Du kan sätta upp ett prishjul där någon väljer en bokstav mellan A och C och sedan väljer ett nummer mellan 1 och 3. När du sammanfogar de två svaren har du en celladress, och vad som helst på den celladressen är priset . Ser ut som om jag vann en fotobok istället för semesterorten.
Vet du hur Excel lagrar datum? När Excel visar dig 17/27/1965 lagras det 23790 i cellen, för 17/2/1965 var den 23790: e dagen på 1900-talet. Kärnan i formeln är en sammanfogning som sammanfogar startdatumet och ett kolon och slutdatumet. Excel använder inte det formaterade datumet. Istället använder den serienumret bakom kulisserna. Så B3&":"&B4blir 23790: 42167. Tro det eller ej, det är en giltig cellreferens. Om du vill lägga till allt i raderna 3 till 5 kan du använda =SUM(3:5). Så när du skickar 23790: 42167 till INDIRECT-funktionen pekar den på alla raderna.
Nästa sak mördareformeln gör är att be om ROW(23790:42167). Normalt passerar du en enda cell: =ROW(D17)är 17. Men i det här fallet passerar du tusentals celler. När du ber om ROW(23790:42167)och avslutar formeln med Ctrl + Skift + Enter returnerar Excel faktiskt varje nummer från 23790, 23791, 23792 och så vidare till 42167.
Detta steg är det fantastiska steget. I det här steget går vi från två nummer och “poppar ut” en matris med 18378 nummer. Nu måste vi göra något med den sortimentet av svar. Cell B9 i föregående figur räknar bara hur många svar vi får, vilket är tråkigt, men det bevisar att det ROW(23790:42167)återkommer 18378 svar.
Låt oss dramatiskt förenkla den ursprungliga frågan så att du kan se vad som händer. I det här fallet hittar vi antalet fredagar i juli 2015. Formeln som visas nedan i B7 ger rätt svar i B6.
Kärnan i formeln är ROW(INDIRECT(B3&":"&B4)). Detta kommer att returnera de 31 datumen i juli 2015. Men formeln skickar sedan de 31 datumen till WEEKDAY(,2)funktionen. Denna funktion returnerar en 1 för måndag, 5 för fredag och så vidare. Så den stora frågan är hur många av dessa 31 datum som returnerar en 5 när de skickas till WEEKDAY(,2)funktionen.
Du kan se formeln beräkna i slow motion med hjälp av kommandot Utvärdera formel på fliken Formel i menyfliksområdet.
Detta efter att INDIRECT konverterar datumen till en radreferens.
I nästa steg kommer Excel att skicka 31 nummer till WEEKDAY-funktionen. Nu, i mördareformeln, skulle det passera 18 378 nummer istället för 31.
Här är resultaten av de 31 WEEKDAY-funktionerna. Kom ihåg att vi vill räkna hur många som är 5.
Kontrollerar om den föregående matrisen är 5 returnerar en hel massa sanna / falska värden. Det finns 5 sanna värden, ett för varje fredag.
Jag kan inte visa dig vad som händer härnäst, men jag kan förklara det. Excel kan inte SUMMA en massa sanna och falska värden. Det strider mot reglerna. Men om du multiplicerar dessa sanna och falska värden med 1 eller om du använder dubbelnegativt eller N (), konverterar du de sanna värdena till 1 och de falska värdena till 0. Skicka dem till SUM eller SUMPRODUCT, så kommer du få räkningen av de sanna värdena.
Här är ett liknande exempel för att räkna hur många månader som har en dag 13 i sig. Det här är trivialt att tänka på: Varje månad har en 13: e, så svaret för ett helt år är bättre 12. Excel gör matematiken, genererar 365 datum, skickar dem alla till DAY () -funktionen och räknar ut hur många som slutar upp den 13: e i månaden. Svaret är som förväntat 12.
Nästa figur är ett kalkylblad som gör hela logiken den enda mördareformeln som visas i början av detta ämne. Jag har skapat en rad för varje dag jag har levt. I kolumn B får jag DAGEN () för det datumet. I kolumn C får jag WEEKDAY () för datumet. I kolumn D är B lika med 13? I kolumn E, är C = 5? Jag multiplicerar sedan D * E för att konvertera True / False till 1/0.
Jag har gömt många rader, men jag visar dig tre slumpmässiga dagar i mitten som råkar vara både en fredag och den 13: e.
Totalt i F18381 är samma 86 som min ursprungliga formel returnerade. Ett bra tecken. Men det här kalkylbladet har 110 268 formler. Min ursprungliga mördareformel gör all logiken i dessa 110 268 formler i en enda formel.
Vänta. Jag vill klargöra. Det finns inget magiskt i den ursprungliga formeln som blir smart och förkortar logiken. Den ursprungliga formeln gör verkligen 110 268 steg, förmodligen ännu fler, för den ursprungliga formeln måste beräkna ROW () -matrisen två gånger.
Hitta ett sätt att använda detta ROW(INDIRECT(Date:Date))i verkliga livet och skicka det till mig via e-post (pub på dot com). Jag skickar ett pris till de första 100 personerna som svarar. Förmodligen inte en utväg vistelse. Mer sannolikt en Big Mac. Men det är så det går med priser. Massor av stora Mac-datorer och inte många utväg.
Jag såg denna formel först publicerad på anslagstavlan 2003 av Ekim. Kredit gavs till Harlan Grove. Formeln uppträdde också i Bob Umlas bok This Isn't Excel, It's Magic. Mike Delaney, Meni Porat och Tim Sheets föreslog alla minus / minus-tricket. SUMPRODUCT föreslogs av Audrey Lynn och Steven White. Tack allihopa.
Kolla på video
- Det finns en hemlig klass av formler som kallas Array Formulas.
- En matrisformel kan göra tusentals mellanliggande beräkningar.
- De kräver ofta att du trycker på Ctrl + Skift + Enter, men inte alltid.
- Den bästa boken om matrisformler är Mike Girvins Ctrl + Shift + Enter.
- INDIRECT låter dig använda sammankoppling för att bygga något som ser ut som en cellreferens.
- Datum är snyggt formaterade men lagras som ett antal dagar sedan 1 januari 1900.
- Sammankoppling av två datum pekar på ett antal rader i Excel.
- Att be om
ROW(INDIRECT(Date1:Date2))kommer att "poppa ut" en matris med många på varandra följande nummer - Använd WEEKDAY-funktionen för att ta reda på om ett datum är fredag.
- Hur många fredagar inträffar i juli?
- För att se en formel beräkna i slow motion, använd verktyget Evalulate Formula
- Hur många 13: e förekommer i år?
- Hur många fredag den 13: e hände mellan två datum?
- Kontrollera varje datum för att se om VEKDAGEN är fredag
- Kontrollera varje datum för att se om DAGEN är 13
- Multiplicera dessa resultat med SUMPRODUCT
- Använd - för att konvertera True / False till 1/0
Videoutskrift
Lär dig Excel från podcast, avsnitt 2026 - Min favoritformel i hela Excel!
Podcasting av hela denna bok, klicka på "i" i det övre högra hörnet för att komma till spellistan!
Okej, det var det 30: e ämnet i boken, vi var typ av i slutet av formelsektionen, eller mitt i formelsektionen, och jag sa att jag måste inkludera min favoritformel genom tiderna. Det här är bara en fantastisk formel, oavsett om du måste räkna numret fredagen den 13: e eller inte, det öppnar en värld i ett helt hemligt område i Excel som heter Array Formulas! Sätt in ett startdatum, sätt in ett slutdatum, och denna formel beräknar antalet fredag 13: e som inträffade mellan dessa två datum. Det gör faktiskt fem beräkningar varje dag mellan de två datumen, 91895 beräkningar + SUM, 91896 beräkningar som händer inuti den här lilla formeln, okej. Nu, i slutet av detta avsnitt, kommer du att bli så fascinerad av matrisformler. Jag vill påpeka,min vän Mike Girvin har den bästa boken om matrisformler som heter "Ctrl + Shift" Enter ", det här är en ny utskrift med det blå omslaget, brukade vara ett gult och grönt omslag. Nu, oavsett vilken du får, är en fantastisk bok, samma innehåll i både den gula och den gröna.
Okej så, låt oss börja på insidan av detta, med en formel som du kanske inte har hört kallad INDIRECT. INDIRECT låter oss sammanfoga, eller på något sätt bygga lite text som ser ut som en cellreferens. Okej så, låt oss säga att vi har ett prishjul här, och jag bad dig bara att välja mellan A, B och C. Okej, så du väljer detta och väljer C, och välj sedan detta och välj 3, okej, och ditt pris är en semesterort, för det är det som lagras i C3. Och formeln här sammanfogas vad som helst sedan C5 och vad som helst i C6 med hjälp av & och sedan skickar det till INDIREKT. Så = INDIREKT (C5 & C6), i detta fall, är C3, det måste vara en balanserad referens. INDIRECT säger "Hej, vi ska gå till C3 och returnera svaret från det, okej?" Tillbaka i Lotus 1-2-3 kallades detta @@-funktionen,i Excel döptes de om till INDIREKT. Okej, så du har det i INDIRECT, nu är det fantastiska som händer där inne.
Vi har två datum, hur lagrar Excel datum, 17/2/1965, det är egentligen bara formatering. Om vi gick och tittade på det faktiska antalet bakom det är det 23790, vilket betyder att det är 23790 dagar sedan 1/1/1900 och 42167 dagar sedan 1/1/1980. På en Mac blir det sedan 1/1/1904, så datumen kommer att vara ungefär 3000 av. Okej, det är så Excel lagrar det, det visar det dock för oss tack vare det här nummerformatet som ett datum, men om vi skulle sammanfoga B3 och a: och B4, skulle det faktiskt ge oss de nummer som lagrats bakom kulisserna. Så = B3 & ”:” & B4, och om vi skulle ge det till INDIRECT kommer det faktiskt att peka på alla rader från 23790 ner till 42167.
Så det finns INDIREKT för B6, jag bad om RADEN för det, det kommer att ge mig en hel massa svar och för att ta reda på hur många svar jag använde räknas, okej. Och för att få det här att fungera, om jag bara trycker på Enter, fungerar det inte, jag måste hålla ner Ctrl och Shift och trycka på Enter och se, det lägger till () runt formeln här uppe. Det berättar för Excel att gå in i superformelläge, matrisformelläge och göra all matematik för allt som dyker upp från den matrisen, 18378, okej. Så det är ett fantastiskt trick, indirekt från date1: date2, skicka det till ROW-funktionen, och här är ett litet exempel.
Så vi vill bara ta reda på hur många fredagar som inträffade i juli, här är ett startdatum, här är ett slutdatum och för var och en av de här raderna kommer jag att be om veckodagen. WEEKDAY berättar vilken veckodag det är, och här i argumentet 2 kommer fredagarna att ha ett värde på 5. Så jag letar efter svaret, och vi ska välja den här formeln, gå till Formler, och utvärdera formel och utvärdera formel är ett utmärkt sätt att se en formel beräknas i slow motion. Så det finns B3, den 1 juli, och du ser att det ändras till ett nummer, och sedan går vi med i kolon, höger, det är B4, som kommer att ändras till ett nummer, och nu får vi texten, 42186: 42216. Vid denna tidpunkt skickar vi det till ROW, och det enkla lilla uttrycket kommer att förvandlas till 31 värden här.
Nu, i exemplet där jag hade allt från 1965 till 2015, skulle det dyka upp 86000 värden, eller hur, och du vill inte göra det och utvärdera formeln, för det skulle vara typ av galen, okej? Men du kan se vad som händer här med 31, och nu passerar jag de 31 dagarna till WEEKDAY-funktionen, och vi får 3-4-5. Så 3 betyder att det var en onsdag, och sedan 4 betyder att det var en torsdag, och sedan 5 betyder att det var en fredag. Ta alla dessa 31 värden och se om de är = 5 vilket är en fredag, och vi kommer att få en massa FALSKA och SANNA, så onsdag, torsdag, fredag och sedan 7 celler senare skulle vara nästa SANT, grymt bra!
Okej, så i det här fallet har vi 5 SANT och 26 FALSKT. För att lägga till dem måste jag konvertera FALSKT till 0 och SANT till 1, och ett mycket vanligt sätt att göra det är att använda - . Okej, tyvärr visade det inte svaret där, där vi såg en hel massa 1 och 0, men det är faktiskt vad som händer, och sedan lägger SUMPRODUCT upp det och tar oss till 5. Här nere, om vi vill ta reda på hur många av 13: e i månaden det var i år, från detta startdatum till slutdatum, mycket liknande process. Även om vi ska ha 365, skicka det till DAY-funktionen och kolla för att se hur många som är 13, okej. För 92000-radsexemplet vet du att vi får dagen, vi får veckodagen, kontrollerar om, DAG = 13, kontrollerar om VEKDAGEN = FALSKT, multiplicerar detta * detta,och bara i de fall där det är en fredag den 13: e slutar det som SANT. SUMPRODUCT säger då "Lägg till alla dessa", och det är så vi får 86, bokstavligen 91895 beräkningar + SUM, 91896 som händer inuti denna formel, det är galet kraftfullt! Gå och köp Mike's book, det är en fantastisk bok, den öppnar en hel värld av Excel-formler för dig, och faktiskt borde du bara köpa båda böckerna. Köp min bok, köp Mikes bok, så får du en fantastisk samling som tar dig igenom resten av året.det öppnar en hel värld av Excel-formler för dig, och faktiskt borde du bara köpa båda böckerna. Köp min bok, köp Mikes bok, så får du en fantastisk samling som tar dig igenom resten av året.det öppnar en hel värld av Excel-formler för dig, och faktiskt borde du bara köpa båda böckerna. Köp min bok, köp Mikes bok, så får du en fantastisk samling som tar dig igenom resten av året.
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!”
Okej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2026.xlsx
