domingo, 20 de diciembre de 2009

Evitar Valores Cero y Blancos en un Gráfico

"Tengo que realizar un gráfico y tengo algunas celdas que contienen el valor cero y otras están en blanco ¿es posible que no aparezcan dichos valores en el gráfico?"

Sí es posible. Lo solucionaremos por medio de la función NOD. El problema planteado es el siguiente:


Como se puede comprobar existen dos celdas con el valor cero y una celda en blanco. Si realizamos un gráfico de esta tabla el resultado obtenido será:


Lo que pretendemos conseguir es que excel "ignore" estos valores y realice una gráfica continua (interpole). Para ello hacemos lo siguiente:

1. Nos situamos en la celda D3 y escribimos la siguiente fórmula:

=SI(O(C3=0;C3="");NOD();C3)

La función NOD() no tiene argumentos. Simplemente devuelve el error #N/A. Aunque pueda parecer inútil, será precisamente la solución a nuestro problema, ya que excel interpola automáticamente este tipo de valor en los gráficos.

2. Copiamos la fórmula de D3 hasta D14:


3. Realizamos nuestro gráfico utilizando los rangos B3:B14 y D3:D14. El resultado obtenido será el deseado:


sábado, 19 de diciembre de 2009

Frecuencia por Rango de Edades



"Tengo una tabla con las edades de distintas personas encuestadas y me gustaría agruparlas por rangos de edades y calcular cuántas personas se encuentran en cada grupo".

No problemo. Utilizaremos la función FRECUENCIA en Fórmula Matricial. Partimos de los siguientes datos:


Queremos clasificar toda esta información en los siguientes grupos de edades:


Es decir, queremos saber cuántas valores están comprendidos en cada rango. Para ello utilizaremos la función FRECUENCIA. Dicha función tiene dos argumentos, a saber =FRECUENCIA(Datos;Grupos). Se trata de indicarle en qué Grupos queremos organizar los Datos. Para ello lo primero que necesitamos saber es cómo trasladar los grupos a la hoja. El formato mostrado en el rango H3:H8 no es válido para excel. Para que excel pueda entender la información debemos realizar la siguiente tabla:


Como puede comprobar, se trata de trasladar el límite superior de cada rango de manera ordenada (obviamente muestro la información de la columna H sólo para facilitar la comprensión, pero no es necesario tenerla en la hoja).

Una vez presentada la tabla hacemos lo siguiente:

1. Seleccionamos el rango J3:J8
2. Con este rango seleccionado escribimos la siguiente fórmula (se trata de una fórmula matricial y, por lo tanto, acabaremos pulsando la combinación de teclas Ctrl + Shift + Enter):

{=FRECUENCIA(B3:F16;I3:I8)}

El rango B3:F16 contiene el conjunto de datos que deseamos clasificar. Por otro lado, el rango I3:I8 contiene los grupos dentro de los que queremos clasificar los datos. El resultado será:


El significado es sencillo: 9 de los encuestados se encuentran en el rango de edad de 18 a 25 años; otros 9 en el rango 26 a 30 años; 7 tienen entre 31 y 35 años; etcétera. Es importante recalcar que hemos seleccionado el rango de salida de datos J3:J9 antes de empezar a escribir la fórmula. Al tratarse de una entrada matricial excel nos devolverá todos los resultados "de golpe" sin necesidad de copiar/pegar nada.

viernes, 18 de diciembre de 2009

Cálculos a Partir de un Valor Mínimo



"Necesito realizar el promedio de una serie de números que se encuentran en una tabla pero quiero realizar el promedio de aquellos que superan un valor mínimo".

Para resolver este problema vamos a utilizar las funciones PROMEDIO y SI en Fórmula Matricial. Partimos del siguiente ejemplo:


En C2 hemos establecido un valor mínimo y queremos calcular el promedio de cada una de las clases pero sólo de aquellos valores que superen dicho mínimo.

Nos situamos en la celda B22 y escribimos la siguiente FÓRMULA MATRICIAL (lo que implica que al terminar de escribir la fórmula, en vez de pulsar Enter debemos pulsar la combinación de teclas Ctrl + Shift + Enter):

{=PROMEDIO(SI(B$5:B$20>=$C$2;B$5:B$20))}

De esta manera excel calcula el promedio de aquellos valores del rango B5:B20 que superen o sean iguales al valor indicado en C2. Puede copiar la fórmula de B22 en C22 y D22 para calcular el promedio de cada clase.

Esta misma fórmula la podríamos adaptar para realizar el cálculo de la SUMA, CONTAR o, por ejemplo, calcular el MINIMO desde un valor:

En B24 {=SUMA(SI(B$5:B$20>=$C$2;B$5:B$20))}

En B25 {=CONTAR(SI(B$5:B$20>=$C$2;B$5:B$20))}

En B26 {=MIN(SI(B$5:B$20>=$C$2;B$5:B$20))}


sábado, 12 de diciembre de 2009

Cálculo Repetición de un Día de la Semana entre Fechas



"Necesito saber cuántos domingos hay entre dos fechas determinadas, siendo, evidentemente, dichas fechas variables".

No existe ninguna función específica para resolver directamente este problema, por lo que tendremos que realizar algunos pasos y fórmulas. Vamos a desarrollar el modelo para calcular cuántos domingos, o cualquier otro día de la semana que queramos especificar, hay entre dos fechas determinadas.

Lo primero que vamos a realizar es un calendario diario de tos el año 2009. Evidentemente si las fechas que necesitemos manejar se encuentran entre 2009 y 2010, por ejemplo, entonces deberíamos realizar dicho calendario diario. Para ello nos situamos en la celda G1 y escribimos 1/1/2009. En G2 escribimos la fórmula =G1+1 y copiamos dicha fórmula hasta que aparezca el 31/12/2009 (que terminará lógicamente en la fila 365):


Como se puede apreciar en la imagen le hemos dado el formato de fecha en el que aparece también el nombre del día de la semana. Seleccionamos el rango H1:H365 y le damos el nombre tablafecha.

Preparamos ahora la entrada de datos de la forma que se muestra en la imagen e introducimos dos fecha, por ejemplo del 7 de Febrero al 15 de Mayo:


A continuación nos situamos en la celda H1 y escribimos la siguiente fórmula:

=RESIDUO(G1;7)

Hacemos doble clic en la parte inferior derecha de la celda H1 (copiado inteligente) y la fórmula se copiará hasta la fila 365. Si nos fijamos en esta nueva columna calculada la función RESIDUO nos devuelve el mismo resultado para cada uno de los días de la semana. A saber:


Preparamos la siguiente tabla:


Seleccionamos el rango J2:K8 y le damos el nombre diaresiduo.

Nos situamos en C4 y vamos a Datos/Validación. Seleccionamos permitir Lista y en Origen seleccionamos el rango J2:J8. De esta manera ya tendremos nuestra lista desplegable en la celda C6 para elegir el día de la semana.

En la celda B6 escribimos la fórmula =C4


A continuación necesitamos realizar algunos "cálculos intermedios" para llegar a nuestra solución. Para ello preparamos las siguientes celdas:


Nos situamos en K12 y vamos a calcular la fila de la tabla de fechas en la que se encuentra la fecha inicial. Para ello escribimos la siguiente fórmula:

=COINCIDIR(C2;tablafecha)

en K13 escribimos (para calcular la fila con la que se corresponde la fecha final):

=COINCIDIR(C3;tablafecha)

Ya tenemos la fila que se corresponde con las fechas de inicio y de fin introducidas en C2 y C3. Ahora, en K14, concatenamos estos resultados para generar un rango:

="H"&K11&":"&"H"&K12

Fíjese que la letra de la columna la hemos introducido como dato (no la calculamos) porque todas las fechas se encuentran en la columna H. El resultado de la fórmula indicada será el texto: H38:H135. También podríamos escribir estas tres fórmulas que acabamos de realizar en una sola:

="H"&COINCIDIR(C2;tablafecha)&":"&"H"&COINCIDIR(C3;tablafecha)


Para calcular cuántos, por ejemplo, domingos hay entre las fechas 7 de febrero y 15 de mayo, sólo nos queda contar cuántas veces se repite entre dichas fechas el número de residuo

Una vez hecho esto, nos situamos en la celda C6 y escribimos la siguiente fórmula que explicamos a continuación:

=CONTAR.SI(INDIRECTO(K16);"="&BUSCARV(C4;diaresiduo;2;FALSO))

La función CONTAR.SI nos permite realizar la cuenta en un rango de aquellas celdas que cumplan una determinada condición. El rango que nos interesa (en función de las fechas introducidas) es el que tenemos en la celda K16. El problema es que en K16 tenemos un texto que representa a un rango. Para convertir dicho texto en referencia valida para excel debemos utilizar la función INDIRECTO. De esta manera INDIRECTO(K16) es lo mismo que si introdujéramos manualmente el rango H38:H135 .

Una vez tenemos el rango considerado nos queda establecer la condición que debe cumplir para que excel proceda a contar. La manera de hacer esto es poniendo la condición entre comillas. Por ejemplo, si queremos que cuente el número de celdas del rango H38:H135 que son igual a 1 escribiríamos =CONTAR.SI(H38:H135;"=1") . Ya hemos visto como resolver la parte del rango de manera automática (con la función INDIRECTO). Si dejáramos la solución así sólo podríamos calcular el número de domingos entre dos fechas. Como queremos tener la posibilidad de calcular el número de repeticiones de cualquier día de la semana entre dos fechas, necesitamos dejar como variable la condición. En realidad sólo necesitamos dejar como variable el residuo de la condición. Como ya hemos visto, cada día de la semana se corresponde con un residuo. Dicha información la tenemos en la tabla que hemos llamado diaresiduo (J2:K8). Por lo tanto podemos asociar el día de la semana seleccionado en la celda C4 con el número de residuo de dicho día). Esto es lo que hace la parte de la fórmula BUSCARV(C4;diaresiduo;2;FALSO)

El resultado será:


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:




martes, 27 de octubre de 2009

Cuadros de Texto con Datos Variables


" Necesitaría saber si es posible utilizar cuadros de texto vinculados a celdas. El motivo es que suelo escribir algunos textos explicativos de algunos resultados en celdas de la hoja utilizando la función CONCATENAR pero me gustaría que aparecieran en cuadros de texto al lado de dichos resultados".

Sí es posible. De hecho la forma es casi idéntica al anterior post "Datos dinámicos en los títulos de gráficos". Partimos del siguiente ejemplo:


Queremos que en la zona del rango F5:G9 aparezca un cuadro de texto indicando cuáles fueron las ventas totales de 2009 y la variación porcentual respecto al 2008.

1. Nos situamos en la celda, por ejemplo, I5 y escribimos la siguiente fórmula:

="Las ventas del 2009 ascendieron a "&TEXTO(SUMA(B5:E5);"#.###")&" lo que supuso una diferencia respecto al pasado año del "&TEXTO((SUMA(B5:E5)-SUMA(B4:E4))/SUMA(B4:E4);"0,00%")

Como puede comprobar estamos realizando cálculos de totales y de ratios de crecimiento que no tenemos en ninguna celda de la hoja. Evidentemente si tuviéramos estos cálculos la fórmula se simplificaría enormemente.

2. Seleccionamos el menú Ver/Barras de herramientas/Dibujo.
3. Dibujamos un cuadro de texto en el rango F5:G9
4. Seleccionamos el cuadro de texto y en la barra de fórmulas escribimos =
5. Tras el igual hacemos clic en la celda I5 y pulsamos Enter. El resultado será el siguiente:


Si modifica cualquier dato podrá comprobar que los resultados se actualizarán en el cuadro de texto. El único inconveniente es que sólo podremos realizar cambios de formato para todo el cuadro de texto en conjunto (no podemos entrar en él y seleccionar parte del contenido para darle formato específico).