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