Vzorce aplikace Excel Array jsou super výkonné. Jakmile se naučíte trik Ctrl + Shift + Enter, můžete nahradit tisíce vzorců jediným vzorcem. Dnes jeden maticový vzorec provádí 86 000 výpočtů.
Triskaidekafobie je strach z pátku třináctého. Toto téma nic nevyléčí, ale ukáže vám naprosto úžasný vzorec, který nahradí 110 268 vzorců. V reálném životě nikdy nemusím počítat, kolik pátku se v mém životě objevilo 13. místo, ale síla a krása tohoto vzorce ilustruje sílu Excelu.
Řekněme, že v pátek třináctého máte kamaráda, který je pověrčivý. Chcete ilustrovat, kolik pátku třináctého váš přítel prožil.
Níže nastavte jednoduchý list s datem narození v B1 a =TODAY()B2. Pak divoký vzorec v B6 každý den vyhodnotí, že váš přítel žil, aby zjistil, kolik z těch dnů bylo v pátek a připadlo na 13. měsíce. Pro mě je to číslo 86. Nic se nebojím.
Mimochodem, 17. 2. 1965 mám opravdu narozeniny. Ale nechci, abyste mi poslali přání k narozeninám. Místo toho k mým narozeninám chci, abyste mi po jednom krůčku vysvětlili, jak ten úžasný vzorec funguje.
Už jste někdy použili funkci NEPŘÍMÝ? Když o to požádáte =INDIRECT("C3"), Excel přejde na C3 a vrátí vše, co je v této buňce. Ale INDIRECT je výkonnější, když vypočítáváte referenci na buňku za běhu. Mohli byste nastavit výherní kolo, kde někdo vybere písmeno mezi A a C a poté vybere číslo mezi 1 a 3. Když spojíte dvě odpovědi, budete mít adresu buňky a cokoli je na této adrese buňky, je cena . Vypadá to, že jsem místo rekreačního pobytu vyhrál fotoknihu.
Víte, jak Excel ukládá data? Když vám Excel zobrazí 17. 2. 1965, ukládá do buňky 23790, protože 17. 2. 1965 byl 23 790. den 20. století. Srdcem vzorce je zřetězení, které spojuje počáteční datum a dvojtečku a konečné datum. Excel nepoužívá formátované datum. Místo toho používá sériové číslo v zákulisí. Tak se B3&":"&B4stává 23790: 42167. Věřte tomu nebo ne, to je platný odkaz na buňku. Pokud jste chtěli sečíst vše v řádcích 3 až 5, můžete použít =SUM(3:5). Když tedy předáte 23790: 42167 funkci INDIRECT, ukazuje na všechny řádky.
Další věc, kterou zabijácká formule dělá, je požádat o ROW(23790:42167). Za normálních okolností projdete jedinou buňkou: =ROW(D17)je 17. Ale v tomto případě předáváte tisíce buněk. Když požadujete ROW(23790:42167)a dokončíte vzorec pomocí Ctrl + Shift + Enter, Excel ve skutečnosti vrátí každé číslo od 23790, 23791, 23792 atd. Až po 42167.
Tento krok je úžasným krokem. V tomto kroku přejdeme od dvou čísel a „vyskakujeme“ řadu 18378 čísel. Nyní musíme s touto řadou odpovědí něco udělat. Buňka B9 na předchozím obrázku pouze počítá, kolik odpovědí dostaneme, což je nudné, ale dokazuje to, že ROW(23790:42167)vrací 18378 odpovědí.
Pojďme dramaticky zjednodušit původní otázku, abyste viděli, co se děje. V tomto případě zjistíme počet pátek v červenci 2015. Vzorec zobrazený níže v B7 poskytuje správnou odpověď v B6.
Jádrem vzorce je ROW(INDIRECT(B3&":"&B4)). Tím se vrátí 31 dat v červenci 2015. Ale vzorec pak předá těchto 31 dat WEEKDAY(,2)funkci. Tato funkce vrátí 1 pro pondělí, 5 pro pátek atd. Velkou otázkou tedy je, kolik z těchto 31 dat po předání WEEKDAY(,2)funkci vrátí 5 .
Výpočet vzorce můžete sledovat zpomaleně pomocí příkazu Vyhodnotit vzorec na kartě Vzorec na pásu karet.
Toto je poté, co INDIRECT převede data na odkaz na řádek.
V dalším kroku se aplikace Excel chystá předat 31 čísel funkci WEEKDAY. Nyní by v zabijáckém vzorci prošel 18 378 čísel místo 31.
Zde jsou výsledky 31 funkcí WEEKDAY. Pamatujte, že chceme spočítat, kolik jich je 5.
Kontrola, zda je předchozí pole 5, vrací celou řadu hodnot True / False. Existuje 5 skutečných hodnot, jedna pro každý pátek.
Nemohu vám ukázat, co se bude dít dál, ale mohu to vysvětlit. Excel nemůže SUMA hromadit pravdivé a nepravdivé hodnoty. Je to v rozporu s pravidly. Pokud ale tyto hodnoty True a False vynásobíte 1 nebo pokud použijete funkci dvojitého záporného čísla nebo N (), převedete hodnoty True na 1 a hodnoty False na 0. Pošlete je na SUM nebo SUMPRODUCT a získejte počet hodnot True.
Zde je podobný příklad, který spočítá, kolik měsíců má v sobě 13 dní. Je to triviální k zamyšlení: Každý měsíc má 13., takže odpověď na celý rok by měla být 12. Excel dělá matematiku, generuje 365 dat, posílá je všechny do funkce DAY () a zjišťuje, kolik končí až 13. měsíce. Odpověď je podle očekávání 12.
Další obrázek je list, který obsahuje veškerou logiku vzorce jednoho zabijáka zobrazeného na začátku tohoto tématu. Vytvořil jsem řádek pro každý den, kdy jsem žil. Ve sloupci B dostanu DEN () daného data. Ve sloupci C dostávám WEEKDAY () data. Ve sloupci D je B rovno 13? Je ve sloupci E C = 5? Pak vynásobím D * E, abych převedl True / False na 1/0.
Skryl jsem spoustu řádků, ale uprostřed vám ukážu tři náhodné dny, které byly shodou okolností jak pátek, tak 13. den.
Celkem v F18381 je stejných 86, že se vrátil můj původní vzorec. Velké znamení. Ale tento list má 110 268 vzorců. Můj původní zabijácký vzorec dělá veškerou logiku těchto 110 268 vzorců v jednom vzorci.
Počkejte. Chci to vyjasnit. V původním vzorci není nic magického, co by bylo chytřejší a zkrátilo by logiku. Tento původní vzorec skutečně dělá 110 268 kroků, pravděpodobně ještě více, protože původní vzorec musí vypočítat pole ROW () dvakrát.
Najděte způsob, jak to použít ROW(INDIRECT(Date:Date))v reálném životě, a pošlete mi jej e-mailem (pub at dot com). Pošlu cenu prvním 100 lidem, kteří mají odpovědět. Pravděpodobně ne rekreační pobyt. Pravděpodobněji velký Mac. Ale tak to chodí s cenami. Spousta velkých počítačů Mac a málo pobytů v letovisku.
Poprvé jsem viděl tento vzorec zveřejněný na nástěnce v roce 2003 Ekimem. Zásluhu na tom měl Harlan Grove. Vzorec se objevil také v knize Boba Umlasa This Isn't Excel, It Magic. Mike Delaney, Meni Porat a Tim Sheets navrhli trik minus / minus. SUMPRODUCT navrhli Audrey Lynn a Steven White. Děkuji vám všem.
Sledovat video
- Existuje tajná třída vzorců, která se nazývá maticové vzorce.
- Maticový vzorec může provádět tisíce mezilehlých výpočtů.
- Často vyžadují, abyste stiskli Ctrl + Shift + Enter, ale ne vždy.
- Nejlepší knihou o maticových vzorcích je Ctrl + Shift + Enter od Mika Girvina.
- INDIRECT vám umožní použít zřetězení k vytvoření něčeho, co vypadá jako odkaz na buňku.
- Data jsou pěkně naformátována, ale jsou uložena jako počet dní od 1. ledna 1900.
- Zřetězení dvou dat bude ukazovat na řadu řádků v aplikaci Excel.
- Žádost o
ROW(INDIRECT(Date1:Date2))vůli "vyskočí" na pole mnoha po sobě jdoucích čísel - Pomocí funkce WEEKDAY zjistíte, zda je datum pátek.
- Kolik pátek nastane v červenci tohoto roku?
- Chcete-li sledovat vzorec, který počítá zpomaleně, použijte nástroj Vyhodnotit vzorec
- Kolik třináctých se tento rok objeví?
- Kolik pátek 13. se stalo mezi dvěma daty?
- Zkontrolujte každé datum, abyste zjistili, zda je WEEKDAY pátek
- Zkontrolujte každé datum a zkontrolujte, zda je DEN 13
- Znásobte tyto výsledky pomocí SUMPRODUCT
- Použijte - pro převod True / False na 1/0
Přepis videa
Naučte se Excel z podcastu, epizoda 2026 - Můj oblíbený vzorec v celém Excelu!
Podcastováním celé této knihy se kliknutím na „i“ v pravém horním rohu dostanete do seznamu skladeb!
Dobře, bylo to 30. téma v knize, byli jsme tak trochu na konci sekce vzorců nebo uprostřed sekce vzorců a řekl jsem, že musím zahrnout svůj oblíbený vzorec všech dob. Jedná se o úžasný vzorec, ať už musíte počítat číslo v pátek 13. nebo ne, otevírá svět do celé tajné oblasti aplikace Excel s názvem Array Formulas! Vložte počáteční datum, vložte konečné datum a tento vzorec vypočítá počet pátku 13., který nastal mezi těmito dvěma daty. Ve skutečnosti dělá pět výpočtů každý den mezi těmito dvěma daty, 91895 výpočtů + SUM, 91896 výpočtů probíhajících uvnitř tohoto malého vzorce, dobře. Na konci této epizody vás pole vzorců pole tak zaujme. Chci poukázat,můj přítel Mike Girvin má nejlepší knihu o maticových vzorcích nazvanou „Ctrl + Shift“ Enter “, toto je nedávný tisk s modrým obalem, dříve žlutým a zeleným obalem. Ať už získáte kteroukoli z nich, je to skvělá kniha se stejným obsahem v žluté i zelené.
Dobře, pojďme začít zevnitř tohoto vzorce s vzorcem, který jste možná neslyšeli, nazvaný NEPŘÍMÝ. INDIRECT nám umožňuje zřetězit nebo nějakým způsobem vytvořit trochu textu, který vypadá jako odkaz na buňku. Dobře, řekněme, že zde máme výherní kolo, a právě jsem vás požádal, abyste si vybrali mezi A, B a C. Dobře, takže si vyberete toto a vyberete C a pak vyberete toto a vyberete 3, v pořádku a vaši cenu je rekreační pobyt, protože právě ten je uložen v C3. A vzorec je zde zřetězen společně od C5 a cokoli v C6 pomocí & a poté předáním NEPŘÍMÉ. Takže = INDIRECT (C5 a C6) je v tomto případě C3, což musí být vyvážená reference. INDIRECT říká: „Hej, půjdeme do C3 a vrátíme z toho odpověď, dobře?“ Zpět v Lotus 1-2-3 se tomu říkalo funkce @@,v aplikaci Excel jej přejmenovali na NEPŘÍMÝ. Dobře, takže máte NEPŘÍMÉ, tady je úžasná věc, která se děje uvnitř.
Máme dvě data, jak Excel ukládá data, 17. 2. 1965, to je opravdu jen formátování. Pokud bychom šli a podívali se na skutečné číslo za tím, je to 23790, což znamená, že je to 23790 dní od 1. 1. 1900 a 42 167 dní od 1. 1. 1980. Na počítačích Mac to bude od 1. 1. 1904, takže data budou asi 3000 slev. Dobře, tak to Excel ukládá, ukazuje nám to díky tomuto formátu čísel jako datum, ale pokud bychom spojili dohromady B3 a a: a B4, dalo by nám to čísla uložená v zákulisí. Takže = B3 & ”:” & B4, a pokud bychom to předali NEPŘÍMÉMU, ve skutečnosti to bude ukazovat na všechny řádky od 23790 do 42167.
Takže existuje NEPŘÍMÝ B6, požádal jsem o ŘÁD toho, což mi dá celou řadu odpovědí a přijít na to, kolik odpovědí jsem použil, dobře. A aby to fungovalo, když jen stisknu Enter, nebude to fungovat, musím držet Ctrl a Shift a stisknout Enter a vidět, že sem přidá () kolem vzorce. Říká aplikaci Excel, aby přešla do režimu superformule, režimu maticového vzorce a udělala veškerou matematiku pro všechno, co se z tohoto pole objevilo, 18378, v pořádku. To je tedy úžasný trik, nepřímý pro date1: date2, předejte to funkci ROW a zde je malý příklad.
Chceme tedy jen zjistit, kolik pátek došlo v červenci tohoto roku, zde je datum zahájení, zde je datum ukončení a pro každý z těchto řádků se zeptám na WEEKDAY. WEEKDAY nám říká, jaký den v týdnu to je, a tady v argumentu 2 budou mít pátky hodnotu 5. Hledám tedy odpověď a zvolíme tento vzorec, jdeme na vzorce, a Vyhodnoťte vzorec a Vyhodnoťte vzorec je skvělý způsob, jak sledovat, jak se vzorec vypočítává zpomaleně. Takže je tu B3, 1. července, a vidíte, že se to změní na číslo, a pak se připojíme k dvojtečce, správně, je tu B4, která se změní na číslo, a teď dostaneme text, 42186: 42216. V tomto okamžiku to předáme ROW a ten jednoduchý malý výraz se změní na 31 hodnot právě tady.
Nyní, v příkladu, kde jsem měl všechno od roku 1965 do roku 2015, by vyskočilo 86 000 hodnot, že, a to nechcete dělat a hodnotit vzorec, protože by to bylo trochu šílené, dobře? Ale můžete vidět, co se tady děje s 31, a teď předávám těch 31 dní funkci WEEKDAY a dostaneme 3-4-5. Takže 3 znamená, že to byla středa, a pak 4 znamená, že byl čtvrtek, a pak 5 znamená, že byl pátek. Vezměte všech těch 31 hodnot a zjistěte, zda jsou = 5, což je pátek, a dostaneme spoustu FALSE a TRUE, takže středa, čtvrtek, pátek a potom o 7 buněk později bude další PRAVDA, skvělý!
Dobře, takže v tomto případě máme 5 PRAVD a 26 NEPRAVDA, Abych je sčítal, musím převést NEPRAVDA na 0 a PRAVDA na 1, a velmi běžným způsobem je použít - . Dobře, bohužel to neukázalo odpověď tam, kde jsme viděli celou hromadu 1 a 0, ale to se vlastně stane, a pak to SUMPRODUCT sčítá a dostane nás do 5. Zde dole, pokud chceme zjistit, kolik 13. měsíce v tomto roce bylo letos, od tohoto data zahájení do tohoto data ukončení, velmi podobný proces. I když budeme mít 365, předejte to funkci DAY a zkontrolujte, kolik jich je 13, dobře. V příkladu řádku 92 000 víte, dostáváme den, dostáváme den v týdnu, kontrolujeme, zda, DAY = 13, kontrolujeme, zda WEEKDAY = FALSE, vynásobíme to * toto,a pouze v případech, kdy je pátek 13., to skončí jako PRAVDA. SUMPRODUCT pak říká „Add all those up“, a tak dostaneme 86, doslova 91895 výpočtů + SUM, 91896, které se dějí uvnitř tohoto jednoho vzorce, je to šíleně silné! Jděte si koupit Mikeovu knihu, je to úžasná kniha, otevře vám celý svět vzorců aplikace Excel a vlastně byste si měli koupit jen obě knihy. Kupte si moji knihu, kupte si Mikeovu knihu a budete mít úžasnou sbírku, která vás provede zbytkem roku.otevře vám celý svět vzorců aplikace Excel a ve skutečnosti byste si měli koupit obě knihy. Kupte si moji knihu, kupte si Mikeovu knihu a budete mít úžasnou sbírku, která vás provede zbytkem roku.otevře vám celý svět vzorců aplikace Excel a ve skutečnosti byste si měli koupit obě knihy. Kupte si moji knihu, kupte si Mikeovu knihu a budete mít úžasnou sbírku, která vás provede zbytkem roku.
Dobře, takže rekapitulace: existuje tajná třída vzorců, která se nazývá maticové vzorce, a maticový vzorec může provádět tisíce mezilehlých výpočtů. Obvykle vyžadují, abyste stiskli Ctrl + Shift + Enter, ale ne vždy, a nejlepší knihou pro maticové vzorce je kniha „Ctrl + Shift + Enter“ od Mikea Girvina. Dobře, takže INDIRECT vám umožní použít zřetězení k vytvoření něčeho, co vypadá jako odkaz na buňku, a pak INDIRECT přejde na tento odkaz na buňku. Zřetězení dvou dat dvojtečkou bude ukazovat na řadu řádků v aplikaci Excel a poté požádat o ŘÁDEK NEPŘÍMÉ datum1: datum2 vyskočí pole mnoha po sobě jdoucích čísel, možná 31, možná 365 nebo možná 85000. Zkontrolovat každé datum, abyste zjistili, zda WEEKDAY = pátek, každý den zkontrolujte, zda je DEN = 13, vynásobte tato dvě pole TRUE a FALSE pomocí SUMPRODUCT. V mnoha případechll use - pro převod PRAVDA / NEPRAVDA na 1 a 0, aby SUMPRODUCT mohl fungovat. Je to úžasný vzorec, nevytvořil jsem ho, našel jsem ho na vývěsce, jak jsem se k tomu dostal, jsem jako „Páni, toto je fakt super!“
Dobře, chci vám poděkovat, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2026.xlsx
