Kā izveidot lietotāja definētu funkciju programmā Microsoft Excel

Anonim

Šajā rakstā mēs uzzināsim, kā izveidot lietotāja definētas funkcijas programmā Microsoft Excel, izmantojot VBA.

Lietotāja definēta funkcija:- Microsoft Excel jau ir daudz funkciju, bet tomēr katram ir atšķirīgas prasības, situācija, mēs varam izveidot savu funkciju atbilstoši prasībai, ko sauc par lietotāja definētu funkciju. Mēs varam izmantot lietotāja definētu funkciju tāpat kā citas funkcijas programmā Excel.

Tālāk ir norādītas tēmas, kurām mēs izveidosim lietotāja definētu funkciju:

1). Kā saskaitīt vārdu skaitu šūnā vai diapazonā?

2). Kā izvilkt vārdu no teikuma vai šūnas programmā Excel?

3). Kā izveidot ISO formulu?

4). Kā uzzināt darblapas un darbgrāmatas nosaukumu, izmantojot VBA?

5). Kā iegūt pirmo un pēdējo vārdu no šūnas programmā Excel?

Kā izveidot lietotāja definētu funkciju, lai saskaitītu vārdu skaitu šūnā vai diapazonā?

Mums ir dati 1. lapā, kurā mums ir dažas adreses, tāpēc mēs vēlamies saskaitīt vārdus šūnā vai diapazonā, izveidojot lietotāja definētu funkciju, izmantojot programmu VBA programmā Excel.

Lai izveidotu lietotāja definētu funkciju, lūdzu, rīkojieties šādi:-

  • Atveriet VBA lapu un nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.

Uzrakstiet zemāk minēto kodu:

 Funkcija WORDSCOUNT (rRange kā diapazons) Tik garš Dim rCell Kā Diapazons Dim Count Katram rCellInrRange lCount = lCount + Len (Apgriezt (rCell)) - Len (Aizstāt (Apgriezt (rCell), "", "")) + 1 Nākamais rCell WORDSCOUNT = lCount beigu funkcija

Koda skaidrojumi: - Lai lietotājs definētu funkciju, mēs sākam kodu ar funkcijas nosaukumu un definējam mainīgos. Mēs esam izmantojuši kodu “Katrai cilpai”, lai skaitītu vārdus diapazonā.
Kā izmantot šo funkciju programmā Excel?

Lai izmantotu šo funkciju, rīkojieties šādi:-

  • Dodieties uz Excel lapu.
  • Lai saskaitītu šūnas vārdus, ievadiet formulu šūnā D7.
  • = WORDSCOUNT (C7), šūna C7 ir šūna, kurā mēs vēlamies aprēķināt vārdus.
  • Funkcija atgriezīs 6, kas nozīmē, ka šūna C7 satur 6 vārdus.
  • Lai veiktu to pašu aprēķinu pārējām šūnām, nokopējiet to pašu formulu un ielīmējiet diapazonā.

  • Lai saskaitītu diapazonā esošos vārdus, izmantojiet formulu kā = WORDSCOUNT (C7: C16) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs vārdu skaitu.


Piezīme:- Šis UDF būs noderīgs, lai saskaitītu vārdus diapazonā vai vienā šūnā.

Tagad mēs uzrakstīsim kodu, lai skaitītu vārdu, izmantojot norādīto norobežotāju (,). Izpildiet tālāk norādītās darbības:-

 Funkcija SEPARATECOUNTWORDS (rRange kā diapazons, pēc izvēles atdalītājs kā variants) Tik garš Dim rCell Kā diapazons Dim skaitīt tik ilgi, ja IsMissing (separators) Tad separator = "," End If for each rCellInrRange lCount = lCount + Len (Trim (rCell)) - - Len (Aizstāt (Apgriezt (rCell), atdalītājs, "")) Nākamais rCell SEPARATECOUNTWORDS = lCount Beigu funkcija

Lai izmantotu šo funkciju, rīkojieties šādi:-

  • Dodieties uz Excel lapu.
  • Lai saskaitītu konkrētos norobežotājus vārdā, mēs izmantosim šo definēto funkciju.
  • = SEPARATECOUNTWORDS (C7) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs konkrēto norobežotāju skaitu.


Kā iegūt vārdu no teikuma vai šūnas programmā Microsoft Excel, izmantojot VBA?

Mums ir dati 1. lapā. Kurās mums ir dažas adreses, tāpēc mēs vēlamies izvilkt vārdus no teikuma vai šūnas vai diapazona, izveidojot lietotāja definētu funkciju, izmantojot programmu VBA programmā Excel.


Lai izveidotu lietotāja definētu funkciju, lūdzu, rīkojieties šādi:-

  • Atveriet VBA lapu un nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Uzrakstiet zemāk minēto kodu:-
 Funkcija GETWORD (teksts kā variants, N kā vesels skaitlis, pēc izvēles norobežotājs kā variants) kā virkne, ja trūkst (norobežotājs), tad atdalītājs = "" beigas, ja GETWORD = sadalīts (teksts, norobežotājs) (N - 1) beigu funkcija

Koda skaidrojums:- Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu ar mainīgajiem. Un tad mēs bijām definējuši kritērijus, kā iegūt vārdu no teikuma vai šūnas.

Tagad mēs iemācīsimies izmantot šo formulu. Izpildiet tālāk norādītās darbības:-

  • Dodieties uz Excel lapu.
  • Izmantojiet šo formulu šūnā D7.
  • = GETWORD (C7,2) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs otro vārdu no šūnas, jo formulā, kuru mēs minējām 2nd skaitļa vārds. Ja vēlaties izgūt vārdu, kas ievietots 3rd pozīcijā, formulā jāmaina skaitlis no 2 uz 3.


Kā izveidot ISO nedēļas skaitļa formulu programmā Microsoft Excel, izmantojot VBA?

Mēs uzzināsim, kā mēs varam izveidot ISO nedēļas skaitļa formulu programmā Excel, izmantojot šo UDF. Šo funkciju mēs izmantosim, lai noteiktu, vai minētais datums pieder pie gada nedēļas numura.

Lapā ir datumu saraksts, un otrajā slejā mēs vēlamies iegūt nedēļas numurus.

Lai izveidotu UDF šai prasībai, veiciet tālāk norādītās darbības:-

  • Atveriet VBA lapu un nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Ierakstiet zemāk minēto kodu:-
 Funkcija ISOWEEKNUMBER (norādīt kā datumu) Tik ilgi Dim Dt kā datums Dt = DateSerial (Gads (norādīt - nedēļas diena (norādīt - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((norādīt - Dt + nedēļas diena (Dt) + 5) ) / 7) beigu funkcija

Koda skaidrojums:-:- Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu ar mainīgajiem. Un tad mēs bijām iestatījuši datuma vērtību un pēc tam definējuši funkcijas “ISOWEENUMBER” kritērijus.
Kā mēs varam izmantot šo funkciju savā Excel failā?

  • Dodieties uz Excel lapu.
  • Ievadiet formulu šūnā D7.
  • = ISOWEEKNUMBER (C7) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs šūnā ievadītā datuma nedēļu. Tagad, lai izgūtu katra datuma nedēļas numuru, kopējiet to pašu formulu diapazonā.


Tagad mēs iemācīsimies atgriezt ISO standartu gada sākumu programmā Excel- gada pirmā pirmdiena.

Šī funkcija būtībā pārbaudīs, vai 1st Gada pirmdiena nokritīs kurā datumā, un tad tā sāks aprēķināt nedēļu skaitu no šī datuma. Apskatīsim, kā mēs varam izveidot UDF šai prasībai.


Izpildiet tālāk norādītās darbības:-

  • Atveriet VBA lapu un nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Uzrakstiet zemāk minēto kodu:-
 Funkcija ISOSTYR (gads kā vesels skaitlis) Kā datums Dim WD kā vesels skaitlis Dim NY kā datums NY = DateSerial (gads, 1, 1) WD = (NY - 2) Mod 7 Ja WD <4, tad ISOSTYR = NY - WD Cits ISOSTYR = NY - WD + 7 beigu funkcija

Koda skaidrojums: - Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu ar mainīgajiem. Un tad mēs bijām noteikuši mainīgo lieluma kritērijus un pēc tam definējuši formulas ievadi.

Jums vienkārši jānorāda 2001. gads šādā formātā, un formula sniegs jums 1st Gada pirmdiena.

Tagad mēs iemācīsimies izmantot UDF Excel failā. Izpildiet tālāk norādītās darbības:-

  • Dodieties uz Excel lapu.
  • Ievadiet formulu šūnā D7.
  • = ISOSTYR (C7) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs datumu 1st Jaunā gada pirmās nedēļas pirmdiena.
  • Lai atgrieztu datumu 1st Jaunā gada pirmās nedēļas pirmdienā kopējiet to pašu formulu un ielīmējiet diapazonā.


Kā uzzināt darblapas un darbgrāmatas nosaukumu, izmantojot Microsoft Excel VBA?

Izpildiet tālāk norādīto kodu un darbības:-

  • Atveriet VBA lapu un nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Uzrakstiet zemāk minēto kodu:-
 Funkcija Darblapas nosaukums () Darblapas nosaukums = Diapazons ("A1"). Vecāks.Nosaukums Beigu funkcija

Koda skaidrojums:- Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu, un tad mēs esam definējuši, kā uzzināt lapas nosaukumu.

Lai izmantotu šo formulu, jums vienkārši jāievada formula jebkurā šūnā šādā veidā: -= Darblapas nosaukums (). Funkcija atgriezīs lapas nosaukumu.


Lai izveidotu funkciju darbgrāmatas nosaukumam, veiciet tālāk norādītās darbības un kodu:-

  • Atveriet VBA lapu, nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Ierakstiet zemāk minēto kodu:-
 Funkcija Darbgrāmatas nosaukums () Darbgrāmatas nosaukums = ThisWorkbook.Name Beigu funkcija

Koda skaidrojums:-:- Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu un pēc tam noteikuši, kā iepazīt darbgrāmatas nosaukumu.

Lai izmantotu šo formulu, jums vienkārši jāievada formula jebkurā šūnā šādā veidā: - = Darbgrāmatas nosaukums (). Funkcija atgriezīs lapas nosaukumu.


Kā iegūt pirmo un pēdējo vārdu no šūnas, izmantojot Microsoft Excel VBA?

Mums ir dati 1. lapā, kurā mums ir dažas adreses, tāpēc mēs vēlamies izvilkt pēdējo un pirmo vārdu no teikuma vai šūnas vai diapazona, izveidojot lietotāja definētu funkciju, izmantojot programmu VBA programmā Excel.

Pirmkārt, mēs uzrakstīsim funkciju pirmā vārda iegūšanai. Lūdzu, veiciet tālāk norādītās darbības:-

  • Atveriet VBA lapu, nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli

Uzrakstiet zemāk minēto kodu:-

 Funkcija GETFW (teksts kā virkne, neobligāts atdalītājs kā variants) Dim FW kā virkne, ja tas pazūd (atdalītājs), tad atdalītājs = "" Beigas, ja FW = pa kreisi (teksts, InStr (1, teksts, atdalītājs, vbTextCompare)) GETFW = Aizstāt (FW , Atdalītājs, "") beigu funkcija

Koda skaidrojums: - Iepriekš minētajā kodā mēs esam minējuši funkcijas nosaukumu ar mainīgajiem. Un tad mēs esam definējuši kritērijus, kā iegūt vārdu no teikuma vai šūnas.

Tagad mēs iemācīsimies izmantot šo formulu. Izpildiet tālāk norādītās darbības:-

  • Dodieties uz Excel lapu.
  • Izmantojiet šo formulu šūnā D9.
  • = GETFW (C9) un nospiediet taustiņu Enter.
  • Funkcija atgriezīs pirmo vārdu no datiem. Tagad, lai izgūtu pirmo vārdu visām šūnām, kopējiet to pašu formulu diapazonā.

Tagad mēs uzrakstīsim kodu, lai no šūnas izņemtu pēdējo vārdu. Izpildiet zemāk minēto kodu:-

  • Atveriet VBA lapu, nospiediet taustiņu Alt+F11.
  • Ievietojiet moduli.
  • Uzrakstiet zemāk minēto kodu:-
 Funkcija GETLW (teksts kā virkne, izvēles atdalītājs kā variants) Dim LW kā virkne, ja IsMissing (atdalītājs), tad atdalītājs = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Aizstāt (LW, atdalītājs, "")) beigu funkcija

Tagad mēs iemācīsimies izmantot šo formulu. Izpildiet tālāk norādītās darbības:-

  • Dodieties uz Excel lapu.
  • Izmantojiet šo formulu šūnā D9.
  • = GETLW (C9) Nospiediet Enter.
  • Funkcija atgriezīs pēdējo vārdu no datiem. Tagad, lai izgūtu pēdējo vārdu visām šūnām, kopējiet to pašu formulu diapazonā.

Šīs ir funkcijas, kuras mēs varam definēt, izmantojot VBA, un pēc tam varam izmantot to kā Excel formulu. Turklāt mēs varam izveidot daudz vairāk lietotāja definētu funkciju. Turpiniet mācīties kopā ar mums, mēs radīsim sarežģītākas formulas.