تجميع الجداول من ملفات Excel المختلفة باستخدام Power Query

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

دعنا نلقي نظرة على حل جميل لأحد المواقف القياسية للغاية التي يواجهها معظم مستخدمي Excel عاجلاً أم آجلاً: تحتاج إلى جمع البيانات بشكل سريع وتلقائي من عدد كبير من الملفات في جدول نهائي واحد. 

لنفترض أن لدينا المجلد التالي ، والذي يحتوي على عدة ملفات بها بيانات من مدن فرعية:

لا يهم عدد الملفات وقد يتغير في المستقبل. يحتوي كل ملف على ورقة مسماة المبيعات حيث يوجد جدول البيانات:

يختلف عدد الصفوف (الطلبات) في الجداول ، بالطبع ، ولكن مجموعة الأعمدة قياسية في كل مكان.

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

نختار الأسلحة

بالنسبة للحل ، نحتاج إلى أحدث إصدار من Excel 2016 (الوظائف الضرورية مضمنة بالفعل بشكل افتراضي) أو الإصدارات السابقة من Excel 2010-2013 مع تثبيت الوظيفة الإضافية المجانية استفسار الطاقة من Microsoft (قم بتنزيله هنا). Power Query هي أداة فائقة المرونة وقوية لتحميل البيانات إلى Excel من العالم الخارجي ، ثم تجريدها ومعالجتها. يدعم Power Query جميع مصادر البيانات الموجودة تقريبًا - من الملفات النصية إلى SQL وحتى Facebook

إذا لم يكن لديك برنامج Excel 2013 أو 2016 ، فلا يمكنك قراءة المزيد (مجرد مزاح). في الإصدارات القديمة من Excel ، لا يمكن إنجاز مثل هذه المهمة إلا عن طريق برمجة ماكرو في Visual Basic (وهو أمر صعب للغاية بالنسبة للمبتدئين) أو عن طريق النسخ اليدوي الرتيب (الذي يستغرق وقتًا طويلاً وينتج عنه أخطاء).

الخطوة 1. استيراد ملف واحد كعينة

أولاً ، دعنا نستورد البيانات من مصنف واحد كمثال ، حتى يلتقط Excel الفكرة. للقيام بذلك ، قم بإنشاء مصنف فارغ جديد و ...

• إذا كان لديك Excel 2016 ، فافتح علامة التبويب البيانات وثم تكوين استعلام - من ملف - من كتاب (بيانات - استعلام جديد - من ملف - من Excel)
• إذا كان لديك Excel 2010-2013 مع تثبيت الوظيفة الإضافية Power Query ، فافتح علامة التبويب استفسار الطاقة واختر منها من ملف - من كتاب (من ملف - من Excel)

بعد ذلك ، في النافذة التي تفتح ، انتقل إلى مجلدنا الذي يحتوي على التقارير وحدد أيًا من ملفات المدينة (لا يهم أي منها ، لأنها كلها نموذجية). بعد بضع ثوانٍ ، يجب أن تظهر نافذة Navigator ، حيث تحتاج إلى تحديد الورقة التي نحتاجها (المبيعات) على الجانب الأيسر ، وسيتم عرض محتوياتها على الجانب الأيمن:

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

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

• تصفية البيانات غير الضرورية والأسطر الفارغة والأسطر التي بها أخطاء
• فرز البيانات حسب عمود واحد أو أكثر
• تخلص من التكرار
• قسّم النص اللاصق على أعمدة (حسب المحددات ، وعدد الأحرف ، وما إلى ذلك)
• ضع النص بالترتيب (أزل المسافات الزائدة ، والحالة الصحيحة ، وما إلى ذلك)
• تحويل أنواع البيانات بكل طريقة ممكنة (تحويل الأرقام مثل النص إلى أرقام عادية والعكس صحيح)
• قم بنقل (تدوير) الجداول وتوسيع الجداول العرضية ثنائية الأبعاد إلى جداول مسطحة
• أضف أعمدة إضافية إلى الجدول واستخدم الصيغ والوظائف فيها باستخدام لغة M المضمنة في Power Query.
• ...

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

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

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

يمكنك استبعاد السطور التي بها أخطاء أو سطور فارغة ، وكذلك المديرين غير الضروريين أو العملاء ، باستخدام عامل تصفية بسيط:

علاوة على ذلك ، يتم إصلاح جميع التحويلات التي تم إجراؤها في اللوحة اليمنى ، حيث يمكن دائمًا إرجاعها (عرضية) أو تغيير معلماتها (الترس):

خفيف وأنيق ، أليس كذلك؟

الخطوة 2. لنحول طلبنا إلى دالة

من أجل تكرار جميع تحويلات البيانات التي تم إجراؤها لكل كتاب تم استيراده لاحقًا ، نحتاج إلى تحويل طلبنا الذي تم إنشاؤه إلى وظيفة ، والتي سيتم بعد ذلك تطبيقها ، بدورها ، على جميع ملفاتنا. القيام بذلك هو في الواقع بسيط للغاية.

في محرر الاستعلام ، انتقل إلى علامة التبويب عرض وانقر فوق الزر محرر متقدم (عرض - محرر متقدم). يجب أن تفتح نافذة حيث سيتم كتابة جميع إجراءاتنا السابقة في شكل رمز بلغة M. يرجى ملاحظة أن المسار إلى الملف الذي قمنا باستيراده للمثال مشفر بشكل ثابت في الكود:

لنقم الآن ببعض التعديلات:

معناها بسيط: السطر الأول (مسار الملف) => يحول إجراءاتنا إلى دالة ذات وسيطة مسار الملف، وفيما يلي نغير المسار الثابت إلى قيمة هذا المتغير. 

الجميع. انقر فوق نهاية ويجب أن ترى هذا:

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

الخطوة 3. تجميع كل الملفات

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

انقر التغيير (تحرير) ومرة أخرى ندخل إلى نافذة محرر الاستعلام المألوفة.

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

بعد النقر على OK يجب إضافة العمود الذي تم إنشاؤه إلى جدولنا على اليمين.

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

والآن "لحظة نجاح باهر" - انقر فوق الرمز الذي يحتوي على أسهمه الخاصة في الزاوية اليمنى العليا للعمود المضاف باستخدام وظيفتنا:

… قم بإلغاء التحديد استخدم اسم العمود الأصلي كبادئة (استخدم اسم العمود الأصلي كبادئة)ثم انقر OK. وستقوم وظيفتنا بتحميل ومعالجة البيانات من كل ملف ، باتباع الخوارزمية المسجلة وجمع كل شيء في جدول مشترك:

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

الجميع! انقر فوق الصفحة الرئيسية - إغلاق وتحميل (الصفحة الرئيسية - إغلاق وتحميل). سيتم تحميل جميع البيانات التي تم جمعها بواسطة الاستعلام لجميع المدن إلى ورقة Excel الحالية بتنسيق "الجدول الذكي":

لا يلزم حفظ الاتصال الذي تم إنشاؤه ووظيفة التجميع الخاصة بنا بشكل منفصل بأي طريقة - يتم حفظهما مع الملف الحالي بالطريقة المعتادة.

في المستقبل ، مع أي تغييرات في المجلد (إضافة المدن أو إزالتها) أو في الملفات (تغيير عدد الأسطر) ، يكفي النقر بزر الماوس الأيمن مباشرة على الجدول أو على الاستعلام في اللوحة اليمنى وتحديد يأمر تحديث وحفظ (ينعش) - سيعيد Power Query "إعادة إنشاء" جميع البيانات مرة أخرى في بضع ثوانٍ.

PS

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

1. اختار إنشاء طلب - من ملف - من مجلد - تحديد مجلد - موافق
2. بعد ظهور قائمة الملفات ، اضغط على التغيير
3. في نافذة محرر الاستعلام ، قم بتوسيع العمود الثنائي بسهم مزدوج وحدد اسم الورقة المراد أخذها من كل ملف

و هذا كل شيء! أغنية!

• إعادة تصميم الجدول الترافقي ليصبح مسطحًا مناسبًا لبناء الطاولات المحورية
• إنشاء مخطط فقاعي متحرك في Power View
• ماكرو لتجميع الأوراق من ملفات Excel المختلفة في ملف واحد