لنفترض أنك تدير عدة مشاريع بميزانيات مختلفة وترغب في تصور التكاليف الخاصة بك لكل منها. أي من جدول المصدر هذا:
.. احصل على شيء مثل هذا:
بمعنى آخر ، تحتاج إلى توزيع الميزانية على أيام كل مشروع والحصول على نسخة مبسطة من مخطط جانت للمشروع. إن القيام بذلك بيديك طويل وممل ، وحدات الماكرو صعبة ، لكن Power Query لـ Excel في مثل هذه الحالة يُظهر قوتها بكل مجدها.
استفسار الطاقة هي وظيفة إضافية من Microsoft يمكنها استيراد البيانات إلى Excel من أي مصدر تقريبًا ثم تحويلها بمجموعة من الطرق المختلفة. في Excel 2016 ، تكون هذه الوظيفة الإضافية مضمنة بالفعل بشكل افتراضي ، وبالنسبة لبرنامج Excel 2010-2013 ، يمكن تنزيلها من موقع Microsoft على الويب ثم تثبيتها على جهاز الكمبيوتر الخاص بك.
أولاً ، دعنا نحول جدولنا الأصلي إلى جدول "ذكي" باختيار الأمر تنسيق كجدول علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - تنسيق كجدول) أو بالضغط على اختصار لوحة المفاتيح CTRL+T :
ثم انتقل إلى علامة التبويب البيانات (إذا كان لديك Excel 2016) أو في علامة التبويب استفسار الطاقة (إذا كان لديك Excel 2010-2013 وقمت بتثبيت Power Query كوظيفة إضافية منفصلة) وانقر فوق الزر من جدول / نطاق. :
يتم تحميل جدولنا الذكي في محرر استعلام Power Query ، حيث تتمثل الخطوة الأولى في إعداد تنسيقات الأرقام لكل عمود باستخدام القوائم المنسدلة في رأس الجدول:
لحساب الميزانية اليومية ، تحتاج إلى حساب مدة كل مشروع. للقيام بذلك ، حدد (اضغط باستمرار على المفتاح CTRL) العمود أولاً نهاية، ومن ثم آبدأ واختيار فريق إضافة عمود - التاريخ - طرح الأيام (إضافة عمود - تاريخ - طرح أيام):
الأرقام الناتجة أقل من اللازم بمقدار 1 ، لأنه من المفترض أن نبدأ كل مشروع في اليوم الأول في الصباح وننتهي في اليوم الأخير في المساء. لذلك ، حدد العمود الناتج وأضف وحدة إليه باستخدام الأمر التحويل - قياسي - إضافة (تحويل - قياسي - إضافة):
لنقم الآن بإضافة عمود حيث نحسب الميزانية لكل يوم. للقيام بذلك ، في علامة التبويب إضافة عمود أنا لا ألعب عمود مخصص (عمود مخصص) وفي النافذة التي تظهر ، أدخل اسم الحقل الجديد وصيغة الحساب ، باستخدام أسماء الأعمدة من القائمة:
الآن أكثر اللحظات دقة - نقوم بإنشاء عمود محسوب آخر بقائمة التواريخ من البداية إلى النهاية بخطوة يوم واحد. للقيام بذلك ، اضغط على الزر مرة أخرى عمود مخصص (عمود مخصص) واستخدم لغة Power Query المضمنة M ، والتي تسمى القائمة. التواريخ:
هذه الوظيفة لها ثلاث حجج:
- تاريخ البدء - في حالتنا ، مأخوذ من العمود آبدأ
- عدد التواريخ التي سيتم إنشاؤها - في حالتنا ، هذا هو عدد الأيام لكل مشروع ، والذي قمنا بحسابه مسبقًا في العمود طرح
- خطوة زمنية - حسب التصميم # المدة (1,0,0,0،XNUMX،XNUMX،XNUMX)، وهذا يعني في لغة M - يوم واحد ، صفر ساعة ، صفر دقيقة ، صفر ثانية.
بعد النقر على OK نحصل على قائمة (قائمة) بالتواريخ ، والتي يمكن توسيعها إلى أسطر جديدة باستخدام الزر الموجود في رأس الجدول:
... ونحصل على:
كل ما تبقى الآن هو طي الجدول ، باستخدام التواريخ التي تم إنشاؤها كأسماء للأعمدة الجديدة. الفريق مسؤول عن هذا. عمود التفاصيل (عمود محوري) علامة التبويب تحول (تحول):
بعد النقر على OK نحصل على نتيجة قريبة جدًا من النتيجة المطلوبة:
Null ، في هذه الحالة ، تناظري لخلية فارغة في Excel.
يبقى إزالة الأعمدة غير الضرورية وإلغاء تحميل الجدول الناتج بجوار البيانات الأصلية باستخدام الأمر إغلاق وتحميل - إغلاق وتحميل ... (إغلاق وتحميل - إغلاق وتحميل إلى ...) علامة التبويب الصفحة الرئيسية (الرئيسية):
نحصل على نتيجة:
لمزيد من الجمال ، يمكنك تخصيص مظهر الجداول الذكية الناتجة في علامة التبويب منشئ (التصميم): تعيين نمط لون واحد ، وتعطيل أزرار التصفية ، وتمكين الإجماليات ، وما إلى ذلك. بالإضافة إلى ذلك ، يمكنك تحديد جدول بالتواريخ وتمكين تمييز الأرقام له باستخدام التنسيق الشرطي في علامة التبويب الصفحة الرئيسية - التنسيق الشرطي - مقاييس الألوان (الصفحة الرئيسية - التنسيق الشرطي - مقاييس الألوان):
وأفضل جزء هو أنه في المستقبل يمكنك تعديل المشروعات القديمة بأمان أو إضافة مشاريع جديدة إلى الجدول الأصلي ، ثم تحديث الجدول الصحيح بالتواريخ باستخدام زر الماوس الأيمن - وسيقوم Power Query بتكرار جميع الإجراءات التي قمنا بها تلقائيًا .
فويلا!
- مخطط جانت في Excel باستخدام التنسيق الشرطي
- تقويم معالم المشروع
- إنشاء صفوف مكررة باستخدام Power Query