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

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

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

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

الأحد، 14 يناير 2018

power Query

بسم الله الرحمن الرحيم 
السؤال
٠١٠٢٠٤٦٥٤٨٧٥- ٠٢٢٥٦٧٨٨٦٤- ٠١٢٧٥٤٣٨٩٥٤
بعد اذن الساده الافاضل .. لو عندي الارقام دي موبايل و ارضي في cell واحده كيف استخرج الموبايل بس و يكون كل موبايل في cell لوحده
 الاجابة 
اولا : حمل الملف الاصلى من هنا 
 يوجد طرق متعدده للاجابه على مثل هذا السؤال ولكن سنستخدم هنا power query 
الخطوه الاولى : 
حدد اى خلية فى البيانات التى لديك
انتقل على Data ثم form table
الخطوة الثانية : قم بازالة علامة الصح من my table has headers

 الخطوة الثالثة: سيتم فتح محرر الاستعلامات
حدد العمود بالكامل واضغط بالماوس بالزر الايمن
واختر split column واختر by delimiter
 الخطوة الرابعه: من custom  اختر علامة _ ثم موافق ok
 الخطوة الخامسة : قم بحفظ وتحميل الاستعلام فى جدول
 وهذه هى النتيجة النهائية ( وبذلك تستطيع معالجة وتهيئة حجم ضخم من البيانات وتحويلها الى شكل مناسب ومفيد ويمكنك بعدها اجراء التحليل كما تريد )
 تحميل الملف النهائى 
http://www.mediafire.com/file/k6b82c576m54ud4/%D8%A7%D9%84%D8%A7%D8%AC%D8%A7%D8%A8%D9%87.xlsx

الأربعاء، 3 يناير 2018

POWER PIVOT 1

طريقة اظهار POWER PIVOT  لاول مره
  •  File > Options > Add-Ins
  •  Manage box, click COM Add-ins> Go
  •  OK





















يمكن ان تجد POWER PIVOT بسهولة فى اوفيس 2013 او 2016
اذا كنت تستخدم اوفيس 2010 فلابد من تحميل هذه الاداه
Power-Pivot add-in download.


الاثنين، 1 يناير 2018

ماهو الفرق PowerPivot, PowerQuery , PowerBI




مقالة بعنوان الفرق بين 
Power Query
Power Pivot
Power View
Power BI

كل هذه الادوات  تظهر امكانيات وقدرات الاكسيل فى تحليل البيانات وفى هذه المقالة سنحاول توضيح كل اداه على حده .
 اولا:   (Power Query (Get & Transform
هى اداة استخراج وتحويل وتحميل البيانات من مصدر سواء كان قاعدة بيانات او موقع ويب او ملفات اكسيل ......) وتعديل هذه البيانات بالشكل المناسب للاحتياج ثم تحميله على ملف اكسيل لاستخراج اى تقارير .
ولها ثلاث وظائف منفصلة 
  (استخراج Extraction): حيث يمكن قراءة البيانات من مصدر محدد ثم استخراج هذه البيانات المطلوبة .
  (تحويل Transformation): حيث يتم تنظيف وتشكيل وتحويل البيانات وتجميع البيانات التى استخرجت الى الشكل المطلوب 
(تحميلLoad): تحميل الشكل النهائى من البيانات الى جدول او تقرير . 
قديما كان يتم تشكيل وتنظيف وتهيئة البيانات يدويا او بالطرق التقليدية ولكن مع ظهور Power Query  اصبح من السهل اسخراج وتحويل البيانات سواء كانت بسيطة او معقدة ثم تحميلها الى جدول او نموذج داخلى تمهيدا لعمل التقارير المطلوبة 
لمعرفة المزيد يمكن الاطلاع على رابط مايكروسوفت من هنا 
ثانيا : Power Pivot



هى تقنية يمكن من خلالها انشاء العديد من العلاقات بين الجداول ( او ربط الجداول) واجراء عمليات حسابية بسيطة او معقدة . وبدلا من البحث بين الجداول باستخدام vlookup  فيمكن ربط هذه الجداول ببعضها واستخراج المعلومات المفيده . 
كانت هناك مشكلة فى ملف الاكسيل انه كان لايستطيع ان يستوعب اكثر من مليون صف وحوالى 16 الف عمود من البيانات فى حدود 2G  وهنا استطاعت اداة Power Pivot  الى ضغط هذه البيانات الى واحد من عشره من الحجم الاصلى بمعنى ان حجم البيانات 100 ميجا فانه يتحول الى 10 ميجا تقريبا 
وحتى نفهم هذه الاداه جيدا 
افترض ان لديك ثلاث جداول ( جدول العملاء وجدول الفواتير وجدول تفصيلى بالمبيعات ) 
ومن خلال هذه الجداول يمكن انشاء ربط بين هذه الجداول واعداد نموذج بيانات ويمكن اعداد تقارير مبيعات العملاء او الاصناف ....) 
لمعرفة المزيد يمكن الاطلاع على رابط مايكروسوفت من هنا

ثالثا : Power View




هو اداة عرض تفاعلية للبيانات باستخدام الرسوم والمخططات البيانية بشكل جميل وسريع تكون هذه البيانات موجوده فى نموذج بيانات داخلى او خارجى Data Model
 لمعرفة المزيد يمكن الاطلاع على رابط مايكروسوفت من هنا
 رابعا:  Power BI
يعرف بانه (business intelligence) ذكاء الاعمال وهو باختصار عملية تحويل كم ضخم من البيانات الى معلومات مفيدة لمتخذى القرار او لاغراض الاعمال حيث يتم تخزين البيانات وربطها من مصادر متعددة ثم استعراضها فى لوحة رسومات بيانية مفيدة ويمكن تحميل برنامج سطح المكتب 
  لمعرفة المزيد يمكن الاطلاع على رابط مايكروسوفت من هنا

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







الأحد، 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