http://www.youtube.com/watch?v=Hye68x43y-c
jueves, 29 de abril de 2010
martes, 19 de enero de 2010
TABLAS Y GRAFICOS DINAMICOS
Crear una tabla dinámica
Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...
Para aquellos que tienen conocimientos de Access es lo más parecido a una consulta de referencias cruzadas, pero con más interactividad.
Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.
Para crear una tabla dinámica, Excel nos proporciona el Asistente para tablas y gráficos dinámicos.
Supongamos que tenemos una colección de datos de los artículos del almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.
Grafico1. Tabla de datos para realizar la tabla dinámica
Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.
Para acceder al asistente accedemos al menú Datos - Informe de tablas y gráficos dinámicos.
Aparece la primera pantalla de tres de las que se compone el asistente para tablas dinámicas, donde nos solicita que indiquemos la situación de los datos a analizar y qué tipo de informe vamos a crear.
Grafico 2. Asistente para crear tablas dinámicas.
En nuestro caso indicamos que vamos a seleccionar los de la Lista de Excel y que vamos a crear una Tabla dinámica.
En la segunda pantalla seleccionamos el rango dónde tenemos los datos a utilizar en la tabla dinámica. Debemos seleccionar todas las celdas incluyendo las cabeceras
Grafico 3. Seleccionar los campos
Grafico 4. Pregunta si deseo crear una nueva hoja de cálculo
En esta última pantalla debemos indicar dónde vamos a situar el resultado de la tabla dinámica. Hemos escogido situarla en una Hoja de cálculo nueva.
Si pulsamos sobre Diseño... accedemos a un cuadro de diálogo donde podemos personalizar la forma en que van a verse los datos en la tabla dinámica
Grafico 5. Asistente para seleccionar los datos a presentarse
En la imagen podemos apreciar que nos aparece una estructura formada por las filas, columnas, datos y además un campo para el nº de página. Y en el lateral derecho tenemos los campos disponibles de la tabla, los cuales podemos arrastrar a las diferentes partes del diagrama de la izquierda.
Por ejemplo podríamos contruir una tabla dinámica con la siguiente estructura:
- En el campo Fila arrastramos REF, las referencias de nuestros artículos.
- En el campo Columna arrastramos MES.
- Y en Datos arrastramos IMPORTE
Nos quedaría una estructura como podemos ver en la imagen de abajo.
Grafico 6. Insertar datos en la tabla
Tras realizar la tabla dinámica este sería el resultado obtenido
Grafico7. Tabla resultado
Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes.
Con esta estructura es mucho más fácil analizar los resultados.
Una vez creada la tabla dinámica nos aparece en la hoja, la barra de herramientas Tabla dinámica:
También aparece el cuadro Lista de campos de tabla dinámica que nos permite modificar la estructura de la tabla añadiendo campos a las distintas zonas, o bien utilizando el botón Agregar a en combinación con el cuadro de lista que aparece al lado del botón, o simplemente arrastrándo el campo sobre la zona deseada.
De igual forma podemos quitar un campo de un zona arrastrándolo fuera de la tabla.
Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.
Grafico 7.1 en el cual podemos modificar la estructura de la tabla
Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información.
Grafico 8. Tabla modificada
Eliminar una tabla dinámica
Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su .
totalidad y presionar la tecla Supr o con la tabla seleccionada hacer clic con el botón derecho del ratón y seleccionar Eliminar del menú Pop-up.
Aplicar filtros a una tabla dinámica
Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.
Los campos principales vemos que están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.
Por ejemplo, si pulsamos sobre la flecha del campo Mes nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.
Grafico 9. Filtros
Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Mostrar todo).
Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.
Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los artículos con referencia 1236 de Abril.
Obtener promedios en una tabla dinámica
Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.
Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú Pop-up con diferentes opciones, debemos escoger la opción Configuración de campo... y nos aparece un cuadro de diálogo como el que vemos en la imagen.
En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.
Grafico 10. Obtener promedios
Tablas dinámicas en tres dimensiones
Crear una tabla en tres dimensiones no es más que añadir un campo más a la tabla de origen y luego utilizarlo como campo principal para el resumen.
Por ejemplo, para continuar con nuestra simulación añadiremos un campo más de "Semana" y ahora podremos organizar por mes y semana.
Para crear la tabla dinámica en tres dimensiones procederíamos del mismo modo que hemos visto anteriormente, es decir:
- Accedemos a Datos - Informe de tablas y gráficos dinámicos.
- Seleccionamos el rango de datos, ahora con el campo de semana. (Puede que nos pregunte si queremos utilizar los datos de la anterior tabla dinámica, contestamos que NO).
- Y en la estructura de la tabla la única diferencia es utilar el campo Página para la tercera dimensión.
Por ejemplo si colocamos en Página el campo Mes, en Columna el campo Semana, veremos los resultados de un mes en cada página
Gráficos con tablas dinámicas
Para crear una gráfica de nuestra tabla dinámica debemos hacer clic con el botón derecho del ratón sobre cualquier celda de la tabla, y del menú Pop-up que nos aparece debemos seleccionar Gráfico dinámico, nos genera un gráfico de barras por defecto en una nueva hoja de gráficos. También podemos utilizar el botón de la barra Tabla dinámica.
Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos, debemos hacer clic con el botón derecho del ratón sobre el gráfico y del menú Pop-up que nos aparece escoger la opción Tipo de gráfico... y escoger el gráfico que más nos convenga, la mecánica a seguir para trabajar con gráficos es la misma que se vio en el tema de gráficos.
Ejercicio
Grafico 11. Datos para hacer un grafico dinámico
1. Selecciona el rango A1:G15
2. Selecciona del menú Datos la opción Informe de tablas y gráficos dinámicos.
3. Se abre el asistente para tablas y gráficos dinámicos.
4. Marca Lista o base de datos de Microsoft Office Excel
5. Marca Tabla dinámica.
6. Pulsa en Siguiente >.
7. En Rango: aparecerá el rango que has seleccionado, sino selecciona el rango A1:G15, el rango de datos que se corresponde con la tabla completa, incluyendo la cabecera.
8. Pulsa en Siguiente >.
9. Marca Hoja de cálculo nueva.
10. Presiona sobre el botón Diseño... para definir las zonas de la tabla dinámica.
11. Arrastra el campo Mes donde pone Página.
12. Arrastra el campo Semana donde pone Columna.
13. Arrastra el campo Cliente donde pone Fila.
14. Arrastra el campo Articulo donde pone Datos.
15. Arrastra el campo Total donde pone Datos.
16. Presiona sobre Aceptar.
17. Volvemos a la tercera pantalla de asistente.
18. Haz clic sobre Finalizar.
19. Nos aparece la tabla dinámica en una nueva hoja. En la zona de página vemos (Todas) esto significa que estamos visualizando el resumen de todos los meses.
20. Abre la lista desplegable del campo MES, selecciona Enero y pulsa Aceptar.
21. Ahora nos aparece el resumen del mes de Enero.
22. Abre la lista desplegable del campo MES, selecciona (Todas) y pulsa Aceptar.
23. Nos vuelve a aparecer el resumen completo.
24. Para cada cliente y semana podemos ver la suma del campo ARTICULO y la suma del campo TOTAL. Con la suma del campo TOTAL tenemos la suma de lo que hemos vendido, pero con la suma del campo artículo no obtenemos información útil ya que el campo ARTICULO contiene códigos de artículos, nos sería más interesante saber cuántas ventas hemos realizado, contando los artículos del campo ARTICULO.
25. Para ello debemos cambiar la operación que se efectúa sobre el campo.
26. Posiciónate en cualquier celda correspondiente a la suma de ARTICULO y haz clic con el botón derecho.
27. Selecciona la opción Configuración de campo, también puedes utilizar el botón de la barra Tabla dinámica.
28. En el cuadro de diálogo que se abre, selecciona la operación Cuenta y haz clic en Aceptar.
29. Observa el resultado.
30. Ahora vamos a quitar ese total de la tabla dinámica.
31. Despliega la lista Datos, desmarca el campo Cuenta de ARTICULO y haz clic en Aceptar.
32. En la zona de datos ahora queda únicamente la suma de TOTAL.
33. Para añadir un nuevo resumen a la zona de datos.
34. Arrastra de la lista de campos, el campo CANTIDAD y suéltalo sobre la zona de datos.
35. Es tan fácil como esto, ahora si no quieres una suma sino otra operación, la puedes cambiar como lo hemos hecho antes con el campo ARTICULO.
36. Por último cierra el libro de trabajo guardando los cambios realizados
Actividades
1 Abre el libro de trabajo Taller de la carpeta Mis documentos (este libro lo creaste en un ejercicio anterior).
Tenemos estos datos:
Grafico12. Datos para realizar la actividades
2 Crear una tabla dinámica a partir de la tabla anterior para saber de cada coche, en cada mes, cuántas horas hemos empleado en repararlo y el desembolso realizado.
3 Visualizar únicamente las reparaciones del mes de Enero.
4 Crear una gráfica de la tabla dinámica.
5 Guardar los cambios realizados con su apellido nombre actividad1.
Ejemplo: Rivera_actividadTD1.xlsx
Complete lo siguiente guarde en archivo de word
1. Para crear una tabla dinámica disponemos de un asistente.
a) Verdadero.
b) Falso.
2. Podemos aplicar filtros en una tabla dinámica.
a) Verdadero.
b) Falso.
3. Las tablas dinámicas de tres dimensiones se visualizan en forma de cubo.
a) Verdadero.
b) Falso.
4. En una tabla dinámica sólo podemos tener sumatorios
a) Verdadero.
b) Falso.
5. El botón sirve para crear una tabla dinámica.
a) Verdadero.
b) Falso.
6. Para poder crear una tabla dinámica...
a) ...es necesario seleccionar previamente las celdas.
b) ...se pueden seleccionar las celdas en un paso del asistente.
c) ...no hay que seleccionar ninguna celda.
7. El asistente se compone de...
a) ...2 pasos.
b) ...3 pasos.
c) ...4 pasos.
8. Cuando filtramos datos...
a) ...no se pueden filtrar datos.
b) ...se tiene que elegir un único valor a visualizar o todos los valores.
c) ...se tienen que marcar los valores que queremos visualizar
9. El campo Página sirve para...
a) ...saber en qué página nos encontramos cuando la tabla es muy larga.
b) ...representar tablas dinámicas en tres dimensiones.
c) ...nos dice el número de la hoja en la que nos encontramos
10. Para crear un gráfico de la tabla dinámica debemos...
a) Ir al menú Datos y escoger Grafico dinámico.
b) Hacer clic con el botón derecho del ratón sobre la tabla y escoger Gráfico dinámico.
c) Ambas son correctas.
d) Ninguna de las anteriores Para crear un gráfico de la tabla dinámica debemos...
a) Ir al menú Datos y escoger Grafico dinámico.
b) Hacer clic con el botón derecho del ratón sobre la tabla y escoger Gráfico dinámico.
c) Ambas son correctas.
d) Ninguna de las anteriores
lunes, 11 de enero de 2010
jueves, 10 de diciembre de 2009
Validación de Datos
Validación de Datos
Objetivos
· Aprender a validar datos en Excel
· Determinar el tipo de dato y rangos de validación
· Determinar criterios de filtrado para los datos
· Proteger hojas o celdas para restricción de ingreso de datos
Validación
Como su nombre lo indica la validación de datos nos permite que en una celda o rango de celdas solo se puedan ingresar datos que cumplan con algún criterio o condición.
Ejemplo:
Validar para las notas del ejercicio solo se puedan ingresar notas entre 0 ó 20
Para esto nos ubicamos en la pestaña Datos opción –validación de datos
Grafico 1. Validación de datos
Marcamos las celdas a ser validadas y luego presionamos la opción validación de datos
Grafico 2. Opción Validación de datos
Al presionar validación de datos, aparece el siguiente cuadro de dialogo.
Grafico 3. Tipo de datos para la validación
Como son las notas son de tipo de dato decimal presionamos clic en decimal, luego se activa las opciones para ingresar el valor mínimo y máximo dependiendo del rango que se tome.
Grafico 4. Rango de validación
Para nuestro ejemplo escogemos entre y aparecerá el siguiente cuadro de dialogo
Además en la pestaña de mensaje de entrada podemos ingresar mensajes de ayuda la cual se presentara cuando nos ubiquemos en las celdas validadas.
Si se requiere enviar mensajes de error también podemos personalizar dicho mensaje, hay tres tipos de estilos de error de advertencia e información.
En Excel quedará de la siguiente forma
Grafico 8. Mensajes de ayuda
Excel permite examinar la hoja y quitar duplicados en caso de ser necesarios
Si al ingresar un valor que no esté dentro del rango valido aparecerá el mensaje de error siguiente.
Grafico 9. Mensaje de error personalizado
Por ejemplo si se requiere ingresar datos en un columna que no existan duplicados se puede elegir la opción quitar duplicados, previamente se selecciona las celdas a ser quitadas.
Aparece el cuadro de dialogo en el cual selecciona la columna a quitar los duplicados en este caso como solo tenemos una columna presionamos aceptar.
Grafico 12. Columnas que se quitarán los duplicados
Al presionar aceptar el resultado será el siguiente:
Grafico 13. Resultado de quitar los duplicados.
FILTROS
Excel permite también la posibilidad de filtrar datos de acuerdo a algún criterio o condición.
Para esto damos un clic en la columna que se desea filtrar y no vamos a la pestaña inicio y opción filtros.
Grafico 14. Filtros
Dado el presente cuadro filtre la columna de observación para que muestre solo los alumnos aprobados
Grafico 15. Filtrar los alumnos aprobados
En filtro presenta las siguientes opciones:
Grafico 16. Opciones de filtrado
Grafico 17. Filtrado por color de celda
Grafico 16. Opciones de filtrado
En el filtro personalizado presenta varias opciones y la condición que debe cumplir
Grafico 18. Filtrado personalizado
Restringir Celdas
Para bloquear el ingreso de datos a una celda o rango de celdas, esta opción se encuentra en la pestaña de revisar.
Grafico 20. Permitir que los usuarios modifiquen rangos
Para esto se necesita marcar las celdas que no serán bloqueadas para marcar varias columnas simplemente se tiene presionado la tecla control y con el mouse presionado botón izquierdo seleccionamos varias columnas.
Grafico 21. Seleccionar varias columnas diferentes.
Vamos a restringir las columnas de promedio y observaciones para esto marcamos las columnas ord, Nombre, Nota1, Nota2, Nota3, Nota4, es decir, se selecciona las columnas que se podrán modificar.
Grafico 22. Selección de celdas para restringir ingreso de datos en las columnas Promedio y Observaciones
Luego presionamos la opción permitir que los usuarios modifiquen rangos.
Grafico 23. Cuadro de dialogo para restringir ingreso de datos
Si se desea se crea uno nuevo para volver a marcar el rango
Grafico 24. Rango a restringir
Como nosotros ya seleccionamos este rango no necesitaríamos entrar a esta opción, además podemos ingresar la contraseña lo cual debemos tener siempre presente no olvidarnos ya que luego si nos olvidamos no podremos deshacer los cambios.
Presionamos aceptar y luego la opción proteger hoja, aparece el siguiente cuadro.
Grafico 25. Marcar las dos primeras opciones y aceptar
Grafico 25. Marcar las dos primeras opciones y aceptar
De esta forma quedarán bloqueadas las columnas que no fueron seleccionadas
Grafico 26. Las 2 columnas quedarán bloqueadas para ingreso de datos.
Suscribirse a:
Entradas (Atom)