View
233
Download
0
Category
Preview:
Citation preview
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 1/13
Sumar y contar celdas
por color de formatocondicionalLas funciones creadas evalúan la propiedad Interior.Color que tiene
cada una de las celdas en Excel, sin embargo, el formato condicional
no utiliza dicha propiedad sino que tiene su propia “versión” para
almacenar el color de fondo de una celda. or esa razón no es posibleutilizar dichas funciones para evaluar colores establecidos a trav!s de
una regla de formato condicional.
la función "#$ %&'() para sumar y contar celdas por color de
formato condicional. Los ob*etos + propiedades que debemos
evaluar para obtener el color de fondo proveniente de un regla de
formato condicional.
La colección FormatConditions
rimero debemos recordar que un mismo rango de celdas en Excel
puede estar su*eto a varias reglas de formato condicional al mismo
tiempo, as que para guardar esa lista de formatos condicionales se
creó la colección FormatConditions en "#$ la cual enumera todas las
reglas de formato condicional aplicadas en un rango.
ara comprender el funcionamiento de esta colección haremos une*emplo. -onsidera la siguiente lista de números en el rango $/$0
donde he aplicado una regla de formato condicional que resalta en
color ro*o los valores ma+ores a 120.
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 2/13
$hora insertar! un botón de comando $ctive3 en la misma ho*a +
colocar! el siguiente código "#$ en su evento -lic4/
5
6
Private Sub CommandButton1_Click()
MsgBox Range("A1A1!")#ormatConditionsCount
$nd Sub
La única lnea de código e*ecutada se encargar7 de mostrar el valor de
la propiedad Count de la colección FormatConditions la cual contiene el
recuento de las reglas de formato condicional para el rango indicado.
$l hacer clic sobre el botón de comando se mostrar7 el siguientemensa*e/
El mensa*e nos indica que el rango $/$0 tiene una sola regla de
formato condicional. $hora crear! una segunda regla para el mismo
rango que resaltar7 de color verde todas las celdas con un valor
menor a 520. &na vez creada la regla de formato condicional, volver!
a pulsar el botón de comando + el número mostrado en el mensa*e
habr7 aumentado debido a la nueva regla creada/
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 3/13
Es as como la colección FormatConditions nos permite obtenerinformación sobre las reglas de formato condicional aplicadas a un
rango de celdas. $hora centraremos nuestra atención a una
propiedad espec8ca de dicha colección.
La propiedad FormatCondition.Interior.Color
-ada regla de formato condicional almacena el estilo que aplicar7 a
las celdas que cumplan con las condiciones establecidas +
espec8camente el color de relleno se almacena en la propiedad
Interior.Color . ara demostrar el valor de esta propiedad agregar! un
nuevo botón de comando con el siguiente código/
5
6
92
:
Private Sub CommandButton%_Click()
#or i & 1 'o Range("A1A1!")#ormatConditionsCount
MsgBox "Regla " i vb* _
"Color "
Range("A1A1!")#ormatConditions(i)+nteriorColor
,ext i
$nd Sub
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 4/13
El código anterior recorre todos los elementos de la colección
FormatConditions + para cada elemento mostrar7 un mensa*e con el
número de regla + su color de relleno que est7 almacenado en la
propiedad Interior.Color . $l pulsar el botón obtengo el siguiente
resultado/
El color devuelto ser7 un valor entre 0 + :11152 que corresponde a
una de las combinaciones de colores primarios %ro*o, verde + azul) que
se pueden formar en Excel.
Los colores mostrados con el código anterior son los colores
pertenecientes a cada una de las reglas de formato condicional + no el
color de una celda espec8ca. ara conocer el color de formato
condicional aplicado a una celda ser7 necesario encontrar la regla que
se cumple sobre dicha celda para entonces obtener el colorcorrespondiente.
Macro para obtener el color de una celda
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 5/13
El desafo m7s grande al crear una macro para obtener el color de
una celda es descubrir la regla de formato condicional que est7 activa.
ara eso utilizamos un bucle (or ;ext que recorrer7 toda la colección
de formatos haciendo una evaluación de cada regla para descubrir si
est7 activa.
5
6
9
2
:
1
<
=
0
5
6
9
2
:
1
<
=
#unction C--R#C(Celda As Range) As ong
.+ndicar/ si la relga de *ormato condicional est/ activa
0im ReglaActiva As Boolean
.Recorrer todas las reglas de *ormato condicional ara la celda
indicada
#or i & 1 'o Celda#ormatConditionsCount
.$valuar la regla #ormatConditions(i)
2it3 Celda#ormatConditions(i)
.Si la regla est/ basada en el valor de la celda
+* '4e & xlCell5alue '3en
.+denti*icar el oerador de la regla 4 evaluar si est/ activa
Select Case -erator
Case xlBet6een ReglaActiva & Celda5alue 7&
$valuate(#ormula1) _
And Celda5alue 8& $valuate(#ormula%)
Case xl,otBet6een ReglaActiva & Celda5alue 8&
$valuate(#ormula1) _
-r Celda5alue 7& $valuate(#ormula%)
Case xl$9ual ReglaActiva & $valuate(#ormula1) &
Celda5alue
Case xl,ot$9ual ReglaActiva & $valuate(#ormula1) 87
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 6/13
5
0
5
55
5
6
5
9
5
2
5:
5
1
5
<
5
=
6
0
6
6
5
6
6
6
96
2
6
:
6
Celda5alue
Case xl:reater ReglaActiva & Celda5alue 7
$valuate(#ormula1)
Case xless ReglaActiva & Celda5alue 8
$valuate(#ormula1)
Case xl:reater$9ual ReglaActiva & Celda5alue 7&
$valuate(#ormula1)
Case xless$9ual ReglaActiva & Celda5alue 8&
$valuate(#ormula1)
$nd Select
.Si la regla es una exresi;n (#;rmula)
$lse+* '4e & xl$xression '3en
AlicationScreen<dating & #alse
CeldaSelect
ReglaActiva & $valuate(#ormula1)
Range(ActiveCellAddress)Select
AlicationScreen<dating & 'rue
$nd +*
.0evolver el color si la regla est/ activa
+* ReglaActiva '3en
C--R#C & +nteriorColor
$xit #unction $nd +*
$nd 2it3
,ext i
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 7/13
1
6
<
6
=9
0
9
9
5
9
69
9
9
2
9
:
9
1
9
<
9
=
$nd #unction
Las primeras lneas de código se encargan de recorrer todas las reglas
de formato condicional para la celda proporcionada. La primera
validación importante es sobre la propiedad Type que nos permite
saber el tipo de regla que estamos analizando, +a sea del tipo xlCellValue o del tipo xlExpression. Es importante mencionar que
existen m7s tipos de reglas de formato condicional, de hecho la
enumeración 3l(ormat-ondition>+pe contiene 9 tipos diferentes,
pero solamente los dos tipos mencionados anteriormente tienen una
manera de ser evaluados desde "#$. or esta razón la función
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 8/13
COLORFC considera en primer lugar las reglas de formato condicional
que evalúan directamente el valor de una celda.
El cuadro de di7logo anterior muestra precisamente la lista de reglas
de formato condicional que son del tipo xlCellValue + que por lo tanto
ser7n reconocidas por nuestra función. $hora hagamos una prueba
con los datos de e*emplo para ver cómo la función COLORFC devuelve
el color aplicado a cada celda de acuerdo a la regla de formato
condicional activa. ?ecuerda que las reglas creadas resaltan de ro*o
las celdas con un valor superior a 120 + de color verde los valores
menores a 520.
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 9/13
;uestra función "#$ funciona correctamente al devolver el código decolor adecuado para cada celda. El otro tipo de reglas consideradas
en la función COLORFC son aquellas basadas en una expresión, que
generalmente son las reglas basadas en una fórmula que contiene
funciones de Excel. ero debo darte una mala noticia, esta opción
solo funcionar7 si tenemos Excel en ingl!s. @ a continuación explico
por qu!.
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 10/13
$l momento de crear una regla de formato condicional que utilice una
función de Excel, colocaremos su nombre en castellano. 'icha
fórmula se almacenar7 en una propiedad de la regla de formato
condicional + al momento de evaluarla desde "#$ la cadena de texto
contendr7 el nombre de la función en espaAol lo cual ocasionar7 unerror +a que "#$ solamente entiende las funciones de Excel por su
nombre en ingl!s. $ún as he de*ado esta funcionalidad porque existe
un porcenta*e de usuarios que utilizan Excel en ingl!s + ellos podr7n
obtener el color de las celdas que utilicen reglas de formato
condicional basadas en fórmulas.
'espu!s de todas estas aclaraciones %que eran necesarias), tenemos
una función que nos devolver7 el color aplicado a una celda desde
una regla de formato condicional, as que ahora solo debemosutilizarla para sumar o contar celdas por color.
Sumar por color de formato condicional
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 11/13
ara sumar por color de formato condicional crear! una nueva
función "#$ de la siguiente manera/
56
9
2
:
1
<
=
0
5
6
9
2
:
1
#unction S<MARP-RC--R#C(CeldaColor As Range= Rango As Range) As
0ouble
0im Celda As Range
0im 'otal As 0ouble
0im Color As ong
Color & C--R#C(CeldaColor)
#or $ac3 Celda +n RangoCells
+* C--R#C(Celda) & Color '3en
'otal & 'otal > Celda5alue
$nd +*
,ext Celda
S<MARP-RC--R#C & 'otal
$nd #unction
El primer argumento de la función es una celda que contiene el colorpor el cual deseamos sumar, as que una de las primeras acciones es
obtener dicho color con la función COLORFC . osteriormente se har7
un recorrido por todo el rango de celdas para sumar el valor de
aquellas que tengan el mismo color. Bbserva cómo traba*a nuestra
función con los datos de e*emplo/
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 12/13
La función devuelve correctamente la suma de las celdas $ + $= que
son las que tienen el mismo color que la celda $ que ha sido
indicada como el primer argumento de la función.
Contar por color de formato condicional
Ci en lugar de sumar deseamos contar las celdas que tienen un
mismo color, ser7 su8ciente con modi8car la función anterior para
que en lugar sumar el valor de la celda se sume el valor por cada
celda con el color indicado. El código de esta nueva función es elsiguiente/
5
6
9
2
:
1
<
=
0
#unction C-,'ARP-RC--R#C(CeldaColor As Range= Rango As Range) As
+nteger
0im Celda As Range
0im 'otal As +nteger
0im Color As ong
Color & C--R#C(CeldaColor)
#or $ac3 Celda +n RangoCells
7/23/2019 Sumar y Contar Celdas Por Color de Formato Condicional
http://slidepdf.com/reader/full/sumar-y-contar-celdas-por-color-de-formato-condicional 13/13
5
6
9
2
:
1
+* C--R#C(Celda) & Color '3en
'otal & 'otal > 1
$nd +*
,ext Celda
C-,'ARP-RC--R#C & 'otal
$nd #unction
$l utilizar esta función con los mismos datos de e*emplo podr7s
observar que la función CONTARORCOLORFC nos devuelve el valor 5
que corresponde a las celdas con el mismo color que la celda $6/
Recommended