Hoja calculo

23
GUZMÁN-RINCÓN 2000,2002 Universidad Rey Juan Carlos 1 Hoja de Cálculo Hoja de Cálculo GUZMÁN-RINCÓN 2000,2002 Universidad Rey Juan Carlos 2 Hoja de Cálculo Una hoja de cálculo es una especie de tabla cuyas casillas o celdas pueden contener: Introducción ! Texto !Valores numéricos !Fórmulas o funciones matemáticas !Gráficos

Transcript of Hoja calculo

Page 1: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 1

Hoja de Cálculo

Hoja de Cálculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 2

Hoja de Cálculo

Una hoja de cálculo es una especie de tabla cuyas casillas o celdas pueden contener:

Introducción

! Texto

!Valores numéricos

!Fórmulas o funciones matemáticas

!Gráficos

Page 2: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 3

Pantalla principal de Excel

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 4

Hoja de Cálculo

Un libro consta de una o varias hojas y se guarda en un archivo.

Es posible añadir hojas al libro, eliminarlas, duplicarlas y cambiarlas de posición.

!Añadir una hoja: Insertar / Hoja de cálculo.!Eliminar una hoja: Edición / Eliminar hoja.!Duplicar hoja o cambiarla de posición: Edición / Mover o copiar hoja.

Hojas y libros

Page 3: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 5

Hoja de Cálculo

!Celda activa: sobre la que se encuentra el cursor.!Celdas seleccionadas: aparecen en “vídeo inverso”.!Modificar el contenido de una celda: posicionarse sobre ella y pulsar F2.!Cortar, copiar y pegar celdas: uso habitual.!Edición / Rellenar: muy útil para propagar la fórmula de una celda a otras de su misma fila o columna.!Insertar o eliminar filas o columnas.

Editar celdas

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 6

Hoja de Cálculo

Las celdas se referencian por sus coordenadas de columna y de fila:A1, B5, etc.

Los rangos rectangulares de celdas se referencian mediante las posicionesde sus extremos.Ej: el rango A5:B7 referencia las celdas A5,A6,A7,B5,B6 y B7.

Se pueden referenciar varias celdas o rangos dispersos separándolos por elcarácter ;Ej: el rango A2:B3;B6:C8 referencia las celdas A2,A3,B2,B3,B6,B7,B8,C6,C7 y C8.

Referenciar celdas y conjuntos de celdas

Page 4: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 7

Hoja de Cálculo

Es posible definir el formato de los datos contenidos en las celdas. Para ello habrá que recurrir a la opción Formato de la ventana principal o bien a los iconos de la barra de herramientas de formato.

Formatear celdas

! Categoría del dato: general, número, fecha, moneda, texto, científico, etc.

!Alineación del contenido

!Tipo de fuente empleada.

!Tipos de bordes y de tramas de la celda.

!Opciones de protección de celdas.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 8

Hoja de Cálculo

Es posible proteger el contenido de las hojas de cálculo o de celdas individuales para prevenir su posible modificación por error o por terceros.

Proteger celdas y hojas

!Desproteger la hoja: elegir Herramientas / Proteger / Desproteger hoja.

!Proteger/desproteger celdas: seleccionar las celdas, elegir Formato / Celdas / Proteger y activar/desactivar la opción Bloquear (la hoja debe estar desprotegida).

! Proteger la hoja: elegir Herramientas / Proteger / Proteger hoja.

Para que la protección de celdas sea efectiva, es necesario que la hoja estéprotegida.

Page 5: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 9

Hoja de Cálculo

Para realizar operaciones con Excel, debemos emplear lo que el programadenomina fórmulas. Estas fórmulas se pueden dividir en dos tipos:

Crear Fórmulas

! Simples: introducen operadores comunes como: +,-,*,/etc.

! Complejas o funciones: utilizan nombres de fórmulas predefinidas por Excel

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 10

Hoja de Cálculo

Crear Fórmulas SimplesTipos de operadores:

Aritméticos: * —— Producto.+ —— Suma.- —— Diferencia.% —— Porcentaje.^ —— Potencia./ —— Cociente.

De Comparación:> —— Mayor.< —— Menor.<> ——Distinto.

(=<) y (=>) —— (menor o igual) y (mayor o igual)

Devuelven: VERDADERO/FALSO

Page 6: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 11

Hoja de Cálculo

Tipos de operadores:de Referencia:

: —— Englobar un rango de celdas.; —— Seleccionar celdas individualmente.

De Texto:& —— Concatenar.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 12

Hoja de Cálculo

Ejemplo.Deseamos calcular la nota final de estos alumnos. Se tiene una calificación de un examen teórico y otra de la parte de prácticas. La primera supone un 60 % de la nota final y la segunda un 40%.

Partimos de la siguiente información:

Page 7: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 13

Hoja de Cálculo

Ejemplo. Introducimos la expresión de la

fórmula que proporciona el

resultado deseado en la celda

correspondiente.

=((60%)*D6)+((40%)*E6)Copiamos la fórmula

que acabamos de escribir al resto de las

celdas donde que realizar el mismo

cálculo. Utilizamos para ello el Autollenado que

proporciona Excel.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 14

Hoja de Cálculo

Ejemplo.La expresión que calcula la nota final se copia a las celdas adyacentes con lo que nos ahorramos la traducción en celdas de la expresión, al escribirla en otra parte.

Page 8: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 15

Hoja de Cálculo

Crear Fórmulas ComplejasExcel posee una serie de fórmulas predefinidas llamadas funciones que podemos introducir directamente, indicando tan sólo el rango de celdas con el que se deberá realizar la operación. El rango de celdas se puede definir de dos formas: introduciendo una a una las celdas (operador ;) o fijando el intervalo (operador :).

Funciones Básicas

La Suma (no representada en este apartado): realiza una sumade los datos contenidos en un rango. Su formula genérica es:

=SUMA(rango).

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 16

Hoja de Cálculo

Funciones Básicas

El promedio o Media Aritmética: suma los datos de un rango y divide el resultado entre el número total de datos. Su fórmula genérica es:

=PROMEDIO(rango).El Máximo: devuelve el valor máximo de un grupo de datos. Su fórmula genérica es:

=MAX(rango).El Mínimo: devuelve el valor mínimo de un grupo de datos. Su fórmula genérica es:

=MIN(rango).

Page 9: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 17

Hoja de Cálculo

Funciones Básicas

Cálculo del valor absoluto de un dato numérico: =ABS(dato)

Cálculo de la parte entera de un dato numérico:=ENTERO(dato)

Elevar un valor numérico a una potencia:=POTENCIA(base;exponente)

Obtener el resto de una división=RESIDUO(dividendo;divisor)

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 18

Hoja de Cálculo

Funciones Básicas

La Mediana: devuelve el valor medio de un grupo de datos, es decir, el que está a la misma distancia en datos del primero y del último. Su fórmula genérica es:

=MEDIANA(rango).La fórmula Contar: Cuenta el número de celdas que contienen un dato no vacio en un rango indicado, es decir, no cuenta las celdas vacias. Su fórmula genérica es:

=CONTAR(rango).

Page 10: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 19

Hoja de Cálculo

Funciones Básicas

La función SI: Permite la estructura compleja de las operaciones introduciendo una condición cuya comprobación determinará una actuación u otra. Su sintaxis es:

= SI(X;Y;Z).

X — Valor lógico (p.e. B4 > 0)

• Si X se cumple se realiza Y.

• Si X no se cumple se realiza Z.

La función SI se puede anidar para crear asignaciones mediante estructuras condicionales complejas.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 20

Hoja de Cálculo

Funciones Básicas

Las funciones lógicas evalúan condiciones y devuelven el valor VERDADERO o el valor FALSO. Pueden emplearse para definir condiciones complejas en estructuras condicionales con la función SI.Conjunción: =Y(condición1;condición2;...;condiciónN)Disyunción: =O(condición1;condición2;...;condiciónN)Negación: =NO(condición)

Hay más funciones que devuelven valores lógicos.Ejemplo: =ESNUMERO(dato) devuelve VERDADERO si el argumento es un valor numérico y FALSO si no lo es.

Page 11: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 21

Hoja de Cálculo

Ejemplo.

Continuando con el ejemplo anterior, calculamos de nuevo las notas finales de los alumnos y ahora, además, determinamos las calificaciones que el aprobado este en el 5, como nota límite. Calculamos, también, la nota media de la clase.

Ahora partimos de la siguiente estructura de datos

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 22

Hoja de Cálculo

Introducimos la expresión de la fórmula que proporciona el valor de la nota final en la celda correspondiente.

=SUMA(PRODUCTO((60%);D6);PRODUCTO((40%);E6))

Utilizaremos el Autollenado para extender la expresión a toda la columna de Nota Final

Ejemplo.

Page 12: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 23

Hoja de Cálculo

Introducimos la expresión de la fórmula que nos va a proporcionar el criterio para determinar cuando un alumno esta aprobado. Paraello marcamos en la celda C12 el valor de la nota de corte.

=SI(G6>$C$12;"APTO/A";"NO APTO/A")

Utilizaremos el Autollenado para extender la expresión a toda la columna de Calificación.

Ejemplo.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 24

Hoja de Cálculo

Introducimos la expresión de la fórmula que nos va a proporcionar la nota promedio de la clase.

=PROMEDIO(G6:G9)

Ejemplo.

Page 13: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 25

Hoja de Cálculo

Ejemplo.La referencia que hemos utilizado para pasar como argumento de la función =PROMEDIO el rango de celdas (G6:G9) se puede realizar de una forma alternativa; asignando al rango de celdas un nombre de rango.

Seleccionamos las celdas que definen el rango sobre el que queremos realizar algún tipo de cálculo.

Elegimos el nombre con él que referirnos alrango de celdas y lo escribimos.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 26

Hoja de Cálculo

Ahora cuando escribimos la expresión que nos calcula la nota media de la clase escribiremos:

=PROMEDIO(nota_final)

Ejemplo.

Page 14: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 27

Hoja de Cálculo

Ejemplo.

Analizamos a continuación las expresiones que hemos utilizado para realizar los sencillos que se nos proponían en este ejemplo.

=SUMA(PRODUCTO((60%);D6);PRODUCTO((40%);E6))

=SI(G6>$C$12;"APTO/A";"NO APTO/A")

=PROMEDIO(nota_final)

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 28

Hoja de Cálculo

Conclusiones:

• Se pueden combinar operadores simples con estructuras complejas predefinidas por Excel.

• Se pueden anidar las funciones utilizando los resultados de unas como argumentos de otras.

• Aparición de un nuevo operador: $. Permitirá introducir un nuevo concepto: distinción entre referenciasde celdas relativas y referencias absolutas.

Page 15: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 29

Hoja de Cálculo

Fórmulas Relativas y Absolutas.

Cuando introducimos los argumentos de las funciones, lo que estamos indicándole a Excel es sobre que celdas conjunto de celdas debe realizar la operación que esta definida bajo el nombre de la función elegida. Estamos dándole la referencia del lugar donde se encuentra el dato con el que debe trabajar. Esto puede realizarse de tres formas distintas, que a su vez implican resultados distintos.Las tres alternativas para indicarle al programa donde se debe buscar los datos son:

• Nombrado de rangos de celdas.• Referencia relativa.• Referencia absoluta.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 30

Hoja de Cálculo

Nombrado de rangos de celdas.En realidad, utilizando este método, nos olvidamos de la posición dónde residen los datos. Cuando introducimos el nombre del rango de celdas en la función, es el programa el que se encarga de buscar el conjunto de valores al que hemos decidido asignar este nombre identificativo.

Seleccionamos las celdas que definen el rango sobre el que queremos realizar algún tipo de cálculo.

Elegimos el nombre con él que referirnos alrango de celdas y lo escribimos.

Introducimos el nombre como argumento de la función olvidandonos de la posición de las celdas sobre el área de trabajo.

=PROMEDIO(nota_final)

Cuadro de nombres

Page 16: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 31

Hoja de Cálculo

Referencias relativas de celdas.En este caso debemos tener claros una serie conceptos relativos a celdas:

• Debemos entender que la función debe ser definida en una celda en la cual se mostrará el valor resultado de las operaciones que la propia función involucre.

• En otras celdas residen los datos que se utilizan en las operaciones definidas en la función.

Hablaremos de referencias relativas a celdas que contienen datos, cuando lo que se almacena, junto con la expresión de la función, es la posición relativa de estas celdas con respecto a la celda que contiene la función.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 32

Hoja de Cálculo

Ejemplo.

Veamos el siguiente ejemplo para entender esto mejor. Se tiene la siguiente estructura en la cual se inserta la formula:

=SUMA(B1;C2;D3;D5;C6;B7)

Page 17: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 33

Hoja de Cálculo

Ejemplo.

Lo que está ocurriendo aquí es que junto con las operaciones relacionadas con la función (en este caso, lo que se guarda es una suma de seis valores) se guarda la posición donde Excel debe buscar esos seis valores. Lo que se almacena en realidad es la posición relativa respecto a la celda que contiene la función.

Se guarda:Operaciones - Sumar el contenido de las seis celdas que

llegan como argumento.La posición relativa 1

23

45

6

Función

La celda situada a cuatro espacios a la izquierda y tres espacioshacia arriba de la celda que contiene a la función almacena el primer argumento de la función. La celda situada a tresespacios a la izquierda y dos espacios hacia arriba de la celdaque contiene a la función almacena el segundo argumento de lafunción. ...............

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 34

Hoja de Cálculo

Ejemplo.

Si ahora copiamos la función en otra celda, el nuevo cálculo se realizará con los valores de las celdas que guarden la misma posición relativa respecto al nuevo emplazamiento de la función.

Page 18: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 35

Hoja de Cálculo

Referencias absolutas de celdas.

Las posibilidades que ofrece la referencia relativa a celdas en las funciones se ve completada por las denominadas referencias absolutas. En ocasiones resulta útil realizar hacer referencia a una celda y que sea siempre de esta celda de donde se lea la información, guardándose la posición exacta de la celda (almacenando el número de fila y la letra de la columna).

Este tipo de referencia hay que forzarla con la utilización del operador $. Escribiremos: =SUMA($B$1;$C$2) y de esta manera estamos fijando que siempre se realizara la suma con los contenidos de las celdas B1 y C2, independientemente de donde copiemos esta expresión.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 36

Hoja de Cálculo

Ejemplo.Escribimos en esta ocasión:

Mediante esta sintaxis estamos fijando la columna ($B, $C, $D,...) y las filas ($1, $2, $3,...).

Page 19: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 37

Hoja de Cálculo

Ejemplo.Si ahora copiamos la expresión en otra celda el resultado se sigue obteniendo en base a los valores que contienen las celdas:

B1,C2,D3,D5,C6,B7

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 38

Hoja de Cálculo

Cuidado con las referencias Circulares.

Cuando una fórmula hace referencia a su propia celda, directa o indirectamente se denomina referencia circular.Excel no puede resolver fórmulas con referencias circulares mediante el cálculo normal.Debido a que algunas fórmulas requieren referencias circulares, podrá ser necesario cambiar el numero de iteraciones. Para cambiar el número de iteraciones, elija Opciones en el menú Herramientas y, a continuación, haga clic en la ficha Calcular. Active la casilla de verificación Iteración e indique el número máximo de iteraciones y el grado de cambio que desea que Excel utilice.

Page 20: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 39

Hoja de Cálculo

Vínculos

Pueden compartirse los datos almacenados en hojas de cálculo y libros diferentes mediante la utilización de vínculo o referencias externas. La vinculación resulta especialmente útil cuando no conviene conservar grandes modelos de hoja de cálculo en el mismo libro. La vinculación no está limitada a vínculos entre dos libros; también pueden crearse jerarquías de libros vinculados.

Podremos distinguir entonces entre:

• Vínculo de una hoja a otra.

• Vínculo de un libro Excel a otro libro.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 40

Hoja de Cálculo

Vínculos de una hoja a otra y de un libro a otro

Sintaxis: “ =Nombre_de_la_Hoja ! Nombre_de_la_celda “

Sintaxis: “ = [Libro1.xls]Nombre_de_hoja ! Nombre_celda ”

Si en lugar de en otra hoja del mismo libro Excel, las celdas sobre las que queremos definir un vínculo, se encuentran en otrolibro la manera de actuar será exactamente igual,exceptuando la sintaxis que hay que emplear, que en este caso es:

Si deseamos realizar un vínculo entre celdas que estén dentro del mismo libro pero en distintas hojas utilizaremos la siguiente sintaxis.

Page 21: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 41

Hoja de Cálculo

Ejemplo.Realizamos un ejemplo de vínculo de hoja a hoja dentro del mismo libro ypara ello volvemos al ejemplo que ya habíamos visto cuando hablábamos de fórmulas simples y complejas. Ahora queremos preparar una hoja dondeúnicamente se muestre el nombre del alumno y si la calificación que haobtenido.

Utilizamos el nombrado de rangos de celdas para mantener localizada los nombre y apellidos de los alumnos.

Cuadro de nombres

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 42

Hoja de Cálculo

Ejemplo.

Modificamos el nombre de la hoja cambiándolo por uno más acorde con el contenido de la misma.

Cambiamos de hoja para introducir un vínculo con el rango de celdas que hemos nominado como alumnos, siguiendo la sintaxis vista. Utilizaremos el Autollenado para extender el vínculo a las celdas adyacentes.

Page 22: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 43

Hoja de Cálculo

Ejemplo.Realizamos los mismos pasos sobre la columna de Calificación paraobtener:

Nombrado de Rango

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 44

Hoja de Cálculo

Ejemplo.

Introducimos el vínculo

Rellenamos el resto de las celdas utilizando el autollenado

Page 23: Hoja calculo

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 45

Hoja de Cálculo

Si ahora modificásemos algún valor en la hoja que es el origen del vínculoel cambio se extendería de manera automática a la hoja receptora de dichovínculo.

Modificamos el valor de la nota de prácticas de uno de los alumnos.

El cambio también se hace efectivo en la segunda hoja, que contiene el vínculo que acabamos de definir.

GUZMÁN-RINCÓN2000,2002

Universidad Rey Juan Carlos 46

Hoja de Cálculo

Opciones Avanzadas.

• Validación de Datos.

• Formularios.

• Tablas Dinámicas.

• Buscar Objetivo.

• Solver.

• Informes.

• Administrador de Escenarios.