الدالات المنطقية في Excel. كل شيء عن تطبيق الوظائف المنطقية في Excel

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

وهي مصممة للتحقق مما إذا كانت القيم تفي بالمعايير المحددة. إذا كان هناك مثل هذا التطابق ، في الخلية التي تمت كتابتها ، يتم إدخال القيمة "TRUE" ، في حالة وجود تناقض - "FALSE". سننظر اليوم بمزيد من التفصيل في قضايا مثل هيكل الوظائف المنطقية ، ونطاق استخدامها.

قائمة الوظائف المنطقية في Excel

يوجد عدد كبير من الوظائف المنطقية ، ولكن الأكثر شيوعًا هي ما يلي:

1. الحقيقة
2. يكذب أو ملقاه
3. IF
4. خطأ مرجّح
5. OR
6. И
7. لا
8. يوشيبكا
9. إيسبلانك

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

عوامل التشغيل TRUE و FALSE

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

لا عامل

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

بناء جملة هذا المشغل كما يلي: = NOT (صح أم خطأ).

عوامل التشغيل AND و OR

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

طريقة إنشاء عامل التشغيل AND بسيطة للغاية: = و (الوسيطة 1 ؛ الوسيطة 2 ؛ ...). الحد الأقصى لعدد الوسائط التي يمكن استخدامها بواسطة هذه الوظيفة هو 255. بناء جملة المشغل OR متشابهة ، لكن آليات العمل مختلفة قليلاً. إذا كانت إحدى قوائم الوظائف تنتج نتيجة الحقيقة، ثم سيتم إرجاع هذا الرقم كتسلسل منطقي كامل.

عبارات IF و ISERROR

هاتان الوظيفتان لهما غرض مهم للغاية - فهما يحددان بشكل مباشر معيار الامتثال الذي يجب التحقق من تعبير معين به. لفهم أعمق لكيفية عمل المشغل خطأ مرجّح، يجب أن تصف الوظيفة أولاً IF. هيكلها العام أكثر تعقيدًا بقليل من الهياكل السابقة: = IF (التعبير المنطقي ، value_if_true ، value_if_false).

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

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

هيكل المشغل خطأ مرجّح متشابهة إلى حد ما ، لكنها لا تزال مختلفة إلى حد ما. يحتوي على وسيطين مطلوبين:

1. المعنى. إنه التعبير نفسه الذي يتم اختباره. إذا اتضح أنها صحيحة ، فسيتم إرجاع تلك القيمة.
2. القيمة إذا كان هناك خطأ. هذا هو النص أو الرقم أو الوظيفة التي سيتم عرضها أو تنفيذها إذا كانت نتيجة التحقق من الوسيطة الأولى FALSE.

بناء الجملة: = IFERROR (القيمة ؛ value_if_error).

مشغلي ISERROW و ISEMPLAND

تحتوي الوظيفة الأولى لما سبق على قيمة واحدة فقط ولها الصيغة التالية: = ISERROR (القيمة). تتمثل مهمة هذا المشغل في التحقق من مدى جودة تعبئة الخلايا (واحدة أو في النطاق بأكمله). إذا اتضح أن المساحة المتروكة كانت خاطئة ، فإنها تُرجع النتيجة الحقيقية. إذا كان كل شيء على ما يرام - خطأ. يمكن تطبيقه مباشرة كمعيار لوظيفة أخرى.

يمكن لبرنامج Excel التحقق من الروابط بحثًا عن أنواع الأخطاء التالية:

• #اسم؟؛
• # N / A ؛
• # DEL / 0!
• #رقم!؛
• #لذا؛
• #فارغة!؛
• #حلقة الوصل!.

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

جدول المذكرة "الوظائف المنطقية في Excel"

لتلخيص كل ما هو موصوف أعلاه ، دعنا نعطي جدولًا صغيرًا يحتوي على معلومات حول جميع وظائف المنطق الشائعة الاستخدام.

وظائف المنطق وأمثلة لحل المشكلات

تجعل الوظائف المنطقية من الممكن حل مجموعة متنوعة من المهام ، بما في ذلك المهام المعقدة. دعنا نعطي بعض الأمثلة عن كيفية عملهم في الممارسة العملية.

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

من أجل حل المهمة الموصوفة بنجاح ، تحتاج إلى استخدام الوظيفة التالية.

يمكنك رؤيته في شريط الصيغة في لقطة الشاشة. الآن دعونا نقدم بعض التوضيحات. يعني التعبير المنطقي الذي تم عرضه في لقطة الشاشة (أي C2> = 8) أن المنتج يجب أن يكون في المخزن لمدة تصل إلى 8 أشهر شاملة. باستخدام عوامل التشغيل الحسابية> = ، نحدد القاعدة أكبر من أو تساوي. بعد كتابة هذا الشرط ، ستُرجع الدالة إحدى القيمتين: "TRUE" أو "FALSE". إذا كانت الصيغة تفي بالمعيار ، فستتم كتابة القيمة بعد إعادة التقييم إلى الخلية (حسنًا ، أو تم تمريرها كوسيطة إلى دالة أخرى ، كل هذا يتوقف على المعلمات التي حددها المستخدم) ، مقسومة على اثنين (لهذا ، قمنا بتقسيم السعر وقت الاستلام في المستودع بمقدار اثنين). إذا تبين بعد ذلك أن المنتج كان في المخزن لمدة تقل عن 8 أشهر ، فسيتم إرجاع نفس القيمة الموجودة في الخلية.

الآن دعونا نجعل المهمة أكثر صعوبة. نطبق الشرط: يجب أن يكون مقياس الخصومات تصاعديًا. ببساطة ، إذا كانت البضاعة أكثر من 5 أشهر ، ولكن أقل من 8 ، يجب تقسيم السعر على مرة ونصف. إذا كان أكثر من 8 ، اثنان. لكي تتطابق هذه الصيغة مع القيمة ، يجب أن تكون على النحو التالي. انظر إلى لقطة الشاشة في شريط الصيغة لرؤيتها.

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

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

دعونا نحللها. استخدمنا الوظيفة كمعيار OR. هناك حاجة لتوفير مثل هذا الشوكة. إذا كانت الخلية D2 تحتوي على الرقم 10 ، فسيتم عرض القيمة "محذوفة" تلقائيًا في السطر المقابل من العمود E. وينطبق الشيء نفسه على الشرط الآخر. إذا لم يتم استيفاء أي منها ، فسيتم إرجاع خلية فارغة ببساطة.

المهمة 3. لنفترض أن لدينا عينة من الطلاب يحاولون الالتحاق بالمدرسة الثانوية. للقيام بذلك ، يحتاجون إلى اجتياز الاختبارات في عدة مواضيع ، كما هو موضح في لقطة الشاشة أدناه. لكي يتم اعتبارهم مؤهلين للقبول في هذه المؤسسة التعليمية ، يجب أن يسجلوا ما مجموعه 12 نقطة. في الوقت نفسه ، هناك شرط مهم وهو ألا تقل الدرجة في الرياضيات عن 4 نقاط. وتتمثل المهمة في أتمتة معالجة هذه البيانات ، بالإضافة إلى تجميع تقرير عن الطلاب الذين تم إدخالهم والذين لم يفعلوا ذلك. للقيام بذلك ، سنقوم بعمل مثل هذا الجدول.

لذا ، فإن مهمتنا هي جعل البرنامج يحسب عدد النقاط في المجموع ، وإلقاء نظرة على النتيجة الناجحة وإجراء مقارنة. بعد هذه العمليات ، يجب أن تضع الوظيفة النتيجة في الخلية التي تناسبها. هناك خياران محتملان: "مقبول" أو "لا". لتنفيذ هذه المهمة ، أدخل صيغة مماثلة (فقط أدخل القيم الخاصة بك): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

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

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

يوصى بشدة بالتدرب على مخطط الاختبار قبل استخدام هذه المهارات في العمل الحقيقي. سيساعد هذا في توفير الكثير من الوقت.

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

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

المهمة 5. لنفترض أننا بحاجة إلى حساب متوسط ​​مبيعات المنتجات المختلفة في متاجر مختلفة من نفس العلامة التجارية. دعونا نصنع مثل هذا الجدول.

مهمتنا هي تحديد المتوسط ​​لجميع القيم ، والتي تناسب خصائص معينة. للقيام بذلك ، نستخدم وظيفة خاصة لم تكن موجودة في القائمة أعلاه. يسمح لك بدمج وظيفتين متوسط и إذا. واتصلت قلب. يحتوي على ثلاث حجج:

1. النطاق المطلوب التحقق منه.
2. الحالة المراد فحصها.
3. متوسط ​​المدى.

نتيجة لذلك ، يتم الحصول على الصيغة التالية (في لقطة الشاشة).

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