Excel Array Formule su izuzetno moćne. Jednom kada naučite trik Ctrl + Shift + Enter možete zamijeniti tisuće formula jednom formulom. Danas jedna formula niza vrši 86 000 izračuna.
Triskaidekafobija je strah od petka 13.. Ova tema neće ništa izliječiti, ali pokazat će vam apsolutno nevjerojatnu formulu koja zamjenjuje 110.268 formula. U stvarnom životu nikada ne moram računati koliko se petka 13. dogodilo u mom životu, ali snaga i ljepota ove formule ilustriraju snagu Excela.
Recite da imate prijatelja koji je praznovjeran oko petka 13.. Želite ilustrirati koliko je petka trinaestog proživio vaš prijatelj.
Postavite jednostavni radni list u nastavku, s datumima rođenja u B1 i =TODAY()u B2. Tada divlja formula u B6 procjenjuje svaki dan da je vaš prijatelj živ kako bi shvatila koliko je tih dana bilo petka i palo 13. u mjesecu. Za mene je taj broj 86. Ničega čega se ne treba bojati.
Inače, 17.2.1965. Stvarno je moj rođendan. Ali ne želim da mi pošalješ rođendansku čestitku. Umjesto toga, za rođendan, želim da mi dopustite da objasnim kako funkcionira ta nevjerojatna formula, jedan po jedan mali korak.
Jeste li ikada koristili funkciju INDIRECT? Kada zatražite =INDIRECT("C3"), Excel će prijeći na C3 i vratiti sve što se nalazi u toj ćeliji. Ali INDIRECT je snažniji kada u trenutku izračunate referencu stanice. Možete postaviti nagradni kotačić gdje netko odabere slovo između A i C, a zatim odabere broj između 1 i 3. Kada spojite dva odgovora, imat ćete adresu stanice, a sve što se nalazi na toj adresi ćelije je nagrada . Izgleda da sam umjesto boravka u odmaralištu osvojio knjigu fotografija.
Znate li kako Excel pohranjuje datume? Kad vam Excel prikaže 17.2.1965., U ćeliju pohranjuje 23790, jer je 17.2.1965. Bio 23790. dan 20. stoljeća. U središtu formule je spajanje koje spaja datum početka i dvotačku i datum završetka. Excel ne koristi formatirani datum. Umjesto toga, koristi serijski broj iza kulisa. Tako B3&":"&B4postaje 23790: 42167. Vjerovali ili ne, to je valjana referenca stanice. Ako želite zbrojiti sve u redovima od 3 do 5, možete koristiti =SUM(3:5). Dakle, kada prenesete 23790: 42167 u INDIRECT funkciju, ona usmjerava na sve retke.
Sljedeće što formula ubojice čini je tražiti ROW(23790:42167). Obično prođete jednu ćeliju: =ROW(D17)je 17. Ali u ovom slučaju prolazite kroz tisuće stanica. Kada zatražite ROW(23790:42167)i dovršite formulu Ctrl + Shift + Enter, Excel zapravo vraća svaki broj od 23790, 23791, 23792 i tako dalje do 42167.
Ovaj korak je nevjerojatan korak. U ovom koraku idemo s dva broja i "iskačemo" niz od 18378 brojeva. Sad moramo nešto poduzeti s tim nizom odgovora. Ćelija B9 prethodne slike samo broji koliko odgovora dobivamo, što je dosadno, ali dokazuje da ROW(23790:42167)vraća 18378 odgovora.
Dramatično pojednostavnimo izvorno pitanje kako biste mogli vidjeti što se događa. U ovom ćemo slučaju pronaći broj petka u srpnju 2015. Formula prikazana u nastavku u B7 daje točan odgovor u B6.
U srcu formule je ROW(INDIRECT(B3&":"&B4)). To će vratiti 31 datum u srpnju 2015. Ali formula zatim prosljeđuje tih 31 datuma WEEKDAY(,2)funkciji. Ova će funkcija vratiti 1 za ponedjeljak, 5 za petak itd. Dakle, veliko je pitanje koliko od tih 31 datuma vraća 5 kada se proslijedi WEEKDAY(,2)funkciji.
Izračun formule možete gledati usporeno pomoću naredbe Procijeni formulu na kartici Formula na vrpci.
To je nakon što INDIRECT pretvori datume u referencu retka.
U sljedećem koraku Excel će proslijediti 31 broj funkciji WEEKDAY. Sada bi u formuli ubojice prošlo 18.378 brojeva umjesto 31.
Ovdje su rezultati funkcija od 31 WEEKDAY. Zapamtite, želimo računati koliko je 5.
Provjera je li prethodni niz 5 vraća cijelu gomilu vrijednosti True / False. Postoji 5 istinskih vrijednosti, po jedna za svaki petak.
Ne mogu vam pokazati što se dalje događa, ali mogu to objasniti. Excel ne može ZBIRATI hrpu točnih i netačnih vrijednosti. To je protiv pravila. Ali ako pomnožite one True i False vrijednosti s 1 ili ako koristite dvostruku negativu ili funkciju N (), pretvorite True vrijednosti u 1, a False vrijednosti u 0. Pošaljite ih na SUM ili SUMPRODUCT i dobit ćete dobiti broj točnih vrijednosti.
Evo sličnog primjera za brojanje koliko mjeseci ima 13 dana. O ovome je trivijalno razmišljati: svaki mjesec ima 13., pa bi odgovor za cijelu godinu trebao biti 12. Excel radi matematiku, generira 365 datuma, šalje ih u funkciju DAY () i utvrđuje koliko ih završava do 13. u mjesecu. Odgovor je, očekivano, 12.
Sljedeća je slika radni list koji čini svu logiku formule jednog ubojice prikazanu na početku ove teme. Stvorio sam red za svaki dan da sam živ. U stupac B dobivam DAY () tog datuma. U stupac C dobivam WEEKDAY () datuma. Je li B u stupcu D jednako 13? Je li u stupcu E C = 5? Zatim pomnožim D * E kako bih pretvorio True / False u 1/0.
Sakrio sam puno redaka, ali pokazujem vam tri slučajna dana u sredini koja su i petak i 13.
Ukupni iznos u F18381 jednak je 86 koji je vratila moja izvorna formula. Sjajan znak. Ali ovaj radni list ima 110.268 formula. Moja originalna formula ubojica čini svu logiku ovih 110.268 formula u jednoj formuli.
Čekati. Želim pojasniti. U izvornoj formuli nema ničeg čarobnog što bi postalo pametno i skratilo logiku. Ta izvorna formula stvarno radi 110.268 koraka, vjerojatno i više, jer izvorna formula mora dvaput izračunati ROW () niz.
Pronađite način da to upotrijebite ROW(INDIRECT(Date:Date))u stvarnom životu i pošaljite mi to e-poštom (pub na dot com). Poslat ću nagradu prvih 100 ljudi da odgovore. Vjerojatno nije odmaralište. Vjerojatnije Big Mac. Ali tako to ide s nagradama. Puno Big Maca i malo odmarališta.
Ovu sam formulu prvi put vidio na Emisijskoj ploči 2003. godine. Zasluge su dodijeljene Harlan Groveu. Formula se pojavila i u knjizi Boba Umlasa This is not Excel, It's Magic. Mike Delaney, Meni Porat i Tim Sheets predložili su trik minus / minus. SUMPRODUCT su predložili Audrey Lynn i Steven White. Hvala svima.
Gledaj video
- Postoji tajna klasa formula pod nazivom Array Formulas.
- Formula niza može izvršiti tisuće posrednih izračuna.
- Često zahtijevaju da pritisnete Ctrl + Shift + Enter, ali ne uvijek.
- Najbolja knjiga o formulama niza je Ctrl + Shift + Enter Mikea Girvina.
- INDIRECT vam omogućuje da koristite spajanje za izgradnju nečega što izgleda kao referenca na ćeliju.
- Datumi su lijepo oblikovani, ali se pohranjuju kao broj dana od 1. siječnja 1900. godine.
- Spajanje dva datuma ukazat će na niz redaka u programu Excel.
- Tražeći
ROW(INDIRECT(Date1:Date2))volju "iskočiti" niz mnogih uzastopnih brojeva - Pomoću funkcije WEEKDAY utvrdite je li datum petak.
- Koliko se petka događa u ovom srpnju?
- Da biste gledali uspoređenu formulu kako biste izračunali formulu, upotrijebite alat za procjenu formule
- Koliko se 13-ih dogodi ove godine?
- Koliko se petka 13. dogodilo između dva datuma?
- Provjerite svaki datum da li je WEEKDAY petak
- Provjerite svaki datum da li je DAN 13
- Pomnožite te rezultate pomoću SUMPRODUCT-a
- Upotrijebi - za pretvaranje True / False u 1/0
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2026 - Moja omiljena formula u cijelom Excelu!
Podcastajući cijelu ovu knjigu, kliknite "i" u gornjem desnom kutu da biste došli do popisa za reprodukciju!
Dobro, bila je to 30. tema u knjizi, bili smo nekako na kraju odjeljka formule, ili usred odjeljka formule, i rekao sam da moram uključiti svoju omiljenu formulu svih vremena. Ovo je samo nevjerojatna formula, bez obzira trebate li računati broj u petak 13. ili ne, ona otvara svijet u cijelo tajno područje Excela zvano Array Formulas! Stavite datum početka, datum završetka i ova formula izračunava broj petka 13. koji se dogodio između ta dva datuma. Zapravo radi pet izračuna svakog dana između ta dva datuma, 91895 izračuna + SUM, 91896 izračuna koji se događaju unutar ove male formule, u redu. Sad, do kraja ove epizode toliko ćete se zaintrigirati formulama niza. Želim naglasiti,moj prijatelj Mike Girvin ima najbolju knjigu o formulama niza pod nazivom "Ctrl + Shift" Enter ", ovo je nedavni tisak s plavom koricom, nekada žutom i zelenom koricom. Sad, koja god da se nađe, izvrsna je knjiga, istog sadržaja i u žutoj i u zelenoj.
U redu, krenimo s unutarnje strane ovoga, s formulom koju možda niste čuli pod nazivom INDIRECT. INDIRECT nam omogućuje spajanje ili na neki način izgradnju dijela teksta koji izgleda poput reference na ćeliju. U redu, recimo da ovdje imamo nagradni kotač, a ja sam vas samo zamolio da odaberete između A, B i C. U redu, pa odaberite ovo i odaberite C, a zatim odaberite ovo i odaberite 3, u redu, i svoju nagradu je odmaralište, jer je to ono što je pohranjeno u C3. I ovdje se formula spaja zajedno od C5 i onoga što je u C6 pomoću & i zatim to prosljeđuje INDIREKTU. Dakle, = INDIRECT (C5 i C6), u ovom slučaju, je C3, to mora biti uravnotežena referenca. INDIRECT kaže "Hej, idemo na C3 i vratit ćemo odgovor s toga, u redu?" Još u Lotusu 1-2-3 to se zvalo @@ funkcija,u Excelu su ga preimenovali u INDIRECT. U redu, znači, imate INDIREKT, evo nevjerojatne stvari koja se događa tamo.
Imamo dva datuma, kako Excel pohranjuje datume, 17.2.1965., To je zapravo samo formatiranje. Ako bismo išli pogledati stvarni broj iza toga, on je 23790, što znači da je od 1.1.1900. Do 23790 dana, a od 1.1.1980. 42167 dana. Na Macu će to biti od 1.1.1904., Tako da će datumi biti oko 3000 popusta. U redu, tako ga Excel pohranjuje, prikazuje nam ga, ipak, zahvaljujući ovom formatu brojeva kao datum, ali ako bismo spojili B3 i a: i B4, to bi nam zapravo dalo brojeve pohranjene iza kulisa. Dakle = B3 & ”:” & B4, a ako bismo to proslijedili INDIRECT-u, to će zapravo pokazati na sve retke od 23790 do 42167.
Dakle, postoji INDIREKT B6, tražio sam RED toga, to će mi dati cijelu hrpu odgovora i shvatiti koliko odgovora sam upotrijebio, u redu. A da bi ovo uspjelo, ako samo pritisnem Enter, to neće uspjeti, moram pritisnuti Ctrl i Shift i pritisnuti Enter i vidjeti, to dodaje () oko formule ovdje gore. To govori Excelu da pređe u način super formule, način formule niza i napravi sve izračune za sve što je iskočilo iz tog polja, 18378, u redu. Dakle, to je nevjerojatan trik, neizravan date1: date2, proslijedite to funkciji ROW, i evo malog primjera.
Dakle, samo želimo otkriti koliko se petka dogodilo u ovom srpnju, evo datuma početka, datuma završetka i za svaki od tih redova tražit ću TJEDAN. WEEKDAY nam govori koji je dan u tjednu, a ovdje u argumentu 2, petak će imati vrijednost 5. Dakle, tražim odgovor, a mi ćemo odabrati ovu formulu, idemo na Formule, i Evaluate Formula, a Evaluate Formula izvrstan je način za promatranje formule kako se izračunava usporeno. Dakle, tu je B3, 1. srpnja, i vidite da se to mijenja u broj, a zatim se pridružujemo dvotočki, točno, tu je B4, koji će se promijeniti u broj, i sada dobivamo tekst, 42186: 42216. U ovom trenutku prenosimo to u RED i taj će se mali mali izraz pretvoriti u 31 vrijednost upravo ovdje.
Sad, u primjeru u kojem sam imao sve od 1965. do 2015., ispalo bi 86000 vrijednosti, zar ne, a vi to ne želite raditi i procijeniti formulu, jer bi to bilo nekako suludo, u redu? Ali možete vidjeti što se ovdje događa s 31, a sada tih 31 dan prelazim na funkciju WEEKDAY i dobivamo 3-4-5. Dakle 3 znači da je bila srijeda, a onda 4 znači da je bio četvrtak, a onda 5 znači da je bio petak. Uzmite svih onih 31 vrijednosti i provjerite jesu li = 5, što je petak, a mi ćemo dobiti hrpu NETOČNIH I ISTINITIH, tako da će srijeda, četvrtak, petak, a zatim 7 ćelija kasnije biti sljedeća ISTINA, super!
U redu, dakle, u ovom slučaju imamo 5 ISTINA i 26 NETOČNIH. Da bih ih zbrojio, moram pretvoriti NETOČNO u 0, a ISTINICE u 1, a vrlo čest način da to učinim je korištenje . U redu, nažalost, tamo se nije pokazao odgovor, gdje smo vidjeli čitavu hrpu 1 i 0, ali to se zapravo događa, a onda se SUMPRODUCT zbraja i dovodi do 5. Ovdje dolje, ako želimo shvatite koliko je bilo 13. u mjesecu ove godine, od ovog datuma početka do ovog datuma završetka, vrlo sličan postupak. Iako ćemo imati 365, proslijedite to funkciji DAY i provjerite koliko ih je 13, u redu. Za primjer 92000 redaka, znate, dobivamo dan, dobivamo dan u tjednu, provjeravamo je li DAY = 13, provjeravamo je li WEEKDAY = FALSE, množeći ovo * ovo,i samo u slučajevima kada je petak 13. to završava kao ISTINA. SUMPRODUCT tada kaže "Zbroji sve one", i tako dobivamo 86, doslovno 91895 izračuna + SUM, 91896 koji se događaju unutar ove jedne formule, to je ludo moćno! Idite, kupite Mikeovu knjigu, to je nevjerojatna knjiga, otvorit će vam čitav svijet Excel formula, i zapravo, trebali biste kupiti obje knjige. Kupite moju knjigu, kupite Mikeovu knjigu i imat ćete nevjerojatnu kolekciju koja će vas provesti kroz ostatak godine.otvorit će vam čitav svijet Excel formula, a zapravo biste trebali kupiti obje knjige. Kupite moju knjigu, kupite Mikeovu knjigu i imat ćete nevjerojatnu kolekciju koja će vas provesti kroz ostatak godine.otvorit će vam čitav svijet Excel formula, a zapravo biste trebali kupiti obje knjige. Kupite moju knjigu, kupite Mikeovu knjigu i imat ćete nevjerojatnu kolekciju koja će vas provesti kroz ostatak godine.
U redu, rezimiraj: postoji tajna klasa formula koja se naziva matrične formule i matrična formula može izvršiti tisuće posrednih izračuna. Obično zahtijevaju da pritisnete Ctrl + Shift + Enter, ali ne uvijek, a najbolja knjiga o formulama niza je knjiga "Ctrl + Shift + Enter" Mikea Girvina. U redu, tako da INDIRECT omogućuje upotrebu spajanja za izgradnju nečega što izgleda kao referenca na ćeliju, a zatim INDIRECT ide na tu referencu na ćeliju. Spajanje dva datuma dvotačkom ukazat će na raspon redaka u Excelu, a zatim će se tražiti RED INDIREKTA datuma1: datum2 iskočit će niz uzastopnih brojeva, možda 31, možda 365 ili možda 85000. Provjerite svaki datum da biste vidjeli je li WEEKDAY = petak, provjerite svaki dan da li je DAY = 13, pomnožite ta dva niza TRUE i FALSEs koristeći SUMPRODUCT. U mnogim smo slučajevimaUpotrijebit ću - za pretvaranje TRUE / FALSE u 1 i 0 kako bi SUMPRODUCT mogao raditi. To je sjajna formula, nisam je stvorio, pronašao sam je na oglasnoj ploči, dok sam je obrađivao, poput "Wow, ovo je stvarno super!"
U redu, želim vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedan prijenos od!
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2026.xlsx
