Fórmula de tabla dinámica en Excel (tabla de contenido)
- Fórmula de tabla dinámica en Excel
- Campo personalizado para calcular la cantidad de beneficio
- Fórmula avanzada en campo calculado
Fórmula de tabla dinámica en Excel
Pivot Table es una herramienta que nos permite analizar grandes rangos de datos. Podemos analizar, interpretar y hacer muchas otras cosas sin rompernos mucho la cabeza y sudar. Puede dar casi todo lo que hay en los datos de origen.
Si parte de la información está allí en los datos de origen, es posible que necesitemos calcular esto por nosotros mismos. Por ejemplo, si tenemos un Monto total de ventas y un Costo total, es posible que necesitemos calcular el Beneficio o la Pérdida total por nuestra cuenta.
No necesitamos hacer esto en la fuente, pero podemos hacerlo dentro de la tabla dinámica en sí, estos se llaman Campos calculados dentro de la tabla dinámica. Podemos usar fórmulas personalizadas para hacer que los datos cuenten más historias de los datos. Los campos calculados nos permiten construir una nueva columna calculada que no existe en la fuente de datos real.
En este artículo, demostraremos las formas de utilizar los campos calculados de la tabla dinámica para crear nuevas columnas según nuestros requisitos.
Campo personalizado para calcular la cantidad de beneficio
Puede descargar esta plantilla de Excel de fórmula de tabla dinámica aquí - Plantilla de Excel de fórmula de tabla dinámicaEste es el campo calculado más utilizado en la tabla dinámica. Eche un vistazo a los datos a continuación, tengo la columna Nombre del país, Nombre del producto, Unidades vendidas, Precio unitario, Ventas brutas, COGS (Costo de los bienes vendidos), Fecha y Año.
Permítanme aplicar la tabla dinámica para encontrar las ventas totales y el costo total de cada país. A continuación se muestra la tabla dinámica para los datos anteriores.
El problema es que no tengo una columna de ganancias en los datos de origen. Necesito averiguar el beneficio y el porcentaje de beneficio para cada país. Podemos agregar estas dos columnas en la propia tabla dinámica.
Paso 1: Seleccione una celda en la tabla dinámica. Vaya a la pestaña Analizar en la cinta y seleccione Campos, Elementos y Conjuntos. Debajo de esto, seleccione Campo calculado.
Paso 2: en el cuadro de diálogo siguiente, asigne un nombre a su nuevo campo calculado.
Paso 3: en la sección Fórmula, aplique la fórmula para encontrar el beneficio. La fórmula para encontrar el beneficio es Ventas brutas - COGS.
Vaya dentro de la barra de fórmulas> Seleccione Ventas brutas del campo a continuación y haga doble clic en ella aparecerá en la barra de fórmulas.
Ahora escriba el símbolo menos (-) y seleccione COGS> Doble clic.
Paso 4: haga clic en AGREGAR y OK para completar la fórmula.
Paso 5: Ahora tenemos nuestra columna TOTAL PROFIT en la tabla dinámica.
Este campo calculado es flexible, no solo se limita al análisis por país, sino que podemos usarlo para todo tipo de análisis. Si quiero ver el análisis por país y por producto, solo tengo que arrastrar y soltar la columna del producto en el campo FILA, se mostrará la división de ganancias para cada producto en cada país.
Paso 6: Ahora necesitamos calcular el porcentaje de ganancia. La fórmula para calcular el porcentaje de beneficio es el beneficio total / las ventas brutas.
Vaya a Analizar y nuevamente seleccione Campo calculado en Campos, artículos y conjuntos.
Paso 7: ahora debemos ver el campo calculado recientemente Ganancia total en la lista Campos. Inserte este campo en la fórmula.
Paso 8: escriba el símbolo divisor (/) e inserte el campo de ventas brutas.
Paso 9: Nombre este campo calculado como porcentaje de beneficio.
Paso 10: haga clic en AGREGAR y OK para completar la fórmula. Tenemos el porcentaje de beneficio como la nueva columna.
Fórmula avanzada en campo calculado
Lo que he mostrado ahora es lo básico del campo calculado. En este ejemplo, le mostraré las fórmulas avanzadas en los campos calculados de la tabla dinámica. Ahora quiero calcular el monto del incentivo basado en el porcentaje de ganancia.
Si el% de beneficio es> 15%, el incentivo debería ser el 6% del beneficio total.
Si el% de beneficio es> 10%, el incentivo debería ser el 5% del beneficio total.
Si el% de beneficio es <10%, el incentivo debería ser el 3% del beneficio total.
Paso 1: vaya al campo calculado y abra el cuadro de diálogo a continuación. Dé el nombre como Monto de incentivo.
Paso 2: Ahora usaré la condición IF para calcular el monto del incentivo. Aplique las siguientes fórmulas como se muestra en la imagen.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Paso 3: haga clic en AGREGAR y OK para completar. Ahora tenemos una columna de Monto de incentivo.
Limitación del campo calculado
Hemos visto la maravilla de los campos calculados, pero también tiene algunas de las limitaciones. Ahora eche un vistazo a la imagen a continuación, si quiero ver el desglose de la cantidad de Incentivo sabio del producto, tendremos un TOTAL SUBTOTAL Y GRAN TOTAL incorrecto de la CANTIDAD DE INCENTIVO.
Así que tenga cuidado al mostrar el Subtotal de campos calculados. Le mostrará las cantidades incorrectas.
Obtenga la lista de todas las fórmulas de campo calculadas
Si no sabe cuántas fórmulas hay en el campo calculado de la tabla dinámica, puede obtener el resumen de todas estas en una hoja de trabajo separada.
Vaya a Analizar> Campos, elementos y conjuntos -> Listar fórmulas.
Le dará un resumen de todas las fórmulas en una nueva hoja de trabajo.
Cosas para recordar sobre la fórmula de tabla dinámica en Excel
- Podemos eliminar, modificar todos los campos calculados.
- No podemos usar fórmulas como VLOOKUP, SUMIF y muchas otras fórmulas involucradas en el rango en campos calculados, es decir, no se pueden usar todas las fórmulas que requieren rango.
Artículos recomendados
Esta ha sido una guía para la fórmula de tabla dinámica en Excel. Aquí discutimos los Pasos para usar la fórmula de la tabla dinámica en Excel junto con los ejemplos y la plantilla de Excel descargable. También puede ver estas funciones útiles en Excel:
- Tutoriales sobre el gráfico dinámico en Excel
- Crear tabla dinámica en Excel
- Tutorial VLOOKUP en Excel
- Excel Crear base de datos