Kaip sukurti vartotojo apibrėžtą funkciją „Microsoft Excel“

Anonim

Šiame straipsnyje mes sužinosime, kaip sukurti vartotojo apibrėžtas funkcijas „Microsoft Excel“ naudojant VBA.

Vartotojo apibrėžta funkcija:- „Microsoft Excel“ jau turi daug funkcijų, tačiau vis tiek kiekvienas turi skirtingus reikalavimus, situaciją, mes galime sukurti savo funkciją pagal reikalavimą, vadinamą vartotojo apibrėžta funkcija. Mes galime naudoti vartotojo apibrėžtą funkciją, kaip ir kitas „Excel“ funkcijas.

Žemiau pateikiamos temos, kurioms sukursime vartotojo apibrėžtą funkciją:

1). Kaip suskaičiuoti žodžių skaičių langelyje ar diapazone?

2). Kaip išgauti žodį iš sakinio ar langelio „Excel“?

3). Kaip sukurti ISO formulę?

4). Kaip sužinoti darbalapio ir darbaknygės pavadinimą naudojant VBA?

5). Kaip išgauti pirmąjį ir paskutinį žodį iš „Excel“ langelio?

Kaip sukurti vartotojo apibrėžtą funkciją, kad būtų galima suskaičiuoti žodžių skaičių langelyje ar diapazone?

1 lape turime duomenų, kuriuose turime keletą adresų, todėl norime suskaičiuoti žodžius ląstelėje ar diapazone, sukurdami vartotojo apibrėžtą funkciją per „VBA“ programoje „Excel“.

Norėdami atlikti vartotojo apibrėžtą funkciją, atlikite toliau nurodytus veiksmus:-

  • Atidarykite VBA puslapį ir paspauskite klavišą Alt+F11.
  • Įdėkite modulį.

Parašykite žemiau nurodytą kodą:

 Funkcija WORDSCOUNT (rRange As Range) Kaip ilgas Dim rCell Kaip Diapazonas Dim Count Kiek Ilgas kiekvienam rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Kitas rCell WORDSCOUNT = lSkaičiavimo pabaigos funkcija

Kodo paaiškinimai: - Norėdami atlikti vartotojo apibrėžtą funkciją, mes pradedame kodą pagal funkcijos pavadinimą ir apibrėžiame kintamuosius. Mes naudojome kodą „Kiekvienai kilpai“, kad skaičiuotume diapazono žodžius.
Kaip naudotis šia funkcija „Excel“?

Norėdami naudoti šią funkciją, atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Norėdami suskaičiuoti langelio žodžius, įveskite formulę langelyje D7.
  • = WORDSCOUNT (C7), C7 langelis yra langelis, kuriame norime apskaičiuoti žodžius.
  • Funkcija grąžins 6, o tai reiškia, kad langelyje C7 yra 6 žodžiai.
  • Norėdami atlikti tą patį skaičiavimą likusiems langeliams, nukopijuokite tą pačią formulę ir įklijuokite į diapazoną.

  • Norėdami suskaičiuoti diapazono žodžius, naudokite formulę kaip = WORDSCOUNT (C7: C16) ir paspauskite Enter.
  • Funkcija grąžins žodžių skaičių.


Pastaba:- Šis UDF padės suskaičiuoti žodžius diapazone arba viename lange.

Dabar mes parašysime kodą, kad skaičiuotume žodį, naudodami nurodytą skyriklį (,). Atlikite toliau nurodytus veiksmus:-

 Funkcija SEPARATECOUNTWORDS (rRange As Range, pasirenkamas separatorius kaip variantas) Kaip Long Dim rCell As Diapazonas Dim Count as Long If IsMissing (separator) Tada separator = "," End If For each rCellInrRange lCount = lCount + Len (Trim (rCell)) - - Len (Pakeisti (Trim (rCell), separatorius, "")) Kitas rCell SEPARATECOUNTWORDS = lCount End Function

Norėdami naudoti šią funkciją, atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Norėdami suskaičiuoti konkrečius žodžio ribotuvus, naudosime šią apibrėžtą funkciją.
  • = SEPARATECOUNTWORDS (C7) ir paspauskite Enter.
  • Funkcija grąžins konkrečių skyriklių skaičių.


Kaip išgauti žodį iš sakinio ar langelio „Microsoft Excel“ naudojant VBA?

Turime duomenų 1 lape. Kuriuose turime tam tikrus adresus, todėl norime išgauti žodžius iš sakinio, langelio ar diapazono, sukurdami vartotojo apibrėžtą funkciją per VBA programoje „Excel“.


Norėdami atlikti vartotojo apibrėžtą funkciją, atlikite toliau nurodytus veiksmus:-

  • Atidarykite VBA puslapį ir paspauskite klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija GETWORD (tekstas kaip variantas, N kaip sveikasis skaičius, pasirinktinis ribotuvas kaip variantas) kaip eilutė, jei trūksta (skiriamieji ženklai), tada ribotuvas = "" pabaiga, jei GETWORD = padalintas (tekstas, skiriamieji ženklai) (N - 1) pabaigos funkcija

Kodo paaiškinimas:- Pirmiau minėtame kode mes paminėjome funkcijos pavadinimą su kintamaisiais. Ir tada mes apibrėžėme kriterijus, kaip ištraukti žodį iš sakinio ar langelio.

Dabar mes išmoksime naudoti šią formulę. Atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Naudokite šią formulę langelyje D7.
  • = GETWORD (C7,2) ir paspauskite Enter.
  • Funkcija grąžins antrąjį žodį iš langelio, nes formulėje, kurią minėjome 2antra skaičiaus žodis. Jei norite gauti žodį, kuris yra 3rd pozicijoje, formulėje turite pakeisti skaičių nuo 2 iki 3.


Kaip sukurti ISO savaitės skaičiaus formulę „Microsoft Excel“ naudojant VBA?

Sužinosime, kaip naudojant šį UDF galime sukurti ISO savaitės skaičiaus formulę „Excel“. Šią funkciją naudosime norėdami nustatyti, ar nurodyta data priklauso metų savaitės skaičiui.

Lape turime datos sąrašą, o antrame stulpelyje norime gauti savaitės numerius.

Norėdami sukurti šio reikalavimo UDF, atlikite toliau nurodytus veiksmus:-

  • Atidarykite VBA puslapį ir paspauskite klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija ISOWEEKNUMBER (nurodyti kaip datą) Kaip ilga Dim Dt kaip data Dt = DateSerial (metai (nurodyti - savaitės diena (rodyti - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((nurodyti - Dt + savaitės diena (Dt) + 5) ) / 7) pabaigos funkcija

Kodo paaiškinimas:-:- Pirmiau pateiktame kode mes paminėjome funkcijos pavadinimą su kintamaisiais. Tada mes nustatėme datos vertę ir tada nustatėme funkcijos „ISOWEENUMBER“ kriterijus.
Kaip galime naudoti šią funkciją „Excel“ faile?

  • Eikite į „Excel“ lapą.
  • Įveskite formulę langelyje D7.
  • = ISOWEEKNUMBER (C7) ir paspauskite Enter.
  • Funkcija grąžins ląstelėje įvestos datos savaitę. Dabar, kad gautumėte kiekvienos datos savaitės numerį, nukopijuokite tą pačią formulę diapazone.


Dabar mes sužinosime, kaip grąžinti ISO standartų metų pradžią „Excel“- pirmąjį metų pirmadienį.

Ši funkcija iš esmės patikrins, ar 1st Metų pirmadienis patenka į kurią datą ir tada bus pradėtas skaičiuoti savaičių skaičius nuo tos datos. Pažiūrėkime, kaip galime sukurti UDF šiam reikalavimui.


Atlikite toliau nurodytus veiksmus:-

  • Atidarykite VBA puslapį ir paspauskite klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija ISOSTYR (metai kaip sveikasis skaičius) Kaip data Dim WD kaip sveikasis skaičius NY kaip data NY = DateSerial (metai, 1, 1) WD = (NY - 2) Mod 7 Jei WD <4, tada ISOSTYR = NY - WD Kita ISOSTYR = NY - WD + 7 pabaiga, jei pabaiga

Kodo paaiškinimas: - Pirmiau pateiktame kode mes paminėjome funkcijos pavadinimą su kintamaisiais. Tada mes nustatėme kintamųjų kriterijus ir tada apibrėžėme įvesties formulę.

Jums tiesiog reikia pateikti 2001 metus tokiu formatu ir formulė duos jums 1st Metų pirmadienis.

Dabar mes išmoksime naudoti UDF „Excel“ faile. Atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Įveskite formulę langelyje D7.
  • = ISOSTYR (C7) ir paspauskite Enter.
  • Funkcija grąžins 1 datąst Naujųjų metų pirmosios savaitės pirmadienis.
  • Norėdami grąžinti datą 1st Naujųjų metų pirmosios savaitės pirmadienį nukopijuokite tą pačią formulę ir įklijuokite į diapazoną.


Kaip sužinoti darbalapio ir darbaknygės pavadinimą naudojant „Microsoft Excel“ VBA?

Vykdykite toliau pateiktą kodą ir veiksmus:-

  • Atidarykite VBA puslapį ir paspauskite klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija Darbo lapo pavadinimas () Darbo lapo pavadinimas = Diapazonas („A1“). Parent.Name Pabaigos funkcija

Kodo paaiškinimas:- Pirmiau pateiktame kode mes paminėjome funkcijos pavadinimą ir tada apibrėžėme, kaip žinoti lapo pavadinimą.

Norėdami naudoti šią formulę, tiesiog įveskite formulę į bet kurį langelį tokiu būdu: -= Darbo lapo pavadinimas (). Funkcija grąžins lapo pavadinimą.


Norėdami sukurti funkciją darbaknygės pavadinimui, atlikite toliau nurodytus veiksmus ir kodą:-

  • Atidarykite VBA puslapį paspausdami klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija Darbaknygės pavadinimas () Darbaknygės pavadinimas = ThisWorkbook.Name Pabaigos funkcija

Kodo paaiškinimas:-:- Pirmiau pateiktame kode mes paminėjome funkcijos pavadinimą ir tada apibrėžėme, kaip susipažinti su darbaknygės pavadinimu.

Norėdami naudoti šią formulę, tiesiog įveskite formulę į bet kurį langelį tokiu būdu: - = Darbaknygės pavadinimas (). Funkcija grąžins lapo pavadinimą.


Kaip išgauti pirmą ir paskutinį žodį iš langelio naudojant „Microsoft Excel“ VBA?

Turime duomenų 1 lape, kuriame turime tam tikrus adresus, todėl norime išgauti paskutinį ir pirmąjį žodį iš sakinio ar langelio ar diapazono, sukurdami vartotojo apibrėžtą funkciją per VBA programoje „Excel“.

Pirmiausia parašysime funkciją išgauti pirmąjį žodį. Atlikite toliau nurodytus veiksmus:-

  • Atidarykite VBA puslapį paspausdami klavišą Alt+F11.
  • Įdėkite modulį

Parašykite žemiau nurodytą kodą:-

 Funkcija GETFW (tekstas kaip eilutė, pasirinktinis skirstytuvas kaip variantas) Dim FW kaip eilutė, jei trūksta (separatorius) Tada Separator = "" Pabaiga, jei FW = kairė (tekstas, InStr (1, tekstas, atskyriklis, vbTextCompare)) GETFW = Pakeisti (FW , Separator, "") pabaigos funkcija

Kodo paaiškinimas: - Pirmiau minėtame kode mes paminėjome funkcijos pavadinimą su kintamaisiais. Ir tada mes apibrėžėme kriterijus, kaip ištraukti žodį iš sakinio ar langelio.

Dabar mes išmoksime naudoti šią formulę. Atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Naudokite šią formulę langelyje D9.
  • = GETFW (C9) ir paspauskite Enter.
  • Funkcija grąžins pirmąjį žodį iš duomenų. Dabar, norėdami gauti pirmąjį žodį visoms ląstelėms, nukopijuokite tą pačią formulę diapazone.

Dabar parašysime kodą, kad ištrauktume paskutinį žodį iš langelio. Sekite žemiau nurodytą kodą:-

  • Atidarykite VBA puslapį paspausdami klavišą Alt+F11.
  • Įdėkite modulį.
  • Parašykite žemiau nurodytą kodą:-
 Funkcija GETLW (tekstas kaip eilutė, pasirinktinis separatorius kaip variantas) Dim LW kaip eilutė, jei yra trūkumų (separatorius), tada Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (pakeisti (LW, separatorius, "")) pabaigos funkcija

Dabar mes išmoksime naudoti šią formulę. Atlikite toliau nurodytus veiksmus:-

  • Eikite į „Excel“ lapą.
  • Naudokite šią formulę langelyje D9.
  • = GETLW (C9) Paspauskite „Enter“.
  • Funkcija grąžins paskutinį žodį iš duomenų. Dabar, norėdami gauti paskutinį žodį visoms ląstelėms, nukopijuokite tą pačią formulę diapazone.

Tai yra funkcijos, kurias galime apibrėžti per VBA, o tada galime naudoti kaip „Excel“ formulę. Be to, galime sukurti daug daugiau vartotojo apibrėžtų funkcijų. Toliau mokykitės su mumis, mes sugalvosime sudėtingesnių formulių.