Cum se creează o funcție definită de utilizator în Microsoft Excel

Anonim

În acest articol, vom învăța cum să creăm funcții definite de utilizator în Microsoft Excel utilizând VBA.

Funcția definită de utilizator: - Microsoft Excel are deja multe funcții, dar totuși toată lumea are cerințe diferite, situație, ne putem crea propria funcție conform cerinței numite Funcție definită de utilizator. Putem folosi funcția Definită de utilizator ca și alte funcții din Excel.

Mai jos sunt subiectele pentru care vom crea funcția definită de utilizator:

1). Cum se numără numărul de cuvinte din celulă sau din interval?

2). Cum se extrage un cuvânt dintr-o propoziție sau o celulă în Excel?

3). Cum se creează formula ISO?

4). Cum să cunoașteți foaia de lucru și numele registrului de lucru folosind VBA?

5). Cum se extrag primul și ultimul cuvânt dintr-o celulă din Excel?

Cum se creează funcția definită de utilizator pentru a număra numărul de cuvinte din celulă sau interval?

Avem date în foaia 1 în care avem niște adrese, așa că dorim să numărăm cuvintele dintr-o celulă sau dintr-un interval prin crearea funcției definite de utilizator prin VBA în Excel.

Pentru a face funcția definită de utilizator, urmați pașii de mai jos: -

  • Deschideți pagina VBA și apăsați tasta Alt + F11.
  • Introduceți un modul.

Scrieți codul menționat mai jos:

 Funcție WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count As Long For Each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Următorul rCell WORDSCOUNT = lCount Funcția de sfârșit

Explicații despre cod: - Pentru a face funcția definită de utilizator, începem codul pentru a numi funcția și a defini variabilele. Am folosit „Pentru fiecare buclă” în cod pentru a număra cuvintele din interval.
Cum se folosește această funcție în Excel?

Pentru a utiliza această funcție, urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Pentru a număra cuvintele pentru o celulă, introduceți formula în celula D7.
  • = WORDSCOUNT (C7), celula C7 este celula în care vrem să calculăm cuvintele.
  • Funcția va returna 6, ceea ce înseamnă că celula C7 conține 6 cuvinte.
  • Pentru a face același calcul pentru restul celulelor, copiați aceeași formulă și lipiți în interval.

  • Pentru a număra cuvintele din interval, utilizați formula ca = WORDSCOUNT (C7: C16) și apăsați Enter.
  • Funcția va returna numărul de cuvinte.


Notă: - Acest UDF va fi util pentru a număra cuvintele dintr-un interval sau într-o singură celulă.

Acum vom scrie codul pentru a număra cuvântul folosind delimitatorul specificat (,). Urmați pașii de mai jos: -

 Funcția 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 (Înlocuiți (Trim (rCell), separator, "")) Următorul rCell SEPARATECOUNTWORDS = lCount Funcția finală

Pentru a utiliza această funcție, urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Pentru a număra delimitatorii specifici din cuvânt, vom folosi această funcție definită.
  • = SEPARATECOUNTWORDS (C7) și apăsați Enter.
  • Funcția va returna numărul delimitatorilor specifici.


Cum se extrage un cuvânt dintr-o propoziție sau o celulă din Microsoft Excel folosind VBA?

Avem date în foaia 1. În care avem câteva adrese, așa că dorim să extragem cuvintele dintr-o propoziție sau dintr-o celulă sau dintr-un interval, creând funcția definită de utilizator prin VBA în Excel.


Pentru a face funcția definită de utilizator, urmați pașii de mai jos: -

  • Deschideți pagina VBA și apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos:-
 Funcția GETWORD (Text ca variantă, N ca întreg, Delimitator opțional Ca variantă) Ca șir Dacă lipsește (Delimitator), atunci Delimitator = "" Închidere Dacă GETWORD = Divizat (Text, Delimitator) (N - 1) Funcție de sfârșit

Explicarea codului: - În codul menționat mai sus, am menționat numele funcției cu variabilele. Și apoi am definit criteriile pentru a extrage cuvântul din propoziție sau celulă.

Acum vom învăța cum să folosim această formulă. Urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Utilizați această formulă în celula D7.
  • = GETWORD (C7,2) și apăsați Enter.
  • Funcția va returna al doilea cuvânt din celulă, deoarece în formula pe care am menționat-o pentru 2nd cuvântul numărului. Dacă doriți să recuperați cuvântul care este plasat la 3rd poziție, trebuie să schimbați numărul de la 2 la 3 în formulă.


Cum se creează formula numărului săptămânii ISO în Microsoft Excel utilizând VBA?

Vom afla cum putem crea formula numărului săptămânii ISO în Excel cu acest UDF. Această funcție o vom folosi pentru a identifica faptul că data menționată aparține numărului săptămânii al anului.

Avem o listă de date în foaie, iar în a doua coloană dorim să preluăm numerele săptămânii.

Pentru a crea UDF pentru această cerință, urmați pașii de mai jos:-

  • Deschideți pagina VBA și apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos: -
 Funcția ISOWEEKNUMBER (Indicați ca dată) Lungime lungă Dt Ca dată Dt = Data Serial (An (Indicați - Ziua săptămânii (Indicați - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indicați - Dt + Ziua săptămânii (Dt) + 5 ) / 7) Funcția de sfârșit

Explicarea codului: -: - În codul de mai sus, am menționat numele funcției cu variabilele. Și apoi am stabilit valoarea datei și apoi am definit criteriile funcției „ISOWEENUMBER”.
Cum putem folosi această funcție în fișierul nostru Excel?

  • Accesați foaia Excel.
  • Introduceți formula în celula D7.
  • = ISOWEEKNUMBER (C7) și apăsați Enter.
  • Funcția va reveni săptămâna pentru data introdusă în celulă. Acum, pentru a prelua numărul săptămânii pentru fiecare dată, copiați aceeași formulă în interval.


Acum vom învăța cum să returnăm standardele ISO începutul anului în Excel - Prima Luni a anului.

Această funcție va verifica practic că 1Sf Luni din an va cădea la ce dată și apoi va începe să calculeze numărul de săptămâni de la acea dată. Să vedem cum putem crea UDF pentru această cerință.


Urmați pașii de mai jos:-

  • Deschideți pagina VBA și apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos:-
 Funcția ISOSTYR (An ca întreg) Ca Data Dim WD Ca Integer Dim NY Ca Data NY = DateSerial (Year, 1, 1) WD = (NY - 2) Mod 7 Dacă WD <4 Atunci ISOSTYR = NY - WD Altele ISOSTYR = NY - WD + 7 End If End Function

Explicarea codului: - În codul de mai sus, am menționat numele funcției cu variabilele. Și apoi am stabilit criteriile pentru variabile și apoi am definit definiția intrării.

Trebuie doar să furnizați anul 2001 în acest format și formula vă va oferi 1Sf Luni din an.

Acum vom învăța cum să utilizăm UDF în fișier Excel. Urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Introduceți formula în celula D7.
  • = ISOSTYR (C7) și apăsați Enter.
  • Funcția va returna data 1Sf Luni din prima săptămână de Anul Nou.
  • Pentru a returna data de 1Sf Luni din prima săptămână de Anul Nou, copiați aceeași formulă și lipiți în interval.


Cum să cunoașteți foaia de lucru și numele registrului de lucru utilizând VBA în Microsoft Excel?

Urmați mai jos codul dat și pașii:-

  • Deschideți pagina VBA și apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos:-
 Funcție Nume foaie de lucru () Nume foaie de lucru = Interval ("A1"). Parent.Name Sfârșit Funcție

Explicarea codului: - În codul de mai sus, am menționat numele funcției și apoi am definit cum să știm numele foii.

Pentru a utiliza această formulă, trebuie doar să introduceți formula în orice celulă în acest fel: - = Worksheetname (). Funcția va returna numele foii.


Pentru a crea funcția pentru numele registrului de lucru, urmați pașii și codul de mai jos: -

  • Deschideți pagina VBA, apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos: -
 Funcție Nume registru de lucru () Nume registru de lucru = ThisWorkbook.Name End Function

Explicația codului: -: - În codul de mai sus, am menționat numele funcției și apoi am definit cum să cunoaștem numele registrului de lucru.

Pentru a utiliza această formulă, trebuie doar să introduceți formula în orice celulă în acest fel: - = Workbookname (). Funcția va returna numele foii.


Cum se extrage primul și ultimul cuvânt dintr-o celulă utilizând VBA în Microsoft Excel?

Avem date în foaia 1 în care avem niște adrese, așa că dorim să extragem ultimul și primul cuvânt dintr-o propoziție sau celulă sau dintr-un interval prin crearea funcției definite de utilizator prin VBA în Excel.

În primul rând, vom scrie funcția pentru a extrage primul cuvânt. Vă rugăm să urmați pașii de mai jos: -

  • Deschideți pagina VBA, apăsați tasta Alt + F11.
  • Introduceți un modul

Scrieți codul menționat mai jos:-

 Funcția GETFW (Text ca șir, opțional Separator ca variantă) Dim FW ca șir Dacă lipsește (Separator) Atunci Separator = "" End if FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Înlocuire (FW , Separator, "") Funcție finală

Explicarea codului: - În codul menționat mai sus, am menționat numele funcției cu variabilele. Și apoi am definit criteriile pentru a extrage cuvântul din propoziție sau celulă.

Acum vom învăța cum să folosim această formulă. Urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Utilizați această formulă în celula D9.
  • = GETFW (C9) și apăsați Enter.
  • Funcția va returna primul cuvânt din date. Acum, pentru a prelua primul cuvânt pentru toate celulele, copiați aceeași formulă în interval.

Acum vom scrie codul pentru a extrage ultimul cuvânt din celulă. Urmați codul menționat mai jos: -

  • Deschideți pagina VBA, apăsați tasta Alt + F11.
  • Introduceți un modul.
  • Scrieți codul menționat mai jos:-
 Funcția GETLW (Text ca șir, separator opțional ca variantă) Dim LW ca șir Dacă lipsește (Separator) Atunci Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Înlocuire (LW, Separator, "")) Funcția de finalizare

Acum vom învăța cum să folosim această formulă. Urmați pașii de mai jos: -

  • Accesați foaia Excel.
  • Utilizați această formulă în celula D9.
  • = GETLW (C9) Apăsați Enter.
  • Funcția va returna ultimul cuvânt din date. Acum, pentru a prelua ultimul cuvânt pentru toate celulele, copiați aceeași formulă în interval.

Acestea sunt funcțiile pe care le putem defini prin VBA și apoi le putem folosi ca formulă Excel. De asemenea, putem crea mult mai multe funcții definite de utilizator. Continuă să înveți cu noi, vom veni cu formule mai dificile.