МАССИВЫ. МАССИВЫ В ФОРМУЛАХ

МАССИВЫ

МАССИВЫ В ФОРМУЛАХ

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

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

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

Переменная - область памяти, за которой закреплено определенной имя, например «x = 5» или «Имя = “Андрей”». Переменная всегда содержит только одно значение, т.е. не может быть разделена на более мелкие части. Данное определение больше подходит для переменных в языках программирования. Чтобы не отходить от темы Excel, переопределим его.

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

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

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

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

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

Одномерным – 1 измерение;
Двумерным – 2 измерения.

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

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

На рисунке ниже представлены оба вида массивов. Обратите внимание, что разные измерения имеют разные разделители («;» - для столбцов и «:» - строк).

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

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

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

{=A3:A7} – это обычный массив;
{1: 2: 3: 4: 5} – это массив констант.

Представьте, что Вам необходимо использовать в расчетах большой массив, состоящий из сотни констант, и использовать его нужно многократно. Набивать константы каждый раз для каждой формулы ручками – дело «неблагодарное». Поэтому, создайте синоним массива с помощью функции присвоения имен, расположенной на вкладке «Формулы» -> раздел «Определенные имена» -> кнопка «Диспетчер имен». В появившемся окне нажмите на кнопку «Создать», после чего появиться следующая форма:

Имя – имя диапазона;
Область – место, где данное имя будет доступно;
Примечание – комментарий. Текст, введенный здесь, будет высвечиваться при выборе имени массива из определенной для него области;
Диапазон – сам массив в виде ссылки на диапазон либо массив констант «={…}».

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

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

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

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

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

Название авто	Литраж бака, л	Расход, л на 100 км
Авто1
Авто2
Авто3
Авто4

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

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

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

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

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

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

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

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

1. Рассчитать километраж для каждого авто;

2. Просуммировать все имеющиеся результаты.

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

Выберите ячейку, в которую хотели бы записать результат. В нее впишите уже применяемую формулу, но в качестве аргумента функции СУММ. Подтвердите использование массива нажатие Ctrl + Shift + Enter . Должно получиться следующее: {=СУММ(B2:B5/C2:C5*100)}.

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

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

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

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

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

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

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

1. Заполните числами несколько ячеек подряд по горизонтали и вертикали;

2. Создайте функцию СУММ;

3. Для первого аргумента функции укажите горизонтальный диапазон, для второго вертикальный;

4. Нажмите на кнопку «вставить функцию» рядом со строкой формул;

5. В окне аргументов функции посмотрите предварительные результаты для каждого аргумента. Те разделите, которые использует программа, необходимо использовать и Вам.

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

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

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

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

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

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

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

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

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

Резюме

1. Горизонтальный массив – это массив, ссылающийся на ячейки одной сроки либо содержащий константы, разделенные символом «;».

2. Вертикальный массив – массив, ссылающий на ячейки одного столбца либо содержащий константы, разделенные символом «:».

3. Размерность массива – количество элементов массива в одном измерении.

4. Одномерный массив – либо горизонтальный либо вертикальный массив;

5. Двумерный массив – вертикальный массив, содержащий в себе горизонтальные массивы одной размерности.

6. Ввод массива ВСЕГДА необходимо подтверждать нажатием клавиш Ctrl + Shift + Enter.

7. Нельзя изменить отдельный элемент массива. Изменению подлежать сразу все его элементы.

8. В случае применения к массиву переменной, переменная будет сопоставлена каждому элементу массива.

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

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

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