Формула массива в excel как вводить

Обновлено: 20.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций рабочего листа. Их также называют формулами «Ctrl-Shift-Enter» или «CSE», поскольку для их ввода необходимо нажать Ctrl+Shift+Enter. Вы можете использовать формулы массива, чтобы сделать, казалось бы, невозможное, например

Подсчитать количество символов в диапазоне ячеек.

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

Суммировать каждое n-е значение в диапазоне значений.

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

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

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

Нажмите ячейку, в которую вы хотите ввести формулу массива.

Введите формулу, которую хотите использовать.

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

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

Формула сначала умножает акции (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем складывает эти результаты, чтобы получить общее число 35 525. Это пример формулы массива с одной ячейкой, поскольку формула находится только в одной ячейке.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Когда вы нажимаете Ctrl+Shift+Enter, Excel автоматически вставляет формулу между (парой открывающих и закрывающих фигурных скобок).

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

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

Введите формулу, которую хотите использовать.

В следующем примере формула умножает акции на цену в каждом столбце, и формула сохраняется в выбранных ячейках в строке 5.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения.Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Если вам нужно включить новые данные в формулу массива, см. раздел Расширение формулы массива. Вы также можете попробовать:

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

Для правильной работы многие (но не все) формулы массива необходимо вводить с помощью клавиш Ctrl + Shift + Enter. При вводе с помощью control + shift + enter вы увидите формулу, заключенную в фигурные скобки <> в строке формул. Не вводите фигурные скобки вручную, иначе формула не будет работать.

Что такое массив?

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

Примеры

Формулы массива несколько сложны для понимания, поскольку терминология насыщена и сложна. Но сами формулы массива могут быть очень простыми. Например, эта формула массива проверяет диапазон A1:A5 на наличие значения "a":

Операция массива представляет собой сравнение каждой ячейки в A1:A5 со строкой "a". Поскольку сравнение работает с несколькими значениями, функция ИЛИ возвращает несколько результатов:

Если какой-либо элемент результирующего массива имеет значение ИСТИНА, функция ИЛИ возвращает значение ИСТИНА.

Иногда формулы массива предоставляют несколько значений в качестве аргумента функции. Например, эта формула массива возвращает общее количество символов в диапазоне B2:B11:

Функция ДЛСТР принимает несколько значений в диапазоне B2:B11 и возвращает несколько результатов в массиве, подобном этому, внутри SUM:

где каждый элемент массива представляет собой длину одного значения ячейки. Затем функция СУММ суммирует все элементы и возвращает 43 в качестве окончательного результата.

Специальный синтаксис

Во всех версиях Excel, кроме Excel 365, для правильной работы многие формулы массива необходимо вводить особым образом. Вместо ввода с помощью клавиши «Ввод» их нужно вводить с помощью Control + Shift + Enter. Иногда вы увидите сочетание клавиш Control + Shift + Enter, сокращенно «CSE», как в «формуле CSE». Формула, введенная таким образом, будет иметь фигурные скобки с обеих сторон:

Эти фигурные скобки автоматически отображаются в Excel. Убедитесь, что вы не вводите фигурные скобки вручную!

Не все формулы массива нужно вводить с помощью клавиш Control + Shift + Enter. Некоторые функции, такие как СУММПРОИЗВ, изначально запрограммированы на обработку операций с массивами и обычно не требуют сочетания клавиш Control + Shift + Enter. Например, обе приведенные ниже формулы представляют собой формулы массива, которые возвращают один и тот же результат, но только для версии SUM требуется сочетание клавиш Control + Shift + Enter:

Excel 365

В Excel 365 формулы массива являются собственными и не требуют управления + Shift + Enter. Общие сведения см. в разделе Формулы динамического массива в Excel.

Эта глава поможет вам понять формулы массива в Excel. Формулы массива с одной ячейкой выполняют несколько вычислений в одной ячейке.

Без формулы массива

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

<р>1. Во-первых, мы будем подсчитывать успеваемость каждого учащегося.

<р>2. Затем мы воспользуемся функцией MAX, чтобы найти наибольший прогресс.

С формулой массива

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

<р>1. Мы уже знаем, что можем определить прогресс первого ученика, используя приведенную ниже формулу.

<р>2.Чтобы найти наибольший прогресс (не перегружайтесь), мы добавляем функцию MAX, заменяем C2 на C2:C6 и B2 на B2:B6.

<р>3. Завершите, нажав CTRL + SHIFT + ENTER.

Примечание: строка формул указывает, что это формула массива, заключая ее в фигурные скобки <>. Не вводите их самостоятельно. Они исчезнут, когда вы отредактируете формулу.

Объяснение: диапазон (константа массива) хранится в памяти Excel, а не в диапазоне. Константа массива выглядит следующим образом:

Эта константа массива используется в качестве аргумента функции MAX, что дает результат 63.

<р>4. Если вы используете Excel 365 или Excel 2021, завершите работу, просто нажав клавишу ВВОД. Прощай, фигурные скобки.

Клавиша F9

При работе с формулами массива вы можете сами взглянуть на эти константы массива.

<р>1. Выберите в формуле C2:C6–B2:B6.

Выглядит хорошо. Элементы в константе вертикального массива разделяются точкой с запятой. Элементы горизонтальной константы массива разделяются запятыми.

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

Что есть на этой веб-странице

1. Почему формулы массива так эффективны?

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

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

Он умножает все числа в столбце B на все числа в столбце D и возвращает наименьшее произведение всех чисел.

Формула массива в ячейке D9:

Объяснение формулы в ячейке D9

Шаг 1. Транспонирование чисел

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

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

Обратите внимание, что символом-разделителем является точка с запятой в вертикальном массиве и запятая в горизонтальном массиве.

На изображении выше показаны числа во втором массиве, расположенные горизонтально.

Шаг 2. Умножение чисел

Результат показан в диапазоне ячеек H3:K7 на изображении выше. В ячейке H3 показано произведение чисел в ячейках H2 и G3 и т. д.

Шаг 3. Извлечение наименьшего числа из массива

Функция MIN возвращает наименьшее число из диапазона ячеек или массива.

и возвращает 0,35, что является наименьшим числом в массиве.

На изображении ниже показан результат в ячейке D9, все эти вычисления были выполнены только в одной ячейке.

2. Как определить формулы массива?

Панель формул находится над рабочим листом. Она может быть пустой или содержать значение или формулу. На изображении выше показана формула массива в ячейке D9.

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

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

3. Как ввести формулу массива?

Создать массив в Excel очень просто. Дважды нажмите левой кнопкой мыши на ячейку A1 и введите = в строке формул, удерживайте и одновременно нажмите CTRL + SHIFT, затем нажмите Enter один раз. Отпустите все ключи.

Формула в строке формул теперь показывает начальную и конечную фигурные скобки. Не вводите эти символы самостоятельно. Убедитесь, что строка формул выглядит так: >

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

= — это массив констант, разделенных символом-разделителем. Эта формула вводится в ячейку A1, но отображается только одно значение в массиве, почему? В следующем разделе ниже объясняется, почему это происходит.

4. Почему показано только одно значение? У меня есть значения x в массиве?

В каждой ячейке может быть только одно значение. Чтобы увидеть все значения, введите формулу в диапазон ячеек A1:C1

Выберите диапазон ячеек A1:C1.
Нажмите левую кнопку мыши в строке формул.
Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
Нажмите клавишу ВВОД.
Отпустить все клавиши

Анимированное изображение выше показывает, как расширить формулу массива из ячейки A1 до A1:C1, что позволяет увидеть все значения в массиве.

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

5. Что такое вертикальные и горизонтальные массивы?

Можно создавать массивы с разделителем в виде запятой и точкой с запятой.

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

Я пытаюсь ввести массив с текстовыми значениями, но это не работает. Почему? Заключите значения в двойные кавычки, например: =

6. Как использовать ссылки на ячейки в формулах массива

Все массивы, представленные выше, содержат константы, то есть они не изменяются, пока вы их не отредактируете. Эта формула =A1:A10 содержит ссылку на ячейку A1:A10. Значения в этой формуле изменяются, если вы изменяете значение в диапазоне ячеек A1:A10. Формула =A1:A10 не содержит констант.

Это означает, что вы можете использовать значения на листе в формуле массива. Это чрезвычайно полезно.

7. Как быстро построить массив

Я хочу построить массив от 1 до 1000? Я не хочу вводить все эти значения.
В этом нет необходимости, функция ROW может сделать это за вас. =СТРОКА(1:1000) создает массив от 1 до 1000.

8. Как жестко закодировать (литеральные) значения в формулах массива?

Можно ли жестко закодировать в формуле значения от 1 до 1000, не вводя их самостоятельно? Да, можете. Введите =СТРОКА(1:1000) в строке формул и нажмите F9.

Excel преобразует =СТРОКА(1:1000) в =

9. Всегда ли необходимы Ctrl + Shift + Enter?

Можно ли создать формулу массива, не вводя ее как формулу массива?
Да, часто можно. Однако это усложняет формулу.
Прочитайте это сообщение: Формулы массива больше не нужны?

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

10. Можно ли ввести формулу массива в объединенные ячейки?

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

11. Есть ли исключения?

Да, и функция СУММПРОИЗВ, и функция ПРОСМОТР позволяют создавать массивы, не требуя ввода их в виде формулы массива.

Функция СУММПРОИЗВ вычисляет произведение соответствующих значений, а затем возвращает сумму каждого умножения.

Находит значение в отсортированном диапазоне ячеек и возвращает значение в той же строке.

Функция ИНДЕКС позволяет инкапсулировать массив, поэтому вам не нужно вводить его как формулу массива:

В этой статье объясняется, как при необходимости избежать формул массива. Однако он не будет работать со всеми формулами массива, но с более мелкими […]

Получает значение в определенном диапазоне ячеек на основе номера строки и столбца.

12. Есть какие-то предостережения при использовании формул массива?

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

Однако это не так, если вы нажмете клавишу Escape, чтобы выйти из редактирования формулы массива, или если вы нажмете CTRL + SHIFT + Enter.

Формулы массива могут замедлить вычисления на листе.

13. Хорошо, покажи мне несколько полезных формул массива?

Во-первых, позвольте мне объяснить разницу между уникальными значениями и уникальными уникальными значениями, важно, чтобы вы знали разницу […]

В этом сообщении объясняется, как искать значение и возвращать несколько значений. Формула массива не требуется.

В этой статье объясняется, как повторять определенные значения на основе таблицы, таблица содержит повторяющиеся элементы […]

Крутые, еще более сложные расчеты?

В этой статье демонстрируется формула массива, которая подсчитывает, сколько раз несколько текстовых строк существуют в диапазоне ячеек. […]

В этой статье показано табло, отображаемое слева, которое сортирует участников по общему количеству баллов и мгновенно обновляется каждый […]

Excelxor — отличный сайт для вдохновения, меня очень впечатлил этот пост Какие цифры в сумме дают […]

В этом сообщении демонстрируется формула в ячейке D16, которая подсчитывает перекрывающиеся даты в нескольких диапазонах дат. Диапазоны дат […]

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

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

Excel 365 показывает все значения, которые формула массива возвращает автоматически, если формула массива возвращает более одного значения, такое поведение называется сбросом.

На изображении выше показана формула динамического массива в ячейке H3, которая возвращает несколько значений и переносит эти значения в ячейки ниже. Функция ФИЛЬТР — это новая функция, доступная только для пользователей Excel 365.

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

В этом посте вы узнаете, как работать с массивами Excel в Visual Basic для приложений (VBA). Почему вы […]

Вы когда-нибудь пытались ввести формулу массива в объединенные ячейки? Тогда вы знакомы с этим сообщением об ошибке: […]

3 ответа на «Руководство по формулам массива Excel для начинающих»

Во-первых, это лучший сайт, который я когда-либо находил, где можно найти советы и рекомендации по Excel, а также то, как вы объясняете свои решения!! Мой вопрос: я заметил, что формулы массива очень интенсивны в вычислениях, поэтому, когда у вас есть 500 или более строк, вычисление листа замедляется. Каков ваш опыт или рекомендация относительно ограничений размера эффективного использования формул массива? Я не нашел ничего опубликованного. (Я работаю с листами, содержащими десятки тысяч строк)

Я попробовал ваш пример, как указано выше, но он не работает в моей электронной таблице.

=ИНДЕКС($C$3:$C$7, МАЛЕНЬКИЙ(ЕСЛИ(($B$10=$B$3:$B$7), ПОИСКПОЗ(СТРОКА($B$3:$B$7), СТРОКА($B$3) :$B$7)), ""),СТРОКИ($A$1:A1)))

ПС. Я использую MS Offic 2010

Оставить ответ

Как комментировать

Как добавить формулу в комментарий
Вставьте сюда формулу.

Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >

Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]

Читайте также: