Как работать с массивами функций в Excel

Автор: | 31.05.2022

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

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

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

В самом широком смысле этого термина массив I представляет собой набор данных. В нашем конкретном случае это подразумевает набор функций.

В принципе, любую таблицу или диапазон можно считать массивом.

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

Виды массивов функций в Excel

Как правило, в Excel могут быть перечислены следующие типы массивов функций:

  1. Одномерный. Характерной их особенностью является то, что они включают в себя только ячейки из одной строки или столбца.
  2. Двумерные массивы могут содержать большое количество ячеек как внутри ячеек или столбцов одного листа, так и на нескольких листах.

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

 

Как работать с массивами функций в Excel

 

В свою очередь, одномерные массивы можно разделить на горизонтальные и вертикальные. Первый включает ячейки в строке, а второй — столбцы.

Формулы массива позволяют обрабатывать информацию из самых разных данных.

Важно то, что этот тип формулы работает не с отдельными значениями, а со всеми ими в одном наборе.

Синтаксис формулы массива

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

Во-первых, нам нужно выбрать диапазон, к которому в дальнейшем будет применяться формула. В нашем случае это набор ячеек, начинающихся с Е3 и заканчивающихся на Е8.

Затем ставим курсор в строку формул и набираем там =C3:C8*D3:D8.

 

Как работать с массивами функций в Excel

 

Преобразуйте формулу в формат массива. Нужно нажать Ctrl+Shift+Enter.

После этого получаем таблицу с готовыми промежуточными итогами.

 

Как работать с массивами функций в Excel

 

Как видите, после выполнения этих операций формула была одета в фигурные скобки. Именно они говорят, что эта функция будет обрабатываться как формула массива. Мы видим, что каждая ячейка в каждой строке стала конечным результатом. 

Важно! Изменить написанное формулой массива невозможно. Не беспокойся об этом. Вся информация надежно защищена.

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

 

Как работать с массивами функций в Excel

 

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

  1. Выберите ячейку, которая будет содержать результат вычисления по формуле массива.
  2. Там мы вводим формулу. В нашем случае мы будем складывать значения в целевом диапазоне, поэтому используем формулу СУММ(C3:C8*D3:D8).
  3. С помощью комбинации Ctrl+Shift+Enter мы преобразовываем стандартную формулу в такую, которая работает с массивами данных.

В итоге получаем следующий результат.

 

Как работать с массивами функций в Excel

 

Если бы мы не знали, как правильно использовать формулы массива, нам пришлось бы использовать две обычные. И поэтому избавляем себя от этой потребности. 

Рассмотрим подробнее синтаксис этой формулы. 

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

Компоненты формул массива:

  1. Функция массива Это описание операции, которую должен выполнять Excel. 
  2. Ранговые массивы. Это непосредственно те диапазоны, которые будут обрабатываться формулой. 
  3. Матричным оператором является знак двоеточия. Указывает расстояние в определенном количестве ячеек между конкретными адресами. 

Чтобы было легче понять, вот небольшая картинка.

 

Как работать с массивами функций в Excel

 

Анализ данных с помощью формулы массива

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

 

Как работать с массивами функций в Excel

 

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

Запишем в соответствующие ячейки информацию, которая послужит критериями для оценки эффективности. В нашем случае это идентификатор продукта и администратора, который мы рассматриваем.

 

Как работать с массивами функций в Excel

 

Для успешного решения этой задачи достаточно использовать формулу небольшого массива.

Наша конечная цель — получить сумму, поэтому нам нужно использовать соответствующую функцию. Дополнительно нам нужно применить условие, по которому будет проверяться диапазон.

Результатом является формула, показанная на скриншоте.

 

Как работать с массивами функций в Excel

 

Используйте 3 множителя. Первыми проверяют менеджеров. Второе – это активы. И третья — напрямую определяет сумму заказа.

Как работает эта формула? Да, очень просто. Его алгоритм следующий:

  1. Сначала Excel проверяет всех менеджеров и продукты на соответствие заданным критериям. В этом случае функция возвращает 1, иначе 0.

     

    Как работать с массивами функций в Excel

     

  2. Если значения не соответствуют хотя бы одному из условий, один из факторов становится равным нулю. Следовательно, конечный результат будет аналогичным.
  3. Если выполняются оба условия, то функция выполняет операцию умножения.
  4. Наконец, все полученные значения суммируются, после чего в ячейке выдается общая эффективность по обработанным заказам. 

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

Пример: товарный чек

А теперь начнем более подробно рассматривать особенности работы с формулами массива на конкретных примерах. Есть много типов задач, где его использование является обязательным. Самый простой из них – это обычный товарный чек.

Допустим, у нас есть набор продуктов, которые были проданы покупателю. Мы знаем его цену и количество, которое успеем продать, наша задача — рассчитать общую стоимость каждого из базовых предметов.

 

Как работать с массивами функций в Excel

 

Как бы вы решили эту задачу, не используя формулу массива? Во-первых, нам нужно рассчитать, сколько всего товаров было продано. Для этого необходимо количество умножить на цену.

 

Как работать с массивами функций в Excel

 

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

 

Как работать с массивами функций в Excel

 

А чтобы сделать то же самое с формулой массива, вам просто нужно указать ее. 

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

 

Как работать с массивами функций в Excel

 

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

 

Как работать с массивами функций в Excel

 

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

Если вы посмотрите на строку формул, то увидите квадратные скобки, показывающие, что человек работает с формулой массива.

 

Как работать с массивами функций в Excel

 

Важно! Эти скобки не являются текстом. Их нельзя ввести вручную. Если вам нужно отредактировать формулу, в конце ввода нужно снова нажать комбинацию Ctrl+Shift+Enter вместо простого нажатия клавиши Enter».

Пример: меняем местами строки и столбцы

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

Нам нужно выбрать диапазон данных, в котором будет размещена готовая таблица. Так как в нашем примере 8 строк и 2 столбца, то соответственно надо выделить наоборот 8 столбцов и 2 строки. 

После этого необходимо ввести формулу =ТРАНСП, указав A1:B8 в качестве аргумента функции.

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

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

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

Сделать это совсем не сложно. Необходимо сначала выделить весь получившийся массив, затем поставить курсор в соответствующую ячейку, а затем внести изменения в формулу. Для подтверждения своих действий не забудьте нажать комбинацию клавиш Ctrl+Shift+Enter. 

Изменение содержимого массива

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

  1. Найдите кнопку «Отмена», расположенную слева от строки формул. Другой вариант — нажать клавишу Esc, которая выполняет ту же операцию. Во всех случаях блокировка будет снята, и все операции можно будет выполнить снова. 
  2. Введите формулу массива еще раз с тем же диапазоном.

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

Функции массивов

Рассмотрим наиболее часто используемые функции массива.

Оператор СУММ

Как и обычная функция SUM, этот оператор складывает значения в диапазоне. Разница лишь в том, что с его помощью вы можете выполнять свои действия в два этапа: сначала выполнить первую операцию над всеми значениями диапазона, а затем сложить полученные результаты. 

Оператор ТРАНСП

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

Оператор МОБР

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

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *