Excel Array-formler er superkraftige. Du kan erstatte tusinder af formler med en enkelt formel, når du lærer Ctrl + Shift + Enter-tricket. I dag udfører en enkelt matrixformel 86.000 beregninger.
Triskaidekaphobia er frygt for fredag den 13.. Dette emne kurerer ikke noget, men det viser dig en helt fantastisk formel, der erstatter 110.268 formler. I det virkelige liv behøver jeg aldrig at tælle, hvor mange fredag de 13. er sket i min levetid, men kraften og skønheden i denne formel illustrerer kraften i Excel.
Sig, at du har en ven, der er overtroisk omkring fredag den 13.. Du vil illustrere, hvor mange fredag den 13., din ven har oplevet.
Opret det enkle regneark nedenfor med fødselsdato i B1 og =TODAY()i B2. Derefter evaluerer en vild formel i B6 hver dag, at din ven har levet for at finde ud af, hvor mange af disse dage der var fredag og faldt den 13. i måneden. For mig er tallet 86. Intet at være bange for.
Forresten, 17/2/1965 er virkelig min fødselsdag. Men jeg vil ikke have, at du sender mig et fødselsdagskort. I stedet for til min fødselsdag vil jeg have dig til at fortælle mig, hvordan den fantastiske formel fungerer, et lille trin ad gangen.
Har du nogensinde brugt INDIRECT-funktionen? Når du beder om =INDIRECT("C3"), går Excel til C3 og returnerer det, der er i den celle. Men INDIRECT er mere kraftfuld, når du beregner cellereferencen on-the-fly. Du kan oprette et præmiehjul, hvor nogen vælger et bogstav mellem A og C og derefter vælger et tal mellem 1 og 3. Når du sammenkæder de to svar, har du en celleadresse, og hvad der er på den celleadresse er prisen . Det ser ud til, at jeg vandt en fotobog i stedet for udvejsopholdet.
Ved du hvordan Excel gemmer datoer? Når Excel viser dig 2/17/1965, gemmer det 23790 i cellen, fordi 17/2/1965 var den 23790. dag i det 20. århundrede. Kernen i formlen er en sammenkædning, der slutter sig til startdatoen og et kolon og slutdatoen. Excel bruger ikke den formaterede dato. I stedet bruger det serienummeret bag kulisserne. Så B3&":"&B4bliver 23790: 42167. Tro det eller ej, det er en gyldig cellereference. Hvis du vil tilføje alt i række 3 til 5, kan du bruge det =SUM(3:5). Så når du sender 23790: 42167 til INDIRECT-funktionen, peger den på alle rækkerne.
Den næste ting, som morderformlen gør, er at bede om ROW(23790:42167). Normalt passerer du en enkelt celle: =ROW(D17)er 17. Men i dette tilfælde passerer du tusinder af celler. Når du beder om ROW(23790:42167)og afslutter formlen med Ctrl + Shift + Enter, returnerer Excel faktisk hvert nummer fra 23790, 23791, 23792 osv. Op til 42167.
Dette trin er det fantastiske trin. I dette trin går vi fra to tal og “popper ud” en matrix med 18378 numre. Nu skal vi gøre noget med den række svar. Celle B9 i den foregående figur tæller bare, hvor mange svar vi får, hvilket er kedeligt, men det beviser, at ROW(23790:42167)det vender tilbage 18378 svar.
Lad os dramatisk forenkle det originale spørgsmål, så du kan se, hvad der sker. I dette tilfælde finder vi antallet af fredage i juli 2015. Formlen vist nedenfor i B7 giver det rigtige svar i B6.
Kernen i formlen er ROW(INDIRECT(B3&":"&B4)). Dette vil returnere de 31 datoer i juli 2015. Men formlen sender derefter de 31 datoer til WEEKDAY(,2)funktionen. Denne funktion returnerer en 1 for mandag, 5 for fredag osv. Så det store spørgsmål er, hvor mange af disse 31 datoer returnerer en 5, når de sendes til WEEKDAY(,2)funktionen.
Du kan se formlen beregne i slowmotion ved hjælp af kommandoen Evaluer formel på båndets formel-fane.
Dette er efter at INDIRECT konverterer datoerne til en rækkehenvisning.
I det næste trin er Excel ved at sende 31 numre til WEEKDAY-funktionen. Nu, i morderformlen, ville det passere 18.378 tal i stedet for 31.
Her er resultaterne af de 31 WEEKDAY-funktioner. Husk, vi vil tælle, hvor mange der er 5.
Kontrollerer, om det foregående array er 5, returnerer en hel række sand / falsk værdier. Der er 5 sande værdier, en for hver fredag.
Jeg kan ikke vise dig, hvad der sker næste gang, men jeg kan forklare det. Excel kan ikke SUME en masse rigtige og falske værdier. Det er imod reglerne. Men hvis du gange disse sande og falske værdier med 1, eller hvis du bruger dobbeltnegativet eller N () -funktionen, konverterer du de sande værdier til 1 og de falske værdier til 0. Send dem til SUM eller SUMPRODUCT, og du vil få optællingen af de sande værdier.
Her er et lignende eksempel for at tælle, hvor mange måneder der har en dag 13 i sig. Dette er trivielt at tænke over: Hver måned har en 13., så svaret i et helt år er bedre 12. Excel laver matematik, genererer 365 datoer, sender dem alle til DAY () -funktionen og finder ud af, hvor mange der slutter op den 13. i måneden. Svaret er som forventet 12.
Den næste figur er et regneark, der udfører hele logikken den ene dræberformel, der vises i starten af dette emne. Jeg har oprettet en række for hver dag, jeg har levet. I kolonne B får jeg DAGEN () for den dato. I kolonne C får jeg datoen WEEKDAY (). I kolonne D er B lig med 13? I kolonne E, er C = 5? Derefter multiplicerer jeg D * E for at konvertere True / False til 1/0.
Jeg har skjult mange af rækkerne, men jeg viser dig tre tilfældige dage i midten, som tilfældigvis er både en fredag og den 13..
Det samlede antal i F18381 er det samme 86 som min oprindelige formel returnerede. Et godt tegn. Men dette regneark har 110.268 formler. Min originale dræberformel udfører al logikken i disse 110.268 formler i en enkelt formel.
Vente. Jeg vil præcisere det. Der er ikke noget magisk i den originale formel, der bliver smart og forkorter logikken. Den oprindelige formel udfører virkelig 110.268 trin, sandsynligvis endnu mere, fordi den oprindelige formel skal beregne ROW () array to gange.
Find en måde at bruge dette på ROW(INDIRECT(Date:Date))i det virkelige liv og send det til mig i en e-mail (pub på dot com). Jeg sender en præmie til de første 100 personer, der skal svare. Sandsynligvis ikke en udvej ophold. Mere sandsynligt en Big Mac. Men sådan går det med præmier. Masser af Big Mac'er og ikke mange udvejsophold.
Jeg så første gang denne formel blev sendt på opslagstavlen i 2003 af Ekim. Kredit blev givet til Harlan Grove. Formlen optrådte også i Bob Umlas bog This Isn't Excel, It's Magic. Mike Delaney, Meni Porat og Tim Sheets foreslog alle minus / minus-tricket. SUMPRODUCT blev foreslået af Audrey Lynn og Steven White. Tak allesammen.
Se video
- Der er en hemmelig klasse af formler kaldet Array Formulas.
- En matrixformel kan udføre tusindvis af mellemberegninger.
- De kræver ofte, at du trykker på Ctrl + Shift + Enter, men ikke altid.
- Den bedste bog om matrixformler er Mike Girvins Ctrl + Shift + Enter.
- INDIRECT giver dig mulighed for at bruge sammenkædning til at opbygge noget, der ligner en cellereference.
- Datoer er pænt formateret, men gemmes som et antal dage siden 1. januar 1900.
- Sammenkædning af to datoer peger på en række rækker i Excel.
- At bede om
ROW(INDIRECT(Date1:Date2))viljen "popper ud" en matrix med mange på hinanden følgende numre - Brug af WEEKDAY-funktionen til at finde ud af, om en dato er fredag.
- Hvor mange fredage finder sted i juli?
- Hvis du vil se en formel beregne i slowmotion, skal du bruge værktøjet Evaluer formel
- Hvor mange 13'ere forekommer i år?
- Hvor mange fredag 13. skete der mellem to datoer?
- Tjek hver dato for at se, om WEEKDAY er fredag
- Tjek hver dato for at se, om DAGEN er 13
- Multiplicer disse resultater ved hjælp af SUMPRODUCT
- Brug - til at konvertere True / False til 1/0
Videoudskrift
Lær Excel fra podcast, afsnit 2026 - Min favoritformel i hele Excel!
Podcasting af hele denne bog, klik på "i" i øverste højre hjørne for at komme til afspilningslisten!
Okay, det var det 30. emne i bogen, vi var lidt i slutningen af formelsektionen eller midt i formelsektionen, og jeg sagde, at jeg er nødt til at medtage min favoritformel nogensinde. Dette er bare en fantastisk formel, uanset om du skal tælle nummeret fredag den 13. eller ej, det åbner en verden i et helt hemmeligt område i Excel kaldet Array Formulas! Indsæt en startdato, sæt en slutdato, og denne formel beregner antallet af fredag den 13., der opstod mellem disse to datoer. Det laver faktisk fem beregninger hver eneste dag mellem disse to datoer, 91895 beregninger + SUM, 91896 beregninger sker inde i denne ene lille formel, okay. Nu ved slutningen af denne episode bliver du så fascineret af matrixformler. Jeg vil gerne påpege,min ven Mike Girvin har den bedste bog om matrixformler kaldet "Ctrl + Shift" Enter ", dette er en nylig udskrivning med det blå omslag, der tidligere var et gult og grønt omslag. Nu, uanset hvilken du får, er en fantastisk bog med samme indhold i både den gule og den grønne.
Okay, lad os starte med indersiden af dette med en formel, som du måske ikke har hørt kaldet INDIRECT. INDIRECT giver os mulighed for at sammenkæde eller på en eller anden måde bygge lidt tekst, der ligner en cellehenvisning. Okay, lad os sige, at vi har et præmiehjul her, og jeg bad dig bare om at vælge mellem A, B og C. Okay, så du vælger dette og vælger C, og vælg derefter dette og vælg 3, okay, og din præmie er et resortophold, for det er det, der er gemt i C3. Og formlen her er sammenkædet uanset siden C5 og hvad der er i C6 ved hjælp af & og derefter videregive det til INDIREKTE. Så = INDIREKTE (C5 & C6), i dette tilfælde, er C3, der skal være en afbalanceret reference. INDIRECT siger ”Hej, vi går til C3 og returnerer svaret fra det, okay?” Tilbage i Lotus 1-2-3 blev dette kaldt @@-funktionen,i Excel omdøbte de det til INDIREKTE. Okay, så har du det i INDIREKTE, nu er her den fantastiske ting, der sker inde derinde.
Vi har to datoer, hvordan gemmer Excel datoer, 17/2/1965, det er egentlig bare formatering. Hvis vi gik og kiggede på det faktiske antal bag det, er det 23790, hvilket betyder, at det er 23790 dage siden 1/1/1900 og 42167 dage siden 1/1/1980. På en Mac vil det være siden 1/1/1904, så datoerne vil være omkring 3000 i rabat. Okay, sådan gemmer Excel det, det viser det dog for os takket være dette nummerformat som en dato, men hvis vi sammenkæder B3 og a: og B4, ville det faktisk give os de numre, der er gemt bag kulisserne. Så = B3 & ”:” & B4, og hvis vi sender det til INDIRECT, vil det faktisk pege på alle rækker fra 23790 og ned til 42167.
Så der er INDIREKTE af B6, jeg bad om RÆKEN af det, det vil give mig en hel masse svar, og for at finde ud af, hvor mange svar jeg brugte tæller, okay. Og for at få dette til at fungere, hvis jeg bare trykker på Enter, virker det ikke, jeg er nødt til at holde Ctrl og Shift nede og trykke på Enter og se, der tilføjer () omkring formlen her oppe. Det fortæller Excel at gå i superformelmodus, matrixformelmodus og gøre al matematik for alt, der dukkede ud fra det matrix, 18378, okay. Så det er et fantastisk trick, indirekte af date1: date2, send det til ROW-funktionen, og her er et lille eksempel.
Så vi vil bare finde ud af, hvor mange fredage der fandt sted i juli, her er en startdato, her er en slutdato, og for hver af disse rækker vil jeg bede om WEEKDAY. WEEKDAY fortæller os hvilken ugedag det er, og her i argumentet 2 vil fredage have en værdi på 5. Så jeg leder efter svaret, og vi vælger denne formel, gå til Formler, og Evaluer formel, og Evaluer formel er en fantastisk måde at se en formel beregnes i slowmotion. Så der er B3, den 1. juli, og du kan se, at der ændres til et tal, og så slutter vi os til tyktarmen, højre, der er B4, der ændres til et tal, og nu får vi teksten, 42186: 42216. På dette tidspunkt videregiver vi det til ROWEN, og det enkle lille udtryk vil blive til 31 værdier lige her.
Nu, i eksemplet, hvor jeg havde alt fra 1965 til 2015, ville det poppe 86000 værdier ud, ikke, og du vil ikke gøre det og evaluere formlen, fordi det ville være sindssygt, okay? Men du kan se, hvad der sker her med 31, og nu overfører jeg de 31 dage til WEEKDAY-funktionen, og vi får 3-4-5. Så 3 betyder, at det var en onsdag, og derefter 4 betyder, at det var en torsdag, og derefter 5, at det var en fredag. Tag alle disse 31 værdier og se om de er = 5, hvilket er en fredag, og vi får en masse FALSKE og SANDE, så onsdag, torsdag, fredag og derefter 7 celler senere ville være den næste SANDE, fantastisk!
Okay, så i dette tilfælde har vi 5 SANDE og 26 FALSKE. For at tilføje dem er jeg nødt til at konvertere FALSE til 0 og SANDE til 1, og en meget almindelig måde at gøre det på er at bruge - . Okay, desværre viste det ikke svaret der, hvor vi så en hel flok 1'er og 0'er, men det er faktisk, hvad der sker, og så tilføjer SUMPRODUCT det og får os til 5. Hernede, hvis vi vil finde ud af, hvor mange af 13. i måneden der var i år, fra denne startdato til denne slutdato, meget lignende proces. Selvom vi skal have 365, skal du give det til DAY-funktionen og kontrollere for at se, hvor mange der er 13, okay. For eksempel på 92000 række ved du, vi får dagen, vi får hverdagen, kontrollerer for at se, om, DAG = 13, kontrollerer for at se, om UGEDAGEN = FALSK, gang dette * dette,og kun i de tilfælde hvor der er en fredag den 13. ender det som SAND. SUMPRODUCT siger derefter “Tilføj alle dem op”, og det er sådan, vi får de 86, bogstaveligt talt 91895 beregninger + SUM, 91896, der sker inde i denne ene formel, den er skør kraftig! Gå og køb Mike's bog, det er en fantastisk bog, den åbner en hel verden af Excel-formler for dig, og faktisk skal du bare købe begge bøger. Køb min bog, køb Mike's bog, så får du en fantastisk samling, der får dig igennem resten af året.det åbner en hel verden af Excel-formler for dig, og faktisk skal du bare købe begge bøger. Køb min bog, køb Mike's bog, så får du en fantastisk samling, der får dig igennem resten af året.det åbner en hel verden af Excel-formler for dig, og faktisk skal du bare købe begge bøger. Køb min bog, køb Mike's bog, så får du en fantastisk samling, der får dig igennem resten af året.
Okay, så resumé: der er en hemmelig klasse af formler kaldet matrixformler, og matrixformel kan udføre tusinder af mellemberegninger. De kræver normalt, at du trykker på Ctrl + Shift + Enter, men ikke altid, og den bedste bog om matrixformler er Mike Girvins "Ctrl + Shift + Enter" -bog. Okay, så INDIRECT lader dig bruge sammenkædning til at opbygge noget, der ligner en cellereference, og derefter går INDIRECT til denne cellereference. Sammenkædning af to datoer med et kolon vil pege på en række af rækker i Excel og derefter bede om RÆKEN for INDIREKTE for dato1: dato2 vil poppe ud en matrix med mange på hinanden følgende tal, måske 31, måske 365 eller måske 85000. Kontroller hver dato for at se, om WEEKDAY = fredag, kontroller hver dag for at se, om DAY = 13, multiplicer disse to arrays med TRUEs og FALSEs ved hjælp af SUMPRODUCT. I mange tilfældeBrug - til at konvertere SAND / FALSK til 1 og 0 for at tillade SUMPRODUCT at arbejde. Det er en fantastisk formel, jeg skabte den ikke, jeg fandt den på opslagstavlen, da jeg arbejdede igennem den, jeg er som "Wow, det er virkelig sejt!"
Okay, jeg vil takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2026.xlsx
