Jeden z moich znajomych pracuje w firmie zajmującej się analizą opieki zdrowotnej. Często łączy się ze mną w sprawie niektórych rzeczywistych problemów, z którymi boryka się podczas pracy z danymi w programie Excel.
Wiele razy konwertuję jego zapytania na samouczki Excela na tej stronie, ponieważ może to być pomocne również dla moich innych czytelników.
To także jeden z takich samouczków.
Mój przyjaciel zadzwonił do mnie w zeszłym tygodniu z następującym problemem:
W kolumnie w Excelu znajdują się dane adresowe i chcę zidentyfikować / przefiltrować komórki, w których adres zawiera zduplikowane ciągi tekstowe (słowa).
Oto podobny zbiór danych, w którym chciał odfiltrować komórki zawierające zduplikowany ciąg tekstowy (te z czerwonymi strzałkami):
To, co sprawia, że jest to trudne, to brak spójności w tych danych. Ponieważ jest to kompilacja zestawu danych, który został ręcznie utworzony przez przedstawicieli handlowych, mogą występować różnice w zestawie danych.
Rozważ to:
- Każdy ciąg tekstowy może się powtarzać w tym zestawie danych. Na przykład może to być nazwa obszaru, nazwa miasta lub jedno i drugie.
- Słowa są oddzielone spacją i nie ma spójności co do tego, czy nazwa miasta pojawi się po sześciu czy ośmiu słowach.
- Takich rekordów są tysiące i trzeba filtrować te rekordy, w których występują zduplikowane ciągi tekstowe.
Po rozważeniu wielu opcji (takich jak tekst do kolumn i formuł) w końcu zdecydowałem się użyć VBA, aby to zrobić.
Stworzyłem więc niestandardową funkcję VBA („IdDuplicate”), aby przeanalizować te komórki i dać mi TRUE, jeśli w ciągu tekstowym jest zduplikowane słowo, i FALSE, jeśli nie ma powtórzeń (jak pokazano poniżej):
Ta niestandardowa funkcja analizuje każde słowo w ciągu tekstowym i sprawdza, ile razy występuje w tekście. Jeśli liczba jest większa niż 1, zwraca TRUE; w przeciwnym razie zwraca FALSE.
Ponadto został stworzony, aby liczyć tylko słowa więcej niż trzy znaki.
Gdy mam dane PRAWDA/FAŁSZ, mogę łatwo filtrować wszystkie rekordy, które są PRAWDA.
Teraz pokażę ci, jak to zrobić w programie Excel.
Kod VBA dla funkcji niestandardowej
Odbywa się to poprzez utworzenie funkcji niestandardowej w VBA. Ta funkcja może być następnie używana jak każda inna funkcja arkusza kalkulacyjnego w programie Excel.
Oto kod VBA:
Function IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Krok -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = „FAŁSZ” Pomiń B: Zakończ funkcję
Dzięki Walterowi za zaproponowanie lepszego podejścia do tego kodu!
Jak korzystać z tego kodu VBA
Teraz, gdy masz kod VBA, musisz umieścić go w zapleczu programu Excel, aby mógł działać jako zwykła funkcja arkusza roboczego.
Poniżej znajdują się kroki, aby umieścić kod VBA na zapleczu:
- Przejdź do zakładki Deweloper.
- Kliknij Visual Basic (możesz również użyć skrótu klawiaturowego ALT + F11)
- W otwartym zapleczu edytora VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu.
- Przejdź do „Wstaw” i kliknij „Moduł”. Spowoduje to wstawienie obiektu modułu dla skoroszytu.
- W oknie Kod modułu skopiuj i wklej wspomniany powyżej kod VBA.
Gdy masz kod VBA na zapleczu, możesz użyć funkcji - „IdDuplicates” jak każdej innej zwykłej funkcji arkusza roboczego.
Ta funkcja przyjmuje jeden pojedynczy argument, który jest odwołaniem do komórki, w której znajduje się tekst.
Wynik funkcji to TRUE (jeśli są w niej zduplikowane słowa) lub FALSE (jeśli nie ma duplikatów). Gdy masz już tę listę TRUE/FALSE, możesz filtrować te z TRUE, aby uzyskać wszystkie komórki, które zawierają zduplikowane ciągi tekstowe.
Uwaga: stworzyłem kod tylko po to, aby uwzględniać te słowa, które mają więcej niż trzy znaki. Gwarantuje to, że jeśli w ciągu tekstowym znajdują się słowa o długości 1, 2 lub 3 znaków (takie jak 12 A, K G M lub L D A), zostaną one zignorowane podczas liczenia duplikatów. Jeśli chcesz, możesz to łatwo zmienić w kodzie.
Ta funkcja będzie dostępna tylko w skoroszycie, do którego skopiowałeś kod w module. Jeśli chcesz, aby było to również dostępne w innych skoroszytach, musisz skopiować i wkleić ten kod w tych skoroszytach. Alternatywnie możesz również utworzyć dodatek (włączenie tej funkcji spowoduje, że ta funkcja będzie dostępna we wszystkich skoroszytach w systemie).
Pamiętaj też, aby zapisać ten skoroszyt w rozszerzeniu .xlsm (ponieważ zawiera kod makra).
