Cómo crear y utilizar funciones definidas por el usuario en Excel

Para muchas tareas, las funciones habituales de Excel no pueden ayudar. Le mostraremos cómo crear nuevas funciones personalizadas y cómo utilizarlas para facilitar su trabajo. Aprenda a crear y utilizar funciones personalizadas:

En el momento en que comencé a escribir este artículo, Excel ya le presentó más de 450 funciones diferentes. Con su ayuda podrás realizar una gran variedad de operaciones diferentes. Sin embargo, no cualquier tarea se puede resolver en Excel ya que sus desarrolladores no pudieron prever todos los problemas a los que nos enfrentamos. Creo que muchos de ustedes han superado al menos uno de estos desafíos:

• No todos los datos pueden procesarse mediante funciones estándar (por ejemplo, fechas anteriores a 1900).
• Las fórmulas pueden ser bastante largas y complejas. Son imposibles de recordar, difíciles de comprender y difíciles de cambiar para cumplir con nuevos criterios.
• No todas las tareas se pueden resolver utilizando funciones estándar de Excel (por ejemplo, no se puede extraer la URL de un hipervínculo).
• Es imposible automatizar operaciones estándar que se repiten con frecuencia (como importar datos de un programa de contabilidad a una hoja de Excel, formatear fechas y números, eliminar columnas innecesarias).

¿Cómo se pueden resolver estos problemas?

• Algunos usuarios crean un archivo de libros con ejemplos. Copian la fórmula deseada desde allí y la aplican a su hoja de cálculo.
• Hay usuarios que prefieren crear un conjunto de macros VBA una vez y ejecutarlas cuando sea necesario.
• Otra forma de hacerlo es crear funciones definidas por el usuario (en adelante, UDF) utilizando el editor VBA.

Si bien las dos primeras opciones suenan familiares, la tercera puede causar cierta confusión. Entonces, echemos un vistazo más de cerca a las funciones personalizadas en Excel y decidamos si vale la pena intentarlo.

¿Qué es la función definida por el usuario (UDF) en Excel?

UDF es una función personalizada que toma datos, realiza un cálculo y devuelve el resultado deseado. Los datos de origen pueden ser números, texto, fechas, valores booleanos e incluso matrices. El resultado de los cálculos puede ser un valor de cualquier tipo con el que trabaje Excel o una matriz de dichos valores.

En otras palabras, UDF es una especie de actualización de las funciones estándar de Excel. Puede utilizarlo cuando las capacidades de las funciones habituales no sean suficientes. Su objetivo principal es complementar y ampliar las capacidades de Excel y realizar acciones que son imposibles con funciones estándar.

Hay varias formas de crear funciones personalizadas:

• Usando Visual Basic para Aplicaciones (VBA). Este método se describe en este artículo.
• Usando la gran función LAMBDA que se introdujo en Office 365.
• Usando scripts de Office. Actualmente, los scripts están disponibles en Excel únicamente en la Web.

Consulte la captura de pantalla a continuación para ver la diferencia entre dos formas de extracción de números: usando una fórmula y una personalizada. ExtraerNúmero() función.

>Puede utilizar UDF de cualquiera de las siguientes formas:

• Como fórmula, donde puede tomar datos sin procesar de su hoja de trabajo y devolver un valor calculado o una matriz de valores.
• Como parte de una macro VBA u otro código de función personalizada.
• Como componente de sus fórmulas de formato condicional.
• Para almacenar constantes y listas de datos.

¿Cómo crear una función personalizada en Excel?

En primer lugar, debe abrir el Editor de Visual Basic (VBE). Tenga en cuenta que simplemente se abre en una ventana nueva y no cierra su hoja de cálculo de Excel.

La forma más sencilla de abrir VBE es mediante un método abreviado de teclado: Alt + F11. Es rápido, sencillo y no es necesario personalizar la cinta ni la barra de herramientas de acceso rápido.

>Después de abrir VBE, necesita agregar un nuevo módulo donde escribirá sus funciones. Haga clic derecho en el panel del proyecto VBA y seleccione Insertar -> Módulo. Aparecerá una ventana de módulo vacía donde deberá especificar su función personalizada.

Antes de comenzar, repasemos las reglas mediante las cuales se crean las UDF:

• Una función definida por el usuario siempre comienza con «Función» y termina con «Finalizar función».
• «Función» va seguida del nombre de la función. Este es un título que usted crea y le da a su función para poder identificarla y usarla más adelante. Este nombre no debe contener espacios. Si desea separar palabras, utilice guiones bajos. Por ejemplo, Count_Words.
• Además, el nombre tampoco puede ser el mismo que los nombres de las funciones estándar de Excel. Si hace esto, siempre se ejecutará la función estándar.
• El nombre de la función definida por el usuario no puede coincidir con las direcciones de las celdas de la hoja de trabajo. Por ejemplo, el nombre ABC1234 no es válido.

• A continuación, los argumentos de la función suelen aparecer entre paréntesis. Estos son los datos con los que funcionará. Puede haber uno o varios argumentos. Si tiene varios argumentos, debe enumerarlos separados por comas.
• Si las funciones dentro de la UDF no usan argumentos (por ejemplo, AHORA, HOY o RAND), entonces puede crear una función con sin argumentos. Además, no se necesitan argumentos si utiliza una UDF para almacenar constantes (como pi).
• Después de eso, especifique las variables que utiliza la UDF. Se indica el tipo de estas variables: número, fecha, texto, matriz.
• Luego colocas varias declaraciones de VBA que realizan cálculos utilizando los argumentos pasados ​​a la función.
• Al final, debes escribir una declaración que asigne el valor final a una variable con el mismo nombre que el de la función. Este valor se devuelve a la fórmula desde la cual se llamó la función definida por el usuario.
• El código de función personalizado puede incluir comentarios. Le ayudarán a recordar el propósito de una función y sus operadores. Si desea realizar algún cambio en el futuro, los comentarios serán de gran ayuda.

Ahora intentemos crear su primera fórmula personalizada. Para empezar, creamos una función personalizada que contará la cantidad de palabras en un rango de celdas. Para hacer esto, inserte este código en la ventana del módulo:

Función ContarPalabras(NumRange Como Rango) Como Largo
Oscuro rCelda Como Rango, recuento Como Largo

Para Cada rCelda En NumRange lCount = lCount + _ Len (Recortar(rCell)) – Len (Reemplazar (Recortar(rCell), » «, «»)) + 1
Próximo rCell CountWords = lCount
Fin Función

>Creo que puede ser necesaria alguna aclaración aquí. Como recordará de las reglas anteriores, el código UDF siempre comienza con la palabra «Función». Luego hacemos una descripción de la nueva función y cerramos nuestra UDF con “End Function”.

Como indicamos entre paréntesis los datos iniciales que utilizará, NumRange como rango significa que el argumento UDF será un rango de valores. Esta función necesita devolver solo un argumento: el rango de celdas.

En la segunda línea de código, declaramos variables.

Mientras tanto indica que el resultado de la Contar palabras La función será un número entero.

El Oscuro declaración declara dos variables de nuestra función:

• rCelda es la variable del rango de celdas en las que contaremos palabras.
• lContar es una variable entera que contendrá el número de palabras.

El Para cada El argumento está diseñado para realizar cálculos en cada elemento de un grupo de elementos (rango de celdas). Este operador de bucle se utiliza cuando se desconoce el número de elementos del grupo. Comenzamos con el primer elemento, luego tomamos el siguiente y así sucesivamente hasta el último valor. El bucle se repite tantas veces como celdas haya en el rango de entrada.

Dentro de este bucle, se aplica una operación que calcula el número de palabras al valor de todas y cada una de las celdas:

Len (Recortar(rCell)) – Len(Reemplazar(Recortar(rCell), » «, «»)) + 1

Como puede ver, esta es una fórmula normal de Excel que utiliza las funciones de texto estándar: LEN, TRIM y REPLACE. En lugar de la referencia de celda, usamos la variable de rango rCelda. Por lo tanto, para cada celda del rango, contamos secuencialmente el número de palabras que contiene.

Los números contados se suman y se almacenan en el lContar variable:

lCount = lCount + Len (Trim(rCell)) – Len(Replace(Trim(rCell), » «, «»)) + 1

Cuando finaliza el ciclo, el valor de la variable se asigna a la función.

CountWords = lCount

La función devuelve el resultado de esta variable a la celda de la hoja de trabajo, que es el número total de palabras.

Es esta línea de código la que garantiza que la función devolverá el lContar valor a la celda desde la que se llamó.

Como puedes ver, no es muy difícil. Guarde su función haciendo clic en el botón «Guardar» en la cinta VBE. Después de eso, puedes cerrar la ventana del editor. Para hacer esto, puedes usar el atajo de teclado. Alt + Q. O simplemente regrese a la hoja de Excel presionando Alt + F11.

Puede comparar el trabajo con la función personalizada CountWords y contar el número de palabras usando fórmulas y elegir el método que más le convenga.

Cómo utilizar funciones personalizadas

Cuando crea una función personalizada, está disponible de la misma manera que otras funciones estándar de Excel. Ahora vamos a aprender a crear fórmulas personalizadas.

Para aplicar una función definida por el usuario, tiene dos opciones.

• Haga clic en el fx botón en la barra de fórmulas. Entre las categorías de funciones, verá un nuevo grupo: Usuario definido. Y en esta categoría, puedes ver nuestra nueva función personalizada. Contar palabras.
  >
• Simplemente puede escribir esta función en una celda de la misma manera que lo hace con las funciones estándar. Cuando comience a escribir un nombre, Excel le mostrará el nombre de la función definida por el usuario en una lista de funciones coincidentes. En el siguiente ejemplo, cuando ingresé =couExcel me mostró una lista de funciones coincidentes, entre las que ves Contar palabras.
  >

Finalmente apliquemos nuestra fórmula personalizada. Escríbelo en cualquier celda:

= CountWords(A1: A4)

Prensa Ingresar. Mira, acabamos de especificar la función y configurar un rango y aquí está el resultado del recuento: 28 palabras.

>Varios tipos de funciones definidas por el usuario

Ahora comprobaremos diferentes tipos de UDF según los argumentos que utilizan y los resultados que devuelven.

Sin argumentos

Excel tiene varias funciones estándar que no requieren argumentos (RAND, HOY, AHORA). Por ejemplo, la función ALEATORIO devuelve un número aleatorio entre 0 y 1. La función HOY devolverá la fecha actual. No es necesario que les especifique ningún valor.

La buena noticia es que también puedes crear una función de este tipo en VBA. A continuación se muestra el código que escribirá el nombre de su hoja de trabajo en una celda:

Función Nombre de hoja() como Cadena

Aplicación.Volatile SheetName = Aplicación.Caller.Worksheet.Name

Fin Función

O puedes usar este código en su lugar:

NombreDeHoja = HojaActiva.Nombre

Observe que no hay argumentos entre paréntesis después del nombre de la función. Dado que el resultado a devolver no depende de ningún valor en su archivo de trabajo, la función no requiere ningún argumento.

El código anterior define el resultado de la función como un tipo de datos de cadena (ya que el resultado deseado es un nombre de archivo, que es texto). Si no especifica el tipo de datos, Excel lo determinará por sí solo.

con un argumento

Ahora creemos una función simple que funcione con un argumento que es una celda.

Nuestra tarea es extraer la última palabra de la cadena de texto. Aquí está el código que usaremos:

Función RetornarÚltimaPalabra(El_Texto Como Cadena)
Oscuro stÚltimaPalabra Como Cadena

‘Extrae la ÚLTIMA palabra de una cadena de texto
stLastWord = StrReverse(The_Text) stLastWord = Left(stLastWord, InStr(1, stLastWord, » «vbTextCompare)) ReturnLastWord = StrReverse(Trim(stLastWord))
Fin Función

El texto es el valor de la celda seleccionada. Indicamos que este debe ser un valor de texto (Como cuerda).

El StrReverse La función devuelve texto en orden inverso de caracteres. A continuación, el InStr La función determina la posición del primer espacio. Utilizando el Izquierda función, obtenemos todos los caracteres que terminan con el primer espacio. Luego eliminamos los espacios con Recortar. Cambie el orden de los caracteres nuevamente usando StrReverse. Obtenemos la última palabra del texto.

Dado que esta función toma un valor de celda, no necesitamos usar Aplicación.Volátil aquí. Tan pronto como cambie el argumento, la función se actualizará automáticamente.

Usar una matriz como argumento

Muchas funciones de Excel utilizan matrices de valores como argumentos. Recuerde las funciones SUMA, SUMA.SI, SUMAPRODUCTO. Ya cubrimos esta situación anteriormente cuando aprendimos cómo crear una función personalizada para contar la cantidad de palabras en un rango de celdas.

El siguiente código crea una función que suma todos los números pares en un rango específico de celdas.

Función SumaPar(NumRange como Rango)
Oscuro RngCell Como Rango

Para Cada RngCell En rangonumérico
Si Es numérico (RngCell.Value) Entonces
Si RngCell.Valor Modificación 2 = 0 Entonces
Resultado = Resultado + RngCell.Value
Fin Si
Fin Si
Próximo RngCell SumEven = Resultado
Fin Función

El rangonumérico El argumento se especifica como Rango. Esto significa que la función utilizará la matriz de datos original. Cabe señalar que también se puede utilizar el tipo de variable Variante. Parece que

Función SumaPar(NumRange como variante)

El Variante type proporciona un contenedor «no dimensional» para almacenar datos. Una variable de este tipo puede almacenar cualquiera de los tipos de datos permitidos en VBA, incluidos valores numéricos, texto, fechas y matrices. Además, la misma variable en el mismo programa en diferentes momentos puede almacenar datos de diferentes tipos. Excel determinará por sí solo qué datos se pasan a la función.

El código tiene un Para cada argumento. Toma cada celda y verifica si contiene un número. Si no es así, no pasa nada y pasa a la siguiente celda. Si encuentra un número, comprueba si es par o no (usando la función MOD).

Todos los números pares se resumen en Resultado variable.

Cuando finaliza el bucle, el Resultado se asigna valor a la suma par variable y pasada a la función.

Con múltiples argumentos

La mayoría de las funciones de Excel tienen múltiples argumentos. Las funciones personalizadas no son una excepción. Por eso es tan importante poder crear UDF con múltiples argumentos.

El siguiente código crea una función que selecciona el número máximo en un rango determinado:

Función GetMaxBetween(rngCells Como Rango, Núm Mínimo, Núm Máx)
Oscuro rangonumérico Como Rango
Oscuro vmáx.
Oscuro números de matriz()
Oscuro i Como Entero

Redim arrNums(rngCells.Count)
Para Cada rangonumérico En rngCells vMax = NumRange
Seleccionar Caso vmáx.
Caso Númmínimo + 0,01 A MaxNum – 0.01 arrNums(i) = vMax i = i + 1
Caso Demás
ObtenerMaxEntre = 0
Fin Seleccionar
Próximo NumRange GetMaxBetween = WorksheetFunction.Max(arrNums)

Fin Función

Tiene 3 argumentos: un rango de valores, un límite inferior para un rango numérico y un límite superior para un rango. El primero es rngCells como rango. Este es el rango de celdas en las que buscar el valor máximo. El segundo y tercer argumento (Número mínimo, Núm máx.) se especifican sin una declaración de tipo. Esto significa que el Variante El tipo de datos se les aplicará de forma predeterminada.

VBA utiliza 6 tipos de datos numéricos diferentes. Especificar solo uno de ellos significa limitar el uso de la función. Por lo tanto, sería mejor si Excel determinara por sí mismo el tipo de datos numéricos.

El Para cada recorre todos los valores en el rango seleccionado secuencialmente. Los números que están en el rango del valor máximo al mínimo se escriben en una matriz especial: números de arreglo. Usando la función MAX estándar, encuentre el número más grande en esta matriz.

Con argumentos obligatorios y opcionales.

Para comprender qué es un argumento opcional, recuerde la función BUSCARV. Su cuarto argumento [range_lookup] es opcional. Si omite uno de los argumentos requeridos, su función arrojará un error. Pero si omites el argumento opcional, tu función funcionará.

Sin embargo, los argumentos opcionales no son inútiles. Le permiten elegir su opción de cálculo.

Por ejemplo, en la función BUSCARV, si omite el cuarto argumento, se realizará una búsqueda aproximada. Si especifica el [range_lookup] argumento como FALSO (o 0), se encontrará una coincidencia exacta.

Si su función definida por el usuario tiene al menos un argumento requerido, debe escribirse al principio. Sólo entonces están los opcionales.

Para hacer que un argumento sea opcional, solo necesita agregar «Opcional» antes. Veamos un ejemplo de una función con argumentos opcionales en VBA:

Función ObtenerTexto(celda de texto Como Rango, Opcional CasoTexto = FALSO) Como Cadena
Oscuro Longitud de la cuerda Como Entero
Oscuro Resultado Como Cadena

Longitud de cadena = Len (Celda de texto)

Para yo = 1 A Longitud de la cuerda
Si No (EsNumeric(Mid(textCell, i, 1))) Entonces Resultado = Resultado & Medio(celda de texto, i, 1)
Próximo i

Si CasoTexto = Verdadero Entonces
Resultado = UCase(Resultado) GetText = Resultado
Fin Función

Esta función personalizada recupera texto de una celda. Opcional CasoTexto = Falso significa el Texto de caso El argumento es opcional. Por defecto, su valor está establecido en FALSO.

si el opcional Texto de caso el argumento es VERDADERO, luego el resultado se devuelve en mayúsculas. Si el argumento opcional es FALSO u omitido, el resultado permanece como está, sin cambiar el caso.

Quizás se pregunte: «¿Puede haber sólo argumentos opcionales en una función definida por el usuario?». Tenemos una respuesta para ti a continuación 🙂

Con sólo argumentos opcionales

Hasta donde yo sé, no existe ninguna función incorporada de Excel que solo tenga argumentos opcionales. Es posible que las cosas hayan cambiado desde el día en que escribí este artículo, pero hasta ahora dicha función no existe.

De todos modos, es posible crear una UDF sólo con argumentos opcionales. Como prueba, aquí hay una función personalizada que escribe un nombre de usuario en una celda:

Función Nombre de usuario(Opcional Mayúsculas Como Variante)

Si Falta (mayúscula) Entonces Mayúscula = FALSO

Nombre de usuario = Aplicación.Nombre de usuario

Si Mayúsculas Entonces Nombre de usuario = UCase(Nombre de usuario)

Fin Función

Como puedes ver, solo hay uno. Mayúsculas argumento, y es opcional.

Si el argumento es FALSO u omitido, entonces el nombre de usuario se devuelve sin cambios. Si el argumento de la función es VERDADERO, el nombre se devuelve en caracteres mayúsculos (usando la función Ucase VBA). Observe la primera declaración de la función. Contiene el Está perdido Función VBA, que detecta la presencia de un argumento. Si no hay ningún argumento, la declaración establece la variable en mayúsculas en FALSO.

Eche un vistazo a otra versión de esta función:

Función Nombre de usuario(Opcional Mayúscula = FALSO) Nombre de usuario = Aplicación.Nombre de usuario

Si Mayúscula = Verdadero Entonces
Nombre de usuario = UCase(Nombre de usuario)

Fin Función

En este caso, el argumento opcional por defecto es FALSO. Si la función se ingresa sin argumentos, entonces FALSO se utilizará de forma predeterminada y el nombre de usuario se obtendrá sin cambiar el caso. Si se ingresa cualquier valor distinto de cero, todos los caracteres se convertirán a mayúsculas.

El valor de retorno es una matriz.

VBA tiene una función muy útil llamada Formación. Devuelve un tipo de datos variante, que es una matriz (en pocas palabras, valores múltiples). Si no está familiarizado con las fórmulas matriciales en Excel, le sugerimos comenzar con la guía para principiantes sobre funciones matriciales en nuestro blog.

Las UDF que devuelven una matriz son muy útiles al almacenar matrices de valores. Por ejemplo, el Meses () la función devolverá una serie de nombres de meses:

Función Meses() Como Variante
Meses = Matriz («Enero», «Febrero», «Marzo», «Abril», «Puede», «Junio», «Julio», «Agosto», «Septiembre», «Octubre», «Noviembre», «Diciembre»)
Fin Función

¿Y si necesitas una matriz vertical de valores? Se mencionó anteriormente que las UDF se pueden usar en fórmulas de Excel junto con funciones estándar. Dicho esto, usemos Meses() como argumento en la función TRANSPONER:

= TRANSPOSE(Months())

>Puede utilizar UDF para ingresar datos rápidamente en una tabla como se muestra en la captura de pantalla anterior. Por ejemplo, en un informe de ventas, no es necesario escribir manualmente los nombres de los meses.

Además, puedes obtener el nombre del mes por su número. Por ejemplo, la celda A1 contiene el número del mes. Entonces el nombre del mes se puede obtener usando la fórmula

= INDEX (Months(), 1, A1)

Una versión alternativa de esta fórmula:

=INDEX({«January», «February», «March», «April», «May», «June «,» July «,» August «,» September «,» October «,» November «,» December «}, 1, A1)

De acuerdo, UDF con una matriz hace que la fórmula de Excel sea mucho más fácil.

Este artículo abrirá una serie de publicaciones sobre funciones definidas por el usuario.

Si logré convencerte de que vale la pena probar esta función, te recomiendo que prestes atención a los artículos siguientes.

Usted también podría estar interesado en