Excel. Как найти на листе строку по 2-м ячейкам?

[igor_m]

Как на листе Excel наиболее быстро найти строку, в которой несколько ячеек по значениям удовлетворяют условиям?

Если это 1 ячейка, думаю, самое быстрое: МойЛист.Range("А:A").Find(what:="Значение1", ...)

А если по нескольким столбцам искать? Пусть по двум первым.
Можно, конечно, циклом - но медленно на больших массивах.
For i=1 to ПоследняяСтрока
if cells(i,1).value=Значение1 and cells(i,2).value=Значение2 then
'Вот нашли
end if
next i

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

[pronto]

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

[igor_m]

Тип значений текстовый. Имеются повторяющиеся значения в каждом столбце, но в совокупности дают уникальный идентификатор (либо как результат не найдено соответствия).
Да, может вы и правы намеком - сделать вспомогательный столбец ЯчейкаСтолбца1 & ЯчейкаСтолбца2, делать по нему поиск а потом после обработки очистить значения в этом столбце.
А как еще можно искать по двум текстовым столбцам?
Для бинарного поиска отсортировано должно быть? Мне не хочется трогать эти данные.

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

[pronto]

А сколько всего столбцов? А поиск должен осуществляться только по первым двум столбцам или по любым двум? Если второе, то идея со вспомогательным столбцом отпадает (да и не эффективно это, так как имеет ту же сложность, что и выражение if cells(i,1).value=Значение1 and cells(i,2).value=Значение2 then).
Например, имеется следующа таблица (под буквами подразумеваются значения ячеек):

Код: Выделить всё

   1 2 3 4 5

1  A B C D E
2  F G H I J
3  K L M N O

То тебе нужно связать все пары в строке с номером этой строки:

Код: Выделить всё

AB = 1  BC = 1  CD = 1  DE = 1
AC = 1  BD = 1  CE = 1
AD = 1  BE = 1
AE = 1

FG = 2  GH = 2  HI = 2  IJ = 2
FH = 2  GI = 2  HJ = 2
FI = 2  GJ = 2
FJ = 2

и так далее...

Остаётся придумать, как хранить эти пары. На ум приходит B-Tree, так как это наиболее часто используемый тип индексов и различные его разновидности используются практически всеми СУБД.

[Qwertiy]

А если просто искать только по первому полю при помощи Find, а для найденных ячеек проверять соседей?

Код: Выделить всё

Option Explicit

Function Get23() As Range
    Dim FirstCell As Range, CurCell As Range, NextCell As Range
   
    Set FirstCell = Cells.Find(What:="2", LookIn:=xlValues)
    If FirstCell Is Nothing Then Exit Function
    Set CurCell = FirstCell
   
    Do
        Set NextCell = Cells(CurCell.Row, CurCell.Column + 1)
        If NextCell.Value = "3" Then
            Set Get23 = CurCell
            Exit Function
        End If
        Set CurCell = Cells.FindNext(After:=CurCell)
    Loop Until CurCell.Row = FirstCell.Row And CurCell.Column = FirstCell.Column
End Function

Sub DoIt()
    Dim Cell As Range
    Set Cell = Get23
    If Not (Cell Is Nothing) Then Cell.Activate
End Sub

[igor_m]

Поиск по двум определенным столбцам.
Идея со вспомогательным столбцом относительно правильная. Потому что вспомогательный столбец делается 1 раз, хоть вручную мышкой протянуть и объединить 2 столбца. А вот поиск по нему потом будет 100000 раз. Временные затраты на создание вспомогательного столбца мизерны.
Но может будет играть значение, что строка поиска удлинняется? Может, действительно, сначала искать совпадения первого столбца, а уже внутри нее проверять значения второго столбца.
Придется пробовать.

[Template]

igor_m, Зацените возможности :
1) функций рабочего листа, в нашем случае из категории Работа с базой данных, например, БИЗВЛЕЧЬ.
Конечно для их использования, в т.ч. и программного, необходимы ячейки раб.листа, но ежели вариант с 100000 доп.ячеек не пугает, то несколько ячеек в двух строках, вообче не должно быть проблемой.
2) расширенного фильтра (правда тоже нужны доп.ячейки)
3) SQL запросов

[igor_m]

Давайте еще!

Я просто не пользовался никогда в Excel запросами, фильтрами и функциями работы с базами. В смысле работал именно с базами из Excel, но не с массивами на листах. Не умею

Со своей стороны могу сказать, что хороший результат показал сначала отбор по первому столбцу и только потом по второму. Предварительно на тестовых данных небольшого размера производительность выросла на 39% (ну немного еще подвинтил в других местах). Т.е., я так думаю, что при поиске строк большое значение имеет длина строки - чем меньше длина, тем быстрее ищется. Спасибо Qwertiy за идею, а я чуть было не пошел неверным путем, соединяя ячейки.

[pronto]

С двумя определёнными столбцами, конечно же, проще...

при поиске строк большое значение имеет длина строки - чем меньше длина, тем быстрее ищется

Это правильно, но алгоритмы с «деревьями» почти не чувствительны к объёму сравнения и длине сравниваемой строки. Поэтому, если ты реализуешь один из них, то скорость вырастит в разы, а может и в десятки раз.
Вот с этого можно начать

[Qwertiy]

Ещё можно делать поиск вперёд и чередовать значения.
Подход с деревьями вызывает сомнения. Да, поиск будет выполняться быстро, но построение - нет. А если можно построить один раз и держать в памяти, то лучше использовать словарь и не мудрить.

[pronto]

Тогда нужно будет подключать «Microsoft Scripting Runtime» через «Tools > References...»

Код: Выделить всё

Dim myDIC As Scripting.Dictionary
Set myDIC = CreateObject("Scripting.Dictionary")

....

Set myDIC = Nothing

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

[igor_m]

Всем спасибо. Остановился на Scripting.Dictionary. Отличный результат, в 40 быстрее самого начального варианта. Устраивает.