ExcelXPIntermedio

Ingeniería de Software I

•

SIN SIGLA

Josefina Bordiga

30/7/2023

¡Este material tiene más páginas!

Entonces, ¿te gustó este material?

Ayude a animar a otros estudiantes a mejorar el contenido

¿Te gustó este material? ¡Compartir! 🧡

Ingeniería de Software I

30.156 Materiales compartidos

Descarga la aplicación para disfrutar aún más

Lea materiales sin conexión, sin usar Internet. Además de muchas otras características!

Vista previa del material en texto

Servicio de Informática

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

Universidad de Córdoba
Servicio de Informática
Noviembre 2004

Servicio de Informática
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 .............................................................. 7
5. Formato de celdas: formato de los datos ............................................................. 9
6. Referencia a los datos ........................................................................................ 15
7. Utilización de 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 .............................................................................................. 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
C O N T E N I D O

Índice de Ejercicios

pág. 1
Servicio de Informática
• Nombre: - - - - -
• Objetivo: Recordar el entorno de trabajo de Microsoft Excel XP.
• Enunciado:

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

- Maximizar Microsoft Excel.
- Maximizar el libro de trabajo que aparece por defecto.
- Ocultar la barra de estado.
- Ocultar la barra de fórmulas.
- Ocultar todas las barras de herramientas que haya en pantalla.
- Mostrar las siguientes barras de herramientas: “Estándar”, “Formato” y “Dibujo”.
- Situar la barra de menú por debajo de las barras de herramientas.
- Mostrar la barra de fórmulas.
- Mostrar la barra de estado.
- Ocultar la barra de herramientas de “Dibujo”.
- Escribir “Ejercicio 1” en la celda A1 del área de trabajo.
- Minimizar el libro de trabajo actual.
- Salir de Microsoft Excel, sin grabar los cambios efectuados.
E j e r c i c i o n º 1

La pantalla de Microsoft Excel XP

pág. 2
Servicio de Informática
• Nombre: - - - - -
• Objetivo: Familiarizarse con los diferentes tipos de ayuda de los que dispone
Microsoft Excel.
• 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, pero sólo lo puedan modificar aquellos que conozcan la contraseña.

E j e r c i c i o n º 2

La ayuda de Excel

pág. 3
Servicio de Informática
• 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 / Con 5 hojas)
Ejemplo2.xls (Autor: UCO / Asunto: Curso / Con 5 hojas)
Ejemplo3.xls (Autor: UCO / Asunto: Curso / Con 5 hojas)
Balances Económicos
Contabilidad
Cuentas Anuales.xls (Autor: UCO / Asunto: Gestión Económica / Con 10 hojas)
Ventas.xls (Autor: UCO / Asunto: Gestión Económica / Con 10 hojas)
Primer Trimestre.xls (Autor: UCO / Asunto: Gestión Económica / Con 3 hojas / Contraseña de apertura: p1102)
Personal
Natalidad.xls (Autor: Área de Personal / Asunto: Balance / Con 8 hojas)
Sectores.xls (Autor: Área de Personal / Asunto: Balance / Con 8 hojas)
Presupuestos
1998.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)
1999.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)
2000.xls (Autor: UCO / Asunto: Control Presupuestario / Con 9 hojas)
2001.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)
Personal
Ejemplo1.xls (Autor: UCO / Asunto: Curso / Con 5 hojas)
Ejemplo2.xls (Autor: UCO / Asunto: Curso / Con 5 hojas)
Ejemplo3.xls (Autor: UCO / Asunto: Curso / Con 5 hojas)
Balances Económicos
Contabilidad
Cuentas Anuales.xls (Autor: UCO / Asunto: Gestión Económica / Con 10 hojas)
Ventas.xls (Autor: UCO / Asunto: Gestión Económica / Con 10 hojas)
Primer Trimestre.xls (Autor: UCO / Asunto: Gestión Económica / Con 3 hojas / Contraseña de apertura: p1102)
Personal
Natalidad.xls (Autor: Área de Personal / Asunto: Balance / Con 8 hojas)
Sectores.xls (Autor: Área de Personal / Asunto: Balance / Con 8 hojas)
Presupuestos
1998.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)
1999.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)
2000.xls (Autor: UCO / Asunto: Control Presupuestario / Con 9 hojas)
2001.xls (Autor: Retribuciones / Asunto: Control Presupuestario / Con 9 hojas)

Los archivos estarán en blanco, no tendrán ningún tipo de información.

E j e r c i c i o n º 3

Libros de trabajo / Hojas de cálculo

pág. 4
Servicio de Informática
Tratamiento de Libros de Trabajo:

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

- Realizar una búsqueda de todos los archivos cuyo autor sea el “Área de 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 posterior al
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 “Balance Económico”.

Tratamiento de Hojas de Cálculo:

- Abrir el archivo “Cursos.xls”, borrar las dos últimas hojas de cálculo y a las tres
primeras asignarle los nombres “primera”, “segunda” y “tercera”.
- En las tres hojas de cálculo escribir en 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 de los doce meses del añ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”, “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. 5
Servicio de Informática

• 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. Tratamiento de Libros de Trabajo:

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 búsqueda, una vez creada la búsqueda como se ve
en la imagen hemos de agregarla a la lista antes de pulsar buscar ahora.

pág. 6
Servicio de Informática

Hemos recreado la búsqueda por autor, pero también podemos hacerla por 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. 7
Servicio de Informática
• Nombre: Ejercicio 4
• 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
de cálculo que contendrá el trabajo realizado llamada “datos”.

E j e r c i c i o n º 4

Formato de celdas: alineación del texto

pág. 8
Servicio de Informática

• 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 pulsando el
botón de combinar y centrar

pág. 9
Servicio de Informática
• Nombre: Ejercicio 5
• 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
resultado que el proporcionado al alumno.
- 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.

El planteamiento es el siguiente:

E j e r c i c i o n º 5

Formato de celdas: formato de los datos

pág. 10
Servicio de Informática

Grabar el libro de trabajo como “Formatos.xls”, que deberá de constar de una única hoja de
cálculo que contendrá el trabajo realizado llamada “datos”.

• Solución:

Para realizar este ejercicio vamos a ir describiendo los formatos de cada módulo,
detallando en cada caso los aspectos que estimemos más relevantes. Lo primero que
debemos saber es que para darle un formato determinado a una celda hemos de

pág. 11
Servicio de Informática
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 solo nos vamos a detener para comentar como se harían el
formato-3 y el 4, los formatos que habría que darle son los siguientes:

Como vemos el formato será personalizado, y el tipo es #.##0. “miles de pesetas”,
aquí las almohadillas le indican a Excel que escriba el 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 del cero hay un punto que hace que Excel omita las tres últimas
cifras. Cuando tengamos que poner algún texto simplemente lo colocamos entre
comillas. El formato-4 será #.##0.. “millones de pesetas” aquí cabe destacar los dos
puntos consecutivos finales que hacen que se omitan las seis últimas cifras del
número.

pág. 12
Servicio de Informática
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
obtenemos los siguientes resultados:

Formato-1: [Azul]#.#00 “ptas.”;[Rojo]-#.#00 “ptas.”;[Verde] “----“ ;@
Formato-2: [Azul]#.#00 “€.”;[Rojo]-#.#00 “€”;[Verde] “----“ ;@
Formato-3: [Azul]”Entrada:” #.#00,00;[Rojo]”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. 13
Servicio de Informática
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éfonos

Formato-1: Especial, Número de telé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:” 00 “municipio” 000
Formato-3: Personalizada, 0 0 0 0 0
Formato-3: Personalizada, 0 0 . 0 0 0

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.

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. 14
Servicio de Informática

Para el bloque de Horas los formatos a aplicar serán los siguientes:

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 valor es
inferior a cinco.

pág. 15
Servicio de Informática
• Nombre: Ejercicio 6
• 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
columnas y copiarla al resto.

2. Referencias absolutas (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 calcular
los totales.

E j e r c i c i o n º 6

Referencias a los datos

pág. 16
Servicio de Informática
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
copiarla al resto.

3. Utilización de Nombres (hoja: Nombres)

El dar un nombre a una celda o conjunto de celdas disminuye el error al trabajar con
referencias relativas/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. 17
Servicio de Informática
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 ambas, para que este tipo de referencia surta efecto.

pág. 18
Servicio de Informática

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. 19
Servicio de Informática

Seguidamente daremos el nombre que deseemos a la celda. Una vez hecho esto,
cuando queramos usar el valor de esa celda solo tendremos que dar su 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 la siguiente:

‘[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. 20
Servicio de Informática
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.

Hemos de hacer lo mismo para todos los precios de cada mes.

pág. 21
Servicio de Informática
• Nombre: Ejercicio 7
• 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:

- Escribir los datos que aparecen en las casillas en blanco.
- 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 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.

E j e r c i c i o n º 7

Utilización de Fórmulas

pág. 22
Servicio de Informática

Grabar el libro de trabajo como “Fórmulas.xls” que deberá de constar de una única hoja de
cálculo que contendrá el trabajo realizado llamada “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. 23
Servicio de Informática

pág. 24
Servicio de Informática
• Nombre: Ejercicio 8
• Objetivo: Practicar el uso de:
- 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 unos
valores realizan una operación devolviendo un resultado.
• Enunciado:

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 tabla:

E j e r c i c i o n º 8

Fórmulas y Funciones

pág. 25
Servicio de Informática
NOTA: El primer total es necesario calcularlo utilizando una fórmula que
multiplique las tres celdas, mientras que el segundo total debe calcularse utilizando
la función PRODUCTO.

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ón CONTAR (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.

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. 26
Servicio de Informática
5. Función SI (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. 27
Servicio de Informática
7. Función BUSCAR (hoja: Buscar)

Escribir la siguiente tabla:

A continuación utilizar la función BUSCAR para determinar:

- Los nombres de los colores que tienen de valor “4,5” y “8,9”.
- Los colores con el valor más alto y con el valor más bajo (Estos valores se
tendrán que 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)

Escribir la columna “Fecha” y, utilizando la función DIASEM calcular el día 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. 28
Servicio de Informática

9. Función PAGO (hoja: 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:

- La cuota mensual que deberemos de pagar a lo largo de toda la vida del
préstamo.
- El número de meses que debemos de estar pagando.
- La cantidad total que habremos pagado 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. 29
Servicio de Informática
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
el período indicado).

pág. 30
Servicio de Informática
10. Función TASA (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, es el 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 préstamo
más ventajoso, así como cuál de ellas nos propone el peor.

pág. 31
Servicio de Informática

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
vector o matriz de búsqueda se encuentre ordenado.

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. 32
Servicio de Informática

Como se observa, la función CONTARA se diferencia de CONTAR en que
CONTARA cuenta todas las celdas que tengan algún carácter.

B8 = CONTAR(B2:B7)
B9 = CONTARA(B2:B7)
B10 = CONTAR.SI(B2:B7;"=letraA")
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. 33
Servicio de Informática
Para el ejercicio de BUSCAR, primero deberemos introducir la siguiente
tabla:

Y el resultado 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. 34
Servicio de Informática

A2 = DIASEM(A2;2), y esta fórmula deberá copiarse al resto de celdas.

DIASEM tiene dos argumentos:

• 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 usandoBUSCAR.

• Usando SI: deberemos introducir una sentencia SI anidada, quedando
el resultado:

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"))))))

Esta fórmula se copiará al resto de celdas.

• Usando BUSCAR: deberemos introducir una tabla auxiliar del
siguiente modo:

pág. 35
Servicio de Informática
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.

Veamos ahora las funciones financieras.

Donde:

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

La función PAGO que hemos usado tiene 3 argumentos:

• 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. 36
Servicio de Informática

.
.
.

Para el primer mes tendremos:

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 el segundo:

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 poder copiar las
fórmulas a otras filas.

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

pág. 37
Servicio de Informática

Para el cálculo de la tasa anual vamos a utilizar la siguiente fórmula:

E2 = TASA(C2;-D2;B2)*12

Esta fórmula deberá copiarse al resto de 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:

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

pág. 38
Servicio de Informática

Pero estas funciones no funcionarán ya que BUSCAR sólo trabaja con listas
ordenadas. Para solucionar esto tenemos dos opciones.

1. Ordenar los datos desde el menú Datos – Ordenar.
2. 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. 39
Servicio de Informática
• Nombre: Ejercicio 9
• Objetivo: Practicar el uso de gráficos dentro de Microsoft Excel para que el alumno se
habitúe a su utilización.
• Enunciado:

El ejercicio consistirá en realizar una serie de gráficos de los alumnos matriculados en las 8
provincias andaluzas durante una serie de añ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.

3. Calcular el promedio o media de los alumnos en cada provincia, utilizando para
ello la función PROMEDIO.

E j e r c i c i o n º 9

Gráficos

pág. 40
Servicio de Informática
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
14%
22%
22%
15%
11%
7%
5% 4%
Córdoba Granada Sev illa Málaga
Jaén Huelv a Almería Cádiz
Curso 97 - 98
14%
22%
23%
14%
10%
7%
5% 5%
Córdoba Granada Sev illa Málaga
Jaén Huelv a Almería Cádiz
Curso 98 - 99
2.480
3.700
3.800
2.500
1.200
940 700
1 . 8 0 0
Córdoba Granada Sev illa Málaga
Jaén Huelv a Almería Cádiz
Curso 99 - 00
2.800
4.250
3.950
2.800
1.300
1.100 850
2 . 0 0 0
Córdoba Granada Sev illa Málaga
Jaén Huelv a Almería Cádiz

pág. 41
Servicio de Informática

5. Crear distintos tipos de gráficos de columnas dónde se muestre la evolución de los
alumnos en cada Universidad a lo largo de los cinco cursos. Así pues, será necesario
crear 8 gráficos diferentes:

Universidad de Córdoba
2.480 2.600 2.750
2.800 3.000
0
1.000
2.000
3.000
4.000
96-97 97-98 98-99 99-00 00-01

Universidad de Granada
3.700 4.000
4.200 4.250 5.000
0
2.000
4.000
6.000
96-97 97-98 98-99 99-00 00-01

Universidad de Sevilla
3.800
4.150
3.950
4.100
3.950
3.600
3.800
4.000
4.200
96-97 97-98 98-99 99-00 00-01

Universidad de Málaga
2.500
2.930
2.800
2.540
2.900
2.200
2.400
2.600
2.800
3.000
96-97 97-98 98-99 99-00 00-01

pág. 42
Servicio de Informática
Universidad de Jaén
1.800
2.000
2.100
1.890 1.945
1.600
1.800
2.000
2.200
96-97 97-98 98-99 99-00 00-01

Universidad de Huelva
1.200 1.3001.2501.180
1.350
0
500
1.000
1.500
2.000
96-97 97-98 98-99 99-00 00-01

Universidad de Almería
940
1.100
1.050975
1.150
0
500
1.000
1.500
96-97 97-98 98-99 99-00 00-01

Universidad de Cádiz
700
850830840
875
0
250
500
750
1.000
1.250
1.500
96-97 97-98 98-99 99-00 00-01

6. 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
3.990
819
1.043
1.256
1.947
2.734
4.230
2.726
0
500
1.000
1.500
2.000
2.500
3.000
3.500
4.000
4.500
Córdoba Granada Sev illa Málaga Jaén Huelv a Almería Cádiz

pág. 43
Servicio de Informática

• 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.

2. Para calcular la media utilizaremos la función PROMEDIO:

G2 = PROMEDIO(B2:F2)

Copiamos el valor de G2 al resto de la columna promedio.

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. 44
Servicio de Informática

a) Seleccionamos el tipo de gráfico:

pág. 45
Servicio de Informática
b) Comprobamos que los datos son los que hemos seleccionado:

c) Cambiamos el título y los rótulos de datos:

pág. 46
Servicio de Informática

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

pág. 47
Servicio de Informática
El resultado final será:

Crearemos otros 4 tipos de gráficos circular para el resto de años. Podemos probar
las combinaciones que queramos en las opciones y ver que resultados obtenemos.

Por ejemplo, podemos conseguir los siguientes gráficos:

pág. 48
Servicio de Informática

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. 49
Servicio de Informática
Aplicando distintos estilos y herramientas de dibujo, podemos obtener, usando los
datos de Cádiz, el siguientegrá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. 50
Servicio de Informática
Aplicando distintos estilos al gráfico, podemos obtener:

pág. 51
Servicio de Informática
• Nombre: Ejercicio 10
• Objetivo: Calcular las ventas realizadas en los meses de Enero, Febrero y Marzo en la
UCO Tienda, partiendo del precio de coste de los productos.
• 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á
cambiar este porcentaje).
- El precio de venta al público (P.V.P.) tanto en euros como en pesetas (se podrá
cambiar el valor del cambio).
- El total de ventas para cada uno de los meses.
- Un pequeño balance de las ventas realizadas en el primer trimestre.
- Una hoja resumen con todas las 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

E j e r c i c i o n º 10

UCO Tienda

pág. 52
Servicio de Informática

pág. 53
Servicio de Informática

• Solución:

1. Abrir un libro nuevo y llamarlo Ucotienda.xls.
2. Cambiar el nombre a cada una de las hojas de cálculo que aparecen por defecto por
los siguientes :

3. En la hoja ENERO escribe la tabla siguiente. Vamos a calcular las columnas %I.V.A.,
P.V.P., Total y Toral – 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 que sigue:

pág. 54
Servicio de Informática

Le damos el nombre de IVA y damos a Agregar. Así habremos creado un nombre
IVA con el valor 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. Ahora calculamos la columna P.V.P. sumando las columnas Precio Coste e I.V.A., la
fórmula en la celda E5 quedará de la forma:

E5 = C5+D5

pág. 55
Servicio de Informática

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

7. Todos las columnas con la moneda €, llevan un formato personalizado aplicado de
la forma en la pantalla de formatos:

8. Para calcular el Total debermos multiplicar el número de unidades vendidas por el
P.V.P. del producto, introduciendo en la celda F5:

pág. 56
Servicio de Informática

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 de la 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. 57
Servicio de Informática

es de la forma:

11. Calculamos la suma total de cada una de las columnas de importes, quedando un
resultado de la forma:

pág. 58
Servicio de Informática
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.

13. 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 1er.trimestre, con
la siguiente tabla:

pág. 59
Servicio de Informática

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,
FEBRERO y MARZO, por tanto deberemos introducir:

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 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. 60
Servicio de Informática
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:

La tabla resumen queda de la forma:

22. Por último, para crear la hoja resumen, copiaremos una a uno todas las tablas.
Tenemos que tener cuidado en que no podemos copiar las fórmulas. Para ello
tendremos que usar el pegado espacial del menú edición.

pág. 61
Servicio de Informática
• Nombre: Ejercicio 11
• 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órdoba en el curso académico 1999-2000, como muestra la siguiente tabla:

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

Titulaciones

pág. 62
Servicio de Informática
Realizar las siguientes operaciones:

- El nº total de alumnos que finalizaron sus estudios en el curso considerado.
- 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
86
35
8
68
10
19
23
19
9
27
35
207
23
0 50 100 150 200 250
Diplomado en Enfermería
Diplomado en Graduado Social
Ingeniero Agrónomo
Ingeniero de M ontes
Ingeniero Técnico de M inas
Ingeniero Técnico en Informát ica
Ingeniero Técnico Industrial
Licenciado en Biología
Licenciado en Bioquímica
Licenciado en Ciencia y
Tecnología de los Alimentos
Licenciado en Ciencias
Ambientales
Licenciado en Derecho
Licenciado en Física
Aprobados en 1999-2000
2212
51
50
47
83
58
101
39
52
26
60
48
43
45
0 50 100 150 200 250
Licenciado en
Historia
Licenciado en
Psicopedagogía
Licenciado
Filología
Hispánica
Licenciado
Historia del Arte
M aestro,
Audición y
Lenguaje
M aestro,
Educación Fisica
M aestro,
Educación
M usical
M aestro, Lengua
Extranjera

pág. 63
Servicio de Informática

• 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. 64
Servicio de Informática

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í obtenemos las celdas F3 y F4.

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. 65
Servicio de Informática

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 método explicado en el

pág. 66
Servicio de Informática
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. 67
Servicio de Informática

10. Pulsamos siguiente y elegimos los datos desde la fila 3 hasta la 15 de la forma:

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. 68
Servicio de Informática
• Nombre: Ejercicio 12
• 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.

E j e r c i c i o n º 12

Notas de Alumnos

pág. 69
Servicio de Informática
Realizar las siguientes operaciones:

- 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 MUY DEFICIENTE
2 INSUFICIENTE
5 SUFICIENTE
6 BIEN
7 NOTABLE
9 SOBRESALIENTE

El resultado obtenido debe ser similar al siguiente:

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: Para realizar esta estadística será necesario utilizar la función CONTAR.SI.

pág. 70
Servicio de Informática
A continuación se desea realizar las siguientes representaciones gráficas:

- Un gráfico que represente las notas medias obtenidas por los alumnos, comparadas
cada una de ellas con la nota media de la clase.

PUNTUACIONES
0,0
2,0
4,0
6,0
8,0
10,0
Alumnos
N
ot
as
Nota media
Media

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

Porcentaje de Alumnos
por sexo
2; 33%
4; 67%
Hombres
Mujeres

pág. 71
Servicio de Informática
- Un gráfico circular que muestre el porcentaje de alumnos aprobados por sexo:

Aprobados
50%
50%
Hombres
Mujeres

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

Suspensos
100%
0%
Hombres
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. 72
Servicio de Informática
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. 73
Servicio de Informática
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. 74
Servicio de Informática

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 (H para hombre y M para mujer). En nuestro ejemplo es:pág. 75
Servicio de Informática

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. 76
Servicio de Informática

12. Las fórmulas que debemos usar en cada celda son:

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. 77
Servicio de Informática
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 el menú Insertar Gráfico.

pág. 78
Servicio de Informática
Aparece una pantalla como la que sigue:

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. 79
Servicio de Informática

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. 80
Servicio de Informática
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. 81
Servicio de Informática

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. 82
Servicio de Informática

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. 83
Servicio de Informática

21. Damos a siguiente y ponemos en la pestaña de Título 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 este resultado.

pág. 84
Servicio de Informática

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 en la pestañ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. 85
Servicio de Informática
• Nombre: Ejercicio 13
• 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).

Consejo
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 el número de con la que Excel rellenará
la celda.

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

E j e r c i c i o n º 13

Tablas de Multiplicar

pág. 86
Servicio de Informática

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

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
- D4=A4*D3
- B5=A5*B3
- B6=A6*B3
- Etc.

pág. 87
Servicio de Informática

Si observamos las fórmulas anteriores nos daremos cuenta que las referencias a la
columna A y a la fila 3 nunca cambian.

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 esta fila.
• Por tanto la fórmula correcta a introducir en la celda B4 sería =$A4*B$3.

A continuaciónse 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
los resultados son correctos.

Copiamos el contenido de la hoja Tabla1 a la hoja Tabla2 y borramos el resultado.
Cambiamos el contenido de la fila 1 a “Tabla Resultado”.
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 la nomenclatura
NombreHoja!celdas.

De este modo, tenemos que horizontalmente:

- B4=Tabla1!B4*Tabla2!B4
- C4=Tabla1!C4*Tabla2!C4
- D4=Tabla1!D4*Tabla2!D4

Y verticalmente:

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

pág. 88
Servicio de Informática
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. 89
Servicio de Informática
• Nombre: Ejercicio 14
• 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
Matriculados” y otra llamada “Gráficos Alumnos”.

2. 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:

E j e r c i c i o n º 14

Matrículas 2003

pág. 90
Servicio de Informática

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.
El resultado deberá quedar del siguiente modo:

pág. 91
Servicio de Informática

• 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ón o el botón de la barra de herramientas.

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.

3. Vamos a calcular el total de los alumnos matriculados. Tendremos:
F4=B4+D4
F5=B5+D5
…

pág. 92
Servicio de Informática
Tenemos 2 opciones:

• 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:
AlumnosTotal
MujeresTotalMujeresPorcentaje =
MujeresPorcentajeHombrePorcentaje −= 1

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
de la casilla.

De esta forma:

G4=(C4+E4)/F4
H4=1-G4

Copiamos estas fórmulas al resto de celdas y obtendremos los resultados buscados.

4. Para calcular los totales de la fila 11 tenemos 2 opciones:

• 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.
Esto nos introducirá la fórmula: B11=SUMA(B4:B10)

Repetiremos la misma acción para las celdas C11, D11, E11 y F11.

5. En este apartada deberemos introducir un gráfico circular. Para ello 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
Servicio de Informática
a) Elegimos el tipo de gráfico y pulsamos siguiente:

pág. 94
Servicio de Informática
b) Comprobamos los datos de origen, que coincidirán con los que hemos
seleccionado y pulsamos siguiente:

pág. 95
Servicio de Informática
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. 96
Servicio de Informática
6. Seleccionamos las columnas A3:A10, G3:G10 y H3:H10 y pulsamos . Seguimos
los siguientes pasos:

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

pág. 97
Servicio de Informática
b) Comprobamos los datos de origen, que coincidirán con los que hemos
seleccionado y pulsamos siguiente:

pág. 98
Servicio de Informática
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. 99
Servicio de Informática
• Nombre: Ejercicio 15
• Objetivo: Utilización de las funciones BUSCARV y SI
• Enunciado:

El siguiente ejercicio consistirá en calcular el total de una factura dependiendo de los
precios que tengamos almacenados.

Crearemos un libro llamado “Facturación.xls” 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 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 si el pago es al contado. 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:

E j e r c i c i o n º 15

Facturación

pág. 100
Servicio de Informática

Cada factura se compondrá de 5 productos, que será necesario indicar obligatoriamente.

• Solución:

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 Euro a la columna C.

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 “Factura” e
introducimos la siguiente hoja:

pág. 101
Servicio de Informática

En la figura hemos indicado los campos que debemos rellenarpara 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 herramientas correspondientes.

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
comporte como queremos.

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 buscan los
datos.
• indicador_columnas: es el número de columna de matriz_de_comparación
desde la cual debe devolverse el valor coincidente.

pág. 102
Servicio de Informática
• 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.

Utilizando BUSCARV construiremos la siguiente fórmula:

C7 = BUSCARV(B7;Productos!$A$2:$B$9;2;FALSO)

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
por unidad del producto. Para ello usamos de nuevo BUSCARV:

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
Servicio de Informática
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
argumentos:

• 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: es el valor que se devolverá si prueba_lógica es FALSO.

En nuestro caso tendremos:

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 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
Servicio de Informática
• Nombre: Ejercicio 16
• 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 elegimos
“Cambiar nombre”, introduciendo el nombre indicado a cada hoja. Borramos el
resto de hojas que crea Excel por defecto deberemos borrarla.

E j e r c i c i o n º 16

Control de Cuenta

pág. 105
Servicio de Informática
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 columna E.

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
o en salida. La fórmula en Excel sería:

E5 = SI(O(C5<>"";D5<>"");E4+C5-D5), donde hemos utilizado el operado lógico O.

Pero esta formula tiene varios problemas:

1. No cumple el requisito de que sólo puede haber o salida o entrada.
2. 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+C5-D5;"")

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
Servicio de Informática

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

Que se comporta del siguiente modo:

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.

Hay otra forma de llegar al mismo resultado utilizando la función CONTAR:

E5 = SI(CONTAR(C5;D5)=1;E4+C5-D5;SI(CONTAR(C5;D5)=2;”Fallo”;””)

De esta forma llegamos al mismo resultado.

Ahora sólo nos queda poner en rojo el saldo cuando sea negativo. Para ello tenemos
dos opciones:

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á el siguiente diálogo:

pág. 107
Servicio de Informática
• Nombre: