Как использовать формулы динамических массивов в Microsoft Excel

Если вы ежедневно работаете с электронными таблицами, вы, вероятно, заметили, что как только они начинают увеличиваться в количестве строк и столбцов, классические формулы Excel оказываются неэффективными для выполнения сложных вычислений. Вот тут-то и пригодятся формулы динамических массивов — одна из тех новых функций, которую, освоив, вы будете использовать практически в каждой рабочей книге.

В современных версиях Excel (особенно в Microsoft 365 ) появился новый механизм вычислений, позволяющий одной формуле генерировать несколько результатов одновременно, охватывая несколько смежных ячеек, без необходимости ручного копирования. Благодаря этому механизму «переполнения» стало намного проще сортировать, фильтровать, создавать списки или выполнять сложные вычисления, не прибегая к необычным уловкам или сочетаниям клавиш, таким как Ctrl+Shift+Enter.

Связанная статья:

Автоматизируйте построение диаграмм с использованием именованных диапазонов и динамических рядов в Excel.

Что такое переполнение в динамических матричных формулах?

В новой модели вычислений Excel формула может возвращать не только одно значение, но и целый упорядоченный набор результатов; этот набор часто называют массивом переполнения или диапазоном вывода . В этом случае Excel автоматически помещает эти значения в соседние ячейки, расширяя формулу вниз, вправо или в обоих направлениях, в зависимости от размера результата.

Например, если вы введете формулу =SORT(D2:D11,1,-1) в одну ячейку (например, F2), Excel сгенерирует отсортированный по убыванию список на основе диапазона D2:D11. Результат займет 10 строк, но вы вводите формулу только в одну ячейку; остальные позиции будут заполнены самим Excel с помощью механизма переполнения.

Формулы, размер результата которых может изменяться в зависимости от исходных данных, называются динамическими формулами массивов . Когда такие формулы возвращают диапазон результатов, выходящий за пределы ячейки, в которой была написана формула, говорят, что формула «переполнилась», а область, которую она занимает, называется диапазоном переполнения.

На практике это означает, что многие функции, такие как SORT, FILTER, RANDMARR, SEQUENCE или UNIQUE, теперь предназначены для возврата полных массивов данных, готовых к использованию , в гораздо более простом и читаемом виде, чем с помощью старых формул для работы с массивами.

Как работает диапазон переполнения в Excel

Когда вы подтверждаете формулу динамического массива, нажав только клавишу Enter, Excel анализирует результат и автоматически корректирует размер выходного диапазона , чтобы вместить все значения, возвращаемые формулой. Затем он помещает каждый элемент массива в соответствующую ячейку в пределах этого диапазона, превышающего допустимый.

Если вы введете одну из этих формул в список или таблицу данных, это может оказаться весьма полезным для преобразования исходных данных в таблицу Excel со структурированными ссылками . Таблицы автоматически адаптируются при добавлении или удалении строк, поэтому формулы динамических массивов, использующие их, обновляются автоматически без вашего участия.

Важно знать, что формулы переполнения не работают внутри самих таблиц: Excel не поддерживает переполнение внутри таблицы . Вместо этого следует размещать такие формулы в обычной сетке (вне таблицы) и использовать таблицу только в качестве источника данных. Таблицы предназначены для хранения записей, а не для расширения на весь диапазон вывода.

При щелчке по любой ячейке в пределах диапазона переполнения Excel обводит выделенной рамкой все затронутые ячейки. Это позволяет с первого взгляда увидеть область действия формулы и ячейки, которые от нее зависят ; как только вы выберете ячейку за пределами этого диапазона, рамка исчезнет.

Ещё одна важная деталь: в диапазоне переполнения формула фактически содержится только в первой ячейке (в верхнем левом углу). В остальных ячейках отображаются результаты, но если вы выберете любую из них, формула в строке формул будет неактивна, и вы не сможете изменить её напрямую . Редактировать нужно только исходную ячейку; после внесения изменений и нажатия клавиши Enter Excel пересчитает весь диапазон переполнения сразу.

Ошибки перекрытия и сообщение #OVERFLOW!

Для плавного расширения формулы динамического массива Excel необходимо, чтобы область вывода была свободной. Если данные, формулы или другие элементы занимают какие-либо ячейки, где должны отображаться результаты, область переполнения перекроется , и Excel не сможет завершить операцию.

В этом случае вместо ожидаемого результата вы увидите сообщение об ошибке #OVERFLOW! в ячейке, куда вы ввели формулу. Таким образом Excel предупреждает вас о блокировке, препятствующей размещению массива во всех необходимых ячейках для хранения результатов.

Если вы выделите нужную формулу, Excel покажет пунктирной рамкой диапазон ячеек, в которых произойдет переполнение, включая ячейки, блокирующие этот процесс. Такой режим просмотра позволяет легко найти проблемные ячейки , чтобы очистить их или переместить содержимое в другое место.

Если удалить данные, препятствующие переполнению (например, лишние значения или предыдущие формулы), при пересчете листа Excel вызовет переполнение формулы, как и положено. Во многих случаях достаточно просто удалить пару ячеек, чтобы ошибка #OVERFLOW! мгновенно исчезла.

Также возможно, что сама формула плохо спроектирована и возвращает массив, слишком большой для доступного пространства. В таких случаях рекомендуется проверить как логику формулы, так и окружающее пространство , чтобы убедиться, что диапазон выходных данных может расширяться по мере необходимости.

Различия между динамическими матричными формулами и устаревшими формулами CSE.

До появления динамических массивов формулы массивов вводились с помощью привычной комбинации клавиш Ctrl+Shift+Enter (CSE) . Эти устаревшие формулы по-прежнему поддерживаются в Excel для обеспечения обратной совместимости, но сегодня рекомендуется работать с новой динамической моделью, которая проще и менее подвержена ошибкам.

Одно из главных преимуществ формул динамических массивов заключается в том, что формулу нужно ввести всего один раз в ячейку в верхнем левом углу . Остальные результаты появляются автоматически благодаря переполнению. В старых формулах CSE приходилось выделять весь диапазон, где должны были отображаться результаты, а затем подтверждать формулу с помощью Ctrl+Shift+Enter.

Динамические формулы также могут изменять свой размер при изменении исходных данных. Если вы добавляете больше строк в источник данных, массив может расширяться; если вы удаляете данные, он может сжиматься — и все это без необходимости вручную редактировать диапазон. В случае с устаревшими массивами CSE, если область возврата была слишком мала , результаты обрезались; а если она была слишком велика, могли возникать ошибки типа #N/A.

Ещё одно интересное отличие заключается в том, что многие классические функции, такие как RAND, ROW или COLUMN, теперь вычисляются в контексте одной ячейки (1x1). Если вы хотите сгенерировать несколько случайных результатов или последовательностей чисел на основе строк и столбцов, рекомендуется использовать такие функции, как RANDMARR или SEQUENCE , которые предназначены для возврата целых массивов и отлично работают с новым динамическим движком.

Кроме того, в прошлом существовало явление, известное как «нарушение CSE», при котором некоторые устаревшие формулы массивов, зависящие друг от друга, могли вычисляться независимо и возвращать противоречивые или трудноотлаживаемые результаты . С динамическими массивами это поведение исчезает: если есть циклические ссылки, Excel пометит их как таковые, вместо того чтобы нарушать логику формулы.

Изменение формулы динамического массива также более удобно. Вам нужно отредактировать только исходную ячейку, и остальное обновится автоматически; с формулами CSE приходилось редактировать весь затронутый диапазон сразу , что было довольно неудобно при работе с большими диапазонами. Кроме того, если рабочий лист содержал активный диапазон с формулой CSE, вы не могли вставлять или удалять строки или столбцы, которые мешали этому диапазону, пока не удалили или не изменили унаследованную формулу массива.

Использование ключевых функций с динамическими массивами

К числу наиболее мощных функций, использующих динамические массивы, относятся FILTER, SORT, SORTBY, UNIQUE, RANDMARRA и SEQUENCE . Все они возвращают полные диапазоны, что делает их идеальными для обработки переполнения, а такие полезные инструменты, как Excel Formula Bot, упрощают их использование.

Например, с помощью функции FILTER можно извлечь из таблицы данных только те строки, которые соответствуют определенным критериям, возвращая набор результатов, который автоматически обновляется при изменении исходных данных. Эта функция отлично работает с функцией UNIQUE, которая позволяет получать списки значений без дубликатов из столбцов с большим количеством повторяющихся точек данных.

Функция RANDOM MATRIX генерирует диапазон случайных чисел пакетом, что идеально подходит для моделирования или тестирования; функция SEQUENCE, с другой стороны, возвращает матрицы с последовательностями чисел, позволяя настраивать шаг и размер матрицы в соответствии с вашими потребностями. Обе функции основаны на новой матричной модели и предназначены для одновременного заполнения больших областей рабочего листа.

Наконец, функции SORT и SORTBY значительно упрощают создание отсортированных списков из существующих столбцов или таблиц. Вместо ручной сортировки или сложных комбинаций функций теперь можно написать одну формулу, которая возвращает отсортированные данные и автоматически адаптируется к изменениям исходных значений.

Все эти функции в сочетании друг с другом, благодаря механизму переполнения, позволяют создавать очень сложные решения без необходимости использования макросов или устаревших формул массивов, которые сложно поддерживать, а для автоматизации рабочих книг отличным подспорьем могут стать Office Scripts в Excel Web .

Различия в вычислениях между динамическими и наследуемыми матрицами

Если вы все еще работаете со старыми рабочими книгами, использующими формулы массивов CSE, важно отметить, что преобразование их в динамические эквиваленты может в некоторых случаях немного изменить поведение . Хотя преобразование в большинстве случаев не представляет сложности, рекомендуется перепроверить результат.

Обычно преобразование из устаревшего массива в динамический массив осуществляется следующим образом: находите первую ячейку диапазона массива, копируете текст формулы, удаляете весь старый диапазон, а затем переписываете формулу только в верхней левой ячейке, используя новый подход. Excel автоматически обрабатывает переполнение результата.

В процессе перехода следует обратить особое внимание на функции, которые ранее использовали неявное пересечение или специальные вычисления в формулах CSE. Теперь Excel предоставляет оператор неявного пересечения (@) , который в некоторых случаях можно использовать для воспроизведения старого поведения, но общая рекомендация по-прежнему заключается в явном переписывании формул с использованием новых функций.

Excel также предоставляет ограниченную поддержку, когда сводная таблица ссылается на данные в другой рабочей книге. Корректная работа гарантируется только при одновременном открытии обоих файлов . Если вы закроете исходную рабочую книгу, формулы связанных сводных таблиц могут выдавать ошибку #REF! при попытке обновления, поэтому это важно учитывать в сложных моделях с множеством внешних ссылок; в таких случаях см. информацию о том, как сохранять и совместно использовать рабочие книги , чтобы избежать проблем.

Хотя формулы CSE будут по-прежнему существовать для обеспечения обратной совместимости, сама Microsoft рекомендует отказаться от их создания в новых проектах и ​​вместо этого выбрать динамические массивы. Читаемость, простота обслуживания и надежность новых формул делают их предпочтительным выбором в будущем.

Диапазон переполнения и удобное редактирование на листе.

Когда формула выходит за пределы допустимого диапазона, область, которую она занимает, называется диапазоном переполнения. В этом диапазоне, как мы уже упоминали, только первая ячейка содержит саму формулу. В остальных ячейках отображаются результаты, но они «управляются» из исходной ячейки, в результате чего весь массив ведет себя как единое целое.

Например, если вы введете формулу =UPPER(E7:E19) в одну ячейку, вы увидите, как Excel вернет текст из этого диапазона в верхнем регистре в нескольких строках. Если вы выберете любую из ячеек в диапазоне вывода, вы увидите результат, но если вы посмотрите на строку формул, вы заметите, что ее содержимое отображается затемненным, что указывает на невозможность прямого редактирования . Любые изменения должны быть внесены в ячейку, куда вы впервые ввели формулу.

Такое поведение имеет явное преимущество: оно предотвращает случайное изменение только части диапазона и оставление остальной части без изменений, что часто приводит к труднообнаружимым ошибкам. Если вам нужно изменить формулу, вы редактируете ее только один раз в исходной ячейке, нажимаете Enter, и Excel пересчитает весь блок корректно.

В повседневной работе это также влияет на удаление или перемещение данных. Если вы попытаетесь удалить отдельную ячейку в пределах диапазона переполнения, Excel предупредит вас о том, что вы затрагиваете динамический массив. Чтобы избежать проблем, обычно либо удаляют или обрезают исходную ячейку напрямую, что перемещает остальную часть диапазона , либо корректируют формулу, чтобы она возвращала массив другого размера.

При объединении диапазонов переполнения с другими формулами следует помнить, что эти ячейки пересчитываются вместе. Любые ссылки на динамический массив следует делать осторожно, используя преимущества нового механизма, не создавая ненужных циклических ссылок или конфликтов с другими частями листа.

Примеры сложных матричных формул с использованием реальных данных

Многие классические формулы для массивов по-прежнему актуальны, особенно при работе с большими диапазонами и сложных условных операциях . Рассмотрим несколько типичных примеров, которые могут оказаться очень полезными в отчетах и ​​моделях.

Представьте, что у вас есть диапазон данных с именем Data, который содержит некоторые значения-ошибки, например, #N/A. Если вы примените функцию SUM непосредственно к этому диапазону, вы получите ошибку. Чтобы избежать этого, вы можете использовать массив, который игнорирует ошибки, с формулой типа =SUM(IF(ISERROR(Data);"";Data)) , которая внутренне создает массив, в котором ошибки заменяются пустыми строками перед суммированием.

Следуя той же идее, вы также можете подсчитать количество ошибок в диапазоне. Формула типа =SUM(IF(ISERROR(Data),1,0)) генерирует массив, в котором 1 соответствует ошибке, а 0 — ее отсутствию. Общая сумма показывает количество некорректных ячеек. Вы даже можете упростить ее до =SUM(IF(ISERROR(Data),1)) и пойти еще дальше до =SUM(IF(ISERROR(Data)*1)) , используя тот факт, что TRUE*1 равно 1, а FALSE*1 равно 0.

Другой распространенный сценарий — суммирование значений на основе условий. Например, у вас может быть диапазон под названием «Продажи», и вы хотите суммировать только положительные значения, используя формулу типа «=SUM(IF(Sales>0,Sales)») . В этом случае функция IF генерирует массив со значениями, удовлетворяющими условию, и ложными значениями для остальных, которые функция SUM на практике игнорирует.

Если вам нужно применить несколько условий одновременно, вы можете умножить логические выражения, чтобы имитировать операцию «И», а затем суммировать результаты. Типичный пример: =SUM((Sales>0)*(Sales<=5)*(Sales)) , которая вычисляет сумму продаж, больших 0 и меньших или равных 5. Однако этот подход требует, чтобы диапазон не содержал текстовых ячеек, иначе могут возникнуть ошибки.

Для имитации операции «ИЛИ» можно использовать суммы логических выражений: например, =SUM(IF((Sales<5)+(Sales>15);Sales)) , где значения меньше 5 или больше 15 суммируются. Таким образом, можно выполнять сложные операции, не используя напрямую функции И и ИЛИ, которые возвращают одно логическое значение, а не полный массив результатов.

Статистические расчеты и сравнения с использованием матричных формул.

Формулы массивов также очень полезны для вычисления средних значений или сравнения целых диапазонов . Классический пример — вычисление среднего значения набора данных, исключая нули. Если ваш диапазон называется Sales, формула =AVERAGE(IF(Sales<>0,Sales)) создает массив только с ненулевыми значениями и вычисляет среднее значение, исключая записи, которые не содержат полезной информации.

Ещё один интересный случай — сравнение двух диапазонов одинакового размера, например, MyData и YourData. Если вы хотите узнать, на сколько ячеек они отличаются, вы можете использовать формулу =SUM(IF(MyData=YourData,0,1)) , которая генерирует массив нулей, когда значения совпадают, и единиц, когда они отличаются, а затем суммирует результаты. Если два диапазона идентичны, формула вернет 0.

Это сравнение можно дополнительно упростить с помощью формулы =SUM(1*(MyData<>YourData)) , в результате чего логическое сравнение (MyData<>YourData) сгенерирует TRUE или FALSE, которые затем преобразуются в 1 и 0 путем умножения на 1. Опять же, ключевой момент — использовать преимущества поведения логических выражений внутри массивов.

Для определения максимального значения в диапазоне и получения его позиции можно также использовать формулы массивов. Если у вас есть диапазон с именем Data, один из вариантов — формула =MIN(IF(Data=MAX(Data),ROW(Data),"")) . Эта формула создает массив, в котором только ячейки, значение которых равно максимальному значению, содержат номер строки; остальные преобразуются в пустую строку. Затем MIN возвращает наименьший номер строки среди этих кандидатов — то есть первое вхождение максимального значения.

Если вместо строки вам нужна полная ссылка на ячейку, вы можете объединить ADDRESS с описанной выше логикой, используя что-то вроде =ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data)) , чтобы получить ссылку типа "$B$7", которая точно определяет, где находится максимальное значение в диапазоне.

Практический пример: продажи по продуктам с использованием матричных формул.

Чтобы лучше понять потенциал матричных формул, представьте небольшую таблицу продаж автомобилей со столбцами для продавца, типа автомобиля, количества проданных единиц, цены за единицу и общего объема продаж . Предположим, что столбцы C и D содержат, соответственно, количество проданных единиц и цену за единицу.

Если вы скопируете эту таблицу в Excel, вы сможете выделить диапазон E2:E11 и ввести формулу, например, =C2:C11*D2:D11 . В более старых версиях вам пришлось бы подтвердить формулу сочетанием клавиш Ctrl+Shift+Enter, чтобы преобразовать ее в классическую формулу массива, которая возвращала бы построчное умножение сразу для всех элементов. Затем Excel вычислял бы общую сумму продаж для каждой строки, умножая количество единиц на цену за единицу.

Ключевой момент заключается в том, что перед написанием формулы массива необходимо всегда выделять все ячейки, которые будут содержать результаты . В противном случае будет вычислена только часть значений, или же придётся повторять процесс несколько раз, что неэффективно.

В этом контексте также часто используется формула массива из одной ячейки для получения общей суммы всех продаж. Например, вы можете перейти в ячейку B13 и ввести формулу =SUM(C2:C11*D2:D11) . После подтверждения формулы (классическим способом с помощью Ctrl+Shift+Enter) Excel умножает каждую пару значений в ячейках C и D и суммирует все продукты, чтобы получить единое итоговое значение.

Хотя эти примеры основаны на унаследованном поведении массивов CSE, основные идеи те же, что и при использовании современных динамических массивов: выполнение операций над целыми диапазонами одновременно и возврат множественных или агрегированных результатов без необходимости использования промежуточных вспомогательных формул в каждой строке.

Сегодня многие из этих задач можно решить, комбинируя динамические функции и переполнение, что делает электронные таблицы более понятными, удобными для чтения и простыми в обслуживании , особенно по мере роста объема данных или когда несколько пользователей работают с одной и той же рабочей книгой.

Освоение формул динамических массивов в Excel полностью меняет подход к созданию электронных таблиц: понимая, как работает переполнение, как ведут себя диапазоны вывода, чем они отличаются от старых формул CSE и как применять практические примеры для игнорирования ошибок, выполнения условных суммирований или сравнения диапазонов, вы в конечном итоге будете работать с гораздо более гибкими, автоматизированными и надежными моделями, экономя время при каждом обновлении и сводя к минимуму ошибки, возникающие при ручном вводе данных.

Исаак

Страстный писатель о мире байтов и технологий в целом. Мне нравится делиться своими знаниями в письменной форме, и именно этим я и займусь в этом блоге: покажу вам все самое интересное о гаджетах, программном обеспечении, оборудовании, технологических тенденциях и многом другом. Моя цель — помочь вам ориентироваться в цифровом мире простым и интересным способом.