EXCEL INTERMEDIO -

AVANZADO
EXCEL INTERMEDIO
AVANZADO
TABLAS DINÁMICAS
Información general sobre tablas
dinámicas y gráficos dinámicos
Puede usar una tabla dinámica para resumir, analizar, explorar y presentar datos de resumen.
Los gráficos dinámicos complementan a las tablas dinámicas al agregar visualizaciones a los
datos de resumen en una tabla dinámica y le permiten ver fácilmente comparaciones, patrones
y tendencias. Tanto las tablas dinámicas como los gráficos dinámicos le permiten tomar
decisiones informadas sobre datos críticos de su empresa. También puede conectarse a orígenes
de datos externos como tablas de SQL Server, cubos de SQL Server Analysis Services, Azure
Marketplace, archivos de conexión de datos de Office (.odc), archivos XML, bases de datos de
Access y archivos de texto para crear tablas dinámicas o usar tablas dinámicas existentes para
crear tablas nuevas.
Información sobre las tablas dinámicas
Una tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de
datos. Puede usar una tabla dinámica para analizar datos numéricos con mayor detalle y para
responder a preguntas imprevistas sobre los datos. Las tablas dinámicas están especialmente
diseñadas para:
* Consultar grandes cantidades de datos de muchas formas sencillas.
* Obtener subtotales y sumas de datos numéricos, resumir datos por categorías y subcategorías,
y crear cálculos y fórmulas personalizadas.
* Expandir y contraer los niveles de datos para destacar los resultados y profundizar en los
detalles de los datos de resumen de las áreas de interés.
* Trasladar filas a columnas o columnas a filas (o "pivotar") para ver diferentes resúmenes de los
datos de origen.
Información sobre las tablas dinámicas
* Filtrar, ordenar y agrupar los subconjuntos de datos más útiles e interesantes, así como darles
formato de forma condicional, para que pueda centrarse en la información que desee.
* Presentar informes en línea o impresos concisos, atractivos y anotados.
* Por ejemplo, aquí tiene una sencilla lista de gastos domésticos a la izquierda y una tabla
dinámica que se basa en ellos a la derecha:
Formas de trabajar una tabla dinámica.
Después de crear una tabla dinámica mediante la selección de su origen de datos, la
organización de los campos de la Lista de campos de tabla dinámica y la elección de un diseño
inicial, puede realizar las siguientes tareas mientras trabaja con una tabla dinámica:
-Expanda y contraiga datos y muestre los detalles subyacentes que pertenecen a los valores.
-Ordene, filtre y agrupe campos y otros elementos.
-Cambie las funciones de resumen y agregue los cálculos personalizados y fórmulas.
-Cambie el diseño del formulario y la disposición de los campos mediante los siguientes
procesos:
-Cambie el formato de la tabla dinámica: Compacto, Esquema o Tabular.
Formas de trabajar una tabla dinámica.
-Agregue, organice o borre campos.
-Cambie el orden de los campos o elementos.
-Cambie el diseño de las columnas, las filas y los subtotales mediante los siguientes procesos:
-Active o desactive los encabezados de campo en columna y fila, o muestre u oculte las líneas en
blanco.
-Muestre los subtotales por encima o por debajo de sus filas.
-Ajuste los anchos de columna al actualizar.
-Mueva un campo de columna al área de filas o un campo de fila al área de columnas.
-Combine o divida celdas para elementos externos de filas y columnas.
-Cambie el formato de visualización de los espacios en blanco y errores mediante los siguientes
procesos:
Formas de trabajar una tabla dinámica.
-Cambie cómo se muestran los errores y las celdas vacías.
-Cambie cómo se muestran los elementos y las etiquetas sin datos.
-Muestre u oculte las filas en blanco.
-Cambie el formato mediante los siguientes procesos:
-Cambie el formato de las celdas y rangos de forma manual y condicional.
-Cambie el estilo de formato de la tabla dinámica general.
-Cambie el formato de número para los campos.
-Incluya el formato de servidor OLAP.
Información sobre los gráficos dinámicos
Los gráficos dinámicos proporcionan representaciones gráficas de los datos en las tablas
dinámicas asociadas. Los gráficos dinámicos también son interactivos. Cuando crea un gráfico
dinámico, aparece el panel de filtro de gráfico dinámico. Puede usar este panel de filtro para
ordenar y filtrar los datos subyacentes del gráfico dinámico. Los cambios que realice en el diseño
y en los datos de una tabla dinámica asociada se reflejan inmediatamente en el diseño y en los
datos del gráfico dinámico y viceversa.
Los gráficos dinámicos muestran series de datos, categorías, marcadores de datos y ejes, al igual
que los gráficos estándar. También puede cambiar el tipo de gráfico y otras opciones como los
títulos, la ubicación de la leyenda, las etiquetas de datos, la ubicación del gráfico, etc.
A continuación le mostramos un gráfico dinámico basado en el ejemplo de tabla dinámica
anterior.
Información sobre los gráficos dinámicos
1200

1000

Enero
800
Febrero

600 Marzo

Abril

400 Mayo

Junio
200

0
Alimentación Combustible Educación Vestimenta Vivienda
Diferencias entre gráficos dinámicos y
gráficos estándares.
Si está familiarizado con los gráficos estándar, observará que la mayoría de las operaciones son
idénticas en los gráficos dinámicos. Sin embargo, existen algunas diferencias:
Orientación de la fila o columna A diferencia de un gráfico estándar, no puede cambiar la
orientación de las filas y columnas de un gráfico dinámico mediante el cuadro de diálogo
Seleccionar origen de datos. En su lugar, puede dinamizar las etiquetas de fila y columna
asociadas a una tabla dinámica para lograr el mismo efecto.
Tipos de gráficos Puede cambiar un gráfico dinámico a cualquier tipo de gráfico, salvo un
gráfico “xy” (dispersión), de cotizaciones o de burbujas.
Datos de origen Los gráficos estándar están vinculados directamente a las celdas de las hojas
de cálculo, mientras que los gráficos dinámicos se basan en el origen de datos de la tabla
dinámica asociada. A diferencia de un gráfico estándar, no se puede cambiar el rango de datos
de un gráfico en el cuadro de diálogo Seleccionar origen de datos de un gráfico dinámico.
Diferencias entre gráficos dinámicos y
gráficos estándares.
Formato La mayor parte del formato, incluso los elementos del gráfico que agrega, el diseño y
el estilo, se conserva cuando actualiza un gráfico dinámico. Sin embargo, no se conservan las
líneas de tendencia, las etiquetas de datos, las barras de error y otros cambios realizados en los
conjuntos de datos. Los gráficos estándar no pierden este formato una vez que se aplica.
Aunque no puede cambiar directamente el tamaño de las etiquetas de datos en un gráfico
dinámico, puede aumentar el tamaño de la fuente del texto para obtener un resultado similar.
Crear una tabla dinámica o un gráfico dinámico
a partir de los datos de una hoja de cálculo
Puede usar los datos de una hoja de cálculo de Excel como base para una tabla dinámica o un
gráfico dinámico. Los datos deben estar en formato de lista con etiquetas de columna en la
primera fila, ya que Excel las utiliza para los Nombres de campo. Todas las celdas de las filas
siguientes deben contener datos que se correspondan con el encabezado de su columna y no
debería mezclar tipos de datos en la misma columna. Por ejemplo, no debería mezclar valores
de moneda y fechas en la misma columna. Además, no debería haber filas o columnas en blanco
en el rango de datos.
Las tablas de Excel: Las tablas de Excel ya están en formato de lista y son ideales como datos de
origen de tabla dinámica. Al actualizar la tabla dinámica, los datos nuevos y los actualizados
desde la tabla de Excel se incluyen de forma automática en la operación de actualización.
Crear una tabla dinámica o un gráfico dinámico
a partir de los datos de una hoja de cálculo
Usar un rango con nombre dinámico: Para facilitar la actualización de una tabla dinámica,
puede crear un rango con nombre dinámico y utilizarlo como el origen de datos de la tabla
dinámica. Si se amplía el rango con nombre para incluir más datos, se incluirán los datos nuevos
al actualizar la tabla dinámica.
Con totales: Excel crea automáticamente los subtotales y totales generales en una tabla
dinámica. Si los datos de origen contienen subtotales y totales generales que ha creado usando
el comando Subtotales en el grupo Esquema de la pestaña Datos, use el mismo comando para
quitar los subtotales y totales generales antes de crear la tabla
dinámica.
Usar un origen de datos externo para crear
una tabla dinámica o un gráfico dinámico
Puede recuperar datos desde un origen de datos externo, como una base de datos, un cubo de
procesamiento analítico en línea (OLAP) o un archivo de texto. Por ejemplo, puede mantener
una base de datos de registros de ventas que desea resumir y analizar.
Archivos de conexión de datos de Office: Si utiliza un archivo de conexión de datos de Office
(ODC) para recuperar datos externos para su tabla dinámica, puede introducir directamente los
datos en una tabla dinámica. Le recomendamos recuperar datos externos para sus informes con
archivos ODC.
Datos de origen OLAP: Al recuperar datos de origen de una base de datos OLAP o desde un
archivo de cubo, los datos se devuelven a Excel solo como una tabla dinámica o una tabla
dinámica que se ha convertido a las funciones de una hoja de cálculo.
Datos de origen que no son OLAP: Son los datos subyacentes para una tabla dinámica o un
gráfico dinámico procedentes de un origen que no es una base de datos OLAP. Por ejemplo,
datos de bases de datos relacionales o archivos de texto.
Usar otra tabla dinámica como un origen
de datos
La caché de la tabla dinámica: Cada vez que crea una nueva tabla dinámica o un gráfico
dinámico, Excel almacena una copia de los datos del informe en la memoria y guarda esta área
de almacenamiento como parte del archivo de libro. Esto se llama la caché de la tabla dinámica.
Cada nueva tabla dinámica requiere memoria adicional y espacio en disco. Sin embargo, cuando
usa una tabla dinámica existente como fuente para una nueva en el mismo libro, ambas
comparten la misma caché. Al reutilizar la misma caché, el tamaño del libro se reduce y se
almacenan menos datos en la memoria.
Requisitos de ubicación: Para utilizar una tabla dinámica como fuente de otra, ambas deben
estar en el mismo libro. Si la tabla dinámica de origen se encuentra en un libro distinto, cópiela
en la ubicación del libro donde desea que aparezca la nueva tabla. Las tablas dinámicas y los
gráficos dinámicos de libros diferentes son independientes, cada uno tiene su propia copia de
los datos en la memoria y en los libros.
Usar otra tabla dinámica como un origen
de datos
Los cambios afectan a ambas tablas dinámicas: Al actualizar los datos de la nueva tabla
dinámica, Excel también actualiza los datos en la tabla dinámica de origen y viceversa. Al
agrupar o desagrupar elementos o crear campos calculados o elementos calculados en una de
ellas, ambas se verán afectadas. Si necesita que una tabla dinámica sea independiente de la
otra, puede crear una nueva basada en el origen de datos inicial, en lugar de copiar la tabla
dinámica original. Tenga en cuenta las implicaciones en la memoria que podría tener hacer esto
a menudo.
Gráficos dinámicos: Puede basar una nueva tabla dinámica o un gráfico dinámico en otra tabla
dinámica, pero no puede basar un gráfico dinámico nuevo directamente en otro gráfico
dinámico. Los cambios en un gráfico dinámico afectan a la tabla dinámica asociada y viceversa.
Cambiar los datos de origen de una tabla
dinámica existente
Los cambios en el origen de datos pueden provocar que haya datos diferentes disponibles para
el análisis. Por ejemplo, es posible que desee cambiar cómodamente de una base de datos de
prueba a una base de datos de producción. Puede actualizar una tabla dinámica o un gráfico
dinámico con nuevos datos que sean similares a la información de conexión de datos original si
vuelve a definir el origen de datos. Si los datos son sustancialmente diferentes y tienen muchos
campos nuevos o adicionales, será más fácil crear una nueva tabla dinámica o un gráfico
dinámico.
Actualizar para mostrar datos nuevos: Actualizar una tabla dinámica también puede cambiar los
datos que se muestran. Excel recupera campos nuevos en el rango de origen o en el rango con
nombre que haya especificado en las tablas dinámicas basadas en datos de hojas de cálculo.
Excel recupera los nuevos datos que cumplen con los criterios para la consulta subyacente o
datos que están disponibles en un cubo de OLAP en los informes basados en datos externos.
Puede ver los nuevos campos en la Lista de campos y agregar los campos al informe.
Cambiar los datos de origen de una tabla
dinámica existente
Cambiar los cubos OLAP que cree: Los informes basados en datos OLAP siempre tienen acceso a
todos los datos del cubo. Si ha creado un cubo sin conexión que contiene un subconjunto de los
datos en un cubo del servidor, puede usar el comando OLAP sin conexión para modificar el
archivo del cubo de forma que contenga datos distintos a los del servidor
Crear una tabla dinámica para analizar
datos de una hoja de cálculo
La posibilidad de analizar rápidamente los datos puede ayudarle a tomar mejores decisiones
empresariales. Aunque a veces es difícil saber por dónde empezar, especialmente cuando se
tiene gran cantidad de datos. Las tablas dinámicas son un excelente modo de resumir, analizar,
explorar y presentar los datos que, además, se pueden crear con unos pocos clics. Las tablas
dinámicas son muy flexibles y se pueden ajustar rápidamente en función de cómo se tengan que
mostrar los resultados. También puede crear gráficos dinámicos a partir de tablas dinámicas que
se actualicen automáticamente al hacerlo las tablas dinámicas.
Considerar los siguientes puntos:
Crear una tabla dinámica para analizar
datos de una hoja de cálculo
Los datos deben organizarse en formato tabular y no tener ninguna fila o columna en blanco
Los tipos de datos de columnas deben ser iguales. Por ejemplo, no debería mezclar las fechas y
texto en la misma columna.
Las tablas dinámicas trabajan en una instantánea de los datos, denominada caché, por lo que los
datos reales no se modifican de ninguna forma.
 Mes Tipo Gasto Valor
Enero Vestimenta $ 100,00
Enero Alimentación $ 150,00
Febrero Alimentación $ 200,00
Marzo Alimentación $ 80,00
Abril Alimentación $ 120,00

EJEMPLO Mayo
Junio
Enero
Enero
Alimentación $
Alimentación $
Alimentación $
Combustible $
350,00
110,00
65,00
20,00

Se requiere realizar un resumen por tipo de Febrero

Marzo
Educación
Vivienda
$
$
700,00
310,00
gasto y por mes de la siguiente tabla de gastos Abril Vivienda $ 480,00

de algunas facturas del primer semestre del Mayo

Junio
Alimentación $
Alimentación $
100,00
350,00
2018. Enero Vestimenta $ 100,00
Febrero Alimentación $ 80,00
Marzo Alimentación $ 30,00
Abril Alimentación $ 50,00
Mayo Alimentación $ 120,00
Junio Combustible $ 60,00
Enero Educación $ 750,00
Enero Vivienda $ 120,00
Enero Vivienda $ 35,00
Febrero Alimentación $ 789,00
Marzo Alimentación $ 120,00
Abril Combustible $ 450,00
Mayo Educación $ 120,00
Enero Vivienda $ 60,00
Febrero Vivienda $ 750,00
Marzo Combustible $ 120,00
Abril Educación $ 200,00
Mayo Vivienda $ 80,00
EJEMPLO
Seleccionamos INSERTAR > Tabla Dinámica
EJEMPLO
En el cuadro “Crear tabla dinámica” debemos:
o Seleccionar la tabla o rango donde se
encuentra la información a analizar. o Elegir el
lugar donde queremos colocar la tabla
dinámica.
EJEMPLO
En el área Nombre de campo de la parte
superior, active la casilla correspondiente a
cualquier campo que quiera agregar a la tabla
dinámica. Puede arrastrar y colocar
manualmente cualquier elemento disponible a
alguno de los campos de tabla dinámica. Si ya
no quiere un elemento de la tabla dinámica,
basta con que lo arrastre fuera de la lista de
campos o desactive su selección. La
posibilidad de reorganizar los elementos de
campo es una de las características de tabla
dinámica que hace que sea tan fácil cambiarle
rápidamente la apariencia.
EJEMPLO
Arrastramos los campos a las siguientes, para
este ejemplo:
o El “tipo gasto” colocamos en las “FILAS”; el
“Mes” en el área de la
“COLUMNAS”, y el “valor” en el área de
“VALORES”
EJEMPLO
Valores de tabla dinámica
Resumir valores por
De forma predeterminada, los campos de
tabla dinámica que se coloquen en el área
Valores se mostrarán como SUMA. Si Excel
interpreta los datos como texto, se mostrarán
como CONTAR. Por eso es tan importante
asegurarse de no mezclar tipos de datos en los
campos de valor. Para cambiar el cálculo
predeterminado, pulse primero en la flecha
situada a la derecha del nombre de campo y
seleccione después la opción Configuración de
campo de valor.
Valores de tabla dinámica
Después, cambie el cálculo en la sección
Resumir valores por. Tenga en cuenta que,
cuando modifique el método de cálculo, Excel
lo anexará automáticamente en la sección
Nombre personalizado, como "Suma de
Nombre de campo", pero puede cambiarlo. Si
hace clic en el botón Formato de número,
puede cambiar el formato de número de todo
el campo.
Valores de tabla dinámica
Mostrar valores como
En lugar de usar un cálculo para resumir los
datos, también puede mostrarlos como
porcentaje de un campo. En el siguiente
ejemplo, hemos cambiado nuestros importes
de gastos domésticos para que aparezcan
como % del total general en lugar de la suma
de los valores. Cuando haya abierto el cuadro
de diálogo Configuración de campo de valor,
puede hacer las selecciones en la pestaña
Mostrar valores como.
Valores de tabla dinámica
Valores de tabla dinámica
Muestre un valor como cálculo y como Actualización de las tablas dinámicas
porcentaje.
Si agrega nuevos datos al origen de datos de
Basta con arrastrar el elemento a la sección tabla dinámica, hay que actualizar las tablas
Valores dos veces y luego establecer las dinámicas que se hayan integrado en ese
opciones Resumir valores por y Mostrar origen de datos. Para actualizar una sola tabla
valores como para cada una de ellas. dinámica, puede hacer clic con el botón
derecho en algún lugar del rango de tabla
dinámica y luego seleccionar Actualizar. Si
tiene varias de las tablas dinámicas, seleccione
primero una celda en cualquier tabla dinámica
y luego, en la Cinta, vaya a Herramientas de
tabla dinámica > Analizar > Datos > Haga clic
en la flecha situada en el botón Actualizar y
seleccione Actualizar todo.
Eliminación de una tabla dinámica
Si creó una tabla dinámica y decide que ya no la quiere, basta con que seleccione todo el rango
de tabla dinámica y luego presione Eliminar. Esto no tendrá ningún efecto en otros datos, tablas
dinámicas ni gráficos relacionados. Si la tabla dinámica se encuentra en una hoja separada que
no tenga otros datos que quiera conservar, eliminar esa hoja es una forma rápida de quitar la
tabla dinámica.
Vista clásica del informe dinámico
Luego de haber creado su tabla dinámica y desea cambiar a vista clásica, haga lo siguiente:
Vista clásica del informe dinámico
Ubíquese en una celda de la tabla dinámica y en la ficha “Opciones”, haga clic en la opción
“Tablas dinámicas” y luego clic en “opciones”
Vista clásica del informe dinámico
En la nueva ventana, diríjase a la ficha “Mostrar” y active la opción “Diseño de tabla dinámica
clásica (permite arrastrar campos en la cuadrícula)” y termine haciendo clic en
Aceptar