Формулы массива excel: Использование формул массива: рекомендации и примеры

Содержание

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

Хитрости »

17 Июнь 2012 Дмитрий 95596 просмотров

Основные понятия (26)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (22)

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (4)

Если Вы довольно много работаете в Excel то наверняка уже слышали выражение «формула массива«. Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
Большинство уже наверняка знакомо с функцией СУММ(SUMM).

Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5). Получим сумму этих чисел — 15. Все просто и понятно. И вроде бы — функция уже работает с массивами — ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1). Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и… Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами(т.е. обрабатывать поочередно каждую ячейку). Ему надо явно указать, что мы хотим это сделать и результат при этом вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш — Ctrl+Shift+Enter.
Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки — {=СУММ(A1:A5+1)}. Если Вы после ввода их увидели — значит все сделано верно и формула массива введена правильно. И результат будет 20.
Важно:не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке {=СУММ(A1:A5+1)} и ни о какой формуле и суммировании речи быть уже не может.
Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5). Теперь мы изменили функцию и ввели её как формулу массива: {=СУММ(A1:A5+1)}. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1)⇒=СУММ(2;3;4;5;6)

Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп. столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
=МИН(ЕСЛИ(A1:A100;A1:A10))
=MIN(IF(A1:A100,A1:A10))
Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
{=МИН(ЕСЛИ(A1:A100;A1:A10))}
то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет — 1.
Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП(TRANSPOSE). Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10):
выделяем диапазон ячеек(D1:M3), равный по количеству ячеек исходному диапазону значений(A1:C10), которые необходимо транспонировать

вписываем функцию ТРАНСП(TRANSPOSE)
в качестве аргумента указываем ссылку на исходный диапазон значений: =ТРАНСП(A1:C10)
завершаем ввод функции сочетанием клавиш Ctrl+Shift+Enter.
В диапазоне D1:M3 получим транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции(D1:M3) должно быть равно числу столбцов в исходном диапазоне(A1:C10), а число столбцов — числу строк. Если указать меньше — не все значения будут транспонированы. Если больше — то все лишние ячейки будут заполнены значениями #Н/Д(#N/A).
Какие особенности подобного применения функций массива:
во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать — так надо;

ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности — только все вместе. В противном случае просто получите сообщение «Нельзя изменять часть массива!». Бывает очень удобно иногда в целях защиты формул от изменений.
Итак, что самое важное надо запомнить для использования формул массива:
ввод формулы завершается сочетанием клавиш Ctrl+Shift+Enter
Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат
Важно: начиная с Excel 2021 и в Excel 365 появились динамические массивы. С их появлением выделять сразу несколько ячеек для ввода формулы массива не обязательно — они сами определяют будущий размер области результата. Так же динамические массивы не требует ввода тремя клавишами Ctrl+Shift+Enter

Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика
Формула массива Excel. Как пользоваться?
Промежуточные итоги в Excel
Специальная вставка в Excel
Формула массива Excel — это формула, при использовании которой происходит работа с диапазонами ячеек (не отдельно с ячейками), именуемыми как раз массивами. Если массив одномерный т.е. выделен только столбец или строку, то такой массив называют вектором, если двухмерный (несколько столбцов и строк), то массив называют матрицой.
Какие преимущества можно получить при использовании формул массива?
Когда не помогают другие возможности Excel, приходится прибегать к формулам массива (например, транспонирование формулой).
Итак, начнем с самих массивов. Объясняя простым языком, массив представляет собой набор данных, обрабатываемых в Excel одним параметром (аргументом).
По сути, формулы массива – такие же привычные формулы, которыми мы пользуемся ежедневно, с тем лишь отличием, что обычной функцией осуществляется использование отдельного значения. Формула массива же допускает использование определенного диапазона ячеек в качестве своих аргументов.
Содержание
Пример 1. Формула массива Excel
Пример №2. Практическое применение
И напоследок еще раз обозначу условия использования формул массива
Похожие статьи
Пример 1.
Формула массива Excel
Имеются два диапазона чисел. Нам требуется получить сумму произведений диапазонов. Обычный пример можно реализовать так: добавляется дополнительный, третий по счету столбец (либо строчка), после чего получается произведение и делается суммирование всего столбца.
Но попробуем сделать тоже самое при помощи формулы массива: сразу определим конечную функцию, которой является сумма:
=СУММ()
После этого определим аргументы, представляющие сумму этого массива в виде произведения из двух массивов, таким образом:
=СУММ(Диапазон1*Диапазон2)
В нашем случае
=СУММ(A1:A10*B1:B10)
В завершении нажимаем одновременно клавиши в следующей комбинации: CTRL+Shift+Enter
Нажатие этого сочетания и означает запуск Формулы массива! Конечная формула будет заключена в фигурные скобки {}
{=СУММ(A1:A10*B1:B10)}
Нюанс. Имеется специальная функция, =СУММПРОИЗВ(), предназначенная для получения суммы сразу всех произведений в массиве.
Пример №2. Практическое применение
Требуется определить сумму 1-го, 3-го и 10-го наибольших элементов из набора данных (диапазона B1:B10). Не прибегая к ФМ это делается так: сортируем по убыванию таблицу, воспользуемся дополнительным столбцом, проставляем в нем порядковые номера и извлекаем сумму при помощи функции =СУММЕСЛИ().
Но формулой массива все делается проще:
=СУММ(НАИБОЛЬШИЙ(B1:B10;{1;3;10}))
1. Здесь функция =НАИБОЛЬШИЙ() осуществляет поиск по убыванию элементов, которые были заданы (в нашем случае это 10, 8 и 1) в диапазоне данных B1:B10
2. Суммирование данного массива происходит с использованием известной функции =СУММ().
3. CTRL+Shift+Enter, не забываем
Удобно!
Пример файла
И напоследок еще раз обозначу условия использования формул массива
— завершаем ввод формулы комбинацией клавиш вида Ctrl+Shift+Enter;
— если произведен ввод формулы массива, нельзя вносить изменения в ячейки диапазона по отдельности. Делать это можно только одновременно со всеми ячейками, иначе получим уведомление вида «Нельзя изменить эту часть массива». Для примера с транспонирование формулой

Промежуточные итоги в Excel
Специальная вставка в Excel
Формулы матрицы Excel и функция Sumproduct
Aurora Training Advantage
на вебинаре по требованию
Формулы матрица Excel и функция Sumproduct
Информация о вебинаре $ 219
RED:
REPATED: RED: RED: . Продолжительность: 80 минут
Приглашенный докладчик: Майкл Соудер
Тема: Деловое администрирование, финансы, управление персоналом, налогообложение и бухгалтерский учет
Кредит: ATAAA 1. 0, ATATX 1.0, ATAOP 1.0, CPE 2.0
Обзор
СОДЕРЖА мощные инструменты, которые позволяют пользователю Excel легко выполнять множество сложных вычислений. Хотя эти инструменты имеют множество применений, большинство пользователей никогда не слышали о них или были напуганы кривой обучения, чтобы научиться их использовать. В этом классе раскрываются концепции, лежащие в основе этих инструментов, и используются несколько реальных примеров, включающих общие функции, чтобы раскрыть их мощь и универсальность.
Этот класс включает следующее:
Объясняет массивы.
Различают массивы с одной и несколькими ячейками.
Демонстрирует, как использовать константы массива.
Создает простые формулы массива с помощью функции СУММ.
Добавляет логические значения в формулы массива.
Строит более сложные формулы массива, используя функции ЕСЛИ, СУММ и СРЗНАЧ.
Предоставляет дополнительные возможности формул массива с использованием функций ДЛСТР, НАИБОЛЬШИЙ и МАЛЕНЬКИЙ.
Объясняет функцию СУММПРОИЗВ.
Решает сложные вычисления с помощью функции СУММПРОИЗВ.
Этот увлекательный интерактивный курс обязателен к посещению для пользователей Excel среднего и продвинутого уровня, которые хотят расширить свои навыки. Присоединяйтесь к Майклу Соудеру, который поможет вам раскрыть всю мощь формул массива и функции СУММПРОИЗВ и покажет, как применять их в реальном мире.
Введение
Темы 00:02:09
Что такое массив? 00:03:32
Простая формула массива 00:04:44
Основы массива 00:09:04
Массивы с одной и несколькими ячейками 00:15:24
Правила массивов с несколькими ячейками 00:16;34
Как использовать константы массива 00:26:08
Простые формулы массива Функции SUM, MIN и MAX 00:35:07
Простые формулы массива — функция LEN 00:38:46
Сложные формулы массива — логические значения 00:42:43
Комплексные Формулы массива — БОЛЬШОЙ и МАЛЕНЬКИЙ 01:00:30
Сложные формулы массива -СУММЕСЛИ, ПОИСКПОЗ и ИНДЕКС 01:06:30
Функция СУММПРОИЗВ 01:18:18
Функция СУММПРОИЗВ — Пример 01:18:48
Функция СУММПРОИЗВ (продолжение) — Пример 01:22:32
Приложение — Список функций 01:32:42
Attene Вопросы 01:34:02
Закрытие презентации 01:40:10
Константы массива 00:26:08
Формула массива 00:02:16, 00:03:34, 00:04:46
Booleans 90 00:42:49, 01:22:36
Функция ЕСЛИ 00:43:19
ИНДЕКС 01:07:38
БОЛЬШОЙ Функция 01:00:45
ДЛИН. 00:38:48
ПОИСКПОЗ 01:07:05
МАКС. 00:36:13
МИН.
СУММ 00:05:23, 00:14:04, 0:35:11
СУММЕСЛИ 01:06:36
СУММПРОИЗВ 00:02:44
Константы массива часто используются : в формулах массива для создания или управления несколькими значениями одновременно, а не одним значением. Существует три типа: горизонтальный, вертикальный и двухмерный.
Формула массива : В Excel формула массива позволяет выполнять сложные вычисления для одного или нескольких наборов значений. Результат может поместиться в одну ячейку или может быть массивом. Массив — это просто список или диапазон значений, но формула массива — это формула особого типа, которую необходимо вводить, нажимая Ctrl+Shift+Enter.
Булевы значения : Логические значения — это тип данных, имеющий только два возможных значения: ИСТИНА или ЛОЖЬ. Вы часто будете видеть логические результаты или логические выражения в Excel.
Функция ЕСЛИ : Используйте функцию ЕСЛИ, одну из логических функций, для возврата одного значения, если условие истинно, и другого значения, если оно ложно. Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.
Функция ИНДЕКС : Функция ИНДЕКС может использоваться для возврата данных из заданного диапазона на основе указанного вами номера строки и/или столбца.
Функция НАИБОЛЬШИЙ : Функция =НАИБОЛЬШИЙ может использоваться для возврата самого большого, второго по величине, третьего по величине и k-го по величине значения из диапазона.
ДЛСТР Функция : Функция Excel ДЛСТР возвращает длину заданной текстовой строки в виде количества символов. LEN также подсчитывает символы в числах, но форматирование чисел не учитывается.
Функция ПОИСКПОЗ : Функция ПОИСКПОЗ выполняет поиск заданного диапазона по указанным критериям и возвращает номер столбца или строки, если совпадение найдено. ПОИСКПОЗ можно использовать с другими функциями, которым требуется номер столбца или строки.
Функция MAX : Функция MAX в Microsoft Excel возвращает наибольшее значение из предоставленных чисел. Функция МАКС — это встроенная функция Excel, относящаяся к категории статистических функций. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию MAX можно ввести как часть формулы в ячейку рабочего листа.
Функция MIN : Функция MIN Microsoft Excel возвращает наименьшее значение из предоставленных чисел. Функция MIN — это встроенная функция Excel, относящаяся к категории статистических функций. Его можно использовать как функцию рабочего листа (WS) в Excel.
МАЛЕНЬКИЙ Функция : Используйте эту функцию для возврата значений с определенным относительным положением в наборе данных.
СУММ : Microsoft Excel определяет СУММ как формулу, которая «суммирует все числа в диапазоне ячеек». Это определение ясно указывает на то, что функция Sum предназначена для сложения чисел, а аргументы могут быть предоставлены с использованием комбинаций как чисел, так и диапазона ячеек. =СУММ Функция СУММ — это встроенная функция Excel, относящаяся к категории математических/триггерных функций. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию СУММ можно ввести как часть формулы в ячейку рабочего листа 9.0151
СУММЕСЛИ : Функция поиска в Excel, позволяющая складывать числа на основе заданного вами критерия. В отличие от ВПР, функция СУММЕСЛИ может суммировать два или более значений и возвращает ноль (вместо #Н/Д), если совпадений не найдено.
СУММПРОИЗВ : Функция СУММПРОИЗВ умножает диапазоны или массивы и возвращает сумму произведений.
Приглашенный докладчик
Кредит ATAAA
Aurora Training Advantage предлагает баллы за непрерывное обучение, предназначенные для признания приверженности обучению и превосходства в управлении.
ATATX Credit
Aurora Training Advantage предлагает баллы за непрерывное обучение, предназначенные для признания приверженности обучению и передового опыта в области бухгалтерского учета.
ATAOP Credit
Aurora Training Advantage предлагает баллы непрерывного образования, предназначенные для признания приверженности обучению и передового опыта в работе.
CPE Credit
Aurora Training Advantage зарегистрирована в Национальной ассоциации государственных советов по бухгалтерскому учету (NASBA) в качестве спонсора непрерывного профессионального образования в Национальном реестре спонсоров CPE. Государственные советы по бухгалтерскому учету имеют окончательные полномочия по принятию отдельных курсов для кредита CPE. Жалобы на зарегистрированных спонсоров могут быть поданы в Национальный реестр спонсоров CPE через его веб-сайт: www.nasbaregistry.org.
Для получения дополнительной информации об административных правилах, таких как жалобы и возмещение, а также отмена, пожалуйста, свяжитесь с нашими офисами по телефону 407-542-4317 или по адресу training@auroratrainingadvantage. com.
Вы должны ответить на все вопросы во время вебинара, полностью просмотреть запись и пройти тест в конце с 70% правильными ответами, чтобы получить балл CPE.
Только записанный веб-семинар
$219,00
Запись участников веб-семинара: нет участников 1 участников — $219.00 2 Участники — 298,00 долл. США 3 участники — 377,00 долл. США 4 участники — 456,00 долл. США 5 посетителей — 535,00 долл. США 6 посетителей — 614,00 долл. США 7 посетителей — 693,00 долл. США 8 посетителей — 772,00 долл. 14 участников — 1 246,00 долларов США 15 участников — 1 325,00 долларов США
Введение в формулы динамического массива в Excel

Введение
В сентябре 2018 года Microsoft представила формулы динамического массива в Excel. Их цель — упростить написание сложных формул и снизить вероятность ошибки.
Формулы динамического массива должны в конечном итоге заменить формулы массива, т. е. расширенные формулы, требующие использования Ctrl + Shift + Enter (CSE).
Вот краткое сравнение между формулой массива и формулой динамического массива, используемой для извлечения списка уникальных отделов из нашего списка в диапазоне A2:A7 .
Устаревшая формула массива (CSE):
Следующая формула вводится в ячейку D2 нажатием Ctrl + Shift + Enter и копированием вниз от D2 до D5 .
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$7, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($D$1:D1, $A$2:$A$7), 0)), "")}

Формула динамического массива:
Следующая формула вводится только в ячейку D2 и вводится нажатием Enter. С первого взгляда вы можете сказать, насколько легко и просто написать формулу динамического массива.
=UNIQUE(A2:A7)
Доступность
По состоянию на август 2020 г. формулы динамического массива доступны только пользователям Office 365.
Разлив и полигон
Формулы динамического массива работают, возвращая несколько результатов в диапазон ячеек на основе одной формулы, введенной в одну ячейку.
Это поведение называется «Разлив» , а диапазон ячеек, в который помещаются результаты, называется «Диапазон разлива» . Когда вы выбираете любую ячейку в пределах диапазона разлива, Excel выделяет ее тонкой синей рамкой.
В приведенном ниже примере формула динамического массива SORT находится в ячейке D2 , а результаты распределены в диапазоне D2:D7
=СОРТИРОВКА(A2:A7)
Результаты формулы являются динамическими, что означает, что если в источнике происходит изменение диапазон, результаты также изменяются, и диапазон разлива изменяется.
#РАЗЛИВ!
Обратите внимание, что если ваш диапазон переполнения не полностью пуст, возвращается ошибка #SPILL.
При выборе ошибки #ПРОБЛЕМА желаемый диапазон разлива формулы выделяется синей пунктирной рамкой. Перемещение или удаление данных в непустой ячейке устраняет эту ошибку, что приводит к сбросу формулы.
Обозначение ссылки на разлив
Для ссылки на диапазон разлива формулы мы помещаем символ # после ссылки на первую ячейку в разливе.
Вы также можете сослаться на разлив, выбрав все ячейки в диапазоне разлива, и ссылка на разлив будет создана автоматически.
В приведенном ниже примере мы хотели бы подсчитать количество сотрудников в нашей фирме, используя формулу COUNTA после того, как они были упорядочены в алфавитном порядке с использованием формулы динамического массива СОРТИРОВКА .
Мы вводим формулу SORT в D2, чтобы упорядочить сотрудников в нашем списке:
=SORT(A2:A7)
Затем мы вводим формулу COUNTA в G2 для подсчета количества сотрудников :
=COUNTA(D2#)
Обратите внимание на использование # в D2# для ссылки на результаты, переданные SORT в диапазоне D2:D7.