تعيين شرط Excel. كيف تتفوق باستخدام الدوال المتداخلة إذا () تحسب مكافأة المبيعات

IF () وظيفة , النسخة الإنجليزية لو ()، تستخدم عند التحقق من الشروط. على سبيل المثال، \u003d IF (A1\u003e 100؛ "الميزانية تجاوزت"؛ "موافق!")... حسب القيمة الموجودة في الخلية أ 1 ستكون نتيجة الصيغة إما "تم تجاوز الميزانية" أو "موافق!"

وظيفة IF () هي إحدى الوظائف الأكثر استخدامًا.

بناء جملة الوظيفة

إذا (boolean_test، value_if_true، [value_if_false])

Log_Expression - أي قيمة أو تعبير يمكن أن يكون صحيحًا أو خطأ.
\u003d IF (A1\u003e \u003d 100؛ "تجاوزت الميزانية"؛ "موافق!")
أولئك. إذا كان في الخلية أ 1 تحتوي على قيمة أكبر من أو تساوي 100 ، ثم تعود الصيغة موافق!،وإذا لم يكن كذلك ، إذن تم تجاوز الميزانية.

يمكن استخدام الصيغ كوسائط دالة ، على سبيل المثال:
\u003d IF (A1\u003e 100، SUM (B1: B10)، SUM (C1: C10))
أولئك. إذا كان في الخلية أ 1 يحتوي على قيمة\u003e 100 ، ثم يتم إجراء الجمع على العمود ب ، وإذا كان أقل ، ثم بالعمود من .

متداخل IF

في EXCEL 2007 كقيم وسيطة القيمة_إذا_ صحيح و القيمة_إذا_ خطأ يمكنك استخدام ما يصل إلى 64 دالة IF () متداخلة لإنشاء عمليات تحقق أكثر تعقيدًا.
\u003d IF (A1\u003e \u003d 100؛ "تجاوز الميزانية"؛ IF (A1\u003e \u003d 90؛ "مشروع كبير"؛ IF (A1\u003e \u003d 50؛ "مشروع متوسط"؛ "مشروع صغير")))

VIEW (A1؛ (0؛ 50؛ 90؛ 100)؛ ("مشروع صغير"؛ "مشروع متوسط"؛ "مشروع كبير"؛ "تجاوز الميزانية"))

VLOOKUP (A1 ؛ A3: B6 ؛ 2)

بالنسبة لوظيفة VLOOKUP () ، تحتاج إلى الإنشاء في النطاق A3: B6 جدول القيمة:

إذا كنت تريد عرض نص مختلف إذا كان في الخلية أ 1 قيمة سالبة أو قيمة موجبة أو 0 ، ثم يمكنك كتابة الصيغة التالية:

LOOKUP (A1 ؛ (- 1E + 307 ؛ 0 ؛ 1E-307) ؛ ("<0";"=0";">0"})

أو ، إذا كنت تريد عرض الصيغ بدلاً من القيم النصية ، يمكنك استخدام مراجع إلى نطاق من الخلايا (يحتوي على صيغ)

LOOKUP (A24؛ (- 1E + 307؛ 0؛ 1E-307)؛ A27: A29) (انظر ملف المثال)

تم حذف الوسيطة الثالثة [قيمة_إذا_ خطأ]

الوسيطة الثالثة للدالة اختيارية ؛ إذا حذفتها ، ستعيد الدالة FALSE (إذا لم يتم استيفاء الشرط).
\u003d IF (A1\u003e 100؛ "تم تجاوز الميزانية")
إذا كان في زنزانة أ 1 يحتوي على القيمة 1 ، ثم الصيغة أعلاه ستعيد FALSE.

تم إدخال الرقم بدلاً من TRUE أو FALSE في الوسيطة الأولى

لان القيمة FALSE تساوي 0 ، ثم الصيغ
\u003d IF (0، "الميزانية تجاوزت" ؛ "موافق!")
أو (إذا كان في الخلية أ 1 يحتوي على القيمة 0)
\u003d IF (A1، "تم تجاوز الميزانية" ؛ "موافق!")
سيعود موافق!

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

ربط دالة IF () بوظائف أخرى باستخدام الشروط

يحتوي EXCEL أيضًا على وظائف أخرى يمكنك استخدامها لتحليل البيانات باستخدام الشروط. على سبيل المثال ، يتم استخدام الدالة COUNTIF () لحساب عدد تكرارات الأرقام في نطاق من الخلايا ، ويتم استخدام الدالة SUMIF () لإضافة القيم التي تفي بشروط معينة.

يمكن أيضًا استخدام الدالة IF () لحساب وإضافة القيم باستخدام الشروط. يتم إعطاء أمثلة توضيحية أدناه.

دع البيانات تكون في النطاق أ 6: أ 11 (انظر ملف المثال)


لنحسب مجموع القيم الأكبر من 10 باستخدام دالة SUMIF () بالكتابة \u003d SUMIF (A6: A11، "\u003e 10")... يمكن الحصول على نتيجة مماثلة (23) باستخدام
\u003d SUM (IF (A6: A11\u003e 10، A6: A11))
(لإدخال صيغة في خلية بدلاً من أدخل بحاجة للضغط CTRL + SHIFT + ENTER)

الآن دعونا نحسب عدد تكرارات الأعداد الأكبر من 10 في نطاق الخلايا أ 6: أ 11 \u003d COUNTIF (A6: A11، "\u003e 10")... يمكن الحصول على نتيجة مماثلة (2) باستخدام
\u003d COUNT (IF (A6: A11\u003e 10، A6: A11))

الآن بعد أن أصبح المبدأ واضحًا ، يمكن إنشاء صيغ أخرى بشروط باستخدام الدالة IF (). على سبيل المثال ، إيجاد الحد الأدنى للقيمة بين أرقام أكبر من 10:
\u003d MIN (IF (A6: A11\u003e 10، A6: A11))

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

في مثل هذه الحالات ، يوفر Excel عدة خيارات: استخدام اذا كان() داخل آخر اذا كان()، المهام و() و IL و()... سننظر في هذه الأساليب أدناه.

استخدام اذا كان() داخل وظيفة أخرى اذا كان()

لنفكر في متغير بناءً على الوظيفة التي تمت دراستها مسبقًا \u003d IF (A1\u003e 1000؛ "a lot"؛ "little"). ماذا لو احتجت إلى طباعة سلسلة أخرى عندما يكون الرقم في A1 ، على سبيل المثال ، أكبر من 10.000؟ بمعنى آخر ، إذا كانت A1\u003e 1000 صحيحة ، فستحتاج إلى إجراء اختبار آخر ومعرفة ما إذا كانت A1\u003e 10000 صحيحة. يمكنك إنشاء مثل هذا المتغير من خلال تطبيق الوظيفة الثانية اذا كان() داخل الأول كوسيطة ، القيمة _if_true: \u003d IF (A1\u003e 1000؛ IF (A1\u003e 10000؛ "very much"؛ "many")؛ "little").

إذا كانت A1\u003e 1000 صحيحة ، فستبدأ وظيفة أخرى اذا كان()، والتي تُرجع القيمة "كثيرًا" عند A1\u003e 10000. ومع ذلك ، إذا كانت A1 أقل من 10000 أو تساويها ، يتم إرجاع القيمة "كثير". إذا كان الرقم A1 أقل من 1000 أثناء الفحص الأول ، فسيتم عرض القيمة "القليل".

لاحظ أنه يمكنك أيضًا تشغيل الفحص الثاني إذا كان الأول خطأ (أي في الوسيطة value_if_false لوظيفة ifo). إليك مثال صغير يُرجع القيمة "صغيرة جدًا" عندما يكون الرقم في A1 أقل من 100: \u003d IF (A1\u003e 1000؛ "many"؛ IF (A1<100;"очень мало"; "мало")) .

حساب مكافأة المبيعات

من الأمثلة الجيدة على استخدام شيك داخل آخر هو حساب مكافأة المبيعات للموظفين. الذي يعمل في النادي - فندق هيليوبارك ثالاسو ، زفينيجورود. في هذه الحالة ، إذا كانت القيمة X ، فأنت تريد نتيجة واحدة ، إذا كانت Y أخرى ، إذا كانت Z
- الثالث. على سبيل المثال ، في حالة حساب مكافأة للمبيعات الناجحة ، هناك ثلاثة خيارات ممكنة:

  1. البائع لم يصل إلى القيمة المخطط لها ، المكافأة هي 0.
  2. تجاوز البائع القيمة المخطط لها بأقل من 10 ٪ ، المكافأة 1000 روبل.
  3. تجاوز البائع القيمة المخطط لها بأكثر من 10٪ ، المكافأة تساوي 10000 روبل.

هذه هي الصيغة لحساب مثل هذا المثال: \u003d IF (E3\u003e 0؛ IF (E3\u003e 0.1؛ 10000؛ 1000)؛ 0). إذا كانت القيمة في E3 سالبة ، فسيتم إرجاع 0 (بدون مكافأة). إذا كانت النتيجة إيجابية ، يتم التحقق مما إذا كانت أكثر من 10٪ ، وبناءً على ذلك ، يتم إصدار 1،000 أو 10،000. يوضح الشكل 4.17 مثالاً لكيفية عمل الصيغة.

و () وظيفة

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

في Excel ، التعبيرات المنطقية و تتم معالجتها باستخدام الوظيفة و(): AND (boolean_value1 ؛ boolean_value2 ؛ ...). كل وسيطة هي قيمة منطقية يجب اختبارها. يمكنك إدخال العديد من الحجج كما تريد.

دعنا نلاحظ كيف تعمل الوظيفة:

  • إذا كانت جميع التعبيرات تُرجع TRUE (أو أي رقم موجب) ، و() إرجاع TRUE.
  • إذا عرضت وسيطة واحدة أو أكثر FALSE (أو 0) ، و() إرجاع FALSE.

في أغلب الأحيان و() تُطبق داخل دالة اذا كان()... في هذه الحالة ، عندما تكون جميع الحجج في الداخل و() سيعود الدالة TRUE اذا كان() سيتبع قيمة فرعها إذا كانت صحيحة. إذا كان واحد أو أكثر من التعبيرات بتنسيق و() سيعود FALSE ، الوظيفة اذا كان() سيتبع الفرع value_if_false.

إليك مثال صغير: \u003d IF (AND (C2\u003e 0؛ B2\u003e 0)؛ 1000؛ "no bonus"). إذا كانت القيمة في B2 أكبر من الصفر وكانت القيمة في C2 أكبر من الصفر ، فستُرجع الصيغة 1000 ، وإلا فسيتم عرض السطر "لا مكافأة".

فصل القيم إلى فئات

من الاستخدامات المفيدة للدالة و () التصنيف حسب القيمة. على سبيل المثال ، لديك جدول بنتائج الاقتراع أو التصويت ، وتريد تقسيم جميع الأصوات إلى فئات وفقًا للنطاقات العمرية التالية: 18-34.35-49 ، 50-64.65 أو أكثر. بافتراض أن عمر المستفتى موجود في الخلية B9 ، فإن وسيطات الوظيفة التالية و () تجري فحصًا منطقيًا للعمر الذي ينتمي إلى النطاق: \u003d AND (B9\u003e \u003d 18 ؛ B9


إذا كانت إجابة الشخص في الخلية C9 ، فستخرج الصيغة التالية نتيجة تصويت الشخص إذا تم تشغيل الاختيار للامتثال للفئة العمرية 18-34: \u003d IF (AND (B9\u003e \u003d 18؛ B9

  • 35-49: \u003d IF (AND (B9\u003e \u003d 35 ؛ B9
  • 50-64: \u003d إذا (و (B9\u003e \u003d 50 ؛ ب 9
  • 65+: \u003d IF (B9\u003e \u003d 65؛ C9؛ "")

أو () وظيفة

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

يتم فحص هذه الشروط في Excel باستخدام الوظيفة أو(): أو (قيمة منطقية 1 ، منطقية 2 ، ...). كل وسيطة هي قيمة منطقية يجب اختبارها. يمكنك إدخال العديد من الحجج كما تريد. نتيجة العمل أو() يعتمد على الشروط التالية:

  • إذا قامت وسيطة واحدة أو أكثر بإرجاع TRUE (أي رقم موجب) ، أو() إرجاع TRUE.
  • إذا أرجعت جميع الوسائط FALSE (صفر) ، تكون النتيجة أو() سيكون خطأ.

إلى جانب و()، في أغلب الأحيان الوظيفة أو() تستخدم داخل الشيك اذا كان()... في هذه الحالة ، عندما تكون إحدى الحجج في الداخل أو() سيعود الدالة TRUE اذا كان() سيتبع قيمة الفرع الخاص به value_if_true. إذا كانت جميع التعبيرات بتنسيق أو() سيعود FALSE ، وظيفة اذا كان() سيتبع الفرع القيمة_إذا_ خطأ... إليك مثال صغير: \u003d IF (OR (C2\u003e 0؛ B2\u003e 0)؛ 1000؛ "no bonus").

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

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


دلائل الميزات

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

أمثلة التطبيق

تبدو الوظيفة كما يلي: \u003d IF (مهمة ؛ صواب ؛ خطأ). الجزء الأول عبارة عن تعبير منطقي. يمكن أن تظهر على شكل جملة أو رقم. على سبيل المثال ، "10" أو "بدون ضريبة القيمة المضافة". يجب ملء هذه المعلمة. True هي القيمة المعروضة كنتيجة إذا كان التعبير صحيحًا. يمثل False البيانات التي سيتم إرجاعها عندما تكون المهمة غير صحيحة.

تساوي معلمات خليتين

لفهم قدرات وظيفة If بشكل أفضل في Excel ، تحتاج إلى تقديم أمثلة. لذلك ، من الجدير البدء في النظر فيها. تحتاج إلى إدخال القيمة 8 في الخلية C1. بعد ذلك ، في الحقل الموجود على العنوان D1 ، يجب إدخال صيغة النموذج: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

يمكن النظر في مثال آخر. هناك العديد من الطلاب ودرجاتهم التي حصلوا عليها في الاختبار. البيانات هي كما يلي: 5 ، 4 ، وكذلك 3 و 2. وفقًا لشروط المهمة ، من المتصور إنشاء تعليق نصي لكل طالب "نجح" أو "لم ينجح". وبالتالي ، إذا حصل الطالب على الدرجة الثالثة أو أعلى ، فسيتم اعتباره قد اجتاز الاختبار. إذا كانت درجته أقل من 3 ، فهذا يعني أن الطالب قد فشل في الأداء الجيد. لحل مثل هذه المشكلة ، اكتب الصيغة التالية: \u003d IF (C1<3; «не справился»; «сдал»).

يبدأ البرنامج بمقارنة نتائج كل طالب. إذا كان المؤشر أقل من ثلاثة ، فسيظهر النقش "فشل" في الخلية المقابلة. عندما تكون العلامة 3 أو أعلى ، في العمود المطلوب ، يمكنك رؤية تعليق يفيد بأن الطالب لم يجتاز الاختبار. تجدر الإشارة إلى أنه يجب دائمًا وضع التعليقات النصية بين علامات تنصيص. عند استخدام هذه الوظيفة ، يمكنك استخدام عوامل المقارنة التالية:< >, =, >, <, >=, <=.

أمثلة باستخدام الشروط "أو" و "و"

من الضروري مواصلة النظر في الاحتمالات المنطقية لهذا التطبيق. من الممكن دمج الدالة If مع عوامل المقارنة. هذه هي المعلمات التالية:

"أو"؛
"و".

من الضروري الإشارة إلى شرط مهم في Excel: عندما تكون درجة الطالب مساوية أو أقل من 5 ، ولكن أكثر من 3. في هذه الحالة ، يجب عرض تعليق: "ناجح" أو "لا". وبالتالي ، لا ينجح إلا الطلاب الذين حصلوا على خمسة وأربعة. لكتابة هذه المهمة في محرر جداول بيانات ، يجب عليك تطبيق صيغة خاصة. يبدو كالتالي: \u003d IF (AND (A1<=5; A1>3) ؛ "يمر" ، "لا").

إذا كنت تفكر في مثال أكثر تعقيدًا ، فستحتاج إلى استخدام "أو" أو "و". وبالتالي ، يمكنك التعرف على تطبيق صيغة في Excel إذا كانت هناك شروط متعددة في الوظيفة. على سبيل المثال: \u003d IF (OR (A1 \u003d 5؛ A1 \u003d 10)؛ 100؛ 0). في هذه الحالة ، يمكننا أن نستنتج أنه عندما تكون القيمة في الخلية A1 هي 5 أو 10 ، يعرض البرنامج النتيجة 100. وإلا فسيكون 0. من الممكن استخدام هذه العوامل لإيجاد حل لمشاكل أكثر تعقيدًا.

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

في هذه الحالة ، يتم تقديم الصيغة بالشكل التالي: \u003d IF (AND (A1\u003e \u003d 6؛ B1\u003e 10000)؛ "مشكلة العميل"؛ ""). وبالتالي ، إذا تم التعرف على شخص يستوفي الشروط المحددة ، يعرض البرنامج تعليقًا مقابلًا مقابل اسمه. بالنسبة للأعضاء الآخرين في القائمة ، ستبقى هذه الخلية فارغة.

يمكنك أيضًا رؤية مثال عندما يكون الموقف حرجًا. يجب إدخال تعليق مناسب. نتيجة لذلك ، ستبدو الصيغة كما يلي: \u003d IF (OR (A1\u003e \u003d 6؛ B1\u003e 10000)؛ "موقف حرج"؛ ""). ومع ذلك ، إذا اكتشف البرنامج تطابقات لواحد على الأقل من المعلمات ، فسيتم عرض ملاحظة مقابلة.

المهام الصعبة

يتم استخدام وظيفة "If" في Excel لتجنب القسمة المضمنة على صفر أخطاء. بالإضافة إلى ذلك ، يتم استخدامه في العديد من المواقف الأخرى. تم تعيين الحالة الأولى على أنها "DIV / 0". يمكن العثور عليها في كثير من الأحيان. يحدث هذا عادةً عندما تتطلب صيغة "A / B" نسخًا. في نفس الوقت ، فإن المؤشر B في خلايا فردية هو 0. لتجنب مثل هذا الموقف ، يجدر استخدام قدرات المشغل المعني. وبالتالي ، فإن الصيغة المطلوبة هي: \u003d IF (B1 \u003d 0 ؛ 0 ؛ A1 / B1). لذلك ، إذا تم ملء الخلية B1 بقيمة "صفر" ، فسيعرض المحرر "0". خلاف ذلك ، سيقوم البرنامج بتقسيم مؤشر A1 على بيانات B1 وإعطاء النتيجة المطلوبة.

خصم

كما تظهر الممارسة ، غالبًا ما تنشأ المواقف ، والتي تتم مناقشتها أدناه. ستحتاج إلى حساب الخصومات بناءً على المبلغ الإجمالي الذي تم إنفاقه على شراء عنصر معين. المصفوفة المستخدمة هي كما يلي: أقل من 1000 - 0٪ ؛ 1001-3000-3٪ ؛ 3001-5000 - 5٪ ؛ أكثر من 5001-7٪. يمكنك رؤية الموقف عندما يكون لدى Excel قاعدة بيانات للزوار ومعلومات حول المبلغ الذي يتم إنفاقه على الشراء. الخطوة التالية هي حساب الخصم لكل عميل. للقيام بذلك ، تحتاج إلى تطبيق التعبير التالي: \u003d IF (A1\u003e \u003d 5001؛ B1 * 0.93؛ IF (A1\u003e \u003d 3001؛ B1 * 0.95؛ ..).

يتحقق النظام من إجمالي مبلغ الشراء. إذا تجاوزت قيمة 5001 روبل ، يتم ضرب المنتج بنسبة 93 في المائة من سعر العنصر. إذا تم تجاوز علامة 3001 وحدة ، فسيتم إجراء مماثل ، ولكن يتم أخذ 95 ٪ بالفعل في الاعتبار.



نوصي بالقراءة