Formulele Excel Array sunt super puternice. Puteți înlocui mii de formule cu o singură formulă odată ce ați învățat trucul Ctrl + Shift + Enter. Astăzi, o singură formulă matrice face 86.000 de calcule.
Triskaidekaphobia este frica de vineri 13. Acest subiect nu va vindeca nimic, dar vă va arăta o formulă absolut uimitoare care înlocuiește 110.268 formule. În viața reală, nu trebuie să număr niciodată câte zile de vineri au avut loc în viața mea, dar puterea și frumusețea acestei formule ilustrează puterea Excel.
Spuneți că aveți un prieten superstițios despre Vineri 13. Doriți să ilustrați câte Vineri 13 a trăit prietenul dvs.
Configurați foaia de lucru simplă de mai jos, cu data nașterii în B1 și =TODAY()B2. Apoi, o formulă sălbatică din B6 evaluează în fiecare zi că prietenul tău a fost în viață pentru a afla câte din acele zile au fost vineri și au căzut pe 13 a lunii. Pentru mine, numărul este 86. Nimic de care să nu-ți fie frică.
Apropo, 17.02.1965 chiar este ziua mea de naștere. Dar nu vreau să-mi trimiteți o felicitare. În schimb, pentru ziua mea de naștere, vreau să mă lăsați să explic cum funcționează acea formulă uimitoare, pas cu pas.
Ați folosit vreodată funcția INDIRECTĂ? Când cereți =INDIRECT("C3"), Excel va merge la C3 și va returna tot ce se află în acea celulă. Dar INDIRECT este mai puternic atunci când calculați referința celulei din mers. Puteți configura o roată de premiu în care cineva alege o literă între A și C și apoi alege un număr între 1 și 3. Când concatenați cele două răspunsuri, veți avea o adresă de celulă și orice este la adresa respectivă de celulă este premiul . Se pare că am câștigat o carte foto în locul sejurului în stațiune.
Știți cum stochează datele Excel? Când Excel vă arată 17.02.1965, acesta stochează 23790 în celulă, deoarece 17.02.1965, a fost ziua 23790th a secolului XX. În centrul formulei se află o concatenare care unește data de începere și două puncte și data de sfârșit. Excel nu folosește data formatată. În schimb, folosește numărul de serie din culise. Așa B3&":"&B4devine 23790: 42167. Credeți sau nu, aceasta este o referință validă pentru celule. Dacă doriți să adăugați totul în rândurile de la 3 la 5, puteți utiliza =SUM(3:5). Deci, când treceți 23790: 42167 la funcția INDIRECT, acesta indică toate rândurile.
Următorul lucru pe care îl face formula ucigașului este să ceară ROW(23790:42167). În mod normal, treceți o singură celulă: =ROW(D17)este 17. Dar, în acest caz, treceți mii de celule. Când solicitați ROW(23790:42167)și terminați formula cu Ctrl + Shift + Enter, Excel returnează de fapt fiecare număr de la 23790, 23791, 23792 și așa mai departe până la 42167.
Acest pas este pasul uimitor. În acest pas, trecem de la două numere și „scoatem” o serie de 18378 de numere. Acum, trebuie să facem ceva cu acea gamă de răspunsuri. Celula B9 din figura precedentă contează doar câte răspunsuri obținem, ceea ce este plictisitor, dar dovedește că ROW(23790:42167)returnează 18378 de răspunsuri.
Să simplificăm dramatic întrebarea inițială, astfel încât să puteți vedea ce se întâmplă. În acest caz, vom găsi numărul de vineri din iulie 2015. Formula prezentată mai jos în B7 oferă răspunsul corect în B6.
În centrul formulei se află ROW(INDIRECT(B3&":"&B4)). Aceasta va returna cele 31 de date din iulie 2015. Dar formula transmite apoi cele 31 de date WEEKDAY(,2)funcției. Această funcție va reveni la 1 pentru luni, 5 pentru vineri și așa mai departe. Deci, marea întrebare este cât de multe dintre aceste 31 de date returnează un 5 când sunt trecute la WEEKDAY(,2)funcție.
Puteți urmări formula calculată în mișcare lentă utilizând comanda Evaluare formulă din fila Formula din panglică.
Acest lucru se întâmplă după ce INDIRECT convertește datele într-o referință de rând.
În pasul următor, Excel este pe cale să treacă 31 de numere funcției WEEKDAY. Acum, în formula ucigașului, ar trece 18.378 de numere în loc de 31.
Iată rezultatele celor 31 de funcții WEEKDAY. Amintiți-vă, vrem să numărăm câți sunt 5.
Verificând dacă matricea anterioară este 5, se returnează o grămadă întreagă de valori True / False. Există 5 valori adevărate, una pentru fiecare vineri.
Nu vă pot arăta ce se va întâmpla în continuare, dar vă pot explica. Excel nu poate SUMA o grămadă de valori adevărate și false. Este împotriva regulilor. Dar dacă înmulțiți acele valori adevărate și false cu 1 sau dacă utilizați funcția dublu-negativă sau N (), convertiți valorile adevărate la 1 și valorile false la 0. Trimiteți-le la SUM sau SUMPRODUCT și veți face obțineți numărul valorilor adevărate.
Iată un exemplu similar pentru a calcula câte luni au o zi 13 în ele. Acest lucru este banal să ne gândim: fiecare lună are un al 13-lea, deci răspunsul pentru un an întreg este mai bun 12. Excel face calcule, generează 365 de date, le trimite pe toate la funcția DAY () și află cât de multe finalizează sus pe 13 a lunii. Răspunsul, așa cum era de așteptat, este 12.
Următoarea figură este o foaie de lucru care face toată logica formulei unui singur ucigaș prezentată la începutul acestui subiect. Am creat un rând pentru fiecare zi în care am trăit. În coloana B, primesc ZIUA () acelei date. În coloana C, primesc ZIUA SĂPTĂMÂNĂ () a datei. În coloana D, B este egal cu 13? În coloana E, este C = 5? Înmulțesc apoi D * E pentru a converti True / False la 1/0.
Am ascuns o mulțime de rânduri, dar îți arăt trei zile aleatorii la mijloc, care se întâmplă să fie atât vineri, cât și 13.
Totalul în F18381 este același 86 pe care mi l-a returnat formula inițială. Un semn minunat. Dar această foaie de lucru are 110.268 de formule. Formula mea originală ucigașă face toată logica acestor 110.268 formule într-o singură formulă.
Aștepta. Vreau să clarific. Nu există nimic magic în formula originală care devine inteligent și scurtează logica. Această formulă originală face cu adevărat 110.268 de pași, probabil chiar mai mult, deoarece formula originală trebuie să calculeze matricea ROW () de două ori.
Găsiți o modalitate de a folosi acest lucru ROW(INDIRECT(Date:Date))în viața reală și trimite-mi-o într-un e-mail (pub la dot com). Voi trimite un premiu primelor 100 de persoane pentru a răspunde. Probabil că nu un sejur în stațiune. Mai probabil un Big Mac. Dar așa merge cu premiile. O mulțime de Big Mac-uri și nu multe sejururi în stațiune.
Am văzut prima dată această formulă postată pe Mesajul în 2003 de Ekim. Credit a fost acordat lui Harlan Grove. Formula a apărut și în cartea lui Bob Umlas This Is not Excel, It's Magic. Mike Delaney, Meni Porat și Tim Sheets au sugerat toți trucul minus / minus. SUMPRODUCT a fost sugerat de Audrey Lynn și Steven White. Va multumesc tuturor.
Urmăriți videoclipul
- Există o clasă secretă de formule numite Formule Array.
- O formulă matrice poate face mii de calcule intermediare.
- Adesea vă solicită să apăsați Ctrl + Shift + Enter, dar nu întotdeauna.
- Cea mai bună carte despre formule matrice este Ctrl + Shift + Enter a lui Mike Girvin.
- INDIRECT vă permite să utilizați concatenarea pentru a construi ceva care arată ca o referință de celulă.
- Datele sunt frumos formatate, dar sunt stocate în mai multe zile de la 1 ianuarie 1900.
- Concatenarea a două date va indica un interval de rânduri în Excel.
- Solicitarea pentru
ROW(INDIRECT(Date1:Date2))„va scoate” o serie de numeroase numere consecutive - Folosind funcția WEEKDAY pentru a afla dacă o dată este vineri.
- Câte vineri au loc în acest iulie?
- Pentru a urmări o formulă calculată în mișcare lentă, utilizați instrumentul Evaluare formulă
- Câte treisprezece se întâmplă anul acesta?
- Câte Vineri 13 s-au întâmplat între două întâlniri?
- Verificați fiecare dată pentru a vedea dacă SĂPTĂMÂNA este vineri
- Verificați fiecare dată pentru a vedea dacă ZIUA este 13
- Înmulțiți aceste rezultate folosind SUMPRODUCT
- Utilizați - pentru a converti True / False la 1/0
Transcriere video
Aflați Excel din podcast, episodul 2026 - Formula mea preferată în întregul Excel!
Podcasting această carte întreagă, faceți clic pe „i” în colțul din dreapta sus pentru a accesa lista de redare!
Bine, a fost al 30-lea subiect din carte, am fost cam la sfârșitul secțiunii cu formule sau în mijlocul secțiunii cu formula și am spus că trebuie să includ formula mea preferată din toate timpurile. Aceasta este doar o formulă uimitoare, indiferent dacă trebuie să numărați numărul vineri 13 sau nu, acesta deschide o lume într-o întreagă zonă secretă a Excel numită Array Formulas! Introduceți o dată de început, introduceți o dată de încheiere, iar această formulă calculează numărul de vineri 13 care a avut loc între aceste două date. De fapt, face cinci calcule în fiecare zi între cele două date, 91895 calcule + SUM, 91896 calcule care se întâmplă în interiorul acestei mici formule, bine. Acum, până la sfârșitul acestui episod, veți fi atât de intrigați de formulele matrice. Vreau să subliniez,prietenul meu Mike Girvin are cea mai bună carte despre formule matrice numite „Ctrl + Shift” Enter ”, aceasta este o tipărire recentă cu coperta albastră, care a fost o copertă galbenă și verde. Acum, oricare dintre acestea, este o carte grozavă, cu același conținut atât în cea galbenă, cât și în cea verde.
Bine, deci, să începem din interior, cu o formulă pe care poate nu ați auzit-o numită INDIRECT. INDIRECT ne permite să concatenăm sau, într-un fel, să construim un text care să arate ca o referință de celulă. Bine, deci, să presupunem că avem o roată de premii aici și doar v-am rugat să alegeți între A, B și C. Bine, deci alegeți acest lucru și alegeți C, apoi alegeți acest lucru și alegeți 3, bine, și premiul dvs. este un sejur de stațiune, pentru că asta este stocat în C3. Și formula de aici este concatenată împreună, indiferent de C5 și orice este în C6, folosind & și apoi trecând asta la INDIRECT. Deci = INDIRECT (C5 și C6), în acest caz, este C3, care trebuie să fie o referință echilibrată. INDIRECT spune „Hei, vom merge la C3 și vom întoarce răspunsul, bine?” Înapoi în Lotus 1-2-3, aceasta a fost numită funcția @@,în Excel l-au redenumit ca INDIRECT. Bine, deci aveți în INDIRECT, acum iată lucrul uimitor care se întâmplă în interior.
Avem două date, cum stochează datele Excel, 17.02.1965, asta este doar formatare. Dacă ne-am dus și ne-am uitat la numărul real din spatele acestuia, acesta este 23790, ceea ce înseamnă că sunt 23790 zile de la 01/01/1900 și 42167 zile de la 01/01/1980. Pe un Mac va fi începând cu 01/01/1904, deci datele vor fi de aproximativ 3000 de reducere. Bine, așa îl stochează Excel, totuși ni-l arată, datorită acestui format numeric ca dată, dar dacă am concatena împreună B3 și a: și B4, ne-ar da de fapt numerele stocate în culise. Deci = B3 & ”:” & B4, iar dacă am trece asta către INDIRECT, de fapt va indica toate rândurile de la 23790 până la 42167.
Deci, există INDIRECTUL lui B6, am cerut rândul, care îmi va oferi o grămadă de răspunsuri și îmi dau seama câte răspunsuri am folosit, bine. Și pentru a face acest lucru să funcționeze, dacă apăs doar Enter, nu funcționează, trebuie să țin apăsate Ctrl și Shift și să apasă Enter și să vedem, care adaugă () în jurul formulei aici. Îi spune lui Excel să intre în modul super formulă, modul formula matrice și să facă toate calculele pentru tot ce a ieșit din matricea respectivă, 18378, bine. Deci, acesta este un truc uimitor, indirect de date1: data2, treceți-l la funcția ROW și iată un mic exemplu.
Așadar, vrem doar să ne dăm seama câte vineri au avut loc în acest iulie, iată o dată de începere, iată o dată de sfârșit și, pentru fiecare dintre aceste rânduri, voi cere SĂPTĂMÂNA. WEEKDAY ne spune ce zi a săptămânii este și aici, în argumentul, 2 vineri vor avea o valoare de 5. Deci, caut răspunsul și vom alege această formulă, accesați Formule, și Evaluați formula și Evaluați formula este o modalitate excelentă de a urmări o formulă calculată în mișcare lentă. Deci, există B3, 1 iulie, și vedeți că se schimbă un număr, iar apoi ne alăturăm punctelor, dreapta, există B4, care se va schimba într-un număr, iar acum primim textul, 42186: 42216. În acest moment, trecem asta la ROW, iar acea mică expresie simplă se va transforma în 31 de valori chiar aici.
Acum, în exemplul în care am avut totul, din 1965 până în 2015, ar apărea 86000 de valori, corect, și nu vrei să faci asta și să evaluezi formula, pentru că ar fi un fel de nebun, bine? Dar puteți vedea ce se întâmplă aici cu 31 și acum trec aceste 31 de zile la funcția WEEKDAY și obținem 3-4-5. Deci 3 înseamnă că a fost o miercuri, iar apoi 4 înseamnă că a fost o joi, iar apoi 5 înseamnă că a fost o vineri. Luați toate cele 31 de valori și vedeți dacă sunt = 5, care este o vineri, și vom obține o grămadă de FALSE și ADEVĂRATE, așa că miercuri, joi, vineri și apoi 7 celule mai târziu ar fi următorul ADEVĂRAT, minunat!
Bine, deci, în acest caz, avem 5 ADEVĂRATURI și 26 DE FALSE. Pentru a adăuga acestea, trebuie să convertesc FALSUL la 0 și ADevărurile la 1 și un mod foarte obișnuit de a face acest lucru este să folosiți . Bine, din păcate, nu a arătat răspunsul acolo, unde am văzut o grămadă întreagă de 1 și 0, dar de fapt așa se întâmplă, apoi SUMPRODUCT îl adaugă și ne duce la 5. Aici jos, dacă vrem să aflați câte din 13 ale lunii au fost anul acesta, de la această dată de început până la această dată de sfârșit, proces foarte similar. Deși vom avea 365, treceți asta la funcția DAY și verificați pentru a vedea câte sunt 13, bine. Pentru exemplul de rând 92000, știți, primim ziua, primim ziua săptămânii, verificăm dacă, DAY = 13, verificăm dacă WEEKDAY = FALSE, înmulțind acest * acest lucru,și numai în cazurile în care este o zi de vineri 13, aceasta ajunge ca ADEVĂRAT. SUMPRODUCT spune apoi „Adăugați-le pe toate” și așa obținem calculele 86, literal 91895 + SUM, 91896 care se întâmplă în interiorul acestei formule, este nebun puternic! Du-te să cumperi cartea lui Mike, este o carte uimitoare, îți va deschide o întreagă lume de formule Excel și, de fapt, ar trebui să cumperi ambele cărți. Cumpărați cartea mea, cumpărați cartea lui Mike și veți avea o colecție extraordinară care vă va ajuta să rămâneți în restul anului.vă va deschide o întreagă lume de formule Excel și, de fapt, ar trebui să cumpărați ambele cărți. Cumpărați cartea mea, cumpărați cartea lui Mike și veți avea o colecție extraordinară care vă va ajuta să rămâneți în restul anului.vă va deschide o întreagă lume de formule Excel și, de fapt, ar trebui să cumpărați ambele cărți. Cumpărați cartea mea, cumpărați cartea lui Mike și veți avea o colecție extraordinară care vă va ajuta să rămâneți în restul anului.
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!”
Bine, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2026.xlsx
