Учимся применять формулы массивов в Excel
Содержание
В MS Excel предусмотрена возможность работы с различными массивами. Под массивом понимается набор данных, объединенных в группу. В зависимости от расположения в документе это может быть вертикальный либо горизонтальный набор данных, а также их сочетание. В данном материале определим, для чего нужны формулы массива и рассмотрим, как ими пользоваться на примере самых распространенных вычислительных операций.
Чем формулы массивов отличаются от обычных
Принципиальная разница заключается в том, что формула массива позволяет обрабатывать несколько значений одновременно, тогда как большинство обычных функций работают только с одной ячейкой. Можно представить массив в Excel как строку, столбец или фрагмент таблицы с данными.
В зависимости от возвращаемого результата формулы массива можно разделить на 2 категории. К первой относятся те, которые возвращают одно значение. Ко второй группе относят функции, которые обрабатывают массив и возвращают новый массив в качестве результата.
В программе Эксель массивы обозначаются с помощью фигурных скобок. В качестве примера приведем небольшой набор чисел — {2; 4; 6; 8; 10}. Это простой одномерный массив, который может использоваться при оформлении функций в программе. Аналогичный набор данных можно представить иным способом — как совокупность ячеек. В данном случае будет использовано более привычное обозначение — А1:Е1.
Алгоритм обработки данных, записанных в массиве, отличается от обычных ячеек. Однако и общего между ними очень много. Рассмотрим простой пример. В нашей таблице есть 2 одномерных набор данных — это столбцы А и С, в которых записаны числа. Задача состоит в том, чтобы получить сумму чисел в каждой строке.
Можно конечно воспользоваться функцией СУММ и просуммировать каждую ячейку. Однако проще воспользоваться функцией, которая примет на входе в качестве аргумента адреса целой группы чисел и вернет результаты, также в виде массива. На практике такая вычислительная функция будет выглядеть следующим образом.
Важно запомнить, что, используя формулы массива, после ввода аргументов нужно нажимать не , а комбинацию + + . В ином случае функция может вернуть некорректный результат. В случае с простыми математическими операциями это случается редко, однако если производятся более сложные вычисления, вероятность ошибки очень высокая, так как программа считает значения по иному алгоритму.
После выполнения функции мы получим результат сложения первых чисел в наборах. Однако на самом деле программа создала целый набор сумм для каждой строки. Достаточно воспользоваться маркером автозаполнения и протянуть клетку с первым результатом вниз.
Обратите внимание на то, что в строке формул запись заключена в фигурные скобки. Это происходит автоматически при использовании комбинации + + . При получении этой команды, программа автоматически интерпретирует входящий набор, как массив данных.
Примеры обработки массивов в Excel
Мы выяснили выше, что такое формула массива и в чем заключаются ее отличия от обычных функций в Эксель. Далее рассмотрим примеры с наиболее распространенными операциями, которые выполняются с одномерными или двухмерными наборами данных.
Пример №1: формируем товарный чек
Очень простой пример из повседневной жизни. Товарный чек можно рассматривать в качестве документа, в котором записаны данные о стоимости и количестве приобретенных товаров. Общая сумма покупки является результатом сложения, умножения, деления и вычитания — основных арифметических функций.
В нашем случае, чтобы найти общую стоимость покупки, необходимо стоимость каждого товара умножить на их количество и просуммировать результат. Это можно сделать при помощи обычных функций Excel. Однако формула массива позволит сделать это намного быстрее и точнее.
Вводим в ячейку для записи результата формулу «СУММ». В качестве аргументов указываем конструкцию с функцией произведения значений из столбца «Кол-во» на значения из столбца «Стоимость».
Обе колонки — это массивы, которые мы перемножаем, а результаты складываем для получения общей стоимости. Чтобы правильно ввести формулу в ячейку, необходимо помнить, что для ее выполнения используется комбинация + + . В результате этого получаем необходимое число — сумму приобретенных товаров.
Пример №2: транспонирование таблицы
Практически каждый пользователь Excel хотя бы раз сталкивался с необходимостью поменять местами столбцы и строки. Для этих целей существует отдельная формула массива — ТРАНСП. С ее помощью можно быстро выполнить перемещение строк и столбцов для изменения их мест расположения. Как это сделать, используя формулу массива, рассмотрим далее.
- В нашем примере есть таблица с числами. В каждом столбце цифры одинаковые. Задача состоит в том, чтобы поменять колонки и строки местами. Выделяем диапазон, в которой будет записан новый, обращенный набор данных и применяем формулу массива. В нашем примере исходная таблица была 3×4, а новая, соответственно будет 4×3 ячейки.
Выделяем ячейки для записи результата - Вводим формулу «ТРАНСП». В качестве аргумента указываем ссылку на текущий набор данных. Затем нажимаем сочетание клавиш для обработки — + + .
Применяем формулу массива - Примененная формула массива обратит исходную таблицу. В результате чего мы получим ее перевернутый вариант, в котором значения из столбцов будут записаны в виде строк.
Результат выполнения
Отметим, что набор данных, полученный в результате применения формулы массивов в таблице Excel, неизменяем. При попытке отредактировать любое значение, на экране появится предупреждение.
В данном случае существует несколько вариантов решения. Первое — оставить результат применения формулы массива неизмененным. Второй вариант — сохраняем полученные числа как значение, а не как результат выполнения функции. Для этого выделяем весь новый массив, нажимаем правую кнопку мыши и выбираем команду «Скопировать». После этого, не убирая выделение, в поле «Параметры вставки» нажимаем пункт «Значения». Данная команда в меню обозначается пиктограммой с цифрами 123 в правом углу.
Пример 3: создание таблицы умножения
Очень хороший пример, который иллюстрирует, как можно использовать формулы массива в Excel для получения нового набора данных в результате выполнения определенной арифметической операции. В данном случае рассматривается вариант умножения. Таблица умножения оформляется следующим образом:
- Вводим цифры от 1 до 10 по вертикали и по горизонтали. Это множители, которые будут подвергаться арифметическому действию для определения произведения.
Прописываем множители - Нетрудно догадаться, что горизонтальный и вертикальный набор множителей — это массивы. Теперь нужно найти каждое значение, которое будет являться результатом умножения этих множителей. Для этого может использоваться простая формула массива в программе Excel. Выделяем незаполненную область таблицы и вводим формулу умножения двух диапазонов. Затем жмем уже привычную комбинацию + + .
Применяем формулу умножения массивов - В результате этого мы получаем полностью заполненную таблицу умножения.
Готовая таблица
Есть множество других способов применения подобного алгоритма. Зная специфику работы с формулами, можно создать подобные таблицы с любыми другими математическими операциями. Например, можно сделать таблицу с числами, возведенными в степень.
Пример 4: умножение матриц
Матрица представляет собой двухмерный массив, то есть набор данных, состоящий из нескольких рядов. Однако из распространенных математических задач — умножение двух матриц между собой. Отметим, что в соответствии с действующими правилами, перемножить две матрицы можно только в том случае, если в массиве первом количество строк соответствует числу столбцов во втором.
Для перемножения матриц есть специальная функция — МУМНОЖ. При выполнении данной формулы создается новый набор чисел, в каждой ячейке которого содержится значение, полученное в результате умножения элементов исходных наборов чисел.
Выделяем диапазон, ширина которого соответствует числу строк первой матрицы, а высота — количеству столбцов второй. Далее вводим формулу МУМНОЖ, где в качестве аргументов указываем ссылки на исходные наборы.
Нажимаем известную нам комбинацию для запуска операции и в результате этого получаем новую матрицу. Каждое значение в ячейке является результатом умножения чисел исходных массивов.
Иногда работа с массивами сопровождается необходимостью выполнить процедуру их удаления. Отсюда возникает закономерный вопрос — как удалить массив в таблице? Здесь нужно понимать, что один элемент матрицы не удаляется. При попытке выполнения такого действия отображается предупреждение, о котором уже шла речь ранее в этом материале. В этом случае можно только очистить все поле с результатом. Для этого выделяем таблицу и жмем «Очистить все», которая располагается среди инструментов вкладки «Главная».
Пример 5: выборка данных
Используя формулы массива, можно осуществлять поиск значений, соответствующих определенным условиям. По сути это аналог функций ВПР и ГПР, которые предназначены для вертикального и горизонтального поиска в таблице.
Рассмотрим метод применения таких операций на простом примере. В нашей таблице содержатся данные о количество проданного товара тем или иным менеджером. Задача состоит в том, чтобы посчитать, сколько кг товара с наименованием «Картофель» продал менеджер по имени «Галина». В данном случае название товара и имя менеджера являются двумя условиями для поиска.
Для осуществления выборки с учетом заданных параметров, вы будете использовать формулу массива с несколькими аргументами. Первое, что нужно сделать — ввести функцию СУММ, так как все значения, которые удовлетворяют заданные условия, должны суммироваться.
Далее задача состоит в том, чтобы прописать нужные аргументы. Здесь все довольно просто. Сначала указываем диапазон для поиска по параметру «Имя менеджера». Далее аналогичным образом делаем проверку по параметру «Наименование товара». Если совпадение будет найдено, в памяти автоматически создается единица, которую будет умножена на значение из столбца «Продано кг». Выглядит такая формула следующим образом.
В результате выполнения мы узнаем, сколько кг картофеля продал менеджер с именем Галина. Преимущество данного способа заключается в том, что он позволяет находить значения даже в очень больших таблицах. Причем количество возможных условий для выборки практически не ограничивается.
Подводим итоги
В данном материале мы выяснили, что такое формула массива, для чего она создавалась и как ее можно применять на практике. С их помощью можно выполнять различные арифметические операции с целыми группами данных. Такие формулы можно использовать как для сложных вычислений с большим количество переменных, так и для выборки данных из исходной таблицы. Вариантов применения этих функций очень много, а их возможности ничем не уступают обычным формулам для операций с ячейками в Excel.
Загрузка...
