Делать несколько расчетов при помощи формул массива Excel

Снимок экрана Excel, показывающий формулу перед тем, как стать массивом

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

Эти аннотации относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

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

Формулы массива похожи на обыденные. Oни:

Начните со знака равенства ( = )
Используйте этот же синтаксис, что и обыденные формулы
Используйте те же математические операторы
Следуйте тому же порядку работы

Но формулы массивов заключены в фигурные скобки <>. И вы не сможете просто ввести их; Вы должны добавить их, нажав Ctrl , Shift , , и введите ключи опосля ввода формулы в ячейку либо ячейки. По данной для нас причине формулу массива время от времени именуют формулой CSE в Excel.

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

Существует два главных типа формул массива:

Формулы массива из одной ячейки, которые делают несколько вычислений в одной ячейке листа которые находятся в нескольких ячейках листа

Как сделать формулу массива

Введите формулу в ячейку.

Удерживайте клавиши Ctrl и Shift на клавиатуре.

Нажмите и отпустите кнопку Enter, чтоб сделать формулу массива.

Отпустите клавиши Ctrl и Shift .

Если все изготовлено верно, фигурные скобки будут окружать формулу.

Формулы для отдельных ячеек

Формула массива одной ячейки употребляет функцию, такую как SUM , AVERAGE либо COUNT , для объединения вывода формулы массива из нескольких ячеек в одно значение в одной ячейке. Ниже приведен пример:

Интересно почитать: Excel доступ к файлу невозможен проверьте следующее

Приведенная выше формула складывает произведение A1 * B1 и A2 * B2 , а потом возвращает один итог в одной ячейке на листе. Иной метод представления данной для нас формулы:

Multi-Cell Array Formulas

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

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

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

Данные в A1 множатся на данные в B1 , а результаты возникают в ячейкеC1 .
Данные в A2 множатся на данные в B2 , а результаты возникают в ячейкеC2 .

Формулы массивов и функции Excel

Вы сможете употреблять почти все интегрированные функции Excel, такие как SUM , AVERAGE и COUNT , в формуле массива. Есть также несколько функций, таковых как TRANSPOSE , которые постоянно должны быть формулой массива, чтоб работать верно. (Функция TRANSPOSE копирует данные из строчки в столбец либо напротив.)

Вы также сможете расширить полезность почти всех функций, таковых как INDEX и MATCH либо MAX и IF , используя их вкупе в формуле массива.

Сделать ординарную формулу массива из одной ячейки

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

Интересно почитать: Верхний индекс в excel

Игнорировать значения ошибок при поиске данных

Эта формула массива употребляет функции AVERAGE , IF и ISNUMBER, чтоб отыскать среднее значение для имеющихся данных , игнорируя значения ошибок, такие как # DIV / 0! и #NAME ?

Подсчет ячеек данных

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

Отыскать наибольшее положительное либо отрицательное число

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

Найдите меньшее положительное либо отрицательное число

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

Отыскать среднее либо среднее значение

Функция MEDIAN в Excel находит среднее значение для перечня данных. Сочитая его с функцией IF в формуле массива, вы сможете отыскать среднее значение для различных групп связанных данных.

Сделайте формулу поиска с несколькими аспектами

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

Создать левую формулу поиска

ВПР функция обычно лишь поиск данных , расположенных в колонках справа, но, сочитая его с ВЫБРАТЬ функции , вы сможете сделать левую формулу подстановки , которая будет находить столбцы данных слева от Lookup_Value аргумента.

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