Работаю с большим объемом однотипных данных. Стоит задача, чтобы к каждому уникальному значению присвоить 2 параметра.
Частично задачу реализовал, но есть 2 проблемы, с которыми самостоятельно справиться мне не под силу (((
1. Нашел в сети макрос, который вычленяет уникальные значения и выводит их в отдельный столбец. Работает вроде как исправно, но при большом объеме данных перегружает процессор i7 вешает комп ((( Буду признателен, если кто-нибудь предложит более оптимизированную альтернативу ему.
Скрытый текст
Код
Function listUnique(rng As Range) As Variant
Dim val As String
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim allocationChunk As Integer
Dim uniqueSize As Integer
Dim r As Long
Dim lLastRow As Long
lLastRow = rng.End(xlDown).Row
elementSize = 1
unqueSize = 0
distance = Range(Application.Caller.Address).Row - rng.Row
If distance <> 0 Then
If Cells(Range(Application.Caller.Address).Row - 1, Range(Application.Caller.Address).Column).Value = "" Then
listUnique = ""
Exit Function
End If
End If
For r = 1 To lLastRow
val = rng.Cells(r)
If val <> "" Then
newElement = True
For i = 1 To elementSize - 1 Step 1
If elements(i - 1) = val Then
newElement = False
Exit For
End If
Next i
If newElement Then
uniqueSize = uniqueSize + 1
If uniqueSize >= elementSize Then
elementSize = elementSize * 2
ReDim Preserve elements(elementSize - 1)
End If
elements(uniqueSize - 1) = val
End If
End If
Next
If distance < uniqueSize Then
listUnique = elements(distance)
Else
listUnique = ""
End If
End Function
2. Формулой реализована подстановка параметров к каждому уникальному значению. С решением помогли тут. Это работает. Но когда пытаюсь аналогичным способом подставить данные - выходит #ССЫЛКА!.
И наверное 3-я проблема, которая плавающая, иногда ячейка E3 оказывается пустой, хотя в E2, E4 и далее есть данные. Не могу сейчас получить пустоту, но пару раз проскакивала. Грешу на макрос или затуп компа.
Прикрепляю пример моего файла https://yadi.sk/d/p_kZ1x6v2CxNHg. Логика работы такая, столбец А содержит данные, столбец E - только уникальные из столбца А. Столбцы F и G -параметры, которые подставляются в столбцы B-C на основе уникальных значений. Задача, связать столбцы E и K, для подстановки параметров из словаря.
Благодарю.
Много букв, наверное ничего не понятно написал, но как смог - так и объяснил свою проблему.
Ни понятно зачем вам список уникальных значений? Если в столбцы "B" "C" нужно подставить данные из столбцов "L" "M" то это можно сделать без уникальных и вообще без формул.
Перечитайте правила: уже давно 300. И потенциальным помощникам не нужен ВЕСЬ Ваш рабочий файл. Достаточно НЕБОЛЬШОГО примера строк на 50 в аналогичной структуре.
Андрей_26, из этих уникальных значений я и формирую словарь для однотипных данных. Если их нет в словаре, то я буду видеть, для каких значений их необходимо добавить. Как-то так.
Alez, а файле сплошные ошибки из-за формул. Поменяйте файл - замените формулы на значения. Для этого выделите диапазон, скопируйте, затем правый кллик - специальная вставка - значения.
Юрий М, так я с этими ошибками и пришел. В столбцах F и G выделил значения желтым, их задал вручную и они подставляются. Но мне надо связать их со словарем, а при связки выдается ошибка ((( Поэтому и поднял эту тему.
Alez написал: Столбцы F и G -параметры, которые подставляются в столбцы B-C
В этих столбцах я вижу только два значения: (в первой строке) томат и свежий. Остальные строки с ошибкой. Что подставлять? И не лучше бы создавать на отдельном листе новую таблицу из трёх столбцов?
Юрий М, слова "томат" и "свежий" должны тянуться из столбцов L и M. Но не тянутся, поэтому я их задал руками для примера, чтобы было понятно, какие данные туда должны идти. Но видимо я всех этим только запутал (((
Сложная у меня конструкция вырисовывается, но логика такая, к уникальным параметрам подставляем значения из словаря, если они имеются, потом из уникальных разбрасываем параметры по основному списку.
Alez написал: "томат" и "свежий" должны тянуться из столбцов L и M.
Теперь ещё два столбца добавились, о которых раньше не говорилось ничего. Получается, что у Вас ещё до этапа подстановки в столбцы В и С нужно решить проблему с формулами в столбцах F и G, а уже потом браться за уникальные, иначе нечего подставлять в В и С. Но к теме про поиск уникальных это какое отношение имеет?
Hugo, все работает отлично. Спасибо большое за решение.
Остается вопрос только с самим макросом. Уж очень сильно он нагружает проц, работает в 12 потоках ((( Может быть у кого-то есть менее требовательный код ?
Есть UDF с таким функционалом на коллекции, и есть на словаре. Будет ли быстрее - может быть. Но нужно понимать что там в каждой ячейке отрабатывает такая UDF, что при большом количестве ячеек долго. Но в принципе если такая таблица обрабатывается одновременно в файле только одна, то можно замутить публичную коллекцию или словарь (одно на всех) - это должно быть быстрее.
У меня данных много бывает, самое большое, что было - это порядка 8000 значений, из них уникальных около 1200, если ничего не путаю. Раньше прописывал ручками через фильтрацию, но пришел к выводу, что время от времени попадаются одни и те же значения, поэтому надо как-то автоматизировать процесс, чтобы не выполнять двойную работу изо дня в день.
Знаю, что есть какие-то надстройки на эксель, но это не мой вариант. Не всегда есть под рукой комп, а устанавливать на чужую машину доп софт не всегда удобно.
Поясню - можно при первом вызове функции создать коллекцию-словарь с этими уникальными значениями, а при повторных вызовах (может их будет тысячи) уже не тратить время на создание словаря, а сразу брать из него значение. Но это в том случае, когда диапазон один для всех. Словарь - это не Ваш словарь, а объект в коде UDF.