كيف تُفرَّز البيانات الديناميكية في Microsoft Excel؟
عند إدارة مجموعات بيانات تتغيّر باستمرار، مثل سجلات مخزون متجر أدوات مكتبية، يصبح فرز المعلومات بكفاءة أمرًا ضروريًّا لإعداد تقارير دقيقة وتحليل سريع. ومع ذلك، فإن إعادة الفرز يدويًّا في كل مرة تُحدَّث فيها البيانات قد تستغرق وقتًا طويلاً وتؤدي إلى أخطاء. ومن هنا تبرز الحاجة: كيف يمكنك الحفاظ على قوائم Excel مرتبة تلقائيًّا، بحيث تعكس نتائج الفرز أحدث المعلومات فور حدوث أي تغيير في البيانات الأساسية—مثل تعديل الكميات أو إدخال عناصر جديدة—دون الحاجة إلى تدخل يدوي؟
يقدّم هذا المقال عدة طرق عملية لفرز البيانات الديناميكية تلقائيًا في Excel. ستتعرّف على أساليب تعتمد على الصيغ، وأخرى تستند إلى أتمتة VBA، فضلاً عن أدوات Excel الحديثة المدمجة التي تحافظ على تنظيم جداولك تلقائيًا كلما تطورت بياناتك. وتُعدّ هذه الطرق مثالية لسيناريوهات مثل إدارة المخزون، وتتبع المبيعات، وتصنيف الدرجات، أو أي مهمة تتطلب بيانات مرتبة ومُحدَّثة باستمرار.
➤ فرز البيانات الديناميكية في Excel باستخدام الصيغة
➤ فرز البيانات تلقائيًا باستخدام حدث Worksheet Change (VBA)
➤ استخدام جدول Excel («تنسيق كجدول») لتسهيل الفرز
➤ الفرز باستخدام دوال المصفوفة الديناميكية SORT أو SORTBY (Excel365/2019+)
فرز البيانات الديناميكية في Excel باستخدام الصيغ
تعمل هذه الطريقة في جميع إصدارات Excel الحديثة، وهي الخيار الأمثل عندما ترغب في الاحتفاظ بنسخة مرتبة ومُحدَّثة تلقائيًّا من بياناتك بجانب جدولك الأصلي. وتعتمد على تعيين رتبٍ ثم البحث عن القيم وفقًا لتلك الرتب، مما يضمن بقاء الجدول المرتب مُحدَّثًا تلقائيًّا كلما تغيَّرت بيانات الإدخال.
على سبيل المثال، افترض أنك تدير كميات التخزين للمخزون لأنواع مختلفة منالأدوات المكتبية. ولجعل جدولك يعكس فورًا أي تغيير في الكميات ويعرض المنتجات بترتيب تنازلي حسب كمية التخزين، اتبع الخطوات التالية:
1.أدرج عمودًا جديدًا في بداية مجموعة البيانات الأصلية. في المستند وسيناريو العيّنة، أضف عمودًا بعنوان «رقم.» قبل البيانات الأصلية، كما هو موضح أدناه:
2.في الخلية A2 (الخلية الموجودة مباشرةً تحت «رقم.»، بافتراض أن نطاق بياناتك هو A2:C6)، أدخل الصيغة التالية لحساب رتبة كل منتج بناءً على كمية التخزين الخاصة به. وبهذا يُمكن Excel من تعيين ترتيب فريد لكل عنصر باستخدام حقل التخزين:
=RANK(C2, C$2:C$6)بعد كتابة الصيغة، اضغط علىEnter. تقارن دالة RANK القيمة المخزَّنة في الخلية C2 مع النطاق الكامل C2:C6، وتعيّن رقم رتبة (حيث تكون)1 هي أعلى كمية تخزين). وإذا كان لديك أكثر من خمسة عناصر، غيّرC6 ليشمل النطاق المطلوب.
3. احتفظ بتحديد الخلية A2، ثم اسحبمقبض التعبئةلأسفل حتى الخلية A6 (أو حتى آخر صف يحتوي على بياناتك) لتطبيق صيغة الترتيب على جميع العناصر في قائمتك.
4.لإنشاء جدول مرتب ديناميكيًّا، انسخ أولًا صف العناوين من بياناتك الأصلية والصقه في موقع جديد (مثلًا، E1:G1). ثم في عمود «الرقم المطلوب» الجديد (E2:E6 في هذا المثال)، أدخل سلسلة متتالية من الأرقام تتوافق مع الرتب (1، 2، 3، …)، حيث يُحدِّد هذا التسلسل ترتيب الاسترجاع.
5.في الخلية F2 (بجانب «المنتج» في الجدول الجديد)، أدخل صيغة VLOOKUP التالية لاسترجاع اسم المنتج المقابل لكل رقم رتبة، ثم اضغط علىEnter:
=VLOOKUP(E2, A$2:C$6, 2, FALSE)تبحث هذه الصيغة عن الرقم المرتب المحدد في العمود A، ثم تُعيد اسم المنتج المرتبط به من العمود الثاني.
6.اسحب مقبض التعبئة من الخلية F2 لأسفل حتى F6 لتعبئة أسماء جميع المنتجات. ولتعبئة أرقام التخزين المرتبة، حدد النطاق F2:F6، ثم اسحب مقبض التعبئة يمينًا حتى النطاق G2:G6.
سيعرض جدولك الجديد المنتجات بـترتيب تنازليحسب قيمة التخزين، مع انعكاس دائم لأي تغييرات من جدولك الأصلي:
على سبيل المثال، إذا تلقّى متجر الأدوات المكتبية شحنةً وقمت بتحديث كمية التخزين لعنصر «القلم» من55 إلى200 في قائمتك الأصلية، فسيُعيد الجدول المرتّب ترتيب إدخال القلم تلقائيًا ليعبّر فورًا عن مرتبته وكميته الجديدة—دون الحاجة إلى فرز يدوي. ويُبسّط هذا الحل صيانة القائمة تلقائيًا، مما يقلل الأخطاء اليدوية ويحافظ على دقة تقاريرك الأساسية.
ملاحظات:
- القيم المكررة (التعادل):إذا كانت هناك قيم متعادلة في أرقام التخزين، فإن دالة
RANKستُعيّن نفس المرتبة لعدة صفوف، وستُعيد دالةVLOOKUPأول تطابق فقط. وللحصول على ترتيب مستقر، استبدل الخطوة 2 بهذه الصيغة المُفصِّلة للتعادل في الخليةA2 (ثم اسحبها لأسفل):
=RANK(C2, C$2:C$6) + COUNTIF($C$2:C2, C2) - 1C$2:C$6،A$2:C$6) لتتماشى مع نمو قائمتك. ويمكن أن يبسّط تحويل المصدر إلىجدول Excelعملية الصيانة (باستخدام المراجع المنظمة).نصائح:
- إذا كنت تستخدم Microsoft 365 أو Excel 2019 فأعلى، فكّر في استخدام دالة
SORTأوSORTBYللحصول على ترتيب ديناميكي مباشر أكثر. - إذا كنت تفضّل تجنُّب أعمدة المساعدة، فإن بديلًا متقدمًا هو استخدام
INDEX/MATCH(أو)XLOOKUP) مقترنًا بدالةSMALL/ROWلإنشاء قائمة مرقمة، رغم أنها أقل وضوحًا وأصعب صيانةً.
نصائح واستكشاف الأخطاء وإصلاحها:تحقّق مجددًا من نطاقات الصيغ الخاصة بك للتأكد من أن جميع العناصر الجديدة أو المحذوفة مشمولة عند تغيير حجم قائمتك الأصلية. قد تحتاج إلى تحديث مراجعك (مثل: C$2:C$10 بدلاً منC$2:C$6) إذا قمت بتوسيع القائمة. وللتغييرات المتكررة في حجم القائمة، فكّر في تحويل بياناتك إلى جدول Excel والاعتماد على أسماء أعمدة الجدول بدلاً من نطاقات الخلايا.
فرز البيانات تلقائيًا باستخدام حدث Worksheet Change (VBA)
يُعدّ هذا الحل مثاليًا عندما تريد أن يظلالجدول الأصليمرتبًا في مكانه—فأي تعديلات يقوم بها المستخدم أو الإدخالات الجديدة تُفعّل فورًا إعادة ترتيب الصفوف، مما يقلل الحاجة إلى الفرز اليدوي ويعمل بسلاسة مع القوائم المشتركة وسجلات الجرد وغيرها من السجلات التي يتم تحديثها بشكل متكرر.
المزايا:يحافظ دائمًا على تنظيم البيانات الأصلية؛ ولا يتطلب جدولًا إضافيًا أو نسخةً منها؛ ويمكن تطبيقه على أي عدد من الأعمدة.
العيوب:يتطلب ماكرو؛ ويجب أن يستخدم أي شخص يُجري تعديلات على الملف إصدار Excel المُفعَّل للماكرو.
مثال توضيحي:يقوم متجرأدوات مكتبيةبتتبع المخزون في جدول، وعندما يُغيّر أحدٌ ما كمية التخزين، يُنقل الصف المقابل تلقائيًا إلى موضعه الصحيح وفقًا للترتيب.
استخدمه بحذر:هذه الطريقة تؤثر مباشرةً على تخطيط بياناتك—لذا احتفظ بنسخ احتياطية أو نظام لتتبع الإصدارات عند الحاجة.
لتطبيقه:
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 Sub3.أغلق محرر VBA. الآن، كلما تم تعديل أي بيانات داخلA1:C6، سيقوم Excel تلقائيًا بإعادة فرز النطاق بأكمله حسب عمود «التخزين» (العمود C) بترتيب تنازلي.
ملاحظات:
- حدّث
Range("A1:C6")ليتطابق مع جدولك الفعلي (بما في ذلك العناوين). - يجب أن يكون هذا الماكرو موجودًا فيوحدة ورقة العمل(مثل)Sheet1 (Code))، وليس في وحدة قياسية.
- احفظ المصنف باسم
.xlsm، وتأكد من تمكين الماكروات؛ وإلا فلن يعمل الفرز التلقائي.
نصائح:
- لفرز البيانات حسب عمود مختلف، غيّر المعامل
Columns(3)إلى الفهرس المطلوب. - هل ترغب في الترتيب التصاعدي؟ غيّر
Order1:=xlDescendingإلىxlAscending. - إذا كان نطاقك في توسع، قم بتوسيع العنوان الثابت بشكل دوري (مثلًا إلى)
A1:C1000) أو حوّل النطاق إلى جدول Excel وحدّث الماكرو ليشير إلى عنوان الجدول.
شرح المعاملات واستكشاف الأخطاء وإصلاحها:يقوم الماكرو بفرز النطاق الثابت الذي تحدده وفقًا للعمود المختار، بشرط أن يحتوي النطاق على صف رؤوس. إذا لم يتم الفرز، فتأكد من تفعيل الماكرو وأن الكود موجود في وحدة الورقة الصحيحة. ولأن الفرز لا يُفعّل عند إجراء تعديلات خارج النطاق المحدد، يُرجى توسيع النطاق ليشمل جميع الصفوف القابلة للتعديل.
استخدم جدول Excel («تنسيق كجدول») لتسهيل الفرز
إن تحويل نطاق البيانات الخاص بك إلى جدول Excel رسمي باستخدام ميزةتنسيق كجدوليوفّر العديد من الفوائد لإدارة القوائم والفرز.
✅ المزايا:يتم تحديث المراجع المنظمة تلقائيًا عند إضافة أو تعديل البيانات، وتوفّر قوائم منسدلة للفرز والتصفية لكل عمود. يمكنك فرز الجدول بأكمله فورًا بالنقر على القائمة المنسدلة لعنوان العمود، كما يتوسع الجدول تلقائيًا عند إضافة صفوف جديدة.
⚠️ العيوب:الفرز ليس تلقائيًا بالكامل — لا يزال عليك النقر لإعادة الفرز بعد إجراء التعديلات، ما لم تُضف ماكرو VBA لتفعيل الفرز التلقائي.
سيناريو نموذجي:في ملفات العمل التعاونية أو مجموعات البيانات الكبيرة، حيث يحتاج المستخدمون إلى تنظيم بصري وإدخال سريع للصفوف، تُسهّل جداول Excel الفرز الروتيني وتجعله أقل عرضةً للخطأ.
كيفية الاستخدام:
- حدد نطاق البيانات واضغط علىCtrl + T لتحويله إلى جدول Excel. تأكد من تأشير خانةMy table has headers.
- انقر على السهم المنسدل في رأس العمود الذي تريد فرزه (مثلًا،)Storage)، ثم اخترSort من الأكبر إلى الأصغرأوSort من الأصغر إلى الأكبر.
إذا كنت ترغب في أن يتم الفرز تلقائيًا كلما تم تعديل الجدول، فاربط ماكرو VBA (كما وُصف سابقًا) بالورقة التي يقع فيها الجدول — مُدمجًا بذلك بين البساطة المنهجية لهياكل جداول Excel وقدرة أتمتة VBA.
💡 نصائح:تدعم جداول Excel المراجع المنظمة في الصيغ، مما يجعل قراءتها وصيانتها أسهل كلما نمت البيانات. ولمسح الفرز، استخدم القائمة المنسدلة للعمود واخترClear Sort. وإذا كنت تستخدم VBA، فتأكد من أن الماكرو يشير إلى اسم الجدول الصحيح (مثلًا،)ListObjects("Table1")).
الفرز باستخدام دوال المصفوفة الديناميكية SORT أو SORTBY (Excel 365/2019+)
تقدم إصدارات Excel الحديثة (Excel 365 وExcel 2019 والإصدارات الأحدث) دوال مصفوفة ديناميكية قادرة على إنشاء نسخة مرتبة من بياناتك تلقائيًا وفي الوقت الفعلي—بدون الحاجة إلى أعمدة مساعدة أو VBA.
✅ المزايا:فرز تلقائي حقيقي في الوقت الفعلي! تنزلق نتائج الصيغ تلقائيًا إلى الخلايا المجاورة كلما توسعَت القائمة الأصلية أو انكمشت، وكل ذلك يتطلب بضع خطوات بسيطة جدًّا للإعداد.
⚠️ العيوب:متوفرة فقط في إصدارات Excel الأحدث، والمخرجات عبارة عن نسخة منفصلة — لن يتم إعادة ترتيب نطاقك الأصلي.
مثال توضيحي:تريد نسخة مرتبة ومحدَّثة تلقائيًا من قائمة الجرد الخاصة بك لعرضها في لوحة التحكم أو لأغراض إعداد التقارير، مع الحفاظ على الترتيب الأصلي للإدخالات لتسهيل التعديل أو إدخال البيانات.
كيفية الاستخدام:
افترض أن جدول بياناتك الأصلي يقع في النطاقA2:C6، بما في ذلك رؤوس الأعمدة فيA1:C1. لإنشاء جدول مرتب ديناميكيًا (حسب)التخزينبترتيب تنازلي)، أدخل هذه الصيغة في أي خلية فارغة، مثلE2:
=SORT(A2:C6, 3, -1)ينتج هذا جدولًا جديدًا مرتبًا تلقائيًا من جدولك الأصلي، مرتبًا حسب العمود الثالث ()التخزين) بترتيب تنازلي. استخدم-1 للترتيب التنازلي و1 للترتيب التصاعدي.
لفرز أكثر دقة، مثل المفاتيح الثانوية أو المعايير المخصصة، استخدمSORTBY:
=SORTBY(A2:C6, C2:C6, -1, B2:B6, 1)يتم الترتيب أولاً حسبالتخزين (بشكل تنازلي)، ثم حسبالمنتج (بشكل تصاعدي).
بعد كتابة الصيغة، اضغطEnter. سيقوم Excel بـ«نسف» البيانات المرتبة في الصفوف والأعمدة المجاورة، وضبط الحجم تلقائيًا كلما تغيّرت بياناتك الأصلية.
💡 نصائح:
- إذا لم تكن الخلايا المجاورة فارغة، فستحصل على خطأ
#SPILL!— تأكد من توفر مساحة فارغة كافية للمخرجات. - بالنسبة للبيانات الموجودة في ورقة أخرى، أدرج اسم الورقة كما في المثال التالي:
=SORT(Sheet1!A2:C100, 3, -1). - إذا كان مصدر بياناتك قابلاً للتوسُّع، فارجع إلى نطاق أوسع أو عرِّفه كجدول Excel لتمكين المراجع المنظمة.
بفضل طرق المصفوفة الديناميكية هذه، يصبح فرز وتحديث القوائم الكبيرة لإعداد التقارير أو لوحات التحكم سهلاً بلا جهد—فنتائجك تظل دائمًا مُحدَّثة تلقائيًّا دون الحاجة إلى أي خطوات إضافية.
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل