عمليات المقارنة

بسم الله الرحمن الرحيم

يتيح لنا الإكسل إستخدام عمليات المقارنة فى المعادلات وتكون نتيجة العملية القيم True أو False وهو ما يمكننا من إستخدام عمليات المقارنة فى العديد من المواضع المختلفة مثل

• التنسيق الشرطى Conditiond Formatting
• التأكد من صحة البيانات Data Validation
• الدوال المختلفة التى تعالج القييم المنطقة مثل الدالة IF

بعد أن ذكرنا نبذة عن فائدة إستخدام عمليات المقارنة فلابد لنا أن نتعرف على علامات المقارنة ومعنى كل علامة

عمليات المقارنة ومعانيها

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

ترتيب معالجة عمليات المقارنة فى المعادلات

إذا إشتملت المعادلة على عمليات مقارنة فإنه يتم معالجتها فى نهاية العمليات وذلك وفقاً لجدول ترتيب معالجة البيانات كالتالى

أولاً : إستخدام عمليات المقارنة مع القيم الرقمية.

تصحيح :
المعادلة بالخلية E5 أخطأت بكتابتها والمعادلة الصحيحة هى

=C5=D5

ثانياً : إستخدام عمليات المقارنة مع الوقت والتاريخ.

سبق وأن أشرنا إلى أن الإكسل يعالج الوقت والتاريخ على أنها أرقام مسلسلة[كيف تتم معالجة الوقت والتاريخ فى الإكسل?] لذلك يمكننا إستخدام عمليات المقارنة مع الوقت والتاريخ

ثالثاً : إستخدام عمليات المقارنة مع القيم النصية.

حين يتم إستخدام عمليات المقارنة مع القيم النصية فإن النتيجة تعتمد على الترتيب الأبجدى للحروف فـ B أكبر من A وهكذا

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

تحويل نتائج المعادلات لقيم ثابتة

بسم الله الرحمن الرحيم

حين نستخدم المعادلات فإن النتيجة النهائية للمعادلة والتى تظهر بالخلية ليست المحتوى الأصلى للخلية وإنما هى نتيجة للعمليات الى تقوم بها المعادلة المستخدمة، وعليه حين ترغب أن تصبح محتويات الخلية هى النتيجة النهائية وليست المعادلة فإننا نستخدم الأداة Paste Values وذلك بواسطة إحدى الطرق التالية:

1. إستخدام الآداة Paste Values من خلال القوائم Ribbons.
2. إستخدام الآداة Paste Values من خلال الماوس Mouse.
3. إستخدام الآداة Paste Values من خلال الإختصارات Shortcuts.

أولاً : إستخدام الآداة Paste Values من خلال القوائم Ribbons.

• نحدد النطاق الذى يحتوى على البيانات
• نقوم بعمل Copy للبيانات من خلال Ctrl + C
• نذهب إلى المربع السفلى للأمر Past ثم نختار Paste Values اى سيكون كالتالى Home » Clipboard » Paste » Paste Values


لاحظ محتويات الخلية فى ال Formula Bar ستجد أن محتويات الخلية هى القيمة الظاهرة به وليست معادلة.

بالنسبة للإكسل 2003 من شريط الأدوات Toolbar نقوم بالأتى:

ثانياً : إستخدام الآداة Paste Values من خلال الماوس Mouse.

• نحدد النطاق الذى يحتوى على البيانات
• نضع مؤشر الماوس على أحد أطراف النطاق حتى يكون شكل المؤشر سهم رباعى أسود
• بإستخدام الزر الأيمن Right Click للماوس نقوم بسحب النطاق من أحد أطرافه/حدوده/جوانبه ثم فى الموضع الذى تريد وضع البيانات نقوم بالإفلات
• تظهر لنا القائمة التالية فنختار منها Copy Here as Values Only
  

ثالثاً : إستخدام الآداة Paste Values من خلال الإختصارات Shortcurs.

• نحدد النطاق الذى يحتوى على البيانات
• نقوم بنسخ البيانات من خلال Ctrl + C
• نقوم بنسخ البيانات من خلال Alt + H + V + V

تعمل فى نسخة 2007 و ما يليها.

لاحظ ان الآداة Paste Values هى إحدى الادوات المتاحة ضمن مربع الحوار Paste Special

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

كيفية حساب مجموع أكبر 4 قيم رقمية

بسم الله الرحمن الرحيم

إذا كنت تتعامل بكثرة من القيم الرقمية وتقوم بالعديد من العمليات الحسابية عليها فربما ترغب فى إيجاد مجموع أكبر 3 قيم أو حتى مجموع أصغر 10 قيم، لايهم عدد القيم الرقمية التى تريد التعامل معها فهذه الطريقة تمكنك من تحديد العدد الذى تريده

الدوال المستخدمة هى :

• SUM
• LARGE
• SMALL

مثال: فيما يلى مجموعة من القيم الرقمية فى النطاق B2:B11 ونريد حساب مجموع اكبر 4 قيم رقمية

المعادلة هى :

=SUM(LARGE(B2:B11;{1;2;3;4}))

فكرة عمل المعادلة:

1. تعتمد هذه الدالة على قدرة الدالة SUM على معالجة المصفوفات Arrays.
2. تبدأ المعادلة بإيجاد أكبر 4 قيم فى النطاق المستخدم اى B2:B11 ومن ثم وضعها فى شكل مصفوفة كالتالى



=SUM({15;13;12;9})

3. تقوم الدالة SUM بإيجاد مجموع القيم فى المصفوفة التى تستخرجها الدالة LARGE

ملاحظات على المعادلة السابقة

• المعادلة المستخدمة هى معادلة عادية فهى ليست معادلة مصفوفية Array Formula وبالتالى لا تستلزم الضغط على Ctrl+Shift+Enter بل يكفى التفعيل بواسطة الضغط على Enter فقط
• يمكنك إستبدال الدالة LARGE بالدالة SMALL لحساب مجموع أقل قيم
• يمكنك التحكم فى عدد القيم التى تريد إيجاد مجموعها من خلال القيم التى تدخلها للدالة LARGE
  أمثلة :
  • مجموع أكبر 3 قيم

  =SUM(LARGE(B2:B11;{1;2;3}))
  

  • مجموع أكبر 5 قيم

  =SUM(LARGE(B2:B11;{1;2;3;4;5}))
  

  • مجموع أقل قيمتان

  =SUM(SMALL(B2:B11;{1;2}))
  

  • مجموع أقل 7 قيم

  =SUM(SMALL(B2:B11;{1;2;3;4;5;6;7}))
  

• فى حالة تكرار بعض البيانات كالمثال التالى فإن الدالة تحسب المجموع بغض النظر عن تكرار أحد القيم
  

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

تحديد البيانات المكررة بواسطة التنسيق الشرطى

بسم الله الرحمن الرحيم

حين تتعامل مع جداول البيانات فى الإكسل قد ترغب فى تحديد البيانات المكررة من أجل تحديد كيف ستعالج هذا التكرار (حذف/تعديل/…)، اليوم سنتعرف على كيفية تحديد البيانات المكررة بواسطة التنسيق الشرطى

مثال :
فيما يلى مجموعة من البيانات فى النطاق B3:B17 والمطلوب تحديد البيانات المكررة

لتحديد البيانات المكررة نتبع الخطوات التالية

1. حدد النطاق الذى يشتمل على اليانات والذى فى حالتنا هو B3:B17
2. نذهب لمربع الحوار Conditonal Formatting من خلال الضغط على Alt+O+D
   • فى حالة الإكسل 2003 إختر Formula Is من القائمة المنسدلة فى اقصى اليسار
   • فى حالة الإكسل 2007 إضغط على زر New Rule ثم إختر الإختيار الأخير كما فى الصورة التالية
3. نكتب المعادلة التى بواسطتها سنحدد البيانات المكررة

   =COUNTIF($D$3:$D$17;D3)>1

4. إضغط على زر Format لتحديد التنسيق الذى تريده
   
5. إضغط على Ok

كما ترى فى الصورة التالية البيانات الأصلية جهة اليمين والبيانات بعد تطبيق التنسيق الشرطى عليها جهة اليسار

فكرة المعادلة المستخدمة:

=COUNTIF($D$3:$D$17;D3)>1

• تم إستخدام الدالة Countif والتى بواسطتها نستطيع حساب عدد تكرار أحد البيانات
• تم أدخال النطاق B3:B17 الذى يشتمل على البيانات وقمنا بعمل Lock اى جعلناه Absolute Reference لكى يظل النطاق ثابت ولا يتغير فى الخلايا التالية
• تركنا المعيار Critria  على الوضع Relative Reference حتى يكون متغير فى الخلايا التالية
• إستخدمنا علامة المقارنة أكبر من والقيمة 1 لكى نحصل على True مع البيانات التى تتكرر أكثر من مرة ونحصل على القيمة False مع البيانات الغير مكررة
• لا تنس أن التنسيق الشرطى يطبق حين تكون نتيجة الشرط المستخدم True

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

معالجة إجمالي الوقت الذى يتعدى 24 ساعة

بسم الله الرحمن الرحيم

سؤال من الأخ / أبوبكر صلاح
تقابلني مشكلة أن الساعات الاضافيه اذا زادت عن 24 ساعة يبدأ إكسيل العد من أول وجديد ، بمعني ان الساعات الاضافيه للموظف اذا كانت 25:30 ساعة ظهرت على أنها 01:30 ساعة ، مع العلم أي معرف جميع الخانات بالتنسيق HH:MM .

سبق وأن أشرنا إلى أن الإكسل يقوم بتخزين الوقت والتاريخ على أنها أرقام تسلسلية [كيف تتم معالجة الوقت والتاريخ فى الإكسل?] ووضحنا أن الوقت يُخزن على أنه قيمة عشرية نتيجة قسمة الوقت فى لحظة معينة على 24 ساعة.

إذن حين يتم تنسيق خلية بالتنسيق HH:MM فإننا نخبر الإكسل أننا نريد إظهار الوقت فقط أى الجزء العشرى فقط وعليه حين يتم إيجاد مجموع الساعات وتصادف أنها تجاوزت 24 ساعة أى أصبحت رقم صحيح وكسر فإن التنسيق يأخذ الجزء العشرى فقط ويقوم بإظهاره فى صورة الوقت.

فى الصورة السابقة نجد أن القيمة العشرية 0.145914 قد تم تمثيلها فى الإكسل بـ 3:30 وتم إهمال الرقم الصحيح 2 والذى يمثل يومان اى 48 ساعة وبالتالى فإن الإجمالي يجب أن يكون 3:30 + 48:00 = 51:30 ساعة.

حل هذه المشكلة يعتمد على تغيير تنسيق الخلية بحيث تقوم بإظهار بمعالجة الوقت إذا زاد عن 24 ساعة

خطوات الحل كالتالى:

1. حدد الخلية التى تشتمل على الوقت
2. نذهب إلى Format Cells من خلال عمل Right click وإختيار Format Cells أو بالضغط على Ctrl + 1
3. نحدد Number Tab ثم نختار Custom من القائمة اليسرى
4. فى الخانة Type نكتب التنسيق التالى

   [h]:mm

5. نضغط على Ok

النتيجة النهائية:

المصدر :

• Working with time periods that exceed 24 hours in Excel for Mac

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

إدراج إسم ال Worksheet فى الخلية بواسطة المعادلات

سؤال :

اذا رغبت باستخدام اسم الورقه (sheet) في احدى الخلايا. فمثلا لدي ورقه باسم عبد اللطيف، وأردت استخدام هذا الاسم كعنوان رئيسي للجدول المقام في نفس الورقه، ولكن حين يتم تغيير اسم الورقه الى عبد الله يؤدي ذلك الى تغير العنوان فورا دون التدخل من المستخدم.

النتيجة النهائية :

للقيام بذلك بواسطة المعادلات نستخدم الدوال التالية:

• MID
• CELL
• FIND

ملحوظة : هناك العديد من الطرق للقيام بهذه الوظيفة وهذه أحدى الطرق.

فكرة المعادلة :

1. يتم إدراج مسار الملف File Path والذى ينتهى بإسم الملف Workbook يليه إسم ورقة العمل Worksheet
2. تحديد موضع نهاية إسم الملف
3. إستخلاص ما يلى إسم الملف

خطوات الحل :

1. إدراج مسار الملف بواسطة الدالة Cell والمتغير FileName
   • اللون الأحمر يمثل إسم ال Workbook
   • اللون الأخضر يمثل إسم ال Worksheet

=CELL("filename";A1)



2. تحديد موضع نهاية ال Workbook من خلال تحديد موضع “["

=FIND("]“;CELL(“filename”;A1))

3. تحديد بداية موضع إسم ورقة العمل من خلال إضافة 1 فى نهاية المعادلة السابقة

=FIND("]";CELL("filename";A1))+1

4. إستخلاص إسم ورقة العمل

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;31)

لتصبح النتيجة النهائية كما يلى

ملاحظات :

• إن عدم إدراج A1 فى المعادلة التالية سيؤدى إلى تغير مسار الملف فى كل مرة يتم إستخدام المعادلة فى ورقة عمل أخرى

=CELL("filename";A1)

• لقد إستخدمنا القيمة 31 فى الدالة MID لأن أقصى عدد من الأحرف لإسم ورقة العمل هو 31 حرف
• لابد من التطبيق على ملف تم عمل Save له وإلا نحصل على الخطأ Value

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

إستخلاص الإسم الأول من الإسم بالكامل

حين يتم التعامل مع جداول البيانات وتكون تشتمل على خانة خاصة بالإسم ثلاثى كـالإسم (محمد على رمضان) ونريد فصل الإسم الأول عن الثانى عن الإسم الأخير فى هذه الحالة يكون لدينا عدة أساليب لإتمام عملية الفصل منها الاداة Text to Columns أو من خلال إستخدام المعادلات.

اليوم سنستعرض كيفية إستخدام المعادلات للحصول على الإسم الأول وسيتم تخصيص موضوعان مستقلان لفصل الإسم الثانى والأخير من الإسم كاملاً.

فصل الإسم الأول من الإسم كاملاً.

فيما يلى مجموعة من الأسماء التى سيتم التطبيق عليها

فكرة المعادلة :

تحديد موضع أول مسافة فى الخلية ثم إستخلاص النص الذى يسبقها وذلك من خلال الدوال التالية :

• LEFT
• FIND
• TRIM

خطوات الحل :

1. تحديد موضع أول مسافة فى الخلية

=FIND(" ",B4,1)

2. حذف المسافات الإضافية فى الخلية

=FIND(" ",TRIM(B4),1)

3. إستخلاص الإسم الأول

=LEFT(TRIM(B4),FIND(" ",TRIM(B4),1))

4. لحذف المسافة التى تلى الإسم الأول نقوم بإضافة -1 فى نهاية المعادلة لتصبح فى صورتها النهائبة كالتالى

=LEFT(TRIM(B4),FIND(" ",TRIM(B4),1)-1)

النتيجة النهائية كالتالى :

لاحظ أن الخلية B9 تشتمل على مسافات إضافية فى بداية ومنتصف الخلية إلا أن الدالة Trim أدت إلى الحصول على النتيجة الصحيحة.

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.

حساب عدد الكلمات فى الخلية

فى بعض الأحيان نرغب فى معرفة عدد الكلمات فى خلية معينة مما يدفعنا إلى القيام بذلك يدوياً بمعنى أن تحسب عدد الكلمات فى الخلية ثم تقوم بتسجيلها فى الموضع المخصص لذلك، لكن هل حاولت القيام بذلك من خلال إستخدام المعادلات؟

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

• LEN
• SUBSTIUTE
• TRIM
• IF

مثال :

فى المثال السابق نرغب فى حساب عدد الكلمات فى كل خلية بالنطاق B2:B7 وتسجيل العدد فى الخلية المقابلة بالنطاق C2:C7.

لكى نقوم بذلك سنستخدم معادلة تعمل على حساب عدد المسافات فى الخلية ثم يتم إضافة 1 إلى عدد المسافات وبذلك نحصل على عدد الكلمات فى الخلية.

نستند فى ذلك إلى أننا نفصل بين كل كلمة والأخرى بمسافة فبالتالى إذا نظرنا إلى عبارة مثل (نسبة النجاح بلغت 90%) سنجد أنها تشتمل على 4 كلمات وعدد 3 مسافات.

خطوات الحل :

1. حساب عدد الأحرف فى الخلية

=len(trim(b2))

2. حساب عدد الأحرف بعد حذف المسافات فى الخلية

=len(substitute(b2," ",""))

3. إيجاد الفرق بين القيمتين

=len(trim(b2))-len(substitute(b2," ",""))

4. إضافة 1 إلى الفرق بين القيميتين

=len(trim(b2))-len(substitute(b2," ",""))+1



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

لحل هذا الخطأ نستخدم الدالة IF لكى تقوم بإظهار القيمة 0 أو كلمة Empty فى حالة أن عدد الأحرف فى الخلية =0 لتصبح المعادلة بعد ذلك كالتالى

=if(len(trim(b2))=0,"Empty",len(trim(b2))-len(substitute(b2," ",""))+1)



وبذلك تصبح النتيجة كالتالى

فى رعاية الله

»يمكنك الإطلاع على جميع موضوعات المدونة من خلال الأرشيف.
»إذا كانت هذه هى زيارتك الأولى للموقع، فلا تنس أن تشترك فى خلاصات الموقع عبر الـ RSS أو عبر الـ e-mailللحصول على جديد الموقع مباشرة.