Для Чего Обработка Больших Массивов Данных в Excel

Что такое массив

Массив — переменная, состоящая из некоторого количества однотипных элементов. У массива, как и у любой другой переменной, есть имя. А доступ к конкретному элементу массива осуществляется через указание в скобках после имени его индекса. Например, A(5) означает, что я обращаюсь к элементу с индексом 5 массива, имеющего имя A.

Типы массивов

Массивы в VBA и во многих других языках программирования делятся на 2 класса:

Фиксированные массивы . Такие массивы состоят из заранее известного количества элементов. Это количество определяется во время объявления массива и уже не может быть изменено в процессе его жизненного цикла. Вы, конечно же, сможете использовать меньшее количество элементов, но не существует способа увеличить количество элементов сверх объявленного.

Объявление массивов

Объявление фиксированных массивов

Рекомендация : при объявлении массивов VBA я советую вам давать всем именам префикс » arr «. Я сторонник венгерской нотации.

Таким образом, по-умолчанию массив arrTemp имеет 11 элементов — от 0 до 10. Но, если в начало модуля, в котором этот массив объявляется, вставить оператор Option Base 1 , то массив arrTemp будет иметь 10 элементов — от 1 до 10.

Помимо вышеуказанного вы вправе использовать следующий синтаксис, который НЕ зависит от option base :

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

Помимо одномерных массивов, можно объявлять и массивы с размерностью больше единицы.

arrMulti — двумерный массив, а arrData3 — трёхмерный. Первый содержит 11*31=341 элемент, второй — 2*3*10=60 элементов. Теоретически допускается объявлять до 60 размерностей массива.

Какие типы данных могут стать элементами массива? Тут всё, как в шутке про фамилию еврея, — абсолютно любой тип данных годится на роль элемента массива, включая объектные типы, User Data Type , другие массивы (через тип Variant ). Если вы не указываете при объявлении тип данных массива, то предполагается, что этим типом является тип Variant .

Объявление динамических массивов

Однако, использовать их после такого объявления пока ещё нельзя. Необходимо выделить память под массив. Особенность работы с динамическим массивом как раз состоит в том, что программист отвечает за его своевременное расширение (усечение) в памяти. Для этого существует специальный оператор, который имеет следующий синтаксис:

Изменение элементов массива

Пора бы нам уже научиться пользоваться нашими массивами — то есть записывать информацию в их элементы и считывать её оттуда. Это довольно просто:

Как и с обычными переменными запись информации в элемент массива происходит через оператор присваивания (=), но указанием индекса элемента массива.

Чтение элементов массива

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

В подпрограммах часто приходится иметь дело с массивами, которые переданы вам в качестве параметра (как это сделать показано ниже), поэтому в этом случае очень актуален вопрос определения нижней и верхней границ индекса массива. Для этого в языке предусмотрены 2 функции: LBound и UBound . Первая возвращает нижнюю границу индекса, вторая — верхнюю.

Для одномерных массивов параметр dimension можно не указывать. Для многомерных массивов его указывать необходимо. Кстати, это означает, что, если вы точно не знаете, с каким массивом имеете дело, но необходимо узнать его первую размерность, то лучше использовать вариант UBound(arrTemp,1) , а не UBound(arrTemp) , так как последний вариант вызовет ошибку, если массив окажется многомерным.

Если вы ошибётесь с указанием правильного индекса массива, то возникнет ошибка периода исполнения с кодом 9. Эта же ошибка возникнет, если вы в функции LBound / UBound укажете несуществующую размерность массива (например, 3 для двумерного массива).

Перебор элементов массива

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

Наиболее удобный оператор цикла для перебора элементов массива — это безусловно For . Next .

так же есть способ не заботиться об определении нижней и верхней границ, если алгоритм не требует от нас знания текущего индекса массива:

Вы, конечно, можете перебирать массив и в других типах циклов Do . Loop , но, право, смысла и удобства в этом не много. По крайней мере я не сталкивался, кажется, с ситуациями, когда для перебора массива цикл For не подошёл.

Передача массивов в подпрограммы

Массивы удобнее всего передавать в подпрограммы в виде параметра типа Variant .

Массив с элементами типа массив

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

Функция Array

Данная функция полезна для создания справочных массивов. Она возвращает переменную типа Variant , содержащую одномерный массив с типом элементов Variant .

Array( arglist )

Вызов функции без параметров приведёт к возврату массива нулевой длинны. При этом будет наблюдаться интересный эффект LBound вернёт вам 0, а UBound вернёт -1, то есть верхняя граница окажется меньше нижней границы.

Функция Split

Split возвращает одномерный массив, содержащий подстроки, из строкового выражении с учётом указанного разделителя

expression — строковое выражение, содержащая подстроки и разделители. Обязательный параметр.

delimiter — текстовый разделитель. Необязательный параметр. Если опущен, то предполагается, что разделителем является символ пробела.

limit — количество подстрок, которое необходимо вернуть. -1 или отсутствие параметра означает, что вернуть надо все подстроки.

compare — константа, указывающая тип сравнения для символов разделителей. 1 — текстовое сравнение (без учёта регистра), 0 — бинарное сравнение (с учётом регистра).

Если вы в качестве разделителя укажете пустую строку, то на выходе получите массив, состоящий из одного элемента. Кстати, split всегда возвращает массив с нулевой нижней границей вне всякой зависимости от наличия option base 1 .

Нюансы работы с динамическими массивами

Неинициализированный массив

У динамического массива есть такое промежуточное состояние, когда он уже объявлен, но ещё не содержит никаких элементов.

Для этого я предлагаю пользоваться функцией подобной нижеописанной IsNotEmptyArray :

Расширение массива

Удаление массива

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

Получение массива на основе диапазона Excel

Самый эффективный по скорости способ получить содержимое диапазона Excel для манипулирования в VBA — это скопировать его в массив с элементами Variant. Делается так:

Даже, если вы передаёте в массив столбец или строку, получаемый массив всегда будет иметь 2 измерения. Измерение 1 отвечает за строки, измерение 2 — за столбцы. То есть ячейка C5 будет в элементе arrTemp(5,3) . Нижняя граница таких массивов всегда будет начинаться с единицы.

Дополнительные источники

[expert_bq id=»1570″]Для работы с группами данных различных размеров и параметров у Microsoft Excel есть специальный оператор массив, который представляет собой ряд смежных ячеек со значениями, находящиеся в одном блоке. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть — если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

Все про массивы в VBA читать в пошаговом руководстве по Excel

Для статического массива функция Erase сбрасывает все значения по умолчанию. Если массив состоит из целых чисел, то все значения устанавливаются в ноль. Если массив состоит из строк, то все строки устанавливаются в «» и так далее.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Формулы массивов в Excel — синтаксис формул массивов и массивов констант

Что такое массив?

В Excel массив – это диапазон ячеек. Ниже приведены несколько примеров. Массивы в Excel бывают двухмерные и одномерные. Одномерные в свою очередь делятся на горизонтальные и вертикальные.

Когда мы говорим о формулах массива, мы подразумеваем, что это нормальная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ…), но немного измененная, чтобы принять в качестве входных данных массив или набор массивов. Это то, что лежит в основе формул массива и делает его столь мощным.

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

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

Части формул массивов в Excel

(Прежде чем мы пойдем дальше, убедитесь, что при вводе формул массива, вы вводите Ctrl + Shift + Enter, а не обычный Enter, как при обычных формулах).

Массив констант в формулах массивов

Массив констант – это набор статических значений. Эти значения не ссылаются на другие ячейки или диапазоны. Поэтому они будут всегда одинаковыми независимо от изменений происходящих на листе.

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

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

Операторы массива в формулах массивов

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Оператор И возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример ниже показывает использование оператора массива И (*) между массивами:

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из условий выражения возвращает значение ИСТИНА. Пример ниже показывает использование оператора массива ИЛИ (+) между массивами:

Что такое диапазон массива?

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

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

Возможно, вы уже получили кое-какое представление об этой части статьи. Все что вам нужно, чтобы написать формулу массива – это использовать в качестве аргументов массив и нажать сочетание клавиш Ctrl + Shift + Enter. Давайте рассмотрим некоторые основные моменты синтаксиса формул массивов.

Скажем, у вас есть набор данных в ячейках D2:D10 и вы хотите отсортировать их в порядке возрастания. Вы уже наверное догадались, что нам понадобиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое наименьшее значение и заданного диапазона. Нам также понадобиться диапазон, где мы будем производить вычисления.

Поиск уникального значения, отвечающего определенным условиям

Консолидация данных по более чем одному условию

Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами. Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0. Затем мы используем функцию СУММ для суммирования всех этих значений массива.

Еще один пример консолидации данных по условию

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

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

[expert_bq id=»1570″]Единственное существенное отличие работы оператора исходная матрица должна иметь одинаковое количество столбцов и строк, и ее определитель не должен быть равен 0. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Работа с диапазонами данных может на порядок ускорить весь текущий процесс работы, нежели вводить нужные формулы в конкретные ячейки больших и громоздких таблиц. Матрицы и массивы имеют хорошую функциональность и могут помочь справиться с большими данными «на лету».

Как выделить массив данных в excel

Возможно, вы уже получили кое-какое представление об этой части статьи. Все что вам нужно, чтобы написать формулу массива – это использовать в качестве аргументов массив и нажать сочетание клавиш Ctrl + Shift + Enter. Давайте рассмотрим некоторые основные моменты синтаксиса формул массивов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]В данном случае формула массива синхронно пробегает по всем эле ментам диапазонов СЗ С21 и ВЗ В21 , проверяя, совпадают ли они с задан ными значениями из ячеек G4 и G5. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Примечание. Функция Debug.Print записывает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G).

Работа с массивами данных в Excel (Эксель) » Компьютерная помощь

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу 20;ЕСЛИ(ВЗ:В14 и скопируйте ее в С26:D26 . В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26 , выберите значок Автосумма ). Вы получите блок, показанный на рис. 105.