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
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
Jerarquías dimensionales
6
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
INTRODUCCIÓN A LAS
TABLAS DINÁMICAS
Se pueden obtener hechos
a diferentes niveles de
agregación.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
Manejo del teclado con Ms Excel
(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.
Lic. Jose L. Ticona Caba
Manejo del teclado con Ms Excel
(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.
Lic. Jose L. Ticona Caba
Manejo del teclado con Ms Excel
(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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
Practica
(1) Arrastrar los campos alas áreas de trabajo.
15
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
CUBO EN EXCEL
Los campos son los
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
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
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
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
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
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
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
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
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
Practica
(1) Clic derecho.(2) Desactivar sub-total “código
item”.
21
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
CUBO EN EXCEL
Los campos son los
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
Practica
(1) Clic en ficha diseño.(2) Seleccionar estilo.
22
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
1
CUBO EN EXCEL
Los campos son los
nombres de cabecera de las
columnas de la base de
datos.
Lic. Jose L. Ticona Caba
Practica
(1) Clic en analizar y acciones.(2) Clic en seleccionar toda la tabla dinámica.
23
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
2
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.
Excel Avanzado aplicado al Business
Analytics
(Sesión 2)
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
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.
Lic. Jose L. Ticona Caba
Creación de Dashboards o Sistemas para la Toma de Decisiones
3
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
DASHBOARD
Un dashboard es una
visualización de la
información.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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
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).
Lic. Jose L. Ticona Caba
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
Lic. Jose L. Ticona Caba
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
DASHBOARD
Un dashboard es una
visualización de la
información.
Lic. Jose L. Ticona Caba
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
Lic. Jose L. Ticona Caba
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
Lic. Jose L. Ticona Caba
Practica
(1) Clic derecho y seleccionar configuración de campo valor.
14
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
Lic. Jose L. Ticona Caba
Practica
(1) Seleccionar promedio.(2) Clic en aceptar.
15
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
2
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.
Excel Avanzado aplicado al Business
Analytics
(Sesión 3)
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
Lic. Jose L. Ticona Caba
Habilitar la pestaña programador
(1) Clic en archivo -> opciones -> complementos.(2) Habilitar Desarrollador -> clic en aceptar.
3
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
2
Lic. Jose L. Ticona Caba
Guardar el archivo habilitado para macros.
(1) Clic en Desarrollador.(2) Habilitar referencias relativas.
4
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
2
1
Lic. Jose L. Ticona Caba
Guardar el archivo habilitado para macros.
(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.
Lic. Jose L. Ticona Caba
Guardar el archivo habilitado para macros.
(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.
Lic. Jose L. Ticona Caba
Practica
(1) Clic en detener grabación.
8
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
DASHBOARD
Un dashboard es una
visualización de la
información.
Lic. Jose L. Ticona Caba
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
Lic. Jose L. Ticona Caba
Practica
(1) Clic en VBA.
11
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
RECOMENDACIÓN
Es necesario guardar el
libro de tipo Macro para
futuros trabajos.
Lic. Jose L. Ticona Caba
Guardar el archivo habilitado para macros.
(1) Guardar el archivo de tipo Libro de Excel habilitado para macros.
13
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
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
Excel Avanzado aplicado al Business
Analytics
(Sesión 4)
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
Á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.
.
Lic. Jose L. Ticona Caba
ENTORNO VBA
3
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
INSERTAR MODULO PARA
INICIAR PROGRAMACIÓN
Lic. Jose L. Ticona Caba
VBA
(1) Click derecho sobre el archivo Excel.(2) Escoger Insertar. (3) Escoger modulo.
4
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
2
3
1
FUNCIONES
Una función devuelve el
resultado en la misma
función.
PROCEDIMIENTOS
Un procedimiento devuelve
un resultado en una
variable.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
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.
Lic. Jose L. Ticona Caba
Practica
(1) Crear el siguiente procedimiento con la grabadora de macros.
9
Apuntes:…………………………………………………………………………………………………………………………………………………………………………………………………………............................................................................................................................................
1
Lic. Jose L. Ticona Caba
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
Lic. Jose L. Ticona Caba
Practica
12
Suma de Costo Total Etiquetas de columna
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).
Lic. Jose L. Ticona Caba
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
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
Lic. Jose L. Ticona Caba
Practica
14
Suma de Costo Total Etiquetas de columna
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).