viernes, abril 18, 2008

Formato condicional por bloques – segunda nota

Uno de lo usos de formato condicional es facilitar la lectura de tablas, señalando con un fondo de color determinadas filas o columnas.
En la primer nota sobre cómo facilitar la lectura de tablas con formato condicional vimos algunas técnicas para señalar filas en forma alternada (por ejemplo, poner un fondo gris a todas las filas impares, o por intervalos determinados).
En la primer nota sobre formato condicional por bloques, vimos como señalar la primer fila de un bloque de datos




En realidad lo que estaba buscando cuando empecé a escribir la nota era como poner un fondo en forma alternada a todo el bloque, es decir, esto



Partiendo de la premisa que nuestra tabla está siempre ordenada por sucursal, queremos poner un fondo alternado que nos ayude a visualizar cada bloque de datos. Queremos que esta funcionalidad sea dinámica, es decir, si agregamos o quitamos líneas a la tabla, el fondo del bloque se adaptará automáticamente.

El problema que debemos resolver es encontrar un criterio que se pueda evaluar como VERDADERO para el primer bloque, FALSO para el segundo, nuevamente VERDADERO para el tercero y así sucesivamente.

Para facilitar la explicación, mostraremos varias columnas auxiliares, pero la solución definitiva utilizará una única columna auxiliar.

Empezamos por crear una columna auxiliar donde evaluamos si el contenido de una celda es distinto del de la celda inmediata superior



El doble signo "-" en la fórmula fuerza a Excel a convertir el valor VERDADERO en 1 y el resultado FALSO en 0. Lo mismo puede hacer multiplicando la expresión por 1.

En la segunda columna auxiliar ponemos esta fórmula en la celda E2

=CONTAR.SI($D$2:D2;1)

y la copiamos a lo largo del rango hasta E16



Como pueden ver, todas las celdas relacionadas a la Sucursal 1, dan como resultado 1, las de Sucursal 2 dan 2 y así sucesivamente.
Así tenemos un número que identifica cada bloque. Pero lo más importante es que tenemos una serie donde se alternan los números pares y los impares. De esta manera podemos usa la función ES.IMPAR (ISODD en inglés) para generar una serie de valores VERDADERO y FALSO que se alternan con cada bloque de sucursales



Podemos prescindir de las columnas auxiliares E, F y G usando esta fórmula

=ISODD(CONTAR.SI($D$2:$D2;1))



Para mayor comodidad encapsulamos esta fórmula en un nombre, "fcc"



Ahora, seleccionamos la tabla y aplicamos formato condicional usando nuestra fórmula, que hemos puesto en el nombre "fcc" como criterio



Como ven, sólo hemos dejado la columna auxiliar D, que usamos en nuestra fórmula.



El archivo con el ejemplo se puede descargar aquí.




Technorati Tags:

6 comentarios:

  1. hola, acabo de encontrar este blog intentando buscar una solución a mi problema y está genial, veo cosas que ni sabía que se podían hacer con excel. Toca una lectura profunda de todos tus hilos. Gracias por tu trabajo desinteresado.

    Ahora, no se si puedo plantear esta duda por aquí pero es que como no consigo encontrar nada que me lo solucione quería saber si tu sabes como podría hacer que teniendo una matrix tipo:

    Cliente1 Trabajo1
    Cliente1 Trabajo2
    Cliente2 Trabajo3
    Cliente3 Trabajo4
    Cliente3 Trabajo5

    obtenga otra matriz concatenando la 2ª columna para los clientes coincidentes:

    Cliente1 Trabajo1 + Trabajo2
    Cliente2 Trabajo3
    Cliente3 Trabajo4 + Trabajo5

    hasta ahora usaba buscarv pero claro, no se me repetian varios clientes.

    Saludos.

    ResponderBorrar
  2. Excelente artículo, como siempre, lo leí y lo puse en práctica inmediatamente en una planilla en la que me viene mas que bien tener identificados los cambios en una columna.

    Me facilitó la lectura.

    Muchas gracias!!!.

    ResponderBorrar
  3. Hola Alberto
    el dato Trabajo1, Trabajo2,etc. es númerico o texto?

    ResponderBorrar
  4. hola jorge, perdona por la tardanza en responder ya que he estado bastante liado.

    El trabajo1 y trabajo2 por ahora son campos numéricos.

    Al final lo solucioné de una forma un poco manual.

    Pongamos que la matriz está en la hoja2 y empieza en la columna A y la fila 2:

    Cliente1 Trabajo1
    Cliente1 Trabajo2
    Cliente2 Trabajo3
    Cliente3 Trabajo4
    Cliente3 Trabajo5

    lo que hice fue poner en la columna C la fórmula =SI(A1=A2;"";"OK")

    y en la columna D la fórmula
    =SI(A2=A3;CONCATENAR(A2;", ";B2;" y ";B3);CONCATENAR(A2;", ";B2))

    Luego en la hoja 1 puse una tabla con 2 columnas, en una la fórmula =SI(Hoja2!C2="OK";Hoja2!A2;"") y en la otra columna la fórmula =SI(Hoja2!C2="OK";Hoja2!D2;"")

    Asi los registros en blanco los borro manualmente y ya puedo imprimir.

    Es algo chapucero y seguro que hay alternativas mejores pero mi nivel no da para mucho más.

    Dejo el archivo ejemplo por si quieres descargarlo y verlo:

    http://rapidshare.com/files/112278610/prueba.xls.html

    Saludos.

    ResponderBorrar
  5. Tengo un problema similar al que planteas en este artículo, pero me gustaría que combinar las celdas que tengan el mismo valor.
    No veo que se pueda hacer con el formato condicional. ¿Sabes si existe alguna función o alguna forma de combinar celdas de forma "inteligente"?

    ResponderBorrar
  6. Que quieres decir con "combinar celdas"?

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.