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

Tablas Dinámicas - Función IMPORTARDATOSDINAMICOS (GetPivotData)

Si queremos crear una referencia a una de las celdas de la tabla dinámica fuera de ella, Excel utiliza automáticamente la función IMPORTARDATOSDINAMICOS (GETPIVOTDATA en la versión inglesa).
Por ejemplo, si queremos crear una referencia al total de ventas del 2005 en la celda A12




El objetivo de IMPORTARDATOSDINAMICOS (GetPivotData) es extraer datos de la tabla dinámica, basados en los argumentos de la función, cuya sintaxis es

IMPORTARDATOSDINAMICOS(campo_datos,tabla_dinámica,campo1,elemento1,campo2,elemento2,…)

Por lo general queremos crear la referencia a la celda sin el uso de esta función. Existen dos maneras de hacer esto:

1 - Crear la referencia manualmente, es decir seleccionar A12 y escribir "=B10" en la barra de las fórmulas

2 – Cancelar la opción "generar getpivotdata". Esta opción es poco conocida. Existe un icono en que nos permite activar o desactivar la opción. Para instalar el icono hacemos lo siguiente:

a. Abrimos el menú Herramientas—Personalizar
b. En la pestaña Comandos elegimos Datos. Al final de la lista aparece el icono Generar Getpivodata



c. Arrastramos el icono a alguna de las barras de herramientas (lo más lógico es instalarlo en la barra de Tablas Dinámicas)



Este icono funciona como un interruptor. Con un clic desactivamos la opción y al crear una referencia veremos sólo la dirección de la celda



Un detalle a notar es que referencias directas heredan el formato de la celda de referencia; referencias con Getpivotdata reciben el formato "General".



Categorìas: Manejo de Datos_

Technorati Tags: Excel, Tablas Dinamicas en Excel