أنشئ طاولات برؤوس مختلفة من كتب متعددة

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

لدينا عدة ملفات (في مثالنا - 4 قطع ، في الحالة العامة - أي عدد تريده) في مجلد واحد التقارير :

في الداخل ، تبدو هذه الملفات كما يلي:

حيث:

• ورقة البيانات التي نحتاجها تسمى دائمًا الصور، ولكن يمكن أن يكون في أي مكان في المصنف.
  
• ما وراء الورقة الصور قد يحتوي كل كتاب على أوراق أخرى.
• تحتوي الجداول التي تحتوي على بيانات على عدد مختلف من الصفوف وقد تبدأ بصف مختلف في ورقة العمل.
• قد تختلف أسماء الأعمدة نفسها في جداول مختلفة (على سبيل المثال ، الكمية = الكمية = الكمية).
• يمكن ترتيب الأعمدة في الجداول بترتيب مختلف.

المهمة: جمع بيانات المبيعات من جميع الملفات من الورقة الصور في جدول مشترك واحد من أجل بناء ملخص أو أي تحليلات أخرى عليه لاحقًا.

الخطوة الأولى. تحضير دليل بأسماء الأعمدة

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

نقوم بتحويل هذه القائمة إلى جدول "ذكي" ديناميكي باستخدام زر التنسيق كجدول في علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - تنسيق كجدول) أو اختصار لوحة المفاتيح CTRL+T وقم بتحميله في Power Query باستخدام الأمر البيانات - من جدول / نطاق (بيانات - من جدول / نطاق). في الإصدارات الأخيرة من Excel ، تمت إعادة تسميته إلى بأوراق الشجر (من الورقة).

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

= Table.ToRows (المصدر)

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

سنحتاج إلى هذا النوع من البيانات بعد ذلك بقليل ، عند إعادة تسمية جماعية للرؤوس من جميع الجداول المحملة.

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

الخطوة 2. نقوم بتحميل كل شيء من جميع الملفات كما هو

لنقم الآن بتحميل محتويات جميع ملفاتنا من المجلد - في الوقت الحالي ، كما هو. اختيار الفرق بيانات - إحضار بيانات - من ملف - من مجلد (بيانات - إحضار بيانات - من ملف - من مجلد) ثم المجلد حيث توجد كتبنا المصدر.

في نافذة المعاينة ، انقر فوق تحول (تحول) or التغيير (تحرير):

ثم قم بتوسيع محتويات جميع الملفات التي تم تنزيلها (الثنائية) زر مع أسهم مزدوجة في عنوان العمود وصف المنتج:

Power Query في مثال الملف الأول (فوستوك.xlsx) سيطلب منا اسم الورقة التي نريد أخذها من كل مصنف - اختر الصور واضغط على موافق:

بعد ذلك (في الواقع) ، ستحدث عدة أحداث غير واضحة للمستخدم ، وتظهر نتائجها بوضوح في اللوحة اليمنى:

1. سيأخذ Power Query الملف الأول من المجلد (سيكون لدينا Vostok.xlsx - انظر تعريف مثال على ملف) كمثال ويستورد محتواه عن طريق إنشاء استعلام تحويل ملف العينة. سيحتوي هذا الاستعلام على بعض الخطوات البسيطة مثل مصدر (وصول الملف) قائمة الإختيارات (اختيار الورقة) وربما رفع العناوين. يمكن لهذا الطلب تحميل البيانات من ملف واحد محدد فقط فوستوك.xlsx.
   
2. بناءً على هذا الطلب ، سيتم إنشاء الوظيفة المرتبطة به تحويل الملف (يشار إليها برمز مميز f_x) ، حيث لن يكون الملف المصدر ثابتًا ، بل قيمة متغيرة - معلمة. وبالتالي ، يمكن لهذه الوظيفة استخراج البيانات من أي كتاب ننزلق إليه كحجة.
   
3. سيتم تطبيق الوظيفة بدوره على كل ملف (ثنائي) من العمود وصف المنتج - الخطوة هي المسؤولة عن هذا استدعاء وظيفة مخصصة في استعلامنا الذي يضيف عمودًا إلى قائمة الملفات تحويل الملف مع نتائج الاستيراد من كل مصنف:

   

4. تتم إزالة الأعمدة الزائدة.
5. يتم توسيع محتويات الجداول المتداخلة (الخطوة عمود الجدول الممتد) - ونرى النتائج النهائية لجمع البيانات من جميع الكتب:

   
   

الخطوة 3. الصنفرة

توضح لقطة الشاشة السابقة بوضوح أن التجميع المباشر "كما هو" تبين أنه رديء الجودة:

• يتم عكس الأعمدة.
• العديد من الأسطر الإضافية (فارغة وليست فقط).
• لا يُنظر إلى رؤوس الجدول على أنها رؤوس ويتم خلطها بالبيانات.
  

يمكنك حل كل هذه المشاكل بسهولة شديدة - فقط قم بتعديل استعلام تحويل نموذج الملف. ستقع جميع التعديلات التي نجريها عليه تلقائيًا في وظيفة تحويل الملف المرتبطة ، مما يعني أنه سيتم استخدامها لاحقًا عند استيراد البيانات من كل ملف.

عن طريق فتح طلب تحويل ملف العينة، أضف خطوات لتصفية الصفوف غير الضرورية (على سبيل المثال ، حسب العمود Column2) ورفع العناوين بالزر استخدم السطر الأول كرؤوس (استخدم الصف الأول كرؤوس). سيبدو الجدول أفضل بكثير.

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

= Table.RenameColumns (# "رؤوس مرتفعة" ، رؤوس ، MissingField.Ignore)

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

في الواقع ، هذا كل شيء.

العودة إلى الطلب التقارير سنرى صورة مختلفة تمامًا - أجمل بكثير من الصورة السابقة:

• ما هو Power Query و Power Pivot و Power BI ولماذا يحتاجها مستخدم Excel
  
• جمع البيانات من جميع الملفات في مجلد معين
• تجميع البيانات من جميع أوراق الكتاب في جدول واحد