5 основных функции для работы с массивами

Доброго времени суток друзья!

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

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

Ну, что же, изучим необходимые функции для работы с массивами:

1. Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван»)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

4. Функция ГПР (HLOOKUP)

Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца: =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

5. Функция ВПР (VLOOKUP)

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

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Деньги для людей умных составляют средство, для глупцов — цель. А. Декурсель

Работа с массивами VBA Excel

Я разрабатываю макрос для удаления пустых строк из рабочего листа, который используется для ввода индивидуальных заказов. Допустим, строки 7,8,9 и 12 имеют содержимое. Я хочу переместить содержимое строки 12 в строку 10.

До сих пор я нашел последнюю занятую строку в столбце c, а затем определил, является ли ячейка в строке в столбце e пустой или нет.

Теперь я хочу поместить значение в массив либо 0 (пусто), либо 1 (занято). Я получаю ошибку (требуется объект) в строке кода, которая устанавливает значение stones (1) равным 1 или 0.

Что происходит не так?

3 ответа

Работа с массивами в VBA

У меня есть небольшая проблема с работой с массивами в VBA, где то же самое было бы тривиально в (почти) любом другом языке: Public Function getArray() As MyType() ‘do a lot of work which returns an array of an unknown length’ ‘this is O(N^2) or something equally intensive so I only want to call.

У меня есть очень большой симулятор Монте-Карло, который моделирует североамериканскую энергосистему. Он довольно большой и требует некоторого времени для запуска, но изначально был сделан в Excel/VBA для простоты. Теперь я хочу перенести код VBA на скомпилированный язык, чтобы оптимизировать.

Я предполагаю, что вы делаете это в целях обучения, а не практичности:

Вы можете погуглить массивы VBA и получить множество материалов по этому вопросу. Я бы начал здесь: http://www.cpearson.com/excel/vbaarrays.htm

Вы бы объявили свой массив следующим образом:

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

Вы можете установить значение 5-го элемента в массиве равным 10, например:

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

Если вы пытаетесь избавиться от пустых ячеек в столбце листа ‘Belmont’ C, то это должно сработать для вас:

Если вы хотите удалить все строки, в которых столбец C пуст, то:

сделает это без зацикливания.

Массивы Excel

Умение работать с массивами является заключительным этапом изучения функций 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	50	6
Авто2	60	7
Авто3	70	10
Авто4	80	12

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

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

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

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

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

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

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

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

Рассчитать километраж для каждого авто;
Просуммировать все имеющиеся результаты.

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

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

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

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

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

Данный массив является одномерным и имеет размерность 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))>.

Работа с массивами данных в Excel (Эксель)

Зачастую, при работе в Excel необходимо оперировать не только с различными ячейками, но с большими диапазонами данных, значений и ячеек. При этом нужно в одно нажатие изменить целый ряд значений и ячеек. Что же делать в таком случае?

Для работы с группами данных различных размеров и параметров у Microsoft Excel есть специальный оператор – массив, который представляет собой ряд смежных ячеек со значениями, находящиеся в одном блоке. Массивы могут быть одномерными, либо многомерными.

В первом случае все значения и вся информация находится лишь в одном столбце либо строке.

Во втором же такие массивы называют матрицей.

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

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

1. Формулы в массивах и их использование

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

К примеру, для того чтоб умножить один массив значений на другой необходимо использовать тот же оператор умножения «*», но с небольшим отличием – в качестве аргументов необходимо указывать уже именно массив, а не адрес конкретной ячейки или диапазона: «=адрес_массива1*адрес_массива2».

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

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

Одномерный массив – А2:А7

Двумерный массив – A2:D7

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

К примеру, чтобы получить конечную суму товара необходимо умножить одномерный массив со значениями количества товаров на массив со значением цены, а для завершения операции нажать не «Ввод», а «Ctrl+Shift+Enter».

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

С ячейками массива диапазона данных нельзя проводить отдельных операций – отныне это целый и единый блок данных значений. Любая попытка будет закончена ошибкой – Excel выдаст окно с ошибкой и укажет на нее – «Нельзя изменять часть массива».

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

Что же делать в таком случае? Выход очень простой – достаточно нажать на «Ок» в сообщении, указав, что поняли свою ошибку, и нажать на кнопку отмены применения изменений в строке формул. Эта отмена завершит работу с массивом данных и даст полную функциональность открытому листу документа программы.

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

Для завершения работы – все та же комбинация «Ctrl+Shift+Enter» вместо привычного нажатия на «Enter».

Для удаления формулы необходимо по-прежнему выделять весь диапазон массива и провести процедуру удаления клавишей «Delete». Массив данных исчезнет и появится возможность работы с отдельными ячейками и другими диапазонами.

2. Функции и массивы

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

Но проще и легче работать с функциями в массивах непосредственно через «Мастер функций» — встроенный оператор функций для массивов.

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

Мастер функций позволяет с легкостью вводить данные с помощью диалогового окна и окна аргументов непосредственно выбранной функции. Для завершения операций – заветное сочетание клавиш «Ctrl+Shift+Enter».

3. СУММ – оператор суммирования массивов

Оператор «СУММ» имеет простой синтаксис – «=СУММ(массив1;массив2;…)».

Стоит учесть, что сумма массивов всегда выводится лишь в одну ячейку, так как, несмотря на то, какая будет большая матрица, ее сумма с другой матрицей всегда будет конкретное число.

4. ТРАНСП – оператор транспортировки матрицы

Оператор «ТРАНСП» имеет так же простой синтаксис «=ТРАНСП(массив)», и позволяет изменять целые массивы и матрицы данных и значений – строки и столбцы будут заменены местами.

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

5. МОБР – оператор вычисления для обратных матриц

Оператор «МОБР» имеет так же простой синтаксис «=МОБР(массив)». Единственное существенное отличие работы оператора – исходная матрица должна иметь одинаковое количество столбцов и строк, и ее определитель не должен быть равен «0».

В ином варианте результат будет «#ЗНАЧ», что указывает на ошибку значений в матрицах.

Чтобы избавить себя от лишних хлопот, перед работой с оператором «МОБР» лучше просчитать определитель матрицы. Для этого есть оператор «МОПРЕД». Результат выведется в одну ячейку.

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

0 0 голоса

Рейтинг статьи

5 основных функции для работы с массивами