"Tengo una tabla con las distintas tiendas de mi empresa y las distintas referencias que tenemos a la venta. En dicha tabla tengo información de las unidades vendidas en cada tienda y de cada referencia. Quiero contar y también sumar cuántas tiendas y cuántas referencias se encuentran entre unos números determinados."
El ejemplo de partida. Supongamos que tenemos la siguiente tabla y queremos saber cuántas referencias de cada tienda tienen en stock entre 1.500 y 2.000 unidades. Además de contar el número de referencias queremos sumar el número de unidades con estos requisitos por tienda:
Este problema lo resolveremos básicamente con las funciones CONTAR.SI y SUMAR.SI. Para que resulte más sencillo, vamos a comenzar resolviéndolo con datos en las fórmulas (cosa que NO SE DEBE hacer jamás...).
1. Nos situamos en la celda C27 y escribimos la fórmula:
=SUMAR.SI(C$7:C$26;">=1500")-SUMAR.SI(C$7:C$26;">2000")
De esta manera estaremos sumando sólo las cifras que sean igual o superen los 1.500 en el rango C7:C26. Si a la cantidad resultante le restamos la suma de aquellas referencias que superen los 2.000 obtendremos precisamente la suma de las referencias que se encuentren entre 1.500 y 2.000 en la tienda 1. Podemos copiar la fórmula en el rango D27:F27. Para proceder a contar el número de referencias por tienda copiamos la fórmula de C27 en C28 y sustitutimos el nombre de la función SUMAR.SI por CONTAR.SI:
=CONTAR.SI(C$7:C$26;">=1500")-CONTAR.SI(C$7:C$26;">2000")
El resultado obtenido tras copiar la fórmula será el siguiente:
Para resolver el mismo problema pero por referencia (en vez de por tienda) nos situamos en la celda G7 y escribimos:
=CONTAR.SI($C7:$F7;">=1500")-CONTAR.SI($C7:$F7;">2000")
y en la celda H7 escribimos:
=SUMAR.SI($C7:$F7;">=1500")-SUMAR.SI($C7:$F7;">2000")
El problema de este planteamiento es que si deseamos realizar la misma operación pero con distintos "topes" entonces tendremos que entrar en cada fórmula y sustituir por los nuevos números. Para resolver este problema utilizaremos la función CONCATENAR, o lo que es lo mismo &. Las funciones SUMAR.SI y CONTAR.SI presentan como segundo argumento las condiciones que debe cumplir el primer argumento (el rango) para proceder a la suma o a la cuenta. Dichas condiciones deben encontrarse entre comillas (tal y como se puede ver en las fórmulas realizadas). Lo que no es necesario que se encuentre entre comillas es la cantidad en si. En nuestro ejemplo nos referimos a los topes máximos y mínimos de 2.000 y 1.500 respectivamente. La manera de formular será la siguiente (utilizaremos las mismas celdas que en las anteriores soluciones):
En C27: =SUMAR.SI(C$7:C$26;">="&$C$3)-SUMAR.SI(C$7:C$26;">"&$C$2)
En C28: =CONTAR.SI(C$7:C$26;">="&$C$3)-CONTAR.SI(C$7:C$26;">"&$C$2)
En G7: =CONTAR.SI($C7:$F7;">="&$C$3)-CONTAR.SI($C7:$F7;">"&$C$2)
En H7: =SUMAR.SI($C7:$F7;">="&$C$3)-SUMAR.SI($C7:$F7;">"&$C$2)
De esta manera si cambiamos las cifras en C2 y C3 las fórmulas seguirán funcionando correctamente.
Solución con INDIRECTO
El mismo planteamiento podríamos solucionarlo de la siguiente forma:
1. Seleccionamos el rango C7:F26 y vamos al menú Insertar/Nombres/Crear. Aceptamos lo que aparece por defecto en la ventana: crear nombres en fila superior y columna izquierda.
De esta manera habremos creado nombres para todas las tiendas y todas las referencias (lo puede comprobar abriendo el cuadro de nombres que se encuentra a la izquierda de la barra de fórmulas). Ya podemos, por lo tanto, sustituir la referencias mixtas de rango por la función INDIRECTO y la celda donde se encuentra el nombre de la tienda o de la referencia. Evidentemente el resultado obtenido será el mismo:
Añadiendo FORMATO CONDICIONAL
Si además de lo visto queremos destacar con un color de trama distinto aquellas referencias tiendas y referencias que se encuentran entre los topes analizados deberemos hacer lo siguiente:
1. Seleccionamos el rango C7:F26 y vamos al menú Formato/Formato Condicional.
2. En Condición1 seleccionamos Fórmula y escribimos:
=SI(Y(C7>=$C$3;C7<=$C$2);1;0)
Pulsamos el botón Formato... y seleccionamos el color de trama y fuente que deseemos. Aceptamos y trabajo resuelto:
Buenas Kiko, me preguntaba si esta función CONTAR.SI() sirve para resolver la siguiente cuestión: Tengo una lista de fechas con formato 20/10/2009 y quiero contar el número de fechas de mi lista que pertenecen a un determinado mes ¿Puedo usar la función CONTAR.SI() o debo usar otra?
ResponderEliminar¡Gracias!