المحتويات
صياغة المشكلة
كبيانات إدخال ، لدينا ملف Excel ، حيث تحتوي إحدى الأوراق على عدة جداول بها بيانات مبيعات من النموذج التالي:
لاحظ أن:
- جداول بأحجام مختلفة ومجموعات مختلفة من المنتجات والمناطق في صفوف وأعمدة دون أي فرز.
- يمكن إدراج أسطر فارغة بين الجداول.
- يمكن أن يكون عدد الجداول أيًا.
اثنين من الافتراضات الهامة. يفترض أن:
- فوق كل جدول ، في العمود الأول ، يوجد اسم المدير الذي يوضح الجدول مبيعاته (إيفانوف ، بتروف ، سيدوروف ، إلخ).
- تتم كتابة أسماء السلع والمناطق في جميع الجداول بنفس الطريقة - مع دقة حالة الأحرف.
الهدف النهائي هو جمع البيانات من جميع الجداول في جدول موحد مسطح واحد ، مناسب للتحليل اللاحق وبناء ملخص ، على سبيل المثال في هذا الجدول:
الخطوة 1. الاتصال بالملف
لنقم بإنشاء ملف Excel فارغ جديد وحدده في علامة التبويب البيانات أمر إحضار البيانات - من ملف - من الكتاب (البيانات - من ملف - من المصنف). حدد موقع الملف المصدر ببيانات المبيعات ثم في نافذة الملاح حدد الورقة التي نحتاجها وانقر فوق الزر تحويل البيانات (تحويل البيانات):
نتيجة لذلك ، يجب تحميل جميع البيانات منه في محرر Power Query:
الخطوة الثانية. نظف سلة المهملات
حذف الخطوات التي تم إنشاؤها تلقائيًا نوع معدل (نوع تم تغييره) и رؤوس مرتفعة (رؤوس تمت ترقيتها) والتخلص من الأسطر والخطوط الفارغة ذات المجاميع باستخدام مرشح فارغة и المجموع من العمود الأول. ونتيجة لذلك نحصل على الصورة التالية:
الخطوة 3. إضافة المديرين
من أجل فهم مكان مبيعاتهم لاحقًا ، من الضروري إضافة عمود إلى جدولنا ، حيث سيكون هناك لقب مقابل في كل صف. لهذا:
1. دعنا نضيف عمودًا إضافيًا بأرقام الأسطر باستخدام الأمر إضافة عمود - عمود الفهرس - من 0 (إضافة عمود - عمود الفهرس - من 0).
2. أضف عمودًا باستخدام صيغة باستخدام الأمر إضافة عمود - عمود مخصص (إضافة عمود - عمود مخصص) وإدخال البناء التالي هناك:
منطق هذه الصيغة بسيط - إذا كانت قيمة الخلية التالية في العمود الأول هي "المنتج" ، فهذا يعني أننا قد تعثرنا في بداية جدول جديد ، لذلك نعرض قيمة الخلية السابقة مع اسم المدير. خلاف ذلك ، فإننا لا نعرض أي شيء ، أي لاغية.
للحصول على الخلية الأصلية مع الاسم الأخير ، نشير أولاً إلى الجدول من الخطوة السابقة # "تمت إضافة الفهرس"، ثم حدد اسم العمود الذي نحتاجه [العمود 1] بين قوسين مربعين ورقم الخلية في هذا العمود بين قوسين معقوفين. سيكون رقم الخلية أقل من الرقم الحالي الذي نأخذه من العمود فهرس، على التوالي.
3. يبقى لملء الخلايا الفارغة ب فارغة أسماء من خلايا أعلى مع الأمر تحويل - تعبئة - أسفل (تحويل - تعبئة - أسفل) وحذف العمود الذي لم تعد هناك حاجة إليه بالفهارس والصفوف ذات الأسماء الأخيرة في العمود الأول. نتيجة لذلك ، نحصل على:
الخطوة 4. التجميع في جداول منفصلة من قبل المديرين
الخطوة التالية هي تجميع الصفوف لكل مدير في جداول منفصلة. للقيام بذلك ، في علامة التبويب التحويل ، استخدم الأمر تجميع حسب (تحويل - تجميع حسب) وفي النافذة التي تفتح ، حدد عمود المدير والعملية كل الصفوف (جميع الصفوف) لتجميع البيانات ببساطة دون تطبيق أي وظيفة تجميع على منهم (مجموع ، متوسط ، إلخ). P.):
نتيجة لذلك ، نحصل على جداول منفصلة لكل مدير:
الخطوة 5: تحويل الجداول المتداخلة
الآن نعطي الجداول الموجودة في كل خلية من العمود الناتج كل المعلومات في شكل لائق.
أولاً ، احذف عمودًا لم يعد مطلوبًا في كل جدول مدير مبيعات. نستخدمها مرة أخرى عمود مخصص علامة التبويب تحول (تحويل - عمود مخصص) والصيغة التالية:
بعد ذلك ، بعمود محسوب آخر ، نرفع الصف الأول في كل جدول إلى العناوين:
وأخيرًا ، نجري التحويل الرئيسي - نفتح كل جدول باستخدام الوظيفة M. الجدول. UnpivotOtherColumns:
ستنتقل أسماء المناطق من الرأس إلى عمود جديد وسنحصل على جدول أضيق ، ولكن في نفس الوقت ، جدول طبيعي أطول. خلايا فارغة مع فارغة يتم تجاهلها.
للتخلص من الأعمدة الوسيطة غير الضرورية لدينا:
الخطوة 6 قم بتوسيع الجداول المتداخلة
يبقى توسيع كل الجداول المتداخلة التي تمت تسويتها في قائمة واحدة باستخدام الزر ذي الأسهم المزدوجة في رأس العمود:
... وأخيراً حصلنا على ما أردناه:
يمكنك تصدير الجدول الناتج مرة أخرى إلى Excel باستخدام الأمر الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ...).
- أنشئ طاولات برؤوس مختلفة من كتب متعددة
- جمع البيانات من جميع الملفات في مجلد معين
- تجميع البيانات من جميع أوراق الكتاب في جدول واحد