Формулы массива в Excel — от А до Я

Автор: | 16.04.2022

Пример формулы массива в Excel Пример формулы массива Содержание скрыть 1 Что такое формула массива 2 Матрицы в Excel 2.1 Разница между матрицами и диапазонами в Excel 2.2 Размеры матриц 2.3 Типы матриц 2.4 Способы создания матриц 2.4.1 Матрица констант, созданная вручную 2.4. 2 Массив именованных констант 2.4.3 Использование функций 2.4.4 Использование математических операторов 2.4.5 Использование операторов сравнения 2.4.6 Использование других матриц 2.4.7 Использование комбинаций этих методов 2.5 Просмотр матрицы на листе 3 Взаимодействие функций Excel с массивами 4 Формулы массива — примеры формул

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

Я не знаю, новичок ли вы в Excel или уже являетесь опытным пользователем, но если судьба привела вас на эту страницу, в процессе чтения:

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

Идем!

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

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

Будьте уверены на 100%: если формула

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

это формула массива.

Слишком очевидно и все еще непонятно? Неважно — продолжайте читать.

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

Массивы в Excel

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

Отличие массивов от диапазонов в Excel

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

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

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

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

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

Часть массива изменить нельзя. Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

И массивы, и диапазоны в Excel могут быть одномерными (вектор), двумерными (таблица) и трехмерными (многоуровневая таблица).

Одномерные массивы (векторы), в свою очередь, могут быть вертикальными и горизонтальными.

два одномерных и один двумерный массивы Одномерные и двумерные массивы в Excel

Трехмерные массивы на практике используются редко.

Виды массивов

Из типов элементов они содержат:
По типу данных —

  • числовой массив (массив чисел)
  • массив строк (текстовые значения)
  • логический массив (значения ИСТИНА-ЛОЖЬ)
  • смешанный тип

Из-за изменчивости элементов —

  • ряд констант,
  • вычисленная матрица

Методы создания массивов

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

Массив констант, созданный вручную

Массивы одномерных и двумерных констант можно создавать вручную и сразу отображать на листе без дополнительных операций с ними.

В этом случае разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

Двумерные массивы вставляются построчно, например, первая строка разделяется точкой с запятой, затем двоеточием, вторая строка разделяется точкой с запятой и так далее.

Именованный массив констант

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

Ряд чисел, как в примере ниже, поможет вам быстро найти числа в текстовых ячейках.

Создать именованный массив в Excel

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

Использование именованного массива

С помощью функций

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

Единственное условие для создания массива с использованием функции — это то, что функция не должна иметь агрегатного свойства. Например, SUM, AVERAGE не создаст массив при ссылке на диапазон.

На этом сайте есть статья о том, как добавить символ, слово или текст в несколько ячеек. Он охватывает несколько способов, в том числе без создания дополнительного столбца. Но вот еще один: использование функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями ЦЕПЬ и СИМВОЛ

Цитаты в виде елки в этой формуле помогают нам создать функцию СИМВОЛ.

С помощью математических операторов

просто примените любую из математических операций к диапазону, и будет создан массив того же размера.

Матрицы Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Как и математические операторы, массивы создаются при сравнении диапазонов с константой или значением ячейки. Операция сравнения возвращает ИСТИНА или ЛОЖЬ.

Массив в Excel с использованием операторов сравнения

С помощью других массивов

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

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

Размер результирующего массива в этом случае будет равен размеру постоянного массива.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

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

Вывод массива на лист

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

Ctrl + Shift + Enter

Если вы выберете недостаточное количество ячеек, будут отображаться не все элементы массива, а только те, которые совпадают по порядку.
Если вы переписываете, дополнительные ячейки выдадут ошибку # N / A.

Например, результирующий массив должен иметь размер 5 * 5, но перед вводом формулы массива был выбран диапазон 6 * 6.

Ошибка ND, когда размер диапазона и массива не совпадает

Взаимодействие функций Excel с массивами

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

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

В разделе математических функций к ним также относятся PRODUCT, SUM.PRODUCT, SUMIF, SUMIF.

Большинство статистических функций по своей природе принимают диапазон или массив в качестве входных данных и возвращают одно число. Самые популярные: AVERAGE, MIN, MAX, COUNT, COUNT, COUNTIF.

Среди логических функций функция И и функция ИЛИ обладают свойством агрегирования.

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

  • учитывать весь массив
  • выводить значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСК — это наиболее часто используемые функции поиска.