Ejemplo Práctico Con Power Pivot en Excel - Excel Total

19/10/2016

INICIO

Ejemplo práctico con Power Pivot en Excel Excel Total

FUNCIONES

CONTACTO

ACERCA

Ejemplo práctico con Power Pivot en Excel El ejemplo que desarrollaremos en esta ocasión nos dejará en claro uno de los más grandes beneficios de utilizar Power Pivot y me refiero a la facilidad con que se pueden crear relaciones entre los datos para generar reportes fácilmente. Para dejar en claro las ventajas de Power Pivot resolveremos el mismo problema utilizando el método tradicional, es decir con fórmulas, y en la segunda parte utilizaremos Power Pivot para que poder comparar las diferencias respecto al primer método.

Planteamiento del problema

Introduce tu email SUSCRIBIRME

El problema que resolveremos es el siguiente. Pertenecemos a una compañía que se dedica a vender teléfonos móviles y nuestro jefe quiere un reporte de las unidades que se han vendido en las cuatro regiones de la ciudad: Norte, Sur, Este y Oeste. El reporte deberá ser similar a lo que muestra la siguiente imagen donde se enlistan las regiones de la ciudad, los modelos de teléfonos móviles y las unidades vendidas:

https://exceltotal.com/ejemplopracticopowerpivot/

1/29

19/10/2016


No habrá ningún problema para generar dicho reporte porque tenemos disponible toda la información, pero tendremos que integrarla adecuadamente porque los datos están distribuidos en diferentes tablas de Excel. La Tabla1 contiene la fecha de la venta, el nombre del representante de ventas, el código del producto y la cantidad de unidades vendidas.


2/29

19/10/2016


Buscar Solo con la información de la Tabla1 no podríamos conocer la región en la que se ha realizado la venta así que es necesario integrar la columna Región de la Tabla2. En dicha tabla tenemos la región a la cual pertenece cada uno de los representantes de ventas y por lo tanto podremos asociarla correctamente a cada orden.

Además, la Tabla1 tiene el código de producto vendido pero necesitamos el nombre del producto, así que incluiremos los https://exceltotal.com/ejemplopracticopowerpivot/

3/29

19/10/2016


datos de la Tabla3 la cual tiene la equivalencia entre el código del producto y su descripción.

Para hacer más fácil la elaboración de nuestro ejemplo, las tablas están en el mismo archivo de Excel y están ubicadas en diferentes hojas. En un caso real, es probable que tuvieras que importar la información de otros sistemas o archivos antes de poder utilizarla.

Solución utilizando fórmulas Nuestra primera tarea es integrar los datos de la Tabla2 y la Tabla3 en nuevas columnas de la Tabla1 y eso lo podemos lograr utilizando la función BUSCARV. Por ejemplo, para conocer la zona a la que pertenece un representante de ventas podemos utilizar la siguiente fórmula: =BUSCARV([@Representante],Tabla2, 2, FALSO) El valor buscado es el nombre del representante que está ubicado en la columna @Representante. Dicha búsqueda se hace sobre la Tabla2 y obtenemos la segunda columna la cual contiene la región a la que pertenece el representante de ventas. Al insertar una nueva columna en la Tabla1 con la fórmula anterior, obtenemos el siguiente resultado:


4/29

19/10/2016


De manera similar, para obtener el nombre de cada producto, podemos utilizar una fórmula como la siguiente: =BUSCARV([@CódigoProducto], Tabla3, 2, FALSO) En este caso la búsqueda la hacemos sobre la Tabla3 que contiene la equivalencia entre los códigos y la descripción de cada producto. Al insertar una nueva columna en la Tabla1 y utilizar la fórmula anterior obtenemos el siguiente resultado:

Con los pasos anteriores tenemos todos los datos necesarios en la Tabla1 y podemos crear el reporte requerido y para eso https://exceltotal.com/ejemplopracticopowerpivot/

5/29

19/10/2016


crearé una Tabla dinámica desde la pestaña Insertar > Tabla dinámica.

En el cuadro de diálogo dejaré las opciones predeterminadas para crear la tabla dinámica en una nueva hoja de cálculo. Una vez que se ha creado la tabla dinámica, en el panel de Campos de tabla dinámica arrastraré el campo Unidades al área de Valores. Y al área de Filas, arrastraré el campo Región y posteriormente el campo Descripción. La tabla dinámica quedará de la siguiente manera:


6/29

19/10/2016


De esta manera hemos logrado el reporte requerido que enlista las unidades vendidas de cada producto en las diferentes regiones de la ciudad.

Solución utilizando Power Pivot Ahora crearemos el mismo reporte pero utilizando Power Pivot donde la diferencia principal con el método anterior será que evitaremos la creación de fórmulas para integrar los datos. Utilizando Power Pivot, dejaremos las tablas de Excel intactas, y crearemos las relaciones dentro del modelo de datos. Recuerda que es indispensable instalar o habilitar Power Pivot antes de hacer este ejercicio. Si no lo has hecho, vista este artículo donde podrás leer los requisitos para utilizar dicho complemento en Excel. El primer paso es seleccionar cualquier celda de la Tabla1 y posteriormente pulsar el botón Power Pivot > Tablas > Agregar a modelo de datos. https://exceltotal.com/ejemplopracticopowerpivot/

7/29

19/10/2016


Se abrirá la ventana de Power Pivot para Excel y tendrás una vista como la siguiente:

Podrás observar los mismos datos de la Tabla1 pero el hecho de que estén presentes en la ventana de Power Pivot indica que han sido agregados al Modelo de datos. Considera que se ha abierto una nueva ventana para Power Pivot pero Excel no se ha cerrado así que, debes regresar a la ventana de Excel para continuar agregando el resto de las tablas al Modelo de datos. https://exceltotal.com/ejemplopracticopowerpivot/

8/29

19/10/2016


Para agregar la Tabla2 y Tabla3 deberás repetir los mismos pasos: seleccionar una celda de la tabla y pulsar el botón Agregar a modelo de datos que se encuentra en la pestaña Power Pivot. Después de haber agregado las tres tablas tendrás una ventana de Power Pivot como la siguiente:

Quiero que pongas especial atención a la esquina inferior izquierda de la ventana donde se encuentran las pestañas para cada una de las tablas que hemos agregado al Modelo de datos. Desde ahí podrás ver los datos de cada una de las tablas. El siguiente paso será crear las relaciones entre las columnas de las tablas que hemos importado al Modelo de datos. Para crear las relaciones de manera gráfica podemos activar la Vista diagrama utilizando el botón que se encuentra en el extremo derecho de la Cinta de opciones en la pestaña Inicio.


9/29

19/10/2016


Esta vista nos permitirá visualizar cada una de las tablas así como las columnas que integran cada una de ellas. Nuestro objetivo es decirle a Power Pivot que la columna Representante de la Tabla2 es lo mismo que la columna Representante de la Tabla1. Para crear esta relación, debes hacer clic en la columna Representante de la Tabla2 y arrastrar el puntero del ratón hasta que se resalte la columna Representante de la Tabla1 como se muestra en la siguiente imagen:

Lo mismo haré para la relación entre la Tabla3 y la Tabla1 haciendo clic en la columna CódigoProducto para indicarle a Power Pivot que dicha columna es la misma en ambas tablas.


10/29

19/10/2016


En muchas ocasiones, Power Pivot es capaz de reconocer varias de las relaciones de las tablas basándose en el nombre y tipo de dato de las columnas, así que no te sorprenda si al activar por primera vez la Vista diagrama ya tengas alguna relación establecida. Puedes validar que una relación se ha creado correctamente si haces clic derecho sobre la línea y seleccionas la opción Editar relación.


11/29

19/10/2016


Esto mostrará el cuadro de dialogo Editar relación que indicará las tablas y columnas involucradas en la relación y podrás editarlas en caso de ser necesario.

Hemos terminado con la creación de nuestro Modelo de datos y sus relaciones. Lo que nos falta es crear la tabla dinámica para obtener el reporte que necesitamos. Para eso deberás pulsar el botón Tabla dinámica de la pestaña Inicio.


12/29

19/10/2016


Se activará Excel y se mostrará un cuadro de diálogo preguntándonos la ubicación de la nueva tabla dinámica. Para nuestro ejemplo elegiré la opción Nueva hoja de cálculo:

A diferencia de las tablas dinámicas convencionales, esta tabla dinámica está basada en nuestro modelo de datos y por lo tanto podrás acceder a cada una de las tablas del modelo así como a sus columnas desde el panel de Campos de tabla dinámica.


13/29

19/10/2016


Para crear el reporte, utilizaré los mismos campos que en el ejemplo anterior. Arrastraré el campo Tabla1.Unidades al área Valores y los campos Tabla2.Region y Tabla3.Descripción al área Filas. La tabla dinámica se verá de la siguiente manera:


14/29

19/10/2016


De esta manera hemos terminado con el reporte. Aunque este ejemplo ha sido muy sencillo, podrías imaginarte el impacto positivo al tener reportes basados en decenas o cientos de tablas que tienen datos relacionados ya que Power Pivot nos ahorraría la creación de un sin número de columnas auxiliares y el uso de fórmulas de búsqueda para relacionar los datos. Existen muchos beneficios de utilizar Power Pivot para analizar nuestra información y no existe más que la práctica, y el empeño que puedas poner al aprendizaje de esta herramienta, para familiarizarte pronto con el uso de este extraordinario complemento en Excel. En el archivo de descarga encontrarás tres archivos. El primero de ellos tiene solamente las tablas de datos del ejemplo. El segundo archivo es la solución utilizando la técnica del primer ejemplo, es decir las columnas auxiliares y fórmulas. El tercer archivo contiene la solución utilizando Power Pivot. https://exceltotal.com/ejemplopracticopowerpivot/

15/29

19/10/2016


No te pierdas el próximo artículo donde haremos este mismo ejemplo pero utilizando Power BI Desktop. Publicado por Moisés Ortíz.

64 comentarios en “Ejemplo práctico con Power Pivot en Excel” Dámaris López Muchas gracias Moisés, como siempre tus artículos son de mucha utilidad ya que nos ayudan a reducir los tiempos de ejecución como con este ejemplo.

Carlos Excelente aporte! Se agradece de verdad!

Miryam


16/29

19/10/2016


Muchas gracias por compartir sus conocimientos son supervaliosos.

José Excelente tutorial, bien explicado. Gracias.

Wilfredo Fermin G Excelente, ahora de manera grafica menos confusión al realizar las formulas que relacionan a cada tabla. Felicitaciones por ese tremendo aprte

Daro Genial! muchas gracias por ser tan claro y compartir todo estos datos tan valiosos

Carlos Gutiérrrez Soto Darte las gracias por compartir tus conocimientos Moisés, eres un tío COJONUDO y además bien explicado

Diana Bolivar Hola Moises, Que pasa cuando quiero ver en el reporte el nombre del representante? En la tabla dinámica coloco el de la tabla 1 o el de la tabla 2?

Moises Ortiz

Autor

Hola Diana, puedes elegir cualquiera de los dos.


17/29

19/10/2016


roberto excelente aporte. muy bien explicado. muchas gracias

CARLOS GARCIA MOISES LO FELICITO POR SU PROFESIONALISMO Y CAPACIDAD QUE HA DEMOSTRADO AL PUBLICAR SUS TRABAJOS, ASI COMO SU GENEROSIDAD DE PARA COMPARTIR LOS MISMOS, MUCHAS GRACIAS

Vielcy Gracias por la información, esta herramienta tiene un excelente futuro.

Rugers No se puede decir mas que EXCELENTE COLABORACION!

Diego Carafí Me pareció súper interesante. Lo voy a estudiar bien porque tengo un mega archivo de excel con varios rangos inter relacionados con fórmulas hasta tortuosas te diría, y de los cuales saco datos según necesidad. Mi pregunta ahora es: ¿qué diferencia al PIVOT DEL ACCESS?. Porque estaba empezando a preparar este archivo para generar las tablas para poder transformarlo en Access. Gracias

Moises Ortiz

Autor

Hola Diego, gracias por tu comentario. Power Pivot no sustituye a los sistemas de gestión de bases de datos (DBMS). Power https://exceltotal.com/ejemplopracticopowerpivot/

18/29

19/10/2016


Pivot es una herramienta que nos ayuda a agregar grandes cantidades de datos para trabajar con ellos en Excel. Dichos datos pueden provenir de diversas fuentes incluyendo bases de datos como Access. Si eres alguien que conoce Access y que sabe realizar consultas SQL, entonces no habría nada de malo en quedarse en Access y si en algún momento necesitas relacionar dicha información con otras fuentes de datos para generar algún reporte, podrás utilizar Power Pivot.

Romer Angeles magistral la informacion, aunque descarque power pivot y despues no me quizo hacer la instalacion, como si microsoft me lo impide… intentare con PBI desktop…

francisco diaz muy provechoso el ejemplo gracias

Jota Pe Excelente ayuda Moises. Gracias por todo lo que he aprendido de exscel con sus clases

Jafet Valdés agradeciendo los conocimientos que comparte, gracias por permitirme actualizar en estos temas de interés y aplicación diaria.

Arisbeth https://exceltotal.com/ejemplopracticopowerpivot/

19/29

19/10/2016


Excelente, muchas gracias por compartir. Ojala próximamente nos oriente sobre la función de KPI’s que viene dentro de la sección de Power Pivot Saludos!

Percy Cansaya Excelente, gracias por el aporte.

Jose luis trejo Soy principiante y me fue de gran utilidad gracias

Julio Sanz Muchas gracias Moisés un modelo muy bueno, felicidades

Alejandro Quintero Excelente, muchas gracias por los temas publicados. Siempre ayudan.

Eduardo Ramírez Definitivamente, Power Pivot es una gran herramienta, gracias!

Manuel Excelente comentario, muy amable, gracias.

Edith Gonzalez https://exceltotal.com/ejemplopracticopowerpivot/

20/29

19/10/2016


Hola Moises, muchas gracias por ayudarnos a usar esta herramienta y ahora el coplemento, ya estaba acostumbrandome a la tabla dinamica, y ahora este nuevo complemento me va a hacer mas facil la existencia, solo es cuestion de practicar, Solo una pregunta, las tablas deben estar en el mismo archivo o podrian estar en archivos separados?

Moises Ortiz

Autor

Edith, las tablas de Excel podrían estar en archivo separados pero deben ser importadas desde Power Pivot para poder utilizarlas en el Modelo de datos.

Jorge Cruz Excelente ahora practicarlo y ver la utilidad y explotar el manejo de información. Gracias

jorge pedro muy bueno, como siempre genial Moisés! No se como hago para incorporar ese instrumento en mi PC. Tengo Excel 2016..me puedes indicar algo sobre el tema? Gracias

Moises Ortiz

Autor

Hola Jorge, consulta las instrucciones de instalación en el siguiente artículo: https://exceltotal.com/instalarpowerpivot excel/

Lizandro Manrique


21/29

19/10/2016


muy bueno, eso si es compartir conocimiento, mil gracias Moises

Juan Gracias por también incluir el power bi desktop

yadelcy excelente esta manera de programar

Pablo Ceballos Guevara ¡Fantástico!, Moisés. Una vez más, mil gracias por compartir todo este conocimiento con nosotros.

Arturo Solano Excelente forma de presenta las tablas dinámicas. Felicidades.

baudilio reyes Muchas gracias toda esta información es de mucha utilidad para mi se lo agradezco

Alfredo Rodriguez Muchas gracias por la info. Saludos

edgar angarita muchas gracias por ese excelente ejemplo https://exceltotal.com/ejemplopracticopowerpivot/

22/29

19/10/2016


jean pier ojeda laura Me parece muy espectacular ya que hay o se persive una informacion usual

Luis Ilama Exelente material y muy práctico, gracias por compartirlo.

sergio Cada vez es mayor mi asombro lo que te facilita excel, y ademas cuando hay una persona muy profesional que se preocupa por enseñar a los demas, como Usted Moises, gracias

Otto Muy amable por sus aportes, son muy interesantes

Roger Diaz Respetado Maestro, primero un gran saludo para Usted, seguidamente agradecerle y expresarle mi respeto por compartir su conocimientos, realmente es un honor tener la oportunidad de recibir sus enseñanzas, y pedirle siga desterrando la ignorancia..

CESAR LOPEZ EXCELENTE MOISES.. UN GRAN APORTE

Jonathan https://exceltotal.com/ejemplopracticopowerpivot/

23/29

19/10/2016


Por todos los ejemplos. Gracias! Saludos!

Eduardo Muchas gracias Moises por tus grandes aportaciones

Rafael Excelente muy buen ejemplo muchas gracias por ser tan generoso y compartir tus conocimientos. GRACIAS

Juan Muy buen artículo. Con un caso práctico como este toda la teoría anterior tiene mucho más sentido. Gracias Moises!

Flora López Me sumo al agradecimiento de todos, la verdad que el contenido es muy útil, la explicación muy didáctica y tu dedicación, ¡ muy generosa ! Muchísimas gracias Moisés, espero que continúes.

Sam Gracias Moisés, me parece genial q nos enseñes más de estas “nuevas” herramientas q comúnmente no se abordan.

Mario Excelente como siempre, muchas gracias por compartir tus conocimientos https://exceltotal.com/ejemplopracticopowerpivot/

24/29

19/10/2016


Graciela Gracias, le agradezco mucho sea tan compartido con su experiencia y conocimientos esto es de gran utilidad para aprender.

Eviky Muchisimas gracias nuevamente, esta muy bien explicado y es ideal para romper el hielo con power pivot.

Mario Maguiña Mendoza Siempre agradecido por los importantes aportes en excel Felicitaciones

Edgar Conde Gracias, es un excelente aporte.

ErickWGB Hola Moises, excelente artículo, pero tengo una observación que hacerle cuando comencé a leer el artículo y analizar las fórmulas utilizadas para la función BuscarV, veo que utiliza el símbolo (@) para hacer referencia a la columna Representante de la tabla 2 resultando [@Representante], yo ni por asomo habia visto esa notación para hacer referencias a columnas y ni tan siquiera había visto el ejemplo, eso me llamó la atención y al probarlo me daba un error, de hecho ni pensé en el archivo de muestra de los ejemplos para verificar y comencé a buscar información en internet sobre referencias a celdas y rangos de celdas, incluyendo la creación de tablas y referencias a columnas y registros dentro de ella y https://exceltotal.com/ejemplopracticopowerpivot/

25/29

19/10/2016


nada, al final aprendí mucho más de lo que conocía, por lo cual le doy las gracias. Sin embargo al descargar el fichero de muestra veo que utilizamos referencias parecidas pero igualmente utilizando las referencias como las había visto que es de esta forma: BUSCARV(Tabla1[[#Esta fila]; [Representante]];Tabla2; 2; FALSO), por lo que mi pregunta es si usted lo hizo con el proposito explicativo de hacer referencia a la columna o que en excel 2013 0 16 aparece esta forma de referenciar una columna. De todas formas sea error o no, un exelente artículo que ya lo tengo de referencia y que me sirvió para hacerlo extensivo a otros compañeros míos como material de trabajo. Saludos,

Moises Ortiz

Autor

Hola Erick, gracias por tu comentario pero lo que describes NO es un error. Las referencias con el símbolo @ son conocidas como “Referencias Estructuradas” y es una manera de acceder a los elementos de una tabla en Excel. En el ejemplo utilizo la referencia [@Representante] omitiendo el nombre de la tabla para que la fórmula tome la columna de la tabla actual. Además, de manera predeterminada la fórmula tomará la fila actual así que al copiar la formula hacia abajo obtendré el resultado esperado.

Walter Sanchez Excelente ejemplo,realmente sus ejemplos y conocimientos nos ayudan mucho con nuestro trabajo diario.

Willinson Castañeda https://exceltotal.com/ejemplopracticopowerpivot/

26/29

19/10/2016


Hola Moisés como como estas?, quiero agradecerle por tan buen ejercicio de práctica, ya había escuchado y visto un tutorial por youtube sobre esta herramienta de un CEO mexicano y me pareció muy interesante. Sin embargo alguien me dijo que no perdiera tiempo con esta herramienta y que mejor aprendiera ACCESS a lo cual le hice caso, pero le confieso que no me fue bien pues se me hizo muy enredado el tema y mas que lo hice ONLINE donde muy difícilmente le pueden aclarar las dudas. Esto me parece como más practico y sencillo a pesar que es muy parecido a Access. Duda señor Moisés… para hacer las relaciones entre las tablas es importante que los nombres tanto en columnas como en filas estén igualmente escritos verdad?

Moises Ortiz

Autor

Hola Willinson, Hablando del nombre de las columnas la respuesta es no. No es necesario que los nombres de las columnas sean los mismos. Al crear el modelo de datos será tu responsabilidad crear las relaciones entre las columnas asegurándose que los datos sean compatibles. Hablando de los valores contenidos en las columnas asociadas, es necesario que los valores sean idénticos para que Power Pivot pueda asociar correctamente la información. Tomando el ejemplo de este artículo, si los nombres de los representantes en la Tabla2 no correspondieran con los nombres de la Tabla1, Power Pivot no podría encontrar relación alguna entre los datos.

Airel N Hola Moisés Súper agradecido con tus muy oportunas y excelentemente explicadas lecciones. Uso Access desde hace mucho tiempo, pero porque no sabía que existía la opción de relacionar diferentes tablas en https://exceltotal.com/ejemplopracticopowerpivot/

27/29

19/10/2016


excel de esta forma. Mi duda va por la creación de las tablas. En este caso, la creación de las tablas las hace excel de forma automática o tengo yo que definirlas? Muchas gracias una vez más y espero tu respuesta

Moises Ortiz

Autor

Hola Airel, las tablas no se hacen en Power Pivot sino que provienen de fuentes externas como bases de datos Access, SQL Server e inclusive de libros de Excel. Recuerda que Power Pivot no es un reemplazo de Access sino una herramienta que permite integrar datos de diferentes fuentes para analizarlos y generar reportes.

JESUS MELENDEZ Este Power Pivot en Excel se las trae Moisés; tiene una ventaja superior para buscar y ordenar como a uno le de la gana. Abrazo fraterno.

Deja un comentario Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con * Comentario


28/29

19/10/2016


Solo se aceptarán comentarios relacionados con el artículo publicado. Nombre * Correo electrónico *

Publicar comentario


29/29

Ejemplo Práctico Con Power Pivot en Excel - Excel Total

Recommend Documents