Egyéni függvények létrehozása az Excelben

Az Excel rengeteg beépített függvényt nyújt, de mégis előfordulhat, hogy olyan számítást kell végeznie, amelyhez nem érhető el beépített függvény. Az Excel tervezői érthető módon nem tudták előre megjósolni az összes felhasználó számítási igényeit. Ehelyett az Excel lehetőséget nyújt Önnek arra, hogy létrehozza a saját, egyéni függvényeit, melynek folyamatával részletesen megismerkedhet a jelen cikkben.

További információra van szüksége arról, hogy miként hozhat létre egy JavaScript-függvényt a Windows Excel, a Mac Excel vagy a Webes Excel használatával? Ebben a cikkben az Excel egyéni függvények áttekintésecímű témakörben talál további tájékoztatást.

Az egyéni függvények, csakúgy mint a makrók, a VBA programozási nyelvet használják. Két lényeges módon azonban különböznek a makróktól. Először is Function típusú eljárásokat használnak Sub típusú eljárások helyett. Vagyis Function utasítással kezdődnek, nem pedig Sub utasítással, és End Function utasítással végződnek, nem pedig End Sub utasítással. Másodszor is az egyéni függvények számításokat végeznek, nem pedig műveleteket. Az utasítások bizonyos típusai, például a tartományokat kijelölő és formázó utasítások, nem használhatók az egyéni függvényekben. A jelen cikkből megtudhatja, hogyan hozhat létre és használhat egyéni függvényeket. A függvények és makrók létrehozásához a Visual Basic Editor (VBE) szerkesztőt kell használnia, mely egy, az Excel ablakától különálló ablakban nyílik meg.

Tegyük fel, hogy a vállalata 10%-os mennyiségi engedményt nyújt a termékek értékesítésekor, ha a rendelés mennyisége meghaladja a 100 egységet. Az alábbiakból megtudhatja, hogyan számíthatja ki ezt az engedményt egy egyéni függvény segítségével.

Az alábbi példában egy olyan rendelési űrlap látható, amely felsorolja az egyes tételeket, azok mennyiségét, egységárát és engedményét (ha érvényes), illetve az ezekből kiszámított árat.

Példa egy egyéni függvény nélküli megrendelésre

Hozzon létre a munkafüzetben egy DISCOUNT nevű egyéni függvényt:

  1. Nyomja le az Alt+F11 billentyűkombinációt (Macen az FN+ALT+F11 billentyűkombinációt) a Visual Basic Editor szerkesztő megnyitásához, majd válassza az Insert (Beszúrás) > Module (Modul) lehetőséget. Ekkor megjelenik egy új modulablak a Visual Basic Editor ablakának jobb oldalán.

  2. Másolja az alábbi kódot a vágólapra, és illessze be az új modulba.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Megjegyzés: Ha olvashatóbbá szeretné tenni a kódot, a Tab billentyűvel behúzhatja a sorokat. A behúzás csak az olvashatóságot javítja, és a kód futását nem befolyásolja. Miután beírt egy behúzással ellátott sort, a Visual Basic Editor azt feltételezi, hogy a következő sort is hasonló behúzással szeretné kezdeni. Ha a behúzást csökkenteni kívánja egy tabulátorkarakterrel, nyomja le a Shift+Tab billentyűkombinációt.

Készen is áll az új DISCOUNT függvény használatára. Zárja be a Visual Basic Editort, válassza a G7 cellát, és írja be a következőt:

=DISCOUNT(D7;E7)

Az Excel ekkor kiszámítja a 200 darab, 4750 forint egységárú tétel 10%-os engedményét, és visszaadja a 95 000 forint értéket.

A VBA-kód első sorában a függvény ENGEDMÉNYe (mennyiség, ár) mezőben azt jelezte, hogy a DISCOUNT függvénynek két argumentuma van, a mennyiség és az Ár. Ha a munkalapon egy cellában hívja fel a függvényt, akkor a két argumentumot is be kell írnia. Az = DISCOUNT (D7, E7) képletben a D7 a mennyiség argumentum, a E7 pedig az Ár argumentum. A DISCOUNT képletet mostantól a G8: G13-ra is másolhatja, hogy az alább látható eredményeket kapja.

Nézzük meg, hogyan értelmezi az Excel a függvényt. Ha lenyomja az ENTERbillentyűt, az Excel az aktuális munkafüzetben keresi az engedményt , és úgy találja, hogy az az egyéni függvény egy VBA-modulban. A zárójelek, a mennyiség és az Árzárójelben lévő argumentumok helyőrzői a kedvezmény számításának alapjául szolgáló értékek helyőrzői.

Példa egy egyéni függvényt használó megrendelésre

Az alábbi kódblokkban az „If” utasítás megvizsgálja a quantity argumentumot, és megállapítja, hogy a tételek eladott száma meghaladja-e a 100 értéket:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ha az eladott tételek száma 100 vagy annál nagyobb, a VBA végrehajtja a következő utasítást, mely megszorozza a quantity értéket a price értékkel, majd az így kapott eredményt megszorozza a 0,1 értékkel:

Discount = quantity * price * 0.1

Az eredményt a kód a Discount nevű változóban tárolja. Azokat a VBA-utasításokat, amelyek egy értéket egy változóban tárolnak, hozzárendelés típusú utasításoknak nevezzük, mert kiértékelik az egyenlőségjel jobb oldalán lévő kifejezést, és hozzárendelik annak eredményét a bal oldalon megnevezett változóhoz. Mivel a Discount változó neve megegyezik a függvényeljárás nevével, az e változóban tárolt értéket visszaadja a program a DISCOUNT függvényt meghívó munkafüzetképletnek.

Ha a quantity értéke kevesebb mint 100, a VBA a következő utasítást hajtja végre:

Discount = 0

Ezután az alábbi utasítás kerekíti a Discount változóhoz társított értéket két tizedesjegy pontosságúra:

Discount = Application.Round(Discount, 2)

A VBA-ban nem található kerekítési függvény, az Excelben azonban igen. Ezért ez az utasítás a kerekítés végrehajtásához az Excelhez tartozó „Application” objektum „Round” (Kerekítés) metódusának használatára utasítja a VBA-t. Ezt úgy éri el, hogy hozzáadja az Application megnevezést a Round szó elé. Használja Ön is ezt a szintaxist, ha Excel-függvényt szeretne elérni egy VBA-modulból.

Az egyéni függvényeknek „Function” utasítással kell kezdődniük, és „End Function” utasítással kell végződniük. A függvény neve mellett a „Function” utasítás általában egy vagy több argumentumot is megad. Hozhat létre azonban argumentumok nélküli függvényt is. Az Excel számos olyan beépített függvényt tartalmaz, amelynek nincs argumentuma, ilyen például a VÉL és a MOST függvény.

A „Function” utasítást követően a függvényeljárásnak egy vagy több olyan VBA-utasítást kell tartalmaznia, amely döntéseket hoz és számításokat végez a függvénynek átadott argumentumok használatával. Végül, a függvényeljárás egy tetszőleges pontján el kell helyeznie egy utasítást, mely hozzárendel egy értéket a függvény nevével megegyező nevű változóhoz. Ezt az értéket adja vissza a program a függvényt meghívó képletnek.

Az egyéni függvényekben kevesebb VBA-kulcsszót tud használni, mint a makrókban. Az egyéni függvények számára mindössze annyi engedélyezett, hogy visszaadjanak egy értéket a munkafüzetben lévő képletnek vagy egy másik VBA-makróban vagy -függvényben szereplő kifejezésnek. Az egyéni függvények például nem méretezhetnek át ablakokat, nem szerkeszthetik a cellákban lévő képleteket, illetve nem módosíthatják a cellaszövegek betűtípusát, színét és kitöltési beállításait. Ha ehhez hasonló „műveleti” kódot helyez el egy függvényeljárásban, a függvény #ÉRTÉK! hibával tér vissza.

A függvények elvégzésére szolgáló egyetlen művelet (a számítások elvégzésén kívül) megjelenik egy párbeszédpanel. Az InputBox utasításait egyéni függvényekben is használhatja, így a függvényt végrehajtó felhasználótól beviheti az adatokat. Az MsgBox utasítással információkat továbbíthat a felhasználóhoz. Használhatja az egyéni párbeszédpaneleket, illetve a UserForm űrlapok, de a bevezetések körén kívül eső témát is.

Még az egyszerűnek tűnő makrókat és egyéni függvényeket is nehéz lehet később megérteni. Könnyebben olvashatóvá teheti őket úgy, hogy magyarázatot fűz hozzájuk megjegyzések formájában. Megjegyzés hozzáadásához írja be a magyarázó szöveget, és helyezzen el elé egy aposztróf karaktert. Az alábbi példa a DISCOUNT függvényhez hozzáadott megjegyzéseket szemlélteti. Ha ehhez hasonló megjegyzéseket ad hozzá, azzal megkönnyítheti a VBA-kód későbbi szerkesztését saját maga és mások számára. Ha később módosítania kell a kódot, egyszerűbb lesz megérteni, hogy a kód milyen műveleteket végez, milyen célból.

Példa megjegyzéseket tartalmazó VBA-függvényre

Az aposztróf karakter arra utasítja az Excelt, hogy hagyja figyelmen kívül az adott sorban az aposztróftól jobbra található összes többi karaktert, így nemcsak teljes sort használó megjegyzéseket tud létrehozni, hanem a VBA-kódsorok jobb szélére is írhat megjegyzést. Célszerű lehet a hosszabb kódblokkokat egy megjegyzéssorral kezdeni, mely ismerteti a kódblokk általános célját, majd további megjegyzéseket hozzáadni az egyes utasításoktól jobbra.

A makrók és egyéni függvények dokumentálásának másik módja az, ha leíró neveket használ. Például a Címkék név helyett használhatja a Hónapcímkék nevet, mely pontosabban leírja a makró rendeltetését. A leíró nevek használata különösképpen nagy segítség lehet akkor, ha sok olyan eljárást kell létrehoznia, amelyek hasonló, de nem teljesen azonos célt szolgálnak.

A makrók és egyéni függvények dokumentálásának pontos módja egyéni ízlés kérdése. A legfontosabb az, hogy a használt dokumentálási módszert egységesen alkalmazza a kódban.

Az egyéni függvények használatához meg kell nyitnia azt a munkafüzetet, amelyben a függvényt tartalmazó modul található. Ha ez a munkafüzet nincs megnyitva, akkor a #NÉV? hibát kapja, amikor megkísérli használni a függvény. Ha egy másik munkafüzetben lévő függvényre hivatkozik, akkor a függvény neve előtt meg kell adnia a függvényt tartalmazó munkafüzet nevét. Ha például létrehoz egy DISCOUNT nevű függvényt egy Személyes.xlsb nevű munkafüzetben, és egy másik munkafüzetből szeretné meghívni ezt a függvényt, akkor a =személyes.xlsb!discount() képletet kell használnia, nem pedig egyszerűen a =discount() képletet.

Csökkentheti a beírandó szöveg mennyiségét (és elkerülheti az esetleges hibákat), ha az egyéni függvényt a Függvény beszúrása párbeszédpanelen választja ki. Az egyéni függvények a Felhasználói kategóriában jelennek meg:

függvény beszúrása párbeszédpanel

Egyszerűbben is elérheti bárhonnan az egyéni függvényeit, ha egy külön munkafüzetben tárolja őket, majd menti az adott munkafüzetet bővítményként. Ezután a bővítményt beállíthatja úgy, hogy mindig elérhető legyen az Excel futtatásakor. Ezt a következő módon teheti meg:

  1. Miután létrehozta a kívánt függvényeket, kattintson a Fájl > Mentés másként parancsra.

    Excel 2007 használata esetén kattintson a Microsoft Office gombra, majd a Mentés másként parancsra.

  2. Nyissa meg a Mentés másként párbeszédpanelen a Fájl típusa legördülő listát, és válassza az Excel-bővítmény lehetőséget. Mentse a munkafüzetet jól felismerhető néven, például SajátFüggvények, az AddIns mappába. A Mentés másként párbeszédpanel automatikusan ezt a mappát fogja felkínálni, így csak annyit kell tennie, hogy elfogadja az alapértelmezett helyet.

  3. Miután mentette a munkafüzetet, kattintson a Fájl > Az Excel beállításai lehetőségre.

    Excel 2007 használata esetén kattintson a Microsoft Office gombra, majd Az Excel beállításai parancsra.

  4. Az Excel beállításai párbeszédpanelen válassza a Bővítmények kategóriát.

  5. Válassza a Kezelés legördülő listában az Excel bővítmények elemet. Ezután kattintson az Ugrás gombra.

  6. A Bővítménykezelő párbeszédpanelen jelölje be a mentett munkafüzet neve melletti jelölőnégyzetet, az alábbi képen szemléltetett módon.

    bővítmények párbeszédpanel

  1. Miután létrehozta a kívánt függvényeket, kattintson a Fájl > Mentés másként parancsra.

  2. Nyissa meg a Mentés másként párbeszédpanelen a Fájl típusa legördülő listát, és válassza az Excel-bővítmény lehetőséget. Mentse a munkafüzetet jól felismerhető néven, például SajátFüggvények.

  3. Miután mentette a munkafüzetet, kattintson az Eszközök > Excel-bővítmények lehetőségre.

  4. A Bővítmények párbeszédpanelen válassza a Tallózás gombot, keresse meg a bővítményt, kattintson a Megnyitás gombra, majd jelölje be a bővítmény melletti jelölőnégyzetet a Választható bővítmények párbeszédpanelen.

A fenti lépések végrehajtása után az egyéni függvényei mindig elérhetők lesznek az Excel használatakor. Ha szeretné bővíteni a függvénytárát, térjen vissza a Visual Basic Editorba. A Visual Basic Editor projektböngészőjének VBAProject csoportjában megtalálhatja a bővítményfájlja nevét. A bővítménye az .xlam kiterjesztést fogja használni.

elnevezett modul a Visual Basic Editorban

Ha duplán kattint erre a modulra a projektböngészőben, a Visual Basic Editor megjeleníti az egyéni függvényei kódját. Új függvény hozzáadásához helyezze a beszúrási pontot a kódablakban az utolsó függvény „End Function” záró utasítása mögé, és kezdje el beírni az új függvényt. Ezzel a módszerrel bármennyi függvényt hozzáadhat, melyek mindig elérhetők lesznek a Függvény beszúrása párbeszédpanel „Felhasználói” kategóriájában.

Ezt a tartalmat eredetileg Mark Dodge és Craig Stinson írta a Microsoft Office Excel 2007 Inside Out című könyv részeként. A tartalom frissítve lett az Excel újabb verzióinak megfelelően.

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Megjegyzés:  Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Hasznos volt ez az információ az Ön számára? Itt találja az eredeti angol nyelvű cikket.

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×