TRABAJAR CON TABLAS

PULSA AQUI PARA VER UN VIDEO DE AYUDA

1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio.

 Primero crearemos la lista.

2 Empieza un nuevo libro de trabajo.

3 Confecciona una fila con los campos que vemos en la imagen.

4 Selecciona el rango A1:I1 (las celdas que has rellenado).

5 Selecciona el la pestaña Insertar.

6 Pulsa sobre el botón Tabla.

Se abre un cuadro de diálogo Crear Tabla con el rango seleccionado.

7 Marca la casilla La tabla tiene encabezados.

8 Pulsa Aceptar.

 Ahora vamos a rellenarla con información de tus Amig@s o información ficticia (10 amigos).

9 Ha aparecido una fila en blanco. En ella introduce los datos de tu primer amigo.

10 En la última celda, pulsa la tecla TAB para ir a una nueva fila donde introducir los datos del segundo amig@.

Vamos a introducir el resto de datos utilizando un formulario.

11 Selecciona del icono Formulario... de la barra de acceso rápido. Si no lo habías añadido, mira como hacerlo en el apartado de formularios. Aparece un formulario con los campos de la segunda fila.

12 Pulsa Nuevo. Aparecen los campos en blanco para que los rellenes con los datos de tu tercer amig@.

13 Rellena los campos con los datos de tu tercer amig@ pasando de un campo a otro con la tecla de tabulación.

14 Pulsa Intro. Estas en un nuevo registro.

15 Rellena el Nombre y el 1er apellido.

16 Pulsa Restaurar. Los valores introducidos desaparecen, vuelves a empezar con el registro en blanco.

17 Repite los pasos 13 y 14 para introducir una lista de amig@s, repite algún apellido en varios registros.

18 Pulsa Cerrar para terminar de introducir nuevos registros.

 Vamos a ordenar la lista por el primer apellido.

19 Posiciónate en la columna del primer apellido. (Columna B)

20 Pulsa sobre . (El primer botón ordena ascendentemente de la A a la Z, el segundo hace lo contrario, de la Z a la A).

Observa como ahora tus amigos están por orden alfabético de apellido.

 Vamos a filtrar la lista de contactos por el apellido que hemos repetido varias veces. Utilizaremos el Filtro.

Si los campos de la cabecera tienen una flecha al lado derecho pasa al paso 23.

21 Sitúate en la pestaña Datos.

22 Pulsa el botón Filtro.

23 Despliega la lista del primer apellido y selecciona sólo el apellido que más se repita.

Observa como ahora sólo aparecen los amigos con ese apellido.

24 Despliega otra vez la lista del primer apellido y selecciona la opción (Todas).

Observa como ahora aparecen todos los registros.

25 Por último cierra el libro de trabajo y guarda el libro en la carpeta Mis documentos del disco duro, con el nombre de Lista_amigos.

ESQUEMAS Y VISTAS

Ejercicios Tema 13. Esquemas y vistas. Si no tienes abierto Excel2007, ábrelo para realizar los ejercicios planteados a continuación. Ejercicio 1: Universidad. 1 Empezar un nuevo libro de trabajo. La Universidad necesita saber los gastos cuatrimestrales previstos para cada proyecto de los distintos departamentos y dirigidos por los directores de cada departamento. 2 Crear el modelo de datos que vemos en la imagen (los datos en negrita son fórmulas). 3 Realizar un esquema automático:  3.1 Estar situado en la hoja donde tenemos los datos. 3.2 Ir a la pestaña Datos. 3.3 Elegir la opción Agrupar. 3.4 Seleccionar la opción Autoesquema. 4 Guardar el libro en la carpeta Mis documentos del disco duro, con el nombre de Universidad. 5 Cerrar el libro. Ejercicio 2: Coches. 1 Empezar un nuevo libro de trabajo. Un concesionario quiere estudiar las ventas realizadas durante el año y las clasifica por la categoría del vehículo y por ventas trimestrales... 2 Crear el modelo de datos que vemos en la imagen. 3 Realiza un esquema manual: 3.1 Estar situado en la hoja donde tenemos los datos. 3.2 Seleccionar las filas 5-7 y presionar Alt+Shift+Flecha derecha. 3.3 Seleccionar las filas 9-11 y presionar Alt+Shift+Flecha derecha. 3.4 Seleccionar las filas 13-15 y presionar Alt+Shift+Flecha derecha. 3.5 Seleccionar las columnas B-D y presionar Alt+Shift+Flecha derecha. 3.6 Seleccionar las columnas F-H y presionar Alt+Shift+Flecha derecha. 4 Guardar el libro en la carpeta Mis documentos del disco duro, con el nombre Venta_coches. 5 Cerrar el libro.

INSERTAR GRAFICOS

1 Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2 Abre el libro de trabajo Robos de la carpeta Mis documentos del disco duro.

 Vamos a realizar un gráfico de líneas para representar la evolución del número de robos en las ciudades de Gandía y Oliva.

3 Selecciona los datos a representar, es decir C13:G14.

4 Ve a la pestaña Insertar.

5 Selecciona uno de los gráficos incluidos en el menú Línea.

6 Haz clic sobre el tipo de gráfico a realizar, en nuestro caso el de líneas.

 Observa como aparece cómo aparece nuestro gráfico.

7 Vamos a cambiar de gráfico, haz clic en el botón Cambiar tipo de gráfico de la pestaña Diseño.

8 Se abrirá un cuadro de diálogo, selecciona la categoría Columna y escoge el diseño de barras sencillo.

9 Haz clic sobre el botón Aceptar para ejecutar los cambios.

Vamos a cambiar los datos que queremos que se muestren, añadiremos también los robos en Elche.

10 Haz clic en el botón Seleccionar datos de la pestaña Diseño.

11 Se abrirá un cuadro de diálogo, no escribiremos nada, simplemente seleccionaremos el rango C12:G14 en la hoja de cálculo.

Si el gráfico tapa los datos, deberás moverlo primero.

12 Pulsamos el botón Aceptar para ver los cambios.

 Vamos a asignar nombre a cada serie de datos:

13 Vuelve a hacer clic en el botón Seleccionar datos.

14 En el cuadro de diálogo selecciona la Serie1 y pulsa el botón Editar.

15 Coloca el cursor en el cuadro de texto Nombre de la serie y haz clic en la celda B12 de la hoja de cálculo para decirle a Excel que el nombre de esta serie se encuentra en esa celda.

16 Pulsa el botón Aceptar.

17 Repite los pasos 14 a 16 para las dos otras series con las celdas B13 y B14.

18 Pulsa el botón Aceptar y observa los cambios.

 Hemos puesto los nombres de los pueblos en la leyenda para saber a cual corresponde cada barra.

Vamos a hacer lo mismo para los literales del eje horizontal.

19 Volvemos a hacer clic en el botón Seleccionar datos.

20 Haz clic en el botón Editar de la columna Etiquetas del eje horizontal (categoría).

21 En este caso, como no se trata de series no tendremos que indicarle una a una cual es su nomenclatura, bastará con darle el rango de los literales para los datos que hemos seleccionado.

Selecciona el rango de celdas C6:G6 y pulsa Aceptar.

22 Pulsa el botón Aceptar y observa los cambios.

Ahora, desde las pestañas Diseño, Presentación y Formato podrás mejorar su visualización cambiando los estilos del gráfico.

Deberá quedarte algo así:

MANIPULANDO CELDAS

Ejercicio 4: Robos

1 Empezar un nuevo libro de trabajo, para crear el modelo siguiente, de momento no nos fijaremos en el aspecto de las celdas sino en su contenido:

2 Guardar el libro de trabajo con el nombre de Robos en la carpeta Mis documentos del disco duro y cerrarlo.

USO DE OTRAS FUNCIONES

1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio.

2 Escribe en la Columna B unos 5 número enteros (mejor para el ejercicio si son de un solo dígito). Cada uno en una celda, pero ten en cuenta de ponerlos siempre en la columna B.

Vamos a multiplicar todos los valores de la columna B,

3 Sitúate en la celda C6 y pulsa sobre , selecciona la categoria de Matemáticas y trigonometría y elige la funciónPRODUCTO(). Pulsa Aceptar.

4 Selecciona la columna B como parámetro. Pulsa Aceptar.

Ya tenemos el resultado del producto en la celda C6.

 Ahora vamos a pasar el resultado a números romanos. (Por esto mejor números de un dígito).

5 Sitúate en la celda D6 y pulsa sobre , selecciona la categoria de Matemáticas y trigonometría, selecciona la funciónNUMERO.ROMANO.

6 Selecciona como parámetro la celda donde tenemos el resultado del producto, la celda C6 y pulsa Aceptar. Debe de aparecer el resultado en números romanos.

 Trabajemos un poco de estadística.

7 Añade 5 números más en la columna B y modifica los que existen por números más grandes, de más de un dígito.

8 Sitúate en la celda B12 y pulsa sobre , selecciona la función PROMEDIO de la categoría Estadísticas.

9 Selecciona como parámetro las 10 celdas de la columna B. Y pulsa sobre Aceptar.

 Ahora vamos a ver la MEDIANA.

10 Sitúate en la celda B13 y pulsa sobre , selecciona la función MEDIANA de la categoría Estadisticas.

11 Selecciona como parámetro las 10 celdas de la columna B. Y pulsa sobre Aceptar.

 Y vamos a ver la MODA.

12 Sitúate en la celda B11 y pulsa sobre , selecciona la función MODA de la categoría Estadísticas.

13 Selecciona como parámetro las 10 celdas de la columna B. Y pulsa sobre Aceptar.

14 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Otras Funciones de Excel.

15 Cierra el libro de trabajo.

FUNCIONES FINANCIERAS

Funciones financieras.

Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para crearte tu propia "caja de ahorros en casa".

Todas estas funciones están agrupadas en la categoría de Financieras.

Vamos a estudiar la amplia gama de funciones financieras que nos ofrece Excel:

Función	Descripción y Ejemplo	Ver Detalle
DB(costo;valor_residual;vida;periodo;mes)	Devuelve la depreciación de un bien para un período especificado, método de depreciación de saldo fijo
DDB(costo;valor_residual;vida;periodo;factor)	Devuelve la depreciación de un bien para un período especificado, mediante el método de depreciación por doble disminución de saldo
DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios)	Devuelve la depreciación de un bien para un período especificado, incluyendo periodos parciales
INT.PAGO.DIR(tasa;periodo;nper;va)	Calcula el interés pagado durante un período específico de una inversión
NPER(tasa;pago;va;vf;tipo)	Devuelve el número de pagos de una inversión
PAGO(tasa;nper;va;vf;tipo)	Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes
PAGOINT(tasa;periodo;nper;va;vf;tipo)	Devuelve el interés pagado por una inversión durante periodo determinado
PAGOPRIN(tasa;periodo;nper;va;vf;tipo)	Devuelve el pago de un capital de una inversión determinada
SLN(costo;valor_residual;vida)	Devuelve la depreciación por método directo de un bien durante un período dado
SYD(costo;valor_residual;vida;periodo)	Devuelve la depreciación por método de anualidades de un bien durante un período específico
TASA(nper;pago;va;vf;tipo;estimar)	Devuelve la tasa de interés por periodo de un préstamo o una inversión
TIR(valores;estimar)	Devuelve la tasa interna de retorno de una inversión para una serie de valores en efectivo
TIRM(valores;tasa_financiamiento;tasa_reinversión)	Devuelve la tasa interna de retorno modificada
VA(tasa;nper;pago;vf;tipo)	Devuelve el valor actual de una inversión
VF(tasa;nper;pago;vf;tipo)	Devuelve el valor futuro de una inversión basada en pagos periódicos y constantes más una tasa de interés constante
VNA(tasa;valor1;valor2;...)	Devuelve el valor neto actual de una inversión a partir de una tasa de descuentos y una serie de pagos futuros

  Vamos a desarrollar un ejemplo práctico para calcular el pago de un préstamo basándonos en pagos constantes y una tasa de interés constante.

1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio.

Para realizar este ejercicio haremos uso de la función PAGO.

2 Haz clic sobre el botón  de la barra de fórmulas.

Aparecerá el cuadro de diálogo Insertar función.

3 Selecciona del recuadro Selecciona una categoría la opción Financieras.

4 Elige del recuadro Selecciona una función, PAGO.

La sintaxis de la función PAGO es PAGO(tasa;nper;va;vf;tipo), vamos a describir cada uno de sus parámetros:

tasa = es el tipo de interés del préstamo.

nper = es el número total de pagos del préstamo.

va = es el valor actual del total de pagos

vf = es el valor futuro o un saldo en efectivo que se desea lograr tras el último pago. Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda ningún saldo pendiente lo que ocurre cuando se trata de un préstamo.

tipo = indica el vencimiento de pagos.

(tipo = 0) --> al final del período

(tipo = 1) --> al inicio del período

Ahora que ya conocemos los parámetros que necesita la función, podemos crear el ejemplo:

 Vamos a pedir un préstamo de 100,000 , en un período de 30 años, es decir 30*12=360 nº de pagos mensuales, a un interés del 6%.

Así pues, ya tenemos un ejemplo de un préstamo.

5 Sitúate en la celda A1 y escribe Préstamo

6 Sitúate en la celda A2 y escribe Tasa

7 Sitúate en la celda A3 y escribe Nº Pagos

8 Sitúate en la celda A4 y escribe Tipo

9 Sitúate en la celda A5 y escribe Cuota Mensual

10 Sitúate en la celda B1 y escribe 100.000 

11 Sitúate en la celda B2 y escribe 6 %

12 Sitúate en la celda B3 y escribe 360

13 Sitúate en la celda B4 y escribe 0

14 Sitúate en la celda B5 y escribe =PAGO(B2/12;B3;B1;0;B4)

Con esta función indicamos que el vencimiento del pago se realiza al final del período y que no existen cuotas al finalizar los pagos.

Como resultado debemos obtener -599,55  que será la cuota mensual. El número sale negativo porque el efectivo que se paga, por ejemplo depósitos en cuentas de ahorros, cuotas de un préstamos, se representa con números negativos; el efectivo que se recibe, se representa con números positivos.

Con la función PAGO también podemos calcular qué cuota mensual debemos ingresar para ahorrar una cantidad de dinero en X años.

 Vamos a calcular cómo podemos ahorrar 30.000  en 5 años, con un interés del 6%.

15 Sitúate en la celda C1 y escribe Ahorro

16 Sitúate en la celda C2 y escribe Tasa Anual

17 Sitúate en la celda C3 y escribe Años

18 Sitúate en la celda C4 y escribe Ingresos Mensuales

19 Sitúate en la celda D1 y escribe 30.000 

20 Sitúate en la celda D2 y escribe 6%

21 Sitúate en la celda D3 y escribe 5

22 Sitúate en la celda D4 y escribe PAGO(D2/12;D3*12;0;D1)

Como resultado debemos obtener en la celda D4 la cantidad de -429,98 

 Vamos a calcular ahora los intereses pagados en un período de tiempo por un préstamo, por ejemplo los intereses del primer ejemplo.

Para realizar este ejercicio utilizaremos la función PAGOINT

Esta función tiene la siguiente sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)

tasa = es el tipo de interés del préstamo.

período = es el período para el que se desea calcular el interés y debe estar entre 1 y el parámetro nper

nper = es el número total de pagos del préstamo.

va = es el valor actual del total de pagos

vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.

tipo = indica el vencimiento de pagos.

(tipo = 0) --> al final del período

(tipo = 1) --> al inicio del período

23 Sitúate en la celda E1 y escribe Préstamo

24 Sitúate en la celda E2 y escribe Tasa Anual

25 Sitúate en la celda E3 y escribe Interés en la Cuota Nº

26 Sitúate en la celda E4 y escribe Cantidad de Cuotas

27 Sitúate en la celda E5 y escribe Interés

28 Sitúate en la celda F1 y escribe 100.000 

29 Sitúate en la celda F2 y escribe 6%

30 Sitúate en la celda F3 y escribe 1

31 Sitúate en la celda F4 y escribe 360

32 Sitúate en la celda F5 y escribe PAGO(F2/12;F3;F4)

Esta función nos debe devolver -361,80  que es el interés pagado en la primera cuota del préstamo. Cambiando el valor en F3 podrás ver el interés pagado en cada caso.

 Vamos a calcular ahora las cuotas amortizadas para un préstamo, seguimos basándonos en el primer ejercicio.

Para realizar este ejercicio utilizaremos la función PAGOPRIN

Esta función tiene la siguiente sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)

tasa = es el tipo de interés del préstamo.

período = es el período para el que se desea calcular la amortización y debe estar entre 1 y el parámetro nper

nper = es el número total de pagos del préstamo.

va = es el valor actual del total de pagos

vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.

tipo = indica el vencimiento de pagos.

(tipo = 0) --> al final del período

(tipo = 1) --> al inicio del período

33 Sitúate en la celda A8 y escribe Préstamo

34 Sitúate en la celda A9 y escribe Tasa Anual

35 Sitúate en la celda A10 y escribe Cálculo amortización en cuota nº

36 Sitúate en la celda A11 y escribe Cuotas Totales

37 Sitúate en la celda A12 y escribe Amortizado

38 Sitúate en la celda B8 y escribe 100.000 

39 Sitúate en la celda B9 y escribe 6 %

40 Sitúate en la celda B10 y escribe 1

41 Sitúate en la celda B11 y escribe 360

42 Sitúate en la celda B12 y escribe PAGOPRIN(B9/12;B10;B11;B8)

 Como resultado debemos obtener en la celda B12 la cantidad de -99,55 .que si nos fijamos es el resultado de la diferencia de quitar los intereses a la cuota total del préstamo. Como vemos en la primera cuota de la amortización los intereses son más del 80% y la amortización propia del préstamo no llega al 20%.

43 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones Financieras.

44 Cierra el libro de trabajo.