Sistemas de Información Digitales II Ejercicios de Exceldi002.edv.uniovi.es/~alberto_mfa/sid2/apuntes/excel/ejercicios... · Ejercicios de Excel Curso 2004-2005 . ... Ejercicio 1

Gestión y administración PúblicaEscuela Universitaria de Empresariales GAP

Sistemas de Información Digitales II

Ejercicios de Excel

Curso 2004-2005

Sistemas de Información Digitales II Curso 2004-2005

Página 2 de 17

EJERCICIO 1 ............................................................................................................................................ 2 EJERCICIO 2 ............................................................................................................................................ 2 EJERCICIO 3 ............................................................................................................................................ 3 EJERCICIO 4 ............................................................................................................................................ 4 EJERCICIO 5 ............................................................................................................................................ 5 EJERCICIO 6 ............................................................................................................................................ 5 EJERCICIO 7 ............................................................................................................................................ 6 EJERCICIO 8 ............................................................................................................................................ 6 EJERCICIO 9 ............................................................................................................................................ 7 EJERCICIO 10 .......................................................................................................................................... 8 EJERCICIO 11 ........................................................................................................................................ 10 EJERCICIO 12 ........................................................................................................................................ 11 EJERCICIO 13 ........................................................................................................................................ 11 EJERCICIO 14 ........................................................................................................................................ 12 EJERCICIO 15 ........................................................................................................................................ 13 EJERCICIO 16 ........................................................................................................................................ 14 EJERCICIO 17 ........................................................................................................................................ 16

Ejercicio 1 Hacer una hoja de cálculo para llevar un control de entradas y salidas de dinero caja al estilo de una cuenta corriente. Tendrá las siguientes columnas: Fecha, Concepto, Haber y Debe. En la columna del Haber situaremos las entradas de dinero y en la columna del Debe las salidas. La última columna la utilizaremos para llevar el control del saldo.

Resolver las celdas A3, B3, C3, D3, E3 y E4

Ejercicio 2 En la tabla siguiente se muestran datos de asistencia de algunos campos de fútbol a lo largo de cuatro jornadas. Se tiene, asimismo, el aforo de dichos campos.


Página 3 de 17

Se pide:

1. Calcular la media de asistencia de las cuatro jornadas y el porcentaje de la media con respecto al aforo del campo.

2. Se creará un gráfica de tipo tarta en tres dimensiones con las medias de las

asistencias. La gráfica se incluirá en la misma hoja que los datos.

3. Se creará una gráfica de tipo columna vertical con los porcentajes de asistencias. La gráfica se incluirá en la misma hoja que los datos.

Ejercicio 3 En la siguiente tabla se presentan datos de algunos test realizados a varias tarjetas gráficas aceleradoras 3D.

En base a los datos presentados, se pide:

1. Calcular la media de cada una de las 3 pruebas realizadas. Este valor se situará justo debajo la columna correspondiente (ver modelo presentado).

2. Añadir tres columnas más después de los datos del tercer test.

a. La primera columna (Resultado sin ponderar) será la media

aritmética para cada tarjeta de los valores obtenidos en cada test. b. La segunda columna (Resultado ponderado) corresponde con el

criterio de cálculo indicado en Eq. (1).


Página 4 de 17

c. La tercera (Valoración) representa la calificación que se le da a la tarjeta en función del resultado ponderado obtenido:

i. EXCELENTE para puntuaciones mayores que 115 ii. ACEPTABLE entre 100 y 115 iii. BAJA CALIDAD con puntuación inferior a 100 puntos.

3. Ordenar las tarjetas según el valor obtenido en la columna Resultado

ponderado. 4. Se creará una gráfica de tipo barras en tres dimensiones donde se muestren

simultáneamente los resultados de los 3 test y el resultado ponderado de la mejor y la peor tarjeta. La gráfica se incluirá en la misma hoja que los datos.

5. Se creará una segunda gráfica de tipo líneas donde se representen

simultáneamente los resultados sin ponderar y ponderados de todas las tarjetas que han obtenido la puntuación EXCELENTE o ACEPTABLE.

Ejercicio 4 Los datos que se le suministran son los movimientos de cuentas -ingresos y gastos- de sesenta clientes ficticios durante un periodo de un año (tres cuatrimestres).

Se pide:

1. Evaluar las columnas PARCIAL1, PARCIAL2 y PARCIAL3, definidas en el apartado anterior, cuyo resultado resulta de restar a los INGRESOS del primer, segundo o tercer cuatrimestre -según corresponda- sus respectivos GASTOS. El formato en el que deben aparecer los números de la tabla (columnas de la D hasta la N) es el formato numérico con un punto de separación para las unidades de millar. Los números positivos serán de color negro, mientras que los negativos irán en rojo.

2. Al final de cada columna, obtener la suma de todos los vectores

correspondientes a ingresos, gastos y resultados parciales de los 60 clientes por cuatrimestres.

3. En la columna etiquetada como TOTAL (columna N) introduzca el resultado

de la suma de los correspondientes resultados PARCIALES por filas, calculados en el párrafo anterior.

4. Como se observa, existen bastantes clientes que deben dinero al banco.

Evalúense las pérdidas que por este motivo deja de ingresar el banco. Se debe sumar por columnas todas las celdas cuyo resultado parcial sea menor que cero y colocar su suma, en las celdas K64, L64 y M64 respectivamente.


Página 5 de 17

Ejercicio 5 Se pide crear una pequeña hoja de Excel donde se pueda desglosar una cantidad dada en Euros en sus diferentes monedas y billetes, de forma que se tenga que utilizar el menor número posible de cada uno de ellos. Resolver las celdas D1, D2, E2, D3, E3.

Ejercicio 6 En la siguiente hoja de cálculo:

Se pide: 1. En la columna “€. ganados” calcular la cantidad ganada teniendo en cuenta

si se han conseguido más de 50 puntos o no (tabla inferior)

2. En la columna “Agenda” aparecerá “SI” en caso de que la persona en cuestión haya participado en el concurso 1, y “NO” en caso contrario.

3. En la columna “Reloj” aparecerá “SI” en caso de que la celda

correspondiente de la columna “Agenda” esté vacía; en otro caso, aparecerá “NO”.


Página 6 de 17

4. En la columna “Premio extra” aparecerá “Viaje a París” en caso de que se hayan conseguido más de 100 ptos., y “Otra vez será”, en otro caso.

En la misma hoja, en la siguiente tabla:

5. En la fila “€ ganados” aparecerá 0 en caso de que no se hayan conseguido más de 80 ptos., y 1500 en caso contrario

6. En la fila “Premio extra” aparecerá “Agenda” si se han conseguido 120

puntos o más y, en caso contrario, “Reloj”.

7. En la fila “Valor premio extra” aparecerá 180 € si el premio extra conseguido ha sido una agenda y 60 € en caso contrario.

Ejercicio 7 Se pretende controlar la fecha de vencimiento de las facturas emitidas por una empresa y hacer un desglose por meses con las previsiones de cobro de estas. En la hoja se introducirán los datos básicos de cada factura:

• Empresa • Num. Factura • Importe • Vencimientos (30, 60, 90, etc) hasta tres.

La hoja calculará las fechas de cada vencimiento y realizará el desglose para doce meses desde Octubre hasta Septiembre. Para cada mes se dará un total. La figura adjunta muestra la estructura de la hoja. Resolver las celdas C1, I5, L5, M5 y M2

Ejercicio 8 Calcular la letra del NIF a partir del DNI. Simplemente 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 12 13 14 15 16 17 18 19 20 21 22 23

Letra T R W A G M Y F P D X B N J Z S Q V H L C K E F


Página 7 de 17

Resolver la celda C1

Ejercicio 9 Cálculo de los dígitos de control de una cuenta bancaria. En España, las cuentas bancarias se identifican por un número de 20 cifras. De ellas, 4 identifican al banco, 4 a la sucursal donde abrimos la cuenta, 10 a la cuenta en sí y otros 2 se incluyen como dígitos de control para validar el número. El sistema se basa en una tabla de pesos:

BBBB SSSS DD CCCCC CCCCC Dónde: BBBB son los cuatro dígitos del banco SSSS son los cuatro de la sucursal CCCCC CCCCC Es el código de la cuenta D D Son los dos dígitos de control a calcular

Dígito 10 9 8 7 6 5 4 3 2 1Peso 1 2 4 8 5 10 9 7 3 6

Forma de cálculo del primer DC:

• Se toman BBBB y SSSS • Se multiplica cada dígito por su peso • Se suman todos los resultados • Se toma el resto de la división del sumatorio anterior entre 11 • Se realiza la operación 11 – resto. • Este es el resultado, con la salvedad de que si el anterior:

o es 11 el resultado es 0 o es 10 el resultado es 1

Forma de cálculo del segundo DC:

• Se toman CCCCC CCCCC • Resto de operaciones igual...


Página 8 de 17

Resolver las celdas C2, D2, H7, H8, H17, H18, K7, K8, K17, K18

Ejercicio 10 Calculo de la amortización de un préstamo. La mayoría de los préstamos que conceden los bancos a sus clientes usan como método de amortización el denominado Sistema Francés” que, a grandes rasgos, se resume en que:

• Siempre se paga una cuota fija a lo largo de toda la vida del crédito. • El importe de la cuota se descompone en:

o Una cantidad destinada al pago de los intereses sobre la cantidad que queda por amortizar.

o La diferencia entre la cuota fija y la cantidad que se destina a intereses se emplea en amortización. Es decir, a devolver parte del capital prestado.

Así, al mes siguiente se pagan intereses sobre la cantidad que queda por devolver (el balance) que ya será un poco menos que el mes anterior y el resto se destina a devolver capital (a amortizar).

De esta forma, aunque cada mes se paga siempre lo mismo, se abonan al principio de la vida del préstamo la mayor parte de los intereses y al final la mayor parte de la cuota se destina a amortización.

Las siguientes formulas se emplean para resolver los cálculos:

Interés Mensual


Página 9 de 17

Con balance inicial

Resolver las celdas B6, B7, B8, C10, D10, E10, C11, D11, E11


Página 10 de 17

Ejercicio 11 Se necesita mejorar la gestión de los prestamos de películas de un video club. Se pide construir un libro que tenga tres hojas: GESTION, que gestionará las películas alquiladas y su fecha de devolución, así como el cliente y la multa en caso de pasar más de un día. La hoja CLIENTES recoge los datos de éstos, y la hoja PELÍCULAS las características de estas últimas.

Esta será la apariencia de las tres hojas. Resolver las celdas I1, E5, F5, G5, H5, I5 y J5 teniendo en cuenta que:

• El nombre del socio se debe aparecer con solo introducir el código del cliente • El título de la película aparecerá al teclear el código de esta. • La fecha de devolución se calculará sumando los días de préstamo a la fecha

en que se lleva la película. • El importe se calculará en función de la fecha de devolución, obteniendo el

precio por día de la tabla de películas. • La multa se irá calculando según pasen los días. Si el importe de esta sube

más de 2000 ptas, se debe mostrar un mensaje de alerta en rojo.


Página 11 de 17

Ejercicio 12 Se necesita facilitar la generación de facturas de una determinada empresa de ferretería al por menor. Las facturas deberán tener el aspecto de la figura. Se dispondrá de otra hoja en la que figurarán todos los artículos con la siguiente información por artículo: Código, Descripción y precio unitario. La factura podrá incorporar hasta 15 items distintos. Si se aplica condición de pago “contado” se hará un descuento del 5%. El IVA se calculará sobre el 16% del importe a pagar.

Resolver la hoja e indicar el contenido de las celdas: B2, C6, D6, E6, E21, E22, E23 y E25.

Ejercicio 13 Se desea realizar una evaluación de profesorado de cierto centro. Para ello, se consulta a cierto número de alumnos sobre profesores de diversas asignaturas (suponiendo que cada asignatura la imparte un solo profesor): lengua española, inglés, historia, ciencias naturales, matemáticas, física, filosofía. Cada alumno califica a cada profesor como malo, regular, normal, bueno o muy bueno; los resultados se guardan en una tabla de frecuencias. En una casilla determinada, al


Página 12 de 17

principio de la hoja, poner el número de alumnos (para que la hoja se pueda usar en otras encuestas).

Se debe:

• Crear una columna de verificación; contiene la suma de votos para cada asignatura.

• Crear a su derecha una columna que avise de los errores; si la suma de votos de esa asignatura no es igual que el número de alumnos en esta columna aparecerá un mensaje de error. Si está correcto, aparecerá en blanco.

• Se debe añadir una columna que mida el porcentaje de descontentos en cada asignatura. Se consideran descontentos los alumnos que califican como "mal" y como "regular".

• Calcular la desviación estándar de cada calificación, y la media de las desviaciones estándar.

• Para cada profesor (es decir, asignatura), realizar un gráfico (de tarta) que muestre la proporción de alumnos que lo consideran malo, regular, normal, bueno o muy bueno. Cada uno de estos gráficos aparecerá en una hoja nueva del libro.

Indicar el contenido de las celdas: G4, H4, I4, B11, B12

Ejercicio 14 La revista “ENIGMA” ha publicado en el número de este mes unos datos referentes a nº de avistamientos de OVNIS en diferentes países del mundo a lo largo de una semana. El autor del artículo trata de demostrar, mediante una serie de representaciones gráficas, que los alienígenas muestran una clara tendencia a mostrarse más los fines de semana que entre semana y más en los países anglosajones (muy especialmente en EEUU) que en los latinos.


Página 13 de 17

Los gráficos que se aportan para apoyar las conclusiones citadas muestran:

1. La evolución del nº de avistamientos a lo largo de la semana en los cinco países estudiados (gráfico de líneas)

2. Una comparación del número de avistamientos en los cinco países cada día

de la semana (gráfico de columnas)

3. Dos gráficos circulares mostrando cómo se distribuyen los avistamientos totales de sábado y domingo entre los países estudiados.

Además, se realiza un estudio particularizado de los fenómenos OVNI registrados en España, EEUU y Gran Bretaña. Para ello, se muestran tres gráficos que representan:

4. La evolución del número de avistamientos en estos tres países los 4 primeros días de la semana (gráfico de líneas)

5. Una comparación del número de avistamientos que han tenido lugar en los

días siguientes: Lunes, Miércoles y Domingo (gráfico de columnas).

6. Un gráfico circular que muestre la distribución del total de experiencias UFO en las tres naciones citadas.

Ejercicio 15 El incansable autor del artículo sobre ovnis, se embarca en un estudio referido esta vez a la proporción de gente de distintas ciudades americanas, convencida de la existencia de: el Yeti, los fantasmas y el monstruo del Lago Ness. Para eso muestra los datos (en porcentajes de convencidos sobre el total de encuestados) publicados en Internet por el prestigioso Institute for Search of Inusual Forms of Life, de Massachussets, y extraídos de encuestas realizadas en 1971, 1985 y 1998 en Nueva York, Los Ángeles, San Francisco y Chicago:

En el estudio se muestran gráficos que indican:


Página 14 de 17

1. La evolución producida en las convicciones de los neoyorquinos referentes a la existencia del Yeti desde 1971 hasta 1998

2. Esa misma evolución pero referida a las convicciones de los habitantes de

San Francisco sobre la existencia de fantasmas.

3. La evolución de las opiniones desde 1971 hasta 1998 en las cuatro ciudades en relación a las tres entidades estudiadas.

4. Una comparación de los porcentajes de convencidos de la existencia del

Monstruo del Lago Ness en las cuatro ciudades y en las tres encuestas.

5. Una comparación de los porcentajes de personas convencidas de la existencia de fantasmas en Los Ángeles y Chicago en el año 1971

6. Una comparación de los datos de las tres encuestas realizadas en la ciudad

de San Francisco.

Ejercicio 16 Un comerciante se ha hecho cargo de una tienda de electrodomésticos. Tras el inventario, ha obtenido, para el mes de Noviembre, los datos que se indican a continuación.

Se pide:

1. Dar un código a cada artículo del almacén. Columna A, 2. Calcular el coste total por artículo (coste de compra + parte proporcional de

transporte). Columna G.

3. El comerciante desea ganar por la venta de cada electrodoméstico, un 30% del precio de coste total del mismo. ¿Qué precios deberá establecer como precios de venta?. Columna H.

4. En el próximo mes de Diciembre, las ventas que se realizarán serán un 25%

de las existencias del mes de Noviembre. Calcular para el mes de diciembre, en una hoja nueva llamada DICIEMBRE:

a. Existencias iniciales por tipo de electrodoméstico b. Unidades vendidas por tipo de electrodoméstico (como no se pueden

vender trozos de electrodoméstico hay que redondear mayorando) c. Existencias finales por tipo de electrodoméstico d. Ingresos por tipo de electrodoméstico e. Ganancias obtenidas por tipo de electrodoméstico

5. Realizar un gráfico de columnas que refleje las existencias iniciales y finales

por tipo de electrodoméstico para el mes de Diciembre. Indicar en cada barra el valor numérico de las existencias. Crearlo en una nueva hoja llamada GRAFICO DICIEMBRE.


Página 15 de 17

6. Se quiere realizar una hoja de salida, para imprimir de forma cómoda

albaranes de salida de los distintos electrodomésticos. Crear una nueva hoja llamada HOJA SALIDA.

Esta factura tendrá el aspecto mostrado en la figura de más abajo.

Factura de salida Fecha 03/02/2003

N. Factura: 1235 Código Unidades Electrodoméstico Precio venta Total

1 1 Lavadoras 241,80 € 241,80 € 2 10 Frigoríficos 319,80 € 3.198,00 € 1 4 Lavadoras 241,80 € 967,20 €

Subtotal 4.407,00 € Oferta (S/N) Descuento 661,05 € S IVA 16% 705,12 € Total 4.451,07 € Una hoja de estas servirá para entregar hasta cuatro tipos de

electrodomésticos distintos. El usuario deberá teclear:

• El número de factura (es solo informativo) • Por cada tipo vendido el código del electrodoméstico • Por cada tipo vendido la cantidad vendida de ese código • Para la factura completa si se hace descuento o no.

El encabezado mostrará:

• La fecha del día de forma automática (cogida del sistema) • El número de factura

La factura deberá reflejar en cada línea:

• El nombre del electrodoméstico o Solo si el usuario ha tecleado el código

• El precio de venta del producto o Solo si el usuario ha tecleado el código

• El total por tipo de producto o Si el usuario ha tecleado el código y la cantidad

Nota: NO deben aparecer mensajes de error de Excel (#¿...?) si el usuario no ha teclado alguno de los campos

La caja de totales deberá contener:

• Total de la factura. • Si hay descuento, se realizará un descuento del 15%.


Página 16 de 17

Ejercicio 17 Con motivo de la próxima convocatoria para las plazas de técnico administrativo para el MAP, se desea automatizar el proceso de selección. La nota final de cada aspirante se obtendrá de cuatro pruebas y de sus méritos. Las pruebas que deberán realizar los aspirantes son las siguientes: • Primer examen pruebas psicotécnicas • Segundo examen cultura general • Tercer examen prueba teórica • Cuarto examen prueba práctica Todas estas pruebas serán excluyentes. Si no se obtiene la calificación de aprobado (nota >= 5) en alguna de ellas la NOTA FINAL será 0. (Se deberá calcular con una formula). El cálculo de la NOTA por PRUEBAS resultante de estas se hará de la siguiente forma: NOTA PRUEBAS = 0,25 * NOTA1 + 0,75 * NOTA2 Donde: NOTA1 = media de las pruebas psicotécnicas y cultura general NOTA2 = media de la prueba teórica y prueba práctica Los meritos a valorar serán:

• Juventud • Titulación académica • Experiencia

Puntuación por juventud: Se premiará con una puntuación de 0,25 puntos a las personas con edad inferior a 30 años. Esta puntuación se denominará NOTA JOVEN. Puntuación por titulación académica: Se asignarán las siguientes puntuaciones. Esta puntuación se denominará NOTA TITULO: • título medio 0,15 • título superior 0,20 Puntuación por experiencia: En función de la tabla siguiente. Esta puntuación se denominará NOTA EXPERIENCIA: • De 1 a 12 meses 0,10 puntos • De 13 a 20 meses 0,20 puntos • De 21 meses en adelante 0,30 puntos La NOTA FINAL definitiva se obtendrá sumando las notas anteriores (NOTA PRUEBAS, NOTA JOVEN, NOTA TITULO y NOTA EXPERIENCIA). Las plazas a cubrir son las que se indican a continuación: • 1 plaza fija que se otorgará a la persona que obtenga la máxima puntuación • varias plazas temporales, que se otorgarán a todas las personas que superen los

7 puntos.


Página 17 de 17

Se pide: Hacer una hoja en la que se refleje automáticamente, a quién se asignará la plaza fija y las temporales. Deberán existir las siguientes columnas:

• Nombre • Edad • Meses de experiencia • Titulación • Nota pruebas psicotécnicas • Nota cultura general • Nota prueba teórica • Nota prueba práctica

Y se deberán calcular estas otras:

• NOTA1 • NOTA2 • NOTA PRUEBAS • NOTA JOVEN • NOTA TITULO • NOTA EXPERIENCIA • NOTA DEFINITIVA • TIPO de PLAZA obtenida.

Comprobar finalmente, a quién corresponderán las plazas, según los siguientes datos:

Documents

Sistemas de Información Digitales II Ejercicios de Exceldi002.edv.uniovi.es/~alberto_mfa/sid2/apuntes/excel/ejercicios... · Ejercicios de Excel Curso 2004-2005 . ... Ejercicio 1