تصبح صيغ Excel أسهل عندما تُرتّب حسب المهمة التي تؤديها. يجمع هذا الدليل الحسابات الأساسية والمجاميع الشرطية ومعالجة الأخطاء والبحث في الجداول ودوال النص والتاريخ والمصفوفات الديناميكية. تستخدم الأمثلة أسماء الدوال الإنجليزية والفواصل بين الوسيطات، كما تعرضها وثائق Microsoft العربية. قد يستخدم Excel فاصلاً مختلفاً وفق إعدادات النظام، بينما يبقى منطق الحساب نفسه.
المحتويات:
كيف تقرأ صيغة Excel؟
تبدأ كل صيغة بعلامة المساواة. تحدد مراجع الخلايا مكان القيم، وتحدد عوامل التشغيل نوع الحساب. على سبيل المثال، تضرب الصيغة =B2*C2 الكمية في سعر الوحدة.
تتحرك المراجع النسبية عند نسخ الصيغة، بينما تبقى المراجع المطلقة ثابتة. يثبّت $A$1 العمود والصف، ويثبّت $A1 العمود فقط، ويثبّت A$1 الصف فقط. يفيد ذلك عندما يتكرر معدل أو نسبة أو نطاق بحث في صفوف كثيرة.
الحسابات ومراجع الخلايا
تغطي العمليات الأساسية معظم جداول التكاليف والمصروفات الصغيرة. إذا كانت الكمية في B2 وسعر الوحدة في C2، يكون مبلغ السطر =B2*C2. ويُستخدم + للجمع و- للطرح و/ للقسمة.
- =B2+C2 يجمع قيمتين.
- =B2-C2 يحسب الفرق.
- =B2/C2 يقسم قيمة على أخرى.
- =ROUND(D2,2) يقرّب القيمة إلى منزلتين عشريتين.
- =ABS(D2) يعيد القيمة المطلقة للعدد.
اختر موضع التقريب بوعي. فقد يختلف مجموع الصفوف المقرّبة عن نتيجة تقريب الإجمالي النهائي.
المجاميع والمتوسطات والحدود
تجيب هذه الدوال عن أسئلة مثل: ما الإجمالي، وما متوسط السعر، وما أدنى وأعلى قيمة؟
- =SUM(D2:D200) يجمع نطاقاً من الخلايا.
- =AVERAGE(C2:C200) يحسب متوسط السعر.
- =MIN(C2:C200) و=MAX(C2:C200) يعرضان أدنى وأعلى قيمة.
- =COUNT(D2:D200) يعدّ الخلايا الرقمية.
- =COUNTA(D2:D200) يعدّ الخلايا غير الفارغة.
في الجداول الطويلة، استخدم نطاقاً يغطي الصفوف المتوقعة، واختر عموداً يُملأ دائماً حتى لا تخفي الخلايا الفارغة نتيجة العد.
الحسابات الشرطية
تفيد الصيغ الشرطية عندما يحتوي الجدول على فئات أعمال أو مشروعات أو مقاولين أو بنود تكلفة.
في الأمثلة التالية، تحتوي F2 على فئة المواد، وF3 على فئة الأعمال، وF4 على فئة التوصيل، بينما تحتوي G2 على اسم المشروع. تبقي مراجع الخلايا نصوص المعايير بلغتك من دون إدخالها داخل الصيغة.
- =SUMIF(A:A,F2,D:D) يجمع الصفوف المصنفة كمواد.
- =COUNTIF(A:A,F3) يعدّ صفوف الأعمال.
- =AVERAGEIF(A:A,F4,C:C) يحسب متوسط تكلفة التوصيل.
- =SUMIFS(D:D,A:A,F2,B:B,G2) يجمع الصفوف التي تحقق شرطين.
- =COUNTIFS(A:A,F2,E:E,">0") يعدّ الصفوف التي تحقق شرطين.
المنطق ومعالجة الأخطاء
تحوّل الدوال المنطقية قواعد العمل إلى حسابات قابلة للتكرار. ويمكنها إبقاء الخلية فارغة حتى إدخال الكمية، أو اختبار عدة شروط، أو استبدال خطأ الصيغة بنتيجة واضحة.
- =IF(B2="","",B2*C2) يحسب المبلغ عند وجود الكمية فقط.
- =AND(A2<>"",C2>0) يتحقق من شرطين معاً.
- =OR(A2=F2,A2=F3) يقبل إحدى الفئتين.
- =IFERROR(B2*C2,"") يعيد خلية فارغة إذا فشل الحساب.
يصعب تدقيق السلاسل الطويلة من دوال IF المتداخلة. غالباً ما تجعل الأعمدة الوسيطة والجدول المرجعي الواضح ملف العمل أكثر أماناً وأسهل في المراجعة.
البحث والجداول المرجعية
يمكن أن يحتفظ الجدول المرجعي بأكواد البنود ووصف الأعمال والوحدات والأسعار القياسية. عندها يضم جدول العمل كود البند ويسترجع البيانات المطابقة تلقائياً.
- =VLOOKUP(A2,$H$2:$K$200,4,FALSE) يبحث في العمود الأول ويعيد قيمة من العمود الرابع.
- =INDEX(K:K,MATCH(A2,H:H,0)) يجمع نطاق إرجاع مرناً مع مطابقة تامة.
- =XLOOKUP(A2,H:H,K:K,"") يعيد النتيجة من دون رقم عمود ثابت.
تتوفر XLOOKUP في Microsoft 365 وExcel 2021 والإصدارات الأحدث، ولا تتوفر في Excel 2016 أو Excel 2019. وتبقى INDEX مع MATCH مفيدة للملفات التي يجب أن تعمل في الإصدارات الأقدم.
النصوص والتواريخ والمصفوفات
تساعد دوال النص في تنظيف البيانات المستوردة، بينما تدعم دوال التاريخ فترات التقارير ومواعيد إنجاز الأعمال. تحوّل TEXT القيمة إلى نص، لذلك احتفظ بالقيمة الأصلية إذا كانت ستدخل في حسابات لاحقة.
- =LEN(A2) يعيد عدد الأحرف.
- =LEFT(A2,5) و=RIGHT(A2,5) و=MID(A2,3,4) تستخرج جزءاً من النص.
- =TRIM(A2) يزيل المسافات الزائدة.
- =TEXT(A2,"dd-mm-yyyy") يحوّل التاريخ إلى نص بالتنسيق المحدد.
- =TODAY() يعيد التاريخ الحالي، و=NOW() يعيد التاريخ والوقت.
- =DATE(A2,B2,C2) ينشئ تاريخاً من السنة والشهر واليوم المخزنة في الخلايا.
تدعم Microsoft 365 وExcel 2021 والإصدارات الأحدث المصفوفات الديناميكية. تعيد =FILTER(A2:D200,A2:A200=F2,"") الصفوف المطابقة، وترتّب =SORT(A2:D200,4,-1) النتائج حسب العمود الرابع، وتعيد =UNIQUE(A2:A200) القيم الفريدة.
إنشاء جدول تكاليف صغير
- أنشئ أعمدة للفئة والوصف والوحدة والكمية وسعر الوحدة والمبلغ والملاحظات.
- أدخل =D2*E2 في عمود المبلغ وانسخ الصيغة إلى الأسفل.
- أضف =SUM(F2:F200) لحساب الإجمالي العام.
- استخدم SUMIF لحساب الإجمالي لكل فئة.
- استخدم XLOOKUP أو INDEX مع MATCH لاسترجاع الأسعار من ورقة مرجعية.
- أضف IFERROR فقط في المواضع التي قد تنتج فيها القيم المفقودة أو القسمة خطأ متوقعاً.
كلما زاد عدد الملفات، أصبحت متابعة النسخة الأخيرة وسلامة الصيغ وصلاحيات الوصول أصعب. تساعد مساحة عمل مشتركة لقطاع البناء في جمع جداول التكاليف والمصروفات الفعلية والموافقات وسجل المشروع في مكان واحد. يوفّر تطبيق 101 أدوات لإدارة التكاليف وتخطيط المشروعات.