ventas del año 2015 · unidad de trabajo 6 - excel 11 jesús fernández toledo - losteatinos.es...

Unidad de Trabajo 6 - Excel

1 Jesús Fernández Toledo - losteatinos.es

Ejercicio 1

Se reciben los datos de previsión de ventas del ejercicio 2015. Pertenecen a la sucursal de Torrecilla de la Abadesa, e indican las ventas que deberá efectuar cada vendedor. Crear la siguiente estructura de entrada y calcular el TOTAL (por empleado y general), la MEDIA (mensual y semestral) y los nuevos OBJETIVOS de ventas para el año 2016 para cada vendedor (10% más que el ejercicio anterior).

Ventas del año 2015

VENDEDOR

1er Semestre 2º Semestre TOTAL

MEDIA

mensual

OBJETIVOS

año 2016

Manuel González 160.000 78.000

Juan José Olmo 85.000 95.000

Román Rodriguez 25.000 45.890

Consuelo Mangas 73.600 67.900

http://www.losteatinos.es/



Ejercicio 2

1. Iniciar Excel.

2. Copiar en la Hoja 1 del libro de trabajo, la siguiente tabla:

Alumno Curso Horario Horas Precio Hra. TOTAL

Feli MS-Windows Mañana 20 15

Jesús MS-Excel Tarde 30 25

Román MS-Word Tarde 25 15

Gaspar MS-Access Mañana 20 40

Mario MS-Access Tarde 35 40

Miguel MS-Windows Mañana 20 15

Chelo MS-Windows Mañana 20 15

José MS-Word Tarde 25 15

Manuel MS-Excel Tarde 30 25

MEDIA

3. Calcular la columna TOTAL: Precio Hra. * Horas.

4. Calcular la MEDIA de las columnas: Horas, Precio Hra. y Total utilizando la fórmula que sume cada celda y divida por el número total de alumnos.

5. Guarda el libro como ejercicio 2 en el escritorio.




Ejercicio 3

Objetivos: aprender referencias absolutas y referencias mixtas.

1. Abrir un libro nuevo de excel. 2. En la hoja de cálculo Hoja 2 crear la siguiente hoja de cálculo y

obtener los valores siguientes: la media aritmética de cada uno de los

conceptos (UNIDADES y VALOR) y sus valores MÁXIMO y MÍNIMO. La hoja debe mostrar los valores calculados con estilo millares, dos

decimales y la siguiente apariencia.

Control de Existencias

Precio Compra: 1200

Precio Venta: 1620

ENTRADAS SALIDAS EXISTENCIAS

Semana Unds. Valor Unds. Valor Unds.

1 2.000,00 56,00

2 2.500,00 200,00

3 3.000,00 1.000,00

4 3.500,00 3.000,00

5 4.000,00 1.200,00

6 2.000,00 4.000,00

7 3.000,00 400,00

8 4.000,00 100,00

9 5.000,00 2.500,00

10 6.000,00 150,00

11 7.000,00 3.400,00

12 8.000,00 6.000,00

Media

Valor Máximo:

Valor Mínimo:

3. Grabar el ejercicio como ejercicio3.xlsx y salir de Excel.




Ejercicio 4

1. Abrir el archivo Libro para ejercicio 4.xlsx ubicado en Google Drive 2. Insertar una fila entre los empleados SUYAMA y KING. Llenar dicha

fila con los siguientes datos: Apellidos: Fernández Nombre: Sonia

Cargo: Directora Tratamiento: Srta.

Fecha de nacimiento: 06-dic-1969 Fecha de contratación: 01-may-1993 Dirección: 507 - 20th Ave. E.Apt. 3A

Ciudad: Seattle Código postal: 98122

País: EE.UU. Teléfono Personal: (206) 555-9863

Extensión: 5469 3. Ordenar la base de datos por CARGO por orden descendente (Z-A). 4. Activar los filtros automáticos a la base de datos y buscar los

registros que en el campo ciudad muestren LONDRES. 5. Borrar el registro de FULLER ANDREW.

6. Utilizando filtros automáticos:

a. Listar todos los empleados del Reino Unido. b. Listar todos los Representantes de ventas y con tratamiento de

Srta.

c. Ordenar la base de datos por el cargo que ocupan los empleados por orden Ascendente.

d. Ordenar la base de datos por País y Ciudad (orden Descendente Z-A).

Importante: conforme vayamos filtrando llegará un momento que solo nos quedará un registro, por lo tanto, deshacer el filtro para poder realizar todos los apartados. Para realizar el ejercicio es

necesario el archivo Libro para ejercicio 4.xlsx




Ejercicio 5

Según los datos de la siguiente tabla, elabora las gráficas del ejercicio.

ELECCIONES

1979 1982 1986 1989 1992 1996 2000 2004 PROMEDIO

PSOE 300 500 500 500 400 450 400 500 443,75

PP/AP 400 400 400 400 500 500 500 400 437,50

IU/PCE 200 200 300 250 350 250 100 100 218,75

CDS/UCD 500 300 100 100 0 0 0 0 125,00

OTROS 150 150 300 250 100 150 200 200 187,50




Ejercicio 6

Suponemos tenemos la siguiente tabla de los gastos del primer trimestre del ejercicio 2009, en los apartados TOTAL deben ir todas las sumas

correspondientes.

1. Realizar una gráfica del estilo a la que se observa a continuación.

2. Realizar otra gráfica del estilo a la que se observa a continuación.

3. Realizar otra gráfica del estilo a la que se observa a continuación.




Ejercicio 7

Crea un libro de Excel llamado ‘Ejercicio7’ con los doce meses del año,

cada mes recogerá el número de litros de agua que ha llovido en ese mes en Mota del Cuervo.

Se pide: a) Los meses del año que no tengan 30 días deben formar un ángulo de

45º, y no como están en el dibujo.

b) Las cantidades de litros de agua del mes, y los meses deben estar

centrados en las diferentes celdas y no como están en el dibujo.

c) Las celdas de los diferentes meses del año deben estar coloreadas

con un color de fondo, el color no se puede repetir en la diferentes

celdas.

d) Insertar un dibujo encima de las celdas F7, F8, G7, G8.

e) Insertar un título encima de los 12 meses del año llamado lluvias

caídas en Mota del Cuervo en el año 2013, el título debe estar

centrado entre los 12 meses del año, además tiene que tener un

color de fondo diferente al de los meses, y la letra tiene que ser una

Tahoma a tamaño 18 y en Negrita.

f) Insertar en la celda M3 una fórmula que sume las lluvias caídas en el

año 2013.




Ejercicio 8

1) En un nuevo libro de Excel, elaborar la siguiente plantilla de datos:

2) Rellenar la columna Subtotal, la que se calcula: Cant * Precio. Unit.

3) Rellenar la columna I.V.A, la que se calcula:

Subtotal * 0.23 o Subtotal * 23% o Subtotal * 23 / 100.

4) Rellenar la columna Total, la que se calcula: Subtotal + I.V.A.

5) Rellenar la fila Total, para lo cual en la celda de la columna Subtotal,

debe mostrarse la suma de los Subtotales; en la celda de la columna I.V.A,

debe mostrarse la suma de los I.V.A; y en la celda de la columna Total,

debe mostrarse la suma de los Totales.

6) Guardar esta hoja con el nombre Artículos.

7) En una nueva hoja de Excel, elaborar la siguiente plantilla de datos:

8) Rellenar la columna Saldo, la que se calcula: Ingresos – Egresos.

9) Al final de la plantilla (en la Columna E), ingresar el texto “Comisión” a

modo de rótulo y debajo de éste calcular para cada sucursal el 5% sobre el

Saldo.

10) Calcular los totales de las columnas Ingresos, Egresos y Saldo.

11) Guardar los cambios realizados en este libro con el nombre

Ejercicio8.xls




Ejercicio 9

1. Crear un nuevo libro. 2. En la Hoja1 introducir los datos que muestra la siguiente tabla en

cuenta: a. La columna con los meses del año la rellenará utilizando el

controlador de relleno (pequeño cuadro negro situado en la

esquina inferior derecha de la selección). b. Creará una lista personalizada con los nombres de las

comunidades autónomas y, a continuación, rellenará la fila correspondiente utilizando el controlador de relleno.

c. Insertar las imágenes utilizando la carpeta de imágenes

prediseñadas que incorpora Microsoft Office.

Ventas por Comunidades

Andalucía Castilla-León Cataluña Madrid País Vasco …

Enero

Febrero

Marzo

Abril

Mayo

Junio

Julio

Agosto

Septiembre

Octubre

Noviembre

Diciembre




Ejercicio 10

1. Da al ejercicio el formato que te doy. Combinando celdas cuando lo creas necesario.

2. Al principio van dos cuadros de texto, con relleno.

3. Inserta un comentario en la Celda Artículo: que diga, los artículos comprados en Murcia. 4. Inserta al principio del documento (A la izda) el anagrama del Corte Inglés, búscalo en las

imágenes del Google en Internet.

5. Pon nombre a la hoja 1, llámala Fra Febrero.

6. Pasa el mismo formato de factura a la hoja 2, en ella haz los siguientes cálculos:

Las cantidades son las de la hoja 1, aumentadas un 5%(sin decimales).

Los precios son los de la hoja 1, aumentados un 15%(con dos decimales).

Las fechas son las de la hoja 1, aumentadas en 30 días.

7. Pon nombre a la hoja 2, llámala Fra Marzo. Y a la etiqueta le pones el color ‘rojo’. 8. Crea un gráfico de líneas por artículos, donde se represente el total de febrero y el total

de marzo.

Al gráfico ponle en título de COMPRAS, créalo en una hoja nueva llamada COMPRAS. 9. Crea un Estilo para los títulos en Negrita, Centrado Vertical y horizontalmente, color letra

AZUL oscuro, sombreado en amarillo claro, bordes en color rojo grueso, Tipo de letra Times New Román en tamaño 12., Aplica dicho estilo a las dos hojas. El estilo hay que llamarlo CORTE.

10. Las columnas de la hoja Fra Marzo tienes un Ancho de 15 ptos, si en alguna no caben los datos, hay que ampliarlas, autoajustando a la selección.

11. Pon un encabezado a la derecha de la hoja que diga Factura nº 231-05, en tamaño 10, Comic Sans MS y Negrita, con doble subrayado.

12. Pon un Pie de página centrado que diga Página 1, en tamaño 8, negrita, comic Sans MS.




Ejercicio 11. Función SI

Objetivos: aprender función SI.

Crear la estructura que muestra la siguiente tabla en un libro nuevo. Inventar tres notas a cada alumno y:

Hallar la nota media de cada alumno, el alumno que ha obtenido el promedio más alto y el más bajo. Mostrar, en la columna

Resultado, el mensaje "Aprobado" a los alumnos que obtengan promedio mayor o igual a 5 "Recupera", al resto.

En la columna Evaluaciones aprobadas:

o Mostrar “Todas”, si ha aprobado todas las evaluaciones.

o Mostrar “Ninguna”, si no ha aprobado ninguna.

o Mostrar “Alguna”, si ha aprobado alguna de las

evaluaciones.

Alumno Nota 1 Nota 2 Nota 3 Media Resultado Evaluaciones Aprobadas

a

b

c

d

e

f

g

Alumno Mejor Promedio:

Alumno Peor Promedio:




Ejercicio 12. FUNCIÓN SI

En un libro nuevo crear la siguiente tabla. En la columna “Avisar” escribir la fórmula en la celda de cada socio que muestre el mensaje “Dar aviso” si la cantidad de cuotas adeudadas es mayor o igual a 2 y el socio tiene

menos de 3 años de antigüedad y si no nada.

Nº de socio Nombre Cuotas Pendientes Antigüedad Avisar

1 Socio 1 0 1

2 Socio 2 0 5

3 Socio 3 1 6

4 Socio 4 5 3

5 Socio 5 0 1

6 Socio 6 3 20

7 Socio 7 1 2

8 Socio 8 1 12

Ejercicio 13. FUNCIÓN SI, SUMAR.SI, CONTAR.SI

Crear una hoja de cálculo con los datos, de la siguiente tabla, que detallan las características de una lista de productos. Determinar:

Cuántos productos pertenecen a la categoría A, y colocar el resultado en la celda a continuación de Nº Productos A:

No olvidar poner el resultado de Suma de Productos B.

En la columna “Precaución” se mostrarán los textos: “Frágil" para los productos de la categoría A e "Inflamable" para la categoría B y `---´

para el resto.

Código Categoría Precaución Existencias

a1 A 100,00

a2 B 45,00

a3 A 6,00

b1 C 78,00

b2 A 90,00

b3 C 234,00

c1 A 34,00

c2 B 56,00

c3 B 123,00

Nº Productos A:

Suma Productos B:




Ejercicio 14

Se dispone de los costes de un nuevo producto. Copiar los siguientes datos en un nuevo libro:

BETA, S.A.

Nombre producto: BET-CHOC

Volumen de ventas: 135.000

Precio unitario: 335 €

COSTES FIJOS

Desarrollo 1200000

Publicidad 2950000

Generales 175.000

Total

COSTES VBLES/UNIDAD

Mano obra 28,9

Materiales 9,4

Varios 4,03

Total

TOTAL COSTES

Fijos

Variables

TOTALES

RESULTADOS

Ingresos

Costes

Beneficios

Margen sobre Bº (Bº/Ctes. Tot.)

Partiendo del supuesto inicial para gastos, unidades vendidas y el precio de venta del producto, realizar las siguientes operaciones:

1. Dar nombre a las celdas que contienen los valores Volumen_de_ventas y Precio_unitario.

2. Las fórmulas empleadas en la hoja son: TOTAL COSTES FIJOS=Desarrollo+Publicidad+Generales TOTAL COSTES UNITARIOS= Mano de obra+Materiales+Varios TOTAL COSTES VARIABLES= Total coste unitario* Volumen de ventas INGRESOS = Volumen de ventas * Precio unitario

TOTAL COSTES = Total costes variables + Total costes fijos BENEFICIOS = Total ingresos – Total costes MARGEN SOBRE Bº = Beneficios / Total Costes

3. Establecer cuatro escenarios al lado de la cantidad de volumen de ventas escenarios; utilizar los siguientes valores:

EXCELENTE Volumen ventas >= 350.000 Euros

BUENO Volumen ventas >= 250.000 Euros

ACEPTABLE Volumen ventas >= 150.000 Euros

MALO Volumen ventas < 150.000 Euros




Ejercicio 15. FORMATOS CONDICIONALES

Partiendo de la siguiente tabla de origen elabora las tablas de referencia, que se adjuntan en el fichero llamado EJERCICIO 15_FORMATOS

CONDICIONALES.pdf

APELLIDO 1 APELLIDO 2 NOTA AGUILAR CAÑAS 6,1 LÓPEZ TOLEDANO 3 GARCÍA TENA 8,2 GARCÍA SANCHEZ 0,2 FUENTE SANCHEZ 2,8 FERNANDEZ MUNERA 4 CORTÉS MORALES 5,7 CASTILLO MARTÍNEZ 8,5 CASTELLANOS MARTÍNEZ 3,6 BURGO LOPEZ 6 ARTEAGA HERVÍAS 4,9 ARAGÓN CASTAÑO 3,5 ARAGÓN BALLESTEROS 4,4

Para la tabla de referencia 8: SUSPENSO <5 APROBADO >=5 Y <6 BIEN >=6 Y <7 NOTABLE >=7 Y <9 SOBRESALIENTE >=9

Ejercicio 16

Realizar el Balance con respecto a la hoja gastos e ingresos. Para realizar

este ejercicio es necesario el archivo, EJERCICIO 16.xlsx

Ejercicio 17. VALIDACIÓN DE DATOS

Realiza el ejercicio siguiente que trata sobre validación de datos, ayudándote del archivo llamado EJERCICIO 17.xlsx. Observad como se han

sacado las fórmulas de las diferentes columnas. Se pide para el ejercicio:

1.- Los clientes se deben insertar desde una lista desplegable. Estos deben ser inventados. 2.- El nombre de los productos debe ser superior a 6 caracteres e inferior a 20

caracteres. Se debe mostrar en la columna un mensaje de entrada avisando de esta característica y un mensaje de error si se incumple.

3.- Ordena la lista por el tiempo entre compras.

4.- indica la cantidad total de toneladas vendidas (T. vendidas), en cada uno de los plazos.




Ejercicio 18. SI, FORMATO CONDICIONAL

1. Haz la factura siguiente teniendo en cuenta el formato que se te da en la Hoja de Cálculo.

2. El vendedor es Almacenes Sánchez, c/ mayor 27. 30500 de Molina.

3. El Comprador es Mercadona, c/ Princesa, 27. 30000 de Murcia.

4. El nº de pedido es el 110, la fecha de pedido es el 10 de abril de 2011, la fecha de envío son 25

días después del pedido, y el nº de factura es el 115, y la fecha de factura 15 días después del

envío.

5. Los códigos representan los conceptos:

a. Si el código es “A” el concepto es coca cola.

b. Si el código es “B”, el concepto es fanta.

c. Si el código es “C” el concepto es sprite.

6. El precio depende de los conceptos:

a. Para la coca cola el precio es 0,72 €.

b. Para la fanta el precio es 0,65 €.

c. Para el sprite el precio es 0,75 €.




7. El descuento comercial depende de la cantidad comprada:

a. Si la cantidad es menor de 1000 el descuento es del 8%.

b. Si la cantidad está entre 1000 y 2000 el descuento es del 9%.

c. Si la cantidad es mayor de 2000 el descuento es del 10%.

8. Hay un descuento por pronto pago que depende de la forma de Pago:

a. Si el Pago es al contado es dto. p.p. es del 3%.

b. Si el Pago es por Cheque el dto. p.p. es del 1,5%.

c. Si el Pago es por Letra el dto. p.p. es del 0%.

9. Los gastos dependen de la forma de envío:

a. Si el envío es por Seur los gastos son 400 €.

b. Si el envío es por tren los gastos son 500 €.

c. Si el envío es por Transportes Propios los gastos son 0 €.

10. El pago es al contado, la forma de envío es por TREN y el IVA de la factura es el que se le

carga a los refrescos.

11. Crea un estilo para todos los títulos, llamado FRA 115. En Times New Román, tamaño 11,

negrita con una trama en color azul claro y los bordes gruesos en color rojo. Centrados

vertical y horizontalmente.

12. Crea un formato condicional para los conceptos:

a. La coca cola en color verde y negrita.

b. La fanta en color rojo y negrita.

c. El sprite en color azul y negrita.

13. Suma las cantidades de coca cola, fanta y sprite con la función sumar.si.

14. Realiza toda la factura utilizando fórmulas, funciones, y que el aspecto quede como yo te la

doy combinando y ajustando texto cuando lo consideres necesario.

15. Haz un gráfico de barras que represente los importes brutos y los netos de la factura por

productos. En una hoja nueva.

16. A la hoja de la factura llámala factura 115 y a la hoja del gráfico llámala Grafico productos.

17. Crea un encabezado en la hoja factura poniendo tu nombre y apellidos a la derecha.

18. Crea un pie de página en la hoja factura poniendo a la izquierda Página = (inserta el nº de

página).

Ejercicio 19. Tablas Dinámicas

Abre el ejercicio Tablas Dinámicas. 1ª Parte. Sucursales.xlsx y realiza las siguientes tablas dinámicas.

1. ¿Cuánto dinero se ha ingresado en cada sucursal? 2. ¿Cuánto dinero se ha ingresado por cada tipo de cuenta?

3. ¿Cuál es la diferencia de ingresos por cuentas del año 2010 al 2011? 4. ¿Cuál es el porcentaje sobre el total del dinero ingresado que le

corresponde a cada sucursal?




Ejercicio 20

La cooperativa “Vinícola de Tomelloso”, necesita realizar tres tipos diferentes de facturas. Con la ayuda de las plantillas definidas en Excel,

elabora las siguientes facturas.

1ª FACTURA

Esta factura se emite el día 25/11/2014, para un agricultor llamado Pedro

López, que ha llevado a la cooperativa: 20.000 Kg de uva cencibel, y se le paga a un precio de 0,40 € Kg.

10.000 Kg de uva airen, y se le paga a un precio de 0,25 € Kg.

60.000 Kg de uva bobal, y se le paga a un precio de 0,30 € Kg.

Todas las cantidades llevan aplicadas un 4% de IVA 2ª FACTURA

Un camión de la empresa J García Carrión se lleva día 22/09/2014, 22000

litros de mosto de la variedad garnacha, esta variedad la cobra la cooperativa a 0,60 € litro. A esta factura se le aplica el 10% de IVA.

3ª FACTURA

Un restaurante de Ciudad Real llamado ILFORNO, compra a esta cooperativa el día 22/10/2014, las siguientes cantidades de botellas, al 21% de IVA:

200 botellas de vino blanco “Añil”, al precio de 2,50 € por botella.

150 botellas de vino tinto crianza “Torre de Gazate”, al precio de 6 € por botella.




Ejercicio 21

De acuerdo a un informe volcado a una tabla, el director general de la

empresa “Arcos”, quiere saber en qué meses tuvo pérdidas o ganancias.

1. Para lo cual estos datos se ponen en una Hoja de Excel y se usa la

función SI en la columna estado para saber en qué meses ha tenido

ganancias o pérdidas.

Arrastrando la función hasta completar, el resultado final es:




2. Realizar un gráfico de barras comparando los ingresos con los gastos

por meses.

Ejercicio 22

Objetivo ejercicio: creatividad

Tenemos una tienda de motos, y la tarde se ha dado bastante bien y le hemos vendido a un cliente los siguientes artículos, los precios están sin

IVA: - Ducati Monster 696 roja precio 6896 €

- Casco Shoei XR-1100 precio 299 €

- Mono Dainese Traje Piel Crono Div precio 483 €

- Guantes Alpinestars precio 34 €

1. El nombre del cliente y el nombre de la tienda de motos tienen que ser

inventados y hay que ponerlos en la cabecera de la factura.

2. A todos los artículos hay que aplicarle un IVA de un 21%.

3. Si el cliente paga al contado ya sea en metálico o con tarjeta se le

aplicará un descuento de un 10%, y si paga a plazos un descuento de

un 2%. (HACERLO CON UNA FUNCIÓN SI)

4. En PAGO realizar un desplegable pudiendo elegir el tipo de pago que

realiza el cliente, como se observa en la figura.




Ejercicio 23

En la siguiente tabla se pueden observar las faltas correspondientes al Módulo 2 de un determinado curso que se compone de 60 horas, impartido

en el mes de abril. Diariamente se imparten 5 horas de clase.

El número 5 que puedes observar en las celdas equivale a las horas que falta un alumno al día, es decir, si la celda contiene un 5, el

alumno falta ese día, un día completo. La columna FALTAS MOD 2 corresponde a la suma de las faltas de

cada alumno.

1. Genera una hoja con el formato que te doy.

2. Crea formato condicional en la columna FALTAS MOD 2 que resalte las

celdas como se observa en la figura de los alumnos que hayan

superado un 25 % de las faltas.

3. Crea una columna que muestre “NO SUPERA FALTAS”, “SUPERA

FALTAS”, con una función SI, dependiendo si el alumno se ha pasado

del 25% de asistencia.




Ejercicio 24

Una agencia de turismo ofrece comisiones a sus vendedores, de

acuerdo al sector que pertenezcan. Completar la tabla de datos de acuerdo a las consignas que aparecen debajo de la misma.

1. Al rango A4:D4 aplicarle: a. Combinar, centrar, negrita y relleno de color azul claro.

b. Aumentar tamaño de fuente a 14 Comic Sans.

2. Al rango A10:D10 aplicarle: a. Negrita, centrar.

b. Relleno de color azul claro .

3. Al rango A10:D18 aplicarle todos los bordes.

4. Colocar los montos de venta en formato Moneda €.

5. COMISIÓN: calcular la comisión que corresponda de acuerdo al

sector que pertenezca. Pista: Utilizar función SI

6. TOTALES: calcular el total del monto de ventas y el total de comisiones.

7. Resolver lo pedido en cada caso en las celdas A21 hasta A26.




Ejercicio 25

Objetivo: creatividad

Diversos clientes compran de INCARLOPSA los productos de la lista adjunta.

producto precio/ unidad

Jamón Serrano de Hembra (Vitola azul) 1,99 €

Jamón Adobado (Vitola roja) 3,98 €

Jamón Gran Reserva (Vitola Negra) 4,27 €

Jamón Serrano de Hembra, 12 lonchas 6,36 €

Taquitos de Jamón Curado 7,95 €

Taco de Jamón de Hembra 4,26 €

Hueso Garrón 2,07 €

Elabora una factura en Excel en la que se reflejen las siguientes

columnas, poner alrededor de 6 filas de pedidos de clientes, los clientes deben ser inventados.

Cliente Producto Unidades Precio TOTAL

Se pide:

1. La columna Cliente se debe configurar para que la longitud del texto

sea superior a 5 caracteres e inferior a 30 caracteres. Además se debe poner un mensaje de entrada con título ‘Mensaje de entrada’ avisando

de esta característica, y un mensaje de error con título ‘grave’ y estilo grave avisando también de esta característica. Los clientes se deben introducir manualmente.

2. Los productos se deben obtener a partir de una lista desplegable, el

origen de datos debe estar ubicado en una hoja diferente a la de la factura.

3. Las unidades deben ser aleatorias entre 1 y 100, es decir, generarlas con una función.

4. El IVA que se aplicará a la factura será del 10% a todos los artículos.




Ejercicio 26. BUSCAR, BUSCARV

26.1. Función BUSCAR

Nuestra empresa, dedicada la distribución y venta de bebidas refrescantes, ha decidido (como método de promoción y vía de investigación de mercado)

premiar a aquellos consumidores que envíen las etiquetas de los refrescos de dos litros a un determinado apartado de correos.

Abre un nuevo Libro de Excel y llámalo EJERCICIO 26.

La tabla de correspondencia de premios, que copiarás en el rango A9:B13 de la Hoja 1, es la siguiente:

Nº de puntos Premio

500 Una camiseta y una bolsa deportiva

1000 Un walkman con auriculares

2000 Una torre de música

4000 Un ordenador de sobremesa

Al cabo de un mes se elabora la lista de los primeros ganadores, incluyendo

los puntos obtenidos por cada uno y el premio que les corresponde. Esta lista, antes de introducir los premios conseguidos por los ganadores, presenta la siguiente apariencia:

Ganador Nº de puntos Premio

Antonio Buesa Fernández 600

Catalina Lago Herrera 1200

Roberto Suárez Vega 900

Luis Ferrer Mas 2100

Ana Sánchez Torres 500

José Alonso Parra Oliver 4050

Se trata de confeccionar dicha lista, en el rango A1:C7 de la Hoja 1, de

modo que el premio conseguido por cada ganador aparezca automáticamente en la tercera columna sólo con introducir el nº de puntos obtenido.

PROCEDIMIENTO:

Para esto será necesario recurrir a la función BUSCAR. Esta función busca la correspondencia con el valor de una tabla en otra tabla distinta. Es útil

siempre que en la segunda tabla sólo haya una correspondencia para cada valor; en nuestro caso, a cada nº de puntos corresponde un solo premio.

Una vez copiadas las tablas indicadas más arriba, sitúate en la celda C2. Activa el asistente para funciones y selecciona, en Categorías de funciones,

Búsqueda y referencia, y en “Nombre de la función”, la función BUSCAR. En




el cuadro de diálogo "Seleccionar argumentos" selecciona los argumentos

"valor_buscado;matriz".

En el argumento "valor_buscado", selecciona la celda B2 (que contiene el nº de puntos obtenido por el ganador).

En el argumento "matriz", selecciona el rango de celdas A10:B13 (donde se establecen las correspondencias de nº de puntos con premios.

Pulsa INTRO y en la celda C2 aparecerá el premio correspondiente. Cuando la función no encuentra en la matriz seleccionada ningún valor coincidente

con el que hemos introducido, selecciona el que más se le aproxima por abajo (p.ej, considerará que lo más aproximado a 900 es 500).

Para poder copiar esta fórmula a las celdas C3 a C7 es necesario convertir

la referencia a la matriz en una referencia absoluta; por tanto, deberás

modificar la fórmula para que quede así: =BUSCAR(C2;$A$10:$B$13).

También funcionaría con referencias mixtas: =BUSCAR(C2;A$10:B$13).

Una vez modificada la fórmula, cópiala a las celdas C3 a C7.

Ahora, introduce los datos "nombre y apellidos" y "nº de puntos" en el rango A2:B7 y observa cómo se introducen automáticamente los premios

correspondientes.

26.2. Función BUSCARV

Esta función es necesaria en aquellos casos en que la matriz en la que realizamos la búsqueda tiene más de 2 columnas (o filas). En tales casos,

se ha de indicar en qué columna (BUSCARV) o fila (BUSCARH) se ha de buscar la correspondencia que queremos

Supongamos que en el ejercicio anterior, en la tabla de correspondencias se

incluyen los datos relativos a tres promociones diferentes:

Nº de puntos

Premios prom. 1 Premios prom. 2 Premios prom. 3

500 Una camiseta y una bolsa deportiva

Una entrada para el cine

Una suscripción a la revista "Pronto"

1000 Un walkman con auriculares

Una entrada para el teatro

El libro "Mil recetas de cocina"

2000 Una torre de música Una entrada para el fútbol

Una vajilla completa

4000 Un ordenador de sobremesa

Una entrada para la ópera

Un viaje a París para dos personas

Aprovechando los nombres de antes y el nº de puntos, supondremos que,

en lugar de participar en la promoción 1 lo han hecho en la promoción 2.




Cambia a la Hoja 2 del Libro activo, haciendo clic sobre la pestaña correspondiente

a la Hoja 2.

Los datos se dispondrán del mismo modo que en el ejercicio anterior.: por tanto,

copia el contenido del rango A1:C7 de la Hoja 1 en el mismo rango de celdas de la

Hoja 2. Haz lo mismo con el rango A9:B13. Luego, cambia (en la Hoja 2) esta

última tabla hasta que tenga el aspecto de la tabla con las tres promociones.

Sitúate en la celda C2 y activa el asistente para funciones.

En Categorías de funciones, selecciona Búsqueda y referencia. En Nombre de la

función, selecciona BUSCARV

En el argumento Valor_buscado, selecciona la celda B2.

En el argumento Matriz_buscar_en, selecciona el rango A10:D13

En el argumento Indicador_columnas, escribe 3 (es decir, la tercera columna de la

matriz), dado que vamos a asignar premios de la promoción 2

En el argumento Ordenado, no es necesario que introduzcas nada

Pulsa INTRO.

Una vez más, para poder copiar la fórmula a las celdas contiguas será necesario

convertir la referencia a la matriz en una referencia absoluta (o mixta) del modo ya

visto antes en el ejercicio 26.1.

Ejercicio 27. BUSCARV o CONSULTAV

Crea la siguiente plantilla, de modo que introduciendo el código de cliente, automáticamente aparezca en nombre, apellidos y saldo del cliente.

INTRODUZCA CÓDIGO A-003

CLIENTE Juana López Ogaván

SALDO 850,00 €

LISTADO DE CLIENTES

CÓDIGO NOMBRE APELLIDOS SALDO

A-000 Antonio Morales Capilla 15,00 €

A-001 Andrés Sánchez Tejedor 53,00 €

A-002 Teresa Melero Serrano 250,00 €

A-003 Juana López Ogaván 850,00 €

A-004 Horcio Mancilla Pérez 150,00 €

A-005 Marco Pérez Torres 357,85 €

A-006 Susana Amador Toledo 1,25 €

A-007 Alegría Ocón Ramos 8,77 €

A-008 María del Carmen Pinos Montes 33,15 €




Ejercicio 28

Crea en siguiente modelo de pedido.

HERMANOS LÓPEZ

C/ Romero, 90

41042 SEVILLA

PEDIDO Nº

FECHA:

Cód. destinatario

Destinatario:

CONDICIONES

Forma envío Plazo entrega

Forma pago Lugar entrega

Cantidad Artículo Precio unit. Importe total

En la hoja2 (llamada clientes), crea la siguiente tabla de correspondencias:

Código destinatario

Destinatario Forma envío

Forma pago

Plazo entrega

Lugar entrega

C-001 Talleres Ramírez

Aéreo Al contado

24 hs. Fábrica

C-002 Mayoristas Centrales Camión

Aplazado (30

d./vta.)

3 días Almacén

C-003 El dedal, SL

Tren Al

contado 2 días Almacén

A continuación, en las celdas del modelo de pedido correspondientes a los datos de Destinatario, Forma envío, Forma pago, Plazo entrega y

Lugar entrega introduce funciones BUSCARV de forma que al escribir el código del destinatario aparezcan automáticamente los datos

correspondientes a dicho código.

En este caso, dado que la tabla de correspondencias no está ordenada,

deberás hacer uso del 4º argumento de la función.




Ejercicio 29. CORRESPONDENCIA

Ayuntamiento de Murcia

Dpto. de Recaudación Especial

«Apellido», «Nombre»

«Dirección1» «CódigoPostal» «Localidad»

Murcia, 4 de agosto de 2015

Estimado amigo/a «Nombre»:

Ante la demora en el abono de sus impuestos municipales, nos vemos

obligados a notificarle que en caso de no hacer efectiva su deuda antes del día 15

del próximo mes, no tendremos más remedio que proceder judicialmente por

el embargo de su vivienda sita en la localidad de «Localidad»

Saludos,

A. Guillotinez

Recaudador Especial

Datos para combinar:

Nombre Apellido Dirección1 Código Postal

Localidad

Antonio García Sánchez Avda. Los Pinos, s/n 30002 Murcia

Luis Gómez Manrique María Campó, 34 30012 Los Garres

Ana María

Ruiz López San Eloy, 1, 1ºA 30009 Murcia




Ejercicio 30

Debe aparecer un mensaje en la columna LLAMAR en la celda de los clientes que hayan excedido más de la “FECHA DE COBRO EN DÍAS” sin

pagar sus facturas.

El mensaje que debe aparecer debe ser "LLAMAR" y hay que ponerle un formato condicional.

NOMBRE APELLIDOS IMPORTE FACTURA

FECHA EMISIÓN FACTURA

LLAMAR FECHA

DE COBRO EN DÍAS

FECHA HOY

PEPE FERNÁNDEZ PÉREZ 3.456,98 € 12/04/2015 LLAMAR 90 17/05/2016

JOSE LÓPEZ AGUILAR 234,56 € 05/08/2015 LLAMAR LUIS RODRÍGUEZ NUÑEZ 9.578,65 € 05/05/2016 JUAN RÓDENAS MARTÍNEZ 4.567,50 € 05/03/2016

Los datos de las columnas NOMBRE, APELLIDOS, IMPORTE FACTURA Y FECHA EMISIÓN FACTURA, se deben importar automáticamente de los libros de las diferentes facturas.


ventas del año 2015 · unidad de trabajo 6 - excel 11 jesús fernández toledo - losteatinos.es...

Documents