Referencias estructuradas en tablas dinámicas

pegones1 · el 22 de octubre

Leyendo el entretenido blog de Chandoo, el último artículo publicado por Jeff Weird me ha parecido tan práctico que he retocado su ejemplo al castellano y lo subo para compartirlo con vosotros.

http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/

¡Que lo disfrutéis!

ReferenciaRangosTablasDinamicas_PW1.1.rar

Antoni · el 22 de octubre

Lástima que no tengo ni la mas pajolera idea de tablas dinámicas.

Pero seguro que el aporte es la leche. :)

pegones1 · el 22 de octubre

Hola Macro, el aporte no es la leche, es la sangre de los pacientes con virus. o_Oo_O

Es un ejemplo de tabla dinámica para egipcios como tú que no han estado en contacto con este virus mutante.

Lo que si te digo es que si pruebas a estar en contacto con las tablas dinámicas no te arrepentirás.

¡¡¡ Ahorran mucho código VBA !!!

http://office.microsoft.com/es-es/excel-help/overview-of-pivottable-and-pivotchart-reports-HP010342752.aspx

Esta aportación es un workaround para que con las tablas dinámicas dispongamos de referencias estructuras equivalentes a las de las tablas "normales", por ejemplo lo que hacen las siguientes fórmulas con las referencias a la tabla "TablaDatos" en la columna "Kilos":

=SUBTOTALES(9;TablaDatos[Kilos])

=SUMA(TablaDatos[Kilos])

Si filtras por paciente, cumpleaños, tipo de sangre, etc., verás que los nombres de referencia de todos los filtros cambian su rango para ajustarse dinámicamente a la tabla dinámica, valga la redundancia.

He puesto como ejemplo este nombre: RefRangos.Tabla_Dinamica.Suma_de_Kilos

que siempre devuelve la suma de los kilos de los pacientes según quiera el doctor filtrarlos.

El formato es: [Nombre de la Hoja].[Nombre de la tabla dinámica].[Nombre de la agregación]

¡Es uno de los temas pendientes que tiene la gente de Microsoft que aún no ha querido hacerlo en Excel, ni siquiera en la versión 2013!

Por cierto, si lo pruebas en Excel 2007 no verás todas sus características (he puesto el condicional de Application.Version), ya que la propiedad Summary de las tablas dinámicas apareció en Excel 2010.

http://msdn.microsoft.com/es-es/library/office/ff198197(v=office.14).aspx

Gerson Pineda · el 22 de octubre

Pedro que excelente aporte!

¡¡¡ Ahorran mucho código VBA !!!

Si es totalmente cierto y esto sin tocar "PowerPivot"

[uSER=23001]@GabrielRaigosa[/uSER] mira este aporte;)

Saludos

GabrielRaigosa · el 22 de octubre

Hola Amigos,

Gracias [uSER=9328]@Gerson Pineda[/uSER] por invitarme a leer este tema.

Estuve estudiando el adjunto de la página y les cuento mis impresiones generales.

Si tengo el origen de los datos, "la tabla", prefiero usar como referencia la tabla origen y no a la dinámica, claro esta que todo depende del entorno de trabajo.

Las tablas dinámicas las uso bastante, y recientemente tengo un proyecto en el cual debo hacer sumas para sacar cumplimientos de presupuestos con varios criterios con los datos que vienen de una consulta con SQL a una base de datos tan grande que no puede ponerse dentro de Excel y lo que obtengo a cambio es una tabla dinámica, o varias, según las necesidades.

Pero en este trabajo el usuario NO quiere ver tablas dinámicas quiere una "ventana" mas sencilla, casi que hacer la tabla dinámica pero con funciones.

Para hacer esto y como no tengo los datos de origen, tengo es la tabla dinámica, lo que hago es hacer las sumas a rangos nombrados en la hoja que tiene la tabla dinámica.

La primera hoja tiene listas de validación, y cuando alguna de esas validaciones cambia se filtra la tabla dinámica por ese concepto (usando macros filtro la tabla dinámica en los campos de filas o filtro de informe), y dentro de las celdas de la primera hoja hay formulas referidas a los rangos nombrados de la hoja que tiene la tabla dinámica. Y como hay un criterio requerido que son los meses hay que usar de todos modos sumas condicionales, funciones [ =Sumar.Si.conjunto( ], referidas a esos rangos nombrados.

Veo que el tema es similar a lo que yo estoy haciendo.

Para los que no han hecho estas cosas les cuento entonces, mi experiencia es que los rangos nombrados en tablas y en tablas dinámicas se comportan como "Rangos dinámicos" ese es el beneficio que he encontrado con esta práctica.

Las tablas aparecieron en Excel 2007 y los rangos nombrados y rangos dinámicos Existen desde versiones anteriores.

La combinación de las tablas dinámicas con el administrador de nombres, tablas y rangos dinámicos facilitan mucho la obtención de información en bases de datos grandes.

Este es un tema interesante y me parece muy bueno que hayan abierto este tema, no se si han dado cuenta que muchas consultas de las que se hacen en el foro se podrían resolver con tablas dinámicas y bases de datos, lo que sucede es que al no conocer el entorno real de trabajo no se conocen los errores en la captura de los datos por parte de los usuarios.

Yo veo que muchos usuarios convierten bases de datos en "cuadros" poco operativos, pero repito, sin conocer el entorno real de trabajo no se pueden proponer soluciones especificas.

La clave de este tema esta en el "Administrador de Nombres", los rangos nombrados.

El Power Query es otro cuento, lo que me gusta de ese recurso es que me permite obtener la tabla resumen a partir de los datos de la consulta SQL sin tener que obtener obligatoriamente una tabla dinámica.

Seguiré atento a sus comentarios y experiencias.

Gabriel

Gerson Pineda · el 22 de octubre

El poder de las TD no tienen comparación con VBA y no digamos PowerPivot / Power Query que en cuanto pueda me ire llenando mas sobre ese tema

La combinación de las tablas dinámicas con el administrador de nombres' date=' tablas y rangos dinámicos facilitan mucho la obtención de información en bases de datos grandes.[/quote']
Por aquí miramos algo de esto Tabla Dinamica con Rango Dinamico

Saludos

digitalboy · el 23 de octubre

Lo que si te digo es que si pruebas a estar en contacto con las tablas dinámicas no te arrepentirás.

¡¡¡ Ahorran mucho código VBA !!!

Esta aportación es un workaround para que con las tablas dinámicas dispongamos de referencias estructuras equivalentes a las de las tablas "normales"

Es verdad! las tablas dinamicas son uno de los mayores fuertes de Excel! Hace poco me ayudaron a resumir un Sell Out, Sell In e inventarios de mas de un millon de registros a solo unos decenas de miles.... pero mejor a un se pueden programar para procesar datos y obtener reumenes de mismo.

En parte veo que alguien ya se me adelanto con esta idea, pero la mia es diferente, he creado un objeto que amplia el poder y mejora las prestaciones de una tabla dinamica a nivek programacion, espero este fin compartir el aporte!

GabrielRaigosa · el 23 de octubre

[uSER=361]@digitalboy[/uSER]

Por acá también sigo esperando ese aporte.

digitalboy · el 23 de octubre

[uSER=361]@digitalboy[/uSER]
Por acá también sigo esperando ese aporte.

Este fin de semana, garantizado y con un buen ejemplo!

jmollan · el 24 de octubre

Digitalboy como siempre tus aportes son de gran utilidad para toda la comunidad del foro, estaré atento a tu aporte, suerte !!!

pegones1 · el 24 de octubre

Veo que el tema es similar a lo que yo estoy haciendo.
Este es un tema interesante y me parece muy bueno que hayan abierto este tema, no se si han dado cuenta que muchas consultas de las que se hacen en el foro se podrían resolver con tablas dinámicas y bases de datos, lo que sucede es que al no conocer el entorno real de trabajo no se conocen los errores en la captura de los datos por parte de los usuarios.

La clave de este tema esta en el "Administrador de Nombres", los rangos nombrados.

Me agrada que estés interesado en este tema y tengo que decirte que estoy de acuerdo contigo en que la clave son los rangos nombrados, lo que Excel hace automáticamente con las tablas "no dinámicas" como "referencias estructuradas" aunque con la salvedad de que no aparecen en el "Administrador de nombres".

Véase como "Usar referencias estructuradas con las tablas de Excel" en este enlace:

http://office.microsoft.com/es-es/excel-help/usar-referencias-estructuradas-con-las-tablas-de-excel-HA010342999.aspx

Las referencias estructuradas hacen que el trabajo con los datos de las tablas sea más fácil y más intuitivo cuando se utilizan fórmulas que hacen referencia a una tabla, ya sea a partes de una tabla o a toda la tabla. Son especialmente útiles porque los rangos de datos de la tabla cambian a menudo y las referencias de celda de referencias estructuradas se ajustan automáticamente. De este modo, se reduce de forma considerable la necesidad de volver a escribir fórmulas cuando se agregan o eliminan filas y columnas de una tabla o se actualizan los datos externos.

El aporte de este tema (que ya dije que no es mío en el comentario #1) permite usar los rangos generados en el "Administrador de nombres" por la macro DynamicPivotNames(pt As PivotTable)

El nombre del tema "Referencias estructuradas en tablas dinámicas" lo puse por similitud con las referencias estructuradas con las tablas de Excel, al crear rangos dinámicos nombrados que se comportan simulando las referencias estructuradas. Por ejemplo con:

=SUBTOTALES(109;TablaDatos[Kilos])

=SUBTOTALES(109;RefRangos.Tabla_Dinamica.Suma_de_Kilos)[/CODE]

La primera fórmula hace referencia a la tabla origen de los datos y la segunda a la tabla dinámica con los rangos nombrados ¡¡¡ y actualizados por la macro [b]DynamicPivotNames() !!![/b]

Si tengo el origen de los datos, "la tabla", prefiero usar como referencia la tabla origen y no a la dinámica, claro esta que todo depende del entorno de trabajo.

Gabriel, no hay que olvidar que la principal ventaja de las tablas dinámicas es que permiten resumir, analizar, explorar y presentar un resumen de los datos de la hoja de cálculo o un origen de datos externos, mucho más fácilmente que con los filtros de la tabla origen, con lo que las referencias dinámicas a tablas dinámicas serían de gran utilidad si Microsoft las hubiera implementado nativamente, pero mientras no sea así debemos conformarnos con la aportación de [b]Jeff Weird[/b] y el adjunto que acompaña a este tema, o esperar a:

Es verdad! las tablas dinamicas son uno de los mayores fuertes de Excel!
En parte veo que alguien ya se me adelanto con esta idea, pero la mia es diferente, he creado un objeto que amplia el poder y mejora las prestaciones de una tabla dinamica a nivek programacion, espero este fin compartir el aporte!

[uSER=361]@digitalboy[/uSER], descuento las horas que faltan para ver tu aporte este finde...