При работе с электронными таблицами Excel часто приходится оперировать целыми аспектами данных. При этом некоторые задачи подразумевают, что всю группу ячеек нужно конвертировать буквально одним кликом. В Excel есть инструменты, позволяющие выполнять такие операции.
Операции с массивами
Массив — это группа данных, которая размещается на листе в соседних ячейках. По большому счету любую таблицу можно считать массивом, но не всякая таблица является таблицей, так как она может быть только диапазоном. По существу, такие области могут быть одномерными или двумерными (матрицы). В первом случае все данные размещаются только в одном столбце или строке.
Во втором - в несколько одновременно.
Кроме того, между одномерными массивами различают горизонтальный и вертикальный типы в зависимости от того, являются ли они строкой или столбцом.
Следует отметить, что алгоритм работы с такими областями несколько отличается от более привычных операций с одиночными ячейками, хотя общего между ними тоже много. Давайте рассмотрим нюансы таких операций.
Создание формулы
Формула массива — это выражение, которое обрабатывает диапазон для получения конечного результата, который отображается в виде всего массива или в одной ячейке. Например, чтобы умножить одну площадь на другую, используйте формулу по следующему шаблону:
=массивадрес1*массивадрес2
Вы также можете выполнять сложение, вычитание, деление и другие арифметические операции над диапазонами данных.
Координаты массива выглядят как адреса первой и последней ячейки, разделенные двоеточием. Если диапазон двумерный, первая и последняя ячейки располагаются по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.
А пример двумерного адреса области выглядит так: A2:D7.
- После ввода не нажимайте кнопку Enter, как обычно, а вводите комбинацию клавиш Ctrl+Shift+Enter. После этого выражение в строке формул автоматически будет заключено в фигурные скобки, а ячейки на листе будут заполнены данными, полученными в результате расчета, в пределах всего выбранного диапазона.
Изменение содержимого массива
Если позже вы попытаетесь удалить содержимое или изменить какую-либо из ячеек, расположенных в области, где отображается результат, операция завершится ошибкой. Это также не сработает, если вы попытаетесь отредактировать данные на функциональной панели. Появится информационное сообщение о том, что вы не можете изменить часть матрицы. Это сообщение появляется, даже если вы не собирались вносить какие-либо изменения и случайно дважды щелкнули ячейку в диапазоне.
Если закрыть это сообщение, нажав кнопку «ОК», а затем попытаться переместить курсор мышью или просто нажать кнопку «Ввод», информационное сообщение появится снова. Вы также не сможете закрыть окно программы или сохранить документ. Это надоедливое сообщение будет всплывать все время, блокируя все действия. Но выход из ситуации есть, и он достаточно прост.
- Затем нажмите кнопку «Отмена», которая находится в группе значков слева от строки формул и представляет собой значок в виде креста. Вы также можете нажать кнопку Esc на клавиатуре. После любой из этих операций действие будет отменено, и вы сможете работать с листом, как и раньше.
Но что, если вам действительно нужно удалить или изменить формулу массива? В этом случае выполните следующие действия.
- После внесения изменений набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.
- После этого формула будет удалена со всей области. Теперь в него можно будет вносить все данные.
Функции массивов
В качестве формул удобнее всего использовать готовые встроенные функции Excel. Доступ к ним можно получить через Мастер функций, нажав кнопку «Вставить функцию» слева от строки формул. Либо во вкладке «Формулы» ленты можно выбрать одну из категорий, в которой находится интересующий вас оператор.
После выбора пользователем имени конкретного оператора в мастере функций или на панели инструментов открывается окно аргумента функции, в котором можно ввести исходные данные для расчета.
Правила ввода и редактирования функций, если они выводят результат сразу в несколько ячеек, такие же, как и для обычных формул массива. То есть после ввода значения обязательно нужно поместить курсор в строку формул и набрать комбинацию клавиш Ctrl+Shift+Enter.
Оператор СУММ
Одна из самых востребованных функций в Excel — СУММ. Его можно использовать как для суммирования содержимого отдельных ячеек, так и для нахождения суммы целых матриц. Синтаксис этого оператора для массивов следующий:
=СУММ(массив1;массив2;…)
Этот оператор выводит результат в ячейку, а значит, для выполнения расчета после ввода входных данных достаточно нажать кнопку «ОК» в окне аргумента функции или клавишу «Ввод», если ввод производился вручную.
Оператор ТРАНСП
Функция ТРАНСП — типичный оператор массива. Он позволяет переворачивать таблицы или матрицы, то есть менять местами строки и столбцы. При этом он применяет результат результата исключительно к ряду ячеек, поэтому после введения этого оператора важно использовать комбинацию Ctrl+Shift+Enter. Также следует отметить, что перед вводом самого выражения необходимо выделить область на листе, где количество ячеек в столбце будет равно количеству ячеек в строке исходной таблицы (матрицы) и , и наоборот, количество ячеек в строке должно быть равно количеству ячеек в исходном столбце. Синтаксис оператора следующий:
=ПРОЗРАЧ(массив)
Оператор МОБР
Функция MIN позволяет вычислить обратную матрицу. Все правила ввода значений для этого оператора точно такие же, как и для предыдущего. Но важно знать, что вычисление обратной матрицы возможно только в том случае, если она содержит одинаковое количество строк и столбцов, и если определитель не равен нулю. Если вы используете эту функцию для диапазона с другим количеством строк и столбцов, на выходе будет отображаться значение «#ЗНАЧ!» вместо правильного результата. Синтаксис этой формулы:
=NBR(матрица)
Чтобы вычислить определитель, используйте функцию со следующим синтаксисом:
=MPRED(массив)
Как видите, операции с диапазонами помогают сэкономить время при расчетах, а также свободное место на листе, ведь не нужно суммировать данные, объединенные в диапазон, для последующей работы с ними. Все это делается на лету. А для преобразования таблиц и матриц подходят только матричные функции, так как обычные формулы с такими задачами не справляются. Но при этом необходимо учитывать, что на такие выражения действуют дополнительные правила ввода и редактирования.
