En esta nota veremos otras funcionalidades de las tablas dinámicas que ayudan a hacer el trabajo con ellas más eficiente.
Para nuestros ejemplos usaremos una tabla que contiene las ventas, día por día, de cuatro departamentos de una empresa imaginaria durante la primera mitad del año.
Nuestra tabla tiene 725 líneas (181 días * 4 departamentos + 1 línea para los encabezamientos).
Con facilidad creamos un informe que nos muestra el total de ventas del medio año por departamento
El campo Fechas lo hemos puesto en el área de páginas de manera que podemos elegir una fecha determinado y ver las ventas por departamento
El menú de Tablas Dinámicas ofrece la posibilidad de agrupar datos ligados a campos que Excel puede reconocer como fechas. Para usar esta funcionalidad el campo de fechas debe encontrarse en el área de las filas o de las columnas. Por lo tanto movemos el campo Fechas al área de columnas
Ahora seleccionamos el rótulo Fecha, abrimos el menú de tablas dinámicas y elegimos la opción "agrupar y mostrar detalle"
Al elegir esta opción se abre un diálogo donde podemos definir la forma de agrupar los datos. Las opciones van desde segundos a años. También podemos definir las fechas de comienzo y fin.
Por ejemplo, si elegimos "trimestres" obtenemos
También podemos elegir más de una opción simultáneamente, por ejemplo trimestre y mes
Con este resultado
Como habrán notado, no existe la opción de agrupar por semanas. Pero Excel nos permite hacer esto, si seleccionamos "días" y en la ventanilla "número de días" ponemos "7"
El resultado es
Nuestro ejemplo tiene una limitación potencial, ya que el número de columnas de una hoja de Excel es 256 y el número de días del año es 365 (o 366 si es bisiesto).
Es decir que si tuviéramos las ventas de todo el año, día por día, no podríamos poner el campo Fecha en el área de columnas, para luego agruparlo.
Lo que podemos hacer en este caso, es poner el campo Fecha en el área de filas,
agrupar, por ejemplo por mes
y luego arrastrar los campos agrupados al área de columnas
Categorías: Manejo de Datos_
Technorati Tags: Excel, Tablas Dinamicas en Excel
hola de nuevo,
ResponderBorrarRealmente tu blog esta siendo un gran descubrimiento. Estoy aprendiendo un monton.
Dessafortunadamente hay enlaces que no funcionan. el archivo ejemplo no esta disponible.
Si no es mucha molestia me lo podrias enlazar correctamente.
Gracias de nuevo
Enlace corregido.
ResponderBorrarGracias por los conceptos.
Hola! quisiera saber como puedo agrupar tiempos, es decir tengo quiero agrupar por horas y que me de el total de datos por hora. Trate de hacerlo con histograma pero no me reconoce los tiempos. mis datos por ejemplo son 18:58
ResponderBorrar19:01
19:40
19:49
20:07
20:08
20:23
22:01
y necesito saber cuantos por hora, ya que son miles de datos. De antemano Gracias!
Lo mejor es hacerlo con una tabla dinámica al como se muestra enla nota. Teines que asegurarte que los datos sean horas y no texto que se ve como horas.
ResponderBorrarholaa!!!
ResponderBorrarno estoy segura de que lo que quiero saber este asociado peor igual voy a preguntar>
quiero saber si es posible asociar campos de pagina en una tabla dinamica, es decir que las opciones que se muestren en un segundo campo de pagina dependan de la seleccion hacha en el primero
Mónica,
ResponderBorrarno, no esta relacionado con la nota, pero respondí a tu comentario en la otra nota.
"si tienes Excel 2010 puedes usar segmentación, como muestro en esta nota.
En versiones anteriores habría que buscar alguna solucipon con Vba."
Hola Jorge,
ResponderBorrarLe tenía un miedo atroz a las tablas dinámicas y con tu ayuda se lo he perdido y ahora no se trabajar sin ellas. Gracias.
Quiero conseguir que la tabla no se muestre como subtotales, sino que cada fila contenga todos los datos. Es como si en tu ejemplo agrupado por mes (penúltima tabla de este blog) y una vez quitados los subtotales apareciera la columna "Fecha" totalmente rellena y no sólo en el primero de los campos ("Ene" en las filas 5, 6, 7 y 8 y así sucesivamente con el resto de los meses).
Lo he conseguido añadiendo un campo único de texto =Fecha&Departamento y poniéndolo en la primera columna pero me parece complejo.
¿Hay otra manera más sencilla?
Gracias,
Flaviano.
Flaviano,
ResponderBorrarespero haber interpretado correctamente tu consulta. Si trabajas con Excel 2010 todo lo que tienes que hacer es abrir el menú de configuración del campo Fecha, en "subtotales y filtros" señalar "ninguno" y en "Diseño e impresión" señalar "Repetir etiquetas de elementos".
En Excel Clásico (97-2003) y Excel 2007 no existe esa posibilidad, así que hay que solucionarlo con un campo auxiliar, como hicistes.
HOla Jorge una consulta con respecto a tablas dinamicas, al querer agrupar por fechas un rango sale perfecto (por ejemplo por meses)
ResponderBorrarpero si luego quiero crear OTRA tabla dinamica (con respecto a la misma tabla) para agrupar por trimestres al crearlo afecta a la tabla anterior PORQUE??
no es posible agrupar de forma independiente??
Si, es posible. La explicación excede el marco de un comentario por lo que estaré publicando una nota en breve.
ResponderBorrarBuenos días, la verdad que encontrar su página y la claridad de sus ejemplos ha sido verdaderamente fascinante. Quería hacerlo una consulta, ya que tengo un problema de comprensión que es el siguiente:
ResponderBorrarTengo una BBDD con n registros de tres campos -realmente tengo más campos, pero para simplificar-, el primero de los cuales contiene el nombre de una Zona geográfica, el segundo un codigo asociado, y el tercero un importe de ventas. Dos preguntas al agrupar en Tabla dinámica (excel 97/03):
1) Si en el area de filas pongo el campo ZONA, ¿porque no me permite agrupar..?
2) Para poder agrupar unn campo de TD es necesario que TODOS los REGISTROS en TODOS los campos contengan información?
Gracias por su tiempo y enhorabuena por la página.
Para agrupar en campos de texto (como Zonas en tu caso) hay que seleccionar previamente los valores a agrupar y luego activar el menú Agrupar.
ResponderBorrarSe puede agrupar también si hay valores en blanco en el campo. Pero no se puede agrupar si hay valores de distinto tipo en el campo, por ejemplo números y texto.
Un caso interesante es cuando hay números y fechas en el mismo campo. A pesar de que las fechas son números, Excel no permite la agrupación.
HOLA JORGE TE AGRADESCO UN MONTON POR TUS PUBLICACIONES HE ENCONTRADO INFORMACION EN ELLAS MUY BUENA Y ESPLICADO DE UNA MANERA SENCILLA COMO PARA ENTENDERLO GRACIAS.
ResponderBorrarQUISIERA HACERTE UNA CONSULTA, TENGO UNA TABLA DINAMICA DONDE UNO DE LOS CAMPOS TENGO ALMACENADO HORAS Y QUIERO AGRUPARLAS EN 3 PERIODOS POR EJEMPLO PRIMER PERIODO DE 10:00 A.M. A 1:00 P.M. SEGUNDO PERIODO DE 02:00 P.M. A 06:00 P.M. Y TERCER PERIODO DE 07:00 P.M. A 11:00 P.M.
LO LA AYUDA DE ESTA PAGINA APRENDI A AGRUPAR POR HORAS PERO QUISIERA SABER SI SE PUEDE AGRUPAR POR LOS PERIODOS QUE E INDICADO, YO ESTOY UTILIZANDO EXCEL 2003.
TE AGRADESCO MUCHA TU AYUDA
MARITERE
La forma más práctica es agrupar las horas manualmente: seleccionamos las horas del turno y apretamos Agrupar.
ResponderBorrarTambién se puede crear una campo auxiliar, pero el método manual es el más práctico.
Puedes fijarte en esta nota.
Y, por favor!, no escrtibir todo en mayúsculas (es como si estuvieras gritando) y revisar la ortografía de nuestro vapuleado idioma (debe ser "agradezco")
Para usar Agrupar todos los datos del campo deben ser del mismo tipo. Por ejemplo, si en la columna de las fechas de la base de datos hay una celda sin dato (vacía) o con un valor que no sea fecha, no se puede agrupar.
ResponderBorrarHola
ResponderBorrarMuy útil esta página.
Mi pregunta, estoy agrupando por semanas para una base de datos que empieza en Agosto 2011 y termina en Junio 2012. Selecciono agrupar, luego por días y marco en número de días 7, pero cuando le digo que la fecha de inicio sea el 01/01/2012 y luego le doy ok las fechas no empiezan en la fecha seleccionada si no en Agosto 2011 en la tabla dinámica. Cómo puedo hacer para que en la tabla dinámica las fechas agrupadas por semanas empiecen a ser agrupadas a partir del 01/01/2012?
Gracias de antemano.
Tienes que usar el filtro de fechas del campo con "Después de..."
ResponderBorrarExcelente ejemplo me sirvió bastante para realizar una tarea, saludos!!
ResponderBorrarEs posible agrupar por semanas-meses-años? Cuando además de agrupar los 7 dias de la semana activo meses o meses-años me desaparece la agrupación por semanas...Estoy usando Excel 2007
ResponderBorrarComo pongo en la nota, Excel no tiene incorporada la opción de agrupar por semanas.
ResponderBorrarLa solución en tu caso sería agregar columnas auxiliares a la tabla para calcular la semana, el mes y el año, y usar estos campos en el área de las filas.
Muchísimas gracias por su aporte en especial, y a los participantes en general. Tenía el mismo problema que algunas personas, al tener datos vacíos en la base de datos y no permitirme la agrupación. Uso Excel 2003. Dios los bendiga.
ResponderBorrarHola Jorge cordial saludo... como hago para ver el ejemplo e ir trabajando junto con él.? pues no está disponible. yo quiero crear un informe de mis ventas trimestralmente. trabajo con productos de ferreteria y la vez quiero saber que productos se vendieron mas durante ese trimestre. gracias por tu ayuda. mi correo a.villegas.s@hotmail.com
ResponderBorrarLa descarga ya está disponible (el sitio estuvo "suspendido" por un pequeño exabrupto de Google :()
ResponderBorrarHola Jorge... Hace tiempo que consulto tus trucos excel, haces un trabajo marvilloso y siempre encuentro soluciones para todo. Me surge un problema con las fechas de tablas dinámicas y como no sepas resolverlo tú, yo ya no sé donde mirar...
ResponderBorrarTengo una hoja excel en un servidor(en inglés). Hago una consulta a BBDD y después le aplico a la columna fecha formato dd/mm/aaaa hh:mm:ss (originalmente viene en formato m/dd/yy hh:mm AM/PM). Creo una tabla dinámica, y al agrupar por fecha me muestra la hora como 12AM, 3PM... y entonces se me desestructura la tabla porque ya no queda ordenada cronológicamente sino alfabéticamente...
He probado a cambiar formatos, mil cosas... pero sigue apareciendo el dichoso AM/PM. Hay alguna manera de poder cambiarlo a formato 24H? Creo que el problema viene de la configuración regional, pero esa parte no la puedo tocar en el servidor.
Muchísimas gracias de antemano!
MartaJ
Martita,
ResponderBorrargracias por los conceptos y me alegro que encuentres el blog útil. En relación a tu consulta mi impresión es que las fechas llegan a Excel como texto, no como números. Esta situación no se altera al cambiar el formato (de hecho tendrías que reentrar cada uno de los valores).
Mi sugerencia es crear una columna auxiliar con la función FECHANUMERO que convierte texto en forma de fecha en el número de serie que representa la fecha. Si necesitas tambiien la hora tienes que usar también la función HORANUMERO.
En tu caso suponiendo que el texto en la celda A1 es "10/09/2013 22:04", tendrías que poner en B1 esta fórmula
=FECHANUMERO(A1)+HORANUMERO(A1)
lo que dará 41527.0914444 que con formato de fecha es 10/09/2013 22:04 y que te permitirá agrupar por fechas en tu tabla dinámica.
Hola Jorge,
ResponderBorrarGracias por tu comentario, pero no es eso... Si aplico las funciones que me indicas me da #VALOR :(
Realmente cuando las fechas vienen en texto(por ejemplo cuando vienen con horas del tipo 22:34:65.000000) ni siquiera me suele dejar agruparlas en la tabla dinámica, y estas sí que me deja. Seguiré buscando a ver qué encuentro! Muchas gracias de nuevo.
MartaJ
Martita, te sugiero que me mandes el archivo. La dirección aparece en el enlace Ayuda (en la parte superior de la plantilla).
ResponderBorrarPor favor, ten en cuenta ue esta semana estoy de vacaciones así que mi respuesta se demorará.
En cuanto al formato 22:34:65.000000 Excel no puede reconocerlo como formato de tiempo y por eso no te permite agrupar.
Hola Jorge,
ResponderBorrarLamento no poder enviarte el archivo, es un excel un poco especial y contiene contraseñas de acceso... pero gracias por tu ayuda igualmente!!
Un saludo,
Marta
Buenos días, ¿podrías decirme cómo puedo agrupar las fechas por número de día para saber cuál día del mes es el que tiene mayor número de ventas por zona? Es decir, todos los días 1 de cada mes en un grupo, los días 2 de cada mes en otro, etc. Muchas gracias por tu apoyo.
ResponderBorrarTendrías que crear una columna auxiliar con el número del día usando la función DIA. Luego puedes agrupar por esa columna.
ResponderBorrarSi la intención es el día de semana (lunes, martes, etc.) haces lo mismo pero con la función DIASEM.
Excelente, muchísimas gracias por tu ayuda.
ResponderBorrarExcel 2013, se desactivo la opcion agrupar desagrupar fechas, en tablas dinamicas, favor su ayuda.
ResponderBorrarPara poder usar la opción agrupar, todos los datos del campo deben ser fechas. Asegurate que no haya algún dato que no sea fecha (aunque se vea como tal) o celda vacía.
ResponderBorrar