Формулы массива в excel примеры

Массивы и формулы массива в Excel.

Для начала следует понимать, что такое массив и какие массивы бывают.

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

Формулы (функции) массивов.

Для работы с массивами в «Эксель» предусмотрены специальные формулы – формулы массивов.

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

Для расчета данных в массивах могут применяться и обычные функции. При нажатии клавиш Ctrl + Shift + Enter обычная формула выделяется фигурными скобками и становится формулой массива.

Рассмотрим в качестве примера смету состоящую из столбцов «Количество», «Трудозатраты на единицу», «Стоимость одного чел часа».

Чтобы получить полную стоимость работ следует перемножить количество, стоимость и трудозатраты на единицы для каждого вида работ, а потом сложить затраты на каждый вид работ. Сделать это можно в несколько действий по порядку, а можно написать одну единственную функцию массива: и нажать сочетание клавиш «Ctrl + Shift + Enter», чтобы «Excel» распознал формулу массив.

Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:

• Выделить диапазон, в который планируете транспонировать таблицу (если в исходной таблице четыре столбца и шесть строк, то выделяем шесть столбцов и четыре строки);
• В строке функций пишем =ТРАНСП();
• В скобках указать массив, который вы хотите транспонировать (перевернуть) и нажать клавишу «ENTER».

Формулы массива в EXCEL. Знакомство

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

Без формул массива (array formulas ) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12 , просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо 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) ;
• в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
• нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
• получим <1:4:9:16:25:36:49:64:81:100:121>– тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение .

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки .

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

2. Основы формул массива (на английском) из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ : При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER . Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя , то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

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

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

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

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

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

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

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

Т.е. 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. Таблица умножения

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

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

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

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

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

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

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

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

Если Вы довольно много работаете в 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:A10<>0;A1:A10)) Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
<=МИН(ЕСЛИ(A1:A10<>0;A1:A10))> то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет — 1.

Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП (TRANSPOSE) . Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10 ):

• выделяете диапазон ячеек( D1:M3 ), равный по количеству ячеек исходному диапазону значений( A1:C10 ), которые необходимо транспонировать;
• вписываете функцию ТРАНСП;
• в качестве аргумента указываете ссылку на исходный диапазон значений: =ТРАНСП( A1:C10 ) ;
• завершаете ввод функции сочетанием клавиш Ctrl+Shift+Enter.

В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции( D1:M3 ) должно быть равно числу столбцов в исходном диапазоне( A1:C10 ), а число столбцов — числу строк. Если указать меньше — не все значения будут транспонированы. Если больше — то все лишние ячейки будут заполнены значениями #Н/Д

Какие особенности подобного применения функций массива:

• во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать — так надо;
• ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности — только все вместе. В противном случае просто получите сообщение «Нельзя изменять часть массива!». Бывает очень удобно иногда в целях защиты формул от изменений.

Итак, что самое важное надо запомнить для использования формул массива:

• ввод формулы завершается сочетанием клавиш Ctrl+Shift+Enter;
• Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат

Статья помогла? Поделись ссылкой с друзьями!