EXCEL HOJA ELECTRÓNICA LA MEJOR

Práctica 1:
Hoja de Cálculo



Ofimática 2003/2004

Dpto. Lenguajes y Sistemas Informáticos
1. INTRODUCCIÓN

Una hoja de cálculo electrónica es una versión electrónica de un libro de contabilidad que permite el almacenamiento, análisis y cálculo de datos de forma automatizada con las ventajas que ello supone.
Excel es una hoja de cálculo electrónica ideal para el manejo de datos numéricos y gran cantidad de operaciones. Entre los elementos que puede manejar Excel, cabe destacar los siguientes:
· Hoja de Cálculo: permiten introducir modelos numéricos para todo tipo de cálculos financieros, estadísticos y matemáticos. A una hoja de cálculo podremos añadir gráficos, documentos importados, imágenes, datos importados de otras bases de datos, etc.
· Gráficos: las posibilidades gráficas de Excel son absolutamente ilimitadas. Incorpora por defecto quince tipos de gráficos y cada uno de ellos incorpora, a su vez, diferentes formatos.
· Bases de datos: una base de datos es un conjunto de datos organizado en el cual pueden localizarse rápidamente entradas determinadas. Estos datos pueden ser clasificados y filtrados en base a diversos criterios de una forma muy sencilla.

2. DESCRICIÓN DE LA PANTALLA DE MICROSOFT EXCEL

Los elementos fundamentales de la ventana de Microsoft Excel son los siguientes:

· Barra de Título: contiene el nombre de la aplicación, Microsoft Excel.
· Barra de menús: Se encuentra debajo de la barra de título y contiene las órdenes de Microsoft Excel agrupadas por categorías. Así el menú Archivo contiene aquellas órdenes de manejo de archivos.
· Barra de herramientas: consta de una serie de botones que permiten llevar a cabo acciones mediante un clic sobre el botón sin necesidad de acceder al menú correspondiente. Excel incorpora trece barras de herramientas ya creadas, pero se pueden personalizar.
· Barra de fórmulas: es la parte de la pantalla donde se podrá editar el contenido de una celda de la hoja de cálculo.
· Área de trabajo: parte de la ventana de Microsoft Excel que contiene las ventanas de los documentos abiertos y visibles.
· Ventana del documento: contiene el libro de trabajo con el que se está trabajando actualmente. Los elementos de un libro de trabajo son los siguientes:
* Barra de título: contiene el nombre del libro actual.
* Documento: un libro de trabajo está compuesto por un conjunto de hojas de cálculo. Cada hoja de cálculo se nombra inicialmente como Hoja1, Hoja2, etc. Toda hoja de cálculo del libro de trabajo está compuesta por celdas. La celda es la unidad fundamental de una hoja. Viene dada por la intersección de una fila con una columna. Existen 65536 filas, numeradas desde la 1 hasta la 65536, y 384 columnas, nombradas desde la A hasta la IV. Las celdas se identifican con el nombre de la columna en la que se encuentran seguido del número de la fila.
* Barras de desplazamiento: Permiten desplazarnos por la hoja de cálculo de cada libro. Existe una barra de desplazamiento vertical y otra horizontal.
· Barra de estado: está situada en la parte inferior de la ventana. Incluye una pequeña información sobre el estado de la hoja y una breve descripción del botón seleccionado en la barra de herramientas o la orden seleccionada en el menú.

3. MECANISMOS BÁSICOS DE EXCEL

3.1. El libro de trabajo
Excel maneja un tipo especial de documento: el libro de trabajo. Un libro de trabajo contiene hojas, que pueden ser hojas de cálculo, de macro o de gráfico. El libro de trabajo predeterminado de Excel se abre con 3 hojas de cálculo nombradas como Hoja1, Hoja2 y Hoja3. Para modificar el nombre de una hoja, basta con hacer doble clic sobre la etiqueta de la hoja e introducir un nuevo nombre.

3.2. Selección de una celda
La unidad fundamental de una hoja de cálculo es una celda. En la celda se podrá introducir, editar y modificar los datos que introduzcamos en la hoja de cálculo.
Para seleccionar una celda, basta con situarse sobre ella y hacer clic sobre ella. El nombre o referencia de la celda seleccionada se sitúa en la barra de fórmulas y, si no está vacía, también aparecerá su contenido.
Es posible seleccionar una determinada celda de forma directa mediante la orden Ir a... del menú Edición.


3.3. Introducción de datos en Excel
Los datos se van a introducir en la hoja de celda en celda. Cuando comenzamos a introducir datos, vemos cómo aparece el cursor dentro de la propia celda indicando dónde se insertará el texto cuando se empiece a escribir. Para completar la entrada en una celda, habrá que pulsar la tecla .
Podemos introducir tres tipos de datos en una celda de Excel: texto, datos numéricos (números, fechas y horas) o fórmulas. El número máximo de caracteres que admite una celda son 25.

· Texto: Excel considera texto a cualquier cadena de texto o combinación de texto y números. Cuando se introducen líneas largas en una celda, el texto invadirá celdas adyacentes y se cortará cuando las celdas de la derecha contengan datos.
· Números: En Excel son números las combinaciones de dígitos decimales aunque pueden ir acompañados de los siguientes símbolos: +, -, (, ), /, Pts, E, e, ,. Cuando un número es demasiado grande para aparecer en una celda, Excel mostrará los símbolos de número (####) o bien presentará el número con formato científico; para visualizarlo, es necesario agrandar la columna.
· Fechas y horas: Las fechas se introducen en Excel con los separadores / ó -. Las horas se introducen en Excel mediante el separador :.
· Fórmulas: Las fórmulas permitirán realizar cualquier tipo de operaciones. Una fórmula puede contener constantes, operadores, referencias a celdas, nombres, funciones predeterminadas o funciones definidas por el usuario. Cuando se hacen referencias a otras celdas dentro de una fórmula, variando el contenido de aquellas, variará automáticamente el resultado de la fórmula. Una fórmula puede contener una función predefinida por Excel. Todas las fórmulas en Excel comienzan por el signo =.

3.4. Edición y modificación de datos
La edición o modificación de datos se puede llevar a cabo dentro de la propia celda o bien en la barra de fórmulas. Cuando se haya terminado de realizar la modificación, podrá pulsar para validarla o bien pulsar la tecla para mantener el contenido inicial.
Para sustituir el contenido total de una celda por otros datos, es suficiente seleccionar la celda e introducir el nuevo contenido. Si se quiere borrar el contenido de una celda y dejarla en blanco, seleccionamos la celda y pulsamos la tecla .
Cuando tengamos que operar con más de una celda, primero tendremos que seleccionarlas. Para seleccionar un rango continuo de celdas podemos utilizar el ratón; basta con situar el puntero en la primera celda del rango y arrastrarlo hasta el vértice opuesto del rango sin soltar el botón. Un rango se denota con el nombre de la primera celda seguido por dos puntos y el nombre de la celda que denota el vértice opuesto (Ej.: B4:E10).

3.5. Copiar y mover celdas
Podemos cambiar la posición de una celda o rango de celdas en la hoja de cálculo copiándolas o moviéndolas a otro rango de la misma hoja. Esto también podremos hacerlo mediante el ratón (arrastrando celdas o seleccionando los botones Cortar, Copiar, Pegar) o bien mediante las órdenes del menú Edición.

3.6. Modificar el ancho de columnas
Para modificar el ancho de una columna, se puede utilizar la orden Formato Columna y seleccionar una de las cinco subórdenes asociadas. No obstante, también se puede modificar el ancho de una columna mediante el ratón; basta con situar el puntero en la línea derecha del encabezado de la columna y, cuando la flecha cambie de forma, arrastrar el ratón conforme a nuestras necesidades de ancho de columna.

3.7. Referencias de celdas
Cuando en una celda escribimos =C3*D3, lo que en realidad estamos haciendo no es sino utilizar el concepto de referencia para colocar en determinadas celdas el resultado de las operaciones realizadas con el contenido de otras celdas. En este caso, lo que estamos haciendo es poner en una celda el resultado del producto del valor escrito en C3 por el valor escrito en D3. Además, en este ejemplo, estamos haciendo uso de referencias relativas a celdas. Una referencia relativa en una fórmula indica la posición relativa de la celda referenciada con respecto a la celda donde se encuentra la fórmula, es decir, indica cuántas filas y cuántas columnas se encuentra la celda que contiene la fórmula con respecto a la celda referenciada. Las referencias relativas suelen ser de gran utilidad para no tener que repetir fórmulas que se aplican a las mismas direcciones relativas.
Habrá casos en los cuales no interese que las referencias cambien al copiar una fórmula a otra celda. Para estos casos, es necesario utilizar referencias absolutas que permanecen invariables tras un cambio de celda. Para hacer referencia absoluta a una celda (en lugar de relativa), debe insertarse el símbolo $, antes de la fila y la columna.

Referencia Relativa F22
Referencia Absoluta $F$22
Referencia Mixta F$22 (Absoluta por fila) ó $F22 (Absoluta por columna).

3.8. Referencias remotas
Son aquellas referencias que se hacen desde una celda a otra que pertenece a una hoja de cálculo distinta. Esta hoja puede pertenecer al mismo libro de trabajo o a otro diferente. Para referenciar una celda remota basta con escribir el nombre de la hoja que contiene el valor referenciado seguida del símbolo de admiración !, seguida de la referencia de la celda de dicha hoja donde se encuentra el valor. Así por ejemplo, para calcular el máximo de los valores almacenados en la celda A15 de las hojas Febrero y Marzo, pondríamos =Enero!$A$15+ Febrero!$A$15.
4. MANEJO DE FICHEROS
Una vez introducidos los datos en la hoja de cálculo deberemos almacenar ésta en un fichero, ya que al editarla se va creando sobre la memoria del ordenador. Esto se realizará desde la orden Archivo de la barra de menú. Para guardar el documento en un nuevo archivo, utilizaremos la orden Guardar como.... Si deseamos grabar un libro de trabajo ya almacenado previamente con un nombre, utilizaremos la orden Guardar. Para cerrar un libro de trabajo seleccionaremos la orden Cerrar y para abrir uno nuevo, la orden Abrir.

5. FUNCIONES PREDEFINIDAS
Las funciones son herramientas de cálculo que Microsoft Excel pone a nuestra disposición para ser usadas en hojas de cálculo. Pueden usarse para tomar decisiones sobre los valores que se ponen en una celda, pueden devolver valores o llevar a cabo acciones y pueden realizar también gran variedad de cálculos distintos. Para que las funciones cumplan su cometido es necesario, en la mayoría de los casos, facilitarles unos datos. Estos datos son lo que se conoce como argumentos de la función. Pueden ser de diferentes tipos, como veremos posteriormente y su número depende de la función de que se trate.

Las funciones en Excel tienen la siguiente sintaxis:

=NOMBRE.FUNCION(argumento1;argumento2;...)

Van siempre precedidas del signo igual y, a continuación, sin espacios en medio, el nombre de la función seguido de un paréntesis que contendrá todos los argumentos separados por punto y coma(;). Los paréntesis son imprescindibles, incluso si la función no lleva argumentos.

En caso de que algún argumento falte, Excel le avisará con un mensaje de error. Cuando alguno de los argumentos sea un rango de celdas, éste irá limitado por la primera celda del rango seguida de dos puntos (:) y la última celda del mismo. Veamos los siguientes ejemplo:

SUMA(A1:A4;A8) nos devuelve el resultado A1+A2+ A3+A4+A8

SUMA(A1;A4;A8) nos devuelve el resultado Al +A4+A8

Como ya dijimos, los argumentos son los valores que facilitamos a las funciones para que éstas nos devuelvan un resultado o lleven a cabo algún tipo de acción. El número de argumentos que una función puede llevar depende de su tipo. Así por ejemplo, la función SUMA puede llevar hasta 30 argumentos. Los límites para los argumentos son los siguientes:

· No se pueden sobrepasar los 1024 caracteres dentro del paréntesis principal que contiene los argumentos de una función.
· Un argumento individual no puede tener más de 255 caracteres.
· Como argumentos en las funciones podemos usar los siguientes:

Números
SUMA (43;32;12)
Rangos
SUMA(A1:A34)
Texto
MAYUSCULAS(“jesus”)
Nombres
PRODUCTO(Cantidad;Peseta.Dolar)
Valores lógicos
SI(A1<23;89;A1)
Referencias
MAX(A1:A34)
Matrices
SUMAPRODUCTO(A1:10;B1:B10)
Vacío
PI()
Otras funciones
SUMA(MAX(A1:A34);B1:B34)

El alumno puede encontrar una descripción de todas y cada una de las funciones de Microsoft Excel en la ayuda: dentro de la ventana de Contenido se hace un clic en la opción Funciones para hojas de cálculo y dentro de esa opción encontrará los temas referidos a las funciones. Para obtener un listado de todas ellas, elija la opción Lista alfabética de funciones para hojas de cálculo.

6. BASES DE DATOS
En Excel, las bases de datos se manejan como listas. Una lista es una forma de estructurar los datos en la hoja de cálculo de acuerdo a unas pautas. Una lista será un rango de celdas que mantienen una estructura fija. Contendrá una primera fila rotulada con nombres de columnas (campos) y una serie de filas que contienen valores del mismo tipo para cada campo (registros). Al realizar la mayoría de las tareas de ordenación, búsqueda, etc. consideraremos a la lista como una base de datos.

6.1. Creación de una lista
Para que Excel reconozca automáticamente una base de datos se han de cumplir una serie de consideraciones: la primera fila (nombres de campos) debe ir en un formato diferente del resto de la base de datos (por ejemplo, en negrita) y todos los registros tendrán los mismos campos aunque pueden dejarse campos en blanco. También es recomendable utilizar un formato común para todas las celdas de una columna.

6.2. Ordenar una base de datos
Para ordenar una base de datos, es suficiente seleccionar una celda dentro del rango de la base de datos y ejecutar la orden Ordenar del menú Datos. Automáticamente, Excel seleccionará todos los registros excepto los nombres de los campos que los empleará como criterios de ordenación. Para ordenar únicamente un subconjunto de datos de la hoja de cálculo, seleccione el rango previamente antes de elegir la orden Ordenar. En el cuadro de diálogo Ordenar tenemos en primer lugar los criterios de ordenación que se corresponden con los campos de la base de datos. Podemos seleccionar un orden ascendente o descendente y se pueden utilizar hasta tres criterios de ordenación al mismo tiempo.

6.3. Filtración de una lista mediante un filtro automático
La filtración consiste en seleccionar una serie de registros dentro de la base de datos de acuerdo a unos criterios ocultando los registros que no cumplan dichos criterios, sin necesidad de mover los registros a otra localización.
Para filtrar una base, elegimos la orden Datos Filtros y de la suborden seleccionamos Filtro automático. Automáticamente Excel colocará flechas desplegables en cada campo de la lista. Haciendo clic en cada flecha aparecerá la lista de los elementos únicos de cada columna. Haciendo clic en un elemento de la lista, Excel presenta los registros cuyo campo contiene ese valor y oculta todos los demás registros. Para mostrar todos los elementos de un campo se ha de seleccionar la opción Todas. Podemos seleccionar además los registros que tengan vacío un determinado campo seleccionando la opción Vacías en la lista desplegable.
Podemos ampliar el rango de criterios de filtro utilizando la opción Personalizadas del cuadro de lista de cada campo. Esto nos permitirá expresar criterios complejos mediante la conjunción y disyunción de criterios simples.

7. GRÁFICOS
Los gráficos son una de las herramientas más vistosas y potentes de Excel. Gracias a ellos podremos representar los datos en un formato visual que facilita su comprensión. A continuación mostramos cuáles son los pasos necesarios para la creación de un gráfico en Excel:

1. Elegir los datos que se desean representar seleccionando un rango de la hoja.
2. Invocar al asistente para gráficos: el asistente para gráficos no es más que un conjunto de cuadros de diálogo que nos permiten la realización de un gráfico paso a paso. En cada cuadro de diálogo, Excel nos pedirá confirmación de unos datos. También nos permitirá cambiar y agregar elementos nuevos a nuestro gráfico. Una manera de llamar al asistente para gráficos es seleccionando el botón asociado al mismo en la barra de herramientas.
3. Insertar el gráfico en la hoja: la forma del puntero del ratón cambiará y deberemos trazar un rectángulo dentro de la hoja con el botón del ratón apretado. Dentro de dicho rectángulo será donde aparezca el gráfico una vez se hayan definido los parámetros.
4. A partir de este momento, el Asistente para Gráficos presentará 5 cuadros de diálogo que permitirán la completa definición del gráfico:
· Primer cuadro (Paso1): Nos muestra la zona que hemos marcado y nos pregunta si efectivamente es esta la zona que debemos representar. En la parte inferior del cuadro de diálogo, aparecen una serie de botones que se repetirán en todos los demás del asistente. Los botones Ayuda y Cancelar ya se supone para que sirven. El botón Atrás permite retroceder al paso anterior de la generación del gráfico. El botón Siguiente permite avanzar en el proceso de creación de un gráfico y el botón Terminar crea el gráfico siguiendo un modelo definido por defecto.
· Segundo cuadro (Paso2): Nos muestra los posibles tipos de gráficos que se pueden escoger (15 tipos diferentes).
· Tercer cuadro (Paso3): Pide que se seleccione el formato que se desea para el tipo de gráfico elegido. Dependiendo de cuál sea el tipo de gráfico elegido en el paso 2, aquí tendremos diferentes posibilidades.
· Cuarto cuadro (Paso4): Se muestra una visión preliminar de nuestro gráfico en la parte izquierda y nos permite definir si queremos representar como datos las filas y las columnas. Se puede probar a cambiar las series de datos de filas a columnas y comprobar lo que sucede con el gráfico.
· Quinto cuadro (Paso5): Nos permite poner o no la leyenda, título al gráfico y títulos de cada uno de los ejes. Una vez puestos estos títulos, si así se desea, se pulsa el botón de Terminar.
EJERCICIOS

1. Crear un libro de trabajo para la gestión y análisis de las calificaciones de los alumnos matriculados en la asignatura denominada "Sistemas de Información de Oficina". Tener en cuenta las siguientes normas que describen el método de evaluación y las restricciones a tener en cuenta:

· Hay 14 alumnos matriculados en la asignatura y cada uno de ellos será identificado por una letra perteneciente al rango [A,...,N].

· La asignatura es anual y cada trimestre (hay 3 trimestres en total) se evalúa de forma independiente. Cada trimestre tiene un peso en la nota global de la asignatura y este peso suele cambiar cada año. Actualmente los pesos para cada trimestre vienen dados por la tripleta (45%, 25%, 30%).

· Cada trimestre se evalúa en base a tres partes: Teoría , Problemas y Prácticas. Cada parte interviene en la nota del trimestre con una ponderación diferente que es distinta para cada trimestre y suele variar cada año. Actualmente, las ponderaciones asociadas a cada parte por trimestres son las siguientes:

Teoría
Problemas
Prácticas
Primer Trimestre
45%
25%
30%
Segundo Trimestre
35%
25%
40%
Tercer Trimestre
30%
35%
35%

· Las calificaciones globales se calculan a partir de la nota global utilizando los siguientes criterios:

Nota
Calificación
<5
Suspenso
£5 y <7
Aprobado
£7 y <9
Notable
£9
Sobresaliente

· Se desea que el libro de trabajo mantenga una hoja de cálculo por cada trimestre y una hoja para las calificaciones globales. También se desea que en las hojas asociadas a cada trimestre y en la de calificaciones globales, se calcule la media y la desviación típica de las notas de los alumnos. Además se desea visualizar una tabla en la hoja de calificaciones globales donde aparezca el número de alumnos por calificación obtenida.

2. Modificar las notas de algunos alumnos y de algunas ponderaciones para partes de la asignatura y para trimestres. Observar cómo varían los valores de aquellas celdas que incluyen fórmulas donde se referencian los valores modificados.

3. Obtener representaciones gráficas apropiadas de los siguientes datos:
· Para cada hoja de trimestre:
· Calificaciones obtenidas por los alumnos en cada parte (teoría, prácticas y problemas).
· Calificaciones obtenidas por los alumnos en ese trimestre.
· En la hoja de calificaciones globales:
· Notas globales obtenidas por los alumnos. Utilizar aquí un diagrama de barras.
· Numero de alumnos por calificación obtenida (sobresaliente, notable, aprobado, suspenso). Utilizar aquí un gráfico de tarta.
· Medias obtenidas por cada trimestre

Utilizar diferentes tipos de gráficos Microsoft Excel y modificar el formato de los gráficos obtenidos.

4. Ordenar la hoja de calificaciones globales en orden ascendente de nota global.

5. Filtrar la hoja de calificaciones globales mediante la utilización de órdenes de filtro automático de base de datos de Microsoft Excel. Por ejemplo, obtener sólo los alumnos que hayan superado la asignatura, los que hayan obtenido una nota mayor que 6 y menor que 8, o aquellos alumnos que hayan obtenido un sobresaliente como calificación global.

6.- Realizar los cambios que sean necesarios para que solo se permita la introducción de datos en las celdas correspondientes a las notas de los alumnos, evitando que se pueda modificar la estructura de las hojas de cálculo, los nombres de las distintas columnas de datos, etc. Se pretende evitar en lo posible modificaciones involuntarias de las zonas de las diferentes hojas donde no se introducen las distintas calificaciones.
Utilizar para ello la herramienta de protección de celdas.