كيفية تطبيق التنسيق الشرطي بناءً على VLOOKUP في Excel؟

على سبيل المثال، قمت بإدراج جميع درجات الطلاب والدرجات من الفصل الدراسي الماضي في ورقتين عمل كما هو موضح في لقطة الشاشة أدناه. الآن تريد مقارنة الدرجات في ورقتين العمل، وتسليط الضوء على الصفوف في الدرجات إذا كانت الدرجات أعلى من تلك الموجودة في الفصل الدراسي الماضي. في هذه الحالة، يمكنك تطبيق التنسيق الشرطي بناءً على دالة VLOOKUP في Excel.

1في ورقة الدرجات، حدد درجات الطلاب باستثناء العناوين (في حالتي أختار B3:C26)، ثم انقر فوق الصفحة الرئيسية > تنسيق شرطي > قاعدة جديدةانظر لقطة الشاشة:

2في مربع الحوار قاعدة التنسيق الجديدة، يرجى القيام بما يلي:
(1) انقر لتحديد استخدم صيغة لتحديد الخلايا التي سيتم تنسيقها في اختر نوع القاعدة مربع القائمة؛
(2) في قيم التنسيق حيث تكون هذه الصيغة صحيحة مربع، يرجى إدخال هذه الصيغة =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3;
(3) انقر فوق الزر تنسيق .

ملاحظة: في الصيغة أعلاه،

• $B3 هي أول خلية اسم طالب في ورقة الدرجات؛
• 'Score of Last Semester'!$B$2:$C$26 هو جدول درجات الفصل الدراسي الماضي في ورقة درجات الفصل الدراسي الماضي؛
• 2 يعني البحث عن القيم في العمود الثاني من جدول درجات الفصل الدراسي الماضي.
• Score!$C3 هي أول خلية درجة في ورقة الدرجات.

3في مربع الحوار تنسيق الخلايا، انتقل إلى تعبئة التبويب، حدد لون تعبئة، ثم انقر فوق موافق > موافق لإغلاق مربعين الحوار.

الآن سترى إذا كانت درجة الطالب في الدرجات أعلى من تلك الموجودة في درجات الفصل الدراسي الماضي الورقة، سيتم تسليط الضوء على صف هذا الطالب تلقائيًا. انظر لقطة الشاشة:

يمكنك أيضًا استخدام دالة VLOOKUP لمطابقة القيم في ورقتين عمل، ثم تطبيق التنسيق الشرطي بناءً على VLOOKUP ومطابقة النتائج في Excel. يرجى القيام بما يلي:

ملاحظة: اسحب شريط التمرير y لعرض الصورة أعلاه.

على سبيل المثال، لدي قائمة فائزين في Sheet1، وقائمة الطلاب في Sheet2 كما هو موضح في لقطة الشاشة أدناه. الآن سأقوم بمطابقة قائمة الفائزين مع قائمة الطلاب باستخدام دالة VLOOKUP، ثم تسليط الضوء على الصفوف المتطابقة في قائمة الفائزين.

1حدد قائمة الفائزين باستثناء العناوين، وانقر فوق الصفحة الرئيسية > تنسيق شرطي > قاعدة جديدة.

2في مربع الحوار قاعدة التنسيق الجديدة، يرجى القيام بما يلي:
(1) في اختر نوع القاعدة مربع القائمة، يرجى النقر لتحديد استخدم صيغة لتحديد الخلايا التي سيتم تنسيقها;
(2) في قيم التنسيق حيث تكون هذه الصيغة صحيحة مربع، يرجى إدخال هذه الصيغة =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)));
(3) انقر فوق الزر تنسيق .

ملاحظة: في الصيغة أعلاه،

• $C3 هو الاسم الأول في قائمة الفائزين؛
• Sheet2!$B$2:$C$24 هو قائمة الطلاب في Sheet2؛
• 1 يعني البحث عن القيمة المتطابقة في العمود الأول من قائمة الطلاب.

إذا كنت بحاجة إلى تسليط الضوء على القيم غير المتطابقة، يمكنك استخدام هذه الصيغة =ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3في مربع الحوار تنسيق الخلايا، انتقل إلى تعبئة التبويب، انقر لتحديد لون تعبئة، وانقر فوق موافق > موافق لإغلاق كلا مربعي الحوار.

الآن سترى إذا كان اسم في قائمة الفائزين يتطابق (أو لا يتطابق) مع قائمة الطلاب، سيتم تسليط الضوء على صف هذا الاسم تلقائيًا.

إذا كان لديك Kutools for Excel مثبتًا، يمكنك تطبيق ميزته تحديد نفس وخلايا مختلفة لسهولة تطبيق التنسيق الشرطي بناءً على VLOOKUP ومطابقة النتائج في Excel. يرجى القيام بما يلي:

Kutools for Excel- يتضمن أكثر من 300 أداة مفيدة لـ Excel. تجربة مجانية كاملة لمدة 60 يومًا، لا حاجة لبطاقة ائتمان! احصل عليه الآن

1انقر فوق Kutools > تحديد > تحديد نفس وخلايا مختلفة لتفعيل هذه الميزة.

2في مربع الحوار تحديد نفس وخلايا مختلفة، يرجى القيام بما يلي:
(1) حدد عمود الاسم في قائمة الفائزين في البحث عن القيم في مربع، حدد عمود الاسم في قائمة الطلاب في بناءً على مربع، ومن الممكن اختيارياً تحديد بياناتي تحتوي على عناوين خيار حسب الحاجة.
(2) حدد كل صف خيار في بناءً على قسم؛
(3) حدد قيم متطابقة أو قيم مختلفة خيار حسب الحاجة في البحث قسم؛
(4) حدد تعبئة لون الخلفية خيار، وحدد لون تعبئة من القائمة المنسدلة أدناه؛
(5) حدد تحديد الصف بأكمله خيار.

3. انقر فوق الزر موافق لتطبيق هذه الميزة.

الآن سترى أن جميع الصفوف ذات القيم المتطابقة (أو غير المتطابقة) يتم تسليط الضوء عليها واختيارها مرة واحدة، وفي الوقت نفسه يظهر مربع حوار ويبلغ عن عدد الصفوف التي تم اختيارها.