Формулите за масив на Excel са супер мощни. Можете да замените хиляди формули с една формула, след като научите трика Ctrl + Shift + Enter. Днес формула с един масив прави 86 000 изчисления.
Triskaidekaphobia е страхът от петък 13-ти. Тази тема няма да излекува нищо, но ще ви покаже абсолютно невероятна формула, която замества 110 268 формули. В реалния живот никога не трябва да броим колко петък 13-ти са настъпили през живота ми, но силата и красотата на тази формула илюстрират силата на Excel.
Кажете, че имате приятел, който е суеверен за петък 13-ти. Искате да илюстрирате колко петък, 13-и е преживял вашият приятел.
Настройте простия работен лист по-долу, с дата на раждане в B1 и =TODAY()в B2. След това дива формула в B6 оценява всеки ден, че вашият приятел е жив, за да разбере колко от тези дни са били петък и са паднали на 13-то число на месеца. За мен числото е 86. Няма от какво да се страхувам.
Между другото, 17.02.1965 г. наистина е моят рожден ден. Но не искам да ми изпращате картичка за рожден ден. Вместо това, за рождения си ден, искам да ми позволите да обясня как работи тази невероятна формула, една малка стъпка в даден момент.
Използвали ли сте някога функцията INDIRECT? Когато поискате =INDIRECT("C3"), Excel ще премине към C3 и ще върне всичко, което е в тази клетка. Но INDIRECT е по-мощен, когато изчислявате референтната клетка в движение. Можете да настроите наградно колело, където някой да избере буква между А и С и след това да избере число между 1 и 3. Когато обедините двата отговора, ще имате адрес на клетка и каквото и да е на този адрес на клетката, е наградата . Изглежда спечелих фотокнига вместо престоя в курорта.
Знаете ли как Excel съхранява дати? Когато Excel ви показва 17.02.1965 г., той съхранява 23790 в клетката, защото 17.02.1965 г. е 23790-ият ден на 20-ти век. В основата на формулата е конкатенацията, която обединява началната дата и двоеточието и крайната дата. Excel не използва форматираната дата. Вместо това използва серийния номер зад кулисите. Така B3&":"&B4става 23790: 42167. Вярвате или не, това е валидна препратка към клетката. Ако искате да съберете всичко в редове от 3 до 5, можете да използвате =SUM(3:5). Така че, когато предадете 23790: 42167 на функцията INDIRECT, тя сочи към всички редове.
Следващото нещо, което формулата на убиеца прави, е да поиска ROW(23790:42167). Обикновено преминавате една клетка: =ROW(D17)е 17. Но в този случай преминавате хиляди клетки. Когато поискате ROW(23790:42167)и завършите формулата с Ctrl + Shift + Enter, Excel всъщност връща всяко число от 23790, 23791, 23792 и така нататък до 42167.
Тази стъпка е невероятната стъпка. В тази стъпка преминаваме от две числа и изскачаме масив от 18378 числа. Сега трябва да направим нещо с този набор от отговори. Клетка B9 от предишната фигура просто брои колко отговора получаваме, което е скучно, но доказва, че ROW(23790:42167)връща 18378 отговора.
Нека опростим драстично първоначалния въпрос, за да можете да видите какво се случва. В този случай ще намерим броя на петките през юли 2015 г. Формулата, показана по-долу в B7, дава правилния отговор в B6.
В основата на формулата е ROW(INDIRECT(B3&":"&B4)). Това ще върне 31-те дати през юли 2015 г. Но след това формулата предава тези 31 дати на WEEKDAY(,2)функцията. Тази функция ще върне 1 за понеделник, 5 за петък и т.н. Така че големият въпрос е колко от тези 31 дати връщат 5, когато бъдат предадени на WEEKDAY(,2)функцията.
Можете да гледате изчислението на формулата в забавен каданс, като използвате командата Evaluate Formula в раздела Formula на лентата.
Това е след като INDIRECT преобразува датите в референтен ред.
В следващата стъпка Excel е на път да предаде 31 числа на функцията WEEKDAY. Сега, във формулата на убиеца, той би предал 18 378 числа вместо 31.
Ето резултатите от функциите на 31 WEEKDAY. Не забравяйте, че искаме да преброим колко са 5.
Проверката дали предишният масив е 5 връща цял куп стойности True / False. Има 5 истински стойности, по една за всеки петък.
Не мога да ви покажа какво се случва по-нататък, но мога да го обясня. Excel не може да СЪБИРА куп стойности True и False. Това е против правилата. Но ако умножите тези True и False стойности по 1 или ако използвате функцията двойно отрицателно или N (), преобразувате True стойностите в 1 и False стойностите в 0. Изпратете тези на SUM или SUMPRODUCT и ще вземете броя на истинските стойности.
Ето подобен пример, за да преброите колко месеца имат ден 13 в тях. Това е тривиално, за което трябва да се мисли: Всеки месец има 13-ти, така че отговорът за цяла година е по-добре да бъде 12. Excel прави математика, генерира 365 дати, изпраща ги всички към функцията DAY () и изчислява колко от тях нагоре на 13-о число на месеца. Отговорът, както се очаква, е 12.
Следващата фигура е работен лист, който прави цялата логика формулата на един убиец, показана в началото на тази тема. Създадох ред за всеки ден, в който съм жив. В колона Б получавам DAY () от тази дата. В колона C получавам WEEKDAY () на датата. В колона D равно ли е B на 13? В колона E C = 5 ли е? След това умножавам D * E, за да преобразувам True / False в 1/0.
Скрих много редове, но ви показвам три произволни дни в средата, които случайно са и петък, и 13-и.
Общата сума във F18381 е същата 86, която върна първоначалната ми формула. Страхотен знак. Но този работен лист има 110 268 формули. Моята оригинална формула убиец прави цялата логика на тези 110 268 формули в една формула.
Изчакайте. Искам да поясня. В оригиналната формула няма нищо вълшебно, което да стане умно и да съкрати логиката. Тази оригинална формула наистина прави 110 268 стъпки, вероятно дори повече, защото оригиналната формула трябва да изчисли масива ROW () два пъти.
Намерете начин да го използвате ROW(INDIRECT(Date:Date))в реалния живот и ми го изпратете по имейл (кръчма на dot com). Ще изпратя награда на първите 100 души да отговорят. Вероятно не е престой в курорта. По-вероятно е Big Mac. Но така стоят нещата с наградите. Много Биг Мак и не много престой в курорта.
За първи път видях тази формула, публикувана на борда за съобщения през 2003 г. от Ekim. Кредит беше даден на Харлан Гроув. Формулата се появи и в книгата на Боб Умлас „Това не е Excel, това е магия“. Майк Дилейни, Меню Порат и Тим Шийтс предлагат трик минус / минус. SUMPRODUCT е предложен от Одри Лин и Стивън Уайт. Благодаря на всички ви.
Гледам видео
- Има таен клас формули, наречени Array Formulas.
- Формулата на масив може да направи хиляди междинни изчисления.
- Те често изискват да натиснете Ctrl + Shift + Enter, но не винаги.
- Най-добрата книга за формули за масиви е Ctrl + Shift + Enter на Майк Гирвин.
- INDIRECT ви позволява да използвате конкатенация, за да изградите нещо, което прилича на препратка към клетка.
- Датите са добре форматирани, но се съхраняват като брой дни от 1 януари 1900 г.
- Обединяването на две дати ще сочи към редица редове в Excel.
- Искането за
ROW(INDIRECT(Date1:Date2))волята „изскача“ масив от много последователни числа - Използване на функцията WEEKDAY, за да разберете дали датата е петък.
- Колко петка се случват през този юли?
- За да гледате изчисление на формула на забавен каданс, използвайте инструмента за оценка на формула
- Колко 13-ти се случват тази година?
- Колко петък 13-ти се случи между две дати?
- Проверявайте всяка дата, за да видите дали уикендът е петък
- Проверете всяка дата, за да видите дали ДЕНЪТ е 13
- Умножете тези резултати, като използвате SUMPRODUCT
- Използване - за преобразуване на True / False в 1/0
Видео транскрипция
Научете Excel от подкаст, епизод 2026 - Моята любима формула в цял Excel!
Подкастирайки цялата тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
Добре, това беше 30-та тема в книгата, бяхме някак в края на раздела за формули или в средата на раздела за формули и казах, че трябва да включа любимата си формула за всички времена. Това е просто невероятна формула, независимо дали трябва да броите числото петък 13-ти или не, тя отваря свят в цяла тайна област на Excel, наречена Array Formulas! Поставете начална дата, поставете крайна дата и тази формула изчислява броя на петък 13-ти, който се е случил между тези две дати. Всъщност прави пет изчисления на всеки един ден между тези две дати, 91895 изчисления + SUM, 91896 изчисления, случващи се в рамките на тази малка формула, добре. Сега, в края на този епизод, ще бъдете толкова заинтригувани от формули за масиви. Искам да отбележа,моят приятел Майк Гирвин има най-добрата книга за формули за масиви, наречена “Ctrl + Shift” Enter ”, това е скорошен печат със синя корица, която преди е била жълта и зелена корица. Сега, която и да получите, е страхотна книга със същото съдържание както в жълтото, така и в зеленото.
Добре, нека да започнем от вътрешната страна на това, с формула, която може да не сте чували, наречена INDIRECT. INDIRECT ни позволява да обединим или по някакъв начин да изградим малко текст, който прилича на препратка към клетка. Добре, да кажем, че тук имаме колело за награди и аз просто ви помолих да изберете между A, B и C. Добре, така че вие изберете това и изберете C, а след това изберете това и изберете 3, добре, и вашата награда е престой в курорта, защото това е, което се съхранява в C3. И формулата тук се свързва заедно, независимо от C5 и каквото е в C6, като се използва & и след това се предава на INDIRECT. Така че = INDIRECT (C5 и C6), в този случай е C3, което трябва да бъде балансирана референтна референция. INDIRECT казва „Хей, ще отидем до C3 и ще върнем отговора от това, нали?“ Обратно в Lotus 1-2-3 това се наричаше функция @@,в Excel го преименуваха на НЕПРЯКО. Добре, значи имате в НЕПРЯКОТО, сега ето невероятното нещо, което се случва вътре от там.
Имаме две дати, как Excel съхранява дати, 17.2.1965 г., това всъщност е просто форматиране. Ако отидем и погледнем действителния брой зад това, той е 23790, което означава, че е 23790 дни от 1/1/1900 и 42167 дни от 1/1/1980. На Mac това ще бъде от 1/1/1904, така че датите ще бъдат около 3000 отстъпки. Добре, така го съхранява Excel, той ни го показва, все пак, благодарение на този цифров формат като дата, но ако обединим B3 и a: и B4, това всъщност ще ни даде числата, съхранявани зад кулисите. Така че = B3 & ”:” & B4, и ако бихме предали това на INDIRECT, то всъщност ще сочи към всички редове от 23790 до 42167.
Така че има НЕПРЯКОТО от B6, поисках РЯДА на това, което ще ми даде цял куп отговори и за да разбера колко отговора съм използвал, добре. И за да направя това да работи, ако просто натисна Enter, това не работи, трябва да задържа Ctrl и Shift и да натисна Enter и да видя, че това добавя () около формулата тук горе. Той казва на Excel да премине в режим на супер формула, режим на формула на масив и да направи цялата математика за всичко, изскочило от този масив, 18378, добре. И така, това е невероятен трик, косвен за date1: date2, предайте го на функцията ROW и ето един малък пример.
Така че просто искаме да разберем колко петъка са настъпили през този юли, ето начална дата, ето крайна дата и за всеки от тези редове ще поискам СРЕДНИЦАТА. WEEKDAY ни казва кой ден от седмицата е и тук, в аргумента, 2 петък ще има стойност 5. И така, търся отговора и ще изберем тази формула, отидете на Formulas, и Оценете формула, а Оценете формула е чудесен начин да гледате как формула се изчислява в забавен каданс. И така, има B3, 1 юли, и виждате, че това се променя на число и след това се присъединяваме към дебелото черво, нали, има B4, което ще се промени на число и сега получаваме текста, 42186: 42216. В този момент предаваме това на ROW и този прост малък израз ще се превърне в 31 стойности точно тук.
Сега, в примера, в който имах всичко от 1965 до 2015 г., ще изскочи 86000 стойности, нали, и не искате да правите това и да оценявате формула, защото би било някак ненормално, нали? Но можете да видите какво се случва тук с 31, и сега предавам тези 31 дни на функцията WEEKDAY и получаваме 3-4-5. Така че 3 означава, че е била сряда, а след това 4 означава, че е бил четвъртък, а след това 5 означава, че е бил петък. Вземете всички тези 31 стойности и вижте дали те са = 5, което е петък, и ще получим куп ФАЛШИ и ИСТИНИ, така че сряда, четвъртък, петък и след това 7 клетки по-късно ще бъдат следващите ИСТИНИ, страхотно!
Добре, така че в този случай имаме 5 TRUE и 26 FALSEs. За да ги събера, трябва да преобразувам FALSE като 0 и TRUEs на 1, и много често срещан начин да се направи това е да се използва - . Добре, за съжаление не показа отговора там, където видяхме цял куп 1 и 0, но всъщност това се случва и след това SUMPRODUCT го добавя и ни отвежда до 5. Тук долу, ако искаме разберете колко от 13-то число на месеца имаше тази година, от тази начална дата до тази крайна дата, много подобен процес. Въпреки че ще имаме 365, предайте това на функцията DAY и проверете колко са 13, добре. За пример 92000 ред, знаете, получаваме деня, получаваме делничния ден, проверяваме дали DAY = 13, проверяваме дали WEEKDAY = FALSE, умножаваме това * това,и само в случаите, когато е петък, 13-ти, това се оказва ВЯРНО. След това SUMPRODUCT казва „Добавете всички тези“ и по този начин получаваме 86, буквално 91895 изчисления + SUM, 91896, които се случват вътре в тази една формула, това е невероятно мощно! Отидете да купите книгата на Майк, това е невероятна книга, тя ще ви отвори цял свят от формули на Excel и всъщност трябва просто да купите и двете книги. Купете моята книга, купете книгата на Майк и ще имате страхотна колекция, която ще ви преведе през останалата част от годината.ще ви отвори цял свят от формули на Excel и всъщност трябва просто да купите и двете книги. Купете моята книга, купете книгата на Майк и ще имате страхотна колекция, която ще ви преведе през останалата част от годината.ще ви отвори цял свят от формули на Excel и всъщност трябва просто да купите и двете книги. Купете моята книга, купете книгата на Майк и ще имате страхотна колекция, която ще ви преведе през останалата част от годината.
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!”
Добре, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2026.xlsx
