viernes, 27 de noviembre de 2009

Contar Fechas


Hoy no puedo estar más feliz... Mi hijo Miguel cumple 2 añitos (todo un personaje...)
¡¡ FELIZ CUMPLEAÑOS HIJO !!



"Tengo una lista de fechas con formato 27/11/2009 y quiero contar el número de fechas de mi lista que pertenecen a un determinado mes"

Partimos del siguiente ejemplo:


Una manera sencilla de solucionar este problema es con Tablas Dinámicas:

1. Nos situamos en E3 y vamos a Datos/Informe de Tablas y gráficos dinámicos:


2. Pulsamos Siguiente.
3. Seleccionamos el rango E2:E22:


4. Seleccionamos Diseño:


5. Arrastramos el campo Fecha a la zona DATOS y también a FILA. Aceptamos y pulsamos Finalizar:


6. Obtendremos la siguiente Tabla Dinámica:


7. Nos situamos en cualquier celda del rango A5:A24 y pulsamos el botón derecho del ratón. En el menú emergente seleccionamos Agrupar y mostrar detalle/Agrupar. En la ventana que aparecerá realizamos la siguiente elección y aceptamos:


8. El resultado obtenido será el objetivo buscado:



Solución con FÓRMULAS

1. Nos situamos en F3 y escribimos la fórmula: =MES(E3)

2. Hacemos doble clic en la esquina inferior derecha de la celda F3 (Copiado inteligente) y obtendremos el siguiente resultado:


3. Preparamos la siguiente entrada de datos en B2:C2 para introducir el número de mes que deseamos contar (puede incluir un Control de número en C2 para facilitar el uso). Nos situamos en C4 y escribimos la fórmula que puede ver en la barra de fórmulas de la imagen:


De esta manera obtendremos el número de veces que se repite en nuestra tabla el mes que escribamos en C2.

martes, 17 de noviembre de 2009

Cuenta y Suma Condicional



"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:


miércoles, 11 de noviembre de 2009

Periodificar Fechas con Fórmulas



Hoy, como no podría ser de otra manera, comienzo este post felicitando el cumpleaños a mi querido hermano Carlos (que cumple los "tres patitos": 2 x 22...) ¡FELICIDADES HERMANO!



"Tengo una tabla con fechas y necesitaría agruparlas por trimestres en un nuevo campo para poder aplicar la herramienta de Subtotales"

Para solucionar este problema sin hacer uso de Tablas Dinámicas utilizaremos las funciones MES, TRUNCAR y BUSCARV.

Como siempre, partamos de un ejemplo:


Lo que pretendemos conseguir es que en el rango C13:C36 nos aparezca el número de trimestre que corresponde a cada una de las fechas. Para ello escribimos en C13 la siguiente fórmula:

=TRUNCAR((MES($A13)+2)/3)

La función TRUNCAR nos devuelve la parte entera de un número. Tal y como explica la ayuda de excel, TRUNCAR y ENTERO son similares, ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes solamente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5, ya que -5 es el número entero menor más cercano.

El motivo de utilizar esta función es porque si observamos los resultados de obtener el número de mes (función MES), sumarle dos y dividirlo por tres comprobamos que no todos son exactos:


Para evitar los decimales usamos la función TRUNCAR.

Una vez generado este nuevo campo ya podríamos aplicar la herramienta SUBTOTALES y agrupar por trimestres. Pero si lo que queremos es poder agrupar en base a otras periodificaciones entonces tendremos que modificar la fórmula.

Hemos utilizado la fórmula =(MES($A13)+2)/3) Fíjese que al tratarse de trimestres hemos utilizado como denominador el 3 y como sumando el 2 (3-1=2). Cuando se trate de bimestres utilizaremos como denominador el 2 y como sumando el 1 (2-1=1); con los cuatrimestres utilizaremos como denominador el 4 y como sumando el 3 (4-1=3); y con semestres el 6 y como sumando el 5 (6-1=5). Como puede ver el patrón de la fórmula es muy sencillo.

Para hacer el modelo "flexible" preparamos la hoja de la siguiente forma:

1. Borramos la solución anterior. Vamos a poner la periodificación a continuación de la fecha. Para ello insertamos una columna entre la A y la columna B (en la columna C aparecerán ahora las dietas).

2. Generamos la siguiente entrada de datos:


3. Le damos el nombre, por ejemplo, Agrupacion al rango A3:B6 (lo podemos hacer seleccionando dicho rango; haciendo clic en el cuadro de nombres - a la izquierda de la barra de fórmulas- y escribiendo dicho nombre y pulsando después Enter).
4. Nos situamos en la celda B8 y vamos a Datos/Validación de datos/Permitir/Lista. Como lista seleccionamos el rango A3:A6.
5. Nos situamos en la celda B13 y escribimos la siguiente fórmula:

=TRUNCAR((MES($A13)+BUSCARV($B$8;agrupacion;2;FALSO)-1)/BUSCARV($B$8;agrupacion;2;FALSO))

Si se fija lo único que estamos haciendo es dejar como variable el tipo de agrupación que deseamos realizar y aplicar, por medio de la función BUSCARV, el patrón comentado anteriormente.

6. En la celda B12 escribimos la fórmula =B8


De nuevo podemos aplicar la herramienta SUBTOTALES sin problema:



jueves, 5 de noviembre de 2009

Agrupar por Fechas en Tablas Dinámicas



"Tengo una tabla con información diversa entre la que se encuentra la fecha exacta de las operaciones, el importe de venta, y el vendedor. Cuando genero una tabla dinámica y quiero realizar una clasificación por meses y años me veo obligado a realizar nuevas columnas en la tabla original para obtener el mes y el año de las fechas en cuestión y poder utilizar estos campos en la tabla dinámica ¿Hay alguna otra manera más sencilla y directa?"

Estimado lector: sí la hay.

Supongamos que partimos del siguiente ejemplo:


Tras proceder a generar la tabla dinámica el resultado obtenido será el siguiente:


Como se puede apreciar, los datos relativos a las fecha aparecen como en la tabla original. Si lo que nos interesa es que aparezcan agrupados en, por ejemplo, meses tendremos que realizar los siguientes pasos:

1. Dentro de la barra de herramientas de la tabla dinámica pulsamos el icono llamado tabla dinámica. Del menú que se presenta seleccionamos Agrupar y mostrar detalle :


2. De la ventana que se nos abre seleccionamos meses,o la opción u opciones que nos interesen (se puede seleccionar varias opciones).


3. Aceptamos y obtenemos el resultado deseado. A partir de aquí ya podemos disponer la información como más nos interese: