Cómo encontrar tablas dinámicas superpuestas

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Un cliente me contactó recientemente y me pidió que le resolviera un problema. Tienen un gran workbook de Power Pivot Excel con muchas tablas dinámicas. Todo estaba bien, excepto a veces, cuando actualizaron los datos, los datos recién cargados cambiaron la forma de algunas de las tablas dinámicas, lo que provocó que al menos una de ellas intentara superponerse a otra. Tome el siguiente ejemplo.

image_thumb-27-9450235

Hay 4 tablas dinámicas en el workbook anterior. He cargado deliberadamente la tabla Productos para que solo contenga «Bicicletas» y he dispuesto las tablas como se muestra arriba. Ahora, cuando actualizo la tabla Productos y recupero todos los Productos (no solo Bicicletas), aparece el siguiente error.

image_thumb-28-2411806

Tenga en cuenta la pregunta molesta en la parte inferior «¿Fue útil esta información?». No, en realidad, no ayuda en absoluto. El error me dice el problema, pero no me dice «qué» tabla dinámica está causando el problema. En este ejemplo simplista, está claro que la tabla dinámica 1 es la culpable, pero en la vida real con un workbook grande, no es tan fácil de decir.

Hay un código VBA en la web para analizar las tablas dinámicas en busca de posibles superposiciones, pero ninguna (que pude encontrar) resuelve este problema en particular, el problema de actualización de Power Pivot. ¿Por qué? Debido a que es el cambio en la forma de la tabla dinámica lo que desencadena el error, no puede detectar el error hasta que la tabla dinámica cambia de forma, pero la tabla dinámica nunca confirma el cambio de forma porque el error evita que esto ocurra. ¿Entonces lo que hay que hacer?

Cree algunos VBA de auditoría

Después de pensar en el problema por un tiempo, se me ocurrió que una forma de resolver este problema era crear un código VBA de auditoría. Mi idea fue actualizar primero todas las tablas dinámicas antes de que ocurra el error y registrar los nombres de todas las tablas dinámicas y el orden en que se actualizan. Entonces podría hacer el cambio para desencadenar el error y registrar los nombres de la tabla dinámica y el orden de actualización nuevamente. Entonces puedo averiguar qué es diferente y descubrir al culpable. Déjame demostrar

Aquí está mi código VBA. Tenga en cuenta que si no sabe cómo copiar este código VBA, lea mi artículo aquí.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
    ActiveCell = Target.Name
    ActiveCell.Offset(1, 0).Select
 
End Sub

Simplemente coloque este código en la página de códigos de la hoja de trabajo en el editor de Visual Basic como se muestra a continuación. Deberá agregar el código en cada hoja que tenga tablas dinámicas.

image_thumb-29-4256427

Lo siguiente que hice fue seleccionar una celda en mi workbook (que se muestra en rojo a continuación).

image_thumb-30-4867549

Luego cambié a la ventana de Power Pivot y seleccioné «Actualizar todo».

image_thumb-31-8948705

Después de la actualización, esto es lo que estaba en mi hoja de trabajo.

image_thumb-32-5302108

Puede ver una lista de los nombres de la tabla dinámica junto con el orden en que se actualizan. Tenga en cuenta que cada tabla dinámica se actualizó dos veces y que la segunda actualización fue en el orden opuesto a la primera.

Luego seleccioné la celda a la derecha de la lista como se muestra a continuación

image_thumb-33-5856182

Luego cargué los nuevos datos de mi fuente de datos en la ventana de Power Pivot. Son los nuevos datos los que provocarán el error de mi demostración. Recibí un par de errores de actualización durante la actualización (como se esperaba) más la siguiente información de auditoría que luego se registró en mi hoja de trabajo.

image_thumb-34-2394980

De las 2 listas anteriores, es bastante fácil ver que el problema es PivotTable1. Para encontrar PivotTable1, puede verificar cada nombre de tabla dinámica uno por uno, o puede usar este otro código que escribí aquí.

Sub gotoPivot()
    TableName = ActiveCell.Value
    For Each Sheet In Sheets
        On Error Resume Next
        x = Sheet.Name
        Y = Sheet.PivotTables(TableName).TableRange1.Cells(1).Address
        If Not IsEmpty(Y) Then GoTo ExitHere:
    Next Sheet
ExitHere:
    Application.Goto Reference:=Sheets(x).Range(Y)
End Sub

Simplemente haga clic en la celda que contiene la tabla dinámica que está buscando, como se muestra en 1 a continuación.

image_thumb-35-8807583

Luego, ejecute el código VBA y será llevado directamente a la tabla de culpables.

¿Qué pasa si necesito esto con regularidad?

Si tiene un workbook que tiene problemas regulares, es posible que desee dejar este código de auditoría en su workbook y guardarlo como un workbook XLSM. Si desea hacer esto, sugiero crear un «interruptor» en una de las hojas de trabajo como esta (la mía está en Sheet2).

image_thumb-36-1180855

Cambié el nombre de la celda A1 anterior y le di a esta celda un RangeName = Audit. Luego cambie el código VBA de la siguiente manera.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
     If Sheets(2).Range("Audit").Value = "Yes" Then
        ActiveCell = Target.Name
        ActiveCell.Offset(1, 0).Select
     End If
End Sub

El beneficio de este enfoque es que puede activar / desactivar el código cambiando la celda A1.

Espero que encuentres esto útil.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.