Kako ustvariti uporabniško določeno funkcijo v programu Microsoft Excel

Anonim

V tem članku se bomo naučili, kako z uporabo VBA ustvariti uporabniško določene funkcije v programu Microsoft Excel.

Uporabniško definirana funkcija:- Microsoft Excel že ima veliko funkcij, vendar imajo vsi različne zahteve, situacijo, lahko ustvarimo svojo lastno funkcijo v skladu z zahtevo, ki se imenuje Uporabniško določena funkcija. Uporabniško definirano funkcijo lahko uporabimo tako kot druge funkcije v Excelu.

Spodaj so teme, za katere bomo ustvarili uporabniško določeno funkcijo:

1). Kako prešteti število besed v celici ali obsegu?

2). Kako izvleči besedo iz stavka ali celice v Excelu?

3). Kako ustvariti formulo za ISO?

4). Kako spoznati ime delovnega lista in delovnega zvezka z uporabo VBA?

5). Kako izvleči prvo in zadnjo besedo iz celice v Excelu?

Kako ustvariti uporabniško določeno funkcijo za štetje števila besed v celici ali obsegu?

V listu 1 imamo podatke, v katerih imamo nekaj naslovov, zato želimo prešteti besede v celici ali obsegu z ustvarjanjem uporabniško določene funkcije prek VBA v Excelu.

Za uporabniško definirano funkcijo sledite spodnjim korakom:-

  • Odprite stran VBA in pritisnite tipko Alt+F11.
  • Vstavite modul.

Napišite spodnjo kodo:

 Funkcija WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count As Long Za vsak rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Naslednja rCell WORDSCOUNT = l Končna funkcija štetja

Pojasnila kode: - Za uporabniško definirano funkcijo zaženemo kodo za ime funkcije in definiramo spremenljivke. V kodi smo uporabili »Za vsako zanko« za štetje besed v obsegu.
Kako uporabljati to funkcijo v Excelu?

Če želite uporabiti to funkcijo, sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Če želite prešteti besede za celico, vnesite formulo v celico D7.
  • = WORDSCOUNT (C7), celica C7 je celica, v kateri želimo izračunati besede.
  • Funkcija bo vrnila 6, kar pomeni, da celica C7 vsebuje 6 besed.
  • Če želite narediti enak izračun za preostale celice, kopirajte isto formulo in prilepite v obseg.

  • Če želite prešteti besede v obsegu, uporabite formulo kot = WORDSCOUNT (C7: C16) in pritisnite Enter.
  • Funkcija vrne število besed.


Opomba:- Ta UDF bo v pomoč pri štetju besed v obsegu ali v eni celici.

Sedaj bomo kodo za štetje besede napisali z določenim ločevalnikom (,). Sledite spodnjim korakom:-

 Funkcija SEPARATECOUNTWORDS (rRange As Range, izbirni ločevalnik kot varianta) As Long Dim rCell As Range Dim Count As Long If IsMissing (separator) Potem separator = "," End If For Each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Zamenjaj (Trim (rCell), ločilo, "")) Naprej rCell SEPARATECOUNTWORDS = lZaključna funkcija

Če želite uporabiti to funkcijo, sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Za štetje posebnih ločil v besedi bomo uporabili to definirano funkcijo.
  • = SEPARATECOUNTWORDS (C7) in pritisnite Enter.
  • Funkcija vrne število posebnih ločil.


Kako izvleči besedo iz stavka ali celice v Microsoft Excelu z uporabo VBA?

Podatke imamo v listu 1. V katerem imamo nekaj naslovov, zato želimo izvleči besede iz stavka ali celice ali obsega z ustvarjanjem uporabniško določene funkcije prek VBA v Excelu.


Za uporabniško definirano funkcijo sledite spodnjim korakom:-

  • Odprite stran VBA in pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija GETWORD (Besedilo kot različica, N kot celo število, neobvezno ločilo kot različica) Kot niz, če manjka (razmejevalnik) Potem je razmejevalnik = "" Konec, če je GETWORD = Razdeljeno (besedilo, ločilo) (N - 1) Končna funkcija

Koda Pojasnilo:- V zgoraj omenjeni kodi smo omenili ime funkcije s spremenljivkami. Nato smo določili merila za izvlečenje besede iz stavka ali celice.

Zdaj se bomo naučili uporabljati to formulo. Sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Uporabite to formulo v celici D7.
  • = GETWORD (C7,2) in pritisnite Enter.
  • Funkcija bo vrnila drugo besedo iz celice, ker smo v formuli, ki smo jo omenili za 2nd številčna beseda. Če želite pridobiti besedo, ki je postavljena na 3rd položaj, morate spremeniti število iz 2 v 3 v formuli.


Kako ustvariti formulo številke tedna ISO v programu Microsoft Excel z uporabo VBA?

Naučili se bomo, kako lahko s tem UDF -jem ustvarimo formulo za število tednov ISO v Excelu. To funkcijo bomo uporabili za ugotavljanje, ali omenjeni datum pripada številki tedna v letu.

Na listu imamo seznam datumov, v drugem stolpcu pa želimo pridobiti številke tednov.

Če želite ustvariti UDF za to zahtevo, sledite spodnjim korakom:-

  • Odprite stran VBA in pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Leto (Indate - Dan v tednu (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Končna funkcija

Koda Pojasnilo:-:- V zgornji kodi smo omenili ime funkcije s spremenljivkami. Nato smo nastavili vrednost datuma in nato merila funkcije »ISOWEENUMBER«.
Kako lahko to funkcijo uporabimo v datoteki Excel?

  • Pojdite na Excelov list.
  • Formulo vnesite v celico D7.
  • = ISOWEEKNUMBER (C7) in pritisnite Enter.
  • Funkcija vrne teden za vneseni datum v celico. Zdaj, če želite pridobiti številko tedna za vsak datum, kopirajte isto formulo v obseg.


Zdaj se bomo naučili, kako vrniti standarde ISO na začetek leta v Excelu- prvi ponedeljek v letu.

Ta funkcija bo v bistvu preverila, da 1st Na kateri datum bo padel ponedeljek v letu, nato pa bo začel izračunavati število tednov od tega datuma. Poglejmo, kako lahko za to zahtevo ustvarimo UDF.


Sledite spodnjim korakom:-

  • Odprite stran VBA in pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija ISOSTYR (Leto kot celo število) Kot datum Dim WD Kot celo število Dim NY Kot datum NY = DatumSerial (leto, 1, 1) WD = (NY - 2) Mod 7 Če je WD <4 Potem je ISOSTYR = NY - WD Ostalo ISOSTYR = NY - WD + 7 Končaj, če funkcija preneha

Koda Pojasnilo: - V zgornji kodi smo omenili ime funkcije s spremenljivkami. Nato smo določili merila za spremenljivke in nato definirali vnos formule.

Leto 2001 morate navesti v tej obliki, formula pa vam bo dala 1st Ponedeljek v letu.

Zdaj se bomo naučili, kako uporabljati datoteko UDF v datoteki Excel. Sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Formulo vnesite v celico D7.
  • = ISOSTYR (C7) in pritisnite Enter.
  • Funkcija vrne datum 1st Ponedeljek prvega tedna novega leta.
  • Če želite vrniti datum 1st V ponedeljek prvega tedna novega leta kopirajte isto formulo in prilepite v obseg.


Kako vedeti ime delovnega lista in delovnega zvezka z uporabo VBA v Microsoft Excelu?

Sledite spodnji kodi in korakom:-

  • Odprite stran VBA in pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija Ime delovnega lista () Ime delovnega lista = Obseg ("A1"). Parent.Name Končna funkcija

Koda Pojasnilo:- V zgornji kodi smo omenili ime funkcije, nato pa smo določili, kako vedeti ime lista.

Če želite uporabiti to formulo, morate formulo vnesti v katero koli celico na ta način: -= Ime delovnega lista (). Funkcija vrne ime lista.


Če želite ustvariti funkcijo za ime delovnega zvezka, sledite spodnjim korakom in kodi:-

  • Odprite stran VBA, pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija Ime delovnega zvezka () Ime delovnega zvezka = Ta delovna knjiga.Ime Končna funkcija

Koda Pojasnilo:-:- V zgornji kodi smo omenili ime funkcije in nato opredelili, kako spoznati ime delovnega zvezka.

Če želite uporabiti to formulo, morate formulo vnesti v katero koli celico na ta način: - = ime delovnega zvezka (). Funkcija vrne ime lista.


Kako izvleči prvo in zadnjo besedo iz celice z uporabo VBA v Microsoft Excelu?

V listu 1 imamo podatke, v katerih imamo nekaj naslovov, zato želimo izvleči zadnjo in prvo besedo iz stavka ali celice ali obsega z ustvarjanjem uporabniško določene funkcije prek VBA v Excelu.

Najprej bomo napisali funkcijo za izvlečenje prve besede. Sledite spodnjim korakom:-

  • Odprite stran VBA, pritisnite tipko Alt+F11.
  • Vstavite modul

Napišite spodnjo kodo:-

 Funkcija GETFW (Besedilo kot niz, izbirni ločevalnik kot različica) Zatemni FW kot niz, če manjka (ločevalnik) Potem je ločevalnik = "" Konec, če je FW = levo (Besedilo, InStr (1, Besedilo, Ločevalnik, vbTextCompare)) GETFW = Zamenjaj (FW , Ločevalnik, "") Končna funkcija

Koda Pojasnilo: - V zgoraj omenjeni kodi smo omenili ime funkcije s spremenljivkami. Nato smo določili merila za izvlečenje besede iz stavka ali celice.

Zdaj se bomo naučili uporabljati to formulo. Sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Uporabite to formulo v celici D9.
  • = GETFW (C9) in pritisnite Enter.
  • Funkcija bo vrnila prvo besedo iz podatkov. Zdaj, če želite pridobiti prvo besedo za vse celice, kopirajte isto formulo v obseg.

Zdaj bomo napisali kodo za izvlečenje zadnje besede iz celice. Sledite spodnji kodi:-

  • Odprite stran VBA pritisnite tipko Alt+F11.
  • Vstavite modul.
  • Napišite spodnjo kodo:-
 Funkcija GETLW (Besedilo kot niz, izbirni ločevalnik kot različica) Zatemni LW kot niz, če manjka (ločilo) Potem je ločilo = "" Konec, če je LW = StrReverse (Besedilo) LW = Levo (zadnja beseda, InStr (1, LW, Ločevalnik, vbTextCompare) ) GETLW = StrReverse (Zamenjaj (LW, separator, "")) Končna funkcija

Zdaj se bomo naučili, kako uporabiti to formulo. Sledite spodnjim korakom:-

  • Pojdite na Excelov list.
  • Uporabite to formulo v celici D9.
  • = GETLW (C9) Pritisnite Enter.
  • Funkcija bo vrnila zadnjo besedo iz podatkov. Zdaj, če želite pridobiti zadnjo besedo za vse celice, kopirajte isto formulo v obseg.

To so funkcije, ki jih lahko definiramo prek VBA in jih nato uporabimo kot formulo Excela. Prav tako lahko ustvarimo veliko več uporabniško določenih funkcij. Učite se z nami, našli bomo bolj zapletene formule.