Jak vytvořit funkci definovanou uživatelem v aplikaci Microsoft Excel

Anonim

V tomto článku se naučíme, jak vytvářet funkce definované uživatelem v aplikaci Microsoft Excel pomocí jazyka VBA.

Funkce definovaná uživatelem:- Microsoft Excel již má spoustu funkcí, ale přesto má každý jiné požadavky, situaci, můžeme vytvořit vlastní funkci podle požadavku, který se nazývá funkce definovaná uživatelem. Můžeme použít funkci definovanou uživatelem jako ostatní funkce v aplikaci Excel.

Níže jsou témata, pro která vytvoříme uživatelem definovanou funkci:

1). Jak spočítat počet slov v buňce nebo rozsahu?

2). Jak extrahovat slovo z věty nebo buňky v aplikaci Excel?

3). Jak vytvořit vzorec pro ISO?

4). Jak poznat název listu a sešitu pomocí VBA?

5). Jak extrahovat první a poslední slovo z buňky v aplikaci Excel?

Jak vytvořit uživatelsky definovanou funkci pro počítání počtu slov v buňce nebo rozsahu?

Máme data v listu 1, ve kterých máme nějaké adresy, takže chceme počítat slova v buňce nebo rozsahu vytvořením funkce definované uživatelem prostřednictvím VBA v aplikaci Excel.

Aby byla funkce definována uživatelem, postupujte podle níže uvedených kroků:-

  • Otevřete stránku VBA a stiskněte klávesu Alt+F11.
  • Vložte modul.

Napište níže uvedený kód:

 Funkce WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count Count as Long for each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Další rCell WORDSCOUNT = lCount End Function

Vysvětlení kódu: - Aby byla funkce definována uživatelem, spustíme kód pro název funkce a definujeme proměnné. V kódu jsme použili „Pro každou smyčku“ pro počítání slov v rozsahu.
Jak tuto funkci používat v Excelu?

Chcete-li použít tuto funkci, postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Chcete -li počítat slova pro buňku, zadejte vzorec do buňky D7.
  • = WORDSCOUNT (C7), buňka C7 je buňka, ve které chceme vypočítat slova.
  • Funkce vrátí 6, což znamená, že buňka C7 obsahuje 6 slov.
  • Chcete -li provést stejný výpočet pro zbývající buňky, zkopírujte stejný vzorec a vložte do rozsahu.

  • Chcete -li počítat slova v rozsahu, použijte vzorec jako = WORDSCOUNT (C7: C16) a stiskněte Enter.
  • Funkce vrátí počet slov.


Poznámka:- Tento UDF pomůže při počítání slov v rozsahu nebo v jedné buňce.

Nyní napíšeme kód pro počítání slova pomocí zadaného oddělovače (,). Postupujte podle níže uvedených kroků:-

 Funkce 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 Every rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function

Chcete-li použít tuto funkci, postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Abychom spočítali konkrétní oddělovače ve slově, použijeme tuto definovanou funkci.
  • = SEPARATECOUNTWORDS (C7) a stiskněte Enter.
  • Funkce vrátí počet konkrétních oddělovačů.


Jak extrahovat slovo z věty nebo buňky v aplikaci Microsoft Excel pomocí VBA?

Máme data v listu 1. Ve kterém máme nějaké adresy, takže chceme extrahovat slova z věty nebo buňky nebo rozsahu vytvořením funkce definované uživatelem prostřednictvím VBA v aplikaci Excel.


Aby byla funkce definována uživatelem, postupujte podle níže uvedených kroků:-

  • Otevřete stránku VBA a stiskněte klávesu Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce GETWORD (text jako varianta, N jako celé číslo, volitelný oddělovač jako varianta) jako řetězec, pokud chybí

Vysvětlení kódu:- Ve výše uvedeném kódu jsme uvedli název funkce s proměnnými. A pak jsme definovali kritéria pro extrahování slova z věty nebo buňky.

Nyní se naučíme, jak tento vzorec používat. Postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Tento vzorec použijte v buňce D7.
  • = GETWORD (C7,2) a stiskněte Enter.
  • Funkce vrátí druhé slovo z buňky, protože ve vzorci, který jsme zmínili pro 2nd číselné slovo. Pokud chcete načíst slovo, které je umístěno na 3rd pozici, musíte ve vzorci změnit číslo z 2 na 3.


Jak vytvořit vzorec čísla týdne ISO v aplikaci Microsoft Excel pomocí VBA?

Naučíme se, jak s tímto UDF můžeme v Excelu vytvořit vzorec počtu týdnů v ISO. Tuto funkci použijeme k identifikaci, že uvedené datum patří číslu týdne v roce.

V listu máme seznam dat a ve druhém sloupci chceme načíst čísla týdnů.

Chcete -li pro tento požadavek vytvořit UDF, postupujte podle níže uvedených kroků:-

  • Otevřete stránku VBA a stiskněte klávesu Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Koncová funkce

Vysvětlení kódu:-:- Ve výše uvedeném kódu jsme uvedli název funkce s proměnnými. A pak jsme nastavili hodnotu data a poté jsme definovali kritéria funkce „ISOWEENUMBER“.
Jak můžeme tuto funkci použít v našem souboru aplikace Excel?

  • Přejít na list aplikace Excel.
  • Do buňky D7 zadejte vzorec.
  • = ISOWEEKNUMBER (C7), a stiskněte Enter.
  • Funkce vrátí týden pro zadané datum v buňce. Chcete -li nyní načíst číslo týdne pro každé datum, zkopírujte stejný vzorec v rozsahu.


Nyní se naučíme, jak vrátit standardy ISO na začátek roku v aplikaci Excel- první pondělí roku.

Tato funkce v zásadě zkontroluje, že 1Svatý Na které datum připadne pondělí roku a poté začne počítat počet týdnů od tohoto data. Podívejme se, jak můžeme pro tento požadavek vytvořit UDF.


Postupujte podle níže uvedených kroků:-

  • Otevřete stránku VBA a stiskněte klávesu Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce ISOSTYR (rok jako celé číslo) jako datum Dim WD jako Integer Dim NY jako datum NY = DateSerial (rok, 1, 1) WD = (NY - 2) Mod 7 If WD <4 Then ISOSTYR = NY - WD Else ISOSTYR = NY - Funkce WD + 7 End If End

Vysvětlení kódu: - Ve výše uvedeném kódu jsme uvedli název funkce s proměnnými. A pak jsme nastavili kritéria pro proměnné a poté jsme definovali vstup vzorce.

Stačí zadat rok 2001 v tomto formátu a vzorec vám poskytne 1Svatý Pondělí v roce.

Nyní se naučíme, jak používat UDF v souboru aplikace Excel. Postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Do buňky D7 zadejte vzorec.
  • = ISOSTYR (C7) a stiskněte Enter.
  • Funkce vrátí datum 1Svatý Pondělí prvního roku nového roku.
  • Chcete -li vrátit datum 1Svatý Pondělí prvního roku nového roku, zkopírujte stejný vzorec a vložte do rozsahu.


Jak poznat název listu a sešitu pomocí jazyka VBA v aplikaci Microsoft Excel?

Postupujte podle níže uvedeného kódu a kroků:-

  • Otevřete stránku VBA a stiskněte klávesu Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce Název pracovního listu () Název pracovního listu = Rozsah ("A1"). Nadřazené jméno Koncová funkce

Vysvětlení kódu:- Ve výše uvedeném kódu jsme zmínili název funkce a poté jsme definovali, jak znát název listu.

Chcete -li použít tento vzorec, stačí zadat vzorec do jakékoli buňky tímto způsobem: -= Název listu (). Funkce vrátí název listu.


Chcete-li vytvořit funkci pro název sešitu, postupujte podle níže uvedených kroků a kódu:-

  • Otevřete stránku VBA stisknutím klávesy Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce Workbookname () Workbookname = ThisWorkbook.Name End Function

Vysvětlení kódu:-:- Ve výše uvedeném kódu jsme zmínili název funkce a poté jsme definovali, jak poznat název sešitu.

Chcete -li použít tento vzorec, stačí zadat vzorec do jakékoli buňky tímto způsobem: - = Název sešitu (). Funkce vrátí název listu.


Jak extrahovat první a poslední slovo z buňky pomocí VBA v aplikaci Microsoft Excel?

Máme data v listu1, ve kterých máme nějaké adresy, takže chceme extrahovat poslední a první slovo z věty nebo buňky nebo rozsahu vytvořením funkce definované uživatelem prostřednictvím VBA v aplikaci Excel.

Nejprve napíšeme funkci pro extrahování prvního slova. Postupujte podle níže uvedených kroků:-

  • Otevřete stránku VBA stisknutím klávesy Alt+F11.
  • Vložte modul

Napište níže uvedený kód:-

 Funkce GETFW (text jako řetězec, volitelný oddělovač jako varianta) Dim FW jako řetězec, pokud chybí , Separator, "") Koncová funkce

Vysvětlení kódu: - Ve výše uvedeném kódu jsme uvedli název funkce s proměnnými. A pak jsme definovali kritéria pro extrahování slova z věty nebo buňky.

Nyní se naučíme, jak tento vzorec používat. Postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Tento vzorec použijte v buňce D9.
  • = GETFW (C9) a stiskněte Enter.
  • Funkce vrátí první slovo z dat. Chcete -li nyní načíst první slovo pro všechny buňky, zkopírujte stejný vzorec v rozsahu.

Nyní napíšeme kód pro extrahování posledního slova z buňky. Postupujte podle níže uvedeného kódu:-

  • Otevřete stránku VBA stisknutím klávesy Alt+F11.
  • Vložte modul.
  • Napište níže uvedený kód:-
 Funkce GETLW (text jako řetězec, volitelný oddělovač jako varianta) Dim LW jako řetězec, pokud chybí ) GETLW = StrReverse (Replace (LW, Separator, "")) End Function

Nyní se naučíme, jak tento vzorec používat. Postupujte podle níže uvedených kroků:-

  • Přejít na list aplikace Excel.
  • Tento vzorec použijte v buňce D9.
  • = GETLW (C9) Stiskněte Enter.
  • Funkce vrátí poslední slovo z dat. Chcete -li nyní načíst poslední slovo pro všechny buňky, zkopírujte stejný vzorec v rozsahu.

Toto jsou funkce, které můžeme definovat prostřednictvím VBA a poté je použít jako vzorec aplikace Excel. Také můžeme vytvořit mnoho dalších funkcí definovaných uživatelem. Učte se s námi dál, vymyslíme záludnější vzorce.