القدرة على إجراء اختبارات منطقية على الخلايا أداة قوية. ستجد عددًا لا حصر له من الاستخدامات لو() في مستنداتك اليومية تكمن المشكلة في أن عمليات الفحص الضرورية غالبًا ما تتجاوز قدرات وظيفة بسيطة لو() . يحدث هذا عندما تحتاج إلى التحقق من شرطين أو أكثر قبل قبول النتيجة.
في مثل هذه الحالات، يوفر Excel عدة خيارات: الاستخدام لو() داخل آخر لو()، المهام و()وايل و(). وبعد ذلك، سنلقي نظرة على هذه الأساليب.
إستعمال لو()داخل وظيفة أخرى لو()
دعونا نفكر في متغير يعتمد على الدالة التي تمت دراستها مسبقًا =IF(А1>1000;"كثير"، "قليل"). ماذا لو كنت بحاجة إلى طباعة سطر آخر عندما يكون الرقم الموجود في A1، على سبيل المثال، أكبر من 10.000؟ بمعنى آخر، إذا كانت A1>1000 صحيحة، فستحتاج إلى إجراء اختبار آخر ومعرفة ما إذا كانت A1>10000 صحيحة. يمكنك إنشاء مثل هذا الخيار من خلال تطبيق الوظيفة الثانية لو() داخل الأول، كوسيطة، القيمة _if_true: =IF(A1>1000;IF(A1>10000;"a alot", "a alot");"few" .
إذا كان A1>1000 صحيحًا، فقم بتشغيل دالة أخرى لو()، والتي تُرجع "عددًا كبيرًا جدًا" عندما يكون A1> 10000. ومع ذلك، إذا كانت A1 أقل من أو تساوي 10000، فسيتم إرجاع القيمة "كثير". إذا كان الرقم A1 أقل من 1000 عند التحقق الأول، فسيتم عرض القيمة "قليل".
لاحظ أنه يمكنك أيضًا إجراء فحص ثانٍ إذا كان الأول خاطئًا (أي في الوسيطة value_if_false للدالة ifo). فيما يلي مثال صغير يُرجع "عدد قليل جدًا" عندما يكون الرقم الموجود في A1 أقل من 100: =IF(A1>1000,"many",IF(A1<100;"очень мало"; "мало")) .
حساب مكافأة المبيعات
من الأمثلة الجيدة على استخدام شيك واحد ضمن شيك آخر هو حساب مكافأة مبيعات الموظفين. الذي يعمل في فندق Club Hotel Heliopark Thalasso، زفينيجورود. في هذه الحالة، إذا كانت القيمة X، فأنت تريد نتيجة واحدة، إذا كانت Y، وأخرى إذا كانت Z
- ثالث. على سبيل المثال، في حالة حساب مكافأة المبيعات الناجحة، هناك ثلاثة خيارات ممكنة:
- لم يصل البائع إلى القيمة المخططة، والمكافأة هي 0.
- تجاوز البائع القيمة المخططة بأقل من 10٪، والمكافأة هي 1000 روبل.
- تجاوز البائع القيمة المخططة بأكثر من 10٪، والمكافأة هي 10000 روبل.
فيما يلي صيغة حساب هذا المثال: =IF(E3>0,IF(E3>0.1,10000,1000),0) . إذا كانت القيمة في E3 سالبة، فسيتم إرجاع 0 (بدون مكافأة). وفي حالة أن النتيجة إيجابية يتم التأكد ما إذا كانت أكبر من 10%، وبناء على ذلك يتم إصدار 1000 أو 10000. يوضح الشكل 4.17 مثالاً لكيفية عمل الصيغة.
الدالة AND()
في كثير من الأحيان يكون من الضروري اختبار الإخلاص لشرطين فقط في نفس الوقت. على سبيل المثال، يتم دفع المكافأة فقط عندما تتجاوز مبيعات منتج معين الهدف وكذلك يتجاوز إجمالي المبيعات الهدف. إذا لم يتم استيفاء أحد هذه الشروط (أو كلا الشرطين)، لا يتم دفع القسط. في المنطق البولياني، يسمى هذا منطقيًا، حيث يجب أن يكون كلا الشرطين صحيحًا حتى يكون التعبير بأكمله صحيحًا.
التعبيرات المنطقية في Excel وتتم معالجتها باستخدام الوظيفة و(): AND(منطقية1، منطقية2،...). كل وسيطة هي قيمة منطقية للاختبار. يمكنك إدخال أي عدد تريده من الوسائط.
مرة أخرى نلاحظ عمل الدالة:
- إذا كانت كافة التعبيرات ترجع TRUE (أو أي رقم موجب)، و()ترجع صحيحا.
- إذا قامت وسيطة واحدة أو أكثر بإرجاع FALSE (أو 0)، و()إرجاع خطأ.
في كثير من الأحيان و()تطبق داخل وظيفة لو(). في هذه الحالة، عندما تكون جميع الحجج في الداخل و()إرجاع صحيح، وظيفة لو()القيمة سوف تذهب على طول فرعها إذا كان صحيحا. إذا كان واحد أو أكثر من التعبيرات في و()سوف يعود خطأ، وظيفة لو()سوف يتبع فرع value_if_false.
إليك مثال صغير: =IF(AND(C2>0;B2>0);1000;"no Bonus" . إذا كانت القيمة في B2 أكبر من الصفر والقيمة في C2 أكبر من الصفر، فسترجع الصيغة 1000، وإلا فسيتم عرض السلسلة "no Bonus".
تقسيم القيم إلى فئات
الاستخدام المفيد للدالة و () هو التصنيف على أساس القيمة. على سبيل المثال، لديك جدول يحتوي على نتائج استطلاع أو تصويت ما، وتريد تقسيم جميع الأصوات إلى فئات وفقًا للفئات العمرية التالية: 18-34.35-49، 50-64.65 وأكثر. بافتراض أن عمر المستجيب موجود في الخلية B9، تقوم وسيطات الوظيفة التالية و() بإجراء فحص منطقي لمعرفة ما إذا كان العمر ينتمي إلى النطاق: =AND(B9>=18;B9
إذا كانت إجابة الشخص موجودة في الخلية C9، فستخرج الصيغة التالية تصويت الشخص إذا تم تشغيل التحقق من المطابقة الفئة العمرية 18-34: =IF(AND(B9>=18,B9
- 35-49: =IF(AND(B9>=35,B9
- 50-64: =IF(AND(B9>=50,B9
- 65+: =IF(B9>=65,C9"")
وظيفة أو ().
هناك مواقف تحتاج فيها إلى قرار فقط عندما يكون أحد الشروط إيجابيًا (صحيح). على سبيل المثال، قد تقرر دفع مكافأة للموظفين عندما تتجاوز المبيعات الإجمالية الهدف أو عندما تتجاوز مبيعات منتج فردي التوقعات. في المنطق المنطقي، هذا يسمى منطقي أو.
يتم التحقق من هذه الشروط في Excel باستخدام الوظيفة أو(): أو (منطقية 1؛ منطقية 2؛ ...). كل وسيطة هي قيمة منطقية للاختبار. يمكنك إدخال أي عدد تريده من الوسائط. نتيجة العمل أو()يعتمد على الشروط التالية:
- إذا قامت وسيطة واحدة أو أكثر بإرجاع TRUE (أي رقم موجب)، أو()ترجع صحيحا.
- إذا كانت جميع الوسائط ترجع FALSE (صفر)، فستكون نتيجة العملية أو()سيكون خطأ.
إلى جانب و()، الوظيفة في أغلب الأحيان أو()تستخدم داخل الاختيار لو(). في مثل هذه الحالة، عندما تكون إحدى الحجج في الداخل أو()سيعود TRUE، الدالة لو()سوف يتبع فرعه الخاص value_if_true. إذا كانت جميع التعبيرات في أو()إرجاع خطأ، وظيفة لو()سوف تتبع الموضوع value_if_false. إليك مثال صغير: = IF(OR(C2>0;B2>0);1000;"no Bonus") .
في حالة احتواء إحدى الخلايا (C2 أو B2) على رقم موجب، سترجع الدالة 1000. فقط عندما تكون كلتا القيمتين سالبتين (أو تساويان الصفر)، ستعيد الدالة السلسلة "بدون مكافأة".
ستركز المقالة على وظيفة محرر جداول البيانات Excel "If". يتعلق الأمر بالقدرات المنطقية للتطبيق. هذه الوظيفةيمكن أن يعزى إلى الأكثر شيوعا، والذي غالبا ما يستخدم أثناء العمل.
دلائل الميزات
في برنامج اكسلتسمح صيغة "If" بأنواع مختلفة من المهام التي تتطلب منك مقارنة قيم معينة والحصول على النتيجة. يتيح هذا الحل إمكانية استخدام خوارزميات التفرع وإنشاء شجرة قرارات.
أمثلة التطبيق
تبدو الوظيفة كما يلي: =IF(task; true; false). المكون الأول هو تعبير منطقي. يمكن أن تكون بمثابة عبارة أو رقم. على سبيل المثال، "10" أو "بدون ضريبة القيمة المضافة". يجب ملء هذه المعلمة. True هي القيمة المعروضة كنتيجة إذا كان التعبير صحيحًا. الخطأ هو البيانات التي سيتم إرجاعها عندما تكون المهمة خاطئة.
المساواة في معلمات خليتين
لفهم إمكانيات وظيفة "إذا" في Excel بشكل أفضل، فمن الضروري إعطاء أمثلة. ولذلك، فإن الأمر يستحق البدء في النظر فيها أبعد من ذلك. تحتاج إلى إدخال القيمة 8 في الخلية C1، وبعد ذلك، في الحقل الموجود بالعنوان D1، تحتاج إلى إدخال صيغة مثل: =IF(C1)<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
ويمكن النظر في مثال آخر. هناك العديد من الطلاب وعلاماتهم التي حصلوا عليها في الامتحان. البيانات هي كما يلي: 5، 4، وكذلك 3 و 2. وبحسب حالة المهمة، يتم توفير إنشاء تعليق نصي "ناجح" أو "راسب" لكل طالب. وبالتالي، إذا حصل الطالب على علامة ثلاثة أو أكثر، يعتبر ناجحاً في الامتحان. إذا كانت درجته أقل من 3، فإن الطالب لم يتعامل مع المهمة. لحل هذه المشكلة، عليك كتابة الصيغة التالية: =IF(C1<3; «не справился»; «сдал»).
يبدأ البرنامج بمقارنة نتائج كل طالب. إذا كان المؤشر أقل من ثلاثة، فسوف يظهر النقش "فشل" في الخلية المقابلة. عندما تكون العلامة 3 فما فوق، في العمود المطلوب يمكنك رؤية تعليق بأن الطالب لم ينجح في الامتحان. ومن الجدير بالذكر أن التعليقات النصية يجب أن تكون مكتوبة دائمًا بين علامتي الاقتباس. عند استخدام هذه الوظيفة، يمكنك استخدام عوامل المقارنة التالية:< >, =, >, <, >=, <=.
أمثلة على استخدام الشروط "OR" و"AND".
من الضروري مواصلة النظر في الإمكانيات المنطقية لهذا التطبيق. من الممكن توصيل الدالة "If" بعوامل المقارنة. هذه هي الخيارات التالية:
"أو"؛
"و".
من الضروري تحديد شرط مهم في برنامج Excel: عندما تكون درجة الطالب تساوي أو تقل عن 5، ولكن أكبر من 3. في هذه الحالة، يجب عرض التعليق: "ناجح" أو "لا". وبالتالي، لا ينجح إلا الطلاب الذين حصلوا على الخمسات والأربعات. لتسجيل هذه المهمة في محرر جداول البيانات، يجب عليك تطبيق صيغة خاصة. يبدو مثل هذا: =IF(AND(A1<=5; A1>3)؛ "تمرير"، "لا").
إذا نظرنا إلى مثال أكثر تعقيدًا، فسوف تحتاج إلى استخدام "OR" أو "AND". وبالتالي، يمكنك التعرف على تطبيق الصيغة في Excel إذا كان هناك عدة شروط في المهمة. على سبيل المثال: =IF(OR(A1=5; A1=10); 100; 0). في هذه الحالة، يمكنك استنتاج أنه عندما تكون القيمة في الخلية A1 هي 5 أو 10، يعرض البرنامج النتيجة 100. وبخلاف ذلك، تكون 0. ومن الممكن تطبيق عوامل التشغيل هذه لإيجاد حل لمشاكل أكثر تعقيدًا.
على سبيل المثال، في قاعدة البيانات، يلزم حساب المدينين الذين يحتاجون إلى دفع أكثر من 10000 روبل. يمكنك ضبط شرط عدم سداد القرض لأكثر من ستة أشهر، أي ستة أشهر. بفضل وظيفة "إذا" في محرر جداول بيانات Excel، من الممكن الحصول تلقائيًا على علامة "العميل الذي لديه مشكلة" بجوار الأسماء المقابلة. لنفترض أن الخلية A1 تحتوي على بيانات تشير إلى فترة الدين (الأشهر). يحتوي الحقل B1 على المبلغ.
في هذه الحالة، يتم تقديم الصيغة كما يلي: =IF(AND(A1>=6; B1>10000); "problem customer"; ""). وبالتالي، إذا تم التعرف على شخص يستوفي الشروط المحددة، يقوم البرنامج بعرض التعليق المقابل مقابل اسمه. بالنسبة للأعضاء الآخرين في القائمة، ستبقى هذه الخلية فارغة.
يمكنك أيضًا رؤية مثال عندما يكون الموقف حرجًا. يجب إدخال التعليق المناسب. ونتيجة لذلك، ستكون الصيغة كما يلي: =IF(OR(A1>=6; B1>10000); "الموقف الحرج"; ""). ومع ذلك، إذا كان البرنامج يطابق واحدًا على الأقل من المعلمات، فسيتم عرض ملاحظة مقابلة.
المهام الصعبة
يتم استخدام الدالة "If" في Excel لتجنب الأخطاء المضمنة عند القسمة على صفر. بالإضافة إلى ذلك، يتم استخدامه في بعض الحالات الأخرى. يتم تعيين الحالة الأولى كـ "DIV/0". يمكن العثور عليها في كثير من الأحيان. يحدث هذا عادةً عندما يلزم نسخ صيغة "A/B". في الوقت نفسه، فإن المؤشر B في الخلايا الفردية هو 0. لتجنب مثل هذا الموقف، فإن الأمر يستحق استخدام قدرات المشغل المعني. وبالتالي، فإن الصيغة المطلوبة هي كما يلي: =IF(B1=0, 0, A1/B1). لذلك، إذا كانت الخلية B1 مليئة بالقيمة "صفر"، فسيعرض المحرر "0". وإلا سيقوم البرنامج بتقسيم المؤشر A1 على البيانات B1 ويعطي النتيجة المطلوبة.
تخفيض
كما تظهر الممارسة، غالبا ما تنشأ المواقف، والتي سيتم مناقشتها أدناه. سوف تحتاج إلى حساب الخصومات، مع الأخذ بعين الاعتبار المبلغ الإجمالي للأموال التي تنفق على شراء منتج معين. المصفوفة المستخدمة هي كما يلي: أقل من 1000 - 0%؛ 1001-3000 - 3%؛ 3001-5000 - 5%؛ أكثر من 5001 - 7%. يمكنك التعرف على الموقف الذي يوجد فيه في Excel قاعدة بيانات للزائرين ومعلومات حول المبلغ الذي تم إنفاقه على الشراء. والخطوة التالية هي حساب الخصم لكل عميل. للقيام بذلك، تحتاج إلى تطبيق التعبير التالي: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..).
يقوم النظام بالتحقق من المبلغ الإجمالي للمشتريات. إذا تجاوزت قيمة 5001 روبل، يتم ضرب سعر المنتج بنسبة 93 بالمائة. إذا تم تجاوز علامة 3001 وحدة، يحدث إجراء مماثل، ولكن يتم أخذ 95٪ في الاعتبار بالفعل.
الدالة IF() ، النسخة الإنجليزية من IF()، تستخدم عند التحقق من الشروط. على سبيل المثال، =IF(A1>100;"تم تجاوز الميزانية";"موافق!"). اعتمادا على القيمة في الخلية أ1ستكون نتيجة الصيغة إما "تم تجاوز الميزانية" أو "موافق!".
تعد الدالة IF() إحدى الدالات الأكثر استخدامًا.
بناء جملة الوظيفة
إذا كان (التعبير المنطقي، value_if_true، [value_if_false])
Logic_expression- أي قيمة أو تعبير يتم تقييمه إلى TRUE أو FALSE.
=IF(A1>=100;"تم تجاوز الميزانية";"حسنًا!")
أولئك. إذا كان في الخلية أ1
يحتوي على قيمة أكبر من أو تساوي 100، سيتم إرجاع الصيغة نعم!،وإذا لم يكن كذلك، ثم تم تجاوز الميزانية.
يمكن استخدام الصيغ كوسائط دالة، على سبيل المثال:
=IF(A1>100,SUM(B1:B10),SUM(C1:C10))
أولئك. إذا كان في الخلية أ1
يحتوي على قيمة > 100، ثم يتم إجراء الجمع على العمود ب
وإذا كان أقل، فبالعمود مع
.
متداخلة إذا
في EXCEL 2007 كقيم وسيطة value_if_trueو value_if_falseيمكنك استخدام ما يصل إلى 64 دالة IF() متداخلة لإنشاء عمليات تحقق أكثر تعقيدًا.
=IF(A1>=100;"تم تجاوز الميزانية";IF(A1>=90;"مشروع كبير";IF(A1>=50;"مشروع متوسط";"مشروع صغير")))
VIEW(A1;(0;50;90;100);("مشروع صغير";"مشروع متوسط";"مشروع كبير";"تم تجاوز الميزانية"))
فلوكوب(A1;A3:B6;2)
بالنسبة للدالة VLOOKUP()، يجب عليك الإنشاء في النطاق أ3:ب6 جدول القيمة:
إذا كنت تريد الانسحاب نص مختلفإذا كان هناك خلية أ1 قيمة سالبة أو قيمة موجبة أو 0، فيمكنك كتابة الصيغة التالية:
عرض(A1,(-1E+307,0,1E-307),("<0";"=0";">0"})
أو، إذا كنت تريد عرض الصيغ بدلاً من القيم النصية، فيمكنك استخدام الروابط إلى نطاق من الخلايا (تحتوي على صيغ)
عرض(A24,(-1E+307,0,1E-307),A27:A29)(انظر ملف المثال)
حذفت الحجة الثالثة [القيمة_إذا_خطأ]
وسيطة الدالة الثالثة اختيارية، إذا تم حذفها، فسترجع الدالة FALSE (إذا لم يتم استيفاء الشرط).
=IF(A1>100،"تم تجاوز الميزانية")
إذا كان في الخلية أ1
يحتوي على القيمة 1، فإن الصيغة أعلاه ستُرجع FALSE.
بدلاً من TRUE أو FALSE، يتم إدخال رقم في الوسيطة الأولى
لأن القيمة FALSE تعادل 0، ثم الصيغ
=IF(0;"تم تجاوز الميزانية";"موافق!")
أو (إذا كانت الخلية أ1
يحتوي على القيمة 0)
=IF(A1;"تم تجاوز الميزانية";"موافق!")
سيعود نعم!
إذا كان في الخلية أ1 إذا كان هناك أي رقم آخر باستثناء 0، فستعود الصيغة تم تجاوز الميزانية. يعد هذا الأسلوب مفيدًا عند التحقق مما إذا كانت القيمة تساوي الصفر.
ربط الدالة IF() بوظائف أخرى باستخدام الشروط
يحتوي EXCEL أيضًا على وظائف أخرى يمكن استخدامها لتحليل البيانات باستخدام الشروط. على سبيل المثال، لحساب عدد تكرارات الأرقام في نطاق من الخلايا، استخدم الدالة COUNTIF () ولإضافة قيم تستوفي شروطًا معينة، استخدم الدالة SUMIF ().
يمكن أيضًا استخدام الدالة IF() كبديل لحساب القيم وإضافتها باستخدام الشروط. وفيما يلي أمثلة توضيحية.
دع البيانات تكون في النطاق ج6:أ11 (انظر ملف المثال)
لنحسب مجموع القيم الأكبر من 10 باستخدام الدالة SUMIF () كتابة =SUMIF(A6:A11،">10"). ويمكن الحصول على نتيجة مماثلة (23) باستخدام
=SUM(IF(A6:A11>10,A6:A11))
(لإدخال صيغة في خلية بدلاً من يدخلبحاجة للضغط CTRL+SHIFT+إدخال)
الآن دعونا نحسب عدد تكرارات الأرقام الأكبر من 10 في نطاق من الخلايا ج6:أ11
=COUNTIF(A6:A11،">10"). ويمكن الحصول على نتيجة مماثلة (2) باستخدام
=COUNT(IF(A6:A11>10,A6:A11))
الآن وبعد أن أصبح المفهوم واضحًا، يمكنك استخدام الدالة IF() لإنشاء صيغ أخرى ذات شروط. على سبيل المثال، إيجاد القيمة الدنيا بين الأعداد الأكبر من 10:
=MIN(IF(A6:A11>10,A6:A11))