miércoles, 29 de julio de 2009

Ejemplo de agrupación en una Tabla dinámica.

Echaremos un vistazo a la manera de agrupar elementos de un campo en una Tabla dinámica. Ya vimos en el post Modificación de una Tabla dinámica la forma en que se agrupaban campos de nuestro origen de datos cuyos elementos eran Fechas; en ese caso cuando seleccionabamos la opción de agrupar se activaba un asistente de agrupación temporal (por meses, trimestres, años, etc). En esta entrada practicaremos la forma de agrupar los elementos de un campo que conceptualmente sean agrupables. En cierto modo analizaremos otras variables, otros campos, de nuestro origen de datos en función de agrupaciones de elementos.
Supongamos nuestro ejemplo con registros diarios de unidades vendidas de manuales por nuestro comerciante, con información adicional de los volumenes de ingresos obtenidos cada día por la venta de aquellos. Podría nuestro tendero necesitar un estudio de qué volumen de ingresos acumulado ha obtenido por agrupaciones de unidades vendidas a lo largo del 2008. Por ejemplo, cuántos ingresos ha alcanzado cuando vendía entre 50 y 99 manuales. Aplicando el criterio de agrupación sobre nuestra tabla dinámica obtendremos ese resultado.
Los pasos a seguir son:
1-Incorporar el Campo 'Número de ventas' al área de filas.
2-Click del botón derecho del ratón sobre el Campo recién añadido, y seleccionar Agrupar y mostrar detalles - Agrupar.
3-En el cuadro diálogo activado, señalar el intervalo de agrupación deseado sobre los valores del campo 'Número de ventas', 50 por ejemplo; el comienzo y final de los valores se rellena de manera automática (aunque siempre podremos definirlo a nuestra necesidad).
Lo podemos visualizar en el siguiente video:


Interpretamos los resultados. El ingreso acumulado de todos los registros (días en nuestro ejemplo) en los que se han vendido entre 0 y 49 unidades ha sido de 129.200 eur. Sería el resultado de ir sumando línea a línea (registro a registro por cada día) que cumpla la condición que el número de unidades vendidas esté entre 0 y 49. ingresosLa misma lectura para el resto de intervalos generados.
La respuesta entonces a la pregunta de nuestro comerciante será que los ingresos alcanzados todas las veces que vendío entre 50 y 99 manuales ha sido de 231.555,00 eur.

22 comentarios:

  1. Estimados: muy bueno el sitio.
    Va mi pregunta a ver si me pueden ayudar:
    tengo inforemacion recopilada en columnas de excel, con la columna fecha. realizo tablas dinamicas y comparo informacion de distintos años por meses. Ejemplo enero-julio 2009 vs enero julio 2010.
    Lo que no he podido hacer es comparar los ultimos 12 meses que incluyen parte en 2 años distintos.

    Agosto09-Julio2010 vs agosto08-julio2009. Es decir acumulado de los ultimos 12 meses o año corrido.
    Como puedo hacer este informe de tabla dinamicaesto? desde ya gracias.

    ResponderEliminar
  2. Hola,
    yo creo que deberías intentarlos usando 'elementos calculados' dentro del campo Fecha.
    Depende de si esos 12 meses es una información muy variable cada mes/semana de trabajo; si es algo mensual me parece bastante práctico, ya que mes a mes puedes editar el 'elemento calculado' para adecuarlo a la nueva situación (sacas Ago09 e incluyes Ago10).

    http://excelforo.blogspot.com/2010/02/elementos-calculados-en-tablas.html
    Espero haberme explicado...
    Slds

    ResponderEliminar
  3. Tengo una hoja de Excel 2003 con una Columna de 13F y los datos que tiene son cedula de alumnos.cada vez que se Retire(R)un una CI tengo que colocar la siguiente CI.Ejemp.
    A/B/ c / RESULTADO:
    R/1/V193205 A/ B /
    R/2/V194566 1/V205325/
    /3/V205325 2/V206543/
    /4/V206543 3/V218875/
    R/5/V217765
    /6/V218875
    . . .
    . . .

    ResponderEliminar
  4. Necesito la ayuda de los expertos,tengo tres columnas a,b,c de 13filas,la columna C contiene los números de Cédula de Identidad,la Columna B contiene la númeracion del (1 al 13) y la Columna A es donde se coloca la letra R cuando hay un retiro de Cédula.Lo gue necesito es que cada vez que aparesca una R(Retiro)la Cédula siguiente suba de posicion,si hay dos o tres R seguidas
    se coloca la Cédula siguiente despues de la R,esto se repite cada vez que hay un Retiro (R)Ejep.
    A B C
    R 1 V195635
    ""2 V196642
    R 3 V201062
    R 4 V202171
    ""5 V211154
    R 6 V212653
    ""7 V222537

    RESULTADO
    A B
    1 V196642
    2 V211154
    3 V222537

    ResponderEliminar
  5. Hola Carlos,
    creo que lo mejor que se adapta a lo que quieres es la aplicación de un Filtro.
    Si he entendido correctamente, lo que quieres es tener un listado de todos tus registros(CI) que no han sido Retirados.
    Para ello, selecciona el rango de 13 filas que tienes, y desde la Ficha Datos > Ordenar y Filtrar > Filtro configuras el filtro para obtener sobre el campo de la columna A, sólo los diferentes a R(retiro).
    Espero haberte comprendido bien y te sirva la solución.
    Slds

    ResponderEliminar
  6. Gracias por tu ayuda el Filto si funciona,pero tengo otro problema que me reduce el número de filas. En el formato deben aparecer 13F es decir las CI sin la (R) y los espacios siguientes vacíos Ejemp.
    A B C
    R 1 V20124
    ""2 V20533
    R 3 V21332
    ""4 V21459
    ""5 V22356
    ""6
    ""7
    RESULTADO
    1 V20533
    2 V21459
    3 V22356
    4
    5
    6
    7

    ResponderEliminar
  7. Hola Carlos,
    en un post expliqué el uso de la función JERARQUIA (ver, que creo tepodrá servir.
    En tu caso yo emplearía algunas columnas auxiliares para determinar el orden que quieres.
    Por ejemplo, en la columna D incluyes la siguiente fórmula:
    =SI($A2<>"R";SI(B2="";"";B2);"") y arrastras hasta el final de tus registros. En la columna E:
    =SI(ESERROR(JERARQUIA(D2;$D$2:$D$8;1));MAX($D$2:$D$8)+1;JERARQUIA(D2;$D$2:$D$8;1))
    En la columna F:
    =SI(ESERROR(BUSCARV(D2;$B$2:$C$8;2;0));"";SI(BUSCARV(D2;$B$2:$C$8;2;0)=0;"";BUSCARV(D2;$B$2:$C$8;2;0)))

    Con los datos obtenidos en estas tres columnas auxiliares pasamos a la ordenación final.
    Sobre una tabla nueva (en las columnas H:I) con valores 1, 2, 3 ... en la columna H, realizamos la reordenación. En la columna I añadimos para cada valor de H una última función:
    =SI(ESERROR(BUSCARV(H2;E2:F8;2;0));"";BUSCARV(H2;E2:F8;2;0))
    que sobre las columnas auxiliares coloca los valores como quieres.
    Slds

    ResponderEliminar
  8. Un millon de gracias por tu ayuda con la formula
    fue excelente. Los resultados fueron los correctos.

    ResponderEliminar
  9. Tengo una columna de excel que contiene CI Y al final en los espacios en blanco se coloca asteriscos "*" Ejemp.
    V1964321
    V1968352
    V2069431
    *
    *
    *
    *
    Al utilizar la formula CONTARA esta cuenta el nº de CI y los "*" pero el Resultado deberia ser Total CI ( 3 ), pero da el Resultado ( 7 ).Por favor si pueden ayudarme con otra formula que de el Resultado correcto.

    ResponderEliminar
  10. Hola Luis,
    si lo que quieres es contar los registros que empiezan en tu rango de datos con V, prueba con
    =CONTAR.SI(rango_celdas;"V*")
    en este caso el * funciona como comodín, es decir, estamos diciendo que cuente aqeullos registros que comiencen con 'V'.
    Espero te sirva.
    Slds

    ResponderEliminar
  11. GRACIAS POR TU AYUDA EXCELENTE

    ResponderEliminar
  12. comco aplico la formula cuando hay un Ex en la lista Ejem.
    V1945898
    V1954582
    V1965423
    E19756982
    *
    *
    *

    ResponderEliminar
  13. fácil...
    =CONTAR.SI(rango_celdas;"V*")+CONTAR.SI(rango_celdas;"E*")
    ;-)
    Slds

    ResponderEliminar
  14. gracias por tu ayuda .excelente

    ResponderEliminar
  15. Buenas, tengo una duda con las agrupaciones en tabla dinamica, si yo hago una agrupacion con fechas, por ejemplo en Semestres, pero tambien quiero otra agrupacion de Trimestres no me lo permite por que se desagrupa la de semestres. Hay alguna forma de "protejer" alguna de las 2 agrupaciones para que no se me elimine alguna de las 2?? espero haberme dado a entender... gracias.

    ResponderEliminar
  16. Buenos días,
    debo entender que empleas la opción de Agrupar seleccionando el campo en la TD, usando sólo las agrupaciones estándar que ofrece Excel (días, meses, trimestres, años). Si es así, la selección de una o varias al tiempo no son excluyentes, es decir, no te debería desagrupar ningún otro campo.
    Igualmente, si has optado por crear un nuevo campo en tu tabla de datos, formulado, para que te devuelva el semestre y/o trimestre de la fecha, y este campo 'auxiliar', incluirlo en tu TD para trabajar con Datos agrupados, no debe darte errores ni problemas.
    El sentido de ambas formas de trabajar es que dentro de la memoría de la TD se crean campos nuevos 'virtuales', trabajables como cualquier campo, respecto a agrupaciones, filtros, cálculos, etc de manera 'independiente' pero cruzada con la estructura de tu TD.
    Si no te he aclarado tu cuestión, mejor envíame a
    excelforo@gmail.com
    el fichero o un pequeño ejemplo de tu problema.
    Slds

    ResponderEliminar
    Respuestas
    1. Buenas,
      Tengo el mismo problema del compañero,te explico:
      Tengo una Tabla, de ella creo 2 Tablas Dinámicas que contienen Fechas.
      Mi problema es que al cambiar la agrupación de fechas de cualquier TDinamica, también le afecta a la otra TD.

      Como puede cambiar esto, muchas gracias

      Eliminar
    2. Hola,
      por defecto las Tablas dinámicas en las últimas versiones de Excel son 'dependientes'... lo que implica que las agrupaciones, campos y elementos calculados se comparten entre tablas dinámicas.

      La solución es crear TD independientes, empleando si fuera el caso el asistente de tablas dinámicas de 2003 (presiona Atl+t+b).

      Saludos

      Eliminar
    3. Buenos días, la de horas dándole vueltas y no daba con la clave, me has dado la vida.

      Muchas muchas gracias.

      Eliminar
    4. ;-)
      me alegra haberte podido ayudar
      Un saludo

      Eliminar
    5. Buenas tardes, en mi caso el problema que tengo es el del inicio que se plantea de la comparacion de meses pero algo mas complicado. Me explico: tengo una tabla dinámica que tiene las ventas por empresa de cada mes. Esta tabla se actualiza mensualmente añadiendo a la tabla una columna mas con el mes correspondiente. Lo que necesito es tener un campo que para cada mes me calcule el % acumulado de los meses de ese año con respecto al anterrior. Es decir si cogemos como referencia el mes de abril de 2016 el resultado del campo debería ser el % (positivo o negativo) del acumuluado de las ventas de ene, feb, mar y abr de 2016 con respecto a los mismos meses del año anterior. Además cuando actualice la tabla el mes siguiente, el campo debería tener en cuenta que tiene que actualizar metiendo un mes en el calculo. En enero debería reiniciarse ya que no tendría meses anteriores. Estoy muy atascado y me encataría si me pudieseis ayudar.
      Gracias!

      Eliminar
    6. Hola,
      fíjate que el 'automatismo' en las TD aparece cuando los campos son constantes y lo que cambia(incrementa) son los elementos..
      Si tu caso es que añades campos/columnas por cada mes en la tabla de origen, tendrás que trabajar cada mes manualmente para realizar el cálculo que necesitas... o bien cambiar la forma de incorporar datos a tu origen para hacerlo por filas y no por columnas...

      Espero haberte dado la idea.
      Saludos

      Eliminar