Funkcia rozdelenia VBA programu Excel - vysvetlená s príkladmi

Pri práci s VBA v Exceli môže byť potrebné rozdeliť reťazec na rôzne časti na základe oddeľovača.

Ak napríklad máte adresu, môžete použiť funkciu VBA Split na získanie rôznych častí adresy, ktoré sú oddelené čiarkou (ktorá by bola v tomto prípade oddeľovačom).

SPLIT je vstavaná funkcia reťazca v programe Excel VBA, ktorú môžete použiť na rozdelenie textového reťazca na základe oddeľovača.

Funkcia Excel VBA SPLIT - syntax

Rozdeliť (výraz, [oddeľovač], [limit], [porovnať])

• Výraz: Toto je reťazec, ktorý chcete rozdeliť na základe oddeľovača. V prípade príkladu adresy bude napríklad „výrazom“ celá adresa. V prípade, že sa jedná o reťazec s nulovou dĺžkou (“”), funkcia SPLIT vráti prázdne pole.
• Oddeľovač: Toto je voliteľný argument. Toto je oddeľovač, ktorý sa používa na rozdelenie argumentu „Výraz“. V prípade nášho príkladu adresy je čiarka oddeľovač, ktorý sa používa na rozdelenie adresy na rôzne časti. Ak tento argument neurčíte, medzerník sa považuje za predvolený oddeľovač. V prípade, že zadáte reťazec s nulovou dĺžkou („“), funkcia vráti celý reťazec „Výraz“.
• Limit: Toto je voliteľný argument. Tu zadáte celkový počet podreťazcov, ktoré chcete vrátiť. Ak napríklad chcete vrátiť iba prvé tri podreťazce z argumentu „Výraz“, bude to 3. Ak tento argument nezadáte, predvolená hodnota je -1, ktorá vráti všetky podreťazce.
• Porovnať: Toto je voliteľný argument. Tu určíte typ porovnania, ktoré má funkcia SPLIT vykonávať pri vyhodnocovaní podreťazcov. K dispozícii sú nasledujúce možnosti:
  • Keď je porovnanie 0: Toto je binárne porovnanie. To znamená, že ak je vašim oddeľovačom textový reťazec (povedzme ABC), bude sa rozlišovať malé a veľké písmená. „ABC“ by sa nerovnalo „abc“.
  • Keď je porovnanie 1: Toto je porovnanie textu. To znamená, že ak je vašim oddeľovačom textový reťazec (povedzme ABC), aj keď máte v reťazci „Výraz“ „abc“, bude sa považovať za oddeľovač.

Teraz, keď sme pokryli základy funkcie SPLIT, pozrime sa na niekoľko praktických príkladov.

Príklad 1 - Rozdeľte slová vo vete

Predpokladajme, že mám text - „Rýchla hnedá líška skáče cez lenivého psa“.

Môžem použiť funkciu SPLIT na to, aby sa každé slovo tejto vety dostalo do samostatnej položky v poli.

Nasledujúci kód by k tomu slúžil:

Sub SplitWords () Dim TextStrng ako reťazec Dim Výsledok () Ako reťazec TextStrng = "Rýchla hnedá líška skáče cez lenivého psa" Výsledok () = Rozdeliť (TextStrng) Koniec Sub

Aj keď kód nerobí nič užitočné, pomôže vám porozumieť tomu, čo funkcia Split vo VBA robí.

Funkcia Split rozdelí textový reťazec a každé slovo priradí do poľa Výsledok.

Takže v tomto prípade:

• Výsledok (0) ukladá hodnotu „The“
• Výsledok (1) ukladá hodnotu „Rýchle“
• Výsledok (2) uloží hodnotu „Brown“ a podobne.

V tomto prípade sme zadali iba prvý argument - text, ktorý sa má rozdeliť. Pretože nebol zadaný žiadny oddeľovač, vyžaduje ako predvolený oddeľovač znak medzery.

Dôležitá poznámka:

1. Funkcia VBA SPLIT vracia pole, ktoré začína od základne 0.
2. Keď je k poli priradený výsledok funkcie SPLIT, toto pole musí byť deklarované ako typ údajov String. Ak ho deklarujete ako dátový typ Variant, zobrazí sa chyba nesúladu typu). V uvedenom príklade si všimnite, že som deklaroval Result () ako dátový typ String.

Príklad 2 - Spočítajte počet slov vo vete

Na získanie celkového počtu slov vo vete môžete použiť funkciu SPLIT. Ide o to, spočítať počet prvkov v poli, ktoré získate po rozdelení textu.

Nasledujúci kód by zobrazil okno so správou s počtom slov:

Sub WordCount () Dim TextStrng ako reťazec Dim WordCount ako celočíselný Dim Výsledok () Ako reťazec TextStrng = "Rýchla hnedá líška skáče cez lenivého psa" Výsledok = Rozdeliť (TextStrng) WordCount = UBound (Výsledok ()) + 1 MsgBox "The Počet slov je „& WordCount End Sub

V tomto prípade nám funkcia UBound hovorí o hornej hranici poľa (tj. O maximálnom počte prvkov, ktoré pole má). Pretože základ poľa je 0, 1 sa pridá, aby sa získal celkový počet slov.

Podobný kód môžete použiť na vytvorenie vlastnej funkcie vo VBA, ktorá bude brať text ako vstup a vráti počet slov.

Nasledujúci kód vytvorí túto funkciu:

Funkcia WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function

Po vytvorení môžete funkciu WordCount používať rovnako ako všetky ostatné bežné funkcie.

Táto funkcia tiež zvláda úvodné, koncové a dvojité medzery medzi slovami. To bolo možné pomocou funkcie TRIM v kóde VBA.

Ak sa chcete dozvedieť viac o tom, ako tento vzorec funguje pri počítaní počtu slov vo vete, alebo sa chcete dozvedieť o spôsobe, ako získať počet slov vo vzorci mimo VBA, pozrite si tento návod.

Príklad 3 - Použitie iného oddeľovača ako medzerníka

V predchádzajúcich dvoch príkladoch sme vo funkcii SPLIT použili iba jeden argument a ostatné boli predvolené argumenty.

Ak používate iný oddeľovač, musíte to zadať vo vzorci SPLIT.

V nižšie uvedenom kóde funkcia SPLIT vráti pole ako oddeľovač na základe čiarky a potom zobrazí správu s každým slovom v samostatnom riadku.

Sub CommaSeparator () Dim TextStrng as String Dim Result () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") For i = LBound (Výsledok ()) Na UBound (Výsledok ()) DisplayText = DisplayText & Výsledok (i) & vbNewLine Ďalej i MsgBox DisplayText Koniec Sub

Vo vyššie uvedenom kóde som použil slučku For Next na prechod každým prvkom poľa „Výsledok“ a jeho priradenie k premennej „DisplayText“.

Príklad 4 - Rozdeľte adresu na tri časti

Pomocou funkcie SPLIT môžete určiť, koľko čísel rozdelení chcete získať. Ak napríklad nič nezadám, na rozdelenie reťazca sa použije každá inštancia oddeľovača.

Ak však ako limit uvediem 3, reťazec bude rozdelený iba na tri časti.

Napríklad, ak mám nasledujúcu adresu:

2703 Winifred Way, Indianapolis, Indiana, 46204

Na rozdelenie tejto adresy na tri časti môžem vo VBA použiť funkciu Rozdeliť.

Rozdeľuje prvé dve na základe oddeľovača čiarok a zvyšná časť sa stáva tretím prvkom poľa.

Nasledujúci kód by zobrazoval adresu v troch rôznych riadkoch v okne správy:

Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) For i = LBound (Výsledok ( )) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

Jedno z praktických použití by to mohlo byť, keď chcete rozdeliť jednu riadkovú adresu do formátu zobrazeného v okne správy. Potom môžete vytvoriť vlastnú funkciu, ktorá vráti adresu rozdelenú na tri časti (s každou časťou v novom riadku).

Nasledujúci kód by to urobil:

Funkcia ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Result (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) End Function

Akonáhle budete mať tento kód v module, môžete použiť funkciu (ThreePartAddress) v zošite rovnako ako všetky ostatné funkcie programu Excel.

Táto funkcia vyžaduje jeden argument - odkaz na bunku, ktorý má adresu.

Upozorňujeme, že aby sa výsledná adresa zobrazila v troch rôznych riadkoch, musíte na bunky použiť formát zalamovacieho textu (nachádza sa na karte Domov v skupine Zarovnanie). Ak nie je povolený formát „Zalamovať text“, uvidíte celú adresu ako jeden riadok.

Príklad 5 - Z adresy zadajte názov mesta

S funkciou Split vo VBA môžete určiť, akú časť výsledného poľa chcete použiť.

Predpokladajme napríklad, že rozdeľujem nasledujúcu adresu na základe čiarky ako oddeľovača:

2703 Winifred Way, Indianapolis, Indiana, 46204

Výsledné pole bude vyzerať nasledovne:

{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}

Keďže ide o pole, môžem sa rozhodnúť zobraziť alebo vrátiť konkrétnu časť tohto poľa.

Nasleduje kód pre vlastnú funkciu, kde môžete zadať číslo a vráti tento prvok z poľa. Ak napríklad chcem názov štátu, môžem zadať 3 (pretože je to tretí prvok v poli).

Funkcia ReturnNthElement (CellRef ako rozsah, ElementNumber ako celé číslo) Dim Výsledok () Ako reťazec Výsledok = Rozdelenie (CellRef, ",") ReturnNthElement = Výsledok (ElementNumber - 1) Koncová funkcia

Vyššie uvedená funkcia má dva argumenty, odkaz na bunku s adresou a číslo prvku, ktoré chcete vrátiť. Funkcia Split rozdelí prvky adresy a priradí ju k premennej Výsledok.

Potom vráti číslo prvku, ktoré ste zadali ako druhý argument. Všimnite si toho, pretože keďže základňa je 0, na vrátenie správnej časti adresy sa používa číslo_čísla-1.

Tento vlastný vzorec je najvhodnejší, ak máte na celej adrese konzistentný formát - t. J. Mesto je vždy uvedené za čiarkami. Ak údaje nie sú konzistentné, nedosiahnete požadovaný výsledok.

V prípade, že chcete názov mesta, môžete ako druhý argument použiť 2. V prípade, že použijete číslo, ktoré je vyššie ako celkový počet prvkov, vráti sa #HODNOTA! chyba.

Kód môžete ďalej zjednodušiť, ako je uvedené nižšie:

Funkcia ReturnNthElement (CellRef ako rozsah, ElementNumber ako celé číslo) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) Koncová funkcia

Vo vyššie uvedenom kóde namiesto použitia premennej Výsledok vracia iba zadané číslo prvku.

Ak teda máte Split („Dobré ráno“) (0), vráti iba prvý prvok, ktorý je „Dobrý“.

Podobne vo vyššie uvedenom kóde vráti iba zadané číslo prvku.