Vzorce programu Excel Array sú super výkonné. Až sa naučíte triky Ctrl + Shift + Enter, môžete nahradiť tisíce vzorcov jediným vzorcom. Dnes vzorec jedného poľa vykoná 86 000 výpočtov.
Triskaidekafóbia je strach z piatku trinásteho. Táto téma nič nevylieči, ale ukáže vám úplne úžasný vzorec, ktorý nahradí 110 268 vzorcov. V skutočnom živote nikdy nemusím počítať, koľko piatkových trinástych sa v mojom živote vyskytlo, ale sila a krása tohto vzorca ilustrujú silu Excelu.
Povedzte, že máte priateľa, ktorý je poverčivý asi v piatok trinásteho. Chcete ilustrovať, koľko piatku trinásteho prežil váš priateľ.
Pripravte si nasledujúci jednoduchý pracovný hárok s dátumom narodenia v B1 a =TODAY()B2. Potom divoký vzorec v B6 každý deň vyhodnotí, že váš priateľ žil, aby zistil, koľko z tých dní bol piatok a pripadol na 13. dňa v mesiaci. Pre mňa je to číslo 86. Niet sa čoho báť.
Mimochodom, 17.2.1965 mám skutočne narodeniny. Ale nechcem, aby ste mi poslali narodeninovú kartu. Namiesto toho by som chcel, k mojim narodeninám, nechať ma vysvetliť, ako ten úžasný vzorec funguje, jeden malý krok po druhom.
Už ste niekedy použili funkciu INDIRECT? Keď o to požiadate =INDIRECT("C3"), Excel prejde na C3 a vráti všetko, čo je v tejto bunke. Ale INDIRECT je výkonnejší pri výpočte referencie na bunku za behu. Môžete vytvoriť výherné koliesko, kde niekto vyberie písmeno medzi A a C a potom vyberie číslo medzi 1 a 3. Keď spojíte tieto dve odpovede, budete mať adresu bunky a čímkoľvek, čo sa na tejto adrese bunky nachádza, je cena . Vyzerá to, že som namiesto rezortného pobytu vyhral fotoknihu.
Viete, ako program Excel ukladá dáta? Keď vám program Excel zobrazí 17.2.1965, ukladá sa do bunky 23790, pretože 17.2.1965 bol 23790. deň 20. storočia. Jadrom vzorca je zreťazenie, ktoré spája dátum začatia a dvojbodku a dátum ukončenia. Excel nepoužíva formátovaný dátum. Namiesto toho používa sériové číslo v zákulisí. Takto sa B3&":"&B4stáva 23790: 42167. Verte tomu alebo nie, toto je platný odkaz na bunku. Ak by ste chceli spočítať všetko v riadkoch 3 až 5, mohli by ste použiť =SUM(3:5). Keď teda odovzdáte 23790: 42167 funkcii INDIRECT, ukáže to na všetky riadky.
Ďalšia vec, ktorú vrahový vzorec urobí, je požiadať o ROW(23790:42167). Za normálnych okolností miniete jednu bunku: =ROW(D17)je 17. Ale v tomto prípade miniete tisíce buniek. Keď požadujete ROW(23790:42167)a dokončíte vzorec pomocou klávesov Ctrl + Shift + Enter, Excel v skutočnosti vráti každé číslo od 23790, 23791, 23792 atď. Až po 42167.
Tento krok je úžasným krokom. V tomto kroku ideme od dvoch čísel a „vyskakujeme“ pole 18378 čísel. Teraz musíme s týmto súborom odpovedí niečo urobiť. Bunka B9 na predchádzajúcom obrázku iba počíta, koľko odpovedí dostaneme, čo je nudné, ale dokazuje to, že ROW(23790:42167)vracia 18378 odpovedí.
Poďme dramaticky zjednodušiť pôvodnú otázku, aby ste videli, čo sa deje. V takom prípade nájdeme počet piatkov v júli 2015. Vzorec uvedený nižšie v B7 poskytuje správnu odpoveď v B6.
Jadrom vzorca je ROW(INDIRECT(B3&":"&B4)). Týmto sa vráti 31 dátumov v júli 2015. Ale vzorec potom odovzdá týchto 31 dátumov WEEKDAY(,2)funkcii. Táto funkcia vráti 1 za pondelok, 5 za piatok atď. Veľkou otázkou teda je, koľko z týchto 31 dátumov vráti po odovzdaní WEEKDAY(,2)funkcii hodnotu 5.
Výpočet vzorca môžete sledovať spomalene pomocou príkazu Vyhodnotiť vzorec na karte Vzorec na páse s nástrojmi.
Toto je potom, čo INDIRECT prevedie dátumy na odkaz na riadok.
V ďalšom kroku Excel predá funkcii WEEKDAY 31 čísel. Teraz by vo vrahovom vzorci prešiel namiesto 18 378 čísel.
Tu sú výsledky 31 funkcií WEEKDAY. Pamätajte, že chceme spočítať, koľko ich je 5.
Kontrola, či je predchádzajúce pole 5, vráti celú hromadu hodnôt True / False. Existuje 5 skutočných hodnôt, jedna pre každý piatok.
Nemôžem vám ukázať, čo sa stane ďalej, ale môžem to vysvetliť. Program Excel nemôže SUMA zhromaždiť množstvo pravdivých a nepravdivých hodnôt. Je to v rozpore s pravidlami. Ale ak vynásobíte tieto hodnoty True a False číslom 1 alebo ak použijete funkciu dvojitého záporu alebo N (), prevediete hodnoty True na hodnotu 1 a hodnoty False na 0. Pošlite ich na SUM alebo SUMPRODUCT a získajte počet skutočných hodnôt.
Tu je podobný príklad na spočítanie toho, koľko mesiacov má v sebe 13. deň. Je to triviálne na zamyslenie: Každý mesiac má trinásty, takže celoročná odpoveď by mala byť 12. Excel vykonáva matematiku, generuje 365 dátumov, všetky ich posiela do funkcie DAY () a zisťuje, koľko končí až 13. dňa v mesiaci. Odpoveď je podľa očakávania 12.
Nasledujúci obrázok je pracovný hárok, ktorý obsahuje všetku logiku vzorca jedného vraha zobrazeného na začiatku tejto témy. Vytvoril som riadok pre každý deň, ktorý som žil. V stĺpci B sa zobrazuje DEŇ () daného dátumu. V stĺpci C dostanem WEEKDAY () dátumu. V stĺpci D sa B rovná 13? Je v stĺpci E C = 5? Potom vynásobím D * E a prevediem True / False na 1/0.
Skryl som veľa riadkov, ale ukážem vám tri náhodné dni uprostred, ktoré sú náhodou piatok aj trinásty.
Celková suma vo F18381 je rovnakých 86, ako vrátil môj pôvodný vzorec. Skvelé znamenie. Tento pracovný hárok má ale 110 268 vzorcov. Môj pôvodný vrahový vzorec má logiku týchto 110 268 vzorcov v jednom vzorci.
Počkaj. Chcem to objasniť. V pôvodnom vzorci nie je nič kúzelné, čo by bolo inteligentné a skrátilo by to logiku. Tento pôvodný vzorec skutočne robí 110 268 krokov, pravdepodobne ešte viac, pretože pôvodný vzorec musí vypočítať pole ROW () dvakrát.
Nájdite spôsob, ako to využiť ROW(INDIRECT(Date:Date))v reálnom živote, a pošlite mi ho e-mailom (pub at dot com). Prvým 100 ľuďom, ktorým odpoviem, pošlem cenu. Pravdepodobne to nie je pobyt v rezorte. Pravdepodobnejšie Big Mac. Ale tak to chodí s cenami. Veľa veľkých počítačov Mac a málo letovísk.
Prvýkrát som tento vzorec uvidel na diskusnej tabuli v roku 2003 od Ekima. Zásluhu na tom mal Harlan Grove. Vzorec sa objavil aj v knihe Boba Umlasa Toto nie je Excel, je to kúzlo. Mike Delaney, Meni Porat a Tim Sheets navrhli trik mínus / mínus. SUMPRODUCT navrhli Audrey Lynn a Steven White. Ďakujem vám všetkým.
Pozeraj video
- Existuje tajná trieda vzorcov, ktorá sa nazýva Array Formulas.
- Maticový vzorec dokáže urobiť tisíce prechodných výpočtov.
- Často vyžadujú stlačenie klávesov Ctrl + Shift + Enter, ale nie vždy.
- Najlepšou knihou o vzorcoch polí je Ctrl + Shift + Enter od Mika Girvina.
- INDIRECT vám umožní pomocou zreťazenia vytvoriť niečo, čo vyzerá ako odkaz na bunku.
- Dátumy sú pekne naformátované, ale od 1. januára 1900 sa ukladajú ako počet dní.
- Zreťazenie dvoch dátumov bude smerovať na rozsah riadkov v programe Excel.
- Žiadosť o
ROW(INDIRECT(Date1:Date2))vôľu „vyskočí“ z radu mnohých po sebe idúcich čísel - Pomocou funkcie WEEKDAY zistíte, či je dátum piatok.
- Koľko piatkov sa stane tento rok v júli?
- Ak chcete sledovať vzorec vypočítaný spomalene, použite nástroj Vyhodnotiť vzorec
- Koľko 13. sa vyskytuje tento rok?
- Koľko piatka 13. sa stalo medzi dvoma dátumami?
- Skontrolujte každý dátum a zistite, či je WEEKDAY piatok
- Skontrolujte každý dátum a zistite, či je DEN 13
- Znásobte tieto výsledky pomocou SUMPRODUCT
- Použite - na prevod hodnoty True / False na 1/0
Prepis videa
Naučte sa Excel z podcastu, epizóda 2026 - Môj obľúbený vzorec v celom Exceli!
Ak podcastujete celú túto knihu, kliknutím na písmeno „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb!
Dobre, išlo o 30. tému v knihe, boli sme tak trochu na konci časti s formulovaním alebo uprostred časti s formulovaním a povedal som, že musím zahrnúť svoj obľúbený vzorec všetkých čias. Toto je úžasný vzorec, či už musíte počítať počet v piatok trinásteho alebo nie, otvára vám svet do celej tajnej oblasti Excelu s názvom Array Formulas! Vložte počiatočný dátum, vložte koncový dátum a tento vzorec vypočíta počet piatkov trinásteho, ktoré nastali medzi týmito dvoma dátumami. V skutočnosti robí päť výpočtov každý deň medzi týmito dvoma dátumami, 91895 výpočtov + SUM, 91896 výpočtov prebiehajúcich vo vnútri tohto jedného malého vzorca, v poriadku. Na konci tejto epizódy vás pole vzorcov poľa tak zaujme. Chcem poukázať,môj priateľ Mike Girvin má najlepšiu knihu o maticových vzorcoch s názvom „Ctrl + Shift“ Enter “, toto je nedávna tlač s modrým obalom, predtým žltým a zeleným obalom. Nech už získate ktorúkoľvek z nich, teraz je to skvelá kniha, rovnakého obsahu v žltej aj zelenej.
Dobre, začnime teda vnútorne. Vzorec, ktorý ste možno nepočuli, sa volá INDIRECT. INDIRECT nám umožňuje zreťaziť alebo nejakým spôsobom vytvoriť kúsok textu, ktorý vyzerá ako odkaz na bunku. V poriadku, povedzme, že tu máme výherné koliesko, a práve som vás požiadal, aby ste si vybrali medzi A, B a C. V poriadku, takže zvoľte toto a vyberte C, potom vyberte toto a vyberte 3, v poriadku a vaša cena je pobyt v letovisku, pretože práve ten je uložený v C3. A vzorec je tu zreťazený spolu od C5 a od C6 pomocou & a potom odovzdaním NEPRIAMEHO. Takže = INDIRECT (C5 a C6), v tomto prípade, je C3, čo musí byť vyvážená referencia. INDIRECT hovorí: „Hej, pôjdeme do C3 a vrátime z toho odpoveď, dobre?“ Späť v Lotus 1-2-3 sa táto funkcia volala @@,v programe Excel ho premenovali na NEPRIAME. Dobre, takže máte v NEPRIAME, teraz je tu úžasná vec, ktorá sa deje tam.
Máme dva dátumy, ako program Excel ukladá dátumy, 17. 2. 1965, čo je v skutočnosti iba formátovanie. Keby sme išli a pozreli sa na skutočný počet, ktorý je za tým, je to 23790, čo znamená, že je to 23790 dní od 1. 1. 1900 a 42167 dní od 1. 1. 1980. Na počítači Mac to bude od 1. 1. 1904, takže dátumy budú asi 3000 min. Dobre, tak to Excel ukladá, ukazuje nám to vďaka tomuto číselnému formátu ako dátum, ale ak by sme spojili B3 a a: a B4, dalo by nám to skutočne čísla uložené v zákulisí. Takže = B3 & ”:” & B4, a ak by sme to odovzdali INDIRECT, bude to smerovať na všetky riadky od 23790 do 42167.
Takže existuje NEPRIAME z B6, požiadal som o RIADOK, ktorý mi dá celú kopu odpovedí a zistím, koľko odpovedí som použil, dobre. A aby to fungovalo, ak stlačím iba kláves Enter, nebude to fungovať, musím podržať Ctrl a Shift a stlačiť kláves Enter a vidieť, že sem () pridá vzorec Hovorí programu Excel, aby prešiel do režimu superformúl, režimu maticového vzorca a urobil všetku matematiku pre všetko, čo z tohto poľa vyskočilo, 18378, v poriadku. Takže to je úžasný trik, nepriamy z date1: date2, odovzdajte to funkcii ROW a tu je malý príklad.
Chceme teda len zistiť, koľko piatkov sa vyskytlo tento rok v júli, tu je dátum začatia, tu je dátum ukončenia a pre každý z týchto riadkov požiadam o DNI. WEEKDAY nám hovorí, aký je deň v týždni, a tu v argumente 2 budú mať piatky hodnotu 5. Takže hľadám odpoveď a my si vyberieme tento vzorec, choď do Vzorcov, a Vyhodnoťte vzorec a Vyhodnoťte vzorec je skvelý spôsob, ako sledovať, ako sa vzorec vypočítava spomalene. Takže je tu B3, 1. júla, a vidíte, že sa mení na číslo, a potom sa pripojíme k dvojbodke, správne, je tu B4, ktorá sa zmení na číslo, a teraz dostaneme text, 42186: 42216. V tomto okamihu to odovzdáme RIADKU a ten jednoduchý výraz sa zmení tu na 31 hodnôt.
Teraz, v príklade, kde som mal všetko od roku 1965 do roku 2015, by vyskočilo 86 000 hodnôt, však, a nechcete to robiť a hodnotiť vzorec, pretože by to bolo trochu šialené, dobre? Ale vidíte, čo sa tu deje s 31-tkou, a teraz tých 31 dní prechádzam funkcii WEEKDAY a dostaneme 3-4-5. Takže 3 znamená, že bola streda, a potom 4 znamená, že bol štvrtok, a potom 5 znamená, že bol piatok. Vezmite všetkých tých 31 hodnôt a zistite, či sú = 5, čo je piatok, a dostaneme kopu NEPRAVDA a PRAVDA, takže streda, štvrtok, piatok a potom o 7 buniek bude ďalšia PRAVDA, úžasné!
Dobre, takže v tomto prípade máme 5 TRUE a 26 FALSE. Aby som ich spočítal, musím previesť FALSE na 0 a TRUE na 1 a veľmi častým spôsobom je použiť - . Dobre, bohužiaľ to neukázalo odpoveď tam, kde sme videli celú kopu čísel 1 a 0, ale to sa vlastne stane, a potom to SUMPRODUCT sčíta a dostane nás k 5. Tu dole, ak chceme spočítajte, koľko ich bolo 13. dňa v tomto roku, od veľmi podobného procesu. Aj keď budeme mať 365, odovzdajte to funkcii DAY a skontrolujte, koľko ich je 13, dobre. Ako príklad pre 92 000 riadkov, viete, dostávame deň, dostávame všedný deň, kontrolujeme, či je, DAY = 13, kontrolujeme, či je WEEKDAY = FALSE, vynásobíme to * toto,a iba v prípadoch, keď je piatok trinásteho, skončí ako PRAVDA. SUMPRODUCT potom hovorí „Add all those up“, a tak dostaneme 86, doslova 91895 výpočtov + SUM, 91896, ktoré sa dejú vo vnútri tohto jedného vzorca, je to šialene silné! Choďte si kúpiť Mikeovu knihu, je to úžasná kniha, otvorí vám celý svet vzorcov programu Excel a vlastne by ste si mali kúpiť obe knihy. Kúpte si moju knihu, kúpte si Mikeovu knihu a budete mať úžasnú zbierku, ktorá vás prevedie zvyškom roka.otvorí vám celý svet vzorcov programu Excel a vlastne by ste si mali kúpiť obe knihy. Kúpte si moju knihu, kúpte si Mikeovu knihu a budete mať úžasnú zbierku, ktorá vás prevedie zvyškom roka.otvorí vám celý svet vzorcov programu Excel a vlastne by ste si mali kúpiť obe knihy. Kúpte si moju knihu, kúpte si Mikeovu knihu a budete mať úžasnú zbierku, ktorá vás prevedie zvyškom roka.
Dobre, teda zrekapitulujte: existuje tajná trieda vzorcov, ktorá sa nazýva maticové vzorce, a maticový vzorec dokáže vykonať tisíce medzivýpočtov. Zvyčajne vyžadujú stlačenie klávesov Ctrl + Shift + Enter, ale nie vždy, a najlepšou knihou pre maticové vzorce je kniha „Ctrl + Shift + Enter“ od Mika Girvina. Dobre, takže INDIRECT vám umožní použiť zreťazenie na vytvorenie niečoho, čo vyzerá ako odkaz na bunku, a potom INDIRECT prejde na tento odkaz na bunku. Zreťazenie dvoch dátumov dvojbodkou bude ukazovať na rozsah riadkov v programe Excel a potom požiadanie o RIADOK NEPRIAMEHO dátumu1: dátum2 vyskočí pole mnohých po sebe idúcich čísel, možno 31, možno 365 alebo možno 85000. Skontrolujte každý dátum, aby ste zistili, či WEEKDAY = piatok, každý deň skontrolujte, či je DEN = 13, vynásobte tieto dve polia TRUE a FALSE pomocou SUMPRODUCT. V mnohých prípadochll use - na premenu TRUE / FALSE na 1 a 0, aby SUMPRODUCT mohol fungovať. Je to úžasný vzorec, nevytvoril som ho, ale našiel som ho na výveske. Keď som sa k tomu dopracoval, hovorím „Páni, toto je naozaj super!“
Dobre, chcem sa vám poďakovať, že ste sa zastavili, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2026.xlsx
