miércoles, 19 de julio de 2017

VBA: Filtrar Números que contengan ciertos dígitos

Recientemente me planteaba un lector la posibilidad de aplicar, desde nuestra programación en VBA,
un autofiltro sobre valores numéricos, con la condición de filtro que contenga ciertos dígitos.

En definitiva se trata de replicar el comportamiento del cuadro de búsqueda incorporado en el Autofiltro. Como se puede fácilmente ver en la imagen siguiente:

VBA: Filtrar Números que contengan ciertos dígitos



El intento del lector fue emplear el comodín * (asterisco) para componer un filtro de contine...
Criteria1:="=*" & CStr(Range("B1").Value) & "*"
pero que NO FUNCIONA!!!.
Ya que al ejecutar lo grabado no cruza los datos como texto en ambos casos:

Sub Macro1()
ActiveSheet.Range("$B$3:$C$12").AutoFilter Field:=1, _
        Criteria1:="=*" & CStr(Range("B1").Value) & "*", _
        Operator:=xlAnd     'xlFilterValues
End Sub



La clave la obtenemos al grabar el proceso con el asistente de grabación de macros, donde obtenemos el siguiente código:

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveSheet.Range("$B$3:$C$12").AutoFilter _
        Field:=1, _
        Criteria1:=Array("12,13", "13", "133", "98813"), _
        Operator:=xlFilterValues
End Sub



Vemos como la macro grabada ha añadido como criterio una matriz de aquellos valores que contenían el 'número' buscado (13 en nuestro ejemplo).

Bien, pues esa será la clave, debemos generar nuestra propia Matriz-Array en nuestra programación, pero que sea dinámica en cuanto al dígito buscado... apoyándonos en el valor de la celda B1.


Abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub FiltrarNumeroComoTexto()
Dim arr() As String
Dim celda As Range

x = 0
'recorremos el rango de celdas del campo
'para contar cuántas coincidencias existen
For Each celda In Range("B4:B12")
    'evaluamos si el valor de la celda
    'contiene los dígitos buscados
    If InStr(1, celda.Value, Range("B1").Value, vbTextCompare) > 0 Then
        'aumentamos el contador
        x = x + 1
    End If
Next celda

'redimensionamos la Matriz que contendrá los valores del rango
'que contienen los dígitos buscados
ReDim arr(1 To x) As String
i = 0
'pasamos por todas las celdas...
For Each celda In Range("B4:B12")
    'si la celda contiene los dígitos
    If InStr(1, celda.Value, CStr(Range("B1").Value), vbTextCompare) > 0 Then
        'cargamos la matriz con el valor
        i = i + 1
        arr(i) = CStr(celda.Value)
    End If
Next celda

'Finalmenete aplicamos el autofiltro estándar, con los componentes de la matriz
ActiveSheet.Range("$B$3:$C$12").AutoFilter _
        Field:=1, _
        Criteria1:=(arr), _
        Operator:=xlFilterValues

End Sub



Al ejecutar comprobamos como el Autofiltro se aplica, como era de esperar, a los valores que contengan el dato de la celda B1.

El procedimiento anterior emplea, así lo requeríamos, el Autofiltro... pero otra forma algo más sencilla es emplear la acción de Ocultar o Mostrar filas (OJO!!, no tiene los mismos efectos que aplicar un autofiltro!!).
Esta sería otra posibilidad:

Sub VisualizarNumeroComoTexto()

Dim celda As Range
For Each celda In Range("B4:B12")
    If InStr(1, celda.Value, CStr(Range("B1").Value), vbTextCompare) > 0 Then
        celda.EntireRow.Hidden = False
    Else
        celda.EntireRow.Hidden = True
    End If
Next celda

End Sub

miércoles, 12 de julio de 2017

Gráfico de Ranking en Excel

Fechas atrás me preguntaba un usuario por un tipo de gráfico de moda como es el gráfico de ranking.
En particular pedía reconstruir de la manera más próxima posible el siguiente gráfico:

Gráfico de Ranking en Excel



Nuestra replica de este gráfico de ranking con Excel tendrás este aspecto:

Gráfico de Ranking en Excel



Nuestro trabajo empieza tratando los datos.
Partimos de la siguiente tabla en el rango I3:M7 donde disponemos de unos importes de ventas por conceptos.
A partir de esos importes generamos en el rango C3:G7 una tabla auxiliar (que será la que representaremos gráficamente) a partir de la función JERARQUIZ.EQUIV.
En la celda C3 insertamos:
=JERARQUIA.EQV(I3;I$3:I$7)
y luego arrastramos al resto del rango C3:G7.
Con esta fórmula obtenemos la ordenación/jerarquía por cada año de ventas para los diferentes conceptos.

Gráfico de Ranking en Excel



Como último paso previo a la generación del gráfico, construimos los textos de las etiquetas de datos para los últimos puntos de cada serie.
Para esto en el rango B3:B7 insertamos la fórmula:
=G3&REPETIR(" ";10)&TEXTO(M3/SUMA($M$3:$M$7);"0%")&REPETIR(" ";5)&A3

Que nos permite concatenar en una celda la posición del último año, junto al peso de ese concepto sobre el total del año y la descripción de ese concepto.

Gráfico de Ranking en Excel



Paso 1: Insertamos el gráfico de línea con marcadores
Tras seleccionar el rango B2:G7, y esde la ficha Insertar > grupo Gáficos > desplegable Líneas o Áreas > tipo Líneas con marcadores

Gráfico de Ranking en Excel



Paso 2: Cambiamos el nombre del gráfico por 'GraficoRanking'
Importante para un paso posterior.


Paso 3: Cambiamos el estilo de diseño del gráfico por el de 'Estilo 2'
Con el gráfico seleccionado, y desde las herramientas de gráfico > pestaña Diseño > grupo Estilos de diseño > Estilo 2

Gráfico de Ranking en Excel



Paso 4: Eliminamos Título del gráfico, Líneas de división principales y Leyenda (opcional).

Paso 5: Añadimos al gráfico un Eje Vertical Primario.
A continuación, dicho eje, lo configuramos marcando la opción de eje: Valores en orden inverso

Gráfico de Ranking en Excel



Paso 6: Eliminamos el Eje vertical primario insertado en el paso anterior.
Este es el aspecto ahora mismo de nuestro gráfico, bastante similar ya a lo buscado.

Gráfico de Ranking en Excel



Paso 7 (y último): Ejecutamos la macro siguiente.
Abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Ultima_Etiqueta()
Dim serie As Series
Dim punto As Long
Dim MiGrafico As Chart
'controlamos el gráfico sobre el que trabajar
Set MiGrafico = ActiveSheet.ChartObjects("GraficoRanking").Chart
        
For Each serie In MiGrafico.SeriesCollection
    With serie
        'identificamos el último punto de las series
        punto = .Points.Count
        'aplicamos características a ese último punto de la serie
        serie.Points(punto).ApplyDataLabels _
            ShowSeriesName:=True, _
            ShowCategoryName:=False, _
            ShowValue:=False, _
            AutoText:=True, _
            LegendKey:=False
        'damos formato en cuanto a posición y color
        With .Points(punto).DataLabel
            .Position = xlLabelPositionRight
            .Font.Color = vbBlack
        End With
    End With
Next serie

End Sub



Tras ejecutarla comprobaremos como la etiqueta de datos del último punto toma los textos deseados...
Solo haría falta ajustar el área del gráfico para que la etiqueta tome la posición deseada.

Este paso se podría realizar manualmente, evitando el uso de la programación.

En cualquier caso el resultado obtenido es el deseado:

Gráfico de Ranking en Excel

lunes, 3 de julio de 2017

Excelforo: VIII aniversario y IV premio Microsoft MVP Excel.

Octavo año... y por cuarto año consecutivo (2014, 2015, 2016 y 2017) he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil)

Quiero comenzar agradeciéndoos a todos vosotros por el apoyo mostrado durante todo este tiempo, de verdad que sin vosotros nada sería posible.
Agradecer, también, a Microsoft por este nuevo honor... tomo el reconocimiento como acicate para continuar difundiendo todo el conocimiento posible de nuestra hoja de cálculo favorita: Excel.

Excelforo: VIII aniversario y IV premio Microssoft MVP Excel.



Por otra parte, son ocho años ya prestando la mejor formación presencial y elearning(online).
Sin olvidar todos aquellos clientes a los que las horas de consultoría han ahorrado tiempo y dinero.
Visita mi web:
www.excelforo.com


Como en años pasados, en este octavo aniversario, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más 3.900.000 visitas únicas, con más de 7.600.000 páginas vistas, y un 3.800.000 usuarios de todo el mundo (España, México, Colombia, Perú, Chile, Argentina, Ecuador, Estados Unidos.. y un largo etcétera)...
Son ya más de 870 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; y casi 10.000 comentarios, y desde luego muchísimas horas dedicadas.

No quiero olvidar aventuras comenzadas, como el grupo de Facebook de Excel: Microsoft Excel en Español...
https://www.facebook.com/groups/ExcelEspanol/
donde lo especial es que trato de controlar al máximo los comentarios que no aporten nada (como publicidad de cursos, web, grupos, etc...), reduciendo el grupo a contenido de valor añadido.
Contento de alcanzar ya más de 4.000 seguidores!!
ÚNETE.. no lo dudes!

Y una nueva comunidad de Google Microsof Excel visto por un MVP, con el mismo objetivo con el que nació el grupo de FB... alcanzar al máximo de personas interesadas.

Pero sin duda la mayor satisfacción es y ha sido poder contestar personalmente todas las consultas presentadas, bien a través del correo bien a través de los comentarios del blog...

Mi eterno agradecimiento a todos vosotros.


Por todo ello no puedo dejar de seguir ofreciendo estos cursos en modalidad elearning, para permitir el acceso a ellos a cualquier persona desde cualquier parte del mundo...
No lo dudes haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Julio de 2017.
Nunca estudiar fue tan fácil.


Los cursos de Excel y Macros abiertos para este mes de Julio son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)



Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)


Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)


Curso Excel Financiero

(ver más)



Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Julio de 2017; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).


Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.