Tablas Dinámicas e Inteligencia de Negocios 1

INTELIGENCIA DE NEGOCIOS Y TABLAS DINÁMICAS Una de las herramientas más potentes de Excel son las tablas dinámicas. Combinándolas con otras herramientas como formato condicional, gráficos, filtros y funciones avanzadas los tomadores de decisiones pueden convertir tus datos en información útil.

MODULO I

CONTENIDO Conceptos fundamentales .............................................................................................................2 Sistemas de información ..............................................................................................................2 Componentes de un sistema de información .....................................................................2 MICROSFOT EXCEL ............................................................................................................................3 Tablas dinámicas ...........................................................................................................................3 Tipos de Datos ................................................................................................................................4 ELABORACIÓN DE TABLAS DINÁMICAS .........................................................................................5 Caso 1 - Servicos Complementarios de Vida ..........................................................................5 Caso 2 - Fábrica de Cementos ..................................................................................................9 Caso 3 - Autolavado ................................................................................................................. 13 Caso 4 - Catering Zap ............................................................................................................... 14 Caso 5 - Sport Store .................................................................................................................... 18 Caso 6 - Inmuebles Imaco ........................................................................................................ 19 Caso 7 - Centro Comercial Vea .............................................................................................. 21 Caso 8 - Centro Comercial Soru ............................................................................................. 22 Caso 9 - Netflix y Spotify ............................................................................................................ 22 Caso 10 - Encuesta de Satisfacción ....................................................................................... 23 Caso 11 - Notas Not as ........................................................................................................................... 24 Caso 12 – Ingresos Ingresos y Gastos ..................................................................................................... 25

P á g i n a 1 | 26 MINPE INSTITUCIÓN DE FORMACIÓN Y ESPECIALIZACIÓN

CONCEPTOS FUNDAMENTALES

SISTEMAS DE INFORMACIÓN Un sistema de información es un conjunto de datos que interactúan entre sí con un fin común. En informática, los sistemas de información ayudan a administrar, recolectar, recuperar, procesar, almacenar y distribuir información relevante para los procesos fundamentales y las particularidades de cada organización. La importancia de un sistema de información radica en la eficiencia en la correlación de una gran cantidad de datos ingresados a través de procesos diseñados para cada área con el objetivo de producir información válida para la posterior toma de decisiones. COMPONENTES DE UN SISTEMA DE I NFORMACIÓN Los componentes que forman un sistema de comunicación son: 1. Entrada: por donde se alimentan los datos, 2. Proceso: uso de las herramientas de las áreas contempladas para relacionar, resumir o concluir, 3. Salida: refleja la producción de la información, y 4. Retroalimentación: los resultados obtenidos son ingresados y procesados nuevamente.

Cuando se relaciona se convierte en

Cuando se agrupan se convierte en

OBSERVACIÓN

DATO

INFORMACIÓN

CONOCIMIENTO

-Texto

-Análisis

-Aplicación

-Imágenes

-Comprensión

-Decisión

-Números

-Proceso

-Sistema

Ilustración 1- SISTEMA DE INFORMACIÓN


EJECUCIÓN

MICROSFOT EXCEL Excel, en su versión 2013 – 2016, ofrece avances en cuanto a su funcionalidad como herramienta de Negocios para todo tipo de usuarios. Una potente herramienta son las tablas dinámicas. Para aprovechar mejor las tablas dinámicas, exige por parte del usuario un conocimiento de la herramienta, pero también un conocimiento de lo quiere hacer previamente y lo más importante; que tipo de información quiere obtener al final del proceso TABLAS DINÁMICAS Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel. Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.


TIPOS DE DATOS Todo sistema de información debe tener unas variables básicas que lo componen y que debemos analizar y entender, los tipos de datos. La identificación de los tipos de datos es fundamental para la elaboración de tablas dinámicas. Tal es así que el diseño de la tabla dinámica está en función de los tipos de datos identificados en la tabla simple. De modo general y práctico tenemos los siguientes tipos de datos:

TIPOS DE VARIABLES

Temporales

Cualitativas

Cuantitativas

DESCRIPCIÓN Nos indican una temporalidad. Las variables temporales se expresan en minutos, horas, días, semanas, meses, años, etc. Las variables cualitativas nos expresan una cualidad del dato a analizar, por ejemplo, la zona, el sector, nombre de un cliente, código de un producto. Son todas aquellas variables que en cierta manera nos expresan el contenido de la información analizada, y a priori no se realizan operaciones con ellas. Las variables cuantitativas son expresadas normalmente en variables numéricas que son objeto de operaciones como pueden ser sumas, restas, etc.

EJEMPLOS

Mes Día

País Licencia de Conducir Código de Producto Nombre Apellidos

Sueldo Peso Notas

Tabla 1- Tipos de Datos

Como veremos en los siguientes ejemplos, estas variables nos permitirán realizar los diferentes tipos de análisis. Las tablas dinámicas nos van a permitir organizar, segmentar y clasificar los diferentes tipos de datos de una forma fácil y rápida.


ELABORACIÓN DE TABLAS DINÁMICAS CASO 1 - SERVICOS COMPLEMENTARIOS DE VIDA 1. Abrir el archivo MODULO I – Servicios Complementarios de Vida 2. El documento contiene registros de ventas en unidades y ventas del primer trimestre del año.

Ilustración 2 – Tablas Dinámicas

3. Identificar los tipos de variables:

TEMPORALES

CUALITATIVAS

CUANTITATIVAS


4. Seleccionar la celda A1 5. Seleccionar: Insertar - Tabla Dinámica

Ilustración 3 - Tablas Dinámicas

6. Aparecerá en la pantalla: Crear tabla dinámica 7. Esta pantalla mostrará dos decisiones: La fuente de datos. El lugar donde se desea mostrar la tabla dinámica.  



8. Presionar Aceptar 9. Una tabla dinámica ayuda a obtener información a partir de una tabla simple de datos. Es importante que los datos a trasformar en información estén organizados adecuadamente.

Filtro General

DATOS / INFORMACIÓN

Ilustración 5 – Tabla Dinámicas

10. Arrastra los campos según lo indicado:

Ilustración 6 – Tabla Dinámicas


11. Como se observa, de forma rápida se ha elaborado un resumen de la venta neta por Región 12. Dar formato a la tabla dinámica como se indica:

Ilustración 7 – Tabla Dinámica

13. Ordenar la tabla dinámica de mayor a menor como se indica:


EJERCICIO 1 Elaborar una nueva tabla dinámica que muestre la venta en unidades por Región. Pregunta: ¿Cuál es la Región de mayor venta en unidades?

EJERCICIO 2 Elaborar una nueva tabla dinámica que muestre la venta neta por mes. Pregunta: ¿En qué mes se vendió más?


CASO 2 - F ÁBRICA DE CEMENTOS 1. Abrir el archivo MODULO I – Fábrica De Cementos 2. Identificar los tipos de variables:

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

3. Seleccionar la celda A1. 4. Insertar Tabla Dinámica.

Ilustración 9 - Tabla Dinámica

5. Aparecerá en la pantalla Crear Tabla Dinámica. 6. Presionar Aceptar. 7. Crear una tabla dinámica para mostrar la producción por departamento, siga los siguientes pasos:



8. Seleccionar los datos numéricos y dar formato Estilo Millares.


9. Elaborar una tabla dinámica. Mostrar la producción semanal por fábrica. Además de podrá seleccionar el tipo de producto en el filtro general.



10. Seleccionar un estilo, Diseño


11. Elaborar una nueva tabla dinámica. Según las siguientes indicaciones:

Ilustración 14 - Tabla Dinámicas


12. Utilizar el comando de ordenar para responder las siguientes preguntas:

Ilustración 15 - Tabla Dinámica     

 

¿En qué semana se fabricaron más cemento anti salitre? ¿En qué semana se fabricaron menos cemento anti salitre? ¿En qué semana se fabricaron más cemento extra forte? ¿En qué semana se fabricaron menos cemento extra forte? ¿Qué porcentaje de cemento anti salitre se han fabricado sobre el total de la fabricación? ¿En qué semana se produjo la mayor producción en Piura? ¿Qué departamento produjo menos?


CASO 3 - AUTOLAVADO

1. Abrir el archivo MODULO I – Auto lavado. 2. El archivo registra los números de autos que han asistido a una Auto lavado, y la venta neta diaria. 3. Antes de elaborar una tabla dinámica, es necesario obtener datos que ayuden a mejorar el diseño. Sin estos datos no sería posible obtener la información. 4. Insertar dos columnas entre las columnas A y B 5. En la celda B1 escribe Mes 6. En la celda C1 escribe Año 7. En la celda B2 escribe la formula +MES(A2) 8. El resultado es el número de mes correspondiente a la fecha seleccionada. 9. Copia la formula al resto de la columna B 10. En la celda C2 escribe la formula +AÑO(A2) 11. El resultado es el año correspondiente a la fecha seleccionada 12. Copia la formula al resto de la columna C 13. Identificar los tipos de variables

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

14. Elaborar una tabla dinámica que muestre el número de visitantes por año y por mes del Auto Lavado. Seguir las siguientes indicaciones:



EJERCICIO 1 Elaborar una nueva tabla dinámica que muestre la venta neta por año y mes.

EJERCICIO 2 ¿Cuál es el día de la semana en que se han recibido más visitantes en el año 2016? Ayuda: Usar la función +DIASEM ¿Cuál es el día de la semana en que se han recibido más visitantes en el año 2017? Ayuda: Usar la función +DIASEM

EJERCICIO 3 ¿Cuál es el día de mayor venta del año 2017? CASO 4 - CATERING ZAP

1. Abrir el archivo MODULO I – Catering Zap 2. El archivo está compuesto por dos hojas: Hoja Data: contiene las compras de productos realizados a sus proveedores durante un año. Hoja Tablas: Contiene códigos y descripción de los proveedores y productos. 3. Seleccionar la Hoja Tablas, luego seleccionar la celda A1 4. Seleccionar desde A1 hasta la celda B5 5. Escribir en el cuadro : Proveedores, presionar luego ENTER 




6. Seleccionar la hoja Data 7. Escribir en la celda D2 la siguiente formula: +BURCARV(C2;Proveedores;2;Falso)


8. La fórmula BURCARV, busca el código del proveedor en el grupo creado Proveedores. Luego, muestra en la celda D2 el contenido correspondiente de la segunda columna del grupo. 9. Copiar la formula al resto de la columna D 10. Seleccionar la Hoja Tablas, luego seleccionar la celda E1 11. Seleccionar desde E1 hasta la celda G6 12. Escribir en el cuadro : Productos, presionar luego ENTER


13. 14. 15. 16. 17. 18. 19. 20.

Seleccionar la hoja Data Escribir en la celda F2 la siguiente formula: +BURCARV(E2;Productos;2;Falso) Copiar la formula al resto de la columna D Escribir en la celda H2 la siguiente formula: +BURCARV(E2;Productos;3;Falso) Copiar la formula al resto de la columna H En la celda I2 ingresa la fórmula: +H2*G2, copiar al resto de la columna Escribir en la celda J1, Mes Escribir la formula en la celda J2: +Mes(B2)


21. Elaborar una tabla dinámica. Seguir las siguientes indicaciones:


22. Dar formato de número: Millares 23. Seleccionar desde la celda B4 hasta la celda B15 24. Aplicar Formato Condicional

25. Excel muestra los valores separados en buenos (check verde), regulares (interrogación amarillo) y malos (aspas rojas).


26. Así se han combinado las tablas dinámicas y el formato condicional para obtener información. 27. Seleccionar la celda B6 de la tala dinámica Insertar Insertar Gráfico de Barras  


EJERCICIO 1 Elaborar una nueva tabla dinámica que muestre la venta por proveedor. Aplicar formato condicional para identificar el proveedor con mayor venta.

EJERCICIO 2 Elaborar una nueva tabla dinámica que muestre a la vez la venta por proveedor y por producto. Crear un gráfico.

EJERCICIO 3 Elaborar una nueva tabla dinámica que muestre a la vez la venta por proveedor; producto y mes.


CASO 5 - SPORT STORE

1. Abrir el archivo MODULO I – Sport Store 2. Crear Grupos en la hoja TABLAS y utilizar la función BUSCARV para mostrar : En la columna D de la Hoja DATA, el país. En la columna F de la Hoja DATA, la categoría. En la columna H de la Hoja DATA, el producto. 3. Identificar los tipos de variables:   

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

4. Utilizar tabla dinámicas para responder lo siguiente:

a. ¿En qué se vendió más? b. ¿En qué categoría se vendió más? c. ¿Cuál es el producto más vendido? d. ¿Cuál es el producto menos vendido? e. ¿En qué año se vendió menos? f.

¿Qué producto se vendió más en Portugal?

g. ¿Qué categoría vendió más España?


CASO 6 - I NMUEBLES IMACO

1. Abrir el archivo MODULO I – Inmueble Imaco 2. El archivo contiene registro de ventas de diferentes tipos de inmuebles; en diversas provincias y realizadas por cinco vendedores. 3. Identificar los tipo de variables:

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

4. Seleccionar desde la celda A1, insertar tabla dinámica 5. En el formulario Crear Tabla Dinámica, seleccionar la opción Hoja de Cálculo existente, elegir la celda L5. Luego presionar Aceptar



6. Preparar el resumen de ventas por vendedor, según lo siguiente:


7. Ordenar la tabla dinámica de mayor a menor. 8. Insertar en la celda L18 una tabla dinámica que muestre el monto por tipo de inmueble 9. Insertar en la celda L30 una tabla dinámica que muestre el monto por operación 10. Insertar en la celda L40 una tabla dinámica que muestre el monto por vendedor y por tipo de inmueble

EJERCICIO 1 Insertar una tabla dinámica en una nueva hija que muestre a la vez el monto por año y por tipo de operación

EJERCICIO 2 Insertar una tabla dinámica que muestre el monto por vendedor y por provincia


CASO 7 - CENTRO COMERCIAL VEA

1. Abrir el archivo MODULO 1 – Centro Comercial Vea 2. Completar las columnas sombreadas en amarillo. Utilizar las hojas Meses, Tipos_Clientes y Trabajadores para crear Grupos junto con la función BUSCARV 3. En la celda K2 escribe la función: +SI (J2<=1000;”Meno o igual a 1000”;”Mayor que 1000”) 4. Identificar los tipos de variables:

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

5. Crear tablas dinámicas: Venta por país y por trabajador 



Venta por año y por mes



Venta por tipo de cliente, país y año

6. Utilizar el campo Clasificación y Trabajador para crear una tabla dinámica, Utilizar CUENTA en Configuración de campo de Valor 7. Insertar un gráfico dinámico 8. Crear una tabla dinámica por Ventas por País y por Mes


CASO 8 - CENTRO COMERCIAL SORU

1. 2. 3. 4. 5.

Abrir el archivo MODULO I – Centro Comercial Soru Escribir en la celda F1, Clasificación Insertar en la celda F2 la fórmula: +SI(Y(D2>40;E2>50000);"Muy Bueno";"-") Copiar la formula al resto de la columna Identificar las variables

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

6. Elaborar una tabla dinámica que muestre cuántos “Muy bueno” existen por vendedor. 7. Graficar CASO 9 - NETFLIX Y SPOTIFY

1. Abrir el archivo MODULO I – Netflix y Spotify 2. Completar las dos columnas en amarillo. Utilizar la función BUSCARV y los grupos ubicados en la hoja Tablas 3. Identificar los tipos de variables

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

4. Responder las siguientes preguntas haciendo de la herramienta tablas dinámicas ¿Cuáles son los dos países con mayor producción de películas? ¿Cuál es el director/autor con mayor producción? ¿Cuántas películas y cuantos discos hay en el listado?   


CASO 10 - ENCUESTA DE SATISFACCIÓN

1. Abrir el archivo MODULO I – Encuesta de Satisfacción 2. El archivo reúne el resultado de cien encuestas de opinión. Califica de 1 a 5 puntos cada una de las respuesta 1 para muy malo o muy desacuerdo y 5 muy bueno o de acuerdo 3. Mostrar en una tabla dinámica la evaluación promedio por ciudad. 4. Mostrar los valores numéricos con dos decimales 5. Seleccionar los valores de las tres ciudades 6. Utilizar el formato condicional para mostrar los dos valores más altos 7. Seleccionar Inicio, luego:


8. Indicar el número dos. 9. Mostrar, utilizando formato condicional, el valor más bajo


CASO 11 - NOTAS

1. Abrir el archivo MODULO I – Notas 2. El archivo contiene las notas de 20 alumnos. Son notas del examen parcial y del final de tres cursos 3. Completar, utilizando Grupos y BUSCARV, las columnas: Apellido Nombre Curso 4. Identificar los tipos de variables   

TEMPORALES

CUALITATIVAS

CUANTITATIVAS

5. Ocultar columnas y filas de la tabla dinámica que no se necesitan visualizar. 6. Seleccionar Diseño, Totales Generales y Desactivado para filas y Columnas.


7. Aplicar un Estilo de Tabla Dinámica 8. En la hoja Data modificar la nota ubicada en la celda G5. Cambiar la nota 0 por 20. 9. Para actualizar los daos de la tabla dinámica: Seleccionar un valor de la tabla dinámica. Presionar click derecho del mouse. Seleccionar Actualizar.   


CASO 12 – INGRESOS Y GASTOS

1. Abrir el archivo MODULO I – Ingresos y Gastos 2. Seleccionar la celda B2 3. Seleccionar Datos, Validación de datos, Validación de Datos:


4. En el formulario de Validación de datos seleccionar Lista dentro de Permitir



5. En la celda Origen seleccionar I2 e I3


6. Presionar Aceptar 7. Copiar la celda B2 hasta la B13 8. En la celda D2 ingresar la siguiente formula: +SI (B2=”Ingreso”; C2; SI ( B2=”Gasto”; -C2;

0))

9. Llenar la comuna B y C con los datos de su preferencia 10. Crear una tabla dinámica en la misma hoja que muestre por día el Tipo_Transacción y el Monto_Validado 11. Agregar datos para dos días más: día 6 y 7 12. Actualizar la tabla dinámica ¿Se actualizo correctamente? 13. Insertar Grafico


Tablas Dinámicas e Inteligencia de Negocios 1

Recommend Documents