1ceformar.edu.co/curso_aprobar/seccion 16/clase 5… · web viewa partir de la celda que contiene...

CURSO DE EXCEL 2007 AVANZADO POR EMAILPROFESOR: LUIS PALACIO

[email protected]

CLASE 51AOTROS EJERCICIOS DE ALGUNOS TEMAS ANTERIORES

A continuación se proponen algunos ejercicios de algunos temas anteriores los

cuales debe realizar por su cuenta (no tiene que enviarme la solución) en un libro

de Excel y luego compararlos con el archivo de Excel que envío de solución de los

mismos.

Ejercicio 1

La figura siguiente muestra la lista de precios de una librería

Visite nuestra página Webwww.cursosmail.com


[email protected]

A partir de los datos de esta lista, establecer las funciones que realicen los

siguientes procesos:

1. Introducir un código en una celda y que aparezcan en las celdas

contiguas el nombre y precio del artículo correspondiente al mismo.

(Utilizar la función buscar).

2. Introducir un código en una celda y que en la celda contigua

aparezca la frase "El precio del artículo es xxx euros", siendo xxx el

precio del artículo correspondiente. (Utilizar la función concatenar).

3. Introducir el título del libro en una celda y que en la celda contigua

aparezca el precio del mismo si éste es mayor de 6 euros y si no lo es que

no aparezca nada.

4. Crear una hoja de cálculo que represente la factura de entrega de cada una de las ventas que realiza la librería. En dicha factura aparecerá

el código de los artículos, el número de éstos vendidos, el nombre del

artículo, el precio unitario, el precio total por artículo, el precio total del

pedido, el I.V.A. y el total de la factura.

Algunos aspecto de la factura a tener en cuenta son:

La nota podrá contener un máximo de seis artículos distintos,

tendrá seis filas en las cuales introducir datos.

Al introducir el código del artículo y el número de éstos, se

rellena automáticamente el resto de la factura.

Las filas que no se precisen, porque se han vendido menos de seis

artículos distintos, no mostrarán ninguna información, aunque contienen la

fórmula que se precisa en cada caso.



[email protected]

AYUDA

Para rellenar de forma automática las celdas correspondientes a

nombre del artículo y precio unitario, se utiliza la función Buscarv, que

localiza en la lista de precios el código del artículo introducido y devuelve el

nombre del artículo, en un caso, y el precio unitario en otro. El resto son

fórmulas sencillas de sumas o multiplicaciones.

Para que las filas de la factura que no se precisen permanezcan en

blanco, habrá que utilizar las función lógica Sí junto a la función de

información Estexto o Esnúmero, para establecer condiciones. Si el valor

de una celda es texto, el valor mostrado en la celda será el resultado de la

función correspondiente y si no lo es no se mostrará ningún valor, es decir

nulo, nada: Esto se representa mediante "" (comillas-comillas).

La siguiente imagen muestra el aspecto de la factura.



[email protected]

Ejercicio 2

El cálculo de la letra que corresponde a un D.N.I. determinado, se realiza en

función de un índice numérico, siendo la relación entre la letra y el índice la que

muestra la tabla siguiente:

Si obtenemos el índice, la letra del NIF resultante será la que corresponda con

dicho índice según la tabla anterior.



[email protected]

Para calcular el valor del índice, a localizar en la parte izquierda de la tabla, los

pasos a seguir son los siguientes:

1. Dividir el número correspondiente al D.N.I. entre 23.

2. Multiplicar por 23 la parte entera del valor obtenido anteriormente.

3. Restar al número del D.N.I. el valor obtenido en el paso 2.

El valor obtenido en el paso 3, es el índice. Ahora lo buscaremos en la tabla, y

la letra correspondiente en la segunda columna, esa es la letra del NIF o DNI.

Conocido el procedimiento para calcular la letra correspondiente a un D.N.I.

realizar las operaciones necesarias para realizar los siguientes procesos:

1. Introducir el D.N.I. en una celda, apareciendo automáticamente en

la celda contigua la letra correspondiente al mismo.

2. Generar de forma automática el D.N.I. completo, número y letra,

en una celda al introducir el D.N.I. en la celda de su izquierda. Utilizar para

ello la función concatenar.

3. A partir de la celda que contiene el D.N.I. completo, extraer la letra

correspondiente al mismo. Utilizar la función extraer.

Ejercicio 3

Crear una hoja de cálculo que permita conocer el peso total de una estructura metálica así como su precio conocido el peso de los distintos perfiles que se

pueden utilizar.

La siguiente imagen muestra la lista de perfiles junto al peso de los mismos y la

tabla que calcula el peso y precio de una determinada estructura.



[email protected]

AYUDA

Introducir los datos correspondientes a los perfiles (o copiar los

datos del enlace que aparece en el enunciado) que integran la estructura

metálica a cuantificar, y los datos correspondientes a la cabecera de la

tabla que se va a crear, dando a continuación formato a la misma.

En la tabla de cálculos, se introducirán los datos correspondientes a

los perfiles empleados en la columna tipo de perfil, en la columna

Longitud total la longitud de cada uno de los perfiles que se ha empleado

y en la columna Euros/Kg hay que introducir el precio por metro lineal. Para establecer el valor del resto de columnas habrá que utilizar las

funciones que se indican en la siguiente imagen.



[email protected]

En todos los casos se emplea la función SI, de este modo

únicamente se presentará un valor en esta casilla si se introducen valores

en las casillas anteriores.

En la columna Kgr/m se establece que en el caso de haber un dato

en la columna Tipo de perfil se emplee la función buscarv, busca el dato

introducido en la columna Tipo de perfil (D7) en la lista de perfiles (A3:B23)

y proporciona el dato correspondiente al peso del perfil (2). En las columnas

Peso total y Precio se indica que se realice el producto del contenido de

dos celdas si en ambas hay un dato.

Estas fórmulas se copiarán a lo largo de las columnas hacia abajo

mediante el cuadro de llenado de tal modo que al ir insertando los valores y

longitudes de los perfiles automáticamente se rellene el resto de la tabla.



[email protected]

Ejercicio 4

Aunque la moneda que actualmente tiene vigor en Europa es el Euro, tan solo

como ejercicio se propone el siguiente supuesto.

A partir de los datos crear una hoja de cálculo que permita obtener la conversión de cualquier cantidad de una determinada divisa a todas las demás.

Hacer este ejercicio siguiendo los pasos que se indican en la ayuda.



[email protected]

AYUDA

A la izquierda de la imagen se muestra el precio de todas las

monedas en relación a la peseta y a la derecha la tabla que permite, de una

manera automática, convertir una determinada cantidad de un divisa en las

demás.

Tan solo habrá que indicar la moneda y su cantidad para obtener la

conversión. La fórmula que se va a establecer es la que se indica en la

siguiente imagen, pudiendo copiarse a lo largo de la columna.

Ejercicio 5

Crear una tabla como la que se muestra en la siguiente imagen donde aparecen

en filas sucesivas, y en registros uno debajo del otro, los datos correspondientes

a una serie de amigos. A partir de ella crear una columna en la cual se indique el

valor "Cumpleaños feliz" junto a los datos del amigo que cumple años durante la semana.



[email protected]

AYUDA

Para determinar si cada uno de los amigos cumple años durante la semana

actual establecer las columnas que se indican en la siguiente imagen por la

derecha de las fechas de nacimiento y en cada una de ellas "desgranar" la fecha

de nacimiento, estableciendo las funciones que se muestran.



[email protected]

Los cálculos que se realizan en cada columna son los siguientes:

Las columnas Día y Mes extraen el día y el mes, respectivamente,

de la fecha de nacimiento de cada amigo.

La columna Día de hoy establece la fecha en el momento en que se

ha abierto el archivo.

La columna Año actual extrae el año de la fecha actual.

La columna Fecha de cumpleaños establece el día de este año en

que el amigo cumple años.

La columna Día de la semana establece el número del día de la

semana que corresponde al día de hoy.

La columna Lunes calcula la fecha correspondiente al lunes de esta

semana.

La columna Domingo calcula la fecha correspondiente al domingo

de esta semana.

La columna Cumpleaños establece si en la casilla no aparece un

valor o si aparece el valor "Cumpleaños feliz". Si la fecha de cumpleaños es

igual a la fecha del lunes o el domingo de esta semana o es mayor que la

fecha del lunes y menor que la fecha del domingo (está dentro de esa

semana), en la casilla se muestra el valor Cumpleaños feliz, en caso

contrario no ("").



[email protected]

Ejercicio 6

1. Elaborar un cuadro en el que se calcule la nómina de la

trabajadora María Gil Ruiz para los 12 meses del año en los que va a estar

contratada, con las siguientes condiciones:

Durante este primer año, su sueldo base será de 600€ en Enero

incrementándose en un 3% mensual hasta Diciembre. Si continúa

trabajando durante el siguiente año en la empresa, el sueldo cobrado en

Diciembre de este año, será el definitivo para todos los meses.

Calcular según la tabla proporcionada por Hacienda, los impuestos

por meses, los importes de los impuestos en euros, así como el total a

cobrar neto cada mes. También se reflejarán los totales anuales.

El porcentaje de impuestos que corresponde a cada mes, se busque en una

tabla que supuestamente nos facilita la Agencia Tributaria y que en su primera

columna parte de sueldos entre 0 y 300 euros y luego desarrolla de 60 en 60

euros hasta un límite de 3.000 euros. En esta tabla y en su segunda columna, los

porcentajes serán de un 2% para sueldos de hasta 300 euros, incrementándose

hacia abajo de 0,25% en 0,25% hasta ese límite de 3.000 euros al que

corresponderá un porcentaje (como comprobación) del 13,50%. A la tabla se le

llamará TABLAIMPU

2. Calcular los totales anuales.

3. Realizar debajo de la primera nómina de María, otra para Juan y otra

más abajo para Alberto siendo para éstos dos empleados el sueldo de partida de enero de 580 y 800 euros respectivamente.

4. Obtener también las sumas de impuestos en euros y totales a cobrar de los 3 empleados por cada mes del año así como de los totales



[email protected]

anuales para saber cuánto declarar de impuestos y cuanto se paga de

nóminas anualmente.

AYUDA

Una forma de organizar el trabajo podría ser como la mostrada en la siguiente

figura:

Ejercicio 7

En un comercio, cada día, al cerrar caja, se cuentan las cantidades recaudadas

tanto de billetes como de monedas. Crear una hoja de cálculo en donde, de

forma organizada se introduzcan las cantidades de monedas y billetes

recaudados y calcule el total euros de arqueo de caja para esa jornada.

AYUDA

Una forma de organizar este trabajo podría ser la de la imagen siguiente:



[email protected]

Ejercicio 8

Tras cerrarse el plazo de solicitud de una beca para el hogar, se dispone de

una tabla en donde se han ido introduciendo los datos de los solicitantes que

fueron recogidos mediante los correspondientes formularios de solicitud (que

podrían estar realizados con Microsoft Word).

En dicho formulario, además de solicitar los datos propios de cada persona

(nombre, dirección, código postal, provincia, etc...) se solicitan los siguientes

datos:

Fecha de nacimiento.

Fecha de solicitud.

Numero de hijos.

Nivel de ingresos.

Si tiene piso propio o no o alquilado (S/N/A).



[email protected]

Si trabaja si o no (S/N).

El sueldo, en el caso de que trabaje.

Las personas que tiene a su cargo demostrables bajo declaración

jurada.

La concesión de la beca se basa en la aplicación de unos criterios y de unas

tablas que asignan puntos a cada situación familiar. Al final cada solicitante

tendrá un número de puntos. Los que tengan 30 o más puntos, tendrán la beca

concedida y los que no lleguen la tendrán denegada.

Las tablas (INGRE, EDADES y SUELDOS) y los criterios, en los que se basa la

concesión de la beca, son los siguientes:

En las tablas anteriores los guiones equivalen a un valor cero.

Criterios para la adjudicación de los puntos:

Por cada hijo se otorga 1 punto, pero los que excedan de 3 hijos, a 2

puntos (el cuarto, quinto...)

Por cada persona a su cargo, 2 puntos, pero las que excedan de 2

(esas) a tres puntos (las otras a 2).

El nivel de ingresos se evalúa de acuerdo a la tabla de INGRESOS.



[email protected]

La edad determina también en algunos casos algún punto más según

la tabla de EDADES.

Si no tiene piso propio y es de alquiler (A) tiene 4 puntos, si es propio

(S) 0 puntos y si no tiene piso (N) se le conceden 8 puntos por este

concepto.

El sueldo determina puntos según la tabla de SUELDOS.

La beca se concede a los que tengan 30 puntos o más.

AYUDA

Se pueden crear a la derecha, columnas de trabajo, una para cada

concepto puntuable, calculando para cada solicitante en esas columnas los

puntos por hijos, por sueldo, por personas a su cargo... al final una columna

de puntos totales sobre la que se preguntará con un SI si llegan a los 30

puntos de baremo o no para presentar el literal "Beca aprobada" o bien

"Beca denegada".

En la vista anterior se han reducido las columnas B, C, D y E.



[email protected]

Ejercicio 9

Realizamos los cálculos de las nominas de los trabajadores de una empresa, en

la que se paga la antigüedad de acuerdo con los siguientes datos:

El trienio (periodo de 3 años), se paga a 18€.

El quinquenio (periodo de 5 años), a paga a 30 €.

El decenio (periodo de 10 años), se paga a 60 €.

Calcular el importe total que recibirá cada trabajador, teniendo en cuenta su

sueldo base y la fecha de alta en la empresa. Dicho importe deberá recoger por

lo tanto el sueldo base y el plus de antigüedad.



[email protected]

Los datos de los trabajadores aparecen en el siguiente cuadro:

NombreFecha de alta en la empresa

Sueldo base

María 14/01/85 1.800 €

Juan 17/12/86 1.000 €

AYUDA

No se requiere ninguna función especial. Sólo operaciones sencillas.

Tener en cuenta, que si por ejemplo un trabajador tiene un decenio ya no

se computan sobre ese tiempo ni quinquenios ni trienios.

Primero se calcularán los años de antigüedad, luego los decenios, a

partir de eso los quinquenios y al final los trienios.



[email protected]

Ejercicio 10

Vamos ahora a elaborar una nómina teniendo en cuenta los siguientes datos:

Mantenemos las mismas cantidades que en el ejercicio anterior para

el pago de la antigüedad: el trienio a 18€, el quinquenio a 30€ y el decenio

a 60€.

Existe un plus de responsabilidad dentro la nómina que se

comporta siguiendo los siguientes criterios:

o Nivel de responsabilidad menor de 3: se paga 12€.

o Nivel de responsabilidad entre 3 y 4: se paga 24€.

o Nivel de responsabilidad de 5: se paga 48€.

A partir de los datos que aparecen en el siguiente cuadro, calcular la nómina de

todos los empleados.



[email protected]

Suponemos una retención del IRPF del 16% para todos los casos.

El esquema de la nómina sería similar al siguiente:


1ceformar.edu.co/curso_aprobar/seccion 16/clase 5… · web viewa partir de la celda que contiene...

Documents