Käyttäjän määrittämän toiminnon luominen Microsoft Excelissä

Anonim

Tässä artikkelissa aiomme oppia luomaan käyttäjän määrittämiä toimintoja Microsoft Excelissä VBA: n avulla.

Käyttäjän määrittämä toiminto:- Microsoft Excelillä on jo paljon toimintoja, mutta silti jokaisella on erilaiset vaatimukset, tilanne, voimme luoda oman toiminnon käyttäjän määrittämän toiminnon vaatimuksen mukaisesti. Voimme käyttää käyttäjän määrittämää toimintoa muiden Excel -toimintojen tavoin.

Alla on aiheita, joille luomme käyttäjän määrittämän toiminnon:

1). Kuinka laskea solun tai alueen sanojen määrä?

2). Kuinka poimia sana lauseesta tai solusta Excelissä?

3). Kuinka luoda ISO -kaava?

4). Kuinka tietää laskentataulukon ja työkirjan nimi VBA: n avulla?

5). Kuinka poimia ensimmäinen ja viimeinen sana Excelin solusta?

Kuinka luoda käyttäjän määrittämä toiminto laskemaan solujen tai alueiden sanamäärä?

Meillä on tietoja taulukossa 1, jossa meillä on joitain osoitteita, joten haluamme laskea solun tai alueen sanat luomalla käyttäjän määrittämän funktion VBA: n kautta Excelissä.

Voit tehdä käyttäjän määrittämän toiminnon seuraavasti:-

  • Avaa VBA -sivu ja paina näppäin yhdistelmää Alt+F11.
  • Aseta moduuli.

Kirjoita alla mainittu koodi:

 Toiminto WORDSCOUNT (rRange As Range) Niin pitkä himmeä rCell As Range Dim Count yhtä pitkä jokaiselle rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Seuraava rCell WORDSCOUNT = lCount End Function

Koodin selitykset: - Saadaksemme käyttäjän määrittämän funktion aloitamme koodin funktion nimeksi ja määritämme muuttujat. Olemme käyttäneet koodissa ”Jokaiselle silmukalle” alueen sanojen laskemista.
Kuinka käyttää tätä toimintoa Excelissä?

Voit käyttää tätä toimintoa seuraavasti:-

  • Siirry Excel -taulukkoon.
  • Jos haluat laskea solun sanat, kirjoita kaava soluun D7.
  • = WORDSCOUNT (C7), solu C7 on solu, jossa haluamme laskea sanat.
  • Funktio palauttaa 6, mikä tarkoittaa, että solu C7 sisältää 6 sanaa.
  • Voit tehdä saman laskelman muille soluille kopioimalla saman kaavan ja liittämällä alueen.

  • Jos haluat laskea alueen sanat, käytä kaavaa = WORDSCOUNT (C7: C16) ja paina Enter.
  • Toiminto palauttaa sanojen määrän.


Huomautus:- Tästä UDF: stä on hyötyä laskemaan alueen tai yksittäisen solun sanat.

Kirjoitamme nyt koodin sanan laskemiseksi käyttämällä määritettyä erotinta (,). Noudata alla annettuja ohjeita:-

 Toiminto SEPARATECOUNTWORDS (rRange As Range, Optional separator as Variant) Long Dim rCell As Range Dim Count as Long If IsMissing (separator) Sitten separator = "," End If For each rCellInrRange lCount = lCount + Len (Trim (rCell)) - - Len (Korvaa (Trim (rCell), erotin, "")) Seuraava rCell SEPARATECOUNTWORDS = lCount End Function

Voit käyttää tätä toimintoa seuraavasti:-

  • Siirry Excel -taulukkoon.
  • Käytämme tätä määritettyä funktiota sanan tiettyjen erotinten laskemiseen.
  • = SEPARATECOUNTWORDS (C7) ja paina Enter.
  • Toiminto palauttaa tiettyjen erottimien määrän.


Kuinka poimia sana lauseesta tai solusta Microsoft Excelissä VBA: n avulla?

Meillä on tietoja taulukossa 1. Joissa meillä on joitain osoitteita, joten haluamme poimia sanat lauseesta tai solusta tai alueesta luomalla käyttäjän määrittämän funktion VBA: n kautta Excelissä.


Voit tehdä käyttäjän määrittämän toiminnon seuraavasti:-

  • Avaa VBA -sivu ja paina näppäin yhdistelmää Alt+F11.
  • Aseta moduuli paikalleen.
  • Kirjoita alla mainittu koodi:-
 Funktio GETWORD (teksti vaihtoehtona, N kokonaislukuna, valinnainen erotin muunnelmana) merkkijonona, jos se on puuttunut (erotin) Sitten erotin = "" Loppu, jos GETWORD = jaettu (teksti, erotin) (N - 1) Lopputoiminto

Koodin selitys:- Edellä mainitussa koodissa olemme maininneet funktion nimen muuttujien kanssa. Ja sitten olimme määritelleet kriteerit sanan poimimiseksi lauseesta tai solusta.

Nyt opimme käyttämään tätä kaavaa. Noudata alla annettuja ohjeita:-

  • Siirry Excel -taulukkoon.
  • Käytä tätä kaavaa solussa D7.
  • = GETWORD (C7,2) ja paina Enter.
  • Funktio palauttaa toisen sanan solusta, koska kaavassa, jonka mainitsimme 2: lletoinen numeron sana. Jos haluat hakea sanan, joka on sijoitettu kohtaan 3rd sijainti, sinun on muutettava kaavan numero 2: sta 3: een.


Kuinka luoda ISO -viikon numerokaava Microsoft Excelissä VBA: n avulla?

Opimme, kuinka voimme luoda ISO -viikon numerokaavan Excelissä tämän UDF: n avulla. Tätä toimintoa käytetään tunnistamaan, että mainittu päivämäärä kuuluu mihin viikon viikkoon.

Meillä on taulukossa päivämääräluettelo, ja toisessa sarakkeessa haluamme hakea viikon numerot.

Luo UDF tätä vaatimusta varten noudattamalla alla annettuja ohjeita:-

  • Avaa VBA -sivu ja paina näppäin yhdistelmää Alt+F11.
  • Aseta moduuli paikalleen.
  • Kirjoita alla mainittu koodi:-
 Toiminto ISOWEEKNUMBER (Ilmoita päivämääränä) Niin kauan Dim Dt Päivämääränä Dt = DateSerial (Vuosi (Ilmoita - Viikonpäivä (Ilmoita - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5) ) / 7) Lopetustoiminto

Koodin selitys:-:- Yllä olevassa koodissa olemme maininneet funktion nimen muuttujien kanssa. Ja sitten olimme asettaneet päivämääräarvon ja sitten määritellyt ISOWEENUMBER -funktion kriteerit.
Kuinka voimme käyttää tätä toimintoa Excel -tiedostossamme?

  • Siirry Excel -taulukkoon.
  • Kirjoita kaava soluun D7.
  • = ISOWEEKNUMBER (C7) ja paina Enter.
  • Toiminto palauttaa soluun syötetyn päivämäärän viikon. Nouda nyt kunkin viikon viikon numero kopioimalla sama kaava alueelta.


Nyt opimme palauttamaan ISO-standardit vuoden alussa Excelissä- Vuoden ensimmäinen maanantai.

Tämä toiminto tarkistaa periaatteessa, että 1st Vuoden maanantai putoaa mihin päivään ja sitten alkaa laskea viikkojen lukumäärä kyseisestä päivämäärästä. Katsotaanpa, kuinka voimme luoda UDF: n tätä vaatimusta varten.


Noudata alla annettuja ohjeita:-

  • Avaa VBA -sivu ja paina näppäin yhdistelmää Alt+F11.
  • Aseta moduuli paikalleen.
  • Kirjoita alla mainittu koodi:-
 Toiminto ISOSTYR (vuosi kokonaislukuna) päivämääränä Dim WD kokonaislukuna Dim NY kuten päivämäärä NY = DateSerial (vuosi, 1, 1) WD = (NY - 2) Mod 7 Jos WD <4 Sitten ISOSTYR = NY - WD Muu ISOSTYR = NY - WD + 7 End If End -toiminto

Koodin selitys: - Yllä olevassa koodissa olemme maininneet funktion nimen muuttujien kanssa. Ja sitten olimme asettaneet muuttujien kriteerit ja sitten määritellyt kaavan syötteen.

Sinun tarvitsee vain antaa vuosi 2001 tässä muodossa, ja kaava antaa sinulle 1st Vuoden maanantai.

Nyt opimme käyttämään UDF: ää Excel -tiedostossa. Noudata alla annettuja ohjeita:-

  • Siirry Excel -taulukkoon.
  • Kirjoita kaava soluun D7.
  • = ISOSTYR (C7) ja paina Enter.
  • Toiminto palauttaa päivämäärän 1st Uudenvuoden ensimmäisen viikon maanantai.
  • Voit palauttaa päivämäärän 1st Uudenvuoden ensimmäisen viikon maanantaina kopioi sama kaava ja liitä alueeseen.


Kuinka tietää laskentataulukon ja työkirjan nimen Microsoft Excelin VBA: n avulla?

Noudata alla annettuja ohjeita ja ohjeita:-

  • Avaa VBA -sivu ja paina näppäin yhdistelmää Alt+F11.
  • Aseta moduuli paikalleen.
  • Kirjoita alla mainittu koodi:-
 Toiminto Laskentataulukon nimi () Työarkin nimi = Alue ("A1"). Parent.Name End Function

Koodin selitys:- Yllä olevassa koodissa olemme maininneet toiminnon nimen ja sitten määritellyt, miten arkin nimi tiedetään.

Jotta voit käyttää tätä kaavaa, sinun tarvitsee vain syöttää kaava mihin tahansa soluun tällä tavalla: -= Työarkin nimi (). Toiminto palauttaa arkin nimen.


Voit luoda funktion työkirjan nimelle noudattamalla alla olevia ohjeita ja koodia:-

  • Avaa VBA -sivu painamalla näppäinyhdistelmää Alt+F11.
  • Aseta moduuli.
  • Kirjoita alla mainittu koodi:-
 Toiminto Työkirjanimi () Työkirjanimi = ThisWorkbook.Name Lopeta toiminto

Koodin selitys:-:- Yllä olevassa koodissa olemme maininneet funktion nimen ja sitten määritelleet, miten opimme tuntemaan työkirjan nimen.

Jos haluat käyttää tätä kaavaa, sinun tarvitsee vain syöttää kaava mihin tahansa soluun tällä tavalla: - = Työkirjan nimi (). Toiminto palauttaa arkin nimen.


Kuinka poimia ensimmäinen ja viimeinen sana solusta käyttämällä VBA: ta Microsoft Excelissä?

Meillä on tietoja taulukossa 1, jossa meillä on joitain osoitteita, joten haluamme poimia viimeisen ja ensimmäisen sanan lauseesta tai solusta tai alueesta luomalla käyttäjän määrittämän funktion VBA: n kautta Excelissä.

Kirjoitamme ensin funktion ensimmäisen sanan poimimiseksi. Noudata alla olevia ohjeita:-

  • Avaa VBA -sivu painamalla näppäinyhdistelmää Alt+F11.
  • Aseta moduuli

Kirjoita alla mainittu koodi:-

 Funktio GETFW (teksti merkkijonona, valinnainen erotin muunnelmana) Dim FW merkkijonona, jos IsMissing (erotin) Sitten Separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Korvaa (FW , Erotin, "") Lopetustoiminto

Koodin selitys: - Edellä mainitussa koodissa olemme maininneet funktion nimen muuttujien kanssa. Ja sitten olemme määrittäneet kriteerit sanan poimimiseksi lauseesta tai solusta.

Nyt opimme käyttämään tätä kaavaa. Noudata alla annettuja ohjeita:-

  • Siirry Excel -taulukkoon.
  • Käytä tätä kaavaa solussa D9.
  • = GETFW (C9) ja paina Enter.
  • Toiminto palauttaa datasta ensimmäisen sanan. Jos haluat hakea kaikkien solujen ensimmäisen sanan, kopioi sama kaava alueelta.

Nyt kirjoitamme koodin, jolla poimitaan viimeinen sana solusta. Noudata alla mainittua koodia:-

  • Avaa VBA -sivu painamalla näppäinyhdistelmää Alt+F11.
  • Aseta moduuli.
  • Kirjoita alla mainittu koodi:-
 Toiminto GETLW (teksti merkkijonona, valinnainen erotin muunnelmana) Dim LW merkkijonona, jos IsMissing (erotin) Sitten Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Replace (LW, Separator, "")) End Function

Nyt opimme käyttämään tätä kaavaa. Noudata alla annettuja ohjeita:-

  • Siirry Excel -taulukkoon.
  • Käytä tätä kaavaa solussa D9.
  • = GETLW (C9) Paina Enter.
  • Toiminto palauttaa datan viimeisen sanan. Jos haluat hakea kaikkien solujen viimeisen sanan, kopioi sama kaava alueelta.

Nämä ovat toimintoja, jotka voimme määritellä VBA: n kautta ja sitten käyttää sitä Excelin kaavana. Lisäksi voimme luoda paljon enemmän käyttäjän määrittämiä toimintoja. Jatka oppimista kanssamme, keksimme lisää hankalia kaavoja.