domingo, enero 27, 2008

Operaciones con colores en Excel

En la nota Usando funciones XLM (Excel 4) en hojas de cálculo, mostraba cómo se podía obtener el número de color del fondo de una celda. Esto implicaba el uso de "macrofunciones" (funciones del lenguaje de macro XLM que existió hasta la versión 4) dentro de nombres. Esta técnica nos permitía realizar operaciones como sumar o contar, basándonos en los colores del fondo de un rango de celdas.
No tenía intenciones de volver sobre el tema, hasta que hace unos días un compañero de trabajo me manda un cuaderno Excel con una lista de cerca de 4.000 clientes de la empresa. La tabla estaba ordenada alfabéticamente y cada nombre de cliente tenía un color distinto de acuerdo a las condiciones de crédito (al contado: azul; 30 días: verde; 60 días: amarillo; etc.). Su tarea era sumar los saldos de los clientes por condiciones de crédito y calcular el promedio, para lo cual había que contar el número de clientes en cada grupo.
Después de señalarle que esa hoja era uno de los mejores ejemplos de lo que no se debe hacer en Excel (preferir la estética a la utilidad) intenté explicarle la técnica a usar con las macrofunciones. Como podrán imaginar mi compañero no estaba del mejor ánimo para explicaciones, después de haber invertido horas en poner fondos de color por tipo de crédito para descubrir al final que no puede hacer nada con la lista.
Decidí que lo mejor sería escribir unas UDF (funciones definidas por el usuario) que hagan la tarea.
Empezamos por una función que de cómo resultado el color del fondo de la celda:

Function extraer_color(miCelda As Range)
extraer_color = miCelda.Interior.ColorIndex
End Function


Aplicamos la fórmula a algunas celdas con fondo de color




La celda A6 no tiene ningún fondo y de ahí el resultado. Podemos cambiar el código de esta manera para que en caso de no haber fondo el resultado sea 0


Function extraer_color(miCelda As Range)
Select Case miCelda.Interior.ColorIndex
Case xlNone
extraer_color = 0
Case Else
extraer_color = miCelda.Interior.ColorIndex
End Select
End Function



Nuestra próxima función nos permitirá contar por color:


Function contar_por_color(RangoColor As Range, CeldaColor As Range)
Dim rngCelda As Range

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
contar_por_color = contar_por_color + 1
End If
Next
End Function




La función tiene dos variables: RangoColor, que es el rango dónde queremos contar por color y CeldaColor, que es la celda que contiene el color del criterio.

Finalmente, una función para contar por color:

Function sumar_por_color(RangoColor As Range, CeldaColor As Range, RangoSumar As Range)
Dim rngCelda As Range
Dim colOffset As Long

colOffset = RangoSumar.Column - RangoColor.Column

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_por_color = sumar_por_color + rngCelda.Offset(0, colOffset).Value
End If
Next
End Function



En esta función usamos un tercer argumento para el rango que contiene los valores a sumar. Los rangos RangoColor y RangoSumar deben tener la filas en común.
La variable colOffset calcula la distancia, en número de columnas, entre el rango con los valores y el rango con los fondos de color. Naturalmente, este número puede ser positivo, si los números están a la derecha de los colores, o negativo si lo están a la izquierda.

El cuaderno con las funciones puede descargarse aquí

Technorati Tags:

64 comentarios:

  1. Jorge: no era mas facil que la estetica dependa de la utilidad y no al reves??? Quiero decir: era mejor que en el ejemplo que mencionaste tu amigo hubiese escrito la condicion, y que dependiendo del valor puesto en la celda el color variase utilizando "formato condicional", no?

    Obviamente entiendo: una vez que completó miles de celdas con colores, ya es dificil de arreglar...

    ResponderBorrar
  2. Hola José
    no se puede cambiar la condición de una celda con fórmulas. Es decir, no existe la fórmula (incluyendo las UDF's) que pueda cambiar el color del fondo de la celda. Este es uno de los conceptos largamente ignorados por usuarios de Excel, incluyendo aquellos que lo usan para sus tareas diarias.
    Existen sólo dos formas de hacerlo: con macros o con Formato Condicional.
    Excel es una de las mejores herramientas, si no la mejor, para cálculos, análisis y manejo de datos. Pero para presentación de datos hay herramientas mejores. Cuando ponemos la estética en primer lugar, necesariamente renunciamos a la funcionalidad y la eficiencia.

    ResponderBorrar
  3. Hola Jorge:

    Muchas gracias por artículo, me ha servido como base para realizar unas mejoras que me habían solicitado realizar en unas hojas de control de turnos de mi trabajo.

    Un saludo
    Javier Pérez
    Estacion Depuradora

    ResponderBorrar
  4. Ete ejemplo es realmente bueno. Lo he probado en una hora que tengo y funciona perfectamente, pero tengo otra en la que el relleno del color de la celda lo hago mediante formatos condicionales, según si el turno de trabjo es de mañana tarde, noche, saliente o libre y no cuenta, supongo que el relleno de esta forma (con formatos condicionales) no es lo mismo. Me gustaría saber cómo se hace. Muchas Gracias.

    ResponderBorrar
  5. Hola
    una posibilidad es que el color que pones con formato condicional no sea el mismo que el de la celda de referencia. Para chequearlo puedes usar la función para ver cuál es el número del color del fondo.

    ResponderBorrar
  6. Hola Jorge, espero me puedas resolver el siguiente problema. Quiero meter en una hoja un botón para resetear los valores de ciertas celdas. Lo que quiero es que borre el contenido de todas las celdas de color amarillo claro(colorindex 36) de la hoja.

    Gracias.

    ResponderBorrar
  7. Hola Fernanado
    copia este código a un módulo común en el editor Vba

    Sub del_yellow()
    Dim Cell As Range

    For Each Cell In Selection
    If Cell.Interior.ColorIndex = 36 Then
    Cell.Interior.ColorIndex = xlNone
    End If
    Next

    End Sub

    y asócialo a un botón.
    Luego selecciona todo el rango relevante, o toda la hoja, y corre la macro apretandoel botón.

    ResponderBorrar
  8. Tengo unas celdas coloreadas mediante formatos condicionales, y necesitaría contar cunatas son. No me funciona ninguna de las formulas descritas. Gracias

    ResponderBorrar
  9. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  10. Ant,

    pro favor mandame el archivo para que pueda analizarlo (jorgedun@gmail.com)

    ResponderBorrar
  11. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  12. COMO ESTAS JORGE EXCELENTE TU INFORMACION ME SIRVIO DE PERLAS EN UN TRABAJO CON EXCEL, MI CONSULTA ES LA SIGUIENTE:

    EL MOMENTO DE CONTAR LAS CELDAS DE UN MISMO COLOR, HAY LA POSIBILIDAD DE QUE CUANDO CAMBIE POR A O B RAZON EL COLOR DE LA CELDA, EL VALOR DEL CONTEO SE ACTUALICE INMEDIATAMENTE.

    CUENTAME Y GRACIAS POR TU COLABORACION

    ResponderBorrar
  13. Estimado Jorge gracias por la ayuda con el conteo de los colores, mi consulta es la siguiente, existe la manera de que al contar los colores de las celdas, si yo modifico el color de una celda por a o b razon, el valor del conteo se actualice inmediatamente.

    Saludos

    Jose Luis

    ResponderBorrar
  14. Hola Jose Luis

    si modificas el color de la celda usando Copiar Formato en lugar de poner el color con la paleta, el resultado se actualizará automáticamente.

    ResponderBorrar
  15. Hola Jorge:

    ¿Existe la posibilidad de usar la función SI preguntando en el primer argumento por el color de una celda?
    Por ejemplo, en A1 tengo el Rojo como color de relleno de la celda y en B1 quiero hacer una fórmula con la función SI (o alguna función personalizada que lo permita) que me asigne a esta celda B1 un valor determinado en función del color.
    En realidad, también me valdría una función que preguntara si A1 tiene color de relleno o no. Es decir, lo que me importa es que si en A1 he asignado cualquier color de relleno, en B1 me coloque un determinado texto o número.
    Gracias de antemano

    ResponderBorrar
  16. Hola Jorge:

    Te he escrito hace un rato para saber si se puede usar la función SI preguntando por el color de una celda. Me he dado cuenta después que podríamos tener una fórmula de este tipo:
    =SI(PERSONAL.XLS!extraer_color(A1)<>0;1;"")
    De esta forma, cualquier color de relleno me daría un resultado de 1 en la celda donde estuviera la fórmula.
    Te quiero hacer 2 pequeñas preguntas:
    1) Hay algún método más simple o así lo doy por bueno?
    2) Se puede asignar el color que no sea a través de Copiar Formato sino con el icono de Color de Relleno y que lo actualice automáticamente?

    Este último punto es el que más me interesaría.
    Gracias por todo

    ResponderBorrar
  17. Fijate en el uso de las macrofunciones como explico en esta nota

    ResponderBorrar
  18. El segundo punto no me queda claro. Podés programar un evento que ponga el color en la celda cuando se cumple alguna condición, pero no estoy seguro que éso es lo que querés hacer.

    ResponderBorrar
  19. MI PROBLEMA ES EL SIGUIENTE TRABAJO EN UN LUGAR DONDE ME ENVIAN DATOS EN EXEL DE TODO TIPO
    CELDAS CON COLORES Y/O FUENTES CON COLORES
    NECESITO SABER COMO HACER QUE POR MEDIO DE MACROS O BV PUEDA SELECIONAR,FILTRAR U ORDENAR POR CUALQUIERA DE LOS COLORES CELDAS O FUENTES

    Arturo_dsp@yahoo.es
    gracias de antemano

    ResponderBorrar
  20. Las funciones necesarias (UDF, definidas por el usuario) están explicadas en la nota. Puedes poner una columna axiliar con el número de color y hacer el filtrado en base a esta columna auxiliar.

    ResponderBorrar
  21. Motivado en este post, lo emplee para un problema parecido al que tenia, el unico detalle es que no se actualiza por si mismo, busque por otros lugares y en uno de ellos (no recuerdo donde era) ponian la funcion NOW, en otros he visto aplicar ´selectionchange´ la pregunta es esto funciona correctamente o es posible?

    ResponderBorrar
  22. La necesidad de usar funciones volátiles como NOW(), (AHORA() en la versión española) o eventos como SelectionChange se debe a que no todo cambio en una hoja de Excel hace que las fórmulas sean recalculadas.
    Por ejemplo, si cambiabamos el colo de la fuente o del fondo de la celda, las fórmulas no son recalculadas.

    ResponderBorrar
  23. hola jorge muchas felicidades por el post muy bueno solo un comentario El cuaderno con las funciones que pusiste para descargar no se baja te dejo mi correo para ver si me lo puedes enviar gracias y un saludo

    jmoedano@legacero.com.mx

    ResponderBorrar
  24. Hola
    el enlace funciona, sólo que la genre de Esnips lo ha hecho complicado. Busca el enlace "Download" en la parte izquierda de la página y sigue las instrucciones.

    ResponderBorrar
  25. Donde tengo que poner el codigo para que me funcione en todos los libros que abra, lo he pegado en un libro y funciona, pero si lo pego en el personal.xls no. QUe puedo hacer GRACIAS.

    ResponderBorrar
  26. Justamente para que la macro esté disponible para todos los cuadernos de la sesión, hay que guardarla en el Personal.xls
    Te sugiero abrir el editor de Vba (Alt+F11) y fijarte si el Perosnal.xls aparece en la lista de proyectos.

    ResponderBorrar
  27. PUEDO SABER EL COLOR DE UNA CELDA CON FORMATO CONDICIONAL

    ResponderBorrar
  28. Las funciones que aparecen en la nota no reconocen colores generados por formato condicional. Existe una solución a esta limitación pero la explicación excede el marco de un comentario.
    Estaré publicando algo sobre el tema más adelante.

    ResponderBorrar
  29. Hola Jorge,
    Hace algún tiempo que di con tu función, dicho sea de paso muchas gracias y respecto a tu última entrada añadir que, además de el problema con los formatos condicionales también encontré que la función no opera bien en otro caso.

    Se trata cuando por código VB asignas valores a algunas celdas. En mi caso, antes de entrar en la hoja colorea unas celdas en función del valor de otras celdas. Ahora bien una vez asignado el color por la macro borra el color antiguo y pega el nuevo, sin embargo la formula sigue contando el color como el antiguo.

    No funciona actualizar en ningún caso, solo abriendo la fórmula y aceptándola de nuevo recalcula el valor.

    He intentado, estoy intentando de hecho, buscar alguna solución, pero sin éxito.

    Un saludo

    ResponderBorrar
  30. HOLA, LA FUNCION QUE CREASTE NO SIRVE PARA CONTAR POR COLOR SI LAS CELDAS A CONTAR TIENEN COLOR DETERMINADO POR FORMATO CONDICIONAL. ALGUNA SUGERENCIA PARA ESTE CASO?

    ResponderBorrar
  31. Así es, VBa no reconoce el color de la celda cuando es aplicado con formato condicional. Chip Pearson publicó una nota sobre el tema con una solución (en inglés).
    Tal vez publique algo en en blog más adelante.

    ResponderBorrar
  32. Gracias Jorge,

    La solución de Chip Pearson tampoco es válida, al ejecutar el código, y puesto que no se trata de una asignación por formato condicional el resultado es el mismo.

    Obviamente estoy ablando de mi caso, es decir, cuando la asignación se realiza "pintando" las celdas mediante una macro. También he intentado adaptar una versión pero sin éxito y difícil de entender puesto que pidiendo las propiedades de la celda éstas coinciden plenamente.

    Seguiremos buscando, muchas gracias de nuevo.

    Salu2

    ResponderBorrar
  33. Hola Jorge. A estas alturas has encontrado algun metodo para sumar celdas con un determinado color por formato condicional. Si es asi me puedes indicar donde esta el tema. Saludos y gracias por compartir tus conocimientos, no te haces una idea de lo beneficiosos que son. Tony

    ResponderBorrar
  34. No. Te sugiero que veas el artículo de Chip Pearson que menciono en mi comentario del 6/12/2010

    ResponderBorrar
  35. LA SEGUNDA FUNCION NO ME ACEPTA EL ; Y NO ME FUNCIONA

    ResponderBorrar
  36. Prueba usar "," en lugar de ";" (el separador depende de las definiciones regionales del Windos). Te sugiero que descargues el archivo con el ejemplo.

    ResponderBorrar
  37. EXCELENTE BLOG.Y LO MÁS IMPORTANTE: FACIL DE ENTENDER. PREGUNTITA:ES POSIBLE CON UNA MACRO O PROGRANMACIÓN QUE ME ORDENE ALFABETICAMENTE LOS REGISTROS DE VARIOS FICHEROS Y ME PONGA DE UN COLOR DETERMINADO(AMARILLO) DE FONDO LOS DUPLICADOS DE UN CAMPO, PERO QUE SE EXTIENDA, EL COLOR CON QUE SE MARCA, A TODA LA FILA QUE TENGA DATOS.
    MUCHAS GRACIAS POR TU AYUDA
    GRACIAS

    ResponderBorrar
  38. En cada hoja de un cuaderno se puede hacer con facilidad usando Formato Condicional (fijate en esta nota).
    Ordenar tablas en Excel es trivial. Ahora, para hacer las dos operaciones en varios archivos de un cuaderno la única forma es con Vba (macros) y como comprenderás la programación de semejante código no es trivial.

    ResponderBorrar
  39. Hola Jorge. Excelente tu blog.Se podría con una macro en la que estan marcadas de un color los registros duplicados que esa marca de color se extienda a toda la fila- que tiene varias columnas- con datos de esos registros duplicados.
    Muchas Gracias

    ResponderBorrar
  40. No hace falta una macro, basta con definir apropiadamente el rango donde aplicar el formato condicional.

    ResponderBorrar
  41. Buen dia, estoy haciendo una hoja de excel para llevar a cabo un inventario de productos si estoy usando =SI().... y un stock minimo, pero al estar por debajo del minimo necesito que la celda se coloree de rojo, como puedo hacer eso, gracias por su ayuda

    ResponderBorrar
  42. Con la Nota de Jorge L. sobre el formato condicional, logre hacer lo que necesitava, muchas gracias

    ResponderBorrar
  43. HOLA QUE TAL ESPERO Y ME PUEDAN APOYAR EN LO SIGUIENTE TENGO DOS MACROS UNA PARA CONTAR CELDAS DE COLOR Y OTRAS PRA SUMAR LOS VALORES QUE SE ENCUENTRAN EN CELDAS DE CIERTO COLOR LO MALO QU AL CAMBIAR EL COLOR DE LA CELDA NO SE ACTULIZAN LOS DATOS AUTOMATICAMENTE COMO LO PUEDO HACER YA QUE TENGO QUE PARARME SOBRE LA FORMULA Y DAR ENTER

    ResponderBorrar
  44. Apretar F9 para forzar el recalculado de la hoja (y no escribir lo comentarios en mayúsculas!!!, es como si estuvieras gritando).

    ResponderBorrar
  45. Necesito ayuda, miren tengo un listado de mas de 500 filas , lo que quiero conseguir es que dos columnas de esta tabla cambien a un color ROJO si una tiene un valor distinto que la otra y cada vez que se ingrese una nueva línea realice esta comparación entre ambas columnas si usara una formula seria algo asi: si(A3<>F3;”COLOR FONDO ROJO”)

    ResponderBorrar
  46. Se hace con formato condicional (no se pueden cambiar las propiedades de una celda con fórmulas).
    Fijae en esta nota.

    ResponderBorrar
  47. Existe algun VBA , para contar colores de celdas cuando los colores están asignado por un formato condicional.

    ResponderBorrar
  48. No, las porpiedades Color y ColorIndex no son reconocidas por Vba si fueron definidas por formato condicional.
    Chip Pearson tiene una nota sobre el tema con algunos códigos para realizar la tarea.

    ResponderBorrar
  49. Hola Jorge.

    Tenés idea de donde puedo conseguir la traducción al español de las funciones XLM 4 (que están en inglés y que las bajé de la web de Microsoft, con tu link)?-
    No puedo encontrar por ninguna parte la traducción de todas las funciones para realizar cierta pruebas en Excel 2007 en español.
    Muchas gracias desde ya.

    Un gran abrazo!
    Alfredo.

    ResponderBorrar
  50. Hola Alfredo,
    al principio de la nota hay un enlace a otra nota sobre el tema. En esta última hay, al final de la nota, un enlace para descargar el archivo en castellano.

    ResponderBorrar
  51. Saludo a todos, disculpen la ignorancia, pero una vez inserto el codigo e intento utilizar la funcion =extraer_color(C5) obtengo como resultado en la celda lo siguiente: #¿NOMBRE? alguien me puede ayudar. Gracias

    ResponderBorrar
  52. ¿Dónde estás poniendo el código?

    ResponderBorrar
  53. Hola Jorge: es posible contar celdas por color aplicado desde una condición ? tengo excel 2007 español

    ResponderBorrar
  54. Supongo que si pero no me queda claro que significa "aplicado desde una condición".

    ResponderBorrar
  55. Cuando digo "aplicado desde una condición" me refiero a que el color es resultado de una condición. EJ: SI una o mas celdas del rango A1:A20 son >=1 entonces la(s) celdas queden de color rojo.
    El código que nos compartiste es válido para colores asignados manualmente, quería saber si tienes un código que identificara y contara los colores que sean resultado de una condición.
    Gracias por la atención prestada.

    ResponderBorrar
  56. La única forma de cambiar el color de una celda es con formato condicional, por eso no entendía tu planteo (fórmulas no pueden cambiar la estructura de las celdas).
    En cuanto a tu consulta, fijate en mi comentario del 6/12/2010 o del 9/11/2013.

    ResponderBorrar
  57. Hola, tengo una consulta, quiero sumar las celdas con un determinado color, pero ajustado a una condición de texto, por ejemplo, dada la celda A2=PLANTA 1, sumar todas las celdas rojas que hay de B2:Z2. Se podría realizar?

    ResponderBorrar
  58. Si buen se puede realizar, no es recomendable usar colores como criterios. Te sugiero usar datos "verdaderos", como números o letras, y usar las funciones nativas de Excel como SUMAR.SI.CONJUNTO, por ejemplo.

    ResponderBorrar
  59. 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),

    ResponderBorrar
  60. 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),

    ResponderBorrar
  61. Joahn, en el post hay una macro para sumar por colores.
    Pero en términos de calidad, es una mala práctica usar colores como criterios.

    ResponderBorrar
  62. Hola Jorge la verdad es que das una información fenomenal y se nota que eres un súper experto en Excel.
    Mi pregunta es qué código hay que poner en Visual Badic para hacer una macro que filtre por color unas celdas en las que se hayan puesto colores o de texto o de relleno.
    Gracias por adelantado

    ResponderBorrar
  63. A partir de la versión 2007 Excel tiene incorporada en Autofiltro la posibilidad de filtrar por color (de fuente o fondo). Así que no necesitás ninguna macro, excepto que estés usando aún versiones anteriores.

    ResponderBorrar

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