معادلات الإكسل الأساسية والمتقدمة: شرح بالأمثلة من بيئة العمل

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

قبل المعادلات: أنواع المراجع

أغلب أخطاء المبتدئين في المعادلات سببها المراجع لا الدوال:

• المرجع النسبي A1: يتغير عند سحب المعادلة، وهو الافتراضي.
• المرجع المطلق $A$1: لا يتغير عند السحب، ويستخدم للخلايا الثابتة مثل نسبة أو إجمالي.
• المرجع المختلط A$1 أو $A1: يثبّت الصف فقط أو العمود فقط.

أثناء كتابة المعادلة اضغط F4 على المرجع للتبديل بين الأنواع.

المعادلات الأساسية التي يحتاجها كل موظف

دوال الحساب والإحصاء البسيطة

• =SUM(D2:D100): مجموع النطاق.
• =AVERAGE(D2:D100): المتوسط الحسابي.
• =MIN(D2:D100) و=MAX(D2:D100): أصغر قيمة وأكبرها.
• =COUNT(D2:D100): عدد الخلايا الرقمية، و=COUNTA(B2:B100): عدد الخلايا غير الفارغة، مثل عدد الموظفين المسجلين.
• =ROUND(D2,2): تقريب الرقم إلى خانتين عشريتين.

الدوال الشرطية والمنطقية

• =IF(E2>=90,"ممتاز","يحتاج تطوير"): تعيد قيمة إذا تحقق الشرط وأخرى إذا لم يتحقق.
• =IFS(E2>=90,"ممتاز",E2>=75,"جيد جدًا",E2>=60,"مقبول",TRUE,"ضعيف"): شروط متعددة بلا تداخل، ومتاحة في Excel 2019 وMicrosoft 365.
• =AND(E2>=75,F2="مكتمل"): تعيد TRUE إذا تحققت كل الشروط، وOR إذا تحقق أحدها.
• =IFERROR(B2/C2,0): تعرض صفرًا أو نصًا بديلًا بدل رسالة الخطأ.

دوال النصوص

• =TRIM(B2): تحذف المسافات الزائدة، وهي أول دالة تستخدمها عند تنظيف بيانات منسوخة من نظام آخر.
• =LEFT(A2,3) و=RIGHT(A2,4) و=MID(A2,4,2): استخراج جزء من النص من اليسار أو اليمين أو المنتصف.
• =LEN(A2): عدد الأحرف، ومفيدة للتحقق من أن رقم الجوال أو الهوية مكتمل الخانات.
• =B2&" "&C2 أو =TEXTJOIN(" ",TRUE,B2:D2): دمج الاسم الأول واسم الأب واسم العائلة في خلية واحدة.
• =TEXT(A2,"dd/mm/yyyy"): عرض التاريخ أو الرقم بالتنسيق الذي تريده داخل نص.

دوال التاريخ

• =TODAY(): تاريخ اليوم، ويتحدّث تلقائيًا كل يوم.
• =DATEDIF(C2,TODAY(),"y"): عدد سنوات الخدمة من تاريخ التعيين حتى اليوم، واستبدل "y" بـ "m" لعدد الأشهر.
• =EDATE(C2,12): التاريخ بعد 12 شهرًا، مثل موعد تجديد عقد سنوي.
• =EOMONTH(C2,0): آخر يوم في شهر التاريخ المحدد.
• =NETWORKDAYS.INTL(C2,D2,7,H2:H10): عدد أيام العمل بين تاريخين مع استبعاد العطل في النطاق H2:H10. والرقم 7 يعني أن العطلة الأسبوعية الجمعة والسبت، وهو المناسب لبيئة العمل في المملكة، بخلاف NETWORKDAYS العادية التي تفترض السبت والأحد.

المعادلات المتقدمة: الجمع والعد بشروط

• =SUMIF(B:B,"الرياض",D:D): مجموع العمود D للصفوف التي فرعها الرياض. وترتيب معاملاتها: نطاق الشرط، ثم الشرط، ثم نطاق الجمع.
• =SUMIFS(D:D,B:B,"الرياض",C:C,"أكتوبر"): مجموع بأكثر من شرط. انتبه: في SUMIFS يأتي نطاق الجمع أولًا، ثم أزواج النطاق والشرط.
• =COUNTIFS(C:C,"المبيعات",F:F,">=90"): عدد موظفي قسم المبيعات الذين تقييمهم 90 فأكثر.
• =AVERAGEIFS(F:F,C:C,"المبيعات"): متوسط تقييم قسم واحد.

دوال البحث: VLOOKUP وXLOOKUP وINDEX/MATCH

دوال البحث تجلب معلومة من جدول آخر اعتمادًا على قيمة مشتركة، مثل الرقم الوظيفي.

• =VLOOKUP(A2,Employees!A:E,3,FALSE): تبحث عن قيمة A2 في العمود الأول من النطاق، وتعيد القيمة من العمود الثالث. وFALSE تعني تطابقًا تامًا، ونسيانها سبب شائع لنتائج خاطئة. وحدّها أنها تبحث في العمود الأول فقط وتعيد ما على يمينه في ترتيب الأعمدة.
• =XLOOKUP(A2,Employees!A:A,Employees!C:C,"غير موجود"): تحدد عمود البحث وعمود النتيجة كلًا على حدة، فتبحث في أي اتجاه، وتطابق تطابقًا تامًا افتراضيًا، وتقبل نصًا بديلًا إذا لم تجد القيمة. وهي متاحة في Microsoft 365 وExcel 2021 وما بعده.
• =INDEX(Employees!C:C,MATCH(A2,Employees!A:A,0)): البديل الكلاسيكي لـ XLOOKUP في النسخ القديمة، ويعمل في أي اتجاه. والصفر في MATCH يعني تطابقًا تامًا.

أيها تختار؟ إذا كانت نسختك تدعم XLOOKUP فهي الأوضح والأقل أخطاء. وإذا كنت تشارك الملف مع زملاء على نسخ قديمة، فاستخدم INDEX/MATCH.

المصفوفات الديناميكية في Microsoft 365

• =UNIQUE(C2:C500): قائمة بالقيم دون تكرار، مثل أسماء الأقسام.
• =FILTER(A2:F500,C2:C500="المالية","لا يوجد"): يعرض كل صفوف قسم المالية في مكان آخر ويتحدّث تلقائيًا.
• =SORT(A2:F500,6,-1): يرتّب البيانات حسب العمود السادس تنازليًا.

نتائج هذه الدوال «تنسكب» في الخلايا المجاورة، فاترك لها مساحة فارغة وإلا ظهر الخطأ #SPILL!.

أشهر رسائل الخطأ وحلولها

• #DIV/0!: قسمة على صفر أو خلية فارغة. الحل: IFERROR أو التحقق من المقام.
• #N/A: دالة البحث لم تجد القيمة. تحقق من المسافات الزائدة بـ TRIM، ومن أن الرقم ليس مخزنًا كنص.
• #VALUE!: نوع بيانات خاطئ، مثل جمع نص مع رقم.
• #REF!: المعادلة تشير إلى خلية حُذفت.
• #NAME?: اسم دالة مكتوب خطأ، أو دالة غير مدعومة في نسختك، أو نص دون علامات تنصيص.
• #####: ليس خطأ في المعادلة، بل العمود أضيق من القيمة. وسّع العمود.

خمس عادات تجعل معادلاتك أدق وأسهل في المراجعة

1. ابنِ المعادلة على مراحل: اكتب كل جزء في عمود مساعد وتأكد من نتيجته، ثم ادمج الأجزاء في معادلة واحدة إذا احتجت.
2. استخدم «تقييم الصيغة»: من تبويب «صيغ» اختر Evaluate Formula لترى كيف يحسب الإكسل المعادلة خطوة بخطوة، وتكتشف موضع الخطأ.
3. سمِّ النطاقات المهمة: اكتب اسمًا مثل VAT_Rate في مربع الاسم لخلية النسبة، فتصبح المعادلة =D2*VAT_Rate مفهومة لأي زميل يقرأ الملف.
4. حوّل البيانات إلى جدول: بعد Ctrl+T تستخدم المعادلات أسماء الأعمدة بدل عناوين الخلايا، وتمتد تلقائيًا للصفوف الجديدة.
5. لا تكتب أرقامًا ثابتة داخل المعادلة: ضع النسب والحدود في خلايا مستقلة وأشر إليها، فإذا تغيّرت عدّلت خلية واحدة فقط.

تعلّم معادلات الإكسل بتدريب منظم

قراءة الصيغ مفيدة، لكن الإتقان يأتي من حل مسائل حقيقية مع مدرب. في معهد أبجدية العالي للتدريب، تتناول دورة مهارات برنامج الإكسل المتقدمة الصيغ ثلاثية الأبعاد، وتحسين المعادلات بتوظيف الأسماء، والدمج بين الأسماء وأنواع مراجع الخلايا، وتطوير التقارير الديناميكية وربطها بالفلاتر. والدورة متزامنة عن بعد عبر أكاديمية زامن، ومدتها 5 أيام بواقع 20 ساعة تدريبية.

وإذا كنت ما زلت في البداية، فابدأ بـ دورة مهارات برنامج الإكسل للمبتدئين ثم انتقل إلى المتقدمة. ولمن يعمل أو يريد العمل في الموارد البشرية، فمقرر تطبيقات الحاسب الآلي في الموارد البشرية من مقررات دبلوم إدارة الموارد البشرية في المعهد.

الأسئلة الشائعة