数据透视表手动更新不起作用 [英] Pivot Table Manual Update Not Working

问题描述

我有一个数据透视表，我试图根据数组中的值选择某些数据透视项.我需要更快地执行此过程，因此我尝试使用application.calculation = xlcalculationmanual和ivottables.manualupdate = true，但似乎都无法正常工作.每当我更改数据透视表项时，数据透视表仍会重新计算.

I have a pivot table, and I am trying to select certain pivot items based on values in an array. I need this process to go faster, so I have tried using application.calculation = xlcalculationmanual and pivottables.manualupdate = true, but neither seem to be working; the pivot table still recalculates each time I change a pivot item.

是否可以做一些不同的事情来防止Excel每次重新计算?

Is there something I can do differently to prevent Excel from recalculating each time?

这是我的代码:

Application.Calculation = xlCalculationManual

'code to fill array with list of companies goes here    

dim PT As Excel.PivotTable
Set PT = Sheets("LE Pivot Table").PivotTables("PivotTable1")

Sheets("LE Pivot Table").PivotTables("PivotTable1").ManualUpdate = True

dim pivItem As PivotItem

'compare pivot items to array.  If pivot item matches an element of the array, make it visible=true, otherwise, make it visible=false
For Each pivItem In PT.PivotFields("company").PivotItems
    pivItem.Visible = False 'initially make item unchecked
    For Each company In ArrayOfCompanies()
        If pivItem.Value = company Then
            pivItem.Visible = True
        End If
    Next company
Next pivItem

推荐答案

数据透视表.ManualUpdate[=设置]
True导致RefreshTable从数据透视表中清除数据，而不是刷新数据
False允许RefreshTable正常工作.
默认值为False.
调用过程结束后，此属性会自动重置为False(重要)

pivottable.ManualUpdate [ = setting ]
True causes RefreshTable to clear data from the pivot table, rather than refreshing it
False allows RefreshTable to work normally.
Default is False.
This property is reset to False automatically after the calling procedure ends (important)

此属性应在您进行更新之前设置为true(例如，更改枢轴项的Visible属性)
下面是一些用C#编写的代码作为示例:

This property should be set to true just before you make an update (e.g. changing pivot item Visible property)
Below is some code written in C# as an example:

    private void FilterByPivotItems(PivotField pf, List<string> pivotItemNames)
    {
        PivotItems pis = pf.ChildItems;

        if (pf.Orientation != 0)
        {
            int oldAutoSortOrder = 0;

            if (pf.AutoSortOrder != (int)Constants.xlManual)
            {
                oldAutoSortOrder = pf.AutoSortOrder;
                pf.AutoSort((int)Constants.xlManual, pf.Name);
            }

            int pivotItemsCount = pf.PivotItems().Count;

            for (int i = 1; i <= pivotItemsCount; i++)
            {
                PivotItem pi = pf.PivotItems(i);

                // check if current pivot item needs to be hidden (if it exists in pivotItemNames)
                var match = pivotItemNames.FirstOrDefault(stringToCheck => stringToCheck.Equals(pi.Value));

                if (match == null)
                {
                    TryFilterPivotItems(pi, false, true);
                }
                else
                {
                    TryFilterPivotItems(pi, true, true);
                }
            }

            if (oldAutoSortOrder != 0)
            {
                pf.AutoSort(oldAutoSortOrder, pf.Name);
            }

            PivotTable pt = pf.Parent as PivotTable;
            if (pt != null)
            {
                // changing a pivot item triggers pivot table update
                // so a refresh should be avoided cause it takes a lot and is unnecessary in this case
                pt.Update();
            }
        }
    }

    private void TryFilterPivotItems(PivotItem currentPI, bool filterValue, bool deferLayoutUpdate = false)
    {
        try
        {
            PivotField pf = currentPI.Parent;
            PivotTable pt = pf.Parent as PivotTable;

            if (currentPI.Visible != filterValue)
            {
                if (deferLayoutUpdate == true && pt != null)
                {
                    // just keep these three lines stick together, no if, no nothing (otherwise ManualUpdate will reset back to false)
                    pt.ManualUpdate = true;
                    currentPI.Visible = filterValue;

                    // this may be redundant since setting Visible property of pivot item, resets ManualUpdate to false
                    pt.ManualUpdate = false;
                }
                else
                {
                    currentPI.Visible = filterValue;
                }
            }
        }
        catch (Exception ex)
        {

        }
    }

    private void TryFilterPivotItems(PivotField pf, string itemValue, bool filterValue, bool deferLayoutUpdate = false)
    {
        try
        {
            PivotItem currentPI = pf.PivotItems(itemValue);
            TryFilterPivotItems(currentPI, filterValue, deferLayoutUpdate);
        }
        catch (Exception ex)
        {

        }
    }

结论是，ManualUpdate属性更改不会持续很长时间(在我的测试中，我看到它会尽快重置为false，因此这就是为什么我建议您随时将其设置为true的原因更改枢纽项目)

As a conclusion, ManualUpdate property change doesn't stay for long (in my tests, I could see that it gets reset to false as soon as possible, so that's why I recommended you to set it to true whenever you want to make a change for a pivot item)

有关在Excel中意味着更新的更多信息，可以检查以下内容:
数据透视刷新与更新–是真的有区别吗?

For more info on what means an update in Excel, you can check the following:
Pivot Refresh vs. Update – is there a real difference?

参考文献:
标题:使用VBA和.NET编程Excel
作者:杰夫·韦伯(Jeff Webb)，史蒂夫·桑德斯(Steve Saunders)
打印ISBN: 978-0-596-00766-9 | ISBN 10: 0-596-00766-3
电子书ISBN: 978-0-596-15951-1 | ISBN 10: 0-596-15951-X

References:
Title: Programming Excel with VBA and .NET
By: Jeff Webb, Steve Saunders
Print ISBN: 978-0-596-00766-9 | ISBN 10: 0-596-00766-3
Ebook ISBN: 978-0-596-15951-1 | ISBN 10: 0-596-15951-X

这篇关于数据透视表手动更新不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！