KutoolsforOffice — حل واحد، خمس أدوات قوية.تحقيق المزيد بجهد أقل.

كيف يمكنك حذف أقل درجة ثم حساب المتوسط أو المجموع في Excel؟

المؤلفXiaoyangتاريخ التعديل

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

حذف أقل درجة وحساب المتوسط أو المجموع باستخدام الصيغ

كود VBA - حذف أقل درجة أو أقل n درجات وحساب المجموع أو المتوسط تلقائيًّا

سهم أزرق فقاعة جهة اليمينحذف أقل درجة وحساب المتوسط أو المجموع باستخدام الصيغ

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

فيما يلي شرحٌ منفصل لكلا الطريقتين—الجمع والحساب المتوسط—مع تعليماتٍ مفصلة ونصائحَ عملية لتطبيقٍ مرن.

اجمع الأرقام ولكن استبعد أقل قيمة أو أقل N قيم:

لحساب المجموع لكل صف أو قائمة مع حذف أقل قيمة، استخدم الطريقة التالية:

1. حدد الخلية الفارغة التي تريد أن تظهر فيها نتيجة مجموع الصف الأول (على سبيل المثال، الخلية I2 إذا كانت بياناتك في B2:H2)، ثم أدخل الصيغة التالية:

=SUM(B2:H2)-SMALL(B2:H2,1)

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

فيما يلي لقطة شاشة للرجوع إليها:

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

ملاحظات ونصائح:

  • لإقصاء أقل قيمتين أو ثلاث قيم أو أكثر، يمكنك توسيع الصيغة بطرح نتائج إضافية من دالةSMALL. على سبيل المثال:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)
  • في هذه الصيغة، يُشيرB2:H2 إلى النطاق الذي تريد جمعه، بينما تُحدّد الأرقام 1 و2 و3 وما إلى ذلك القيم n لأصغر الأرقام التي سيتم استبعادها. قم بتعديل قيمةn وفقًا لعدد أقل الدرجات التي ترغب في حذفها.
  • احرص ألا تعيّن قيمةn لتكون أكبر من أو تساوي إجمالي عدد القيم؛ وإلا فستواجه أخطاءً أو نتائج غير مرغوبة.
  • تعمل هذه الصيغ بشكل مستقل لكل صف. إذا كانت بياناتك تمتد عبر الأعمدة بدلًا من الصفوف، فعدّل النطاقات وفقًا لذلك.
  • إذا احتوت مجموعة البيانات على قيم مكررة لأصغر رقم، فإن الدالةSMALL(B2:H2,1) تستبعد حدوثًا واحدًا فقط في كل مرة. ولحذف تكرارات متعددة، كرّر دالةSMALL مع زيادة قيمة k كما هو موضح أعلاه.

احسب المتوسط ولكن استبعد أقل قيمة أو أقل N قيم:

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

1. حدد خلية لعرض متوسط الدرجات (مثلاً، J2 إذا كانت درجاتك في النطاق B2:H2)، ثم أدخل الصيغة التالية:

=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)

2. بعد الضغط على Enter، اسحب الصيغة لأسفل حسب الحاجة لحساب المتوسط للصفوف الإضافية، مع استبعاد أقل درجة في نطاق كل صف على حدة.

احسب متوسط الأرقام ولكن استبعد أقل قيمة باستخدام صيغة

ملاحظات وتوجيهات مهمة:

  • لحساب المتوسط مع استبعاد أكثر من أقل درجة واحدة، قم بتوسيع الصيغة عبر طرح حدود إضافية من دالةSMALLوخفض القاسم وفقًا لذلك:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)
  • مرة أخرى،B2:H2 هو النطاق الذي سيتم حساب متوسطه، وn يمثل عدد أقل القيم التي لن تُضمَّن في الحساب.
  • إذا حاولت طرح عدد من القيم يفوق العدد الموجود في النطاق، فستُعيد الصيغة خطأً من نوع#NUM!، مما يشير إلى أن القيم غير كافية لحساب المتوسط. تأكد دائمًا من أن قيمةn أقل من عدد القيم المتاحة.
  • يُنصح بالتحقق مرتين من أن القيم الدنيا ليست ضرورية أو مطلوبة لحسابك قبل استبعادها، إذ قد يؤثر ذلك على النتائج النهائية.
  • عند التعامل مع مجموعات بيانات ضخمة جدًّا أو عند الحاجة إلى استبعاد أقل n قيم ديناميكيًّا، يُوصى باستخدام حل آلي أو صيغة صفيفية (Array).
لقطة شاشة لـ kutools for excel الذكاء الاصطناعي

افتح سحر إكسل مع KUTOOLS AI

  • التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
  • الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
  • برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
  • تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
  • ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
حسِّن قدراتك في إكسل بأدوات مدعومة بالذكاء الاصطناعي.حمِّل الآنوجرِّب الكفاءة كما لم تفعل من قبل!

سهم أزرق فقاعة جهة اليمين كود VBA - حذف أقل درجة أو أقل n درجات وحساب المجموع أو المتوسط تلقائيًّا

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

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

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

1. انقر علىDeveloper > Visual Basic. في نافذةMicrosoft Visual Basic for Applications، انقر علىInsert > Module، ثم أدخل الكود التالي:

Sub DropLowestNandCalculate()
    Dim WorkRng As Range
    Dim OutputRng As Range
    Dim n As Integer
    Dim FuncType As String
    Dim i As Integer, j As Integer, k As Integer
    Dim Arr() As Variant, TempArr() As Double
    Dim RowSum As Double
    Dim RowCount As Integer
    Dim MinIdx() As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
    
    Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
    
    n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
    
    FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
    
    For i = 1 To WorkRng.Rows.Count
        Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
        RowCount = UBound(Arr)
        
        ReDim TempArr(1 To RowCount)
        For j = 1 To RowCount
            TempArr(j) = Arr(j)
        Next j
        
        ' Mark n lowest values as used by setting to very high number
        For k = 1 To n
            Dim MinVal As Double, MinPos As Integer
            MinVal = Application.WorksheetFunction.Min(TempArr)
            
            For j = 1 To RowCount
                If TempArr(j) = MinVal Then
                    TempArr(j) = 1E+308
                    Exit For
                End If
            Next j
        Next k
        
        RowSum = 0
        Dim ValidCount As Integer
        ValidCount = 0
        
        For j = 1 To RowCount
            If TempArr(j) <> 1E+308 Then
                RowSum = RowSum + Arr(j)
                ValidCount = ValidCount + 1
            End If
        Next j
        
        If UCase(FuncType) = "AVG" Then
            If ValidCount = 0 Then
                OutputRng.Cells(i, 1).Value = "N/A"
            Else
                OutputRng.Cells(i, 1).Value = RowSum / ValidCount
            End If
        Else
            OutputRng.Cells(i, 1).Value = RowSum
        End If
    Next i
End Sub

2. بعد إضافة الكود، انقر على زرزر التشغيل أو اضغط علىF5 للتنفيذ.

3. اتبع المطالبات التي تظهر على الشاشة:

  • حدد نطاق الدرجات الذي ترغب في معالجته، مع التأكد من أن درجات كل طالب موجودة في صف واحد.
  • اختر الخلية العلوية اليسرى في منطقة قائمة التخطيط (سيتم ملء النتائج عموديًّا لأسفل وفقًا لعدد الصفوف).
  • أدخل أقل عدد من الدرجات التي تريد حذفها (على سبيل المثال،)1 لاستبعاد أقل درجة واحدة فقط في كل صف).
  • اكتبSUM للحصول على المجموع (باستثناء الدرجات المحذوفة)، أوAVG للحصول على المتوسط المعاد حسابه (باستثناء الدرجات المحذوفة).

يقوم الماكرو بمعالجة كل صف ضمن منطقة الدرجات المحددة، ويضع إما المجموع أو المتوسط (حسب اختيارك) في منطقة القائمة الخاصة بك. وإذا تم حذف جميع الدرجات في صفٍ ما، تُشار النتيجة بـN/A لتجنب الأخطاء.

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

إذا واجهت مشكلات مثل نتائج غير صحيحة أو أخطاء، فتأكد من أن النطاقات مُحدَّدة بدقة، وأن قيمة «n» ليست أكبر من أو تساوي إجمالي عدد الدرجات المتاحة لكل صف.

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

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

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

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

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

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

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

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

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