Algo más sobre la función REDONDEAR (ROUND) de Excel.

La función REDONDEAR, una de las tantas funciones de redondeo que tiene Excel, tiene una curiosidad sobre la cual me llamaba la atención un lector.
Esta función redondea en forma simétrica a la cantidad de decimales deseada. Por ejemplo,

la fórmula =REDONDEAR(10,43;1) da 10.4

la fórmula =REDONDEAR(10,46;1) da 10.5

Si ponemos 0 como número de decimales, el redondeo es obviamente al número entero más cercano.

La curiosidad es que REDONDEAR también acepta números negativos como parámetro para el número de decimales. En ese caso, Excel redondea "hacia la izquierda" a la decena (-1), centena (-2), etc., más cercana.

Por ejemplo:

=REDONDEAR(123,45;-1) da 120

=REDONDEAR(123,45;-2) da 100

Y finalmente =REDONDEAR(123,45;-3) da 0

Si quisiéramos redondear el número 12345, los resultados serían

=REDONDEAR(12345;-1) = 12350

=REDONDEAR(12345;-2) = 12300

=REDONDEAR(12345;-3) = 12000

=REDONDEAR(12345;-4) = 10000

=REDONDEAR(12345;-5) = 0

Technorati Tags: MS Excel

Filtrado de fechas en Excel con Autofiltro o Filtro Avanzado

Supongamos que queremos filtrar una lista de fechas de manera de dejar visibles sólo las fechas que caen en miércoles. Podemos hacer esto con Autofiltro o con Filtro Avanzado, pero cuál sería el criterio?
Si usamos Autofiltro necesitamos crear una columna auxiliar que nos dé el día de la semana para cada una de las fechas del rango. Luego filtramos de acuerdo a esta columna auxiliar.



¿Cómo calculamos el día de la semana para cada fecha? Dos posibilidades, una sencilla y otra complicada:

1 - La complicada, con funciones. Con la función DIASEM, por ejemplo

=DIASEM(A2,2) da 3.

Para transformar el resultado 3 en "miércoles" usamos la función ELEGIR de esta manera

=ELEGIR(DIASEM(A2,2),"lunes","martes","miércoles","jueves","viernes","sábado","domingo")

O con la función INDICE:
=INDICE(semana,DIASEM(A2,2)),

donde "semana" es un nombre que contiene un rango con los días de la semana o directamente los días de la semana.

2 - La posibilidad sencilla. Creamos en la columna auxiliar una referencia a la celda con la fecha (en B2 ponemos =A2) y cambiando el formato de B con el formato personalizado "dddd".

Si queremos usar Filtro Avanzado, tenemos que usar una fórmula lógica, es decir, que dé como resultado VERDADERO o FALSO. Agregamos algunas filas en blanco para poner las filas de criterios y en la celda A2 ponemos esta fórmula

=DIASEM(A5,2)=3



Luego usamos el menú de Filtro avanzado






La ventaja de usar Filtro Avanzado es que nos permite copiar los resultados a otro rango de la hoja o usar más de dos criterios para filtrar la lista.

Ahora veamos otros casos que se presentan.

Para dejar visibles sólo fechas que sean el primer día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)

Si la fecha en A2 coincide con el primer día del mes el resultado será VERDADERO. En caso contrario, FALSO. Luego filtramos la lista usando como criterio VERDADERO en la columna auxiliar.



Si queremos usar Filtro Avanzado, usamos la misma fórmula como criterio






Para filtrar las fechas que sean el último día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)

Para filtrar el primer día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)+ELEGIR(DIASEM(FECHA(AÑO(A2),MES(A2),1),2),0,0,0,0,0,2,21

La primer parte de esta fórmula calcula la fecha del primer día del mes. En la segunda parte de la fórmula DIASEM calcula el número de día de la semana de esta fecha. Este resultado es usado como parámetro en la función ELEGIR, que agrega 0 (cero) si el día de semana cae entre lunes y viernes, suma 2 si el día de semana del primer día del mes cae un sábado o 1 si es domingo.

Para filtrar el último día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)-(MAX(0,DIASEM(FECHA(AÑO(A2),MES(A2)+1,0),2)-5))

Como en los casos anteriores, si queremos usar Autofiltro usamos la fórmula para construir una columna auxiliar. Si queremos usar Filtro Avanzado usamos la fórmula en la celda de criterio.

Technorati Tags: MS Excel

Gráfico de columnas con banda sombreada.

La misma lectora que me consultaba cómo poner un fondo en un gráfico de columnas, me presenta un nuevo desafío: cómo poner una banda sombreada en un gráfico de columnas.
En mi blog sobre gráficos y presentación de datos, que seguirá inactivo por ahora, ya había publicado una nota sobre cómo colorear un área entre dos líneas de un gráfico.
Aquí usaremos esa técnica, pero con algunas adaptaciones. Empecemos por plantear el problema. Tenemos esta tabla de velocidad de lectura de cuatro alumnos



Con la cual hemos creado este gráfico



Queremos crear este gráfico, donde una banda de colar señala la zona comprendida entre un límite inferior y uno superior



Los pasos a seguir son los siguientes:

1 - Agregamos una fila antes de la primer fila de datos e incluimos una fila en blanco después de la última fila de la tabla; en nuestro ejemplo la fila 2 y la fila 7



2 - Modificamos la serie de del gráfico para que incluya las filas que acabamos de agregar



Ahora el gráfico se ve así



3 - Agregamos dos columnas, una "limite inferior" y la otra "límite superior". Estas columnas contienen los puntos de dos series que agregaremos al gráfico para crear la banda



4 - Agregamos las series "límite inferior" y "límite superior" al gráfico en el
formulario de Datos de origen, asegurándonos que la serie "limite superior" sea la segunda y "límite inferior la tercera" (el orden de las series es importante!)



No asustarse del resultado y seguir adelante!

5 - Seleccionamos la serie "limite superior" y en el menú Formato serie de datos-Eje, la relacionamos al eje secundario



Esto hará que aparezca un eje Y secundario. Seleccionamos la serie "límite inferior" y la relacionamos al eje secundario.

Un paso importante es asegurarnos que ambos ejes Y tengan exactamente la misma escala.

6 - Seleccionamos la serie "limite superior" y cambiamos el tipo de gráfico a Área. Luego hacemos lo mismo con la serie "límite inferior". Nuestro gráfico se ve ahora así



7 - Elegimos un color más "blando" para la serie "límite superior" y para "limite inferior elegimos el color del fondo del gráfico. A esta altura del partido el gráfico debe verse así



8 - Seleccionamos el eje de las X y abrimos el menú de Formato de ejes. En la pestaña Escala quitamos la marca de la opción "Eje de valores Y cruza entre categorías"



Finalmente llegamos a nuestra meta:



Este gráfico es totalmente dinámico. Si cambiamos los valores de las series límite superior e inferior, la banda sombreada se "moverá" de acuerdo a los nuevos valores.

Technorati Tags: MS Excel