¿Odias la función GETPIVOTDATA de Excel? ¿Por qué aparece? ¿Cómo puedes prevenirlo? ¿Hay un buen uso para GETPIVOTDATA?
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 como = D5 / C5-1 sin 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.
Construya una tabla dinámica con Store in ROWS. Ponga el mes y escriba las 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 los 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 pequeña 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 se debe completar es enero, Actuals for 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 los datos reales de enero 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.
Aquí está la fórmula después de editarla. Atrás quedaron "Baybrook", "Jan" y "Actual". En cambio, apunta a $ D6, E $ 3, 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 anual:
- 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.
Cambiar celda P1
Debe admitir que el uso de un informe simple 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.
Ver video
- GetPivotData ocurre cuando una fórmula apunta dentro de una tabla dinámica
- Si bien la fórmula inicial es correcta, no puede copiar la fórmula
- La mayoría de las personas odian getpivotdata y quieren evitarlo.
- Método 1: cree una fórmula sin el mouse o las teclas de flecha
- Método 2: desactive GetPivotData permanentemente usando el menú desplegable junto a las opciones
- Pero hay un uso para GetPivotData
- Su gerente desea un informe con datos reales de los últimos meses y un presupuesto para el futuro.
- El flujo de trabajo normal le haría crear una tabla dinámica, convertir a valores, eliminar columnas
- Eliminación de subtotales para evitar que el plan real + de enero use la configuración de campo
- En su lugar, cree una tabla dinámica con "demasiados" datos
- Utilice una hoja de trabajo de informe con un formato agradable
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Desde la primera celda de datos en la hoja de trabajo, cree una fórmula con el mouse
- Permitir que suceda GetPivotData
- Examine la sintaxis de GetPivotData (campo para devolver, ubicación de pivote, pares)
- Cambiar el valor codificado para que apunte a una celda
- Presionar F4 tres veces bloquea solo la columna
- Presionar F4 dos veces bloquea solo la fila
- Pegar fórmulas especiales
- Flujo de trabajo el próximo mes: agregue datos, actualice la tabla dinámica, cambie la fecha
- Cuidado con las nuevas tiendas
Transcripción del video
Aprenda Excel del podcast, episodio 2013: ¡GetPivotData puede no ser completamente malvado!
Estaré publicando un podcast de este libro completo, haga clic en la "i" en la esquina superior derecha para suscribirse.
Muy bien, en el episodio de 1998 hablé brevemente sobre este problema de GetPivotData. Si calculamos un% de varianza y estamos fuera de la tabla dinámica apuntando hacia adentro, y uso el mouse o la tecla de flecha, entonces 2019 / 2018-1. Esta respuesta que vamos a obtener aquí es correcta para enero, pero cuando hacemos doble clic para copiarla, la fórmula no se copia, obtenemos la respuesta de enero completamente. Y cuando lo miramos, obtenemos GetPivotData, no escribí GetPivotData, solo señalé esas celdas, y esto comenzó a suceder en Excel 2002 sin ninguna advertencia. Y en ese momento dije que la forma de evitar esto es escribir la fórmula C5 / B5-1, y obtendrás una fórmula que puedes copiar. O si simplemente odia GetPivotData, si es "completamente malvado", vaya a la pestaña Analizar, not abre el botón Opciones por cierto. Vuelva a la tabla dinámica, vaya a la pestaña Analizar, abra el menú desplegable junto a Opciones, desmarque esta casilla, es una configuración global. Una vez que lo apagues, estará apagado para siempre, está bien.
La mayoría de las veces, las preguntas que recibo son "¿Cómo desactivo GetPivotData?" pero de vez en cuando conseguiré a alguien que ama GetPivotData. Y estaba almorzando con Rob Collie cuando todavía estaba en Microsoft, y dijo: "Bueno, a nuestros clientes internos les encanta GetPivotData". ¿Dije que? ¡No, todo el mundo odia GetPivotData! " Rob dice "Tienes razón, fuera de Microsoft, absolutamente, odian GetPivotData". Estoy hablando de los contadores dentro de Microsoft, y luego conocí a uno que ahora trabaja para el equipo de Excel, Carlos, y Carlos era uno de los contadores que usaba este método.
Muy bien, esto es lo que tenemos que hacer. Tenemos nuestro informe, un conjunto de datos aquí que para cada mes tenemos el plan para cada tienda, y luego en la parte inferior estamos acumulando datos reales. Muy bien, tenemos datos reales de enero a diciembre, pero solo tenemos datos reales de algunos meses, los meses que han pasado. Y lo que nuestro gerente quiere que hagamos es crear un informe con las tiendas en el lado izquierdo, solo las tiendas de Texas, por supuesto, para hacer la vida más difícil. Y luego, tenemos meses, y si tenemos un valor real para ese mes, mostramos el real, entonces el real de enero, el real de febrero, el real de marzo, el real de abril. Pero luego, para los meses en los que no tenemos datos reales, cambiamos y mostramos el presupuesto, así que presupuestamos hasta diciembre y luego un total total de todo, está bien. Bueno, cuando intentas crear esta tabla dinámica, sí,no funciona.
Así que inserte la tabla dinámica, Nueva hoja de trabajo, coloque Tienda en el lado izquierdo que es hermoso, coloque Meses en la parte superior, escriba Tipo en la parte superior, coloque Ventas aquí, de acuerdo. Entonces, esto es lo que obtenemos con lo que tenemos que comenzar a trabajar, por lo que tenemos el plan de enero real, el plan de enero y luego el plan de enero real plus completamente inútil. Nadie usará esto nunca, pero puedo deshacerme de estas columnas grises, eso es bastante fácil, algunos aquí en esta celda, vaya a Configuración de campo y cambie los Subtotales a Ninguno. Pero no tengo absolutamente ninguna forma de eliminar el plan de enero que no eliminará también el plan de abril, mayo, junio, julio, está bien, no hay forma de deshacerme de esto. Entonces, en este punto, todos los meses, estoy atascado seleccionando toda la tabla dinámica, yendo a Copiar y luego a Pegar, Pegar valores. Ya no es una tabla dinámica,y luego comienzo a eliminar manualmente las columnas que no aparecen en el informe.
Muy bien, ese es el método normal, pero los contadores de Microsoft agregaron un paso adicional en enero, toma 15 minutos, y este paso permite que esta tabla dinámica viva para siempre, ¿verdad? Yo llamo a esta la tabla dinámica más fea del mundo, y los contables de Microsoft aceptan que esta es la tabla dinámica más fea del mundo, pero nadie verá este informe excepto ellos. Lo que hacen es venir aquí a una nueva hoja y crear el informe que su gerente quiere. Muy bien, aquí están las tiendas en el lado izquierdo, incluso las agrupé en Houston, Dallas y Otros, es un informe muy bien formateado. He resaltado los totales, verá que cuando ingresamos algunos números, hay moneda en la primera fila, pero no en las filas siguientes, filas en blanco. Oh, filas en blanco en la tabla dinámica.Y un poquito de lógica aquí arriba, donde puedo poner la fecha de finalización en la celda P1, y luego tengo una fórmula aquí que analiza que SI el mes de la fecha de finalización es> esta columna, y luego poner la palabra Real, de lo contrario pon la palabra Plan, está bien. Así que todo lo que tengo que hacer es cambiar esto hasta la fecha, y luego la palabra Actual cambia al plan, Está bien.
Ahora, esto es lo que hacemos, vamos a permitirnos ser GetPivotData'd, ¿verdad? No estoy seguro de que sea un verbo, pero vamos a permitir que Microsoft obtenga GetPivotData. ¡Entonces empiezo a construir una fórmula con un =, agarro el mouse y voy a buscar el Baybrook real de enero! Así que vuelvo a la tabla dinámica más fea del mundo, encuentro Baybrook, encuentro enero, encuentro real, y hago clic en Entrar, y dejo que me lo hagan, está bien, ahí vamos, ahora tenemos una fórmula GetPivotData. Recuerdo el día que hice esto, fue como, ya sabes, después de que Rob me explicó lo que estaban haciendo, y volví y lo intenté. Ahora, de repente, toda mi vida, me he estado deshaciendo de GetPivotData, nunca he abrazado GetPivotData. Entonces, lo que es, el primer elemento es lo que estamos buscando, ahí 'En un campo llamado Ventas, aquí es donde comienza la tabla dinámica, y puede ser cualquier celda de la tabla dinámica, usan la parte superior izquierda.
Muy bien, este es un nombre de campo "Tienda", y luego han codificado "Baybrook", este es un nombre de campo "Mes", han codificado "Enero", este es un nombre de campo "Tipo", y ' he codificado "Actual". Por eso no puede copiarlo, porque han codificado los valores. Pero los contadores de Microsoft, Carlos y sus compañeros de trabajo se dan cuenta de que “Espera un segundo, aquí tenemos la palabra Baybrook, aquí tenemos enero, aquí tenemos Actual. Solo tenemos que cambiar esta fórmula para que apunte a las celdas reales del informe en lugar de estar codificadas ". Muy bien, a esto lo llaman parametrizar GetPivotData.
Elimina la palabra Baybrook, ven aquí y haz clic en la celda D6. Ahora, necesito bloquear esto en la columna, está bien, entonces presiono la tecla F4 3 veces, obtengo un solo $ antes de la D, está bien. Para el mes de enero, elimino el enero codificado, hago clic en la celda E3, presionaré F4 dos veces para bloquearlo en la fila, E $ 3. Escriba Actual, elimine la palabra Actual, haga clic en E4, nuevamente F4 dos veces, está bien, y obtengo una fórmula que ahora recupera esos datos. Voy a copiar eso, y luego Pegar especial, elegir Formatos, alt = "" ESF, veo que la F está subrayada allí, ESF Enter, y luego, ya lo hice, lo repetiré con F4, F4 es un rehacer y F4. Muy bien, ahora tenemos un informe atractivo, tiene espacios en blanco, tiene formato, tiene un solo subrayado contable debajo de cada sección,en la parte inferior tiene el subrayado de doble contabilidad.
Bien, nunca obtienes estas cosas en una tabla dinámica, eso es imposible, pero este informe se genera desde la tabla dinámica. Entonces, lo que hacemos cuando obtenemos los datos reales de mayo, volver aquí, pegarlos, actualizar la tabla dinámica más fea del mundo, y luego, aquí en el informe, simplemente cambiar la fecha de finalización del 30/04 al 31/05. Y lo que hace es que esta fórmula cambie de la palabra Plan a Actual, que va y extrae los datos reales del informe, en lugar del plan, está bien. Ahora, esto es lo que es genial, ¿verdad? Puedo ver dónde haría mucho esto si todavía, ya sabes, trabajara en contabilidad.
Lo que debe tener mucho cuidado es que si construyen una nueva tienda, debe saber agregarla manualmente, correcto, los datos se mostrarán en la tabla dinámica, pero lo agregaría manualmente. Ahora bien, este es un subconjunto de todas las tiendas, si estuviera informando todas las tiendas, probablemente aquí, fuera del rango de impresión, tendría algo que sacara el total general de la tabla dinámica. Y luego sabría, si este total no coincide con el total general de la tabla dinámica, que algo está mal y tiene una función SI aquí abajo que dice “Oye, ya sabes, se han agregado nuevos datos, ten mucho cuidado. " Tienen algún tipo de mecanismo para detectar que hay nuevos datos. Pero lo entiendo, es un uso genial. Entonces, aunque la mayoría de las veces GetPivotData simplemente nos vuelve locos, en realidad puede ser útil. Bien,así que ese es el consejo # 21 de los 40 en el libro, compre el libro ahora mismo, ordene en línea, haga clic en la "i" en la esquina superior derecha.
Recapitulación larga, larga hoy, de acuerdo: GetPivotData ocurre cuando una fórmula apunta dentro de una tabla dinámica, una fórmula fuera de la tabla dinámica apunta hacia adentro. Si bien la fórmula inicial es correcta, no se copiará. La mayoría de la gente odia GetPivotData y quiere evitarlo. Entonces puede crear una fórmula sin el mouse o las teclas de flecha, simplemente escriba la fórmula o apague GetPivotData permanentemente, ah, pero hay un uso, está bien. Así que tenemos que crear un informe con datos reales del mes pasado y presupuesto para el futuro. Flujo de trabajo normal, crear una tabla dinámica, convertir a valores, eliminar columnas. Hay una manera de eliminar los subtotales mediante la configuración de campo, deshaciéndose de ese plan plus real de enero. En su lugar, solo crearemos la tabla dinámica más fea del mundo con demasiados datos.
Cree una hoja de trabajo de informe antigua y bien formateada con tal vez un poco de lógica para cambiar la palabra Real a Plan. Y luego, desde la primera celda del informe, el primer lugar donde van a estar los números en ese informe, escriba un =, vaya a la tabla dinámica y permita que GetPivotData suceda. Examinamos la sintaxis de GetPivotData, por lo que es el campo para devolver, Ventas, donde vive la tabla dinámica, y luego los pares de criterios, el nombre del campo y el valor. Vamos a eliminar el valor codificado y apuntar a una celda, presionando F4 3 veces bloquea solo la columna, presionando F4 2 veces bloquea solo la fila, copia esa fórmula, Pega fórmulas especiales. Agregué un consejo adicional de que F4 es un rehacer, por lo que solo tuve que ir al cuadro de diálogo Pegado especial una vez, y luego, para las siguientes fórmulas de Pegado especial, solo usé F4. El mes que viene agregue los datos,actualice la tabla dinámica, cambie la fecha de finalización. Asegúrese de que no hayan construido tiendas nuevas, ya sabe, tengan algún tipo de mecanismo, ya sea manual o una fórmula de verificación, compruébelo. Gracias a iTrainerMX en Twitter, quien sugirió GetPivotData, también Carlos y Rob de Microsoft, Rob ahora de Power Pivot Pro. Carlos por usar esto, y Rob por decirme que Carlos lo estaba usando, conocí a Carlos más tarde, y me confirmó que sí, era uno de los contadores que usaba esto todo el tiempo en Microsoft, está bien, ahí lo tienes.y Rob por decirme que Carlos lo estaba usando, conocí a Carlos más tarde, y me confirmó que sí, era uno de los contadores que usaba esto todo el tiempo en Microsoft, está bien, ahí lo tienes.y Rob por decirme que Carlos lo estaba usando, conocí a Carlos más tarde, y me confirmó que sí, era uno de los contadores que usaba esto todo el tiempo en Microsoft, está bien, ahí lo tienes.
Bueno, oye, quiero darte las gracias por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2013.xlsx
