الفرز والتصفية ميزتان مفيدتان في Excel. 🔄 تُستخدم هذه الميزات على نطاق واسع في علم البيانات لتنظيم بياناتك وفرزها وتجميعها استنادًا إلى ظروف معينة. في فيديو تعليمي كامل اليوم حول كيفية فرز البيانات في Excel من Nooh Freestyle ، سنتحدث بشكل كامل عن كيفية استخدام هاتين الميزتين.
تعلم كيفية الفرز في Excel
كيفية فرز البيانات في Excel: دليل خطوة بخطوة
عند فرز البيانات في Excel ، من المهم تحديد ما إذا كنت تريد تطبيق الفرز على ورقة العمل بأكملها أو على نطاق من الخلايا.
في فرز ورقة العمل ، يتم تنظيم جميع بياناتك وفقًا لعمود واحد. عند تطبيق الفرز ، يتم الاحتفاظ بالمعلومات ذات الصلة في كل صف معًا. على سبيل المثال ، يمكن فرز عمود الاسم الأخير أبجديًا.
يؤدي فرز نطاق من الخلايا إلى فرز البيانات من عدة خلايا ، مما قد يكون مفيدًا عند العمل باستخدام ورقة تحتوي على جداول متعددة. لن يؤثر فرز النطاق على محتويات ورقة العمل.
ما هو الفرز في Excel؟
يعد الفرز في Excel ميزة تساعدك على تنظيم البيانات. يمكنك فرز العمود أبجديًا (AZ أو ZA). يمكننا فرز عمود رقمي من كبير إلى صغير أو صغير إلى كبير. يمكننا أيضًا فرز عمود التاريخ والوقت من الأقدم إلى الأحدث أو من الأحدث إلى الأقدم. يمكن أيضًا إجراء الفرز في Excel عن طريق قائمة مخصصة أو عن طريق التنسيق مثل لون الخلية أو لون الخط أو مجموعة الرموز. إذا كنت لا تعرف كيفية الفرز والتصفية في Excel ، فما عليك سوى البقاء معنا حتى نهاية هذه المقالة.
فرز عمود
ضع في اعتبارك مجموعة البيانات الموضحة أدناه. تحتوي ورقة البيانات هذه على معلومات حول الوكيل وتاريخ تسجيل المنزل والسعر وعدد غرف النوم وما إلى ذلك.
الآن نريد فرز البيانات حسب سعر المنزل بترتيب تنازلي.
لفرز عمود ، يمكنك تحديد البيانات واستخدام اختصار لوحة المفاتيح Ctrl + Shift + L.
أو
لفرز الأرقام في Excel ، انقر فوق السهم لأسفل في عمود List Price وحدد Sort Largest to Smallest.
الآن دعنا نفرز البيانات بناءً على التاريخ الذي قمنا بإدراج المنازل فيه.
لفرز التاريخ في Excel ، حدد مجموعة البيانات المطلوبة وانقر على خيار Sort في علامة التبويب DATA.
حدد العمود الذي تريد فرزه.
ضمن Sort On ، حدد Cell Value.
في قسم Order ، حدد Sort Oldest to Newest.
أو انتقل إلى علامة التبويب Home ، وحدد مجموعة Formats ، وانقر على Sort & Filter ، وحدد Sort Oldest to Newest.
أو يمكنك استخدام خيار AZ في علامة التبويب Data وفي مجموعة Sort & Filter.
إذا صادفت النافذة التالية أثناء الفرز ، فحدد Expand the selection وانقر على فرز.
بعد الانتهاء من عملك ، يمكنك استخدام 9 طرق لتغيير اتجاه الجدول واتجاه النص في Excel.
فرز التاريخ في Excel: حسب الشهر
ولكن قد ترغب في فرز البيانات حسب شهر معين أثناء فرز التاريخ في Excel. على سبيل المثال ، تريد تجميع أعياد ميلاد زملائك أو أقاربك. في هذه الحالة ، لا تعمل ميزة الفرز الافتراضية في Excel لأنها تعتبر العام دائمًا. الحل هو إضافة عمود إضافي ، واستخراج رقم الشهر والفرز بناءً على هذا العمود. للحصول على شهر من تاريخ ما ، يمكنك استخدام وظيفة MONTH:
باستخدام الصيغة = MONTH (B2) ، ستقوم باستخراج شهر الخلية B2.
لاحظ أنه إذا تم عرضه هنا كتاريخ بدلاً من رقم ، فيجب عليك تعيين التنسيق على خلايا الصيغة.
والآن ، قم بفرز الجدول حسب عمود الشهر. للقيام بذلك ، حدد عمود Month (C2: C8) ، انقر فوق Sort & Filter > Sort Smallest to Largest ، وحدد Expand the selection إذا رأيت النافذة المنبثقة. إذا قمت بتنفيذ الخطوات بشكل صحيح ، فسترى النتيجة على النحو التالي:
يرجى ملاحظة أنه يتم الآن تصنيف بياناتنا حسب الشهر ، مع تجاهل السنوات والأيام من كل شهر.
الفرز في Excel حسب التاريخ: الفرز حسب اليوم والشهر
لأننا شعرنا أن الفرز حسب التاريخ في Excel يحتاج إلى مزيد من الأمثلة ، نقدم لك هنا المزيد من التوضيحات.
لنفترض أننا نريد فرز تواريخ المواليد حسب الشهر واليوم في Excel. تحتاج هنا إلى صيغة تفصل بين الشهر واليوم من تاريخ الميلاد. في هذه الحالة ، ستكون وظيفة TEXT في Excel ، والتي يمكنها تحويل التاريخ إلى سلسلة نصية بالتنسيق المحدد ، مفيدة. على سبيل المثال ، يعمل تنسيق mmdd أو mm.dd.
لذلك نستخدم تاريخ العمود B2 في الصيغة التالية:
=TEXT(B2, “mm.dd”)
ثم ستقوم بفرز أعمدة الشهر واليوم من الأكبر إلى الأصغر وسنقوم بفرز البيانات بترتيب أيام كل شهر.
يمكنك الحصول على نفس النتيجة باستخدام الصيغة التالية:
=DATE(2000, MONTH(B2),DAY(B2))
تستخرج هذه الصيغة الشهر واليوم من التاريخ الذي تم إدخاله في العمود B2 ، وتستبدل السنة الحقيقية بالعام الوهمي 2000 ، وتنشئ قائمة بالتواريخ. على الرغم من أنه يمكنك استخدام أي رقم آخر بدلاً من 2000. الفكرة العامة هي أن يكون لديك عام لجميع التواريخ ثم فرز قائمة التواريخ بترتيب زمني. نظرًا لأن العام هو نفسه هنا ، يتم فرز التواريخ حسب الشهر واليوم ، وهذا بالضبط ما نبحث عنه.
تاريخ الفرز في Excel: حسب السنة
ولكن كيف يتم فرز التاريخ في Excel حسب السنة؟ عندما يتعلق الأمر بالفرز حسب السنة ، فإن أسهل طريقة هي فرز التواريخ بترتيب زمني تصاعدي (Oldest to Newest).
يتم فرز التواريخ حسب السنة ، ثم الشهر ، ثم اليوم ، كما هو موضح في الصورة أدناه.
إذا لم تكن راضيًا عن هذا الترتيب لسبب ما ، فيمكنك إضافة عمود إضافي باستخدام صيغة YEAR ، والتي تستخرج السنة من التاريخ:
=YEAR(C2)
بعد فرز البيانات استنادًا إلى العمود YEAR ، تلاحظ أنه يتم فرز التواريخ فقط بناءً على السنة ولا يتم أخذ الشهر واليوم في الاعتبار.
ملاحظة: إذا كنت تريد فرز التاريخ استنادًا إلى اليوم بغض النظر عن الشهر والسنة ، فيجب عليك استخدام الدالة DAY لاستخراج اليوم ثم فرز العمود وفقًا لذلك:
=DAY(B2)
فرز التاريخ في إكسيل: بناءً على أيام الأسبوع
تحتاج إلى عمود إضافي لفرز البيانات حسب أيام الأسبوع (كما في الأمثلة السابقة). في هذه الحالة ، نملأ العمود الإضافي بصيغة WEEKDAY ، والتي تُرجع رقمًا متعلقًا بيوم الأسبوع ، ثم يتم الفرز بناءً على العمود الإضافي.
لمدة أسبوع يبدأ من الأحد (1) إلى السبت (7) ، يتم استخدام هذه الصيغة:
=WEEKDAY(A2)
إذا كان أسبوعك يبدأ من الاثنين (1) إلى الأحد (7) ، فاستخدم الصيغة التالية.
=WEEKDAY(A2, 2)
في هذا المثال ، استخدمنا الصيغة الأولى ووصلنا إلى النتيجة التالية:
تاريخ الفرز في Excel بناءً على اسم الشهر أو اسم أيام الأسبوع
إذا كانت لديك قائمة بأسماء الأشهر في شكل نصي وليس في نموذج التاريخ ، فقد يكون استخدام الفرز التصاعدي في Excel مشكلة لأنه بدلاً من الفرز حسب ترتيب الشهر من يناير إلى ديسمبر ؛ يرتب أسماء الأشهر أبجديًا. في هذه الحالة ، سيساعدك استخدام Custom Sort.
حدد السجلات التي تريد فرزها حسب اسم الشهر.
انتقل إلى علامة التبويب Data وقسم Sort & Filter وانقر على Sort.
في صفحة Sort ، قم بإجراء الإعدادات التالية:
في قسم Column ، حدد اسم العمود الذي يحتوي على اسم الشهر.
حدد Cell Values في قسم Sort on.
في قسم Order ، حدد Custom List.
في نافذة Custom Lists ، اختر أسماء الأشهر الكاملة (January, February, March, …) أو أسماء قصيرة (Jan, Feb, Mar…) بناءً على كيفية سرد الأشهر في ورقة العمل الخاصة بك:
انقر فوق OK. انقر فوق "OK" مرة أخرى لإغلاق كلا النافذتين.
نصيحة. للفرز حسب اسم أيام الأسبوع ، أدخل الأسماء الكاملة (Sunday, Monday, Tuesday, …) أو الأسماء القصيرة (Sun, Mon, Tue…).
إذا فشلت في تنفيذ الصيغ في أي من الأقسام المذكورة أعلاه ، يمكنك الحصول على المساعدة من شرح كامل كامل حول 6 طرق لحذف جدول وتنسيقه ومحتوياته في Excel .
كيفية الفرز تلقائيًا حسب التاريخ في Excel
كما ترى ، فإن ميزة Excel Sort تتعامل مع العديد من التحديات. العيب الوحيد هو أنه ليس ديناميكيًا. هذا يعني أنك ستحتاج إلى إعادة ترتيب بياناتك عندما تتغير المعلومات الجديدة أو تُضاف. قد تتساءل عما إذا كانت هناك طريقة للفرز تلقائيًا في كل مرة يتم فيها إضافة تاريخ جديد أو إجراء تغييرات.
للإجابة على هذا السؤال ، يجب أن نقول إن أفضل طريقة للقيام بذلك هي استخدام وحدة ماكرو. ستجد أدناه بعض نماذج التعليمات البرمجية لفرز البيانات تلقائيًا حسب التاريخ.
الماكرو 1: الفرز التلقائي مع كل تغيير في ورقة العمل
يتم تنفيذ هذا الماكرو كلما حدث تغيير في ورقة العمل. من المفترض أن تكون بياناتنا في الأعمدة من A إلى C ، والتواريخ التي تريد الفرز وفقًا لها موجودة في العمود C ، بدءًا من C2. من المفترض أيضًا أن الصف 1 يحتوي على الرأس. إذا كانت سجلاتك في أعمدة مختلفة ، فقم بالإعدادات التالية:
قم بتغيير مرجع A1 إلى الخلية اليسرى العلوية من النطاق المستهدف (بما في ذلك الرؤوس).
قم بتغيير المرجع C2 إلى أعلى خلية تحتوي على التاريخ.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Range(“A1”).Sort Key1:=Range(“C2”), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
الماكرو 2: الفرز التلقائي عند إجراء تغييرات في نطاق معين
إذا كنت تعمل باستخدام ورقة عمل كبيرة تحتوي على الكثير من المعلومات ، فقد تكون إعادة الفرز مع كل تغيير في الورقة مشكلة. في هذه الحالة ، من المنطقي قصر إطلاق الماكرو على التغييرات التي تحدث ضمن نطاق معين. يفرز رمز VBA التالي البيانات فقط عند إجراء تغيير على العمود C الذي يحتوي على التاريخ.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(“C:C”)) Is Nothing Then
Range(“A1”).Sort Key1:=Range(“C2”), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
نصيحة. يمكن استخدام وحدات الماكرو هذه لفرز الأرقام في Excel (أي نوع من الأرقام ، وليس التواريخ فقط). يفرز نموذج التعليمات البرمجية أعلاه ترتيبًا تصاعديًا. إذا كنت تريد فرز الأرقام من كبير إلى صغير في Excel ، فيمكنك تغيير Order1:=xlAscending لـــ Order1:=xlDescending
كيف تضيف ماكرو إلى ورقة العمل الخاصة بك؟
نظرًا لأن كلا وحدات الماكرو تعمل تلقائيًا عند تغيير ورقة العمل ، يجب إدراج الرمز في الورقة حيث تريد فرز البيانات Sheet1) في هذا المثال). خطوات العمل كالتالي:
اضغط على مفتاحي Alt + F11 معًا لفتح VBA Editor.
على الجانب الأيسر من Project Explorer ، انقر نقرًا مزدوجًا فوق علامة التبويب التي تريد تشغيل الفرز التلقائي لها .
ضع الكود في نافذة Code.
الفرز التلقائي للأرقام في Excel بالصيغ
لفرز أرقام التاريخ تلقائيًا ، افترض أن لديك قائمة بالتواريخ وتريد فرزها تلقائيًا ترتيبًا زمنيًا في عمود منفصل بجوار القائمة الرئيسية. يمكن القيام بذلك باستخدام الصيغة التالية:
=IFERROR(INDEX($A$2:$A$20, MATCH(ROWS($A$2:A2), COUNTIF($A$2:$A$20, “<=”&$A$2:$A$20), 0)), “”)
بحيث تكون A2:A20 هي التواريخ الأصلية (التي لم يتم فرزها) وتحتوي على بعض الخلايا الفارغة للإدخالات الجديدة المحتملة.
أدخل الصيغة في خلية فارغة بجوار العمود الرئيسي أو التواريخ (C2 في هذا المثال) واضغط على Ctrl + Shift + Enter في نفس الوقت. ثم اسحب الصيغة إلى الخلايا المتبقية (في هذا المثال C2:C20).
فرز لا يعمل في Excel
تأكد من تضمين خلايا فارغة كافية في النطاق المشار إليه لفرز التواريخ المضافة حديثًا تلقائيًا. على سبيل المثال؛ التواريخ الخاصة بنا تقع في النطاق A2:A7 ولكننا نستخدم 20$2 دولارًا أمريكيًا :$A$20$. تمنع الدالة IFERROR حدوث الأخطاء في خلايا إضافية وتقوم بدلاً من ذلك بإرجاع سلسلة فارغة ("").
فرز جدول في Excel بناءً على أعمدة متعددة
تحتاج في معظم الأوقات إلى فرز عمود واحد ولكن قد تكون هناك حالات تحتاج فيها إلى فرز عمودين أو أكثر. يمكنك استخدام تقنيات الفرز المتقدمة في Excel وفرز البيانات في أعمدة متعددة.
هنا ، لفرز الجدول في Excel بناءً على عدة أعمدة ، سنقوم بفرز مجموعة بيانات الإسكان بترتيب Area السعودية و Agent التنازلي ، لهذا:
حدد مجموعة البيانات التي تريدها ، وانقر على خيار Sort في علامة التبويب Data.
حدد عمود Area للفرز.
ضمن Sort On ، حدد Cell Values.
في حقل Order ، حدد خيار AZ.
حدد Add Level وحدد عمود Agent.
الآن قم بفرز بيانات الخلية وفرزها حسب ZA.
تدريب Custom Sort في Excel
يُستخدم Custom Sort في Excel للفرز المخصص. في بعض الأحيان قد يكون من الضروري فرز البيانات التي لم يتم فرزها بترتيب تصاعدي أو أبجدي. يتيح لك Excel إنشاء قوائم مخصصة لفرز البيانات.
لنفترض أننا نريد فرز مجموعة البيانات حسب Area و S. County, Central, and N. County على التوالي.
حدد مجموعة Data ، وانقر على خيار Sort في علامة التبويب البيانات.
حدد عمود Area للفرز.
في قسم Sort On ، حدد Cell Values.
في قسم Order ، انقر فوق Custom List.
في مربع الحوار Custom List ، أضف إدخالات قائمة مفصولة بفواصل (S. County ، Central ، N. County).
انقر فوق Add ثم OK.
أدناه هو الإخراج المبين.
ما هو عامل التصفية في Excel؟
نستخدم عوامل التصفية في Excel لإخفاء بعض البيانات مؤقتًا في جدول. سيساعدك هذا في العثور على السجلات التي تلبي معايير معينة.
تصفية حسب الشرط
سنجد هنا جميع المنازل التي يتم ملء عمود "Area" بها بالكلمة Central. من أجل هذه الوظيفة:
حدد مجموعة البيانات الخاصة بك واستخدم اختصار لوحة المفاتيح Ctrl + Shift + L. أو حدد data وانتقل إلى علامة التبويب البيانات ، وانقر على أيقونة Filter من قسم Sort and Filter
انقر فوق رمز السهم في عمود Area.
قم بإلغاء تحديد "Select All" وحدد "Central".
هنا سنجد منازل بها 3 أو 4 غرف نوم. لفعل هذا:
حدد مجموعة البيانات التي تريدها ، وانقر على أيقونة Filter في علامة التبويب Data.
انقر فوق السهم الموجود بجانب عمود Bedrooms.
قم بإلغاء تحديد "Select All" وحدد المربعين 3 و 4.
فيما يلي يمكنك الاطلاع على بيانات المنازل المصفاة على أساس 3 أو 4 غرف نوم.
تصفية حسب عدة أعمدة وشروط
يمكنك استخدام مرشحات متعددة لتضييق نطاق نتائجك. يجب عليك استخدام أداة التصفية المتقدمة عند تضمين عدة أعمدة. يوفر Excel أدوات تصفية متقدمة مثل التاريخ والنص والأرقام وعوامل تصفية البحث.
في المثال أدناه ، نريد العثور على جميع المنازل التي تحتوي على حمامات سباحة في المنطقة Central ومنازل لا تحتوي على حمامات سباحة في منطقة S. County. لفعل هذا:
قم بإنشاء Criteria Range بقائمة بجميع الأعمدة في الجدول.
أضف Central و S. County تحت عمود Area.
أضف TRUE لـ Central تحت عمود Pool.
أضف FALSE لـ S. County تحت عمود Pool.
انتقل إلى علامة التبويب Data وحدد الخيار Advanced في قسم Sort and Filter.
هنا ، حدد Filter the list, in-place او Copy to another location .
List range يحتوي على البيانات الأصلية المحددة.
حدد Criteria range الذي يحتوي على الشروط الموضحة أعلاه وانقر فوق "OK".
سنبحث الآن عن المنزل الذي يحتوي على قيمة N. County في عمود Area ، و 2 في عمود Bedrooms ، و Single Family في عمود Type.
يوجد أدناه Criteria Range الذي تم إنشاؤه على سبيل المثال.
انتقل إلى علامة التبويب "Data" وانقر فوق "Advanced" في قسم "Sort & Filter".
هنا ، حدد Filter the list, in-place او Copy to another location.
List range يحتوي على البيانات الأصلية المحددة.
حدد Criteria range بالـ Area التي تحتوي على N. County، Bedroom – 2 و Type – Single Family.
انقر فوق OK.
في المثال التالي ، نريد إيجاد جميع المنازل التي يتراوح سعرها بين 300000 و 400000.
لهذا ، تحتاج إلى إنشاء عمودين لـ List Price ، أحدهما له القيمة => 300000 والآخر به القيمة = <400000.
أدخل المعايير المناسبة للنطاق في مربع Advanced Filter وانقر فوق OK وانظر النتيجة أدناه.
في المثال التالي ، نريد العثور على جميع المنازل التي لها قيمة N. في عمود المنطقة وعمود السعر أكبر من 300000 ولها قيمة 3 أو 4 في عمود Bedrooms
الصورة أدناه توضح نطاق المعايير.
فيما يلي المعلومات النهائية التي تمت تصفيتها بناءً على الظروف.
فرز الصفوف في Excel
لفرز الصفوف في Excel ، استخدم الخطوات التالية:
انقر فوق بيانات الصف التي تريد فرزها.
انتقل إلى علامة التبويب Data وحدد Sort.
الآن سترى نافذة منبثقة تظهر.
انقر فوق Options.
في قسم Orientation، حدد Sort left to right ثم انقر فوق OK.
سترى ثلاثة أعمدة. في قسم Row ، حدد الصف الذي تريد فرزه من القائمة المنسدلة (في هذا المثال ، Row1). انقر فوق "OK" عند الانتهاء.
فرز الأرقام في Excel حسب اللون
لفرز البيانات ببساطة بناءً على تنسيقات مثل لون الخلية ، نقوم بما يلي:
انقر فوق الصف الذي تريد الفرز.
انتقل إلى علامة التبويب Data في شريط الأدوات وانقر على خيار Sort.
ستظهر نافذة منبثقة. إذا كانت لديك رؤوس ، فتأكد من تحديد خيار My list has headers.
سترى ثلاثة أقسام. في قسم Column ، حدد العمود الأول الذي تريد فرزه من القائمة المنسدلة. في هذا المثال ، يتم تحديد Grades.
في قسم Sort On ، حدد Cell Color.
في قسم Order الأخير ، اختر الخيار الأخضر.
انقر فوق Add Level. كرر الخطوات المذكورة أعلاه وبدلاً من اختيار اللون الأخضر في حقل Order ، اختر اللون الأصفر.
انقر فوق Add Level مرة أخرى وكرر الخطوات المذكورة أعلاه. بدلاً من اختيار اللون الأصفر ، اختر هذه المرة اللون الأحمر في قسم Order.
انقر فوق OK.
الفرز في Excel على أساس الحروف الهجائية الفارسية
للفرز بناءً على الحروف الهجائية الفارسية في Excel ، يجب عليك تحديد جميع أعمدة الجدول التي تريدها ثم الانتقال إلى علامة التبويب "Data" من القائمة وتحديد "Sort".
مع هذا ، سيتم عرض نافذة مثل النافذة أدناه. هنا ، في قسم Column ، من قائمة Sort by ، حدد العمود الذي تريد الفرز حسب الأحرف.
من قسم Sort on ، يمكنك تحديد خيار القيم ، ومن قسم Order ، نختار الخيار A to Z.
بالنقر فوق "OK" ، ربما يمكنك الفرز في Excel أبجديًا.
لاحظ أنه للفرز حسب اللون والتاريخ للجداول الفارسية ، يجب أن تتصرف تمامًا مثل الطريقة التي شرحناها أعلاه.
تعليقاتكم واقتراحاتكم؟
في الفيديو التعليمي الكامل حول كيفية فرز البيانات في Excel اليوم ، Nooh Freestyle ، حاولنا أن نقدم لك شرحًا شاملاً للترتيب المتقدم في Excel لتقديم بعض وظائف الفرز في Excel. إذا كنت تعرف طريقة أخرى أو كانت هناك نقطة فاتتك ، فيمكنك مشاركتها معنا في قسم التعليقات في هذا المنشور.