Archive

Posts Tagged ‘Funciones’

Crear categoría y descripción para funciones personalizadas UDF en Excel

November 24, 2012 8 comments

En un artículo del 2010 explicaba cómo Crear funciones personalizadas en Excel y tener nuestras propias funciones escritas por nosotros mismos.

En esta ocasión comparto un código en vba que nos permitirá crear una categoría personalizada y agrupar nuestras UDF’s (User Define Function), así como darles una descripción a los argumentos que tendrán y a la misma función.

Tendremos 3 partes principales para tener la categoría y las descripciones de la función:

  1. Mandar llamar la función desde el evento Open del archivo.
  2. El código que asignará los elementos de la función.
  3. El código de la función.

Código

En el evento Open del objeto ThisWorkbook asignamos el código siguiente.

Private Sub Workbook_Open()
    Call DescribeFunctionEXCELeINFOCOLORCELDA
End Sub

En un módulo normal.

'El código que describe la categoría de la función y sus argumentos
Private Sub DescribeFunctionEXCELeINFOCOLORCELDA()
    Dim NombreFunc As String        'nombre de la función
    Dim DescFunc As String          'descrición de la función
    Dim Categoria As String         'categoría de la función
    Dim DescArg(1 To 3) As String   'descripción de los argumentos
    '
    NombreFunc = "EXCELeINFOCOLORCELDA"
    DescFunc = "Devuelve el índice de color de la celda seleccioada"
    Categoria = "EXCELeINFO"    'Text category
    DescArg(1) = "Es la celda de donde se obtendrá el índice de color"
    '
    Application.MacroOptions _
            Macro:=NombreFunc, _
            Description:=DescFunc, _
            Category:=Categoria, _
            ArgumentDescriptions:=DescArg
End Sub
'
'Función personalizada UDF
Function EXCELeINFOCOLORCELDA(celda As Range)
'
' EXCELeINFOCOLORCELDA Función
' Devuelve el color de la celda de la celda seleccionada
'
    EXCELeINFOCOLORCELDA = celda.Interior.ColorIndex
    '
End Function

Imágenes

image

image

Uso de ALEATORIO para crear tablas de prueba en Excel

September 8, 2012 1 comment

Para este video se utilizaron las funciones de ENTERO y ALEATORIO, esto para que nos arrojará números al azar y formar bases de datos de ejemplo y hacer pruebas.

La función ALEATORIO no arroja números al azar menores a 1, por lo que nos dará números tales como 0.1154, y es por eso que usamos ENTERO para que nos de la unidad, además de multiplicarlo por un número entero para que nos de números de 0 al número especificado.

La primera manera es:

=ALEATORIO(), nos arroja números > 0 y <1

La segunda opción es:

=ENTERO(ALEATORIO()*10), nos arroja números > 0 y <10

La tercera opción es:

=ENTERO(ALEATORIO()*10)+1, nos arrojas número > 1 y <10

Video

Video tutorial fórmulas largas en Excel

August 19, 2012 1 comment

En este video tutorial expongo un ejemplo de fórmula larga, en donde tenemos una lista de correos electrónicos y deseamos extraer el dominio de todas ellas.

Se utilizan las funciones de ENCONTRAR, LARGO y DERECHA para encontrar el dominio y dejarlo en una celda.

Pasos

  1. Usamos la función ENCONTRAR para saber en qué posición está la @: =ENCONTRAR("@",A1)
  2. Usamos la función de LARGO para saber la longitud de nuestro correo electrónico:=LARGO(A1).
  3. Hacemos una resta de el largo del correo menos la posición donde está el @: =LARGO(A1)-ENCONTRAR("@",A1).
  4. Usamos la función DERECHA para extraer x número de caracteres de derecha a izquierda: =DERECHA(A1,LARGO(A1)-ENCONTRAR("@",A1)).

El principio para crear fómulas largas es el mismo: usar celdas temporales para armarla.

Video

Video tutorial perteneciente al blog EXCELeINFO. Si deseas más información sobre Excel y vba, visita mi Blog https://exceleinfo.wordpress.com

Sumar datos en Excel con diferentes condiciones con SUMAR.SI.CONJUNTO

March 22, 2012 2 comments

En la tabla de la imagen se muestra una tabla con los siguientes encabezados:

  • VENTAS
  • REGIÓN
  • VENDEDOR
    Si lo que quisiéramos es obtener las ventas del VENDEDOR 1, podríamos utilizar la función SUMAR.SI, cuyos parámetros serían:

  • Rango a evaluar.
  • Criterio que se tomará en cuenta para sacar los datos.
  • Rango que se sumará.

El resultado que obtendríamos será 3472 ventas realizadas por el VENDEDOR 1.

Pero qué sucede si necesitamos obtener el dato de las ventas del VENDEDOR 1, pero realizadas en la REGIÓN 4. La función que nos ayudaría para tal propósito es SUMAR.SI.CONJUNTO, que es una versión avanzada de SUMAPRODUCTO. Los criterios para la función son:

  • El rango que se sumará.
  • El rango del 1er criterio.
  • El dato del 1er criterio.
  • El rango del 2do criterio.
  • El dato del 2do criterio.
  • .. se pueden añadir hasta 127 criterios.

El resultado que que obtenemos es 1329 ventas realizadas por el VENDEDOR 1, en  la REGIÓN 4.

Fórmula

=SUMAR.SI.CONJUNTO(C2:C28,A2:A28,"REGION 4",B2:B28,"VENDEDOR 1")

Imagen de la tabla

image

Vuelve BUSCARV a Excel 2010 con SP1

November 29, 2011 Leave a comment

Para todos aquellos somos asiduos a la función BUSCARV, al instalar Office 2010 nos percatamos que cambió el nombre de la función a CONSULTAV. Sin embargo, si ya instalaste el Service Pack 1 de dicha versión, te habrás dado cuenta que volvió el nombre que tanta gente estaba acostumbrada a utilizar.

Dejo los links donde tendrás más información al respecto:

Descripción de Office 2010 SP1

Nombres de funciones cambiados en versiones diferentes del inglés de Excel 2010 SP1

Función personalizada Concatenar en Excel

February 25, 2011 4 comments

Twittear este post Compartir en Facebook

La función Concatenar de Excel se me hace realmente lenta al momento de tener que unir varias celdas, por lo que me vi a la tarea de realizar una función personalizada en la que sólo elijo el rango que quiero unir y listo!.

He aquí el código:

Function EXCELeINFOCONCATENAR(rango As Range) As String
Dim t As String
Application.Volatile
For Each celda In rango
    t = t & " " & celda.Value
Next celda
EXCELeINFOCONCATENAR = Trim(t)
End Function

Uso de K.ESIMO.MAYOR, COINCIDIR, INDICE en una misma fórmula de Excel

October 22, 2010 7 comments

Compartir:

Twittear este post Compartir en Facebook

En varias ocasiones nos topamos con fórmulas grandes con funciones anidadas y que a simple vista son de difícil lectura. El siguiente ejemplo nos deja más claro cómo realizar este tipo de fórmulas paso a paso. El ejemplo nos muestra cómo obtener de una lista de nombres con sus respectivas ventas, quién es el 1ro, 2do y 3er lugar dependiendo de las ventas.

Primeramente tenemos nuestra base de datos con sólo dos columnas: la columna Nombre y la columna Ventas.

1.- El primer paso es obtener cuál es el número mayor de ventas. Esto lo obtenemos con la función K.ESIMO.MAYOR. Cabe destacar que esta función nos da el número k.esimo de una lista, de mayor a menor:

[B10]=K.ESIMO.MAYOR(B2:B8,1)

2.- Ahora que obtenemos el valor del número k, que en esta ocasión fue el número mayor, el 1, nos disponemos a obtener la posición de dicho número en la fila, con la función COINCIDIR:

[B11]=COINCIDIR(B10,B2:B8,0)

3.- Cuando sabemos la posición en la que está el número mayor, el 1ro, nos disponemos a obtener el nombre de la persona que tuvo ese número de ventas, con la función INDICE. Dicha función nos da el valor correspondiente de acuerdo a la intersección de fila columa que definimos en los parámetros.

[B12]=INDICE(A2:B8,B11,1)

4.- Hasta este momento ya obtuvimos el valor de la persona que tuvo más ventas, pero utilizando 3 funciones, una en cada celda. Ahora necesitamos unirlas todas. Si nos damos cuenta, en la celda [B11] hacemos referencia a la celda [B10], entonces la fórmula que está en [B10] la copiamos y la pegamos íntegramente donde dice ‘B10’ en la fórmula de [B11], y así hacemos lo mismo en la celda [B12]. Dándonos como resultado la siguiente fórmula:

=INDICE(A2:B8,COINCIDIR(K.ESIMO.MAYOR(B2:B8,1),B2:B8,0),1)

Resumen:

Con K.ESIMO.MAYOR obtuvimos el número mayor de ventas que es 120, después con COINCIDIR sacamos la posición de ese valor en la tabla, que fue la posición 3. La intersección que nos dan las dos funciones pasadas son fila 3 y la columna donde está el valor que queremos es la columna 1, por lo tanto, el nombre que nos arroja es Toño.

Imagen del ejemplo:

image

:: Descarga el ejemplo