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

Формула массива в Excel. Массивы данных и значений.

Количество просмотров: 573

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

Доброе время суток всем!  В текущем уроке мы рассмотрим работу с таким удобным инструментом Excel, как массивы и формулы массивов. Что же это такое и как с этим работать? Если говорить официально, то массив это диапазон, содержащий данные одного типа. Массив обрабатывается как одно целое. Именно поэтому поменять формулу только для части массива невозможно.  Почему это удобно и как с этим работать? Давайте разберемся на практике. Прежде всего стоит заметить, что те ячейки с определенной структурой и границами, которые мы гордо называем таблицами – это не таблицы. Почему? Потому что данные в таблице связаны между собой. Точно так же, как номер телефона в телефонной книге привязан к определенному абоненту. Для простоты, чтобы проще различать таблицы и диапазоны между собой, в русской версии Excel настоящие таблицы называются умными.

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

Диапазоны могут хранить данные разного типа. В Excel это могут быть числа, текст, логические данные, дата, время, ссылки на объекты. Вектор ячеек, в котором находятся данные произвольного типа, одинакового или нет – неважно, называют списком. А вот любой диапазон, в том числе и вектор, содержащий данные одного типа, называют массивом. В чем его польза и преимущество? В том, что если тип данных одинаковый, то для них можно использовать одинаковые формулы. Массив можно рассматривать как единое целое, состоящее из отдельных элементов. Поэтому любую формулу можно применять сразу ко всему массиву, не используя ее по отдельности для каждой ячейки. Именно такая формула, связанная с массивом в целом, а не с отдельной его частью, называется формула массива. Приведем примеры использования формул массива для решения практичеких задач.

Простой пример использования массива для получения общего итога.

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

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

Однако давайте внимательно посмотрим на итоговую формулу. В скобках указан диапазон, значения ячеек которого просуммированы. Что же находится в этих ячейках? Очевидно, что результаты умножения ячеек в текущей строке из столбцов «В» и «С» с ценой и количеством соответственно.

А теперь подумаем вот о чем. Диапазон с ценами – это массив? Безусловно, там же только числа. Аналогично и диапазон с количеством также является массивом. А если это массивы, то вместо того, чтобы попарно перемножать значения цены и находящегося в той же строки количества,  можно применить Функцию «СУММ» непосредственно к произведению этих массивов!

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

Обратите внимание на фигурные скобки в строке формул. Они говорят о том, что перед нами не просто формула, а формула массива. Для ее ввода одновременно нажимаем комбинацию клавиш «Ctrl + Shift + Enter». Если просто нажать»Enter» то программа решит, что мы вводим обычную формулу. Она просто не поймет, как можно в ней ОДНОВРЕМЕННО перемножать несколько ячеек и даст ошибку. Поэтому будьте внимательны!

Применение массива для подсчета отработанных часов.

Рассмотрим на примере, как используется формула массива в типичной для бухгалтеров ситуации  по начислению заработной платы. Мы применяем для начисления почасовую оплату труда. Раньше, что греха таить, всем ставили одинаковые часы. Главное, чтобы сотрудник на работу пришел, а 8 отработанных часов в табеле будут в любом случае.

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

В качестве варианта можно поступить таким образом. Вначале извлечь и просуммировать только часы. Затем извлечь и просуммировать минуты. Минуты превратить в часы, поделив полученное значение на 60. Наконец, сложить начальную сумму часов с суммой часов, полученных из минут. Все хорошо, но только пока вы высчитываете часы максимум для десяти человек. А если их хотя бы 100? Причем в реальности вариантов гораздо больше. Это и срок доставки, когда мы оплачиваем за каждый час работы курьера. Это и время перевозок. Примеров много. Как же быть? На помощь опять приходит формула массива. В ней мы сделаем все этапы, перечисленные выше, вместе, применив для расчета функции ЧАС, МИНУТЫ И СУММ.

ЧАС – извлекает из указанного времени значение часа. В частности, формула =ЧАС(«10:52») даст результат 10.

МИНУТЫ – аналогична предыдущей, но извлекает соответственно минуты. Таким образом, формула =МИНУТЫ(«10:52») даст результат 52. Кстати, обратите внимание на кавычки в формуле. Они нужны, так как время указано явно, а не получено из ячейки.

СУММ – суммирует данные.

В итоге для данного примера получаем комбинацию:

{=СУММ(ЧАС(D2:D6))+СУММ(МИНУТЫ(D2:D6))/60}

Не забываем про нажатие в конце сочетания клавиш «Ctrl + Shift + Enter».

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

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

В следующем примере мы с помощью формулы массива получим среднюю, минимальную и максимальную цену по каждому товару. Причем, если среднюю цену можно найти с помощью встроенной функции СРЗНАЧЕСЛИ, то функций МИНЕСЛИ и МАКСЕСЛИ не предусмотрено.

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

{=СРЗНАЧ(ЕСЛИ($A$2:$A$17=F2;$B$2:$B$17))}

Формула расшифровывается так: если в диапазоне $A$2:$A$17 значение очередной ячейки совпадает с значением в ячейке F2, то для соответствующих   ячеек из диапазона $B$2:$B$17 вычислить среднее значение. Excel загрузит таблицу, уберет из нее все строки, которые не содержат нужное нам наименование. После этого по оставшимся строкам в цене – а $B$2:$B$17 это диапазон с ценами – вычисляется   среднее значение.

Если теперь отфильтровать наименования, выбрав только «Стол» и вычислить среднее значение по отображаемым ячейкам с помощью стандартных функций СРЗНАЧ или СРЗНАЧЕСЛИ, то закономерно выходит тот же результат.

Это значит, что формула написана верно. Поменяв в ней СРЗНАЧ на МИН, мы можем найти минимальное значение цены для выбранного товара, поменяв на МАКС – максимальное значение цены, ну и так далее.

В примере ниже для адреса ячейки с наименование товара применена смешанная адресация с закреплением только столбца. Такой трюк позволил вначале протянуть полученную формулу вправо, после чего поменять в ней вариант вычисления на нужный именно в текущем столбце. К примеру, для минимального использовалась функция МИН, а для максимального значения – функция МАКС. Затем все результаты выбрали вместе и протянули вниз.

 С помощью фактически ОДНОЙ ФОРМУЛЫ получили несколько РАЗНЫХ ВАРИАНТОВ итоговых значений. Это и минимум, и максимум, и среднее. Удобно? Конечно!

 В новом примере с помощью аналогичной формулы были найдены максимальные списания процентов по кредиту. С учетом того, что проценты для клиента отрицательны, то использовалась функция МИН, а не МАКС, как кто-то, может быть, ожидал. Обратите внимание, что указывать нужное наименование полностью в этой формуле можно, а вот использовать подстановочные знаки, увы, нельзя.

Выбор данных с помощью ВПР с учетом двух и более критериев.

 Наконец, разберем более сложный вариант с применением формул массива. Имеются следующие данные.

Необходимо найти цену на хлеб в марте. Можно было бы применить сразу ВПР, но – позиций с хлебом несколько, как и позиций с мартом. Как быть? На помощь снова приходит формула массива.

Еще раз обращаю внимание на схожесть формулы с той, которую разбирали в предыдущей части. Конструкция ЕСЛИ($A$2:$A$19=F2;$B$2:$D$19) позволила отсечь строки в диапазоне ;$B$2:$D$19 , у которых месяц совпадал с указанным в ячейке F2, то есть – мартом. Из оставшихся данных был создан виртуальный диапазон, в котором и сработала функция ВПР. Формулу можно было бы и усложнить, например, получив цену по хлебу не только в определенном месяце, но и по определенном городе.

Заключение.

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

Однако не стоит забывать, что такие формулы применяют тогда, когда надо стразу применить один и тот же расчет к множеству данных ОДНОГО типа. Если значения в диапазоне разного типа, то формула может и не сработать. В то же время — это мощное оружие в руках пользователя, и применять его для решения мелких задач все равно что из пушки стрелять по воробьям. Кроме этого, не забывайте, что для ввода формул массива обязательно требуется комбинация «Ctrl + Shift + Enter».

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

На этом наш урок завершен. Всем хорошего дня и успехов в работе! Если вас заинтересовал материал, то встретимся на занятиях в нашем учебном центре для еще более углубленного обучения возможностям Excel на практике.

90400cookie-checkМассивы в Excel. Примеры использования формул массива.no

Dashboard с динамическими массивами | finalytics.pro

Еще в начале этого года на глаза попался интересный пример Dashboard-а, подготовленного в Tableau. Ради интереса решил некоторые приёмы и оформление воспроизвести в Excel и сделал файл без «стероидов» – надстроек Power Query & Pivot, без которых последнее время не обходится практически ни один отчет. Ну и заодно чтобы проверить, получится ли воспроизвести функционал из оригинальной версии.

Оригинал в Tableau, советую обязательно посмотреть:

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

Отдельно хочу обратить внимание на формулы динамических массивов. Этот функционал разработчики относительно недавно добавили в Excel 365 и 2021 (у новизны есть побочный эффект: если файл не будет работать и выдаст ошибку, например, ИМЯ, вам придется обновить версию Excel). Но, как показывает опыт, у многих аналитиков версия Excel всегда самая свежая. Поэтому скачивайте, смотрите и разбирайте формулы 🙂

Скачать файл Excel

Относительно недавно разработчики добавили в Excel новый функционал – формулы и ссылки для динамических массивов. И заодно создали новую концепцию, которая, как нам кажется, повлияет на многие будущие подходы к построению таблиц и работе с ними. Чтобы объяснить, как работают динамические массивы, начнём с формул, которые их создают. Например, с формулы УНИК.

Формула УНИК извлекает уникальные значения из диапазона.

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

У созданного динамического spill-массива* нет фиксированного размера – столбец автоматически меняется при добавлении новых значений в таблицу с исходными данными.

Динамические массивы подсвечиваются синей рамкой при их выделении мышкой.

* от spill — проливаться

Кто говорил, что типы ссылок в Excel – абсолютные и относительные – тема для новичков и все её знают? Разбираем новый функционал ссылок для динамических массивов. Заодно можно поностальгировать и вспомнить, когда вы впервые познакомились с темой про типы ссылок в Excel =)

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

В конце такой ссылки должен быть значок # – решетка.

В целом, у ссылок на динамические массивы логика очень похожа на ссылки диапазонов, которые записываются в фигурных скобках { } с нажатием Ctrl+Shift+Enter. Но, в отличие от диапазонов, ссылку на массив записывают только в одну ячейку, а ещё массивы умеют сами автоматически «протягиваться».

Диапазон

{ = A1:A3 }

Динамический массив

= A1#

Ссылки на динамические массивы можно применять так же, как обычные, добавлять значки $. И применять в вычислениях с «обычными» формулами. Ниже несколько примеров применения таких ссылок с формулами ранжирования, транспонирования, подсчета значений. Проверка вводимых значений тоже становится проще и быстрее.

Ранжирование

= РАНГ.РВ( A2#; A2# )

Подсчёт количества непустых ячеек

= СЧЁТЗ( A2# )

Транспонирование

=ТРАНСП( A1# )

Выпадающие списки

в меню Данные → Проверка данных

На сегодняшний день в Excel существует восемь формул для динамических массивов. Первоначально в этот список не входили ПРОСМОТРX и ПОИСКПОЗX. Формула Let также «заточена» для работы с массивами. И ещё там можно использовать оператор неявного пересечения @ (обязательно почитайте, что это такое). При обращении к массивам может появиться новый вид ошибки — #ПЕРЕНОС! или #SPILL!

Формула	Для чего нужна
УНИК	Извлекает список уникальных значений
ФИЛЬТР	Фильтрация по заданным условиям
СОРТ	Сортировка по столбцу (строке)
СОРТПО	Сортирует по данным в другом массиве
СЛМАССИВ	Генерация массива случайных чисел
ПОСЛЕД	Генерация массива последовательных чисел
ПРОСМОТРX	Современный аналог ВПР
ПОИСКПОЗX	Современный аналог ПОИСКПОЗ

Похоже, что в будущем динамические массивы будут всё чаще применяться в Excel.   Нам с коллегами они тоже показались классными – формула вводится только в одну ячейку, ничего не надо «протягивать», потому что массив обновляется сам (даже кнопку «Обновить» не надо нажимать). На этот раз разработчики хорошо позаботились о пользователях – особенно о тех, которые во всём разобрались 😉

Теги: ExcelЭкономика и финансы

Автор: Станислав Салостей

Функции работы с текстом и массивами в Excel

Привет, офицеры-инсайдеры! Меня зовут Джо МакДейд, и я руководитель программы в команде Excel. Я очень рад поделиться с вами доступностью 14 новых функций Excel, разработанных для облегчения работы с текстом и массивами на ваших листах.

Функции работы с текстом 

При работе с текстом обычной задачей является «разделение» текстовых строк с помощью разделителя. Вы уже можете сделать это с помощью комбинаций ПОИСК, НАЙТИ, ВЛЕВО, ВПРАВО, СРЕДНЯЯ, ПОДСТАВИТЬ и ПОСЛЕДОВАТЕЛЬНОСТЬ, но мы слышали от многих из вас, что это может быть сложно использовать.

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

Мы также упростили «разделение» текста на несколько сегментов с помощью TEXTSPLIT. Затем каждый текстовый сегмент автоматически помещается в свою ячейку с помощью магии динамических массивов.

 

• TEXTBEFORE – возвращает текст, который стоит перед символами-разделителями 
• TEXTAFTER — возвращает текст после символа-разделителя
.
• TEXTSPLIT — разбивает текст на строки или столбцы с помощью разделителей

Функции работы с массивами

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

Объединение массивов

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

 

 

• VSTACK — Массивы складываются вертикально
• HSTACK — складывает массивы по горизонтали

Формирование массивов 

Было непросто изменить «форму» данных в Excel, особенно из массивов в списки и наоборот. Если у вас есть двумерный массив, который вы хотите преобразовать в простой список, используйте TOROW и TOCOL для преобразования двумерного массива в одну строку или столбец данных.

Используя функции WRAPROWS и WRAPCOLS, сделайте обратное: создайте 2D-массив заданной ширины или высоты, «перенося» данные на следующую строку (точно так же, как текст в этом документе), как только будет достигнуто выбранное вами ограничение ширины/высоты. .

 

• TOROW — возвращает массив в виде одной строки
• TOCOL — возвращает массив в виде одного столбца 
• WRAPROWS — перенос массива строк в двумерный массив 
• WRAPCOLS — превращает массив столбцов в двумерный массив 

Изменение размера массивов

Массивы слишком велики? Без проблем. Введите функции ВЗЯТЬ и БРОСИТЬ! Они позволяют вам уменьшить ваши массивы, указав количество строк, которые нужно сохранить или удалить из начала или конца вашего массива.

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

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

• TAKE — возвращает строки или столбцы из начала или конца массива
• DROP — удаление строк или столбцов из начала или конца массива
• CHOOSEROWS — возвращает указанные строки из массива 
• CHOOSECOLS — возвращает указанные столбцы из массива  
• EXPAND — расширяет массив до указанных размеров

Сценарии, которые можно попробовать 

• Используйте » » (пробел) в качестве разделителя с TEXTBEFORE для извлечения имени и TEXTAFTER для извлечения фамилии 
• Используйте TEXTSPLIT для разделения имен в массив с » » (пробелом) в качестве разделителя 

Если вы хотите объединить два диапазона данных: 

• Используйте VSTACK для объединения двух диапазонов данных по вертикали 
• Использовать HSTACK для объединения двух диапазонов по горизонтали 

Доступность  

В настоящее время эти функции доступны пользователям бета-канала версии 2203 (сборка 15104. 20004) или более поздней версии для Windows и версии 16.60 (сборка 22030400) или более поздней версии для Mac.

Еще нет? Это скорее мы, а не вы.

Функции выпускаются в течение некоторого времени, чтобы обеспечить бесперебойную работу. Мы выделяем функции, которых у вас может не быть, потому что они медленно выпускаются для большего числа инсайдеров. Иногда мы удаляем элементы, чтобы улучшить их на основе ваших отзывов. Хотя это случается редко, мы также оставляем за собой право полностью исключить функцию из продукта, даже если вы, как инсайдер, имели возможность попробовать ее.

Отзыв 

Если у вас есть отзывы или предложения, вы можете отправить их, нажав Справка > Отзыв .

Узнайте, какую еще информацию вы должны включить в свой отзыв, чтобы убедиться, что он действенный и доходит до нужных людей. Мы рады услышать от вас!

Подпишитесь на информационный бюллетень программы предварительной оценки Office и получайте последнюю информацию о функциях программы предварительной оценки на свой почтовый ящик раз в месяц!

Формулы массива в Excel (пример, ярлык)

Формулы массива — это очень полезные и мощные формулы, используемые для выполнения некоторых очень сложных вычислений в Excel. Она также известна как формула CSE. Нам нужно нажать «CTRL + SHIFT + ENTER» вместе, чтобы выполнить формулы массива вместо нажатия клавиши ввода. Существует два типа формул массива: один дает нам один результат, а другой дает несколько результатов.

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

Массивы в excel. Массивы в Excel. Массив VBA в Excel — это единица хранения или переменная, которая может хранить несколько значений данных. Эти значения обязательно должны быть одного типа данных. Это означает, что связанные значения группируются вместе для сохранения в переменной массива. Подробнее можно назвать формулами массива в Excel. Массив в Excel — это мощная формула, которая позволяет нам выполнять сложные вычисления.

Что такое массив? Массив — это набор значений или переменных в наборе данных, например {a,b,c,d} — это массив со значениями от «a» до «d». Точно так же в массиве Excel есть диапазон ячеек значений,

На приведенном выше снимке экрана ячейки от B2 до G2 представляют собой массив или диапазон ячеек.

Они также известны как «формулы CSE» или «формулы Control Shift Enter». Например, для создания формулы массива в Excel мы должны нажать « Ctrl + Shift + Enter ».

В Excel у нас есть два типа формул массива:

1. Один, который дает нам один результат.
2. Другой, который дает нам более одного результата.

В этой теме мы изучим оба типа формул массива.

Содержание

• Формулы массива в Excel
  • Объяснение формул массива в Excel
  • Как использовать формулы массива в Excel?
  • Пример №1
  • Пример №2
  • Пример №3
  • Что нужно помнить
  • Рекомендуемые статьи

Объяснение формул массива в Excel очень эффективны, формулы массива помогают нам выполнять сложные формулы в Excel

расчеты.

Из приведенных выше примеров мы узнали, что эти формулы в Excel упрощают сложные и длительные вычисления. Есть одна вещь, которую нужно помнить. Однако в примере 2 мы вернули несколько значений, используя эти формулы в Excel. Мы не можем изменить или вырезать значение ячейки, так как она является частью массива.

Предположим, мы хотим удалить ячейку G6 Excel. Это выдаст нам ошибку. Даже если мы изменим значение в типе ячейки, любое случайное значение в ячейке Excel выдаст ошибку.

Например, введите число в ячейку G6 и нажмите клавишу «Ввод». Это может привести к следующей ошибке,

При вводе случайного значения 56 в ячейку G6 может выдавать ошибку, что мы не можем изменить часть массива.

Если нам нужно отредактировать формулу массива, перейдите на панель функций и отредактируйте предоставленные значения или диапазон. Если мы хотим удалить формулу массива, удалите весь массив, т. е. диапазон ячеек от B6 до G6.

Как использовать формулы массива в Excel?

Давайте изучим формулу массива на нескольких примерах:

Вы можете скачать этот шаблон Excel формулы массива здесь – Шаблон Excel формулы массива

Можно использовать формулы массива двух типов:

1. Если мы хотим вернуть одно значение, используйте эти формулы в одной ячейке, как в примере 1.
2. Если мы хотим вернуть более одного значения, используйте эти формулы в Excel, выбрав диапазон ячеек, как в примере 2.
3. Нажмите CTRL + Shift + Enter, чтобы создать формулу массива.

Пример #1

Данные о продажах ресторана состоят из цены каждого продукта и количества проданных продуктов.

Владелец хочет рассчитать общий объем продаж этих продуктов.

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

Кроме того, он получает общий объем продаж, как указано ниже:

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

Мы создадим нашу первую формулу массива в ячейке B7.

Ниже приведены шаги для создания первого массива: –

1. В ячейке B7 введите =СУММ и нажмите кнопку «Tab» на клавиатуре. Это может открыть формулу суммы.

   
   

2. Выберите диапазон ячеек от B2 до G2.

   
   

3. Теперь поставьте знак звездочки «*» после B2:G2 для умножения.

   
   

4. Выберите диапазон ячеек от B3 до G3.

   
   

5. Вместо нажатия клавиши «Ввод» нажмите «CTRL + SHIFT + ENTER».

   
   

   В результате Excel может предоставить общую стоимость продаж путем умножения количества продуктов на цену в каждом столбце и их суммирования. Например, в выделенном разделе мы видим, что Excel создал массив для диапазона ячеек от B2 до G2 и от B3 до G3.

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

Пример #2

Что, если при тех же данных владелец хочет знать продажи каждого продукта в отдельности, например продажи продуктов 1 и 2?

Он может пройти долгий путь. В каждой ячейке выполните функцию, которая рассчитает стоимость продаж.

Уравнение, указанное в ячейке B5, потребуется повторить для ячеек C4, D4 и т. д.

Опять же, это было бы утомительной задачей, если бы это были большие данные.

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

#1 – Выберите ячейки, в которых мы хотим получить наши промежуточные итоги, т. е. продажи по каждому продукту. В данном случае это диапазон ячеек от B8 до G8.

#2 – Введите знак равенства «=» a

#3 – Выберите диапазон ячеек от B2 до G2.

#4 – Введите знак звездочки «*» после B2:G2.

#5 – Теперь выберите диапазон ячеек от B3 до G3.

#6 – Не нажимайте клавишу «Ввод» для формулы массива. Нажмите «CTRL + SHIFT + ENTER» для формулы массива.

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

Ячейки с B6 по G6 представляют собой массив.

Пример #3

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

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

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

#1 –  В ячейке D8 введите =среднее. Затем нажмите кнопку «таб».

#2 –  Чтобы рассчитать рост, нам нужно будет вычесть значения одного месяца из предыдущего месяца, чтобы выбрать диапазон ячеек от B3 до B7.

#3 –  Поместите знак вычитания (-) после B3:B7,

#4 –  Теперь выберите ячейки от B2 до B6.

#5 —  Что касается массива в Excel, не нажимайте клавишу «Ввод». Нажмите CTRL + SHIFT + ВВОД . »

Формулы массива в Excel легко рассчитывают средний рост продаж без каких-либо хлопот.

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

Что следует помнить

1. Они также известны как формулы CSE или Control Shift Enter ExcelControl Shift Enter ExcelCtrl-Shift Enter In Excel — это команда быстрого доступа, которая облегчает реализацию формулы массива в функции Excel для выполнения сложного вычисления заданного данные. В целом он преобразует определенные данные в формат массива в Excel с несколькими значениями данных для этой цели. Подробнее Формулы.
2. Не делать скобки для массива; Excel сам это делает. Это вернет ошибку или неверное значение.
3. При вводе скобки «{» вручную Excel будет воспринимать ее как текст.
4. Не нажимайте клавишу «Ввод». Вместо этого нажмите «CTRL + SHIFT + Enter», чтобы использовать формулу массива.