التنسيق الشرطي باستخدام المعادلات في إكسل
تعلم كيفية استخدام التنسيق الشرطي مع المعادلات في إكسل لتمييز الصفوف والتواريخ المكتملة والقيم المكررة بدقة واحترافية.
بقلم Ayoub Farid · · 3 دقائق قراءة
تغطي القواعد الجاهزة في برنامج Excel (مثل أكبر من، أو القيم المكررة، أو تدرجات الألوان) الحالات البسيطة فقط. لكن عند استخدام صيغة أو معادلة، يمكنك تمييز أي شيء تريده تقريباً: تلوين صف بأكمله عندما تصبح الحالة "Late"، أو تمييز التواريخ المستحقة خلال أسبوع، أو تحديد المبيعات التي تقل عن الهدف المحدد لها. يفيدك هذا الأسلوب كثيراً عند تنظيم أي جدول بيانات أو أثناء التحضير لأي اختبار إكسل للتوظيف.
كيف تضيف قاعدة تنسيق باستخدام صيغة
- حدد النطاق المطلوب تنسيقه، بدءاً من الخلية الأولى في الزاوية العلوية (مثلاً A2:F100).
- انتقل إلى الشريط الرئيسي (Home) > التنسيق الشرطي (Conditional Formatting) > قاعدة جديدة (New Rule) > استخدام صيغة لتحديد الخلايا التي سيتم تنسيقها (Use a formula to determine which cells to format).
- اكتب معادلة تعطي النتيجة TRUE أو FALSE بالنسبة للخلية الأولى في أعلى النطاق.
- اختر التنسيق المطلوب (Format)، ثم اضغط موافق (OK).
سيطبق Excel هذه المعادلة على كل خلية داخل النطاق المحدد، مع تعديل مراجع الخلايا تلقائياً تماماً كما لو قمت بإجراء سحب المعادلة للأسفل.
قاعدة علامة التثبيت ($)
هنا بالتحديد يقع أغلب المستخدمين في الخطأ أثناء تعلم إكسل وتطبيق قواعده.
لتمييز الصف بأكمله عندما تحتوي الخلية في العمود F على كلمة "Late":
=$F2="Late"$F: تعني تثبيت العمود F (المرجع المطلق للعمود)، بحيث يفحص هذا العمود دائماً حتى أثناء تنسيق الخلايا في الأعمدة A و B و C وما يليها.2(بدون علامة $): المرجع النسبي لرقم الصف، لكي يتحرك التنسيق نزولاً مع كل صف على حدة.
إذا لم تضع علامة $، فسيقوم العمود B بفحص الخلية G2، والعمود C بفحص الخلية H2، وهكذا. لمعرفة المزيد حول هذه النقطة، راجع المراجع المختلطة في إكسل.
صيغ وقواعد مفيدة للتنسيق الشرطي
| التمييز المطلوب | المعادلة (إذا كان النطاق يبدأ من الصف 2) |
|---|---|
| تواريخ متأخرة عن موعدها في (C) | =AND($C2<TODAY(),$C2<>"") |
| تاريخ يستحق خلال 7 أيام | =AND($C2>=TODAY(),$C2<=TODAY()+7) |
| قيمة أقل من الهدف المحدد في الخلية F1 | =$B2<$F$1 |
| قيمة أقل من هدفها الخاص في العمود (C) | =$B2<$C2 |
| القيم المكررة في العمود A | =COUNTIF($A$2:$A$500,$A2)>1 |
| أعلى 3 مبيعات | =$B2>=LARGE($B$2:$B$100,3) |
| تلوين صف بعد صف بالتناوب | =MOD(ROW(),2)=0 |
| حقل إلزامي فارغ | =$D2="" |
اختبر المعادلة أولاً
اكتب المعادلة في خلية فارغة بجوار الصف الثاني وقم بعمل سحب المعادلة للأسفل. إذا ظهرت النتيجة TRUE في الصفوف المناسبة التي تريد تلوينها، فهذا يعني أن دوال إكسل وصيغتها صحيحة وجاهزة للاستخدام كقاعدة تنسيق شرطي.
إدارة القواعد وإصلاح أخطائها
انتقل إلى الشريط الرئيسي (Home) > التنسيق الشرطي (Conditional Formatting) > إدارة القواعد (Manage Rules)؛ وستظهر لك جميع القواعد الحالية مع معادلات إكسل الخاصة بها ونطاق التطبيق لكل منها. ترجع أغلب مشاكل "تنسيق الصفوف الخاطئة" إما إلى وضع علامة $ في مكان غير صحيح، أو لأن النطاق يبدأ من صف يختلف عن الصف المذكور في المعادلة نفسها.
الأسئلة الشائعة
كيف أقوم بتمييز صف كامل بناءً على قيمة خلية معينة؟
حدد الصفوف بالكامل، ثم أضف قاعدة تنسيق تعتمد على صيغة مثل =$F2="Late"، مع التأكد من وضع علامة $ قبل اسم العمود فقط وليس رقم الصف.
لماذا يقوم التنسيق الشرطي بتمييز خلايا غير صحيحة؟
يحدث هذا عادةً عندما لا تتطابق مراجع الخلايا في المعادلة مع أول خلية أعلى النطاق المحدد، أو بسبب استخدام غير صحيح لعلامات التثبيت ($).
كيف أميز التواريخ التي تسبق تاريخ اليوم؟
استخدم المعادلة =$C2<TODAY() كصيغة لتطبيق قاعدة التنسيق الشرطي.
هل يدعم تطبيق Google Sheets التنسيق الشرطي القائم على المعادلات؟
نعم، من القائمة العلوية: تنسيق (Format) > التنسيق الشرطي (Conditional formatting) > ثم اختر "صيغة مخصصة هي" (Custom formula is).