Kako stvoriti korisnički definiranu funkciju u programu Microsoft Excel

Anonim

U ovom ćemo članku naučiti kako stvoriti korisnički definirane funkcije u programu Microsoft Excel pomoću VBA.

Korisnički definirana funkcija:- Microsoft Excel već ima mnogo funkcija, ali ipak svatko ima različite zahtjeve, situaciju, možemo stvoriti vlastitu funkciju prema zahtjevu koji se naziva Korisnički definirana funkcija. Korisnički definiranu funkciju možemo koristiti kao i ostale funkcije u Excelu.

Ispod su teme za koje ćemo stvoriti korisnički definiranu funkciju:

1). Kako prebrojiti broj riječi u ćeliji ili rasponu?

2). Kako izdvojiti riječ iz rečenice ili ćelije u Excelu?

3). Kako stvoriti formulu za ISO?

4). Kako saznati naziv radnog lista i radne knjige koristeći VBA?

5). Kako izdvojiti prvu i posljednju riječ iz ćelije u Excelu?

Kako stvoriti korisnički definiranu funkciju za brojanje broja riječi u ćeliji ili rasponu?

Imamo podatke u tablici 1 u kojima imamo neke adrese pa želimo brojati riječi u ćeliji ili rasponu stvaranjem korisnički definirane funkcije putem VBA u Excelu.

Da biste korisnički definiranu funkciju, slijedite dolje navedene korake:-

  • Otvorite VBA stranicu i pritisnite tipke Alt+F11.
  • Umetnite modul.

Napišite dolje navedeni kod:

 Funkcija WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count As Long Za svaki rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Sljedeća rCell WORDSCOUNT = l Funkcija završetka računa

Objašnjenja koda: - Kako bi korisnički definirana funkcija pokrenula kod za naziv funkcije i definirala varijable. Koristili smo "Za svaku petlju" u kodu za brojanje riječi u rasponu.
Kako koristiti ovu funkciju u Excelu?

Da biste koristili ovu funkciju, slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Za brojanje riječi za ćeliju unesite formulu u ćeliju D7.
  • = WORDSCOUNT (C7), ćelija C7 je ćelija u kojoj želimo izračunati riječi.
  • Funkcija će vratiti 6, što znači da ćelija C7 sadrži 6 riječi.
  • Da biste izvršili isti izračun za ostale ćelije, kopirajte istu formulu i zalijepite u raspon.

  • Za prebrojavanje riječi u rasponu upotrijebite formulu kao = WORDSCOUNT (C7: C16) i pritisnite Enter.
  • Funkcija će vratiti broj riječi.


Napomena:- Ovaj UDF bit će koristan za brojanje riječi u rasponu ili u jednoj ćeliji.

Sada ćemo napisati kôd za brojanje riječi pomoću navedenog razdjelnika (,). Slijedite dolje navedene korake:-

 Funkcija SEPARATECOUNTWORDS (rRange As Range, Optional separator As Variant) Dugačka dim rCell As Range Dim Count As Long If IsMissing (separator) Then separator = "," End If For Each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Zamijenite (Trim (rCell), separator, "")) Sljedeće rCell SEPARATECOUNTWORDS = lZavršna funkcija

Da biste koristili ovu funkciju, slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Za brojanje određenih graničnika u riječi koristit ćemo ovu definiranu funkciju.
  • = SEPARATECOUNTWORDS (C7) i pritisnite Enter.
  • Funkcija će vratiti broj posebnih graničnika.


Kako izdvojiti riječ iz rečenice ili ćelije u programu Microsoft Excel pomoću VBA?

Imamo podatke u listu 1. U kojima imamo neke adrese pa želimo izdvojiti riječi iz rečenice ili ćelije ili raspona stvaranjem korisnički definirane funkcije putem VBA u Excelu.


Da biste korisnički definiranu funkciju, slijedite dolje navedene korake:-

  • Otvorite VBA stranicu i pritisnite tipke Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija GETWORD (Tekst kao varijanta, N kao cijeli broj, izborni razdjelnik kao varijanta) Kao niz ako nedostaje (razdjelnik) Zatim razdjelnik = "" Kraj Ako je GETWORD = Razdjeljak (tekst, razdjelnik) (N - 1) Kraj funkcija

Objašnjenje koda:- U gore spomenutom kodu spomenuli smo naziv funkcije s varijablama. Zatim smo definirali kriterije za izdvajanje riječi iz rečenice ili ćelije.

Sada ćemo naučiti kako koristiti ovu formulu. Slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Koristite ovu formulu u ćeliji D7.
  • = GETWORD (C7,2) i pritisnite Enter.
  • Funkcija će vratiti drugu riječ iz ćelije jer smo u formuli koju smo spomenuli za 2nd broj riječi. Ako želite dohvatiti riječ koja se nalazi na 3rd položaj, morate promijeniti broj iz 2 u 3 u formuli.


Kako stvoriti formulu broja ISO tjedna u Microsoft Excelu pomoću VBA?

Naučit ćemo kako možemo stvoriti ISO formulu broja tjedna u Excelu s ovim UDF -om. Ovu ćemo funkciju koristiti za identifikaciju da spomenuti datum pripada broju tjedna u godini.

Na listu imamo popis datuma, a u drugom stupcu želimo dohvatiti brojeve tjedana.

Da biste stvorili UDF za ovaj zahtjev, slijedite dolje navedene korake:-

  • Otvorite VBA stranicu i pritisnite tipke Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Godina (Indate - Radni dan (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + radni dan (Dt) + 5 ) / 7) Funkcija završetka

Objašnjenje koda:-:- U gornjem kodu smo spomenuli naziv funkcije s varijablama. Zatim smo postavili vrijednost datuma i definirali kriterije funkcije "ISOWEENUMBER".
Kako možemo koristiti ovu funkciju u Excel datoteci?

  • Idite na Excel tablicu.
  • Unesite formulu u ćeliju D7.
  • = ISOWEEKNUMBER (C7) i pritisnite Enter.
  • Funkcija će vratiti tjedan za unijeti datum u ćeliju. Sada da biste dohvatili broj tjedna za svaki datum, kopirajte istu formulu u rasponu.


Sada ćemo naučiti kako vratiti ISO standarde početkom godine u Excelu- Prvi ponedjeljak u godini.

Ova funkcija će u osnovi provjeriti je li 1sv Na koji datum će padati ponedjeljak u godini, a zatim će početi računati broj tjedana od tog datuma. Pogledajmo kako možemo stvoriti UDF za ovaj zahtjev.


Slijedite dolje navedene korake:-

  • Otvorite VBA stranicu i pritisnite tipke Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija ISOSTYR (Godina kao cijeli broj) Kao datum Dim WD Kao cijeli broj Dim NY Kao datum NY = DatumSerija (Godina, 1, 1) WD = (NY - 2) Mod 7 Ako je WD <4 Tada je ISOSTYR = NY - WD Ostali ISOSTYR = NY - WD + 7 Završi ako završi funkcija

Objašnjenje koda: - U gornjem kodu smo spomenuli naziv funkcije s varijablama. Zatim smo postavili kriterije za varijable i definirali ulaz formule.

Samo trebate navesti 2001. godinu u ovom formatu, a formula će vam dati 1sv Ponedjeljak u godini.

Sada ćemo naučiti kako koristiti UDF u Excel datoteci. Slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Unesite formulu u ćeliju D7.
  • = ISOSTYR (C7) i pritisnite Enter.
  • Funkcija će vratiti datum 1sv Ponedjeljak prvog tjedna Nove godine.
  • Za vraćanje datuma 1sv U ponedjeljak prvog tjedna Nove godine kopirajte istu formulu i zalijepite u raspon.


Kako saznati naziv radnog lista i radne knjige pomoću VBA u programu Microsoft Excel?

Slijedite dolje navedene kodove i korake:-

  • Otvorite VBA stranicu i pritisnite tipke Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija Naziv radnog lista () Naziv radnog lista = Raspon ("A1"). Roditelj.Naziv Kraj funkcija

Objašnjenje koda:- U gornjem kodu smo spomenuli naziv funkcije, a zatim smo definirali kako znati naziv lista.

Da biste koristili ovu formulu, samo trebate unijeti formulu u bilo koju ćeliju na ovaj način: -= Naziv radnog lista (). Funkcija će vratiti naziv lista.


Da biste stvorili funkciju za naziv radne knjige, slijedite dolje navedene korake i kôd:-

  • Otvorite VBA stranicu pritisnite tipku Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija Naziv radne knjige () Naziv radne knjige = Ova radna knjiga.Naziv Funkcija završetka

Kôd Objašnjenje:-:- U gornjem kodu smo spomenuli naziv funkcije, a zatim smo definirali kako upoznati naziv radne knjige.

Da biste koristili ovu formulu, samo trebate unijeti formulu u bilo koju ćeliju na ovaj način: - = naziv radne knjige (). Funkcija će vratiti naziv lista.


Kako izdvojiti prvu i posljednju riječ iz ćelije pomoću VBA u Microsoft Excelu?

Imamo podatke u listu 1 u kojem imamo neke adrese pa želimo izdvojiti posljednju i prvu riječ iz rečenice ili ćelije ili raspona stvaranjem korisnički definirane funkcije putem VBA u Excelu.

Prvo ćemo napisati funkciju za izdvajanje prve riječi. Slijedite dolje navedene korake:-

  • Otvorite VBA stranicu pritisnite tipku Alt+F11.
  • Umetnite modul

Napišite dolje navedeni kod:-

 Funkcija GETFW (Tekst kao niz, opcijski separator kao varijanta) Zatamni FW kao niz ako nedostaje (separator) Zatim separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Replace (FW , Odvajač, "") Funkcija završetka

Objašnjenje koda: - U gore spomenutom kodu spomenuli smo naziv funkcije s varijablama. Zatim smo definirali kriterije za izdvajanje riječi iz rečenice ili ćelije.

Sada ćemo naučiti kako koristiti ovu formulu. Slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Koristite ovu formulu u ćeliji D9.
  • = GETFW (C9) i pritisnite Enter.
  • Funkcija će vratiti prvu riječ iz podataka. Sada, da biste dohvatili prvu riječ za sve ćelije, kopirajte istu formulu u raspon.

Sada ćemo napisati kôd za izdvajanje posljednje riječi iz ćelije. Slijedite dolje navedeni kod:-

  • Otvorite VBA stranicu pritisnite tipku Alt+F11.
  • Umetnite modul.
  • Napišite dolje navedeni kod:-
 Funkcija GETLW (Tekst kao niz, opcijski separator kao varijanta) Zatamni LW kao niz ako nedostaje (separator) Tada je separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Zamijeni (LW, separator, "")) Krajnja funkcija

Sada ćemo naučiti kako koristiti ovu formulu. Slijedite dolje navedene korake:-

  • Idite na Excel tablicu.
  • Koristite ovu formulu u ćeliji D9.
  • = GETLW (C9) Pritisnite Enter.
  • Funkcija će vratiti zadnju riječ iz podataka. Sada, da biste dohvatili posljednju riječ za sve ćelije, kopirajte istu formulu u raspon.

Ovo su funkcije koje možemo definirati kroz VBA, a zatim ih koristiti kao formulu Excela. Također, možemo stvoriti još mnogo korisnički definiranih funkcija. Nastavite učiti s nama, smislit ćemo još zamršenije formule.