Image Workbook Statistics in Excel 365

Workbook Statistics in Excel 365

اضافة جميلة و جديدة من مايكروسوفت اكسيل على نسخة الاكسيل التي تعمل على الاوفيس 365 لمشتركي الـ

Insider

و هي خاصية إحصائية للملف الذي نعمل عليه و الذي تعطينا معلومات عن الملف الذي نعمل عليه من ناحيتين :

1.        الصفحة التي نعمل عليها Current Sheet .

2.        الملف كامل Workbook

حيث ستحتوي على المعلومات التالية إن وجدت كلها:

1.        أخر خلية تم العمل عليها End of sheet.

2.        الخلايا التي تحتوي بيانات Cells with data.

3.        الجداول Tables ( الفرق بين الجداول و النطاقات, الجداول هي التي تم تحويلها من خلال خاصية الجداول Table  او Ctrl T فعند النظر اليها بالشكل متساويين ).

4.        الجداول المحورية PivotTable .

5.        المعادلات Formulas

6.        الرسومات البيانية Charts .

7.        العناصر Objects.

8.        الصور Pictures.

و هذه الخيارات ستظهر عند توفرها في الملف.

و للعمل مع هذا الخيار موجود في تاب

Review

Workbook Statistics

اذا كان الملف الذي نعمل عليه فارغ سنحصل على الشاشه التالية :

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

أما إذا تعاملنا مع ملف يحتوي :

1.        خلايا بها بيانات .

2.        جداول .

3.        صور.

4.        إلخ ....  

فإن النتيجة تظهر بالشكل التالي :

دمتم في حفظ الله

يحيى حسين

Image المعادلة SORT

السلام عليكم و رحمة الله و بركاته 

المعادلة SORT

وظيفتها عمل إعادة تترتيب للبيانات, حيث تعمل على ترتيب النتائج بالشكل الذي نختاره تنازليا أو تصاعدياً.

و لها أربع متغيرات :

1.      Array : متغير إجباري و هو النطاق الذي يحتوي القيمة التي نريد ترتيبها و قد تكون عامود واحد أو عدة أعمدة.

2.      Sort_index  : متغير إختياري, و يأخذ قيمته حسب عدد الأعمدة في النطاق المحدد في المتغير الأول,  فإذا كانت النطاق يحتوي عامود واحد فهنا القيمة تلقائياً هي 1 , أما إذا كان النطاق المحدد أكثر من عامود فإن عملية الترتيب ستكون على العامود الأول في حالة عدم تحديد أي عامود آخر, فإذا تم تحديد رقم عامود آخر في النطاق فسيكون إعادة الترتيب بناء عليه,  وإذا إخترت رقم عامود أكثر من عدد الأعمدة الموجودة في النطاق ستكون نتيجة المعادلة في هذه الحالة خطأ #VALUE!.

3.      Sort_order : المقصود به خيارات الترتيب هل هي تصاعدية أم تنازلية:

a.      القيمة 1 : Ascending إعادة الترتيب تصاعدي .

b.      القيمة -1 :  Descending إعادة الترتيب تنازلي.

4.      By_col : المقصود بها هل الترتيب سيكون بإتجاه الأعمدة أو الأسطر, فكما نعلم أنه عملية الترتيب تطون دائماً من الأعلى للأسفل و يمكن أن نغير التريب من اليسار إلى اليمين , وهذا هو المقصود بهذا المتغير.

 لتوضيح آلية عملها كما في الصورة التالية لدينا أسماء الدول و مبيعاتها و عند تطبيق المعادلة  حددنا عامود واحد فقط, لذلك سنحصل على إعادة ترتيب القيم  و هو أسماء الدول بالترتيب الأبجدي :

و هذه هي النتيجة أسماء الدول بترتيب أبجدي من A to Z :

و لو إخترنا النطاق الخاص بالأرقام فقط سنحصل على الترتيب الأرقام من الأصغر إلى الأكبر

و لكن إذا إخترنا الجدول كامل في المعادلة, فكما ذكرنا سابقاُ سيكون الترتيب على العامود الأول و لكن سنطبق المتغيرات الأخرى في المعادلة, حيث سنختار الرقم 2 و التي تشير إلى العامود رقم 2 بالنطاق المحدد, و ثم سنختار الرقم -1 حتى يكون الترتيب من الأكبر إلى الأصغر.

و كما ذكرنا سابقاً في حال وجود أي قيمة في الوسط سنحصل على خطأ إسمه #SPILL .

في الفيديو المرفق شرح تفصيلي : 

رابط الفيديو

دمتم في حفظ الله 

يحيى حسين 

Excel MVP

Image المعادلة UNIQUE

السلام عليكم و رحمة الله 

المعادلة UNIQUE

من إسمها هي معادلة تستخرج قائمة بالقيم الفريده من النطاق الذي نحدده.

و لها ثلاث متغيرات هم :

1.      Array : متغير إجباري و هو النطاق الذي يحتوي القيمة المكررة و قد يكون عامود واحد أو عدة أعمدة, فإذا إخترت عامود واحد ستكون النتيجة القيمة الفريدة فقط داخل العامود المحدد, و إذا إخترت الأعمدة كلها فسيحسب التكرار على كل الأعمدة.

2.      By_col : متغير إختياري لإختيار القيم الفريدة في سطر أو عامود.

3.      Occurs_once  : متغير إختياري لإختبار القيم الفريدة التي تكررت (مرة واحدة )أو (مرة واحدة أو أكثر).

لتوضيج آلية عملها كما في الصورة التالية لدينا أسماء الدول و المدن و عند تطبيق المعادلة حددنا عامود واحد فقط, لذلك سنحصل على قيمة فريدة فقط من العامود الذي إخترناه .

و عند الضغط على انتر سنحصل على النتيجة التالية :

و هي أسماء الدول في العامود المحدد, هنا يجب أن ننتبه لأمرين :

1.      أن المعادلة بعد الضغط على انتر قامت تلقائياً بتحديد كل القيم الفريدة و إستخدمت المساحة التي احتاجتها و السبب في ذلك إستخدام نظام الصفيف الديناميكي Dynamic Array.

2.      الأمر الثاني أن المعادلة كتبت مرة واحد في أول خلية و نسخت تلقائياً في كل الخلايا و لكن اذا وضع مؤشر الماوس على أي خلية منهم لن تجد بداخلها معادلة, و إذا نظرت إلى الـ Formula Bar ستجد أن المعادلة في الخلايا التي تأتي بعد ثاني خلية قد أخذت لون باهتاً كما في الصورة التالية :

و هنا يتبادر إلى الأذهان سؤال, ماذا لو كتبت أي قيمة داخل أي خلية تحت المعادلة الأصلية

سنجرب ذلك و نرى ما هي النتيجة :

سنحصل على خطأ إسمه #SPILL,  و سببه عدم قدرة الإكسيل على تجاوز الخلية التي وجدت في إتجاه سحب المعادلة و نلاحظ وجود خط أزرق متقطع.

و اذا حذفت القيمة او الرقم الذي تم كتابته فستعود المعادلة لعملها بشكل طبيعي .

و في الفيديو المرفق شرح تفصيلي 

على هذا الرابط  

دمتم في حفظ الله 

يحيى حسين 

Excel MVP 

Image جمع المبلغ من عامودين في الاكسيل في حال إختلفت وحدة القياس

السلام عليكم و رحمة الله و بركاته 

عملية الجمع في الاكسيل فيما اذا كانت القيمة من عامودين و كل عامود له وحدة قياس مختلفة عن العامود الثاني, فعلى فرض لو أن العامود الأول به المبلغ بالدينار و العامود الثاني به المبلغ بالفلس, حتى نحصل على قيمة صحيحة و رقم صحيح يجب أن يتم توحيد العامودين في عامود واحد و ان يكون الفاصل بينهم علامة النقطة كما هو متعارف عليه في عالم الأرقام .

و لكن بعض الحالات تتطلب أن يبقى عامود الدنانير منفصل عن عامود الفلسات بمعنى أن العامود الاول الذي يحتوي القيمة بالعملة الرئيسية منفصل عن العامود بالعملة الفرعية و بنفس الوقت نريد الحصول على الرقم الصحيح من جمع الدنانير و الفلسات.

فكما نرى في الصورة, عامود الدنانير منفصل عن عامود الفلسات :

و الحل الاول كما ذكرنا في اضافة عامود ليجمع البيانات حركة حركة في الجدول 

ثم الحصول على المجموع النهائي :

حيث أخذنا مجموع المبلغ بالدينار و أضفنا اليه مجمود المبلغ بالفلس مقسوما على ألف لان كل دينار يحتوي 1000 فلس ( فلو كانت العملة التي تعمل عليها تقاس بالمائة كالريال السعودي مائة هللة يكون القسمة على مائة ), و من ثم سحب المعادلة أعلاه و الخطوة الأخيرة جمع البيانات كما في الشكل التالي بحيث يكون المجموع الكلي 344.492 دينار.

و لكن هذه الطريقة لن تفيدنا في حالة رغبتنا في إبقاء الاعمدة منفصلة,  و لذلك سنقوم بجمع عامود الفلسات على حده و عامود الدنانير على حده, و الفرق بينهما سيضاف الى عامود الدنانير, و لعمل لو أخذنا مجموع الفلسات لوحده بعملية الجمع العادية لحصلنا على النتيجة التالية 2492 لأن عملية الجمع ستقوم بجمع كامل القيم في عامود الفلسات, و لكن يتوجب علينا الحصوص على مجموع الفلسات ما قل عن الف و هو 492 و أخذ ما زاد عن الالف للعامود الثاني و هو في هذه الحالة رقم 2 الى مجموع عامود الدنانير : 

و لعمل ذلك سنتخدم معادلة 

MOD 

مع معادلة 

SUM

 حيث ستقوم هذه المعادلة بعد جمع العامود بحساب باقي القسمى على الف و التي ستحقق المطلوب 492 دينار كما في الشكل التالي : 

و بذلك نكون قدد حصلنا على مجموع الفلسات 

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

1. الحصول على مجموع عامود الدناينر.
2. اضافة مجموع عامود الفلسات اليه و لكن بعد طرح فرق الفلسات منه.
3. قسمة فرق الفلسات على الف لتحويل 2000 الى رقم 2 .

و المعادلة التالية تفي بالغرض ان شاء الله.

و بذلك نحصل على المجموع مطابق في العامودين.

و القيمة مطابقة ما بين عامود E  و عامود C,D

و لمشاهدة الفيديو على اليوتيوب

على هذا الرابط

دمتم في حفظ الله 

يحيى حسين 

Excel MVP