جدول محوری در چندین محدوده داده

فرمول بندی مسئله

Pivot table یکی از شگفت انگیزترین ابزارهای اکسل است. اما تا کنون، متأسفانه، هیچ یک از نسخه‌های اکسل نمی‌توانند چنین کاری ساده و ضروری را به‌عنوان ایجاد خلاصه‌ای برای چندین محدوده داده اولیه که مثلاً در برگه‌های مختلف یا در جداول مختلف قرار دارند، انجام دهند:

قبل از شروع، اجازه دهید چند نکته را روشن کنیم. پیش از این، من معتقدم که شرایط زیر در داده های ما وجود دارد:

• جداول می توانند هر تعداد ردیف با هر داده ای داشته باشند، اما باید سرصفحه یکسانی داشته باشند.
• در برگه‌های دارای جداول منبع نباید داده اضافی وجود داشته باشد. یک ورق - یک میز. برای کنترل، به شما توصیه می کنم از میانبر صفحه کلید استفاده کنید کلیدهای Ctrl+پایان، که شما را به آخرین سلول استفاده شده در کاربرگ منتقل می کند. در حالت ایده آل، این باید آخرین سلول در جدول داده باشد. اگر وقتی روی کلیدهای Ctrl+پایان هر سلول خالی در سمت راست یا زیر جدول برجسته می شود - این ستون های خالی در سمت راست یا ردیف های زیر جدول بعد از جدول را حذف کنید و فایل را ذخیره کنید.

روش 1: با استفاده از Power Query جداول برای یک Pivot بسازید

از نسخه 2010 برای اکسل، یک افزونه رایگان Power Query وجود دارد که می تواند هر داده ای را جمع آوری و تبدیل کند و سپس آن را به عنوان منبعی برای ساخت جدول محوری ارائه دهد. حل مشکل ما با کمک این افزونه اصلا سخت نیست.

ابتدا یک فایل خالی جدید در اکسل ایجاد می کنیم – اسمبلی در آن انجام می شود و سپس یک جدول محوری در آن ایجاد می شود.

سپس روی زبانه داده ها (اگر اکسل 2016 یا جدیدتر دارید) یا روی برگه پرس و جو برق (اگر اکسل 2010-2013 دارید) دستور را انتخاب کنید ایجاد پرس و جو – از فایل – اکسل (دریافت داده - از فایل - اکسل) و فایل منبع را با جداول جمع آوری شده مشخص کنید:

در پنجره ای که ظاهر می شود، هر برگه ای را انتخاب کنید (مهم نیست کدام یک) و دکمه زیر را فشار دهید تغییر دادن (ویرایش):

پنجره Power Query Query Editor باید در بالای اکسل باز شود. در سمت راست پنجره روی پانل درخواست پارامترها تمام مراحل ایجاد شده به طور خودکار به جز مرحله اول را حذف کنید - منبع (منبع):

اکنون یک لیست کلی از تمام برگه ها را می بینیم. اگر علاوه بر برگه‌های داده، برگه‌های جانبی دیگری نیز در فایل وجود داشته باشد، در این مرحله وظیفه ما این است که تنها برگه‌هایی را انتخاب کنیم که اطلاعات باید از آن‌ها بارگیری شود، بدون در نظر گرفتن سایر صفحات با استفاده از فیلتر در هدر جدول:

تمام ستون ها به جز ستون را حذف کنید داده هابا کلیک راست روی عنوان ستون و انتخاب ستون های دیگر را حذف کنید (برداشتن ستون های دیگر):

سپس می توانید محتویات جداول جمع آوری شده را با کلیک بر روی فلش دوتایی در بالای ستون گسترش دهید (چک باکس از نام ستون اصلی به عنوان پیشوند استفاده کنید می توانید آن را خاموش کنید):

اگر همه کارها را به درستی انجام داده اید، در این مرحله باید محتویات تمام جداول را که یکی زیر دیگری جمع آوری شده اند ببینید:

باقی مانده است که ردیف اول را با دکمه به عنوان جدول بالا ببرید از خط اول به عنوان سرصفحه استفاده کنید (از ردیف اول به عنوان سرصفحه استفاده کنید) برگ صفحه اصلی (خانه) و هدرهای جدول تکراری را با استفاده از یک فیلتر از داده ها حذف کنید:

تمام کارهای انجام شده با دستور را ذخیره کنید بستن و بارگیری – بستن و بارگیری در… (بستن و بارگذاری - بستن و بارگذاری به…) برگ صفحه اصلی (خانه)و در پنجره باز شده گزینه را انتخاب کنید فقط اتصال (فقط اتصال):

همه چيز. تنها برای ساختن یک خلاصه باقی مانده است. برای انجام این کار، به تب بروید درج - PivotTable (درج - جدول محوری)، گزینه را انتخاب کنید از منبع داده خارجی استفاده کنید (از منبع داده خارجی استفاده کنید)و سپس با کلیک بر روی دکمه اتصال را انتخاب کنید، درخواست ما. ایجاد و پیکربندی بیشتر Pivot به روشی کاملاً استاندارد با کشیدن فیلدهای مورد نیاز در ردیف‌ها، ستون‌ها و ناحیه مقادیر انجام می‌شود:

اگر اطلاعات منبع در آینده تغییر کند یا چند برگه فروشگاه دیگر اضافه شود، کافی است پرس و جو و خلاصه خود را با استفاده از دستور به روز کنیم. همه را بازخوانی کنید برگ داده ها (داده ها - بازخوانی همه).

روش 2. جداول را با دستور UNION SQL در یک ماکرو یکی می کنیم

راه حل دیگری برای مشکل ما توسط این ماکرو نشان داده شده است که با استفاده از دستور یک مجموعه داده (کش) برای جدول محوری ایجاد می کند. وحدت زبان پرس و جو SQL. این دستور جداول را از همه مشخص شده در آرایه ترکیب می کند Sheetnames برگه های کتاب در یک جدول داده واحد. یعنی به جای کپی و چسباندن فیزیکی بازه های مختلف برگه به ​​یک، همین کار را در رم کامپیوتر انجام می دهیم. سپس ماکرو یک صفحه جدید با نام داده شده (متغیر) اضافه می کند ResultSheetName) و بر اساس کش جمع آوری شده یک خلاصه کامل (!) بر روی آن ایجاد می کند.

برای استفاده از ماکرو، از دکمه ویژوال بیسیک در تب استفاده کنید توسعه دهنده (توسعه دهنده) یا میانبر صفحه کلید دگرساز+F11. سپس یک ماژول خالی جدید را از طریق منو وارد می کنیم درج - ماژول و کد زیر را در آنجا کپی کنید:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache به عنوان PivotCache Dim objRS به عنوان Object Dim ResultSheetName به عنوان رشته Dim SheetsNames به عنوان متغیر 'نام برگه که در آن پیوت حاصل شده نمایش داده می شود. نام‌ها با جداول منبع SheetsNames = آرایه ("آلفا"، "بتا"، "گاما"، "دلتا") 'ما یک کش برای جداول از برگه‌های SheetsNames با ActiveWorkbook ReDim arSQL(1 به (UBound(SheetsNames) + 1) تشکیل می‌دهیم. ) برای i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" بعدی i Set objRS = CreateObject("ADODB.Recordset") objRS .باز کردن Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" "")، vbNullString ) پایان با 're-create the sheet to display the pivot table. On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo تی Name = ResultSheetName 'نمایش خلاصه حافظه پنهان تولید شده در این برگه Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivotaachet. objPivotCache = Nothing Range ("A3"). End With End Sub را انتخاب کنید    

سپس ماکرو تمام شده را می توان با یک میانبر صفحه کلید اجرا کرد دگرساز+F8 یا دکمه Macros در برگه توسعه دهنده (توسعه دهنده - ماکروها).

معایب این رویکرد:

• داده ها به روز نمی شوند زیرا حافظه پنهان هیچ ارتباطی با جداول منبع ندارد. اگر داده های منبع را تغییر دهید، باید ماکرو را دوباره اجرا کنید و خلاصه را دوباره بسازید.
• هنگام تغییر تعداد برگه ها، لازم است کد ماکرو (آرایه Sheetnames).
  

اما در پایان ما یک جدول محوری کامل واقعی را دریافت می کنیم که بر روی چندین محدوده از صفحات مختلف ساخته شده است:

وجود دارد!

یادداشت فنی: اگر هنگام اجرای ماکرو با خطایی مانند «ارائه‌دهنده ثبت نشده» مواجه شدید، به احتمال زیاد نسخه 64 بیتی اکسل دارید یا نسخه ناقص آفیس نصب شده است (بدون دسترسی). برای رفع این وضعیت، قطعه را در کد ماکرو جایگزین کنید:

	 ارائه دهنده=Microsoft.Jet.OLEDB.4.0;  

به:

	ارائه دهنده=Microsoft.ACE.OLEDB.12.0;  

و موتور پردازش داده رایگان را از اکسس از وب سایت مایکروسافت دانلود و نصب کنید – Microsoft Access Database Engine 2010 Redistributable

روش 3: جادوگر PivotTable را از نسخه های قدیمی اکسل ادغام کنید

این روش کمی قدیمی است، اما هنوز هم قابل ذکر است. به طور رسمی، در تمام نسخه‌های تا سال 2003 و از جمله، گزینه‌ای در PivotTable Wizard وجود داشت که «یک محور برای چندین محدوده ادغام ایجاد کند». با این حال، متأسفانه گزارشی که به این شکل ساخته شده است، فقط ظاهری رقت انگیز از یک خلاصه کامل واقعی خواهد بود و از بسیاری از «تراشه‌های» جداول محوری معمولی پشتیبانی نمی‌کند:

در چنین محوری، هیچ عنوان ستونی در لیست فیلد وجود ندارد، هیچ تنظیم ساختار انعطاف پذیری وجود ندارد، مجموعه توابع استفاده شده محدود است، و به طور کلی، همه اینها خیلی شبیه به جدول محوری نیست. شاید به همین دلیل است که از سال 2007، مایکروسافت این تابع را از گفتگوی استاندارد هنگام ایجاد گزارش های جدول محوری حذف کرد. اکنون این ویژگی فقط از طریق یک دکمه سفارشی در دسترس است جادوگر PivotTable(جادوگر جدول محوری)، که در صورت تمایل می توان از طریق آن به نوار ابزار دسترسی سریع اضافه کرد فایل - گزینه ها - سفارشی کردن نوار ابزار دسترسی سریع - همه دستورات (فایل - گزینه ها - سفارشی کردن نوار ابزار دسترسی سریع - همه دستورات):

پس از کلیک بر روی دکمه اضافه شده، باید گزینه مناسب را در مرحله اول ویزارد انتخاب کنید:

و سپس در پنجره بعدی هر محدوده را به نوبت انتخاب کرده و به لیست کلی اضافه کنید:

اما باز هم، این یک خلاصه کامل نیست، بنابراین انتظار زیادی از آن نداشته باشید. من می توانم این گزینه را فقط در موارد بسیار ساده توصیه کنم.

• ایجاد گزارش با PivotTables
• محاسبات را در PivotTables تنظیم کنید
• ماکروها چیست، چگونه از آنها استفاده کنیم، کجا کد VBA را کپی کنیم و غیره.
• جمع آوری داده ها از چندین برگه به ​​یک (افزونه PLEX)