Формули масиву Excel надзвичайно потужні. Ви зможете замінити тисячі формул однією формулою, коли вивчите фокус Ctrl + Shift + Enter. Сьогодні одна формула масиву робить 86 000 обчислень.
Тріскаїдекафобія - це страх п’ятниці 13-го. Ця тема нічого не вилікує, але вона покаже вам абсолютно дивовижну формулу, яка замінює 110 268 формул. У реальному житті мені ніколи не доводиться підраховувати, скільки п’ятниці 13-го відбулося за моє життя, але сила та краса цієї формули ілюструє силу Excel.
Скажіть, що у вас є друг, який забобонний щодо п’ятниці 13-го. Ви хочете проілюструвати, скільки п’ятниці 13-го пережив ваш друг.
Налаштуйте простий аркуш нижче, з датою народження в B1 і =TODAY()в B2. Потім дика формула в В6 кожен день оцінює, що ваш друг був живий, щоб з’ясувати, скільки з цих днів було п’ятницею і припало на 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)функції.
Ви можете спостерігати за розрахунком формули в уповільненому темпі, використовуючи команду «Оцінити формулу» на вкладці «Формула» на стрічці.
Це відбувається після перетворення INDIRECT дат у посилання на рядок.
На наступному кроці Excel збирається передати 31 число функції WEEKDAY. Тепер, у формулі вбивці, воно передало б 18 378 чисел замість 31.
Ось результати функцій 31 WEEKDAY. Пам’ятайте, ми хочемо порахувати, скільки це 5.
Перевірка, чи є попередній масив 5, повертає цілу купу значень True / False. Існує 5 справжніх значень, по одному для кожної п’ятниці.
Я не можу показати вам, що відбувається далі, але можу пояснити це. Excel не може підсумувати купу справжніх і помилкових значень. Це суперечить правилам. Але якщо ви помножите ці значення True і False на 1 або якщо ви використовуєте функцію подвійного від'ємного значення або N (), ви перетворюєте значення True на 1, а значення False на 0. Надішліть їх на SUM або SUMPRODUCT, і ви отримаєте отримати кількість справжніх значень.
Ось подібний приклад, щоб підрахувати, скільки місяців має день 13. Про це тривіально подумати: кожен місяць має 13-е число, тому відповідь на цілий рік краще буде 12. Excel робить математику, генерує 365 дат, відправляє їх усіх у функцію DAY () і з’ясовує, скільки закінчується 13 числа місяця. Відповідь, як очікувалося, 12.
Наступна фігура - це робочий аркуш, який робить всю логіку формулою одного вбивці, показаною на початку цієї теми. Я створив рядок на кожен день, коли я був живий. У стовпці B я отримую 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 людям, які відповідуть. Можливо, не курортне перебування. Швидше за все, Біг Мак. Але так відбувається з призами. Багато Біг Маків і не так багато курортних готелів.
Я вперше побачив цю формулу, опубліковану на дошці повідомлень у 2003 році Екімом. Кредит отримав Харлан Гроув. Формула також з'явилася в книзі Боба Умласа "Це не 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.02.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. На цьому етапі ми передаємо це в РЯД, і цей простий маленький вираз перетвориться на 31 значення саме тут.
Зараз, у прикладі, коли у мене було все з 1965 по 2015 рік, воно випустило б 86000 значень, так, і ви не хочете це робити і оцінювати формулу, бо це було б якось божевільно, добре? Але ви можете побачити, що тут відбувається з 31, і зараз я передаю ці 31 дні функції ВІДТЕНКУ, і ми отримуємо 3-4-5. Отже 3 означає, що це була середа, а потім 4 означає, що це був четвер, а потім 5 означає, що це була п’ятниця. Візьміть усі ці 31 значення і подивіться, якщо вони = 5, що є п’ятницею, і ми отримаємо купу НЕПРАВИЛЬНИХ та ІСТИННИХ, тому середа, четвер, п’ятниця, а потім 7 клітинок пізніше буде наступною ІСТИНОЮ чудово!
Добре, отже, у цьому випадку ми маємо 5 ІСТИН і 26 НЕПРАВИЛЬНИХ. Для того, щоб їх скласти, мені потрібно перетворити ФАЛЬШУ на 0 та ІСТИНИ на 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
