Vba excel поиск в массиве

Ответить

Сообщение Словари-массивы нужны в общем случае. особенно если данных много.
Массив в котором ищем циклом (одним) заносим в словарь, массив который проверяем циклом (одним) проверяем по словарю.
Ну а покраску можно организовать разными способами, в зависимости от количества данных. Если не спешим — можно сразу и красить по одной ячейке, это просто. Но долго.

Файл не смотрел — с работы туда ходу нет.

Да, по примеру — поудаляйте всё лишнее и разместите тут на форуме, если файл больше 100к. И не забывайте, что ещё есть архиваторы.
Если в рабочем файле десятки тысяч строк — это не лишне упомянуть в описании задачи, будет влиять на алгоритм решения задачи.

Если файл не появится — придётся тему закрыть. Автор — Hugo
Дата добавления — 19.09.2013 в 10:42

Быстрый поиск в двумерном массиве

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

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

оба способа получают на выходе отфильтрованный двумерный массив.

Способы формирования отфильтрованных массивов — разные:

второй способ — функцию ArraySearchResults

Основные отличия и особенности этих 2 способов поиска:

• ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
• ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
• ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
  соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз
• ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
• ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
• ArraySearchResults производит поиск в заранее сформированной текстовой строке
  Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.

Примеры поиска в огромных массивах:

Поиск с использованием ArrAutofilterEx

Поиск с использованием ArraySearchResults

Код функции ArraySearchResults:

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

• 59134 просмотра

Комментарии

подскажите, как сделать поиск нескольких искомых значений?

Привет!
Для уважающих 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 колонки большого (чтобы было более понятно: по номерам продавцов найти все операции, которые они совершали (первый столбец большого), при этом, отбор производится только в том случае, если операции проводились с другими продавцами). Т.е. в результате поиска по продавцу «Пегасову» (из мелкого файла) должны отобраться строки, когда «Пегасов» что либо продавал другим продавцам, перечисленным в мелком файле. Одному продавцу может соответствовать множество операций.

За ответ: премного благодарен — попробую поэксперементировать (о результатах нагрузочного тестирования сообщу (для информации: обработка 5 файлов (6 500 + 870 000 + 870 000 + 870 000 + 870 000) занимает порядка 28-30 минут на машине с intel i5 650, RAM 4 Gb (на 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.

А возможна работа только для значений со знаком «=»? А можно ли использовать «<>«?

VBA Excel. Метод Find объекта Range

Метод Find объекта Range для поиска ячейки по ее данным в VBA Excel. Синтаксис и компоненты. Знаки подстановки для поисковой фразы. Простые примеры.

Предназначение и синтаксис метода Range.Find

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

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

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Параметры метода Range.Find

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Знаки подстановки для поисковой фразы

Условные знаки в шаблоне поисковой фразы:

• ? – знак вопроса обозначает любой отдельный символ;
• * – звездочка обозначает любое количество любых символов, в том числе ноль символов;

Простые примеры

При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:

1. Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
2. Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.

В примерах используются переменные:

• myPhrase – переменная для записи поисковой фразы;
• myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.

Поиск на листе Excel

Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

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

Поиск функцией Find

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

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Оценка статьи:

Загрузка...

Vba excel поиск в массиве Ссылка на основную публикацию

Похожие публикации/div>

• 
  Средство просмотра word

wpDiscuz

Insert

Adblock
detector