Viens no maniem draugiem strādā veselības aprūpes analīzes uzņēmumā. Viņš bieži sazinās ar mani par dažiem reālās dzīves jautājumiem, ar kuriem viņš saskaras, strādājot ar datiem programmā Excel.
Daudzas reizes es pārvērsu viņa vaicājumus par Excel pamācībām šajā vietnē, jo tas varētu noderēt arī citiem maniem lasītājiem.
Šī ir arī viena šāda apmācība.
Mans draugs man zvanīja pagājušajā nedēļā ar šādu jautājumu:
Programmas Excel kolonnā ir adreses dati, un es vēlos identificēt/filtrēt šūnas, kurās adresē ir dublikātu teksta virknes (vārdi).
Šeit ir līdzīga datu kopa, kurā viņš vēlējās filtrēt šūnas, kurās ir dublikāta teksta virkne (tās, kurām ir sarkanas bultiņas):
Tagad to apgrūtina tas, ka šajos datos nav konsekvences. Tā kā šis ir datu kopas apkopojums, ko manuāli izveidojuši pārdošanas pārstāvji, datu kopā var būt atšķirības.
Apsveriet šo:
- Jebkura teksta virkne šajā datu kopā varētu atkārtoties. Piemēram, tas varētu būt apgabala nosaukums vai pilsētas nosaukums vai abi.
- Vārdi ir atdalīti ar atstarpes rakstzīmi, un nav konsekvences, vai pilsētas nosaukums būtu pēc sešiem vai astoņiem vārdiem.
- Šādu ierakstu ir tūkstošiem, un ir jāfiltrē tie ieraksti, kuros ir teksta virkņu dublikāti.
Apsverot daudzas iespējas (piemēram, tekstu kolonnās un formulas), es beidzot nolēmu izmantot VBA, lai to paveiktu.
Tāpēc es izveidoju pielāgotu VBA funkciju (“IdDuplicate”), lai analizētu šīs šūnas un sniegtu man TRUE, ja teksta virknē ir dublikāts, un FALSE, ja nav atkārtojumu (kā parādīts zemāk):
Šī pielāgotā funkcija analizē katru teksta virknes vārdu un pārbauda, cik reizes tas parādās tekstā. Ja skaitlis ir lielāks par 1, tas atgriež TRUE; citādi tas atgriež FALSE.
Tas ir arī izveidots, lai skaitītu tikai vārdus, kas pārsniedz trīs rakstzīmes.
Kad man ir dati par PATIESIEM/FALSE, es varu viegli filtrēt visus ierakstus, kas ir PATIESI.
Tagad ļaujiet man parādīt, kā to izdarīt programmā Excel.
Pielāgotas funkcijas VBA kods
Tas tiek darīts, izveidojot pielāgotu funkciju VBA. Šo funkciju programmā Excel var izmantot kā jebkuru citu darblapas funkciju.
Šeit ir VBA kods:
Funkcija IdDuplicates (rng kā diapazons) Kā virkne Dim StringtoAnalyze Kā variants Dim i Kā vesels skaitlis Dim j Kā vesels skaitlis Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") Attiecībā uz i = UBound (StringtoAnalyze) uz 0 Solis -1 Ja Len (StringtoAnalyze (i)) <minWordLen, tad GoTo SkipA Par j = 0 Uz i - 1 Ja StringtoAnalyze (j) = StringtoAnalyze (i) Tad IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: beigu funkcija
Paldies Valteram, kas ieteica labāku pieeju šim kodam!
Kā izmantot šo VBA kodu
Tagad, kad jums ir VBA kods, tas jāievieto Excel aizmugurē, lai tas varētu darboties kā parasta darblapas funkcija.
Tālāk ir norādītas darbības, kā VBA kodu ievietot aizmugurē:
- Dodieties uz cilni Izstrādātājs.
- Noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu ALT + F11)
- Atvērtajā VB redaktora aizmugurē ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta.
- Dodieties uz “Ievietot” un noklikšķiniet uz “Modulis”. Tas ievietos darbgrāmatas moduļa objektu.
- Logā Moduļa kods nokopējiet un ielīmējiet iepriekš minēto VBA kodu.
Kad aizmugurē ir VBA kods, varat izmantot funkciju - “IdDuplicates” kā jebkuru citu parastu darblapas funkciju.
Šī funkcija aizņem vienu argumentu, kas ir tās šūnas atsauce, kurā ir teksts.
Funkcijas rezultāts ir TRUE (ja tajā ir dublēti vārdi) vai FALSE (ja nav dublikātu). Kad esat izveidojis šo sarakstu ar TRUE/FALSE, varat filtrēt tos, kuriem ir TRUE, lai iegūtu visas šūnas, kurās ir dublētas teksta virknes.
Piezīme. Es esmu izveidojis kodu, lai ņemtu vērā tos vārdus, kuru garums pārsniedz trīs rakstzīmes. Tas nodrošina, ka, ja teksta virknē ir 1, 2 vai 3 rakstzīmes gari vārdi (piemēram, 12 A, K G M vai L D A), tie tiek ignorēti, skaitot dublikātus. Ja vēlaties, varat to viegli mainīt kodā.
Šī funkcija būs pieejama tikai darbgrāmatā, kurā esat nokopējis kodu modulī. Ja vēlaties, lai tas būtu pieejams arī citās darbgrāmatās, jums ir nepieciešams nokopēt un ielīmēt šo kodu šajās darbgrāmatās. Varat arī izveidot pievienojumprogrammu (iespējojot šo funkciju, šī funkcija būtu pieejama visās jūsu sistēmas darbgrāmatās).
Atcerieties arī saglabāt šo darbgrāmatu paplašinājumā .xlsm (jo tajā ir makro kods).
