Formule Excel Array so izjemno zmogljive. Ko se naučite trika Ctrl + Shift + Enter, lahko na tisoče formul zamenjate z eno samo. Danes ena matrična formula naredi 86.000 izračunov.
Triskaidekafobija je strah pred petkom 13. Ta tema ne bo ničesar pozdravila, vendar vam bo pokazala popolnoma neverjetno formulo, ki nadomešča 110.268 formul. V resničnem življenju mi nikoli ni treba šteti, koliko petka 13. se je zgodilo v mojem življenju, vendar moč in lepota te formule ponazarja moč Excela.
Recimo, da imate prijatelja, ki je vraževeren v petek, 13. dne. Ponazoriti želite, koliko petka 13. dne je preživel vaš prijatelj.
Spodaj nastavite preprost delovni list z datumom rojstva v B1 in =TODAY()B2. Nato divja formula v B6 vsak dan oceni, ali je bil vaš prijatelj živ, da ugotovi, koliko teh dni je bilo v petek in je padlo 13. v mesecu. Zame je številka 86. Ničesar se ne bi bilo treba bati.
Mimogrede, 17.2.1965 je res moj rojstni dan. Ampak nočem, da mi pošlješ rojstnodnevno voščilnico. Namesto tega za svoj rojstni dan želim, da mi razložite, kako deluje ta neverjetna formula, en majhen korak naenkrat.
Ste že kdaj uporabili funkcijo INDIRECT? Ko to zahtevate =INDIRECT("C3"), Excel preide na C3 in vrne vse, kar je v tej celici. Toda INDIRECT je močnejši, ko sproti izračunate referenco celice. Lahko postavite nagradno kolo, kjer nekdo izbere črko med A in C in nato izbere številko med 1 in 3. Ko združite oba odgovora, boste dobili naslov celice in kar je na tem naslovu celice, je nagrada . Izgleda, da sem namesto bivanja v letovišču dobil knjigo fotografij.
Ali veste, kako Excel shranjuje datume? Ko vam Excel prikaže 17.2.1965, v celico shrani 23790, ker je bil 17.2.1965 23790. dan 20. stoletja. V središču formule je združitev, ki združuje začetni datum, dvopičje in končni datum. Excel ne uporablja oblikovanega datuma. Namesto tega uporablja serijsko številko v zakulisju. Tako B3&":"&B4postane 23790: 42167. Verjeli ali ne, to je veljavna referenca celice. Če bi radi sešteli vse v vrsticah od 3 do 5, lahko uporabite =SUM(3:5). Ko posredujete 23790: 42167 v funkcijo POSREDNO, ta kaže na vse vrstice.
Naslednja stvar, ki jo naredi formula morilca, je, da zahteva ROW(23790:42167). Običajno prenesete eno samo celico: =ROW(D17)je 17. Toda v tem primeru mimo tisoče celic. Ko zahtevate ROW(23790:42167)in dokončate formulo s kombinacijo tipk Ctrl + Shift + Enter, Excel dejansko vrne vsako številko od 23790, 23791, 23792 itd. Do 42167.
Ta korak je neverjeten korak. V tem koraku gremo iz dveh številk in izpišemo vrsto 18378 številk. Zdaj moramo nekaj storiti s to vrsto odgovorov. Celica B9 prejšnje slike samo šteje, koliko odgovorov dobimo, kar je dolgočasno, vendar dokazuje, da ROW(23790:42167)vrača 18378 odgovorov.
Dramatično poenostavimo prvotno vprašanje, da boste lahko videli, kaj se dogaja. V tem primeru bomo našli število petkov v juliju 2015. Formula, prikazana spodaj v B7, daje pravilen odgovor v B6.
V središču formule je ROW(INDIRECT(B3&":"&B4)). To bo vrnilo 31 datumov v juliju 2015. Toda formula nato teh 31 datumov posreduje WEEKDAY(,2)funkciji. Ta funkcija bo vrnila 1 za ponedeljek, 5 za petek itd. Veliko vprašanje je torej, koliko od teh 31 datumov vrne petico, ko je preneseno v WEEKDAY(,2)funkcijo.
Izračun formule si lahko ogledate v počasnem posnetku z uporabo ukaza Evaluate Formula na zavihku Formula na traku.
To je potem, ko INDIRECT pretvori datume v sklic vrstice.
V naslednjem koraku bo Excel prenesel 31 številk v funkcijo WEEKDAY. Zdaj bi v formuli morilca namesto 31 prenesla 18.378 številk.
Tu so rezultati funkcij 31 WEEKDAY. Ne pozabite, da želimo prešteti, koliko jih je 5.
Če preverite, ali je prejšnja matrika 5, vrnete cel kup vrednosti True / False. Obstaja 5 resničnih vrednosti, ena za vsak petek.
Ne morem vam pokazati, kaj se zgodi naprej, lahko pa vam razložim. Excel ne more sešteti veliko vrednosti True in False. To je v nasprotju s pravili. Če pa vrednosti True in False pomnožite z 1 ali če uporabite funkcijo dvojnega negativa ali N (), pretvorite True vrednosti v 1 in False vrednosti v 0. Pošljite jih v SUM ali SUMPRODUCT in dobite števec resničnih vrednosti.
Tu je podoben primer, da preštejemo, koliko mesecev ima dan 13. Razmisliti je treba o tem trivialno: vsak mesec ima 13., zato je odgovor za celo leto boljši 12. 12. Excel izračuna matematiko, generira 365 datumov, jih vse pošlje v funkcijo DAY () in ugotovi, koliko jih konča do 13. v mesecu. Odgovor je pričakovano 12.
Naslednja slika je delovni list, ki naredi vso logiko ena ubijalska formula, prikazana na začetku te teme. Vsak dan sem ustvaril vrsto, da sem živ. V stolpcu B dobim DAY () tega datuma. V stolpcu C dobim WEEKDAY () datuma. Ali je B v stolpcu D enak 13? Je v stolpcu E C = 5? Nato pomnožim D * E, da pretvorim True / False v 1/0.
Veliko vrstic sem skril, vendar vam na sredini pokažem tri naključne dni, ki so v petek in 13..
Vsota v F18381 je enaka 86, kot jo je vrnila moja prvotna formula. Odličen znak. Toda ta delovni list vsebuje 110.268 formul. Moja originalna formula morilca vso logiko teh 110.268 formul naredi v eni sami formuli.
Počakaj. Želim pojasniti. V prvotni formuli ni nič čarobnega, kar bi postalo pametno in skrajšalo logiko. Ta izvirna formula resnično opravi 110.268 korakov, verjetno celo več, ker mora prvotna formula dvakrat izračunati matriko ROW ().
Poiščite način, kako to uporabiti ROW(INDIRECT(Date:Date))v resničnem življenju, in mi ga pošljite po e-pošti (pub na dot com). Prvim 100 ljudem bom poslal nagrado, da se javi. Verjetno ne bivanje v letovišču. Verjetneje Big Mac. Ampak tako gre z nagradami. Veliko Big Macov in malo bivališč.
To formulo sem prvič videl leta 2003 na Odboru za sporočila, ki jo je objavil Ekim. Zasluge je dobil Harlan Grove. Formula se je pojavila tudi v knjigi Boba Umlasa This Is not Excel, It's Magic. Mike Delaney, Meni Porat in Tim Sheets so predlagali trik minus / minus. SUMPRODUCT sta predlagala Audrey Lynn in Steven White. Hvala vsem.
Oglejte si video
- Obstaja skrivni razred formul, imenovan Array Formulas.
- Formula matrike lahko naredi na tisoče vmesnih izračunov.
- Pogosto zahtevajo, da pritisnete Ctrl + Shift + Enter, vendar ne vedno.
- Najboljša knjiga o formulah matrike je Ctrl + Shift + Enter Mikea Girvina.
- INDIRECT vam omogoča, da z združevanjem ustvarite nekaj, kar je videti kot sklic na celico.
- Datumi so lepo oblikovani, vendar so shranjeni v številu dni od 1. januarja 1900.
- Združevanje dveh datumov bo kazalo na vrsto vrstic v Excelu.
- Vprašanje za
ROW(INDIRECT(Date1:Date2))voljo "izpade" množico številnih zaporednih številk - S pomočjo funkcije WEEKDAY ugotovite, ali je datum petek.
- Koliko petkov se zgodi v tem juliju?
- Če si želite ogledati izračun formule v počasnem posnetku, uporabite orodje za ocenjevanje formule
- Koliko trinajstih se zgodi letos?
- Koliko petka 13. se je zgodilo med dvema datumoma?
- Na vsakem datumu preverite, ali je TEDEN petek
- Preverite vsak datum, da preverite, ali je DAN 13
- Pomnožite te rezultate z uporabo SUMPRODUCT
- Uporabi - za pretvorbo True / False v 1/0
Video zapis
Naučite se Excel iz podcasta, epizoda 2026 - Moja najljubša formula v celotnem Excelu!
Če objavite celotno knjigo, kliknite na i v zgornjem desnem kotu, da pridete do seznama predvajanja!
V redu, to je bila 30. tema v knjigi, nekako smo bili na koncu oddelka s formulo ali sredi oddelka s formulami in rekel sem, da moram vključiti svojo najljubšo formulo vseh časov. To je le neverjetna formula, ne glede na to, ali morate šteti število v petek, 13. ali ne, odpira svet v celotno skrivno področje Excela, imenovano Array Formulas! Vnesite začetni datum, dajte končni datum in ta formula izračuna število petka 13., ki se je zgodil med tema datumoma. Dejansko opravi pet izračunov vsak dan med tema datumoma, 91895 izračuni + SUM, 91896 izračuni, ki se dogajajo znotraj te majhne formule, v redu. Zdaj, do konca te epizode vas bodo tako zanimale matrične formule. Želim poudariti,moj prijatelj Mike Girvin ima najboljšo knjigo o matričnih formulah, imenovano "Ctrl + Shift" Enter ", to je nedavno tiskanje z modro platnico, nekoč rumeno in zeleno platnico. Katera koli, ki jo dobite, je odlična knjiga z enako vsebino v rumeni in zeleni.
V redu, začnimo z notranje strani tega, s formulo, ki je morda niste slišali, imenovano INDIRECT. INDIRECT nam omogoča povezovanje ali na nek način zgraditi del besedila, ki je videti kot sklic na celico. V redu, recimo, da imamo tukaj nagradno kolo in pravkar sem te prosil, da izbereš med A, B in C. V redu, torej izberi to in izberi C, nato izberi to in izberi 3, v redu in tvoja nagrada je bivanje v letovišču, ker je to tisto, kar je shranjeno v C3. In formula je tukaj združena, ne glede na to, od C5 in vse, kar je v C6, z uporabo & in nato posreduje to INDIRECT. Torej = INDIRECT (C5 in C6), v tem primeru je C3, to mora biti uravnotežena referenca. INDIRECT pravi: "Hej, šli bomo na C3 in vrnili odgovor iz tega, prav?" V Lotusu 1-2-3 se je to imenovalo funkcija @@,v Excelu so ga preimenovali v POSREDNO. V redu, torej imate v posrednem, zdaj je tu neverjetna stvar, ki se dogaja znotraj tam.
Imamo dva datuma, kako Excel shranjuje datume, 17.2.1965, to je v resnici samo oblikovanje. Če bi šli in pogledali dejansko število za tem, je 23790, kar pomeni, da je od 1. 1. 1900 23790 dni, od 1. 1. 1980 pa 42167 dni. Na Macu bo to od 1. 1. 1904, zato bodo datumi približno 3000 popustov. V redu, tako ga Excel shrani, vendar nam ga pokaže, zahvaljujoč tej obliki številk kot datum, toda če bi združili B3 in a: in B4, bi nam dejansko dali številke, shranjene v zakulisju. Torej = B3 & ”:” & B4, in če bi to posredovali posrednemu, bo dejansko kazal na vse vrstice od 23790 do 42167.
Torej obstaja posredno B6, prosil sem za VRST tega, kar mi bo dalo cel kup odgovorov in ugotovilo, koliko odgovorov sem uporabil, v redu. Da bi to delovalo, če samo pritisnem Enter, ne deluje, moram pritisniti Ctrl in Shift in pritisniti Enter, in glej, to doda () okoli formule tukaj zgoraj. Excelu pove, naj preide v način super formule, način formule matrike in opravi vso matematiko za vse, kar se pojavi iz tega polja, 18378, v redu. Torej, to je neverjeten trik, posreden za date1: date2, podajte to funkciji ROW in tukaj je majhen primer.
Torej želimo samo ugotoviti, koliko petkov se je zgodilo v tem juliju, tukaj je začetni datum, tukaj je končni datum in za vsako od teh vrstic bom prosil za TEDEN. WEEKDAY nam pove, kateri dan v tednu je, in tukaj v argumentu 2 bosta petka imela vrednost 5. Torej, iščem odgovor in izbrali bomo to formulo, pojdite na Formule, in Evaluate Formula, in Evaluate Formula je odličen način za opazovanje izračuna formule v počasnem posnetku. Torej je B3, 1. julij, in vidite, da se to spremeni v številko, nato pa se pridružimo dvopičju, desno, tam je B4, ki se bo spremenil v številko, in zdaj dobimo besedilo, 42186: 42216. Na tej točki to prenesemo v VRSTICO in ta preprost majhen izraz se bo tu spremenil v 31 vrednosti.
Zdaj, v primeru, ko sem imel vse od leta 1965 do 2015, bi izpisalo 86000 vrednosti, kajne, in tega ne želite narediti in oceniti formulo, ker bi bilo nekako noro, v redu? Ampak lahko vidite, kaj se tukaj dogaja z 31, zdaj pa teh 31 dni prenašam na funkcijo WEEKDAY in dobimo 3-4-5. Torej 3 pomeni, da je bila sreda, nato 4 pomeni, da je bil četrtek, nato pa 5 pomeni, da je bil petek. Vzemite vseh tistih 31 vrednosti in preverite, če so = 5, kar je petek, in dobili bomo kup LAŽNOSTI IN RESNIC, tako da bi bila sreda, četrtek, petek in nato 7 celic naslednja TRUE, super!
Torej, v tem primeru imamo 5 TRUE in 26 FALSE, da jih seštejem, moram pretvoriti FALSE v 0 in TRUE v 1, in zelo pogost način za to je, da uporabimo - . V redu, na žalost ni bilo videti odgovora tam, kjer smo videli cel kup 1 in 0, toda pravzaprav se to zgodi, nato pa SUMPRODUCT sešteje in nas pripelje do 5. Tu spodaj, če želimo ugotovite, koliko 13. v mesecu je bilo letos, od tega začetka do konca, zelo podoben postopek. Čeprav jih bomo imeli 365, jih prenesite v funkcijo DAY in preverite, koliko jih je 13, v redu. V primeru vrstice 92000 veste, dobimo dan, dobimo dan v tednu, preverimo, ali je DAY = 13, preverimo, ali je WEEKDAY = FALSE, pomnožimo to * to,in samo v primerih, ko je petek, 13., se to konča kot RES. Nato SUMPRODUCT reče "Seštej vse" in tako dobimo 86, dobesedno 91895 izračunov + SUM, 91896, ki se dogajajo znotraj te formule, to je noro močno! Pojdi kupiti Mikovo knjigo, to je neverjetna knjiga, odprla ti bo cel svet Excel formul in pravzaprav bi moral kupiti obe knjigi. Kupite mojo knjigo, kupite Mikovo knjigo in dobili boste čudovito zbirko, ki vas bo vodila do konca leta.odprl vam bo cel svet Excelovih formul in pravzaprav bi morali kupiti obe knjigi. Kupite mojo knjigo, kupite Mikovo knjigo in dobili boste čudovito zbirko, ki vas bo vodila do konca leta.odprl vam bo cel svet Excelovih formul in dejansko bi morali kupiti obe knjigi. Kupite mojo knjigo, kupite Mikovo knjigo in dobili boste čudovito zbirko, ki vas bo vodila do konca leta.
V redu, povzemite: obstaja skrivni razred formul, imenovan matrične formule, in matrična formula lahko naredi na tisoče vmesnih izračunov. Običajno zahtevajo, da pritisnete Ctrl + Shift + Enter, vendar ne vedno, in najboljša knjiga o formulah matrike je knjiga Mika Girvina "Ctrl + Shift + Enter". V redu, torej INDIRECT vam omogoča, da s pomočjo združevanja sestavite nekaj, kar je videti kot sklic na celico, nato pa INDIRECT preide na sklic na to celico. Združevanje dveh datumov z dvopičjem bo kazalo na obseg vrstic v Excelu, nato pa bo vprašal za VRSTICO POSREDNEGA datuma1: datum2 bo prikazal vrsto zaporednih števil, morda 31, morda 365 ali morda 85000. Preverite vsak datum, da preverite, ali je WEEKDAY = petek, vsak dan preverite, če je DAY = 13, pomnožite ta dva polja TRUE in FALSE s SUMPRODUCT. V mnogih primerih smoll uporaba - za pretvorbo TRUE / FALSE v 1 in 0 omogoči delovanje SUMPRODUCT. To je čudovita formula, nisem je ustvaril, našel sem jo na oglasni deski, ko sem jo obdeloval, sem rekel: "Vau, to je res super!"
V redu, rad bi se vam zahvalil, da ste se ustavili, se vidimo naslednjič za novo oddajo od!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2026.xlsx
