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.

61 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
    2. Hola Ismael, tengo un problema similar al de Javier,aunque tenga los filtros aplicados me sigue saliendo la data sin filtrar, tengo el Excel 2013. Me puedes ayudar por favor,
      Saludos,

      Eliminar
    3. Hola Ismael mi mi ayuda es en el excel 2010.
      Hola Ismael, tengo un problema similar al de Javier,aunque tenga los filtros aplicados me sigue saliendo la data sin filtrar, tengo el Excel 2010. Me puedes ayudar por favor,
      Saludos,

      Eliminar
    4. Hola,
      te presente que la herramienta de segmentación apareció en la versión 2010, y parece que tenía 'asuntos pendientes', entre otras cosas este error que comentas.. que quedó depurado en versiones superiores (2013 y 2016)
      Solo puedo decirte que la solución pasa por incluir el campo en el área de filtro
      Saludos

      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
    2. Si Funciona""", estoy probando abriendo varias ventanas con bastante registros para ver el limite!!!

      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
  12. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  13. Buenos días:
    Estoy utilizando la opción "Mostrar detalles" pero el resultado no me aparece en el mismo orden de los datos de origen:
    El origen de datos está ordenado mediante fechas de forma ASCENDENTE (empieza en enero termina en diciembre). Pero al abrirse la nueva hoja de detalle me muestra la información de forma DESCENDENTE (desde diciembre a enero). Dado que tengo que analizar muchos datos de la tabla dinámica, al final tengo que estar ordenando hoja por hoja la información de forma ascendente.
    ¿Hay alguna forma de que al abrirse la hoja de detalles muestre el mismo orden que los datos de origen?
    Gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola,
      en teoría los datos (en la nueva hoja, tras hacer doble clic) se muestran en el mismo orden que aparezcan en el origen...
      No hay posibilidad de configuración.

      Lo único que se me ocurre es que la TD esté pendiente de actualizar y te muestre los datos según la previa ordenación (descendente)
      Slds

      Eliminar
  14. NECESITO EXPANDIR MAS DE 1000 FILAS CUANDO LE DOY DOBLE CLICK. COMO LO PUEDO HACER?

    ResponderEliminar
    Respuestas
    1. Hola Edgar, que tal estás?
      un placer saludarte.
      Para expandir o contraer filas, lo más simple suele ser aplicar un esquema.
      Si quieres asociarlo a un evento de hoja Worksheet_BeforeDoubleClick
      inserta un código para ocultar dichas filas
      Rows("5:12").EntireRow.Hidden = True
      dentro del evento

      un cordial saludo

      Eliminar
  15. Hola amigo Ismael tengo un problema trabajo con tablas dinámicas en power pivot en excel 2016 tengo creada mis tablas con mi segmentador de datos el problema es que cuando selecciondo mas de un dato al filtra en el sementador ya no me muestra el detalle en la tabla
    dinámica y cuando eligo un fitro si a que se debe ese problema. Y el otro problema es que cuando le doy clik al detalle para mostrarme los datos no me los muestras como estan en el origen de datos me pueden ayudar en ese problema se los agradeceria

    ResponderEliminar
    Respuestas
    1. Hola
      Trataré de replicar lo que comentas y si fuera el caso darte una solución
      Saludos

      Eliminar
  16. Buenas tardes, hay forma de no mostrar todas las columnas al dar doble clic sobre un celda y salga su detalle.

    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola,
      no hay forma, la acción de Mostrar detalle muestra todo los registros asociados a esa celda clicada... comppleto con todos sus campos
      :(

      Saludos

      Eliminar
  17. Hola, es posible extraer los datos de manera automática de una tabla dinámica sin hacer el doble click? no cuento con acceso (permisos) al origen de datos. Gracias

    ResponderEliminar
    Respuestas
    1. Hola
      la acción mostrar detalle muestra los datos cargados en la memoria caché de la tabla dinámica.. en principio no es necesario tener acceso directo al origen de datos...
      Salvo que se guarde la tabla dinámica liberando memoria, podrías hacer doble clic y ver el detalle

      Saludos

      Eliminar
    2. Gracias Ismael, pero la duda precisamente es esa.. poder acceder al detalle de los datos sin hacer el doble click.. automatizar el proceso.

      Eliminar
    3. No sería posible sin hacer ese doble click (Mostrar detalle).
      En cualquier caso, todo 'automatismo' estaría sujeto a algún proceso (=macro) y lo deberás asociar a algún botón o evento, lo que exigiría una acción por tu parte, esto es, una acción igual o similar a la de mostrar detalle...
      ;-)

      Eliminar
  18. Gracias!
    Qué fácil, era el tic en "Habilitar"!!!, y yo buscando en Proteger Hoja, Libro, ....

    ResponderEliminar
  19. Buenas tardes, cuando voy al detalle de la tabla dinamica, me aparecen las columnas en orden alfabetico y tengo varias columnas que su orden no debería ser ese, ejemplo, identificación, nombre, apellido y me aparece en otro orden, desde la base esta bien pero no encuentro donde deshabilitarle esta opción, me podrías por favor ayudar. estoy trabajando en excel 2016

    ResponderEliminar
    Respuestas
    1. Hola,
      Entiendo te refieres al panel de la lista de campos, donde aparecen , por defecto, tal cual están dispuestos en el origen.
      Si te aparecen ordenados alfabéticamente será que en algún momento se han cambiado... accede dentro del Panel a la rueda de configuración, donde encontrarás la opción de ordenación deseada

      Saludos

      Eliminar
  20. hola, puedo hacer algo para que cada detalle lo muestre en un hoja específica y no en una hoja nueva?

    ResponderEliminar
    Respuestas
    1. Hola Adriana
      no es configurable... lo crea siempre en una hoja nueva
      :'(

      Saludos

      Eliminar
  21. Muy buenas. Lo primero de todo felicitar la labor en esta línea. Me gustaría saber si hay una solución para el siguiente problema. He creado una TD y he habilitado la opción de "Habilitar mostrar detalles" en Excel 2016. Funciona sin problemas. Sin embargo, ese archivo en el ordenador de un compañero con Excel 2013 no funciona ya que en lugar de mostrar únicamente el número de filas indicado en la TD, muestra muchos más. ¿Será un problema de compatibilidad de versiones Excel? Entiendo que no por 2 motivos: 1) Excel 2013 tiene esa misma funcionalidad de mostrar detalles. 2) En un ordenador con Excel 2013 ha funcionado en otras ocasiones. Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Álvaro,
      es muy raro lo que comentas... sobre todo que unas veces funcione y otras no ¿¿??.
      En mi caso he probado con 2013 y 2016 y funciona perfectamente.

      Por lo que no sabría darte una respuesta... quizá sea un problema de alguna actualización...

      Siento no poder decirte nada más..
      :'(

      Slds

      Eliminar
    2. Gracias por la respuesta. De todos modos, ¿puedo enviarte de alguna manera el archivo y lo pruebas en un equipo con Excel 2013 a ver si viéndolo te ayuda?

      Eliminar
    3. en realidad, como te decía, lo he probado con 2013 y 2016..
      pero si quieres envíamelo a
      excelforo@gmail.com
      Slds

      Eliminar
  22. Hola una consulta, realicé una TD, guarde el archivo y lo envié desde mi cuenta de correo a uno en gmail, y la persona que lo recibe no visualiza la TD solo la tabla de datos. Porqué sucede esto?...

    ResponderEliminar
    Respuestas
    1. Hola!
      quizá lo esté abriendo con otra aplicación que no sea Excel, quizá con la hoja de cálculo de Google??
      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.