Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية إنشاء قائمة ديناميكية لأفضل 10 أو n في Excel؟

Author Xiaoyang Last modified

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

إنشاء قائمة ديناميكية لأفضل 10 في Excel

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

صيغ لإنشاء قائمة ديناميكية لأفضل 10

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

=LARGE($B$2:$B$20,ROWS(B$2:B2))
ملاحظة: هنا، B2:B20 هو نطاق الدرجات أو القيم، وB2 هي الخلية الأولى في هذا العمود. قم بتعديل هذه المراجع الخلوية بناءً على حجم بياناتك وموقعها.

apply a formula to extract the top10 values

2. بعد ذلك، لعرض الأسماء (أو المعرفات) المرتبطة بتلك القيم العليا، أدخل الصيغة التالية في الخلية F2. هذه صيغة صفيف، لذا بعد إدخالها، اضغط Ctrl + Shift + Enter للتأكيد. تقوم هذه الصيغة بإيجاد الأسماء المقابلة للقيم العليا التي استخرجتها للتو:

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
شرح المعاملات:
- A2:A20 هو النطاق الذي يتم سحب الأسماء منه;
- B2:B20 هو نطاق الدرجات أو القيم;
- G2 هي القيمة العليا من الصيغة أعلاه;
- B1 هو رأس قائمة القيم ويُستخدم لحساب إزاحة الصفوف في ROW.
هذه الصيغة تربط بشكل ديناميكي القيم الأعلى بأسمائها. إذا كان نطاق القيم الخاص بك يحتوي على تكرارات، فإن COUNTIF يضمن ظهور كل اسم مطابق مرة واحدة فقط مع درجته.

use a formula to get relative item

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

drag and fill the formula to other cells

a screenshot of kutools for excel ai

اكتشف سحر Excel مع Kutools AI

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

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

create a dynamic top10 list with criteria

1. ابدأ باستخراج أفضل 10 قيم تتناسب مع المعيار المحدد الخاص بك (مثل "الصف الأول") من مجموعة البيانات. أدخل هذه الصيغة في الخلية المستهدفة (على سبيل المثال، J2):

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. بعد إدخال الصيغة، اضغط Ctrl + Shift + Enter للتأكيد كصيغة صفيف ثم اسحب مقبض التعبئة لأسفل لملء الخلايا الأخرى. ستقوم الصيغة بإرجاع القيم العالية العشرة التي تطابق الشرط المختار (مثل جميع الدرجات من "الصف الأول").

apply a formula to extract the top10 values based on criteria

3. لسرد الأسماء المقابلة لهذه القيم العليا ضمن شروطك، انسخ الصيغة أدناه والصقها في الخلية I2 واضغط Ctrl + Shift + Enter كصيغة صفيف. بعد ذلك، املأ لأسفل حسب الحاجة لتوليد القائمة الكاملة للأسماء.

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

use a formula to create a dynamic top10 list in Office365

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

إنشاء قائمة ديناميكية لأفضل 10 في Office 365

بينما تتطلب الإصدارات السابقة من Excel دمج العديد من الوظائف مع صيغ الصفيف، قدمت Office 365 (وExcel 2021) وظائف صفائف ديناميكية مثل INDEX، SORT، SEQUENCE، وFILTER التي تبسط سير العمل بشكل كبير. هذه الوظائف تسهل إنشاء قوائم ديناميكية لأفضل 10، تقلل الأخطاء، وهي مفيدة بشكل خاص للجداول التي تنمو أو تتغير بشكل متكرر. إذا كنت تعمل في بيئة مع تحديثات مستمرة للبيانات، يمكن أن تساعدك هذه الوظائف في تبسيط التحليل وتمكين اتخاذ قرارات عمل أسرع.

الصيغة لإنشاء قائمة ديناميكية لأفضل 10

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

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

ما عليك سوى الضغط على مفتاح Enter. ستظهر قائمة أفضل 10 كاملة فورًا وستبقى ديناميكية، بحيث تنعكس البيانات الإضافية أو التعديلات في الترتيب فورًا.

use a formula to create a dynamic top10 list in Office365

نصائح:

وظيفة SORT:

=SORT(array, [sort_index], [sort_order], [by_col])

  • array: النطاق الذي تريد فرزه.
  • [sort_index]: رقم العمود الذي سيتم الفرز بناءً عليه. بالنسبة لجدول الدرجات النموذجي، غالبًا ما يكون هذا العمود الثاني.
  • [sort_order]: استخدم 1 للتسلسل التصاعدي أو -1 للتسلسل التنازلي. للحصول على الدرجات الأعلى، استخدم -1.
  • [by_col]: ما إذا كان سيتم الفرز بواسطة الأعمدة (TRUE) أو الصفوف (FALSE أو متروكة).

على سبيل المثال: SORT(A2:B20,2,-1) يقوم بترتيب A2:B20 بناءً على العمود الثاني بترتيب تنازلي.

وظيفة SEQUENCE:

=SEQUENCE(rows, [columns], [start], [step])

  • rows: عدد الصفوف المراد إرجاعها، على سبيل المثال، 10 لقائمة أفضل 10.
  • [columns]: (اختياري) عدد الأعمدة المراد إرجاعها.
  • [start]: (اختياري) القيمة الابتدائية.
  • [step]: (اختياري) قيمة الزيادة.

SEQUENCE(10) تولد الأرقام من 1 إلى 10، مما يسمح لـ INDEX باختيار أفضل 10 نتائج مرتبة.

بجمع هذين، =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}) يؤتيك قائمة ديناميكية ذات عمودين لأفضل 10.

الصيغة لإنشاء قائمة ديناميكية لأفضل 10 بشروط

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

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

بعد إدخال الصيغة، ما عليك سوى الضغط على مفتاح Enter. تعرض قائمة أفضل 10 التي تم تصفيتها وترتيبها وفقًا للمعيار المحدد فوراً، وتُحدث كلما قمت بتعديل بياناتك أو المعيار.

another formula to create a dynamic top10 list with criteria in office365

نصائح:

وظيفة FILTER:

=FILTER(array, include, [if_empty])

  • array: النطاق الخلوي المراد تصفيته.
  • include: الشرط (مثل يساوي فصلًا معينًا) للإدراج.
  • [if_empty]: (اختياري) ما يجب عرضه إذا لم تكن هناك نتائج تلبي المعايير.

=FILTER(A2:C25,B2:B25=F2) تعيد فقط تلك الصفوف حيث يتطابق العمود B مع القيمة في F2.

إنشاء قائمة ديناميكية لأفضل 10 باستخدام الجدول المحوري

الجدول المحوري: عرض تلقائي للنتائج N الأعلى بطريقة تفاعلية

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

لإنشاء قائمة ديناميكية بأعلى N باستخدام الجدول المحوري:

  1. انقر في أي مكان داخل جدول بياناتك، ثم انتقل إلى إدراج > PivotTable.
  2. في مربع حوار الجدول المحوري، اختر مكان وضع الجدول المحوري وانقر موافق.
  3. اسحب حقل "الاسم" (أو معرّف مشابه) إلى منطقة الصفوف.
  4. اسحب حقل "الدرجة" (أو عمود القيمة) إلى منطقة القيم. سيُفترض عادةً أنه "مجموع" أو "عدد" - بالنسبة للقوائم العليا، عادةً ما ترغب في "المجموع" أو "الأقصى". غيّر حساب حقل القيمة إذا لزم الأمر عن طريق النقر بزر الماوس الأيمن واختيار تلخيص القيم بـ.
  5. رتب عمود "الدرجة" بترتيب تنازلي بالنقر بزر الماوس الأيمن على قيمة واختيار ترتيب > ترتيب الأكبر إلى الأصغر.
  6. للتقييد بالنتائج N الأعلى، انقر فوق السهم المنسدل على تسميات الصفوف، حدد فلاتر القيم > أعلى 10...، قم بتعيين الرقم (على سبيل المثال، أعلى 10) والحقل الذي سيتم الترشيح بناءً عليه، ثم انقر موافق.

يعرض الجدول المحوري الآن القائمة الديناميكية لأعلى 10 (أو أي N تحددها). لتغيير أعلى N، ما عليك سوى إعادة زيارة إعدادات الفلتر. إذا تغيرت بياناتك، حدث الجدول المحوري لتحديث الترتيب فورًا.

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

إنشاء قائمة ديناميكية لأفضل 10 باستخدام VBA

ماكرو VBA: توليد وتحديث تلقائي لقائمة أعلى N

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

لاستخدام ماكرو VBA لإنشاء قائمة ديناميكية لأعلى N، اتبع الخطوات التالية:

  1. انقر على مطور > Visual Basic لفتح محرر VBA. (إذا لم ترى علامة تبويب مطور، انتقل إلى ملف > خيارات > تخصيص الشريط وفعّل "مطور".)
  2. في نافذة VBA، انقر على إدراج > وحدة لإضافة وحدة جديدة.
  3. الصق رمز VBA التالي في الوحدة:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
    Dim DataRange As Range
    Dim OutputRange As Range
    Dim N As Integer
    Dim ws As Worksheet, tempWS As Worksheet
    Dim xTitleId As String
    Dim LastCol As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
    Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
    N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
    
    If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
    
    ' Create a temporary worksheet to avoid sorting original data
    Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    DataRange.Copy tempWS.Range("A1")
    
    ' Determine last column for sorting key
    LastCol = DataRange.Columns.Count
    
    ' Sort in temporary sheet
    tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
    
    ' Copy headers and top N rows to output
    tempWS.Rows(1).Copy Destination:=OutputRange
    tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
    
    ' Optional: Delete temporary sheet
    Application.DisplayAlerts = False
    tempWS.Delete
    Application.DisplayAlerts = True
    
    Application.CutCopyMode = False
End Sub

4. لتنفيذ الماكرو، تأكد من أن بياناتك مُرتبة بشكل صحيح في جدول مع رؤوس. اضغط F5 أو انقر على الزر Run button في محرر VBA. ستتم مطالبتك بـ:

  1. تحديد نطاق بياناتك (بما في ذلك الرؤوس للفرز الصحيح).
  2. تحديد الخلية التي سيتم لصق النتائج فيها.
  3. أدخل الرقم N (على سبيل المثال، 10 لأعلى 10).

سيقوم الماكرو بنسخ أعلى N إدخالات (بما في ذلك الرؤوس) إلى الموقع الذي حددته.

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

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

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

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

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

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات اعتمادًا على: التنفيذ الذكي |  توليد الأكواد |  إنشاء الصيغ المخصصة |  تحليل البيانات وتوليد الرسوم البيانية |  استخدام Kutools Functions...
ميزات شائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو الخلايا بدون فقدان البيانات | تقريب بدون الصيغة...
بحث متقدم: بحث بمعايير متعددة VLookup | بحث بقيم متعددة VLookup | بحث في عدة ورقات VLookup | مطابقة غامضة...
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة معتمدة | قائمة منسدلة متعددة الاختيارات...
مدير الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاقات والأعمدة...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | مدير أدوات المصنف وورقة العمل | مكتبة النص التلقائي | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب الجدول | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...)...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع رسم بياني (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |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.

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