Yksi ystävistäni työskentelee terveydenhuollon analytiikkayrityksessä. Hän on usein yhteydessä minuun joistakin tosielämän ongelmista, joita hän kohtaa työskennellessään Excelin tietojen kanssa.
Muunan hänen kyselynsä usein tämän sivuston Excel -opetusohjelmiksi, koska siitä voi olla hyötyä myös muille lukijoilleni.
Tämä on myös yksi tällainen opetusohjelma.
Ystäväni soitti minulle viime viikolla seuraavan ongelman kanssa:
Excelin sarakkeessa on osoitetietoja, ja haluan tunnistaa/suodattaa solut, joissa osoitteessa on päällekkäisiä tekstimerkkijonoja (sanoja).
Tässä on samanlainen tietojoukko, jossa hän halusi suodattaa solut, joissa on päällekkäinen tekstimerkkijono (ne, joissa on punaiset nuolet):
Tämän tekee nyt vaikeaksi se, että näissä tiedoissa ei ole johdonmukaisuutta. Koska tämä on kokoelma tietojoukkoja, jotka myyjät ovat luoneet manuaalisesti, tietojoukossa voi olla vaihteluita.
Harkitse tätä:
- Mikä tahansa tekstimerkkijono voi toistaa tässä tietojoukossa. Se voi olla esimerkiksi alueen nimi tai kaupungin nimi tai molemmat.
- Sanat erotetaan välilyönnillä, eikä ole johdonmukaista, onko kaupungin nimi siellä kuuden vai kahdeksan sanan jälkeen.
- Tällaisia tietueita on tuhansia, ja tarve on suodattaa tietueet, joissa on päällekkäisiä tekstimerkkijonoja.
Harkittuani monia vaihtoehtoja (kuten teksti sarakkeisiin ja kaavoja), päätin lopulta käyttää VBA: ta tämän tekemiseen.
Joten loin mukautetun VBA -toiminnon ("IdDuplicate") analysoimaan näitä soluja ja antamaan minulle TOSI, jos tekstimerkkijonossa on päällekkäinen sana, ja EPÄTOSI, jos toistoja ei ole (kuten alla):
Tämä mukautettu toiminto analysoi jokaisen tekstimerkkijonon sanan ja tarkistaa, kuinka monta kertaa se esiintyy tekstissä. Jos luku on enemmän kuin 1, se palauttaa arvon TOSI; muuten se palauttaa EPÄTOSI.
Lisäksi se on luotu laskemaan vain sanoja, joissa on enemmän kuin kolme merkkiä.
Kun minulla on TRUE/FALSE -tiedot, voin suodattaa helposti kaikki TIEDOT.
Näytän nyt, kuinka tämä tehdään Excelissä.
VBA -koodi mukautetulle toiminnolle
Tämä tehdään luomalla mukautettu toiminto VBA: ssa. Tätä toimintoa voidaan sitten käyttää kuten mitä tahansa muuta Excel -laskentataulukkotoimintoa.
Tässä on VBA -koodi sille:
Toiminto IdDuplicates (rng As Range) merkkijonona Dim StringtoAnalyze Varianttina Dim i kokonaislukuna Dim j kokonaislukuna Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) to 0 Vaihe -1 Jos Len (StringtoAnalyze (i)) <minWordLen Sitten 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 = "FALSE" SkipB: Lopeta toiminto
Kiitos Walter, joka ehdotti parempaa lähestymistapaa tähän koodiin!
Kuinka käyttää tätä VBA -koodia
Nyt kun sinulla on VBA -koodi, sinun on sijoitettava se Excelin taustajärjestelmään, jotta se voi toimia tavallisena laskentataulukkotoimintona.
Alla on ohjeet VBA -koodin asettamiseen taustajärjestelmään:
- Siirry Kehittäjä -välilehdelle.
- Napsauta Visual Basic (voit käyttää myös pikanäppäintä ALT + F11)
- Napsauta avautuvassa VB Editorin takaosassa mitä tahansa työkirjan objektia hiiren kakkospainikkeella.
- Mene "Lisää" ja napsauta "Moduuli". Tämä lisää työkirjan moduuliobjektin.
- Kopioi ja liitä yllä mainittu VBA -koodi Moduulikoodi -ikkunassa.
Kun sinulla on VBA -koodi taustapuolella, voit käyttää IdDuplicates -toimintoa kuten mitä tahansa muuta tavallista laskentataulukkotoimintoa.
Tämä toiminto käyttää yhtä argumenttia, joka on solun viite solulle, jossa sinulla on teksti.
Funktion tulos on TOSI (jos siinä on päällekkäisiä sanoja) tai EPÄTOSI (jos päällekkäisyyksiä ei ole). Kun sinulla on tämä luettelo TOSI/EPÄTOSI, voit suodattaa ne, joilla on TOSI, jotta saat kaikki solut, joissa on päällekkäisiä tekstimerkkijonoja.
Huomautus: Olen luonut koodin vain ottaakseni huomioon ne sanat, jotka ovat yli kolme merkkiä pitkiä. Tämä varmistaa, että jos tekstimerkkijonossa on 1, 2 tai 3 merkin pituisia sanoja (kuten 12 A, K G M tai L D A), ne jätetään huomiotta kopioita laskettaessa. Jos haluat, voit helposti muuttaa tämän koodissa.
Tämä toiminto on käytettävissä vain työkirjassa, johon olet kopioinut koodin moduulista. Jos haluat tämän olevan saatavana myös muissa työkirjoissa, sinun on kopioitava ja liitettävä tämä koodi näihin työkirjoihin. Vaihtoehtoisesti voit myös luoda apuohjelman (jonka avulla tämä toiminto olisi käytettävissä kaikissa järjestelmän työkirjoissa).
Muista myös tallentaa tämä työkirja .xlsm -laajennukseen (koska siinä on makrokoodi).
