2013

[SEPARATA N. 01] Copyright – Ing. Carlos Lon Kan Prado

EXCEL AVANZADO PARA LOS NEGOCIOS

1

CURSO : Excel Avanzado para los negocios PROFESOR : Ing. Carlos Lon Kan Prado

LABORATORIO DIRIGIDO 01 INDICACIONES Leer todos los pasos preliminares antes de empezar a desarrollar el presente laboratorio.

La carpeta principal se llama: 01-excel-avanzado

Ejemplo Ruta-

Unidad( C )

Alumno.Utp

01-excel-avanzado

Apoyo

01-Guia.docx

Objetivo: Utilizar la herramienta Excel para representar datos de negocios.

Conceptos nuevos en Hoja de Cálculo Microsoft Excel 2007.

Definición de rangos. Utilización de funciones de búsqueda.

Ordenamiento de datos, Cuadros, Funciones aritméticas, formato condicional.

Actividad 1 (Archivo Laboratorio1.xlsx)

Abrir el archivo, elegir guardar como y colocar sus apellidos, dejarlo en la carpeta

principal.

1. En la hoja people.

Utilice opción de nombres a rangos.

a. Defina el nombre “empleados” para el rango de celdas:

- B8:B30

b. Defina el nombre “departamentos” para el rango de celdas:

- C8:C30

c. Defina el nombre “sexos” para el rango de celdas:

- D8:D30

d. Defina el nombre “salarios” para el rango de celdas:

- E8:E30

e. Defina el nombre “antigüedad” el rango de celdas:

- F8:F30

2

f. En las celdas de relleno amarillo escriba las funciones de Excel necesarias para determinar las

respuestas a cada enunciado propuesto.

g. En la hoja BDMAX. Utilice solo funciones de base de datos. En la celda H5 coloque la función que

permita determinar, el máximo salario entre aquellas personas que pertenecen al Departamento

Administrativo. Notar que los rangos incluyen los títulos.

EMPLEADO DEPARTAMENTO SEXO SALARIO ANTIGÜEDAD ADMINISTRATIVO <16

h. En la hoja BDMIN. Utilice funciones de base de datos. En la celda H5 coloque la función que permita

determinar, el menor valor de antigüedad para las personas del departamento de producción. Notar que

los rangos incluyen los títulos.

EMPLEADO DEPARTAMENTO SEXO SALARIO ANTIGÜEDAD PRODUCCIÓN M

Actividad 2 (Archivo Busqueda.xlsx)

2. Realizar la actividades de las hojas Buscar, Ventas, BuscarH y BuscarV

Actividad 3 (Archivo Personas.xlsx)

3. Abrir el archivo, elegir la opción guardar como, dejarlo en la carpeta principal y colocar de nombre el

número de este laboratorio.

a. En la hoja Profesores. Al costado de la columna fecha de nacimiento, insertar una nueva columna,

rotularla con el nombre Edad, en esta columna mostrar la edad del profesor. Utilice la función

=SIFECHA(fecha de nacimiento, HOY( ), “Y”).

b. Crear una copia de la hoja de profesores y dejarla al final de las hojas, cambiar de nombre a esta copia y

llamarla Dato1.

c. En la hoja Dato1. Construir la siguiente tabla.

La cantidad de profesores discriminados por asignatura. Tiene que utilizar funciones para obtener los

valores en la columna “Cantidad profesores”. (Utilice rangos, celdas absolutas y la función Contar.si)

Asignaturas Cantidad profesores

Biología

Física

Ingles

Italiano

Literatura

Matemáticas

Química

Total general

Actividad 4

3

4. La compañía Farmacias del Perú está evaluando crear un código especial (código del lote) para

cada producto considerando para ello la concatenación de algunos caracteres, como por ejemplo

caracteres del nombre del laboratorio, el nombre del proveedor, el nombre del producto la fecha de

producción y la fecha de vencimiento.

Utilice el archivo Excel Lotes.xlsx y trabaje en la hoja medicamentos. Guarde el documento en la carpeta

principal con el nombre Codificacion

Luego siga los siguientes pasos para crear el CODIGO PRODUCTO:

Los dos primeros caracteres del laboratorio

Colocar un guion

Los dos primeros caracteres del nombre del producto

Colocar un guion

Extraer tres caracteres del proveedor, empezar a partir de la segunda posición

Colocar un guión

Colocar el año de producción (utilice la función año)

Colocar el mes de producción (utilice la función mes)

Colocar el día de la producción (utilice la función día)

Colocar un guión

Colocar el año de vencimiento (utilice la función año)

Actividad 5

5. Se desea construir un módulo de consultas sobre los datos vinculados a un código de producto

definido. El propósito es que conociendo el código del producto se muestre automáticamente la

información del Nombre del producto, fecha de vencimiento, el nombre del laboratorio y el

nombre del proveedor.

En la hoja Información del libro Excel lotes.xlsx y siga las siguientes instrucciones:

Colocar un nombre al rango de los códigos (Los códigos de lotes que se encuentran en la hoja

medicamentos)

En la celda D5 de la hoja información aplicar validación de datos del tipo lista, aquí utilice el rango

creado en el paso anterior.

En las celdas D7, D8, D9, D10 Aplicar la función BUSCARV para obtener y mostrar los resultados

después de elegir un determinado código.

Guardar los cambios efectuados de este libro.

Finalmente guarde el archivo trabajado con el nombre medicinas.

Actividad 6

4

6. Validación de datos: (utilice el archivo Excel validación.xlsx)

a. En la hoja valor1 configure en la zona amarilla números enteros.

b. En la hoja valor2 configure en la zona amarilla números decimales.

c. En la hoja valor3 configure en la zona amarilla fechas superiores al año 1992.

d. En la hoja registro complete la tabla con las validaciones necesarias para que se cumpla:

Actividad 7

7. Utilice la hoja envíos del archivo Excel Ejercicio2.xlsx

Utilizando varias funciones de Excel, determine el costo total en la celda C8 de acuerdo a los parámetros

elegidos.

a. Tiene que configurar la celda C5 de modo que muestre una lista para elegir el tipo de Tarifa (Tarifa

1, Tarifa 2, Tarifa 3)

b. Tiene que configurar la celda C6 de modo que muestre una lista para elegir el tramo.

c. Configurar la celda C7 para encontrar la tarifa de acuerdo a los parámetros elegidos en C5 y C6.

d. Finalmente en la celda C8 se calcula con operaciones básicas el costo total.

Actividad 8

5

8. En la hoja Propio del archivo Excel Ejercicio2.xlsx, construya una pequeña base de datos con 8 nombres

de productos de vestir para luego elaborar una consulta automática (con determinados parámetros) que

muestre el precio de un producto que se va a exportar a un determinado país.

Se tienen 4 precios o tarifas diferentes por cada uno de los 8 productos locales que dependen de

la calidad y materiales de fabricación)

Considere además que son 4 los países destinos (Al extranjero).

La ruta de envío a cada uno de los cuatro países del extranjero pueden ser por vía aérea,

terrestre o marítima. A cada una de las vías se le aplica un cierto porcentaje de incremento a la

tarifa local.

Los datos de entrada para la consulta son el nombre del producto, el país destino, la ruta de

envío y el tipo de tarifa (tarifa 1, tarifa 2, tarifa 3 o tarifa 4)

Nota: Usted a buen criterio elige los nombres de productos, los nombres de países, los valores

de las tarifas y los valores de los porcentajes.

Por ejemplo aquí se muestra la vista de la consulta para el producto casaca, teniendo como país

destino Brasil, hacia una ruta terrestre, con tipo de tarifa 2 y finalmente el resultado del Precio

del producto para exportación es 144.20

Finalmente guarde este archivo con el nombre Consultas en la carpeta

principal.