Пример формулы массива Содержание скрыть 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.
Среди логических функций функция И и функция ИЛИ обладают свойством агрегирования.
Ну а поисковые функции можно выделить отдельно. Их нельзя назвать полностью агрегирующими, но их взаимодействие с массивами довольно похоже. Ведь они тоже:
- учитывать весь массив
- выводить значение
ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСК — это наиболее часто используемые функции поиска.
