Convertir celdas de tabla dinámica en fórmulas de hoja de cálculo

Una tabla dinámica tiene varios diseños que proporcionan una estructura predefinida al informe, pero no se pueden personalizar. Si necesita más flexibilidad al 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, después, 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 usen 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.

Cuando convierte celdas en fórmulas, estas fórmulas acceden a los mismos datos que la tabla dinámica y se pueden actualizar para ver resultados actualizados. Sin embargo, con la posible excepción de los filtros de informe, ya no tendrá acceso a las características interactivas de una tabla dinámica, como filtrar, ordenar o expandir y contraer niveles.

Obtenga información sobre escenarios comunes para convertir tablas dinámicas en fórmulas de hoja de cálculo

Estos son ejemplos típicos de lo que puede hacer después de convertir las celdas de una tabla dinámica en fórmulas de hoja de cálculo para personalizar el diseño de las celdas convertidas.

Reorganizar y eliminar celdas 

Supongamos que tiene un informe periódico que necesita crear cada mes para su personal. Solo necesita un subconjunto de la información del informe y prefiere presentar los datos de forma personalizada. Puede mover y organizar las celdas en el diseño que desee, eliminar las celdas que no son necesarias para el informe mensual de personal y, a continuación, dar formato a las celdas y la hoja de cálculo según sus preferencias.

Insertar filas y columnas 

Supongamos que quiere mostrar información de ventas de los dos años anteriores desglosada por región y grupo de productos, y que quiere 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 que desea y, a continuación, rellenar la columna hacia abajo para obtener los resultados de cada fila.

Usar varios orígenes de datos 

Suponga que desea comparar los resultados entre una base de datos de producción y una base de datos de prueba para asegurarse de que la base de datos de prueba produce los resultados esperados. Puede copiar fácilmente fórmulas de celda y, a continuación, 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 quiere que todo el informe cambie en función de la entrada del usuario. Podría cambiar los argumentos de las fórmulas del cubo por referencias de celda en la hoja de cálculo y, a continuación, escribir valores diferentes 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 y una columna de 2009 denominada Ventas proyectadas, pero no quiere 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 fórmulas de cubo propias y expresiones MDX 

Supongamos que quiere crear un informe que muestre las ventas de un producto determinado de tres vendedores específicos para el mes de julio. Si tiene conocimientos sobre las expresiones MDX y las 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.

Convertir las celdas en fórmulas que usan funciones de cubo

1. Para guardar la tabla dinámica para usarla en el futuro, se recomienda hacer una copia del libro antes de convertirla. Para ello, haga clic en Guardar archivo>como. Para obtener más información, consulte Guardar un archivo.

2. Prepare la tabla dinámica para minimizar el reordenamiento de las celdas después de la conversión haciendo lo siguiente:

   • Cambie a un diseño que se parezca lo más posible al diseño que desea.
   • Interactúe con el informe, como filtrar, ordenar y rediseñar el informe, para obtener los resultados que desea.

3. Haga clic en la tabla dinámica.

4. En la pestaña Opciones , en el grupo Herramientas , haga clic en Herramientas OLAP y, después, 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 varios filtros de informe, se muestra el cuadro de diálogo Convertir en fórmulas .

5. Decida cómo quiere convertir la tabla dinámica:
   Convertir toda la tabla dinámica 

   • Active la casilla Convertir filtros de informe .
     De esta forma, todas las celdas se convertirán en fórmulas de hoja de cálculo y se eliminará toda la tabla dinámica.
     Convertir solo las etiquetas de fila, las etiquetas de columna y el área de valores de la tabla dinámica, pero mantenga los filtros de informe 

   • Asegúrese de que la casilla Convertir filtros de informe esté desactivada. (Esta es la opción predeterminada).
     Esto convierte todas las celdas de etiquetas de fila, etiquetas de columna y áreas de 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 la tabla dinámica es la versión 2000-2003 o anterior, sólo puede convertir toda la tabla dinámica.

6. 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 convertir en otro momento, presione ESC para cancelar la operación.

   Nota

   • No puede convertir celdas con filtros aplicados a niveles que están ocultos.
   • No puede convertir las celdas en las que los campos tienen un cálculo personalizado que se crearon 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, después, haga clic en Configuración de campo de valores).
   • Para las celdas que se convierten, se conserva el formato de celda, pero se eliminan los estilos de tabla dinámica porque estos estilos solo se pueden aplicar a tablas dinámicas.

Convertir celdas con la función IMPORTARDATOSDINAMICOS

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 no OLAP, cuando prefiera no actualizar inmediatamente al formato de la nueva versión de tabla dinámica 2007 o cuando desee evitar la complejidad de usar las funciones de cubo.

1. 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 de tabla dinámica para referencias de tablas dinámicas en la categoría Fórmulas de la sección Trabajo con fórmulas del cuadro de diálogo Opciones de Excel .

2. En la tabla dinámica, asegúrese de que la celda que desea usar en cada fórmula está visible.

3. En una celda de la hoja de cálculo situada fuera de la tabla dinámica, escriba la fórmula que desee hasta el punto en el que quiera incluir datos del informe.

4. Haga clic en la celda de la tabla dinámica que desea usar en la fórmula de 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 continúa recuperando los datos correctos si cambia el diseño del informe o si actualiza los datos.

5. Termine de escribir la fórmula y presione ENTRAR.

Problema: no se pueden convertir celdas de tabla dinámica en fórmulas de hoja de cálculo