Saltar al contenido

Referencias estructuradas en tablas dinámicas


pegones1

Recommended Posts

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!

33u947a.jpg

ReferenciaRangosTablasDinamicas_PW1.1.rar

Enlace a comentario
Compartir con otras webs

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. :mad:

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

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!

2crkbbl.jpg

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

Enlace a comentario
Compartir con otras webs

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

post-23001-145877011751_thumb.png

post-23001-145877011755_thumb.png

Enlace a comentario
Compartir con otras webs

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

Enlace a comentario
Compartir con otras webs

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

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!

Enlace a comentario
Compartir con otras webs

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

Enlace a comentario
Compartir con otras webs

Archivado

Este tema está ahora archivado y está cerrado a más respuestas.

  • 93 ¿Te parecen útiles los tips de las funciones? (ver tema completo)

    1. 1. ¿Te parecen útiles los tips de las funciones?


      • No
      • Ni me he fijado en ellos

  • Ayúdanos a mejorar la comunidad

    • Donaciones recibidas este mes: 0.00 EUR
      Objetivo: 130.00 EUR
  • Archivos

  • Estadísticas de descargas

    • Archivos
      177
    • Comentarios
      90
    • Revisiones
      27

  • Crear macros Excel

  • Mensajes

    • No va a ser necesario distinguir el tipo de proceso entre 1 y 2. Revisa el adjunto a ver si es eso lo que quieres. Function MediaAttention(mImp As Range, _ q25 As Range, q50 As Range, _ Optional q75 As Range, _ Optional q100 As Range) As Double '-- Opción 1 If q75 Is Nothing And q100 Is Nothing Then MediaAttention = q25 / q50 Exit Function End If '-- Opción 2 MediaAttention = ((0.25 * (q25 - q50)) / mImp) + _ ((0.5 * (q50 - q75)) / mImp) + _ ((0.75 * (q75 - q100)) / mImp) + _ (q100 / mImp) End Function   Media Attention Formula 1.2.xlsb
    • Gracias tomarse el tiempo de leer por responder Maestro @Antoni Adjunto el archivo con la idea a la que deseo llegar, sigo atento. Mil gracias por el tiempo y la ayuda brindada   Media Attention Formula 1.2.xlsb
    • He analizado la UDF y entiendo perfectamente lo que hace, lo que no entiendo es lo que pretendes hacer. Mejor sube un ejemplo resuelto de  como debería funcionar la UDF con los nuevos parámetros solicitados. 
    • Gracias, ya lo conseguí solucionar
    • Hola a todos Primero que nada deseo agradecer el tiempo en leer este post, Muchas Gracias. Me acerco a ustedes para pedir su ayuda para lo siguiente: Tengo una UDF, la cual tiene dos escenarios: 1.- Si Tiempo Promedio y Duración del Video son diferentes de 0, hacer el calculo 2.- Si lo anterior es igual a 0, se realiza el otro calculo por cuartiles. El detalle es que son muchas celdas a seleccionar, lo cual creo puede ser engorroso, entonces, pensando en simplificar la función, me pregunte si fuera posible: a.- Colocar 1 al principio de la función y después solo seleccionar 2 celdas correspondientes (Rango continuo o discontinuo) b.- Colocar 2 al principio de la función y después seleccionar las 5 celdas correspondientes (Rango continuo o discontinuo) Espero me puedan ayudar y/o orientar al respecto, quedo atento para cualquier duda, de antemano les agradezco cualquier ayuda brindada. Mil Gracias!! Media Attention Formula 1.1.xlsb  
  • Visualizado recientemente

    • No hay usuarios registrado para ver esta página.
×
×
  • Crear nuevo...

Información importante

Echa un vistazo a nuestra política de cookies para ayudarte a tener una mejor experiencia de navegación. Puedes ajustar aquí la configuración. Pulsa el botón Aceptar, si estás de acuerdo.