Поиск в массиве в excel vba

Добрый день!
Есть массив данных на одном листе (3 столбца — RAWDATA) и массив данных на другом листе (1 столбец — LIST).
Берем значение в первой ячейке на листе LIST и ищем его на листе RAWDATA, если находим — то красим зеленым, нет — красным и т.д.
Написала код, но он отрабатывает только по первой ячейке, помогите, пожалуйста, разобраться.

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Dim r As Range
Dim l As Range

Set r = ThisWorkbook.Sheets(«RawData»).UsedRange
Set l = ThisWorkbook.Sheets(«LIST»).UsedRange

For j = 1 To r.Rows.Count
For i = 1 To l.Rows.Count
If l.Cells(i, 1).Value = r.Cells(j, 1).Value Then
l.Cells(i, 1).Interior.Color = RGB(190, 245, 116)
Else: l(i, 1).Cells.Interior.Color = RGB(227, 38, 54)
End If
Next i
Next j

Красим где? Судя по цитате, надо на листе RAWDATA, а в макросе у Вас на листе LIST.

KuklP, извините, не увидела сообщения до цитаты. Красим на листе LIST.

Файл вроде прикрепила, но не вижу.

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

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

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

Если файл не появится — придётся тему закрыть.

Поиск на листе 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-я строка продолжаем поиск после найденной ячейки.

Читать еще: Фигурные скобки в excel

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 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 марта любого года.

Поиск в массиве в excel vba

как мне найти в масиве байт последовательность байт, но быстрым способом, мой метод маленька тормозащий

For l = 0 To FileSize — Len(SearchText)
If exitsrch = True Then GoTo ExSrch

Searched = True
For l1 = 0 To 3 »Len(SearchText)
If Chr(FileArr(l + l1)) <> Mid(SearchText, l1 + 1, 1) Then Searched = False
Next l1

If Searched = True Then
Images(SRpic) = l
SRpic = SRpic + 1
End If

Номер ответа: 1
Автор ответа:
Воронков Василий

Номер ответа: 2
Автор ответа:
vig11

в первом For на переменную.

Dim i As Integer

i = FileSize — Len(SearchText)

If Searched = True Then

If Searched Then

For l1 = 0 To 3 »Len(SearchText)
If Chr(FileArr(l + l1)) <> Mid(SearchText, l1 + 1, 1) Then

For l1 = 0 To 3 »Len(SearchText)
If StrComp(Chr(FileArr(l + l1)) , Mid(SearchText, l1 + 1, 1), vbBinaryCompare)<>0 Then

Номер ответа: 3
Автор ответа:
Alex3

Откуда такая уверенность, что GOTO самая тормозящая вещь во всех ЯВУ? По моему это одна из самых быстрых команд!

Номер ответа: 4
Автор ответа:
S e M a

Номер ответа: 5
Автор ответа:
Воронков Василий

VB — структурно ориентированный ЯВУ. Goto, если кто не знает, элемент линейного программирования. Линейные команды всегда выполняются знач. медленнее. Если вас это удивляет, то вы блуждаете во тьме, и для выхода на свет могу порекомендовать книгу «QuickBasic 4.5 для носорогов» 1992 года производства.

ЗЫ. Для c длиной файла лучше использовать EOF

While not EOF(1) ‘ if opened as #1

Номер ответа: 6
Автор ответа:
С т р а ш ны й С о н

Совет для всех программистов на Visual Basic:

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

Номер ответа: 7
Автор ответа:
Alex3

После компиляции VB код превращает в машинный. А процессору совершенно по фигу каким там ЯВУ является VB. Goto компилится в одну машинную команду плюс адрес.

В качестве демонстрации:

Создаем две кнопочки, добавляем код, КОПИЛИРУЕМ.

Обладатели медленных машин могут убрать нолики, если надо.

Private Sub Command1_Click()
Dim i As Long, t As Double
t = Timer
mBegin:

Private Sub Command2_Click()
Dim i As Long, t As Double
t = Timer
For i = 0 To 1000000000

Next i
MsgBox Timer — t
End Sub

Пустой цикл с GoTo почти на 40% быстрее (на P4). А приводить примеры для языков, код которых нельзя откомпилить в машинный ИМХО не правильно.

З.Ы. А излишнее использование GoTo не рекомендуется, по крайней мере для программистов, а не для носорогов, потому что код становится прохо структурированных и плохо читаемый.

Номер ответа: 8
Автор ответа:
Воронков Василий

Интересно. Если верить вашему посту, то получается, что все равно какой компайлер используется — скорость все равно одна?

зы. пузырьковый for/next штука тоже не особо скоростная

Номер ответа: 9
Автор ответа:
Alex3

Во-первых, я не агитирую за использование GoTo, я лишь опроверг мнение, что он «знач. медленнее».

Во-вторых , как интересно такой профи как ты обработку ошибок делает, не нарушая принципа «Никакой профессиональный программист никогда не поставит в своей проге слово GoTo»?

Ну, а в третьих, из-за отсутствия в VB Continue (кстати, в VB.Net случайно не добавили?) использование GoTo бывает очень даже полезно (н только мое мнение).

Естественно, что в зависимости от компилятора скорость может отличаться. Откуда такой вывод сделан. К тому же мы говорим про VB, а в QuickBasic компилятора вообще нет.

Читать еще: Сумма в excel по модулю

Номер ответа: 10
Автор ответа:
Воронков Василий

О QuickBasic я написал чтоб товарищи ознакомились с линейным программированием. Такая книга и вправду была, и я её читал в 1992 году — так что никого не хотел задеть.

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

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

Номер ответа: 11
Автор ответа:
С т р а ш ны й С о н

Насчёт обработки ошибок: если в Advanced Optimizations установить флажки, никакой GoTo уже не поможет. Я ошибки не обрабатываю, а ПРЕДОТВРАЩАЮ.

Номер ответа: 12
Автор ответа:
Alex3

Есть ошибки, которые нельзя предотвратить! «On Error Resume Next» Forever?

Номер ответа: 13
Автор ответа:
С т р а ш ны й С о н

Например какие?

Номер ответа: 14
Автор ответа:
Alex3

Удаление занятого файла, не дискеты в дисководе, не подключен сетевой диск, ошибки доступа, соединения, unknown errors и много чего другого.

Вообще глупо отрицать необходимость обработчика ошибок, а он в VB реализуется только с помощью goto.

Возвращаемый индекс элемента в массиве Excel VBA

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

Кажется, нет никакого ресурса, показывающего мне, как без превращения массива в диапазон на листе. Это кажется немного сложным. Возможно ли это с VBA?

Создан 11 авг. 11 2011-08-11 19:06:11 H3lue

Обновлено показать, используя Match (с .index), чтобы найти значение в размерности двумерного массива:

EDIT: он стоит здесь, иллюстрирующую, что @ARich отметил, что использование Index() для среза массива имеет ужасную производительность, если вы делаете это в цикле.

При тестировании (код ниже) подход Index() почти в 2000 раз медленнее, чем использование вложенного цикла.

и он работает! +1 Я действительно не знал, что можно использовать этот метод совпадения совпадений в массиве VBA! – Patrick Honorez 11 авг. 11 2011-08-11 20:26:51

Многие из функций листа Excel имеют аналогичную форму, доступную через Application.WorksheetFunction.[FunctionName] Обратите внимание, что если вы отпустите часть WorksheetFunction (как в моем примере), тогда возвращаемое значение функции может быть проверено с помощью IsError(). Если вы включаете часть WorksheetFunction, то (например), где Match() не находит совпадение, она выдает ошибку, которую вам нужно поймать с помощью обработчика ошибок. – Tim Williams 11 авг. 11 2011-08-11 20:55:06

Аккуратно! Совместим ли также с многомерными массивами? – aevanko 11 авг. 11 2011-08-11 23:57:04

Спасибо за обновление 🙂 – aevanko 12 авг. 11 2011-08-12 12:02:40

Я использовал эту функцию для другой программы, которую я пишу, и по какой-то причине я получаю ошибку несоответствия типа, хотя первое значение является целым числом, а второе значение представляет собой массив, содержащий целые числа. Есть ли причина, почему это произойдет? – H3lue 31 авг. 11 2011-08-31 23:26:00

@ H3lue Трудно сказать, в чем проблема, без фактического кода. Плюс вы даже не упоминаете, какую версию вы пытаетесь использовать . – Tim Williams 31 авг. 11 2011-08-31 23:29:30

@TimWilliams Простите, что это произошло после такого долгого времени . Я просто хотел указать будущим читателям, что в некоторых тестах, которые я сделал, для возврата ссылки «строка», такой как метод «Матч/Индекс» выше, значительно быстрее использовать регулярный цикл «Do While» (примерно на 530 процентов быстрее). Метод, описанный выше, по-прежнему действительно полезен в определенных ситуациях, но если время имеет существенное значение, я рекомендую использовать цикл. – ARich 02 ноя. 13 2013-11-02 07:01:17

Следует также упомянуть, что я тестировал только метод «Матч/Индекс» в 2D-массиве; Я не тестировал пример «Match» в массиве 1D, как показано выше. – ARich 02 ноя. 13 2013-11-02 07:05:07

Да, это не взлом производительности. Я не думаю, что это будет быстрее в массиве 1d. – Tim Williams 02 ноя. 13 2013-11-02 16:27:16

Есть ли способ использовать функцию Match с некоторым допуском ошибки? Я хотел бы использовать это для нахождения индекса элемента в массиве чисел с плавающей запятой. – Hans 26 июн. 15 2015-06-26 04:44:03

Это вы что искали?

Создан 11 авг. 11 2011-08-11 20:14:49 Jon49

Вот еще один способ:

Создан 11 авг. 11 2011-08-11 20:18:14 Paul McLain

петля, чтобы найти значение? – egidiocs 21 май. 14 2014-05-21 23:04:13

максимально быстрое исполнение для целых чисел (как испытано ниже прив)

фрагмент, позволяет проверить предположение, что передача по ссылке в качестве аргумента означает что-то. (Ответ нет), чтобы использовать его заменить MyList и MyValue ваших имен переменных:

доказать точку я сделал несколько тестов

здесь не результаты:

класс с именем PerformanceMonitor

Плохая производительность с использованием цикла . – Holene 09 ноя. 15 2015-11-09 08:59:47

Я думаю, что плохая производительность — это использование варианта в качестве аргументов. по причине prefetcher эффект. т.е. если все память может быть прочитана заранее. как и все переменные, одинаковы и читаются, чтобы они хорошо работали. если он скачет в ячейках памяти, использование ссылок может работать медленнее. каждый раз, когда он перескакивает через ссылку, он снижает производительность на o (1). для многих ссылок это подобно (o (1) + o (1) + o (1) + o (1)) * nloop. – Shimon Doodkin 09 ноя. 15 2015-11-09 20:43:47

вариант — это формат инкапсуляции. OLE-объекты, такие как bstr и безопасный массив, обычно являются ссылками вне процесса в системной памяти. и распределяется динамически. в разных положениях в памяти. и безопасный массив легко может быть массивом ссылок. и варианты также могут быть ссылкой на ссылку. поэтому он должен быть медленным по определению. Я предполагаю, что функции excel взламывают систему и оптимизированы для этого типа проблем, и, чтобы быть быстрее, как-то игнорировать несколько ссылок и проверок, когда это возможно – Shimon Doodkin 09 ноя. 15 2015-11-09 20:48:42

Читать еще: Excel выравнивание ячеек по содержимому

Я думаю, возможно, это может работать быстрее, если оно будет использовать несколько ссылок меньше. например, если аргументы будут простыми типа типа integer. и переменные не будут ссылаться как массив byref. но byval для них является локальной копией, а не ссылкой (изменить тип аргумента от варианта до целого, а byref — byval) также, возможно, не использовать функцию, а фрагмент в каждом месте – Shimon Doodkin 09 ноя. 15 2015-11-09 20:52:31

Я сделал pef-тест. на самом деле производительность очень хорошая. потому что использовать цикл и не использовать функцию excel. префиксное тестирование показывает также, что для функции есть ссылка на ссылочную стоимость доступа, а также для каждого варианта в цикле. также кажется, что сравнение с excel делает некоторое копирование данных и, возможно, конвертирование в Range. thta является дорогостоящим с точки зрения производительности – Shimon Doodkin 09 ноя. 15 2015-11-09 22:23:34

Проголосовать за меня за класс контроля производительности – nkatsar 01 июн. 17 2017-06-01 18:16:36

Поиск в массиве в excel vba

как мне найти в масиве байт последовательность байт, но быстрым способом, мой метод маленька тормозащий

For l = 0 To FileSize — Len(SearchText)
If exitsrch = True Then GoTo ExSrch

Searched = True
For l1 = 0 To 3 »Len(SearchText)
If Chr(FileArr(l + l1)) <> Mid(SearchText, l1 + 1, 1) Then Searched = False
Next l1

If Searched = True Then
Images(SRpic) = l
SRpic = SRpic + 1
End If

Номер ответа: 1
Автор ответа:
Воронков Василий

Номер ответа: 2
Автор ответа:
vig11

в первом For на переменную.

Dim i As Integer

i = FileSize — Len(SearchText)

If Searched = True Then

If Searched Then

For l1 = 0 To 3 »Len(SearchText)
If Chr(FileArr(l + l1)) <> Mid(SearchText, l1 + 1, 1) Then

For l1 = 0 To 3 »Len(SearchText)
If StrComp(Chr(FileArr(l + l1)) , Mid(SearchText, l1 + 1, 1), vbBinaryCompare)<>0 Then

Номер ответа: 3
Автор ответа:
Alex3

Откуда такая уверенность, что GOTO самая тормозящая вещь во всех ЯВУ? По моему это одна из самых быстрых команд!

Номер ответа: 4
Автор ответа:
S e M a

Номер ответа: 5
Автор ответа:
Воронков Василий

ЗЫ. Для c длиной файла лучше использовать EOF

While not EOF(1) ‘ if opened as #1

Номер ответа: 6
Автор ответа:
С т р а ш ны й С о н

Совет для всех программистов на Visual Basic:

Номер ответа: 7
Автор ответа:
Alex3

В качестве демонстрации:

Создаем две кнопочки, добавляем код, КОПИЛИРУЕМ.

Обладатели медленных машин могут убрать нолики, если надо.

Private Sub Command1_Click()
Dim i As Long, t As Double
t = Timer
mBegin:

Private Sub Command2_Click()
Dim i As Long, t As Double
t = Timer
For i = 0 To 1000000000

Next i
MsgBox Timer — t
End Sub

Номер ответа: 8
Автор ответа:
Воронков Василий

зы. пузырьковый for/next штука тоже не особо скоростная

Номер ответа: 9
Автор ответа:
Alex3

Во-первых, я не агитирую за использование GoTo, я лишь опроверг мнение, что он «знач. медленнее».

Номер ответа: 10
Автор ответа:
Воронков Василий

Номер ответа: 11
Автор ответа:
С т р а ш ны й С о н

Номер ответа: 12
Автор ответа:
Alex3

Есть ошибки, которые нельзя предотвратить! «On Error Resume Next» Forever?

Номер ответа: 13
Автор ответа:
С т р а ш ны й С о н

Например какие?

Номер ответа: 14
Автор ответа:
Alex3

Вообще глупо отрицать необходимость обработчика ошибок, а он в VB реализуется только с помощью goto.