Формулы с массивами в Excel: полное руководство и практические примеры

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

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

Что такое массив и что такое формула массива в Excel?

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

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

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

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

Например, представьте, что в столбце B указано количество проданных единиц, а в столбце C — цена каждой единицы. С помощью формулы массива типа =СУММ(B2:B11*C2:C11)Excel умножает каждую строку (единицы на цену), а затем суммирует все эти продукты, без необходимости использования промежуточных столбцов.

Как ввести и распознать классическую матричную формулу

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

Когда вы пишете формулу массива и подтверждаете ее этой комбинацией, Excel отображает формулу в строке формул, окруженную ключи { }Эти ключи не вводятся вручную: Excel добавляет их автоматически, когда определяет, что это формула массива.

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

Каждый раз, когда вы редактируете формулу массива, фигурные скобки временно исчезают: вам придется снова нажать [соответствующую клавишу]. Ctrl + Shift + Enter После завершения редактирования формула перестанет быть матричной и будет рассчитываться только для первого элемента диапазона.

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

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

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

В первом случае формула принимает массив данных, выполняет вычисления и возвращает только один результат в одной ячейке (например, сумма, среднее значение, количество, минимум или максимум).

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

Такие функции, как SUMA, СРЕДНИЙ, MAX o MIN (и его варианты, MINIFS и MAXIFS) могут работать с массивами, если они введены как формулы массива в одной ячейке, в то время как другие, такие как ТРАНСПОНИРОВАНИЕ, ТЕНДЕНЦИЯ o ЧАСТОТАОни предназначены для возврата массивов из нескольких ячеек.

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

Расширенные примеры классических матричных формул

Матричные формулы позволяют решать задачи, которые стандартные формулы решить было бы затруднительно или просто невозможно. Ниже приведено несколько примеров. Ejemplos типичные и расширенные, основанные на диапазонах, с названиями вроде Data, Sales, MyData или YourData.

Диапазоны суммирования, содержащие ошибки

Когда диапазон включает такие ошибки, как # N / AОбычная сумма с использованием функции СУММ завершится ошибкой. Используя формулу массива, можно игнорировать эти ошибки и суммировать только допустимые значения:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);"",Данные))

Функция ЭМИТЕНТ Обнаруживает ячейки с ошибками в диапазоне данных и функция SI Создайте новый массив, в который вместо ошибок поместите пустые строки. "", и где ячейки без ошибок сохраняют свое первоначальное значение.

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

Подсчитайте количество ошибок в диапазоне

Если вам нужно подсчет ошибок Вместо того, чтобы складывать их вместе, вы можете использовать похожий вариант:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0))

Эта формула создает матрицу, в которой каждая ячейка с ошибкой преобразуется в 1 и каждая ячейка без ошибки в 0так что сумма всех этих единиц и нулей дает общее количество ошибок.

Формула может упрощать Удаляем третий аргумент функции IF, поскольку, если условие ложно, функция возвращает FALSE, а SUM интерпретирует FALSE как 0:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0))

И его все еще можно сократить еще больше, напрямую умножив логический результат на 1, воспользовавшись тем фактом, что ИСТИНА*1=1 y ЛОЖЬ*1=0:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1))

Добавление значений, соответствующих условиям (AND и OR «вручную»)

С помощью формул массива можно суммировать только значения, удовлетворяющие определённым условиям, без необходимости всегда использовать функцию СУММЕСЛИМН. Например, чтобы суммировать только значения положительный из ассортимента продаж:

=СУММ(ЕСЛИ(Продажи>0;Продажи))

Вот функция SI Она генерирует массив, в котором ячейки со значением больше 0 сохраняют свое значение, а остальные становятся ЛОЖНЫМИ; функция SUMA Игнорируйте ЛОЖНЫЕ числа и добавляйте только положительные числа.

Вы также можете объединить несколько условий, используя умножение (эквивалентно И логично) или суммы (эквивалентные Или логично). Например, чтобы сложить значения больше 0 и меньше или равные 5:

=СУММ((Продажи>0)*(Продажи<=5)*(Продажи))

В этом случае логические выражения возвращают массивы ИСТИНА/ЛОЖЬ, которые при умножении становятся 1 или 0 и действуют как фильтры по показателям продаж.

Если вам нужно поведение типа О, вы можете использовать сумма логических условий в системе СИ, как в этой формуле, которая складывает значения меньше 5 или больше 15:

=СУММ(ЕСЛИ((Продажи<5)+(Продажи>15);Продажи))

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

Вычислить среднее значение, исключая нули

Если вы хотите получить среднее без учета нулейВы можете объединить СРЗНАЧ с условием массива по диапазону продаж:

=СРЕДНЕЕ(ЕСЛИ(Продажи<>0;Продажи))

Полученная матрица SI содержит только ненулевые значения, которые в конечном итоге и используются. СРЕДНИЙ для расчета среднего значения.

Подсчет разностей между двумя диапазонами

Предположим, у вас есть два диапазона одинакового размера и формы, называемые MyData и YourData, и вы хотите узнать в скольких ячейках они различаются?Матричная формула решает это так:

=СУММ(ЕСЛИ(МоиДанные=ВашиДанные;0;1))

Функция ЕСЛИ генерирует массив, где каждое совпадение становится 0, а каждое несовпадение — 1. Суммирование массива дает вам считать из разных ячеек.

Здесь также есть более компактная версия, которая напрямую использует неравное сравнение (<>) умноженное на 1:

=СУММ(1*(МоиДанные<>ВашиДанные))

Еще раз, трюк что TRUE равно 1, а FALSE равно 0 при умножении на число.

Найдите максимум и его положение в диапазоне.

С помощью матричных формул вы можете не только узнать, что максимальное значение набора, но и его точное положение на листе; вы также можете использовать РАНГ функция для заказа и определения местоположения позиций.

Для того, чтобы найти номер строки Для максимального значения в диапазоне столбца с именем «Данные» можно использовать:

=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»))

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

Если то, что вы хотите получить, это ссылка на ячейку Для максимального результата вы можете обернуть предыдущий расчет с помощью ADDRESS и COLUMN:

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);"")),СТОЛБЕЦ(Данные))

Пошаговое создание формул многоячеечного массива

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

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

В диапазоне C2:C11 у вас есть проданные количества, а в диапазоне D2:D11 — цены; цель состоит в том, чтобы заполнить диапазон E2:E11 произведение каждой строки без написания формул одну за другой.

Чтобы сделать это как формулу массива для нескольких ячеек, сначала выберите весь диапазон. E2: E11, введите в строку формул:

=C2:C11*D2:D11

и подтвердите с помощью Ctrl + Shift + ВводExcel заполнит все ячейки от E2 до E11 одновременно результатом, соответствующим каждой строке.

Формула массива отдельных ячеек на том же примере

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

Вместо того, чтобы писать формулы построчно, просто введите в ячейку B13 следующее:

=СУММ(C2:C11*D2:D11)

и подтвердите с Ctrl + Shift + ВводExcel выполняет внутреннюю операцию умножения каждой пары ячеек Cx*Dx, а затем складывает все полученные результаты, получая общую сумму.

Как отладить и понять сложную матричную формулу

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

Хитрость заключается в том, чтобы выбрать определенную часть в строке формул (например, только В2:В11*С2:С11), а затем нажмите F9 так что Excel временно заменяет этот фрагмент промежуточным результатом.

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

После того, как вы осмотрели выбранную деталь, вы можете нажать Esc Чтобы выйти без сохранения изменений, или нажмите Ctrl + Z, если вы случайно подтвердили оценку и хотите отменить ее.

Константные массивы в Excel: как их создавать и использовать

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

Матричная константа может содержать числа, тексты, логические значения (ИСТИНА/ЛОЖЬ) или ошибкино он не может включать ссылки на ячейки, определенные имена, даты, функции или другие массивы.

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

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

Например, вертикальная матрица с месяцами года это можно выразить так:

={"Январь";"Февраль";"Март";"Апрель";"Май";"Июнь";"Июль";"Август";"Сентябрь";"Октябрь";"Ноябрь";"Декабрь"}

Присвоение имени константе массива

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

Процесс прост: вы переходите на вкладку «Формулы», используете опцию Определенное имя или Назначенное имяВы пишете желаемое имя и в поле «Ссылается на» непосредственно вводите константу матрицы.

Например, вы можете создать название «Месяцы», которое указывают на константу:

={"Январь"\"Февраль"\"Март"\"Апрель"\"Май"\"Июнь"\"Июль"\"Август"\"Сентябрь"\"Октябрь"\"Ноябрь"\"Декабрь"}

Затем просто выберите столько ячеек, сколько элементов в матрице, и введите имя =Месяцы и подтвердить как формулу массива, чтобы отобразились все значения, распределенные по листу.

Если константа вызывает проблемы, рекомендуется ее проверить. используемые сепараторы и что перед вводом формулы с помощью Ctrl + Shift + Enter был выбран диапазон, соответствующий размеру матрицы.

Примеры использования матричных констант

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

Аналогично можно просуммировать N наименьших значений с К.ЕСИМО.МИНОР, просто изменяя функцию, но сохраняя массив позиций, которые вы хотите суммировать.

Другой типичный случай — подсчет того, сколько раз оценщик (например, Педро) дал оценку несколько конкретных значений без необходимости повторять критерии в функции COUNTIFS снова и снова.

В одном диапазон оценок Вы можете использовать такую ​​формулу:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))

Здесь константа {3\4\5} Он собирает принятые баллы (3, 4 и 5) и делает формулу более компактной и простой в использовании, хотя при необходимости вы можете расширить ее, добавив больше значений, всегда соблюдая максимальный лимит символов в формуле.

Формулы динамического массива в Excel 365 и 2021

В современных версиях Excel (Microsoft 365 и Excel 2021) было внесено очень важное изменение: динамические матричные формулычто устраняет необходимость использования Ctrl + Shift + Enter в большинстве случаев.

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

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

Кроме того, появились новые функции, специально предназначенные для работы с динамическими массивами, такие как Скрининг, АККУРАТНЫЙ, ТОЛЬКО, ПОСЛЕДОВАТЕЛЬНОСТЬ, СОРТИРОВАТЬ ПО o СЛУЧАЙНАЯ МАТРИЦА, Среди других.

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

Ключевые различия между классическими и динамическими матричными формулами

Классические матричные формулы требуют Ctrl + Shift + ВводИх может быть несколько сложнее читать, и во многих случаях они ограничены в своей способности автоматически выдавать результаты, в отличие от современных функций, таких как ВПР и КСПР.

Динамические матричные формулы записываются как нормальные формулыОни подтверждаются простым нажатием Enter и выводят результаты самостоятельно, без выбора предыдущих диапазонов или использования унаследованных формул массива.

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

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

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

Настройка и использование динамических формул массива

Чтобы использовать динамическую формулу, просто выберите начальная ячейкаВведите формулу с функцией, например, ФИЛЬТР или СОРТИРОВКА, и нажмите Enter. Excel автоматически распределит результаты по полю вниз и вправо.

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

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

Вы также можете обратиться к диапазон переполнения из других формул, использующих оператор переполнения (например, =СУММ(F2#)), так что если он увеличится или уменьшится в размере, формулы, которые его используют, автоматически адаптируются.

В средах programaciónбиблиотеки, такие как Aspose.Cells Они позволяют вам задавать и пересчитывать динамические формулы массива с помощью кода, используя специальные методы для их назначения ячейке и обновления перед выполнением расчета общей формулы.

Расширенные приложения матриц: линейная алгебра и финансы

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

Квадратную матрицу A можно инвертировать в Excel с помощью функции МИНВЕРСА, для чего требуется матричная (или динамическая, в зависимости от версии) формула в диапазоне того же размера, что и исходная матрица.

Чтобы получить обратную матрицу 3×3, расположенную в B3:D5, нужно выбрать пустой блок 3×3 и написать =МОБР(B3:D5) и вы бы подтвердили это как матричную формулу, получив таким образом матрицу A^-1.

Если умножить исходную матрицу на ее обратную, используя MMULT в соответствующем диапазоне вы получите единичная матрица, аналогично умножению числа на его обратное, что всегда дает 1.

Аналогично можно составить систему линейных уравнений в матричной форме, где A — матрица коэффициентов, K — вектор неизвестных, а P — вектор независимых членов, и решить ее, используя соотношение К = А^-1 · П с использованием MINVERSA и MMULT.

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

Исходя из функции Лагранжа и вывода условий первого порядка, мы снова приходим к матричной системе типа A·X = P, решение которой X = A^-1·P дает нам оптимальные веса каждого актива в портфеле.

В примере с тремя акциями A, B и C с заданными ковариациями и ожидаемой доходностью можно определить инвестиционный вектор таким образом, чтобы портфель получал ожидаемую доходность 4% при минимальная дисперсияв результате получается комбинация, которая позволяет диверсифицировать инвестиции для снижения риска по сравнению с инвестированием в одну ценную бумагу.

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

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