Эксель массивы: что такое, формула, как сделать / Skillbox Media

Формулы массива в Excel

90126 28.10.2012 Скачать пример

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т. е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(   
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:h21 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т. е. в диапазон A10:h21 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

• Формула массива для извлечения непустых ячеек из диапазона
• Формула массива для извлечения уникальных ячеек из диапазона
• Формула массива для извлечения данных из списка (многоразовый ВПР)

 

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

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

Добрый день, уважаемые подписчики и читатели блога! Сегодня чуть больше поговорим о массивах и практике их применения в Excel. 

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

Имеется таблица.

 

 

Как обычно мы действуем? Вводим формулу — количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

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

 

 

Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге. 

 

 

К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть — если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

Комбинации формул массива и обычных формул

Усложняем задачу — нужно подвести итог (СУММ) по всему столбцу «ИТОГО», высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

 

 

Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

 

 

Взглянем в строчку формул.

 

 

Посмотрим, что в этому случае делает программа Excel — пройдём по шагам вычислений. На вкладке «Формулы» нажмём кнопку «Вычислить формулу».

 

 

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

Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

 

 

Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

 

 

Готово. В этом случае даже получилось «экономнее» нежели в обычных формулах, так как для промежуточного итога нет своего столбца!

Как видно их примеров — у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс — улучшаем безопасность, минус — приходится переделывать весь массив, если что-то пошло не так.

Если остались вопросы — посмотрите новое видео. Ну и по традиции — вам может понравиться статья про функцию ПЛТ. Всем удачи! 

 

Формулы динамического массива и поведение разлитого массива

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Дополнительно… Меньше

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

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

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

Что означает разлив?

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

Разброс означает, что формула привела к нескольким значениям, и эти значения были помещены в соседние ячейки. Например, =SORT(D2:D11,1,-1), , которая сортирует массив в порядке убывания, вернет соответствующий массив из 10 строк. Но вам нужно всего лишь ввести формулу в верхнюю левую ячейку или в данном случае F2, и она автоматически переместится в ячейку F11.

Ключевые точки

• org/ListItem»>

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

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

• Формулы с переполненным массивом не поддерживаются в самих таблицах Excel, поэтому их следует размещать в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
• org/ListItem»>

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

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

  Введите .

• org/ListItem»>

  Перекрытие формул — формулы массива не могут быть введены, если что-то блокирует выходной диапазон. и если это произойдет, Excel вернет #SPILL! Ошибка , указывающая на наличие блокировки. Если вы удалите блокировку, формула выльется, как и ожидалось. В приведенном ниже примере выходной диапазон формулы перекрывает другой диапазон с данными и показан с пунктирной рамкой, перекрывающей ячейки со значениями, указывающими, что он не может перетекать. Удалите блокирующие данные или скопируйте их куда-нибудь еще, и формула выльется, как и ожидалось.

• Устаревшие формулы массива, введенные с помощью CTRL+SHIFT+ENTER (CSE), по-прежнему поддерживаются по соображениям обратной совместимости, но их больше нельзя использовать. При желании вы можете преобразовать устаревшие формулы массива в формулы динамического массива, найдя первую ячейку в диапазоне массива, скопировав текст формулы, удалив весь диапазон устаревшего массива, а затем повторно введя формулу в верхней части левая ячейка. Перед обновлением устаревших формул массива до формул динамического массива вы должны знать о некоторых различиях в вычислениях между ними.

• Excel имеет ограниченную поддержку динамических массивов между книгами, и этот сценарий поддерживается, только если открыты обе книги . Если вы закроете исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! ошибка при обновлении.

Нужна дополнительная помощь?

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

См. также

ФИЛЬТР

Функция СЛУЧАЙ

ПОСЛЕДОВАТЕЛЬНОСТЬ

Функция СОРТИРОВКИ

СОРТИРОВАТЬ функцию

УНИКАЛЬНАЯ функция

#ПРОЛИВАТЬ! ошибки в экселе

Неявный оператор пересечения: @

Формулы динамического массива по сравнению с устаревшими формулами массива CSE

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel для iPad Excel для iPhone Дополнительно. ..Меньше

В сентябре 2018 г. мы выпустили формулы динамического массива для Excel для Microsoft 365. Различия между динамическими массивами и устаревшими формулами Ctrl+Shift+Enter (CSE) обсуждаются ниже.

Примечание. В настоящее время эта функция доступна только для части участников программы предварительной оценки Office. Мы продолжим оптимизировать эти функции в течение следующих нескольких месяцев. Когда они будут готовы, мы выпустим их для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

Формулы динамического массива:

• Может «вытекать» за пределы ячейки, в которую вводится формула. В следующем примере показана функция RANDARRAY в D1, которая распределяется по D1: F5 или 5 строк по 3 столбца. Технически формула динамического массива существует только в ячейке D1, тогда как в устаревшей формуле СПП формулу необходимо вводить во всем диапазоне.

• Автоматически изменяет размер при добавлении или удалении данных из исходного диапазона. Формулы массива СПП будут усекать возвращаемую область, если она слишком мала, или возвращать #N/A, если она слишком велика.

• Расчет в контексте 1×1. Например, функция СЛУЧАЙ вернет один результат, поэтому, если вы хотите, чтобы в сетку возвращалось несколько случайных чисел, вы можете использовать функцию СЛУЧАЙ. Функции ROW и COLUMN возвращают строку и столбец исходной ячейки. SEQUENCE(ROW()) можно использовать для воспроизведения старого поведения ROW в устаревшей формуле массива.

• Любые новые формулы, возвращающие более 1 результата, будут автоматически перенесены. Нет необходимости нажимать Ctrl+Shift+Enter.

• Формулы массива

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

• Если ячейки в устаревшей формуле массива СПП зависели друг от друга, Excel попытается вычислить каждую по отдельности, а не как массив. Такое поведение было названо «распадом CSE», и при определенных обстоятельствах формулы, рассчитанные таким образом, могли давать противоречивые результаты. Чтобы избежать возможных противоречивых результатов, мы рекомендуем использовать отдельные формулы вместо того, чтобы полагаться на разбиение CSE. Формулы динамического массива не будут распадаться. Вместо этого они сообщат о циклической ссылке, чтобы вы могли обратиться к ней.