كيف تحسب المئين أو الربع مع تجاهل القيم الصفرية بسرعة في Excel؟

عند استخدام دالتي **PERCENTILE** أو **QUARTILE** في Excel، يواجه المستخدمون غالبًا مواقف تحتوي فيها مجموعة البيانات على قيم صفرية. وبشكل افتراضي، تُضمّن هاتان الدالتان القيم الصفرية في الحسابات، ما قد يؤثر بشكل كبير على النتائج من خلال خفض قيم المئين أو الربع—خاصةً إذا لم يُمثّل الصفر بيانات ذات معنى في السياق المعني. ولإجراء تحليل إحصائي أكثر دقة، قد ترغب في استبعاد القيم الصفرية تمامًا عند حساب المئين أو الربع. يقدم لك هذا البرنامج التعليمي عدة تقنيات عملية لمعالجة هذه المشكلة بكفاءة في Excel، بما في ذلك صيغ Excel الأصلية وحلول VBA، إضافةً إلى مناقشة السيناريوهات المناسبة لكل طريقة لمساعدتك على اختيار الأنسب لاحتياجاتك.

المئين أو الربع مع تجاهل الأصفار

المئين مع تجاهل الأصفار (صيغة صفيف)

لحساب المئين مع تجاهل الأصفار، استخدم صيغة صفيف تقيّم فقط القيم الأكبر من الصفر.

اختر خلية فارغة تريد عرض النتيجة فيها وأدخل الصيغة التالية:

=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)

بعد كتابة الصيغة، اضغط علىCtrl + Shift + Enter (وليس مفتاح Enter وحده)، لأنها صيغة صفيف. وسيقوم Excel تلقائيًا بإحاطة الصيغة بأقواس معقوفة{ }، مما يدل على أنها أُدخلت بشكل صحيح. وفي هذه الصيغة:

• A1:A13 هو نطاق البيانات الخاص بك — عدّله حسب احتياجات ورقتك.
• 0.3 يُحدّد المئين^30th. يمكنك تغيير هذه القيمة إلى أي مئين ترغب في حسابه (مثلًا، 0.75 للمئين)^75th).

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

انتبه: الضغط على مفتاح Enter وحده لن يعمل بشكل صحيح؛ بل يجب عليك استخدامCtrl + Shift + Enter. كما أن الصيغ التي تتضمّنIF(...) داخل دوال التجميع قد تكون أقل كفاءة عند التعامل مع مجموعات البيانات الكبيرة.

الربع مع تجاهل الأصفار (صيغة صفيف)

هذا النهج مشابه للرباعيات. اختر خلية لعرض النتيجة وأدخل:

=QUARTILE(IF(A1:A18>0,A1:A18),1)

بعد إدخال الصيغة، اضغط علىCtrl + Shift + Enter لتأكيدها كصيغة صفيف.

• A1:A18 هو نطاق البيانات العيّني (عدّله حسب الحاجة).
• 1يعني أنك تريد الربع الأول (الرُّبع الأدنى أو المئين)²⁵). يمكنك استخدام2 للوسيط أو3للربع الثالث (الرُّبع الأعلى أو المئين)⁷⁵).

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

ماكرو VBA لتصفية البيانات وحساب المئين/الربع باستثناء الصفر

يمكنك أيضًا استخدام VBA (Visual Basic for Applications) لأتمتة عملية استبعاد القيم الصفرية ثم حساب المئين أو الربع على البيانات المتبقية. هذا النهج عملي جدًّا خاصةً عند التعامل مع مجموعات بيانات كبيرة، أو عندما تريد تكرار الإجراء مرارًا دون إدخال الصيغ يدويًّا.

السيناريوهات المناسبة:مثالي للمستخدمين المتقدمين، أو المهام المتكررة، أو النطاقات المعقدة. ومن خلال تخصيص الكود، يمكنك التعامل مع أي مؤشر مئين أو ربع، وأي نطاق بيانات.

1. انتقل إلى علامة التبويبأدوات المطورفي Excel. إذا لم تكن ظاهرة، انقر بزر الماوس الأيمن على الشريط، واخترتخصيص الشريط، ثم فعّل خيارالمطور. بعد ذلك، انقر علىأدوات المطور > Visual Basic.
2. في نافذةMicrosoft Visual Basic for Applications، انقر علىإدراج > وحدة نمطية.
3. انسخ والصق كود VBA التالي في الوحدة النمطية:

Sub FilterZeroAndPercentile()
    Dim rng As Range
    Dim ws As Worksheet
    Dim arr As Variant
    Dim filteredArr As Variant
    Dim i As Long, count As Long
    Dim percentileVal As Double
    Dim quartileVal As Double
    Dim pctl As Double
    Dim quartIdx As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    ' Prompt for percentile value (e.g., 0.75 for 75th percentile)
    pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
    
    ' Prompt for quartile index (1, 2, 3, 4)
    quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
    
    arr = rng.Value
    count = 0
    
    ' Count non-zero numbers
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) > 0 Then
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        MsgBox "No non-zero data found!", vbExclamation, xTitleId
        Exit Sub
    End If
    
    ReDim filteredArr(1 To count)
    count = 0
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) > 0 Then
            count = count + 1
            filteredArr(count) = arr(i, 1)
        End If
    Next i
    
    ' Calculate percentile / quartile
    percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
    quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
    
    MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
           "Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub

4. انقر على الزر أو اضغط علىF5في نافذة VBA لتشغيل الماكرو. بعد ذلك، سيُطلب منك تحديد نطاق البيانات (أرقام فقط)، ثم إدخال المئين المطلوب (مثلًا، 0.3 للمئين الـ30)^th)، ومؤشر الربع (مثلًا، 1 للربع الأول). وسيقوم الماكرو تلقائيًّا باستبعاد القيم الصفرية وعرض النتائج في مربع رسالة.

المزايا:يتعامل بسرعة مع البيانات الكبيرة أو غير المنتظمة، ويستبعد القيم الصفرية تمامًا، ويُجنّبك الحاجة إلى إدخال الصيغ يدويًّا. كما يدعم الاستخدام المتكرر والتخصيص بسهولة.
العيوب:يتطلب تمكين الماكرو ومعرفة أولية بـ VBA، ولا يناسب استخدامه مباشرة في الصيغ داخل أوراق العمل ما لم يُحوَّل إلى دالة معرّفة من قِبل المستخدم (UDF).

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

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

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

أفضل أدوات الإنتاجية لمكتبتك

🤖	KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
	الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
	بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
	قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
	مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
	ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
	أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   12 أدواتالتحويل(تحويل إلى كلمات،تحويل العملة، ...)|   7 أدواتدمج وتقسيم(دمج متقدم للصفوف،تقسيم الخلايا، ...)|... وأكثر من ذلك

استخدم Kutools باللغة التي تفضلها – يدعم الإنجليزية والإسبانية والألمانية والفرنسية والصينية و40+ لغات أخرى!

عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...

مزيد من التفاصيل حول Kutools لـ Excel...تنزيل مجاني...

---

يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير

• تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
• افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
• يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!

مزيد من التفاصيل حول Office Tab...تنزيل مجاني...

---

جميع الإضافات من Kutools في برنامج تثبيت واحد!

Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.

• حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
• برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
• يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
• تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
• أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل

مزيد من التفاصيل حول Kutools for Office...تنزيل مجاني...