Работа с массивами в excel полезные функции
Массив функций Excel позволяет решать сложные задачи в автоматическом режиме одновременно. Те, которые выполнить посредством обычных функций невозможно.
Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат. Рассмотрим подробно работу с массивами функций в Excel.
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
- одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
- двумерные (НЕСКОЛЬКО строк и столбцов, матрица).
Одномерные массивы бывают:
- горизонтальными (данные – в строке);
- вертикальными (данные – в столбце).
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
- подсчитать количество знаков в определенном диапазоне;
- суммировать только те числа, которые соответствуют заданному условию;
- суммировать все n-ные значения в определенном диапазоне.
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Синтаксис формулы массива
Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.
- Выделяем диапазон Е3:Е8.
- В строку формул вводим следующую формулу: =C3:C8*D3:D8.
- Нажимаем одновременно клавиши: Ctrl + Shift + Enter. Промежуточные итоги посчитаны:
Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.
Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» – ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:
Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.
- Выделяем ячейку Е9 (напротив «Итого»).
- Вводим формулу вида: =СУММ(C3:C8*D3:D8).
- Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:
Формула массива в данном случае заменила две простые формулы. Это сокращенный вариант, вместивший всю необходимую информацию для решения сложной задачи.
Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.
Рассмотрим ее синтаксис:
Функции работы с массивами Excel
Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.
- Посмотрим, как работает оператор «И» в функции массива. Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
- Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
- Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
- Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: . Получаем:
Скачать примеры массива функций
Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.
Порой пользователю Excel приходится выполнять огромное количество задач. Это занимает немало времени. Чтобы значительно ускорить процесс, были придуманы формулы массива. Они позволяют выполнять очень сложные задачи в автоматическом режиме и одновременно. При использовании обычных формул такое становится невозможным.
Формулы массива же позволяют сразу выполнять огромное количество операций и получать результат мгновенно. Давайте более подробно разберемся, что такое формулы массива (массивы функций) и как их можно использовать для облегчения своей жизни.
Терминология
В широком понимании этого термина я массив – это набор данных. В нашем же конкретном случае здесь подразумевается набор функций.
В принципе, массивом может считаться любая таблица или диапазон.
1
Формула массива из полученных данных возвращает или одно значение, или результат, состоящий из массива в зависимости от типа функции.
Виды массивов функций в Excel
В целом, можно перечислить следующие виды массивов функций в Excel:
- Одномерные. Их характерная особенность заключается в том, что они включают только ячейки одного ряда или колонки.
- Двумерные массивы могут содержать огромное количество ячеек как в пределах ячеек или колонок одного листа, так и нескольких.
Таким образом, массивы функций дают возможность обрабатывать огромное количество данных, состоящих из сотен и даже тысяч ячеек. Причем даже тех, которые находятся за пределами листа.
2
В свою очередь, одномерные массивы можно разделить на горизонтальные и вертикальные. Первые включают ячейки одного ряда, а вторые – колонки.
Формулы массива позволяют обрабатывать информацию из всего разнообразия данных.
Важно то, что формула этого типа работает не с единичными значениями, а с ними всеми в одном комплекте.
Синтаксис формулы массива
Давайте представим, что у нас есть таблица, в которой есть набор ячеек, а сумма к оплате описывается в одной ячейке. Для первого будем искать промежуточные итоги. Во втором же случае будем рассчитывать итоговую сумму.
Для начала нам нужно выделить диапазон, к какому в дальнейшем будет применена формула. В нашем случае это набор ячеек, начинающийся E3, а заканчивающийся E8.
Затем ставим курсор в строку формул, и там записываем =C3:C8*D3:D8.
3
Превращаем формулу в формат массива. Нужно нажать Ctrl + Shift + Enter.
После этого мы получаем таблицу с готовыми промежуточными итогами.
4
Как видим, после совершения этих операций формула облачилась в фигурные скобки. Именно они и говорят о том, что эта функция будет обрабатываться, как формула массива. Видим, что каждая ячейка каждого ряда превратилась в готовый результат.
Важно! Изменить невозможно то, что было записано формулой массива. Не стоит беспокоиться насчет этого. Просто вся информация надежно защищена.
Если попробовать внести какие-угодно изменения в результирующую ячейку, то появится такое уведомление о том, что операция запрещена.
5
Давайте приведем еще один вариант использования формулы массива. На этот раз мы выведем только одно результирующее значение в итоговую ячейку. Чтобы достичь этой цели, необходимо выполнить следующие действия.
- Выделяем ту ячейку, которая будет содержать результат вычисления по формуле массива.
- Вводим туда формулу. В нашем случае мы будем суммировать значения из целевого диапазона, поэтому используем формулу СУММ(C3:C8*D3:D8).
- С использованием комбинации Ctrl + Shift + Enter осуществляем трансформацию стандартной формулы в ту, которая работает с массивами данных.
По итогу, получаем следующий результат.
6
В случае, если бы мы не знали, как правильно использовать формулы массива, нам бы пришлось использовать две обычные. А так мы избавлены от этой необходимости.
Давайте более подробно рассмотрим синтаксис этой формулы.
В нашем случае были использованы одномерные массивы. Формула обрабатывает каждый, после чего выполняет необходимые результаты, а итог выводит в ячейку.
Составные части формул массива:
- Функция массива. Это описание той операции, которую Эксель должен выполнить.
- Массивы диапазона. Это непосредственно те диапазоны, которые будут обрабатываться формулой.
- Оператор массива – знак двоеточия. Обозначает расстояние в определенное количество ячеек между конкретными адресами.
Чтобы было проще понять, вот небольшой рисунок.
7
Анализ данных с помощью формулы массива
Формулы массива очень удобно использовать, если стоит задача анализировать информацию. В частности, можно проверять ее на предмет соответствия определенному критерию. Допустим, у нас есть такая таблица, в которой описываются продажи разными менеджерами. В одном отделе работает три сотрудника, а также продается три вида товаров. Ну и наконец, каждый из них имеет свою цену, а итоговая стоимость записывается в третьей колонке таблицы.
17
Перед нами стоит задача определить, насколько каждый из менеджеров является эффективным, учитывая продаваемые ими товары. Это можно сделать и самостоятельно, но с помощью формулы массива данную задачу можно выполнить буквально в несколько кликов.
Давайте запишем в подходящих ячейках информацию, которая будет служить критерием для оценки эффективности. В нашем случае это товар и идентификатор менеджера, которого мы проверяем.
18
Чтобы успешно решить эту задачу, достаточно просто воспользоваться небольшой формулой массива.
Конечная наша цель – получение суммы, поэтому нужно использовать соответствующую функцию. Кроме этого, нам надо применить условие, на соответствие которым будет проверяться диапазон.
В результате получится формула, приведенная на скриншоте.
19
В ней используется 3 множителя. Первый проверяет менеджеров. Второй – товары. А третий – непосредственно определяет сумму заказа.
Как работает эта формула? Да очень просто. Ее алгоритм следующий:
- Сначала Excel проверяет всех менеджеров и товары, соответствуют ли они заданным критериям. Если да, функция возвращает значение 1, если нет – 0.
20 - В случае несоответствия значений хотя бы одному из условий, один из множителей становится равным нулю. Соответственно, и итоговый результат будет аналогичным.
- Если же оба условия выполняются, то функция осуществляет операцию умножения.
- Наконец, все полученные значения суммируются, после чего ячейка выдает общую эффективность по обработанным заказам.
Вот таким образом можно простую формулу использовать, чтобы выполнять даже самую сложную обработку информации. В конечном итоге, можно несколько модернизировать отчет, добавив выпадающий список, в котором выбираются товары и менеджеры. Но это уже совсем другая тема.
Пример: товарный чек
А теперь давайте приступим к рассмотрению особенностей работы с формулами массива более детально, на конкретных примерах. Существует множество видов задач, в которых ее использование является необходимостью. Наиболее простой среди них является обычный товарный чек.
Допустим у нас есть набор товаров, которые были проданы клиенту. Нам известна их цена и количество, которое получилось реализовать. наша задача – посчитать итоговую стоимость каждой из товарных позиций.
8
Как бы эта задача решалась без использования формулы массива? Сперва нам необходимо было бы посчитать, сколько в сумме было продано товаров. Для этого надо было бы умножить количество на цену.
9
После этого нам нужно было бы получить сумму от всех стоимостей, чтобы получить итоговый результат. Причем нам надо было бы создавать отдельную колонку, чтобы осуществить подсчеты.
10
А чтобы сделать то же самое с помощью формулы массива, достаточно лишь ее указать.
Поскольку перед нами стоит задача рассчитать сумму, то нам и нужно использовать соответствующую функцию. В нашем случае нужно осуществлять суммирование произведений, поэтому нам так и нужно записать. Сначала выбрать нужный диапазон из столбца B, после чего осуществить умножение на соседние ячейки, находящиеся в столбце C.
11
Правда, если все оставить в таком же виде, то будет выдана ошибка после подтверждения ввода формулы с помощью клавиши Enter. Все потому, что наша формула пока еще не является формулой массива. Для этого нажимаем комбинацию Ctrl + Shift + Enter.
12
Почему формула массива оказалась более успешной в выполнении этой задачи? Последовательность действий была довольно сложной. Сначала осуществилось умножение соседних ячеек по горизонтали, а потом было произведено их сложение. Простыми словами, пришлось осуществлять суммирование массива. При этом результат получился один, а дополнительных вычислений не понадобилось.
Если посмотреть на строку формул, то вы увидите, что там показываются фигурные скобки, которые показывают на то, что человек работает с формулой массива.
13
Важно! Эти скобки не являются текстовыми. Их нельзя вводить вручную. Если потребуется редактирование формулы, то в конце ввода опять нужно нажимать комбинацию Ctrl+Shift+Enter вместо простого нажатия клавиши «Ввод».
Пример: меняем местами строки и столбцы
Очень часто может появляться ситуация, когда нужно строки и колонки менять местами. Такая операция называется транспонированием. С помощью массивов функций это делать невероятно легко.
Представим, у нас есть такой двумерный массив, расположенный по вертикали.
14
Нам нужно выделить диапазон данных, в котором будет размещаться готовая таблица. Поскольку в нашем примере 8 рядов и 2 колонки, то соответственно нужно выделять наоборот, 8 колонок и 2 ряда.
После этого надо ввести формулу =ТРАНСП, введя в качестве аргумента функции A1:B8.
15
После этого нажимаем вышеуказанную комбинацию клавиш для создания формулы массива, после чего получаем транспонированную таблицу.
16
Редактирование формулы массива
Насколько мы уже знаем, если понадобится внести изменения в итоговый диапазон, то этого сделать не получится. Эксель выдаст предупреждающее сообщение. Но что же нужно делать, если необходимо отредактировать формулу?
Сделать это вовсе несложно. Для начала нужно выделить весь итоговый массив, после чего поставить курсор на соответствующей ячейке, а потом ввести изменения в формулу. Для подтверждения своих действий не забываем нажимать комбинацию клавиш Ctrl + Shift + Enter.
Изменение содержимого массива
Новички нередко сталкиваются с трудностями в попытках изменить часть массива, потому что каким бы способом они не пытались бы выкрутиться, Эксель все равно показывает сообщение, что это сделать нельзя. Тем не менее, все гениальное просто. В этом случае – также. Достаточно просто выполнить несколько элементарных действий:
- Найти кнопку «Отмена», расположенную слева от строки формул. Еще один вариант – нажатие клавиши Esc, которая выполняет ту же операцию. Во всех случаях блокировка будет снята и все операции можно выполнять заново.
- Снова ввести формулу массива с тем же диапазоном.
Таким образом, изменение содержимого массива возможно лишь если все отменить, а потом заново вводить формулу. Неудобно, конечно, но значительно удобнее, чем использовать стандартные формулы.
Функции массивов
Рассмотрим наиболее часто используемые функции массивов.
Оператор СУММ
Точно так же, как и обычная функция СУММ, этот оператор осуществляет суммирование значений диапазона. Единственное отличие, что с ее помощью можно выполнять свои действия в два этапа: сначала осуществлять первую операцию со всеми значениями диапазона, а потом получившиеся результаты просуммировать между собой.
Оператор ТРАНСП
Детально, как осуществлять транспонирование с помощью функции массива, было рассмотрено выше. Если подвести итоги, эта функция дает возможность создать диапазон, в котором строки и колонки будут поменяны местами. Например, с ее помощью можно создать два варианта таблицы: горизонтальную и вертикальную.
Оператор МОБР
Эта функция дает возможность сделать обратную матрицу. Используется, если в уравнении есть несколько неизвестных.
Оцените качество статьи. Нам важно ваше мнение:
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице 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 умножаются на ноль и суммируются только нужные заказы.
Ссылки по теме
- Формула массива для извлечения непустых ячеек из диапазона
- Формула массива для извлечения уникальных ячеек из диапазона
- Формула массива для извлечения данных из списка (многоразовый ВПР)