اكسل

كيفية استخدام معايير الصيغة (50 مثالاً)

How Use Formula Criteria

يعد الإنشاء أحد أهم المهارات في بناء الصيغ المفيدة المعايير - جزء الصيغة الذي يقرر ما يجب تضمينه أو استبعاده في عملية حسابية. ومع ذلك ، قد يكون من الصعب بشكل مفاجئ بناء معايير فعالة لأنه يتطلب فهمًا جيدًا لكيفية معالجة Excel للبيانات. إذا كنت قد أمضيت يومًا ما في استكشاف الأخطاء وإصلاحها بصيغة يبدو أنها يجب أن 'تعمل فقط' ، فأنت تعرف ما أعنيه :)



يهدف هذا الدليل إلى مساعدتك في إنشاء الصيغ التي تعمل مع أول زمن.

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





أسماء الوظائف على الخلفيات الداكنة أدناه هي روابط لمزيد من المعلومات.

ماذا تفعل المعايير؟

من بين أمور أخرى ، المعايير:

  • التدفق المنطقي المباشر مع منطق IF / THEN
  • قصر المعالجة على القيم المطابقة فقط
  • إنشاء مجاميع وحسابات شرطية
  • تصفية البيانات لاستبعاد المعلومات غير ذات الصلة
  • تشغيل قواعد التنسيق الشرطي

للمساعدة في تمهيد الطريق ، دعنا نلقي نظرة على ثلاثة أمثلة للمعايير قيد التنفيذ.



مثال 1

في الشاشة أدناه ، يحتوي F3 على هذه الصيغة:

 
= IF (E3>30,'Yes','No')

مثال معايير الصيغة # 1

الترجمة: إذا كانت القيمة في E3 أكبر من 30 ، فقم بإرجاع 'نعم' ، وإلا فقم بإرجاع 'لا'.

هنا ، E3> 30 هي المعايير المستخدمة داخل IF لتحديد ما إذا كان يجب أن تُرجع الصيغة 'نعم' أو 'لا' لكل فاتورة.

المثال رقم 2

في المثال التالي ، تحتوي D3 على هذه الصيغة:

 
= IF ( OR (B3='red',B3='green'),C3*1.1,C3)

مثال معايير الصيغة رقم 2 - زيادة السعر إذا كان أحمر أو أخضر

التفوق التقريبي لأقرب 0.5

الترجمة: إذا كانت B3 إما 'أحمر' أو 'أخضر' ، قم بزيادة السعر بنسبة 10٪. خلاف ذلك ، قم بإرجاع السعر الأصلي.

المثال رقم 3

في هذا المثال ، تُستخدم الدالة SUMIFS لجمع الإجمالي فقط عندما يكون اللون 'أحمر':

 
= SUMIFS (E3:E7,B3:B7,'red')

مثال معايير الصيغة # 2 - SUMIF عندما يكون اللون

الترجمة: مجموع القيم في E3: E7 عندما تكون القيمة في B3: B7 هي 'أحمر'.

أساسيات المعايير

يغطي هذا القسم اللبنات الأساسية لمعايير الصيغة ، وبعض الطرق البسيطة للتحقق من أن المعايير تعمل على النحو المتوقع.

ما هي المعايير؟

المعايير هي تعبيرات منطقية تُرجع TRUE أو FALSE ، أو ما يعادلها من القيم العددية ، 1 أو 0.

هذا كل شيء.

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

العوامل المنطقية

غالبًا ما تستخدم المعايير العوامل المنطقية المدرجة في الجدول أدناه.

المشغل أو العامل المعنى مثال
= يساوي = A1 = 10
لا يساوي = A110
> أكثر من = A1> 100
< أقل من = A1<100
> = أكبر من أو يساوي = A1> = 75
<= اقل او يساوي = A1<=0

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

وظائف منطقية

يحتوي Excel على العديد من ما يسمى بـ 'الوظائف المنطقية' التي يمكن استخدامها لبناء الشروط واستخدامها. يسرد الجدول أدناه الوظائف المنطقية الرئيسية.

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

معايير متعددة

بطبيعة الحال ، هناك العديد من الحالات التي تريد فيها استخدام معايير متعددة. في المواقف البسيطة ، يمكنك استخدام الدالات AND و OR و NOT. وفيما يلي بعض الأمثلة على ذلك:

 
= AND (A1>0,A1<10) // greater than 0 and less than 10 = OR (A1='red',A1='blue') // red or blue = NOT ( OR (A1='red',A1='blue')) // not red or blue = AND ( ISNUMBER (A1),A1>100) // number greater than 100

البدل

يوفر Excel ثلاثة 'أحرف بدل' لمطابقة النص في الصيغ:

اختلاف الشخصيات اسم غرض
* النجمة تطابق صفر أو أكثر من الأحرف
؟ علامة استفهام تطابق أي شخصية واحدة
~ علامة التوكيد تطابق حرف البدل الحرفي

يمكن استخدام أحرف البدل بمفردها أو مجتمعة للحصول على مجموعة متنوعة من سلوكيات المطابقة:

إستعمال سلوك سيطابق
؟ أي شخصية واحدة 'أ' ، 'ب' ، 'ج' ، 'ع' ، إلخ.
؟؟ أي حرفين 'AA' و 'AZ' و 'zz' وما إلى ذلك.
؟؟؟ أي ثلاثة أحرف 'Jet' و 'AAA' و 'ccc' وما إلى ذلك.
* أي شخصيات 'apple' ، 'APPLE' ، 'A100' ، إلخ.
ينتهي بـ 'th' 'الحمام' ، 'الرابع' ، إلخ.
ج * يبدأ بحرف 'c' 'Cat' و 'CAB' و 'cindy' و 'candy' وما إلى ذلك.
؟ * شخصية واحدة على الأقل 'a' ، 'b' ، 'ab' ، 'ABCD' ، إلخ.
؟؟؟ - ؟؟ 5 أحرف مع واصلة 'ABC-99' ، '100-ZT' ، إلخ.
* ~؟ ينتهي بعلامة الاستفهام 'مرحبا؟' ، 'أي شخص في المنزل؟' ، إلخ.
* س ص * يحتوي على 'xyz' 'الكود هو XYZ' ، '100-XYZ' ، 'XyZ90' ، إلخ.

فيما يلي بعض الأمثلة على استخدام أحرف البدل للمعايير في دالة COUNTIFS.

 
= COUNTIFS (A1:A100,'*red*') // count cells that contain 'red' = COUNTIFS (A1:A100, 'www*') // count cells starting with 'www' = COUNTIFS (A1:A100,'?????') // count cells with 5 characters

لا تسمح كل الوظائف بأحرف البدل. فيما يلي قائمة بالوظائف الشائعة التي تعمل:

لاحظ أن الدالة IF هي ليس في هذه القائمة. للحصول على سلوك أحرف البدل مع IF ، يمكنك دمج دالتي SEARCH و ISNUMBER ، كما هو موضح أدناه.

معايير الاختبار

الطريقة الكلاسيكية لاختبار المعايير هي لفها في دالة IF. على سبيل المثال ، للتحقق من وجود 'أحمر' أو 'أزرق' ، يمكننا التفاف الدالة 'أو' داخل IF مثل هذا:

 
= IF ( OR (B3='red',B3='blue'),'OK', '')

معايير الصيغة - الاختبار باستخدام دالة IF

الترجمة: إذا كان اللون 'أحمر' أو 'أزرق' ، فارجع إلى 'موافق'. خلاف ذلك لا تعود شيئا.

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

 
=B3>=80

معايير الصيغة - الاختبار مباشرة على ورقة العمل

الترجمة: القيمة في B3 أكبر من أو تساوي 80.

بدون IF أو دالة أخرى ، نحصل فقط على نتيجة TRUE أو FALSE ، لكن يكفي التحقق من أن المعايير تعمل كما هو متوقع.

لا تتجاهل علامة يساوي (=) عند اختبار المعايير كصيغة. يجب أن تبدأ جميع صيغ Excel بعلامة يساوي ، لذا يجب تضمينها. قم بإزالة علامة التساوي عند نقل المعايير إلى صيغة أخرى.

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

فيديو: كيفية استخدام F9 لتصحيح أخطاء صيغة .

إضافة معايير إلى الصيغ

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

في المثال أدناه ، تحتوي الصيغة C3 على هذه الصيغة ، والتي تستخدم المعايير أعلاه كاختبار منطقي داخل IF:

 
= IF (B3>=80,'Pass','Fail')

إضافة معايير إلى صيغة

الترجمة: إذا كانت القيمة في B3 أكبر من أو تساوي 80 ، فقم بإرجاع 'Pass'. وإلا ، فقم بإرجاع 'فشل'.

أنظر أيضا: 23 تلميحات عن الصيغ ( فيديو | مقالة - سلعة )

أمثلة المعايير

يعرض هذا القسم أمثلة على كيفية بناء معايير لإنجاز مجموعة متنوعة من المهام لأنواع مختلفة من المحتوى.

فارغ أم لا

هناك عدة طرق يمكنك من خلالها التحقق من الخلايا الفارغة أو غير الفارغة. لإرجاع TRUE إذا كان A1 فارغًا ، يمكنك استخدام إما:

 
= ISBLANK (A1) =A1=''

لعكس المنطق والتحقق من الخلايا غير الفارغة ، يمكنك استخدام:

 
= NOT ( ISBLANK (A1)) =A1''

هناك طريقة أخرى لاختبار خلية فارغة وهي التحقق من عدد الأحرف:

 
= LEN (A1)=0

إذا كان العدد صفرًا ، تكون الخلية 'فارغة'. هذه الصيغة مفيدة عند اختبار الخلايا التي قد تحتوي على صيغ تُرجع سلاسل فارغة (''). ستقوم ISBLANK (A1) بإرجاع FALSE إذا قامت الصيغة بإرجاع سلسلة فارغة في A1 ، ولكن LEN (A1) = 0 سترجع TRUE.

معايير النص

لإرجاع القيمة TRUE إذا كانت الخلية تحتوي على 'أحمر' ، يمكنك استخدام:

 
=A1='red' 

لعكس المنطق ، يمكنك استخدام الدالة NOT أو لا يساوي عامل التشغيل () على النحو التالي:

 
= NOT (A1='red') =A1'red'

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

معايير الأرقام

لاختبار ما إذا كان A1 يساوي 5 ، يمكنك استخدام معايير مثل هذه:

 
=A1=5 // TRUE if A1 equals 5

فيما يلي بعض الأمثلة الأخرى للمعايير لاختبار القيم الرقمية:

 
=A1<100 // less than 100 =A1>=1 // greater than or equal to 0 =A10 // not equal to zero = AND (A1>0,A1<5) // greater than zero, less than 5 = MOD (A1,3)=0 // value is a multiple of 3

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

معايير التواريخ

التواريخ في Excel هي مجرد أرقام ، مما يعني أنك حر في استخدام العمليات الحسابية العادية في التواريخ إذا أردت. مع تواريخ الطلب في العمود A وتواريخ التسليم في العمود B ، ستحدد هذه الصيغة في العمود C أوقات التسليم التي تزيد عن 3 أيام على أنها 'متأخر':

 
= IF ((B2-A2)>3,'Late','')

يوفر Excel أيضًا عددًا كبيرًا من الوظائف المحددة للعمل مع التواريخ. على سبيل المثال ، للتحقق مما إذا كان التاريخ 'في المستقبل' ، يمكنك استخدام وظيفة TODAY مثل هذا:

 
=A1> TODAY ()

مثال تاريخ معايير الصيغة - أكبر من اليوم

للتحقق مما إذا كان التاريخ سيحدث في الثلاثين يومًا القادمة ، يمكن تمديد الصيغة إلى:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

الترجمة: إذا كانت A2 أكبر من اليوم وأقل من أو تساوي اليوم + 30 يومًا ، فقم بإرجاع TRUE.

كيفية تربيع الجذر في Excel

فيما يلي بعض الأمثلة الأخرى لمعايير التواريخ ، بافتراض أن A1 تحتوي على تاريخ صالح:

 
= DAY (A1)>15 // greater than 15th = MONTH (A1)=6 // month is June = YEAR (A1) = 2019 // year is 2019 = WEEKDAY (A1)=2 // date is a Monday

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

 
=A1> DATE (2019,1,1) // after Jan. 1, 2019 = AND (A1>= DATE (2018,6,1),B4<= DATE (2018,8,31)) // Jun-Aug 2018

معايير الأوقات

الأوقات عبارة عن أرقام كسرية في Excel ، لذا يمكنك استخدام الرياضيات البسيطة للوقت في بعض الحالات. على سبيل المثال ، للتحقق مما إذا كان الوقت في A1 بعد الساعة 12:00 ظهرًا (أكثر من 12 ساعة) ، يمكنك استخدام:

 
=A1>.5

يعمل هذا لأن 1 يوم = 24 ساعة ، لذلك نصف يوم = 12 ساعة.

لمزيد من العمل الدقيق ، يحتوي Excel على وظائف خاصة لاستخراج الوقت حسب المكون. على سبيل المثال ، الساعة 8:45 صباحًا في الخلية A1:

 
= HOUR (A1) // returns 8 = MINUTE (A1) // returns 45 = SECOND (A1) // returns 0

الطريقة الأكثر أمانًا لإدراج الوقت في المعايير هي استخدام وظيفة الوقت. وهنا بعض الأمثلة:

 
=A1> TIME (9,15,0) // after 9:15 AM = AND (A1>= TIME (9,0,0),A1<= TIME (17,0,0)) // 9 AM to 5 PM

معايير SUMIFS و COUNTIFS وما إلى ذلك.

تتبع معايير SUMIFS و COUNTIFS و AVERAGEIFS والوظائف المماثلة القائمة على النطاق قواعد مختلفة قليلاً. هذا بسبب تقسيم المعايير إلى جزأين (نطاق المعايير والمعايير) ، وهذا يؤثر على بناء الجملة عندما تتضمن المعايير عوامل التشغيل.

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

 
= COUNTIFS (A1:A100,10) // count cells equal to 10 = COUNTIFS (A1:A100,'red') // count cells that equal 'red'

ومع ذلك ، تتغير الأشياء عندما نضيف عوامل التشغيل:

 
= COUNTIFS (A1:A100,'>10') // count cells greater than 10 = COUNTIFS (A1:A100,'<0') // count cells less than zero

لاحظ الاقتباسات (') حول المعايير؟ هذه مطلوبة عندما تتضمن المعايير عاملًا في هذه الوظائف.

معايير لأنواع البيانات

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

يوفر Excel ثلاث وظائف يمكنك استخدامها للتحقق من أنواع البيانات: ISTEXT و ISNUMBER و ISLOGICAL. ترجع هذه الدالات TRUE أو FALSE. في الشاشة أدناه ، تحتوي الخلايا D3 و F3 و H3 على هذه الصيغ ، التي تم نسخها لأسفل:

 
= ISTEXT (B3) = ISNUMBER (B3) = ISLOGICAL (B3)

معايير الصيغة - استخدام الدوال لاختبار أنواع البيانات

لاستخدام هذه الوظائف كمعايير ، ما عليك سوى وضعها في المكان الصحيح للصيغة. على سبيل المثال ، للتحقق مما إذا كان A1 يحتوي على رقم ، يمكنك استخدام ISNUMBER كاختبار منطقي داخل IF مثل هذا:

 
= IF ( ISNUMBER (B3),'OK','Invalid')

ملاحظة: الصيغ ليست نوع بيانات ، ولكن يمكنك التحقق من الصيغ بامتداد دالة ISFORMULA :

 
= ISFORMULA (A1) // TRUE if A1 contains formula

يتوهم

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

جعل المعايير متغيرة

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

 
= IF (B3>=$E,'Pass','Fail')

جعل المعايير متغيرة - مثال على درجة الاختبار

يؤدي وضع درجة النجاح في الخلية E3 إلى تسهيل التغيير في أي وقت دون تحرير الصيغ. لاحظ أن الإشارة إلى $ E $ 3 هي مطلق لمنع التغييرات حيث يتم نسخ الصيغة لأسفل.

جعل المعايير متغيرة في COUNTIFS ، SUMIFS ، إلخ.

كما كان من قبل ، إذا كانت المعايير تختبر من أجل المساواة ، فلا حاجة إلى معالجة خاصة:

 
= COUNTIF (range,A1) // count cells equal to A1

ومع ذلك ، إذا كانت المعايير تتضمن عوامل تشغيل ، فستحتاج إلى استخدام سلسلة . على سبيل المثال ، لحساب عدد الخلايا أكثر من ج 1 ، ستحتاج إلى الانضمام '>' إلى 'A1' على النحو التالي:

 
= COUNTIF (range,'>'&A1)

يتم تشغيل التسلسل أولاً. إذا احتوى A1 على الرقم 10 ، فهذه هي الصيغة بعد التسلسل:

 
= COUNTIF (range,'>10')

لاحظ أن النمط هو نفسه كما تم توضيحه سابقًا - إذا كانت المعايير تتضمن عوامل تشغيل ، فيجب أن تظهر بين علامتي اقتباس ('').

فيما يلي المزيد من الأمثلة على استخدام التسلسل في المعايير:

 
= COUNTIF (range,'<'&B1) // count less than value in B1 = COUNTIF (range,''&'') // count not blank cells = COUNTIF (range,'*'&B1&'*') // count contains text in B1 = COUNTIF (range,'>'& TODAY ()) // count dates in future = COUNTIF (range,'<'& TODAY ()+7) // count up to 7 days from today

يحتوي على نص محدد

أحد المواقف الصعبة هو عندما تريد اختبار ما إذا كانت خلية يحتوي على نص محدد. بالنسبة للوظائف التي تدعم أحرف البدل (مثل COUNTIFS و SUMIFS وما إلى ذلك) ، يمكنك استخدام أحرف البدل للقيام بذلك. على سبيل المثال ، لحساب عدد الخلايا التي تحتوي على 'أحمر' في أي مكان في خلية بها COUNTIFS ، يمكنك استخدام علامة النجمة مثل هذا:

 
= COUNTIFS (A1:A100,'*red*')

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

 
= ISNUMBER ( SEARCH (C3,B3))

معايير الصيغة - الخلية تحتوي على نص محدد

يمكنك استخدام هذا التعبير كمعايير داخل IF مثل هذا

 
= IF ( ISNUMBER ( SEARCH ('red',A1)),'red', '')

الترجمة: إذا تم العثور على 'أحمر' في أي مكان في A1 ، فقم بإرجاع 'أحمر'.

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

IFs متداخلة

غالبًا ما تُستخدم صيغ IF المتداخلة للتحقق من معايير متعددة وإرجاع نتائج متعددة. بشكل عام ، يتمثل التحدي في إنشاء IFs متداخلة بحيث تعمل المعايير في التسلسل الصحيح. على سبيل المثال ، فيما يلي صيغة IF متداخلة تقوم بتعيين تقدير بالأحرف بناءً على درجة رقمية:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

مثال IF متداخل لتعيين التقديرات

لاحظ أننا نقوم باختبار الدرجات المنخفضة أولاً ، ثم أعلى الدرجات تدريجياً.

أكثر: 19 نصيحة حول IFs المتداخلة (مع البدائل)

ثوابت الصفيف في المعايير

ثوابت المصفوفات عبارة عن مصفوفات ذات تشفير ثابت ذات قيم ثابتة مثل: {'A'، 'B'، 'C'}. يمكن استخدامها أحيانًا كمعايير لإنشاء معايير منطقية أو بسيطة. على سبيل المثال ، في الشاشة أدناه ، تحتوي الخلية F4 على هذه الصيغة:

 
= SUM ( SUMIFS (C3:C7,B3:B7,{'red','gold'}))

معايير الصيغة مع ثوابت الصفيف

الترجمة: مبيعات SUM حيث يكون اللون 'أحمر' أو 'ذهبي'.

نظرًا لأننا نعطي SUMIFS قيمتين للمعايير ، فإنه يُرجع نتيجتين. ثم تقوم الدالة SUM بإرجاع مجموع النتيجتين.

معايير صيغة الصفيف البسيطة

تعد صيغ الصفيف موضوعًا معقدًا ، ولكن معايير صيغ الصفيف البسيطة يمكن أن تكون بسيطة للغاية. المثال الكلاسيكي هو استخدام الدالة IF 'لتصفية' القيم التي يجب استبعادها ، ومعالجة النتيجة بوظيفة أخرى.

في الشاشة أدناه ، الصيغة في G4 هي:

 
{= MAX ( IF (regions=F4,totals))}

حيث 'المناطق' هو نطاق مسمى B3: B8 و 'إجماليات' هو النطاق المسمى D3: D8.

ملاحظة: هذه صيغة مصفوفة ويجب إدخالها باستخدام control + shift + enter.

النتيجة هي أعلى قيمة لكل منطقة.

معايير الصيغة لصيغة الصفيف البسيطة

بالنسبة للمعايير ، نستخدم التعبير:

 
regions=F4

يقارن هذا جميع قيم المنطقة بـ 'الغرب' من F4 ، ويعيد نتيجة الصفيف التالية في الاختبار المنطقي لـ IF:

 
{TRUEFALSETRUEFALSETRUEFALSE}

تبدو المصفوفة النهائية التي تم إرجاعها بواسطة IF كما يلي:

 
{10500FALSE12500FALSE11800FALSE}

فقط القيم المرتبطة بمنطقة 'الغرب' هي التي تدخل في المصفوفة. القيم المرتبطة بالمنطقة 'الشرقية' خاطئة.

تقوم الدالة MAX عندئذٍ بإرجاع أكبر قيمة في الصفيف ، متجاهلة جميع القيم FALSE.

معايير الصيغة المتقدمة

فيما يلي ارتباطات لأمثلة معايير الصيغة الأكثر تقدمًا. يحتوي كل رابط على لقطة شاشة وشرح كامل.

المزيد من موارد الصيغة

تحتوي الروابط التالية على معلومات أكثر تفصيلاً حول صيغ Excel:

المؤلف ديف برونز


^