Az Excel tömbképletek rendkívül hatékonyak. A Ctrl + Shift + Enter trükk megtanulása után több ezer képletet cserélhet le egyetlen képlettel. Ma egyetlen tömbképlet 86 000 számítást végez.
A Triskaidekaphobia a 13. péntek félelme. Ez a téma nem gyógyít semmit, de egy teljesen elképesztő képletet mutat be, amely 110 268 képletet helyettesít. A való életben soha nem kell megszámolnom, hogy hányadik péntek történt meg életem során, de ennek a képletnek az ereje és szépsége szemlélteti az Excel erejét.
Mondd, hogy van egy barátod, aki babonás 13-án, pénteken. Illusztrálni szeretné, hogy barátja hány péntek 13-át élt meg.
Állítsa be az alábbi egyszerű munkalapot, a születési dátummal a B1-ben és =TODAY()a B2-ben. Ezután egy vad képlet a B6-ban minden nap kiértékeli, hogy a barátod életben van-e, hogy kiderítse, hány ilyen nap volt péntek és esett a hónap 13. napján. Számomra a szám 86. Semmi félnivaló.
Egyébként 1965.02.17-én valóban a születésnapom. De nem akarom, hogy szülinapot küldjön nekem. Ehelyett a születésnapomra azt akarom, hogy hadd magyarázzam el, hogyan működik ez a csodálatos formula, egy-egy apró lépésben.
Használtad már a INDIRECT funkciót? Amikor ezt kéri =INDIRECT("C3"), az Excel átmegy a C3-ba, és visszaadja a cellában találhatóakat. De a INDIRECT erősebb, ha menet közben kiszámítja a cella referenciát. Felállíthat egy nyereménykereket, ahol valaki betűt választ A és C között, majd 1 és 3 közötti számot. Amikor összefűzi a két választ, megkapja a cellacímét, és ami ezen a cellacímen van, az a nyeremény . Úgy tűnik, fotófüzetet nyertem az üdülőhelyi tartózkodás helyett.
Tudja, hogy az Excel hogyan tárolja a dátumokat? Amikor az Excel megmutatja 1965.02.17., Akkor 23790-et tárol a cellában, mert 1965.02.17. A 20. század 23790. napja volt. A képlet középpontjában egy összefűzés áll, amely egyesíti a kezdő dátumot, valamint a kettőspontot és a befejezés dátumát. Az Excel nem használja a formázott dátumot. Ehelyett a kulisszák mögötti sorozatszámot használja. Így B3&":"&B4lesz 23790: 42167. Hiszed vagy sem, ez egy érvényes cellahivatkozás. Ha a 3–5. Sorokban mindent össze szeretne adni, használhatja =SUM(3:5). Tehát, amikor átadja a 23790: 42167-et az INDIRECT funkciónak, akkor az az összes sorra mutat.
A következő dolog, amit a gyilkos képlet tesz, az a ROW(23790:42167). Normális esetben egyetlen cellán halad el: =ROW(D17)17. De ebben az esetben több ezer cellán halad át. Amikor kéri ROW(23790:42167)és befejezi a képletet a Ctrl + Shift + Enter billentyűkombinációval, az Excel minden 23790, 23791, 23792 stb. Számot visszaad, 42167-ig.
Ez a lépés a csodálatos lépés. Ebben a lépésben két számból indulunk ki, és 18378 szám tömböt „ugrunk ki”. Most valamit kell tennünk a válaszok tömbjével. Az előző ábra B9 cellája éppen azt számolja, hogy hány választ kapunk, ami unalmas, de azt bizonyítja, hogy ROW(23790:42167)18378 választ ad vissza.
Egyszerűsítsük drámaian az eredeti kérdést, hogy lássa, mi történik. Ebben az esetben meg fogjuk találni a 2015. júliusi péntekek számát. Az alábbiakban a B7-ben bemutatott képlet a helyes választ adja meg a B6-ban.
A képlet középpontjában az áll ROW(INDIRECT(B3&":"&B4)). Ez visszaadja a 31 dátumot 2015 júliusában. De a képlet ezt a 31 dátumot átadja a WEEKDAY(,2)függvénynek. Ez a funkció hétfőre 1-et, péntekre 5-öt ad vissza. Tehát a nagy kérdés az, hogy a 31 dátum közül hány ad vissza 5-öt, ha átadják a WEEKDAY(,2)függvénynek.
Megnézheti a képlet lassított számítását a szalag Képlet fülén található Formula értékelése paranccsal.
Ez azután történik, hogy a INDIRECT konvertálja a dátumokat sor referenciává.
A következő lépésben az Excel 31 számot fog átadni a WEEKDAY függvénynek. Most a gyilkos képletben 31 helyett 18 378 számot adna át.
Itt vannak a 31 HÉTNAP funkció eredményei. Ne feledje, meg akarjuk számolni, hogy hányan vannak 5.
Annak ellenőrzése, hogy az előző tömb 5-ös, egy csomó igaz / hamis értéket ad vissza. 5 True érték van, minden péntekre egy.
Nem tudom megmutatni, mi történik ezután, de meg tudom magyarázni. Az Excel nem összegezhet egy csomó igaz és hamis értéket. A szabályokkal ellentétes. De ha ezeket az Igaz és Hamis értékeket megszorozza 1-vel, vagy ha a kettős-negatív vagy az N () függvényt használja, akkor a True értékeket 1-re, a Hamisakat 0-ra konvertálja. Ezeket elküldi a SUM vagy a SUMPRODUCT fájlba, és akkor kapja meg a True értékek számát.
Itt van egy hasonló példa arra, hogy megszámoljuk, hány hónapban van 13 nap. Ezen elgondolkodni triviális: Minden hónapnak van egy 13. napja, tehát az egész évre adott válasz jobb legyen 12. Az Excel matematikát készít, 365 dátumot generál, mindet elküldi a DAY () függvénybe, és kitalálja, hogy hány vége van. fent a hónap 13-án. A válasz a várakozásoknak megfelelően 12.
A következő ábra egy munkalap, amely a téma elején bemutatott egy gyilkos képletet teljes egészében megadja. Készítettem egy sort minden napra, amellyel már éltem. A B oszlopban megkapom az adott nap NAPJÁT (). A C oszlopban megkapom a dátum HÉTNAPJÁT (). A D oszlopban B értéke 13? Az E oszlopban C = 5? Ezután megszorzom a D * E értéket, hogy az Igaz / Hamis értéket 1/0 értékre konvertáljam.
Sok sort elrejtettem, de három véletlenszerű napot mutatok középen, amelyek történetesen péntek és 13. is.
Az F18381 teljes összege ugyanaz, mint az eredeti képletem. Remek jel. De ez a munkalap 110 268 képletet tartalmaz. Eredeti gyilkos képletem a 110 268 képlet logikáját egyetlen képletben végzi.
Várjon. Szeretném tisztázni. Az eredeti képletben nincs semmi varázslat, amely intelligenssé és rövidíti a logikát. Ez az eredeti képlet valóban 110 268 lépést tesz meg, valószínűleg még többet is, mert az eredeti képletnek kétszer kell kiszámítania a ROW () tömböt.
Találd meg, hogyan használhatod ezt ROW(INDIRECT(Date:Date))a való életben, és küldd el nekem e-mailben (pub at dot com). Nyereményt küldök az első 100 embernek, aki válaszol. Valószínűleg nem üdülőhelyi tartózkodás. Valószínűleg egy Big Mac. De így jár ez a díjakkal is. Sok Big Mac és nem sok üdülőhely.
Ezt a képletet először az Üzenőtáblán láttam el 2003-ban, az Ekim. Hitelet Harlan Grove kapott. A képlet megjelent Bob Umlas This Is not Excel, It's Magic című könyvében is. Mike Delaney, Meni Porat és Tim Sheets egyaránt javasolta a mínusz / mínusz trükköt. A SUMPRODUCT-ot Audrey Lynn és Steven White javasolta. Köszönök mindent.
Videót nézni
- Van egy titkos képletcsoport, az úgynevezett tömbképletek.
- Egy tömbképlet ezernyi közbenső számítást képes elvégezni.
- Gyakran megkövetelik, hogy nyomja meg a Ctrl + Shift + Enter billentyűt, de nem mindig.
- A tömbképletekről szóló legjobb könyv Mike Girvin Ctrl + Shift + Enter.
- A INDIRECT lehetővé teszi az összefűzés használatát, hogy olyan elemet készítsen, amely cellahivatkozásnak tűnik.
- A dátumok szépen vannak formázva, de 1900. január 1-je óta napokként vannak tárolva.
- Két dátum összefűzése egy sor sorra mutat az Excelben.
- Az
ROW(INDIRECT(Date1:Date2))akarat kérése "kiugrik" egy sor egymást követő számból álló tömbből - A WEEKDAY funkció használatával megtudhatja, hogy dátum-e péntek.
- Hány péntek van ebben a júliusban?
- A képlet lassított számításának megtekintéséhez használja a Formula kiértékelése eszközt
- Hány 13. fordul elő ebben az évben?
- Hány péntek 13. történt két időpont között?
- Ellenőrizze az egyes dátumokat, hogy a HÉTNAP péntek-e
- Ellenőrizze az egyes dátumokat, hogy a NAP 13-e
- Szorozza meg ezeket az eredményeket a SUMPRODUCT segítségével
- A - használatával a True / False értéket 1/0 értékre konvertálja
Videó átirat
Ismerje meg az Excel programot a podcastból, 2026. epizód - Kedvenc képletem az Excel összes verziójában!
A teljes könyv podcastolásakor kattintson a jobb felső sarokban található „i” gombra, hogy a lejátszási listához jusson!
Rendben, ez volt a 30. téma a könyvben, valahogy a képletrész végén vagy a képletrész közepén voltunk, és azt mondtam, hogy minden idők kedvenc képletét be kell írnom. Ez csak egy csodálatos képlet, függetlenül attól, hogy meg kell-e számolnia a 13. pénteki számot, vagy sem, ez egy világot nyit az Excel teljes titkos területére, amelyet Array Formulas-nak hívnak! Tegyen be egy kezdési dátumot, tegyen be egy befejezési dátumot, és ez a képlet kiszámítja a két dátum között eltelt 13. péntek számát. Valójában öt számítást végez minden nap a két dátum között, 91895 számítás + SUM, 91896 számítás ebben az egy kis képleten belül, rendben. Most, ennek az epizódnak a végén, annyira érdekelni fogják a tömbképletek. Szeretném rámutatni,Mike Girvin barátomnak van a legjobb könyve a tömbképletekről, a „Ctrl + Shift” Enter nevű. Ez egy friss nyomtatás, kék borítóval, korábban sárga és zöld borítóval. Bármelyiket is kapja, ez egy remek könyv, amelynek tartalma mind a sárga, mind a zöld színben megegyezik.
Rendben, kezdjük ennek belsejében, olyan képlettel, amelyet esetleg még nem hallottál, és ezt INDIRECT-nek hívják. A INDIRECT lehetővé teszi számunkra, hogy összefűzzük, vagy valamilyen módon felépítsünk egy kis cellahivatkozásnak tűnő szöveget. Rendben, tegyük fel, hogy van itt nyereménykerekünk, és csak arra kértem, válasszon A, B és C. között. üdülőtelep, mert ezt tárolják a C3-ban. És az itt található képletet összefűzzük, bármi is legyen a C5 óta, és bármi is a C6-ban, a & használatával, majd ezt továbbítva a INDIRECT-nek. Tehát = INDIRECT (C5 és C6), ebben az esetben C3, ennek kiegyensúlyozott referenciának kell lennie. INDIRECT azt mondja: "Hé, elmegyünk a C3-ba, és visszaadjuk a választ, rendben?" Vissza a Lotus 1-2-3-ban ezt @@ függvénynek hívták,az Excel-ben átnevezték KÖZVETLennek. Rendben, szóval a INDIRECT-ben van, most itt van a csodálatos dolog, ami odabent történik.
Két dátumunk van, hogyan tárolja az Excel a dátumokat, 1965.02.17., Ez valójában csak a formázás. Ha elmentünk és megnéztük a tényleges számot mögötte, akkor ez 23790, ami azt jelenti, hogy 23790 nap telt el 1920. január 1-je óta, és 42167 nap volt 1980. január 1. óta. Mac gépen 1904. január 1-jétől lesz, tehát a dátum kb. 3000 kedvezmény. Rendben, az Excel így tárolja, ennek ellenére megmutatja nekünk, ennek a számformátumnak köszönhetően dátumként, de ha összefűznénk a B3 és a: és B4 elemeket, akkor valóban megadnánk a kulisszák mögött tárolt számokat. Tehát = B3 & ”:” & B4, és ha ezt átadnánk a INDIRECT-nek, akkor valójában a 23790-től a 42167-ig terjedő összes sorra mutat.
Tehát van a B6 INDIRECT-je, ennek a sorát kértem, ez egy csomó választ fog adni nekem, és hogy kitaláljam, hány választ használtam, rendben. És ahhoz, hogy ez működjön, ha csak az Enter billentyűt nyomom meg, akkor nem működik, akkor meg kell tartanom a Ctrl és a Shift billentyűt, majd meg kell nyomni az Enter billentyűt, és látni kell, ami itt összeadja a () képletet. Azt mondja az Excel-nek, hogy lépjen szuper képlet módba, tömb képlet módba, és végezze el a matematikát mindarról, ami az 18378-as tömbből rendben van. Tehát ez egy csodálatos trükk, a date1: date2 közvetett, adja át ezt a ROW függvénynek, és íme egy kis példa.
Tehát csak azt szeretnénk kitalálni, hogy hány péntek történt júliusban. Itt van egy kezdési dátum, itt egy befejezési dátum, és ezekhez a sorokhoz mindegyikhez a WEEKDAY-t fogom kérni. WEEKDAY megmondja, hogy a hét melyik napja ez, és itt a 2 argumentumban a péntekeknek értéke 5 lesz. Tehát keresem a választ, és ezt a képletet választjuk, menjünk a Képletek oldalra, és a Formula értékelése, és a Formula értékelése nagyszerű módja annak, hogy figyelje a képlet lassított számítását. Tehát ott van a B3, július 1-je, és látja, hogy ez változik egy számra, majd csatlakozunk a kettősponthoz, ugye, ott van a B4, amely számra változik, és most megkapjuk a 42186: 42216 szöveget. Ezen a ponton ezt átadjuk a ROW-nak, és ez az egyszerű kis kifejezés itt 31 értékké válik.
Most abban a példában, ahol 1965-től 2015-ig mindenem megvolt, 86000 értéket ugrott volna ki, ugye, és nem akarja ezt megtenni, és értékelni a képletet, mert valahogy őrült, rendben lenne? De láthatja, mi történik itt a 31-gyel, és most ezt a 31 napot átadom a WEEKDAY függvénynek, és 3-4-5-t kapunk. Tehát 3 azt jelenti, hogy szerda volt, majd 4 azt jelenti, hogy csütörtök volt, majd 5 azt jelenti, hogy péntek volt. Vedd meg mind a 31 értéket, és nézd meg, hogy ezek = 5, ami péntek, és kapunk egy csomó HAMIS-t és IGAZOT, tehát szerda, csütörtök, péntek, majd 7 cella később lesz a következő IGAZ, fantasztikus!
Rendben, tehát ebben az esetben 5 IGAZ és 26 HAMIS van. Ahhoz, hogy ezeket összeadjam, a FALSE értéket 0-ra és az IGAZ-t 1-re kell konvertálnom, és ennek nagyon gyakori módja a - . Rendben, sajnos ez nem mutatta a választ ott, ahol láttunk egy csomó 1-et és 0-at, de valójában ez történik, majd a SUMPRODUCT összeadja, és eljut az 5-öshöz. Itt lent, ha akarjuk kitalálni, hogy a hónap 13. napja hány volt ebben az évben, ettől a kezdési dátumtól kezdve egészen a végdátumig, nagyon hasonló folyamat. Bár 365-ös számunk lesz, adjuk át a DAY függvénynek, és ellenőrizzük, hogy hányan 13 évesek, rendben. A 92000 soros példához tudod, hogy megkapjuk a napot, megkapjuk a hétköznapot, ellenőrizzük, hogy a, NAP = 13, ellenőrizzük, hogy a HÉTNAP = HAMIS, ezt megszorozva * ezt,és csak azokban az esetekben, amikor péntek 13-a van, ez IGAZ-nak bizonyul. A SUMPRODUCT ekkor azt mondja: „Adja hozzá mindezeket”, és így kapjuk meg a 86, szó szerint 91895 számítást + SUM, 91896 ebben az egy képletben, ez őrülten hatalmas! Menj, vedd meg Mike könyvét, ez egy csodálatos könyv, amely az Excel képleteinek egész világát megnyitja előtted, és valójában csak meg kell vásárolnod mindkét könyvet. Vásárolja meg a könyvemet, vásárolja meg Mike könyvét, és fantasztikus gyűjteménye lesz, amely végigvezeti Önt az év hátralévő részében.az Excel képleteinek egész világát megnyitja előtted, és tulajdonképpen csak meg kell vásárolnod mindkét könyvet. Vásárolja meg a könyvemet, vásárolja meg Mike könyvét, és fantasztikus gyűjteménye lesz, amely végigvezeti Önt az év hátralévő részében.az Excel képleteinek egész világát megnyitja előtted, és tulajdonképpen csak meg kell vásárolnod mindkét könyvet. Vásárolja meg a könyvemet, vásárolja meg Mike könyvét, és fantasztikus gyűjteménye lesz, amely végigvezeti Önt az év hátralévő részében.
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!”
Rendben, szeretném megköszönni, hogy megálltál, legközelebb találkozunk egy újabb netcast-lal!
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2026.xlsx
