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