Skip to main content

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

كيفية فرز البيانات الديناميكية في مايكروسوفت إكسيل؟

Author Kelly Last modified

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

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

sort data dynamically

فرز البيانات الديناميكية في إكسيل باستخدام صيغة

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

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

1. أدخل عمودًا جديدًا في بداية مجموعة البيانات الأصلية. في السيناريو العينة، أدخل عمودًا بعنوان “الرقم” قبل البيانات الأصلية كما هو موضح أدناه:

sample data

2. في الخلية A2 (الخلية العلوية تحت “الرقم”، بافتراض أن نطاق بياناتك هو A2:C6)، أدخل الصيغة التالية لحساب ترتيب كل منتج بناءً على رقم التخزين الخاص به. هذا يسمح لإكسيل بتعيين ترتيب فريد لكل عنصر باستخدام حقل التخزين:

=RANK(C2, C$2:C$6)

اضغط Enter بعد كتابة الصيغة. تقوم دالة RANK بمقارنة قيمة التخزين في C2 مقابل النطاق الكامل C2:C6، وتحدد رقم الترتيب (حيث يكون 1 هو أعلى تخزين). إذا كان لديك أكثر من خمسة عناصر، قم بتعديل C6 لتغطية النطاق اللازم.

enter a formula to sort original products by their storage

3. احتفظ بتحديد الخلية A2. اسحب مؤشر التعبئة إلى الخلية A6 (أو الصف الأخير من بياناتك) لتطبيق صيغة الترتيب على جميع العناصر في قائمتك.

drag the formula to other cells

4. لإنشاء الجدول المرتب ديناميكيًا، انسخ أولًا صف الرؤوس من بياناتك الأصلية والصقه في موقع جديد (على سبيل المثال، E1:G1). في العمود الجديد “الرقم المطلوب” (E2:E6 في هذا المثال)، أدخل قائمة متسلسلة من الأرقام التي تتطابق مع الترتيب (1، 2، 3، ...). يقوم هذا التسلسل بإعداد الترتيب للاسترجاع.

Copy the titles of the original data to another cell,and insert the sequence numbers

5. في الخلية F2 (بجانب “المنتج” في الجدول الجديد)، أدخل صيغة VLOOKUP التالية لاسترجاع اسم المنتج المقابل لكل رقم ترتيب، ثم اضغط Enter:

=VLOOKUP(E2, A$2:C$6, 2, FALSE)

تبحث هذه الصيغة عن الترتيب المحدد في العمود A وتقوم بإرجاع اسم المنتج المرتبط من العمود الثاني.

apply the VLOOKUP function to return the corresponding data

6. اسحب مؤشر التعبئة من F2 إلى F6 لملء أسماء جميع المنتجات. لملء أرقام التخزين المرتبة، حدد F2:F6، ثم اسحب مؤشر التعبئة إلى G2:G6.

سيعرض الجدول الجديد المنتجات بترتيب تنازلي حسب قيمة التخزين، دائمًا يعكس التغييرات من الجدول الأصلي:

get a new storage table sorting in descend order by the storage

على سبيل المثال، إذا تلقت محلك للقرطاسية شحنة جديدة وقمت بتحديث كمية التخزين الخاصة بـ “القلم” من 55 إلى 200 في قائمتك الأصلية، سيعيد الجدول المرتب وضع القلم تلقائيًا ليُظهر رتبته وكميته الجديدة - بدون الحاجة إلى الفرز اليدوي. توفر هذه الحلول أتمتة صيانة القوائم، مما يقلل الأخطاء اليدوية ويحافظ على دقة التقارير الرئيسية.

the new table will update based on the original data changes

ملاحظات:

  • القيم المكررة (التعادلات): إذا كانت هناك تعادلات في أرقام التخزين، ستحدد دالة RANK نفس الترتيب لعدة صفوف وستعيد VLOOKUP التطابق الأول فقط. لترتيب مستقر، استبدل الخطوة 2 بهذه الصيغة في A2 (ثم املأ لأسفل):
    • =RANK(C2, C$2:C$6) + COUNTIF($C$2:C2, C2) - 1
  • قم بتعديل النطاقات (C$2:C$6, A$2:C$6) مع زيادة قائمتك. تحويل المصدر إلى جدول إكسيل يمكن أن يسهل الصيانة (المراجع المنظمة).
  • احرص على أن تكون قائمة “الرقم المطلوب” متصلة (1، 2، 3، ...) لضمان استرجاع كل صف مرتب.

نصائح:

  • في Microsoft 365 / Excel 2019+، فكر في استخدام SORT/SORTBY لفرز ديناميكي مباشر أكثر.
  • إذا كنت تفضل تجنب الأعمدة المساعدة، البديل المتقدم هو INDEX/MATCH (أو XLOOKUP) مع SMALL/ROW لتكوين قائمة مرتبة، رغم أنها أقل قابلية للقراءة وأصعب في الصيانة.

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

الفرز التلقائي للبيانات باستخدام حدث تغيير ورقة العمل (VBA)

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

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

السلبيات: يتطلب ماكرو؛ يحتاج أي شخص يقوم بتحرير الملف إلى إصدار إكسيل يدعم الماكرو.

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

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

للتنفيذ:

1. انقر بزر الماوس الأيمن على علامة ورقة العمل التي تريد فرزها تلقائيًا واختر عرض الكود.

2. في نافذة الكود الخاصة بورقة العمل (وليس وحدة قياسية)، الصق الكود التالي:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim SortRange As Range
    ' Adjust your range as appropriate (example: A1:C6 includes headers)
    Set SortRange = Range("A1:C6")
    ' Sort by Storage in descending order (assuming Storage is in column C)
    SortRange.Sort Key1:=SortRange.Columns(3), Order1:=xlDescending, Header:=xlYes
End Sub

3. أغلق محرر VBA. الآن، كلما تم تعديل بيانات ضمن A1:C6، يقوم إكسيل تلقائيًا بإعادة فرز النطاق بالكامل حسب عمود “التخزين” (العمود C) بترتيب تنازلي.

ملاحظات:

  • قم بتحديث Range("A1:C6") لتتناسب مع جدولك الحقيقي (بما في ذلك العناوين).
  • يجب أن تعيش هذه الماكرو في وحدة ورقة العمل (مثل Sheet1 (Code))، وليس في وحدة قياسية.
  • احفظ المصنف بصيغة .xlsm وتأكد من تمكين الماكرو، أو لن يعمل الفرز التلقائي.

نصائح:

  • لتغيير الفرز حسب عمود مختلف، قم بتغيير الوسيطة Columns(3) إلى الفهرس المطلوب.
  • هل تحتاج إلى ترتيب تصاعدي؟ قم بتغيير Order1:=xlDescending إلى xlAscending.
  • إذا زاد نطاقك، قم بتوسيع العنوان الثابت بشكل دوري (مثلًا إلى A1:C1000) أو حول النطاق إلى جدول إكسيل وقم بتحديث الماكرو لعنوان الجدول.

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

استخدام جدول إكسيل (“تنسيق كجدول”) لتسهيل الفرز

تحويل نطاق بياناتك إلى جدول إكسيل رسمي باستخدام ميزة “تنسيق كجدول” يوفر العديد من المزايا لإدارة القوائم والفرز.

✅ الإيجابيات: تحديثات المراجع المنظمة تلقائيًا عند إضافة تعديل البيانات، وتوفر القوائم المنسدلة للفرز/التصفية لكل عمود. يمكنك فرز الجدول بالكامل فورًا بالنقر على القائمة المنسدلة لرأس العمود. يتوسع الجدول تلقائيًا عند إضافة صفوف جديدة.

⚠️ السلبيات: الفرز ليس تلقائيًا بالكامل — لا يزال عليك النقر لفرز بعد التغييرات، إلا إذا قمت بإضافة ماكرو VBA لتشغيل الفرز تلقائيًا.

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

كيفية الاستخدام:

  1. حدد نطاق بياناتك واضغط Ctrl + T لتحويله إلى جدول إكسيل. تأكد من تحديد خيار My table has headers.
  2. انقر على السهم المنسدل في رأس العمود الذي تريد فرزه (مثل التخزين) واختر Sort Largest to Smallest أو Sort Smallest to Largest.

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

💡 نصائح: تدعم جداول إكسيل المراجع المنظمة في الصيغ، مما يجعلها أسهل في القراءة والصيانة مع نمو البيانات. لمسح الفرز، استخدم القائمة المنسدلة للعمود وحدد Clear Sort. إذا كنت تستخدم VBA، تأكد من أن الماكرو يشير إلى اسم الجدول الصحيح (مثل ListObjects("Table1")).

الفرز باستخدام وظائف الصفيف الديناميكية SORT أو SORTBY (Excel 365/2019+)

الميزات الحديثة من إكسيل (Excel 365، Excel 2019 وما يليها) تقدم وظائف الصفيف الديناميكية التي يمكنها تلقائيًا إنشاء نسخة مرتبة من بياناتك في الوقت الفعلي — بدون الحاجة إلى أعمدة مساعدة أو VBA.

✅ الإيجابيات: فرز حقيقي تلقائي في الوقت الفعلي. الصيغ تُخرج النتائج إلى الخلايا المجاورة بينما تنمو أو تنخفض القائمة الأصلية. يتطلب خطوات قليلة جدًا للإعداد.

⚠️ السلبيات: متوفرة فقط في إصدارات إكسيل الأحدث. الإخراج هو نسخة منفصلة — النطاق الأصلي لا يتم إعادة ترتيبه.

مثال على السيناريو: تريد نسخة محدثة تلقائيًا ومرتبة من قائمة المخزون لأغراض عرض لوحة المعلومات أو التقارير، مع الحفاظ على ترتيب الإدخال لتحرير أو إدخال البيانات.

كيفية الاستخدام:

لنفترض أن جدول بياناتك الأصلي موجود في النطاق A2:C6 بما في ذلك العناوين في A1:C1. ولإنشاء جدول مرتب ديناميكيًا (حسب التخزين، تنازليًا)، أدخل هذه الصيغة في أي خلية فارغة، مثل E2:

=SORT(A2:C6, 3, -1)

يؤدي هذا إلى إنتاج نسخة جديدة، تُرتب تلقائيًا من جدولك الأصلي، مرتبة حسب العمود الثالث (التخزين) بترتيب تنازلي. استخدم -1 للترتيب التنازلي و1 للترتيب التصاعدي.

للحصول على فرز أكثر تفصيلاً، مثل المفاتيح الثانوية أو المعايير المخصصة، استخدم SORTBY:

=SORTBY(A2:C6, C2:C6, -1, B2:B6, 1)

يقوم هذا بفرز أولاً حسب التخزين (تنازليًا)، ثم حسب المنتج (تصاعديًا).

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

💡 نصائح:

  • إذا لم تكن الخلايا المجاورة فارغة، ستحصل على خطأ #SPILL! — تأكد من وجود مساحة فارغة كافية للإخراج.
  • لبيانات على ورقة أخرى، قم بتضمين اسم الورقة، مثل =SORT(Sheet1!A2:C100, 3, -1).
  • إذا كان مصدرك قد يكبر، قم بالإشارة إلى نطاق أكبر أو قم بتعريفه كجدول إكسيل للمراجع المنظمة.

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

a screenshot of kutools for excel ai

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

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

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

🤖 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد