В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки { } (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).
Если бы мы нажали просто ENTER , то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.
Чтобы глубже понять формулы массива проведем эксперимент:
- выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
- в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим {1:4:9:16:25:36:49:64:81:100:121} – массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).
Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:
- выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
- в строке формул выделим аргумент функции СУММ() , т.
2 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим {1:4:9:16:25:36:49:64:81:100:121} – тот же массив, что и в первом случае.
Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.
Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). 2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.
Формулы массива в Excel — введение
Сегодня я решил приступить к теме, которая будет совсем не для новичков, а именно к формулам массива. Знание формул массива и свободное оперирование ими является одним из признаков значительного уровня в Excel. Формулы массива свободно используются знатоками Excel, на форумах часто можно заметить, как мастера перед аудиторией щеголяют своим уровнем владения этим инструментом.
Многие, точнее подавляющее большинство пользователей, среди которых есть и многолетние практики, вполне спокойно обходятся без формул массива. Причина в том, что это довольно сложный инструмент для понимания, плюс он используется как правило для решения сложных и заковыристых задач. Есть задачи, которые без формул массива (далее ФМ) в принципе средствами Excel не решаемы.
В чем же фишка? Уже из названия видно, что это формулы, которые работают с целыми диапазонами (то бишь массивами). Есть функции, которые работают даже с двумерными массивами, например ИНДЕКС. Но принцип действия гораздо проще, чем в ФМ.
В чем преимущества использования этих формул? Огласите списочек Я бы выделил следующие:
- Они позволяют решать сложные задачи там, где пасуют остальные средства Excel, кроме, пожалуй сводных таблиц. Несложные задачи лучше не решать, это как забивать кувалдой кнопку в доску, чтобы повесить объявление о субботнике (был недавно на работе ). Дело даже не в этом, а в том, что они жрут памяти больше обычных формул.
- Они позволяют обходиться одной формулой там, где в противном случае надо было бы делать макросы, плодить вспомогательные таблицы и т.д.
Давайте наконец перейдем от вступления к практике. Итак, сначала определимся с массивами. Массив, если по простому, это набор данных, который Excel’ем обрабатывается как один аргумент (параметр). Он может быть одномерным (горизонтальным или вертикальным) или двумерным.
Грубо говоря, наши формулы массива – это те же формулы, что мы используем повседневно, только в качестве аргументов там, где обычная функция использует отдельное значение, формула массива может использовать диапазон.
Первый пример, пока простенький.
У нас есть два диапазона. Мы хотим получить сумму их произведений. Это простой пример, который я обычно реализую следующим образом: добавляю третий столбец (или строчку), получаю произведения и суммирую весь столбец
Сделаем это через формулу массива: сначала мы определяем конечную функцию, это сумма.
=СУММ()
Затем определяем аргументы – а это для нас сумма массива, который сам по себе является произведением двух массивов, т.е.
=СУММ(массив1*массив2)
И последний, завершающий штрих, без которого Excel огрызнется «сам дурак!» — надо нажать в режиме редактирования CTRL+Shift+Enter.
Есть нюанс – вообще-то есть функция СУММПРОИЗВ, которая призвана давать сумму произведений массивов. Так что прежде чем использовать ФМ, надо посмотреть, может нам помогут функции СУММЕСЛИ и ей подобные?
Пример второй, сложноватый.
Найти сумму 5го, 6го и 7го наибольших элементов набора данных. Как это делается без ФМ: таблица сортируется по убыванию, берется дополнительный столбец, там проставляются порядковые номера и через функцию СУММЕСЛИ вытаскиваем сумму. Ну или еще каким-нибудь способом, которым это можно сделать. Не сильно проще.
Формула массива делает все без дополнительных телодвижений:
=СУММ(НАИБОЛЬШИЙ(B3:B25;{5;6;7}))
Понятно? Нет? Ну, давайте по шагам:
- Функция НАИБОЛЬШИЙ ищет те элементы, которые заданы (5,6 и 7-й) по порядку убывания. Результатом будет три числа, т.е. отдельный массив.
- Функция СУММ суммирует этот массив.
Не буду говорить за всех, я чаще всего использую в ФМ следующие функции:
ЕСЛИ
ИНДЕКС
ПОИСКПОЗ
СТРОКА (СТОЛБЕЦ)
НАИБОЛЬШИЙ (НАИМЕНЬШИЙ)
СУММ
И(ИЛИ)
Теперь, раз вы имеете малое представление о действии ФМ, скажу, для чего я лично использую их
- Для нахождения в какой-либо таблице ВСЕХ(!) элементов, отвечающих заданному условию.
Кто уже просматривал мой «Ускоренный практикум», помнит, что с помощью ФМ я выстраивал первую пятерку игроков, ранжируя по определенному признаку. Т.е., у меня например есть таблица банков, где один из признаков – количество банкоматов. В принципе, самое простое решение – использовать функцию НАИБОЛЬШИЙ, чтобы вытащить первую пятерку (десятку, двадцатку) значений по показателю банкоматов и затем с помощью функции ВПР найти соответствующие им названия банков. Но что делать, если значения повторяются? (Забудем на время про сводную таблицу.) Т.е., Альфабанк (не путать с реальным названием ) имеет одно и то же количество банкоматов, что и Бетабанк. ВПР в этом случае найдет только первого, того, кто в таблице стоит повыше. Т.е. будет в пятерке банков два Альфабанка.
- Для автоматической сортировки и фильтрации элементов.
Для решения такой же задачи стандартными средствами потребовалось бы все время пользоваться сортировкой или сводной таблицей. И в этом случае ссылаться на эту таблицу было бы не то что сложно, но немного муторно. А у меня например, есть в списке тех отчетов, что я составляю, рейтинг банков. Он составляется на ежемесячной основе и содержит 10-20 показателей – кредитный портфель, депозитный портфель, ROE, ROA и т.д. И по форме отчета есть 10-20 таблиц, которые являются основой для диаграмм, соответственно должны быть отсортированы по убыванию.
Само собой, мне, как и каждому экселисту, лень было делать эти сортировки, поэтому я просто настроил ФМ и теперь просто завожу данные, а они уже сами сортируются и выделяются нужными цветами.
Тонкости использования ФМ:
- Бывает, приходится использовать константы, типа {1;2;3;4;5}. Это проще, чем где-то его набивать и вставлять ссылку. Но надо учитывать, что если числа разделяются точкой с запятой, то это горизонтальный массив. А если двоеточием – {1:2:3:4:5} – то вертикальный.
- ФМ может быть применена как к ячейке, так и к диапазону.
Соответственно, на выходе будет диапазон. Менять формулу в этом случае можно только во всем диапазоне. Как вводится и меняется формула – выделяется диапазон, и забивается формула, завершается CTRL+Shift+Enter.
- Отличительным признаком ФМ являются фигурные скобки. Они видны, пока не перейдете в режим редактирования ячейки. Как-то непривычно порой разбирая формулы, видеть, что там где должна быть ссылка на ячейку, стоит диапазон –
Кто еще считает, что можно нормально обойтись и без ФМ, напишите в комментариях к статье, сможете ли реализовать расчет цены остатков на складе по методу ФИФО/ЛИФО.
Эксель Практик
«Глаза боятся, а руки делают»
P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс «Топ-10 инструментов Excel») и будьте в курсе новых событий.
Формула массивав Google Sheets и Excel и чем она отличается Формула массива
в Google Sheets и Excel полностью различается.
Во-первых, ознакомьтесь с некоторыми преимуществами использования таблиц Google и формул массива Excel.
В качестве примечания: сравнение функциональности проводится между Таблицами и более ранними версиями Excel, а не с Excel 365.
В Excel 365 Microsoft представила функции динамического массива, а функция SPILL полностью преобразовала возможности массива Excel.
Так что, если вы являетесь пользователем Microsoft 365, сравнение может показаться вам неинтересным. Но это поможет вам лучше узнать Таблицы.
Преимущества формул массива Google Sheets и ExcelИспользование формул массива в решениях для работы с электронными таблицами, таких как Excel и Google Sheets, — это настоящее волшебство. Но обратите внимание, что вы не можете использовать формулы массива во всех случаях.
Вот некоторые преимущества использования формулы массива в Google Sheets и Excel.
- Это одна формула, которая обрабатывает несколько строк.
Это означает, что формула в одной ячейке может принимать значения в других строках/столбцах и заполнять расширенный результат, называемый результатом массива или результатом одной ячейки.
- Его легко редактировать, особенно в Google Таблицах и Excel 365.
- Когда вы используете формулы во многих ячейках, процесс редактирования может занять много времени.
- Повышает производительность электронных таблиц.
- Формула массива расширяет функциональность таких функций, как Sumif, Vlookup и т. д.
- Поддерживает бесконечные диапазоны в Google Таблицах.
Вам может понравиться: Как использовать Vlookup для возврата результата массива в Google Sheets.
Формула массива в Google Sheets и ExcelВ Google Sheets есть функция ArrayFormula, а в Excel ее нет.
Таким образом, использование формулы массива Google Таблиц в Excel может вернуть ошибку #ИМЯ?. Это из-за нераспознанного текста в формуле.
Excel предлагает другую функциональность формулы массива.
Мы можем использовать открывающие/закрывающие фигурные скобки вокруг формул, чтобы сделать их формулами массива в Excel.
Опять же, просто ввести фигурные скобки недостаточно. Вводить его следует с помощью сочетания клавиш Ctrl+Shift+Enter.
При копировании формулы массива Excel в Google Таблицы она может вести себя как нерасширяемая формула.
Итак, когда вы открываете файлы Google Sheets в Excel или наоборот, вы столкнетесь с такими проблемами с формулами.
Давайте посмотрим, чем отличается формула массива в Google Таблицах и Excel, на примерах формул.
Чем отличается формула массива в Google Таблицах и ExcelЧтобы понять разницу, давайте изучим использование. Это лучший способ.
Я начинаю с формулы массива из нескольких ячеек в Excel и Google Sheets.
Формулы массива с несколькими ячейками Формула массива с несколькими ячейками возвращает расширяющийся результат.
Google Sheets и Excel Non-Array Formulas:
У меня есть таблица, содержащая детали заказа на покупку нескольких заполнителей/песчаных материалов, и я хочу рассчитать общее количество каждого продукта на основе их количества в заказе.
Вот как этого добиться, используя формулы без массивов.
Связанный: — Как суммировать, умножать, вычитать, делить числа в Google Таблицах.
Формула массива в Google Таблицах с использованием функции ArrayFormula:
Дважды щелкните ячейку F2 и введите приведенную ниже формулу.
=Формуламассива(D2:D6*E2:E6)
Или
Дважды щелкните ячейку F2 и введите =D2:D6*E2:E6
, а затем нажмите Ctrl+Shift+Enter. Таким образом, Google Sheets вставит функцию ArrayFormula.
Массив Excel с использованием фигурных скобок:
Подход к массиву Excel совершенно другой, и мне кажется, что Google Таблицы в этом случае намного лучше, чем Excel.
Excel поддерживает только сочетание клавиш. Однако Excel в Office 365 является исключением.
В Excel выберите ячейку F2:F6.
Введите формулу =D2:D6*E2:E6
и нажмите Ctrl+Shift+Enter.
Выше я подробно описал разницу в применении формул массива в Excel и Google Sheets. Сейчас вот последствие .
Excel:
Когда вы попытаетесь удалить/вставить строку/строки в этот диапазон формул, вы получите уведомление в виде сообщения ниже.
Вы не можете изменить часть массива
Тогда как я могу изменить диапазон формул массива Excel?
Выберите ячейки, содержащие формулу (в нашем примере это F2:F6).
Затем нажмите «Удалить».
При желании вы можете скопировать формулу перед ее удалением. Теперь вы можете вносить изменения в отдельные ячейки.
Google Sheets:
В Google Sheets вы можете вставлять строки или удалять новые строки в диапазоне формул.
Формула будет автоматически охватывать вновь вставленные строки. Никаких предупреждающих сообщений или ограничений нет.
Итак, вот результаты Google Sheets ArrayFormula.
Бесконечные диапазоны в формуле массива в Google Sheets и ExcelФормула массива Google Sheets поддерживает бесконечные/открытые диапазоны, такие как A:A или A10:A.
Пример:
Формула в ячейке F2 может снизить производительность Таблицы.
Кроме того, нежелательные значения будут помещены в пустые строки.
Чтобы разобраться в этом, при использовании бесконечных диапазонов рекомендуется использовать комбинацию IF + LEN вместе с формулой массива, как показано ниже.
=Формуламассива(if(len(A2:A),(D2:D*E2:E)))
Это может не работать в массиве Excel.
Формулы массива с одной ячейкойНекоторые формулы массива не расширяют свой результат.
Когда вы суммируете два столбца и умножаете их с помощью одной формулы, вы можете использовать формулу массива одной ячейки.
Google Таблицы: — Введите формулу в ячейку D2.
Excel:- Введите формулу в ячейку D2 и нажмите Ctrl+Shift+Enter.
Это все, что касается использования формул массива в Google Sheets и Excel.
Альтернативы функции ArrayFormula в Google SheetsВ Google Sheets функция ArrayFormula не является обязательной для обработки результатов расширения массива! Да! Вы меня правильно поняли.
Я могу объяснить это на примере.
Следующая формула вернет номера столбцов от 1 до 10 по горизонтали.
=ArrayFormula(column(A1:J1))
Здесь вы можете заменить ArrayFromula функцией Index, как показано ниже.
=ИНДЕКС(столбец(A1:J1))Заключение
В разработке находится несколько руководств, в которых я собираюсь сравнить некоторые популярные формулы Excel и Google Sheets.
Это как Excel Vs. Сравнение формул Google Таблиц.
Я не мог найти такого усилия до сих пор ни у кого.
Этот урок — только начало в этом направлении.
Формула массива в Excel | MyExcelOnline
Объяснение формул массива Excel
Формула массива выполняет операцию Excel (математическую, сравнительную, объединение или аргумент функции) над массивом или диапазоном данных. Это может быть диапазон ячеек, ссылка на рабочий лист или определенное имя.
Массив содержит более одной ячейки, поэтому формулу массива нельзя применить к одной ячейке.
Чтобы превратить вашу формулу в формулу массива, вам нужно будет нажать CTRL+SHIFT+ENTER , что поместит волнистые скобки {} вне формулы:
{=MAX(D13:D16–C13:C16)}
Разберем различные операции Excel, которые можно использовать для создания формулы массива:
МАТЕМАТИКА: +-*/()
СРАВНЕНИЕ: =
СОЕДИНЕНИЕ: &
Давайте рассмотрим пример формулы массива, которая рассчитывает максимальное изменение стоимости акций за четырехдневный период:
СКАЧАТЬ EXCEL WORKBOOK
ШАГ 1: Введите формулу MAX ST EP =MAX(4 2: Вычесть один массив/диапазон данных из другого массива/диапазона данных =MAX(D13:D16–C13:C16)
ШАГ 3: Вместо нажатия клавиши ВВОД для вычисления формулы вам нужно нажать CTRL+SHIFT+ВВОД , чтобы превратить формулу в формулу массива, которая будет выглядеть следующим образом: {=MAX(D13:D16–C13:C16)}
ШАГ 4: К нажатие F9 на выбранном массиве формул даст вам результирующий массив чисел (нажмите CTRL+Z, чтобы выйти из этого режима, когда вы закончите проверку результатов формулы):
столбец, который вычитает ячейки Open & Close, а затем введите формулу MAX для ссылки на эти результаты. Это двойная работа!
Вычисление общего объема продаж с помощью формулы массива
Формула массива выполняет операцию Excel (математическую, сравнительную, объединение или аргумент функции) над массивом или диапазоном данных. Это может быть диапазон ячеек, ссылка на рабочий лист или определенное имя.
Одним из самых популярных применений формулы массива является расчет общего объема продаж.
Если бы нам нужно было рассчитать общий объем продаж обычным способом, нам пришлось бы создать «вспомогательный столбец» для столбца «Итоги», а затем ввести формулу для суммирования всех итогов.
Используя формулу массива, мы пропускаем «вспомогательный столбец» и просто вводим только одну формулу. Быстро и просто!
Давайте сделаем пример формулы массива, которая рассчитывает общие продажи для различных продуктов:
Скачать Excel Workbook
Шаг 1: Введите сумму формулы = Сумма (
Шаг 2: . массив/диапазон данных для ПРОДАНО ЕДИНИЦ, введите знак множителя * и выберите массив/диапазон данных для ЦЕНЫ ЕДИНИЦЫ:
=СУММ(C13:C16*D13:D16)
ШАГ 3: Вместо нажатия клавиши ВВОД для вычисления формулы вам нужно нажать CTRL+SHIFT+ВВОД , чтобы преобразовать формулу в формулу массива, которая будет выглядеть так:
{=СУММ(C13:C16*D13:D16)}
ШАГ 4: По нажатие F9 на выбранном массиве формул даст вам результирующий массив чисел (нажмите CTRL+ Z, чтобы выйти из этого режима после проверки результатов формулы):
Если бы нам нужно было получить приведенный выше результат с помощью формулы, отличной от массива, нам пришлось бы создать вспомогательный столбец, который умножает ПРОДАННЫЕ ЕДИНИЦЫ на ЦЕНУ ЗА ЕДИНИЦУ, а затем ввел формулу СУММ, чтобы получить тот же результат. Это двойная работа!
Общий бонус к выплате по формуле поиска в массиве
Функция ПРОСМОТР имеет три аргумента, она похожа на упрощенную функцию ВПР:
Что она делает?
Ищет значение (lookup_value) в одном диапазоне (lookup_vector) и возвращает значение из той же позиции во втором диапазоне (result_vector)
Разбивка формулы:
=ПРОСМОТР(искомое_значение, искомый_вектор, результирующий_вектор)
=ПРОСМОТР(Значение, которое вы хотите найти, Диапазон, который содержит значения для поиска, Диапазон который содержит возвращаемые значения)
** Обратите внимание, что функция ПРОСМОТР автоматически использует аргумент Approximate Match (аргумент TRUE в функции ВПР), поэтому результирующий_вектор должен быть отсортирован в порядке возрастания **
Прочтите этот блог, в котором объясняется функция ВПР с использованием аргумента «Приблизительно» или «ИСТИНА».

Допустим, вы хотите рассчитать общую сумму бонусов, причитающихся торговым представителям. Это можно сделать с помощью формулы СУММ(ПРОСМОТР()), а затем нажать CTRL+ SHIFT+ВВОД, чтобы превратить его в формулу массива.
Вам нужно будет настроить данные в три отдельных столбца: один для значений поиска (продажи, совершенные торговым представителем), другой для диапазона поиска
Скачать Excel Workbook
Шаг 1: Введите функцию суммы = Сумма (
Шаг 2: NET.