tab din y pp oct 2018... · tablas dinámicas e introducción a powerpivot impartido por francisco...

www.fcervantes.es

© Francisco Guillermo Cervantes Medina

http://www.fcervantes.es

Tablas dinámicas e introducción a PowerPivot

Impartido por Francisco Guillermo Cervantes Medina

4 y 9 de octubre de 2018


Comencemos…


Contenidos

1. Funciones básicas2. Tablas dinámicas3. Introducción a PowerPivot

© Francisco Guillermo Cervantes Medina4

11 7 Funciones básicas


1. PROMEDIO2. CONTAR3. CONTARA4. MAX5. MIN6. PRODUCTO7. DESVEST.M8. DESVEST.P9. SUMA10. VAR.S11. VAR.P

6 © Francisco Guillermo Cervantes Medina

11 7 Funciones básicas

¿Por qué estas 11 funciones básicas?1. Porque su uso es bastante frecuente en el cálculo de

variables relevantes en la gestión; las iremos aplicando a lo largo de los ejercicios del curso.

2. Porque las utilizaremos como punto de partida y ejercicios para familiarizarnos con:

a. La sintaxis de las funciones de Excel y b. Con su introducción directa en la hoja de cálculo.

3. Porque son el punto de partida para entender y trabajar con:a. La función SUBTOTALESb. Las Tablas Dinámicas


7 Funciones básicas


9. SUMA:

Sintaxis:

= SUMA (número1; [número2]; ...)

Devuelve la suma todos los números especificados como argumentos.

Cada argumento puede ser un rango.

Argumentos:a. Información que necesita la función para realizar la operación

y/ob. Datos sobre los que se va a realizar la operación.

Rango: dos o más celdas de una hoja. Pueden ser adyacentes o no.



1. PROMEDIO:

2. CONTAR: Devuelve la cantidad de celdas que contienen números. Es decir, cuenta las celdas que contienen un valor numérico.

Sintaxis:

= CONTAR (valor1; [valor2]; ...)

푃푟표푚푒푑푖표 = 푋 =∑ 푋푛

Sintaxis:

= PROMEDIO (número1; [número2]; ...)

Devuelve la media simple de una serie de números.



3. CONTARA: Devuelve la cantidad de celdas no vacías en un rango. Es decir, en un rango, cuenta las celdas que contienen algún valor.

Sintaxis:

= CONTARA (valor1; [valor2]; ...)

4. MAX: Devuelve el valor máximo de un conjunto de valores o rango.

Sintaxis:

= MAX (número1; [número2]; ...)



5. MIN: Devuelve el valor mínimo de un conjunto de valores o rango.

Sintaxis:

= MIN(número1; [número2]; ...)

6. PRODUCTO: Devuelve el producto de todos los números proporcionados como argumentos.

Sintaxis:

= PRODUCTO(número1; [número2]; ...)

Ejercicio 0, Parte 2: Preguntas

a) ¿A cuánto ascienden los mayores adeudos de esta empresa (aquéllos mayores a 5.000 €)?

b) ¿Con qué proveedores tiene esta empresa sus mayores adeudos?

c) ¿Con quién de estos proveedores tengo abonos pendientes y a cuánto ascienden dichos abonos?


Contenidos



Ejercicio 5a – Tablas dinámicas


1. Emular la segunda parte del ejercicio 2b usando una tabla dinámica.

Tablas dinámicas


1. ¿Qué es una tabla dinámica?Es una herramienta para manipular fácilmente una base de datos para su análisis.

Una imagen vale más que mil palabras…

Tablas dinámicas


1. Posicionar el cursor en cualquier sitio de la tabla que queramos dinamizar.

2. Verificar que el rango de la tabla es correcto.3. Elegir el posicionamiento de la tabla dinámica

(opción sugerida: nueva hoja de cálculo).

Tablas dinámicas


Tablas dinámicas


• Información presentada en formato muy resumido

• Nos permite visualizar los criterios de búsqueda

• Útil cuando el cálculo o filtrado es parte de un modelo más grande y lo que nos interesa es únicamente el resultado final (ejemplo: lo usamos para realizar un cálculo y el visualizar el criterio nos indica los parámetros para los que realizamos dicho cálculo)

• Máximo detalle de información• Máxima flexibilidad para elegir parámetros de

filtradoPERO…

• Sólo permite visualizar la información en el mismo formato de la tabla original

• No aplica ningún cálculo a los datos originales a menos que dichos cálculos se añadan manualmente como campos (columnas) en la tabla o base de datos.

• Permite visualizar la información de distintas maneras y desde distintas perspectivas.

• PERO

• A la vez restringe la información que se podemos visualizar según dónde tengamos puestos los campos de filtrado

• Poca flexibilidad a la hora de elegir los parámetros de filtrado

• Sólo muestra la información numérica aplicándole una de las 11 operaciones básicas.

Tablas dinámicas - Ejercicio


A. Emular las tablas creadas en el ejercicio 2b haciendo uso de una tabla dinámica.

B. Jugar con varias maneras de confeccionar una tabla dinámicai. Arrastrar campos a distintos sitios de la tablaii. Incluir subtotales y totales generalesiii. Mostrar información haciendo uso de las opciones de cálculo que nos ofrece

una tabla dinámica

Tablas dinámicas – Principales herramientas


Menú temático

Contenidos



Acceso a la herramienta PowerPivot


Ejercicio:

1. Obtener facturación por región y comercial;

2. Obtener facturación por región y comercial incluyendo el nombre del cliente.


¿A dónde quiero llegar?


Suma de Importe RegionNombre Comercial Norte Sur Total generalArte gráfico Amaia Gil 4.000 € 4.000 €Los lunes Javier Ruiz 1.000 € 1.000 €Los martes Amaia Gil 2.000 € 2.000 €Pérez escayolistas Amaia Gil 3.000 € 3.000 €The lucky guys Javier Ruiz 5.000 € 5.000 €Total general 7.000 € 8.000 € 15.000 €

Suma de Importe Etiquetas de columnaEtiquetas de fila Norte Sur Total generalAmaia Gil 2.000 € 7.000 € 9.000 €Javier Ruiz 5.000 € 1.000 € 6.000 €Total general 7.000 € 8.000 € 15.000 €

1

2

¿Qué tengo?

FACTURACION CLIENTES

FacturacionCódigo ClienteNo. facturaImporte

Series facturacíonSerieRegion

COMERCIAL

Hoja 1Nombre clienteComercial

NOMBRES CLIENTES

Nombres clientesCódigo clienteNombreRazón Social


Para ello ¿qué necesito?


Una tabla que contenga todos los datos que quiero visualizar.

TABLA COMBINADA


Nombre clienteComercialRegion

+

Por ejemplo:

Hagámoslo…

Para ello ¿qué necesito?


Para obtener lo anterior, necesito:1. Nombre del cliente.2. Nombre del comercial.3. Región. Y para obtener la región, a su vez necesito:

a. Serie de factura (elemento común entre la factura y la tabla de regiones).

Quiero:1. Facturación por región y comercial.2. Facturación por región y comercial incluyendo el nombre del cliente.

Relaciones entre tablas



Series facturacionSerieRegion

COMERCIAL


NOMBRES CLIENTES


[Serie Col Calc]

Para obtener lo anterior, necesito:

1. Nombre del cliente, para lo cual puedo relacionar…2. Nombre del comercial, para lo cual puedo relacionar…3. Región. Y para obtener la región, a su vez necesito:

a. Serie de factura (elemento común entre la factura y la tabla de regiones), para lo cual puedo relacionar…

Hagámoslo…© Francisco Guillermo Cervantes Medina


Traslademos ahora este concepto a la herramienta PowerPivot…

Pasos para crear un modelo de datos

1. Identificar las tablas de datos y las tablas de transacciones…


Modelos de relaciones



Series facturacíonSerieRegion

COMERCIAL


NOMBRES CLIENTES


Datos(d)

Datos(d)

Datos(d)Transacciones (t)




Anteponemos una “d” al nombre de la tabla para indicarnos que en el modelo de datos que estamos a punto de crear esta es una tabla de datos (no de transacciones).



Anteponemos una “t” al nombre de la tabla para indicarnos que en el modelo de datos que estamos a punto de crear esta es una tabla de transacciones (no de datos).

Anteponemos una “d” al nombre de la tabla para indicarnos que en el modelo de datos que estamos a punto de crear esta es una tabla de datos (no de transacciones).

Pasos para crear un modelo de datos2. Transformar las tablas de datos y transacciones en tablas y/o

nombrarlas usando un criterio para distinguir las tablas de datos de las tablas de transacciones. Por ejemplo: dRegiones, tVentas.

3. CERRAR hojas en donde se encuentren las tablas a importar en el modelo de datos

4. Incorporar las tablas a modelo de datos usando la herramienta PowerPivot.

5. Establecer relaciones entre las tablas buscando elementos comunes.Nota: las tablas de datos son las tablas en las que haríamos una búsqueda si esta relación la hiciésemos con un BUSCARV o CONSULTAV.





Series facturacionSerieRegion

COMERCIAL


NOMBRES CLIENTES


Datos(d)

Datos(d)


[Serie Col Calc]



6. Crear una Columna Calculada

Escribimos primero = y luego las primeras letras del nombre de la función o bien usamos el botón Fx



6. Crear una Columna Calculada (cont.)

Primero escribimos = y luego las primeras letras del nombre de la función.

También podemos usar el botón Fx y seleccionar la función deseada.



6. Crear una Columna Calculada (cont.)Establecemos los argumentos de función de forma similar a como lo haríamos con una función en una hoja de cálculo normal.

Argumento 1Argumento 2

Salvo que…

1. Las funciones DAX (Data Analysis Expressions) hacen referencia a una columna de datos en lugar de una celda y por ello hemos de especificar la tabla y la columna de dicha tabla con la que deseamos operar.

2. El separador de argumentos, como en las funciones normales de Excel es un ;


.


6. Llevar las relaciones que hemos establecido en nuestro modelo de datos a la herramienta PowerPivot de Excel.

7. Crear tabla dinámica.


Ejercicio:

1. Obtener descuentos por comercial y región.


Modelos de relacionesFACTURACION

FacturacionCódigo ClienteNo. facturaImporte[Serie Col Calc]

Series Fact.SerieRegion

COMERCIAL


NOMBRES CLIENTES

Nombres ClientesCódigo clienteNombreRazón Social

Datos(d)

Datos(d)


Facturas

FacturasNo. FacturaSin descuentoDescuentoBase Imponible


Creación de una medida o campo calculado


1 En el área de cálculo introducir el nombre del campo calculado terminado en dos puntos :

2

Introducir la función (normal o DAX) que necesitemos para realizar el cálculo.

Funciones DAX


= SUMX (Tabla , Expresión)

Tabla que se relaciona con la tabla en donde se encuentran los datos que quiero sumar.

Columna de la tabla donde se encuentran los datos que quiero sumar.

Puede ser simplemente el nombre de una columna o una formula que calcule los datos que quiero sumar.

“Quiero la suma de los elementos de una columna correspondientes a los elementos de estatabla.”

Funciones DAX


= RELATED (Columna)

Columna de la tabla donde se encuentran los datos que quiero trasladar.

Equivalente a la función BUSCARV

Creación de una medida o campo calculado en versiones anteriores de Excel


3

21

Seleccionar un encabezado de la tabla que se relaciona con el dato (“campo”) que queremos calcular.

Introducir el nombre del campo y la función correspondiente.

tab din y pp oct 2018... · tablas dinámicas e introducción a powerpivot impartido por francisco...

Documents