Један од мојих пријатеља ради у здравственој аналитичкој компанији. Често се повезује са мном о неким стварним проблемима са којима се суочава док ради са подацима у Екцелу.
Много пута претварам његове упите у Екцел водиче на овој веб локацији, јер би то могло бити од помоћи и мојим другим читаоцима.
Ово је такође један такав водич.
Прошле недеље ме је звао мој пријатељ са следећим проблемом:
Подаци о адреси се налазе у колони у Екцелу и желим да идентификујем/филтрирам ћелије у којима адреса садржи дупле текстуалне низове (речи).
Ево сличног скупа података у којем је желео да филтрира ћелије које имају дупли текстуални низ (оне са црвеним стрелицама):
Оно што ово отежава је то што нема доследности у овим подацима. Пошто је ово компилација скупа података који су ручно креирали продајни представници, могу доћи до варијација у скупу података.
Размотрити ово:
- Било који текстуални низ се може поновити у овом скупу података. На пример, то може бити назив области или назив града или обоје.
- Речи су раздвојене размаком и нема доследности у томе да ли би се назив града нашао после шест речи или осам речи.
- Оваквих записа има на хиљаде, а потребно је филтрирати те записе у којима постоје дупли текстуални низови.
Након разматрања многих опција (као што су текст у колоне и формуле), коначно сам одлучио да употребим ВБА да бих то постигао.
Тако сам створио прилагођену ВБА функцију („ИдДуплицате“) за анализу ових ћелија и дај ми ТРУЕ ако постоји дуплирана реч у текстуалном низу, и ФАЛСЕ у случају да нема понављања (као што је приказано испод):
Ова прилагођена функција анализира сваку реч у текстуалном низу и проверава колико се пута појављује у тексту. Ако је број већи од 1, враћа ТРУЕ; иначе враћа ФАЛСЕ.
Такође, креиран је да броји само речи веће од три знака.
Када добијем ТРУЕ/ФАЛСЕ податке, могу лако филтрирати све записе који су ТРУЕ.
Сада ћу вам показати како то учинити у Екцелу.
ВБА код за прилагођену функцију
Ово се постиже креирањем прилагођене функције у ВБА. Ова функција се тада може користити као било која друга функција радног листа у Екцелу.
Ево ВБА кода за то:
Функција ИдДуплицатес (рнг Ас Ранге) Ас Стринг Дим СтрингтоАнализе Ас Вариант Дим и Ас Интегер Дим ј Ас Интегер Цонст минВордЛен Ас Интегер = 4 СтрингтоАнализе = Сплит (УЦасе (рнг.Валуе), "") Фор и = УБоунд (СтрингтоАнализе) То 0 Корак -1 Ако је Лен (СтрингтоАнализе (и)) <минВордЛен Затим идите на СкипА за ј = 0 На и - 1 Ако је СтрингтоАнализе (ј) = СтрингтоАнализе (и) Затим ИдДуплицатес = "ТРУЕ" Иди на СкипБ Крај ако је следећи СкипА: Следећи и ИдДуплицатес = "ФАЛСЕ" СкипБ: Заврши функцију
Хвала Валтеру што је предложио бољи приступ овом коду!
Како се користи овај ВБА код
Сада када имате ВБА код, морате га поставити у позадину програма Екцел, тако да може радити као редовна функција радног листа.
Испод су кораци за постављање ВБА кода на позадину:
- Идите на картицу Девелопер.
- Кликните на Висуал Басиц (можете користити и тастерску пречицу АЛТ + Ф11)
- У задњем делу ВБ уређивача који се отвори кликните десним тастером миша на било који објект радне свеске.
- Идите на „Уметни“ и кликните на „Модул“. Ово ће уметнути објект модула за радну свеску.
- У прозору кода модула копирајте и залепите горе поменути ВБА код.
Када имате ВБА код на задњој страни, можете користити функцију - „ИдДуплицатес“ као и било коју другу редовну функцију радног листа.
Ова функција узима један једини аргумент, а то је референца ћелије ћелије у којој имате текст.
Резултат функције је ТРУЕ (ако у њој постоје дуплиране речи) или ФАЛСЕ (ако нема дупликата). Када добијете ову листу ТРУЕ/ФАЛСЕ, можете филтрирати оне са ТРУЕ да бисте добили све ћелије које садрже дуплиране текстуалне низове.
Напомена: Код сам створио само да бих узео у обзир оне речи које имају више од три знака. Ово осигурава да, ако у текстуалном низу постоје речи дужине 1, 2 или 3 знака (попут 12 А, К Г М или Л Д А), оне се занемарују при пребројавању дупликата. Ако желите, ово можете лако променити у коду.
Ова функција ће бити доступна само у радној свесци где сте копирали код у модулу. У случају да желите да ово буде доступно и у другим радним свескама, морате да копирате и залепите овај код у те радне свеске. Алтернативно, можете да креирате и додатак (омогућујући тако да ова функција буде доступна у свим радним свескама на вашем систему).
Такође, не заборавите да сачувате ову радну свеску у .клсм екстензији (пошто у њој има макро код).
