Формулы массивов Excel очень мощные. Вы можете заменить тысячи формул одной формулой, если научитесь трюку Ctrl + Shift + Enter. Сегодня одна формула массива выполняет 86 000 вычислений.
Трискаидекафобия - это страх пятницы 13-го числа. Эта тема ничего не вылечит, но покажет вам совершенно потрясающую формулу, заменяющую 110 268 формул. В реальной жизни мне никогда не приходится подсчитывать, сколько пятниц, 13-го числа произошло за мою жизнь, но сила и красота этой формулы иллюстрируют мощь Excel.
Скажите, что у вас есть друг, который суеверно относится к пятнице 13-го числа. Вы хотите проиллюстрировать, сколько Пятницы 13-го числа пережил ваш друг.
Создайте простую таблицу ниже, указав дату рождения в B1 и =TODAY()B2. Затем дикая формула в B6 оценивает каждый день, когда ваш друг был жив, чтобы выяснить, сколько из этих дней было пятницей и приходилось на 13-е число месяца. Для меня это число 86. Бояться нечего.
Кстати, 17.02.1965 действительно мой день рождения. Но я не хочу, чтобы ты присылал мне поздравительную открытку. Вместо этого, на мой день рождения, я хочу, чтобы вы позволили мне объяснить, как работает эта удивительная формула, шаг за шагом.
Вы когда-нибудь использовали функцию КОСВЕННО? Когда вы попросите =INDIRECT("C3"), Excel перейдет к C3 и вернет все, что находится в этой ячейке. Но INDIRECT более эффективен, когда вы вычисляете ссылку на ячейку на лету. Вы можете настроить призовое колесо, где кто-то выбирает букву между A и C, а затем выбирает число от 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 функции ДВССЫЛ, она указывает на все строки.
Следующее, что делает формула убийцы, - это запрашивает расширение 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 я получаю ДЕНЬ () этой даты. В столбце 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))в реальной жизни и отправьте мне по электронной почте (pub at dot com). Я отправлю приз первым 100 ответящим людям. Наверное, не курортный отдых. Скорее Биг Мак. Но так бывает с призами. Много Биг-Маков и не так много курортных отелей.
Я впервые увидел эту формулу, размещенную на доске объявлений Экимом в 2003 году. Кредит был отдан Харлану Гроуву. Формула также появилась в книге Боба Умласа «Это не Excel, это волшебство». Майк Делейни, Мени Порат и Тим Шитс предложили трюк «минус / минус». SUMPRODUCT был предложен Одри Линн и Стивеном Уайтом. Спасибо вам всем.
Смотреть видео
- Существует секретный класс формул, называемый формулами массива.
- Формула массива может выполнять тысячи промежуточных вычислений.
- Они часто требуют нажатия Ctrl + Shift + Enter, но не всегда.
- Лучшая книга по формулам массива - Ctrl + Shift + Enter Майка Гирвина.
- INDIRECT позволяет использовать конкатенацию для создания чего-то похожего на ссылку на ячейку.
- Даты красиво отформатированы, но хранятся в виде количества дней с 1 января 1900 года.
- Объединение двух дат указывает на диапазон строк в Excel.
- Запрашивая
ROW(INDIRECT(Date1:Date2))волю, "выскочит" массив из множества последовательных чисел - Использование функции WEEKDAY, чтобы выяснить, является ли дата пятницей.
- Сколько пятниц бывает в июле?
- Чтобы наблюдать за вычислением формулы в замедленном режиме, используйте инструмент Вычислить формулу.
- Сколько 13-го происходит в этом году?
- Сколько в пятницу 13-е произошло между двумя свиданиями?
- Проверьте каждую дату, чтобы узнать, является ли ДЕНЬ НЕДЕЛИ пятницей.
- Проверьте каждую дату, чтобы узнать, ДЕНЬ ли 13.
- Умножьте эти результаты с помощью СУММПРОИЗВ
- Используйте -, чтобы преобразовать True / False в 1/0
Стенограмма видео
Изучайте Excel из подкаста, выпуск 2026 - Моя любимая формула во всем Excel!
Подкастируя всю книгу, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения!
Хорошо, это была 30-я тема в книге, мы были как бы в конце раздела с формулами или в середине раздела с формулами, и я сказал, что должен включить мою любимую формулу на все времена. Это просто потрясающая формула, нужно ли считать число пятница, 13-е или нет, она открывает мир в целую секретную область Excel, называемую формулами массива! Введите дату начала, введите дату окончания, и эта формула вычислит количество пятниц 13 числа, которое произошло между этими двумя датами. На самом деле он выполняет пять вычислений каждый день между этими двумя датами, 91895 вычислений + СУММ, 91896 вычислений, происходящих внутри этой одной маленькой формулы, хорошо. Теперь, к концу этого эпизода, вы будете очень заинтригованы формулами массива. Хочу отметить,у моего друга Майка Гирвина есть лучшая книга по формулам массива под названием «Ctrl + Shift» Enter », это недавняя печать с синей обложкой, которая раньше была желто-зеленой. Какую бы книгу вы ни выбрали, это отличная книга с одинаковым содержанием как в желтой, так и в зеленой.
Хорошо, давайте начнем с внутренней части этого, с формулы, которую вы, возможно, не слышали, называется НЕПРЯМОЙ. INDIRECT позволяет нам объединить или каким-то образом создать фрагмент текста, который выглядит как ссылка на ячейку. Хорошо, допустим, у нас здесь призовое колесо, и я просто попросил вас выбрать между A, B и C. Хорошо, вы выбираете это и выбираете C, а затем выбираете это и выбираете 3, хорошо, и ваш приз это курортный отдых, потому что это то, что хранится в C3. И формула здесь объединяется вместе все, начиная с C5 и все, что находится в C6, с помощью &, а затем передает это в INDIRECT. Итак, = INDIRECT (C5 & C6), в данном случае, это C3, который должен быть сбалансированной ссылкой. НЕПРЯМОЙ говорит: «Эй, мы собираемся пойти в C3 и вернуть ответ оттуда, хорошо?» Еще в Lotus 1-2-3 это называлось функцией @@,в Excel переименовали в КОСВЕННО. Хорошо, так что у вас есть КОСВЕННЫЙ, а теперь вот удивительная вещь, которая происходит внутри там.
У нас есть две даты, как Excel сохраняет даты, 17.02.1965, это действительно просто форматирование. Если мы пойдем и посмотрим на фактическое число за этим, то получится 23790, что означает, что это 23790 дней с 01.01.1900 и 42167 дней с 01.01.1980. На Mac это будет с 01.01.1904, поэтому даты будут примерно на 3000. Хорошо, вот как Excel хранит это, хотя он показывает это нам, благодаря этому числовому формату в виде даты, но если мы объединим вместе B3, a: и B4, это фактически даст нам числа, хранящиеся за кулисами. Итак, = B3 & ”:” & B4, и если мы передадим это в INDIRECT, он будет указывать на все строки от 23790 до 42167.
Итак, есть НЕПРЯМОЙ B6, я попросил СТРОКУ этого, это даст мне целую кучу ответов, и чтобы выяснить, сколько ответов я использовал, посчитайте, хорошо. И для того, чтобы это сработало, если я просто нажму Enter, это не сработает, мне придется удерживать Ctrl и Shift и нажать Enter, и я вижу, что здесь добавляется () вокруг формулы. Он сообщает Excel перейти в режим суперформулы, режим формулы массива и выполнить все вычисления для всего, что извлекается из этого массива, 18378, хорошо. Итак, это удивительный трюк, косвенно связанный с date1: date2, передайте его функции ROW, и вот небольшой пример.
Итак, мы просто хотим выяснить, сколько пятниц произошло в этом июле, вот дата начала, вот дата окончания, и для каждой из этих строк я собираюсь запросить ДЕНЬ НЕДЕЛИ. WEEKDAY сообщает нам, какой сегодня день недели, и здесь, в двух аргументах, пятница будет иметь значение 5. Итак, я ищу ответ, и мы собираемся выбрать эту формулу, перейти к формулам, и "Вычислить формулу", и "Вычислить формулу" - отличный способ наблюдать, как формула вычисляется в замедленном темпе. Итак, есть B3, 1 июля, и вы видите, что оно меняется на число, а затем мы присоединяемся к двоеточию, так, вот B4, который изменится на число, и теперь мы получаем текст 42186: 42216. На этом этапе мы передаем это в строку ROW, и это простое маленькое выражение превратится прямо здесь в 31 значение.
Теперь, в примере, где у меня было все с 1965 по 2015 год, выскочило бы 86000 значений, верно, а вы не хотите делать это и оценивать формулу, потому что это было бы безумием, хорошо? Но вы можете увидеть, что здесь происходит с 31, и теперь я передаю эти 31 день функции WEEKDAY, и мы получаем 3-4-5. Итак, 3 означает, что это была среда, затем 4 означает, что это был четверг, а затем 5 означает, что это была пятница. Возьмите все эти 31 значение и посмотрите, равно ли они 5, что является пятницей, и мы получим кучу ЛОЖНЫХ и ИСТИННЫХ значений, поэтому среда, четверг, пятница, а затем 7 ячеек позже будут следующим ИСТИННЫМ, классно!
Хорошо, в этом случае у нас есть 5 ИСТИННЫХ и 26 ЛОЖНЫХ значений. Чтобы сложить их, мне нужно преобразовать ЛОЖЬ в 0 и ИСТИНУ в 1, и очень распространенный способ сделать это - использовать - . Хорошо, к сожалению, он не показал ответа там, где мы видели целую кучу единиц и нулей, но на самом деле это то, что происходит, а затем SUMPRODUCT складывает это и приводит нас к 5. Здесь, внизу, если мы хотим выяснить, сколько 13 числа месяца было в этом году, от этой даты начала до этой даты окончания, очень похожий процесс. Хотя у нас будет 365, передайте это функции ДЕНЬ и проверьте, сколько их 13, хорошо. Для примера строки 92000, как вы знаете, мы получаем день, мы получаем день недели, проверяем, DAY = 13, проверяем, не является ли WEEKDAY = FALSE, умножая это * this,и только в тех случаях, когда пятница, 13-е, оказывается ИСТИНА. Затем СУММПРОИЗВ говорит: «Сложите все это», и вот как мы получаем 86, буквально 91895 вычислений + СУММ, 91896, происходящие внутри этой одной формулы, это безумно мощно! Пойдите, купите книгу Майка, это потрясающая книга, она откроет вам целый мир формул Excel, и на самом деле вам стоит просто купить обе книги. Купите мою книгу, купите книгу Майка, и у вас будет потрясающая коллекция, в которой вы сможете прожить остаток года.он откроет вам целый мир формул Excel, и на самом деле вам стоит просто купить обе книги. Купите мою книгу, купите книгу Майка, и у вас будет потрясающая коллекция, в которой вы сможете прожить остаток года.он откроет вам целый мир формул Excel, и на самом деле вам стоит просто купить обе книги. Купите мою книгу, купите книгу Майка, и у вас будет потрясающая коллекция, в которой вы сможете прожить остаток года.
Хорошо, подведем итоги: существует секретный класс формул, называемый формулами массива, а формула массива может выполнять тысячи промежуточных вычислений. Обычно они требуют, чтобы вы нажимали Ctrl + Shift + Enter, но не всегда, и лучшая книга по формулам массива - книга Майка Гирвина «Ctrl + Shift + Enter». Хорошо, поэтому INDIRECT позволяет использовать конкатенацию для создания чего-то, что выглядит как ссылка на ячейку, а затем INDIRECT переходит к этой ссылке. Объединение двух дат с двоеточием будет указывать на диапазон строк в Excel, а затем запрос СТРОКИ НЕПРЯМОЙ даты date1: date2 выскочит массив из многих последовательных чисел, может быть 31, может быть 365 или может быть 85000. Проверить каждую дату, чтобы увидеть, является ли WEEKDAY = Friday, проверять каждый день, чтобы видеть, = DAY = 13, умножьте эти два массива ИСТИНА и ЛОЖЬ, используя СУММПРОИЗВ. Во многих случаях мыЯ воспользуюсь - для преобразования ИСТИНА / ЛОЖЬ в единицы и 0, чтобы СУММПРОИЗВ работал. Это потрясающая формула, я не создавал ее, я нашел ее на доске сообщений, когда я работал над ней, я такой: «Вау, это действительно круто!»
Хорошо, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другой сетевой трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2026.xlsx
