Como Hacer Una Tabla Dinamica De Varias Hojas De Excel 2013
Ammiel Fried
Cuando desea crear una tabla dinmica, qu hace si tiene datos en diferentes hojas de clculo? Si utiliza Excel 2013, tiene un mtodo optimizado para hacerlo. Hay una tcnica llamada Modelo de Datos, que utiliza las relaciones de datos como lo hace una base de datos.
Si desea seguir este tutorial con su propio archivo de Excel, puede hacerlo. O si lo prefiere, descargue el archivo zip incluido para este tutorial, que contiene un libro de ejemplo denominado Pivot Consolidate.xlsx.
Al conectar todas estas hojas dentro del panel de tareas de la tabla dinmica, podemos seleccionar datos de cada una de las hojas. Puesto que los nmeros de pedido existen en las tres hojas, se convertirn en los puntos de conexin. Esto es lo que una base de datos llama a una clave primaria. Tenga en cuenta que no siempre es necesario tener una clave primaria, pero reduce la posibilidad de error.
Haga clic de nuevo en la Customer Table y, a continuacin, haga clic en cualquier lugar dentro del rea de datos. Vaya a la pestaa Insertar de la barra de la cinta de opciones y, a continuacin, haga clic en el icono Tabla.
El cuadro de dilogo Crear tabla identifica correctamente el rea de la tabla. La casilla de verificacin en la parte inferior tambin debe identificar que la primera fila de la tabla es para los encabezados. (Si no, seleccione esa opcin.)
Haga clic en Aceptar y ahora tiene una tabla con sombreado a rayas y botones de filtro. Puede hacer clic dentro de ella para anular la seleccin, si desea verla mejor (no haga clic fuera de la tabla). La barra de la cinta de opciones tambin muestra una pestaa Diseo para la tabla. En el lado izquierdo de la cinta, el cuadro Nombre de tabla muestra un nombre temporal de Tabla1. Brrelo y llmelo Customer_Info (utilice un guin bajo en lugar de un espacio). A continuacin, pulse Intro.
En la hoja Payment Info, asegrese de que el cursor est en algn lugar de la tabla. Vuelva a la pestaa Insertar de la cinta de opciones y haga clic en el icono de la tabla dinmica (es el primer icono).
El cuadro de dilogo que aparece debe identificar correctamente la tabla y seleccione que la tabla dinmica se va en una hoja de clculo nueva. En la parte inferior, haga clic en la casilla de verificacin Agregar estos datos al modelo de datos. A continuacin, haga clic en Aceptar.
El panel de tareas muestra la tabla y los campos de la hoja activa, as que haga clic en Todas para ver todas las tablas que cre. Pero antes de poder usarlos, tenemos que conectarlos entre s, y eso significa crear relaciones. Haga clic en el botn Relaciones en la barra de la cinta de opciones.
Al hacer clic en ese botn se muestra el dilogo Administrar relaciones. Haga clic en el botn Nuevo y se mostrar el cuadro de dilogo Crear relacin. Crearemos dos relaciones utilizando el campo Order # como el conector.
Utilizando la nueva caracterstica de modelo de datos de objetos en Excel 2013, puede recopilar los campos de varias hojas de clculo para crear una tabla dinmica unificada. Tenga en cuenta que las filas de cada tabla deben estar relacionadas entre s de alguna manera. Tendr las mejores posibilidades de xito cuando las tablas tengan un campo comn con valores nicos.
Si est buscando buenas formas de presentar sus datos, Envato Market tiene una buena seleccin de plantillas de Excel y PowerPoint, as como scripts y aplicaciones para convertir los datos de Excel en formatos web y viceversa.
Al crear una tabla dinmica generalmente tenemos todos nuestros datos en una sola hoja, sin embargo es posible crear una tabla dinmica a partir de varias hojas de Excel. En esta ocasin te mostrar como crear una sola tabla dinmica basada en mltiples hojas.
La condicin necesaria para crear una tabla dinmica desde mltiples hojas es que cada una de las tablas de datos de las diferentes hojas debe tener la misma cantidad de columnas. Los datos pueden variar, pero los encabezados de las tablas deben ser los mismos. Para nuestro ejemplo supondremos que tengo dos hojas. La primera contiene los datos de las ventas para la zona Norte y la segunda hoja las ventas de la zona Sur.
Para agregar un rango debes seleccionarlo directamente de la hoja y posteriormente pulsar el botn Agregar. Esta accin se debe repetir para todos los rangos que se desees consolidar. Al terminar de agregar todos los rangos pulsa el botn Siguiente para ir al paso final el cual nos preguntar si deseamos insertar la tabla dinmica en una hoja de clculo nueva o en una hoja de clculo existente. Selecciona la opcin de tu preferencia y pulsa el botn Finalizar.
Al crearse la nueva tabla dinmica notars de inmediato que se muestra el recuento de todos los valores. Si lo que necesitas es la suma de los valores, puedes cambiar este comportamiento haciendo clic derecho sobre alguna de las celdas de valores y seleccionando la opcin Resumir valores por y posteriormente la opcin Suma.
Si adicionalmente necesitas ocultar alguna de las columnas mostradas debes hacer clic en la flecha que se encuentra junto a Etiquetas de columna y seleccionar solamente aquellas columnas que deseas ver en pantalla.
Tambin notars que se ha agregado una columna llamada Total general cuya funcin es mostrar la suma de cada fila. Para nuestro ejemplo esta columna no es necesaria, as que puedo remover dicha columna haciendo clic derecho sobre cualquier celda de valor y seleccionando Opciones de tabla dinmica.
Dentro de este cuadro de dilogo, dentro la pestaa Totales y filtros, debes remover la seleccin de la opcin Mostrar totales generales de las filas y finalmente pulsar el botn Aceptar. Con esta ltima modificacin habremos llegado a la tabla dinmica que nos permite analizar adecuadamente los datos provenientes de varias hojas.
Esto est bien si solo lo necesitamos una vez, pero si es un informe que queremos hacer cada semana o cada mes, o si es un informe en tiempo real que queremos realizar, entonces debemos encontrar alguna manera de poder juntar estos datos ms fcilmente.
Para solucionar esto, tambin vamos a quitarle el encabezado a Colombia. pegar como valores el encabezado, es decir la primera fila, y empezamos nuestra frmula desde la fila 2 y no desde la 1 como antes.
Hola Gonzalo
Ahora hay un par de nuevas funciones en Sheets que permiten hacer eso ms fcil
una es CHOOSECOLS que te permite crear una tabla solo con las columnas que necesitas y en el orden que quieras.
La otra es VSTACK que te permite unir tablas de manera rapida
Saludos!
Hola Juan! Necesito poder ordenar por alguna columna en especial en la planilla donde Query me compila todos los datos. Usando tu ejemplo de Ecuador, Colombia y Per, supongamos que quiero ordenarlo por Pas Importador (en mi caso, fechas que esta en la octava columna). Cmo se hara? Explico que si le pongo la opcin de filtros en los encabezados, me trae filas vacas al comienzo pero nunca me ordena por fecha, intenta pero vuelve a la original)
Buenos das,
tengo unas 144 hojas, con 40000 filas cada una. El lmite creo que est en un milln y algo de filas. Todas las hojas tienen las mismas columnas. Son hoja por mes de los ltimos 12 aos. Unificarlas no se puede por el lmite de filas. hay alguna forma de hacer una tabla dinmica para poder consultar los datos de todas al mismo tiempo? o una base de datos alternativa donde poner toda la informacin y hacer la consulta desde google sheets??
Muchas gracias
Estoy consolidando dos consultas (Query), pero cuando un de los dos no tiene datos que extraer no me salen los datos de la otra consulta. Como puedo hacer para que de como respuesta la consulta que si tiene datos?
Esta genial este tutorial, muy bien explicado y todos las opciones me funcionaron, ahora tengo una duda:
en el escenario en que los datos estan en diferentes libros? es decir no en el mismo documento supongo se usuario la instruccin IMPORTRANGE, pero como serian los argumentos?
Hay varias pestaas que hacen la misma funcin siempre, pero hay pestaas que en su contenido pues como no ha llegado la fecha estn vacas. Sin embargo deseo a travs de un Query hacer sumas de los elementos comunes y es necesario considerar aun cuando todo este vaco, es posible eso?
Hola buenos das, la verdad es que se agradece tu facilidad para explicar las cosa, estoy intentado consolidar hojas pero al momento de aplicar la formula solo me vaca los datos de la primera hoja, ojal me pudiera ayudar a resolver.
Saludos
Hola! Muchas gracias.. ya aprend a consolidar pero, ya lo hice con QUERY y con Filter y aun as no me funciona el filtro para ordenarlo cronolgicamente! Sabes si s se puede ordenar cronolgicamente y de ser as, me puedes ensear cmo hacerlo?
Mil gracias
Esta web utiliza cookies para que podamos ofrecerte la mejor experiencia de usuario posible. La informacin de las cookies se almacena en tu navegador y realiza funciones tales como reconocerte cuando vuelves a nuestra web o ayudar a nuestro equipo a comprender qu secciones de la web encuentras ms interesantes y tiles.
La tabla dinmica es una de las funciones ms potentes e intimidantes de Microsoft Excel. Potente porque puede ayudarte a resumir y dar sentido a grandes conjuntos de datos. Intimidante porque no todos somos expertos en Excel, y las tablas dinmicas siempre han tenido la reputacin de ser complicadas.
Una tabla dinmica es un resumen de datos, empaquetado en una sola tabla, que te permite informarte y explorar tendencias en tu informacin. Son muy tiles si tienes filas o columnas largas donde necesitas conocer cantidades rpidamente y compararlas entre s.
En otras palabras, las tablas dinmicas analizan los datos que contienen tus bases de datos. Y, ms especficamente, te posibilitan agrupar tus datos de diferentes maneras para que puedas sacar conclusiones fcilmente.
Supongamos que tienes una hoja de trabajo que contiene datos de ventas mensuales de tres productos diferentes (producto 1, producto 2 y producto 3) y deseas averiguar cul de los tres ha generado mayor cantidad de dinero. Por supuesto, puedes consultar la hoja de trabajo y agregar manualmente la cifra de ventas correspondiente a un total acumulado cada vez que aparece el producto 1. Entonces podras hacer lo mismo para el producto 2 y 3, hasta que tengas los totales para todos ellos. Suena bastante sencillo hasta el momento.
d3342ee215