Una tabla dinámica tiene varios diseños que proporcionan una estructura predefinida al informe, pero no puede personalizar estos diseños. Si necesita más flexibilidad para diseñar el diseño de un informe de tabla dinámica, puede convertir las celdas en fórmulas de hoja de cálculo y, a continuación, cambiar el diseño de estas celdas aprovechando al máximo todas las características disponibles en una hoja de cálculo. Puede convertir las celdas en fórmulas que usan funciones de cubo o usar la función IMPORTARDATOSDINAMICOS. Convertir celdas en fórmulas simplifica enormemente el proceso de creación, actualización y mantenimiento de estas tablas dinámicas personalizadas.
Al convertir celdas en fórmulas, estas fórmulas obtienen acceso a los mismos datos que la tabla dinámica y se pueden actualizar para ver los resultados actualizados. Sin embargo, con la posible excepción de los filtros de informe, ya no tiene acceso a las características interactivas de una tabla dinámica, como filtrar, ordenar o expandir y contraer niveles.
Nota: Al convertir una tabla dinámica de procesamiento analítico en línea (OLAP), puede seguir actualizando los datos para obtener valores de medida actualizados, pero no puede actualizar los miembros reales que se muestran en el informe.
Más información sobre escenarios comunes para convertir tablas dinámicas en fórmulas de hoja de cálculo
A continuación se muestran ejemplos típicos de lo que puede hacer después de convertir celdas de tabla dinámica en fórmulas de hoja de cálculo para personalizar el diseño de las celdas convertadas.
Reorganizar y eliminar celdas
Supongamos que tiene un informe periódico que debe crear cada mes para el personal. Solo necesita un subconjunto de la información del informe y prefiere disponer los datos de forma personalizada. Solo tiene que mover y organizar las celdas en un diseño de diseño que desee, eliminar las celdas que no son necesarias para el informe mensual de personal y, después, dar formato a las celdas y la hoja de cálculo según sus preferencias.
Insertar filas y columnas
Supongamos que desea mostrar la información de ventas de los dos años anteriores desglosada por región y grupo de productos, y que desea insertar comentarios extendidos en filas adicionales. Solo tiene que insertar una fila y escribir el texto. Además, desea agregar una columna que muestre las ventas por región y grupo de productos que no está en la tabla dinámica original. Solo tiene que insertar una columna, agregar una fórmula para obtener los resultados deseados y rellenar la columna hacia abajo para obtener los resultados de cada fila.
Usar varios orígenes de datos
Supongamos que desea comparar los resultados entre una base de datos de producción y de prueba para asegurarse de que la base de datos de prueba genera los resultados esperados. Puede copiar fácilmente fórmulas de celda y luego cambiar el argumento de conexión para que apunte a la base de datos de prueba para comparar estos dos resultados.
Usar referencias de celda para variar la entrada del usuario
Supongamos que desea que el informe completo cambie en función de la entrada del usuario. Puede cambiar los argumentos de las fórmulas de Cubo por referencias de celda en la hoja de cálculo y, a continuación, escribir diferentes valores en esas celdas para obtener resultados diferentes.
Crear un diseño de fila o columna no uniforme (también denominado informe asimétrico)
Supongamos que necesita crear un informe que contenga una columna de 2008 denominada Ventas reales, una columna de 2009 denominada Ventas proyectadas, pero no desea ninguna otra columna. Puede crear un informe que contenga solo esas columnas, a diferencia de una tabla dinámica, que requiere informes simétricos.
Crear sus propias fórmulas de Cubo y expresiones MDX
Supongamos que desea crear un informe que muestre las ventas de un producto determinado por tres vendedores específicos para el mes de julio. Si tiene conocimientos sobre expresiones MDX y consultas OLAP, puede escribir las fórmulas del cubo usted mismo. Aunque estas fórmulas pueden llegar a ser bastante elaboradas, puede simplificar la creación y mejorar la precisión de estas fórmulas mediante Fórmula Autocompletar. Para obtener más información, consulte Usar Fórmula Autocompletar.
Nota: Solo puede convertir una tabla dinámica de procesamiento analítico en línea (OLAP) mediante este procedimiento.
-
Para guardar la tabla dinámica para su uso futuro, le recomendamos que haga una copia del libro antes de convertir la tabla dinámica haciendo clic en Archivo > Guardar como. Para obtener más información, vea Guardar un archivo.
-
Prepare la tabla dinámica para que pueda minimizar la reorganización de las celdas después de la conversión haciendo lo siguiente:
-
Cambie a un diseño que se parezca más al diseño que desee.
-
Interactúe con el informe (como filtrar, ordenar y rediseñar el informe) para obtener los resultados que desee.
-
-
Haga clic en la tabla dinámica.
-
En la pestaña Opciones , en el grupo Herramientas , haga clic en Herramientas OLAP y, a continuación, haga clic en Convertir en fórmulas.
Si no hay filtros de informe, la operación de conversión se completa. Si hay uno o más filtros de informe, se mostrará el cuadro de diálogo Convertir en fórmulas .
-
Decida cómo desea convertir la tabla dinámica:
Convertir toda la tabla dinámica
-
Active la casilla Convertir filtros de informe .
Esto convierte todas las celdas en fórmulas de hoja de cálculo y elimina toda la tabla dinámica.
Convertir solo las etiquetas de fila de tabla dinámica, las etiquetas de columna y el área de valores, pero mantener los filtros de informe
-
Asegúrese de que la casilla Convertir filtros de informe está desactivada. (Este es el valor predeterminado).
Esto convierte todas las celdas del área de etiquetas de fila, columna y valores en fórmulas de hoja de cálculo y mantiene la tabla dinámica original, pero solo con los filtros de informe para que pueda seguir filtrando con los filtros de informe.
Nota: Si el formato de tabla dinámica es la versión 2000-2003 o anterior, solo puede convertir toda la tabla dinámica.
-
-
Haga clic en Convertir.
La operación de conversión actualiza primero la tabla dinámica para asegurarse de que se usan datos actualizados.
Se muestra un mensaje en la barra de estado mientras se realiza la operación de conversión. Si la operación tarda mucho tiempo y prefiere convertirla en otro momento, presione ESC para cancelar la operación.
Notas:
-
No puede convertir celdas con filtros aplicados a niveles ocultos.
-
No puede convertir las celdas en las que los campos tienen un cálculo personalizado creado a través de la pestaña Mostrar valores como del cuadro de diálogo Configuración de campo de valores. (En la pestaña Opciones , en el grupo Campo activo , haga clic en Campo activo y, a continuación, haga clic en Configuración de campo de valores).
-
Para las celdas que se convierten, el formato de celda se conserva, pero los estilos de tabla dinámica se quitan porque estos estilos solo se pueden aplicar a las tablas dinámicas.
-
Puede usar la función IMPORTARDATOSDINAMICOS en una fórmula para convertir celdas de tabla dinámica en fórmulas de hoja de cálculo cuando desee trabajar con orígenes de datos que no sean OLAP, cuando prefiera no actualizar inmediatamente al nuevo formato de tabla dinámica versión 2007 o cuando desee evitar la complejidad del uso de las funciones del cubo.
-
Asegúrese de que el comando Generar IMPORTARDATOSDINAMICOS del grupo Tabla dinámica de la pestaña Opciones está activado.
Nota: El comando Generar IMPORTARDATOSDINAMICOS establece o desactiva la opción Usar funciones IMPORTARDATOSDINAMICOS para referencias de tabla dinámica en la categoría Fórmulas de la sección Trabajar con fórmulas del cuadro de diálogo Opciones de Excel .
-
En la tabla dinámica, asegúrese de que la celda que desea usar en cada fórmula es visible.
-
En una celda de la hoja de cálculo fuera de la tabla dinámica, escriba la fórmula que desee hasta el punto en el que desea incluir los datos del informe.
-
Haga clic en la celda de la tabla dinámica que desea usar en la fórmula en la tabla dinámica. Se agrega una función de hoja de cálculo IMPORTARDATOSDINAMICOS a la fórmula que recupera los datos de la tabla dinámica. Esta función sigue recuperando los datos correctos si cambia el diseño del informe o si actualiza los datos.
-
Termine de escribir la fórmula y presione ENTRAR.
Nota: Si quita del informe cualquiera de las celdas a las que se hace referencia en la fórmula IMPORTARDATOSDINAMICOS, la fórmula devuelve #REF!.
