Utilizar un informe de tabla dinámica para que los datos de tablas externas estén disponibles en Excel Services

Si desea trabajar en Servicios de Excel con los datos de una tabla basados en una conexión de datos externos, no podrá utilizar un rango de datos externos (lo que también se conoce como tabla de consulta). Debe crear un informe de tabla dinámica que acople los datos jerárquicos multidimensionales en una tabla o en dos dimensiones. Ésta es la forma de hacerlo.


Comparación de datos multidimensionales y bidimensionales

Llamada 1 Convierta un informe de tabla dinámica multidimensional…
Llamada 2 …en un informe de tabla dinámica bidimensional…
Llamada 3 …para poder ver los datos de la tabla acoplados en Excel Services.

¿Qué desea hacer?


Obtener información acerca del modo en que Excel Services admite conexiones a orígenes de datos externos

Los informes de tabla dinámica están diseñados para agregar una serie de datos numéricos a sumas y totales, así como para trabajar con datos multidimensionales organizados en una jerarquía. Por otra parte, los rangos de datos externos son tablas bidimensionales estructuradas como filas y columnas que muestran registros no agregados de datos de origen.

Al utilizar el Asistente para la conexión de datos o Microsoft Query para conectarse a datos externos, se suele crear un rango de datos externos. La única excepción a esto tiene lugar cuando se crea un informe de tabla dinámica que está conectado a datos externos. Un informe de tabla dinámica no crea un rango de datos externos. Esta diferencia en cuanto al modo de creación de las conexiones es importante para comprender cuándo publicar un libro en Servicios de Excel, ya que Servicios de Excel sólo admite conexiones de datos externos basadas en informes de tabla dinámica y no rangos de datos externos.

Volver al principio Volver al principio

Obtener información acerca de las diferencias entre los informes de tabla dinámica conectados a datos externos y los rangos de datos externos

Al trabajar con un informe de tabla dinámica conectado a datos externos, existen diferencias de las que debería ser consciente entre el comportamiento de un informe de tabla dinámica y un rango de datos externos. Dichas diferencias incluyen:

Los informes de tabla dinámica crean grupos y subgrupos de datos en un orden de izquierda a derecha

En general, los datos agrupados se basan en una ordenación de los datos por una o más columnas. Por ejemplo, si desea ver todos los empleados agrupados por sus departamentos, puede realizar una ordenación primaria de los datos por departamentos y una secundaria por empleados. También puede anidar grupos, como Línea de productos, Categoría y Producto, o Geografía, País/región, Estado/provincia y Ciudad. De forma predeterminada, los informes de tabla dinámica se ordenan automáticamente en grupos y subgrupos en un orden de izquierda a derecha para facilitar la visualización de los datos relacionados, mostrar la información de encabezado y resumen y expandir o contraer los elementos relacionados.


Línea de productos Categoría Producto
Motores Avión Montaje de las alas
Montaje de la cola
Propulsor
Automóvil 269 C.V.
454 C.V.
Tubos de escape Pasajero Calor extremo
Normal
Todoterreno Pequeño
Datos agrupados por Línea de productos y, en cada Línea de productos, subagrupados por Categoría

Al colocar una columna con valores únicos en el área Rótulos de fila situada a la izquierda, es posible acoplar los datos de forma automática.


Núm. Id. Línea de productos Categoría Producto
WM-345 Motores Avión Montaje de las alas
TM-231 Motores Avión Montaje de la cola
BSTR-567 Motores Avión Propulsor
6C-734 Motores Automóvil 269 C.V.
8C-121 Motores Automóvil 454 C.V.
MF-202 Tubos de escape Pasajero Calor extremo
MF-321 Tubos de escape Pasajero Normal
MF-211 Tubos de escape Todoterreno Pequeño
Los mismos datos de Línea de productos acoplados al agregar la columna Núm. Id.

Si no desea mostrar la columna, puede ocultarla una vez que la haya agregado al informe de tabla dinámica. (Seleccione la columna y, en la ficha Inicio, en el grupo Celdas, haga clic en la flecha situada junto a Formato, seleccione Ocultar y mostrar y, a continuación, haga clic en Ocultar columnas.)

 Importante   Si un informe de tabla dinámica detecta dos o más filas duplicadas de datos del origen de datos, sólo muestra una fila. Si desea ver todas las filas, incluso las duplicadas, en un informe de tabla dinámica, debe agregar una columna que contenga valores únicos a la tabla, consulta o vista que importe desde el origen de datos. Si éste no cuenta con una columna única, podría agregársela. Por ejemplo, podría agregar una columna con el tipo de datos Autonumérico a una tabla de Microsoft Office Access, una columna con el tipo de datos Identidad a una tabla de Microsoft SQL Server o una columna calculada que muestre un número único para cada registro a una consulta o vista de base de datos.

Editar datos de celdas y agregar columnas calculadas

Antes de publicar el libro en Excel Services, puede editar los datos de un rango de datos externos, aunque los datos de tabla dinámica siempre sean de sólo lectura.

Al hacer doble clic en una celda de un rango de datos externos, se pasa al modo de edición, donde se puede cambiar el valor o incluso especificar una fórmula. No obstante, tenga en cuenta que no está cambiando el valor original en el origen de datos, por lo que la siguiente vez que actualice los datos es posible que el nuevo valor se sobrescriba, según la configuración de la propiedad del rango de datos externos Si cambia el número de filas en el rango de datos después de la actualización.

Si hace doble clic en una celda de rótulo de fila de un informe de tabla dinámica, la celda expande o contrae los elementos de ese rótulo de fila. Cuando se acopla un informe de tabla dinámica en una tabla bidimensional, el acoplamiento hace que todos los valores de fila de un grupo situado a la izquierda de la celda desaparezcan o vuelvan a aparecer.

Puede agregar columnas calculadas a cualquier lugar de una tabla de Microsoft Office Excel basada en un rango de datos externos o insertar columnas y autorrellenar fórmulas en un rango de datos externos. No puede insertar columnas calculadas en un informe de tabla dinámica (aunque puede insertar campos calculados). Sin embargo, puede agregar una columna que autorrellene datos o fórmulas inmediatamente a la izquierda o la derecha del informe de tabla dinámica y, dentro de una fórmula autorrellenada, puede utilizar referencias de celda en el informe de tabla dinámica.

Crear gráficos

Si desea crear un gráfico basado en los datos de un informe de tabla dinámica, puede crear un informe de este tipo, que se comporta de una forma similar a un gráfico estándar, aunque con algunas diferencias, incluidas las siguientes:

Interacción     Con los gráficos estándar, se crea un gráfico para cada vista de los datos que se desean ver, aunque estas vistas no son interactivas. Con los informes de tabla dinámica, se puede crear un único gráfico y ver los datos de forma interactiva y de varias maneras si se cambia el diseño del informe o el detalle que aparece. En Excel Services, tanto los gráficos estándar como los de tabla dinámica se pueden actualizar basándose en la interacción del usuario con el libro, aunque ambos gráficos se muestran como imágenes estáticas.

Tipos de gráfico     El tipo de gráfico predeterminado para un gráfico estándar es un gráfico de columnas agrupadas, que compara valores entre categorías. El tipo de gráfico predeterminado para un informe de tabla dinámica es un gráfico de columnas apiladas, que compara la contribución de cada valor a un total entre categorías. Es posible cambiar un informe de tabla dinámica a cualquier tipo de gráfico excepto XY (Dispersión), de cotizaciones o de burbujas.

Elementos de un gráfico     Los informes de tabla dinámica contienen los mismos elementos que los gráficos estándar, aunque también incluyen campos y elementos que se pueden agregar, girar o eliminar a fin de mostrar diferentes vistas de los datos. Las categorías, las series y los datos de los gráficos estándar son campos de categoría, campos de serie y campos de valor en los informes de tabla dinámica. Éstos también pueden incluir filtros de informe. Cada uno de esos campos contiene elementos que en los gráficos estándar aparecen como etiquetas de categoría o nombres de serie en las leyendas (leyenda: cuadro que identifica los diseños o colores asignados a las series de datos o categorías de un gráfico.).

Formato     La mayor parte del formato, incluidos los elementos, el diseño y el estilo, se conserva al actualizar (actualizar: renovar el contenido de un informe de tabla dinámica o gráfico dinámico para reflejar los cambios realizados en el origen de datos subyacente. Si el informe está basado en datos externos, la actualización ejecuta la consulta subyacente para recuperar los datos nuevos o modificados.) un informe de tabla dinámica. No obstante, las líneas de tendencia (línea de tendencia: representación gráfica de tendencias en series de datos, como una línea inclinada ascendente para representar el aumento de ventas a lo largo de un período de meses. Las líneas de tendencia se utilizan para el estudio de problemas de predicción, lo que se denomina también análisis de regresión.), los rótulos de datos (etiqueta de datos: etiqueta que proporciona información adicional acerca de un marcador de datos, que representa un solo punto de datos o valor que se origina a partir de una celda de una hoja de cálculo.), las barras de error (barras de error: se suelen utilizar en datos estadísticos o científicos y muestran un posible error o grado de incertidumbre relativa a cada marcador de datos de una serie.) y otros cambios de las series de datos no se conservan. Los gráficos estándar no pierden este formato una vez que se ha aplicado.

Mover elementos o cambiar su tamaño     En un informe de tabla dinámica no es posible mover ni cambiar el tamaño del área de traza (área de trazado: en un gráfico 2D, área delimitada por los ejes, incluidas todas las series de datos. En un gráfico 3D, área delimitada por los ejes, incluidas las series de datos, los nombres de categoría, las etiquetas de marcas de graduación y los títulos de eje.), la leyenda, los títulos del gráfico o los títulos del eje, aunque es posible seleccionar una entre varias posiciones preestablecidas para la leyenda, así como cambiar el tamaño de la fuente de los títulos. En un gráfico estándar, es posible mover y cambiar el tamaño de todos estos elementos.

Después de crear un informe de tabla dinámica, si lo prefiere, puede convertirlo en un gráfico estándar. Para obtener más información, vea el artículo Crear o eliminar un informe de tabla dinámica o de gráfico dinámico.

Comparar las propiedades de los rangos de datos externos y las opciones de tabla dinámica

Los rangos de datos externos y los informes de tabla dinámica tienen propiedades que se pueden utilizar para controlar el formato y el diseño de los datos, así como para actualizar el comportamiento de los mismos.

Tanto los rangos de datos externos como los informes de tabla dinámica permiten ajustar el ancho de las columnas, mantener el orden de las mismas, filtrar, aplicar formato a las celdas y elaborar el diseño de los datos si se actualizan los datos externos.

En un rango de datos externos, también existe la opción de incluir números de fila como primera columna de datos. En un informe de tabla dinámica no existe ninguna opción parecida. Vea la sección Los informes de tabla dinámica crean grupos y subgrupos de datos en un orden de izquierda a derecha para obtener información acerca de la adición de una columna única al origen de datos.

Al actualizar un rango de datos externos, si el número de filas cambia, es posible controlar lo que le sucede a los datos existentes mediante el establecimiento de una de las siguientes opciones:

  • Insertar celdas para los nuevos datos, eliminar celdas sin usar (predeterminada)
  • Insertar filas para los datos nuevos, borrar celdas sin usar
  • Sobrescribir celdas existentes con los datos nuevos, borrar celdas sin usar

Al actualizar un informe de tabla dinámica, éste incorpora datos nuevos. No obstante, es posible que se le pregunte si desea sobrescribir celdas existentes por debajo del informe actual.

Filtrar datos

El proceso de filtrado de datos en un rango de datos externos y un informe de tabla dinámica es muy similar, aunque existen algunas diferencias como las siguientes:

  • Los rangos de datos externos utilizan los comandos Filtro de texto, Filtro de número y Filtro de fecha, mientras que los informes de tabla dinámica utilizan los comandos Filtrar por etiqueta, Filtrar por valor y Filtrar por fecha.
  • El menú Filtrar de un rango de datos externos siempre quita elementos que ya no son válidos, así que nunca incluye nuevos elementos tras una operación de actualización si hay un filtro aplicado, mientras que un informe de tabla dinámica tiene opciones para controlar este comportamiento. Para obtener más información, vea la sección Cambiar las opciones y la configuración de los campos de tabla dinámica.
  • Un informe de tabla dinámica tiene una característica de filtrado (no disponible en un rango de datos externos) que permite a los usuarios trabajar con un subconjunto de datos en el informe de tabla dinámica.

Ordenar datos

En un rango de datos externos, es posible realizar una ordenación de varias columnas (hasta por 24 columnas), establecer cualquier columna como ordenación primaria y otras columnas como secundarias. No obstante, los procesos de ordenación de datos en un informe de tabla dinámica y un rango de datos externos son distintos. A diferencia de lo que ocurre en un rango de datos externos, en un informe de tabla dinámica sólo es posible realizar una ordenación primaria de la columna situada más a la izquierda. El resto de las columnas realiza ordenaciones secundarias basándose en la columna situada inmediatamente a la izquierda. Para resumir, sólo es posible realizar un ordenación primaria y secundaria en un orden de izquierda a derecha.

Además, cuando se coloca una columna con valores únicos como primera columna de la izquierda, sólo es posible ordenar por esa columna, ya que, en efecto, se está creando un grupo primario para cada fila. Según las necesidades de los usuarios, es posible que desee ordenar previamente los datos en Excel y, una vez publicado el libro en Excel Services, desactivar la ordenación al desactivar la casilla de verificación Ordenación del panel de herramientas Elemento Web de Microsoft Office Excel Web Access.

Utilizar hipervínculos

A diferencia de los rangos de datos externos, las celdas de tabla dinámica no son compatibles con los hipervínculos activos. Éstos se tratan como texto, pero no es posible seguirlos a una página Web o un documento en Excel o Excel Services.

Volver al principio Volver al principio

Elaborar un informe de tabla dinámica tabular bidimensional

Hay una serie de pasos obligatorios para acoplar datos multidimensionales en un informe de tabla dinámica tabular bidimensional. Para obtener los mejores resultados posibles, lleve a cabo los siguientes procedimientos en este orden.

Volver al principio Volver al principio

Crear el informe de tabla dinámica y conectarlo al origen de datos externos

  1. Seleccione una celda en blanco.
  2. En la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica.

Cinta de opciones de Excel

Aparece el cuadro de diálogo Crear tabla dinámica.

  1. Haga clic en Utilice una fuente de datos externa.
  2. Haga clic en Elegir conexión.

Aparece el cuadro de diálogo Conexiones existentes.

  1. En la lista Mostrar situada en la parte superior del cuadro de diálogo, seleccione la categoría de conexiones para la que desea seleccionar una conexión, o seleccione Todas las conexiones existentes (que es la opción predeterminada).
  2. Seleccione una conexión de la lista Seleccione una conexión y, a continuación, haga clic en Abrir.
  3. Especifique una ubicación. Realice uno de los procedimientos siguientes:
    • Para colocar un informe de tabla dinámica en una nueva hoja de cálculo a partir de la celda A1, haga clic en Nueva hoja de cálculo.
    • Para colocar el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango en el que desea colocar el informe de tabla dinámica.

De forma alternativa, haga clic en Contraer diálogoImagen del botón para ocultar el cuadro de diálogo de forma temporal, seleccione la celda de inicio en la hoja de cálculo y, a continuación, haga clic en Expandir diálogoImagen del botón.

  1. Haga clic en Aceptar.

Se agrega un informe de tabla dinámica vacío, con la Lista de campos de tabla dinámica visible, a la ubicación especificada.

Volver al principio Volver al principio

Agregar, distribuir y organizar los campos del área Rótulos de fila

Utilice la Lista de campos de tabla dinámica para agregar, distribuir y organizar los campos y para asegurarse de que todos se han agregado al área Rótulos de fila.

  1. Haga clic en el informe de tabla dinámica.
  2. Siga uno o varios de estos procedimientos:
    • Para agregar campos al informe, siga uno o varios de estos procedimientos:
      • Active la casilla de verificación situada junto a cada nombre de campo en la sección de campos de la Lista de campos. Cada campo se coloca en un área predeterminada de la sección de diseño, aunque también se pueden reorganizar los campos.

De forma predeterminada, los campos no numéricos se agregan al área Rótulos de fila, los numéricos se agregan al área Valores y los de fecha y hora al área Rótulos de columna.

  • Para mover un campo al área Rótulos de fila, haga clic con el botón secundario del mouse (ratón) en el nombre del campo y, a continuación, seleccione Agregar a etiquetas de fila en el menú contextual.

 Sugerencia   También puede hacer clic en un nombre de campo, mantener presionado el botón del mouse y arrastrar ese campo desde la sección de campos a un área de la sección de diseño.

  • Para reorganizar los campos, haga clic en el nombre del campo en una de las áreas y, a continuación, seleccione uno de los siguientes comandos:
Subir     Sube el campo una posición en el área.
Bajar     Baja el campo una posición en el área.
Mover al principio     Mueve el campo al principio del área.
Mover al final     Mueve el campo al final del área.
Mover a rótulos de fila     Mueve el campo al área Rótulos de fila.
  • Para quitar un campo, haga clic en su nombre en una de las áreas de diseño y, a continuación, haga clic en Quitar campo o desactive la casilla de verificación situada junto a cada nombre de campo que desee quitar en la sección de campos.

 Sugerencia   También puede hacer clic en un nombre de campo en la sección de diseño, mantener presionado el botón del mouse y arrastrar el nombre del campo fuera de la Lista de campos de tabla dinámica.

 Nota   Si se desactiva una casilla de verificación en la sección de campos, se quitan del informe todas las instancias de ese campo.

Volver al principio Volver al principio

Cambiar las opciones y la configuración de los campos de tabla dinámica

Para garantizar que un informe de tabla dinámica se comporte de forma similar a un rango externo, siga este procedimiento:

Cambiar las opciones de tabla dinámica    

  1. Haga clic en el informe de tabla dinámica.
  2. En la ficha Opciones, en el grupo Tabla dinámica, haga clic en Opciones.

Aparece el cuadro de diálogo Opciones de tabla dinámica.

  1. Para que el ancho de una columna se ajuste de forma automática tras una operación de actualización, haga clic en la ficha Diseño y formato y, a continuación, en la sección Mostrar, active la casilla de verificación Autoajustar anchos de columnas al actualizar.
  2. Para conservar el formato cada vez que se actualicen los datos, haga clic en la ficha Diseño y formato y, a continuación, en la sección Mostrar, active la casilla de verificación Mantener el formato de la celda al actualizar.
  3. Para asegurarse de que el menú Filtrar quite los elementos que ya no sean válidos, tras la operación de actualización, haga clic en la ficha Datos y, en el cuadro Número de elementos que desea conservar por campo, seleccione Ninguno.

Cambiar la configuración de los campos    

  1. Haga clic en el informe de tabla dinámica.
  2. Para incluir nuevos elementos en un informe de tabla dinámica que tenga un filtro aplicado, para cada campo, haga clic con el botón secundario en una celda, haga clic en Configuración de campo en el menú contextual, en la ficha Subtotales y filtros y, a continuación, en la sección Filtros, active la casilla de verificación Incluir nuevos elementos en el filtro manual.

Volver al principio Volver al principio

Distribuir el informe de tabla dinámica como una tabla bidimensional

Para distribuir el informe de tabla dinámica como una tabla bidimensional, siga este procedimiento:

  1. Haga clic en el informe de tabla dinámica.
  2. En la ficha Diseño, en el grupo Diseño, haga clic en Subtotales y, a continuación, en No mostrar subtotales.
  3. En la ficha Diseño, en el grupo Diseño, haga clic en Totales generales y, a continuación, en Desactivado para filas y columnas.
  4. En la ficha Diseño, en el grupo Diseño, haga clic en Diseño de informe y, a continuación, en Mostrar en formato tabular.
  5. En la ficha Opciones, en el grupo Mostrar u ocultar, haga clic en Encabezados de campo.

Volver al principio Volver al principio

Personalizar el diseño del informe de tabla dinámica

Los estilos y las opciones de estilo integrados de tabla dinámica están diseñados para datos multidimensionales, no para tablas bidimensionales. Cuando se acopla el informe en una tabla bidimensional, estos estilos no muestran los bordes de las celdas; además, las casillas de verificación Filas con bandas y Columnas con bandas del grupo Opciones de estilo de tabla dinámica de la ficha Diseño no afectan al formato de las celdas. No obstante, es posible personalizar un estilo de tabla dinámica de modo que cada celda cuente con un borde y que el informe de tabla dinámica utilice formato condicional para mostrar filas o columnas con bandas.

Personalizar el estilo de tabla dinámica    

  1. Haga clic en el informe de tabla dinámica.
  2. En la ficha Diseño, en el grupo Estilos de tabla dinámica, seleccione el estilo que desee y, a continuación, siga este procedimiento:
    1. Haga clic con el botón secundario en un estilo visible, desplácese por la galería o, para ver todos los estilos disponibles, haga clic en Más en la parte inferior de la barra de desplazamiento.
    2. Haga clic en Duplicar.

Se abre el cuadro de diálogo Modificar estilo rápido de tabla dinámica.

  1. De forma opcional, escriba un nombre nuevo en el cuadro Nombre.
  2. En el cuadro Elemento de tabla, seleccione Toda la tabla y, a continuación, haga clic en Formato.

Aparece el cuadro de diálogo Formato de celdas.

  1. Haga clic en la ficha Borde y cree un borde de celda.
  2. De forma opcional, haga clic en las fichas Relleno y Fuente y realice otros cambios.
  3. Haga clic en Aceptar dos veces.
  4. Haga clic en el botón Más que se encuentra en la parte inferior de la barra de desplazamiento y, a continuación, en la categoría Personalizado, haga clic en el estilo de tabla dinámica que acaba de crear.

Mostrar filas o columnas con banda mediante formato condicional    

  1. Seleccione todas las columnas del informe de tabla dinámica.
  2. En el grupo Estilos de la ficha Inicio, haga clic en la flecha situada junto a Formato condicional y, a continuación, en Administrar reglas.

Cinta de opciones de Outlook

Aparece el cuadro de diálogo Administrador de reglas de formato condicionales.

  1. Para agregar un formato condicional, haga clic en Nueva regla.

Aparece el cuadro de diálogo Nueva regla de formato.

  1. En Seleccionar un tipo de regla, haga clic en Utilizar una fórmula para determinar en qué celdas desea aplicar el formato.
    1. En Editar una descripción de regla, en el cuadro Dar formato a los valores donde esta fórmula sea verdadera, escriba una fórmula.
    2. Realice uno de los procedimientos siguientes:
      • Para aplicar bandas a las filas, escriba la siguiente fórmula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

  • Para aplicar bandas a las columnas, escriba la siguiente fórmula:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  • Para aplicar bandas a las filas y a las columnas, escriba la siguiente fórmula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  1. Haga clic en Formato para mostrar el cuadro de diálogo Formato de celdas.
  2. Seleccione el formato de número, fuente, borde o relleno que desee aplicar cuando el valor de la celda cumpla la condición, y a continuación, haga clic en Aceptar.

Puede seleccionar más de un formato. Los formatos que seleccione se mostrarán en el cuadro Vista previa.

Volver al principio Volver al principio

Publicar el libro en Excel Services

  1. Haga clic en el botón de Microsoft OfficeImagen del botón, en la flecha situada junto a Publicar y, a continuación, en Excel Services en Distribuir el documento a otras personas.
  2. En el cuadro Nombre de archivo, escriba la ruta de acceso al servidor y acepte el nombre sugerido para el libro o, si fuera necesario, escriba uno nuevo.

 Sugerencia   Si fuera necesario, busque el destino en la lista Guardar en o escriba la ruta de acceso al servidor antes del nombre del archivo. Por ejemplo, escriba http://servidor/sitio/nombre de archivo

 Nota   Excel únicamente puede publicar un libro en el servidor en formato de archivo basado en XML de Microsoft Office Excel 2007 (.xlsx) o en formato de archivo binario de Office Excel 2007 (.xlsb).

  1. Haga clic en Aceptar.
  2. Para comprobar que las áreas visualizables del libro aparecen correctamente en el explorador, active la casilla de verificación Abrir este libro en el explorador después de guardar.
  3. Haga clic en Guardar.

Para obtener más información, vea el artículo Publicar un libro en Excel Services.

Volver al principio Volver al principio

 
 
Corresponde a:
Excel 2007