Excel Avanzado

Un blog para compartir los tips de Excel Avanzado que todos necesitamos!

Excel Avanzado

Funciones definidas por el usuario (UDF)

Las funciones definidas por el usuario, conocidas también como UDF por sus siglas en Inglés (User Defined Functions)  permiten, por medio del uso de macros o código vba, el contar con funciones similares a las que existen de forma predefinida en excel.

La variedad de temas que se pueden abordar por medio de estas funciones es numerosa, si se esta comenzando a aprender el uso de este tipo de funciones convendría comenzar realizando rutinas pequeñas que favorezcan el aprendizaje del lenguaje,  a  continuación se presenta una relación de ejemplos de lo que se pueden realizar por medio de las funciones definidas por el usuario.

Cabe recordar que estas funciones pueden ser empleadas desde cualquier parte del proyecto, ya sean las hojas, ThisWorbook, módulos, y formularios, y si son empleadas desde las hojas la invocación se realiza de la misma forma que se procede para las funciones ya existentes como BuscarV, Concatenar, etc.

Revise los siguientes ejemplos de UDF (funciones definidas por el usuario), en todos ellos encontrará muy diversos ejemplos de como aplicar estas funciones.

Si alguna de estas funciones definidas por el usuario posee un error o contiene una mejora que usted considera conveniente o necesaria, por favor registre un comentario para que procedamos a revisar la publicación.

  1. Contar el número de alumnos aprobados/desaprobados en un rango de celdas
  2. Problema de Maximización de Utilidad
  3. Calcular el monto de pago total por un préstamo (El principal)
  4. Distancia entre 2 puntos
  5. Punto de Equilibrio
  6. ¿Qué es el WACC?
  7. Categoría Deudor SBS
  8. Función Vida Optima de Explotación
  9. Cálculo de Tasas Equivalentes y Cuota
  10. Indicador de grasa corporal, RCC e IMC.
  11. Hallando la distancia de dos puntos
  12. Deflactar una Base de Datos
  13. Cálculo de Integrales Inmediatas Definidas
  14. Valor en Riesgo (VaR)
  15. UDF: Cálculo de cuota por departamento para recibos compartidos
  16. Calcular propina
  17. Ingreso Estimado con descuento
  18. UDF - Índices de Rentabilidad
  19. UDF - Sacar nota de un curso
  20. Funciones definidas por el usuario
  21. UDF-Cálculo de Resistencia Equivalente
  22. UDF control de calidad IKEA
  23. Función para hallar el Promedio Ponderado del Costo del Capital (WACC)
  24. Calculo de descuentos por prestamos
  25. UDF Calorías necesarias según Harris-Benedict (mujeres)
  26. UDF- Prueba Defensiva (Ratio de Liquidez)
  27. UDF Tabla de Equivalencias para Repostería
  28. Precio de una acción
  29. UDF para la Calificación y Evaluación de Proveedores
  30. UDF Cálculo Volumen Reservorio
  31. UDF para hallar la cantidad de horas trabajadas
  32. UDF Índice de concentración de mercado
  33. UDF para el cálculo de los ratios de endeudamiento GAO, GAF y GAT
  34. UDF Aplicado para el Calculo de La Demanda Agregada
  35. UDF aplicado al cálculo de la Energía Potencial, Energía Cinética y Energía Mecánica
  36. UDF aplicado aun juego cotidiano: BINGO
  37. UDF Costos totales de un presupuesto de producción
  38. Cálculo de Impuesto de Renta 5ta Categoría
  39. UDF Descuento AFP
  40. UDF - Cálculo de Límites de control
  41. Función Tamaño de Intervalo de Confianza (TIC)
  42. UDF aplicado a el principio de Arquímides
  43. UDF Cálculo de Comisión Mensual
  44. UDF Aplicado al Flujo de Caja Libre y al Periodo de Recuperación de la Inversión
  45. Funcion Calculo de BETA (Aplicación a Finanzas)
  46. UDF aplicado al Costo Unitario Total
  47. UDF aplicado al Ratio de Sharpe
  48. UDF Ahorro anual proyectado
  49. Elasticidad de la Demanda
  50. UDF Conversiones de Temperatura
  51. UDF del cálculo de pronostico usando el método estacional multiplicativo
  52. UDF Funcion de Indice Masa Corporal
  53. El Campo Magnético de un alambre según la Ley de Biot-Savart
  54. UDF aplicado a Cálculo de la CTS
  55. UDF Aplicado al cálculo del Índice de Ruffier
  56. Función Porcuantotevas
  57. UDF para calcular el costo de la energía consumida por un aparato eléctrico.
  58. Función ValorCajaCero
  59. UDF aplicado al Cálculo de la Rentabilidad Real
  60. UDF aplicado a Cálculo de Ahorro en un Cuadro Comparativo de Precios
  61. UDF aplicado a obtener la Variación de Productividad de una empresa
  62. UDF aplicado al calculo de rentabilidad de un portafolio, modelo CAPM
  63. UDF aplicado al cálculo del Indice de Masa Corporal
  64. UDF Aplicado a la suma de decibeles
  65. UDF Aplicado a cálculo de Fracciones de Colesterol y Riesgo coronario
  66. UDF Suma de valores en negrita
  67. UDF para calcular el Descuento de AFP
  68. UDF aplicado a los rangos de Retorno Esperado de una acción
  69. Cantidad de agua requerida, según peso, calorías consumidas y horas de actividad física.
  70. UDF aplicado al cálculo del lote económico de compra (EOQ)
  71. UDF: función del Indice de Riesgo Ambiental IRA
  72. UDF Función Tipo de Cambio Forward Venta (fwventa)
  73. Función CompensacLCE
  74. Velocidad Final y Altura en MRUV en caida libre
  75. UDF-cuota leasing
  76. USD - Función del Ratio BEP
  77. Extraer números VBA - Ejemplo con Tipo de cambio
  78. Cambiar de categoría de una UDF
  79. Ideas para concatenar celdas con VBA o Macros
  80. UDF aplicado a Cálculo de las Notas Curso Excel
  81. UDF aplicado a Calculo de la CTS
  82. UDF - Estado de Resultados para cuantificar la Utilidad Empresarial
  83. Cálculo Nitrógeno Amoniacal
  84. UDF Promedio de ventas
  85. Distancia entre los puntos
  86. UDF - Tipo de Obesidad según el IMC
  87. UDF Función Utilidad Retail
  88. UDF: determinación del tamaño de una muestra estadística
  89. UDF - Cálculo de notas del laboratorio de Sistemas Integrados de Producción
  90. Formulas para sacar promedio total y nota requerida
  91. Cantidad Económica de Pedido - modelo comercial de inventarios
  92. Función sumtotaldeQ
  93. Concatenar el contenido de un rango de celdas (mail recordatorio)
  94. Función Suma de Dígitos
  95. UDF – Presupuesto
  96. UDF sacar promedio del curso de Finanzas 1
  97. UDF para calcular el pedido de materiales en el mes
  98. UDF Nota Ponderada de Curso de Postgrado
  99. Función FRC/FCS
  100. Cálculo de Tasas Efectivas y Anualidades
  101. Cálculo del WACC en Excel
  102. Crecimiento promedio geométrico del PBI
  103. Función RandomNumber en VBA
  104. UDF aplicado al conteo de datos
  105. UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico
  106. Punto de Equilibrio y Valor de Equilibrio
  107. Excel Avanzado UDF aplicado al cálculo de la cuota flat
  108. Excel Avanzado: UDF aplicado a determinar la variación de productividad
  109. Excel Avanzado: UDF aplicado al cálculo del costo de oportunidad de un retailer
  110. Excel Avanzado: UDF aplicado al calculo del número de operarios en una planta
  111. UDF aplicado al cálculo de la diagonal de un poliedro
  112. Excel avanzado: UDF aplicado a valuación de inventarios
  113. Excel Avanzado: UDF para el cálculo de Productividad de una Agencia Bancaria
  114. Excel avanzado: UDF aplicado al cálculo del área de un círculo
  115. Excel Avanzado: UDF Aplicado a eleccion de Depositos a Plazo Fijo
  116. Calculando ahorros
  117. Excel Avanzado: UDF aplicado para el cálculo de variación de costo por eficiencia
  118. Excel Avanzado: UDF aplicado al cambio de una Tasa de Interés en Dólares a Soles
  119. Excel Avanzado: UDF para dar vuelto en soles a una venta en dólares
  120. UDF aplicado a la aprobación de un curso
  121. Excel Avanzado: UDF Número de cifras de un Decimal transformado en Binario
  122. Cálculo de tiempo de trabajo de un empleado
  123. Funcion Excel Avanzado: UDF aplicado al calculo del sobregiro bancario
  124. UDF Aplicado al calculo del Rendimiento Esperado de una accion
  125. Uso de Macros En la Elaboracion de un Índice Bursátil
  126. Ejemplo de "For" aplicado al Cálculo del Factorial de un Número
  127. Ejemplo de UDF para el cálculo de la retracción de una factura.
  128. UDF aplicado a la evaluación de avance de un proyecto
  129. Excel Avanzado: UDF aplicado al cálculo del promedio de prácticas y promedio final.
  130. Ejemplo de función para el cálculo de la comisión ganada por ventas
  131. Funcion para calcular el tipo de cambio proyectado
  132. Ejemplo de función para el cálculo del porcentaje de merma ideal
  133. Función para el Cálculo del Test de Diferencias
  134. Calculo de función FORGEN (fórmula general)
  135. Funcion para calcular pago ventas
  136. Función para la Descomposición Factorial de un Número con recursividad
  137. Función para calcular el CRAEST
  138. Función para el Cálculo de la Hipotenusa de un Triángulo Rectángulo
  139. Excel Avanzado: UDF Aplicado al cálculo del índice de Lerner
  140. Ejemplo de UDF para valorar opciones Call con el modelo Black Scholes
  141. Ejemplo de UDF para el Cálculo de Pago de Impuesto a la Renta de Cuarta Categoría
  142. Ejmplo de UDF para Calcular el Tipo de Cambio Forward Teórico
  143. Ejemplo de UDF para calcular el Volumen de un cilindro
  144. Función Ingreso
  145. Función Consumo
  146. Función definida por el usuario: Par-Impar
  147. Función Discriminante de un Polinomio Cuadrático
  148. Función definida por el usuario: Área de un triangulo
  149. Función Calificación
  150. Función BeneficioCosto
  151. Función IMC

44 Comments

  1. PROFESOR COMO CREAR UNA MACRO QUE ME CALCULE EL VALOR% DE MAS DE DOS VALORES Y ENCONTRAR EL PORCENTAJE QUE ESTA MAS BAJO EN LOS TRES AÑOS

    EJEMPLO ENCONTRAR EL VALOR PORCENTUAL D 2009,2010,2011 EN HURTO DE VEHICULOS

  2. Hola Federico
    Respecto a tu consulta, ingresé al ejemplo 27 – “Excel Avanzado: UDF aplicado al cálculo del promedio de prácticas y promedio final”, y con base en ese ejemplo generé una UDF, la cual muestro a continuación:
    Function minimo(año1, año2, año3, año4)
    int1 = año1 / (año1 + año2 + año3 + año4)
    int2 = año2 / (año1 + año2 + año3 + año4)
    int3 = año3 / (año1 + año2 + año3 + año4)
    int4 = año4 / (año1 + año2 + año3 + año4)
    If int1 < int2 And int1 < int3 And int1 < int4 Then
    minimo = int1
    ElseIf int2 < int1 And int2 < int3 And int2 < int4 Then
    minimo = int2
    ElseIf int3 < int1 And int3 < int2 And int3 < int4 Then
    minimo = int3
    Else
    minimo = int4
    End If
    End Function
    Saludos
    Juan Jaén

  3. Hola

    He creado una UDF para determinar la variación porcentual de un dato con respecto de otro. Por ejemplo, deseo conocer el porcentaje de variación del consumo de papel del año 2012 con respecto del año anterio (2011).

    La fórmula sería la siguiente:

    Consumo Año 2011: 1500
    Consumo Año 2012: 2000
    Fórmula = ((2000*100)/1500)-100
    Resultado=33.33% –
    Interpretación: Hubo un incremento en el consumo de papel en un 33.33%
    (menos árboles en el mundo?)

    Bueno, aquí la UDF:

    Function var_porcentual(dato1, dato2)
    resultado = (dato2 * 100 / dato1) – 100
    var_porcentual = resultado
    End Function

    Es una UDF sencilla, pero considero que puede ser de vuestra utilidad…

    Quedo a la espera de comentarios…

    Buen día

    Saludos,
    Juan Jaén

  4. Molesto su atención para que me ayuden a resolver una inquietud que está referida a cómo puedo subir imagenes en este Blog.

    En mi caso y seguramente en el de muchos, es más fácil explicar situaciones, o el desarrollo de lo que hacemos o queremos hacer con la hoja de cálculo, a través de gráficos o imagenes (o copia de pantallas).

    Es por ello que acudo a ustedes a ver si me dan un alcance.

    Mil gracias

    Jorge Rojas.

  5. estima profesor estoy tratando aplicar la funcion buscarv y concatenar, para asignar el nombre a un rango, en una lista que crada con codigo nombre y apellido, a la hora de aplicar la funcion = BUSCARV (A6;ListaClientes;2;FALSO) me sale siempre #¿NUMERO? LE HE DADO TODAS LAS POSIBLES SOLUCIONES y corregido de muchas maneras la formula pero me sigue sliendo lo mismo no se si es que estas funciones no estan en mi equipos y no se como implementarlas, me seria de mucha ayuda ya que soy nuevo en el tema de exel gracias

  6. Hola amigos (as)

    He creado una UDF que permite calcular la muestra númerica a partir de parametros estadisticos el argumento es:

    function muestra (arg1,arg2,arg3,arg4)
    muestra=(arg1*arg1*arg2*arg3)/(arg4*arg4)
    Resultado=muestra
    end function

    Espero sus comentarios y criticas para mejorar.

    • José,
      Buenas noches,

      Podrías reducir el código a una sola línea, igualar resultado a muestra y no tendría sentido si puedes igualar directamente la operación.

      Saludos,
      Yajaira

  7. Necesito crear una función UDF que reciba dos parámetros:

    la hoja de excel y el rango de celdas. y pueda llamar la función:

    =copiar(“Hoja2″,”A1:C10″)

    Al reemplazar en forma UDF no me funciona…

    Código anterior de Excel:

    Sub Macro1()

    sheets(“Hoja1″).Select
    Range(“D5:X30″).Select
    Selection.Copy
    sheets(“Hoja5″).Select
    Range(“A2″).Select
    ActiveSheet.Paste

    End Function

    Código UDF personal:

    Public Function copiar(hoja As Worksheet, celdas As Excel.Range) As Variant

    sheets(“Hoja1″).Select
    Range(“D5:X30″).Select
    Selection.Copy
    sheets(“Hoja5″).Select
    Range(“A2″).Select
    ActiveSheet.Paste

    End Function

    Espero su ayuda, gracias!!

  8. Estimados:
    Bien, si se prueba el MsgBox y se pulsa el boton ayuda, salta la ayuda de office, esto me hace pensar que estoy haciendo algo mal y por eso no salta mi ayuda… necesito alguna respuesta:

    Sub MessageBox()
    MsgBox “El proceso tardará 5 minutos ¿Desea Continuar?”, 3 + 256 + 16384, “Copia de Seguridad.”, “C:\ejemplo.chm”, 71
    End Sub

  9. Pra llenar una base de datos hasta que punto es funcional usar un formulario?…

  10. Como dejo una UDF en Excel de manera que la pueda llamar desde cualquier otro archivo …… Gracias por la atención

    • Luis,
      Para que una UDF esté disponible para cualquier archivo Excel, primero debes crear tu función y guardarla como “Complemento de Excel”, se guardará por defecto en una ruta similar a esta de acuerdo a tu computadora:

      C:\Users\usuario\AppData\Roaming\Microsoft\Complementos

      Luego cierras, vas a otra hoja Excel nueva, ingresas a Archivo/Opciones/Complementos y das click en el botón inferior “Ir”, te va a salir una ventana, das click en “Examinar” y aparecerá tu función guardada, la seleccionas y Aceptar.

      Con esto puedes verificar que en cualquier archivo Excel, en la categoría Definidas por el usuario, aparecerá tu función creada.

      Espero que te sirva.

      Saludos,
      Yajaira

  11. Las funciones definidas por el usuario nos permiten generalizar formulas que usamos a diario y que no se encuentran definidas en excel como los ejemplos que están en este blog. Dejo un código por el cual se puede calcular el área de cualquier polígono regular conociendo su semiperimetro y su apotema:

    Function area_poligonor(semiperimetro, apotema)
    resultado = semiperimetro * apotema
    area_poligonor = resultado
    End Function

  12. Profesor,

    ¿Como puedo verificar que una determinada UDF es más eficiente que la combinación de funciones predeterminadas?

    • Christian,
      Una UDF y la combinación de funciones deben realizar la misma acción que necesitamos hacer en Excel. Sin embargo, una UDF permite ahorrar tiempo, generar un código más sencillo y con una sola acción simplificar eso uso de varias funciones, más aún si éstas son repetitivas.

      Si verificas que la acción se realiza más rápido, con menos código y hace exactamente lo que necesitas, el uso de UDF es el adecuado.

      Saludos,

      Yajaira

  13. Como puedo actualizar el valor de una udf, ya que al copiar y pegar mi función creada a otras celdas, estas muestran el ultimo valor obtenido (el valor de la celda donde se copio)

    he intentado con F9, comandos como “application.volatile” o “Worksheets(“nombre hoja”).Calculatey” y no he podido resolverlo.

    este es mi función:

    Function hosp_capi(numpro As Double)

    Application.Volatile False

    Dim monto As Double
    Dim p As Double

    monto = 0
    p = 0

    For w = 0 To 5

    Select Case w
    Case 0
    p = 0.2
    Case 1
    p = 0.2
    Case 2
    p = 0.2
    Case 3
    p = 0.2
    Case 4
    p = 0.1
    Case 5
    p = 0.1
    End Select

    If ActiveCell.Offset(-2, -w) = “” Then
    hosp_capi = monto
    Exit Function
    Else:
    If ActiveCell.Offset(-2, -w) = 0 Then
    monto = monto + 0
    Else:
    monto = monto + ((ActiveCell.Offset(-2, -w) * p) * ActiveCell.Offset(-1, -w))
    End If
    End If

    Next

    hosp_capi = monto

    End Function

    Espero puedan ayudarme
    Saludos

  14. Hola como pudo usar varias UDF s ? es decir varias funciones como se concatenar

  15. He creado una UDF que me ayuda a calcular el valor futuro conociendo el capital inicial y la tasa efectiva, pero me gustaría crear una UDF que calcule desde la tasa nominal a una efectiva y que calcule también el tiempo.

    Function Vfuturo(C, ief, t)
    resultado = C * (1 + ief) ^ t
    Vfuturo = resultado
    End Function

    • Para calcular la tasa efectiva desde una tasa nominal anual, se debe conocer el periodo de capitalización, por ejemplo si tenemos una tasa de 12% nominal anual, y la capitalización es mensual, nuestra tasa efectiva será: 12%/12 =1% efectiva mensual, se debe conocer el número de periodos en un año, en este caso hay 12 periodos (meses) en un año, aquí el código en VBA:

      Function TasaEfectiva(TNA, num_per_año)

      TasaEfectiva = TNA / num_per_año

      End Function

  16. Hola, qué tal, quisiera saber si alguien me puede ayudar con lo siguiente: es que estoy tratando de hacer un Excel que me permita calcular las diferentes horas para liquidar una nomina, es decir, que al ingresar la hora de inicio del trabajador y la hora de salida, pueda obtener las horas ordinarias, las extras diurnas, extras nocturnas, dominicales… yo lo estuve tratando de hacer con funciones e incluyendo muchas columnas pero me salían unas funciones demasiado complejas que después no se entendían, y cuando creía que estaba listo, faltaba alguna cosa; mejor dicho se me volvió paquidérmico el proyecto. No sé si alguien lo ha hecho antes, pero sería un reto interesante. muchas gracias. por su colaboración. espero su pronta respuesta.

  17. La aplicación de esta herramienta me a ayudado a crear hojas de cálculo en excel para cálculos de funciones geométricas y poder hacer interactura a los alumnos con la matemática y el excel

  18. Hay UDFs muy interesantes.

    En las funciones propias de excel, al momento de usarlas, aparece un comentario de lo que hace la función. Asimismo, cuando se usa el boton de Insertar Función, en la pestaña de Fórmulas, aparecen comentarios para cada una de las variables de la función seleccionada.

    Mi pregunta es como se pueden poner comentarios en las UDF.
    Gracias.

    • Sergio,
      Para poder colocar comentarios en las UDF, basta con colocar el signo ‘ delante de tu comentario, éste cambiará a color verde y dejará de formar parte de la programación del mismo.

      Si deseas que un texto numeroso se convierta en comentario, activas la barra de Edición, seleccionas tu texto y haces click en el ícono “Bloque con comentarios”; para deshacer, haces click en el ícono “Bloque sin comentarios”

      Espero que te sirva.

      Saludos,
      Yajaira

    • Hola :),para poder insertar una descripción a las udf, debes abrir el cuadro de las macros y luego escribir el nombre de la función, notarás que el botón opciones se activa, dale clic y en la descripción escribes lo que la función hace .
      Saludos

  19. Quisera saber como puedo crear una macro porcentual que se de color verde cuando es positivo y rojo cuando es negativo.

    • hola :) con esta función me salio, espero que te sirva

      Function porc(a, b)

      x = ((b – a) / a) * 100
      If x > 0 Then
      porc = x
      ActiveCell.Select
      With Selection.Font
      .ThemeColor = xlThemeColorAccent3
      .TintAndShade = -0.24997711111789
      End With
      End If
      If x < 0 Then
      porc = x
      ActiveCell.Select

      With Selection.Font
      .Color = -16776961
      .TintAndShade = 0
      End With
      End If
      End Function

      Saludos

    • Por cierto , el color lo saque utilizando el grabador de macro :)

    • Se podría prender el grabador y efectuar el cambio de color de texto mediante el formato condicional, luego revisas el codigo VBA, se tendrá algo parecido a esto:

      Sub Macro2()

      ‘ Macro2 Macro

      Cells.Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
      Formula1:=”=11″
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
      .Color = -11489280
      .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
      Formula1:=”=11″
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
      .Color = -16383844
      .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Range(“A2″).Select
      End Sub

  20. Quisiera saber como puedo crear una macro porcentual que se de color verde cuando es positivo y rojo cuando es negativo.

Deja un comentario

Required fields are marked *.


Excel Avanzado located at , Lima, Perú . Reviewed by usuarios rated: 4.7 / 5