Cálculos y fórmulas en informes de tabla dinámica y de gráfico dinámico

Los informes de tabla dinámica y gráfico dinámico (informe de gráfico dinámico: gráfico que proporciona análisis de datos interactivo, como un informe de tabla dinámica. Puede cambiar las vistas de los datos, ver niveles de detalle diferentes o reorganizar el diseño del gráfico arrastrando campos y mostrando u ocultando elementos de los mismos.) proporcionan varios tipos de cálculos. Los campos de datos (campo de datos: campo de una lista, tabla o base de datos de origen que contiene datos que se resumen en un informe de tabla dinámica o de gráfico dinámico. Un campo de datos suele contener datos numéricos, como estadísticas o índices de ventas.) utilizan funciones de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) para combinar valores de los datos de origen (datos de origen: lista o tabla que se utiliza para crear un informe de tabla dinámica o gráfico dinámico. Los datos de origen pueden tomarse de una lista o rango de Excel, de un cubo o base de datos externa o de otro informe de tabla dinámica.) subyacentes. Puede utilizar cálculos personalizados (cálculo personalizado: método para resumir valores del área de datos de un informe de tabla dinámica utilizando los valores de otras celdas del área de datos. Utilice la lista Mostrar datos como del cuadro de diálogo Campo de tabla dinámica para un campo de datos con el fin de crear cálculos personalizados.) para comparar valores de datos o agregar sus propias fórmulas que empleen elementos del informe o datos de otras hojas de cálculo.

MostrarCómo resumen los datos los informes de tabla dinámica y de gráfico dinámico

Ejemplo de datos de origen de tabla dinámica

Datos de origen

Los valores del área de datos (área de datos: parte de un informe de tabla dinámica que contiene datos de resumen. Los valores de cada celda del área de datos representan un resumen de los datos de los registros o filas de origen.) resumen los datos de origen subyacentes del informe.

Ejemplo de informe de tabla dinámica

Informe de tabla dinámica basado en los datos de origen anteriores

El campo de columna (campo de columna: campo asignado a una orientación de columna en un informe de tabla dinámica. Los elementos asociados a un campo de columna se muestran como etiquetas de columna.) Mes proporciona los elementos (elemento: subcategoría de un campo en informes de tabla dinámica y de gráfico dinámico. Por ejemplo, el campo "Mes" podría tener los elementos "Enero", "Febrero", etc.) Marzo y Abril. El campo de fila (campo de fila: campo asignado a una orientación de fila en un informe de tabla dinámica. Los elementos asociados a un campo de fila se muestran como etiquetas de fila.) Región proporciona los elementos Norte, Sur, Este y Oeste. El valor que está en la intersección de la columna Abril y la fila Norte corresponde a los ingresos totales de ventas de los registros de los datos de origen que tengan Abril como valor de Mes y Norte como valor de Región.

Ejemplo de un informe de gráfico dinámico

Informe de gráfico dinámico basado en el informe de tabla dinámica de ejemplo

En un informe de gráfico dinámico, el campo Región puede ser un campo de categoría (campo de categoría de gráfico dinámico: campo al que se asigna una orientación de categoría en un informe de gráfico dinámico. En un gráfico, las categorías suelen aparecer en el eje x, o eje horizontal, del gráfico.) que muestra Norte, Sur, Este y Oeste como categorías. El campo Mes podría ser un campo de serie (campo de serie de gráfico dinámico: campo al que se asigna una orientación de serie en un informe de gráfico dinámico. En un gráfico, las series se representan en la leyenda.) que mostrara los elementos Marzo, Abril y Mayo como series representadas en la leyenda. Un campo de datos denominado Suma de ventas podría contener marcadores de datos (marcador de datos: barra, área, punto, sector u otro símbolo de un gráfico que representa un único punto de datos o valor que procede de una celda de una hoja de cálculo. Los marcadores de datos relacionados en un gráfico constituyen una serie de datos.) que representaran los ingresos totales de cada región por mes. Por ejemplo, un marcador de datos representará, por su posición en el eje de valores (eje: línea que rodea el área de trazado de un gráfico utilizada como marco de referencia de medida. El eje y suele ser el eje vertical y contener datos. El eje x suele ser el eje horizontal y contener categorías.), las ventas totales correspondientes a Abril y a la región Norte.

Los valores y cálculos de un informe de gráfico dinámico reflejan los del informe de tabla dinámica asociado (informe de tabla dinámica asociado: informe de tabla dinámica que proporciona los datos de origen a un informe de gráfico dinámico. Se crea automáticamente cuando se crea un nuevo informe de gráfico dinámico. Cuando se cambia el diseño de alguno de los informes, el otro cambia también.) y viceversa.

Cálculos personalizados       Un cálculo personalizado muestra los valores basados en otros elementos o celdas del área de datos. Por ejemplo, podría mostrar los valores del campo de datos Suma de ventas como un porcentaje de las ventas de marzo o como el total acumulado de los elementos del campo Mes.

Fórmulas       Si las funciones de resumen y los cálculos personalizados no proporcionan los resultados que desea, puede crear sus propias fórmulas en los campos calculados (campo calculado: campo de un informe de tabla dinámica o de gráfico dinámico que utiliza una fórmula que usted cree. Los campos calculados pueden realizar cálculos utilizando el contenido de otros campos del informe de tabla dinámica o de gráfico dinámico.) y elementos calculados (elemento calculado: elemento de un campo de tabla dinámica o gráfico dinámico que utiliza una fórmula que usted cree. Los elementos calculados pueden realizar cálculos utilizando el contenido de otros elementos del mismo campo del informe de tabla dinámica o gráfico dinámico.). Por ejemplo, podría agregar un elemento calculado con la fórmula de la comisión de venta, que podría ser diferente para cada región. Así, el informe incluiría la comisión automáticamente en los subtotales y en los totales generales.

MostrarCómo el tipo de datos de origen afecta a los cálculos

Los cálculos y las opciones disponibles en un informe dependen de si los datos de origen proceden de una base de datos OLAP (OLAP: tecnología de base de datos que se ha optimizado para la elaboración de consultas e informes, en lugar de procesar transacciones. Los datos OLAP se organizan jerárquicamente y se almacenan en cubos en lugar de tablas.) o de otro tipo de base de datos.

Datos de origen OLAP       Para los informes basados en cubos (cubo: estructura de datos OLAP. un cubo contiene dimensiones, como País, Región, Ciudad, y campos de datos, como Índice de ventas. Las dimensiones organizan los tipos de datos en jerarquías con niveles de detalle y cantidades de medida de campos de datos.) OLAP, los valores de resumen se calculan previamente en el servidor OLAP antes de que Microsoft Excel muestre los resultados. Por ello, no se puede cambiar la forma de calcular estos valores desde el informe. No se puede cambiar la función de resumen utilizada para calcular los campos de datos o subtotales, ni agregar campos calculados (campo calculado: campo de un informe de tabla dinámica o de gráfico dinámico que utiliza una fórmula que usted cree. Los campos calculados pueden realizar cálculos utilizando el contenido de otros campos del informe de tabla dinámica o de gráfico dinámico.) o elementos calculados (elemento calculado: elemento de un campo de tabla dinámica o gráfico dinámico que utiliza una fórmula que usted cree. Los elementos calculados pueden realizar cálculos utilizando el contenido de otros elementos del mismo campo del informe de tabla dinámica o gráfico dinámico.). Si el servidor OLAP proporciona campos calculados, conocidos como miembros calculados, dichos campos aparecen en la lista de campos de la tabla dinámica. También aparecen los campos y elementos calculados creados mediante macros (macro: acción o conjunto de acciones utilizados para automatizar tareas. Las macros se graban en el lenguaje de programación de Visual Basic para Aplicaciones.) escritas en Visual Basic para Aplicaciones (Visual Basic para Aplicaciones (VBA): versión del lenguaje de macros de Microsoft Visual Basic que se utiliza para programar aplicaciones basadas en Microsoft Windows y que se incluye en varios programas de Microsoft.) y almacenadas en el libro, pero no se puede cambiar estos campos y elementos. Si necesita realizar otros tipos de cálculos, póngase en contacto con el administrador de la base de datos OLAP.

Otros tipos de datos de origen       En los informes basados en otros tipos de datos externos o en datos de hoja de cálculo, Microsoft Excel utiliza la función de resumen Suma para calcular los campos de datos que contienen datos numéricos y la función de resumen Contar para calcular los que contienen texto. Es posible elegir otra función de resumen,  como Promedio, Max o Min , para seguir analizando y personalizando los datos. También puede crear fórmulas propias que utilicen elementos del informe o datos de otras hojas de cálculo mediante la creación de un campo o un elemento calculado dentro de un campo.

Elementos ocultos en los totales       En los datos de origen OLAP, puede incluir o excluir los valores de los elementos ocultos al calcular subtotales y totales generales. Para otros tipos de datos de origen, los valores de los elementos ocultos se excluyen de forma predeterminada, pero es posible incluir de forma opcional los elementos ocultos de los campos de página (campo de página: campo asignado a una orientación de página en un informe de tabla dinámica o de gráfico dinámico. Puede mostrarse un resumen de todos los elementos de un campo de página o bien un elemento cada vez que filtre los datos para los demás elementos.).

MostrarSintaxis de las fórmulas

Sólo se pueden crear fórmulas en los informes que no se basen en datos de origen OLAP (OLAP: tecnología de base de datos que se ha optimizado para la elaboración de consultas e informes, en lugar de procesar transacciones. Los datos OLAP se organizan jerárquicamente y se almacenan en cubos en lugar de tablas.).

Las fórmulas están disponibles en los informes de gráfico dinámico y utilizan la misma sintaxis que las de los informes de tabla dinámica. Para obtener resultados óptimos al trabajar con un informe de gráfico dinámico, cree y edite las fórmulas en el informe de tabla dinámica asociado (informe de tabla dinámica asociado: informe de tabla dinámica que proporciona los datos de origen a un informe de gráfico dinámico. Se crea automáticamente cuando se crea un nuevo informe de gráfico dinámico. Cuando se cambia el diseño de alguno de los informes, el otro cambia también.), donde puede ver los distintos valores que componen los datos y, a continuación, visualice los resultados en el informe de gráfico dinámico.

Elementos de fórmula       En las fórmulas que cree para los campos calculados (campo calculado: campo de un informe de tabla dinámica o de gráfico dinámico que utiliza una fórmula que usted cree. Los campos calculados pueden realizar cálculos utilizando el contenido de otros campos del informe de tabla dinámica o de gráfico dinámico.) y los elementos calculados (elemento calculado: elemento de un campo de tabla dinámica o gráfico dinámico que utiliza una fórmula que usted cree. Los elementos calculados pueden realizar cálculos utilizando el contenido de otros elementos del mismo campo del informe de tabla dinámica o gráfico dinámico.), puede utilizar operadores (operador: signo o símbolo que especifica el tipo de cálculo que se debe llevar a cabo en una expresión. Hay operadores matemáticos, comparativos, lógicos y referenciales.) y expresiones como en cualquier otra fórmula de hoja de cálculo. Pueden utilizarse constantes (constante: valor que no ha sido calculado y que, por tanto, no varía. Por ejemplo, el número 210 y el texto "Ingresos trimestrales" son constantes. Las expresiones, o los valores resultantes de ellas, no son constantes.) y hacer referencia a los datos del informe, pero no pueden utilizarse referencias de celda o nombres (nombre: palabra o cadena de caracteres que representa una celda, rango de celdas, fórmula o valor constante. Utilice nombres fáciles de entender, como Productos, para referirse a rangos difíciles de entender, como Ventas!C20:C30.) definidos. No es posible utilizar funciones (función: fórmula ya escrita que toma un valor o valores, realiza una operación y devuelve un valor o valores. Utilice funciones para simplificar y acortar fórmulas en una hoja de cálculo, especialmente aquellas que llevan a cabo cálculos prolongados o complejos.) que requieran referencias de celda o nombres definidos como argumentos, ni funciones matriciales (matriz: utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.).

Nombres en los informes       Microsoft Excel proporciona nombres para identificar los elementos de un informe en las fórmulas. Los nombres están formados por el nombre de campo (campo: en un informe de tabla dinámica o de gráfico dinámico, categoría de datos que se deriva de un campo de los datos de origen. Los informes de tabla dinámica tienen campos de fila, columna, página y datos. Los informes de gráfico dinámico tienen campos de serie, categoría, página y datos.) y el nombre de elemento (elemento: subcategoría de un campo en informes de tabla dinámica y de gráfico dinámico. Por ejemplo, el campo "Mes" podría tener los elementos "Enero", "Febrero", etc.). En el siguiente ejemplo, los datos del rango C3:C9 reciben el nombre Lácteos.

Informe de tabla dinámica de ejemplo

En un informe de gráfico dinámico, los nombres de campo se muestran en los botones de campo y los nombres de elemento aparecen en la lista desplegable de cada campo. No confunda estos nombres con los que aparecen en las sugerencias para gráficos, que reflejan nombres de series y puntos de datos.

Ejemplos       Un campo calculado denominado Previsión puede prever pedidos futuros con una fórmula como la siguiente:

=Ventas* 1,2

Un elemento calculado en el campo Tipo que calcula las ventas de un nuevo producto basándose en las ventas de Lácteos puede utilizar una fórmula como la siguiente:

=Lácteos * 115%

Fórmulas que operan sobre el total de la suma, no sobre los registros individuales       Las fórmulas de los campos calculados operan sobre la suma de los datos subyacentes de los campos de la fórmula. Por ejemplo, la fórmula =Ventas * 1,2 multiplica por 1,2 la suma de las ventas de cada tipo y región; no multiplica por 1,2 cada venta individual para luego sumar los importes multiplicados. Sin embargo, las fórmulas para los elementos calculados operan sobre los registros individuales. La fórmula de elemento calculado =Lácteos *115% multiplica cada venta individual de Lácteos por 115%. A continuación, los importes multiplicados se resumen conjuntamente en el área de datos (área de datos: parte de un informe de tabla dinámica que contiene datos de resumen. Los valores de cada celda del área de datos representan un resumen de los datos de los registros o filas de origen.).

Espacios, números y símbolos en los nombres       En los nombres que incluyen más de un campo, los campos pueden estar en cualquier orden. En el ejemplo anterior, las celdas C6:D6 pueden ser 'Abril Norte' o 'Norte Abril'. Utilice comillas simples para encerrar los nombres que tengan más de una palabra o que incluyan números o símbolos.

Totales       Las fórmulas no pueden referirse a totales (como Total Marzo, Total Abril y Total general en el ejemplo).

Nombres de campo en referencias a elementos       Es posible incluir el nombre de campo en una referencia a un elemento. El nombre de elemento debe incluirse entre corchetes,  por ejemplo, Región[Norte]. Utilice este formato para evitar los errores #¿NOMBRE? que se producen cuando dos elementos en dos campos diferentes de un informe reciben el mismo nombre. Por ejemplo, si un informe tiene un elemento denominado Carne en el campo Tipo y otro elemento denominado Carne en el campo Categoría, puede evitar los errores #¿NOMBRE? haciendo referencia a los elementos como Tipo[Carne] y Categoría[Carne].

Referencias a elementos por posición       Puede hacer referencia a un elemento por su posición en el informe tal como esté ordenado y se muestre en ese momento. Tipo[1] es Lácteos y Tipo [2] es Marisco. El elemento al que se haga referencia de esta forma puede cambiar siempre que cambien las posiciones de los elementos o se muestren u oculten los distintos elementos. Los elementos ocultos no se incluyen en este índice.

Pueden utilizarse posiciones relativas para hacer referencia a los elementos. Las posiciones se determinan relativas al elemento calculado que contenga la fórmula. Si Sur es la región actual, Región[-1] es Norte; si Norte es la región actual, Región[+1] es Sur. Por ejemplo, un elemento calculado podría utilizar la fórmula =Región[-1] * 3%. Si la posición que se da es anterior al primer elemento o posterior al último elemento del campo, la fórmula generará un error #¡REF!

En las fórmulas de los elementos calculados, si hace referencia a los elementos por su posición o posición relativa, las opciones configuradas en Las diez mejores de Automostrar y Opciones de autoordenar en los cuadros de diálogo Ordenar tabla dinámica y las diez mejores y Opciones avanzadas de campo de tabla dinámica se restaurarán a Desactivado o Manual y dichas opciones no estarán disponibles.

MostrarUtilizar fórmulas en informes de gráfico dinámico

Los métodos y las reglas de creación de fórmulas de informe de gráfico dinámico son los mismos que se utilizan en los informes de tabla dinámica. Al crear un campo calculado (campo calculado: campo de un informe de tabla dinámica o de gráfico dinámico que utiliza una fórmula que usted cree. Los campos calculados pueden realizar cálculos utilizando el contenido de otros campos del informe de tabla dinámica o de gráfico dinámico.) o un elemento calculado (elemento calculado: elemento de un campo de tabla dinámica o gráfico dinámico que utiliza una fórmula que usted cree. Los elementos calculados pueden realizar cálculos utilizando el contenido de otros elementos del mismo campo del informe de tabla dinámica o gráfico dinámico.) en un informe de gráfico dinámico, los cálculos se reflejan en el informe de tabla dinámica asociado (informe de tabla dinámica asociado: informe de tabla dinámica que proporciona los datos de origen a un informe de gráfico dinámico. Se crea automáticamente cuando se crea un nuevo informe de gráfico dinámico. Cuando se cambia el diseño de alguno de los informes, el otro cambia también.) y viceversa. Para obtener resultados óptimos, cree las fórmulas de un informe de gráfico dinámico en el informe de tabla dinámica asociado, donde puede ver los distintos valores que componen los datos y, a continuación, visualice los resultados en el informe de gráfico dinámico.

Por ejemplo, el siguiente informe de gráfico dinámico muestra las ventas regionales de cada vendedor:

Informe de gráfico dinámico con las ventas regionales de cada vendedor

Si desea ver qué aspecto tendrán las ventas si se aumentan en un diez por ciento, puede crear un campo calculado que aplique la fórmula siguiente:

=Ventas * 110%

El resultado se reflejará en el gráfico de esta forma:

Informe de gráfico dinámico incrementado en un diez por ciento por región

Por ejemplo, si desea ver un marcador de datos (marcador de datos: barra, área, punto, sector u otro símbolo de un gráfico que representa un único punto de datos o valor que procede de una celda de una hoja de cálculo. Los marcadores de datos relacionados en un gráfico constituyen una serie de datos.) independiente de las ventas de la región Norte, menos un costo de transporte del ocho por ciento, puede crear un elemento calculado en el campo Región que aplique la fórmula siguiente:

=Norte - (Norte * 8%)

El resultado sería el siguiente:

Informe de gráfico dinámico con un elemento calculado

Un elemento calculado creado en el campo Vendedor, no obstante, aparecería como una serie representada en la leyenda y, en el gráfico, como un punto de datos de cada categoría.

 
 
Corresponde a:
Excel 2003