martes, 28 de junio de 2016

VBA: Listar celdas con mismo formato.

Al hilo del post anterior (Búscar en Excel por formato) desarrollaremos hoy un proceso equivalente con programación...
La ventaja de emplear macros es que podemos recuperar las posiciones de las celdas para cualquier acción posterior.


Partimos de la misma hoja que el ejemplo anterior:

VBA: Listar celdas con mismo formato.



La clave de esta búsqueda es la propiedad .FindFormat, que nos habilita la búsqueda por formato de celda...

Insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Sub EncontrarFormato()
'limpiamos cualquier anterior búsqueda por formato
'y aplicamos el color de relleno a buscar (amarillo:= 65535)
With Application.FindFormat
    .Clear
    .Interior.Color = 65535
End With
'localizamos la primera coincidencia por color de relleno
Dim CeldaPrimeraCoincidencia As Range
'buscamos en el rango definido A1:H20 empezando por A1....
Set CeldaPrimeraCoincidencia = Range("A1:H20").Find(What:="", _
    After:=Range("A1"), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=True)     'la clave es poner TRUE en SearchFormat...

'si encontramos algo, seguimos la búsqueda...
Set rIgualFormato = Nothing
If Not CeldaPrimeraCoincidencia Is Nothing Then
    Dim CeldaActual As Range
    Set CeldaActual = CeldaPrimeraCoincidencia
    'entramos en el bucle de búsqueda
    Do
        'componemos un rango con las celdas de igual formato
        If rIgualFormato Is Nothing Then
            Set rIgualFormato = CeldaActual
        Else
            Set rIgualFormato = Union(rIgualFormato, CeldaActual)
        End If

        'empezamos una nueva búsqueda del formato
        'a partir de la celda anterior..
        Set CeldaActual = Range("A1:H20").Find(What:="", _
            After:=CeldaActual, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=True)
    Loop Until CeldaActual.Address = CeldaPrimeraCoincidencia.Address
End If

'sacamos un Mensaje con el listado de celdas...
MsgBox rIgualFormato.Address
'y las dejamos seleccionadas
rIgualFormato.Select

End Sub



Al emplear .FindFormat no podemos emplear la propiedad .Next habitual en búsquedas por contenido, motivo por el cual, debemos aplicar un segundo método .Find igual al primero.. que replicaría la acción de 'buscar siguiente'.

Al ejecutar nuestra macro obtenemos:

VBA: Listar celdas con mismo formato.

jueves, 23 de junio de 2016

Buscar en Excel por formato.

Veremos hoy una opción de búsqueda poco conocida: Búsqueda por formato.
La idea será localizar las celdas que coincidan con nuestro criterio, pero no por el valor contenido en ellas, sino por el formato aplicado...
Veamos un ejemplo. Tenemos la siguiente hoja con ciertas celdas formateadas de igual forma.. es indiferente que tengan o no contenido: texto, fechas, datos, fórmulas, etc..:

Buscar en Excel por formato.



Primer paso. Accedemos a la herramienta de búsqueda.. por ejemplo presionando Ctrl+b.
Esto nos abrirá la siguiente ventana diálogo:

Buscar en Excel por formato.



Segundo paso. Desplegamos desde el botón Formato y marcaremos: Elegir formato de celda.
Esto nos habilita el cursor con una especie de cuentagotas que recuperará el formato completo de la celda que seleccionemos:

Buscar en Excel por formato.



Tercer paso. Tras hacer clic retornamos a la ventana diálogo, donde veremos el formato elegido...

Buscar en Excel por formato.



Finalmente presionaremos el botón de 'Buscar todos' para visualizar el listado de todas las celdas con formato coincidente.

Buscar en Excel por formato.

martes, 21 de junio de 2016

El separador espacios como intersección de rangos para búsquedas cruzadas.

Cuando tenemos que recuperar datos de una tabla de valores es muy frecuente utilizar las funciones de búsqueda, como INDICE + COINCIDIR, o BUSCARV o DESREF...
Hoy veremos una curiosidad de la función SUMA que nos permitirá obtener el dato de una Tabla que corresponda a un referencia cruzada.
Tenemos la siguiente tabla:

La función SUMA para búsquedas cruzadas.



Lo que deseamos conseguir es el valor correspondiente a un Mes y un Color concreto.

Para facilitar la búsqueda hemos asignado Nombres definidos a los siguientes rangos de celdas:
ene. =Hoja1!$C$3:$F$3
feb. =Hoja1!$C$4:$F$4
mar. =Hoja1!$C$5:$F$5
abr. =Hoja1!$C$6:$F$6
may. =Hoja1!$C$7:$F$7
jun. =Hoja1!$C$8:$F$8

rojo =Hoja1!$C$3:$C$8
amarillo =Hoja1!$D$3:$D$8
verde =Hoja1!$E$3:$E$8
azul =Hoja1!$F$3:$F$8


Obviamente podríamos optar por la combinación de las funciones INDICE y COINCIDIR:
=INDICE(C3:F8;COINCIDIR("mar.";B3:B8;0);COINCIDIR("verde";C2:F2;0))


También por otra algo más sofisticada con la función SUMAPRODUCTO:
=SUMAPRODUCTO(C3:F8*(C2:F2="verde")*(B3:B8="mar."))


Pero hoy emplearemos la sencilla función SUMA.
Existe una manera de informar los argumentos casi desconocida donde no empleamos los separadores de argumentos (ni : ni ;) y que causa un efecto sorprendente.
Si escribiéramos en una celda cualquiera:
=SUMA(C5:F5 E3:E8)
ejecutando normalmente (no matricial) obtendríamos el valor correspondiente al cruce de ambos rangos!!.

Notemos la forma peculiar de añadir los rangos... sin separadores.
La explicación es que emplear este operador (espacio) trabaja como un Operador de intersección, esto es, devuelve una referencia a las celdas comunes de los dos rangos.


Empleando, y extendiendo la técnica a nuestro ejemplo:



La función empleada en la celda J3 de nuestro ejemplo:
=SUMA(INDIRECTO(H3) INDIRECTO(I3))
nos retorna el valor esperado...


Una última apreciación... funcionaría incluso sin la función suma, únicamente indicando los rangos:
=C5:F5 E3:E8

jueves, 16 de junio de 2016

Listado de Registros Únicos con MsQuery (SQL SELECT DISTINCT).

Utilizaremos hoy un viejo conocido de nuestras versiones de Excel: MSQuery, para emplear una sencilla sentencia SQL que nos permitirá recuperar un listado de nuestros registros únicos, no repetidos.
En concreto la sentencia SQL a emplear es: SELECT DISTINCT


Partiremos de un libro de trabajo llamado 'DISTINCT_MSQuey.xlsx' (es necesario que el fichero exista y esté guardado en alguna ubicación!).
Dentro del fichero tendremos una Tabla llamada 'TblHerramientas' (o cualquier otro nombre).. podríamos trabajar de igual forma sobre un rango de celdas.

Listado de Registros Únicos con MsQuery (SQL SELECT DISTINCT).


Fíjate que deberemos entender como registro repetido cuando simultáneamente se repitan los tres campos de la tabla a la vez!!!

Empezamos entonces el proceso accediendo a la herramienta MSQuery desde la ficha Datos > grupo Obtener Datos Externos > de Otras Fuentes > desde Microsoft Query:

Listado de Registros Únicos con MsQuery (SQL SELECT DISTINCT).


Se abrirá el Asistente de la aplicación y en el primer paso seleccionaremos como Origen de datos la opción: Excel files

Listado de Registros Únicos con MsQuery (SQL SELECT DISTINCT).


En el siguiente paso buscaremos la ubicación de nuestro fichero Excel ('DISTINCT_MSQuey.xlsx')

Listado de Registros Únicos con MsQuery (SQL SELECT DISTINCT).


martes, 14 de junio de 2016

VBA: Encontrar coincidencias de formato.

Hoy veremos un uso diferente del método .Find, enfocándolo a la búsqueda por formato.

La idea es por tanto localizar dentro de un rango determinado qué celdas responden a un formato dado; en nuestro ejemplo aquellas con un color de relleno amarillo.

VBA: Encontrar coincidencias de formato.



Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Sub EncontrarFormato()
'limpiamos cualquier anterior búsqueda por formato
'y aplicamos el color de relleno a buscar (amarillo)
With Application.FindFormat
    .Clear
    .Interior.Color = 65535
End With
'localizamos la primera coincidencia por color de relleno
Dim CeldaPrimeraCoincidencia As Range
'buscamos en el rango definido A1:H25 empezando por A1....
Set CeldaPrimeraCoincidencia = Range("A1:H20").Find(What:="", _
    After:=Range("A1"), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=True)     'la clave es poner TRUE en SearchFormat...
'si encontramos algo, seguimos la búsqueda...
If Not CeldaPrimeraCoincidencia Is Nothing Then
    Dim CeldaActual As Range
    Set CeldaActual = CeldaPrimeraCoincidencia
    'entramos en el bucle de búsqueda
    Do
        'sacamos un MsgBox que muestre la dirección de la celda
        MsgBox CeldaActual.Address
        'empezamos una nueva búsqueda del formato
        'a partir de la celda anterior..
        Set CeldaActual = Range("A1:H20").Find(What:="", _
            After:=CeldaActual, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=True)
    Loop Until CeldaActual.Address = CeldaPrimeraCoincidencia.Address

End If

End Sub



Por cada coincidencia del color de relleno aparecerá un MsgBox.

Las claves de este procedimiento residen en tres puntos.
El primero en la propiedad SearchFormat como TRUE, dentro del método .Find
El segundo en añadir una nueva búsqueda (.Find) dentro del bucle Doo...Loop para localizar las celdas siguientes...
El tercero es emplear .FindFormat para fijar la búsqueda por formato!!.

jueves, 9 de junio de 2016

La Opción Crear Siempre una Copia de Seguridad de nuestro libro Excel.

Hablaremos hoy de una opción poco conocida, pero que en algunos casos puede resultar útil.
Hablo de la opción Crear siempre una copia de seguridad.. algo distinto a la opción de Autoguardado.


La opción se configura desde la ventana de Guardar como, habilitando el desplegable de Herramientas (abajo a la derecha) y dentro de este, la opción de Generales

La Opción Crear Siempre una Copia de Seguridad de nuestro libro Excel.


Esto abrirá una nueva ventana donde podremos activar la opción comentada:

La Opción Crear Siempre una Copia de Seguridad de nuestro libro Excel.



El efecto es que siempre se creará, al guardar y cerrar el fichero tras algún cambio, una copia de seguridad con extensión .xlk, llamándose el nombre 'Copia de seguridad de'[nombre original del fichero].xlk:

La Opción Crear Siempre una Copia de Seguridad de nuestro libro Excel.



La ubicación donde se guarda por defecto el libro con la copia de seguridad es la misma del fichero original.
Como dato curioso, para versiones antiguas de Excel la extensión del fichero era la de .bak


Para abrir un fichero .xlk bastará abrirlo como cualquier otro fichero, haciendo caso omiso al mensaje de advertencia.

martes, 7 de junio de 2016

VBA: Contando Únicos dentro de una cadena de texto.

Veremos hoy una sencilla UDF (función personalizada) que nos devuelve un conteo de elementos únicos contenidos en una celda.
Se trata de dar solución a un usuario del blog:
[...] tengo los datos de1-2-3-4-5-6-2-3-5-4-7-, cuantas números hay sin considerar repeticiones? [...]


Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Function ContarUnicos(celda As Range, separador As String) As Long
Application.Volatile
'definimos la matriz de trabajo, empleando el 'separador' dado por el usuario
matriz = Split(celda.Text, separador)

'generamos la coleccion
Set unicos = New Collection
'loop por todos los elementos de la matriz
For Each dato In matriz
    'cuando encuentre un item repetido, daría un error
    'que salvamos con la instrucción On Error Resume Next
    On Error Resume Next
    'por tanto, nuestra coleccion solo agrega elementos no repetidos
    'objeto.Add item, key, before, after
    'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
    unicos.Add dato, CStr(dato)
    On Error GoTo 0
Next dato

'escribir los datos unicos en la Hoja de cálculo
ContarUnicos = unicos.Count

End Function



El uso es simple, basta indicar la celda que contiene los elementos y cuál es el separador empleado.
Vemos el resultado:

VBA: Contando Únicos dentro de una cadena de texto.

miércoles, 1 de junio de 2016

Excelforo: Siete años de Excel.

Si, siete años ya...el tiempo vuela...
Siete años prestando la mejor formación presencial y elearning(online). Si olvidar todos aquellos clientes a los que las horas de consultoría han ahorrado tiempo y dinero.


Como en ocasiones anteriores, en este séptimo 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 5.200.000 páginas vistas, y un 3.100.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 765 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; más de 8.300 comentarios, y muchísimas horas dedicadas.

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

Además, me he lanzado con una nueva aventura, gestionando un nuevo 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.
ÚNETE.. no lo dudes!

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 Junio de 2016.
Nunca estudiar fue tan fácil.


Los cursos de Excel y Macros abiertos para este mes de Junio 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 Junio de 2016; 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.