Üks mu sõber töötab tervishoiu analüütilises ettevõttes. Ta suhtleb minuga sageli mõnedes tegelikes küsimustes, millega ta Excelis andmetega töötades silmitsi seisab.
Ma teisendan tema päringud sageli sellel saidil Exceli õpetusteks, kuna see võib olla kasulik ka mu teistele lugejatele.
See on ka üks selline õpetus.
Mu sõber helistas mulle eelmisel nädalal järgmise probleemiga:
Exceli veerus on aadressiandmed ja ma tahan tuvastada/filtreerida lahtrid, kus aadressil on dubleerivad tekstistringid (sõnad).
Siin on sarnane andmekogum, milles ta soovis filtreerida lahtreid, milles on duplikaat tekstistring (need, millel on punased nooled):
Nüüd teeb asja keeruliseks see, et nendes andmetes puudub järjepidevus. Kuna tegemist on müügiesindajate poolt käsitsi loodud andmekogumiga, võib andmekogumis olla erinevusi.
Mõelge sellele:
- Selles andmestikus võib korduda mis tahes tekstistring. Näiteks võib see olla piirkonna nimi või linna nimi või mõlemad.
- Sõnad eraldatakse tühikutega ja puudub järjekindlus selles, kas linnanimi oleks seal kuue või kaheksa sõna järel.
- Selliseid kirjeid on tuhandeid ja vajadus on filtreerida need kirjed, kus on duplikaate.
Olles kaalunud paljusid võimalusi (nt tekst veergudesse ja valemid), otsustasin lõpuks selle saavutamiseks kasutada VBA -d.
Nii lõin nende rakkude analüüsimiseks kohandatud VBA -funktsiooni („IdDuplicate”) ja andsin mulle tõese väärtuse, kui tekstistringis on dubleeriv sõna, ja FALSE, kui kordusi pole (nagu allpool näidatud):
See kohandatud funktsioon analüüsib tekstistringi iga sõna ja kontrollib, mitu korda see tekstis esineb. Kui arv on rohkem kui 1, tagastab see tõe; muidu tagastab see vale.
Samuti on see loodud sõnade lugemiseks rohkem kui kolm tähemärki.
Kui mul on tõesed/valed andmed, saan hõlpsalt filtreerida kõik tõesed kirjed.
Nüüd näitan teile, kuidas seda Excelis teha.
Kohandatud funktsiooni VBA kood
Seda tehakse VBA -s kohandatud funktsiooni loomisega. Seda funktsiooni saab seejärel kasutada mis tahes muu Exceli töölehe funktsioonina.
Siin on selle VBA kood:
Funktsioon IdDuplicates (rng as Range) String Dim StringtoAnalyze 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 Samm -1 Kui Len (StringtoAnalyze (i)) <minWordLen Siis GoTo SkipA Sest j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: lõppfunktsioon
Täname Walterit selle koodi parema lähenemise soovitamise eest!
Kuidas seda VBA koodi kasutada?
Nüüd, kui teil on VBA -kood, peate selle paigutama Exceli taustaprogrammi, et see saaks töötada tavalise töölehe funktsioonina.
Allpool on toodud sammud VBA koodi tagaküljele panemiseks:
- Minge vahekaardile Arendaja.
- Klõpsake Visual Basic (saate kasutada ka kiirklahvi ALT + F11)
- Avanevas VB redaktori tagaosas paremklõpsake mõnda töövihiku objekti.
- Minge jaotisse "Lisa" ja klõpsake "Moodul". See lisab töövihiku mooduli objekti.
- Kopeerige ja kleepige aknas Mooduli kood ülalmainitud VBA -kood.
Kui teil on VBA -kood tagaosas, saate funktsiooni - IdDuplicates - kasutada mis tahes muu tavalise töölehe funktsioonina.
See funktsioon võtab ühe argumendi, mis on selle lahtri viide, kus teil on tekst.
Funktsiooni tulemus on TRUE (kui selles on dubleeritud sõnu) või FALSE (kui duplikaate pole). Kui olete loendi TRUE/FALSE loonud, saate filtreerida tõega TRUE, et saada kõik lahtrid, milles on topelttekstid.
Märkus. Olen loonud koodi ainult selleks, et arvestada nende sõnadega, mis on pikemad kui kolm tähemärki. See tagab, et kui tekstistringis on 1, 2 või 3 märgi pikkust sõna (näiteks 12 A, K G M või L D A), siis neid duplikaatide loendamise ajal eiratakse. Soovi korral saate seda koodis hõlpsalt muuta.
See funktsioon on saadaval ainult töövihikus, kus olete koodi moodulis kopeerinud. Kui soovite, et see oleks saadaval ka teistes töövihikutes, peate selle koodi nendesse töövihikutesse kopeerima ja kleepima. Teise võimalusena saate luua ka lisandmooduli (mille lubamine muudaks selle funktsiooni kättesaadavaks kõikides teie süsteemi töövihikutes).
Samuti pidage meeles, et salvestage see töövihik laiendisse .xlsm (kuna selles on makrokood).
