Formato Condicional en Excel – Ocultar Datos

A veces queremos ocultar datos de una hoja. La opción más común es usar el menú Formato-Fila-Ocultar (o Formato-Columna-Ocultar).
Esta opción no nos permite aplicar ningún criterio, como podríamos hacer con Autofiltro, por ejemplo.
Pero con Formato Condicional y un pequeño truco podemos lograr un efecto similar al de Autofiltro, que también es válido para ocultar datos en columnas.

Supongamos que tenemos esta tabla de ventas de tres productos para los años 2002 – 2006.



Usaremos Formato Condicional y Validación de Datos para crear un mecanismo que nos permita decidir que rango de años mostrar.

Primero agregamos una celda con una lista desplegable para elegir el último año del período a mostrar. En la celda B8 agregamos la lista




Ahora usaremos Formato Condicional para hacer "desaparecer" las celdas que estén fuera del período elegido.

Seleccionamos el rango B1:F5 y abrimos el menú de Formato Condicional. Con la celda B1 seleccionada, elegimos la opción "Fórmula" y escribimos ésta =B$1>$B$8



Es importante poner atención al tipo de dirección de las celdas en la fórmula (B$1 es mixta y $B$8 es absoluta).

Luego apretamos el botón Formato y en la pestaña "Fuente" elegimos el color blanco, para que el contenido de la celda se "mimetice" con el fondo de la celda.



En la pestaña "Bordes" apretamos el botón "ninguno"



Finalmente apretamos "Aceptar".

A partir de este momento, si por ejemplo, elegimos el año 2004 en la celda B8, veremos



Los datos siguen estando, pero se han vuelto invisibles al mimetizarse con el fondo de la celda.
Si queremos compartir nuestro cuaderno, es recomendable proteger las celdas para evitar que un usuario desprevenido borre los datos originales.




Categorías: Manejo de Datos_

Technorati Tags: Excel

Construir con Excel una tabla con las 10 primeras posiciones.

Uno de mis lectores me consulta como construir con Excel una tabla donde aparezcan los 10 alumnos de un curso que han conseguido el mejor puntaje. El pedido incluye que la tabla se actualice automáticamente a medida que se vayan ingresando nuevos datos.

Para construir este modelo tomaremos en consideración dos elementos:

1 – La función JERARQUIA (RANK en la versión inglesa)

2 – Rangos dinámicos con nombres.

Supongamos esta lista de alumnos con sus notas







Para la posición de cada uno de acuerdo al puntaje usamos la función JERARQUIA (RANK en la versión inglesa). En la celda C2 escribimos la fórmula =JERARQUIA(B2,puntaje) y la copiamos al resto de las celdas.
El argumento "puntaje" en la función es un rango dinámico definido en un nombre. La definición es =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1).

El objetivo del rango dinámico es permitir que la fórmula se vaya adaptando a medida que agregamos o quitamos alumnos de la lista.

Si observamos el resultado de la fórmula veremos que hay un problema





Ana y Enrique, al tener el mismo puntaje reciben el mismo número de posición. Ambos reciben la posición 10 y el próximo en la lista recibirá la posición 12.
Para solucionar este problema creamos un nuevo campo en la columna D, al que llamamos "posición sin empate", con la fórmula

=JERARQUIA(B2,puntaje)+CONTAR.SI($C$2:C2,C2)-1

Al ordenar la tabla en orden ascendente de "puntaje sin empate"




podemos ver las diferencias entre los resultados de ambas fórmulas.

Ahora podemos crear un cuadro que muestre los primeros diez alumnos del curso




En la columna F ponemos números (constantes) de 1 a 10.
En la columna G usamos la fórmula
=INDICE(alumno,COINCIDIR(F2,posicion_sin_empate,0)),
que usa los nombres

Alumno: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

Posición_sin_empate: =DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1,1)

En la columna H usamos, similarmente la fórmula
=INDICE(puntaje,COINCIDIR(F2,posicion_sin_empate,0))

El problema con esta tabla es que "deja afuera" a Enrique y a Pablo que tienen el mismo puntaje que Ana.

Lo que queremos lograr es una tabla que muestre todos los alumnos que comparten las primeras diez posiciones. En nuestro ejemplo hay 12 alumnos con los mejores diez puntajes.

Para solucionar este problema modificamos nuestra tabla y sus fórmulas:




1 – agregamos un campo (número de orden) con la fórmula
=SI(INDICE(posicion,COINCIDIR(FILA()-1,posicion_sin_empate,0))>10,"",FILA()-1)

2 – En la columna K usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_posicion,3,0))

3 - En la columna L usamos la fórmula
=SI(CELDA("contenido",J2)="","",INDICE(alumno,COINCIDIR(FILA()-1,posicion_sin_empate,0)))

4 - En la columna M usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_puntaje,2,0))

Todas estas fórmulas usan una función SI para condicionar el resultado. Si el valor en el campo "número de orden" nos es "blanco" las fórmulas darán el resultado buscado; en caso contrario el resultado es "blanco".

El número de líneas de esta tabla debe coincidir con el número de alumnos en el curso.

Estas fórmulas usan función CELDA que es volátil y también funciones de búsqueda INDICE, COINCIDIR y BUSCARV, con búsqueda exacta. En hojas con un gran número de alumnos esto puede causar que la recalculación sea lenta.

Una alternativa para esos casos es utilizar Tablas Dinámicas. Los pasos a dar son los siguientes:

1 – definimos un rango dinámico rango_
td: =DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),4)
para que nuestra tabla dinámica se adpate a los cambios en la lista de alumnos

2 – Ponemos el campo Alumnos en el área de filas; en el área de datos ponemos Posición y Puntaje.

3 - Abrimos el menú de configuración de campo




y apretamos el botón Avanzado

4 - en el diálogo de avanzado definimos Opciones de Autoordenar: ascendente; activamos la opción de mostrar los 10 valores inferiores; en ambas ventanillas de "Usar campo" elegimos "posición"




El resultado es



La ventaja de usar tablas dinámicas es que nos exime de escribir fórmulas complicadas y los cálculos son mucho más eficientes en términos de tiempo; la desventaja es que las posibilidades de formato son menores que en tablas normales de Excel.





Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Excel

Tablas Dinámicas en Excel – Gráficos

Excel nos permite crear un gráfico basado en una tabla dinámica con un solo clic. En nuestro ejemplo



Apretamos el icono de gráficos en la barra de herramientas de tablas dinámicas y obtenemos un gráfico en una nueva hoja



Si queremos presentar el gráfico en la misma hoja de la tabla dinámica, cambiamos su ubicación (clic en el botón derecho del mouse) a la hoja de la tabla






El gráfico esta ligado a la tabla dinámica de manera que todo cambio en la tabla afecta inmediatamente al gráfico, y viceversa. Como se puede ver, en el gráfico aparecen los mismos botones de campos que aparecen en la tabla.
Por ejemplo, si cambiamos la selección de departamentos en el gráfico para mostrar sólo los departamentos 1 y 2




al apretar Aceptar veremos los cambios también en la tabla dinámica



Los gráficos basados en tablas dinámicas son menos flexibles que los gráficos basados en tablas de datos corrientes de Excel. Si cambiamos formatos en el gráfico original (por ejemplo el color de una serie), al actualizar la tabla volverán a aparecer los formatos originales. Otra limitación es que ciertos tipos de gráficos no son permitidos. Si intentan convertir el gráfico a uno del tipo XY recibirán esta advertencia



Tampoco podemos crear un gráfico usando sólo parte de las celdas en la tabla dinámica. Aún cuando seleccionemos sólo un rango de la tabla, Excel generará un gráfico basado en todos los datos presentes en la tabla dinámica.

Para sobreponernos a estas limitaciones lo que tenemos que hacer, básicamente, es quebrar el vínculo entre el gráfico y la tabla dinámica. Esto lo podemos hacer de varias maneras:
1 – Seleccionar la tabla y usar Copiar—Pegado Especial—Valores para copiar los datos en formar estática en alguna otra ubicación. Luego a partir de estos datos generar el gráfico deseado. Si copiamos sólo una parte de la tabla, no hace falta usar Pegado Especial—Valores. También con Pegar (Ctrl+V) obtenemos datos estáticos.

2- Seleccionar toda la tabla, copiar (Ctrl+C) y luego Pegado Especial—Valores.

3 – Copiar el dato seleccionado de una tabla dinámica a un gráfico sin datos creado previamente.




Categorías: Manejo de Datos

Technorati Tags: Excel, Tablas Dinamicas en Excel