Excel x Pinter Medio

Servicio de Informática

C u r s o d e M i c r o so f t E x c e l XP Nivel Nivel Intermedi o Duración: 25 horas

Universidad de Córdoba Servicio de

I n f orm ática Noviembre 2004

CONTENID O Servicio de Informática

Índice de Ejercicios

1.

La pantalla de Microsoft Excel XP.............................. .............. ............................... ............................... ............................ ............ 1

2.

La ayuda de Excel ................................................................................................. 2

3.

Libros de trabajo / Hojas de cálculo ............................... ............... ............................... ............................... ....................... ....... 3

4.

Formato de celdas: alineación del texto texto .............................. .............. ............................... ............................... .................. .. 7

5.

Formato de celdas: formato de los datos................................ ................ ............................... .............................. ............... 9

6.

Referencia a los datos ....................................................................................... 15

7.

Utilización de fórmulas fórmulas ..................................................................................... 21

8.

Fórmulas y funciones ........................................................................................ 24

9.

Gráficos ............................................................................................................

39

10.

UCO Tienda ....................................................................................................... 51

11.

Titulaciones ...................................................................................................... 61

12.

Notas de Alumnos ............................................................................................. 68

13.

Tablas de Multiplicar ......................................................................................... 85

14.

Matrículas 2003 ................................................................................................ 89

15.

Facturación ....................................................................................................... 99

16.

Control de Cuenta ............................................................................................ 104

17.

Cálculo del N.I.F. ............................................................................................. 107

18. Ventas Comercial Comercial

............................................................................................ 110

19.

Cambio de Divisas ........................................................................................... 118

20.

Fútbol .............................................................................................................. 124

21.

P.N.B. Países ................................................................................................... 128

22.

Selectividad .................................................................................................... 136

23.

Elecciones Sindicales ...................................................................................... 142

24.

Elecciones a Claustro ...................................................................................... 146

E je rc ic io n 1 º

Lapantalla LapantalladeMicrosoft deMicrosoftExcelXP


•

•

•

Nombre:

-----

Objetivo:

Recordar el entorno de trabajo de Microsoft Excel XP.

Enunciado: Entrar en Microsoft Excel y, posteriormente, realizar las siguientes acciones: -

MaximizarMicrosoft MaximizarMicrosoftExcel. Excel.

-

Maximizarel Maximizarellibrode librodetrabajoque trabajoqueaparece aparecepordefecto. pordefecto.

-

Ocultar la barra de estado.

-

Ocultar la barra de fórmulas.

-

Ocultar todas las barras de herramientas que haya en pantalla.

-

Mostrarlassiguientes barrasde herramientas herramientas::“Estánd “Estándar”, ar”,“Formato” “Formato”y y “Dibujo”.

-

Situar la barra de menú por debajo de las barras de herramientas.

-

Mostrar la barra de fórmulas.

-

Mostrarla Mostrarlabarrade barradeestado.

-

Ocultar la barra de herramientas de “Dibujo”.

-

1”en Escribir“Ejercicio Escribir“Ejercicio1” enla lacelda celdaA1 A1del delárea áreade detrabajo. trabajo.

-

Minimizar el libro de trabajo actual.

-

Salirde SalirdeMicrosoft MicrosoftExcel, Excel,singrabar singrabarlos loscambios cambiosefectuados. efectuados.

pág. 1

Ejercicio nº 2 LaayudadeExcel


•

•

•

Nombre:

-----

Objetivo:

Familiarizarse con los diferentes tipos de ayuda de los que dispone MicrosoftExcel.

Enunciado: Utilizando el Ayudante de Office, obtener ayuda sobre los siguientes aspectos: -

Cómo abrir un libro de trabajo.

-

Cómo ocultar los elementos de la ventana para mostrar más información en la pantalla de Excel.

-

Cómo cambiar el nombre del autor de un libro de trabajo.

-

Cómo cerrar un libro de trabajo.

Utilizando la ayuda de Contenido e índice, obtener información sobre: -

La función PRODUCTO.

-

La función RAIZ.

-

Qué función permite obtener la media aritmética.

-

Qué función permite contar el número de celdas que tienen algún valor.

-

Cómo poner una contraseña al fichero, de forma que cualquier persona puede verlo,perosólolopuedanmodificaraquellosqueconozcanla contraseña.

pág. 2

Ejercicio nº 3 Librosdetrabajo/Hojasdecálculo


•

•

•

Nombre:

-----

Objetivo:

Tratamiento de libros de trabajo y gestión de las hojas de cálculo asociadas a cada libro de trabajo.

Enunciado: El ejercicio consiste en crear la siguiente estructura de carpetas y archivos, a partir de la carpeta “Personal” que deberá tener cada alumno en su escritorio de Windows: Æ

Personal Ejemplo1.xls(Autor:UCO /Asunto:Curso/Con5 hojas) Ejemplo2.xls(Autor:UCO /Asunto:Curso/Con5hojas) Ejemplo3.xls(Autor:UCO /Asunto:Curso/Con5hojas) Æ

Balances Económicos CuentasAnuales.xls(Autor:UCO/Asunto: GestiónEconómica/Con10hojas)

Ventas.xls (Autor:UCO /Asunto:Gestión Económica /Con 10 hojas) Æ

Contabilidad PrimerTrimestre.xls(Autor:UCO / Asunto:GestiónEconómica / Con 3hojas/ Contraseñade

apertura: p1102) Æ

Personal Natalidad.xls (Autor:Área dePersonal/Asunto: Balance/Con 8hojas) Sectores.xls(Autor:ÁreadePersonal /Asunto:Balance /Con 8hojas)

Æ

Presupuestos 1998.xls(Autor:Retribuciones/Asunto: ControlPresupuestario/Con9hojas) 1999.xls(Autor:Retribuciones/Asunto: ControlPresupuestario/Con9hojas)

2000.xls(Autor:UCO /Asunto:ControlPresupuestario /Con 9hojas) 2001.xls(Autor:Retribuciones/Asunto:ControlPresupuestario/Con9hojas)

Los archivos estarán en blanco, no tendrán ningún tipo de pág. 3

información.

pág. 4


TratamientodeLibrosdeTrabajo:

Una vez construida la estructura anterior, se deberán realizar las siguientes operaciones: -

Realizarunabúsquedade todoslosarchivoscuyoautorseael“Áreade Personal”.

-

Realizar una búsqueda de todos los archivos cuyo asunto sea “Control Presupuestario”.

-

Realizar una búsqueda de todos los archivos cuya fecha de creación sea posterioral 1 de marzo de 2003.

-

Borrar los archivos“Ejemplo2.xls”,“Ejemplo3.xls”y“2000.xls”.

-

Hacer una copia del archivo“1999.xls”,llamándole“2000.xls”.

-

Renombrar el archivo“Ejemplo1.xls”a“Cursos.xls”.

-

Borrar la carpeta “Contabilidad”, habiendo copiado previamente sus archivos a la carpeta“BalanceEconómico”.

Tratamiento de Hojas de Cálculo: -

Abrir el archivo “Cursos.xls”, borrar las dos últimas hojas de cálculo y a las tres primerasasignarlelosnombres“primera”,“segunda”y“tercera”.

-

En las tres hojas de cálculo escribiren el rango A1:A12, los 12 meses del año.

-

Agregar al archivo “2001.xls” tres hojas más y, poner como nombre de las hojas las iniciales delos docemeses delaño.

-

Borrar cuatro de las hojas del archivo “Sectores.xls” y, etiquetarlas con:

“Funcionarios”,

“Laborales”,

“Proyectos”,

“Becarios”.

Posteriormente ordenarlas en el siguiente orden: “Becarios”, “Proyectos”, pág. 5

“Laborales” y“Funcionarios”. -

Abrir el archivo “Cuentas Anuales.xls”, etiquetar sus hojas desde “0001”, hasta la “0010” y, posteriormente, copiar todas estas hojas a un nuevo archivo llamado “Auditoria.xls”.

pág. 6


•

Solución: 1.

Estructura de carpetas Para crear la estructura pedida vamos a realizar los siguientes pasos: Crear la carpeta personal en el escritorio (o lugar que nos indiquen para

ello) haciendo clic con el botón derecho del ratón pulsando en nuevo seguido de carpeta, tal y como se indica en la figura. Una vez creada la carpeta, vamos a tener dos posibilidades de crear las hojas de Excel, una sería desde el explorador de Windows, la cual no nos va a permitir darle las características que nos pide el ejercicio. La otra, y más correcta sería desde dentro de Excel, para esto lo que hacemos es abrir el programa y entrar en Herramientas/Opciones/General, modificando el nº de hojas que queremos que aparezcan en el próximo libro que abramos (esto lo hemos de hacer cada vez que tengamos que cambiar el número de hojas del libro). Una vez hecho esto cerramos Excel sin grabar nada y seguidamente

lo

volvemos

a

abrir,

y

entrando

en

Archivo/Propiedades/Resumen escribimos los datos que deseemos que aparezcan en Autor, Asunto,

…,

seguidamente lo grabamos con el nombre

de Ejemplo1.xls. Siguiendo estos pasos podemos crear la estructura de archivos que deseamos.

2.

TratamientodeLibrosdeTrabajo: Vamos a continuación a describir como podemos realizar las búsquedas que se nos proponen en los ejercicios. Para realizar las búsquedas, lo primero que hemos de

hacer es

abrir Excel,

seguidamente pulsamos

Archivo/Abrir/Avanzada… y ahí vamos a definir los criterios de la pág. 7

búsqueda, una vez creada la búsqueda como se ve en la imagen hemos de agregarlaalalistaantesdepulsarbuscarahora.

pág. 8


Hemosrecreadolabúsquedaporautor,perotambiénpodemoshacerlapor asunto, fecha de creación, etc.…

Para borrar archivos, renombrarlos, copiarlos, etc., la forma más fácil será desde el explorador de Windows y usando el botón derecho del ratón. 3.

Tratamiento de Hojas de Cálculo: En las siguientes imágenes se explica la forma de insertar nuevas hojas y darles los nombres correspondientes.

pág. 9

pág. 10

Ejercicio nº 4 Formato de celdas: alineación del texto


•

•

•

Nombre: 4

Ejercicio

Objetivo:

Explicar las distintas alineaciones que puede tener un texto dentro de una celda.

Enunciado: El ejercicio consiste en escribir el texto que aparece a continuación y, aplicarle la alineación necesaria a las distintas celdas para obtener el mismo resultado.

Grabar el libro de trabajo como“Alineación.xls”, que deberá de constar de una única hoja decálculoquecontendráeltrabajorealizadollamada“datos”.

pág. 11


•

Solución: Este ejercicio, aunque tedioso, es bastante fácil, ya que consiste en repetir la misma operación varias veces, cambiando, eso sí, algunos parámetros. El modo de resolución es el siguiente. Haciendo clic con el botón derecho del ratón en la celda a la que queramos aplicarle la alineación, y eligiendo formato de celdas podemos conseguir todos los resultados deseados. A modo de ejemplo tenemos la siguiente imagen, en la que se recoge el cuadro de dialogo que hemos de utilizar en este ejercicio

Simplemente nos queda ver como se haría la fila 9, esto lo hemos conseguido pág. 12

pulsando el botón de combinar y centrar

pág. 13

Ejercicio nº 5 Formatodeceldas:formatodelosdatos


•

•

•

Nombre: 5

Ejercicio

Objetivo:

Practicar con los distintos formatos de números que Excel proporciona para dar un aspecto más legible a nuestro trabajo.

Enunciado: El ejercicio consiste en escribir la columna “Valor” del ejemplo proporcionado (datos sombreados) y, posteriormente, realizar las siguientes operaciones: -

Copiar los datos de la columna “Valor”, a las distintas columnas de “Formato” existentes.

-

Aplicarle a las columnas de “Formato” el formato adecuado para obtener el mismo resultadoqueelproporcionadoalalumno.

-

La sección de Alumnos está basada en un formato condicional, de forma que si el alumno tiene una nota superior o igual a 5, la fila aparecerá de color verde, en caso contrario, aparecerá de color rojo.

Elplanteamientoeselsiguiente:

pág. 14

pág. 15


Grabarellibrodetrabajocomo“Formatos.x como“Formatos.xlls”, s”,quedeberádeconstardeunaúnica hojade cálculoquecontendrá eltrabajorealizadollamada“datos”. llamada“datos”.

•

Solución: Para realizar este ejercicio vamos a ir describiendo los formatos de cada módulo, detallando detallan do en cada caso los aspectos que estimemos más relevantes. Lo primero que debemos saber es que para darle un formato determinado determinado a pág. 16

una celda hemos de

pág. 17


estar sobre ella y seguidamente hace clic con el botón derecho de ratón y elegir formato de celdas o bien elegir esto mismo en el menú Formato de la barra de herramientas. En el bloque de salarios salarios solo nos vamos a detener para comentar como se harían el formato-3 y el 4, los formatos que habría quedarle son los siguientes:

Como vemos el formato será personalizado, personalizado, y el tipo es #.##0. “miles de pesetas”, aquí las almohadillas le indican a Excel que escriba el e l número solo con sus cifras significativas en cambio el cero obliga a poner el número sin importarnos si la cifra es significativa o no. Los puntos son los separadores de miles, y como podemos observar después después del cero cero hay un punto punto que hace que Excel Excel omita las tres últimas cifras. Cuando tengamos que poner algún texto simplemente lo colocamos entre comillas. El formato-4 será #.##0.. “millonesde “millones depesetas” pesetas”aquí aquí cabe destacar los dos puntos consecutivos consecutivos finales pág. 18

que hacen que se omitan las seis últimas cifras del número.

pág. 19


Para el bloque de porcentajes sólo vamos a comentar el Formato-4, el cual sería 00000,0% que como dijimos en el apartado anterior hace que se pongan todas las cifras, incluso las no significativas. En los tres formatos anteriores solo hemos de jugar con las posiciones decimales que queremos poner. Vamos ahora con el bloque de caja, que es quizás el más complejo. En Excel los formatos de celdas complejos los vamos a dividir en cuatro partes, todas ellas separadas por punto y coma. El primer bloque nos indica como serán los números positivos, el segundo los negativos, el tercero el cero y por último el formato de texto. Teniendo en cuenta esto y usando como referencia el Capitulo 3 del manual obtenemoslossiguientesresultados: Formato-1: [Azul]#.#00 “ptas.”;[Rojo]-#.#00 “ptas.”;[Verde] “----“;@ Formato-2: [Azul]#.#00“ €.”;[Rojo]-#.#00“ €”;[Verde]“---“;@ Formato-3: [Azul]”Entrada:” #.#00,00;[Ro jo]”Salida:”#.#00,00;[Verde] “cero”;@

Para Totales es muy fácil, ya que solo hemos de jugar con la opción que nos da Excel de repetir un carácter de forma indefinida con la utilización del * de esta forma el formato-1, formato-2 y formato-3 quedarían de la siguiente forma:

pág. 20

pág. 21


Para los bloques de Teléfonos y Códigos postales, no vamos a dar ninguna explicación, ya que todo lo necesario se ha dicho antes, simplemente daremos el resultado final del formato.

Teléfono s

Formato-1:Especial,Númerodeteléfono Formato-2 Personalizada,(000) 00 00 00 Formato-3 Personalizada,“Prefijo:”000–“Número:”00 00 00

Códigos postales Formato-1: Número, con separación de miles Formato-2: Personalizada, “provincia:” “municipio”000 Formato-3: Personalizada, 0 0 0 00 Formato-3: Personalizada, 0 0 . 0 0 0

00

En el bloque de fechas vamos hemos de tener en cuenta que tanto para los días como para los meses el formato variará de la siguiente forma: d nos devuelve el día solo con cifras significativas. dd nos devuelve el día con cifras no significativas. ddd nos devuelve el día de la semana de forma abreviada. dddd nos devuelve el día de la semana sin abreviar.

pág. 22

Igualmente ocurre para los meses solo que en lugar de una d hemos de poner una m.

De esta forma los formatos quedarán de la siguiente manera:

pág. 23


Paraelbloque deHoraslosformatosaaplicarserán lossiguientes: Formato-1: Personalizada, h:mm AM/PM. Formato-2:Personalizada,h:mm:ss. Formato-3:Personalizada,“hora–“h“minutos–“mm“segundos–“ss. Formato-4: Personalizada, h . mm . ss.

Por último tenemos el bloque Alumnos, lo que hay que aplicarle será un formato condicional, que quedará de la siguiente forma:

Lo que hacemos es poner dos condiciones, la primera nos rellena de verde las celdas si el valor de la nota es igual o superior a cinco y la segunda de rojo si el pág. 24

valor es inferioracinco.

pág. 25

Ejercicio nº 6 Referenciasalos datos


•

•

•

Nombre: 6

Ejercicio

Objetivo: Practicar los distintos tipos de referencias a celdas y rangos existentes (relativas, absolutas y mixtas), las cuales permiten indicar a Excel en qué celdas buscar los datos a usar en una fórmula. Enunciado: Crear un nuevo libro de trabajo, que se llamará “Referencias.xls”, y realizar en distintas hojas de cálculo los siguientes planteamientos: 1.

Referencias relativas (hoja: Relativas) Este tipo de referencias CAMBIAN al copiar la fórmula de una celda a otra. Realizar la siguiente tabla utilizando referencias relativas para calcular los totales.

NOTA: El alumno debe escribir la línea de “Producto”, “Precio” y “Unidades”. Para calcular la línea de “TOTAL” debe escribir la fórmula apropiada en una de las columnasycopiarlaalresto.

2.

Referenciasabsolutas(hoja:Absolutas)

Este tipo de referencias NO CAMBIAN al copiar la fórmula de una celda a otra. Realizar la siguiente tabla utilizando referencias absolutas (o mixtas) para pág. 26

calcular los totales.

pág. 27


NOTA: El alumno debe escribir la columna de “Precio” y “Unidades”. Para calcular la columna de “TOTAL” debe escribir la fórmula apropiada en la primea línea y copiarlaalresto.

3.

UtilizacióndeNombres(hoja:Nombres) El darun nombre a una celda o conjunto deceldas disminuyeel erroral trabajar con referenciasrelativas/absolutas.

NOTA: El alumno debe escribir la columna de “Precio” y “Unidades”. Posteriormente, debe asignar un nombre a la celda que contiene el valor del precio y, calcular la línea de “TOTAL” haciendo referencia a este nombre.

4.

Referencias entre distintas Hojas de Cálculo Crear una nueva hoja de cálculo llamada “Productos” que contenga la siguiente información:

A continuación, crear una nueva hoja llamada “Unidades” como la siguiente:

pág. 28

pág. 29


NOTA: Para obtener el precio en cada uno de los meses, será necesario multiplicar el nº de unidades del producto por el precio de cada producto en el mes correspondiente que se encuentra en la hoja de “Productos”.

5.

Referencias entre distintos Libros de Trabajo Crear un nuevo libro de trabajo que se llame “Referencias2.xls”, que contenga una hoja de cálculo con el siguiente aspecto:

NOTA: El precio de los productos se debe obtener automáticamente mediante

referencias a la hoja “Productos” del libro de trabajo

“Referencias”. Los totales de los productos para cada uno de los meses, así

como el total de ventas, deberán calcularse mediante la fórmula correspondiente.

•

Solución: 1. Este ejercicio es bastante simple si tenemos claro el concepto de referencias en Excel, en el cual no vamos a entrar con detalles ya que se explico en el anterior curso, simplemente vamos a recordar que para realizar una referencia relativa (cambian al copiar la fórmula) solo hemos de colocar el nombre de la celda, en cambio cuando la referencia es absoluta o mixta hemos de colocar el símbolo $ delante del indicador de la fila, de la columna o de pág. 30

ambas,paraqueestetipodereferenciasurtaefecto.

pág. 31


Ahora con copiar y pegar esta celda en C8 y C8 conseguimos el resultado deseado usando referencias relativas.

Copiamos y pegamos hacia abajo y tendríamos el ejercicio resuelto con referencias absolutas.

3. El uso de nombres de celdas para hacer referencias (son absolutas) es bastante útil, y evita errores. Para darle un nombre a una celda haremos lo siguiente:

pág. 32

pág. 33


Seguidamente daremos el nombre que deseemos a la celda. Una vez hecho esto, cuandoqueramosusarelvalordeesaceldasolotendremosque darsu nombre.

4. Vamos ahora a comentar las referencias entre hojas y entre libros, este tipo de referencias son siempre absolutas, y las podemos realizar de dos formas diferentes, la primera y más fácil será usando el ratón haciendo clic el la celda desde la cual queremos recuperar el valor (esto solo es posible cuando estamos introduciendo una fórmula). La otra será haciendo uso de la sintaxis que nos ofrece Excel para identificar la posición de cada celda,esta sería lasiguiente: „[Nombre_Libro.xls]Hoja_nº‟!Celda.

Si en esta fórmula omitimos el nombre del libro nos indicará que la hoja referenciada es del mismo libro de trabajo. Veamos como sería en nuestro ejercicio

pág. 34

pág. 35


5. Estas tablas son las correspondientes a las referencias entre hojas del mismo libro, el siguiente resultado será el correspondiente a las referencias entre libros distintos.

Hemosdehacerlomismoparatodoslospreciosdecadames.

pág. 36

Ejercicio nº 7 UtilizacióndeFórmulas


•

•

•

Nombre: 7

Ejercicio

Objetivo:

Afianzar el manejo y utilización de las referencias a celdas en las fórmulas de uso común.

Enunciado: El ejercicio consiste en realizar las siguientes operaciones: -

Escribirlosdatosqueaparecenenlascasillasenblanco.

-

Calcular las casillas sombreadas haciendo uso de las fórmulas correspondientes, utilizando referencias relativas y absolutas según corresponda.

-

Aplicarle el formato adecuado a las celdas para obtener un resultado similar al ejercicio propuesto.

NOTA: Para realizar la “Conversión Euro-pesetas” se debe asignar un pág. 37

nombre a la celda que contiene el valor del euro, y posteriormente referenciar el nombre asignado a esta celda para obtener la conversión del precio total en pesetas.

pág. 38


Grabarel libro detrabajo como“Fórmulas.xls”quedeberá deconstardeuna única hoja de cálculoquecontendráel trabajorealizadollamada“datos”.

•

Solución: La solución a este ejercicio, por ser bastante fácil la vamos a dar de forma gráfica, dando para cada columna tanto la fórmula necesaria para calcular su valor como el formato que habrá que aplicar para obtener el aspecto deseado.

pág. 39


pág. 40

Ejercicio nº 8 Fórmulas y Funciones


•

•

Nombre:

Ejercicio8

Objetivo: de:

Practicar el uso - Fórmulas: mediante éstas se llevan a cabo todos los cálculos en una

hoja de cálculo. - Funciones: son fórmulas incorporadas de Excel que a partir de •

Enunciad o:

unos valores realizan una operación devolviendo un resultado.

Crear un nuevo libro de trabajo, que se llamará “Formulas_y_Funciones.xls”, y realizar en distintas hojas de cálculo los siguientes planteamientos: 1.

Función SUMA (hoja: Suma) Realizar la siguiente tabla:

NOTA: El primer total es necesario calcularlo utilizando una fórmula que sume las tres celdas, mientras que el segundo total debe calcularse utilizando la función SUMA. 2.

Función PRODUCTO (hoja: Producto) Realizar la siguiente pág. 41

tabla:

pág. 42


NOTA: El primer total es necesario calcularlo utilizando una fórmula que multiplique las tres celdas, mientras que el segundo total debe calcularse utilizando lafunciónPRODUCTO. 3.

Función PROMEDIO (hoja: Promedio) Realizar la siguiente tabla:

NOTA: El primer total es necesario calcularlo utilizando una fórmula que permite obtener la media aritmética de los tres valores, mientras que el segundo total debe calcularse utilizando la función PROMEDIO.

4.

FunciónCONTAR(hoja:Contar)

Realizar la siguiente tabla y utilizar la función CONTAR para determinar cuántos valores numéricos existen en el rango de datos considerado.

pág. 43

Utilizar también la función CONTARA y comprobar en qué se diferencia de la función CONTAR. Para contar el número de celdas que contienen “letraA”, se usará la función CONTAR.SI que se verá a continuación.

pág. 44


5.

FunciónSI(hoja:Si)

Realizar la siguiente tabla y determinar utilizando la función SI, si el valor de A es mayor, igual o menor que B:

Asignar ahora los siguientes valores: A = 200 y B = 100. ¿Qué ocurre? A continuación, asignar a “A” y a “B” el mismo valor, de forma que el resultado de la función debe ser “A es igual a B”.

6.

Función CONTAR.SI (hoja: Contar_Si) Realizar la tabla siguiente y utilizando la función CONTAR.SI determinar cuántas celdas tienen un valor mayor que cero:

Utilizando ahora los siguientes datos, determinar cuántas celdas tienen un valor mayor, igual o menor que cero.

pág. 45

pág. 46


7.

Función BUSCAR (hoja: Buscar) Escribir la siguiente tabla:

A

continuación

utilizar

la

función

BUSCAR

para

determinar:

-

Los nombres delos colores quetienende valor“4,5” valor“4,5”y y “8,9”. “8,9”.

-

Los colores con el valor más alto y con el valor más bajo (Estos valores se tendránque tendránquecalcular). calcular).

NOTA: El formato de la función BUSCAR es el siguiente: BUSCAR (valor_buscado; vector_de_comparación; vector_resultado)

8.

Función DIASEM (hoja:DiaSem) (hoja:DiaSem)

Escribir la columna “Fecha” y, utilizando la función DIASEM calcular el día pág. 47

de la semana (en número) al que corresponde la fecha correspondiente. Posteriormente determinar el día de de la semana en texto utilizando para ello la función SI o la función buscar, según se desee, tal y como aparece en la siguiente tabla:

pág. 48


9.

FunciónPAGO FunciónPAGO(hoja: (hoja:Pago) Pago)

Se va a realizar un estudio de un préstamo hipotecario, utilizando para ello la función financiera PAGO. El préstamo solicitado es de 90.000,00 € en 15 años. El banco nos ofrece un interés anual del 11,5 %. Partiendo de estos datos y, suponiendo que el interés no va a cambiar,calcular: cambiar,calcular: -

La cuota mensual que deberemos de pagar a lo largo de toda la vida del préstamo.

-

Elnúmerode mesesquedebemosdeestar pagando. pagando.

-

La cantidad total que habremos pagado pag ado una vez finalizado el préstamo.

-

La cantidad que se lleva el banco en cuestión de intereses.

El resultado que deberá de obtener el alumno será como muestra la siguiente tabla: Entrada:

pág. 49


Salida:

A continuación, utilizar la función PAGOINT para determinar la cantidad de intereses que pagamos y la cantidad de capital que amortizamos en el primer año de la hipoteca en cada uno de los pagos mensuales como muestra el siguiente desglose:

A continuación, hacer distintas pruebas variando la cantidad de préstamo solicitado, interés y período de amortización, para comprobar el ajuste de la función PAGO y PAGOINT (considerándose siempre que se trata de un interés constante en elperíodo indicado). pág. 50


10.

FunciónTASA(hoja:Tasa)

La función TASA devuelve la tasa de interés de un préstamo en un año. Su formato básico es: TASA (nper; pago; va) dónde: - nper, esel nº total de periodos de pago en una anualidad. - pago, es el pago efectuado en cada periodo. - va, es la cantidad a pagar. Así pues, utilizando la función TASA, realizar el siguiente ejercicio: hemos pedido en varias entidades bancarias un préstamo de 18.000,00 €. Cada banco ofrece unas condiciones de amortización distintas (periodo de amortización y cuota mensual a pagar). Calcular la tasa de interés anual correspondiente a cada uno de los bancos.

A continuación, calcular cuál de las entidades bancarias nos propone el pág. 51

préstamo más ventajoso, así como cuál de ellas nos propone el peor.

pág. 52


NOTA: Para ello, es necesario hacer uso de las funciones BUSCAR, MAX y MIN. Recordad que para que la función BUSCAR trabaje correctamente es idóneo que el vectoromatrizdebúsquedaseencuentreordenado.

Por último, calcular también el número de propuestas que superan la tasa media anual.

•

Solución: Para las soluciones de esta práctica vamos a dar las funciones utilizadas en cada caso, sin entrar a comentar su funcionamiento.

A5 = A2+A3+A4 B5 = SUMA(B2:B4)

D5 = D2*D3*D4 E5 = PRODUCTO(E2:E4)

A12 = (A9+A10+A11)/3 B12 = PROMEDIO(B9:B11)

pág. 53


B8 = CONTAR(B2:B7) B9 = CONTARA(B2:B7) B10 = CONTAR.SI(B2:B7;"=letraA")

Como se observa, la función CONTARA se diferencia de CONTAR en que CONTARAcuentatodaslasceldasquetenganalgúncarácter.

A4 = SI(B2>B3;"A es mayor que B"; SI(B3>B2;"B es mayor que A"; "Son iguales"))

D13 = CONTAR.SI(C9:D12;">0")

D21 = CONTAR.SI(C17:D20;"<=0") D22 = CONTAR.SI(C17:D20;"=0") D23 = CONTAR.SI(C17:D20;">0")

pág. 54


Para el ejercicio de BUSCAR, primero deberemos introducir la siguiente tabla:

Yelresultado será:

F2 BUSCAR(E2;B2:B10;A2:A10) F3 BUSCAR(E3;B2:B10;A2:A10) E4 = MAX(B2:B10) F4 BUSCAR(E4;B2:B10;A2:A10) E5 = MIN(B2:B10) F5 BUSCAR(E5;B2:B10;A2:A10)

= =

=

=

El siguiente apartado es el uso de DIASEM y buscar el día de la semana. Hay que tener en cuenta, que según la configuración del sistema, 12/01/20 puede ser de 1920 o 2020, cambiando el resultado según se considere. pág. 55

pág. 56


A2 = DIASEM(A2;2), y estafórmula deberá copiarse al resto de celdas. DIASEMtienedosargumentos: •

•

Núm_de_serie: es la fecha que buscamos. Tipo: Es un parámetro que indica como se comporta la función. Esta función está preparada para distintas regiones en las que el lunes no es el primer día de la semana. Para que considere que el lunes es 1, tendremos que poner este valor a 2.

Para calcular el día de la semana que le corresponde, tenemos dos opciones, usando SI o usando BUSCAR. •

Usando SI: deberemos introducir una sentencia SI anidada, quedando elresultado: C2 = SI(B2=1;"LUNES"; SI(B2=2;"MARTES"; SI(B2=3;"MIERCOLES"; SI(B2=4;"JUEVES"; SI(B2=5;"VIERNES"; SI(B2=6;"SÁBADO"; "DOMINGO"))))))

Estafórmulasecopiaráalrestode celdas. •

Usando BUSCAR: deberemos introducir una tabla auxiliar del siguientemodo:

pág. 57

pág. 58


Y a continuación usar la función buscar: D2 = BUSCAR(B2;$E$2:$E$8;$F$2:$F$8) Copiando la fórmula al resto de celdas.

Veamosahoralasfuncionesfinancieras.

Donde: D2 = PAGO(B3/12;B4*12;-B2) D3 = B4*12 D4 = D2*D3 D5 = D4-B2

LafunciónPAGOquehemosusadotiene3argumentos: •

•

•

B3/12: Es el interés mensual. B4*12: Son el número de plazos (de meses). -B2: Lo que tenemos que pagar (se pone en negativo).

pág. 59


. . .

Para el primer tendremos:

mes

F3 = $D$2 G3 = F3-H3

H3 = PAGOINT($B$3/12;E3;$D$3;$B$2) I3 = SUMA($G$2:G3) J3 = $B$2-I3

Para segundo:

el

F4 = $D$2 G4 = F4-H4

H3 = PAGOINT($B$3/12;E4;$D$3;$B$2) I3 = SUMA($G$2:G4) J3 = $B$2-I4 Tenemos que observar como va cambiando la columna I para poder usar la suma y como se han usado las referencias relativas y absolutas para pág. 60

poder copiar las fórmulasaotrasfilas.

Podemos obtener información de la función PAGOINT en la ayuda de Excel.

pág. 61


Paraelcálculodelatasaanualvamosautilizarlasiguiente fórmula: E2 =TASA(C2;-D2;B2)*12

Estafórmuladeberácopiarsealrestode celdas. Ahora tenemos que calcular la mejor y la peor oferta:

Para E17 y E18 no hay ninguna complicación y usaremos la funciones MIN y MAX del siguiente modo: E17 = MIN(E2:E12) E18 = MAX(E2:E12)

Ahora hay que buscar el nombre del banco, para ello podemos usar BUSCAR: pág. 62

D17 = BUSCAR(E17;E2:E12;A2:A12) D18 = BUSCAR(E18;E2:E12;A2:A12)

pág. 63


Pero estas funciones no funcionarán ya que BUSCAR sólo trabaja con listas ordenadas.Parasolucionarestotenemosdosopciones. 1. 2.

Ordenar los datos desde el menú Datos – Ordenar. Utilizar una columna adicional y la función BUSCARV, del siguiente modo:

y ahora: D17 = BUSCAR(BUSCARV(E17;$E$2:$G$12;3;FALSO);$G$2:$G$12;$A$2:$A$ 12) D18 = BUSCAR(BUSCARV(E18;$E$2:$G$12;3;FALSO);$G$2:$G$12;$A$2:$A$12) La función BUSCARV se verá mas detenidamente en otros ejercicios. Por último debemos calcular la tasa media y el número de propuestas que la superan.

H17 = PROMEDIO(E2:E12) H18 = CONTAR.SI(E2:E12;">10,83%") pág. 64

Ejercicio nº 9 Gráficos


•

•

•

Nombre: 9

Ejercicio

Objetivo:

Practicar el uso de gráficos dentro de Microsoft Excel para que el alumno se habitúe a su utilización.

Enunciado: Elejercicioconsistiráenrealizar unaseriede gráficosdelosalumnosmatriculadosen las 8 provinciasandaluzasduranteunaseriedeaños.

También, se calculará la media del número de alumnos en cada provincia. Las operaciones a realizar son las siguientes: 1.

Crear un libro de trabajo llamado “Graficos.xls” que contendrá una hoja de cálculo llamada “Alumnos”.

2.

Posteriormente, se debe introducir la siguiente tabla en la hoja de “Alumnos”, aplicándole el formato según se desee. No introducir el

valor de la columna “Media”, pues esta se calculará en el siguiente punto.

pág. 65

3.

Calcular el promedio o media de los alumnos en cada provincia, utilizando para ellolafunciónPROMEDIO.

pág. 66


4.

Crear distintos tipos de gráficos circulares en los que se muestren los alumnos de cada universidad en los diferentes cursos académicos. Se crearán, por tanto, cinco gráficos diferentes, uno por curso. El resultado debe ser similar al que se presenta a continuación:

Curso 96 - 97 7%

5%

Curso 97 - 98

4%

14%

5%

7%

11%

5%

14%

10%

22% 15%

22% 14%

22%

23%

Córdoba

Granada

Sev illa

Málaga

Córdoba

Granada

Sev illa

Málaga

Jaén

Huelv a

Almería

Cádiz

Jaén

Huelv a

Almería

Cádiz

Curso 98 - 99 940

Curso 99 - 00

700

2.480

1.200

1.300

1. 8 0 0

2. 0 0 0

1.100

850

2.800

3.700 2.500

4.250 2.800

3.800

3.950

Córdoba

Granada

Sev illa

Málaga

Córdoba

Granada

Sev illa

Málaga

Jaén

Huelv a

Almería

Cádiz

Jaén

Huelv a

Almería

Cádiz

pág. 67


5.

Crear distintos tipos de gráficos de columnas dónde se muestre la evolución de los alumnosencadaUniversidadalolargodeloscincocursos.Asípues,será necesario crear8gráficosdiferentes: Universidad de Córdoba 4.000 3.000

2.480

2.600

2.750

2.800

Universidad de Granada 3.000

6.000 4.000

3.700

4.000

4.200

4.250

96-97

97-98

98-99

99-00

5.000

2.000 2.000

1.000 0

0 96-97

97-98

98-99

99-00

00-01

Universidad de Sevilla

Universidad de Málaga

4.150

4.200

2.930

3.000

4.100 3.950

4.000

00-01

3.950

2.600

3.800

3.800

2.800

2.900

99-00

00-01

2.800 2.500

2.540

96-97

97-98

2.400 2.200

3.600 96-97

97-98

98-99

99-00

00-01

98-99

pág. 68


Universidad de Jaén 2.200

Universidad de Huelva 2.000

2.100 2.000

2.000 1.800

1.500

1.945

1.890

1.200

1.250

97-98

98-99

1.300

1.350

99-00

00-01

1.000

1.800

500 0

1.600 96-97

97-98

98-99

99-00

00-01

96-97

Universidad de Almería 1.500 940

1.000

97 5

1.050

1.100

Universidad de Cádiz 1.500 1.250 1.000 750 500 250 0

1.150

500 0 96-97

6.

1.180

97-98

98-99

99-00

00-01

700

84 0

830

96-97

97-98

98-99

850

875

99-00

00-01

Por último es necesario crear un gráfico de líneas que refleje las medias del número de alumnos en cada universidad, tal y como muestra la siguiente figura:

Media de Alumnos 4.230

4.500 4.000

3.990

3.500 2.734

3.000 2.500 2.000

1.947

2.726

1.256

1.500

1.043

819

1.000 500 0 Córdoba

Granada

Sev illa

Málaga

Jaén

Huelv a

Almería

Cádiz

pág. 69


•

Solución:

1.

Creamos un nuevo libro y cambiamos el nombre a las hojas con lo indicado en el enunciado del ejercicio y borramos el resto de las hojas. Introducimoslosdatosindicadosyledamosel formatoquedeseemos.

2.

Para calcular la media utilizaremos la función PROMEDIO: G2 = PROMEDIO(B2:F2) CopiamoselvalordeG2alrestodelacolumnapromedio.

3.

Vamos a realizar un gráfico circular de los alumnos del año 99-00. Para ello deberemos seleccionar la columna A de A1 a A9 y la columna E de E1 a E9. (Primero seleccionamos A1:A9 y después, con la tecla Ctrl pulsada, seleccionamos E1:E9). A continuación pulsamos sobre el botón y seguimos los pasos del asistente que nos guiará para crear el gráfico. Los pasos son los siguientes:

pág. 70


a) Seleccionamos el tipo de gráfico:

pág. 71


b) Comprobamosquelosdatossonlos quehemosseleccionado:

c) Cambiamoseltítuloylosrótulosdedatos:

pág. 72


d) Creamos el gráfico en una hoja nueva que llamamos, por ejemplo, “Circular 99-00”:

pág. 73


El resultado final será:

Crearemos otros 4 tipos de gráficos circular para el resto de años. Podemos probar lascombinacionesquequeramosenlasopcionesyver queresultados obtenemos.

Por ejemplo, podemos conseguir los siguientes gráficos:

pág. 74


4.

Vamos a realizar la gráfica con los datos de córdoba. Los pasos a seguir son iguales que en el apartado 3, pero en este caso seleccionaremos filas y utilizaremos gráficos de columnas. Para Córdoba podemos tener:

pág. 75


Aplicando distintos estilos y herramientas de dibujo, podemos obtener, usando los datos de Cádiz, el siguiente gráfico:

5.

Para este apartado, deberemos seleccionar A1:A9 y G1:G9, siguiendo los mismos pasos que en los puntos anteriores, eligiendo el tipo de gráfico lineal. El resultado puede ser:

pág. 76


Aplicando distintos estilos al gráfico, podemos obtener:

pág. 77

E j e rc ic io n º 10 UCOTienda


•

•

•

Nombre: 10

Ejercicio

Objetivo:

Calcularlas ventasrealizadas en los meses de Enero,Febrero y Marzo en la UCOTienda,partiendodelpreciodecostede losproductos.

Enunciado: Partiendo de los productos vendidos en enero, febrero y marzo y conociéndose el precio de coste de estos productos en estos meses, calcular:

-

El porcentaje de I.V.A. que debe ser aplicado a cada producto (16%) (se podrá cambiaresteporcentaje).

-

El precio de venta al público (P.V.P.) tanto en euros como en pesetas (se podrá cambiarelvalordelcambio).

-

Eltotaldeventasparacadaunodelosmeses.

-

Unpequeñobalancedelas ventasrealizadasenelprimertrimestre.

-

Unahojaresumencontodaslas tablas.

NOTA: Se deberán crear 5 hojas de cálculo, una para las ventas de enero, otra para las de febrero, otra para las de marzo y otra para el balance del 1er trimestre. Finalmente crea una nueva hoja llamada “Presenta” que reúna toda la información anterior y que tenga el siguiente aspecto:

UCO-TIENDA

pág. 78


pág. 79


•

Solución: 1. 2.

Abrir un libro nuevo y llamarlo Ucotienda.xls. Cambiarel nombrea cada una delas hojasdecálculo queaparecen pordefecto por lossiguientes:

3.

En la hoja ENERO escribe la tabla siguiente. Vamos a calcular las columnas

%I.V.A., P.V.P.,TotalyToral –ptas.

4. Para calcular la columna % I.V.A., creamos un nombre. Siguiendo con el ejemplo, nos posicionamos sobre la celda C18 y escribimos 16%, vamos a Insertar/Nombre/Definir…, y obtenemos una pantalla como la quesigue:

pág. 80


Le damos el nombre de IVA y damos a Agregar. Así habremos creado un nombre IVAconelvalor 16%

5. Ahora vamos a calcular el porcentaje de I.V.A. de cada artículo, para hacerlo escribimos la fórmula siguiente en la celda D5: D5 = C5*IVA donde IVA es el nombre creado anteriormente. Copiamos la fórmula al resto de las celdas de la columna y obtenemos la columna completa de la forma:

6. AhoracalculamoslacolumnaP.V.P.sumandolascolumnasPrecioCostee I.V.A.,la fórmulaenlaceldaE5quedarádelaforma: pág. 81

E5 = C5+D5

pág. 82


Copiamos al resto de las celdas de la columna y obtenemos el siguiente resultado:

7. Todos las columnas conlamoneda €,llevanunformatopersonalizado aplicado de laformaenlapantalladeformatos:

8. Para calcular el Total debermos multiplicar el número de unidades vendidas por el pág. 83

P.V.P. del producto, introduciendo en la celda F5:

pág. 84


F5 = B5*E5 Copiamos al resto de las celdas de la columna y obtenemos el siguiente resultado:

9. Para calcular la columna Total –ptas., creamos otro nombre llamado EURO, de la misma forma que para IVA, la ubicación del nombre será en C19, la pantalla donde se define el nombre quedaría dela forma:

10. Aplicamos una fórmula =E5*EURO, en la celda G5 y la copiamos al resto de las celdas de la columna. El resultado después de haber aplicado el formato:

pág. 85


esdelaforma:

11.

Calculamos la suma total de cada una de las columnas de importes,

quedando un resultado de la forma:

pág. 86


Para calcular los totales usamos la opción de autosuma, disponible en la barra de herramientas o en el menú, que es más rápida. 12.

A continuación vamos a hacer lo mismo en las dos hojas siguientes,

FEBRERO y MARZO. En cada una de ellas escribe la tabla que corresponda y calcula los valores de las columnas Precio Coste, %I.V.A. P.V.P, Total y

Total – ptas.-, siguiendo los mismos pasos. Recuerda que los nombres no tienen que volver a definirse en cada una de las hojas, sirven en todo el libro.

Una vez obtenidos los resultados en cada una de las hojas vamos a sacar un balance del primer trimestre del año en ventas. Creamos otra hoja llamada

13.

1er.trimestre, con la siguiente tabla:

pág. 87

pág. 88


14.

Vamos rellenando la tabla tomando los datos obtenidos de las hojas

anteriores, se puede decir que esta tabla es una tabla resumen de los tres trimestres.

15. Primero vemos la Unidades Vendidas. Este dato lo calculamos en la hojas ENERO, FEBREROyMARZO,portantodeberemosintroducir:

B7 = ENERO!B14 B8 = =FEBRERO!B11 B9 = =MARZO!B11 La sintaxis de las referencias usadas tienen: primero el nombre de la hoja seguidas de una exclamación y las coordenadas de las celdas donde están los resultados, por ejemplo en el primer caso buscamos en la celda B14 de la hoja ENERO, y así sucesivamente.

16. La columna Precio Coste, sólo debe mostrar los totales de las columnas Precio

Coste de cada mes anteriormente calculados en cada una de las hojas. Usamos referencias a otras hojas del libro, para cada uno de los meses del trimestre quedaría de la forma, en las celdas de nuestra tabla C7, C8 y C9: C7 = ENERO!C14 C8 = FEBRERO!C11 C9 = MARZO!C11

17. Repetimos el proceso con la columna %I.V.A para que muestre los totales obtenidos en cada una de las hojas, empezamos en nuestra tabla en la pág. 89

celda D7, vamos copiando cada una de estas referencias:

D7 = ENERO!D14 D8 = FEBRERO!D11 D9 = MARZO!D11

18. Ahora continuamos con la columna P.V.P., escribimos por orden en la columna E de nuestra tabla desde la fila 7 a la 9:

pág. 90


E7 = ENERO!E14 E8 = FEBRERO!E11 E9 = MARZO!E11

19. Para la columna Total, tendremos: F7 = ENERO!F14 F8 = FEBRERO!F11 F9 = MARZO!F11 20. Y por último la columna Total –ptas.: G7 = ENERO!G14 G8 = FEBRERO!G11 G9 = MARZO!G11

21. Calculamos la suma total de esta tabla, usando la opción de autosuma, quedando la fila de la forma:

Latablaresumenquedadela forma:

22. Por último, para crear la hoja resumen, copiaremos una a uno todas las tablas. pág. 91

Tenemos que tener cuidado en que no podemos copiar las fórmulas. Para ello tendremosqueusarelpegadoespacialdelmenúedición.

pág. 92

E j e r c i c i o n º 11 Titulaciones


•

•

•

Nombre: 11

Ejercicio

Objetivo:

Calcular el mayor y menor número de aprobados de las diversas titulaciones impartidas en la Universidad.

Enunciado: Disponemos del número de alumnos que finalizaron sus estudios en la Universidad de Córdobaenelcursoacadémico1999-2000,comomuestralasiguiente tabla:

pág. 93


Realizar las siguientes operaciones: -

Elnºtotaldealumnosquefinalizaronsusestudiosenelcursoconsiderado. Calcular la titulación que tuvo el mayor número de aprobados, y la que

tuvo el menor número de aprobados.

-

Realizar en una nueva hoja de cálculo un gráfico comparativo que muestre el número de aprobados de cada una de las titulaciones. Aprobados en 1999-2000

Aprobados en 1999-2000 M aestro , Lengua

23

Licenciado en Física

45

Ext ranjera

43

207

Licenciado en Derecho

Licenciado en Ciencias Ambient ales

M aest ro, Educación M usical

35

48 60

Licenciado en Ciencia y Tecnologí a de l os Alimentos

27

M aest ro,

26

Educación Fisica

9

Licenciado en Bioq uí mica

52 Licenciado en Bio log í a

19

Ingeniero Técnico Indust rial

23

Ingeniero Técnico en Informát ica

19

M aest ro, Audición y Lenguaje

39

101 Licenciado

58

Historia del Ar t e

83

10

Ingeniero Técnico de M inas

Licenciado Filologí a Hispánica

68

Ingeniero de M ont es

47 50

8

Ingeniero Agrónomo

Licenciado en

51

Psicopedagogí a

35

Diplomado en Graduado Social

12 Licenciado en

86

Diplomado en Enfermería

0

50

100 150

22

Hist oria 200 250

0

50

100 150

200

250

pág. 94


•

Solución: 1.

Escribimos la tabla anterior y le damos formato, de la forma:

2.

Cambiamos el nombre a la hoja y ponemos el mostrado en la hoja de cálculo. Calculamos el total de alumnos que finalizaron sus estudios en el curso 1999, para ello usamos la función suma. Nos posicionamos en la celda B3 y escribimos la fórmula:

=SUMA(B3:B30) Esta fórmula suma todos los valores incluidos entre la celda B3 y la B30. O bien podemos usar la autosuma de la forma: pág. 95


3.

Para calcular la titulación que tuvo el mayor número de aprobados, y la que tuvo el menor número de aprobados, nos hacemos otra tablita de la siguiente forma, y vamos a calcular las coordenadas E3, E4, F3 y F4.

4. Primero calculamos cuál es el número máximo y mínimo de alumnos, lo hacemos con las funciones MAX y MIN. =MAX(B3:B30)

Usamos la función MAX y entre paréntesis los valores entre los que tiene que buscar, en este caso la columna B. Lo mismo para la función MIN. =MIN(B3:B30)

AsíobtenemoslasceldasF3yF4.

5. A partir de ahí buscamos el nombre de la titulación a la que corresponde el máximo y mínimo número de aprobados, obtenidos anteriormente. Llamamos al asistente de gráficos y usamos la función BUSCAR, obteniendo una pantalla de la forma: pág. 96


6. Así obtenemos el nombre de la titulación de mayor número de alumnos, hacemos lo mismo con la titulación de menor número de alumnos, pero en lugar de tomar F3 como valor buscado usamos F4.

Nota: Para que este procedimiento funcione, hay que ordenar previamente la lista por Número de alumnos o entonces usar el pág. 97

método explicado en el

pág. 98


ejercicio 8 de usar una columna adicional y una combinación de BUSCAR – BUSCARV. 7. Realizamos en una nueva hoja de cálculo, llamada Gráficos, un gráfico comparativo que muestre el número de aprobados de cada una de las titulaciones.

8. Como tenemos muchas titulaciones para presentar gráficamente, las dividimos en dos gráficos. En el primero representamos desde los Diplomados en Enfermería hasta los Licenciados en Física y en el segundo gráfico representamos desde los Licenciados en Humanidades hasta Maestro en Lengua Extranjera.

9. Para el primer gráfico, llamamos al asistente de gráficos y elegimos el Tipo de gráfico Barras y el Subtipo de Gráfico, Barra agrupada, igual que en la imagen.

pág. 99

pág.


10. Pulsamossiguienteyelegimoslosdatosdesdelafila3hastala15 delaforma:

11. Pulsamos siguiente y damos formato al gráfico hasta que quede de la forma dada anteriormente. 12. Repetimos lo mismo con el resto de los datos que faltan por representar, desde la fila 16 hasta la 30.

pág.

E je rc ic io n º 12 Notas de Alumnos


•

•

•

Nombre: 12

Ejercicio

Objetivo:

Obtener visual y gráficamente los resultados de una serie de alumnos a lo largo del curso.

Enunciado: Disponemos de las notas obtenidas por trimestres de un reducido número de alumnos. Las notas se encuentran distribuidas en tres trimestres y una nota final, tal y como muestra la siguiente tabla:

NOTA: Todas las celdas que contienen una nota numérica tienen aplicado un formato condicional, de forma que: -

Si la nota es 0 tendrá un fondo de color azul y el texto en negrita de color blanco.

-

Si la nota es 10 tendrá un fondo de color rojo y el texto en negrita de color blanco.

-

En cualquier otro caso tendrá el formato que muestra la tabla anterior.

pág.

Realizarlassiguientesoperaciones: -

Insertar una nueva columna para calcular la nota media de cada alumno, considerando las tres notas de cada trimestre y la nota final.

-

Insertar otra columna que indique la nota alfabética (Sobresaliente, Notable, Bien, Suficiente, Insuficiente, Muy deficiente) en base a la nota numérica teniendo en cuenta que: 0 2 5 6 7

MUY DEFICIENTE INSUFICIENTE SUFICIENTE BIEN NOTABLE

9 SOBRESALIENTE

Elresultadoobtenidodebesersimilaralsiguiente:

Seguidamente realizar un cuadro estadístico desglosado por sexos, recogiendo el número total de alumnos, el número de aprobados y el número de suspensos:

NOTA:PararealizarestaestadísticaseránecesarioutilizarlafunciónCONTAR.SI.

pág.

A continuación se desea realizar las siguientes representaciones gráficas: -

Ungráficoquerepresentelas notasmediasobtenidasporlosalumnos, comparadas cadaunadeellasconlanotamediade laclase.

PUNTUACIONES 10,0

Nota media

8,0

Media 6,0

4,0

2,0

0,0

Alumnos

- Ungráficocircularquemuestreelporcentajedealumnosconsideradospor sexo: Porcentaje de Alumnos por sexo

2; 33%

4; 67% Hombres Mujeres

pág.


-

Un gráfico circular que muestre el porcentaje de alumnos aprobados por sexo:

Aprobados 50%

Hombres

50%

-

Mujeres

Un gráfico circular que muestre el porcentaje de alumnos suspensos por sexo:

Suspensos 0%

Hombres 100%

•

Mujeres

Solución: 1. Abre un libro nuevo y llámalo Notas_alumnos.xls . Borra todas las hojas menos una. Escribe la tabla que tienes debajo, y dale formato.

pág. 71


2. Inserta una columna, al final de la tabla anterior, y calcula la media de los tres trimestres y la nota final, usando la función PROMEDIO para que quede de la forma: =PROMEDIO(B5:E5)

Escribimos la fórmula en la celda F5 y luego copiamos la fórmula al resto de celdas de esa columna F.

3. Nombra la hoja anterior como Calificaciones, al final del ejercicio tendremos tres hojas de esta forma:

4. Inserta una nueva hoja llamada Nota_letra y escribe una tabla con el contenido de la siguiente:

pág. 72


5. Inserta otra columna y aquí calcula la nota alfabética(Sobresaliente, Notable, Bien, Suficiente, Insuficiente, Muy deficiente) en base a la nota numérica, usando la función BUSCARV, por ejemplo en nuestro ejemplo: =BUSCARV(F5;Nota_letra!A$2:B$7;2)

Escribimos la fórmula en G5 y después copiamos al resto de las celdas la fórmula.

6. Vamos a calcular la nota máxima y la mínima obtenida de entre los tres trimestres y la nota final. Esto lo hacemos con las funciones MIN y MAX: =MIN(B5:E10) =MAX(B5:E10)

Creamos una tablita de la forma:

7. También queremos buscar en la tabla donde están estas dos calificaciones, para esto usamos el formato condicional. Seleccionamos los datos de la tabla donde están la calificaciones, desde la celda B5 hasta la celda E10, y vamos al menú Formato, Formato Condicional y escribimos en la pantalla lo siguiente:

pág. 73


Al aceptar el valor todas las celdas que contengan el valor de la celda aparecerán con el formato especificado, en azul para el mínimo y en rojo para el máximo, visualmente lo vemos en la primera tabla que hemos escrito. 8. Insertamos otra columna donde se calcule la media de la columna Nota

media número =PROMEDIO(F$5:F$10)

9. Los pasos que sigamos ahora, nos van a llevar a obtener una estadística de aprobados y suspensos. Insertamos otra columna donde aparezca el sexo del alumno(HparahombreyMparamujer).Ennuestroejemploes:

pág. 74


10. Ahora insertamos otra columna donde aparezca solamente, el sexo de aquellos alumnos y alumnas que hayan aprobado, esto nos servirá posteriormente para hacer una estadística por sexos de aprobados y suspensos. El valor de esta columna lo rellenamos usando las columnas de Nota media número y Sexo. La fórmula en nuestro ejemplo es: =SI(F5>=5;J5;" ")

Esta fórmula nos dice si el alumno tiene una nota mayor o igual a 5 entonces ponemos el valor que haya en la celda que contiene el Sexo, en nuestro ejemplo es J5, y si no pues lo deja en blanco y no especifica el sexo del alumno.

11.

Debajo de la primera tabla creamos otra tabla, donde contaremos

usando las funciones SUMAR.SI y CONTAR.SI. Debemos crear otra columna done se inserten solo unos, de la forma: pág. 75


12.

Lasfórmulasquedebemosusarencadaceldason: Total alumnos =SUMAR.SI(J5:J10;"H";I5:I10) Total alumnas =SUMAR.SI(J5:J10;"M";I5:I10)

En la fórmula las columnas usadas son la J que es la del Sexo y la I que es la que previamente hemos rellenados de unos. Explicando la función SUMAR.SI, tenemos que toma la columna J para comparar si el valor allí contenido es H o M y si es así va sumando los unos contenidos en la columna I, al final obtenemos el número de hombre y mujeres que han sido alumnos. Obtenemos la celda B20 y B21 en nuestro ejemplo.

Para ver los alumnos y alumnas aprobados y aprobadas escribimos las fórmulas anteriores usando la función CONTAR.SI, que va contando de la columna K (sexo_apro) cuantos son H y cuantos son M. Alumnos aprobados =CONTAR.SI(K5:K10;"H") Alumnas aprobadas =CONTAR.SI(K6:K11;"M")

pág. 76


Los suspensos se calculan simplemente con una resta, el total de alumnos o alumnas menos los aprobados o aprobadas. Las fórmulas y tabla de resultados son las siguientes: Alumnos suspensos =B20-C20 Alumnas suspensas =B21-C21

13. Ocultamos las columnas I, J y K, que hemos usado solamente para los cálculos de la tabla de Aprobados y Suspensos

14. Ahora vamos a representar gráficamente los resultados obtenidos, para ello insertamos otra hoja llamada Gráficos. Primero representamos las notas medias obtenidas por los alumnos y las comparamos cada una de ellas con la media de la clase. Hacemos uso el asistente de gráficos bien desde la barra de herramientas pulsando sobre el icono

, o bien en elmenú InsertarGráfico.

pág. 77


Apareceunapantallacomolaquesigue:

15. Elegimos el tipo de gráfico Líneas y el Subtipo de gráfico marcado y damos a siguiente.

16. Ahora vamos a elegir los datos de nuestro gráfico. Pulsamos sobre el botón de Rango de datos

pág. 78


y seleccionamos dos columnas de datos a la vez, la de Alumnos y la de Nota

Media Número. El resultado queda de la forma:

pág. 79


Aquí tenemos representado la nota media de cada alumno. Añadimos otra serie de datos que nos muestre la media de la clase, nos vamos a la pestaña

Series, y queda de la forma:

17. Damos a siguiente y aparece otra pantalla con seis pestañas, en cada una de ellas podemos elegir distintas opciones y ver los cambios que se producen en el gráfico, en nuestro caso:

pág. 80


18. Este gráfico es que hemos obtenido después de seguir los pasos anteriores y después de haber modificado las propiedades de cada elemento del gráfico, recuerda que haciendo doble clic sobre cada elemento del gráfico podemos cambiar su aspecto externo, haz la prueba hasta que obtengas este resultado final.

pág. 81

pág. 82


19. Ahora representamos el porcentaje total de alumnos. El gráfico que elegimos en este caso es Circular :

20. Damos a siguiente y elegimos la serie de datos a representar, en este caso vamos a usar la pequeña tabla que contenía el número total de alumnos, discriminados por sexos y por aprobados y suspensos. En este gráfico tomamos las columnas que contiene el Sexo y el Total, el resultado es de la forma:

pág. 82


21. Damos a siguiente y ponemos en la pestaña de Título Títu lo Porcentaje de alumnos y en la pestaña de Rótulo de datos, elegimos la casilla Porcentaje, damos a finalizar y tras cambiar el color de la trama de fondo obtenemos obtenemos este resultado.

pág. 83


22. Los dos siguientes gráficos representan cuantos suspenso y aprobados ha habido discriminados por sexo. Seleccionamos el Tipo de gráfico de anillo y la columna que contiene los Sexos y la de Suspensos, en la siguiente pantalla en la pestaña del Titulo Suspensos, y para tener el porcentaje igual que anteriormente la casilla Porcentaje enlapestaña Rótulo de datos.

23. Hacemos lo mismo en este caso pero seleccionando la columna de

Aprobados , el resultado es este. Trabaja pinchando sobre las distintas partes del gráfico y observa como van cambiando.

pág. 84

E j e rc ic io n º 13 Tablasde Multiplicar


•

•

•

Nombre: 13

Ejercicio

Objetivo:

Trabajar con referencias absolutas, relativas y mixtas.

Enunciado: 1.

Crear un libro llamado “Tablas de Multiplicar” con 3 hojas. A la primera la llamaremos “Tabla1”, a la segunda “Tabla2” y a la tercera “Tabla3”.

2.

En hoja Tabla1 construiremos una tabla de multiplicar como la que se muestra a continuación, en la que cada celda contiene el producto de la fila por la columna correspondiente (dar a las columnas el tamaño 5).

Consej o Puede introducir todos los números, o introducir los dos primeros, seleccionarlos y arrastrar el vértice inferior derecho de la selección al resto de las celdas. Verá como se va indicando i ndicando el número de con la que que Excel rellenará la celda. 3.

A continuación introduciremos las fórmulas oportunas para mostrar el resultado deseado:

pág. 85


4.

Copiar el contenido de la hoja Tabla1 a la hoja Tabla2. Cambiar el contenido de la fila 1 a“Tabla de Multiplicar2”.

5.

En la hoja Tabla3 deberemos calcular la multiplicación del contenido del la tabla de multiplicar de Tabla1 por el de tabla2, es decir, si para la intersección de 2,2, el resultado será 4*4=16.

•

Solución: Lo primero que haremos será cambiar el nombre a cada hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. Una vez introducida la tabla en la hoja “Tabla1”, tenemos que introducir en cada celda el producto del valor contenido en la columna A y en su misma fila, por el valor contenido en la celda de su misma columna y fila 3, tal y como muestran las flechas en la tabla anterior donde el valor de la celda D5=A5*D3. Así los resultados a obtener serían: -

B4=A4*B3 C4=A4*C3 pág. 86

-

D4=A4*D3 B5=A5*B3 B6=A6*B3 Etc.

pág. 87


Si observamos las fórmulas anteriores nos daremos cuenta que las referencias a la columnaAyalafila 3nuncacambian.

Sin embargo si sólo se usan referencias relativas al copiar la fórmula de la celda B4=A4*B3, al resto obtendríamos los siguientes resultados: •

•

•

Al copiarla hacia la derecha: C4=B4*C3, D4=C4*D3, E4=D4*E3, etc. Es decir, la referencia a la columna A en el primer multiplicando va cambiando, la solución sería fijar esta columna. Al copiarla hacia abajo: B5=A5*B4, B6=A6*B5, B=A7*, etc. En este caso la referencia a la fila 3 en el segundo multiplicando va cambiando, la solución será fijar estafila. Por tanto la fórmula correcta a introducir en la celda B4 sería =$A4*B$3.

A continuación se copiará el contenido de la hoja “Tabla1” a la hoja “Tabla2”. Cambiamos el contenido de la fila 1 a “Tabla de Multiplicar 2”. Comprobamos que losresultadossoncorrectos. Copiamos el contenido de la hoja Tabla1 a la hoja Tabla2 y borramos el resultado. Cambiamoselcontenidodelafila1 a“TablaResultado”. Introducimos las fórmulas adecuadas para que el resultado sea el producto de las dos tablas, es decir, B4 = B4 Tabla1 * B4 Tabla2 y así sucesivamente. Para ello deberemos usar referencias a otras hojas, para ello deberemos usar lanomenclatura NombreHoja!celdas. De este modo, horizontalmente: -

tenemos

que

B4=Tabla1!B4*Tabla2!B4 C4=Tabla1!C4*Tabla2!C4 D4=Tabla1!D4*Tabla2! pág. 87

D4 Y verticalmente: -

B5= Tabla1!B5*Tabla2!B5 B6=Tabla1!B6*Tabla2!B6 B7=Tabla1!B7*Tabla2!B7

pág. 88


En esta ocasión, no hay referencias constantes, por tanto, rellenando la celda B4 y copiando su contenido al resto de celdas, obtenemos el resultado buscado, quedando la hoja Tabla3 del siguiente modo:

pág. 88

E je rc ic io n º 14 Matrículas2003


•

•

•

Nombre: 14

Ejercicio

Objetivo:

Realizar cálculo de totales y Gráficos

Enunciado: Calcular el total de alumnos matriculados en el primer curso, así como el porcentaje de hombres y mujeres.

1.

Crear un libro llamado “Matrículas 2003.xls” con 2 hojas, una llamada “Alumnos

2.

Matriculados” y otra llamada “Gráficos Alumnos”.

En la hoja “Alumnos Matriculados ” construiremos la siguiente tabla con los datos indicados:

3.

Introducir las fórmulas adecuadas para calcular la columna TOTAL. Las columnas de %Mujeres y %Hombres deberán expresarse en %. La hoja deberá mostrar el siguiente aspecto:

pág. 89

pág. 90


4.

Calcular los Totales de la fila 11. El resultado mostrará:

5.

Realizar un gráfico en la hoja “Gráficos Alumnos” donde se muestre el reparto de matrículas por titulación. El gráfico quedara del siguiente modo:

6.

Realizar un gráfico con en el que se muestre el reparto por sexos en cada titulación. Elresultadodeberáquedardelsiguientemodo:

pág. 90


•

Solución: 1.

Lo primero que haremos será cambiar el nombre a cada hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. La tercera hoja que crea Excel por defecto deberemos borrarla.

2.

A continuación introducimos la tabla que se nos indica. Para ello deberemos combinar las celdas de B1 a H1 para el título, de B2 a C2, de D2 a E2 y de F2 a H2 para las categorías de las columnas. Para combinar las columnas podemos usar la opción “Combinar celdas” que tenemos en el menú Formato – Celdas… - Alineacióno el botón delabarradeherramientas.

Se puede dar el formato a la tabla en estos momentos, pero es preferible esperar a que este terminado. Si lo realizamos ahora puede que tengamos que volver a aplicarlo a distintas partes de la tabla según introducimos datos y fórmulas. pág. 91

3.

Vamosacalculareltotaldelosalumnosmatriculados.Tendremos: F4=B4+D 4 F5=B5+D 5

…

pág. 92


Tenemos2opciones: •

•

Introducir las fórmulas en todas las casillas. Utilizar la potencia de Excel en el uso de referencias relativas. Introducimos sólo la fórmula en la celda F4 y la copiamos a las demás.

A la hora de calcular el porcentaje de Hombres y Mujeres, tendremos que usar las fórmulas: Porcentaje Mujeres =

Total Mujeres Total Alumnos

Porcentaje Hombre = 1 − Porcentaje Mujeres

Observamos que hemos calculado el porcentaje en “tanto por 1”. Esto es debido a que cuando apliquemos a las celdas el formato %, Excel multiplica por 100 el valor delacasilla.

De forma:

esta

G4=(C4+E4)/F4 H4=1-G4 Copiamos estas fórmulas al resto de celdas y obtendremos los resultados buscados. 4.

Paracalcularlostotalesdelafila11tenemos2opciones: •

•

Introducir la fórmula de la manera B11=B4+B5+B6+…+B10. Utilizar la auto-suma que proporciona Excel. Para elle seleccionamos

desde la celda B4 a la B11 y pulsamos sobre el botón de la barra de herramientas. Estonos introducirálafórmula: B11=SUMA(B4:B10) RepetiremoslamismaacciónparalasceldasC11,D11, E11 yF11. 5.

Enesteapartadadeberemosintroducirungráficocircular.Paraello pág. 92

seleccionamos las celdas de A4 a A10 (A4:A10) y F4 a F10 (pulsaremos la tecla Ctrl para que no perdamos la primera selección) A continuación pulsamos sobre el botón de la barra de herramientas y seguimos los siguiente pasos:

pág. 93


a) Elegimos el tipo de gráfico y pulsamos siguiente:

pág. 93


b) Comprobamos los datos de origen, que coincidirán con los que hemos seleccionadoypulsamossiguiente:

pág. 94


c) Introducimos los títulos, leyenda y rótulos de datos y pulsamos siguiente:

d) Indicamos donde queremos crear el gráfico y pulsamos terminar:

pág. 95


6.

Seleccionamos las columnas columnas A3:A10, A3:A10, G3:G10 y H3:H10 y pulsamos . Seguimos lossiguientes lossiguientespasos: pasos: a) Elegimos el tipo de gráfico y pulsamos siguiente:

pág. 96


b) Comprobamos los datos de origen, que coincidirán con los que hemos seleccionadoy seleccionadoypulsamossiguiente: pulsamossiguiente:

pág. 97


c) Modificamos los títulos, leyenda y tabla de datos y pulsamos siguiente:

d) Indicamos donde queremos crear el gráfico y pulsamos terminar:

pág. 98

E j e rc ic io n º 15 Facturació n


Nombre: 15

Ejercicio

•

Objetivo:

UtilizacióndelasfuncionesBUSCARVySI

•

Enunciado:

•

El siguiente ejercicio consistirá en calcular el total de una factura dependiendo de los precios que tengamos almacenados. Crearemos un libro llamado“Fact llamado “Facturación.x uración.xls” ls”que que contendrá dos hojas.

La primera hoja de ella la llamaremos “Productos” y contendrá una serie de productos en los que se indicará el código, descripción y precio precio por unidad. Tendrá el siguiente aspecto:

La segunda hoja se llamará “Factura”. Escribiremos un código de artículo de la hoja anterior y nos aparecerá su descripción automáticamente en la celda de la derecha. También nos aparecerá el precio unitario. Luego introduciremos la cantidad deseada y Excel nos calculará el total de cada fila, y el total de toda la columna. Aplicaremos el descuento indicado indicado si el pago es al contado. Para Para ello deberemos introducir el tipo de pago “contado” que no debe ser sensible a mayúsculas (da igual si lo escribimos en mayúsculas o minúsculas). Aplicaremos un IVA del 16% y mostraremos el total a pagar. La factura deberá tener el siguiente aspecto: pág. 99


Cadafacturasecompondráde5productos,queseránecesarioindicar obligatoriamente.

•

Solución: Lo primero que haremos será cambiar el nombre a cada hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. La tercera hoja que crea Excel por defecto deberemos borrarla. Ahora introduciremos la tabla indicada en la hoja “Productos”. Introducimos los productos y podemos darle el formato que queramos, siempre que respetemos la posición en la hoja. Tenemos que dar el formato EuroalacolumnaC. En el ejemplo se ha aplicado el Autoformato “Multicolor 2”, desde el menú Formato – Autoformato …. Una vez introducida los productos nos desplazamos a la hoja pág. 100

“Factura” e introducimoslasiguientehoja:

pág. 101


En la figura hemos indicado los campos que debemos rellenar para realizar la factura con sombreado gris y los que se deberán ser calculados con una trama rallada. Deberemos dar el formato fecha la celda B2, el formato % a la celda D4 y el formato Euro a las celdas E7:E11, F7:F14 y F16. Para ello utilizaremos la pestaña Números de Formato-Celdas o los botones de la barra de herramientascorrespondientes. Daremos el aspecto visual que deseemos a la hoja respetando la posición de las celdas. Ahora deberemos introducir las fórmulas adecuadas para que la hoja “Factura”se comportecomoqueremos. Lo primero que tenemos que obtener es la descripción del producto a partir del código. Para ello usaremos la función BUSCARV. Si recordamos, BUSCARV tienen 4 argumentos: •

•

valor_buscado: el valor a comparar matriz_de_comparación: es el conjunto de información donde se pág. 101

•

buscan los datos. indicador_columnas: es el número de columna de matriz_de_comparación desdelacualdebedevolverseelvalorcoincidente.

pág. 102


•

Ordenado: es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto,

devolverá

el

valor

inmediatamente

menor

que

valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error#N/A.

Si necesitamos más datos de esta función u otra, podremos acudir a la ayuda de Excel, la cual nos indicará como usar la función e incluso nos dará ejemplos de su uso. UtilizandoBUSCARVconstruiremoslasiguiente fórmula: C7= BUSCARV(B7;Productos!$A$2:$B$9;2;FALS O)

Con esto le indicamos que busque el código del producto introducido en B7 en la tabla de productos y devuelva el contenido de la columna Descripción de dicha tabla. Al indicar FALSO en ordenado indicamos que la búsqueda debe de ser exacta. Hemos puesto referencias absolutas ya que vamos a utilizar esta celda para copiar la fórmula a C8, C9, C10 y C11, y si utilizamos referencias relativas nos cambiará la matriz_de_comparación con lo que no se realizará correctamente la búsqueda. Una vez copiado el valor de C7 a C8, C9, C10 y C11, tenemos que buscar el valor porunidaddelproducto.Para ellousamosdenuevoBUSCARV: pág. 102

E7 = BUSCARV(B7;Productos!$A$2:$C$9;3;FALSO) Hemos seguido las mismas consideraciones que para C7, pero esta vez el resultado devuelto es el precio unitario. Copiamos el valor de E7 a E8, E9, E10 y E11.

Ahora calculamos el total: F7 =D7*E7 Y copiamos este el valor de la celda F7 a F8, F9, F10 y F11.

pág. 103


Para calcular el Total (celda F12) usaremos la función SUMA, introduciendo: F12 = SUMA(F7:F11) Para calcular el descuento deberemos usar la función condicional SI, que tiene 3 argumento s: •

•

•

prueba_lógica: es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. valor_si_verdadero: es el valor que se devolverá si prueba_lógica es VERDADERO. valor_si_falso:eselvalorquesedevolverásiprueba_lógicaes

FALSO. Ennuestrocasotendremos:

F12 = SI(MAYUSC(B14)="CONTADO";F12*D4;0) Observamos que hemos utilizado como prueba_lógica B14, que es donde escribiremos el tipo de pago, con la función MAYUSC que pasa el valor de B14 a mayúsculas. De esta forma dará igual como escribamos la palabra“contado”.

Nota : Realmente no es necesario el uso de MAYUSC ya que SI no es

sensible a mayúsculas. Si B14 es igual a “CONTADO”, devuelve el valor de F12 (suma total) por el valor de D4 (porcentaje de descuento). Si no es contado el valor es 0 con lo que no aplica descuento alguno. Ya solo nos queda calcular el IVA que será la suma total menos el descuento pág. 103

por el 16% (F13 = (F12-F13)*16%) y el total a pagar que será la suma total menos el descuento más el IVA (F16 =F12-F13+F14). Con esto hemos cumplido los objetivos del ejercicio.

pág. 104

E j e rc ic io n º 16 Control de Cuenta


•

•

•

Nombre: 16

Ejercicio

Objetivo:

Estudiar el formato condicional

Enunciado: El siguiente ejercicio consistirá en llevar un control de entradas y salidas de dinero al estilo de una cuenta corriente. En la columna del Entrada situaremos las entradas de dinero y en la columna del Salida las salidas. La última columna la utilizaremos para llevar el control del saldo, que se calculará sumando al saldo anterior la última posible entrada y restándole la última posible salida de dinero. Si el saldo es negativo deberá de aparecer en color rojo. Sólo se permite un concepto por movimiento. Crearemos un libro llamado “Control de Cuenta ” con una sola hoja llamada “Cuenta”. Para facilitar la visualización y entrada de fórmulas, crearemos espacio

para 10 movimientos.

•

Solución: Lo primero que haremos será cambiar el nombre de la primera hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y pág. 104

elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. Borramos el resto de hojas que crea Excel por defecto deberemos borrarla.

pág. 105


Una vez que hayamos introducido la hoja, indicando el saldo inicial, y el formato Euro a las columnas Entrada, Salida y Saldo, tendremos que ver que fórmula introducimos en la celda E5 y ver si se puede copiar al resto de la columnaE.

Tenemos que introducir una fórmula que compruebe si hay un valor en entrada o en salida y según sea ese valor, realizar la acción adecuada. Expresado lógicamente sería:

Si hay valor en Salida o hay valor en Entrada, entonces sumar Salida y restar Entrada al Saldo anterior. De esta forma nos ahorramos tener que comprobar donde está el valor, si en entrada oensalida.LafórmulaenExcelsería:

E5 = SI(O(C5<>"";D5<>"");E4+C5-D5), donde hemos utilizado el operado lógico O. Pero esta formula tiene varios problemas: 1. 2.

Nocumpleelrequisitodequesólopuedehabero salidaoentrada. Da error si no introducimos ni entrada ni salida, obligándonos a rellenar todas

las filas. Tenemos que modificar nuestra fórmula de partida:

Si hay valor en Salida o hay valor en Entrada y no hay valor en entrada y salida, entonces sumar Salida y restar Entrada al Saldo anterior. La traducción de esta fórmula en Excel sería: E5 = SI(Y(O(C5<>"";D5<>"");NO(Y(C5<>"";D5<>""))); E4+C5D5;"") pág. 105

Esta fórmula se comporta mucho mejor que la anterior, pero presenta otro problema: No distingue si se han introducido valores tanto en entrada como en salida o si no se han introducido ningún valor. En ambos casos el operador_lógico del SI es FALSO. Podemos seguir realizando uniones lógicas para solventar este error, pero ese no es el objetivo de este curso. Nosotros proponemos la siguiente fórmula:

pág. 106


E5 = SI(Y(C5<>"";D5<>"");"Error";SI(Y(C5="";D5="");"";E4+C5-D5)) Quesecomportadelsiguientemodo:

Si Salida y entrada tienen valor, entonces error, sino si entrada y salida no tienen valor no hacer nada, sino sumar Salida y restar Entrada al Saldo anterior Copiando esta fórmula al resto de celdas de la columna E tendremos resulto el ejercicio. Hayotraformadellegaralmismoresultadoutilizandolafunción CONTAR: E5 =SI(CONTAR(C5;D5)=1;E4+C5D5;SI(CONTAR(C5;D5)=2;”Fallo”;””) Deestaformallegamosalmismoresultado. Ahorasólonosquedaponer enrojoel saldocuandoseanegativo.Paraello tenemos dosopciones: 1.

Utilizar el formato de celda e introducir el formato: #.##00,0 €;[ROJO]-#.##0,00 €

2.

Utilizar el formato condicional, para ello seleccionaremos la/s celda/s a la que queremos aplicarlo y en el menú Formato, Formato Condicional… y apareceráelsiguientediálogo:

pág. 106

pág. 107

E je rc ic io n º 17 CálculodelN.I.F.


Nombre: 17

Ejercicio

•

Objetivo:

UtilizacióndelasfuncionesEXTRAEy RESIDUO

•

Enunciado:

•

El siguiente ejercicio consistirá en Crear una hoja de cálculo que calcule la letra de un DNI dado. Vamos a explicar como se calcula la letra de un DNI. Para ello deberemos dividir el DNI entre 23 y quedarnos con el resto. Seguidamente deberemos mirar en la siguiente tabla para obtener la letra que forma parte del NIF:

Resto

0

1

2

3

4

5

6

7

8

9

10

11

Letra

T

R

W

A

G

M

Y

F

P

D

X

B

Resto

12

13

14

15

16

17

18

19

20

21

22

Letra

N

J

Z

S

Q

V

H

L

C

K

E

Crearemos un libro llamado “Cálculo NIF” con una hoja llamada “NIF”, quedando de la siguiente forma:

•

Solución: Lo primero que haremos será cambiar el nombre de la primera hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y pág. 107

elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. Borramos el resto de hojas que crea Excel por defecto.

pág. 108


Ahora tenemos que buscar que funciones debemos utilizar para obtener la letra en la celda B4. Excel nos proporciona una función llamada EXTRAE que devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique. Tiene los argumentos: •

•

Texto: Es la cadena de texto que contiene los caracteres que desea extraer. Posición_inicial: Es la posición del primer carácter que desea extraer del argumento texto. La posición_inicial del primer carácter de texto es 1 y así sucesivamente. o

o

o

•

siguientes

Si posición_inicial es mayor que la longitud de texto, EXTRAE devuelve "" (texto vacío). Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto. Si posición_inicial es menor que 1, EXTRAE devuelve el valor de error #¡VALOR!

Núm_de_caracteres: especifica el número de caracteres de texto que se han de devolver. Si núm_de_caracteres es negativo, EXTRAE devuelve el valor de error #¡VALOR!

También tenemos la función RESIDUO que nos da el resto de una división, con los argumentos: •

•

Número: es el número que desea dividir y cuyo residuo o resto desea obtener. Núm_divisor: es el número por el cual desea dividir número. Si núm_divisor es 0, RESIDUO devuelve el valor de error #¡DIV/0!

Conestasfuncionespodemosconstruirnuestrafórmulaparacalcularel pág. 108

NIF: B4 = EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";1+RESIDUO(B3;23); 1) Analizando la fórmula observamos que se va a extraer de la cadena que coincide con la tabla que dimos al principio del ejercicio la letra que está en la posición del valor del resto de la división del contenido de la celda B3 (DNI) entre23sumándole

pág. 109


1. Esta suma es debido a que para la función extrae, la posición de la primera letra es 1.

Pero con esta formula el ejercicio no es del todo correcto, ya que si no introducimos ningún DNI, la Excel nos muestra en la celda B4 “T”. Para evitar esto deberemos usar la función condicional SI: B4 = SI(B3<>"";EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B3;23);1); "") De esta forma, si B3 no contiene ningún valor, en B4 no se mostrará nada. Con esto estaría el ejercicio resuelto, pero todavía podemos mejorar el resultado si mostramos todo el NIF en lugar de sólo la letra. Para ello deberemos concatenar el resultado al DNI mediante “&” que nos sirve para concatenar cadenas. La fórmula quedaría: B4 SI(B3<>"";

=

B3 &“–“& EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";1+RESIDUO(B3;2 3);1); "") Conestecambiodamosporterminadoelejercicio.

pág. 109

E j e rc ic io n º 18 VentasComercial


•

•

•

Nombre: 18

Ejercicio

Objetivo:

Utilización de funciones comunes

Enunciado: El siguiente ejercicio consiste en controlar las ventas por comercial de una empresa de alimentación. 1.

Crearemos un libro llamado “Ventas Comercial” con 2 hojas de datos:

“Comerciales” y

“Gráficos”.

En

la

hoja

“Comerciales”

construiremos la siguiente tablaconlosdatosindicados:

2.

Introducir la fórmula adecuada en la columna B para que se presente el número de ventas de cada comercial.

3.

Introducir la fórmula adecuada en la columna C para que se presente el total de ventas de cada comercial.

pág. 110

4.

Crear un gráfico en la hoja “Gráficos” que presente las ventas de cada comercial.

pág. 111


•

Solución: 1.

Lo primero que haremos será cambiar el nombre a cada hoja, para ello pinchamos con el botón derecho de ratón sobre la pestaña de la hoja y elegimos “Cambiar nombre”, introduciendo el nombre indicado a cada hoja. La tercera hoja que crea Excel por defecto deberemos borrarla. Como la segunda hoja es para contener el gráfico, podemos crearla cuando introduzcamos el gráfico. A continuación introduciremos la hoja “Comerciales” como se indica, dando formatoEuroalacolumnaH ylaColumnaJ.

2.

Tenemos que contar el número de ventas de cada comercial. Para ello utilizamos en la celda la B2 la función CONTAR.SI que cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. Esta función tiene los siguiente argumentos: Rango: es el rango dentro del cual desea contar el número de celdas que no están en blanco. Criterio: es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. •

•

Por ejemplo, para la celda B2 tenemos que contar en el rango E2:E20 las celdas que sonigualesaA2:

B2 = CONTAR.SI(E2:E20;A2). Para B3:

B3 = CONTAR.SI(E2:E20;A3). Observamos que podemos copiar la fórmula de la celda B2 al resto de la pág. 111

columna B, pero utilizando adecuadamente las referencias absolutas y relativas. Deberemos de usar referencias absolutas para el rango y relativas para el criterio: B2 = CONTAR.SI($E$2:$E$20;A2). Copiando el valor de la celda B2 al resto de la columna B obtendremos los siguientes resultados:

pág. 112


3.

Para calcular los valores de la columna C, deberemos usar la función de suma condicional SUMAR.SI que suma las celdas en el rango que coinciden con el argumento criterio. Tiene los siguientes argumentos: •

•

•

Rango: es el rango de celdas que desea evaluar. Criterio: es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Rango_suma: son las celdas que se van a sumar. Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio. Si rango_suma se omite, se suman las celdas contenidas en el argumento rango.

Aplicando la tendremos:

fórmula

anterior

C2 = SUMAR.SI(E2:E20;A2;J2:J20) C3 = SUMAR.SI(E2:E20;A3;J2;J20) Podemos introducir la fórmula en cada casilla de de la columna C o copiar el pág. 112

valor de C2 al resto de la columna, modificando su fórmula con las referencias absolutas adecuadas,quedando:

C2 = SUMAR.SI($E$2:$E$20;A2;$J$2:$J$20) El resultado deseado será el siguiente:

pág. 113


Not a: De la forma que hemos configurado nuestra hoja de cálculo no podremos

añadir nuevos datos para los vendedores, la forma de solucionarlo será cambiar el final de los rangos (ahora está en la fila 20) y colocar el número 65536, que es la última fina que introduce Excel en su hojas. Las fórmulas quedan: B2 = CONTAR.SI($E$2:$E$65536;A2) C2 = SUMAR.SI($E$2:$E$65536;A2;$J$2:$J$65536) 4.

Para crear el gráfico pedido deberemos seleccionar las columnas de datos que queremos que aparezcan. En nuestro caso seleccionaremos A2:A11 y C2:C11 y a continuación pulsamos .

pág. 113


Después elegimos el tipo de gráfico:

pág. 114


Comprobamosqueelrangodedatosescorrecto:

pág. 115


Cambiamoseltítuloylaleyenda:

pág. 116


Yporúltimo seleccionamoscomodestinodelgráficola hoja“Gráficos”:

El resultado será el siguiente:

pág. 117

E j e rc ic io n º 19 Cambio de Divisas


Nombre: 19

Ejercicio

•

Objetivo:

UtilizacióndelafuncionesBUSCARV

•

Enunciado:

•

El siguiente ejercicio consistirá en calcular el cambio de moneda dado el valor de la divisa enun díadeterminado. 1.

Crearemos un libro llamado “Divisas” que contendrá tres hojas. La primera hoja la llamaremos “Divisas” y contendrá el cambio de la divisa en un determinado día. La segunda se llamará “Cambio Dólar” y la tercera “Cambio Divisas” Copia los siguientes datos en la hoja “Divisas”:

pág. 118


2.

Enlahojadellibro“CambioDólar”introduciremoslossiguientesdatos:

En la columna“Cambio”deberáaparecerelcambiodeldólareneldíaindicado y en la columna “Total” deberá aparecer el total en Euros. Por último, “Importe total” nosmostraráeltotaldeEuroscambiados. 3.

Añadir una divisa más, la Libra. Para ello cambiaremos la hoja“Divisas” para que tengaelaspecto:

pág. 119


4.

Introducir en la hoja “Cambio Divisas” los siguientes datos:

En la columna “Cambio” deberá aparecer el valor del cambio de la moneda en el día indicado. Se deberán calcular los totales.

•

Solución: 1.

Creamos un nuevo libro y cambiamos el nombre a las hojas con lo indicado en el enunciado del ejercicio. Aplicamos el formato Euro de B2 a B18. Aplicaremos el formato fecha a la columna A. Deberemos crear un formato personalizado,yaqueel indicado no viene por defecto en Excel. Para ello Nos vamos a Formato – Celdas… - Números y elegimos personalizado, introduciendo“dd/mm/aaaa”.

2.

Introducimos la hoja “Cambio Dólar” y aplicamos el formato adecuado para que quede como indica el ejemplo (Podemos cambiar el formato teniendo en cuenta de que tenemos que respetar la posición de las celdas). Aplicamos el formato Euro a C4:C8, D4:D8 y D10. También aplicamos el formato Fecha del apartado 1 a la columnaA. Ahora debemos buscar el valor del cambio del dólar en una determinada fecha. Para ello usaremos la función BUSCARV. Si recordamos, BUSCARV tienen 4 argumentos: pág. 120

•

•

valor_buscado: el valor a comparar matriz_de_comparación: es el conjunto de información donde se buscan los datos.

pág. 121


•

•

indicador_columnas: es el número de columna de matriz_de_comparación desde la cual debe devolverse el valor coincidente. Ordenado: es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto,

devolverá

el

valor

inmediatamente

menor

que

valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error#N/A. La fórmula que deberemos introducir será:

C4 = BUSCARV(A4;Divisas!$A$2:$B$18;2;FALSO) Hemos tenido que hacer referencia a otra hoja del libro y hemos usado referencias absolutas para poder copiar la fórmula al resto de la columna 4 en lugar de volverla a escribir en cada celda. Para calcular el total de euros sólo tendremos que realizar una multiplicación: D4 = B4*C4 Y copiamos la fórmula al resto de la columna D. Por último, para calcular el total podemos usar la autosuma. Seleccionamos de D4 a D10 y pulsamossobreel botón . Tendremos:

D10 = SUMA(D4:D9)

pág. 121


El resultado final será:

3.

Para realizar esta punto sólo deberemos introducir la nueva columna Libra y darle el formato de Euro a los valores.

4.

En este punto deberemos tener en cuenta el tipo de moneda para buscar el cambio. Lafórmulaquedeberemosusaresunpocomáscomplicadaqueenelpunto2. C4 =SI(MAYUSC(B4)=Divisas!$B$1;BUSCARV(A4;Divisas!$A$2:$B$18;2;FA LSO); SI(MAYUSC(B4)=Divisas!$C$1;BUSCARV(A4;Divisas!$A$2:$C$18;3;FAL SO);0)) Vayamosporpartes. Utilizamos MAYUSC para no tener que la columna “moneda” no sea sensible a mayúsculas(Noes necesarioyaqueSIno essensibleamayúsculas). Loquehacelafórmulaes: 1.

Si B4 es igual a DÓLAR, entonces devuelve el valor del cambio del dólar en el día especificado. pág. 122

2.

Si no se cumple 1, entonces comprobamos que la moneda sea LIBRA. Si es así devolvemos el valor del cambio de la libra en el día especificado.

3.

Sinosecumple,entoncesdevolvemos0.

Copiaremosestafórmulaalrestodelacolumna D.

pág. 123


Para los totales sólo tendremos que realizar una multiplicación: E4 = C4*D4 YcopiamosalrestodelacolumnaE.

Para la suma total, utilizamos la autosuma seleccionando de E4 a E9 y pulsando sobre , quedando: E9 = SUMA(E4:E8) La hoja mostrará el aspecto:

pág. 123

E j e r c i c i o n º 20 Fútbol


•

•

•

Nombre: 20

Ejercicio

Objetivo:

Utilización de funciones anidadas

Enunciado:

El siguiente ejercicio consistirá en calcular los puntos que consigue España en función del resultado. 1.

Crearemos un libro llamado “Fútbol” que contendrá dos hojas. La primera hoja la llamaremos “Resultados” y contendrá los resultados y los puntos obtenidos. La segunda se llamará “Puntos” y contendrá el los puntos que se consiguen por victoria,empatey derrota.Introducirlasiguientetabla enlahoja”Puntos”.Intentar darelmismoaspecto.

2.

Introducir en la hoja “Resultados” la siguiente tabla, sin introducir los valores en la columna Puntos, que deberemos calcular teniendo en cuenta los valores de victoria, empate y derrota de la hoja “Puntos”:

pág. 124


•

Solución:

1.

Creamos un nuevo libro y cambiamos el nombre a las hojas con lo indicado en el enunciado del ejercicio y borramos la tercera hoja. Introducimos los datos indicados. Para darle formato hemos utilizado la función “Autoformato…”

del

menú

“Formato”

y

elegimos

el

formato predefinido “Multicolor 1”. Después seleccionamos toda la tabla y pulsamos sobre el botó de la barra de herramientas, con lo que centraremos el texto dentro de las celdas. 2.

Introducimos los datos indicados en la hoja “Resultados” Para conseguir el aspecto indicado tendremos que combinar y centrar las celdas A1, B1 y C1 y las celdas A2 y B2. También pondremos en negrita el valor de estas celdas. También deberemos centrar las celdas A3:C9 y la fila 3 ponerle de fondo el color granate, el color de las letras en blanco y en negrita. Ahora deberemos introducir la fórmula adecuada en la columna puntos. El comportamiento que tiene que tener esta celda es: 1.

Sielresultadodeloponentees menorqueeldeEspaña,entonces introducimos enpuntos“Victoria”.

2.

Sino, si el resultado del oponente es mayor que el de España, entonces introducimos“Derrota”.

3.

Sino, introducimos “Empate”.

Comovemosesunacondición,porlotantotendremosqueutilizarlafunción SI. C4 = SI(B4>A4;Puntos!$A$3;SI(A4>B4;Puntos!$C$3;Puntos!$B$3)) Not pág. 125

a: Podemos ahorrarnos tener que usar referencias a celdas utilizando lo que

Excel denomina nombre. Para ello seleccionamos la celda a la que queremos poner etiqueta y nos vamos al menú Insertar – Nombre – Definir, y definimos el nombre que queramos dar a la celda.

También podemos utilizar la zona de la barra de herramientas que está a la izquierda del campo donde se introduce el valor de la/s celda/s seleccionadas. Para ello seleccionamos la celda, escribimos el nombre en esta áreaypulsamosretorno.

pág. 126


De esta forma podríamos escribir la fórmula de la siguiente manera: C4 = SI(B4>A4;Victoria;SI(A4>B4;Derrota;Empate))

Como vemos esta función se comporta con la lógica indicada. Hemos utilizado las referencias absolutas necesarias a la hoja “Puntos” para poder copiarla de la celda C4 al resto de la columna C. Este es un planteamiento simple que da buen resultado si todos los datos son correctos, pero ¿qué pasaría si no hay datos o hay algún datos incorrecto? El resultadoqueobtendríamossería:

pág. 126

Vemos que nuestra hoja de cálculo no se comporta de forma adecuada. Una de las formas para “arreglar” esto es utilizar la función ESNUMERO que tiene un

pág. 127


argumento y devuelve VERDADERO si el argumento es un número y FALSO si el argumentonoesunnúmero.

Por lo tanto cambiamos el valor de la celda C4 a: C4 = SI(Y(ESNUMERO(A4);ESNUMERO(B4)); SI(B4>A4;Puntos!$A$3;SI(A4>B4;Puntos!$C$3;Puntos!$B$3));"") Con lo que si el valor de A o el valor de B no es un número, la función no devolverá nada (bueno, devolverá una cadena vacía). Copiaremos el valor de C4 al resto de la columna y con esto corregiremos el mal funcionamiento comentado. Esto que hemos realizado se llama anidar funciones y todavía se podría refinar más la fórmula eliminando otros comportamientos incorrectos anidando más funciones.

pág. 127

E je rc ic io n º 21 P.N.B.Países


•

•

•

Nombre:

Ejercicio21

Objetivo: gráficos.

Porcentajes y

Enunciado:

El siguiente ejercicio consistirá en calcular el PNB de un país conociendo el del año anterior y el crecimiento económico. También crearemos un gráfico circular 3D que nos muestre el %delPNBdeunpaíscomparadoconlasumadelPNBdetodoslospaísesenelaño2002.

1.

Crearemos un libro llamado “PNB Países” que contendrá una hoja a la que llamaremos “PNB” y otra a la que llamaremos “Crecimiento”. Introducir la siguiente tabla en la hoja ”PNB”. Cambiar el formato según se desee.La columna C tienequetenerunformatode%.

pág. 128


2.

Introducirla siguientetabla en la hoja“Crecimiento”.Cambiarelformato según se desee.LacolumnaBtieneque tenerunformatode%.

3.

Introducir las fórmulas adecuadas en la hoja“PNB”para que introduzca la subida segúnloindicadoenlahoja“Crecimiento”ycalcularelPNBdel2002.

4.

Crear un gráfico circular 3D que nos muestre el % del PNB de un país comparado conlasumadelPNB detodoslospaísesenelaño2002.

•

Solución: 1.

Creamos un nuevo libro y cambiamos el nombre a las hojas con lo indicado en el enunciado del ejercicio y borramos el resto de las hojas. Introducimos los datos indicados y le damos el formato que deseemos.

Ahora aplicamos el formato % a la columna C. No podemos usar el botón de la barra de herramientas, ya que aplica un formato sin decimales. Tendremos que ir al menú Formato – Celdas… y aplicar el formato porcentajecon1posicióndecimal. pág. 129

2.

Introducimos los datos indicados y le damos el formato que deseemos. Tenemos que realizar la misma consideración al formato % de punto anterior.

pág. 130


3.

Lo primero que tenemos que hacer es buscar cual es la subida en la hoja “Crecimiento” según el valor de la columna A. Excel tiene una función queseajusta aestadefinición:BUSCARV.Tienelossiguientesargumentos. •

•

•

•

valor_buscado: el valor a comparar matriz_de_comparación: es el conjunto de información donde se buscan los datos. indicador_columnas: es el número de columna de matriz_de_comparación desdelacualdebedevolverseelvalorcoincidente. Ordenado: es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto,

devolverá

el

valor

inmediatamente

menor

que

valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error#N/A.

Si necesitamos más datos de esta función u otra, podremos acudir a la ayuda de Excel, la cual nos indicará como usar la función e incluso nos dará ejemplos de su uso. El valor para C2 sería: C2 = BUSCARV(A2;Crecimiento!$A$2:$B$12;2;0) Se ha de observar el uso de las referencias a otra hoja y el uso de referencias absolutas para que podamos copiar la fórmula de la celda C2 al resto dela columna C. Ahora nos queda calcular la columna D. Para ello tendremos en cuenta que el nuevo PNBserá: pág. 130

PNB 2002 = PNB 2001 + ((PNB 2001 * Crecimiento)/100) En este caso, como hemos utilizado el formato % para la subida, Excel aplicará la fórmula del porcentaje automáticamente. Entonces la fórmula se simplificará que quedará: PNB 2002 = PNB 2001 + PNB 2001 * Crecimiento(%)

pág. 131


Por tanto: D2 = B2+B2*C2

Copiamos el la celda D2 al resto de la columna D y obtendremos el resultado buscado.

4.

Si leemos el enunciado parece que tenemos que calcular el total de PNB del 2002 de los 11 países y después calcular el porcentaje que le corresponde a cada país para, a continuación, crear el gráfico con estos datos. Pero esto no es necesario en Excel, ya que al componer el gráfico realizará estas operaciones pornosotros.

Lo primero que tenemos que hacer es seleccionar la columna A y la columna D y pulsar sobre el botón y seguir el asistente:

pág. 131


a) Seleccionamos el tipo de gráfico, en nuestro caso uno circular 3D.

pág. 132


b) Comprobamosqueelrangodedatosescorrecto.

pág. 133


c) Modificamos el “Rótulo de datos” para que nos muestre el porcentaje. Seleccionamos la opción “Mostrar rótulo y porcentaje”. De esta

forma se presentará en el gráfico el nombre de cada país con el porcentaje en el total del PNB.

d) Situamoselgráficoenuna hojanuevallamada“GráficoPNB2002”.

pág. 134


e) Vamos a cambiar la inclinación de gráfico. Para ello pulsaremos sobre “Vista 3D…” del menú “Gráfico” y nos aparecerá un diálogo donde podemos cambiar la elevación y el giro. Introduciremos una elevaciónde35.

El resultado obtenido será similar al siguiente:

pág. 135

E j e r c i c i o n º 22 Selectividad


•

•

•

Nombre: 22

Ejercicio

Objetivo:

Realizar un resumen comparativo de los resultados de selectividad en las asignaturasdeFísicayQuímica.

Enunciado: Se dispone de dos ficheros texto que contienen los resultados de selectividad de los alumnos que han cursado las asignaturas de “Física” y de “Química” del curso académico 2001-02. Realizar las operaciones: -

siguientes

Cargar los datos de las asignaturas de “Física” y de “Química” a hojas de cálculo diferentes de un mismo libro de trabajo. El nombre de las hojas será “Física” y “Química”, respectivamente.

-

Calcular para cada alumno, en ambas asignaturas, su nota alfanumérica (Suspenso, Suficiente,NotableoSobresaliente)ysiesapto(SíoNo).

-

En una nueva hoja de cálculo llamada “Resumen” se deberá realizar una tabla que contenga los siguientes datos por asignatura: Materia Nº de Presentados Nº de Aprobados Nº de Suspensos Nº de Suficientes Nº de Notables pág. 136

Nº de Sobresalientes Nº de Aptos NºdenoAptos

Calcular los totales (tendiendo en cuenta ambas asignaturas). - Realizar una tabla comparativa entre ambas asignaturas con los siguientes datos:

pág. 137


Asignatura dónde hay mayor nº de Aprobados.

Asignatura dónde hay mayor

nº de Suspensos. Asignatura dónde hay mayor nº de Suficientes. Asignatura dónde hay mayor nº de Notables. Asignatura dónde hay mayor nº de Sobresalientes. -

Realizar una tabla por asignatura considerando la siguiente información: Nota máxima. Notamínima. Notamedia. Notamediana. Notamás común.

-

Realizar un gráfico de líneas que muestre una comparativa del nº de presentados, nº de aprobados, nº de suspensos, nº de suficientes, nº de notables, nº de sobresalientes, nº de aptos y nº de no aptos entre ambas asignaturas.

Losficherosquecontienenlosresultadosson: -

notas_FISICA.txt

-

notas_QUIMICA.txt

pág. 137

A continuación se presenta el aspecto y resultado que debe obtener el alumno una vez concluido el ejercicio.

pág. 138


pág. 138


pág. 139


pág. 140


•

Solución: La solución de este ejercicio se deja para el alumno.

pág. 141

E j e r c i c i o n º 23 Elecciones Sindicales


•

•

•

Nombre: 23

Ejercicio

Objetivo: Realizar unos cuadros comparativos que muestren los resultados de las elecciones sindicales celebradas en la Universidad en el año 1998 y en el año 2003. Enunciado:

Una vez celebradas las elecciones sindicales 2003 en la Universidad se han obtenido los resultados que a continuación se muestran por órgano de representación. También se muestra la comparativa respecto a las elecciones sindicales del 98. Resultado s: - Junta de Personal del PDI (21

representantes): CC.OO.: 6 delegados (igual que en 1998) CSI–CSIF: 10 delegados(1 menos que en 1998) UGT:5delegados(1másqueen1998) - Juntade Personal del PASFuncionario (11 miembros): CC.OO.:3delegados(1menos

queen1998) CGT: 3 delegados (no tuvo representación en 1998) CSI – CSIF: 3 delegados (igual que en 1998) GIPI:1delegado(notuvorepresentaciónen1998) UGT:1delegado(3menosqueen1998) -

Comitéde EmpresadelPASLaboral (13 pág. 142

miembros): CC.OO.:5delegados(2

menosqueen1998) CGT: 6 delegados (no tuvo representación en 1998) CSI–CSIF:1delegados(igualqueen1998) UGT:1delegado(4menosqueen1998)

pág. 143


En base a estos datos, crear un libro de trabajo que contenga en una hoja de cálculo estos datos y, en otra hoja de cálculo los cuadros comparativos, por órganos de representación, del número de delegados en 1998 y en estas elecciones. El resultado deberá ajustarse al siguiente formato: Æ

Hoja

“Datos”:

Elecciones Sindicales Junta de Personal del PDI - Personal Docente e Investigador Año

CCOO

CSIF

UGT

2003

6

10

5

1998

6

11

4

Junta de Personal del PAS Funcionario - Personal de Administración y Servicios Año

CC.OO

CGT

CSIF

GIPI

UGT

2003

3

3

3

1

1

1998

4

3

4

Comité de Empresa del PAS Laboral - Personal de Administración y Servicios Año

CC.OO

CGT

CSIF

UGT

2003

5

6

1

1

pág. 143

1998

7

1

5

pág. 144


Æ

Hoja“Cuadros”:

pág. 144


•

Solución: La solución de este ejercicio se deja para el alumno.

pág. 145

E j e r c i c i o n º 24 Eleccionesa Claustro


•

•

•

Nombre: 24

Ejercicio

Objetivo: Realizar unos cuadros estadísticos con sus correspondientes representaciones gráficas de la participación en las elecciones a claustro universitario celebradas en el 2002. Enunciado:

En el 2002 tuvieron lugar las elecciones a Rector y a Claustro Universitario. A continuación se muestran los datos de participación, así como el nº de miembros de la comunidad universitaria llamados a las urnas, según los distintos grupos electorales: -

Grupo A: Censo:659 Participación en las elecciones a Rector: 537

Participación en las elecciones a Claustro: 579 -

GrupoB: Censo: 126 Participación en las elecciones a Rector: 93


GrupoC: Censo:404 Participación en las elecciones a Rector: 148


GrupoD: Censo:623 Participación en las elecciones a Rector: 523

Participación en las elecciones a Claustro: 543 pág. 146

-

GrupoE: Censo:659 Participación en las elecciones a Rector: 537

Participación en las elecciones a Claustro: 579

pág. 147


Realizarlassiguientesoperaciones: -

Crear una tabla que recoja la participación en las elecciones a Claustro Universitario, indicando el porcentaje de participación y el porcentaje de abstención para cada uno de los grupos electorales.

-

Realizar un gráfico de columnas 3D que muestre visualmente el porcentaje de participación por grupos electorales, y otro respecto a la abstención.

EL ECCIONE S A CLA USTRO 23/05/02

ESTAD ÍSTI CA DE PARTI CI PACI ÓN EN L AS EL ECCI ONES Grupos El ectorales Grupo Electoral

Censo

Participación

Nºde Int egran tes

Nºde Part icip antes

Porcentaje de Participación

Porcentaje de Abs tención

A

87,86%

12,14%

B

66,67%

33,33%

C

30,20%

69,80%

D

87,16%

12,84%

9,13%

90,87%

15,15%

84,85%

17.493

E Totales:

19.305

Porcentaje de Participación por Grupos Electorales

Porcentaje de Abstención por Grupos Electorales

100%

100%

90%

90%

80%

80%

70%

70%

60%

60%

50%

50%

40%

40%

30%

30%

20%

20%

10%

10%

0%

0%

A

B

C

D

E

A

B

C

D

E

pág. 147

-

Seguidamente, crear una tabla y un gráfico de barras 3D que muestre una comparativa en la participación en las elecciones a rector y en las elecciones a claustro universitario.

pág. 148

EL ECCI ONES A CL AU STRO 23/05/02

COM PARATI VA D E PARTI CIPACIÓN Gr upos El ectorales Grupo Electoral

Censo Nºde Int egran tes

Participación en las Elecciones a Rector

Participación en las Elecciones a Claustro

Diferencia de Participación

A B C D E Totales:

17.493

-2.110

19.305

-2.083

Comparativa de Participación en las Elecciones

A

B

C

D

E 0%

10%

20%

30%

40%

Elecciones a Rector

50%

60%

70%

80%

90%

100%

Elecciones a Claustro

El grupo E (grupo electoral correspondiente a los Estudiantes) se desglosa por centros / circunscripciones electorales. Los datos de participación del grupo E por centro

/

circunscripciónelectoralsonlossiguientes: pág. 149

-

Facultad de Veterinaria:

Censo:1.511 Participación en las elecciones a Rector: 185 Participación en las elecciones a Claustro: 134 -

EscuelaTécnicaSuperiordeIngenierosAgrónomosy Montes: Censo: 1.846 Participación en las elecciones a Rector: 308 Participación en las elecciones a Claustro: 85

-

Facultad de Medicina: Censo: 773 Participación en las elecciones a Rector: 220 Participación en las elecciones a Claustro: 75

-

Facultad de Ciencias: Censo:2.335 Participación en las elecciones a Rector: 645


FacultaddeFilosofíay Letras: Censo: 1.714 Participación en las elecciones a Rector: 401


Facultad de Derecho: Censo: 1.699 Participación en las elecciones a Rector: 549


FacultaddeCienciasdela pág. 150

Educación: Censo: 1.763 Participación en las elecciones a Rector: 407


Escuela Politécnica Superior:

Censo:

2.196

pág. 151

Participación en las elecciones a Rector: 447 Participación en las elecciones a Claustro: 241 -

Escuela Universitaria Politécnica de Belmez: Censo: 427 Participación en las elecciones a Rector: 178 Participación en las elecciones a Claustro: 113

-

Escuela Universitaria de Enfermería: Censo: 485 Participación en las elecciones a Rector: 96 Participación en las elecciones a Claustro: 30

-

EscuelaUniversitariadeRelaciones Laborales: Censo: 1.010 Participación en las elecciones a Rector: 211 Participación en las elecciones a Claustro: 33

-

Colegio Mayor“NuestraSeñoradelaAsunción”–Tercer

Ciclo: Censo: 1.734 Participación en las elecciones a Rector: 60 Participación en las elecciones a Claustro: 33 Realizar el mismo proceso anterior, pero considerando únicamente las distintas circunscripcioneselectoralesdelgrupoE.Estoes: -

Crear una tabla que recoja la participación en las elecciones a Claustro Universitario, indicando el porcentaje de participación y el porcentaje de abstención para cada una de las circunscripciones electorales del grupo E.

-

Realizar un gráfico de columnas 3D que muestre visualmente el pág. 152

porcentaje de participación. -

Crear una tabla y un gráfico de columnas que muestre una comparativa en la participación en las elecciones a rector y en las de claustro universitario.

pág. 153

ELECCIONES A CLAUSTRO 23/05/02

ESTADÍSTICA DE PARTICIPACIÓN EN LAS ELECCIONES Grupo "E" desglosado por Centros Centro /

Censo

Participación

Porcentaje

Porcentaje

Nº de Integrantes

Nº de Participantes

de Participación

de Abstención

Facultad de Veterinaria

1.511

8,87%

91,13%

E.T.S.I.A.M.

1.846

4,60%

95,40%

9,70%

90,30%

Facultad de Medicina Facultad de Ciencias

2.335

14,00%

86,00%

Faculta de Filosofía y Letras

1.714

12,72%

87,28%

Faculta de Derecho

1.699

14,24%

85,76%

Facultad de Ciencias de la Educación

1.763

3,74%

96,26%

Escuela Politécnica Superior

2.196

10,97%

89,03%

26,46%

73,54%

6,19%

93,81%

Escuela Universitaria Politécnica de Belmez Escuela Universitaria de Enfermería Escuela Universitaria de Relaciones Laborales

1.010

3,27%

96,73%

Tercer Ciclo

1.734

1,90%

98,10%

17.493

9,13%

90,87%

Totales del Grupo "E":

Porcentaje de Participación del Grupo "E" desglosado por Centros 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0%

pág. 154

Excel x Pinter Medio

Recommend Documents