что такое массив эксель

Работа с массивами функций в Excel

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

Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат. Рассмотрим подробно работу с массивами функций в Excel.

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

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

Одномерные массивы бывают:

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

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

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

Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.

Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.

Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.

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

Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

Рассмотрим ее синтаксис:

Функции работы с массивами Excel

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

Источник

Массивы Excel

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

Содержание статьи:

Определение массива

Чтобы понять, что такое массив, необходимо вспомнить, что такое переменная.

Каждый лист Excel является таблицей, за которой закреплена область в памяти компьютера. Таблица состоит из ячеек, которые имеют свой уникальный адрес. Сама ячейка не может быть разделена на более мелкие части, поэтому ее можно назвать переменной. Массив содержит набор переменных и имеет имя. То, что называют в Excel диапазоном, по своей сути является массивом: строка листа, столбец листа, количество ячеек >1, все это массивы данных. НО! Чтобы не вносить путаницу в определения функций и т.п. данные понятия необходимо разделять, т.к. приложение по-разному обрабатывает диапазоны и массивы.

Чтобы дать программе понять, что формула содержит массив, нужно после ввода данных в строку формул одновременно нажать клавиши клавиатуры Ctrl + Shift + Enter. Все ее содержимое заключится в фигурные скобки <>.

Далее будет подробнее описана работа с массивами.

Измерения массива

Массивы могут содержать несколько измерений вплоть до измерений в несколько десятков и даже больше, но хорошо это или плохо, в формулах Excel используются максимум 2 измерения, поэтому массив в Excel может быть:

Одномерный массив состоит из одного ряда значений. Это может быть строка или столбец.

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

Массив констант

Константа это та же переменная, только не меняющая значение. Если значение переменной можно поменять в любое время, то константа задается один раз и больше не меняется. Наверное, самая известная константа – число Пи.

Массив констант отличается от обычного массива тем, что обычный массив ссылается на диапазон ячеек, а массив констант задается пользователем вручную:

После заполнения формы, нажмите «OK».

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

О том, как их применять рассказывается дальше.

Операции с массивами

Перейдем, наконец, к примерам использования массивов.

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

При стандартных вычислениях формула составлялась бы так: =B2/C2*100. Затем ее необходимо протянуть. Мы получили готовый результат.

Повторим расчет, только с использованием массивов.

Сначала выделяем ячейки, в которых необходимо произвести расчет. Далее записываем в строку формул: =B2:B5/C2:C5*100, где “B2:B5” диапазон всех ячеек для литража, “C2:C5” диапазон всех ячеек для расхода. На данный момент это именно диапазоны, чтобы они превратились в массивы, нажмите одновременно клавиши Ctrl + Shift + Enter. Формула автоматически будет заключена в фигурные скобки, а расчет появиться во всех предварительно выделенных ячейках. Результат тот же, что и в первом варианте.

Следовательно, уже навязывается вопрос о смысле использования массивов.

В данном конкретном примере, что использовать – разницы нет, если только Вы не хотите защитить ячейки от случайного или намеренно изменения. Поясним. Когда к какому-то диапазону применяется массив, то затем никакой элемент этого массива не может быть отдельно изменен либо удален. Если попробовать произвести эти действия, приложение Excel выдаст ошибку. Чтобы избежать ее, выделите весь диапазон, к которому применен массив, а затем измените строку формул либо удалите ее полностью, после подтвердите изменения для всех элементов нажатием Ctrl + Shift + Enter.

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

Используя стандартные формулы, подсчет производиться двумя действиями:

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

Согласитесь, что в данном примере плюсы массивов куда более заметные.

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

Применим массив, который мы создавали в начале урока: Имя_диапазона.

Данный массив является одномерным и имеет размерность 5, т.к. содержит пять элементов. Перенесем значения массива в ячейки книги. Для этого выделим диапазон A1:E1, в строку формул введем имя массива и нажмем Ctrl + Shift + Enter. Получим результат:

В данном случае программа поочередно будет вытаскивать значения из массива для каждой ячейки. Если ячеек окажется больше, чем элементов есть в массиве, то для последних ячеек будет выдана ошибка #Н/Д, т.е. элемент не найден. Если ячеек, наоборот окажется меньше, то массив будет задействован частично, а именно в количестве заданных ячеек.

Теперь поступим по-другому. Выделите вертикальный диапазон A1:A5 и введите в него массив. Во все ячейки диапазона загрузиться только первый элемент массива. Это произошло от того, что созданный нами массив является горизонтальным, а не вертикальным.

Для создания горизонтального массива его элементы разделяются «;», для создания вертикального используется «:». Для создания двумерных массивов используются оба символа. Имейте в виду, иногда, где и какой символ использовать, задается настройками, чтобы проверить, какие разделители использовать именно Вам, поступите следующим образом:

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

Для начала создадим одномерный вертикальный массив = <1: 1: 1>и умножим его на 5, после чего узнаем сумму произведений его элементов.

Здесь все просто, программа умножила каждый элемент массива на 5, затем их просуммировала.

Теперь умножим тот же диапазон на горизонтальный массив =<5; 5>.

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

Снова изменим условия и из вертикального массива сделаем двумерный массив =<1; 1: 1; 1: 1; 1>. Перемножим на =<5; 5>. И вот оно! Результат не поменялся. Почему? Потому что в случае использования двумерных массивов их элементы сопоставляются другим элементам массива по их порядковым номерам в равных измерениях.

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

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

Имейте в виду, что к массивам можно применять все формулы, которые применяются в стандартных ситуациях, включая логические. Например, в случае, описанном выше, когда перемножаются массивы с разными размерностями в одинаковых измерениях, чтобы избежать возврата ошибки, необходимо изменить формулу – <=СУММ(ЕСЛИОШИБКА(A1:C3*E1:F1;0))>.

Источник