Vba excel проверить есть ли элемент в массиве
Обновлено: 07.07.2024
Массивы используются в большинстве языков программирования и VBA, как язык высокого уровня, тоже не мог обойтись без них. Данный материал расскажет о создании массивов, их применении, а так же вы здесь найдете примеры использования массивов. Для начала разберем синтаксис определения массивов.
Объявление массивов
Пример 1. Создание (объявление) одномерного массива выполняется, так:
В данном примере объявляются: одномерный массив Arr1, содержащий ячейки с 0-й до 10-й типа Integer, массив Arr2, содержащий ячейки с 5-й до 10-й типа String и динамический массив Arr3.
Прежде чем использовать динамический массив, его необходимо "инициализировать", т.е. выделить для него необходимую память. Менять размер динамического массива можно много раз в программе, даже в цикле, если хотите. Правда учитывайте, что выделение памяти для массива - ресурсно-затратная операция, поэтому не стоит менять размер массива очень часто, это сильно замедлит программу.
Пример 2. Инициализация динамического массива и изменение его размеров
В данном примере мы сначала с помощью ReDim задали размер динамического массива в 11 элементов (c 0-го по 10-й), а затем снова увеличили размер до 21-го элемента. Кроме того, использовали ключевое слово Preserve - означающее, что нужно сохранить уже имеющиеся элементы с их значениями (без этого ключевого слова массив обнуляется).
Примечание: изменение размеров функцией ReDim возможно только для динамических массивов. Если размер массива был изначально задан, то его размер уже не будет меняться функцией.
Пример 3. Объявление многомерного массива
Arr4 - двумерных массив 11х11 элементов, а массив Arr5 - трехмерный.
Пример 4. Создание массива массивов
В следующем примере массив Arr2 будет содержать элементы другого массива Arr1
Определение нижней и верхней границы массива
Чтобы узнать какой самый наименьший индекс у массива и какой самый максимальный индекс массива, нужно использовать функции LBound для определения нижней границы и UBound для определения верхней границы.
Пример 5. Определение границ массива
Чтобы определить границы многомерных массивов, нужно просто использовать второй параметр функций UBound и LBound.
Задание нижней границы по-умолчанию
Иногда бывает очень не удобно, что VBA начинает нумерацию элементов массивов с нуля (0), это часто может привести к путанице и усложнению кода программы. Для решения этой проблемы есть специальный оператор Option Base , аргумент которого может быть 0 или 1. Указав значение 1, индексация массивов будет начинаться с 1, а не с 0.
Пример 6. Указание нижней границы по-умолчанию.
Примечание: Оператор Option Base так же влияет на функцию Array и не влияет на функцию Split (будут рассмотрены ниже), что означает, что при задании "Option Base 1", функция Array вернет массив с индексацией с 1-цы, а функция Split вернет массив с индексацией с 0.
Запись данных в массивы
Для сохранения каких-либо значений в массив, можно воспользоваться несколькими способами. С помощью примеров, покажу эти способы.
Пример 7. Запись данных в массив в цикле.
Пример 8. Запись заранее известных данных с помощью Array
Функция Array возвращает массив из перечисленных данных типа Variant. При этом, массив, который будет содержать значения, должен быть динамическим, т.е. без указания его размеров и границ.
Пример 9. Получение массива из строки с разделителями
Обход элементов массива
Обычно, массивы используются для хранения большого кол-ва данных, а не 1-2 значений, поэтому чтобы получить все эелементы и использовать их для чего-то, обычно используют циклы. Наиболее удобны в этом плане циклы For и For Each .
Пример 10. Обход элементов массива циклом For.
Пример 11. Обход элементов массива циклом For Each.
Иногда, бывает необходимость работы с массивом внутри других типов циклов, но получение значение элемента, всё-равно в них будет таким же, как и в цикле For, т.е. через индекс элемента.
У меня есть код ниже, который должен проверять, находится ли значение в массиве или нет.
Если ячейка A1 содержит слово Examples , по какой-то причине обе IsInArray обнаруживают ее как существующую для обоих массивов, когда она должна находить ее только в массиве vars1
Что мне нужно изменить, чтобы сделать мою функцию IsInArray для ее точного соответствия?
Вы можете грубо заставить ее так:
Используйте функцию Match() в excel VBA, чтобы проверить, существует ли значение в массиве.
Хотя это, по сути, просто ответ @Brad, я подумал, что, возможно, стоит включить слегка модифицированную функцию, которая будет возвращать индекс искомого элемента, если он существует в массиве. Если элемент отсутствует в массиве, он возвращает -1 .
Вывод этого можно проверить так же, как функцию "in string", If InStr(. ) > 0 Then , поэтому я сделал небольшую тестовую функцию под ней в качестве примера.
Затем я пошел немного за борт и выделил один для двухмерных массивов, потому что, когда вы генерируете массив на основе диапазона, он обычно в этой форме.
Он возвращает один вариантный массив измерений только с двумя значениями, два индекса массива используются в качестве входных данных (при условии, что значение найдено). Если значение не найдено, возвращается массив (-1, -1) .
Вот картина данных, которые я настроил для теста, а затем тест:
Приведенная ниже функция возвращает 0, если совпадения нет, и положительное целое в случае совпадения:
Function IsInArray(stringToBeFound As String, arr As Variant) As Integer IsInArray = InStr(Join(arr, ""), stringToBeFound) End Function ______________________________________________________________________________
Примечание: функция сначала объединяет содержимое всего массива со строкой, используя 'Join' (не уверен, использует ли метод join внутреннее или нет циклическое выполнение), а затем проверяет наличие macth внутри этой строки, используя InStr.
Я хотел бы предоставить еще один вариант, который должен быть и производительным и мощным, потому что
- он не использует иногда более медленный Match )
- поддерживает String , Integer , Boolean и т.д. (не String -only)
- возвращает индекс искомого элемента
- поддерживает nth-вхождение
используйте это так:
Вы хотите проверить, существует ли Примеры в Range ( "A1" ). Значение Если это не удается, проверьте правильность Пример. Я думаю, что mycode будет работать идеально. Пожалуйста, проверьте.
Посмотрите другие вопросы по меткам arrays vba excel-vba excel или Задайте вопрос
В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.
Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива,
оба способа получают на выходе отфильтрованный двумерный массив.
Способы формирования отфильтрованных массивов - разные:
второй способ - функцию ArraySearchResults
Основные отличия и особенности этих 2 способов поиска:
- ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
- ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
- ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз - ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
- ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
- ArraySearchResults производит поиск в заранее сформированной текстовой строке
Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.
Примеры поиска в огромных массивах:
Поиск с использованием ArrAutofilterEx
Поиск с использованием ArraySearchResults
Код функции ArraySearchResults:
При поиске только одного значения время работы обоих макросов поиска не сильно отличается - но обычно функция ArraySearchResults оказывается немного быстрее.
Комментарии
Работает быстро!
Даже в Accesse работает, но у меня массив начинается с 0, и результат выдает на строчку выше.
txt = spl(i): ro& = ro& + 1 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2' Было
txt = spl(i): ro& = ro& + 0 + (Len(spl(i)) - Len(Replace(spl(i), Sep, ""))) / Len(Sep) \ 2' Поменял
Может будет лучше если добавить СтолбецДляВывода&
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 1)' Было
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, СтолбецДляВывода&)' Поменял
Привет, спасибо за реализацию функции, помогла для обработки!
подскажите, как сделать поиск нескольких искомых значений?
Привет!
Для уважающих Option Explicit
в ArraySearchResults
Dim ro As Long, spl, i As Long, j As Long
В SearchString
Dim buffer As String, buffer2 As String, Sep2 As String, i As Long
и скорость возрастёт
Здравствуйте! А подскажите, пожалуйста, возможно ли использование подстановочных знаков для поиска искомого значения?
решено - можно. Всё работает
И второй вопрос - есть ли у вас функция типа SearchString, но для сцепления ВСЕГО двумерного массива в текстовую строку с разделителями, а не одного столбца. Или придётся цикл делать, чего бы очень не хотелось.
Я так понимаю, что ваш вариант даже шустрее, чем Join, который, к тому же, не работает с двумерными массивами (как я понял).
Здравствуйте! Подскажите пожалуйста - могу ли я объявить Optional ByVal ArrayColumn As Long=1 в функции SearchString? Дело в том, что я часто загружаю в массив данные с листа в 1 столбец…
Задача: есть большой список в 1 столбце, текстовый, сотни тысяч записей. И есть второй список тоже текстовый из нескольких сотен записей в двух столбцах. Задача - если в текстовый элемент в первом массиве входит элемент из первого столбца второго то во второй столбец первого массива проставить соответсвующий элемент из второго столбца второго массива. Т.е. поиск совпадений не по всему значению, а по вхождению в него куска.
Справился. Немного не так, как хотел, но работает.
For i = 1 To UBound(resArr)
ListBox1.AddItem resArr(i, 2)
Next i
Уважаемый Игорь, подскажите, пожалуйста, как вывести значения, например, второго столбца отфильтрованного массива в листбокс?
Debug.Print "Результат - строка " & i & " из " & UBound(resArr) & ": ", resArr(i, 2)
все показывает, а вывести в листбокс не могу
ListBox1.List = resArr
естественно выводит 2 столбца
А вот и третья функция, которую я применил в своей работе в течение недели.
Все работает "на ура"!
А теперь вот думаю, чтобы я делал без Ваших функций? :)
Большое Вам спасибо!
Удачи!
А для большого файла и делается два массива, при этом каждый состоит только из одного столбца (своего рода индексы получаю для поиска (пробовал для теста подсовывать файл на 870 000 записей и загружал в массив порядка 20 столбцов - "машинка" с 4 гигами очень серьезно задумывалась при этом (собственно еще и по этой причине отказался от загрузки всего листа в массив (первая причина отказа - искажение данных при "перегонах")))).
При поиске важно найти все строки, которые есть в большом файле и которым соответствуют строки из маленького, при этом должны анализироваться 2 колонки большого (чтобы было более понятно: по номерам продавцов найти все операции, которые они совершали (первый столбец большого), при этом, отбор производится только в том случае, если операции проводились с другими продавцами). Т.е. в результате поиска по продавцу "Пегасову" (из мелкого файла) должны отобраться строки, когда "Пегасов" что либо продавал другим продавцам, перечисленным в мелком файле. Одному продавцу может соответствовать множество операций.
Сделать можно, но - оба столбца большого файла не надо загружать в один массив (иначе компу памяти не хватит, т.к. в массиве будет много лишних столбцов)
Компьютер с 2 гигами памяти - для такого макроса более чем достаточно.
Функцию быстрого поиска в массиве можно использовать, только надо искать значения второго (огромного) массива в маленьком (первом)
Можно и наоборот - но возможно понадобится тройное кеширование строки поиска ( buffer$, buffer2$, и ещё добавить buffer3$)
Ничего конкретного подсказать не могу - надо знать, для чего это делается, и как это все должно работать.
Но функцию использовать можно.
Доброго времени суток!
Подскажите. пожалуйста, стоит ли использовать предложенные функции при сравнении трех массивов и копировании результатов на отдельный лист (первый массив - порядка 2 500 записей (данные отдельного файла, берется только 1 столбец), второй и третий - 150 000 - 250 000 (второй и третий формируются на основе одной таблицы (второй файл), но разных столбцов, которые отстоят друг от друга на неком расстоянии (грубо - первый столбец "А", второй - "AB") и изменять порядок столбцов нельзя))?
Последовательный перебор записей относительно медленный, при этом внесение всей таблицы из второго файла (по которому строятся второй и третий массивы) нежелательно, т.к. теряется формат отдельных столбцов при перегоне данных сначала в массив, а потом на лист Excel (собственно по этой причине приходится копировать с листа исходного файла на итоговый лист диапазон ячеек, при этом номер строки вычисляется на основании номера элемента массива). Количество колонок во втором файле - порядка 50-60.
Есть существенное ограничение: рабочая станция, на которой происходит обработка данных, относительно слабая и ждать от нее рекордов не приходится (памяти на ней всего 2 гига, но офис - 2010). Если бы была возможность прикрепить файл, то показал бы - что получилось (если вставить код здесь, то очень много получится).
а если необходимо найти значение в столбце равное 3, затем спуститься на 2 строки и от этой строчки начать отсчет. такое возможно реализовать?
помогите, пожалуйста
Да, можно, если написать для этого специальную функцию.
а если использовать один массив и фильтровать его на основе значений из другого массива? так можно?
Всё можно сделать - но проще под вашу задачу написать отдельную функцию.
Или поступить иначе:
1) сформировать 3 массива при помощи функции ArrAutofilterEx (для каждого из значений)
2) соединить 3 массива в один при помощи функции CombineArrays
Ещё вариант: использовать средства Excel (автофильтр по нескольким значениям)
Тут вам поможет макрорекордер (запись макросов)
И как можно сделать, чтобы отбирать значения из столбца не только с одним значением? Например, столбец для поиска один и тот же = 3, а значения надо отобрать 560, 570, и 580.
А возможна работа только для значений со знаком "="? А можно ли использовать "<>"?
Существует несколько способов поиска строки в массиве - в зависимости от того, является ли массив одномерным или многомерным.
Поиск в одномерном массиве
Для поиска значения в одномерном массиве вы можете использовать функцию фильтра.
| 123 | Dim z как вариант'фильтровать исходный массивz = Фильтр (Массив, Строка, Истина, vbCompareBinary) |
Синтаксис опции фильтра следующий
Фильтр (исходный массив, сопоставить как строку, [включить как логическое], [сравнить как vbCompareMethod])
В Исходный массив и Соответствовать как строка требуются, в то время как Включить как логическое и Сравнить как vbCompareMethod являются необязательными. Если они не включены, для них установлено значение Правда а также vbCompareBinary соответственно.
Найдите значения, соответствующие фильтру
| 1234567891011121314 | Sub FindBob ()'Создать массивDim strName () как вариантstrName () = Array («Боб Смит», «Джон Дэвис», «Фред Джонс», «Стив Дженкинс», «Боб Уильямс»)'объявить вариант для хранения данных фильтра вDim strSubNames как вариант'фильтровать исходный массивstrSubNames = Фильтр (strName, «Боб»)'если значение LBound больше -1, значит, значение найденоЕсли LBound (strSubNames)> -1, то MsgBox («Я нашел Боба»)Конец подписки |
Второй массив будет содержать значения, найденные фильтром. Если ваши значения LBound и UBound не равны -1, значит массиву удалось найти значение, которое вы искали.
Вы также можете увидеть, сколько раз текст появляется в исходном массиве.
| 1234567891011121314 | Sub CountNames ()'Создать массивDim strName () как вариантstrName () = Array («Боб Смит», «Джон Дэвис», «Фред Джонс», «Стив Дженкинс», «Боб Уильямс»)'объявить массив для хранения данных фильтра вDim strSubNames как вариант'фильтровать исходный массивstrSubNames = Фильтр (strName, «Боб»)'если вы вычтите LBound из значений UBound и добавите 1, мы получим количество раз, когда текст появляетсяMsgbox UBound (strSubNames) - LBound (strSubNames) + 1 & «имена найдены».Конец подписки |
Найдите значения, которые НЕ соответствуют фильтру
В [Включить как логическое] опция позволяет вам узнать, сколько значений в вашем массиве, которые НЕ НАДО соответствовать вашему фильтру
| 1234567891011121314 | Sub CountExtraNames ()'создать массивDim strName () как вариантstrName () = Array («Боб Смит», «Джон Дэвис», «Фред Джонс», «Стив Дженкинс», «Боб Уильямс»)'объявить массив для хранения данных фильтра вDim strSubNames как вариант'фильтровать исходный массивstrSubNames = Фильтр (strName, «Боб», ложь)'если вы вычтите LBound из значений UBound и добавите 1, мы получим количество раз, когда текст появляетсяMsgbox UBound (strSubNames) - LBound (strSubNames) + 1 & «имена найдены».Конец подписки |
поэтому мы изменили эту строку:
| 1 | strSubNames = Фильтр (strName, «Боб») |
| 1 | strSubNames = Фильтр (strName, «Боб», ложь) |
Использование этой строки в коде вернет все имена, НЕ совпадающие с «Боб».
Фильтры с учетом регистра
Вы обнаружите, что фильтр по умолчанию чувствителен к регистру. Это верно для всех функций VBA. Если вы хотите искать текст без учета регистра, вам нужно немного изменить код.
| 1 | z = Фильтр (strName, "bob" ,, vbTextCompare) |
Добавление vbTextCompare к вашей строке фильтра позволит вашему коду найти «bob» или «Bob». Если это опущено, VBA по умолчанию использует vbBinaryCompare который будет искать только те данные, которые ТОЧНЫЙ соответствие. Обратите внимание, что в приведенном выше примере мы не учли [Включить как логическое] аргумент, поэтому предполагается True.
Вариант Сравнить текст
Как вариант, вы можете добавить текст Вариант Сравнить текст в верхнюю часть вашего модуля - это сделает все функции, которые вы пишете в этом конкретном модуле, нечувствительными к регистру.
Использование цикла для поиска в массиве
Использование цикла немного сложнее, чем использование функции фильтра. Мы можем создать функцию, которая будет перебирать все значения в массиве.
| 1234567891011121314151617 | Sub LoopThroughArray ()'создать массивDim strName () как вариантstrName () = Array («Боб Смит», «Джон Дэвис», «Фред Джонс», «Стив Дженкинс», «Боб Уильямс»)Dim str Найти как строкуstrFind = "Боб"Тусклый я до тех пор, пока'перебирать массивДля i = LBound (strName, 1) To UBound (strName, 1)Если InStr (strName (i), strFind)> 0, тоMsgBox "Боб найден!"Выход дляКонец, еслиДалее яКонец подписки |
Поиск в многомерном массиве
Мы также используем цикл для поиска в многомерном массиве. Еще раз, нам нужно создать функцию, которая позволит нам перебрать все значения в массиве, но на этот раз нам также нужно перебрать каждое измерение массива.
Читайте также: