La mayoría de las personas se encuentran por primera vez con GETPIVOTDATA cuando intentan crear una fórmula fuera de una tabla dinámica que usa números en la tabla dinámica. Por ejemplo, este porcentaje de variación no se copiará a los otros meses debido a que Excel inserta funciones GETPIVOTDATA.
Excel inserta GETPIVOTDATA cada vez que utiliza el mouse o las teclas de flecha para señalar una celda dentro de la tabla dinámica mientras crea una fórmula fuera de la tabla dinámica.
Por cierto, si no desea que aparezca la función GETPIVOTDATA, simplemente escriba una fórmula, por ejemplo, =D5/C5-1sin usar el mouse o las teclas de flecha para señalar las celdas. Esa fórmula se copia sin problemas.
Aquí hay un conjunto de datos que contiene un número de plan por mes por tienda. También hay ventas reales por mes por tienda para los meses que se completan. Su objetivo es crear un informe que muestre los datos reales de los meses completados y planificar los meses futuros.
Cree una tabla dinámica con Store in Rows. Coloque el mes y el tipo en columnas. Obtiene el informe que se muestra a continuación, con el plan enero real, el plan enero y el plan enero real + completamente absurdo.
Si selecciona una celda de mes y va a Configuración de campo, puede cambiar Subtotales a Ninguno.
Esto elimina el Plan Actual + inútil. Pero aún debe deshacerse de las columnas del plan de enero a abril. No hay una buena forma de hacer esto dentro de la tabla dinámica.
Entonces, su flujo de trabajo mensual se convierte en:
- Agregue los datos reales del nuevo mes al conjunto de datos.
- Construya una nueva tabla dinámica desde cero.
- Copie la tabla dinámica y péguela como valores para que ya no sea una tabla dinámica.
- Elimina las columnas que no necesitas.
Hay una mejor manera de hacerlo. La siguiente figura muy comprimida muestra una nueva hoja de cálculo de Excel agregada al libro de trabajo. Todo esto es simplemente Excel, sin tablas dinámicas. La única magia es una función SI en la fila 4 que cambia de Real a Plan, según la fecha en la celda P1.
La primera celda que debe llenarse es January Actual para Baybrook. Haga clic en esa celda y escriba un signo igual.
Usando el mouse, navegue de regreso a la tabla dinámica. Busque la celda de enero real para Baybrook. Haga clic en esa celda y presione Entrar. Como de costumbre, Excel crea una de esas molestas funciones GETPIVOTDATA que no se pueden copiar.
Pero hoy, estudiemos la sintaxis de GETPIVOTDATA.
El primer argumento a continuación es el campo numérico "Ventas". El segundo argumento es la celda donde reside la tabla dinámica. Los pares de argumentos restantes son nombre de campo y valor. ¿Ves lo que hizo la fórmula generada automáticamente? Codificó "Baybrook" como el nombre de la tienda. Es por eso que no puede copiar estas fórmulas GETPIVOTDATA generadas automáticamente. En realidad, codifican nombres en fórmulas. Aunque no puede copiar estas fórmulas, puede editarlas. En este caso, sería mejor si editara la fórmula para que apunte a la celda $ D6.
La siguiente figura muestra la fórmula después de editarla. Atrás quedaron "Baybrook", "Jan" y "Actual". En cambio, apunta a $ D6, E $ 3 y E $ 4.
Copie esta fórmula y luego elija Pegado especial, fórmulas en todas las demás celdas numéricas.
Ahora aquí está su flujo de trabajo mensual:
- Construya una tabla dinámica fea que nadie verá nunca.
- Configure la hoja de trabajo del informe.
Cada mes tienes que:
- Pegue nuevos datos reales debajo de los datos.
- Actualiza la fea tabla dinámica.
-
Cambie la celda P1 en la hoja del informe para reflejar el nuevo mes. Todos los números se actualizan.
Debe admitir que el uso de un informe que extrae números de una tabla dinámica le brinda lo mejor de ambos mundos. Puede formatear el informe de forma que no pueda formatear una tabla dinámica. Las filas en blanco están bien. Puede tener símbolos de moneda en la primera y última fila, pero no en el medio. También obtiene doble subrayado debajo de los totales generales.
Gracias a @iTrainerMX por sugerir esta función.
