Эксел массив формула представляет собой формулу, которая вычисляет значения в одном или нескольких массивах, а не одно значение данных. В программах электронных таблиц массив представляет собой диапазон или ряд связанных значений данных, которые обычно хранятся в смежных ячейках на листе. В этом руководстве объясняется, как использовать формулы массива во всех современных версиях Excel.
Этот учебник совместим с большинством версий Excel, включая 2007, 2010, 2013, 2016 и Office 365.
Что такое формулы массива?
Формулы массива аналогичны регулярным формулам:
- Они начинаются с знака равенства ( = )
- Используйте тот же синтаксис, что и обычные формулы
- Используйте те же математические операторы
- Следуйте тому же порядку работы
В Excel формулы массива окружены фигурными фигурными скобками { } - эти фигурные скобки нельзя просто ввести; их необходимо добавить в формулу, нажав Ctrl , сдвиг , а также Войти ключи после ввода формулы в ячейку или ячейки. По этой причине формулу массива иногда называют CSE формула в Excel.
Исключение из этого правила - это когда фигурные скобки используются для ввода массива в качестве аргумента для функции, которая обычно содержит только одно значение или ссылку на ячейку. Например, в следующем учебном пособии используется ВПР и ВЫБИРАТЬ для создания формулы поиска слева, создается массив для ВЫБИРАТЬ функция-х номер_индекса аргумент, введя скобки вокруг введенного массива.
Шаги по созданию формулы массива
- Введите формулу.
- Удерживайте Ctrl а также сдвиг клавиши на клавиатуре.
- Нажмите и отпустите Войти для создания формулы массива.
- ВыпуститьCtrl а также сдвиг ключи.
Если все сделано правильно, формула будет окружена фигурными фигурными скобками, и каждая ячейка, содержащая формулу, будет иметь другой результат.
Редактирование формулы массива
Каждый раз, когда редактируется формула массива, фигурные скобки исчезают из формулы массива. Чтобы вернуть их, формулу массива необходимо ввести, нажав Ctrl, сдвиг, а также Войти снова, так же, как когда сначала была создана формула массива.
Типы формул массива
Существует два основных типа формул массива:
- Формулы массива одиночных ячеек, которые выполняют несколько вычислений в одной ячейке рабочего листа.
- Многоячеечные формулы массива, которые расположены в нескольких ячейках листа.
Формулы с одиночными ячейками
Этот тип формулы массива использует функцию, такую как SUM, СРЕДНИЙ, или же COUNT, чтобы объединить вывод формулы многоэлементного массива в одно значение в одной ячейке. Примером одной формулы массива ячеек может быть:
{= SUM (A1: A2 * B1: B2)}
Эта формула объединяет произведение A1 * B1 а также A2 * B2, а затем он возвращает единственный результат в одной ячейке на листе. Другим способом написания вышеприведенной формулы было бы следующее:
Многоклеточные матрицы
Как следует из их названия, эти формулы массива расположены в нескольких ячейках листа, и они возвращают массив в качестве ответа. Другими словами, одна и та же формула находится в двух или более ячейках и возвращает разные ответы в каждой ячейке.
Каждая копия или пример, формулы массива, выполняет те же вычисления в каждой ячейке, в которой он обитает, но каждый экземпляр формулы использует разные данные в своих расчетах. Поэтому каждый экземпляр дает разные результаты. Пример формулы массива с несколькими ячейками будет выглядеть так:
{= A1: A2 * B1: B2}
Если приведенный выше пример находится в ячейки С1 а также С2 на листе, то результаты будут следующими:
- Данные в A1 умножается на данные в B1, и результаты сохраняются в клетка С1.
- Данные в A2 умножается на данные в Би 2, и результаты сохраняются в клетка С2.
Использование массивов для переноса строк и столбцов
ТРАНСПОНИРОВАНИЕ функция используется для копирования данных из строки в столбец или наоборот. Эта функция является одной из немногих в Excel, которая всегда должна использоваться как формула массива.
Формулы массива и функции Excel
Многие встроенные функции Excel, такие как SUM, СРЕДНИЙ, а также COUNT, также может использоваться в формуле массива. Существует также несколько функций, таких как ТРАНСПОНИРОВАНИЕ функцию, которая всегда должна вводиться как массив, чтобы она работала правильно. Полезность многих функций, таких как ИНДЕКС а также МАТЧ или же МАКСИМУМ а также ЕСЛИ могут быть расширены, используя их вместе в формуле массива.
Примеры формул массива в Excel:
01 из 08Создание простой формулы ячейки с одиночной ячейкой
Формулы массива одиночных ячеек обычно сначала выполняют многокамерный расчет, а затем используют такую функцию, как СРЕДНИЙ или SUM чтобы объединить вывод массива в один результат.
02 из 08Игнорировать значения ошибок при поиске данных
Эта формула массива использует СРЕДНИЙ, ЕСЛИ, а также ISNUMBER функции для поиска среднего значения для существующих данных при игнорировании значений ошибок, таких как # DIV / 0! а также #НАЗВАНИЕ?
03 из 08Считать ячейки данных
Использовать SUM а такжеЕСЛИ функции в формуле массива для подсчета ячеек данных, удовлетворяющих одному из нескольких условий; этот метод отличается от использования Excel COUNTIFS функция, которая требует, чтобы все заданные условия выполнялись до подсчета ячейки.
04 из 08Найти наибольший положительный или отрицательный номер
Этот пример объединяет МАКСИМУМ функции иЕСЛИ функция в формуле массива, которая будет находить наибольшее или максимальное значение для диапазона данных при выполнении определенных критериев.
05 из 08Найти наименьший положительный или отрицательный номер
Подобно приведенному выше примеру, этот MIN функции и IF функцию в формуле массива, чтобы найти наименьшее или минимальное значение для диапазона данных при выполнении определенных критериев.
06 из 08Найти среднюю или среднюю ценность
MEDIAN функция в Excel находит среднее значение для списка данных. Объединив его с ЕСЛИ функции в формуле массива, среднее значение для разных групп связанных данных может быть найдено.
07 из 08Сделайте формулу поиска с несколькими критериями
Эта формула массива включает вложение МАТЧ а также ИНДЕКС функции для поиска конкретной информации в базе данных.
08 из 08Сделать формулу поиска слева
ВПР функция обычно выполняет поиск только данных, расположенных в столбцах справа, но, объединяя их с ВЫБИРАТЬ функции, вы можете создать формулу поиска слева, которая будет искать столбцы данных слева от lookup_Value аргумент.
