TABLAS DINAMICAS

¿Qué es una tabla dinámica?

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las

características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te

estás perdiendo de utilizar una gran herramienta de Excel. Las tablas dinámicas te permiten resumir

y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes

columnas que formarán el reporte.

INTEGRANTES:Isabel Jaramillo

Elvis MacasDennise Mayon

Katherine NoboaDOCENTE: Ing. Karina GarcíaCURSO: Tercer Semestre “A”

UNIVERSIDAD TECNICA DE MACHALA

UNIDAD ACADEMICA DE CIENCIAS QUIMICAS Y DE LA SALUDCARRERA DE ENFERMERIA

CATEDRA DE INFORMATICA

TEMA:TABLAS DINÁMICAS

Y FORMATO DIFERENCIAL

MACHALA-EL ORO- ECUADOR2015

Funcionamiento de las tablas dinámicas

Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes

para poder obtener la información que necesitamos.

Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la

compañía entre las cuales se encuentra una columna con los productos de la empresa, también la

ciudad donde se vende y las ventas correspondientes para cada ciudad.

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así

que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada

producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica

para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre

diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a

comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla

dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones.

Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la

tabla dinámica:

Así tendrás un reporte que compara las ciudades y los productos y para cada combinación

obtendrás el total de ventas. Lo más importante es que las tablas dinámicas te permiten elegir entre

todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un

buen análisis de la información.

Partes de una tabla dinámica en Excel

Justo cuando se ha creado una tabla dinámica

se muestra en la parte derecha de la hoja la lista

de campos disponibles y por debajo las áreas

donde podemos arrastrar dichos campos. Estas

áreas denotan cada una de las partes de una

tabla dinámica.

Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla

dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos

filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.

Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas

de la tabla dinámica.

Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.

Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que

serán totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes

tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros

son solamente una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.

Cómo crear una tabla dinámica

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la

información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un

ejemplo claro de cómo crearlas.

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla

dinámica.

Ahora selecciona el comando Tabla dinámica que

se encuentra dentro del grupo Tablas de la ficha

Insertar.

Se

mostrará el cuadro de diálogo Crear tabla

dinámica. Si es necesario podrás ajustar el rango

de datos que se considerará en la tabla dinámica.

En este mismo cuadro de

diálogo se puede elegir si

se desea colocar la tabla

dinámica en una nueva

hoja de Excel o en una ya

existente. Haz clic en el

botón Aceptar y se creará

la nueva tabla dinámica

Excel agregará en la parte izquierda del

libro la tabla dinámica y en la parte

derecha la lista de campos. Esta lista de

campos está dividida en dos secciones,

primero la lista de todos los campos de

los cuales podremos elegir y por debajo

una zona a donde arrastraremos los

campos que darán forma al reporte ya

sea como columna, fila, valor o como un

filtro.

Para completar la tabla dinámica

debemos arrastrar los campos al área

correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el

campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que

resulta después de hacer esta configuración es la siguiente:

Dar formato a una tabla dinámica

Una vez que has creado una tabla dinámica, Excel

permite aplicarle formato fácilmente como si fuera una

tabla de datos. La ficha Diseño incluye comandos

especiales para aplicar formato a una tabla dinámica.

La ficha Diseño es una ficha contextual, por lo que

deberás seleccionar la tabla dinámica para que se muestre.

Esta ficha está dividida en tres grupos.

Diseño. Este grupo permite agregar subtotales

y totales generales a la tabla dinámica así

como modificar aspectos básicos de diseño.

Opciones de estilo de tabla

dinámica. Las opciones de este grupo

permiten restringir la selección de

estilos que se muestran en el grupo

que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”,

entonces se mostrarán a la derecha los estilos que tienen filas con bandas.

Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla

dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.

Modificar el tipo de cálculo de una tabla dinámica

De manera predeterminada Excel utiliza la función SUMA al momento de crear los totales y

subtotales de los valores de una tabla dinámica sin embargo es probable que necesites utilizar

alguna otra función diferente como el promedio o la cuenta de elementos.

Para cambiar la función que se utiliza en los valores resumidos

debes hacer clic sobre el campo de valor y seleccionar la

opción de menú Configuración de campo de valor:

En el cuadro de diálogo mostrado podrás cambiar la función que deseas utilizar:

Segmentación de datos en tablas

dinámicas

La segmentación de datos en tablas dinámicas es una nueva característica de Excel que permite

hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente

la información por más de una columna.

En primer lugar debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la

ficha Opciones y dentro del grupo Ordenar y filtrar deberás hacer clic sobre el comando Insertar

Segmentación de datos.

Excel mostrará el cuadro de diálogo Insertar

segmentación de datos.

En este cuadro deberás seleccionar los campos que deseas

utilizar como filtros en la tabla dinámica y Excel colocará un

filtro para cada campo seleccionado:

FORMATO CONDICIONALFormato condicional en Excel sirve para resaltar celdas interesantes, enfatizar valores y llamar la atención rápidamente sobre cifras deseadas. Para esto estas celdas deben cumplir con algunos criterios y condiciones determinados.

Excel ofrece los siguientes tipos de formatos condicionales para poder especificar qué condiciones se desea resaltar.

1. Resaltar reglas de celdas2. Reglas superiores e inferiores3. Barras de datos4. Escalas de color5. Conjuntos de iconos

A continuación se describen brevemente cada una de estas opciones de formato condicional.

1. RESALTAR REGLAS DE CELDAS

Es mayor que: Se aplicará el formato a todas las celdas con un valor mayor al especificado. La opción muestra el siguiente cuadro de diálogo:

Es menor que: El formato será aplicado a las celdas con un valor menor que el indicado

Entre: Excel evaluará las celdas para saber aquellas que tengan un valor dentro del rango indicado y se les aplicará el formato

Sirve para encontrar más fácilmente celdas específicas dentro de un rango de celdas, se les aplica formato basándose en un operador de comparación, dentro de estas se despliegan las siguientes.

Es igual a: Solo las celdas que sean

iguales al valor indicado tendrán el formato

Texto que contiene: esta opción resalta celdas que muestren valores en “texto” iguales al valor que nosotros determinamos

Una fecha: Esta opción nos permitirá resaltar fácilmente las celdas que contengan una fecha que cumpla con el criterio seleccionado: Hoy, Ayer, Mañana, En los últimos 7 días, Semana pasada, etc

Duplicar valores: Esta opción funciona para cualquier tipo de dato que tengamos en las celdas, ya sean números, fechas e inclusive texto

Esto mostrará un cuadro de diálogo que nos permitirá elegir si queremos resaltar los valores que están duplicados o los valores únicos

2. REGLAS SUPERIORES E INFERIORES

10 superiores: Se aplicará el formato exactamente a las 10 celdas que tengan los valores más altos. Es posible modificar la cantidad de celdas superiores a las que se aplicará el formato.

Sirve Para buscar los valores más altos y más bajos en un rango de celdas según un valor de corte que especifique.

10% de valores superiores: Excel aplicará el formato al 10% de las celdas que contengan los valores más altos. También es posible indicar un porcentaje diferente al 10%.

10 inferiores: El formato se aplica a las 10 celdas con los valores más bajos.

10% de valores inferiores: El formato es aplicado al 10% de las celdas con los valores más bajos dentro del rango.

Por encima del promedio: Excel obtiene el promedio de todos los valores numéricos del rango y aplica el formato a las celdas que tengan un valor por encima de dicho promedio.

Por debajo del promedio: Después de

obtener el promedio, el formato será

aplicado en las celdas que tengan un

valor inferior.

PASOS PARA CREAR EL FORMATO CONDICIONAL

Paso 1: Selecciona las celdas a las que quieres agregar el formato. (D4:D18)

Paso 2: En la ficha Inicio, haz clic en el comando Formato condicional. Un menú desplegable aparecerá.

Paso 3: Selecciona Resaltar reglas de celdas o Reglas superiores e inferiores. Aquí, vamos a elegir la primera opción. Verás un menú con varias reglas.

Paso 4: Selecciona la regla que quieras (Mayor que, por ejemplo).

Paso 5: En el cuadro de diálogo, introduce un valor en el espacio correspondiente. En este ejemplo, queremos dar formato a las celdas cuyo valor es superior a $ 18, así que vamos ingresar ese número.

Paso 6: Selecciona un estilo de formato en el menú desplegable. Verás que éste se aplica a las celdas que seleccionaste. (podemos escoger una de las opciones que ya están definidas o crear otro formato, si es la segunda opción debemos hacer clic en “formato personalizado”

Paso 7: nos aparecerá este cuadro de dialogo, donde contamos con las opciones de personalizar: Número, Fuente, Bordes y Relleno

Paso 8: Luego de personalizar hacer clic en Aceptar y nuestras celdas seleccionadas Se resaltan todos los valores mayores a 18 con el formato que nosotros establecimos

Pasos para quitar las reglas de formato condicional

Paso 1: Selecciona las celdas que tienen formato condicional.

Paso 2: En la ficha Inicio, haz clic en el comando Formato condicional.

Paso 3: Selecciona la opción Borrar reglas del menú desplegable.

Paso 4: Aparecerá un menú que te muestra varias opciones. En este ejemplo, vamos a elegir la opción Borrar reglas de toda la hoja.

3. BARRA DE DATOS Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La longitud de la barra de datos representa el valor de la celda. Una barra más grande representa un valor más alto y una barra más corta representa un valor más bajo. Las barras de datos son útiles para encontrar números más altos y más bajos especialmente con grandes cantidades de datos, como las mayores y menores ventas de juguetes en un informe de ventas.

Ejemplo 5: Comparar la venta mensual de una lista de productos

Aplicar Formato

1º. Selecciona el rango C4:C18

2º. En la ficha Inicio, en el grupo Estilos, haz clic en la flecha junto a Formato condicional

3º. Haz clic en Barras de datos y, a continuación se muestran diferentes colores de relleno: degradado y sólido, la opción Más reglas para realizar otras configuraciones. Selecciona un icono de la barra de datos.

El resultado será el siguiente:

Si deseas que se visualice solo la barra, entonces en la columna D copia los datos de la venta mensual, así:

4º. Ahora, selecciona D4:D18 y en Formato condicional à Barra de datos, selecciona la opción Más

reglas…

5º. En la ventana que aparece, debes activar la casilla Mostrar sólo la barra, debes seleccionar el tipo de relleno y color. Para este caso elegir un relleno sólido y un color rojo.

El resultado se muestra a la derecha.Para que se aprecie mejor el cuadro

4. ESCALAS DE COLOR Las escalas de colores son guías visuales que ayudan a comprender la variación y la distribución de datos. Una escala de dos colores permite comparar un rango de celdas utilizando una gradación de dos colores. El tono de color representa los valores superiores o inferiores. Por ejemplo, en una escala de colores verde y rojo, especifican que las celdas con el valor superior tengan un color más verde y las celdas de valor inferior tengan un color más rojo. Una escala de tres colores permite comparar un rango de celdas utilizando una gradación de tres colores. El tono de color representa los valores superiores, medios o inferiores. Por ejemplo, en una escala de colores verde, amarillo y rojo, especifican que las celdas con el valor superior tengan un color verde, las celdas de valor medio tengan un color amarillo y las celdas de valor inferior tengan un color rojo.

Ejemplo 6: Aplicar el formato Escala de color a la columna Semana 4, del cuadro de datos del ejemplo 4.

1º. Selecciona el rango E4:E15

2º. En la ficha Inicio, en el grupo Estilos, haz clic en la flecha situada junto a Formato condicional.

3º. Selecciona la opción Escalas de color y elige una escala de color de las que se muestran, por ejemplo verde,amarillo y rojo.

Quedando así:

La celda que tiene el valor más alto tiene el color verde más oscuro, de igual manera la celda que contiene el valor medio, tiene el color amarillo y la celda que tiene el valor más bajo tiene el color más oscuro. Las otras celdas muestran colores degradados.

5. CONJUNTO DE ICONOS

Utilice un conjunto de iconos para comentar y clasificar datos de tres a cinco categorías separadas por un valor de umbral. Cada icono representa un rango de valores. Por ejemplo, en el conjunto de iconos de 3 flechas, la flecha verde hacia arriba representa valores más altos, la flecha hacia el lado amarilla representa valores medios y la flecha hacia abajo rojo representa valores más bajos.

Ejemplo 7: Establecer el nivel de la Línea de Crédito de la lista de clientes.

Aplicar Formato

1º. Selecciona el rango B3:B19

2º. En la ficha Inicio, en el grupo Estilos, haz clic en la flecha situada junto a Formato condicional y, después, en Conjunto de íconos y, a continuación, selecciona un conjunto de íconos, por ejemplo tres flechas de colores.

El resultado será el siguiente:

ADMINISTRAR REGLAS

Excel, en forma predeterminada establece intervalos para los valores altos, medios y bajos respectivamente.

Pero uno puede Definir valores con nuevos intervalos, por ejemplo:

- Valores altos mayor o igual que 6000- Valores medios entre 3000 y 6000- Valores bajos menores que 3000

Si deseas cambiar los intervalos correspondientes a los valores altos, medios y bajos, en la opción Formato condicional, selecciona Administrar reglas.

Selecciona el formato a editar y haz clic en el botón Editar regla. Finalmente, defina los nuevos valores, como se muestra a continuación.

El resultado será el siguiente:

Bibliografía

http://exceltotal.com/funcionamiento-de-las-tablas-dinamicas/

http://si.ua.es/es/documentos/documentacion/office/pdf-e/tablas-dinamicas-pdf.pdf

http://exceltotal.com/resaltar-celdas-con-formato-condicional/

https://support.google.com/docs/answer/78413?hl=es

https://support.office.microsoft.com/es-es/article/Usar-un-f%C3%B3rmula-para-aplicar-

formato-condicional-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?CorrelationId=8104396c-b2d1-

4f51-8054-b6d298e13850&ui=es-ES&rs=es-ES&ad=ES

http://www.excelwithbusiness.es/portals/0/29_conditional_formatting_2007.pdf

https://tecnologiaeinformaticacji.files.wordpress.com/2013/04/unidad-2-formato-condicional-

y-grc3a1ficos.pdf