Tablas Dinámicas en Excel – Agrupar datos

sábado, octubre 14, 2006

En el mes de marzo publiqué una serie de notas sobre el manejo de pequeñas bases de datos en Excel usando tablas dinámicas (pivot tables). En estas notas cubría aspectos funcionales de las tablas dinámicas como actualización de datos y referencias dinámicas
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: ,

34 comments:

Tecno 22 abril, 2007 02:12  

hola de nuevo,

Realmente 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

Jorge L. Dunkelman 22 abril, 2007 21:41  

Enlace corregido.
Gracias por los conceptos.

Marisol,  21 octubre, 2010 04:14  

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
19: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!

Jorge L. Dunkelman 21 octubre, 2010 05:24  

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.

monica 06 junio, 2011 20:29  

holaa!!!
no 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

Jorge L. Dunkelman 06 junio, 2011 22:37  

Mónica,

no, 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."

Anónimo,  22 julio, 2011 15:52  

Hola Jorge,
Le 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.

Jorge L. Dunkelman 23 julio, 2011 16:39  

Flaviano,

espero 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.

Anónimo,  07 agosto, 2011 20:43  

HOla Jorge una consulta con respecto a tablas dinamicas, al querer agrupar por fechas un rango sale perfecto (por ejemplo por meses)

pero 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??

Jorge L. Dunkelman 07 agosto, 2011 21:24  

Si, es posible. La explicación excede el marco de un comentario por lo que estaré publicando una nota en breve.

Anónimo,  03 noviembre, 2011 12:25  

Buenos 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:

Tengo 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.

Jorge L. Dunkelman 03 noviembre, 2011 14:06  

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.
Se 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.

Anónimo,  04 abril, 2012 01:20  

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.
QUISIERA 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

Jorge L. Dunkelman 04 abril, 2012 19:05  

La forma más práctica es agrupar las horas manualmente: seleccionamos las horas del turno y apretamos Agrupar.
Tambié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")

Anónimo,  09 junio, 2012 00:52  

Buenas tardes
Tengo una base de datos de 173000 filas y 20 columnas, y al hacer la tabla dinamica, no puedo usar esta opcion de agrupar. Me sale que no se puede agrupar la seleccion. Por que no me saldria? Al seleccionar la fecha no me muestra el menu. Puedes decirme que estoy haciendo mal por favor.
Gracias.
Rosa A

Jorge L. Dunkelman 09 junio, 2012 07:15  

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.

Anónimo,  01 agosto, 2012 22:21  

Hola

Muy ú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.

Jorge L. Dunkelman 02 agosto, 2012 07:13  

Tienes que usar el filtro de fechas del campo con "Después de..."

UnTalCarlos 08 noviembre, 2012 00:36  

Excelente ejemplo me sirvió bastante para realizar una tarea, saludos!!

Anónimo,  10 marzo, 2013 12:43  

Es 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

Jorge L. Dunkelman 10 marzo, 2013 19:50  

Como pongo en la nota, Excel no tiene incorporada la opción de agrupar por semanas.
La 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.

Anónimo,  26 abril, 2013 15:52  

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.

Anónimo,  16 junio, 2013 21:14  

Hola 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

Jorge Dunkelman 17 junio, 2013 16:14  

La descarga ya está disponible (el sitio estuvo "suspendido" por un pequeño exabrupto de Google :()

Martita 10 septiembre, 2013 15:45  

Hola 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...
Tengo 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

Jorge Dunkelman 10 septiembre, 2013 22:10  

Martita,

gracias 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.

Martita 13 septiembre, 2013 10:40  

Hola Jorge,
Gracias 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

Jorge Dunkelman 14 septiembre, 2013 09:41  

Martita, te sugiero que me mandes el archivo. La dirección aparece en el enlace Ayuda (en la parte superior de la plantilla).
Por 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.

Martita 26 septiembre, 2013 14:05  

Hola Jorge,
Lamento 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

Juan Pablo,  11 abril, 2014 17:10  

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.

Jorge Dunkelman 11 abril, 2014 18:24  

Tendrías que crear una columna auxiliar con el número del día usando la función DIA. Luego puedes agrupar por esa columna.
Si la intención es el día de semana (lunes, martes, etc.) haces lo mismo pero con la función DIASEM.

Juan Pablo,  14 abril, 2014 19:48  

Excelente, muchísimas gracias por tu ayuda.

Anónimo,  18 febrero, 2015 19:08  

Excel 2013, se desactivo la opcion agrupar desagrupar fechas, en tablas dinamicas, favor su ayuda.

Jorge Dunkelman 18 febrero, 2015 21:43  

Para 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.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP