2013

[SEPARATA N. 08] 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 08 INDICACIONES

Leer todos los pasos preliminares antes de empezar a desarrollar el presente laboratorio.

La carpeta principal se llama: 08-excel-avanzado

Ejemplo Ruta-

C:/Alumno.Utp

08-excel-avanzado

Apoyo

08-Guia.docx

Objetivo: Subtotales, consolidación de datos, tablas y gráficos dinámicos.

ACTIVIDAD 1

1. Utilizar el archivo Excel Subtotales elegir guardar como, dejarlo en la carpeta principal y colocar de

nombre el CODIGO de alumno:

La opción subtotales se encuentra en la ficha DATOS

a. En la hoja Subtotal 1: Obtener el total del salario por departamento

Procedimiento:

- Primero: Ordenar por el campo que se desea totalizar, en este caso es departamento.

- Segundo: Utilizar la opción subtotales, aquí se completa los siguientes parámetros en la

ventana de dicha opción.

2

-

Y finalmente elegir aceptar.

Departamento Sección Salario

Total Administración

$608,893

Total Contabilidad

$421,873

Total Diseño

$241,347

Total I + D

$495,781

Total Ingeniería

$1,863,396

Total Mercado

$1,242,747

Total general

$4,874,037

b. En la hoja Subtotal 2: Obtener el promedio del salario por sección.

Sección Salario

Promedio Copiadoras $43,906

Promedio Fax $41,509

Promedio Impresoras $42,747

Promedio general $42,755

c. En la hoja Subtotal 3: Obtener el total del salario por departamento y sección. Notar aquí que se

eligen dos campos, departamento y sección. Se realizan dos veces el procedimiento. Empezar

primero por ordenar ambos campos, teniendo prioridad departamento. Luego realizar dos veces el

procedimiento de subtotales.

3

ACTIVIDAD 2

2. Abrir el libro Excel llamado informacion.xlsx, elegir la opción guardar como, dejarlo en la carpeta

principal, poner de nombre sus APELLIDOS.

La opción tabla dinámica se encuentra en: Insertar, grupo Tablas:

Elegir Tabla dinámica

4

Esta ventana se genera por default y con los campos llenos.

3. Utilice la hoja Seres vivos.

a. Crear una tabla dinámica que muestre la cantidad de

animales por especie (contar). En una hoja nueva.

Rótulos de fila Cuenta de PROCEDENCIA

BOVINO 10

EQUINO 9

OVINO 14

Total general 33

5

Crear el gráfico siguiente tomando los valores de la tabla dinámica. Elija tipo circular seccionado

3D.

b. Crear una tabla dinámica que muestre la cantidad de animales por raza. En una hoja nueva.

Rótulos de fila Cuenta de PROCEDENCIA

ARABE 3

AVEERDEN ANGUS 2

CORRIEDALE 4

CRIOLLO 5

CUARTO MILLA 1

HEREFORD 3

HOLANDO 3

IDEAL 4

LINCOL 1

MERINO 3

SHORTON 2

TEXEL 2

Total general 33

Crear el gráfico tomando como valores la tabla dinámica. Elija tipo línea apilada con marcadores.

BOVINO 30%

EQUINO 27%

OVINO 43%

Porcentaje de existencias según la especie

6

c. Crear una tabla dinámica que muestre el total de las cotizaciones por tipo de sexo.

Rótulos de fila Suma de COTIZACION U$

HEMBRA 41253

MACHO 43838

Total general 85091

Crear el gráfico tomando como valores la tabla dinámica. Elija columna agrupada y estilos 44.

ACTIVIDAD 3

4. Utilice la hoja profesores y resuelva los siguientes enunciados

3

2

4

5

1

3 3

4

1

3

2 2

Cantidad de animales por raza

Total

41253

43838

39500

40000

40500

41000

41500

42000

42500

43000

43500

44000

44500

HEMBRA MACHO

Importe total de las cotizaciones por tipo de sexo

Total

7

a. Crear una tabla dinámica que muestre la cantidad de profesores por categoría y por curso.

Cuenta de Nombre Rótulos de columna

Rótulos de fila Interino Suplente Titular

Total general

Biologia 5 6 7 18

Fisica 8 12 4 24

Ingles 6 11 4 21

Italiano 6 7 8 21

Literatura 6 6 9 21

Matematicas 9 12 5 26

Quimica 7 9 5 21

Total general 47 63 42 152

Crear el siguiente gráfico. Elija columna agrupada 3D, estilo 2.

b. Crear una tabla dinámica que muestre la cantidad de colegios por ciudad.

Rótulos de fila Cuenta de Colegio

Arequipa 2

Cajamarca 14

Chimbote 9

Cuzco 24

Huancavelica 13

Huancayo 16

Iquitos 5

Loreto 9

Moquegua 18

Tacna 28

Trujillo 14

Total general 152

Crear el siguiente gráfico. Elija tipo línea

5 8

6 6 6 9

7 6

12 11

7 6

13

9 7

4 4

8 9

4 5

Cantidad de docentes por categoría y por curso

Interino Suplente Titular

8

c. Crear una tabla dinámica que muestre el promedio de sueldos por asignatura en orden descendente.

Rótulos de fila Promedio de Sueldo

Matematicas 480.77

Biologia 486.11

Ingles 488.10

Italiano 500.00

Fisica 510.42

Literatura 511.90

Quimica 547.62

Total general 503.29

Crear el siguiente gráfico. Elija barra agrupada.

28 24

18 16

14 14 13 9 9

5 2

0

5

10

15

20

25

30

Cantidad de colegios por ciudad en orden descendente

Total

440.00 460.00 480.00 500.00 520.00 540.00 560.00

Matematicas

Biologia

Ingles

Italiano

Fisica

Literatura

Quimica

Promedio de sueldos por asignatura en orden ascendente

Total

9

ACTIVIDAD 4

5. Utilice la hoja ventas y resuelva los siguientes enunciados.

a. Crear una tabla dinámica que muestre el promedio en metros cuadrados por zona y por servicio.

Promedio de Nro. Baños Rótulos de columna

Rótulos de fila Baño

Baño y Ducha Ducha Total general

Centro 1.70 3.18 2.64 2.69

Este 2.75 3.36 2.00 2.75

Norte 1.86 3.00 2.54 2.48

Oeste 2.33 3.50 2.23 2.56

Sur 2.86 3.44 2.15 2.70

Total general 2.32 3.29 2.33 2.63

Crear el siguiente gráfico tomando como base la tabla dinámica. Elija agrupado por barras y

finalmente seleccione colores.

1.70

2.75

1.86

2.33

2.86

3.18 3.36

3.00

3.50 3.44

2.64

2.00

2.54

2.23 2.15

Centro Este Norte Oeste Sur

Promedio del número de habitaciones por servicios y por zona

Baño Baño y Ducha Ducha

10

Ejercicios:

b. Crear una tabla dinámica que muestre el total del precio por zona.

c. Crear una tabla dinámica que muestre el total del precio por distrito.

d. Crear una tabla dinámica que muestre el total de metros cuadrados construido y el total del precio

por distrito.

e. Crear una tabla dinámica que muestre la cantidad de metros cuadrados construido y el total del

precio por zona y por distrito.

f. Crear una tabla dinámica que muestre la cantidad de habitaciones, el total de metros cuadrados y el

precio total por zona y por distrito.

ACTIVIDAD 5

Resolver las aplicaciones:

1. Archivo: Aplicaciones-01

2. Archivo: Aplicaciones-02