Editar valores de tabla dinámica

En esta publicación, hablaremos sobre cómo editar el valor en una celda de tabla dinámica. ¿¡Esperar lo!? He estado usando PT durante mucho tiempo y siempre recibo un error cuando intento editar el valor en una celda de la tabla dinámica. Sí, hemos podido editar las celdas de etiqueta , pero no las celdas de valor . En esta publicación, editaremos una celda de valor de PT y los totales de PT se actualizarán en consecuencia.

Objetivo

Antes de llegar a ello, analicemos nuestro objetivo. Disponemos de un PT básico. Nos gustaría agregar una columna de Entrada para que las personas puedan ingresar valores en el PT. Queremos que nuestros totales de PT se actualicen para incluir los valores ingresados. Por ejemplo, llega el momento de pagar nuestro bono trimestral. El bono es discrecional y no está sujeto a una ecuación fija. Por lo tanto, debemos permitir que un administrador ingrese un monto de bonificación arbitrario. Como pauta general, el gerente necesita ver los salarios y comisiones totales del último trimestre al determinar el monto del bono. Entonces, exportamos los datos de la nómina y los almacenamos en una tabla. Luego, creamos una tabla dinámica para proporcionar los totales. Se parece un poco a esto:

Ahora, nos gustaría agregar una columna de Bonificación para que nuestro gerente pueda escribir los montos de la bonificación directamente en el PT… Y… el Gran Total debe incluir los valores de la bonificación. Como esto:

Podemos hacer esto con un PT tradicional y, como nota rápida, me gustaría compartir cómo encontré esta capacidad. Estaba mirando el cuadro de diálogo Opciones de tabla dinámica y leyendo sobre cada casilla de verificación individual. (Totalmente nerd, lo sé). De todos modos, vi una opción llamada “Análisis de hipótesis, habilitar la edición de celdas en el área de valores” y pensé, ¿qué? Entonces comencé a investigar. Hice una búsqueda en la web que me llevó a mrexcel.com (gracias Bill), contexturesblog.com (gracias Deb), microsoft.com (gracias Microsoft) y otros. Y lo que aprendí fue muy interesante.

Hasta donde puedo decir, esa casilla de verificación específica está deshabilitada excepto en circunstancias limitadas, incluida la versión de Excel y la fuente de datos, y ahora se incluye por motivos de compatibilidad. Pero, durante mi búsqueda, descubrí que hay otra forma de permitir que un usuario ingrese valores en las celdas de la tabla dinámica. Es mediante el uso de un artículo calculado . Y esto es algo que me encanta de Excel… es un lugar tan grande con tantas cosas interesantes que aprender.

Ahora, sigamos adelante y construyamos un PT con una columna de entrada.

Narrativo

Haremos esto realizando los siguientes pasos:

• Haga funcionar el PT básico
• Agregar un artículo calculado
• Pruébalo

Hagámoslo.

Haga funcionar el PT básico

Primero hagamos funcionar el PT básico. Exportamos los datos de la nómina y los almacenamos en una tabla de Excel, una parte de la cual se muestra a continuación.

Seleccionamos cualquier celda de la tabla de datos y usamos el comando Insertar Tabla dinámica . Luego, insertamos el campo EEName en el área de diseño de Filas , el campo Importe en el área de Valores y el campo Tipo en el área de Columnas . El PT resultante se muestra a continuación.

Una vez realizado nuestro PT básico, es hora de agregar un elemento calculado.

Agregar un artículo calculado

Paso 1: seleccione la celda de etiqueta correspondiente

El primer paso para agregar un elemento calculado es decirle a Excel a qué campo pertenece el nuevo elemento. La forma en que comunicamos esto a Excel es seleccionando la celda de etiqueta del informe correspondiente.

Analicemos esto por un segundo. Un elemento calculado es una fórmula de tabla dinámica que opera con elementos dentro de un campo. Un elemento calculado NO es un campo calculado . Un campo calculado opera en otros campos. Un elemento calculado opera sobre elementos dentro de un campo. Esto puede ser complicado de visualizar al principio, pero, si observa los datos de origen anteriores, notará que hay una columna Tipo ( campo ). El campo Tipo tiene dos elementos , Pago base y Comisión. Crearemos otro elemento para el campo Tipo creando un Elemento calculado y lo llamaremos Bonificación.

Nota: Podemos crear un elemento calculado para los campos que se han insertado en el PT. Esto significa que podemos crear una columna de bonificación porque hemos insertado el campo Tipo en el área de diseño de Columnas. Entonces, si no tenemos un campo en el área de columnas, no podremos crear una columna de elemento calculado. Pero, si tenemos un campo en el área Filas, podemos crear una fila de elemento calculado.

Una vez que hayamos terminado con esos pequeños antecedentes sobre los elementos calculados, sigamos adelante con nuestro ejemplo.

Hacemos el primer paso, que consiste en seleccionar la celda BasePay o la celda Comisión en el informe de tabla dinámica. Aquí, seleccioné la celda BasePay:

Ahora podemos crear el artículo calculado. Hacemos clic en el comando Herramientas de tabla dinámica Analizar Campos, elementos y conjuntos Elemento calculado .

Nota: si la opción Artículo calculado está deshabilitada, deberá seleccionar la celda Pago base o Comisión en su informe de tabla dinámica.

Paso 2: escribe la fórmula

En el cuadro de diálogo Elemento calculado resultante, le damos a nuestro Artículo calculado un nombre, Bonificación , y aceptaremos la fórmula predeterminada =0 como se muestra a continuación.

Nota: para crear un elemento calculado que realmente calculó un valor, ingrese la fórmula deseada. En esta aplicación, queremos permitir a los usuarios ingresar un valor en la celda PT, por lo que no nos importa la fórmula y la dejaremos como predeterminada =0. Si usáramos una fórmula como =PagoBase*.05, entonces sería el monto predeterminado, pero un usuario podría anular el valor en el PT ingresando un nuevo monto de bonificación.

El PT se actualiza para reflejar nuestro artículo calculado y ahora tenemos una columna de bonificación como se muestra a continuación.

Ahora es el momento de probarlo.

Pruébalo

Para ver si esto funciona, intentamos ingresar un monto de bonificación, 500, para Kristine. Vaya… ¡funciona! Excel nos permite editar una celda de valor de PT y actualiza los totales en consecuencia, como se muestra a continuación.

Si desea comprobarlo, el archivo de muestra siguiente incluye el elemento calculado.

Y si tiene algún otro consejo de PT, compártalo publicando un comentario a continuación… ¡gracias!

Archivo de muestra

• EditarPTValues.xlsx