Operaciones con colores en Excel

domingo, enero 27, 2008

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:

53 comments:

Jose,  30 enero, 2008 18:35  

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...

Jorge L. Dunkelman 30 enero, 2008 22:46  

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.

Anónimo,  29 febrero, 2008 10:55  

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

albersil 01 mayo, 2008 20:25  

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.

Jorge L. Dunkelman 01 mayo, 2008 20:58  

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.

Fernando,  11 junio, 2008 22:25  

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.

Jorge L. Dunkelman 12 junio, 2008 21:20  

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.

Ant 07 julio, 2008 13:05  

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

Jorge L. Dunkelman 07 julio, 2008 18:42  
Este comentario ha sido eliminado por el autor.
Jorge L. Dunkelman 07 julio, 2008 18:43  

Ant,

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

Jorge L. Dunkelman 07 julio, 2008 18:43  
Este comentario ha sido eliminado por el autor.
Anónimo,  18 julio, 2008 00:04  

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

Anónimo,  18 julio, 2008 00:47  

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

Jorge L. Dunkelman 18 julio, 2008 09:07  

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.

Anónimo,  28 enero, 2009 13:11  

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

Anónimo,  28 enero, 2009 13:34  

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

Jorge L. Dunkelman 28 enero, 2009 18:27  

Fijate en el uso de las macrofunciones como explico en esta nota

Jorge L. Dunkelman 28 enero, 2009 18:29  

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.

Anónimo,  25 setiembre, 2009 00:42  

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

Jorge L. Dunkelman 25 setiembre, 2009 10:14  

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.

Anónimo,  16 octubre, 2009 04:22  

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?

Jorge L. Dunkelman 16 octubre, 2009 07:35  

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.

J.Moedano 20 octubre, 2009 19:29  

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

Jorge L. Dunkelman 20 octubre, 2009 21:29  

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.

Anónimo,  26 octubre, 2009 22:30  

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.

Jorge L. Dunkelman 27 octubre, 2009 21:14  

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.

Anónimo,  27 julio, 2010 21:46  

PUEDO SABER EL COLOR DE UNA CELDA CON FORMATO CONDICIONAL

Jorge L. Dunkelman 28 julio, 2010 07:07  

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.

Anónimo,  04 diciembre, 2010 13:01  

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

Anónimo,  06 diciembre, 2010 06:41  

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?

Jorge L. Dunkelman 06 diciembre, 2010 08:53  

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.

Anónimo,  06 diciembre, 2010 19:12  

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

Anónimo,  27 noviembre, 2011 14:04  

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

Jorge L. Dunkelman 28 noviembre, 2011 07:13  

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

Anónimo,  25 diciembre, 2011 22:59  

LA SEGUNDA FUNCION NO ME ACEPTA EL ; Y NO ME FUNCIONA

Jorge L. Dunkelman 26 diciembre, 2011 07:13  

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

Anónimo,  17 febrero, 2012 14:44  

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

Jorge L. Dunkelman 17 febrero, 2012 15:37  

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.

Anónimo,  17 febrero, 2012 16:16  

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

Jorge L. Dunkelman 17 febrero, 2012 18:01  

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

Anónimo,  17 julio, 2012 20:26  

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

Anónimo,  17 julio, 2012 20:39  

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

Anónimo,  04 setiembre, 2012 21:44  

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

Jorge L. Dunkelman 05 setiembre, 2012 07:22  

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

Alvaro Gutierrez 13 febrero, 2013 23:26  

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”)

Jorge L. Dunkelman 14 febrero, 2013 07:02  

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

Juan Ortiz 06 noviembre, 2013 20:40  

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

Jorge Dunkelman 09 noviembre, 2013 18:37  

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.

Alfredo Bravo 19 noviembre, 2013 21:32  

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.

Jorge Dunkelman 20 noviembre, 2013 07:06  

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.

Anónimo,  29 octubre, 2014 20:19  

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

Jorge Dunkelman 30 octubre, 2014 07:03  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP