Формулы массива в Excel
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:
Формулы массива в Excel - это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории - те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах...
Пример 1. Классика жанра - товарный чек
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
- выделяем ячейку С7
- вводим с клавиатуры =СУММ(
- выделяем диапазон B2:B5
- вводим знак умножения (звездочка)
- выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:
- чтобы 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:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
- выделяем диапазон B2:K11
- вводим формулу =A2:A11*B1:K1
- жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Ссылки по теме
- Формула массива для извлечения непустых ячеек из диапазона
- Формула массива для извлечения уникальных ячеек из диапазона
- Формула массива для извлечения данных из списка (многоразовый ВПР)
Почему не реализован расчет по принципу формул массива любых формул, а не только тех, которые введены тремя пальцами?
Например, в именах и условном форматировании нет необходимости (возможности) вводить формулы как формулы массива, любая формула расчитывается как формула массива. То же самое можно было бы сделать и с формулами в ячейках.
К примеру, достаточно сложно угадать в каком случае нужен массивный ввод, а в каком нет:
=СУММ({1:2:3}*{1;2;3}) =СУММ(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1)) =СУММПРОИЗВ(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1)) =СУММПРОИЗВ(ТРАНСП(СТРОКА(A1:A3)*СТОЛБЕЦ(A1:C1))) =СУММ(Ч(ИНДЕКС(СТРОКА(A1:A3);))*Ч(ИНДЕКС(СТОЛБЕЦ(A1:C1);)))Будучи введенными как формулы массива все формулы вернут одинаковые значения - 36, без массивного ввода - не все
Можно конечно сослатся на тяжелое наследие Lotus123 или то, что много лет назад вычислительные мощности компьютеров были ограничены и для повышения производительности алгорим расчета обычных формул отличался от массивных.
А как это относится к формулам массива?
Не могли бы Вы, пожалуйста, подсказать.
Немножко только пришлось поправить Вашу формулу, а то она считала пустые ячейки: {=СЧЁТ(ЕСЛИ(B9:Z9<>"";ЕСЛИ(ОСТАТ(B9:Z9;1)=0;1;"")))}.
Ещё немножко пришлось подправить, чтобы считала только если число больше нуля: {=СЧЁТ(ЕСЛИ(B9:Z9>0;ЕСЛИ(B9:Z9<>"";ЕСЛИ(ОСТАТ(B9:Z9;1)=0;1;""))))}
Вопрос:
возникла задача сравнить два массива на кол-во совпадений одного с другим.
Например:
массив №1: собака, кошка, кобыла
массив №2: кобыла, конь, кошка
Ожидаемый результат = 2. (кошка и кобыла)
Одно значение с массивом найти получается: {=(N13=I11:N11)*1}
а вот как заставить стравнить каждый член массива с другим массивом по отдельности? не получается: {=(I13:N13=I11:N11)*1}
Как это можно организовать?
, где - C14:N14 это массив №1
- $C$11 это массив №2 сцеплённый вручную, так как не нашел спобос сцепить N ячеек в одну с помощью формулы
Впринципи, меня бы этот вариант устроил если бы нашел способ сцеплять с разделителем формулами.
=COUNT(MATCH(massiv №1,massiv№2,0)) c Ctrl+Shift+Enter
То чно нужно, Спасибо.
Не могли бы Вы пожалуйста подсказать, где ошибка в моей формуле:
{=СЧЁТ(ПОИСКПОЗ((Лист1!A4:A11730)*(Лист2!A4:A11730);0))}
=СЧЁТ(ПОИСКПОЗ(Лист1!A4:A11730;Лист2!A4:A11730;0))
(also dont insert "{" and "}" symbol manually)
Нужно со столбца А найти совпадение с столбцом С, и при нахождении взять значение из той же строки но столбца D и записать его в столбец B.
То есть по сути нужно пути к категориям заменить их ID-шками.
Такую задачу можно решить формулами Екселя?
=ВПР(A1;C:D;2;0) и скопируйте ее потом вниз до конца таблицы.
На будущее очень рекомендую почитать - сэкономите себе много-много часов
Возникает вопрос по поводу примера 4.
Допустим, что я хочу просуммировать данные по более сложным условиям. Например, я создаю таблицу-список с именем NAME, которая будет включать Anissed Syrup и Boston Crab Meet . Причем список NAME может состоять не строго из 2х наименований, а от 1 до n.
Теперь я хочу найти сумму 1)По списку NAME И 2)По заказчику BOTTM.
Подскажите, пожалуйста, хотя бы 1 вариант. Сложность возникает в том, что непонятно какие функции и операторы следует использовать, когда необходимо проверить совпадение по массиву условий.
На данный момент выхожу из положения так, что создаю дополнительный столбец в таблице и там через ВПР создаю новые сущности(они бы назывались группой товаров, если бы речь шла о примере 4), а дальше уже как описано в вашей ссылке через формулу массива суммирую, но выглядит это не так элегантно, как если бы сразу можно было бы обращаться к списку критериев и проверять на соответствие непосредственно в формуле.
Типа если все удалю оставлю май, формула суммирует только май и наоборот все 12 месяцев.
можно конечно привязывать каждый месяц к массиву, но это долго...
В существующем виде формула перестает работать как только появится первая пустая сточка. спасибо!
{=СУММ(ЕСЛИ(L6:P6=C6;ЕСЛИ(K12:K28=A15;ЕСЛИ(L5:P5>=C5;ЕСЛИ(L5:P5<=D5;L12:P28)))))}Подскажите, пожалуйста как отличающиеся формулы массива 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 формула активна. Как протянуть формулу вниз чтобы и диапазон спускался на строчку вниз?
Не понимаем что дает два минуса перед скобками( один минус - это как раз на нахождение наивысшего значения,а второй зачем?)
Заранее благодарю за помощь!
1. `AN9767:BC9767`: Область, в которой будет происходить поиск (ряд данных на строке 9767 от столбца AN до BC).
2. `--(AN9767:BC9767<>0)`: Преобразует значения TRUE и FALSE в числовые значения (1 и 0), где TRUE означает, что значение в ячейке не равно 0.
3. `ПОИСКПОЗ(1;--(AN9767:BC9767<>0);0)`: Ищет позицию первого вхождения 1 (то есть первого ненулевого значения) в преобразованном массиве.
4. `ИНДЕКС(AN9767:BC9767;1;...)`: Возвращает значение в строке 9767 из столбца, который был найден на предыдущем шаге.
Таким образом, формула находит и возвращает первое ненулевое значение в диапазоне от AN9767 до BC9767.
Двойной минус используется для преобразования логических значений в числовые.
1 Способ: как-то ее транспонировать в двумерный массив и исп формулу влукап (впр);
2 Способ: сделать ссылку непосредственно на массив (если это можно сделать).
Буду рад любому способу решения данной задачи! Спасибо!
Помогите пожалуйста решить такой вопрос.
Есть таблица допускаемых напряжений для сталей при разных температурах:
Нужно, чтобы при выборе любой из этих сталей (выпадающий список) и при промежуточной температуре (н-р, 70 С) считалось значение допускаемого напряжения (линейной интерполяцией).
Возможно ли вообще сделать такое в массиве и как???
Подскажите плиз, очень нужно!
Буду признательна за помощь!
Я новичок в углубленном EXCELE и только начинаю его постигать....в данном случае не могу справиться со след. задачей:
как мне из списка автомобилей колонка А (А4:A83) по определенной цене колонка В ( В4:В83) с определенным количеством каждой марки (колонка С4:C83) выбрать КОЛИЧЕСТВО АВТОМОБИЛЕЙ ПО ЦЕНЕ НИЖЕ СРЕДНЕЙ, средняя 389 542 р, какая формула в данном случае используется и как правильно она записывается? помогите разобраться....)))
Или =СУММЕСЛИМН(C4:C83;A4:A83;ссылка на ячейку с маркой автомобиля;B4:B83;"<"&ссылка на ячейку со средней ценой)
Если просто общее количество автомобилей с ценой ниже средней, то =СУММЕСЛИ(B4:B83;"<"&ссылка на ячейку со средней ценой;C4:C83)
Это если без формул массива. Ссылка на ячейку - то есть A85, к примеру
Где C2:BM2=B7[img]D:\Акты[/img] это день недели. День недели высчитывается по формуле "=ДЕНЬНЕД(C3)", т.к. вся таблица завязана на снятии данных с базы 1С с отчета где выводится только дата в формате д.м.г.. В итоге столкнулся со сложностью, массив не распознает дату недели с формулы, только с текстового формата (т.е. если просто набираю текстом). Заменять все ячейки не вариант, т.к. таблица будет использоваться другими людьми с некой периодичностью и вопрос именно в оперативности заполнения...т.е. таблица будет неким шаблоном для вставки данных и расчета показателей.
Вот прописал я себе для удобства формулы, они идентичны, просто исходные данные лежат в разных закладках, но есть одно НО, первая формула ВЕРНО работает как обычная формула, а вторая только с применением массива
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. Первый вопрос не столь важный как этот: Можно ли применить ко всем формулам МАССИВ и жить себе счастливо, зная о том, что подсчет первой формулы, как ОБЫЧНАЯ формула и как формула МАССИВА на будущее, при добавлении строк в таблицах ВСЕГДА БУДЕТ РАБОТАТЬ ВЕРНО. т.е. ЕСЛИ ОБЫЧНАЯ формула верно подсчитывает, можно ли применить к ней формулу МАССИВА?
в первом случае ОБЫЧНАЯ формула и МАССИВ дают одинаковую цифру, во втором разнятся, но массив вернее
Просьба, подсказать, возможна ли реализация нижеописанного вопроса ОДНОЙ формулой стандартными функциями 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)
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 поэтому больше подходит.
Как быть?
Скажите пожалуйста, как во всех последующих рядах всегда использовать в этой формуле значения только из В1:G1, а не переносить значения с предыдущей строки?
Спасибо, проблема решена: нужный диапазон ячеек выделить знаком $
=ArrayFormula(СУММ($B$3:$G$3*B4:G4))
Есть таблица с отдельными символами "/", "-", "=" и др.
И есть диапазон ячеек с текстом в каждой ячейке. К примеру, "Пет=я", "Сал/ют" и др.
Требуется проверить содержит ли каждая ячейка хотя бы один символ из указанных.
Можно ли реализовать это через формулу массива?
{=СУММ((ДЕНЬНЕД($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))}Столкнулся со следующей проблемой - прописал в таблице формулу массива в ячейке выделив диапазон, куда надо ввести формулу, нажал ctrl+shift+enter, формула ввелась. Однако!:
В ячейке C2 пописана формула как
{=IFERROR(IF(NUMBERVALUE(RIGHT(Sheet1!C$1;1))<=Sheet1!$B2;INDEX('Product Data'!$A:$O;SMALL(IF(Sheet1!$A2='Product Data'!$A:$A;ROW('Product Data'!$A:$A));RIGHT(Sheet1!C$1;1));MATCH(LEFT(C$1;LEN(C$1)-1);'Product Data'!$1:$1;0));"";);"";)}
Подразумевается, что в D2, например, формула должна измениться на:
{=IFERROR(IF(NUMBERVALUE(RIGHT(Sheet1!D$1;1))<=Sheet1!$B2;INDEX('Product Data'!$A:$O;SMALL(IF(Sheet1!$A2='Product Data'!$A:$A;ROW('Product Data'!$A:$A));RIGHT(Sheet1!D$1;1));MATCH(LEFT(D$1;LEN(D$1)-1);'Product Data'!$1:$1;0));"";);"";)}
Однако такого изменения не происходит, и во все ячейках ссылка идет на C$1 (и в столбце D, и E, и тд)
Office 365
Прошу подсказать, где я идиот, что у меня в выделенном диапазоне при вводе массива не меняются динамические части адресов?
С уважением,
Сергей
как-то можно так сделать, используя Вашу таблицу на самом верху (с продажами по годам и месяцам),
чтобы в качестве ответа на формулу показало месяц, за который по всем годам максимальная сумма?
Или, например, год, в который была максимальная сумма?
То есть нужно не просто максимум посчитать, а именно выявить значение из столбца "месяц" (или строки "год" во втором случае), которые соответствуют максимальному значению.
Можно ли это сделать, используя функции Excel?
Огромное спасибо!
Катерина
комбинация формул макс, поискпоз и индекс:
Попробовал воспроизвести последний пример, но почему то формула срабатывает при одном сочетании (выделено желтым), при другом сочетании переменных, считает неправильно, выделено красным. Где ошибка?
Допустим у меня 2 столбца. Первый столбец текстовое поле, а второй столбец число. И мне нужно чтобы в третьем столбце получился массив чисел по условию первого столбца
И нужен именно массив в 3 столбце
{=СЧЁТ(ЕСЛИ(вид="бег";вид))}
{=СЧЁТ(ЕСЛИ(вид="бег";app))}
{=СЧЁТ((вид="бег")*app)}
и подобные вариации. Не выходит