Excel Array-formler er superkraftige. Du kan erstatte tusenvis av formler med en enkelt formel når du lærer Ctrl + Shift + Enter-trikset. I dag gjør en enkelt matriseformel 86 000 beregninger.
Triskaidekaphobia er frykten for fredag den 13.. Dette emnet vil ikke kurere noe, men det vil vise deg en helt fantastisk formel som erstatter 110 268 formler. I det virkelige liv trenger jeg aldri å telle hvor mange fredag 13. som har skjedd i løpet av livet mitt, men kraften og skjønnheten i denne formelen illustrerer kraften i Excel.
Si at du har en venn som er overtroisk fredag den 13.. Du vil illustrere hvor mange fredag 13-årene vennen din har opplevd.
Sett opp det enkle regnearket nedenfor, med fødselsdato i B1 og =TODAY()B2. Deretter evaluerer en vill formel i B6 hver dag at vennen din har levd for å finne ut hvor mange av disse dagene som var fredag og falt den 13. i måneden. For meg er tallet 86. Ingenting å være redd for.
Forresten, 17/2/1965 har egentlig bursdagen min. Men jeg vil ikke at du skal sende meg et bursdagskort. I stedet for bursdagen min vil jeg at du skal la meg forklare hvordan den fantastiske formelen fungerer, ett lite trinn av gangen.
Har du noen gang brukt INDIRECT-funksjonen? Når du ber om =INDIRECT("C3"), vil Excel gå til C3 og returnere det som er i den cellen. Men INDIRECT er kraftigere når du beregner cellereferansen på farten. Du kan sette opp et premiehjul der noen velger en bokstav mellom A og C og deretter velger et tall mellom 1 og 3. Når du sammenkobler de to svarene, vil du ha en celleadresse, og det som er på den celleadressen er premien . Ser ut som jeg vant en fotobok i stedet for ferieanlegget.
Vet du hvordan Excel lagrer datoer? Når Excel viser deg 2/17/1965, lagrer den 23790 i cellen, fordi 17/2/1965 var den 23790. dagen i det 20. århundre. I hjertet av formelen er en sammenkobling som blir med startdatoen og et kolon og sluttdatoen. Excel bruker ikke den formaterte datoen. I stedet bruker den serienummeret bak kulissene. Så B3&":"&B4blir 23790: 42167. Tro det eller ei, det er en gyldig cellehenvisning. Hvis du ønsket å legge sammen alt i rad 3 til 5, kan du bruke =SUM(3:5). Så når du sender 23790: 42167 til INDIRECT-funksjonen, peker den mot alle radene.
Den neste tingen morderformelen gjør er å be om ROW(23790:42167). Normalt passerer du en enkelt celle: =ROW(D17)er 17. Men i dette tilfellet passerer du tusenvis av celler. Når du ber om ROW(23790:42167)og avslutter formelen med Ctrl + Shift + Enter, returnerer Excel faktisk hvert tall fra 23790, 23791, 23792 og så videre til 42167.
Dette trinnet er det fantastiske trinnet. I dette trinnet går vi fra to tall og "spretter ut" en matrise med 18378 tall. Nå må vi gjøre noe med den rekke svar. Cell B9 i forrige figur teller bare hvor mange svar vi får, noe som er kjedelig, men det beviser at ROW(23790:42167)det returnerer 18378 svar.
La oss dramatisk forenkle det opprinnelige spørsmålet slik at du kan se hva som skjer. I dette tilfellet finner vi antall fredager i juli 2015. Formelen vist nedenfor i B7 gir riktig svar i B6.
I hjertet av formelen er ROW(INDIRECT(B3&":"&B4)). Dette kommer til å returnere de 31 datoene i juli 2015. Men formelen sender deretter de 31 datoene til WEEKDAY(,2)funksjonen. Denne funksjonen returnerer en 1 for mandag, 5 for fredag og så videre. Så det store spørsmålet er hvor mange av de 31 datoene som returnerer en 5 når de sendes til WEEKDAY(,2)funksjonen.
Du kan se formelen beregne i sakte film ved å bruke kommandoen Evaluer formel i fanen Formel på båndet.
Dette etter at INDIRECT konverterer datoene til en radreferanse.
I neste trinn er Excel i ferd med å sende 31 tall til WEEKDAY-funksjonen. Nå, i morderformelen, ville den passere 18 378 tall i stedet for 31.
Her er resultatene av de 31 WEEKDAY-funksjonene. Husk at vi vil telle hvor mange som er 5.
Kontrollerer om den forrige matrisen er 5 returnerer en hel haug med sanne / falske verdier. Det er 5 sanne verdier, en for hver fredag.
Jeg kan ikke vise deg hva som skjer videre, men jeg kan forklare det. Excel kan ikke SUME en haug med sanne og falske verdier. Det er mot reglene. Men hvis du multipliserer disse sanne og falske verdiene med 1, eller hvis du bruker dobbeltnegativt eller N () -funksjonen, konverterer du de sanne verdiene til 1 og de falske verdiene til 0. Send de til SUM eller SUMPRODUCT, så vil du få tellingen av de sanne verdiene.
Her er et lignende eksempel for å telle hvor mange måneder som har en dag 13 i seg. Dette er trivielt å tenke på: Hver måned har en 13., så svaret for et helt år er bedre 12. Excel gjør matematikken, genererer 365 datoer, sender dem alle til DAY () -funksjonen og finner ut hvor mange som slutter opp den 13. i måneden. Svaret er som forventet 12.
Den neste figuren er et regneark som gjør hele logikken den ene morderformelen som ble vist i begynnelsen av dette emnet. Jeg har laget en rad for hver dag jeg har levd. I kolonne B får jeg DAGEN () for den datoen. I kolonne C får jeg datoen WEEKDAY (). Er B lik 13 i kolonne D? I kolonne E, er C = 5? Deretter multipliserer jeg D * E for å konvertere sant / usant til 1/0.
Jeg har skjult mange av radene, men jeg viser deg tre tilfeldige dager i midten som tilfeldigvis er både en fredag og den 13..
Totalen i F18381 er den samme 86 som den opprinnelige formelen min returnerte. Et flott tegn. Men dette regnearket har 110 268 formler. Min originale morderformel gjør hele logikken til disse 110 268 formlene i en enkelt formel.
Vente. Jeg vil avklare. Det er ikke noe magisk i den opprinnelige formelen som blir smart og forkorter logikken. Den opprinnelige formelen gjør virkelig 110 268 trinn, sannsynligvis enda mer, fordi den opprinnelige formelen må beregne ROW () -matrisen to ganger.
Finn en måte å bruke dette på ROW(INDIRECT(Date:Date))i det virkelige liv, og send det til meg på en e-post (pub på dot com). Jeg sender en premie til de første 100 personene som svarer. Sannsynligvis ikke et feriestedopphold. Mer sannsynlig en Big Mac. Men det er slik det går med premier. Mange store Mac-er og ikke mange ferieanlegg.
Jeg så denne formelen første gang lagt ut på Message Board i 2003 av Ekim. Kreditt ble gitt til Harlan Grove. Formelen dukket også opp i Bob Umlas bok This Isn't Excel, It's Magic. Mike Delaney, Meni Porat og Tim Sheets foreslo alle minus / minus-trikset. SUMPRODUCT ble foreslått av Audrey Lynn og Steven White. Takk alle sammen.
Se på video
- Det er en hemmelig klasse med formler som heter Array Formulas.
- En matriseformel kan gjøre tusenvis av mellomberegninger.
- De krever ofte at du trykker på Ctrl + Shift + Enter, men ikke alltid.
- Den beste boka om matriseformler er Mike Girvins Ctrl + Shift + Enter.
- INDIRECT lar deg bruke sammenkobling til å bygge noe som ser ut som en cellereferanse.
- Datoer er pent formatert, men lagres i flere dager siden 1. januar 1900.
- Sammenslåing av to datoer vil peke på et rekke rader i Excel.
- Be om
ROW(INDIRECT(Date1:Date2))viljen "spretter ut" en rekke med mange påfølgende tall - Bruk WEEKDAY-funksjonen til å finne ut om en dato er fredag.
- Hvor mange fredager skjer i juli?
- For å se en formel beregne i sakte film, bruk verktøyet Evaluer formel
- Hvor mange trettendedeler oppstår i år?
- Hvor mange fredag 13. skjedde mellom to datoer?
- Sjekk hver dato for å se om UKEDAGEN er fredag
- Sjekk hver dato for å se om DAGEN er 13
- Multipliser resultatene ved hjelp av SUMPRODUCT
- Bruk - for å konvertere True / False til 1/0
Videoutskrift
Lær Excel fra podcast, episode 2026 - Min favorittformel i hele Excel!
Podcasting av hele boken, klikk på “i” øverst til høyre for å komme til spillelisten!
Ok, det var det 30. emnet i boka, vi var på slutten av formelseksjonen, eller midt i formelseksjonen, og jeg sa at jeg må ta med favorittformelen min gjennom tidene. Dette er bare en fantastisk formel, enten du må telle tallet fredag den 13. eller ikke, det åpner en verden i et helt hemmelig område av Excel som heter Array Formulas! Sett inn en startdato, legg inn en sluttdato, og denne formelen beregner antall fredag 13. dager som skjedde mellom disse to datoene. Det gjør faktisk fem beregninger hver eneste dag mellom disse to datoene, 91895 beregninger + SUM, 91896 beregninger som skjer inne i denne ene lille formelen, ok. Nå, på slutten av denne episoden, vil du bli så fascinert av matriseformler. Jeg vil påpeke,min venn Mike Girvin har den beste boka om matriseformler kalt “Ctrl + Shift” Enter ”, dette er en nylig utskrift med det blå omslaget, pleide å være et gult og grønt omslag. Nå, uansett hvilken du får, er en flott bok, samme innhold i både den gule og den grønne.
Greit så, la oss starte på innsiden av dette, med en formel du kanskje ikke har hørt kalt INDIRECT. INDIRECT lar oss sammenkoble, eller på en eller annen måte bygge litt tekst som ser ut som en cellehenvisning. OK, la oss si at vi har et premiehjul her, og jeg ba deg bare om å velge mellom A, B og C. OK, så du velger dette og velger C, og deretter velger du dette og velger 3, ok, og premien din er et feriestedopphold, fordi det er det som er lagret i C3. Og formelen her er sammenkoblet uansett siden C5 og hva som helst i C6 ved hjelp av & og deretter overfører den til INDIREKTE. Så = INDIREKTE (C5 & C6), i dette tilfellet, er C3, som må være en balansert referanse. INDIRECT sier "Hei, vi skal gå til C3 og returnere svaret fra det, ok?" Tilbake i Lotus 1-2-3 ble dette kalt @@-funksjonen,i Excel omdøpte de det til å være INDIREKT. Ok, så har du det i INDIREKTE, nå er det fantastiske som skjer inne der inne.
Vi har to datoer, hvordan lagrer Excel datoer, 17/2/1965, det er egentlig bare formatering. Hvis vi gikk og så på det faktiske tallet bak det, er det 23790, noe som betyr at det er 23790 dager siden 1/1/1900, og 42167 dager siden 1/1/1980. På en Mac vil det være siden 1/1/1904, så datoene vil være omtrent 3000 av. Greit, det er slik Excel lagrer det, men viser det for oss takket være dette tallformatet som en dato, men hvis vi sammenføyer B3 og a: og B4, vil det faktisk gi oss tallene som er lagret bak kulissene. Så = B3 & ”:” & B4, og hvis vi sender det til INDIREKTE, vil det faktisk peke på alle rader fra 23790 og ned til 42167.
Så det er INDIREKT av B6, jeg ba om RADEN for det, det kommer til å gi meg en hel haug med svar, og for å finne ut hvor mange svar jeg brukte teller, ok. Og for å få dette til å fungere, hvis jeg bare trykker Enter, fungerer det ikke, jeg må holde nede Ctrl og Shift og trykke Enter, og se, det legger til () rundt formelen her oppe. Det forteller Excel å gå inn i superformelmodus, arrayformelmodus og gjøre all matematikken for alt dukket opp fra arrayet, 18378, ok. Så det er et fantastisk triks, indirekte av date1: date2, send det til ROW-funksjonen, og her er et lite eksempel.
Så vi vil bare finne ut hvor mange fredager som skjedde i juli, her er en startdato, her er en sluttdato, og for hver av disse radene skal jeg be om HØYDAGEN. UKEDAG forteller oss hvilken ukedag det er, og her i argumentet 2 vil fredager ha verdien 5. Så jeg ser etter svaret, og vi skal velge denne formelen, gå til Formler, og evaluere formel, og evaluere formel er en fin måte å se en formel bli beregnet i sakte film. Så det er B3, 1. juli, og du ser at det endres til et tall, og så blir vi med i kolon, ikke sant, det er B4, som vil endre seg til et tall, og nå får vi teksten, 42186: 42216. På dette punktet overfører vi det til ROW, og det enkle lille uttrykket vil bli til 31 verdier akkurat her.
Nå, i eksemplet der jeg hadde alt fra 1965 til 2015, ville det poppe ut 86000 verdier, ikke sant, og du vil ikke gjøre det og evaluere formelen, fordi det ville være vanvittig, ok? Men du kan se hva som skjer her med 31, og nå gir jeg de 31 dagene til WEEKDAY-funksjonen, og vi får 3-4-5. Så 3 betyr at det var en onsdag, og deretter 4 betyr at det var en torsdag, og deretter 5 betyr at det var en fredag. Ta alle de 31 verdiene og se om de er = 5 som er en fredag, og vi får en haug med FALSE og SANNER, så onsdag, torsdag, fredag, og så vil 7 celler senere være neste SANNE, Rått!
Greit, så i dette tilfellet har vi 5 SANN og 26 FALSE, For å legge dem sammen, må jeg konvertere FALSE til 0 og SANN til 1, og en veldig vanlig måte å gjøre det på er å bruke - . Greit, dessverre viste det ikke svaret der, hvor vi så en hel haug med 1 og 0, men det er faktisk det som skjer, og så legger SUMPRODUCT det opp og får oss til 5. Her nede, hvis vi vil finne ut hvor mange av 13. i måneden det var i år, fra denne startdatoen til denne sluttdatoen, veldig lik prosess. Selv om vi skal ha 365, send det til DAY-funksjonen, og sjekk for å se hvor mange som er 13, alright. For eksempel på 92000-rad, vet du, vi får dagen, vi får ukedagen, sjekker for å se om, DAG = 13, sjekker for å se om UKEDAGEN = FALSK, multiplisere dette * dette,og bare i tilfeller der det er en fredag den 13., ender det som SANN. SUMPRODUCT sier da “Legg alle disse opp”, og det er slik vi får de 86, bokstavelig talt 91895 beregningene + SUM, 91896 som skjer inne i denne ene formelen, den er gal kraftig! Gå og kjøp Mikes bok, den er en fantastisk bok, den vil åpne en hel verden av Excel-formler for deg, og egentlig bør du bare kjøpe begge bøkene. Kjøp boken min, kjøp Mikes bok, så får du en fantastisk samling som får deg gjennom resten av året.det vil åpne en hel verden av Excel-formler for deg, og egentlig bør du bare kjøpe begge bøkene. Kjøp boken min, kjøp Mikes bok, så får du en fantastisk samling som får deg gjennom resten av året.det vil åpne en hel verden av Excel-formler for deg, og egentlig bør du bare kjøpe begge bøkene. Kjøp boken min, kjøp Mikes bok, så får du en fantastisk samling som får deg gjennom resten av året.
OK, så oppsummering: det er en hemmelig klasse med formler som kalles matriseformler, og matriseformel kan gjøre tusenvis av mellomberegninger. De krever vanligvis at du trykker på Ctrl + Shift + Enter, men ikke alltid, og den beste boken på matriseformler er Mike Girvins "Ctrl + Shift + Enter" -bok. OK, så INDIRECT lar deg bruke sammenkobling til å bygge noe som ser ut som en cellereferanse, og deretter går INDIRECT til den cellereferansen. Sammenkobling av to datoer med et kolon vil peke på et rekke rader i Excel, og deretter be om RADEN for INDIREKT av dato1: date2 vil vise en rekke med mange påfølgende tall, kanskje 31, kanskje 365 eller kanskje 85000. hver dato for å se om HUDDAGEN = Fredag, sjekk hver dag for å se om DAGEN = 13, multipliser de to matriser med SANT og FALSE ved å bruke SUMPRODUCT. I mange tilfellerBruk - for å konvertere SANN / FALSK til 1 og 0 for å la SUMPRODUCT fungere. Det er en fantastisk formel, jeg skapte den ikke, jeg fant den på oppslagstavlen, mens jeg jobbet gjennom den, er jeg som "Wow, dette er veldig kult!"
OK, jeg vil takke deg for at du var innom, vi sees neste gang for en ny netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2026.xlsx
