جدول محوري عبر نطاقات بيانات متعددة

صياغة المشكلة

تعد الجداول المحورية من أكثر الأدوات المدهشة في Excel. ولكن حتى الآن ، لسوء الحظ ، لا يمكن لأي من إصدارات Excel القيام بأمر بسيط وضروري مثل إنشاء ملخص للعديد من نطاقات البيانات الأولية الموجودة ، على سبيل المثال ، في أوراق مختلفة أو في جداول مختلفة:

قبل أن نبدأ ، دعنا نوضح بضع نقاط. بداهة ، أعتقد أنه تم استيفاء الشروط التالية في بياناتنا:

• يمكن أن تحتوي الجداول على أي عدد من الصفوف مع أي بيانات ، ولكن يجب أن يكون لها نفس الرأس.
• يجب ألا تكون هناك بيانات إضافية على الأوراق ذات الجداول المصدر. ورقة واحدة - جدول واحد. للتحكم ، أنصحك باستخدام اختصار لوحة المفاتيح CTRL+النهاية، والتي تنقلك إلى آخر خلية مستخدمة في ورقة العمل. من الناحية المثالية ، يجب أن تكون هذه هي الخلية الأخيرة في جدول البيانات. إذا عند النقر فوق CTRL+النهاية يتم تمييز أي خلية فارغة إلى اليمين أو أسفل الجدول - احذف هذه الأعمدة الفارغة إلى اليمين أو الصفوف الموجودة أسفل الجدول بعد الجدول واحفظ الملف.

الطريقة 1: إنشاء جداول للمحور باستخدام Power Query

بدءًا من إصدار 2010 لبرنامج Excel ، هناك وظيفة إضافية مجانية في Power Query يمكنها جمع أي بيانات وتحويلها ومن ثم إعطائها كمصدر لإنشاء جدول محوري. حل مشكلتنا بمساعدة هذه الوظيفة الإضافية ليس بالأمر الصعب على الإطلاق.

أولاً ، دعنا ننشئ ملفًا فارغًا جديدًا في Excel - سيتم التجميع فيه ثم سيتم إنشاء جدول محوري فيه.

ثم في علامة التبويب البيانات (إذا كان لديك Excel 2016 أو إصدار لاحق) أو علي علامة التبويب استفسار الطاقة (إذا كان لديك Excel 2010-2013) حدد الأمر إنشاء استعلام - من ملف - Excel (إحضار بيانات - من ملف - إكسل) وحدد الملف المصدر بالجداول التي سيتم جمعها:

في النافذة التي تظهر ، حدد أي ورقة (لا يهم أي منها) واضغط على الزر أدناه التغيير (تحرير):

يجب أن تفتح نافذة محرر Power Query Query أعلى Excel. على الجانب الأيمن من النافذة على اللوحة معلمات الطلب احذف جميع الخطوات التي تم إنشاؤها تلقائيًا باستثناء الأولى - مصدر (مصدر):

الآن نرى قائمة عامة لجميع الأوراق. إذا كانت هناك بعض الأوراق الجانبية الأخرى في الملف بالإضافة إلى أوراق البيانات ، فإن مهمتنا في هذه الخطوة هي تحديد الأوراق التي يجب تحميل المعلومات منها فقط ، باستثناء جميع الأوراق الأخرى التي تستخدم عامل التصفية في رأس الجدول:

احذف جميع الأعمدة باستثناء العمود البياناتعن طريق النقر بزر الماوس الأيمن فوق عنوان العمود وتحديده احذف الأعمدة الأخرى (يزيل أعمدة أخرى):

يمكنك بعد ذلك توسيع محتويات الجداول المجمعة بالنقر فوق السهم المزدوج الموجود أعلى العمود (مربع الاختيار استخدم اسم العمود الأصلي كبادئة يمكنك إيقاف تشغيله):

إذا فعلت كل شيء بشكل صحيح ، فيجب أن ترى في هذه المرحلة محتويات جميع الجداول مجمعة واحدة أسفل الأخرى:

يبقى رفع الصف الأول إلى رأس الجدول بالزر استخدم السطر الأول كرؤوس (استخدم الصف الأول كرؤوس) علامة التبويب الصفحة الرئيسية (الرئيسية) وإزالة رؤوس الجدول المكررة من البيانات باستخدام عامل تصفية:

احفظ كل شيء تم القيام به مع الأمر إغلاق وتحميل - إغلاق وتحميل ... (إغلاق وتحميل - إغلاق وتحميل إلى ...) علامة التبويب الصفحة الرئيسية (الرئيسية)، وفي النافذة التي تفتح ، حدد الخيار اتصال فقط (اتصال فقط):

كل شىء. يبقى فقط لبناء ملخص. للقيام بذلك ، انتقل إلى علامة التبويب إدراج - PivotTable (إدراج - جدول محوري)، اختر الخيار استخدم مصدر بيانات خارجي (استخدام مصدر بيانات خارجي)ثم بالنقر فوق الزر اختر اتصال، طلبنا. يتم إنشاء المزيد من المحور وتكوينه بطريقة قياسية تمامًا عن طريق سحب الحقول التي نحتاجها إلى منطقة الصفوف والأعمدة والقيم:

إذا تغيرت بيانات المصدر في المستقبل أو تمت إضافة بضع أوراق تخزين أخرى ، فسيكون ذلك كافيًا لتحديث الاستعلام وملخصنا باستخدام الأمر تحديث كافة علامة التبويب البيانات (البيانات - تحديث الكل).

الطريقة الثانية. نقوم بتوحيد الجداول باستخدام أمر UNION SQL في ماكرو

يمثل هذا الماكرو حلاً آخر لمشكلتنا ، مما يؤدي إلى إنشاء مجموعة بيانات (ذاكرة تخزين مؤقت) للجدول المحوري باستخدام الأمر وحدة لغة استعلام SQL. يجمع هذا الأمر الجداول من كل ما هو محدد في المصفوفة أسماء الأوراق أوراق الكتاب في جدول بيانات واحد. أي ، بدلاً من نسخ ولصق نطاقات من أوراق مختلفة إلى أخرى ، نقوم بنفس الشيء في ذاكرة الوصول العشوائي للكمبيوتر. ثم يضيف الماكرو ورقة جديدة بالاسم المحدد (متغير اسم ورقة النتيجة) ويقوم بإنشاء ملخص كامل (!) بناءً على ذاكرة التخزين المؤقت التي تم جمعها.

لاستخدام ماكرو ، استخدم زر Visual Basic في علامة التبويب المطور (مطور) أو اختصار لوحة المفاتيح قديم+F11. ثم نقوم بإدخال وحدة فارغة جديدة من خلال القائمة إدراج - وحدة وانسخ الكود التالي هناك:

Sub New_Multi_Table_Pivot () Dim i As Long Dim arSQL () مثل String Dim objPivotCache As PivotCache Dim objRS مثل Object Dim ResultSheetName As String Dim SheetsNames As Variant 'اسم الورقة حيث سيتم عرض المحور الناتج ResultSheetName = "Pivot" مصفوفة من الورقة أسماء بجداول المصدر SheetsNames = Array ("Alpha"، "Beta"، "Gamma"، "Delta") 'نشكل ذاكرة تخزين مؤقت للجداول من الأوراق من SheetsNames مع ActiveWorkbook ReDim arSQL (1 إلى (UBound (SheetsNames) + 1) ) بالنسبة لـ i = LBound (SheetsNames) إلى UBound (SheetsNames) arSQL (i + 1) = "SELECT * FROM [" & SheetsNames (i) & "$]" Next 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) قم بإعادة إنشاء الورقة لعرض الجدول المحوري الناتج عند خطأ استئناف التطبيق التالي. ر. Name = ResultSheetName 'يعرض ملخص ذاكرة التخزين المؤقت الذي تم إنشاؤه في هذه الورقة Set objPivotCache = ActiveWorkbook.PivotCaches.Add (xlExternal) Set objPivotCache.Recordset = objRS Set objRS = لا شيء مع wsPivot objPivotCache.CreatePivotTable (" objPivotCache = Nothing Range ("A3"). حدد End With End Sub    

يمكن بعد ذلك تشغيل الماكرو النهائي باستخدام اختصار لوحة المفاتيح قديم+F8 أو زر وحدات الماكرو في علامة التبويب المطور (المطور - وحدات الماكرو).

سلبيات هذا النهج:

• لا يتم تحديث البيانات نظرًا لعدم اتصال ذاكرة التخزين المؤقت بالجداول المصدر. إذا قمت بتغيير البيانات المصدر ، يجب عليك تشغيل الماكرو مرة أخرى وإنشاء الملخص مرة أخرى.
• عند تغيير عدد الأوراق ، من الضروري تحرير كود الماكرو (المصفوفة أسماء الأوراق).
  

لكن في النهاية نحصل على جدول محوري حقيقي كامل ، مبني على عدة نطاقات من أوراق مختلفة:

فويلا!

المذكرة التقنية: إذا تلقيت خطأ مثل "الموفر غير مسجل" عند تشغيل الماكرو ، فمن المرجح أن يكون لديك إصدار 64 بت من Excel أو تم تثبيت إصدار غير مكتمل من Office (لا يوجد وصول). لإصلاح الموقف ، استبدل الجزء في كود الماكرو:

	 الموفر = Microsoft.Jet.OLEDB.4.0 ؛  

إلى عام

	الموفر = Microsoft.ACE.OLEDB.12.0 ؛  

وقم بتنزيل محرك معالجة البيانات المجاني وتثبيته من Access من موقع Microsoft على الويب - Microsoft Access Database Engine 2010 Redistributable

الطريقة الثالثة: دمج معالج PivotTable من الإصدارات القديمة من Excel

هذه الطريقة قديمة بعض الشيء ، لكنها لا تزال تستحق الذكر. من الناحية الرسمية ، في جميع الإصدارات حتى عام 2003 ، بما في ذلك ، كان هناك خيار في معالج PivotTable "لإنشاء محور للعديد من نطاقات الدمج". ومع ذلك ، فإن التقرير الذي تم إنشاؤه بهذه الطريقة ، للأسف ، سيكون مجرد مظهر مثير للشفقة لملخص حقيقي كامل ولا يدعم العديد من "شرائح" الجداول المحورية التقليدية:

في مثل هذا المحور ، لا توجد عناوين أعمدة في قائمة الحقول ، ولا يوجد إعداد هيكل مرن ، ومجموعة الوظائف المستخدمة محدودة ، وبشكل عام ، كل هذا لا يشبه إلى حد كبير الجدول المحوري. ربما لهذا السبب ، بدءًا من عام 2007 ، قامت Microsoft بإزالة هذه الوظيفة من مربع الحوار القياسي عند إنشاء تقارير الجدول المحوري. الآن هذه الميزة متاحة فقط من خلال زر مخصص معالج PivotTable(معالج الجدول المحوري)، والتي ، إذا رغبت في ذلك ، يمكن إضافتها إلى شريط أدوات الوصول السريع عبر ملف - خيارات - تخصيص شريط أدوات الوصول السريع - كافة الأوامر (ملف - خيارات - تخصيص شريط أدوات الوصول السريع - كافة الأوامر):

بعد النقر فوق الزر المضاف ، تحتاج إلى تحديد الخيار المناسب في الخطوة الأولى من المعالج:

ثم في النافذة التالية ، حدد كل نطاق بدوره وأضفه إلى القائمة العامة:

لكن ، مرة أخرى ، هذا ليس ملخصًا كاملًا ، لذلك لا تتوقع الكثير منه. يمكنني أن أوصي بهذا الخيار فقط في حالات بسيطة للغاية.

• إنشاء التقارير باستخدام جداول PivotTable
• قم بإعداد العمليات الحسابية في PivotTables
• ما هي وحدات الماكرو ، وكيفية استخدامها ، ومكان نسخ كود VBA ، وما إلى ذلك.
• جمع البيانات من أوراق متعددة إلى ورقة واحدة (إضافة PLEX)