Esta funcionalidad no existe en forma nativa en Excel 2010, pero podemos agregarla instalando el complemento PowerPivot (en la nota del enlace se señalan todos los requisitos para la instalación).
No me voy a extender aquí sobre las bondades del PowerPivot (lo que muestro a continuación es la puntita de la punta del iceberg), pero a todos mis lectores que usen Excel 2010 o Excel 2013 les recomiendo ver este tutorial (incluye la descarga del archivo de datos para las prácticas).
Volviendo a nuestro tema, veamos cómo usamos el PowerPivot para crear un reporte dinámico con más de una fuente de datos.
Una vez instalado el complemento, veremos una nueva pestaña en la cinta
Como en el ejemplo de la nota anterior, nuestro cuaderno incluye dos hojas:
Ventas: las ventas de los productos (fecha, cliente, producto y suma)
- Producto: la categoría de cada producto (producto, categoría).
- Nuestro objetivo es crear un reporte de ventas por categoría.
Seleccionamos una celda de una de las tablas del cuaderno y en la pestaña del PowePivot apretamos "Datos de Excel-Crear Tabla Vinculada"
Excel abre la ventana del PowerPivot donde podemos ver que ha agregado la tabla seleccionada.
Hacemos lo mismo con la segunda tabla. Ahora ambas tablas aparecen en la ventana del PowerPivot
Ahora tenemos que crear una relación entre ambas tablas. El campo en común aquí es Producto. Una de las formas de hacerlo tenemos que cambiar la vista de la ventana a "Vista de diagrama"
Con el mouse conectamos el campo Producto de la tabla Categorías (donde los productos son valores únicos) con el campo Producto de la tabla de ventas
Otra forma de hacerlo es usando "Crear relaciones" en la pestaña "Diseñar"
Una vez definidas las relaciones, abrimos la pestaña Inicio del PowerPivot y usamos Tabla dinámica para general el informe
Excel genera una tabla dinámica a partir de ambas tablas como podemos ver en la ventana de la lista de campos
Hola Jorge,
ResponderBorrarLo primero agradecerte por el blog, y por todo lo que compartes y ayudas.
Lo segundo una pregunta, y perdón por el offtopic con respecto al post.
Estoy buscando que las formulas o cálculos que preciso en una tabla varíen en función de si los mismos están filtrados o no, dando el resultado en todo momento de lo que es visible una vez filtrado.
¿Es la función subtotales la forma de hacer esto? Ya vi una entrada tuya sobre esta función. Pero la aplicabas a hacer el cálculo a todas las celdas aunque el filtro las hubiera ocultado. ¿Y si esa función es la adecuada, se puede aplicar a todo tipo de fórmula por compleja que sea?
Gracias de nuevo.
Efectivamente, SUBTOTALES es la función para la tarea. SUBTOTALES incluye 11 operaciones (suma, promedio, contar, max, min, etc.) para dos tipos de situaciones: tablas filtradas o fila ocultas manualmente.
ResponderBorrarTe recomiendo ver la ayuda en línea de Excel.
Buenas Tardes Jorge, te hago la siguiente consulta/duda.
ResponderBorrarEl complemento de la Power Pivot es útil pero para uso personal. ¿no es acaso complicado tener que compartir un archivo con este complemento?
Yo lo quise implementar, pero caía en dicho problema, ya que lo que creaba, otros no lo podían utilizar.
Supongo que te referís a compartir el cuaderno con otros usuarios que no usan las versiones 2010 o 2013 de Excel.
ResponderBorrarEstimado Jorge es la primera vez que te escribo, y quisiera primero agradecerte por esta pagina que has creado. He analizado varias de los temas que desarrollas y me sirven de mucho.
ResponderBorrarBueno mi consulta no se trata de algo puntual si no más bien general, y por lo tanto no pretendo quitarte mucho tiempo con respuestas profundas, aunque de se así bienvenidas sean. Es decir me ayudaría mucho saber tu apreciación y proyección sobre esta nueva herramienta. Esto me orientaría mucho para saber que herramienta utilizar a la hora de iniciar un pequeño proyecto.
Te cuento, he comenzado a utilizar de a poco la herramienta Ms Query de excel, lo uso unicamente para hacer consultas de tablas que están dentro de un mismo libro. Pero cuando supe lo que es capaz excel 2013 (excel que todavía no utilizo) me pregunto lo siguiente:
¿Ms Query siguen existiendo para excel 2013?
¿Qué comparación entre ambas herramientas podrías hacer para que pueda comprender mejor las ventajas o alcance de cada una?
Con este avance de excel creo que un .xls sería comparable a un .mdb de access, es correcto? ¿que diferencias entre ambos programas podrías comentar?
desde ya muchas gracias Jorge. Atte Ruben
Hola Rubén,
ResponderBorrarme alegro que el blog te resulte útil. EN relación a tu consulta, si, MS Query existe tambien en Excel 2013 (Datos-Obtener datos externos-de otras fuentes).
La comparación no debe hacerse entre MS Query y Excel 2013 sino en comparación con las nuevas herramientas de Excel 2013: Data Explorer y PowePivot. En especial esta última.
Excel y Access son dos herramientas diferentes para tareas diferentes. Si bien es cierto que con las nuevas herramientas, hay muchas tareas que antes se hacían en Access y ahora pueden hacerse en Excel.
Con todo, Excel no es una base de datos. Una de los problemas con Excel, cuando queremos usarlo como base de datos, es el volumen de datos. Si bien dede la versión 2007, cada hoja de Excel tiene más de un millón de filas, cuando se alcanza mucho menos de ese tamaño se presentan problemas de velocidad de cálculo. Además, si se superan las 1,048,756 filas de una hoja, ¿dónde se ponen los próximos registros? Este problema no existe en Access. Otro problema es la transparencia de los procesos y cálculos. Dada la flexibilidad de Excel, hay modelos que resultan imposibles de controlar o entender por su complejidad.
Estas debilidades de Excel son más significantes cuando mayor es la cantidad de datos que debemos manejar.
Muchas gracias Jorge por tu respuesta.
ResponderBorrarJorge:
ResponderBorrarEn primer lugar, muchas felicidades por tu blog es excelente.
Estaba empezando a usar el Powerpivot, en excel 2010 y tengo algunas consultas:
- Tengo una tabla con una columna con valores numericos, por ejemplo, 0,1,2,3,4,5,6.. etc. y necesito agruparlos, por ejemplo: grupo1 = 0,1,2; grupo2 = 3,4,5 y mayor de 6 grupo3. ¿Como puedo hacerlo?
- Cuales son las funciones que no estan en el excel 2010, por ejemplo, no me aparece el Switch o el distinctcount.
- por ultimo, he tratado de subir un tabla de 4 MM de registros, solo 4 campos numericos y se me cae por problema de memoria, es problem que mi excel es 32 bits u otro posible motivo.
Muchas gracias, y felicidades
Hola, te sugiero que consultes el blog de Miguel Ángel que ocasionalmente colabora con este blog. Miguel tiene excelentes materiales sobre PowerPivot en castellano.
ResponderBorrar