Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
pokud vám nestačí 400 funkcí, které jsou součásti Microsoft Excelu - vytvořte si vlastní například na obsah kruhu :)
Doplněno 21.4.2013: Popis a nápověda pro vlastní funkce.
Jak na vlastní funkce od teoretických základu, přes vytvoření funkce, její popis, doplnění o nápovědu. Opět pro přehlednost je článek rozdělen na kapitoly:
Při pokročilejší práci v Excelu se vám stane, že potřebujete speciální funkcí, která se v Excelu nenachází. Mnohdy jde vytvořit kombinací z několika standartních funkcí. Pokud nechce tvořit složitou dlouhou funkci (která je na první pohled nepřehledná), můžete si vytvořit funkcí vlastní.
Na pozadí to bude stále složená funkce, ale na popředí bude vypadat jednoduše. Navíc si můžete zvolit její pojmenování (ať se Vám dobře hledá).
Poznámka: Jedná se o odpověď na dotazy z emailu, týkající se tvorby funkcí. Z dotazu to vypadá na školní úkol ;). Ono tvořit funkci na výpočet obsahu kruhu, nebo objem kvádru není úplně praktické, ale na druhou stranu se na těchto příkladech lehce demonstruje tvorba vlastních funkcí.
Než dlouze povídat, ukáží použití na jednoduchém příkladu. Potřebujeme vypočíst plochu kruhu pomocí vlastní funkce, kdy zadáme jen průměr. Jako druhý příklad vypočteme objem kvádru, kdy jsou zadány tři strany (výška, šířka, hloubka).
V klasickém řešení napíšeme do buňky vzorec:
=PI()*A1^2/4
Pro objem krychle vzorec:
=A1*A2*A3
Poznámka - takto jednoduchý vzorec je zvolen záměrně. Klidně si udělejte funkci na výpočet plochy čtverce :), nebo jakýkoliv složitější výpočet (např. výpočet momentu setrvačnosti, transfigurace hvězdy na trojúhelník, atd.).
Po standardní instalaci MS Excelu, není karta vývojář k dispozici. Proto si ji musíte zobrazit. Trvalé zobrazení karty vývojář je sepsáno v článku:
Na kartě vývojář v sekci Kód ikona Visual Basic nebo klávesová zkratka Alt + F11.
V okně Visual Basic for Applications přidáte přes menu modul: Insert - Module. Do okna Project - VBAProject se přidá Module1
Module rozkliknout a přidat vaše funkce:
Function ObsahKruh(Prumer)
Pi = Application.WorksheetFunction.Pi()
ObsahKruh = Pi * Prumer ^ 2 / 4
End Function
Function ObjemKvadru(Delka, Sirka, Hloubka)
ObjemKvadru = Delka * Sirka * Hloubka
End Function
Teď jen otestujete, zda funguje v Excelu. Stačí zadat příslušné názvy funkcí:
=ObsahKruh(A1)
=ObjemKvadru(A1;A2;A3)
Jednoduchou funkci umíte vytvořit. Vlastní funkce v Excelu mohou být funkce trojího druhu:
Function MojeKonstanta()
Function ObsahKrychle(Cislo)
Function MojeNahodneCislo(Optional Cislo)
Poznámka: Je jasné, že může jít i o kombinaci povinných a volitelných parametrů.
Budete-li chtít k funkci doplnit popis. Jednodušší možnost je následující (osobně doporučuji využít následující pomocí VBA kódu).
Ve VBA Alt + F11 zobrazíte Object Browser přes menu view - Object Browser (F2)
V Members of naleznete váš název funkce. Při volbě Propertes doplnit popis funkce a OK
Poznámka: Pokud se Vám výpis všech proměnných, zdá moc dlouhý tak v Object Browser zvolte VBAProject a vidíte své funkce.
Pomocí VBA kódu doplníte funkci popis, odkazy na nápovědu. K tomu nám slouží MacroOptions
MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile, ArgumentDescriptions)
Poznámka: Všechny proměnné jsou volitelné, datový typ Variant (kromě ArgumentDescriptions což je Array).
Kategorie funkcí jak jsou k dispozici v Excelu:
Máme například takovouto jednoduchou funkci:
Function OBJEMKVADRU(Delka, Sirka, Hloubka)
ObjemKvadru = Delka * Sirka * Hloubka
End Function
Kód, který stačí spustit jednou, pak již nemusí být součásti sešitu ;)
Sub PopisNasiVlastniFunkce()
Dim FunkceJmeno As String
Dim FunkcePopis As String
Dim Kategorie As String
Dim Argumenty(1 To 3) As String
FunkceJmeno = "OBJEMKVADRU"
FunkcePopis = "Tato naše vlastní funkce vratí objem kvádru."
Kategorie = 14 'Uživatelem definovaná funkce
Argumenty(1) = "Zadej stranu a - délka"
Argumenty(2) = "Zadej stranu b - šířka"
Argumenty(3) = "Zadej stranu c - hloubka"
Application.MacroOptions _
Macro:=FunkceJmeno, _
Description:=FunkcePopis, _
Category:=Kategorie, _
ArgumentDescriptions:=Argumenty
End Sub
Pokud proběhlo správně ve vlastních funkcích naleznete vaši funkci OBJEMKVADRU.
Včetně popisu argumentů
Jak doplnit vlastní funkci o odkaz do vlastní nápovědy? viz další kapitola.
Jak si pro vlastní funkci napsat nápovědu (help).
Osobně používám freeware: HelpNDoc v kterém vytvořím vlastní nápovědu (*.chm). Sice se do každé stránky přidá reklamní link, což pro ukázkové a demnostrační příklad nevadí.
Poznámka: Máte možnost je vytvořit i starší *.hlp soubor nápovědy.
Výše uvedený kód je potřeba doplnit o odkaz na soubor nápovědy a ID konkrétní kapitoly (článku) z nápovědy.
HelpFile:=ThisWorkbook.Path & "\vlastni-funkce.chm", _
HelpContextID:=200
Ukázková nápověda ke stažení.
Soubor Vlastní funkce - nápověda ke stažení zdarma.
Ukázky možných funkcí
Function CestakSouboru()
CestakSouboru = ThisWorkbook.Path
End Function
V případě dotazů v komentářích se pokusím článek doplnit.
Napřílad:
Dle dotazů v komentářích se pokusím článek rozšířit (doplnit).
Soubor Vlastní funkce Microsoft Excelu ke stažení zdarma. Soubor využívá makra.
Používáte vlastní funkce? Máte nějakou zajímavou funkci o kterou se rádi podělíte? Nebo tvoříte raději kompilace z několika standartních funkcí přímo v listech Excelu?
Doplněno 20.4.2013, použita nápověda od Microsoftu
Článek byl aktualizován: 19.09.2020 11:06
Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.
Přidáno: 19.04.13 12:15
Ahoj, chtěl bych se zeptat jak funkci doplnit o informace: K čemu funkce slouží, a napovědu k použitým argumentům. Díky moc za odpověď.
Přidáno: 19.04.13 17:48
To Radek: Uložit komentář k funkci. V Object Browser pravým tlačítkem na funkci vybereme Properties... vyplnit.
Přidáno: 20.04.13 09:36
To Radek: Tak jsem doplnil info do článku.
Přidáno: 21.04.13 17:29
Pro doplnění: Argumenty funkce (ArgumentDescriptions) je možné popisovat až v Excelu 2010, dřívější verze toto neumí.
Přidáno: 21.04.13 17:35
A ještě drobnůstka: Hodnotu Pí lze bez použití funkce listu vyjádřit jako = 4 * Atn(1), kde Atn je standardní funkce VBA, resp. VB.
Přidáno: 06.06.13 12:13
Následující fce nefunguje v ex 2002. Nevíte proč? Function Slovy(Cis As Double) As String Dim StrCis As String Dim LenCis As Byte, Rad As Integer, Ofs As Byte Dim Pol As Byte, pom As String, pom1 As String, pom2 As String Dim Jedn As Variant, Des1 As Variant, Des As Variant, Sta As Variant Dim JednTM As Variant, Tis As Variant, Mil As Variant ' If IsEmpty(Cis) Then End ' Jedn = Array("", "jedna", "dvě", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Des1 = Array("deset", "jedenáct", "dvanáct", "třináct", "čtrnáct", "patnáct", "šestnáct", "sedmnáct", "osmnáct", "devatenáct") Des = Array("", "", "dvacet", "třicet", "čtyřicet", "padesát", "šedesát", "sedmdesát", "osmdesát", "devadesát") Sta = Array("", "jednosto", "dvěsta", "třista", "čtyřista", "pětset", "šestset", "sedmset", "osmset", "devětset") Tis = Array("tisíc", "tisíc", "tisíce", "tisíce", "tisíce", "tisíc", "tisíc", "tisíc", "tisíc", "tisíc") JednTM = Array("", "jeden", "dva", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Mil = Array("milionů", "milion", "miliony", "miliony", "miliony", "milionů", "milionů", "milionů", "milionů", "milionů") ' ' StrCis = CStr(Format(Cis, "0.00")) Pol = InStr(StrCis, ",") - 1 ' poloha radu jednotek v cisle If Pol > 9 Then Slovy = ">999 999 999": Exit Function Rad = 0 ' rad cislice v cisle Slovy = "" Do pom = Mid(StrCis, Pol, 1) If Pol > 1 Then pom1 = Mid(StrCis, Pol - 1, 1) Else pom1 = "0" End If ' Select Case Rad Case 0 pom2 = IIf(pom1 <> 1, Jedn(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) Case 1 pom2 = Des(pom): Ofs = 1 Case 2 pom2 = Sta(pom): Ofs = 1 Case 3 pom2 = IIf(pom1 <> 1, JednTM(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) If Pol > 3 Then ' kdyz zustavaji jeste >3 cislice If Mid(StrCis, Pol - 2, 3) <> "000" Then pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") ' a jsou i tisice -> vlozeni slova tisic Else Ofs = 3 ' preskoci na rad 6 - miliony End If Else ' kdyz zustava jeste <3 cislice -> vlozeni slova tisic pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") End If Case 4 pom2 = Des(pom): Ofs = 1 Case 5 pom2 = Sta(pom): Ofs = 1 Case 6 pom2 = IIf(pom1 <> 1, JednTM(pom) & Mil(pom), Des1(pom) & "milionů"): Ofs = IIf(pom1 <> 1, 1, 2) Case 7 pom2 = Des(pom): Ofs = 1 Case 8 pom2 = Sta(pom): Ofs = 1 End Select ' Slovy = pom2 & Slovy Pol = Pol - Ofs: Rad = Rad + Ofs ' Loop While Pol > 0 Slovy = Trim(Slovy) ' & " " & Right(StrCis, 2) ' pridani destinne casti End Function
Přidáno: 22.07.14 09:30
To Gabo: Exc2003Sub Test()
MsgBox Slovy(123456789.5)
End Sub
Function Slovy(Cis As Double) As String
'Suma(Číslo) -> slovom
Dim StrCis As String
Dim LenCis As Byte, Rad As Integer, Ofs As Byte
Dim Pol As Byte, pom As String, pom1 As String, pom2 As String
Dim Jedn As Variant, Des1 As Variant, Des As Variant, Sta As Variant
Dim JednTM As Variant, Tis As Variant, Mil As Variant
If IsEmpty(Cis) Then End
Jedn = Array("", "jedna", "dvě", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět")
Des1 = Array("deset", "jedenáct", "dvanáct", "třináct", "čtrnáct", "patnáct", "šestnáct", "sedmnáct", "osmnáct", "devatenáct")
Des = Array("", "", "dvacet", "třicet", "čtyřicet", "padesát", "šedesát", "sedmdesát", "osmdesát", "devadesát")
Sta = Array("", "jednosto", "dvěsta", "třista", "čtyřista", "pětset", "šestset", "sedmset", "osmset", "devětset")
Tis = Array("tisíc", "tisíc", "tisíce", "tisíce", "tisíce", "tisíc", "tisíc", "tisíc", "tisíc", "tisíc")
JednTM = Array("", "jeden", "dva", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět")
Mil = Array("milionů", "milion", "miliony", "miliony", "miliony", "milionů", "milionů", "milionů", "milionů", "milionů") '
StrCis = CStr(Format(Cis, "0.00"))
Pol = InStr(StrCis, ",") - 1 ' poloha radu jednotek v cisle
If Pol > 9 Then Slovy = ">999 999 999": Exit Function
Rad = 0 ' rad cislice v cisle
Slovy = ""
Do
pom = Mid(StrCis, Pol, 1)
If Pol > 1 Then
pom1 = Mid(StrCis, Pol - 1, 1)
Else
pom1 = "0"
End If
Select Case Rad
Case 0
pom2 = IIf(pom1 <> 1, Jedn(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2)
Case 1
pom2 = Des(pom): Ofs = 1
Case 2
pom2 = Sta(pom): Ofs = 1
Case 3
pom2 = IIf(pom1 <> 1, JednTM(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2)
If Pol > 3 Then ' kdyz zustavaji jeste >3 cislice
If Mid(StrCis, Pol - 2, 3) <> "000" Then
pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") ' a jsou i tisice -> vlozeni slova tisic
Else: Ofs = 3 ' preskoci na rad 6 - miliony
End If
Else ' kdyz zustava jeste <3 cislice -> vlozeni slova tisic
pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ")
End If
Case 4
pom2 = Des(pom): Ofs = 1
Case 5
pom2 = Sta(pom): Ofs = 1
Case 6
pom2 = IIf(pom1 <> 1, JednTM(pom) & Mil(pom), Des1(pom) & "milionů"): Ofs = IIf(pom1 <> 1, 1, 2)
Case 7
pom2 = Des(pom): Ofs = 1
Case 8
pom2 = Sta(pom): Ofs = 1
End Select
Slovy = pom2 & Slovy
Pol = Pol - Ofs: Rad = Rad + Ofs
Loop While Pol > 0
Slovy = Trim(Slovy) & " " & Right(StrCis, 2) ' pridani destinne casti
End Function
Přidáno: 17.12.14 08:47
Zdravím Vás, začínám s VBA a mám potíže s matematiskou funkcí logaritmu "log()". Když zadám log(10) ve VBA spočte se mi 2,3..., což odpovídá funkci ln(10). Nevím čím to může být, pokud by někdo věděl, moc prosím o radu. PS.: zvláštní je, že v klasickém zadání vzorců v excelu, to je v pořádku log(10) = 1 a ln(10)=2,3..
Přidáno: 17.12.14 09:25
definul dekadický logaritmus pomocí přirozeného : log10(x)=ln(x)/ln(10) HOTOVO ;-)
Přidáno: 23.01.15 17:29
Dobrý den, při pokusu vložit nápovědu k funkci mne VBA zastaví v místě := . Resp HelpFile:=
Píše mi to tu - expected expression. Kde je chyba v proměnné?
HelpFile:=ThisWorkbook.Path & "\super.chm", _
HelpContextID:=200
Přidáno: 23.01.15 18:15
Tak už jsem na to přišel. Doplnil jsem kod s nápovědou, ale zapomněl jsem ukončit předešlý příkaz
ArgumentDescriptions:=Argumenty
Správně
ArgumentDescriptions:=Argumenty, _
A zajímá mě jedna věc. Lze nějakým způsobem docílit toho, aby mi excel při vkládání funkce zobrazoval nápovědu? Podobně jako když napíšu SUMA( - zobrazí se poté číslo1;číslo2...
Přidáno: 25.01.15 01:26
Zdravím, potřeboval bych poradit.
V sešitu mám několik listů a potřebuji ve VBA napsat podmínku pro přechod na daný list při stisku tlačítka. Přesněji, rozeznávám plátce DPH a hodnotu. V buňce L6 se zaznamenává ANO/NE pokud je plátce a v buňce L8 zda zapaltil či ne.
Pokud zvololím jednu z variant (celkem jsou 4) potřebuji se po stisku tlačítka dostat na list, který s těmito údaji pracuje.
Zkusil jsemnapsat zatim toto:
Private Sub CommandButton21_Click()
If Range("L6") = "NE" Then
Sheets("NC pro plátce DPH NEM").Select
Else
Sheets("NC pro neplátce DPH NEM").Selec
End If
End Sub
Ale nefunguje to.
Poradíte prosím. Děkuji
Přidáno: 25.01.15 09:47
To Frantino: nenačte se hodnota z buňky, správně je např ... Range("L6").Text = "NE" ...více o načítání buněk jsme popsal: Načítaní hodnot z buněk rozdíl mezi Text, Value, Value2 upravte kód na
Private Sub CommandButton21_Click()
If Range("L6").Text = "NE" Then
Sheets("NC pro plátce DPH NEM").Select
Else
Sheets("NC pro neplátce DPH NEM").Select
End If
End Sub
Přidáno: 11.03.15 12:27
Dobrý den, řeším ten samý problém jako Jakub: Lze nějakým způsobem docílit toho, aby mi excel při vkládání funkce zobrazoval nápovědu? Podobně jako když napíšu SUMA( - zobrazí se poté číslo1;číslo2... Jde mi hlavně o to, aby uživatel při použití vlastní funkce viděl, kolik má zadat parametrů. Děkuji za odpověď
Přidáno: 11.03.15 16:28
Dobrý den, potřeboval bych funkci, která by do buňky vrátila hodnotu - název souboru(toho konkrétního excelu) a pak další která by vložila název adresáře ve kterém je právě umístěn.
Přidáno: 03.04.15 13:52
Dobrý den,
potřebovala bych poradit: pomocí VBA projectu jsem vložila následující funkce pro sčítání buněk označených určitou barvou
Function ColorSummer(RangeToSum As Range, ColorToSum As Integer) As Double
'scita obsah bunek z rozsahu RangeToSum podbarvenych barvou ColorToSum
Dim tmp As Double
tmp = 0
For Each cell In RangeToSum
If cell.Interior.ColorIndex = ColorToSum Then
If IsNumeric(cell.Value) Then tmp = tmp + cell.Value
End If
Next
ColorSummer = tmp
End Function
Function ColorPicker(CellToPickColorFrom As Range) As Integer
'Pokud nechceme hledat cislo barvy vydobneme si je z konkretni bunky
ColorPicker = CellToPickColorFrom.Interior.ColorIndex
End Function
Jak bych měla změnit toto zadání pokud nechci součet buněk, ale počet buněk označených určitou barvou...
Děkuji za odpověď
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2024 |