Post on 21-Sep-2018
18/02/2008
1
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Excel: IntroducciónExcel: Introducción
1. Introducción y objetivos2. El aspecto de la hoja de cálculo3. Referencias, cálculos y fórmulas4. Gráficos de Excel5. Bases de datos en Excel6. Análisis de los datos7. Compartir datos con otras aplicaciones
ContenidosContenidos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 22
18/02/2008
2
Brindar los conocimientos necesarios para el manejo adecuado del programa Microsoft Excel:
Brindar diversas opciones, y trucos para la realización de aplicaciones en el manejo de Microsoft ExcelExplicar la terminología básica utilizada en Microsoft ExcelExplicar el uso adecuado de las fórmulas, funciones, gráficas en el programa Microsoft ExcelDefinir los pasos necesarios para la manipulación de fuente, bordes, sombreados, y demás aplicaciones de Microsoft Excel
ObjetivosObjetivos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 33
Introducción: las Introducción: las hojas de cálculohojas de cálculoLas hojas de cálculo son aplicaciones que facilitan la creación y manipulación de tablas de datos. Incluyen muchas herramientas de cálculo, así como facilidades de formato, representación gráfica de la información, análisis de datos y mucho más.Las hojas de cálculo permiten:
Almacenar y manipular datos numéricos, alfanuméricos (texto), de fecha y hora, etcétera.
Analizar los datos y calcular fórmulas o funciones matemáticas aplicadas sobre los datos.
Formatear y representar gráficamente los datos.
Microsoft Excel es una de las aplicaciones de hojas de cálculo más conocidas y más utilizadas.Excel llama hojas a las tablas de datos. Y a los documentos, que son grupos de hojas, los llama libros.José Mª González RíosJosé Mª González Ríos 44Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
3
Microsoft ExcelMicrosoft Excel
José Mª González RíosJosé Mª González Ríos 55Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Al ejecutar Excel aparecen dos ventanas en la pantalla, una de las cuales se encuentra dentro de la otra.
Ventana de aplicación o ventana de programa, es la verdadera ventana de Excel y permite comunicarnos con el programa. Ésta permanecerá igual independientemente de la hoja de cálculo que se haya creado.La ventana de documento: su misión es mostrar la hoja de cálculo activa actualmente, así como el nombre del libro en la que está contenida. La ventana de documento no puede salir de los límites de la ventana de aplicación.
Los documentos de Excel se llaman libros de trabajo, y cada uno de ellos se compone de varias hojas de cálculo.Una celda va a ser el lugar donde reside un dato, pero en concreto la celda en la que se introducirá la información se denomina celda activa o celda actual
Elementos básicos de ExcelElementos básicos de Excel
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 66
18/02/2008
4
1. Barra de título:2. Barra de menú:3. La barra de herramientas estándar:
4. La barra de formato
5. La barra de fórmulas6. La barra de etiquetas7. Las barras de desplazamiento
Los elementos de la pantalla de ExcelLos elementos de la pantalla de Excel
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 77
Se entiende por moverse la acción de cambiar de lugar el puntero de la celda activa. Se entiende por desplazarse la acción de cambiar la parte de la hoja de cálculo que se ve en pantalla, pero sin cambiar la celda activa (utilizando las barras de desplazamiento horizontal y vertical).Un grupo de dos o más celdas contiguas reciben el nombre de rango de celdas o simplemente rango .Excel utiliza el formato SI:ID para definir un rango, donde SI representa la celda del extremo superior izquierdo, e ID representa la celda del extremo inferior derecho.Para seleccionar una fila o una columna se sitúa el ratón encima de la cabecera de la fila o de la columna. El puntero tiene que ser una cruz ancha. Luego hacemos clic y la columna o la fila queda seleccionada. Si se quieren seleccionar otras filas o columnas contiguas a la seleccionada, se arrastra el ratón.
Moverse, desplazarse por Excel y Moverse, desplazarse por Excel y seleccionarseleccionar
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 88
18/02/2008
5
Los documentos de Excel consisten en una serie de hojas.
Cada hoja consiste en una tabla de celdas.
Cada celda contendrá un dato o una fórmula.
Las hojas de ExcelLas hojas de Excel
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 99
Filas
Celda actual(Cursor de celda)
Columnas
Celdas
Los datos que se pueden colocar en las celdas pueden ser:Textos
Números: con o sin decimales, monetarios, de contabilidad, porcentajes o fracciones
Fechas
Horas
Las celdasLas celdas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1010
18/02/2008
6
Cada celda se encuentra en la intersección de una fila y una columna, identificándose así mediante los rótulos de las mismas:
En primer lugar una o dos letras: el rótulo de la columna correspondiente (A ... Z, AA ... ZZ).
A continuación un número: el rótulo de la fila correspondiente.
Por ejemplo, B4 identifica a la celda situada en la intersección de la segunda columna y la cuarta fila. También se dice que es la dirección de la celda.
Identificación de las celdasIdentificación de las celdas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1111
Identificación de la celda actual
En la hoja hay en todo momento una celda distinguida entre todas las demás. Es la celda actual y se distingue porque es en la que se encuentra el cursor de celda (recuadro grueso que rodea la celda).Si no hay celdas seleccionadas, las operaciones se realizan sobre la celda actual (inserción de un dato, edición, eliminación, ...), aquella en la que se encuentra el cursor de celda.Se puede colocar el cursor en una celda pulsando sobre ella. También se puede desplazar por la hoja de la forma habitual:
Comprueba lo que hace cada tecla (o combinación de teclas).
El cursor de celda El cursor de celda -- MoverseMoverse
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1212
18/02/2008
7
Otra forma de moverse por la hoja:Menú Edición/Ir a...
Pulsando F5
Aparecerá un cuadro de diálogo. Escribe en el recuadro Referencia la dirección de la celda a la que quieres desplazarte y AceptaExcel dispone de un método alternativo para agilizar la ejecución de ciertas órdenes. Este método se conoce como menú emergente o contextual pulsando el botón dcho. del ratón.
Mas sobre moverseMas sobre moverse
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1313
Para trabajar con algún libro de trabajo almacenado en un archivo se debe recuperar dicho archivo mediante la orden Abrir del menú Archivo o bien haciendo clic en el botón Abrir de la barra de herramientas estándar .Para guardar un libro de trabajo se debe recurrir a la orden Guardar del menú Archivo o bien hacer clic en el botón Guardar de la barra de herramientas estándar . Si el libro de trabajo no tiene nombre, aparecerá el cuadro de diálogo Guardar como para dar nombre al libro. También existe la posibilidad de utilizar a la orden Guardar como del menú Archivo.
Abrir un libro de trabajoAbrir un libro de trabajo
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1414
18/02/2008
8
1. Arrancar Excel. Distinguir la ventana de aplicación de la ventana de documento
2. Comprobar la diferencia entre libro de trabajo y hoja de cálculo3. Determinar cuál es la celda activa4. Moverse a la celda A5 utilizando exclusivamente las flechas5. Moverse a la celda D8 utilizando exclusivamente las flechas6. Moverse a la celda H277. Selecciona la celda a la derecha de la actual de varias formas diferentes8. Selecciona el rango B6:F129. Seleccionar el rango D4:D10, y darle el nombre de Prueba. Comprobar que
sucede si se utiliza la orden Ir a.10. Probar las opciones del comando Zoom del menú Ver11. Guardar la hoja de cálculo en la carpeta Mis Documentos con el nombre
Primera.xls (teclear sólo el nombre, la extensión la coloca la aplicación por defecto)
12. Salir de Excel.
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 1515
La interfaz de usuario de ExcelLa interfaz de usuario de ExcelExcel tiene una interfaz de usuario al estilo Microsoft Office. Similar a la de los demás programas de la suite y similar en general a la mayoría de las aplicaciones Windows.Aunque la utilidad de cada orden o cada botón es específica de Excel, los distintos elementos de la interfaz funcionan como se puede esperar:
Menús (con sus abreviaturas de teclado).Barras de herramientas (muchas; las que se ven se pueden elegir con el menú contextual de cualquier barra).Barras de desplazamiento.Órdenes de edición.La ayuda y el asistente.
Y muchas de las órdenes se pueden ejecutar de tres formas: opción de menú, botón de barra de herramientas y combinación de teclas.José Mª González RíosJosé Mª González Ríos 1616Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
9
Introducción de datos en las celdasIntroducción de datos en las celdasPara introducir un dato en una celda basta colocar en ella el cursor de celda y comenzar a escribir. Cuando se pulse la tecla Intro o Tab se dará por concluida la entrada.Para insertar fechas:
Barras o guiones separando las partes de la fecha (si el mes va en número). Ejemplos: 24/7/03, 24-jul-2003, 24 jul 2003.
Para insertar la fecha actual: Ctrl + ; (Ctrl + Mayús + ,)
Para insertar horas:Dos puntos para separar las partes de la hora.
Introducir una “a” o una “p” para diferenciar A.M. y P.M. después de la hora, separado por un espacio en blanco (por defecto, A.M.)
Ejemplos: 9:45 9:45 a 13:22:07 1:22 p
José Mª González RíosJosé Mª González Ríos 1717Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Introducción de datos en las celdasIntroducción de datos en las celdasEl dato puede no caber en la celda. Si se trata de texto, simplemente se trunca (a no ser que se indique otra cosa) y si se trata de un número se muestran barras cruzadas (####).Se puede ajustar el ancho de una columna arrastrando su borde.
Introduce los siguientes datos en las celdas que aparecen:
No utilices puntos de miles en los números. Si queremos podemos hacer que Excel los coloque por nosotros.José Mª González RíosJosé Mª González Ríos 1818Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
10
Introducción de fórmulas en las celdasIntroducción de fórmulas en las celdasPara introducir una fórmula en una celda, en lugar de un dato, se introduce en primer lugar un =. Excel interpretará lo que vaya a continuación como una fórmula.Una fórmula puede ser tan sencilla como una expresión aritmética en la que se utilicen los valores de otras celdas (numéricas).Ya sabemos cómo se referencian (identifican) las celdas.Cuando se pulse Intro o Tab se calculará la fórmula y se colocará el resultado en la celda (la celda contiene la fórmula, pero muestra el resultado):
Intro
José Mª González RíosJosé Mª González Ríos 1919Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Introducción de fórmulas en las celdasIntroducción de fórmulas en las celdasPara utilizar expresiones aritméticas dentro de las fórmulastenemos a nuestra disposición muchos operadores.Los operadores aritméticos más habituales son:
+ (suma) - (resta) * (multiplicación) / (división)
^ (potencia) % (porcentaje)
Coloca las siguientes fórmulas en la hoja:En las celdas C7 y D7 la suma de las cuatro celdas que tienen justo encima cada una de ellas.
En las celdas E3, E4, E5, E6 y E7 la suma de las tres celdas que tienen justo a su izquierda cada una de ellas.
Guarda el documento con el nombre “Ventas”.
José Mª González RíosJosé Mª González Ríos 2020Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
11
Crear la siguiente hoja de cálculo, guardarla como PEDIDO
EjemploEjemplo
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 2121
Selección de celdasSelección de celdasPara realizar operaciones sobre una o varias celdas hay que seleccionar primero la celda o el grupo de celdas.
Para seleccionar una celda basta pulsar sobre ella o desplazar el cursor hasta que esté sobre ella.
Para seleccionar un área rectangular de celdas adyacentes pulsamos sobre la primera y arrastramos hasta la última (o pulsamos sobre la primera y luego sobre la última teniendo la tecla Mayús presionada).
Para seleccionar celdas o grupos de celdas que no sean adyacentes basta seleccionar la siguiente celda o grupo manteniendo presionada la tecla Ctrl.
Para seleccionar una fila completa basta pulsar sobre el rótulo de la fila (botón a su izquierda que contiene su número).
Para seleccionar una columna completa basta pulsar sobre el rótulo de la columna (botón arriba que contiene su letra).
José Mª González RíosJosé Mª González Ríos 2222Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
12
Ejercicios de selección de celdasEjercicios de selección de celdasReproduce en la Hoja 2 estas mismas selecciones de celdas:
José Mª González RíosJosé Mª González Ríos 2323Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Más sobre introducción de datosMás sobre introducción de datosPara insertar datos repetidos en múltiples celdas:1. Seleccionar las celdas donde se desean introducir los
datos.2. Escribir los datos por teclado.3. Pulsar Ctrl + Intro.Repetición automática de texto dentro de una misma columna:
Si los primeros caracteres introducidos en una celda coinciden con el contenido de alguna celda de la columna, Excel propone automáticamente el resto del texto.Para aceptar la entrada propuesta pulsar Intro o Tab o una tecla de dirección (flechas).Para desechar la entrada propuesta, continuar escribiendo normalmente.
José Mª González RíosJosé Mª González Ríos 2424Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
13
Más sobre introducción de datosMás sobre introducción de datosPara replicar el contenido de una o varias celdas dentro de una misma fila o columna:1. Seleccionar las celdas que contienen los datos a replicar.2. Arrastrar la esquina inferior derecha de las celdas
seleccionadas abarcando todas las celdas dónde se desea copiar los contenidosIMPORTANTE: si el contenido de las celdas originales forma una serie, los datos replicados extenderán dicha serie (sólo en una de las dimensiones, en la otra se copian)
Si no queremos que los datos copiados extiendan la serie basta pulsar Ctrl mientras se arrastra.
José Mª González RíosJosé Mª González Ríos 2525Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Gestor de llenado
Donde se le puede sacar partido al autollenado es en las series de números, pero en este caso además del número que da comienzo a la serie se debe indicar el intervalo entre dos números consecutivos.El proceso es como sigue:1. Se introduce en la primera celda el número que da
comienzo a la serie.2. Se introduce en otra celda el siguiente número de la
serie. Excel de forma automática calcula el incremento restando ambos números.
3. Selecciona la primera y segunda celda.4. Ahora se procede a arrastrar el gestor de llenado.
AutollenadoAutollenado
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 2626
18/02/2008
14
Extensión de seriesExtensión de seriesEjemplos de extensiones de series:1 2 3 4 5 6 7 ...12 13 14 15 16 17 ...Lun mar mie jue ...1-ene 2-ene 3-ene 4-ene ...1-ene 1-mar 1-may 1-jul 1-sep ...Dato 1 Dato2 Dato3 Dato 4 ...Texto 1 Texto A Texto 2 Texto A Texto 3 Texto A ...1er periodo 2do periodo 3er periodo ...La extensión de la serie puede hacerse ascendente (arrastrando el ratón hacia abajo y hacia la derecha) o descendente (arrastrando hacia arriba y hacia la izquierda).
José Mª González RíosJosé Mª González Ríos 2727Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Numerar los pedidos del libro PEDIDO.XLS
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 2828
18/02/2008
15
EjercicioEjercicioReproduce, en la Hoja 2, lo siguiente (usando las facilidades de replicación de Excel):
José Mª González RíosJosé Mª González Ríos 2929Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Modificación de los datosModificación de los datosPara reemplazar el contenido completo de una celda:
Seleccionar la celda, escribir el nuevo dato y pulsar Intro.
Para corregir los datos de una celda, uno de estos métodos:Pulsar dos veces sobre la celda, editar y pulsar Intro.
Pulsar una vez sobre la celda y editar el contenido en la barra de fórmulas. Terminar pulsando Intro.
Barra de fórmulas
José Mª González RíosJosé Mª González Ríos 3030Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
16
Copiar y pegarCopiar y pegarPara copiar y pegar el contenido de un grupo de celdas:
1. Seleccionar el grupo de celdas que se desea copiar.
2. Copiar presionando Ctrl + C, pulsando el botón Copiar de la barra de herramientas o con la orden Copiar del menú Edición.
3. Colocarse sobre la celda a partir de la cual se desea copiar el bloque seleccionado.
4. Pegar el contenido presionando Ctrl + V, pulsando el botón Pegar de la barra de herramientas o con la orden Pegar del menú Edición.
También pueden usarse los menús contextuales (pulsador derecho del ratón).
José Mª González RíosJosé Mª González Ríos 3131Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Cortar y pegarCortar y pegarPara cortar y pegar el contenido de un grupo de celdas:
1. Seleccionar el grupo de celdas que se desea mover.2. Cortar presionando Ctrl + X, pulsando el botón Cortar de la
barra de herramientas o con la orden Cortar del menú Edición.3. Colocarse sobre la celda a partir de la cual se desea pegar el
bloque seleccionado.4. Pegar el contenido presionando Ctrl + V, pulsando el botón Pegar
de la barra de herramientas o con la orden Pegar del menú Edición.
Mover (cortar y pegar) también puede hacerse con el ratón, pulsando sobre uno de los bordes de la selección y arrastrando.Para eliminar el contenido de un grupo de celdas:
Seleccionar el grupo de celdas que se desea mover y pulsar Supr o elegir Borrar Todo en el menú Edición.
José Mª González RíosJosé Mª González Ríos 3232Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
17
Copia de fórmulasCopia de fórmulasCuando se copia una fórmula de una celda a otra, la forma en la que se pega depende de cómo se referencian otras celdas de la hoja en la fórmula.Con las direcciones normales de celdas que hemos visto hasta ahora, tales como A4, F7 o D1, al pegar la fórmula las referencias se adaptan a la situación de la celda en la que se pega.Supongamos que insertamos una fórmula en una celda:
La fórmula hace referencia a otras celdas de la misma columna.
José Mª González RíosJosé Mª González Ríos 3333Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Copia de fórmulasCopia de fórmulasSi copiamos y pegamos la fórmula en la celda de su derecha:
Se han trasladado las direcciones de las celdas en la fórmula para que ahora hagan referencia a las que están encima de la celda con la fórmula pegada.También se trasladan la referencias entre filas cuando la celda donde se pega está en otra fila.Con otros tipos de referencias de celdas, que veremos más adelante, el funcionamiento de la operación de pegado es distinto.
José Mª González RíosJosé Mª González Ríos 3434Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
18
EjercicioEjercicioCrea una hoja de cálculo (VentasTrimestrales) como la siguiente:
Los datos de las filas 4 a 8 se calculan:Las ventas brutas son las unidades por el PVP. Se aplica un 10% de descuento. Las ventas netas son las ventas brutas menos el descuento. Las comisiones son de un 20%. Y el neto es igual a las ventas netas menos las comisiones.
Obviamente, los totales también se deben calcular.Prueba a utilizar el botón Autosuma.
José Mª González RíosJosé Mª González Ríos 3535Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Deshacer las últimas accionesDeshacer las últimas accionesExcel permite deshacer las últimas acciones realizadas.Para deshacer la última acción se puede usar la orden Deshacer del menú Edición, presionar Ctrl + Z o pulsar el botón Deshacer de la barra de herramientas.Para deshacer las N últimas acciones, o se hace lo anterior N veces seguidas o se elige en la lista desplegable del botón Deshacer de la barra de herramientas la acción hasta la que se quiere deshacer.Sólo se pueden deshacer acciones consecutivas.Para rehacer una acción deshecha previamente o volver a realizar la última operación se puede usar la orden Rehacer del menú Edición, presionar Ctrl + Y o pulsar el botón Rehacer de la barra de herramientas.José Mª González RíosJosé Mª González Ríos 3636Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
19
BúsquedaBúsquedaPara buscar un dato en la hoja:1. Seleccionar la opción Buscar del menú Edición
(Ctrl + B).2. Introducir el dato a buscar.
Indicar si la búsqueda se realizará por filas o columnas.Si se desea distinguir entre mayúsculas y minúsculas, activar la opción Coincidir mayúsculas y minúsculas.Si se desea que se busquen las celdas cuyo contenido concuerda exactamente con el dato especificado, activar la opción Buscar sólo celdas completas.
3. Pulsar el botón Buscar siguiente para buscar la siguiente ocurrencia del dato.
José Mª González RíosJosé Mª González Ríos 3737Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Búsqueda y reemplazoBúsqueda y reemplazoPara reemplazar datos en la hoja:1. Seleccionar la opción Reemplazar del menú
Edición (Ctrl + L).2. Introducir el dato a buscar.
Seleccionar las distintas opciones como en el caso anterior.
3. Pulsar ...... el botón Buscar siguiente para buscar la siguiente ocurrencia del dato.... el botón Reemplazar para sustituir la ocurrencia encontrada.... el botón Reemplazar todas para sustituir todas las ocurrencias del texto especificado.
José Mª González RíosJosé Mª González Ríos 3838Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
20
Inserción de celdasInserción de celdasPara insertar celdas vacías:1. Seleccionar la posición o posiciones donde se desea insertar
las nuevas celdas.2. Seleccionar la opción Celdas... del menú Insertar.3. Seleccionar la opción Desplazar las celdas a la derecha o
Desplazar las celdas hacia abajo para indicar cómo se hará hueco a la nueva celda o conjunto de celdas.
José Mª González RíosJosé Mª González Ríos 3939Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Inserción de filas o columnasInserción de filas o columnasPara insertar una o varias filas nuevas:
1. Seleccionar la fila donde se desea insertar las nuevas filas.
2. Pulsar la opción Filas del menú Insertar (o la opción Insertar del menú contextual). Se insertarán tantas filas como se hayan seleccionado, desplazando el resto hacia abajo.
Para insertar una o varias columnas nuevas:1. Seleccionar la columna donde se desea insertar las nuevas
columnas.
2. Pulsar la opción Columnas del menú Insertar (o la opción Insertar del menú contextual).
Se insertarán tantas columnas como se hayan seleccionado, desplazando el resto hacia la derecha
José Mª González RíosJosé Mª González Ríos 4040Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
21
Eliminación de celdas, filas o columnasEliminación de celdas, filas o columnasPara eliminar una o varias celdas, selecciona(n) y se usa la orden Eliminar... del menú Edición o del menú contextual.
Para eliminar una o varias filas, se selecciona(n) y se usa la misma orden Eliminar...Se desplazarán las filas siguientes hacia arriba.Para eliminar una o varias columnas, se selecciona(n) y se usa la misma orden Eliminar...Se desplazarán las columnas siguientes hacia la izquierda.
José Mª González RíosJosé Mª González Ríos 4141Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
EjercicioEjercicioCrea la tabla de datos que se ve, sin preocuparte del aspecto. Tan sólo los textos y los números. Más adelante veremos cómo cambiar el aspecto de las celdas o cómo hacer que se vean los puntos de miles y los símbolos de euro.Eso sí, ajusta el tamaño de las columnas convenientemente.
Guarda el documento con el nombre “Gastos”.
José Mª González RíosJosé Mª González Ríos 4242Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
22
EjercicioEjercicio
Selección de celdas en la hoja del ejercicio anterior:
1. Selecciona la celda B2.2. Selecciona el conjunto de celdas B2 a B5 utilizando
el ratón.3. Selecciona el conjunto de celdas C2 a C5 usando la
tecla Mayús.4. Selecciona el conjunto de celdas B2 a B5, C3 y D3.5. Selecciona la fila 2.6. Selecciona la columna B.
José Mª González RíosJosé Mª González Ríos 4343Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
Ejercicio guiadoEjercicio guiadoMenús y barras de herramientas:
1. Mostremos el cuadro de diálogo “Abrir” de tres maneras distintas. Una vez visto pulsaremos “Cancelar” para cerrarlo.
Menú Archivo
Abrir.
Botón de la barra de herramientas .
Abreviatura de teclado Ctrl + A.
2. Mostremos la barra de herramientas Dibujo:Menú Ver
Barras de herramientas.
3. Quitamos de la barra de herramientas Dibujo el botón Rectángulo (Agregar o quitar botones, al final de la barra). Luego, volvemos a colocar el botón en la barra.
4. AyudaAbrimos el ayudante.
Buscamos ayuda sobre “guardar documentos” y la leemos.
Utilizamos la opción ¿Qué es esto? del menú Ayuda sobre la barra de fórmulas.
José Mª González RíosJosé Mª González Ríos 4444Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
23
EjerciciosEjerciciosReproduce los siguientes ejemplos:
Usa la esquinainferior derecha
Usa autocompletado de series
Usa autorelleno
Usa Ctrl + Intro
Evita el autocompletadomanteniendo presionada Ctrl
José Mª González RíosJosé Mª González Ríos 4545Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
EjercicioEjercicio1. Reproduce el siguiente documento:
2. Guárdalo con el nombre “Albarán”.
José Mª González RíosJosé Mª González Ríos 4646Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
18/02/2008
24
EjercicioEjercicio3. Busca todas las apariciones de la palabra “Tornillos”.
4. Repite la búsqueda distinguiendo mayúsculas y minúsculas. Comprueba que sólo aparecen dos resultados.
5. Busca todas las apariciones de la palabra “Destornillador”.
6. Repite la búsqueda con la opción “Buscar sólo celdas completas”. ¿Qué ha ocurrido?
7. Reemplaza el texto “Por grupos” por “Agrupados” (no incluyas los dos puntos al buscar).
8. Reemplaza la cantidad de “Destornillador tornillos planos”. Para ello:
En el cuadro de diálogo “Reemplazar” escribe “75” en el campo Buscar y “70” en Reemplazar con.
Pasa por alto la primera ocurrencia de “75”, ya que corresponde a “Llaves de paso”. Reemplazar sólo la segunda ocurrencia.
José Mª González RíosJosé Mª González Ríos 4747Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008
EjercicioEjercicio1. Abre el libro “Albarán” (Albarán.xls).2. Selecciona la celda “Tuercas” e inserta una celda en su lugar desplazando
hacia abajo.3. Repite lo mismo con la celda que contiene el “4000” relativo a “Tuercas”.4. Inserta el par “Alicates” y “60” en las nuevas celdas:
5. Deshaz todos los cambios hasta llegar a la tabla original.6. Selecciona otra vez la celda “Tuercas”.7. Inserta una fila en su lugar.8. Vuelve a insertar el par “Alicates” “60”.9. Copia toda la tabla y elimínala.10. Inserta una columna vacía entre las columnas de “Destornillador tornillos
estrella” y “Por grupos:”.
José Mª González RíosJosé Mª González Ríos 4848Curso de Curso de hojashojas de cálculo: Excel de cálculo: Excel –– 2007/20082007/2008
18/02/2008
25
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
El aspecto de la hoja El aspecto de la hoja de cálculode cálculo
El aspecto de las celdasEl aspecto de las celdasEl aspecto de cada celda puede ser establecido como se quiera, independientemente del aspecto de las otras celdas.Excel permite establecer las siguientes características de aspecto:
Fuente, tamaño, estilo, etcétera.La alineación del dato dentro de la celda, la sangría, la orientación y el ajuste.Los bordes y sombreados de la celda.Tamaño de las celdas (filas / columnas). Simplemente arrastrando los bordes de las filas / columnas. O la correspondiente opción del menú contextual del rótulo.
Además se pueden combinar celdas.Y hay otros ajustes para cada tipo de dato que afectan a la visualización del dato dentro de la celda.Empecemos por esto último.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5050
18/02/2008
26
Los tipos de datos y el formatoLos tipos de datos y el formatoLa forma en la que se representa cada dato depende de su tipo.Para el texto no hay nada que ajustar: simplemente se muestran los caracteres uno tras otro.Para las cantidades, sin embargo, se pueden establecer distintas formas de visualización: con o son decimales, con símbolo de moneda, con separadores de miles, etcétera.Todas las características que afectan al aspecto visual de la información contenida en una celda se pueden ajustar por medio del cuadro de diálogo "Formato de celda", al que se accede por medio de la orden de menú.Formato Celdas..., o por medio de la opción "Formato del celdas...“ del menú contextual de la celda a la que se quiere ajustar el formato (o Ctrl+1).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5151
Los tipos de datos y el formatoLos tipos de datos y el formato
Números en general
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5252
18/02/2008
27
Los tipos de datos y el formatoLos tipos de datos y el formatoCantidades monetarias Datos de contabilidad
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5353
Cantidades monetariasque se alinean convenientemente
Los tipos de datos y el formatoLos tipos de datos y el formato
Notación científica Porcentajes
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5454
18/02/2008
28
Los tipos de datos y el formatoLos tipos de datos y el formato
Números fraccionales Fechas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5555
Los tipos de datos y el formatoLos tipos de datos y el formato
Horas Formatos personalizados
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5656
Excel puede detectar el tipo de dato que introducimos, asignando un formato predeterminado para el dato de acuerdo con su tipo.
18/02/2008
29
EjercicioEjercicioAbre la hoja de cálculo "Ventas" y haz que sea como esta:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5757
EjercicioEjercicioCambia el formato ahora a este otro aspecto (más habitual):
Nota: se puede aplicar un mismo formato a varias celdas seleccionándolas antes de elegir la orden de formato.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5858
18/02/2008
30
Dar formato (según los pasos vistos) a la hoja Stock.xslhasta convertirla en:
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 5959
Se pide crear una hoja de cálculo para apuntar las llamadas que se hacen desde una casa. Se pide grabarla en un fichero que se llame LLAMADAS.XLS
EjerciciosEjercicios
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6060
18/02/2008
31
Realizar una hoja de cálculo en la que se tengan almacenadas las ventas de los vendedores de la empresa de los tres últimos trimestres
EjerciciosEjercicios
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6161
Características de fuenteCaracterísticas de fuentePor defecto, los datos se muestran en las celdas con fuente Arial en un tamaño de 10 puntos, en negro y sin estilos de fuente.
Si queremos que se muestren los datos con otras características de fuente, utilizamos la ficha Fuente del cuadro de diálogo Formato de celdas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6262
18/02/2008
32
AlineaciónAlineaciónPor defecto, los datos se alinean dentro de la celda de acuerdo con el tipo de dato que se trata. Por ejemplo, los textos se ajustan a la izquierda y los números a la derecha.
Si queremos cambiar la alineación de los datos dentro de las celdas utilizamos la ficha Fuente del cuadro de diálogo Formato de celdas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6363
Arrastrar el punto rojo o introducir
grados
Letra a letra en vertical
Ejercicio guiadoEjercicio guiadoVamos a crear una hoja de cálculo como esta:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6464
18/02/2008
33
EjercicioEjercicioHaz que la hoja de cálculo "Ventas" tenga este aspecto:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6565
EjercicioEjercicioCrea una hoja de cálculo de VentasTrimestrales que tenga este aspecto:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6666
18/02/2008
34
Bordes y sombreadosBordes y sombreadosLos bordes y los sombreados de las celdas se establecen con otras dos fichas del cuadro de diálogo Formato de celdas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6767
EjercicioEjercicioHaz que la hoja de cálculo "Ventas" tenga este aspecto:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6868
18/02/2008
35
EjercicioEjercicioHaz que la hoja de cálculo de ventas trimestrales tenga los siguientes aspectos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 6969
La barra de herramientas FormatoLa barra de herramientas FormatoMuchas de las opciones de formato que se encuentran en las fichas del cuadro de diálogo Formato de celdas se pueden acceder más fácilmente por medio de la barra de herramientas Formato:
Son las opciones que, en principio, más se utilizan.Averigua para qué sirve cada control de la barra de herramientas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7070
18/02/2008
36
Formato condicionalFormato condicionalSe puede hacer que el aspecto de una celda sea distinto dependiendo del valor que contenga la celda.
Se usa la opción de menú Formato Formato condicional:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7171
Otra condición
Para indicar el valor de una celda(seleccionándola)
AutoformatoAutoformatoExcel contempla varios formatos predeterminados que se pueden aplicar a las tablas de datos. Se selecciona el conjunto de datos de la tabla a los que se les quiere aplicar un formato estándar.Luego se elige la opción de menú Formato Autoformato:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7272
A no ser que se indiquelo contrario, se ajustala fuente, el estilo de número, la alineación, los bordes, las tramas y los tamaños de celdas.
18/02/2008
37
ImágenesImágenesEn las hojas de cálculo también se pueden insertar imágenes y similares, de la misma forma como se hace en Microsoft Word.
Para insertar una imagen se usa la opción de menú Insertar Imagen, que da paso a un submenú:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7373
Además de imágenes propiamente dichas (prediseñadas, desde archivo o desde escáner), también se pueden insertar autoformas, WordArt y organigramas.
WordArtWordArt y organigramasy organigramas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7474
18/02/2008
38
La barra de herramientas DibujoLa barra de herramientas DibujoCon la barra de herramientas Dibujo se pueden insertar cuadros de texto, líneas, rectángulos, círculos y similares, además de poder acceder también a WordArt, las utoformas o las imágenes prediseñadas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7575
EjercicioEjercicioReproduce la siguiente hoja de cálculo (una factura):
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7676
Imagen prediseñada(categoría Oficina)
18/02/2008
39
El formato de páginaEl formato de páginaComo las hojas de cálculo se imprimirán en algún momento, también se pueden establecer ciertas características de formato de la página.Para ver cómo se va a imprimir la hoja de cálculo se usa la opción de menú Archivo Vista preliminar (o el botón correspondiente de la barra de herramientas).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7777
El formato de páginaEl formato de páginaLa ventana de vista preliminar tiene varios botones que permiten, además de ver la página anterior o la siguiente, ver la página más grande o más pequeña (lo que también se consigue pulsando sobre la página), imprimir, configurar la página e insertar / quitar saltos de página.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7878
Se pueden arrastrar las marcas para cambiar los
márgenes de la página o las anchuras de las columnas.
Los saltos de página también se pueden insertar o quitar
con el menú Insertar.
18/02/2008
40
El formato de páginaEl formato de páginaLa configuración de la página se puede realizar fácilmente también con la opción de menú Archivo Configurar página...
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 7979
El formato de páginaEl formato de páginaLa ficha Márgenes permite establecer éstos, así como centrar la hoja de cálculo en la página.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8080
18/02/2008
41
Encabezados y pies de páginaEncabezados y pies de página
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8181
Encabezados y pies predefinidos
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Referencias, cálculos Referencias, cálculos y fórmulasy fórmulas
18/02/2008
42
Los nombres de las hojasLos nombres de las hojasPara hacer referencia a las celdas de una hoja se usará el nombre de la hoja. Por defecto Excel denomina las hojas como Hoja1, Hoja2, Hoja3, etcétera.Si queremos dar un nombre elegido por nosotros a un hoja basta con pulsar dos veces sobre el nombre de la hoja y escribir el nuevo (o usar la opción del menú contextual).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8383
Doblepulsación
Pulsar Intropara terminarEscribir el
nuevo nombre
Operaciones sobre hojasOperaciones sobre hojasSeleccionar una hoja
Pulsar sobre su pestaña (nombre).
Seleccionar varias hojasTras seleccionar la primera, seleccionar las otras manteniendo presionada la tecla Control. Si son varias consecutivas, se puede pulsar sobre la primera y luego sobre la última manteniendo presionada la tecla de cambio a mayúsculas.
Cuando están seleccionadas varias hojas, se dice que están agrupadas. Cuando varias hojas están agrupadas, el dato que se introduce en una celda de una de las hojas se coloca también automáticamente en la misma celda de las otras hojas del grupo.
Para desagrupar las hojas se puede usar la opción del menú contextual de cualquiera de las hojas o simplemente pulsar sobre una de las hojas, la que quedará seleccionada sola.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8484
18/02/2008
43
Operaciones sobre hojasOperaciones sobre hojasPara mover una hoja (cambiar su posición en la lista de hojas), simplemente se arrastra su pestaña hasta el lugar deseado. O se usa la correspondiente opción del menú contextual.
Para copiar la hoja arrastramos mientras mantenemos pulsada la tecla Control o marcamos la casilla "Crear una copia“ en el cuadro de diálogo "Mover o copiar".José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8585
Operaciones sobre hojasOperaciones sobre hojasPara insertar una hoja en blanco se selecciona la hoja delante de la que se quiere insertar la nueva y se utiliza la orden correspondiente del menú Insertar o la del menú contextual.
Para eliminar la hoja actual se usa la orden correspondiente del menú Edición o la del menú contextual.¡Atención! La eliminación de una hoja no podrá ser anulada. Como no se puede deshacer la operación, se pide confirmación.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8686
18/02/2008
44
Nombres de celdas o grupos de celdasNombres de celdas o grupos de celdasPara referirse a una celda o a un grupo de celdas podemos utilizar el conjunto de direcciones de todas las celdas del grupo o asignar un nombre al grupo. El uso de nombres tiene sus ventajas:
Los nombres son más descriptivos y fáciles de recordar.
Cuando se mueve una celda se lleva si nombre con ella.
Se pueden utilizar los nombres en expresiones o fórmulas exactamente igual que las direcciones.
Los nombres de celdas o grupos deben ajustarse a estas normas:Hasta 255 letras, dígitos, subrayados o puntos.
Comenzando por una letra o un subrayado.
No pueden ser direcciones de celdas (A200 no sería válido).
Sin distinguir mayúsculas y minúsculas.
Sin que se repita en todo el libro (único para todas las hojas).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8787
Nombres de celdas o grupos de celdasNombres de celdas o grupos de celdasPara dar nombre a la celda o al grupo de celdas, se puede usar el cuadro nombre: basta pulsar y escribir el nombre.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8888
También se puede usar el cuadro de diálogo Definir nombre, que se abre con la opción de menú Insertar
Nombre Definir.Este cuadro también permite eliminar nombres.
Celda seleccionada
18/02/2008
45
Nombres de celdas o grupos de celdasNombres de celdas o grupos de celdasPor último, se puede usar el contenido de una de las celdas del grupo para dar nombre al resto de las celdas. Se tratará de la celda superior, inferior, izquierda o derecha.Estando seleccionado el grupo se usa la orden de menú Insertar Nombre Crear:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 8989
Celda que se usa para dar nombre
Grupo de celdas al que se le da nombre
Nombres de celdas o grupos de celdasNombres de celdas o grupos de celdasLos nombres que se han dado a las celdas (o grupos) aparecen en la lista desplegable del cuadro nombre.
Eligiendo un nombre se selecciona la celda o grupo de celdas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9090
18/02/2008
46
Uso de nombres como referenciasUso de nombres como referenciasLos nombres se pueden usar para referirse a celdas o grupos de celdas de la misma forma que las direcciones de las celdas.Por ejemplo, si queremos que se muestre en otra celda la suma de un grupo de celdas al que se le ha dado nombre, se puede usar la función SUMA(nombre) en la barra de fórmulas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9191
Barra de fórmulas (Comenzar con = para
introducir una fórmula.)
Celda en la que se inserta la fórmula
Referencias de celdasReferencias de celdasComo ya sabemos, cada celda tiene una dirección asociada que está compuesta por la letra de fila y el número de columna.Para referirnos a una celda normalmente utilizamos su dirección.
Sin embargo, como también sabemos, cuando en una fórmula se utiliza la dirección de una celda tal cual, al copiar la fórmula se traslada la referencia de celda en la misma medida que la fórmula es trasladada.Esto se debe a que las direcciones de celdas se interpretan como referencias relativas.Si en la celda B3 se hace referenciaa la celda A2, en realidadse está haciendo referenciaa la celda que estáuna a la izquierda y una hacia arriba.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9292
18/02/2008
47
Referencias de celdasReferencias de celdasCuando se copia la expresión "= A2" de la celda B3 a la celda B4, la referencia de la expresión se adapta para hacer referencia a la celda que está una a la izquierda y una hacia arriba: la A3.
Si se quiere que cuando se copie la fórmula se respete la celda indicada y no se traslade, se debe usar una referencia absoluta. Para que se trate de una referencia absoluta hay que poner el signo $ delante de la letra y delante del número: $A$2.Ahora al copiar la fórmula sigue referenciándose la celda A2.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9393
Referencias de grupos de celdasReferencias de grupos de celdasPara hacer referencia a un grupo de celdas consecutivas, una sección rectangular de celdas (lo que Excel llama rango), se pone la referencia de la celda de la esquina superior izquierda (relativa o absoluta), dos puntos (:) y la referencia de la esquina inferior derecha.
A2:G6 $B$1:$C$5Si se quieren referenciar grupos disjuntos de celdas, basta juntarlos con punto y coma (;).
A2:G6;H1:K5Y si se quiere hacer referencia a celdas de otras hojas, basta colocar delante de la referencia el nombre de la hoja y un símbolo de admiración(!).
Hoja3!A2:G6O una mismas celdas de varias hojas:
Hoja1:Hoja3!B5:C8
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9494
18/02/2008
48
Un fallo en la ayuda de ExcelUn fallo en la ayuda de ExcelAcabamos de ver que para juntar celdas o grupos de celdas (lo que se denomina la unión de las celdas o grupos) se utiliza punto y coma (;).
A2:G6;H1:K5En la versión anglosajona de Microsoft Excel se utiliza la coma (,) para unir celdas o grupos de celdas. Como nosotros utilizamos la coma como separador decimal en los números, en la versión española se estableció el punto y coma para la unión.Sin embargo, la ayuda de Excel sigue diciendo que se use la coma para unir. No se debe hacer caso.No recomiendo pasar por la experiencia de estar días peleándose con un error en una fórmula hasta darse cuenta de que la ayuda de Excel estaba equivocada y debía usarse el punto y coma en lugar de la coma. Doy fe de que no es nada agradable.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9595
Referencias de celdasReferencias de celdasAlgunos ejemplos de referencias relativas:
Celda de la columna A y la fila 10 A10Celdas de la columna A y de las filas de la 10 a la 20 A10:A20Celdas de la fila 15 y de las columnas B a E B15:E15Todas las celdas de la fila 5 5:5Todas las celdas de las filas 5 a 10 5:10Todas las celdas de la columna H H:HTodas las celdas de las columnas H a la J H:JCeldas de las columnas A a E y de las filas 10 a 20 A10:E20
Referencias 3D: todas las celdas con igual dirección de varias hojas. Cuando se indique la celda de que se trata, seleccionar primero las hojas antes de pulsar en la celda (o grupo).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9696
18/02/2008
49
Ejemplo de referencia relativa: Se quiere realizar el promedio de cada una de las columnas, y situar el resultado en la fila 4:
Ejemplo de referencia absoluta: crear una hoja que calcule las tablas de multiplicar de los n números:
EjemploEjemplo
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9797
FórmulasFórmulasComo hemos visto, para introducir una fórmula en una celda basta con comenzar con un signo igual (=). A continuación del = irá la fórmula, que no es más que una expresión correcta.Las fórmulas son expresiones que combinan referencias a celdas, valores literales, operadores y funciones.
0,84 * (A5 + $G$3) + SUMA(Marzo)
Los valores literales numéricos se escriben de la forma usual, utilizando la coma como separador decimal como acostumbramos. Las cadenas de caracteres se encierran entre comillas dobles.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9898
Operadores
Valor literal
Referencias
Función
18/02/2008
50
OperadoresOperadoresOperadores aritméticosOperadores aritméticos
+ Suma
– Resta (A2 – A3) o cambio de signo (–A2)
* Multiplicación
/ División
% Porcentaje
^ Exponenciación(A8 ^ A9 El valor en A8 elevado al valor en A9)
Operadores relacionales (de comparación)Operadores relacionales (de comparación)= Igual a
<> Distinto de
< Menor que
<= Menor o igual que
> Mayor que
>= Mayor o igual que
Operador de concatenación de textoOperador de concatenación de texto& ("Super" & "mercado" "Supermercado")
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 9999
Devuelven un valor lógico(VERDADERO o FALSO)
Combinan rangos de celdas para los cálculosLos operadores de referencia son:
: (dos puntos)- operador de rango que genera una referencia a todas las celdas entre dos referencias, éstas incluidas. Ejemplo: B5: B15
; (punto y coma)- operador de unión que combina varias referencias en una sola. Ejemplo: SUMA(B5:B15;D5:D15)
(un espacio)- operador de intersección, que genera una referencia a las celdas comunes a dos referencias. Ejemplo: (B5:B15 A7:D7)
Fórmulas de referenciaFórmulas de referencia
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 100100
18/02/2008
51
Orden en el que se realizan las operacionesOrden en el que se realizan las operacionesCuando en una fórmula se utilizan varios operadores, Excel realiza las operaciones en el siguiente orden:
Operador dos puntos “:” Operador espacio “ “.Las negaciones (cambio de signo) “-”.
Luego los porcentajes “%”.
Le siguen las exponenciaciones “^”.
A continuación las multiplicaciones y las divisiones “*” y “/”.
Para seguir con las sumas y las restas “+” y “-”.
Las concatenaciones “&”.
Y por último las comparaciones “=“, “<“, “>“, “<=“, “>=“ y “<>“.
Si se quiere hacer que se evalúe antes una operación, basta encerrarla entre paréntesis (A1 + A2) * A3. En operadores con igual precedencia se evalúan de izquierda a derechaAntes de evaluar los operadores se habrán ejecutado las funciones.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 101101
EjerciciosEjerciciosEn una hoja de cálculo nueva, introducir valores numéricos en varias de las celdas.En otras celdas de la hoja introducir fórmulas usando los operadores aritméticos y de comparación disponibles, así como las celdas anteriores como operandos.Cambiar los valores de las primeras celdas y observar cómo cambian los valores calculados de las segundas.Introducir cadenas de texto en varias celdas distintas y concatenar el contenido de las mismas en otras celdas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 102102
18/02/2008
52
EjercicioEjercicioDiseñar una hoja de cálculo para usarla como Eurocalculadora.Se podrá utilizar para convertir de una moneda antigua de la zona euro a euros y viceversa.En una de las celdas se encontrará el tipo de cambio, el factor de conversión de la moneda elegida al euro (166,386 para pesetas).En otra celda se introducirá la cantidad en la moneda elegida, cuyo cambio en euros se mostrará automáticamente en una celda cercana.Y la inversa: una celda en la que se introducirá una cantidad en euros, cuyo cambio en la moneda elegida se mostrará automáticamente en otra celda cercana.Dar a la hoja de cálculo un aspecto agradable.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 103103
EjercicioEjercicioSupongamos que, por motivos de trabajo, debemos alquilar un coche de forma bastante asidua. Conocemos tres empresas de alquiler que tienen ofertas distintas:
Diseñar una hoja de cálculo que, introduciendo el nº de días que vamos a tener el coche en alquiler y el nº de kilómetros que pensamos realizar nos diga cuál es el coste de alquiler con cada una de las empresas, para así conocer cuál es la más barata en cada caso.Añadir una celda que nos diga cuál es la más barata.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 104104
Empresa 1 35 € de seguro + 0,45 €/km + 38 € al díaEmpresa 2 15 € de seguro + 0,3 €/km + 75 € al díaEmpresa 3 22 € de seguro + 110 € al día con kilometraje ilimitado
18/02/2008
53
Funciones de ExcelFunciones de ExcelExcel proporciona una amplia colección de funciones predefinidas para manipular y extraer información de los datos de una hoja de cálculo.Cada función tiene un nombre único y puede aceptar argumentos. Para ejecutar la función, se pone en la fórmula su nombre seguido de paréntesis. Entre los paréntesis se colocan los argumentos, si es que se admiten.Los argumentos son datos sobre los que trabaja la función. Pueden ser valores literales o referencias a celdas.
SUMA(A3:B7)
La función devuelve un resultado que será el que se utilice en sustitución de la función para seguir calculando la fórmula. Si se conoce la función que se quiere utilizar, se puede escribir directamente en la fórmula. Si no, Excel nos ayudará.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 105105
Categorías de funciones de ExcelCategorías de funciones de ExcelFunciones matemáticas y trigonométricas
SUMA, PRODUCTO, RAIZ, SENO, COSENO, ...
Funciones estadísticasMAX (máximo), MIN (mínimo), PROMEDIO, DISTR.NORM (distribución normal), DESVEST (desviación estándar), ...
Funciones lógicasY, O, NO, ...
Funciones financierasRealizan cálculos financieros (cálculo de depreciaciones, cálculo de intereses, cálculo de valores de una inversión, ...)
Funciones de fecha y horaFunciones sobre texto
CONCATENAR, MAYUS (a mayúsculas), MINUS (a minúsculas)
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 106106
18/02/2008
54
Uso de funcionesUso de funcionesVeamos un ejemplo de inserción de una función.Lo que queremos es contabilizar el número de celdas que satisfacen una determinada condición. En Excel existe una función que sirve para eso y que se llama CONTAR.SI.Aunque podemos insertar la función en medio de un fórmula, esta vez colocaremos tan sólo la función como fórmula.Lo primero que hacemos es ir a la celda donde queremos insertar la función.La función se puede escribir directamente en la propia celda o en la barra de fórmulas, precedida en ambos casos por un =.Pero como no estamos seguros de cómo se escribe la función, usamos el botón Pegar función (o la orden de menú Insertar Función).José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 107107
Uso de funcionesUso de funciones
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 108108
Barra de fórmulas
Celda en la que se pegará la función
18/02/2008
55
Uso de funcionesUso de funcionesEl cuadro Pegar función incluye todas las funciones definidas en Excel, organizadas por categorías. Si se conoce el nombre de la función pero no se sabe en qué categoría se encuentra, se puede localizar en la categoría Todas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 109109
CONTAR.SI(rango;criterio)
Argumentos que toma esta función: rango: grupo de celdas a comprobar
criterio: condición que se quiere probar
Uso de funcionesUso de funcionesUna vez elegida la función, al pulsar Aceptar aparece un cuadro en el que se han de introducir los valores para los argumentos.
Como se puede ver, el cuadro proporciona bastantes indicaciones.Se puede introducir directamente cada argumento, pero si un argumento es una referencia, se puede usar el cuadro de selección de celdas (a la derecha del cuadro texto).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 110110
Cuadros de selección de
celdas
18/02/2008
56
Uso de funcionesUso de funcionesCuando se pulsa un cuadro de selección de celdas, se repliega momentáneamente el cuadro de la función:
Esto se hace para permitir ver (y seleccionar) las celdas de la hoja. Para seleccionar las celdas que componen la referencia, utilizamos los métodos habituales de selección.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 111111
Para dar por terminada la selección de celdas
Referencia que se construye
Uso de funcionesUso de funciones
El criterio se puede escribir directamente o recuperar de alguna celda. Por ejemplo, si queremos contar los valores que son mayores de 1200, basta escribir > 1200.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 112112
Si no colocamos las comillas, Excel las añade por nosotros
18/02/2008
57
Uso de funcionesUso de funcionesLa fórmula resultante, que en este caso sólo contiene la llamada a la función CONTAR.SI se puede ver en la barra de fórmulas:
Y en la celda, el resultado: 3 valores superiores a 1200.Si se quiere usar el valor de una celda como criterio, o bien se indica su dirección o se usa el cuadro de selección de celda.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 113113
Uso de funcionesUso de funcionesPor supuesto, cuando cambien las celdas sobre las que trabaja la función, se recalculará automáticamente la función para actualizar el valor de la celda en la que se encuentra ésta.Algunas funciones sólo pueden trabajar sobre listas (celdas consecutivas de una fila o de una columna) o sobre matrices (área rectangular de celdas).También puede haber argumentos opcionales en las funciones.Tanto en la descripción de la función como en el cuadro de argumentos los argumentos obligatorios aparecen en negrita y los opcionales sin negrita.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 114114
18/02/2008
58
Una función especial: la función SUMAUna función especial: la función SUMALa función SUMA se usa mucho más que todas las demás.Por esto, en la barra de herramientas hay un botón para calcular la suma de un grupo de celdas. Ya lo hemos utilizado:
Cuando se pulsa el botón Autosuma, Excel sugiere la serie de celdas a sumar y las preselecciona. Elige celdas con números que se encuentren o bien por encima de la celda resultado o bien a la izquierda.Si ha elegido bien la lista, basta pulsar Intro. Si no, habrá que seleccionar el grupo de celdas que corresponda.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 115115
AutocalcularAutocalcularAutocalcular es una facilidad de Excel que permite conocer el resultado de una función antes de colocarla en una celda de forma definitiva. Tan sólo sirve para las seis funciones más utilizadas y se usa pulsando con el pulsador derecho del ratón sobre la barra de estado, después de haber seleccionado el grupo de celdas.
Una vez seleccionada la función, en la barra de estado se muestra el resultado que se obtendría. Cuando se seleccionan varias celdas automáticamente aparece en la barra de estado el valor que se obtendría con la última función Autocalcular elegida.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 116116
18/02/2008
59
Copia y desplazamiento de fórmulasCopia y desplazamiento de fórmulasLas fórmulas se pueden copiar y desplazar (cortar y pegar).Ya sabemos que cuando se copia una fórmula se trasladan las referencias relativas en la misma medida que la fórmula.Sin embargo, cuando se desplaza una fórmula, las referencias a las celdas permanecen tal como estaban.Si lo que se quiere pegar en otra celda no es una copia de la fórmula, sino el resultado que arroja (lo que se ve en la celda), entonces se ha de elegir Pegado especial y luego Valores.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 117117
ErroresErroresCuando se comete un error en una fórmula, en la celda no se ve ningún resultado, sino una indicación del tipo de error que se ha cometido en la fórmula:
####La columna no es lo bastante ancha para mostrar el dato.
#¡DIV/0!La fórmula intenta dividir por cero.
#¿NOMBRE?Algún nombre de función o de celdas es incorrecto.
#¡VALOR!Alguna casilla contiene un valor que no se puede utilizar.
#¡REF!Hay alguna referencia a una celda que no es válida.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 118118
18/02/2008
60
Más sobre referenciasMás sobre referenciasNo se permiten referencias circulares: Una celda no puede referenciarse a sí misma.Se pueden analizar las referencias de las fórmulas o las celdas en las que se referencia otra celda:Para hacerlo, se utiliza la barra Auditoría:
Para ver esa barra de herramientas se usa la orden de menú Herramientas Auditoría Mostrar barra de Auditoría.Por ejemplo, para analizar las referencias que se usan en la fórmula se selecciona la celda con la fórmula y se pulsa el botón Rastrear precedentes (el primero).José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 119119
EjercicioEjercicio1. Introducir valores numéricos en varias celdas.2. Probar a realizar distintas operaciones sobre los valores
introducidos, utilizando operadores y funciones.3. Probar a copiar y a mover fórmulas. Observar cómo
cambian las referencias relativas de los argumentos al copiar las fórmulas, pero no al moverlas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 120120
18/02/2008
61
EjercicioEjercicioDiseñar una hoja de cálculo para llevar control de los gastos personales. Los gastos se contabilizarán por mes, y se agruparán bajo las siguientes categorías:
ROPA, COMIDASDISCOS, LIBROS, PERIÓDICOS, OCIO (Cine, Copas, etc.)TRANSPORTE (Gasolina, Metro, Tren, etc.)
Crear una nueva hoja de cálculo donde se calcularán los siguientes datos:
Gasto mensual total y gasto anual total.Gasto anual por concepto.Gasto medio mensual por concepto y gasto medio mensual total.
Crear una tercera hoja de cálculo donde se refleje la previsión de gastos por cada concepto para el año próximo, teniendo en cuenta la inflación (por ejemplo, de un 3%).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 121121
EjercicioEjercicioDiseñar una hoja de cálculo para hallar la velocidad y el tiempo de caída de un cuerpo pesado que se deja caer desde diferentes alturas (se despreciará la fuerza de rozamiento del aire).
Las alturas se especificarán en metros.
Estas alturas pueden ser 1, 2, 3, 4, 5, 10, 20, 30, 40, 50, 100, 200, 300, 400, 500, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000.
Las fórmulas que se deben aplicar son las siguientes:Tiempo de caída (en segundos):
T = RAIZ(2*Altura/9,8)
Velocidad del objeto al llegar al suelo (en Km/h):
V = 3,6*RAIZ(2*9,8*Altura)
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 122122
18/02/2008
62
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Gráficos de ExcelGráficos de Excel
GráficosGráficosLos gráficos son representaciones gráficas de datos numéricos.Facilitan la interpretación y comparación de los datos.
Una imagen vale más que mil palabras.Los gráficos estarán vinculados a los datos con los que se producen, de forma que si cambia una de las celdas usadas se actualizará automáticamente el gráfico.Los gráficos se crean con series de datos (rangos).Los datos deben encontrarse en una o más áreas rectangulares (no tienen por qué ser contiguas).Si las celdas superiores de las columnas o las celdas de la izquierda en las filas contienen etiquetas, éstas se usarán para rotular el gráfico.Con el asistente para gráficos es muy sencillo crear gráficos.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 124124
18/02/2008
63
El asistente para gráficosEl asistente para gráficosEl asistente para gráficos nos ayuda en la construcción del gráfico.Antes de invocar al asistente se puede seleccionar el grupo de celdas con las que queremos que se construya el gráfico.
Para crear el gráfico usamos la opción Gráfico del menú Insertar o pulsamos el botón "Asistente para gráficos" de la barra de herramientas.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 125125
El asistente para gráficosEl asistente para gráficosDe momento aceptaremos todos los ajustes que nos propone el asistente (simplemente pulsaremos el botón Siguiente).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 126126
1.- El tipo de gráfico
18/02/2008
64
El asistente para gráficosEl asistente para gráficos2. Los datos del gráfico(las celdas ya seleccionadas)
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 127127
3. Opciones del gráfico
El asistente para gráficosEl asistente para gráficos4. Ubicación del gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 128128
El resultado
18/02/2008
65
El gráficoEl gráficoEl gráfico generado se ha colocado en medio de la hoja donde están los datos. Se puede hacer que se coloque en otra hoja o en una nueva en la que sólo estará el mismo.
El gráfico es un objeto que se puede mover y cambiar de tamaño. El gráfico adapta siempre su aspecto al espacio disponible.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 129129
Se podrá modificar el gráfico a voluntad
(fuentes, colores, leyendas, ...)
Gráficos en dos dimensionesGráficos en dos dimensionesElementos de un gráfico en dos dimensiones:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 130130
Líneas de división Título Borde
Puntosde datos(barras)
Leyenda(series)
Nombres decategorías
Marca de graduaciónTítulo del eje XEje X (categorías)
Escala
Títulodel
eje Y
Eje Y (valores)
También puede haber rótulos de datos (los valores).
18/02/2008
66
Gráficos en dos dimensionesGráficos en dos dimensiones
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 131131
Columnas agrupadas Columnas apiladas Columnascompletamente apiladas
Barras agrupadas Líneas Líneas con marcadores
Gráficos en dos dimensionesGráficos en dos dimensiones
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 132132
Circular (de tarta) Circular seccionado Dispersión
Áreas Áreas apiladas Anillos
18/02/2008
67
Gráficos en dos dimensiones con efecto 3DGráficos en dos dimensiones con efecto 3D
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 133133
Columnas agrupadas Columnas apiladas Barras agrupadas
Circular (de tarta) Circular seccionado Áreas apiladas
Gráficos 3DGráficos 3DAdemás de los elementos de los gráficos en dos dimensiones, los gráficos 3D tienen algunos otros:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 134134
Plano de fondo
Eje X categorías) Plano inferior
Eje Y (series)
Eje Z(valores)
Esquinas
18/02/2008
68
Gráficos 3DGráficos 3D
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 135135
Barras agrupadas
Líneas Áreas
Superficie Cónico
Los datos del gráficoLos datos del gráficoAntes de poner en marcha la creación del gráfico, debemos tener claro cuáles son los datos a partir de los cuales queremos que se construya el gráfico.Normalmente los datos para el gráfico serán una o más series de datos. Una serie vendrá dada por una lista de celdas contiguas, bien a lo largo de una fila o bien a lo largo de una columna.Si hay varias series, se tratará de varias listas en filas o varias listas en columnas (no necesariamente contiguas).Aunque se pueden indicar las series en el propio asistente, lo mejor es seleccionar las celdas antes de iniciar el asistente.Éste interpretará las celdas seleccionadas como las distintas series para el gráfico.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 136136
18/02/2008
69
Las series de datosLas series de datosCada valor de una serie constituye un punto del eje Y. En el eje X se disponen tantas marcas como valores haya en la serie. El gráfico se construye en función de los pares (marca, valor), o lo que es lo mismo, las coordenadas (X, Y).
Dependiendo del tipo de gráfico que se elija, para cada X se resaltará el punto (X, Y), se dibujará una barra de altura Y o se representará el valor Y de alguna otra forma.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 137137
Las series de datosLas series de datosUn mismo grupo de celdas se puede interpretar de dos formas distintas, a la hora de generar el gráfico. Se puede interpretar que cada serie se encuentra en una fila de celdas o que cada serie se encuentra en una columna de celdas.
Series en filasSeries en filas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 138138
1ª serie: 72,12 60,10 72,12 30,052ª serie: 48,08 51,09 48,08 51,093ª serie: 24,04 24,04 24,04 24,04. . .
9ª serie: 240,40 90,15 72,12 24,04
18/02/2008
70
Las series de datosLas series de datosSeries en columnasSeries en columnas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 139139
4ª serie: 30,05 51,09 24,04 ... 24,043ª serie: 72,12 48,08 24,04 ... 72,12
2ª serie: 60,10 51,09 24,04 ... 90,151ª serie: 72,12 48,08 24,04 ... 240,40
Las series de datosLas series de datos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 140140
X
Y
Z
Si hay varias series, en cada X se representan juntos los valores correspondientes de cada serie: como varios puntos seguidos, como varias barras seguidas, etcétera.
En los gráficos 3D los valores de las series se corresponden con el eje Z, de forma que en el eje Y se encuentran los valores de las distintas series.
18/02/2008
71
Etiquetas para el gráficoEtiquetas para el gráficoSi la primera celda de una serie contiene texto, ese texto se tomará como nombre de la serie, omitiendo la celda a la hora de representar los valores.Los nombres de las series se indican en la leyenda del gráfico. Para los valores de cada serie se utiliza un color diferente, color que se muestra en la leyenda.Si una de las series está compuesta por celdas con texto, esos textos se tomarán como nombres de categorías, omitiendo la serie a la hora de representar los valores. El eje X es el eje de categorías, de forma que cada categoría identifica cada valor de la serie.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 141141
Etiquetas para el gráficoEtiquetas para el gráficoUn gráfico de series en filas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 142142
Serie extra:Categorías
(eje X)
Primerasceldas de
cada serie:Nombre decada serie(leyenda)
18/02/2008
72
Etiquetas para el gráficoEtiquetas para el gráficoUn gráfico de series en columnas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 143143
Serie extra:Categorías
(eje X)
Primerasceldas de
cada serie:Nombre decada serie(leyenda)
El Asistente para gráficos con detalleEl Asistente para gráficos con detallePaso 1: Elección del tipo de gráficoPaso 1: Elección del tipo de gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 144144
18/02/2008
73
El Asistente para gráficos con detalleEl Asistente para gráficos con detallePaso 2: Elección del tipo de gráficoPaso 2: Elección del tipo de gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 145145
Referencia de las celdas con los datos del gráfico. Si no se ha seleccionado el grupo antes de iniciar el asistente, se podrá seleccionar con el botón de forma similar a como se hace con las funciones.
¿Series en filas o series en columnas?
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleConfirmación de la interpretación de las celdas:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 146146
Para cadaserie
Celda que contiene el nombre de la serie.
Celdas que contienen los valores de la serie.
Seleccionar
Celdas que contienen los nombres de categorías.
18/02/2008
74
El Asistente para gráficos con detalleEl Asistente para gráficos con detallePaso 3: Opciones del gráficoPaso 3: Opciones del gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 147147
Rótulos que se pueden mostrar junto al gráfico
Se actualiza la vista previa con las elecciones que se hacen.
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleOpciones de los ejes:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 148148
Mostrar o no los rótulos de graduación de los distintos ejes
Z
X
Y
18/02/2008
75
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleOpciones de las líneas de división:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 149149
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleOpciones de la leyenda:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 150150
18/02/2008
76
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleOpciones de los rótulos de datos:
Que se vea o no cada valor encima de la barra que lo representa. Si hay muchos datos, con los rótulos el gráfico suele quedar muy cargado y difícil de interpretar.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 151151
El Asistente para gráficos con detalleEl Asistente para gráficos con detalleOpciones de la tabla de datos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 152152
La tabla de datos
18/02/2008
77
El Asistente para gráficos con detalleEl Asistente para gráficos con detallePaso 4: Ubicación del gráficoPaso 4: Ubicación del gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 153153
En una nueva hoja que inserta Excel con el nombre indicado.
En una otra hoja existente cuyo nombre se selecciona de la lista.
Edición del gráficoEdición del gráficoEl gráfico resultante:
Se pueden seleccionar los distintos elementos del gráfico y ajustar sus opciones con el menú Formato o el menú contextual.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 154154
18/02/2008
78
Edición del gráficoEdición del gráficoFormato del título del gráfico:
También se puede usar la barra de herramientas Formato
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 155155
Edición del gráficoEdición del gráficoFormato del título del gráfico:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 156156
18/02/2008
79
Edición del gráficoEdición del gráficoPara los títulos de los ejes tenemos las mismas opciones que para el título del gráfico.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 157157
Tanto el título del gráfico como los títulos de los ejes se pueden desplazar y ubicar en otros lugares del gráfico. Basta arrastrarlos.
Edición del gráficoEdición del gráficoFormato del título de la leyenda:Además de las fichas Tramas y Fuente, hay una ficha Posición.
También se puede arrastrar la leyenda al lugar en el que se quiere que se vea.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 158158
18/02/2008
80
Edición del gráficoEdición del gráficoFormato de las líneas de división:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 159159
Edición del gráficoEdición del gráficoPara el área de trazado (el fondo), los planos laterales (en gris claro en el gráfico) y el plano inferior (en gris oscuro en el gráfico) también está disponible una ficha Tramas con colores.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 160160
Planoslaterales
Planoinferior
Área detrazado
18/02/2008
81
Edición del gráficoEdición del gráficoFormato de los ejes: Además de las fichas Tramas, Fuente y Alineación hay otras dos.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 161161
Edición del gráficoEdición del gráficoFormato de las series de datos: Para cada serie tenemos la ficha Tramas y otras cuatro.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 162162
18/02/2008
82
Edición del gráficoEdición del gráfico
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 163163
Opciones de los gráficos 3DOpciones de los gráficos 3DPara los gráficos 3D tenemos también la posibilidad, además de todo lo anterior, de manipular el gráfico en el espacio (Gráfico Vista en 3D o menú contextual).
También se pueden arrastrar las esquinas del gráfico.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 164164
Esquina que se arrastra
18/02/2008
83
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Bases de datos en ExcelBases de datos en Excel
Bases de datos en ExcelBases de datos en ExcelHasta ahora hemos usado Excel básicamente para realizar cálculos con datos numéricos mantenidos en celdas de las hojas.En Excel también podemos trabajar con bases de datos. Las bases de datos contienen tablas; las filas son los registros; las columnas se corresponden con los campos de los registros.Las capacidades de Excel para el manejo de bases de datos son mucho más limitadas que las de Microsoft Access, ya que Access es una aplicación diseñada específicamente para gestionar bases de datos.Sin embargo, si las necesidades de gestión de la base de datos son sencillas, a menudo Excel puede servir perfectamente. Si las posibilidades de Excel se quedan cortas, entonces habrá que recurrir a Access u otro sistema de gestión de bases de datos.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 166166
18/02/2008
84
Conceptos básicos de bases de datosConceptos básicos de bases de datosLas tablas normalmente se interpretan como listas. Las filas de la tabla se corresponden con los elementos de la lista. La lista tiene tantos elementos como filas tenga la tabla (sin contar la fila superior de títulos). Cada elemento de la lista se denomina registro.
Campos; con nombres (títulos) distintosTodos los registros tienen la misma estructura. La estructura viene dada por un conjunto de campos, las columnas de la tabla. Cada campo es un dato del registro. Cada registro está compuesto por los datos de los campos que tiene definidos.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 167167
2º registro
Una base de datosUna base de datosTrabajaremos con una lista de ventas de consumibles informáticos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 168168
Los nombres de camposocupando una sola celda.
Sin filas en blanco.
La primera fila en blanco indica el final de la lista.
Se debe dejar una fila en blanco antes de los totales, promedios, ...
18/02/2008
85
Una base de datosUna base de datosEsta base de datos es muy sencilla. Tan sólo contiene una tabla. Las bases de datos a menudo contienen varias tablas, con la información distribuida entre esas tablas.La información de las tablas puede estar relacionada. Las bases de datos relacionales, como Access, son muy eficientes y permiten manejar cómodamente grandes listas de registros.Excel sólo permite trabajar con bases de datos muy simples. Para muchas ocasiones será suficiente, pero para bases de datos más completas, complejas y profesionales no resultará adecuado.La tabla de la base de datos de ejemplo establece seis campos: Fecha, Nombre (del cliente), Apellidos (del cliente), Artículo, Precio y Unidades.Hay 25 registros.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 169169
Operaciones sobre la base de datosOperaciones sobre la base de datosDos son las operaciones básicas que debemos poder realizar con la base de datos:
Organizar (ordenar) los registros con un orden determinado.
Separar (filtrar) los registros con el fin de localizar cierta información fácilmente.
Para ordenar la base de datos primero debemos tener el cursor de celda en cualquier celda de la tabla. Luego elegimos la opción Ordenar del menú Datos. Excel selecciona automáticamente todos los registros de la base de datos.Si quisiéramos ordenar sólo un grupo de registros tendríamos que seleccionar el grupo antes de elegir la opción del menú.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 170170
18/02/2008
86
Ordenación de la base de datosOrdenación de la base de datosAparece el cuadro de diálogo Ordenar. Excel detecta la fila de encabezamiento (nombres de los campos) y permite entonces seleccionar las columnas por los nombres de los campos.
Como se ve, se puede ordenar por hasta tres campos.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 171171
De menor a mayor o de mayor a menor
Ordenación de la base de datosOrdenación de la base de datos
Ordenado por apellidos (de la Z a la A).
Para iguales apellidos, por nombres de artículos.
Para iguales artículos, por fechas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 172172
18/02/2008
87
Ordenación de la base de datosOrdenación de la base de datos
Ordenado por artículos.
Para iguales artículos, por fechas.
Para iguales fechas, por apellidos.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 173173
EjercicioEjercicioCrea una base de datos de facturas para una tienda de electrodomésticos. Cada registro corresponderá a un electrodoméstico comprado por un cliente un determinado día.Como un cliente puede comprar varios electrodomésticos ese día, a cada factura le pueden corresponder varios registros.Los campos serán los siguientes:
Número (de factura): año seguido de un guión y de un entero.
Fecha.
Cliente: nombre y apellidos del cliente.
Artículo.
Precio.
Unidades.
Subtotal, IVA (16%) y Total (campos calculados).José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 174174
18/02/2008
88
EjercicioEjercicioIntroduce estos datos en la base de datos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 175175
Campos calculados
EjercicioEjercicioUna vez creada la base de datos, ordénala de las siguientes formas:
Por cliente y para cada cliente por artículo (en ambos casos de menor a mayor).
Por artículo, por cliente y por fecha (de más reciente a más antiguo).
Por número (de mayor a menor), por cliente y por artículo.
Por total, por artículo y por fecha.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 176176
18/02/2008
89
Filtrado de la base de datosFiltrado de la base de datosSiempre es más sencillo localizar un dato en un pequeño grupo de registros que en una interminable lista.Los filtros permiten seleccionar los registros de la lista que satisfagan ciertas condiciones. Cuando el filtro esté aplicado sólo se verán en la lista esos registros. (Por supuesto, el resto de registros siguen estando ahí, aunque ocultos.)Los filtros nos permiten centrarnos tan sólo en esos registros que cumplen las condiciones, por ejemplo para buscar un dato.AutofiltroAutofiltroLos filtros se aplican mediante la opción Autofiltro del menú Datos (o el botón correspondiente de la barra de herramientas). Excel lee el contenido de todos los registros y crea una lista para cada campo con los distintos valores encontrados. Al mismo tiempo utiliza el contenido de la celda actual para filtrar por su valor, de forma que sólo se vean los registros con ese valor.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 177177
AutofiltroAutofiltro
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 178178
Los registros con esos apellidos
En azul indican
filtrado
La flecha en azul indica que la columna está filtrada
18/02/2008
90
AutofiltroAutofiltroLas listas permiten aplicar rápidamente otros filtros:
Los filtros se acumulan, de forma que si estando aplicado el filtro de Apellidos seleccionamos algún Artículo, sólo se verán los registros con esos apellidos y ese artículo.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 179179
Quitar filtro
Valores encontrados en los registros
Filtro actual
AutofiltroAutofiltro(Las 10 más...) Para Precio:
Los 10 mayores precios (ordenado por precio después de aplicar el filtro)
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 180180
Mayores o menores Valores o porcentajes
18/02/2008
91
AutofiltroAutofiltro(Personalizar...)
Para Artículo:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 181181
Hasta dos condiciones necesarias (Y) o alternativas (O).
EjercicioEjercicioPara la base de datos de la tienda de electrodomésticos crea los filtros para obtener la siguiente información:
Facturas en las que se vendió un "Frigorífico Ser H17".
La factura 2003-13 (lo que se vendió).
Las ventas individuales del "Calefactor portátil MX112“ (ventas de sólo una unidad).
Las cinco ventas de mayor precio.
Las ventas de octubre de artículos de la marca OPS.
Las ventas de septiembre de artículos que no sean de OPS.
Las ventas de la segunda quincena de septiembre de artículos de la marca OPS.
Las ventas en septiembre de microondas o frigoríficos de más de 100 euros.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 182182
18/02/2008
92
FiltrosFiltrosPara quitar todos los filtros de una vez se puede usar la opción Mostrar todo del menú Datos.Con la base de datos filtrada se puede trabajar de la misma forma que con la base de datos al completo. Se imprimirá sólo el grupo de registros que se ven, se crearán gráficos sólo teniendo en cuenta esos registros, se ordenarán sólo ellos, etcétera.El filtrado también sirve para extraer subconjuntos de registros de la base de datos, bien para proporcionar a otro sólo esos datos o bien para crear otra base de datos con sólo esos registros.Aunque el subconjunto se puede obtener con Autofiltro, para esta tarea Excel proporciona un Filtro avanzado (menú Datos).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 183183
Filtro avanzadoFiltro avanzadoEl Filtro avanzado de Excel permite seleccionar registros por medio de condiciones más complejas.Las condiciones se colocan en otras celdas de la hoja de cálculo, de forma que tenemos mucha más flexibilidad.Para crear las condiciones primero copiamos la fila de nombres de campos en otro lugar de la hoja:
En las filas siguientes se escribirán las condiciones. En cada fila una condición alternativa (O). Dentro de la fila se pueden incluir varias condiciones necesarias (Y); basta con poner algo en varios campos.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 184184
18/02/2008
93
Filtro avanzadoFiltro avanzado
Registros con Apellidos = Hernández Pérez YY Precio < 50
OO con Artículo comenzando por CD YY Unidades > 30
Para aplicar el filtro avanzado se elige la opción en el menú Datos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 185185
¿Sin duplicados?
Por defecto, la lista entera
Celdas que contienen las condiciones
Filtro avanzadoFiltro avanzado
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 186186
18/02/2008
94
EjercicioEjercicioRepite los filtros anteriores de la base de datos de la tienda de electrodomésticos pero con filtros avanzados:
Facturas en las que se vendió un "Frigorífico Ser H17".
La factura 2003-13 (lo que se vendió).
Las ventas individuales del "Calefactor portátil MX112“ (ventas de sólo una unidad).
Las cinco ventas de mayor precio.
Las ventas de octubre de artículos de la marca OPS.
Las ventas de septiembre de artículos que no sean de OPS.
Las ventas de la segunda quincena de septiembre de artículos de la marca OPS.
Las ventas en septiembre de microondas o frigoríficos de más de 100 €.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 187187
EjercicioEjercicioCrea estos filtros avanzados en la base de datos de la tienda de electrodomésticos:
Compras de Hernández Pérez superiores a 100 € y compras de más de una unidad con un total mayor de 150 €.Ventas de electrodomésticos de la marca OPS de septiembre con un importe total superior a 300 € y ventas de electrodomésticos de la marca Ser de octubre.Facturas del 15 de septiembre, del 2 de octubre o del 15 de octubre, de frigoríficos o de microondas. (Tendrás que aplicar dos filtros avanzados consecutivos, copiando primero los registros filtrados por fechas en otra hoja.)Ventas con artículos que contengan la letra M, de menos de 100 €, una sola unidad y de alguna Ana o de algún Carlos.
¿Cuáles de estos filtros se pueden crear con Autofiltro? ¡Inténtalo!
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 188188
18/02/2008
95
EjercicioEjercicioExtrae (y copia en otra hoja) las facturas en septiembre de cada cliente por orden alfabético de clientes y de artículos que han comprado. De esta forma:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 189189
SubtotalesSubtotalesSe pueden crear subtotales (sumas, cuentas, promedios, ...) para los campos de la base de datos. Primero ordenamos por el campo sobre el que queramos obtener los totales (la fecha aquí).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 190190
MenúDatos
18/02/2008
96
SubtotalesSubtotales
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 191191
Ocultar (o mostrar) los detalles
Subtotales anidadosSubtotales anidados
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 192192
18/02/2008
97
Subtotales anidadosSubtotales anidados
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 193193
Click
Click
EjercicioEjercicioPara la base de datos de la papelería La Económica crea los siguientes listados:
Ventas por cliente y por fecha para cada cliente (apellidos), con subtotales por cliente y por fecha.
Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente.
Ventas por cliente y por fecha para cada cliente, con subtotales por cliente y por fecha. Sólo de septiembre.
Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente. Sólo ventas de octubre con un total inferior a 100 €.
Los subtotales sólo para el total de la venta.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 194194
18/02/2008
98
EjercicioEjercicioPara la base de datos de la tienda de electrodomésticos crea los siguientes listados:
Ventas por fecha y por cliente para cada fecha, con subtotales por fecha y por cliente.
Ventas por cliente y por fecha para cada cliente, con subtotales por cliente y por fecha.
Ventas de cada artículo (orden alfabético) y para cada artículo de cada fecha, con subtotales por artículo y por fecha.
Ventas de cada factura con subtotales.
Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente. Sólo ventas de septiembre con un total mayor de 100 €.
Los subtotales para los campos Subtotal, IVA y Total.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 195195
Formularios de datosFormularios de datosPara añadir nuevos registros o modificar los existentes de forma más cómoda. También para buscar registros fácilmente.
Datos Formulario
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 196196
Registro anterior Búsquedas
Campo calculado Siguiente registro
18/02/2008
99
Tablas dinámicasTablas dinámicasPara analizar los datos de la base de datos por dos campos a la vez. La tabla dinámica se construye con un campo para las filas y otro campo para las columnas. En la intersección el valor de otro campo numérico, el correspondiente a los otros dos.
Datos Informe de tablas y gráficos dinámicos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 197197
Tablas dinámicasTablas dinámicas
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 198198
Toda la base de datos (tabla)
Antes de finalizar hay que establecer el diseño
18/02/2008
100
Diseño de la tabla dinámicaDiseño de la tabla dinámica
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 199199
Arrastrar los campos
elegidos
La tabla dinámicaLa tabla dinámicaTotal vendido cada día de cada artículo
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 200200
Área de datos(centro)
18/02/2008
101
La tabla dinámicaLa tabla dinámicaSe puede complicar más la tabla, añadiendo más campos. Los podemos encontrar en la barra de herramientas Tabla dinámica:
Si se arrastra un campo a una fila o una columna, se agrupan los datos por el campo existente y el nuevo arrastrado:
El orden en el que se agrupen dependerá de cómo hayan quedado al arrastrar (en el ejemplo, 1º apellidos y luego artículo).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 201201
Se ha arrastrado Apellidos arriba
Campos
Ver o no los campos
La tabla dinámicaLa tabla dinámicaSi se arrastra el campo al centro, se mostrará el total, el promedio, etcétera, de ese campo para los valores de fila y columna.En el ejemplo se ha arrastrado el campo Unidades al centro:
Total vendido y total de unidades cada día para cada artículo comprado por cada cliente (con totales para cada cliente)
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 202202
18/02/2008
102
La barra de herramientas La barra de herramientas Tabla dinámicaTabla dinámica
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 203203
Ocultar o mostrar detalle. Si se quita detalle estando seleccionado el nombre del campo, se quita para todos los registros. Si está seleccionado un valor del campo sólo se quita detalle a ese registro.
Autoformato Asistente Actualizar tabla
Propiedades de la tabla dinámicaPropiedades de la tabla dinámicaEn el paso 3 del asistente o en el menú de la barra de herramientas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 204204
18/02/2008
103
Propiedades de los camposPropiedades de los campos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 205205
Campos de datosCampos de fila o columna
EjerciciosEjerciciosJuega con la tabla dinámica de la base de datos de la papelería La Económica, cambiando y añadiendo campos en la fila, en la columna y en el área de datos (centro). Juega también con las opciones.
Crea una tabla dinámica para la base de datos de la tienda de electrodomésticos. Juega con ella cambiando y añadiendo campos en la fila, en la columna y en el centro. Juega también con las opciones.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 206206
18/02/2008
104
Gráficos dinámicosGráficos dinámicosPara crear un gráfico dinámico a partir de la tabla dinámica utilizamos el correspondiente botón de la barra de herramientas.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 207207
Gráficos dinámicosGráficos dinámicosIgual que con la tabla dinámica, se pueden cambiar los campos que usa el gráfico dinámico arrastrando hacia o desde la barra de herramientas Tabla dinámica.
Y se pueden establecer todas las características del gráfico de la misma forma que con cualquier otro gráfico. Al pulsar otra vez el botón del Asistente para gráficos, aparece éste:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 208208
18/02/2008
105
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Análisis de los datosAnálisis de los datos
Análisis de los datosAnálisis de los datosUna de las capacidades más interesantes de Excel es la actualización automática de los cálculos, lo que permite no sólo disponer siempre de los resultados correspondientes, sino también poder realizar análisis sofisticados de los datos.Jugando con los datos de las tablas podemos realizar predicciones, obtener valores de entrada para determinados resultados deseados y otros análisis interesantes.En este tema repasaremos las diversas formas en las que podemos analizar los datos y obtener conclusiones.Algunas de las técnicas ya son conocidas, pero procede su revisión aquí debido a la utilidad que tienen para el análisis.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 210210
18/02/2008
106
Análisis con seriesAnálisis con seriesUna simple serie de valores puede convertirse en una buena herramienta de análisis, permitiendo comprobar cómo varía una fórmula respecto de una o varias variables.Por ejemplo, supongamos que queremos ver cómo varía el pago mensual de una hipoteca en función de la duración del préstamo.Comenzamos colocando en varias celdas los demás parámetros del préstamo.Sabemos que la fórmula que calcula el pago mensual de una hipoteca es PAGO. Toma los tres siguientes datos (en este orden):
Interés mensual (anual / 12).Número de pagos mensuales.Cantidad que se presta.
El número de pagos será la variable frente a la que queremos ver la variación del pago mensual. Por eso, la ponemos debajo...José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 211211
Análisis con seriesAnálisis con series
Se pueden añadir fácilmente otros cálculos, como la cantidad total satisfecha por intereses. Ahora ya sólo nos queda crear la serie para los años de hipoteca y copiar la fórmula.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 212212
Podemos seleccionar la celdacon el primer valor de años
y crear la serie conEdición Rellenar Serie
18/02/2008
107
Análisis con seriesAnálisis con series
Ahora podemos estudiar la tabla y encontrar el número de años que más nos convenga.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 213213
TendenciasTendenciasPodemos hacer que Excel amplíe una serie de valores calculando su tendencia proyectada más allá de los datos disponibles. Podemos hacer que Excel muestre la tendencia en un gráfico.
Por ejemplo, habiendo creado un gráfico de líneas que represente la variación del pago mensual frente al número de años, podemos elegir la opción Agregar línea de tendencia del menú contextual de la línea de valores de pagos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 214214
18/02/2008
108
TendenciasTendenciasEstablecemos las opciones para la tendencia que ha de añadir Excel ...
... y obtenemos el resultado deseado.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 215215
TendenciasTendencias
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 216216
Línea de tendencia
18/02/2008
109
Cálculos estadísticosCálculos estadísticosExcel incluye herramientas muy útiles para el procesamiento estadístico de los datos de nuestras hojas de cálculo.Para obtener datos estadísticos se utiliza la herramienta Análisis de datos, accesible a través del menú Herramientas:
La Estadística es una ciencia compleja que necesita ser estudiada antes que aplicada, por lo que no nos detendremos más en esto.Si la opción Análisis de datos no está en el menú Herramientas, habrá que instalarla o activarla con la opción Complementos de ese mismo menú.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 217217
Tablas de datosTablas de datosPodemos crear tablas de datos de una o dos variables que muestren el valor de una fórmula en función de la variación de uno o dos valores.
Sigamos con el ejemplo de la hipoteca. Supongamos que queremos obtener el valor del pago mensual en base al interés aplicado, variando éste desde el 3% hasta el 5% en incrementos de 0,25. Comenzamos colocando los datos iniciales y el primer valor de interés a partir del cual creamos la serie:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 218218
Los porcentajes están realmente entre 0 y 1
18/02/2008
110
Tablas de datosTablas de datosColocamos la fórmula (PAGO) en la celda de arriba y a la derecha del primer valor de la serie:
Ahora sólo queda seleccionar las celdas con las fórmulas y los valores a sustituir y utilizar la orden Tabla del menú Datos.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 219219
Celda de entrada
Lista de valores que sustituirán a la celda de entrada
Fórmula calculada con la celda de entrada
Tablas de datosTablas de datos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 220220
18/02/2008
111
Tablas de datosTablas de datosPodemos repetir el proceso añadiendo otra variable: la duración del crédito hipotecario. Para dos variables se debe colocar la fórmula en la celda superior del primer valor de la serie en columna, de forma que podamos colocar los valores de la otra variable en las filas que le siguen:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 221221
Fórmula
Celda de entrada de columna (interés)
Celda de entrada de fila (años)
Intereses en columna
Años en fila
Tablas de datosTablas de datos
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 222222
18/02/2008
112
Ejercicio: gráficos de superficieEjercicio: gráficos de superficieCrea una tabla que muestre x2 + y2 para x e y de 1 a 20:
Genera también la gráfica de la función.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 223223
EjercicioEjercicioGenera la siguiente gráfica por medio de una tabla de datos de dos variables:
Tienes que conseguir que se parezca lo más posible en todo. La función que has de usar es: 16 - √x2 + y2
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 224224
18/02/2008
113
EjercicioEjercicioGenera la siguiente gráfica por medio de una tabla de datos de dos variables:
La función que has de usar es: 1 - x2 - y2
Has de conseguir que los valores negativos se consideren ceros.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 225225
Búsqueda de objetivosBúsqueda de objetivosCuando aplicamos fórmulas en las que intervienen varias variables a menudo se nos plantea la necesidad de encontrar el valor de una de las variables que hace que otra de las variables tenga uno prefijado. Esto se puede conseguir fácilmente con la opción Buscar objetivo del menú Herramientas.
Por ejemplo, sigamos con la hipoteca y supongamos que queremos saber cuánto tiempo debemos prolongar el préstamo para pagar exactamente una determinada cantidad al mes.Partiremos de los datos y la fórmula:
La cantidad y el interés permanecerán constantes.La celda a variar es B4 y aquella en la que deseamos un resultado concreto es la celda B5.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 226226
18/02/2008
114
Búsqueda de objetivosBúsqueda de objetivosHerramientas Buscar objetivo
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 227227
Queremos que el pago mensual sea de 700 € ...... variando los años.
EjercicioEjercicioObtener los siguientes datos:
El número de años que se necesitarán para pagar un préstamo hipotecario de 100.000 € al 5,3% de interés anual y con pagos mensuales de 500 €.El número de años que se necesitarán para pagar un préstamo hipotecario de 300.000 € al 12,5% de interés anual y con pagos mensuales de 500 €.(A veces le pedimos demasiado a Excel. Puede ocurrir que no haya forma de encontrar el valor deseado. Simplemente no existe y Excel así nos lo hace saber. En este caso con 500 € al mes no cubriríamos ni los intereses, así que el capital aumentaría en lugar de disminuir, lo que obviamente no permitiría ningún banco ;-)La cantidad que se puede pedir pagando 500 € al mes durante 30 años al 5,25% de interés.El interés que se aplicaría para pagar 100.000 € a 600 €mensuales durante 20 años.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 228228
18/02/2008
115
EscenariosEscenariosPodemos establecer escenarios en los que se establezcan determinados valores para variables de la hoja de cálculo.Aplicando cada escenario se verá el resultado de usar esos valores. Y podremos así comparar diferentes situaciones.Supongamos que estamos metidos en economía doméstica:
Y supongamos que queremos contemplar varios escenarios, en los que varían los ingresos, los gastos fijos y el porcentaje de interés.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 229229
EscenariosEscenariosEl mejor escenario será aquel en el que los ingresos anuales son mayores, los gastos fijos menores y el porcentaje de interés bajo.
Elegimos la opción Escenarios del menú Herramientas y agregamos un escenario. Indicamos las celdas que varían (las seleccionamos manteniendo Ctrl presionada).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 230230
18/02/2008
116
EscenariosEscenariosA continuación se nos piden los valores para esas celdas:
Creamos otro caso (el caso peor):
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 231231
EscenariosEscenariosUna vez creados los escenarios podemos utilizar el Administrador de escenarios para aplicar los valores en la hoja de cálculo.
Basta usar el botón Mostrar una vez seleccionado el escenario:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 232232
18/02/2008
117
EscenariosEscenariosCuando disponemos de varios escenarios, podemos utilizar el Administrador de escenarios para obtener un resumen (o tabla dinámica) que informa de los casos previstos:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 233233
SolverSolverSi se quiere encontrar una solución de una fórmula con varias variables (varias celdas que pueden variar), la herramienta Buscar objetivo no nos sirve, por lo que habrá que recurrir a la herramienta Solver.Solver permite establecer muchas variables, así como restricciones que se han de aplicar sobre esas variables.Además, se puede indicar que se quiere que el resultado sea máximo o mínimo, en lugar de un resultado concreto.Por ejemplo, nos puede interesar saber cuánto hay que gastar en publicidad en cada trimestre del año (sin exceder en total los 40.000 €) con el fin de que los beneficios anuales sean los mayores posibles. Las ventas y gastos de cada trimestre serán datos conocidos. Solver obtendrá fácilmente esos valores ajustándose a las restricciones impuestas.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 234234
18/02/2008
118
SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 235235
Máx. 40.000
Valor a maximizarValores a encontrar
Unid. vendidas = 35 x Índice temporada x (Gasto publicidad + 3000)0,5
Costes fijos = 15% x Ingresos por ventas
SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 236236
18/02/2008
119
SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 237237
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolverEn un taller se fabrican tres productos distintos. Para construir cada producto se utilizan distintas piezas. Hay cinco piezas distintas y cada una se usa en varios productos.La siguiente tabla muestra cuántas unidades de cada pieza se usan para construir cada producto:
Con cada producto se obtienen unos beneficios:75 € por cada unidad de Producto 1.50 € por cada unidad de Producto 2.35 € por cada unidad de Producto 3.
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 238238
18/02/2008
120
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Sin embargo, cuantas más unidades se fabrican de un producto el margen de beneficio es menor, ya que se ha de abaratar el precio para darles salida. Se estima un factor de disminución exponencial del 0,9. El beneficio es, entonces:
75 x unidades^0,9 para el Producto 1.
50 x unidades^0,9 para el Producto 2.
35 x unidades^0,9 para el Producto 3.
Y por supuesto, el número de unidades de cada pieza está limitado por las que haya en el almacén (en stock).
Con esta situación de partida queremos aumentar el beneficio total. Como tenemos un factor exponencial que reduce los beneficios al aumentar las unidades, el problema es no lineal, aunque se puede cambiar el factor a 1 para que sea lineal.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 239239
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Restricciones: que no se usen más unidades de las que hay.En lugar de añadir cinco restricciones (C5 <= B5, C6 <= B6, ...) se pueden abreviar en una: C5:C9 <= B5:B9.Y, por supuesto, no puede haber números negativos de unidades.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 240240
Restricciones
Valor a maximizar
Valores a encontrar
18/02/2008
121
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 241241
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Ahora imaginemos que tenemos que calcular el personal de mantenimiento que necesitamos para unas grandes instalaciones.Cada empleado trabaja cinco días seguidos y descansa dos. Todos los empleados reciben un salario de 40 € diarios.Y como el público varía mucho de unos días a otros, el total de empleados que se necesita cada día de la semana varía:
Lo que tenemos que calcular es cuántos empleados de cada horario cubren las necesidades de todos los días. Y no sólo eso, sino que, además (¿cómo no?), queremos que el gasto de personal sea el menor posible (problema de minimización).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 242242
18/02/2008
122
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolverTenemos siete horarios distintos (identificados de la A a la G). En cada horario se descansa unos días distintos:
Para calcular los empleados que hay en total cada día creamos una matriz de horarios y días que contenga en cada posición un 1 si ese día se trabaja con ese horario y un 0 en caso contrario:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 243243
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
A partir de la matriz y el número de empleados de cada horario se calcula fácilmente el total de empleados de cada día:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 244244
X +X +X +X +X +X +X =
18/02/2008
123
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Ya tenemos bien preparado el escenario:
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 245245
Valores a encontrar
Valor a minimizar
Restricciones
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 246246
Sin porciones de empleado ni números negativos
18/02/2008
124
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 247247
Ejecutando más veces Solver, se
pueden obtener
otras soluciones
EjercicioEjercicioAdaptar el problema para el caso en el que se pague más los sábados (45 €) y los domingos (50 €).
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 248248
18/02/2008
125
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Para terminar, un problema de cálculo de rutas.Supongamos que tenemos tres plantas de producción situadas en Galicia, La Rioja y Murcia. En ellas se produce nuestro producto y actualmente disponen, respectivamente, de los siguientes números de unidades: 310, 260 y 280.Tenemos por otro lado cinco almacenes desde los que se distribuye el producto: Sevilla, Madrid, Barcelona, Santander y Bilbao. Cada almacén solicita un número de unidades del producto de acuerdo con su demanda. Tenemos que hacerles llegar las unidades que piden, pero podemos hacerlo desde cualquiera de las plantas. Incluso resultará habitual enviar algunas de ellas desde una planta y el resto desde otra planta distinta.Actualmente cada almacén ha solicitado, respectivamente, 180, 80, 200, 160 y 220 unidades del producto.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 249249
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
Obviamente, no cuesta lo mismo hacer llegar el producto desde una planta más cercana que desde otra más lejana. Por tanto, disponemos de una matriz de costes que indica lo que cuesta enviar una unidad desde cada planta hasta cada almacén:
Lo que tenemos que hacer es encontrar el número de unidades que se han de enviar desde cada planta hasta cada almacén, de forma que se satisfaga la demanda de cada almacén, haya unidades suficientes en cada planta y se minimice el coste total.José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 250250
18/02/2008
126
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 251251
Ejemplos de problemas que soluciona Ejemplos de problemas que soluciona SolverSolver
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 252252
18/02/2008
127
Escuela Politécnica Superior de ZamoraEscuela Politécnica Superior de ZamoraDepartamento de Informática y AutomáticaDepartamento de Informática y AutomáticaPrácticas de Informática: Hojas de cálculoPrácticas de Informática: Hojas de cálculoJosé Mª González RíosJosé Mª González RíosCurso 2007/2008Curso 2007/2008
E.P.S. de ZamoraLas hojas de cálculoLas hojas de cálculo
Compartir datos con Compartir datos con otras aplicacionesotras aplicaciones
Word y Excel son dos aplicaciones entre las que puede ser especialmente interesante compartir datos, por ejemplo para completar un informe con datos numéricos y gráficos calculados desde ExcelUna forma sencilla de copiar información un libro de Excel en un documento que se está realizando con Word consiste en copiar y pegar la información. Pasos:1. Se selecciona la información de la hoja que se desea copiar en el
documento Word.2. Se hace clic en el botón Copiar de la barra de herramientas
estándar.3. Se cambia a Microsoft Word y nos situamos en la parte del
documento en la que se quieren pegar los datos de Excel4. Se hace clic en el botón Pegar de la barra de herramientas
estándar de Word5. Se pegan los datos en forma de tabla, ahora se pueden editar y dar
formato como si se tratase de una tabla normal de Word
Copiar datos de Microsoft ExcelCopiar datos de Microsoft Excelen un documento de Worden un documento de Word
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 254254
18/02/2008
128
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 255255
Permite insertar información de Excel en un documento de Word, conservando toda la potencia y funcionalidad de Excel.Esto se consigue integrando la información como un objeto.Al hacer doble clic sobre el objeto, se pueden utilizar todas las herramientas de Microsoft Excel con la información integrada, sin abandonar el documento de Word, los menús y las barras de herramientas de Excel sustituyen a las de Word. Al hacer clic fuera del objeto, se recupera el entorno de Word.Cuando se integra la información de Excel en Word se inserta todo el libro.
Integración de un libro de Microsoft ExcelIntegración de un libro de Microsoft Excel
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 256256
18/02/2008
129
Pasos:1. Situarse en la parte del documento Word dónde se quiere
integrar el libro correspondiente2. Se elige la orden Objeto del menú Insertar cuadro de
diálogo Objeto.
3. Se hace clic en la ficha Crear desde un archivo. Se busca el archivo XLS, que queremos integrar. Para ello se hace clic en el botón Examinar
4. Se hace clic en Aceptar.
Integración de un libro de Microsoft ExcelIntegración de un libro de Microsoft Excel
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 257257
Insertar el libro INFORME.XSL
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 258258
Haciendo doble clic en el gráfico:• Obtendrás el menú de Excel• Podrás realizar todas lasmodificaciones necesarias• Datos del libro• Gráfico• Etc.
18/02/2008
130
Pasos:1. Colocar el punto de inserción en
el lugar dónde se quiera integrar la hoja de cálculo.
2. Se elige la orden Objeto del menú Insertar cuadro de diálogo Objeto
3. Se hace clic en la ficha Crear nuevo/ hoja de Microsoft Excel.
4. Como resultado se obtiene una hoja de cálculo dentro del documento Word, y cuando se encuentre activa, los menús y las barras de herramientas serán las de Excel, de forma que se podrá operar con ella como si se tratase de una hoja de cálculo
Insertar una hoja de cálculo nueva de Insertar una hoja de cálculo nueva de Microsoft Excel en WordMicrosoft Excel en Word
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 259259
La vinculación se va a utilizar para crear documentos de Word que incluyan información de Excel que se actualice de forma automática.Con un objeto vinculado, la información sólo se actualiza si se modifica el archivo Excel de origen.Los datos vinculados se almacenan en el archivo de origen.En el archivo Word de destino solamente se almacena la ubicación del archivo de origen y se muestra una representación de los datos vinculados.
Vinculación de un libro de Excel en un Vinculación de un libro de Excel en un documento de Worddocumento de Word
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 260260
18/02/2008
131
Pasos utilizando pegado especial:1. Se abre en Excel el libro XLS correspondiente.2. Se selecciona el rango que queremos introducir en el informe en word.3. Se hace clic en el botón Copiar de la barra de herramientas estándar.4. Se cambia a Word, y se sitúa el punto de inserción en el lugar del
documento de Word dónde se quiera vincular la selección.5. En el menú Edición se elige la orden Pegado especial.6. En el cuadro de diálogo Pegado especial, se selecciona Objeto de Hoja de
cálculo de Microsoft Excel y se activa la opción Pegar con vínculo.7. Se hace clic en Aceptar.
Vinculación de un libro de Excel en un Vinculación de un libro de Excel en un documento de Worddocumento de Word
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 261261
Crear una hoja de Excel con una tabla de cuadrados y raíces cuadradas hasta el número que se fije entre 1 y 100: vincular como archivo y modificar.
EjercicioEjercicio
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 262262
18/02/2008
132
TheThe endend
José Mª González RíosJosé Mª González Ríos Curso de hojas de cálculo: Excel Curso de hojas de cálculo: Excel –– 2007/20082007/2008 263263