excel avanzado aplicado al business analytics...• tablas dinámicas y gráficos dinámicos a....

2

Titulo

(Sesión 1)

Temario• Tablas dinámicas y gráficos dinámicos

A. Controlar Bases de Datos con tablas dinámicas.

Excel Avanzado aplicado al Business

Analytics

(Sesión 1)

Curso taller en computadora preparado como apoyo a buenas

practicas profesionales y actualización en la materia de

Ofimática Aplicada a los Negocios.

Lic. Jose L. Ticona Caba

Control de Bases de Datos con Tablas Dinámicas

3

INTRODUCCIÓN A LAS

TABLAS DINÁMICAS

Representan los datos en

forma más cercana a la

intuición del usuario.

Resolver problemas

planteados en sistemas

relacionales.


Tablas dinámicas y modelos multidimensionales

4

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

Ejemplo: Análisis de ventas de autosModelo Color Volumen-ventas

Toyota Azul 6

Toyota Rojo 5

Toyota Blanco 4

Nissan Azul 3

Nissan Rojo 5

Nissan Blanco 5

Sedan Azul 4

Sedan Rojo 3

Sedan Blanco 2

M

O

D

E

L

O

Azul Rojo Blanco COLOR

5 4

5 5

3 2

6

3

4

Toyota

Nissan

Sedan

Base de Datos

Representación multidimensional

CUBOS O MATRICES

En los ejes están los

criterios de análisis.

En los cruces están los

valores a analizar.

A esta estructura se le

llama Cubo o Hipercubo.


Análisis mediante Cubos

5

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

HECHO: “El primer

trimestre de 2004 la

empresa vendió en

Valencia por un importe

de 22.000 euros del

producto tauritón 33 cl.”

INTRODUCCIÓN A LAS

TABLAS DINÁMICAS

Se pueden obtener hechos

a diferentes niveles de

agregación.


Jerarquías dimensionales

6

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

INTRODUCCIÓN A LAS

TABLAS DINÁMICAS

Se pueden obtener hechos

a diferentes niveles de

agregación.


Jerarquías dimensionales

7

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

MANEJO DE TECLADO

La tecla shift mas cursor

permite desplazarse y

hacer pintados en la hoja

calculo con el objetivo de

copiar determinadas bases

de datos.


Manejo del teclado con Ms Excel

(1) Clic en la tecla Shift(2) Clic en cualquiera de las teclas cursor

8Shift+cursor

1 21

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

MANEJO DE TECLADO

La combinación de teclas

de ctrl mas cursor permite

hacer saldos entre celdas

para desplazarse en una

base de datos.



(1) Clic en la tecla Ctrl(2) Clic en cualquiera de las teclas cursor

9

Ctrl+cursor

21

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

21

MANEJO DE TECLADO

La combinación de teclas

de control inicio o control

fin permite a diferencia del

anterior, ir al ultimo dato

de una base de datos.



(1) Clic en la tecla Ctrl(2) Clic en cualquiera de las teclas inicio o fin

10

Ctrl+inicio o fin

21

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

MANEJO DE TECLADO

Control shift cursor permite

realizar un pintado de toda

una base de datos.



(1) Clic en la teclas Ctrl+Shift(2) Clic en cualquiera de las teclas cursor

11Ctrl+shift+cursor

21

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

FUNCIÓN TEXTO

A diferencia de la función

DIA la función texto

permite extraer el en

formato literal.


Practica

(1) Abrir el archivo “Base de datos Inventarios”.(2) Crear campos jerárquicos para la dimensión “fecha compra”.

12

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

CUBO EN EXCEL

Se puede llegar a trabajar

con toda la DATA para la

creación de un cubo.


Practica

(1) Seleccionar filas y columnas para crear la tabla dinamica.(2) Clic en insertar.(3) Clic en tabla dinámica.

13

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

12

3

CUBO EN EXCEL

Para la creación de un cubo

en Excel.


Practica

(1) Clic en nueva hoja de calculo.(2) Clic en aceptar.

14

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

CUBO EN EXCEL

Los campos son los

nombres de cabecera de las

columnas de la base de

datos.


Practica

(1) Arrastrar los campos alas áreas de trabajo.

15

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1


Practica

16

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Clic sobre “suma costo total”.(2) Seleccionar configuración de

campo valor.

17

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

1

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Seleccionar suma.(2) Clic en formato de numero (cambiar el formato de numero).

18

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

11

2

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Clic derecho en cualquier lugar de la tabla en la primera columna.

(2) Seleccionar opciones tabla dinámica.

19

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Seleccionar ficha mostrar.(2) Habilitar casilla de Texto de diseño de tabla dinámica de

tipo clásica.

20

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Clic derecho.(2) Desactivar sub-total “código

item”.

21

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Clic en ficha diseño.(2) Seleccionar estilo.

22

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

1

CUBO EN EXCEL

Los campos son los



datos.


Practica

(1) Clic en analizar y acciones.(2) Clic en seleccionar toda la tabla dinámica.

23

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

GRACIASpor su atención!!!

Contactos:Jose L. Ticona Caba

Cel 72510220

[email protected]

1

Titulo

(Sesión 1)

Temario• Tablas dinámicas y gráficos dinámicos

A. Aplicar filtros a una tabla dinámica.B. Obtener promedios de una tabla

dinámica.C. Gráficos con tablas dinámicas.


Analytics

(Sesión 2)





Gráficos Dinámicos

2

SISTEMAS DE APOYO A LAS

DECISIONES (DSS)

Permiten modelar el

análisis, simulación de

toma de decisiones, crear

reportes especiales,

respuestas a consultas,

para la gerencia funcional y

media.

Un dashboard es una

visualización de la

información.


Creación de Dashboards o Sistemas para la Toma de Decisiones

3

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

DASHBOARD

Un dashboard es una


información.


Practica

(1) Abrir una nueva pestaña.(2) Crear una maquetación de Dashboard.

4

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

GRÁFICOS DINÁMICOS

El trabajo con gráficos

dinámicos es similar al

trabajo con tablas

dinámicas.


Practica

(1) Seleccionar filas y columnas para crear la tabla dinámica.(2) Clic en insertar.(3) Clic en grafico dinámico, (insertar 2 gráficos dinámicos)

5

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2


Resultado parcial con valores

6

Aplicación de filtros a Tablas Dinámicas

7

ESCALAS DE TIEMPO

En la medida que los

campos que se utilizan en

una tabla dinámica

aumentan en cantidad, el

diseño de la misma se

puede convertir en un reto

para hacer realmente el

análisis de la información y

eso es precisamente uno de

los beneficios que nos

ofrecen los Slicers (o la

segmentación de datos).


Aplicar escala de tiempo en Tabla Dinámica

(1) Clic en analizar.(2) Clic en insertar escala de tiempo.(3) Llevar a la maquetación en trimestres.

8

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

SLICERS

Filtrar los datos de una

manera mas efectiva


Segmentación de datos o slicers

(1) Clic en analizar.(2) Clic en insertar segmentación de datos.(3) Llevar a la maquetación en: (Meses y Días).

9

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2


Resultado parcial con dimensiones

10

DASHBOARD

Un dashboard es una


información.


Practica

(1) Clic en opciones. (2) Clic en conexiones de informe.

11

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

33

VINCULACIÓN CON TODAS

LAS TABLAS DINÁMICAS

Un filtro de informe solo

puede controlar una sola

tabla dinámica, pero

la segmentación de

datos funciona diferente y

es posible conectarla a

varias tablas dinámicas

para vincularlas


Practica

(1) Habilitar casilla de texto de la otra tabla dinámica. (2) Clic en aceptar (repetir los mismos pasos para los otros filtros.

12

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

21

Promedios de una tabla dinámica

13


Practica

(1) Clic derecho y seleccionar configuración de campo valor.

14

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1


Practica

(1) Seleccionar promedio.(2) Clic en aceptar.

15

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2



Cel 72510220

[email protected]

1

Titulo

(Sesión 1)

Temario• Macros

A. Introducción a Macros con la analítica de datos.

B. Crear una Macro Automáticamente.C. Ejecutar una Macro.D. Crear una macro manualmente.E. Guardar un archivo con Macros.


Analytics

(Sesión 3)





Introducción a Macros

2


Habilitar la pestaña programador

(1) Clic en archivo -> opciones -> complementos.(2) Habilitar Desarrollador -> clic en aceptar.

3

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2


Guardar el archivo habilitado para macros.

(1) Clic en Desarrollador.(2) Habilitar referencias relativas.

4

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

1

Crear una Macro

5



(1) Clic en Desarrollador.(2) Clic en Grabar Macro.

6

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

1

RECOMENDACIONES

El nombre de la macro va

sin espacios.

El método abreviado no

puede utilizar comandos

que ya se están utilizando.

Se puede guardar la macro

de 2 formas solo para el

libro o de forma personal.

Es recomendable poner

una descripción.



(1) Poner nombre a la macro sin espacios.(2) Comandos para ejecutar la macro “y”(3) Seleccionar solo este libro.(4) Escribir una descripción.

7

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

2

3

4

MACRO

Cuando se inicia la

grabadora esta se debe

detener al finalizar el

ultimo paso.


Practica

(1) Clic en detener grabación.

8

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

Ejecutar una Macro y crear la misma de VBA

9

DASHBOARD

Un dashboard es una


información.


Practica

(1) “Ctrl + y” y se ejecutara la macro automáticamente.

10

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

MACRO MANUAL

Se puede observar su

creación manual en el

código de VBA


Practica

(1) Clic en VBA.

11

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

Guardar un archivo como Macros

12

RECOMENDACIÓN

Es necesario guardar el

libro de tipo Macro para

futuros trabajos.



(1) Guardar el archivo de tipo Libro de Excel habilitado para macros.

13

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1



Cel 72510220

[email protected]

1

Titulo

(Sesión 1)

Temario• Visual Basic Aplicación VBA

A. Trabajo mediante módulos.B. Programación por funciones y

procedimientos. • Macros y VBA sobre Business Analytics

A. Crear y controlar tablas dinámicas con macros.

B. Gestionar tablas dinámicas con programación VBA


Analytics

(Sesión 4)





Trabajo mediante módulos

2

ÁRBOL DE PROYECTOS DE

VBA

Se debe de tener mucho

cuidado donde se esta

creando la función macro

debe ser dentro del archivo

de trabajo Ms Excel.

.


ENTORNO VBA

3

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

INSERTAR MODULO PARA

INICIAR PROGRAMACIÓN


VBA

(1) Click derecho sobre el archivo Excel.(2) Escoger Insertar. (3) Escoger modulo.

4

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

2

3

1

Programación por funciones y procedimientos

5

FUNCIONES

Una función devuelve el

resultado en la misma

función.

PROCEDIMIENTOS

Un procedimiento devuelve

un resultado en una

variable.


Diferencia entre procedimientos y funciones

6

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

INICIO PROCEDIMIENTO

FIN PROCEDIMIENTO

INICIO FUNCIÓN

RESULTADO

FIN FUNCIÓN

RESULTADO

FUNCIÓN SI

También conocida como

función condicional.


Repaso Función SI

(1) Crear la siguiente función condicional, que calcule un margen de utilidad del 5%, si la cantidad de productos es > a 3, caso contrario muestre un mensaje de cantidad insuficiente.

7

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

VBA

Visual Basic Aplicación es

un lenguaje de

programación estructurada

y modular.


Practica

(1) Crear la siguiente función con VBA.

8

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

PROCEDIMIENTOS CON

MACROS

Es mas sencillo crear

procedimientos con la

grabadora de macros.


Practica

(1) Crear el siguiente procedimiento con la grabadora de macros.

9

Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................

1

Macros y VBA sobre Business Analytics

10


Practica

(1) Crear la siguiente tabla dinámica.(2) Copiar la tabla dinámica como texto.(3) Calcular la suma de los costos de La Paz, Cbba y Santa Cruz si solo si el costo de

CBBA es mayor a Bs3.000 caso contrario mostrar 0 (hacer este ejercicio con la Grabadora de Macros).

11

Suma de Costo Total Etiquetas de columna

Etiquetas de fila Cochabamba La Paz Santa Cruz Total general

SWROOM 2.106 2.554 1.197 5.857

TALLER 6.494 10.714 4.090 21.298

Total general 8.600 13.268 5.287 27.155


Practica

12


Etiquetas de fila SWROOM TALLER Total general

enero 139 321 460

febrero 2.000 2.013 4.013

marzo 925 948 1.873

abril 1.643 596 2.239

mayo 92 1.424 1.516

junio 55 1.896 1.951

julio 125 2.162 2.287

agosto 102 2.755 2.857

septiembre 56 1.673 1.729

octubre 525 3.100 3.625

noviembre 25 2.359 2.384

diciembre 170 2.051 2.221

Total general 5.857 21.298 27.155

(1) Crear la siguiente tabla dinámica.(2) Copiar la tabla dinámica como texto.(3) Calcular la suma de los costos de SWOROOM y TALLER si solo si el costo de la suma de

mayo y junio de ambos almacenes supera los Bs200, si no se cumple la condion mostrar un mensaje de “INSUFICIENTE COSTEO” (hacer este ejercicio con la Grabadora de Macros).


Practica

(1) Crear la siguiente tabla dinámica.(2) Copiar la tabla dinámica como texto.(3) Calcular la suma de los costos de La Paz, Cbba y Santa Cruz si solo si el costo de CBBA es

mayor a Bs3.000 caso contrario mostrar 0 (hacer este ejercicio con VBA).

13


Etiquetas de fila Cochabamba La Paz Santa Cruz Total general

SWROOM 2.106 2.554 1.197 5.857

TALLER 6.494 10.714 4.090 21.298

Total general 8.600 13.268 5.287 27.155


Practica

14


Etiquetas de fila SWROOM TALLER Total general

enero 139 321 460

febrero 2.000 2.013 4.013

marzo 925 948 1.873

abril 1.643 596 2.239

mayo 92 1.424 1.516

junio 55 1.896 1.951

julio 125 2.162 2.287

agosto 102 2.755 2.857

septiembre 56 1.673 1.729

octubre 525 3.100 3.625

noviembre 25 2.359 2.384

diciembre 170 2.051 2.221

Total general 5.857 21.298 27.155

(1) Crear la siguiente tabla dinámica.(2) Copiar la tabla dinámica como texto.(3) Calcular la suma de los costos de SWOROOM y TALLER si solo si el costo de la suma de

mayo y junio de ambos almacenes supera los Bs200, si no se cumple la condion mostrar un mensaje de “INSUFICIENTE COSTEO” (hacer este ejercicio con VBA).



Cel 72510220

[email protected]

excel avanzado aplicado al business analytics...• tablas dinámicas y gráficos dinámicos a....

Documents