الوظائف الإحصائية في برنامج Excel: برنامج تعليمي مع أمثلة للبحث، الجزء الرابع

يوفر برنامج Excel نطاقًا واسعًا من الوظائف الإحصائية التي تقوم بإجراء العمليات الحسابية بدءًا من المتوسط ​​الأساسي والوسيط والوضع وحتى وظائف البحث.

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

يرجى ملاحظة أنه تم تقديم بعض الوظائف الإحصائية في الإصدارات الحديثة من برنامج Excel وبالتالي فهي غير متوفرة في الإصدارات الأقدم.

وقت القراءة المقدر: 18 دقيقة

وظائف البحث

MAX

وظيفة MAX يتم إدراج برنامج Excel في فئة الوظائف الإحصائية لـ Microsoft Excel. إرجاع أكبر قيمة من قائمة القيم. MAX يرمز إلى الحد الأقصى وعندما تحدد قائمة من القيم فإنه يبحث عن أعلى قيمة فيها ويعيد تلك القيمة في النتيجة.

بناء الجملة

= MAX(number1, [number2], …)

المواضيع

• number1:  رقم، أو خلية تحتوي على رقم، أو نطاق من الخلايا تحتوي على أرقام تريد الحصول على أكبر رقم منها.
• [number2]:  الرقم هو خلية تحتوي على رقم أو نطاق من الخلايا تحتوي على أرقام تريد الحصول على أكبر رقم منها.

مثال

لإتقان وظيفة MAX، نحتاج إلى تجربتها في مثال وفيما يلي أحد الأمثلة التي يمكنك تجربتها:

في المثال التالي، قمنا بإدخال الأرقام مباشرة في الدالة عن طريق الفصل بينها بفاصلة.

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

في المثال التالي، قمنا بالإشارة إلى نطاق وأرجعت النتيجة 1861 كأكبر قيمة. يمكنك أيضًا الرجوع إلى مصفوفة.

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

MAXA

وظيفة الاكسل Maxa إنها تشبه إلى حد كبير وظيفة اكسل Max.

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

وظيفة Max يتجاهل القيم المنطقية والنصية أثناء الوظيفة Maxa تحسب القيمة المنطقية TRUE كما 1، القيمة المنطقية FALSE كـ 0 والقيم النصية كـ 0.

وظيفة MAXA يقوم Excel بإرجاع أكبر قيمة من مجموعة القيم الرقمية المتوفرة، مع حساب النص والقيمة المنطقية FALSE كقيمة 0 وحساب القيمة المنطقية TRUE كقيمة 1.

بناء الجملة

= MAXA(number1, [number2], …)

المواضيع

• number1:  رقم (أو صفائف من القيم الرقمية)، أو خلية تحتوي على رقم، أو نطاق من الخلايا تحتوي على أرقام تريد الحصول على أكبر رقم منها.
• [number2]:  الرقم هو خلية تحتوي على رقم (أو صفائف من القيم الرقمية) أو نطاق من الخلايا تحتوي على أرقام تريد الحصول على أكبر رقم منها.

في الإصدارات الحالية من Excel (Excel 2007 والإصدارات الأحدث)، يمكنك توفير ما يصل إلى 255 وسيطة رقمية للدالة Maxa، ولكن في Excel 2003، يمكن للوظيفة قبول ما يصل إلى 30 وسيطة رقمية فقط.

أمثلة

مثال 1

الخلية B1 من جدول البيانات التالي يوضح الوظيفة Excel Maxa، يستخدم لاسترداد أكبر قيمة من مجموعة القيم في الخلايا A1-A5.

مثال 2

الخلية B1 من جدول البيانات التالي يوضح الوظيفة Excel Maxa، يستخدم لاسترداد أكبر قيمة من مجموعة القيم في الخلايا A1-A3.

لاحظ أن القيمة TRUE في الخلية A1 يتم التعامل مع جدول البيانات كقيمة رقمية 1 بواسطة الدالة Maxa. لذلك، هذه هي القيمة الأكبر في النطاق A1-A3.

مزيد من الأمثلة على الوظيفة Excel Maxa يتم توفيرها على موقع مايكروسوفت أوفيس .

خطأ في الوظيفة MAXA

إذا حصلت على خطأ من الوظيفة Maxa من Excel، فمن المحتمل أن يكون هذا هو الخطأ #VALORE!: يحدث إذا تم توفير القيم مباشرة للدالة Maxa فهي ليست رقمية.

MAXIFS

وظيفة الاكسل Maxifs هي وظيفة بحث تقوم بإرجاع القيمة القصوى من مجموعة فرعية من القيم المحددة بناءً على معيار واحد أو أكثر.

بناء الجملة

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

المواضيع

• max_range:  مصفوفة من القيم الرقمية (أو نطاق من الخلايا تحتوي على قيم رقمية)، والتي تريد إرجاع القيمة القصوى منها إذا تم استيفاء المعايير.
• criteria_range1:  مصفوفة من القيم (أو نطاق من الخلايا التي تحتوي على قيم) للاختبار مقابلها criteria1 .(يجب أن تكون جميع هذه المصفوفة بنفس طول max_range ).
• criteria1: الشرط المراد اختباره فيما يتعلق بالقيم الموجودة criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: مصفوفات اختيارية إضافية من القيم المطلوب اختبارها والشروط المعنية للاختبار.

وظيفة Maxifs يمكنه التعامل مع ما يصل إلى 126 زوجًا من المواضيع criteria_range e criteria.

يمكن أن يكون كل معيار من المعايير المقدمة:

• قيمة رقمية (والتي يمكن أن تكون عددًا صحيحًا أو عشريًا أو تاريخًا أو وقتًا أو قيمة منطقية) (على سبيل المثال 10، 01/01/2017، TRUE)

أو

• سلسلة نصية (على سبيل المثال، "الاسم"، "Mercoleل")

أو

• تعبير (على سبيل المثال ">1"، "<>0").

في criteria المتعلقة بالنص يمكنك استخدام أحرف البدل:

• ؟ لتتناسب مع أي حرف واحد
• * لمطابقة أي تسلسل من الأحرف.

اذا كان criteria عبارة عن سلسلة نصية أو تعبير، ويجب توفيرها للوظيفة Maxifs في اقتباسات.

وظيفة Maxifs وهي ليست حساسة لحالة الأحرف. لذلك، على سبيل المثال، عند مقارنة القيم في criteria_range مع criteria، السلاسل النصية "TEXTواضاف "و"text"سيتم اعتبارها متساوية.

وظيفة Maxifs تم تقديمه لأول مرة في Excel 2019 وبالتالي فهو غير متوفر في الإصدارات السابقة من Excel.

أمثلة

يعرض جدول البيانات أدناه بيانات مبيعات ربع سنوية لثلاثة مندوبي مبيعات.

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

دعونا نلقي نظرة على الأمثلة التالية.

مثال 1

للعثور على الحد الأقصى لرقم المبيعات خلال الربع الأول:

=MAXIFS( D2:D13, A2:A13, 1 )

الذي يعطي النتيجة $ 456.000 .

في هذا المثال، Excel Maxifs يحدد الصفوف التي تكون فيها القيمة في العمود A تساوي 1 وترجع القيمة القصوى من القيم المقابلة في العمود D.

أي أن الدالة تعثر على الحد الأقصى للقيم 223.000 دولار و125.000 دولار و456.000 دولار (من الخلايا D2 وD3 وD4).

مثال 2

مرة أخرى، باستخدام جدول البيانات أعلاه، يمكننا أيضًا استخدام وظيفة Maxifs للعثور على الحد الأقصى لرقم المبيعات لـ "Jeff"، خلال الربعين 3 و 4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

هذه الصيغة ترجع النتيجة $ 310.000  .

في هذا المثال، Excel Maxifs يحدد الخطوط التي:

• القيمة في العمود A أكبر من 2

E

• الإدخال في العمود C يساوي "Jeff"

وإرجاع الحد الأقصى للقيم المقابلة في العمود D.

أي أن هذه الصيغة تعثر على الحد الأقصى للقيمتين 310.000 دولار و261.000 دولار (من الخليتين D8 وD11).

استشر موقع مايكروسوفت أوفيس لمزيد من التفاصيل حول أمثلة وظائف Excel Maxifs.

خطأ في الوظيفة MAXIFS

إذا حصلت على خطأ من وظيفة Excel Maxifs، فمن المحتمل أن يكون واحدًا مما يلي:

#VALUE!: يتحقق مما إذا كانت المصفوفات max_range e criteria_range المتوفرة ليست جميعها بنفس الطول.

@NAME?: يحدث إذا كنت تستخدم إصدارًا أقدم من Excel (ما قبل 2019)، والذي لا يدعم الميزة Maxifs.

MIN

وظيفة MIN هي وظيفة بحث تقوم بإرجاع أقل قيمة من قائمة القيم. MIN يرمز إلى الحد الأدنى وعندما تحدد قائمة من القيم فإنه يبحث عن أقل قيمة فيها ويعيد تلك القيمة في النتيجة.

بناء الجملة

= MIN(number1, [number2], …)

المواضيع

• number1:  رقم، أو خلية تحتوي على رقم، أو نطاق من الخلايا التي تحتوي على أرقام تريد الحصول على أصغر رقم منها.
• [number2]:  رقم، أو خلية تحتوي على رقم، أو نطاق من الخلايا التي تحتوي على أرقام تريد الحصول على أصغر رقم منها.

مثال

في المثال التالي، قمنا بإدخال الأرقام مباشرة في الدالة عن طريق الفصل بينها بفاصلة.

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

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

MINA

وظيفة الاكسل MINA إنها تشبه إلى حد كبير وظيفة اكسل MIN.

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

في هذه الحالة الوظيفة MIN يتجاهل القيم المنطقية والنصية أثناء الوظيفة MINA تحسب القيمة المنطقية TRUE كما 1، القيمة المنطقية FALSE كـ 0 والقيم النصية كـ 0.

وظيفة MINA يقوم Excel بإرجاع أصغر قيمة من مجموعة القيم الرقمية المتوفرة، مع حساب النص والقيمة المنطقية FALSE كقيمة 0 وحساب القيمة المنطقية TRUE كقيمة 1.

بناء الجملة

= MINA( number1, [number2], ... )

المواضيع

• number1:  رقم، أو خلية تحتوي على رقم، أو نطاق من الخلايا (أو صفائف من القيم الرقمية) التي تحتوي على أرقام تريد الحصول على أصغر رقم منها.
• [number2]:  رقم، أو خلية تحتوي على رقم، أو نطاق من الخلايا (أو صفائف من القيم الرقمية) التي تحتوي على أرقام تريد الحصول على أصغر رقم منها.

في الإصدارات الحالية من Excel (Excel 2007 والإصدارات الأحدث)، يمكنك توفير ما يصل إلى 255 وسيطة رقمية للدالة MINA، ولكن في Excel 2003، يمكن للوظيفة قبول ما يصل إلى 30 وسيطة رقمية فقط.

أمثلة

مثال 1

الخلية B1 من جدول البيانات التالي تظهر وظيفة Excel MINA، المستخدمة لاسترداد أصغر قيمة من مجموعة القيم في الخلايا A1-A5.

مثال 2

الخلية B1 من جدول البيانات التالي يظهر وظيفة Excel MINA، يستخدم لاسترداد أصغر قيمة من مجموعة القيم في الخلايا A1-A3.

ضع في اعتبارك أن القيمة TRUE في الخلية A1 يتم التعامل مع جدول البيانات كقيمة رقمية 1 بواسطة الدالة MINA. لذلك، هذه هي أصغر قيمة في النطاق A1-A3.

مزيد من الأمثلة على وظيفة Excel MINA يتم توفيرها على موقع مايكروسوفت أوفيس .

خطأ في الوظيفة MINA

إذا حصلت على خطأ من الوظيفة MINA من Excel، فمن المحتمل أن يكون هذا هو الخطأ #VALORE!. يحدث إذا كانت القيم المقدمة إلى الدالة MINA ليست رقمية.

MINIFS

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

بناء الجملة

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

المواضيع

• min_range:  مصفوفة من القيم الرقمية (أو نطاق من الخلايا تحتوي على قيم رقمية)، والتي تريد إرجاع القيمة القصوى منها إذا تم استيفاء المعايير.
• criteria_range1:  مصفوفة من القيم (أو نطاق من الخلايا التي تحتوي على قيم) للاختبار مقابلها criteria1 .(يجب أن يكون هذا المصفوفة بنفس طول min_range ).
• criteria1: الشرط المراد اختباره فيما يتعلق بالقيم الموجودة criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: مصفوفات اختيارية إضافية من القيم المطلوب اختبارها والشروط المعنية للاختبار.

وظيفة Minifs يمكنه التعامل مع ما يصل إلى 126 زوجًا من المواضيع criteria_range e criteria.

يمكن أن يكون كل معيار من المعايير المقدمة:

• قيمة رقمية (والتي يمكن أن تكون عددًا صحيحًا أو عشريًا أو تاريخًا أو وقتًا أو قيمة منطقية) (على سبيل المثال 10، 01/01/2017، TRUE)

أو

• سلسلة نصية (على سبيل المثال، "الاسم"، "Mercoleل")

أو

• تعبير (على سبيل المثال ">1"، "<>0").

في criteria المتعلقة بالنص يمكنك استخدام أحرف البدل:

• ؟ لتتناسب مع أي حرف واحد
• * لمطابقة أي تسلسل من الأحرف.

اذا كان criteria عبارة عن سلسلة نصية أو تعبير، ويجب توفيرها للوظيفة Minifs في اقتباسات.

وظيفة Minifs وهي ليست حساسة لحالة الأحرف. لذلك، على سبيل المثال، عند مقارنة القيم في criteria_range مع criteria، السلاسل النصية "TEXT"و" النص "سيتم اعتبارهما نفس الشيء.

وظيفة Minifs تم تقديمه لأول مرة في Excel 2019 وبالتالي فهو غير متوفر في الإصدارات السابقة من Excel.

أمثلة

يعرض جدول البيانات أدناه بيانات المبيعات ربع السنوية لثلاثة بائعين.

وظيفة Minifs يمكن استخدامه للعثور على الحد الأدنى لرقم المبيعات لأي ربع أو منطقة أو مندوب مبيعات.

ويظهر ذلك في الأمثلة التالية.

مثال 1

للعثور على الحد الأدنى لرقم المبيعات خلال الربع الأول:

=MINIFS( D2:D13, A2:A13, 1 )

الذي يعطي النتيجة $ 125.000  .

في هذا المثال، Excel Minifs يحدد الصفوف التي تكون فيها القيمة في العمود A تساوي 1 وترجع القيمة الدنيا من القيم المقابلة في العمود D.

أي أن الدالة تعثر على الحد الأدنى من القيم 223.000 دولار و125.000 دولار و456.000 دولار (من الخلايا D2 وD3 وD4).

مثال 2

مرة أخرى، باستخدام جدول البيانات أعلاه، يمكننا أيضًا استخدام الوظيفة Minifs للعثور على الحد الأدنى لمبيعات "جيف" خلال الربعين الثالث والرابع:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

هذه الصيغة ترجع النتيجة $261.000 .

في هذا المثال، Excel Minifs يحدد الخطوط التي:

• القيمة في العمود A أكبر من 2

E

• الإدخال في العمود C يساوي "Jeff"

وإرجاع الحد الأدنى من القيم المقابلة في العمود D.

أي أن هذه الصيغة تعثر على الحد الأدنى للقيمتين 310.000 دولار و261.000 دولار (من الخليتين D8 وD11).

لمزيد من الأمثلة على وظيفة Excel Minifsاستشر موقع مايكروسوفت أوفيس .

خطأ في الوظيفة MINIFS

إذا تلقيت خطأ من وظيفة Excel Minifs، فمن المحتمل أن يكون ذلك لأحد الأسباب التالية:

• #VALORE! -التحقق مما إذا كانت المصفوفات min_range e criteria_range المتوفرة ليست جميعها بنفس الطول.
• #NOME? – يحدث إذا كنت تستخدم إصدارًا أقدم من Excel (ما قبل 2019)، والذي لا يدعم الميزة Minifs.

LARGE

وظيفة الاكسل Large هي دالة بحث تقوم بإرجاع أكبر قيمة k من مجموعة من القيم الرقمية.

بناء الجملة

= LARGE( array, k )

المواضيع

• المصفوفة - مصفوفة من القيم الرقمية للبحث عن أكبر قيمة k.
• K – الفهرس، أي أن الدالة ترجع أكبر قيمة من karray فورنيتو.

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

مثال

يعرض جدول البيانات التالي وظيفة Excel Large، يستخدم لاسترداد القيم الأولى والثانية والثالثة والرابعة والخامسة الأكبر من مجموعة القيم في الخلايا A1-A5.

بعض الأفكار حول مثال جدول البيانات أعلاه:

• في الخلية B1، حيث يتم ضبط k على 1، الدالة Large ينفذ نفس الإجراء كما وظيفة اكسل ماكس ;
• في الخلية B5، عندما يتم ضبط k على 5 (عدد القيم في المصفوفة المتوفرة)، تنفذ الدالة الكبيرة نفس الإجراء مثل وظيفة إكسل مين .

يمكن العثور على مزيد من التفاصيل والأمثلة حول وظيفة Excel Large على موقع مايكروسوفت أوفيس .

خطأ في الوظيفة LARGE

إذا كان إكسل Large بإرجاع خطأ، فمن المحتمل أن يكون واحدًا مما يلي:

• #NUM! – يحدث إذا:
  • القيمة المتوفرة لـ k أقل من 1 أو أكبر من عدد القيم في المصفوفة المتوفرة
    أو
    L 'array المقدمة فارغة.
• #VALUE! - يحدث إذا لم يكن k المقدم رقميًا.

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

ويمكن تحقيق الحل عن طريق تحويل جميع قيم المصفوفة إلى قيم رقمية. 

SMALL

دالة Excel Small هي دالة بحث تقوم بإرجاع أصغر قيمة kth من صفيف من القيم الرقمية.

بناء الجملة

= SMALL( array, k )

المواضيع

• array – مجموعة من القيم الرقمية للبحث عن القيمة الأكبر k.
• K – الفهرس، أي أن الدالة ترجع أكبر قيمة من karray فورنيتو.

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

مثال

يعرض جدول البيانات التالي وظيفة Excel Small، يستخدم لاسترداد القيم الأصغر الأول والثاني والثالث والرابع والخامس من مجموعة القيم في الخلايا A1-A5.

في المثال من الضروري مراعاة ما يلي::

• في الخلية B1، حيث تم تعيين k على 1، الدالة Small ينفذ نفس الإجراء كما وظيفة إكسل مين ;
• في الخلية B5، عندما يتم تعيين k على 5 (عدد القيم في الخليةarray المقدمة) الوظيفة Small ينفذ نفس الإجراء كما دالة ماكس في إكسيل .

مزيد من التفاصيل والأمثلة على وظيفة Excel Small يتم توفيرها على موقع مايكروسوفت أوفيس .

خطأ في الوظيفة SMALL

إذا كان إكسل SMALL بإرجاع خطأ، فمن المحتمل أن يكون واحدًا مما يلي:

• #NUM! – يحدث إذا:
  • القيمة المتوفرة لـ k أقل من 1 أو أكبر من عدد القيم في المصفوفة المتوفرة
    أو
    المصفوفة المتوفرة فارغة.
• #VALUE! - يحدث إذا لم يكن k المقدم رقميًا.

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

يمكن الوصول إلى الحل عن طريق تحويل جميع قيمarray في القيم العددية. 

قراءات ذات صلة

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

Ercole Palmeri