Формула массива в excel как ввести

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

history 11 ноября 2012 г.

Группы статей
• Формулы массива

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

Без формул массива (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 необязательно.

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

Высший пилотаж: массивы в Excel

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

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

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

Например, чтобы посчитать среднее значение чисел в диапазоне А1:А10 , можно использовать функцию СРЗНАЧ(Диапазон)

Среднее значение в Excel

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

Давайте запишем формулу: =СРЗНАЧ(ЕСЛИ(A1:A10=0;»»;A1:A10)) и нажмем Ctrl+Shift+Enter . Такой комбинацией мы сигнализируем Excel, что применяется формула массива. Программа обернет формулу в фигурные скобки, обозначая применение именно формулы массива. Не дописывайте фигурные скобки сами, это не сработает, нужно именно нажать комбинацию клавиш. Как же работает эта формула:

1. Обычно первым аргументом функции «ЕСЛИ» записывается условие. Например, «A1=0», и если значение в ячейке А1 равно нулю, выполняется второй аргумент, в противном случае – третий. Мы же передали функции ссылку не на одну ячейку, а на массив из десяти значений ( А1:А10 ). Функция «ЕСЛИ» проверит каждое из них и все нулевые заменит пустой строкой. Результатом будет массив из десяти чисел или пустых значений. Такой список функция СРЗНАЧ уже отработает правильно;
2. Полученный массив передается в функцию СРЗНАЧ, и теперь она считает среднее значение чисел без учета нулей, т.к. их уже нет.

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

Рассмотрим еще один пример, похожий на предыдущий.

У нас есть ежедневные продажи за 20 дней. Нужно посчитать сумму продаж в те дни, когда касса превысила 5000. В обычных условиях нам понадобится промежуточный расчет. Так, в колонке С мы проверяем, если продажи выше пяти тысяч – возвращаем эту сумму, если ниже – записываем ноль. Далее, в ячейке С23 суммируем все числа столбца С и получаем искомый результат.

Теперь посмотрите, как мы сделали все эти действия одновременно в одной лишь ячейке Е4 и получили такой же результат! Обратите внимание на простую формулу в строке формул:

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

Сумма без учета ошибок

Отличный способ применения формул массивов – сложить числа в массиве, содержащем ошибки. Пусть в массиве В2:F5 у нас результаты вычислений, среди которых встречаются ошибки. Как сложить эти числа, игнорируя ошибки? Очень просто, вот вам формула: =СУММ(ЕСЛИОШИБКА(B2:F5;0)) . Здесь функция ЕСЛИОШИБКА проверит содержание каждой ячейки и заменит ( в памяти компьютера, не на листе ) ошибки на нули. Далее, функция СУММ просуммирует все обработанные значения и выдаст результат без учета ошибок.

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

Эксперты MS Excel выстраивают из подобных формул огромные комбинации, выполняя в одной ячейке столько операций, сколько обычные пользователи умещают на несколько листов. Так что, экспериментируйте, пробуйте разные варианты применения, и не забывайте: для завершения ввода нажимаем Ctr+Shift+Enter , вместо клавиши «Ввод».

Если что-то из темы статьи вам непонятно или не получается, задавайте вопросы в комментариях, будем разбираться вместе!

А в следующей публикации мы с вами будем рисовать диаграммы, а это слишком важная тема, чтобы ее пропустить, подписывайтесь на электронную рассылку и читайте свежие статьи пока горячи!