صفحه خبير اكسيل

صفحة تهتم بتعليم الاكسيل المتقدم الذى يخدم المحاسبين.فى جميع مجالات اعمالهم

جروب خبير اكسيل

جروب خاص بمواضيع ومشاكل الاكسيل وبرمجة الاكسيل بصفة خاصة

الأحد، 31 ديسمبر 2017

unpivot 2



تحويل جدول متداخل الى تقرير pivot table

باستخدام  
Unpivot with Power Query

في هذا المثال :
 بافتراض ان لدينا جدول مبيعات حسب الفروع والمنتجات والسنوات وكل هذه البيانات في جدول واحد فقط . والمطلوب استخدام هذا الجدول لعمل تقارير pivot table .

وحتى نقوم بتحويل هذا الجدول الى pivot table

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

◘الخطوة الأولى :  
click From Table in the Get & Transform
 الخطوة الثانية : سيظهر لك امر انشاء الجداول قم بازالة علامة √ من My table has headers
 بعد ذلك سيظهر امامك Query Editor

 الخطوة الثالثة : والان فى العمود الاول المطلوب ملىء الفراغات الى اسم الفرع 
اضغط Right click على راس العمود Column1 واختار Fill ثم Down

 الخطوة الرابعة : المطلوب ملىء فراغات رقم العام وحتى يتم ذلك سنقول بتحويل الصفوف الى اعمدة والعكس
 حدد العمود الاول وانتقل الى علامة تبويب
 Transform tab, click Transpose.

الخطوة الخامسة سنقوم بملىء فراغات رقم السنة 
اختر العمود الاول المطلوب ملىء الفراغات الى اسم الفرع 
اضغط Right click على راس العمود Column1 واختار Fill ثم Down
الخطوة السادسة : 
الآن يجب علينا تحويل السنوات إلى نص، ودمجها مع العمود الشهر وإنشاء عمود التاريخ.
اختر العمود الاولColumn1 ثم انتقل الى علامة التبويبTransform واخترData Type: Any ثم  Text   
الخطوة السابعة : 
اختيار العمود الاول والثانى (→+shift)  اختيار راس العمود والضغط بزر الماوس الايمن واختيار Merge Columns









الخطوة الثامنة : والان سيتم عمل فراغ بين العام والشهر
الخطوة التاسعة : تحويل الشهر والعام الى الاعمده
 الخطوة العاشرة : 
حدد ايقونة الجدول واضغط بزر الماوس الايمن واختار
Use First Row As Headers
 الخطوة الحادى عشر : 
حدد العمود الاول والثانى واضغط بزر الماوس الايمن واختر
Unpivot Other Columns.

الخطوة الثانية عشر :  تقسيم عمود العام والتاريخ الى عمودين 

 يمكن تغيير اسماء روؤس الاعمدة بالضغط على راس العمود بضغطة مزدوجة واعادة تسمية العمود
الخطوة الاخيرة  Close & Load in Home
 ثم قم بانشاء التقارير باستخدام الجداول المحورية 
 وهذا تقرير مقترح





















المصدر العلمى مع بعض التعديلات 

نسال الله ان يتقبل منا ومنكم صالح الاعمال 
خبير اكسيل : محمد الريفى 
فى 1 يناير 2018 
تحميل الملف النهائى
http://www.mediafire.com/file/gx8065v4cnvn4g5/%u0627%u0644%u0645%u0644%u0641%20%u0627%u0644%u0646%u0647%u0627%u0626%u0649.xlsx














الأربعاء، 27 ديسمبر 2017

UNPIVOT EXCEL 2016

Unpivot in excel 2016

صحيح ان جداول pivot table لديها قدرة عالية فى تلخيص كم هائل من البيانات لكن فى احيان اخرى
قد تعجز فعلى سبيل المثال لو لدينا تقرير بمبيعات الشهور كما فى الشكل التالى.
وهذا الشكل يعتبر بالظبط تقرير  PIVOT TABLE
  

وترغب انت باعادة ترتيبة كما فى الشكل التالى . هنا يعجز PIVOT TABLE

واليك الخطوات التالية :
الخطوه الاولى.
قف فى اى خلية فى الجدول الخاص بك
انتقل الى علامة التبويب Data
فى جروب  Get & Transform section اختر منها From Table
  


الخطوه الثانية :
قف فى Query Editor وحدد الاعمدة من يناير وحتى ديسمبر باستخدام مفتاح SHIFT والسهم



الخطوة الثالثة :
قف على رؤس الاعمدة واضغط بزر الماوس الايمن على Unpivot Columns

 الخطوة الرابعة :
غير اسماء رؤوس الاعمدة الى الاسماء المطلوبه
فقط قف على راس العمود واضغط بالماوس ضغط مزدوج وقم بتغيير الاسم


الخطوه الخامسة :
اضغط على Close and Load button

 الخطوة السادسة

اعمل تقرير باستخدام pivot كما تريد ....


  
تحميل الملف
مرفق ملف pdf من 4 صفحات بالصور يشرح الخطوات +excel file
 
http://www.mediafire.com/file/3l6d88nvg91h681/unpivot-1.rar