Com crear funcions definides per l'usuari a Microsoft Excel

2024 Autora: Jason Gerald | [email protected]. Última modificació: 2023-12-16 11:00

Tot i que Excel ja té centenars de funcions integrades com SUM, VLOOKUP, LEFT, etc., les funcions integrades disponibles no solen ser prou suficients per fer tasques bastant complexes. Tot i això, no us preocupeu, perquè només heu de crear les funcions necessàries vosaltres mateixos.

Pas

Pas 1. Creeu un llibre nou o obriu el llibre que vulgueu processar amb les funcions definides per l'usuari (UDF)

Pas 2. Obriu l'Editor Visual Basic a Microsoft Excel mitjançant Eines-> Macro-> Visual Basic Editor (o premeu la drecera Alt + F11)

Pas 3. Feu clic al botó Mòdul per afegir un mòdul nou al full de càlcul

Podeu crear un UDF en un llibre sense afegir cap mòdul nou, però la funció no funcionarà en altres fulls de treball del mateix llibre.

Pas 4. Creeu el "cap" o el "prototip" de la vostra funció

El prototip de funció ha de seguir l'estructura següent:

funció pública "Nom de la funció" (paràmetre1 com a tipus1, paràmetre2 com a tipus2) com a tipus de resultat.

Els prototips poden tenir tantes funcions com sigui possible i els seus tipus poden ser tots tipus de dades bàsics o tipus d’objectes Excel en forma d’interval. Podeu pensar en paràmetres com a "operants" (operadors) sobre els quals actuarà la funció. Per exemple, quan escriviu SIN (45) per calcular el sinus de 45 graus, es prendrà el número 45 com a paràmetre. A continuació, el codi de la funció utilitzarà aquests valors per realitzar càlculs i mostrar els resultats.

Pas 5. Afegiu el codi de la funció per assegurar-vos que: 1) utilitzeu el valor donat pel paràmetre; 2) passeu el resultat al nom de la funció; i 3) tanqueu la funció amb la frase "funció final"Aprendre a programar en VBA o en qualsevol altre idioma requereix molt de temps i orientacions detallades. Afortunadament, aquestes funcions solen tenir petits blocs de codi i no fan gaire ús de les funcions del llenguatge de programació. Aquests són alguns elements del llenguatge VBA que es poden utilitzar:

1. El bloc If (if), que us permet executar una part del codi només si es compleix la condició. Com un exemple:

Resultat del curs de funció pública (com a valor enter) com a cadena

Si el valor> = 5, llavors

Resultats del curs = "Acceptat"

Altrament

Resultats del curs = "Rebutjat"

Finalitza If

Funció final

Fixeu-vos en els elements del bloc de codi If:

Condició IF LLAVORS codi CODI ELSE FINALitza IF

2. . Podeu deixar de banda la paraula clau Else juntament amb la segona part del codi, ja que és opcional.
3. El bloc Do (do), que executa una part del codi While o Until quan o fins que es compleixi la condició. Com un exemple:

Funció pública BilPrima (valor com a enter) Com a booleà

Dim i As Integer

i = 2

BilPrima = Cert

Fes-ho

Si valor / i = Int (valor / i) Aleshores

BilPrima = Fals

Finalitza If

i = i + 1

Bucle mentre i <value I NumberPrima = True

Funció final

Torneu a mirar els elements:

Feu el codi DO LOOP MENTRE / FINS a la condició

4. . Tingueu en compte també la segona línia que "declara" la variable. Podeu afegir variables al codi per utilitzar-les més tard. Les variables actuen com a valors temporals al codi. Finalment, considereu la declaració de funció com a BOOLEAN, que és un tipus de dades que només permet els valors TRUE o FALSE. Aquest mètode de determinació de nombres primers és lluny de ser òptim, però el codi ha estat escrit de manera que sigui fàcil de llegir.
5. Per bloc (a), que executa una certa quantitat de codi. Com un exemple:

Factor de funció pública (valor com a enter) sempre

Disminueix els resultats com a llargs

Dim i As Integer

Si valor = 0 Aleshores

resultat = 1

ElseIf value = 1 Aleshores

resultat = 1

Altrament

resultat = 1

Per a i = 1 Valorar

resultat = resultat * i

Pròxim

Finalitza If

Factorial = resultat

Funció final

Torneu a mirar els elements:

FOR variable = límit inferior TO límit superior del codi NEXT

. Tingueu en compte també l’element ElseIf addicional a la sentència If, que us permet afegir més opcions al codi que s’executa. Finalment, considereu la funció i el resultat "resultat" declarats com a llargs. El tipus de dades Long permet valors molt més grans que Integer.

A continuació es mostra el codi d'una funció que converteix els nombres petits en paraules.



Pas 6. Torneu al llibre i utilitzeu la funció escrivint el símbol "igual" (=) seguit del nom de la funció a la cel·la

Escriviu els claudàtors d’obertura (“(“) després del nom de la funció amb el signe coma per separar els paràmetres i acabar amb claudàtors de tancament (")"). Com un exemple:

= Carta a número (A4)

. També podeu utilitzar fórmules casolanes cercant-les per categories Usuari definit dins de l’opció Insereix fórmula. Simplement feu clic al botó Fx a l'esquerra de la barra de fórmules. Hi ha tres tipus de formes de paràmetre en les funcions:

1. Un valor constant que s’escriu directament a la fórmula de la cel·la. En aquest cas, s’ha de citar el text (cadena).
2. Referències de cel·les, per exemple B6 o rang com A1: C3 (el paràmetre ha de ser el tipus de dades "Interval")

3. Una altra funció inclosa a la vostra funció (la vostra funció també es pot incloure a una altra funció), per exemple: = Factorial (MAX (D6: D8))

   

   Pas 7. Assegureu-vos que els resultats siguin correctes

   Feu-lo servir diverses vegades per assegurar-vos que la funció sigui capaç de gestionar correctament diversos valors de paràmetres:

   Consells

   • Quan escriviu blocs de codi en estructures de control com ara Si, Per, Fer, etc., assegureu-vos de sagnar (inseriu lleugerament la vora esquerra de la línia interior) el bloc de codi prement diverses vegades la barra espaiadora o la pestanya. Això farà que el codi sigui més fàcil d’entendre i que els errors siguin molt més fàcils de trobar. A més, l’augment de la funcionalitat es fa més fàcil de fer.
   • Si no sabeu com escriure codi per a funcions, llegiu l'article Com escriure una macro simple a Microsoft Excel.
   • De vegades, les funcions no necessiten tots els paràmetres per calcular el resultat. En aquest cas, podeu utilitzar la paraula clau opcional abans del nom del paràmetre a la capçalera de la funció. Podeu utilitzar la funció IsMissing (nom_paràmetre) al codi per determinar si a un paràmetre se li assigna un valor o no.
   • Utilitzeu els noms que no s’utilitzen com a funcions a Excel perquè no se sobreescrivin i esborrin cap funció.
   • Excel té moltes funcions integrades i la majoria de càlculs es poden fer utilitzant aquestes funcions integrades, de manera individual o simultània. Assegureu-vos de fer un cop d'ull a la llista de funcions disponibles abans de començar a codificar-vos. L'execució es pot fer més ràpidament si utilitzeu funcions integrades.

   Advertiment

   • Per motius de seguretat, molta gent desactiva les macros. Assegureu-vos que notifiqueu als destinataris del llibre que el llibre enviat té macros i que aquestes macros no perjudicaran els seus equips.
   • La funció utilitzada en aquest article no és la millor manera de resoldre el problema relacionat. L'exemple s'utilitza per explicar l'ús d'estructures de control del llenguatge.
   • VBA, com altres idiomes, té diverses altres estructures de control a més de Do, If i For. L'estructura comentada aquí només descriu el que es pot fer al codi font de la funció. Hi ha moltes guies a Internet que us poden ajudar a aprendre VBA.