jueves, 8 de agosto de 2013

Mostrar detalle de una Tabla dinámica en Excel.

Veremos hoy una característica muy práctica, y creo poco empleada, de las Tablas dinámicas en Excel. Hablo en particular de la posibilidad de Mostrar detalle de un dato dentro de la Tabla dinámica.
Ojo!!, no confunidir con Expandir o Contraer todo el campo.
Lo que hace especial esta funcionalidad de Mostrar detalle, es que genera una nueva hoja donde lista, en forma de tabla, los registros del origen de datos que componen aquel dato que hemos seleccionado para Mostrar el detalle (Tabla segmentada). Esta hoja además no guardará ninguna relación de conectividad con la tabla dinámica o el origen de datos de partida; lo que nos proporciona la posibilidad de realizar todo tipo de pruebas sobre dicha Tabla 'segmentada'.


Pongamos un ejemplo de cómo trabajar con esta opción. Partiremos del siguiente origen de datos y su correspondiente Tabla dinámica ya construida:



Sabemos que si dejamos un instante el cursor sobre algún dato de la tabla dinámica, aparecerá una etiqueta informativa de en qué área de la tabla dinámica estamos, del valor y del cruce de filas y/o columnas...

Mostrar detalle de una Tabla dinámica en Excel.

pero nosotros necesitamos más. ¿Qué registros son los que componen ese valor resumido?.


Aquí es donde entra en juego 'Mostrar detalles'. Podemos llamar a esta funcionalidad al menos de dos maneras. Situándonos en la celda que represente el valor del que queremos conocer su detalle:
1- haciendo doble clic izquierdo con nuestro ratón.
2- clicando el botón derecho del ratón (Menú contextual) y luego presionando la opción Mostrar detalles

Mostrar detalle de una Tabla dinámica en Excel.



De ambas maneras, lo que se crea es una nueva hoja en nuestro libro de trabajo, donde se lista en forma de Tabla, los registros que componían ese valor. En nuestro ejemplo, aquellos registros que cumplían las condiciones de filtro sobre los campos de la Tabla dinámica (Zona Norte y Comercial BBB) cuyas 'Ventas' en conjunto sumaban 11.987,00:

Mostrar detalle de una Tabla dinámica en Excel.


Ya sobre esta Tabla realizaremos todo tipo de pruebas o comprobaciones sin restricciones.

Por cierto, para acabar con esta entrada, esta opción se puede activar/desactivar. Para ello deberemos acceder a las Opciones de tabla dinámica > pestaña Datos > opción Habilitar Mostrar detalles:

Mostrar detalle de una Tabla dinámica en Excel.

32 comentarios:

  1. Excelente articulo, no sabia que se podia hacer esto, desde ya lo estoy aplicando, gracias!!!

    ResponderEliminar
  2. Hola , buenos días ; interesante artículo ... solo tengo una duda , el formato con que se muestran los detalles de la tabla pueden ser personalizados.

    Es decir , al dar clic y cuando se muestren los detalles deseo que tenga el formato que el usuario elija.

    Gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes configurar el Tema genérico para adecuarlo como más te guste. Para ello navega a la Ficha Diseño de página > grupo Temas > botón Temas.
      También puedes guaradar tu propio 'Tema personalizado' y emplearlo como genérico.

      Espero te sirva.
      Slds

      Eliminar
  3. ¡Hola!

    ¿Es posible mostrar el detalle de celda en un determinado orden? Tomando el ejemplo presentado en el artículo, mostrar el detalle ordenado descendentemente el monto de las ventas.

    ¡Muchas gracias de antemano por la respuesta!

    --- maoh

    ResponderEliminar
    Respuestas
    1. Hola Miguel Angel,
      en principio el orden en el que aparece el detalle es en el que están dispuestos los datos en el origen (de donde toma los datos la tabla dinámica), asi que si quieres un orden determinado, deberías ordenar el origen de datos.

      un cordial saludo

      Eliminar
  4. Hola, es posible que se haga para todos los valores de la tabla de forma automática, sin tener que clikar uno a uno?

    Gracias,

    ResponderEliminar
    Respuestas
    1. Hola,
      sólo haz doble clic en el 'Total General'... sería suficiente.
      Pero para ver todos los valores de la tabla basta con que vayas al origen de datos de la tabla dinámica, no te parece?
      ;-)
      Saludos

      Eliminar
    2. me referia a que cada fila me apareciera en una hoja de datos distinta. Lo he arreglado con una macro. Gracias,

      Eliminar
    3. Hola, dependiendo de tu configuración y diseño de Tabla Dinámica quizá podrías aplicar la opción Mostras filtros de páginas de informes

      Saludos

      Eliminar
  5. Hola,

    Estoy creando TD's a partir de un modelo de datos PowerPivot. Cuando uso la opción "Mostrar detalles" sólo muestra los 1.000 primeros registros, y así lo especifica Excel mediante un mensaje. Ocurre tanto en Excel 2010 como en Excel 2013.

    ¿Es posible conseguir todos los registros?

    Gracias!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      ten presente que las TD (provenientes de PowerPivot) están pensadas para resumir información...
      No es solución, desde luego, pero si necesitas ver el detalle de registros que cumplen ciertos criterios de filtros, siempre podrías generar una consulta personalizada, con las restricciones y relaciones entre Tablas necesarias, y en la ventana de PowerPivot acceder a esa Tabla y aplicar los filtros necesarios...

      De todas formas investigaré por si hubiera alguna opción de configuración que permita directamente mostrar el detalle completo.

      Un saludo

      Eliminar
    2. En las Propiedades de la Conexión, pestaña Uso > Nivel de detalle OLAP > Número máximo de registros que se recuperarán está por defecto en 1000. Allí lo puedes cambiar al número que necesitas y de esta forma ver más registros cuando das Mostrar Detalle en la tabla dinámica

      Eliminar
    3. Gracias por el aporte !
      lo probaré
      Un saludo

      Eliminar
    4. Saludos, intenté esa forma que menciona mecorredor, pero cuando se utiliza el complemento de Power Pivot esta sale deshabilidata. Intenté ir a las propiedades del Power Pivot pero no encuentro una opcion Similar.

      Eliminar
    5. Hola!
      es algo específico de las conexiones a cubos OLAP, si tu conexión es de otro tipo, no podrás hacerlo
      :(
      saludos

      Eliminar
  6. ¿Qué hay que hacer para eliminar esa hoja que muestra los detalles (registros) que componen una celda, sin afectar a la Tabla Dinámica?

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      que tal estás? espero te encuentres bien.

      La hoja generada que muestra los detalles de un dato resumido de la tabla dinámica es totalmente independiente de esta, no guarda relación ni vínculo alguno, y por tanto puedes eliminarla sin consecuencias sobre ésta.
      Un saludo

      Eliminar
    2. Buenas noches, como puedo hacer para que siempre que haga click para ver los detalles valla a la misma hoja y no cree una hoja nueva

      Eliminar
    3. Hola,
      no es posible tal cosa...hasta donde yo conozco; al Mostrar detalle abrirá una hoja nueva...
      Un saludo

      Eliminar
  7. Cuando tienes filtrada una tabla por segmentación de datos, al hacer doble click para mostrar el detalle salen todos los registros¿Hay alguna forma de solucionar este tema?

    Gracias,

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      pues la solución es llevando el campo al Área de filtro de página.. además de tenerlo en el cuadro de segmentación
      :(

      Un saludo

      Eliminar
  8. Hola,
    Interesante artículo.
    Tengo una consultilla: tengo una tabla dinámica que creé sobre una serie de datos. Quiero que pinchando a cualquier valor o dato de columna me lleve directamente a esa fila en concreto de la hoja de cálculo origen de los datos que tengo en el mismo libro Excel, es posible?
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Fran,
      pues me temo que tal cosa no es posible..
      ten presente que además el dato mostrado puede representar (normalmente es así) un conjunto de registros..
      :(
      Un saludo

      Eliminar
    2. Gracias.
      ¿Me podrías aconsejar alguna solución para poder hacer lo que comento utilizando algún truco o planteándolo de otra forma? quiero poder visualizar los datos al detalle desde la tabla dinámica.
      Gracias

      Eliminar
    3. La única opción es hacer doble clic sobre el dato y que te abra una nueva hoja con los registros que verifican las condiciones de la celda cliceada.
      Saludos

      Eliminar
  9. Hola, cuando le doy mostrar detalles, no muestra los detalles de la tabla sino toda la base de datos, ¿como hago para que me muestre solo lo que esta en la tabla?. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Erick,
      esta funcionalidad es así.. muestra los registros correspondientes al cruce de información en el que se ha hecho doble clic.. pero mostrando TODOS los campos del origen.

      Saludos

      Eliminar
  10. buenos dias

    ismael mi nombre es lina, una pregunta, es que estoy trabajando con power pivot y cuando doy doble clic para ver el detalle me muestra solo las primeras 1000 filas, en un comentario arriba leí como se cambia el numero de filas para OLAP, sabes como se cambia para power pivot?

    ResponderEliminar
    Respuestas
    1. Hola!
      realmente ese es el límite establecido... y no es posible cambiarlo (hasta donde yo sé).

      Personalmente no lo he probado nunca pero he leído que hay un truco que a veces funciona, pensado para trabajar en las Tablas dinámicas creadas a partir de u modelo de datos en Power Pivot::
      1) Situa el ratón fuera de la TD
      2) Ve a ficha Datos > grupo Conexiones
      3) En la ventana diálogo de conexiones busca: "ThisWorkbookDataModel" (o similar)
      4) Clic en Propiedades.
      5) En alguna de las pestañas busca, “Máximo número de registros a devolver”.. si está habilitado podrás cambiarlo.

      En caso contrario, la conexión no te permitirá tal acción.

      Suerte y comenta si lo consigues.

      Eliminar
  11. ¡Hola Ismael! ¿Hay forma de configurar que el detalle se abra en otro libro?

    ResponderEliminar
    Respuestas
    1. Hola Ivett,
      no es posible tal cosa.
      En todo caso, y sería bastante laborioso, se podría desarrollar con un evento doubleclick algo similar...

      Saludos

      Eliminar