VBA: Contar o Sumar valores según el color de Fondo o Fuente de una celda.

Hoy contaré un clásico de las cuestiones planteadas: Contar o Sumar los valores de una celda según el color de fondo o de la fuente.

Estimado tengo una duda. Si tuviera un rango por ejemplo de "$A$1:$A$10", en donde:  A1(color negro), A2(color azul), A3, (color amarillo), A4 (color blanco), A5 (color blanco), A6 (color amarillo), A7 (color blanco), A8 (color blanco), A9 (color azul), A10 (color amarillo). Como haría para sumar las celdas del mismo color mediante una macro, es decir: La A1 = SUMA(A2,A9) , A3 = SUMA(A4,A5) , A6 = SUMA(A7,A8) , A9 = SUMA(A10).

Hay muchos ejemplos al respecto en casi todos los foros especializados, pero en el caso de hoy, le daremos un poco más de valor añadido, incorporando variaciones para controlar el color de la fuente o del Fondo de la celda, así como el tipo de operación a realizar (Sumar o Contar).

Insertamos nuestro procedimiento Function en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Function FxColor(rngRange As Range, CeldaComparacion As Range, Operacion As String, Optional Tipo As String) As Double
Dim color As Long, Resultado As Double, CeldaColor As Double
'rngRange: será el rango de estudio
'CeldaComparacion: la celda donde compararemos el color
'Operación: puede ser SUMA o CONTAR
'Tipo: Fuente o Fondo

For Each celda In rngRange
    'Definimos la casuística a comparar según el argumento Tipo
    If UCase(Tipo) = "FONDO" Then
        'si elegimos en Tipo Fondo, tomamos para comparar el color del Fondo
        color = CeldaComparacion.Interior.ColorIndex
        CeldaColor = celda.Interior.ColorIndex
    ElseIf UCase(Tipo) = "FUENTE" Then
        'si elegimos en Tipo Fuente, tomamos para comparar el color de la Fuente
        color = CeldaComparacion.Font.ColorIndex
        CeldaColor = celda.Font.ColorIndex
    Else
        'en cualquier otro caso tomamos para comparar el color del Fondo
        color = CeldaComparacion.Interior.ColorIndex
        CeldaColor = celda.Interior.ColorIndex
    End If
    
    'Evaluamos si el Argumento Tipo es SUMA
    If UCase(Operacion) = "SUMA" Then
        If CeldaColor = color Then
            'acumulamos valores sumados cuando coincida con color
            Resultado = WorksheetFunction.SUM(celda.Value, Resultado)
        End If
    'Evaluamos si el Argumento Tipo es CONTAR
    ElseIf UCase(Operacion) = "CONTAR" Then
        If CeldaColor = color Then
            Resultado = 1 + Resultado
        End If
    'Evaluamos si el Argumento Operacion es cualquier otra cosa distinto de SUMA o CONTAR
    'devolvemos valor como SUMA... lo dejamos abierto y preparado para otras opciones ;-)
    Else
        If CeldaComparada = color Then
            'acumulamos valores sumados cuando coincida con color
            Resultado = WorksheetFunction.SUM(celda.Value, Resultado)
        End If
    End If
Next celda
'devolvemos el valor a la función
FxColor = Resultado
End Function

Lo que hemos construido es una UDF-función personalizada con VBA que consta de cuatro argumentos:
1- rngRange: será el rango de estudio
2- CeldaComparacion: la celda donde compararemos el color
3- Operación: puede ser SUMA o CONTAR
4- Tipo: Fuente o Fondo
El cruce del tercer y cuarto argumento nos dará un flexibilidad adicional...

En la imagen vemos el resultado de aplicar esta función sobre diferentes rangos...

haz clic en la imagen

Seguir leyendo...

Power Query: Una función personalizada con dos argumentos.

Continuando la entrada anterior, sobre el mismo ejemplo, realizaremos una modificación a nuestra consulta-función de Power Query, para indicar un segundo argumento de la función personalizada, de tal forma que podamos informar de cuál es el separador...

En realidad habría que comenzar desde cero, ya que se trata de una nueva función.. pero siempre podemos copiar y pegar desde el Editor avanzado.
Por tanto, en la ventana del Editor de consultas, iremos al menú Inicio > grupo Consultas > botón Editor avanzado y escribiremos lo siguiente:

(Origen, Separador)=> let
    #"Líneas del texto" = Lines.FromText(Origen),
    #"Tabla de lista" = Table.FromList(#"Líneas del texto", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Dividir columna por delimitador" = Table.SplitColumn(#"Tabla de lista","Column1",Splitter.SplitTextByEachDelimiter({(Separador)}, null, false),{"Column1.1", "Column1.2"}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Tipo cambiado"

En este caso nos fijaremos en la primera línea del código, donde hemos insertado entre paréntesis las dos variables:
Origen: para indicar la columna a tratar
Separador: para indicar qué separador queremos tener en cuenta.

Además en la fila del código
#"Dividir columna por delimitador" = Table.SplitColumn(#"Tabla de Lista","Column1",Splitter.SplitTextByEachDelimiter({(Separador)}, null, false),{"Column1.1", "Column1.2"}),

al emplear la función Splitter.SplitTextByEachDelimiter hemos añadido nuestro parámetro 'Separador'.

El resultado es el esperado para cualquier tipo de Separador, como vemos en la imagen siguiente, donde en la Tabla original el separador es el asterisco (informado así con nuestra función al realizar la carga de la Tabla):

Seguir leyendo...

Power Query: Construir una función personalizada.

Hoy, en mi serie de artículos sobre esta gran herramienta (Power Query), aprenderemos a crear una función personalizada dentro de Power Query que nos permitirá emplearla en el resto de Consultas de nuestro Libro de trabajo.

Partimos de varias tablas en nuestra hoja de cálculo, deseando que en todas ellas podamos convertir la columna que indiquemos en dos columnas nuevas, separando los textos por el primer separador (en el ejemplo un guión medio '-').

Esa será nuestra función, aquella que separe el texto de la columna que indiquemos en dos nuevas columnas, dividiéndola por el primer separador que encuentre.

Nuestras tablas son:

Comenzaremos por crear nuestra función personalizada en PowerQuery.
Puesto que será algo muy genérico comenzaremos desde la nada, por ejemplo, iniciando Power Query desde Otros orígenes:
Ficha Power Query > Obtener Datos Externos > De Otros Orígenes > Consulta en Blanco

Esto nos abrirá el editor de consultas de Power Query, y en la barra de fórmulas escribiremos un valor cualquiera que asemeje los diferentes códigos a tratar.. en mi caso, escribiré:
BBB-000-000
ya que los diferentes elementos a tratar en nuestras tablas responden más o menos a esa estructura: 2 ó 3 grupos de caracteres separados por guiones medios.

Tras escribir ese código de ejemplo haremos clic en el cuerpo de la ventana, lo que nos lleva el valor escrito a la ventana de trabajo.
Si ahora hacemos clic derecho sobre ese elemento, aparecerá una opción:
A lista
que convierte, precisamente, el valor en el inicio de una lista:

Al convertirlo en Lista se nos ofrece una posibilidad de reconvertirlo en Tabla... mucho más cómodo para nuestro objetivo.

Aplicaremos ahora los pasos buscados, esto es, separar el dato en dos columnas por el primer separador que encuentre...
Desde el menú Inicio > grupo Transformar > botón Dividir columna > Por delimitador, configuraremos la acción:

El siguiente paso es el importante, puesto que es ahora cuando convertimos nuestra consulta en una función.
Para ello, en la ventana del Editor de consultas, iremos al menú Inicio > grupo Consultas > botón Editor avanzado y escribiremos lo siguiente:

(Origen)=> let
    //Origen = "BBB-000-000",
    #"Líneas del texto" = Lines.FromText(Origen),
    #"Tabla de lista" = Table.FromList(#"Líneas del texto", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Dividir columna por delimitador" = Table.SplitColumn(#"Tabla de lista","Column1",Splitter.SplitTextByEachDelimiter({"-"}, null, false),{"Column1.1", "Column1.2"}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Tipo cambiado"

Fundamental, escribir el texto
(Origen)=>
antes de 'let'; e igualmente desactivar de donde tomamos el dato, es decir, insertar dos barras // antes de Origen = "BBB-000-000", en la segunda línea...

Finalmente, una vez creada nuestra función, la guardamos con un nombre, por ejemplo fxSeparador.
Para ello basta guardar la consulta con ese nombre:

Ahora Cerramos y cargamos la consulta-función...
La podemos ver en el panel de consultas del Libro:

Seguir leyendo...

Diseñar y ordenar nuestras tablas dinámicas de una manera diferente.

Fue por casualidad, cambiando el nombre de algunos campos y elementos de mi tabla dinámica, que me percaté de algo que desconocía... (esto es lo que me gusta de Excel.. cada día aprendo alguna cosa nueva!).

La cuestión es que es posible realizar 'ordenaciones' manuales de elementos en un campo sobre-escribiendo otros elementos; eso sí, debemos escribir nombres de elementos existentes.
En esos casos los elementos del campo se re-ordenan para situar el elemento último escrito en la posición del 'sobre-escrito'.

De igual forma probé sobre los campos y de igual forma se incorporaban o movían de situación (entre o dentro de las diferentes áreas de las tablas dinámicas).

Partiremos de esta tabla dinámica ya construida a partir del origen de su izquierda:

Observemos en el vídeo el comportamiento:

Seguir leyendo...

Contando registros en columnas alternas según columna impar.

Hace pocos días una lectora planteaba una cuestión sobre cómo calcular el número de registros en un rango que verifican dos condiciones diferentes.

...Estoy utilizando esta formula: =CONTAR.SI(K2:AA2,">0") pero el tema es que solo necesito que lo haga en las columnas intermedias K2, M2, O2, Q2 etc...

La primera idea podría llevarnos a emplear la función CONTAR.SI.CONJUNTO que inicialmente nos haría pensar reúne los requisitos, contar sobre un mismo rango, aquellos valores que verifican dos criterios... el problema en el caso que nos plantea la lectora es que una de las condiciones afecta a su posición en columnas (y no al valor).

Una alternativa será emplear una fórmula matricial, que en su estructura ya se ha empleado en algunas ocasiones en ejemplos expuestos.
Se trata de conseguir dos rangos 'virtuales' de ceros y unos, uno que evalúe si el valor es mayor a 10, y el otro si la columna de cada celdas es impar.

La función matricial buscada la vemos en la celda C7:
=SUMA(SI(B3:S3>10;SI(ES.IMPAR(COLUMNA(B3:S3));1)))
notemos que empleamos un doble SI matricial anidado para evaluar las dos condiciones necesarias.
Además empleamos la función ES.IMPAR sobre la función COLUMNA par conocer si la columna es IMPAR.

El resultado detallado de uno y otro rango 'virtual' lo podemos comprobar en las filas 1 y 2...

Seguir leyendo...

Transponiendo datos con TRANSPONER Y DESREF

Un caso muy habitual es que al descargarnos información de sitios webs, los datos aparecen listados en lugar de en formato tablas.
Un lector preguntaba por la forma de conseguirlo en su caso concreto:

...He leído en el foro como usar TRANSPONER usando DESREF e INDIRECTO, mas no he logrado aplicarlo. Mi ejemplo (adjunto) se explica así: Al descargar información de una pagína web a excel (en cualquier versión) la descarga se ejecuta en una sola columna en rangos del mismo tamaño (10) separados por una celda (rango total del ejemplo A3:A45)....

Veamos los datos...

Observamos como la situación de los datos responde, tal y como indicaba el lector, a una regla fija.. valores tomados de 10 en 10, separados por una fila sin datos...
Nótese que cada grupo de valores a transponer se encuentra a partir de la fila 3 y siguientes sumadas + 11, esto es 3, 14, 25, 36, 47, etc.

Montamos el rango a trabajar en las celdas M3:V3 e insertamos la siguiente matricial:
=TRANSPONER(DESREF(INDIRECTO("A"&L3);0;0;10;1))

OJO, por que me he apoyado en los número de filas 3, 14, 25, 36, 47, etc. que había determinado como comienzo de los tramos a trasponer.. consiguiendo la celda Ancla con la función INDIRECTO:
INDIRECTO("A"&L3)
que nos devuelve, precisamente, cada celda inicial de cada grupo de valores.

Al ser grupos de 10 valores colocados en una misma columna, la función DESREF se monta de manera directa:
DESREF(celda_inicio;0;0;10;1)
fórmula que nos devuelve el conjunto de datos de cada tramo.

Finalizamos nuestra fórmula con TRANSPONER que convierte en filas lo que antes era columna...

Acabaríamos copiando y pegando el rango generado para el resto de filas...

Seguir leyendo...

Contar registros únicos dentro de un intervalo dado.

Al hilo de una entrada anterior (ver)un lector me planteaba la forma de realizar un conteo de registros únicos pero dentro de un intervalo de valores dados.
Partiremos de este listado mezclado de valores repetidos (marcados en color rojo) y únicos:

haz clic en la imagen

Se trata por tanto de determinar para cada intervalo del rango D5:E7 qué numero de registros únicos (sin contar sus repeticiones) existe.
Observemos como con la fórmula descrita en el link del inicio del post, en la celda F4 calculamos el número total de valores únicos en nuestro rango (TblaValores4), con la fórmula matricial:
=SUMA(SI(FRECUENCIA(Tbl_Valores4[Valores:];Tbl_Valores4[Valores:])>0;1))

Para conseguir nuestro objetivo, y conocer el dato de únicos por intervalo, sustituiremos el rango completo por una nueva matricial que únicamente nos devuelva los valores dentro del intervalo:
SI(Tbl_Valores4[Valores:]>=$D5;SI(Tbl_Valores4[Valores:]<=$E5;Tbl_Valores4[Valores:];"")) esto es, obtenemos un rango auxiliar sobre el que trabajar, pero sólo con los valores dentro de cada intervalo...

Insertamos entonces en al celda F5 (y luego arrastraremos al F6 y F7) la siguiente fórmula matricial:

=SUMA(SI(FRECUENCIA(SI(Tbl_Valores4[Valores:]>=$D5;SI(Tbl_Valores4[Valores:]<=$E5;Tbl_Valores4[Valores:];""));SI(Tbl_Valores4[Valores:]>=$D5;SI(Tbl_Valores4[Valores:]<=$E5;Tbl_Valores4[Valores:];"")))>0;1;0))

Recordemos ejecutar nuestras funciones presionando Ctrl+Mayusc+Enter en lugar de solo Enter).

Listo, el resultado sería:

haz clic en la imagen

Un par de formas sencillas para comprobar esta realidad sería Seleccionar nuestro origen y Eliminar Duplicados o bien construir una Tabla dinámica llevando al área de filas el campo de valores...
de ambas maneras veríamos muy facilmente que el conteo obtenido es correcto.

Seguir leyendo...

Gráfico de columna apilada a doble columna.

Al hilo de un post anteriormente publicado (ver), un lector planteaba si habría forma de mostrar el gráfico, similar al del post, pero con la apariencia de dos columnas aplicadas con diferentes puntos, esto es, llegar a este gráfico:

Lo más importante es tratar previamente el origen de datos, convirtiéndolo en:

Observemos la distribución de datos en filas diferentes, apareciendo los datos que deseamos mostrar en la misma columna apilada en la misma fila.
De igual forma notemos como lo que serán las etiquetas del eje horizontal los hemos separado una columna.. y fundamental el truco siguiente: en la columna 'vacía' (en ele ejemplo rango B8:B15) insertar en cada celda un espacio en blanco!!!.

Con esta configuración ya podemos generar nuestro gráfico de columnas apiladas seleccionando el rango A7:F15

Seguir leyendo...