کاهش اندازه فایل برای کتابهای کاری با جدول محوری در اکسل

Question

PivotTable ها برای انواع خاصی از تجزیه و تحلیل داده ها عالی هستند. از آنجایی که PivotTable ها مقدار زیادی اعداد را خرد می کنند، یکی از تکنیک هایی که اکسل برای پردازش سریعتر آنها استفاده می کند، ایجاد یک "داده داده میانی" برای کار با آن است. این مجموعه داده میانی، به طور پیش‌فرض، همراه با کاربرگ ذخیره می‌شود، بنابراین PivotTables می‌تواند اندازه کتاب‌های کاری شما را، گاهی اوقات به‌طور چشمگیری افزایش دهد.

اگر کتاب کار شما حاوی چندین PivotTable است که همه بر اساس یک منبع داده واحد هستند، اکسل ممکن است به جای استفاده از یک مجموعه داده میانی، یک مجموعه داده میانی برای هر PivotTable ایجاد کند. این، البته، می تواند اندازه کتاب کار شما را به سرعت افزایش دهد.

می‌توانید با تغییر گزینه‌هایی که هنگام ایجاد PivotTable استفاده می‌کنید، نحوه ایجاد مجموعه داده‌های میانی اکسل را کنترل کنید.

شکل 1. تب Data از کادر محاوره ای PivotTable Options.

1. PivotTable خود را همانطور که به طور معمول انجام می دهید ایجاد کنید.
2. برگه Options (Excel 2007 و Excel 2010) یا زبانه Analyze (Excel 2013) یا Tab Analyze PivotTable (نسخه های بعدی اکسل) روبان را نمایش دهید. (این برگه فقط زمانی قابل مشاهده است که یک سلول را در PivotTable انتخاب کنید.)
3. در انتهای سمت چپ نوار روی ابزار Options در گروه PivotTable کلیک کنید. اکسل کادر محاوره ای PivotTable Options را نمایش می دهد.
4. مطمئن شوید که تب Data نمایش داده شده است. (شکل 1 را ببینید.)
5. تیک Save Source Data with File را پاک کنید.
6. تیک Refresh on Open را انتخاب کنید.
7. روی OK کلیک کنید تا کادر محاوره ای PivotTable Options بسته شود.

اگر نمی‌خواهید، نیازی به انتخاب کادر Refresh on Open (مرحله 6) ندارید، اما اگر نمی‌خواهید، باید به یاد داشته باشید که هر بار که کتاب کار را باز می‌کنید، PivotTable را به‌صورت دستی بازخوانی کنید.

اگر قبلاً تعداد زیادی PivotTable در کتاب کار خود دارید و نمی‌خواهید دوباره فرآیند ایجاد آنها را انجام دهید، می‌توانید از یک ماکرو برای عبور از PivotTables استفاده کنید و شاخص ذخیره را تغییر دهید و ذخیره داده‌های میانی را خاموش کنید. به دیسک ماکرو زیر این وظایف را انجام می دهد:

Sub PTReduceSize()
    Dim wks As Worksheet
    Dim PT As PivotTable

    For Each wks In ActiveWorkbook.Worksheets
        For Each PT In wks.PivotTables
            PT.RefreshTable
            PT.CacheIndex = 1
            PT.SaveData = False
        Next
    Next
End Sub

هنگامی که ماکرو اجرا می شود (طولانی طول نمی کشد)، باید کتاب کار خود را با استفاده از گزینه Save As ذخیره کنید. با این کار یک فایل کتاب کار جدید می نویسید و می توانید مقایسه کنید که این تغییر چقدر باعث کاهش اندازه کتاب کار شما شده است. با این حال، به یاد داشته باشید که با ذخیره نشدن داده های میانی در دیسک، زمانی که برای اولین بار کتاب کار را باز می کنید، تازه سازی PivotTables بیشتر طول می کشد.