Ako vytvoriť funkciu definovanú používateľom v programe Microsoft Excel

Anonim

V tomto článku sa naučíme vytvárať funkcie definované používateľom v programe Microsoft Excel pomocou jazyka VBA.

Užívateľom definovaná funkcia:- Microsoft Excel už má veľa funkcií, ale stále má každý iné požiadavky, situáciu, môžeme vytvoriť vlastnú funkciu podľa požiadavky, ktorá sa nazýva funkcia definovaná používateľom. Užívateľom definovanú funkciu môžeme používať ako ostatné funkcie v Exceli.

Nasledujú témy, pre ktoré vytvoríme funkciu definovanú používateľom:

1). Ako spočítať počet slov v bunke alebo rozsahu?

2). Ako extrahovať slovo z vety alebo bunky v programe Excel?

3). Ako vytvoriť vzorec pre ISO?

4). Ako poznať názov pracovného hárka a zošita pomocou VBA?

5). Ako extrahovať prvé a posledné slovo z bunky v programe Excel?

Ako vytvorím užívateľom definovanú funkciu na počítanie počtu slov v bunke alebo v rozsahu?

Máme údaje v hárku 1, v ktorom máme niekoľko adries, takže chceme spočítať slová v bunke alebo rozsahu vytvorením funkcie definovanej používateľom prostredníctvom VBA v programe Excel.

Ak chcete, aby bola funkcia definovaná používateľom, postupujte podľa nižšie uvedených krokov:-

  • Otvorte stránku VBA a stlačte kláves Alt+F11.
  • Vložte modul.

Napíšte nižšie uvedený kód:

 Funkcia WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count Count for Long for each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Nasledujúci rCell WORDSCOUNT = lCount End Function

Vysvetlenia kódu: - Aby bola funkcia definovaná užívateľom, spustíme kód pre názov funkcie a definovanie premenných. Na spočítanie slov v rozsahu sme v kóde použili „Pre každú slučku“.
Ako používať túto funkciu v programe Excel?

Ak chcete použiť túto funkciu, postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Ak chcete počítať slová pre bunku, zadajte vzorec do bunky D7.
  • = WORDSCOUNT (C7), bunka C7 je bunka, v ktorej chceme vypočítať slová.
  • Funkcia vráti 6, čo znamená, že bunka C7 obsahuje 6 slov.
  • Ak chcete vykonať rovnaký výpočet pre ostatné bunky, skopírujte rovnaký vzorec a prilepte ho do rozsahu.

  • Ak chcete počítať slová v rozsahu, použite vzorec ako = WORDSCOUNT (C7: C16) a stlačte kláves Enter.
  • Funkcia vráti počet slov.


Poznámka:- Tento UDF pomôže pri počítaní slov v rozsahu alebo v jednej bunke.

Teraz napíšeme kód na spočítanie slova pomocou zadaného oddeľovača (,). Postupujte podľa nižšie uvedených krokov:-

 Funkcia SEPARATECOUNTWORDS (rRange As Range, Optional separator As Variant) As Long Dim rCell As Range Dim Count As Long If IsMissing (separator) Then separator = "," End If For each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function

Ak chcete použiť túto funkciu, postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Aby sme spočítali konkrétne oddeľovače v slove, použijeme túto definovanú funkciu.
  • = SEPARATECOUNTWORDS (C7) a stlačte Enter.
  • Funkcia vráti počet konkrétnych oddeľovačov.


Ako extrahovať slovo z vety alebo bunky v programe Microsoft Excel pomocou jazyka VBA?

Máme údaje v hárku 1. V ktorom máme niekoľko adries, takže chceme extrahovať slová z vety alebo bunky alebo rozsahu vytvorením funkcie definovanej používateľom prostredníctvom VBA v programe Excel.


Ak chcete, aby bola funkcia definovaná používateľom, postupujte podľa nižšie uvedených krokov:-

  • Otvorte stránku VBA a stlačte kláves Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia GETWORD (text ako variant, N ako celé číslo, voliteľný oddeľovač ako variant) ako reťazec, ak chýba

Vysvetlenie kódu:- Vo vyššie uvedenom kóde sme uviedli názov funkcie s premennými. A potom sme definovali kritériá na extrahovanie slova z vety alebo bunky.

Teraz sa naučíme používať tento vzorec. Postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Tento vzorec použite v bunke D7.
  • = GETWORD (C7,2) a stlačte Enter.
  • Funkcia vráti druhé slovo z bunky, pretože vo vzorci, ktorý sme uviedli pre 2nd číselné slovo. Ak chcete načítať slovo, ktoré je umiestnené na 3rd pozíciu, musíte vo vzorci zmeniť číslo z 2 na 3.


Ako vytvoriť vzorec čísla ISO týždňa v programe Microsoft Excel pomocou VBA?

Naučíme sa, ako s týmto UDF vytvoriť v Exceli vzorec počtu týždňov v ISO. Túto funkciu použijeme na identifikáciu, že uvedený dátum patrí číslu týždňa v roku.

V liste máme zoznam dátumov a v druhom stĺpci chceme načítať čísla týždňov.

Pri vytváraní UDF pre túto požiadavku postupujte podľa nižšie uvedených krokov:-

  • Otvorte stránku VBA a stlačte kláves Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia ISOWEEKNUMBER (indikovať ako dátum) Ako dlho Dim Dt ako dátum Dt = DateSerial (Rok (Indate - deň v týždni (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Koniec funkcie

Vysvetlenie kódu:-:- Vo vyššie uvedenom kóde sme uviedli názov funkcie s premennými. Potom sme nastavili hodnotu dátumu a potom sme definovali kritériá funkcie „ISOWEENUMBER“.
Ako môžeme použiť túto funkciu v našom súbore Excel?

  • Prejdite na hárok programu Excel.
  • Zadajte vzorec do bunky D7.
  • = ISOWEEKNUMBER (C7), a stlačte kláves Enter.
  • Funkcia vráti týždeň pre zadaný dátum v bunke. Teraz, aby ste získali číslo týždňa pre každý dátum, skopírujte rovnaký vzorec v rozsahu.


Teraz sa naučíme, ako vrátiť štandardy ISO na začiatku roka v programe Excel- Prvý pondelok v roku.

Táto funkcia v zásade skontroluje, či 1sv Na ktorý dátum pripadne deň v roku a potom sa začne počítať počet týždňov od tohto dátumu. Pozrime sa, ako môžeme pre túto požiadavku vytvoriť UDF.


Postupujte podľa nižšie uvedených krokov:-

  • Otvorte stránku VBA a stlačte kláves Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia ISOSTYR (rok ako celé číslo) ako dátum dim WD ako integer dim NY ako dátum NY = dátum sériové (rok, 1, 1) WD = (NY - 2) Mod 7 Ak WD <4 Potom ISOSTYR = NY - WD inak ISOSTYR = NY - Funkcia WD + 7 End If End

Vysvetlenie kódu: - Vo vyššie uvedenom kóde sme uviedli názov funkcie s premennými. Potom sme nastavili kritériá pre premenné a potom sme definovali vstup vzorca.

Stačí zadať rok 2001 v tomto formáte a vzorec vám poskytne 1sv Pondelok v roku.

Teraz sa naučíme používať UDF v súbore Excel. Postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Zadajte vzorec do bunky D7.
  • = ISOSTYR (C7) a stlačte Enter.
  • Funkcia vráti dátum 1sv Pondelok prvého týždňa nového roka.
  • Ak chcete vrátiť dátum 1sv Pondelok prvého roka nového roka, skopírujte rovnaký vzorec a prilepte do rozsahu.


Ako poznať názov pracovného hárka a zošita pomocou jazyka VBA v programe Microsoft Excel?

Postupujte podľa nižšie uvedeného kódu a krokov:-

  • Otvorte stránku VBA a stlačte kláves Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia Názov pracovného hárka () Pracovný hárok = Rozsah ("A1"). Nadradený_názov Koniec Funkcia

Vysvetlenie kódu:- Vo vyššie uvedenom kóde sme spomenuli názov funkcie a potom sme definovali, ako poznať názov hárka.

Ak chcete použiť tento vzorec, stačí zadať vzorec do ľubovoľnej bunky týmto spôsobom: -= Názov pracovného hárka (). Funkcia vráti názov hárka.


Ak chcete vytvoriť funkciu pre názov zošita, postupujte podľa nižšie uvedených krokov a kódu:-

  • Otvorte stránku VBA stlačením klávesov Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia Workbookname () Workbookname = ThisWorkbook.Name Koniec funkcie

Vysvetlenie kódu:-:- Vo vyššie uvedenom kóde sme spomenuli názov funkcie a potom sme definovali, ako spoznať názov zošita.

Ak chcete použiť tento vzorec, stačí zadať vzorec do ľubovoľnej bunky týmto spôsobom: - = Názov zošita (). Funkcia vráti názov hárka.


Ako extrahovať prvé a posledné slovo z bunky pomocou jazyka VBA v programe Microsoft Excel?

Máme údaje v hárku 1, v ktorých máme niekoľko adries, takže chceme extrahovať posledné a prvé slovo z vety alebo bunky alebo rozsahu vytvorením funkcie definovanej používateľom prostredníctvom jazyka VBA v programe Excel.

Najprv napíšeme funkciu na extrahovanie prvého slova. Postupujte podľa nižšie uvedených krokov:-

  • Otvorte stránku VBA stlačením klávesov Alt+F11.
  • Vložte modul

Napíšte nižšie uvedený kód:-

 Funkcia GETFW (text ako reťazec, voliteľný oddeľovač ako variant) Dim FW ako reťazec, ak chýba (oddeľovač), potom oddeľovač = "" koniec, ak FW = vľavo (text, InStr (1, text, oddeľovač, vbTextCompare)) GETFW = nahradiť (FW , Oddeľovač, "") Ukončiť funkciu

Vysvetlenie kódu: - Vo vyššie uvedenom kóde sme uviedli názov funkcie s premennými. A potom sme definovali kritériá na extrahovanie slova z vety alebo bunky.

Teraz sa naučíme používať tento vzorec. Postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Tento vzorec použite v bunke D9.
  • = GETFW (C9) a stlačte Enter.
  • Funkcia vráti prvé slovo z údajov. Teraz, aby ste získali prvé slovo pre všetky bunky, skopírujte rovnaký vzorec v rozsahu.

Teraz napíšeme kód na extrahovanie posledného slova z bunky. Postupujte podľa nižšie uvedeného kódu:-

  • Otvorte stránku VBA stlačením klávesov Alt+F11.
  • Vložte modul.
  • Napíšte nižšie uvedený kód:-
 Funkcia GETLW (text ako reťazec, voliteľný oddeľovač ako variant) Dim LW ako reťazec, ak chýba (oddeľovač), potom oddeľovač = "" koniec, ak LW = StrReverse (text) LW = vľavo (posledné slovo, InStr (1, LW, oddeľovač, vbTextCompare) ) GETLW = StrReverse (Replace (LW, Separator, "")) End Function

Teraz sa naučíme používať tento vzorec. Postupujte podľa nižšie uvedených krokov:-

  • Prejdite na hárok programu Excel.
  • Tento vzorec použite v bunke D9.
  • = GETLW (C9) Stlačte Enter.
  • Funkcia vráti posledné slovo z údajov. Teraz, aby ste získali posledné slovo pre všetky bunky, skopírujte rovnaký vzorec v rozsahu.

Toto sú funkcie, ktoré môžeme definovať prostredníctvom VBA a potom ich použiť ako vzorec programu Excel. Tiež môžeme vytvoriť oveľa viac funkcií definovaných používateľom. Učte sa s nami ďalej, vymyslíme záludnejšie vzorce.