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