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

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

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

arrays0.gif

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

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

arrays1.gif

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

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

    arrays2.gif

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

Вуаля!

arrays3.gif

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

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

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

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

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

arrays6.gif

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

arrays7.gif

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

arrays8.gif

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

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

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

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

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

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

arrays4_1.jpg

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

arrays5.gif

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

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

arrays4.gif

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

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

arrays10.gif

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

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

 



MCH
12.08.2013 13:33:09
Николай, Вы можете пояснить, для чего вообще есть разграничение: формула массива и обычная формула.
Почему не реализован расчет по принципу формул массива любых формул, а не только тех, которые введены тремя пальцами?
Например, в именах и условном форматировании нет необходимости (возможности) вводить формулы как формулы массива, любая формула расчитывается как формула массива. То же самое можно было бы сделать и с формулами в ячейках.

К примеру, достаточно сложно угадать в каком случае нужен массивный ввод, а в каком нет:
=СУММ({1:2:3}*{1;2;3})
=СУММ(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1))
=СУММПРОИЗВ(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1))
=СУММПРОИЗВ(ТРАНСП(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1)))
=СУММ(Ч(ИНДЕКС(СТРОКА(A1:A3);))*Ч(ИНДЕКС(СТОЛБЕЦ(A1:C1);)))


Будучи введенными как формулы массива все формулы вернут одинаковые значения - 36, без массивного ввода - не все

Можно конечно сослатся на тяжелое наследие Lotus123 или то, что много лет назад вычислительные мощности компьютеров были ограничены и для повышения производительности алгорим расчета обычных формул отличался от массивных.
31.08.2013 23:21:09
На больших таблицах формулы массива значительно медленнее обычных формул, разве нет?
05.02.2014 18:02:10
Николай добрый вечер не подскажете почему у меня в Excele объединение ячеек не активна? Спасибо заранее
08.05.2014 10:00:38
Скорее всего включена защита листа либо общий доступ к листу (все это на вкладке Рецензирование).
А как это относится к формулам массива?
07.05.2014 16:29:18
Добрый день Николай, я долго ломал голову, как подсчитать в строке (или в столбце) КОЛИЧЕСТВО ЦЕЛЫХ ЧИСЕЛ (возникла необходимость на работе). Каких только вариантов не пробовал, типа "{=СУММ((B9:Z9>0)*(ОКРУГЛ(B9:Z9;0)-B9:Z9=0))}". Ничего не получается...
Не могли бы Вы, пожалуйста, подсказать.
07.05.2014 19:36:06
Простите мой французский: {=СЧЁТ(ЕСЛИ(ОСТАТ(B9:Z9;1)=0;1;""))}
08.05.2014 08:06:01
Уважаемый Spagnola, Вы не представляете, как меня выручили! Большое Вам спасибо!
Немножко только пришлось поправить Вашу формулу, а то она считала пустые ячейки: {=СЧЁТ(ЕСЛИ(B9:Z9<>"";ЕСЛИ(ОСТАТ(B9:Z9;1)=0;1;"")))}.
Ещё немножко пришлось подправить, чтобы считала только если число больше нуля: {=СЧЁТ(ЕСЛИ(B9:Z9>0;ЕСЛИ(B9:Z9<>"";ЕСЛИ(ОСТАТ(B9:Z9;1)=0;1;""))))}
09.05.2014 18:58:08
Спасибо за статью, доступно!

Вопрос:
возникла задача сравнить два массива на кол-во совпадений одного с другим.

Например:
массив №1: собака, кошка, кобыла
массив №2: кобыла, конь, кошка
Ожидаемый результат = 2. (кошка и кобыла)

Одно значение с массивом найти получается: {=(N13=I11:N11)*1}
а вот как заставить стравнить каждый член массива с другим массивом по отдельности? не получается: {=(I13:N13=I11:N11)*1}

Как это можно организовать?
10.05.2014 01:44:23
За это время получилось добится ожидаемого результата только с условием что есть только один массив, а другой нужно сцепить в одну ячейку:
=SUM(IFERROR(IF(FIND(C14:N14,($C$11)),1*(C14:N14<>""),0),0))

, где - C14:N14 это массив №1
  - $C$11 это массив №2 сцеплённый вручную, так как не нашел спобос сцепить N ячеек в одну с помощью формулы

Впринципи, меня бы этот вариант устроил если бы нашел способ сцеплять с разделителем формулами.
03.07.2014 00:19:21
так?
=COUNT(MATCH(massiv №1,massiv№2,0)) c Ctrl+Shift+Enter
03.07.2014 00:29:27
Гениально!! :)

То чно нужно, Спасибо.
19.12.2014 12:31:57
Уважаемый Useful, туплю и не могу разобраться с Вашей формулой.
Не могли бы Вы пожалуйста подсказать, где ошибка в моей формуле:
{=СЧЁТ(ПОИСКПОЗ((Лист1!A4:A11730)*(Лист2!A4:A11730);0))}
19.12.2014 14:00:13
with CSE
=СЧЁТ(ПОИСКПОЗ(Лист1!A4:A11730;Лист2!A4:A11730;0))

(also dont insert "{" and "}" symbol manually)
24.12.2014 15:59:31
Спасибо !
05.10.2014 16:16:41
Есть такая задача:

Нужно со столбца А найти совпадение с столбцом С, и при нахождении взять значение из той же строки но столбца D и записать его в столбец B.
То есть по сути нужно пути к категориям заменить их ID-шками.
Такую задачу можно решить формулами Екселя?
05.11.2014 03:41:54
По-моему, это решает функция ВПР
05.11.2014 13:24:29
Введите в ячейку В1 формулу
=ВПР(A1;C:D;2;0) и скопируйте ее потом вниз до конца таблицы.

На будущее очень рекомендую почитать статью про функцию ВПР - сэкономите себе много-много часов :)
13.11.2014 15:45:25
Здравствуйте!

Возникает вопрос по поводу примера 4.
Допустим, что я хочу просуммировать данные по более сложным условиям. Например, я создаю таблицу-список с именем NAME, которая будет включать Anissed Syrup и Boston Crab Meet . Причем список NAME может состоять не строго из 2х наименований, а от 1 до n.
Теперь я хочу найти сумму 1)По списку NAME И 2)По заказчику BOTTM.
Подскажите, пожалуйста, хотя бы 1 вариант. Сложность возникает в том, что непонятно какие функции и операторы следует использовать, когда необходимо проверить совпадение по массиву условий.
15.11.2014 17:27:10
Лиза, посмотрите статью про выборочный подсчет по одному или нескольким условиям с помощью функции СУММЕСЛИМН
17.11.2014 09:37:37
Николай, если я ничего не упустила, то в примерах приведены способы использования суммеслимн, когда условие содержит только 1 критерий. Также с формулой массива. Мне же нужно, чтобы в условии стоял не один критерий, а список критериев. Вариант с вложением функции ИЛИ не устраивает, поскольку возникает необходимость менять списки критериев.

На данный момент выхожу из положения так, что создаю дополнительный столбец в таблице и там через ВПР создаю новые сущности(они бы назывались группой товаров, если бы речь шла о примере 4), а дальше уже как описано в вашей ссылке через формулу массива суммирую, но выглядит это не так элегантно, как если бы сразу можно было бы обращаться к списку критериев и проверять на соответствие непосредственно в формуле.
17.06.2015 15:04:01
Подскажите, как сцепить два массива. использую СУММПРОИЗ, хочу задать туда условие чтобы у меня суммировались месяца, заданные в другом массиве. Вроде как - столбец АА= равнялся массиву ниже.
ЯнварьфевральМартАпрельМайИюньИюльАвгустСентябрьОктябрьНоябрь
и если я буду удалять месяц или добавлять все будет пересчитываться.
Типа если все удалю оставлю май, формула суммирует только май и наоборот все 12 месяцев.
можно конечно привязывать каждый месяц к массиву, но это долго...
18.06.2015 16:33:19
В примере 4 в формуле обозначен диапазон с 3 по 21 строчку. А как составить формулу, если у меня строки меняются (прибавляются и убавляются)? Можно ли сделать формулу в общем виде так, чтобы, например нижний диапазон обозначить до строчки 15000 (заведомо большой)?
В существующем виде формула перестает работать как только появится первая пустая сточка. спасибо!
26.11.2015 09:15:33
Как написать формулу вычислений по массиву, если данные находятся на другом листе? Если данные и таблица вычислений на одном, то формула работает, а вот если ссылается на данные диапазона на другом листе, то нет. Подскажите пожалуйста! Спасибо!
{=СУММ(ЕСЛИ(L6:P6=C6;ЕСЛИ(K12:K28=A15;ЕСЛИ(L5:P5>=C5;ЕСЛИ(L5:P5<=D5;L12:P28)))))}
 
07.12.2015 13:26:18
Между делом, таблицу умножения проще сделать формулой =B$1*$A2
07.01.2016 15:14:17
Добрый день.
Подскажите, пожалуйста как отличающиеся формулы массива VLOOKUP3, введенные в строку, можно одновременно вставить в соответствующие строки ниже. Т.е. должны получиться формулы массива по столбцам. Формулы по столбцам отличаются друг от друга только значением SearchValue, и таких столбцов полторы тысячи.
Спасибо.  
Добрый день! Помогите,пожалуйста, решить проблему, есть формула в отчете, от коллеги который уволился.

В диапазоне AN9767:BC9767 (например 0,0,0,0,63,0,0,87,0,0) нужно найти первую цену после нулей. И протянуть эту формулу на несколько строчек вниз.

{=ИНДЕКС(AN9767:BC9767;1;ПОИСКПОЗ(1;--(AN9767:BC9767<>0);0))}

Знаем что она привязана к массиву, через CTRL+SHIFT+ENTER формула активна. Как протянуть формулу вниз чтобы и диапазон спускался на строчку вниз?
Не понимаем что дает два минуса перед скобками( один минус - это как раз на нахождение наивысшего значения,а второй зачем?)

Заранее благодарю за помощь!

 
18.02.2016 07:14:01
Здравствуйте! Вопрос из серии "как сослаться на массив". Есть курс валют, слева в столбец указано число, сверху в строку указан месяц. Выбираем месяц (сверху) и число (слева), и выходим на курс валюты на определенную дату. Как можно подтянуть значение курса валюты из данного массива к определенной дате?
1 Способ: как-то ее транспонировать в двумерный массив и исп формулу влукап (впр);
2 Способ: сделать ссылку непосредственно на массив (если это можно сделать).

Буду рад любому способу решения данной задачи! Спасибо!
04.04.2016 09:19:46
Добрый день, Николай!
Помогите пожалуйста решить такой вопрос.
Есть таблица допускаемых напряжений для сталей при разных температурах:

Трасч, ССт309Г2ССт20Ст1010Г2
20154196147130180
100149177142125160
150145171139122154
200142165136118148

Нужно, чтобы при выборе любой из этих сталей (выпадающий список) и при промежуточной температуре (н-р, 70 С) считалось значение допускаемого напряжения (линейной интерполяцией).
Возможно ли вообще сделать такое в массиве и как???
Подскажите плиз, очень нужно!
Буду признательна за помощь!
10.01.2017 17:39:17
Ссылка - здесь файлик с расчетом. В ячейке B13 можно выбрать из выпадающего списка марки стали, в ячейке C13 - температуру, а расчет будет в D13. Табличка ниже, B16:K16, это просто прикидочные расчеты. Формула массива не требуется.
24.05.2016 15:23:32
Добрый день, Николай!
Я новичок в углубленном EXCELE и только начинаю его постигать....в данном случае не могу справиться со след. задачей:
как мне из списка автомобилей колонка А (А4:A83) по определенной цене колонка В ( В4:В83) с определенным количеством каждой марки (колонка С4:C83) выбрать КОЛИЧЕСТВО АВТОМОБИЛЕЙ ПО ЦЕНЕ НИЖЕ СРЕДНЕЙ, средняя 389 542 р, какая формула в данном случае используется и как правильно она записывается? помогите разобраться....)))
10.01.2017 18:06:43
Для каждой марки автомобиля: =СУММЕСЛИМН(C4:C83;A4:A83;ссылка на ячейку с маркой автомобиля;B4:B83;"<389542")
Или =СУММЕСЛИМН(C4:C83;A4:A83;ссылка на ячейку с маркой автомобиля;B4:B83;"<"&ссылка на ячейку со средней ценой)

Если просто общее количество автомобилей с ценой ниже средней, то =СУММЕСЛИ(B4:B83;"<"&ссылка на ячейку со средней ценой;C4:C83)

Это если без формул массива. Ссылка на ячейку - то есть A85, к примеру
01.06.2016 09:45:42
Доброго времени суток, Николай можете подсказать. Использую формулу массива: =СУММ((C2:BM2=B7)*(C4:BM4=C6)*C5:BM5)
Где C2:BM2=B7[img]D:\Акты[/img] это день недели. День недели высчитывается по формуле "=ДЕНЬНЕД(C3)", т.к. вся таблица завязана на снятии данных с базы 1С с отчета где выводится только дата в формате д.м.г.. В итоге столкнулся со сложностью, массив не распознает дату недели с формулы, только с текстового формата (т.е. если просто набираю текстом). Заменять все ячейки не вариант, т.к. таблица будет использоваться другими людьми с некой периодичностью и вопрос именно в оперативности заполнения...т.е. таблица будет неким шаблоном для вставки данных и расчета показателей.
15.07.2016 08:54:07
Здравствуйте! Вопрос к экспертам.

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

1. =СЧЁТЕСЛИ(‘Ф-2-2016’!D286:D320;»<=93";)+(СЧЁТЕСЛИ('Ф-2-2016'!D323:D327;"<3";)+СЧЁТ(ЕСЛИ('Ф-2-2016'!D323:D327=3;ЕСЛИ('Ф-2-2016'!E323:E327<=305;))))+СЧЁТ('Ф-2-2016'!D330:D331)


2. {=СЧЁТЕСЛИ('Ф-2-2015'!D640:D725;"<=93";)+(СЧЁТЕСЛИ('Ф-2-2015'!D728:D731;"<3";)+СЧЁТ(ЕСЛИ('Ф-2-2015'!D728:D731=3;ЕСЛИ('Ф-2-2015'!E728:E731<=305;))))+СЧЁТ('Ф-2-2015'!D734:D736)}

Собственно сам вопрос, даже два)):
1. Почему так, на одинаковых условиях формулы считают по-разному?
2. Первый вопрос не столь важный как этот: Можно ли применить ко всем формулам МАССИВ и жить себе счастливо, зная о том, что подсчет первой формулы, как ОБЫЧНАЯ формула и как формула МАССИВА на будущее, при добавлении строк в таблицах ВСЕГДА БУДЕТ РАБОТАТЬ ВЕРНО. т.е. ЕСЛИ ОБЫЧНАЯ формула верно подсчитывает, можно ли применить к ней формулу МАССИВА?

в первом случае ОБЫЧНАЯ формула и МАССИВ дают одинаковую цифру, во втором разнятся, но массив вернее
10.01.2017 18:38:24
Первая формула работает правильно только в том случае, если у Вас в диапазоне 'Ф-2-2016'!D323:D327 нет значений, равных 3, а в диапазоне 'Ф-2-2016'!E323:E327 нет значений, меньше или равных 305. В таком случае "кусок" формулы СЧЁТ(ЕСЛИ('Ф-2-2016'!D323:D327=3;ЕСЛИ('Ф-2-2016'!E323:E327<=305;)))) выдаст просто ноль, а не ошибку. А все остальные куски подсчитают сумму. На листе Ф-2-2015 в соответствующих диапазонах есть значения, удовлетворяющие заданным условиям, поэтому при вводе простой формулы, она выдает неправильное значение, т.к. этот "кусок" формулы вместо правильного значения выдает ноль. Функция СЧЁТ выдает ноль, если обрабатывает ошибку типа #ЗНАЧ! в отличие от СУММ или СРЗНАЧ. Эти две функции при обработке #ЗНАЧ! выдадут тоже ошибку.
02.02.2017 14:41:00
Всем доброго дня!
Просьба, подсказать, возможна ли реализация нижеописанного вопроса ОДНОЙ формулой стандартными функциями Excel - на VBA уже сделал.

Имеется 2 массива. Необходимо вычислить среднее значение от корреляций элементов этих массивов. Корреляция элементов рассчитывается следующим образом: сверху вниз из каждого массива берётся по пять элементов и по ним считается корреляция (Excel-функция КОРРЕЛ). Далее одновременно по двум массивам происходит смещение +1 по строке - берутся следующие пять элементов. После расчета все корреляции суммируются и сумма делится на количество корр. - в примере 11, т.е. выполняется стандартный расчет среднего.

массив А массив Б
1 10 0,81 =КОРРЕЛ(A2:A6;B2:B6)
2 5 0,98 =КОРРЕЛ(A3:A7;B3:B7)
3 7 0,97 =КОРРЕЛ(A4:A8;B4:B8)
4 17 0,14 =КОРРЕЛ(A5:A9;B5:B9)
5 28 -0,49 =КОРРЕЛ(A6:A10;B6:B10)
6 31 -0,72 =КОРРЕЛ(A7:A11;B7:B11)
7 55 -0,57 =КОРРЕЛ(A8:A12;B8:B12)
8 11 0,75 =КОРРЕЛ(A9:A13;B9:B13)
9 9 0,87 =КОРРЕЛ(A10:A14;B10:B14)
10 7 0,24 =КОРРЕЛ(A11:A15;B11:B15)
11 21 -0,35 =КОРРЕЛ(A12:A16;B12:B16)
12 21
13 55
14 5
15 7
0,24 =СРЗНАЧ(D2:D12)
06.07.2017 12:43:47
1. Книга1\Лист1\А1 ссылается формулой массива на Книга2\Лист1\А1;

2. Книга2\Лист1\А1 ссылается формулой "=ЕСЛИ(Книга2\Лист2\А2="";"";ТЕКСТ(Книга2\Лист2\А2;0 0,00) на Книга2\Лист2\А2.

3.Книга1\Лист1\А1 возвращает 0, а не нужное значение , которое находится в Книга2\Лист1\А1.

Вопрос:
С чем это может быть связано? Ссылаясь обычной формулой возвращается верное значение, но массив дает возможность получать данные при условии закрытой книги2 поэтому больше подходит.
Как быть?  
08.07.2017 17:58:51

Скажите пожалуйста, как во всех последующих рядах всегда использовать в этой формуле значения только из В1:G1, а не переносить значения с предыдущей строки?

таблица

Спасибо, проблема решена: нужный диапазон ячеек выделить знаком $
=ArrayFormula(СУММ($B$3:$G$3*B4:G4))
11.01.2018 20:53:21
Добрый день!
Есть таблица с отдельными символами "/", "-", "=" и др.
И есть диапазон ячеек с текстом в каждой ячейке. К примеру, "Пет=я", "Сал/ют" и др.
Требуется проверить содержит ли каждая ячейка хотя бы один символ из указанных.
Можно ли реализовать это через формулу массива?
Добрый день! Помогите разобраться. Есть потребность подсчета среднего значения в зависимости от дня недели(не учитывать выходные), пробую так:
{=СУММ((ДЕНЬНЕД($C$2:E$2;2)<>6)*(ДЕНЬНЕД($C$2:E$2;2)<>7)*($C$39:E$39))/ 
    СЧЁТ((ДЕНЬНЕД($C$2:E$2;2)<>6)*(ДЕНЬНЕД($C$2:E$2;2)<>7)*($C$39:E$39))} 
Сумму считает адекватно, а количество – с учетом выходных, когда они появляются в диапазоне, со СРЗНАЧ() – аналогично получается. Помогите пожалуйста обойти проблему.
Сам допёр, если кому-то интересно:
{=СУММ((ДЕНЬНЕД($C$2:E$2;2)<>6)*(ДЕНЬНЕД($C$2:E$2;2)<>7)*($C$39:E$39))/ 
   СУММ((ДЕНЬНЕД($C$2:E$2;2)<>6)*(ДЕНЬНЕД($C$2:E$2;2)<>7))}
 
Наверх