Страницы: 1
RSS
Золотой ключ. Создание поля уникальных значений.
 
Этот код помогает создавать уникальные ключи во всех данных, но требует небольшого ручного управления.Буду рад конструктивной критике и дополнениям

Прикреплен файл с примером.
Макрос срабатывает при открытии листа книги.

Код
Private Sub Worksheet_Activate()
Dim e As String
ThisWorkbook.Activate            'строка не обязательна если работать в одной книги
Set Tab_1 = Лист1.ListObjects("Таблица1")

y = Tab_1.ListRows.Count         'посчитать количество строк в таблице
x = Tab_1.ListColumns.Count      'посчитать количество строк в таблице

a = Tab_1.ListColumns("Au_key").Range.Column  'Определить № столбца по имени столбца

e = "Уникальное название книги" & y
    For b = 1 To y
        If Tab_1.DataBodyRange(b, a) = "" Then
            Tab_1.DataBodyRange(b, a) = e
            
                For с = 1 To y

                    If Tab_1.DataBodyRange(с, a) = Tab_1.DataBodyRange(b, a) Then
                        MsgBox "найден дубликат"
                            For d = 1 To y
                                If Tab_1.DataBodyRange(d, a) = Tab_1.DataBodyRange(b, a) Then
                                    If d = b Then
                                     GoTo перенос2
                                    End If
                                    Tab_1.DataBodyRange(b, a) = e & "_" & d
                                    EXitFor
перенос2:
                                End If
                            Next d
                    End If
                Next с
        End If
    Next b
End Sub
Изменено: Kor - 06.08.2019 23:59:23 (заменил 1 GoTo на EXitFor)
 
Цитата
Kor написал:
Буду рад конструктивной критике
тогда держите)
1. непонятно на кой это нужно)
2. срабатывает по активации листа - то есть если работать на одном листе, то никаких сообщений и не увидишь.
3. если нет пустот - дубликаты не считает.
4. попробуйте вставить 10 пустих строк и активируйте макрос)
5. GoTo  можно избежать, используя более логичный порядок строк в коде и оператор EXitFor

это так, навскидку)
 
Dima S, Благодарю =)
1) при использовании Power Bi, Power Query, Power Pivot даннная схема сильно упрощает жизнь. и поэтому все упаковано в динамическую таблицу  
2) Это просто пример и можно сделать не на активацию листа, а на любое событие книги.
3) Отсутствие пустот предполагает, что лезть туда уже не стоит и тратить на это ресурсы тоже сомнительно, хотя здесь есть над чем подумать. Имеются свои плюсы и минусы.
4) MsgBox легко удаляются, а так все работает без сбоев и существующая схема проявляет достаточную надежность, чтобы обеспечить создание уникальных полей.
5) выходить из цикла нельзя. т.к. останавливается дальнейшая проверка, а нужно только пропусить проверку текущей (бывшей пустой)  строки и запустить провеку с первой строки.

Так как это динамиеская таблица и работать с ней иногда опасно, то при открытии книги рекомендую отключать протягивание формул, дабы ненавредить данным, а то все данные перезатираются при легком движении руки (все прям весь столбец).
Изменено: Kor - 05.08.2019 23:29:08
 
Если честно , то не понял в чем вообще фишка выполнения данной процедуры, ну нашли мы дубль, ну сообщили это пользователю, и что дальше.
Если описать задачу, то думаю можно проще код написать, как было сказано выше без всяких GoTo. Лично мое мнение. Но нужно понимание сути этого кода, как написал выше, я ее не понял.
"Все гениальное просто, а все простое гениально!!!"
 
Цитата
Kor написал:
выходить из цикла нельзя
Но с помощью GoTo вы именно это и делаете) причем по-английски- не прощаясь)
Каким образом это помогает Power-ам мне тоже не очень понятно.
Но дело ваше.
 
торможу. Да goto действительно можно менять, но только 1 (2 нужен для пропуска строки, чтобы не определяла дубль как саму сравниваюмую строку).
При работе с данными критично иметь ключевое поле .т.к. с помощью него становится возможно связать несколько разнородных таблиц с минмальным колиеством общих полей.
Дата, Деньги, Вес, Название таблицы (откуда берем данные). Объединив все эти таблицы в одно большое полотно мы можем обращаться с этого полотна обратно в исходники. К сожадлению это уже не совсем отностится к теме сайта. Создание ключей для этого всего - это как раз работа с VBA. Конечно же описанные заачи должены решать базы данных. но блин их не всегда дают, а работать быстро здесь и сейас нужно. Стандартного способа создание клюевого поля я не нашел и мне пришлось писать код. при добавлении строк в середину ключ на прежних полях не меняется, то позволяет отследить изменения.
https://zen.yandex.ru/media/id/5c7acf0ae8513400b2ceeccf/power-bi-vajno-byt-poniatnym-5d00220342211f0... - это примерно то что должно полуиться в итоге.
Изменено: Kor - 07.08.2019 00:04:55 (орфография)
 
Цитата
Kor написал:
но только 1
не только.
но все равно непонятно) по моему вы изобретаете велосипед.
Цитата
Nordheim написал:
Если описать задачу, то думаю можно проще код написать
 
Честно пытался понять высший смысл этого макроса. Увы, не дано.
Если просто по коду, то значения на уникальность проще всего проверять Словарем или Коллекцией
Согласие есть продукт при полном непротивлении сторон
 
Тут, скорей всего нужно описание задачи, а не код разбирать, суть которого я не понял(о чем писал ранее)  :D
"Все гениальное просто, а все простое гениально!!!"
 
Попробуем угадать. :D
Допустим так - 'Необходимо создавать уникальный ключ, соответствующий определенному шаблону, при возникновении какого-то события'
Может так пойдет?
При вводе данных в любой столбец Умной таблицы (кроме первого, он недоступен для ввода), в первом столбце будет генерироваться ключ  
Изменено: Sanja - 07.08.2019 10:55:35
Согласие есть продукт при полном непротивлении сторон
 
OFF
Цитата
Kor: Золотой ключ
а где Буратино?  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,Это я. из ДУБА =)
 
Sanja, в случае вставки строки ключ уже не уникальный (дураковоустойчивость низкая), а блокировать доступ к 1ому столбцу это конено супер, я даже не думал об этом.
Изменено: Kor - 07.08.2019 19:50:56
 
Цитата
Kor написал: в случае вставки строки ключ уже не уникальный

Я показал ПРИМЕР того, как можно решить подобную задачу.
Изменено: Sanja - 08.08.2019 05:47:49
Согласие есть продукт при полном непротивлении сторон
 
Немного модернизировал файл из сообщения №10, как вариант.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, здорово! :excl:  
Согласие есть продукт при полном непротивлении сторон
 
Sanja,Если как-то не корректно ответил, то прошу прощения.
Nordheim, код выглядит приятно.
 
Немного изменил логику работы заполнения ключей:
1) Убрал заполнение словаря с открытия книги, теперь словарь перезаписывается при дополнении новой строки в таблицу, что бы не было разрыва в нумерации ключей при удалении строки (одной).
2) ключи сделал пятизначные т.е. таблицу можно заполнять на 99999 позиций а не как ранее на 9999 иначе можно получить бесконечный цикл, если пользователь попытается заполнить 100000 строку, то появится сообщение об ошибке переполнения таблицы и все строки после 100000 будут удалены.
Если нужна сортировка по ключам, это уже сами доделаете.
"Все гениальное просто, а все простое гениально!!!"
Страницы: 1
Наверх