я в 8м сообщении написал, что изначально в заголовке это было (про ВПР и подсчёт дублей), но Виктор увидел ответы про сортировку и удалил "лишнее" из заголовка. А лишним, наверное, было именно упоминание "методов" в заголовке…
Господа, а у меня одного сайт глючит? Панель с форматированием не активна и странно отправляются комментарии к посту…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
По этой теме: что теперь делать? Накрутил-навертел, теперь разбираться... Говорено-переговорено: описывать не пути решения, а ЗА-ДА-ЧУ! Может, правильнее создать новую тему, а эта пусть о сортировке остается.
по работе форума: спасибо - смотрю)) по этой теме: давайте оставим, тут уже много данных именно по задачам (по ВПР пока что). Ссылку на тему о сортировке массивов (та, которая потом в копилку ушла) я разместил в шапке. Да и поддержать такую тему я не смогу(( а тут хоть что-то могу сам написать или попытаться разобраться в предложенных вариантах…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, дело в том, что всегда найдутся люди, которые не разберутся в чьем-то алгоритме. И я не являюсь исключением, как и многие другие.... Дело в том, что в чужом коде разбираться дело не благодарное... Кроме того, работа, например, в команде разработчиков весьма может замедлить реализацию конечного продукта из-за, именно, такого подхода (специфика однозначно скажется на времени). И здесь любой из самых крутых профессионалов может оказаться жертвой вилки между производительностью и локальностью поставленной задачи в рамка временнОй эффективности. Вопрос несколько в другом, например, Вы написали "использование ВПР", как одно из условий, а я, например, никогда не использую вмонтированные функции (ну это только по отношению к моему пониманию)... Но мне показалось, что тема, в процессе своего развития, не без Вашего участия или с ним, вылетает за заявленные границы. Возможно моя философия не совершенна, так же как и советы по использованию значений по умолчанию в VBA для объектов (предлагается как ускорение процесса в рамках использования, например, использования значений по умолчанию (например, if err, работает быстрее, чем if err.number))... Ведь это не так (а таких советов очень много на просторах форумов, включая темы о скорости работы, например, с классами, где анализ ситуации не достиг своего апогея). Хотя исключить некоторые варианты я не могу. Это зависит не от нас, а от тех кто писал тот или иной код. Посмотрите на код в теме Anchoret (oldy7). Там тоже осталось не мало отдушин для оптимизации, хотя не каждое решение будет являться универсальным. Та или иная оптимизация влечет за собой жертвы. Ведь только конкретное решение в рамках поставленной задачи приводит к реальной эффективности, но в рамках поставленной ВАМИ задачи. И если Вы хотите получить реальный выхлоп, то стоит перед постановкой задачи, хотя бы, задать границы Ваших изысканий (что является, по сути Вашим пониманием проблемы, чем и стоит поделиться с коллегами).... И вот в этом месте начинается конкретизация, который мы все пытаемся подсознательно избежать. Может быть мы хотим решить те или иные проблемы раз и навсегда, но мир, который мы познаем намного более разнообразен и нет панацеи, которая все разрешит. Простите за излишне философский подход...
спасибо вам большое! как мне кажется, вы всё по делу написали. Что-то подобное хотел донести и ZVI. Попробую уточнить: 1. "ВПР": хотел сделать функцию, для "подтягивания" значений по ключу из другой таблицы, при этом срабатывала бы для ключей длиннее 255 символов (ограничение Match). При этом, предложенные варианты по бинарному поиску сложны для моего полного понимания (надеюсь, что пока). Те топорные варианты, которые я предложил хоть и долгие, но задачу решают и мне полностью прозрачны для понимание и редактирования (это к вопросу приоритетов) 2. "Подсчёт дубликатов": сейчас решаю сортировкой и циклом со счётчиком по диапазону. Хотел отойти от изменений на листе (сортировка). Написать функцию (предположительно на массивах и словарях), которая бы возвращала двумерный массив типа "уникальные значения диапазона-количество вхождений". Таким образом, можно бы было просто передать этой функции диапазон, а потом по возвращённому массиву выбирать или список уникальных, или список дублей и т.д.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Function sTxtFinder(txt$, mass(), ByVal n%, SResult&()) As Boolean
'Параметры функции:
'- текстовая строка для поиска
'- массив, в котором этот поиск осуществляется
'- столбец массива (где ищем)
'- результирующий массив индексов основного массива, где мы что-то нашли
'---------------------------------------------------
Dim ll&, hh&, cc As Byte, cc1 As Byte, a&, x&, dt1$
ReDim SResult(1 To 1) 'одномерный массив индексов
If txt = "" Then sTxtFinder = False: Exit Function
cc = UChr(Asc(txt))'берем первый символ поисковый строки и приводим его к верхнему регистру
If Len(txt) > 1 Then cc1 = UChr(Asc(Mid(txt, 2, 1)))'теперь второй
ll = LBound(mass, 1): hh = UBound(mass, 1): x = 1
For a = ll To hh
If Len(mass(a, n)) = Len(txt) Then
dt1 = mass(a, n)
If UChr(Asc(dt1)) = cc Then'сравниваем первые символы поисковой строки со строкой в массиве
If Len(dt1) = 1 Then GoTo lp1
If UChr(Asc(Mid(dt1, 2, 1))) = cc1 Then'вторые...
If StrComp(txt, dt1, 1) = 0 Then'сравнение без учета регистра
lp1: ReDim Preserve SResult(1 To x)
SResult(x) = a: x = x + 1
End If
End If
End If
End If
Next
If SResult(1) = 0 Then sTxtFinder = False Else sTxtFinder = True
End Function
'вся разница между верхним и нижним регистром символов в 1/0 пятого бита
'в данном случае мы обнуляем этот бит в коде символа
Function UChr(ByVal ch As Byte) As Byte'приведение кода символа к его (символа) верхнему регистру
Select Case ch
Case 97 To 122: UChr = ch Xor 32: Exit Function
Case 224 To 255: UChr = ch Xor 32: Exit Function
End Select
UChr = ch
End Function
Ну и попутно остальные функции по битовым операциям с кодами символов:
Скрытый текст
Код
Function TChr(ByVal ch As Byte) As Byte
Select Case ch
Case 65 To 90: TChr = ch Xor 32: Exit Function
Case 97 To 122: TChr = ch Xor 32: Exit Function
Case 192 To 255: TChr = ch Xor 32: Exit Function
End Select
TChr = ch
End Function
Function LChr(ByVal ch As Byte) As Byte
Select Case ch
Case 65 To 90: LChr = ch Xor 32: Exit Function
Case 192 To 223: LChr = ch Xor 32: Exit Function
End Select
LChr = ch
End Function
блин((( надеюсь только на комментирование кода(( если сможете, то надежда разобраться - есть)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: редложенные варианты по бинарному поиску сложны для моего полного понимания
бинарный поиск, по своей сущности, наиболее прост, чем другие варианты, обремененные дополнительными условиями, как таковой, и не надо смотреть на терминологию, а вполне пора разобраться с его сущностью, а здесь yandex и google рулит (прошу не считать, если это возможно, данную рекомендацию, как выпендрос... ). Ведь это, по сути сравнение двух значений без учета регистра и не более... Понятие "сравнение", как мне кажется наиболее понятно... Кроме того бинарный подход обеспечивает максимальную скорость.
"про без учёта регистра" вы имели ввиду, что именно? Ведь коды символов для разных регистров одной буквы - разные… про гугл учту))) не первый раз сёрфить)) а сам двоичный поиск мне симпатичен ввиду скорости и "внутренней" простоты ))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
ZVI, думаю что 10к строк в справочнике и 50к строк в базе более чем достаточно. На самом деле обычно намного меньше и хотелось бы знать примерные "границы", после увеличения которых нужен другой способ. При больших объёмах в таких функциях на листе нет смысла - пересчёт (даже ручной) будет очень долгим и проще ВПРить макросом по кнопке или же связывать таблицы, например, через PQ, чем я последовательно сейчас занимаюсь
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Для Вас я это написал не более чем аргумент, т.к. вижу, как мне кажется, Ваш подход к полученной информации. Вот, например, простой вопрос: почему StrComp(строка1,строка2), будет быстрей чем StrComp(строка1,строка2,1)?
AAF, прочитал))) 0 - по умолчанию это бинарное сравнение, а 1 это vbTextCompare))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
AAF. потому что vbTextCompare чувствителен к регистру, а бинарный - нет… (только что прочитал) Вот теперь я запутался)) я думал, что бинарный посимвольно проверяет…по кодам то есть ZVI, спасибо вам большое! Понимаю, что для больших объёмов нужны совершенно другие решения… Anchoret, ну да - тут не огромедные объёмы)) поэтому главное мне разобраться)
УРРРАААААА сайт заработал в полную мощь!)))) опять пропало всё
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, так вот прежде чем запутаться следует ПОПЫТАТЬСЯ воспринять и проанализировать полученную информацию логическим путем, используя его как самый помехоустойчивый принцип. Как Вы писали каждый символ имеет свой неповторимый код, и код, который назначен "A" и "a" имеет разное значение и для того чтоб посчитать их равными (CompareText=1), требуется дополнительное сравнение, что требует доп. времени, а время сравнения String занимает не мало времени, именно поэтому бинарное сравнение рулит по времени! И почему бы это не использовать? Но все зависит от поставленной задачи, ведь иногда данная очевидность теряет свое преимущество, например, оно весьма зависит от алгоритма, но это уже другой порядок восприятия...
AAF, если мне только что-то было непонятно в кодах от Anchoret, то да… Но там совершенно новый для меня метод, осваивание которого только впереди. Например сразу такой вопрос: как же тогда учитывать регистр при бинарном сравнении/поиске?… Ну и масса других вопросов, вероятно, появится, пока не изучу матчасть. После этого со мной хоть можно будет говорить на более-менее одном языке))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: в кодах от Anchoret, то да… Но там совершенно новый для меня
Методы разные, а фундаментальные принципы одни... Не нужно это возводить в ранг чего-то непостижимого или само собой разумеющегося. Все это не более чем решение некой понятной человечеству задачи с использованием уже имеющегося под рукой чемоданчика с приготовленными инструментами...
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Предлагаю простое решение, заточенное под конкретный пример из сообщения #1. Цель - показать, как несложным кодом получить эффективное решение, поэтому на универсальности аналога функции ВПР не заморачиваемся. Кодовое имя листа базы - Sh01, а листа справочника - Sh02. Эти имена задаются в поле (Name) если в VBE стать на модуль листа и нажать F4 (см. первую строчку свойств листа). Алгоритм: 1. Функция ВПР1(ЧтоИскать) будет по ключу ЧтоИскать брать значение из заранее подготовленного словаря Dic. Если при вызове ВПР1 словарь окажется пустым (это происходит при редактировании кода или при нажатии кнопки сброса в VBE), то он однократно создается и заполняется заново, а затем используется готовенький в остальных быстрых вызовах ВПР1. 2. Словарь берет в качестве ключей значения с листа справочника Sh02 из столбца таблицы "справочник" под номером ColFind, а значения элементов словаря - из столбца ColGet. Заполнение словаря завершается вызовом пересчета листа Sh01 базы. 3. В коде листа справочника записан код обновления справочника при деактивации листа. Там, по сути, одна строка вызова: UpdateDic Тем самым справочник можно набивать данными без каких-либо тормозов, а обновление словаря c пересчетом листа базы произойдет однократно после ухода с листа справочника. При вводе новых данных или редактировании старых на листе Sh01 базы пользовательская функция ВПР1 будет просто брать готовые данные из словаря.
Недостаток такого подхода - код написан под конкретные кодовые имена листов, имя таблицы справочника, а номера столбцов таблицы справочника жестко прописаны константами в коде. Тем не менее, всё достаточно наглядно, поменять не сложно и работает без лишних тормозов. Пример с кодом приложен. проверяйте на реальных или тестовых данных. Если что-то не понятно или не устраивает - напишите.
ZVI, ЭТО. ПРОСТО. ОФИГЕННО. Огромное вам спасибо!!! действительно, очень простой код - легко могу всё менять))) с именами листов разобрался, скорость просто супер. Да - это не универсальная функция, но отличный вариант для больших таблиц. Очень понравилась реализация "однократной" загрузки справочника - буду пробовать реализовать в других проектах))
Ещё раз большое спасибо!!!
P.S.: я же могу вместо
Код
With Sh02.Range("справочник")
a() = .Columns(ColFind).Value
b() = .Columns(ColGet).Value
End With
таким образом, макрос же будет работать в любом файле, где есть эти 2 именованных диапазона? кстати, использование диапазона через "внутреннее" имя листа Sh_01 вместо Sheets("база") вижу впервые)) буду юзать теперь — таким образом можно много проверок на переименования листа пользователями убрать
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Очень хорошо, что легко прикрутили код в свою книгу! При именованные диапазоны - хороший вариант. А вот .Value2 я бы не стал использовать, т.к. хоть это и чуть быстрее, но даты превращает в числа, т.е. теряется признак даты и с поиском дат будут проблемы.
Можно пойти немного дальше. Подчеркну некоторые особенности предложенного метода: 1. В поиске чисел при ошибках округления в 15-м значащем знаке обычная ВПР ошибается, а данная ВПР1 - нет 2. Обычная ВПР различает числовые значения и такие же, но текстовые значения, а ВПР1 не различает. 3. Словарь обновляется всякий раз при уходе с листа справочника По п.1 - это преимущество, а п.п. 2 и 3 это недостатки. Недостатки по п 2 и п.3 легко устранить - см. приложенный обновленный вариант.
ZVI, спасибо за обновление! Учусь)) по п.3: заметил прикольную вещь с "флагом" на событии листа - тип, если что-то меняли, то обновить словари при смене листа, а, если нет, то не менять))) всё гениальное просто в данном случае, конечно, само обновление занимает милисекунды, однако метод я подчеркнул - спасибо!
Цитата
ZVI написал: 2. Обычная ВПР различает числовые значения и такие же, но текстовые значения, а ВПР1 не различает
вот тут спорный момент: с одной стороны, excel воспринимает эти данные, конечно, по-разному, однако хранит в ячейке по сути одно и то же (как я понял, вы сцепляете "ничего" с числом, чтобы этого добиться). Скорее всего, если число представлено как текст, то это косяк юзера и нужно не различать их - как вы считаете? Безусловно, в плане обучения разным методам я подчеркнул этот момент для себя и запомню - спасибо! Кстати, с Match и Find такая же штука - Find не различает (вроде).
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: если число представлено как текст, то это косяк юзера
Обычная ВПР не найдет число в виде текста в диапазоне поиска чисел. Считаю это нормальным, хотя бы из-за возможным различий в разделителе десятичных разрядов (он может быть и не системным). Но если кому-то нужно иначе, то можно использовать изначальный код.
Числа в виде текста зачастую это не косяк пользователя, а идентификаторы номенклатур в справочниках, которые начинаются с нулей типа такого 00000000111. Так что ИМХО ZVI абсолютно прав что учел это в функции.
да - я в курсе про эту особенность - она-то мне и нравится, потому что даты "чистые" - именно такие, какими их видит xl. Лично я осуществляю поиск как правило в однородных данных, а для смешанных буду применять .Value (ну или оставлю .Value по умолчанию). Спасибо за урок!
Код ZVI
Код
Option Explicit
' ============================================================================================================================
' Автор: ZVI:2018-02-15
' Тема на форуме: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=101190&TITLE_SEO=101190-massivy-i-slovari-sortirovka-massivov&MID=837940#message837940
' ============================================================================================================================
' Словарь справочника
' ============================================================================================================================
Public ZVI_Dic As Object
Public Function ZVI_FastVlookUp(ЧтоИскать)
Dim txt$
If ZVI_Dic Is Nothing Then Application.Run "ZVI_UpdateDic" ' наполнить словарь, если он пустой
txt = ЧтоИскать
'If VarType(ЧтоИскать) = vbString Then txt = Chr$(1) & ЧтоИскать Else txt = ЧтоИскать 'Земенить верхнюю строку на эту, чтобы функция различала число и текст-как-число
With ZVI_Dic
If .Exists(txt) Then
ZVI_FastVlookUp = .Item(txt) 'Если найдено - взять из словаря
Else
ZVI_FastVlookUp = "" ' Если не найдено, то выдать пустое значение или = CVErr(2042), если нужно #Н/Д
End If
End With
End Function
' ============================================================================================================================
' Процедура наполнения/обновления словаря. Запуск вручную или кодом
' ============================================================================================================================
' Пример кода автообновления справочника. Для модуля листа справочника
' Option Explicit
' Dim IsChanged As Boolean ' Признак изменения листа
' Private Sub Worksheet_Change(ByVal Target As Range)
' IsChanged = True
' End Sub
' Private Sub Worksheet_Deactivate()
' If IsChanged Then ZVI_UpdateDic 'При выходе - обновить справочник (c пересчетом листа базы)
' IsChanged = False
' End Sub
' ============================================================================================================================
Sub ZVI_UpdateDic() 'Обновление словаря по данным справочника
Const ColFind = 1 'Столбец справочника, где искать (не нужно при использовании именованных диапазонов)
Const ColGet = 2 'Столбец справочника, откуда брать (не нужно при использовании именованных диапазонов)
Dim a(), b()
Dim i&
Dim txt$
' Проинициализировать словарь. Текстовое сравнение с учётом регистра
Set ZVI_Dic = CreateObject("Scripting.Dictionary"): ZVI_Dic.CompareMode = 1
' Скопировать справочные данные столбцов поиска и результата в массивы
' Если указаны константы с номерами полей справочника
With ZVI_List.Range("справочник")
a() = .Columns(ColFind).Value
b() = .Columns(ColGet).Value
End With
' Если используются именованные диапазоны
'a() = [_ZVI_FastVlookUp_Find].Value
'b() = [_ZVI_FastVlookUp_Get].Value
' Заполнить словарь
With ZVI_Dic
For i = 1 To UBound(a)
txt = a(i, 1)
If Len(txt) > 0 Then
'If VarType(a(i, 1)) = vbString Then txt = Chr$(1) & txt 'Раскомментировать, чтобы функция различала число и текст-как-число
.Item(txt) = b(i, 1)
End If
Next i
End With
'ZVI_base.Calculate 'Раскомментировать, для автопересчёта листа-базы
End Sub
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Это уже Ваш код, но в нем от обилия "ZVI" рябит немного в глазах Копирайты здесь меня не волнуют (public domain же). В комментарии я обычно один раз упоминаю кто, когда и по какой ссылке нарисовал код, это чисто для удобства, чтобы потом легче вспомнить и найти источник. Обращу внимание на то, что из-за переименования процедуры обновления словаря вызов из кода листа справочника уже неправильный - попробуйте скомпилировать код через Debug - Compile VBAProject. А в принципе, переименовывайте, как хотите, конечно.