كيفية جعل أقل من أو يساوي في إكسيل. يقوم Excel بوظائف if (if) وifs (ifs) لعدة شروط

وظيفة المنطق لوفي Excel - واحدة من الأكثر شعبية. تقوم بإرجاع نتيجة (قيمة أو صيغة أخرى) حسب الشرط.

الدالة IF في برنامج Excel

تحتوي الدالة على بناء الجملة التالي.

لو( log_expression; value_if_true; [القيمة_إذا_خطأ])

log_expression هو شرط ليتم التحقق. على سبيل المثال، A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

value_if_true – القيمة أو الصيغة التي يتم إرجاعها عند وقوع الحدث المحدد في المعلمة الأولى.

value_if_false هي قيمة أو صيغة بديلة يتم إرجاعها في حالة عدم استيفاء الشرط. هذا الحقل ليس مطلوبا لملءه. في هذه الحالة، عند حدوث حدث بديل، ستقوم الدالة بإرجاع FALSE.

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

مبيعات المنتج الأول هي 75، أي. استيفاء شرط أن يكون أكبر من 30. ولذلك، تقوم الدالة بإرجاع ما هو محدد في الحقل التالي - "موافق". مبيعات المنتج الثاني أقل من 30، لذلك لم يتم استيفاء الشرط (> 30) وتم إرجاع القيمة البديلة المحددة في الحقل الثالث. هذا هو بيت القصيد من الدالة IF. ومن خلال توسيع الحساب، نحصل على النتيجة لكل منتج.

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

حتى الآن كل شيء منطقي، ولكن العيوب مربكة. هل هناك أرصدة سلبية؟ بالطبع لا. لا يمكن أن تكون المخزونات أقل من الصفر. ولكي تكون التوقعات صحيحة، يجب استبدال القيم السالبة بالأصفار. تعتبر صيغة IF مساعدة كبيرة هنا. سوف يتحقق من القيمة المتوقعة وإذا كانت أقل من الصفر فإنه سيعطي الإجابة بقوة 0، وإلا فإنه سيعطي نتيجة الحساب، أي. بعض الأرقام الإيجابية. بشكل عام، نفس المنطق، ولكن بدلا من القيم نستخدم صيغة كشرط.

لم تعد هناك قيم سلبية في توقعات المخزون، وهو أمر جيد جدًا بشكل عام.

تُستخدم صيغ Excel IF أيضًا على نطاق واسع في صيغ الصفيف. لن نتعمق كثيرًا هنا. للمهتمين أنصح بقراءة المقال عنه. صحيح أن الحساب في تلك المقالة لم يعد ذا صلة، لأنه قدم Excel 2016 وظائف MINESLI وMAXESLI. ولكن من المفيد جدًا أن ننظر إلى المثال، فهو سيكون مفيدًا في موقف آخر.

صيغة IF في Excel - أمثلة لعدة شروط

في كثير من الأحيان، لا يكون عدد الشروط المحتملة 2 (قابلة للاختبار وبديلة)، بل 3 أو 4 أو أكثر. في هذه الحالة، يمكنك أيضًا استخدام الدالة IF، ولكن سيتعين عليك الآن دمجها داخل بعضها البعض، مع تحديد جميع الشروط بدورها. النظر في المثال التالي.

يحتاج العديد من مديري المبيعات إلى منح مكافأة اعتمادًا على تنفيذ خطة المبيعات. نظام التحفيز هو على النحو التالي. إذا تم تنفيذ الخطة بنسبة أقل من 90%، فلا توجد مكافأة مستحقة، وإذا كانت من 90% إلى 95% - مكافأة 10%، ومن 95% إلى 100% - مكافأة 20%، وإذا تم تجاوز الخطة، إذن 30%. كما ترون هنا هناك 4 خيارات. للإشارة إليهم في صيغة واحدة، ستحتاج إلى ما يلي البنية المنطقية. فإذا تحقق الشرط الأول وقع الخيار الأول، وإلا فإذا تحقق الشرط الثاني وقع الخيار الثاني، وإلا إذا... إلخ. يمكن أن يكون عدد الشروط كبيرًا جدًا. تتم الإشارة إلى الأخير في نهاية الصيغة خيار بديل، والذي لم يتم استيفاء أي من الشروط المذكورة مسبقًا (مثل الحقل الثالث في صيغة IF عادية). ونتيجة لذلك، تبدو الصيغة كما يلي:

تعمل مجموعة وظائف IF بطريقة بحيث أنه عند استيفاء أي شرط محدد، لم يعد يتم التحقق من الشروط التالية. ولذلك، فمن المهم الإشارة إليها بالتسلسل الصحيح. إذا بدأنا التحقق مع B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

من السهل أن تصاب بالارتباك عند كتابة صيغة، لذا من الجيد إلقاء نظرة على تلميح الأداة.

في النهاية، يجب عليك إغلاق جميع الأقواس، وإلا فسيقوم برنامج Excel بإلقاء خطأ.

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

أضاف MS Excel 2016 وظيفة IFS، من أجل كتابة هذا المقال بأكمله. هذا هو نفس IF، ولكن تم شحذه خصيصًا لاختبار العديد من الحالات. الآن لا تحتاج إلى كتابة IF مائة مرة وعد الأقواس المفتوحة. يكفي ذكر الشروط وإغلاق قوس واحد في النهاية.

يعمل على النحو التالي. لنأخذ المثال أعلاه ونستخدم صيغة IFS الجديدة في Excel.

كما ترون، كتابة الصيغة تبدو أبسط وأكثر وضوحًا.

يجدر الانتباه إلى ما يلي. مازلنا ندرج المصطلحات بالترتيب الصحيح لتجنب تداخل النطاق غير الضروري. يجب أيضًا تحديد الشرط البديل الأخير، على عكس IF المعتاد. IF يحدد فقط قيمة بديلة تحدث في حالة عدم استيفاء أي من الشروط المدرجة. هنا تحتاج إلى الإشارة إلى الشرط نفسه، والذي سيكون في حالتنا B2>=1. ومع ذلك، يمكن تجنب ذلك عن طريق كتابة TRUE في حقل الشرط، للإشارة إلى أنه في حالة عدم استيفاء الشروط المذكورة مسبقًا، يحدث TRUE ويتم إرجاع القيمة البديلة الأخيرة.

إذا كان (التعبير المنطقي، value_if_true، value_if_false)

للقيام بذلك عليك القيام بما يلي:

1. انقر على أيقونة إدراج الصيغة (Fx).
2. في القائمة التي تظهر، حدد "إذا".

1. ونتيجة لذلك، سترى نافذة تحتاج فيها إلى تحديد وسيطات الوظيفة:
   • تعبير منطقي؛
   • القيمة إذا كانت صحيحة؛
   • القيمة إذا كاذبة.
2. بعد إدخال القيم في هذه الحقول، سيتم عرض نتيجة أولية على يمينها.
3. للإدراج، انقر على زر "موافق".

أمثلة

دعونا نلقي نظرة على بعض التعبيرات المنطقية المختلفة لمساعدتك على فهم كيفية استخدام هذه الوظيفة.

1. اجعل الخلية "A1" نشطة.
2. كرر الخطوات الموضحة أعلاه لفتح نافذة إدراج "الوسائط".
3. أدخل الكود التالي في حقل الحالة.

معنى هذا التعبير هو كما يلي: إذا كانت الخلية C1 تحتوي على فراغ. أي أنه لا يوجد شيء فيه.

1. في حقل الحقيقة، أدخل ما يلي.

هذا هو النص الذي سيتم عرضه إذا تم استيفاء هذا الشرط.

1. في الحقل "إذا كان خطأ"، أدخل ما يلي.

وسنرى هذا النص إذا كان الشرط خاطئا.

1. لإدراج الصيغة لدينا، انقر على زر "موافق".

1. ونتيجة لذلك سنرى ما يلي (بما أن الخلية فارغة فالرسالة مناسبة).

1. أدخل أي نص في الخلية C.

1. بعد الضغط على زر Enter ستظهر لك النتيجة التالية.

كما ترون، قام محرر Excel بإنتاج الرسالة التي أشرنا إليها في حالة وجود نتيجة خاطئة للشرط.

مشغلي المقارنة

أدناه ترى قائمة عوامل المقارنة:

العوامل الحسابية

وهذا جدول العوامل الحسابية:

يمكنك قراءة المزيد حول العوامل الحسابية وأسبقيتها في Excel في التعليمات عبر الإنترنت على موقع Microsoft على الويب.

شروط متعددة

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

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

للقيام بذلك، يجب عليك تنفيذ الخطوات التالية.

1. انتقل إلى علامة التبويب "الصيغ". انقر على زر "إدراج وظيفة".

1. ونتيجة لذلك، سيتم استبدال محتويات الخلية بالكامل تلقائيًا في "المنشئ".

1. احذف كل شيء في الحقل "Value_if_false".

1. الآن سيتم كتابة تعبير منطقي جديد في هذا السطر. أي أنه سيكون لدينا حالة متداخلة جديدة. أولاً، أدخل الشرط "إذا كانت قيمة الخلية C1 أكبر من 1000". للقيام بذلك، أدخل الكود التالي. تأكد من تضمين فاصلة منقوطة في النهاية.

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

1. لنقم الآن بإضافة رسالة سيتم عرضها إذا كان الرقم الموجود في الخلية C1 أكبر من 1000.

يجب إدخال جميع القيم النصية بين علامتي اقتباس.

1. والآن، بنفس الطريقة تمامًا، نقوم بإدخال القيمة إذا لم يتم استيفاء هذا الشرط.

1. وأخيرا، انقر على زر "موافق".

1. ونتيجة لذلك تظهر لنا رسالة تفيد بأن الرقم المدخل أكبر من 1000.

1. احذف محتويات الخلية C. والنتيجة هي التالية.

لقد اختبرنا جميع النتائج الثلاثة المحتملة. كل شيء يعمل بشكل رائع.

نسخ دالة في الجداول

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

دعونا نلقي نظرة على مثال للنسخ على جدول المكافآت للموظفين خلال العطلات. للقيام بذلك تحتاج إلى اتخاذ الخطوات التالية.

1. قم بإنشاء الجدول المناسب.

1. انقر على خلية فارغة في الصف الأول وحدد "Fx" في حقل الإدخال.

1. في النافذة التي تظهر، حدد وظيفة "IF" وانقر على زر "موافق".

1. في الحقل الأول أدخل الشرط التالي.

بهذه الطريقة نتحقق مما إذا كان الموظف رجلاً.

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

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

1. وستكون النتيجة ما يلي.

1. الآن انسخ الجدول بأكمله بالكامل (باستخدام اختصار لوحة المفاتيح Ctrl + C وCtrl + V). تغيير العنوان إلى 8 مارس.

1. انتقل إلى الخلية الأولى. انقر على حقل إدخال الصيغة.

1. تغيير الحرف "M" إلى "F".

1. الآن قم بتكرار الوظيفة إلى الأسفل.

1. وستكون النتيجة ما يلي.

وهنا نرى أن العكس تماماً قد حدث. وهذا يعني أن كل شيء يعمل بشكل صحيح.

استخدام عوامل تشغيل إضافية

بالإضافة إلى التعبيرات الحسابية والمقارنات، يمكنك أيضًا استخدام عاملي التشغيل AND وOR. دعونا نلقي نظرة فاحصة عليهم، لأنه بفضلهم يتم توسيع إمكانيات وظيفة "IF" بشكل كبير.

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

المشغل و

سوف نستخدم الوظيفة القديمة التي تحققنا فيها مما إذا كان الموظف رجلاً. الآن أنت بحاجة إلى إجراء التغييرات التالية.

1. أضف قوسين إلى الشرط والحرف "I" بجانبه.
2. الشرط القديم سيكون الأول، والثاني سيكون بعد الفاصلة المنقوطة.

1. قم بتكرار هذه الصيغة وصولاً إلى الأسفل.

1. ونتيجة لذلك، نرى أن هؤلاء الموظفين الذين ليسوا جزءًا من فريق العمل الأساسي سيتم حرمانهم من مكافآت العطلات. رغم أنهم رجال.

المشغل أو

يمكن تحقيق نفس التأثير باستخدام عامل التشغيل OR.

للقيام بذلك، قم بإجراء التغييرات التالية على الصيغة:

1. تغيير الحرف "F" إلى "M".
2. تغيير حالة الموظف إلى "عامل بدوام جزئي".
3. نضع 0 في حقل الحقيقة، و3000 لحدث كاذب ونبدلهما.

1. دعونا نكرر الصيغة حتى السطر الأخير.

1. وكانت النتيجة هي نفسها تماما. الحقيقة هي أن العاملين "AND" و"OR" هما عكس بعضهما البعض تمامًا. لذلك، من المهم جدًا الإشارة بشكل صحيح إلى القيم في الحقول الصحيحة والخاطئة. لا تخطئ.

1. للتحقق، يمكنك تغيير حالة موظف واحد إلى "الموظف الأساسي".
2. بعد ذلك مباشرة سترى أن الرقم 3000 سيظهر أمام اسمه الأخير.

دالة سوميف

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

على سبيل المثال، لنأخذ جدولاً يسرد المنتجات المختلفة وتكلفتها وكميتها وحالتها. حساب المبلغ الإجمالي سهل. ولكن كيف يمكنك معرفة عدد المنتجات المباعة؟

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

• 5 أكثر من 8؟
• هل محتوى الخلية A5 أقل من 8؟
• أو ربما يساوي 8؟

عوامل المقارنة في Excel

يحتوي Excel على عدد من عوامل التشغيل القياسية التي يتم استخدامها لتعيين شروط منطقية بسيطة. يتم عرض جميع عوامل المقارنة الستة المحتملة في الجدول أدناه:

كيفية تعيين شرط في Excel

تتيح لك عوامل المقارنة تحديد الشروط التي تُرجع القيم المنطقية TRUE أو FALSE. فيما يلي أمثلة على استخدام الشروط المنطقية:

=أ1=ب1- سيرجع هذا الشرط TRUE إذا كانت القيم في الخلايا A1 وB1 متساوية، أو FALSE بخلاف ذلك. من خلال تعيين هذا الشرط، يمكنك مقارنة السلاسل النصية بطريقة غير حساسة لحالة الأحرف. على سبيل المثال، عند مقارنة "يناير" و"يناير"، ستعرض الصيغة TRUE.

=أ1>ب1– ستُرجع الصيغة التالية TRUE إذا كانت القيمة الموجودة في الخلية A1 أكبر من تلك الموجودة في B1. وإلا، سترجع الصيغة FALSE. يمكن أيضًا إجراء مثل هذه المقارنات عند العمل مع النص.

على سبيل المثال، إذا كانت الخلية A1 تحتوي على القيمة "برتقالي" وكانت الخلية B1 تحتوي على "بطيخ"، فسترجع الصيغة FALSE لأن "البطيخ" أقل ترتيبًا أبجديًا من "برتقالي". كلما كان أقل، كلما كان ذلك أكثر.

=أ1<=B1 – ستُرجع الصيغة TRUE إذا كانت القيمة الموجودة في الخلية A1 أقل من أو تساوي القيمة الموجودة في الخلية B1. وإلا فإن النتيجة ستكون كاذبة.

=أ1<>ب1- سترجع الصيغة TRUE إذا كانت قيم الخلايا A1 و B1 غير متساوية. خلاف ذلك - خطأ.

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

إذا كنت متأكدا من أنك قد أتقنت هذا الموضوع جيدا بما فيه الكفاية، فيمكنك الرجوع إلى المقالة استخدام وظائف Excel المنطقية لتعيين الشروط المعقدةلمعرفة كيفية ضبط الشروط باستخدام وظائف منطقية مختلفة، على سبيل المثال. و()أو أو().