Para mostrar valores específicos en una tabla dinámica en Microsoft Excel, use uno de los muchos filtros integrados, como Top 10.
En un artículo anterior de TechRepublic, Cómo resaltar los n valores superiores en una hoja de Microsoft Excel , explico dos métodos de formato condicional diferentes para resaltar los valores n superiores en un conjunto de datos en Microsoft Excel . Cualquiera de los dos es razonable cuando desea ver todos los datos. Cuando desee ver solo los n registros superiores , filtrando todos los demás registros, necesitará una estrategia diferente.
Afortunadamente, Microsoft Excel tiene un filtro incorporado para las tablas dinámicas que le permitirá mostrar el registro n superior (o inferior) . En este artículo, creará una tabla dinámica simple y luego usará el filtro incorporado para mostrar solo los 10 registros principales en la fuente de datos. Luego, discutiremos algunos problemas con los resultados y las posibles soluciones. Es esta última sección del artículo la que podría proporcionar el material más nuevo para algunos de ustedes.
Estoy usando Office 365 (escritorio) , pero puede usar versiones anteriores del formato de cinta. Puede trabajar con sus propios datos o descargar el archivo de demostración .xlsx (este artículo no se aplica a la versión anterior de .xls). La edición del navegador mostrará la tabla dinámica, pero no puede ejecutar el código en el navegador.
Cómo construir una tabla dinámica en Excel
Esta sección es un tutorial sobre cómo crear una tabla dinámica. Si sabe cómo construir uno, puede omitir esta sección. Para generar la tabla dinámica de demostración utilizando el conjunto de datos de la figura A , haga lo siguiente:
1.-Haga clic en cualquier lugar dentro del conjunto de datos y luego haga clic en la pestaña Insertar. En el grupo Tablas, haga clic en Tabla dinámica.
2.-En el cuadro de diálogo resultante, haga clic en Aceptar; no necesitamos cambiar ninguna configuración predeterminada.
3.-Haga clic dentro del marco de la tabla dinámica. En el panel de lista de campos de tabla dinámica, arrastre el nombre del producto a la sección Filas. Arrastre el Precio unitario a la sección Valores ( Figura B ). El marco se actualizará en consecuencia a medida que agregue campos.
Figura A
Figura B
De forma predeterminada, la tabla dinámica suma los valores del precio unitario por productos, pero nuestra lista de productos es única, por lo que ninguno de los valores cambia. Para formatear la columna de precio unitario, haga clic con el botón derecho en la celda del encabezado y elija Formato de número en el submenú resultante. (Elegí la moneda).
Tal como está, la tabla dinámica muestra todos los registros. Veamos nuestro primer método para mostrar los principales valores de precio unitario n .
Cómo usar la opción incorporada
Aplicar un filtro es fácil y hay muchas opciones. La desventaja es que no son dinámicos, pero lo discutiremos en un momento. Ahora, apliquemos ese filtro:
1.-Haga clic en el filtro desplegable Etiquetas de fila.
2.-Elija los filtros de valor (recuerde, estamos evaluando los valores del precio unitario, no los productos), y luego elija los 10 principales ( Figura C ). No hay un menú desplegable para la columna de precio unitario porque es una columna de valores. Hay muchas opciones, por lo que querrás explorarlas más adelante.
3.-El cuadro de diálogo resultante tiene varias opciones, pero la configuración predeterminada ( Figura D ) es perfecta para nuestro ejemplo: mostrar los 10 elementos principales en la columna Suma del precio unitario.
4.-Haga clic en Aceptar para ver los resultados en la figura E .
Figura C
Figura D
Figura E
Puede usar la tabla dinámica resultante como está, pero hay algunos problemas que quizás desee considerar primero. Echemos un vistazo a continuación.
Solucionando algunos problemas pequeños
Ahora, modifiquemos un poco los resultados. En este caso, la fila de totalización en la parte inferior no es significativa, por lo que puede desactivarla. Para hacerlo, haga clic en la pestaña Diseño contextual. En el grupo Diseño, haga clic en el menú desplegable Totales generales y seleccione Desactivado para Filas y Columnas.
Excel ordena por la etiqueta de la fila (productos) para que pueda agrupar y sumar múltiples valores correctamente. En este caso, es innecesario y el tipo de producto no es particularmente significativo. Lo más probable es que desee ordenar por precio unitario. Para hacerlo, haga clic con el botón derecho en cualquier celda del precio unitario, elija Ordenar y luego elija Ordenar de mayor a menor. El texto del encabezado no es descriptivo. Para cambiarlo, simplemente haga clic dentro de la celda y reemplace los encabezados predeterminados, o no. Si decide ocultar la flecha desplegable, puede omitir este paso.
Lo más probable es que no quieras que los espectadores cambien el filtro. Existen algunos métodos complejos para hacerlo, pero lo más fácil es eliminar el menú desplegable del filtro. Para hacerlo, haga clic en cualquier lugar dentro de la tabla dinámica y haga clic en la pestaña Análisis de tabla dinámica contextual. Luego, haga clic en el menú desplegable Opciones en el grupo Tabla dinámica (en el extremo izquierdo) y elija Opciones. En el cuadro de diálogo resultante, haga clic en la pestaña Pantalla y desactive la opción Mostrar subtítulos del campo y desplegar filtros. Hacerlo inhibirá tanto el texto del encabezado como el menú desplegable, lo cual no es realmente un problema. Simplemente oculte la fila y agregue el texto o título apropiado sobre la tabla dinámica. (Esta opción no se usa en el archivo de demostración).
Si no está familiarizado con las tablas dinámicas, este es un comportamiento importante que debe recordar: no se actualizan automáticamente. Si cambia un valor en los datos de origen que afectaría a las 10 tablas dinámicas principales, la tabla dinámica no mostrará ese cambio hasta que lo actualice. Pero hay ayuda, si no una solución perfecta.
Una solución no tan dinámica
Para actualizar una tabla dinámica, haga clic con el botón derecho en cualquier celda y elija Actualizar. Sin embargo, los espectadores no sabrán hacer esto, por lo que la pantalla puede estar detrás de las actualizaciones. La solución más simple es forzar a Excel a actualizar las tablas dinámicas cuando alguien abre el libro. Puede hacerlo de la siguiente manera:
1.-Haga clic en cualquier lugar dentro de la tabla dinámica y luego haga clic en la pestaña contextual Análisis de tabla dinámica.
2.-En el grupo Tabla dinámica (en el extremo izquierdo), haga clic en el menú desplegable Opciones (debajo del cuadro Nombre de tabla dinámica) y elija Opciones en la lista desplegable.
3.-En el cuadro de diálogo resultante, haga clic en la pestaña Datos.
4.-Verifique los datos de actualización al abrir la opción de archivo ( Figura F ).
5.-Haga clic en Aceptar.
Figura F
Al elegir esta opción, los espectadores ven la información más actualizada, pero tienen que abrir el archivo, por lo que no es una solución perfecta para el problema. (Esta opción no está establecida en el archivo de demostración para que pueda experimentar el comportamiento por sí mismo.) Tal vez una mejor solución es el procedimiento de evento simple en la inclusión de una .
Listado A
Private Sub Worksheet_Deactivate ()
ThisWorkbook.RefreshAll
End Sub
Agregue este procedimiento al módulo de hoja adjunto a la fuente de datos. Cuando actualiza los datos y luego deja esa hoja, el método Desactivar activa un método de actualización. Esta solución fácil viene con algunas limitaciones. Primero, la tabla dinámica y la fuente de datos deben estar en diferentes hojas. Además, el método RefreshAll actualiza todo: todas las tablas dinámicas, consultas, etc. Si desea limitar la actualización a la tabla dinámica, utilice el siguiente método de actualización:
sheetname .PivotTables (» pivottablename «) .PivotCache.Refresh
Asegúrese de actualizar el nombre de la hoja y el nombre de la tabla dinámica en consecuencia cuando aplique a su propio trabajo. El archivo de demostración contiene el código, pero está comentado. Cuando aplique código a su propio trabajo, no copie desde esta página web porque el Editor de Visual Basic (VBE) no podrá compilar caracteres invisibles. Ingrese el código usted mismo o copie el código en Word (u otro editor de texto) y luego copie el código desde allí en el VBE. Además, si agrega el código a su libro de trabajo, asegúrese de guardar el archivo como un libro de trabajo habilitado para macros (.xlsm).