10 معادلات في برنامج الاكسل لا يعرفها سوى المحترفين

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

معادلات الاكسل الاحترافية: ما هي ولماذا تهمّك؟

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

1- معادلة max

معادلة max عند استخدامها في برنامج الاكسل تكشف لك أعلى قيمة من بين الخيارات المحددة، بإمكانك كتابتها كالآتي في الأكسل: () Max= وبين القوسين تحدّد مجال القيم في الأكسل.

الصيغة: =MAX(B2:B20)

2- معادلة min

على عكس معادلة max فإن معادلة min تتيح لك الوصول إلى أقل قيمة من الخيارات المتاحة، ودالتها هي () Min= وبين القوسين تكون الخلايا أو المجال الذي ترغب في تحديد أقل قيمة فيه.

الصيغة: =MIN(B2:B20)

3- معادلة IF

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

الصيغة: =IF(B2>12; "ممتلئ"; "فارغ")

4- معادلة find

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

الصيغة: =FIND("خطاف"; A2)

5- معادلة Count

معادلة Count هي دالة تتيح لك عدد الخلايا التي تحتوي على بيانات (أرقام، تواريخ) للتعرف على الخلايا التي تحتوي على بيانات داخل ملفك.

الصيغة: =COUNT(B2:B20)

6- معادلة SUMIF وCOUNTIF

معادلة SUMIF وCOUNTIF من معادلات أكسل المتقدمة والمستخدمة لتبيين الوظائف الشرطية، إذ تعمل دالة SUMIF على جمع كافة العناصر الموجودة في الخلايا المحددة ولكن بناء على شرط معين، على سبيل المثال جمع القيم التي تساوي وأكبر من 30 وترك الباقي. ودالة COUNTIF تُستخدم في عد الخلايا والعناصر بناء على شرط محدد.

الصيغة: =SUMIF(B2:B20; ">=30") · =COUNTIF(B2:B20; ">=30")

7- معادلة PMT وIPMT

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

الصيغة: =PMT(5%/12; 60; -100000) · =IPMT(5%/12; 1; 60; -100000)

8- معادلة CONCATENATE

معادلة CONCATENATE هي ليست معادلة وظيفية بالكامل، بل تُستخدم لتنظيم الخلايا وربط المعلومات من خلايا مختلفة وجعل المستند أكثر ديناميكية ومتناسق. أداة قوية للمحللين الماليين الذين يستخدمون النمذجة المالية.

الصيغة: =CONCATENATE(A2; " "; B2) · أو الأقصر: =A2&" "&B2

9- معادلة LOOKUP

معادلة LOOKUP تبحث عن القيم عموديًا وأفقيًا في الجدول أو المصفوفة وتقوم بإرجاع النتيجة من نفس الصف. فعلى سبيل المثال من المُمكّن استخدامها للبحث عن رقم التواصل مع أحد العملاء أو اسم مدينة بناء على الرقم السري أو للاطلاع على عدد المبيعات من منتج معين. 

الصيغة: =VLOOKUP("خطاف"; A2:D50; 3; 0)

10- معادلة IFERROR

معادلة IFERROR مفيدة للغاية من أجل استرجاع القيم عندما يعطي الأكسل خطأ، فمثلًا، بإمكانك عبرها عرض “بيانات غير صالحة” بدلاً من #VALUE! فعلى سبيل المثال إذا كُنت ترغب في البحث عن تاريخ محدد في الملف ولم يكُن هناك متاح فسوف يتم إظهار خطأ #N/A. وبدلًا من ذلك يمكنك تخصيص الرسالة التي تظهر للمستخدم. 

الصيغة: =IFERROR(VLOOKUP("خطاف"; A2:D50; 3; 0); "بيانات غير صالحة")

جدول مرجعي سريع: الصيغ جاهزة للنسخ

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

الفاصلة أم الفاصلة المنقوطة؟ أول ما يعطّل المعادلة

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

في الإعدادات الإنجليزية يكون فاصلة ,، وفي كثير من الإعدادات العربية يكون فاصلة منقوطة ;. فإن لم تعمل معك =IF(B2>12,"ممتلئ","فارغ") فجرّب =IF(B2>12;"ممتلئ";"فارغ") والعكس صحيح. لذلك كتبت الصيغ في هذا المقال بالفاصلة المنقوطة.

لتثبيته نهائياً: لوحة التحكم ← المنطقة ← إعدادات إضافية ← فاصل القوائم.

لماذا تنكسر المعادلة عند السحب؟ المرجع النسبي والمطلق

حين تكتب =B2*C2 ثم تسحب المعادلة لأسفل، يتحرك المرجعان معاً فتصير =B3*C3 — وهذا ما تريده غالباً. لكن إن كان أحد الطرفين ثابتاً، كنسبة ضريبة في الخلية F1، فسحب =B2*F1 سيحوّلها إلى =B3*F2 وتنكسر النتيجة دون أن ينبّهك الاكسل.

الحل علامة الدولار: $F$1 تُثبّت العمود والصف معاً، وF$1 تُثبّت الصف وحده، و$F1 تُثبّت العمود وحده. وأسرع طريقة: ضع المؤشر على المرجع داخل شريط المعادلة واضغط F4 ليتنقّل بين الأشكال الأربعة.

هذه النقطة تحديداً تهمّ مع VLOOKUP وSUMIF: نطاق البحث يجب أن يكون مطلقاً دائماً، أي $A$2:$D$50، وإلا انزلق النطاق مع كل صف تسحبه.

رسائل الخطأ في الاكسل وماذا تعني كل واحدة

رسالة الخطأ ليست عطلاً، بل تشخيص. معرفة ما تعنيه تختصر عليك نصف وقت البحث:

وهنا تظهر فائدة IFERROR: بدل أن يرى من يفتح ملفك رموزاً غامضة، يرى عبارة مفهومة تكتبها أنت.

ثلاثة أخطاء تتكرر مع هذه المعادلات

الأول: نسيان الصفر في آخر VLOOKUP. بدون 0 أو FALSE يبحث الاكسل عن أقرب تطابق لا التطابق التام، فيعطيك نتيجة خاطئة دون أي رسالة خطأ — وهذا أخطر من الخطأ الظاهر.

الثاني: الخلط بين COUNT وCOUNTA. دالة COUNT تعدّ الأرقام فقط، فإن كان عمودك أسماء ستحصل على صفر. استخدم COUNTA لعدّ كل الخلايا غير الفارغة.

الثالث: أرقام مخزّنة كنصوص. تبدو أرقاماً لكن MAX وSUM تتجاهلها. علامتها مثلث أخضر صغير في زاوية الخلية، وعلاجها من قائمة التنبيه بـ «تحويل إلى رقم».

أسئلة شائعة عن معادلات الاكسل

ما الفرق بين COUNT وCOUNTA وCOUNTBLANK؟

COUNT تعدّ الخلايا ذات الأرقام والتواريخ، وCOUNTA تعدّ كل خلية فيها شيء أيّاً كان نوعه، وCOUNTBLANK تعدّ الفارغة وحدها. الثلاث معاً تعطيك صورة كاملة عن جودة بياناتك.

متى أستخدم VLOOKUP ومتى XLOOKUP؟

VLOOKUP تبحث يميناً من عمود البحث فقط، وتنكسر إن أدرجت عموداً جديداً. وXLOOKUP — المتاحة في Microsoft 365 وإصدارات 2021 فما بعدها — تبحث في أي اتجاه، وتقبل قيمة بديلة عند عدم العثور دون حاجة لـ IFERROR. استخدمها إن كان إصدارك يدعمها، وإلا فـ VLOOKUP تؤدي الغرض.

هل تعمل هذه المعادلات في Google Sheets؟

نعم، العشر كلها تعمل بنفس الأسماء. الفرق الوحيد أن Google Sheets يستخدم الفاصلة , دائماً لا يتبع إعداد النظام، وأن CONCATENATE يُفضّل فيه استخدام TEXTJOIN. وإن كنت تعمل على قوقل شيت أكثر من الاكسل، ففيه دوال لا نظير لها هنا مثل QUERY وARRAYFORMULA — جمعتها في ٢٥ مهارة في قوقل شيت لا يعرفها أكثر المستخدمين.

ختامًا، الآن تكون قد تعرّفت على 10 معادلات في برنامج الاكسل تساعدك على إتمام مهامك اليومية والحصول على إجابات دقيقة وتجعلك تستخدم الأكسل مثل المحترفين.

ولأن العمل المكتبي لا يقتصر على الجداول، جمعت لك في ٦ من أفضل محررات النصوص أدوات تُكمّل الأكسل في إنشاء المستندات وتحريرها.