فرز البيانات وتصفيتها في Microsoft Excel 2
فرز البيانات وتصفيتها في الاكسل، تُعدّ هاتان الخاصيتان من أكثر الخصائص استخدامًا في اكسل، خاصةً مع جداول البيانات الكبيرة. حيث تتيحان لك التحكم بشكل دقيق في كيفية عرض البيانات، والتركيز على المعلومات ذات الصلة، وتحليلها بكفاءة أكبر.
فرز البيانات |
يعتبر الفرز والتصفية من أروع أدوات تحليل البيانات التي يوفرها اكسل، تأتي وظيفتا الفرز Sort والتصفية Filter، كأدوات ذكية لتنظيم البيانات وجعلها أكثر وضوحًا وقابلية للفهم.
في الدرس السابق، أخذنا فرز البيانات وتصفيتها في Microsoft Excel
وتم شرح عملية التصفية وكيف نقوم بالتصفية في حالة الخلايا الرقمية وفي حالة الخلايا الأخرى، واليوم سنستكمل درسنا وسنأخذ عملية الفرز وخيارات الفرز والتصفية التلقائية والتصفية المتقدمة.
ماهي خيارات الفرز؟
في المثال السابق قمنا بفرز عمود يحتوي على بيانات رقمية عادية. يمكنك عمل فرز لبيانات رقمية بأنواع أخرى (كالتواريخ أو الأوقات)، أو فرز البيانات النصية. وتختلف خيارات الفرز كل حسب نوع البيانات. مثلا لو أردنا فرز البيانات في عمود "اسم الدورة"، وهي بيانات نصية، ستكون خيارات الفرز حسب الأبجدية؛ فرز من الألف إلى الياء Sort A to Z، أو فرز من الياء إلى الألف Sort Z to A. سنقوم بعمل فرز من الألف إلى الياء:
خيارات الفرز |
عند قيامك بعمل عملية فرز للبيانات في عمود "تاريخ البداية"، وهي بيانات رقمية بصيغة تاريخ، ستحتاج إلى تحديد خيار الفرز المناسب. بناءً على ما تم ذكره، يمكنك اختيار الفرز من الأحدث إلى الأقدم "Sort Newest to Oldest". باستخدام هذا الخيار، سيتم عرض البيانات بترتيب يبدأ من الأحدث ويتجه نحو الأقدم وفقًا لقيم تاريخ البداية. بمعنى آخر، ستظهر السجلات بحيث يكون لديك الأحدث منها في البداية والأقدم في النهاية.
خيارات الفرز |
يمكن الوصول إلى خيارات فرز متقدمة عن طريق تبويب "بيانات" (Data) في برامج جداول البيانات. يمكنك اتباع الخطوات التالية للوصول إلى تلك الخيارات:
حدد خلية في النطاق الذي ترغب في فرزه، أو حدد الجدول بأكمله إذا كنت ترغب في فرز المزيد من الأعمدة.
- انتقل إلى تبويب "بيانات" (Data) في شريط الأوامر العلوي لبرنامج جداول البيانات.
- ابحث عن خيار "فرز" (Sort) وانقر عليه. قد يكون موجودًا في مجموعة أدوات "ترتيب وتصفية" (Sort & Filter) أو قد يكون لديه أيقونة خاصة به.
- بعد النقر على "فرز" (Sort)، ستظهر لك نافذة خيارات الفرز. يمكنك تحديد العمود الأول الذي ترغب في فرزه وتحديد ترتيب الفرز، سواء "من الأكبر إلى الأصغر" (Largest to Smallest) أو "من الأصغر إلى الأكبر" (Smallest to Largest).
- إذا كنت ترغب في فرز عمودين أو أكثر معًا، فيمكنك إضافة معايير فرز إضافية. تحديد العمود الثاني الذي ترغب في فرزه، وحدد ترتيب الفرز له أيضًا.
بعد تحديد جميع المعايير المطلوبة، انقر على زر "موافق" (OK) لتنفيذ عملية الفرز. باستخدام هذه الخطوات، يمكنك استخدام خيارات الفرز المتقدمة في برامج جداول البيانات لتنفيذ عمليات الفرز بشكل مخصص ووفقًا لمعايير متعددة.
تحتوي البيانات على عناوين" (My Data has headers) هو خيار مفيد عند استخدام أمر الفرز. عند تحديده، سيتم اعتبار الصف الأول في الجدول كعناوين لأعمدة البيانات، وستظهر هذه العناوين في خانة "فرز حسب" (Sort By) لتمكينك من تحديد العمود الذي ترغب في فرز البيانات بناءً على قيمه.
بهذا الخيار، يصبح من السهل تحديد العمود المناسب للفرز دون الحاجة إلى الاعتماد على مواقع الخلايا أو تحديد العمود بواسطة رقمه. يمكنك ببساطة اختيار العنوان المناسب للعمود من القائمة المنسدلة في خانة "فرز حسب" (Sort By) لتنفيذ عملية الفرز المطلوبة، هذا يساعد في تجنب الأخطاء ويسهل عملية فهم وتحليل البيانات، حيث يتم توضيح أسماء الأعمدة المرتبطة بالبيانات في الجدول.
الفرز بواسطة Sort By |
عندما تقوم بتحديد خيار "تحتوي البيانات على عناوين" (My Data has headers)، يصبح من السهل عليك تحديد العمود الذي ترغب في فرزه. فعندما يتم تحديد هذا الخيار، ستظهر أسماء الأعمدة الفعلية في خانة "فرز حسب" (Sort By)، وبالتالي يمكنك بسهولة تحديد العمود الذي يحتوي على البيانات التي ترغب في فرزها.
وبالمقابل، عندما يتم إلغاء تحديد هذا الخيار، ستظهر عناوين الأعمدة بأسمائها الافتراضية مثل "العمود A" و "العمود B"، وهذا قد يسبب بعض الالتباس، خاصةً عندما يكون لديك جدول كبير يحتوي على العديد من الأعمدة، وقد يكون من الصعب تذكر أي عمود يحتوي على البيانات التي ترغب في فرزها.
لذا من الناحية العملية ولتجنب الالتباس، يعد استخدام خيار "تحتوي البيانات على عناوين" مفيدًا جدًا لتحديد العمود المناسب للفرز وتنفيذ العملية بسهولة ودقة.
الفرز في الاكسل |
سنقوم بتنفيذ عملية الفرز حسب الأبجدية لعمود "اسم الدورة"، ثم سنقوم بترتيب العمود "تاريخ البداية" من الأقدم إلى الأحدث.
لذا، في خانة "فرز حسب" (Sort By)، سنحدد "اسم الدورة" (Course Name)، وفي خانة الترتيب (Order)، سنحدد "من الألف إلى الياء" (A to Z). بعد ذلك، سنضيف مستوى فرز آخر بالنقر على "إضافة مستوى" (Add Level). في المستوى الثاني، سنحدد "تاريخ البداية" (Start Date) في خانة "فرز حسب" (Then By)، وفي خانة الترتيب (Order)، سنحدد "من الأقدم إلى الأحدث" (Oldest to Newest).
|
وستكون نتيجة الفرز (لاحظ عمودي "اسم الدورة" و"تاريخ البداية")
نتيجة الفرز |
يمكنك عمل فرز حسب قائمة مخصصة تضيفها بنفسك. لنفترض أنك ترغب في فرز العمود "الحالة" باستخدام ترتيب محدد، حيث يتم عرض البيانات المرتبطة بالتوقف أولاً، ثم النجاح، ثم الفشل.
إليك الخطوات:
1. انتقل إلى تبويب "بيانات" (Data) في شريط الأوامر العلوي لبرنامج جداول البيانات.
2. انقر على زر "فرز" (Sort) لفتح نافذة خيارات الفرز.
3. في خانة "فرز حسب" (Sort By)، حدد العمود "الحالة" (Status) الذي ترغب في فرزه.
4. في خانة "الترتيب" (Order)، اختر "قائمة مخصصة" (Custom List).
5. بعد الخطوة السابقة، ستظهر خانة إضافية تسمى "قائمة مخصصة" (Custom List). انقر فوق الزر "إدارة القوائم المخصصة" (Manage Custom Lists).
6. في نافذة "إدارة القوائم المخصصة" (Manage Custom Lists)، انقر على زر "إضافة" (Add) لإنشاء قائمة مخصصة جديدة.
7. في القائمة المخصصة الجديدة، أدخل القيم "توقف"، "نجاح"، "اخفاق" بالترتيب المطلوب.
8. انقر على زر "موافق" (OK) لإغلاق نافذة "إدارة القوائم المخصصة" (Manage Custom Lists).
9. انقر مرة أخرى على زر "موافق" (OK) لتنفيذ عملية الفرز.
بهذه الطريقة، ستقوم بتنفيذ عملية الفرز حسب العمود "الحالة" باستخدام القائمة المخصصة التي تحددتها، حيث يتم عرض البيانات المرتبطة بالتوقف أولاً، ثم النجاح، ثم الفشل.
خيارات الفرز |
لإضافة قائمة مخصصة في حقل "List entries"، قم بإدخال القيم الواحدة تلو الأخرى واضغط على مفتاح "Enter" بين كل قيمة. يمكنك أيضًا كتابة القيم على سطر واحد مع فاصلة بدلاً من استخدام مفتاح "Enter". في مثالنا، يمكنك إدخال القيم "توقف"، "نجاح"، و "اخفاق" في حقل "List entries". بعد ذلك، انقر على "إضافة" (Add) ومن ثم "موافق" (OK) لإنشاء القائمة المخصصة.
الترتيب حسب القائمة |
وسيكون الترتيب حسب القائمة التي قمنا بإضافتها، كالتالي:
|
يمكنك أيضًا الفرز حسب تنسيق الخلية، سواء كان ذلك تنسيق النص أو لون الخلية.
إليك الخطوات لفرز العمود حسب تنسيق اللون في الأعلى:
1. في نافذة الفرز، حدد العمود الذي ترغب في فرزه حسب تنسيق اللون.
2. في خانة "فرز حسب" (Sort by)، حدد العمود المراد الفرز عليه.
3. في خانة "فرز على أساس" (Sort On)، حدد "تنسيق الخلية" (Cell Color).
4. في خانة "الترتيب" (Order)، حدد "على الأعلى" (On Top) لعرض الخلايا ذات التنسيق المحدد في الجزء العلوي.
5. انقر على "موافق" (OK) لتنفيذ عملية الفرز.
بهذه الطريقة، سيتم فرز العمود حسب تنسيق الخلية، وسيتم عرض الخلايا ذات التنسيق المحدد (مثل الخلايا ذات اللون الأحمر في هذا المثال) في الأعلى.
الفرز في الاكسل |
بالتأكيد! للتعامل مع اللونين المتبقيين (الأصفر وبدون لون)، يمكننا نسخ المستوى لتحديد ترتيبهما.
هنا هي الخطوات لنسخ المستوى وتحديد ترتيب الألوان:
1. انقر على "نسخ المستوى" (Copy level) من القائمة.
2. حدد اللون وترتيبه للألوان المتبقية، وفي هذه الحالة اللون الأصفر.
3. انقر على "موافق" (OK) لتنفيذ النسخ وتحديد ترتيب الألوان.
سيتم نسخ المستوى وتحديد ترتيب اللون الأصفر، وبدون لون سيأتي بعد اللون الأحمر في الأعلى. وبالتالي، سيكون اللون الأصفر هو آخر لون في الترتيب.
نسخ المستوى |
وستكون النتيجة كالتالي:
فرز البيانات في الاكسل |
كما يمكنك أن تقوم بفرز الصفوف كما تقوم بفرز الأعمدة بالخطوات التالية:
بيانات Data > فرز Sort > خيارات Options > فرز من اليمين إلى اليسار Sort Right to Left > موافق OK
في حالة حدوث خطأ أو يحب المستخدم أن يراجع عن الفرز عليه أن يستخدم زر التراجع في شريط الوصول السريع أو الاختصار Ctrl+Z.
إذا رغبت في التراجع عن أمر الفرز استخدم زر التراجع في شريط الوصول السريع، أو استخدم الاختصار Ctrl+Z. التصفية التلقائية، كما هو الحال في الفرز، تتوفر عدة خيارات في أمر التصفية. إذ تستطيع تصفية نطاق الخلايا أو الجدول حسب معايير محددة كل حسب نوع البيانات في العمود.
في البداية حدد خلية داخل الجدول أو نطاق الخلايا ثم انقر فرز وتصفية Sort & filter من تبويب الصفحة الرئيسية Home، ثم اختر تصفية Filter لإظهار أسهم التصفية التلقائية AutoFilter. سنطبق بعض الأمثلة لتوضيح خيارات التصفية.
إذا كانت بعض الخلايا فارغة في العمود سيقوم البرنامج بتضمين هذا الخيار في خيارات التصفية:
|
في المثال أعلاه لدي بعض الخلايا الفارغة في عمود "الرقم التعريفي للمتدرب" وأرغب في تصفية البيانات لعرض الصفوف المقترنة بهذه الخلايا فقط. سأقوم بالنقر على السهم في عنوان العمود، ثم ألغي تأشير تحديد الكل Select All وأقوم بتأشير الفراغات Blanks فقط:
فرز من اليمين إلى اليسار |
وسيقوم البرنامج بعرض الصفوف المقترنة بالخلايا الفارغة فقط:
فرز |
بالإضافة إلى ذلك هناك معايير محددة لتصفية البيانات تختلف حسب نوع تلك البيانات. فإذا كانت البيانات رقمية، انقر فوق سهم التصفية التلقائية الخاص بالعمود الذي يحتوي على بيانات رقمية، أشر فوق عوامل تصفية الأرقام Number Filters، ثم اختر أحد المعايير المتاحة:
خيارات الفرز |
مثال: سنقوم بتصفية عمود "العمر" لعرض الأعمار أكبر من 30 سنة باختيار المعيار أكبر من Greater Than، وإدخال الرقم 30 في مربع حوار تصفية تلقائية مخصصة:
وإذا كانت البيانات رقمية بصيغة تاريخ انقر فوق سهم التصفية التلقائية الخاص بالعمود الذي يحتوي على تواريخ، أشر فوق عوامل تصفية التاريخ، ثم اختر أحد المعايير المتاحة:
مثال: سنقوم بتصفية عمود "تاريخ البداية" لعرض التواريخ بين 1/5/2011 و1/12/2011 باختيار المعيار بين Between، ثم إدخال التاريخين المحددين في مربع حوار تصفية تلقائية مخصصة:
ونفس الطريقة تطبق على بقية أنواع البيانات.
وكذلك يمكنك تصفية نطاق الخلايا أو الجدول حسب تنسيق الخلية كلون النص أو لون الخلية إذا كانت الخلايا منسقة بعدة ألوان، كما في عمود "الحالة" في مثالنا:
![]() |
فرز البيانات في الاكسل |
ولديك أيضا خيار تطبيق أمر التصفية أكثر من مرة على نفس نطاق الخلايا أو الجدول.
مثال:
أرغب في عرض المتدربين الناجحين في دورات اللغة الإنجليزية. سأقوم أولا بتصفية الجدول حسب اللون باختيار معيار بلا تعبئة No Fill:
ثم سأقوم بإلغاء تأشير تحديد الكل Select All في عمود "اسم الدورة" وأبقي على قيمة "اللغة الإنجليزية"، وبذلك سيتم عرض الصفوف حسب المعيارين. لاحظ أن البرنامج يخبرنا في شريط الحالة عن عدد الصفوف المطابقة للمعايير (2 من أصل 13 صف في هذا المثال).
![]() |
عرض الصفوف حسب معيار معين |
لإلغاء التصفية من عمود معين انقر على أيقونة القمع واختر مسح عامل التصفية من Clear Filter From، ولإيقاف التصفية التلقائية بشكل تام انقر فوق فرز وتصفية Sort & Filter من تبويب الصفحة الرئيسية Home واختر تصفية Filter:
التصفية المتقدمة.
في التصفية المتقدمة تتوفر المزيد من الخيارات لتصفية البيانات، كتصفية القيم الفريدة unique values أو استخدام المعاملات operators مثل المعامل أو OR.
في الجدول أدناه توجد قيمة فريدة (متكررة) وهي المميزة باللون الأخضر. هنا لدينا خياران، أما تصفية هذه القيمة من خيارات التصفية المتقدمة أو إزالتها من الجدول بشكل نهائي. لتصفية القيم الفريدة من نطاق الخلايا أو الجدول اذهب إلى تبويب بيانات Data، ثم انقر فوق خيارات متقدمة Advanced:
![]() |
Advanced |
في مربع حوار تصفية متقدمة، ومن خانة List range حدد نطاق الخلايا/العمود الذي يحتوي على القيم الفريدة بالنقر والسحب من أول خلية في النطاق/العمود إلى آخر خلية. بعد تحديد الخلايا حدد خيار السجلات الفريدة فقط Unique records only وانقر موافق OK:
![]() |
التصفية المتقدمة |
في المثال أعلاه قمت بتحديد عمود "العمر"، علما أنني سأحصل على النتيجة نفسها فيما لو قمت بتحديد أحد الأعمدة الأخرى لأن جميعها تحتوي على قيمة فريدة:
![]() |
التصفية المتقدمة Advanced |
لاحظ كيف قام البرنامج بتصفية القيمة الفريدة. الخيار الآخر هو استخدام أمر إزالة التكرارات Remove Duplicates في تبويب بيانات Data، لكن في هذه الحالة سيتم حذف القيم المتكررة وليس تصفيتها فقط:
Remove Duplicates |
تستطيع أيضا استخدام التصفية المتقدمة للتصفية حسب معايير متعددة. لكن أولا عليك أن تقوم بإنشاء نطاق المعايير. ويجب أن تكون الخلايا العليا لنطاق المعايير هي نفسها عناوين الأعمدة التي تريد تصفيتها حسب تلك المعايير، في المثال أدناه قمت بإنشاء نطاق المعايير لعرض الصفوف التي تحتوي على "عمر" مساو لـ"30" و"حالة" مساوية لـ"نجاح"
لاحظ أنني قمت بإنشاء الخلايا العليا لنطاق المعايير بنفس عناوين الأعمدة التي أريد تصفيتها حسب تلك المعايير، وهي "العمر" و"الحالة". لتصفية الجدول حسب نطاق المعايير هذا انقر فوق خيارات متقدمة من تبويب بيانات. في مربع حوار تصفية متقدمة حدد الجدول بأكمله في خانة List Range، وحدد نطاق المعايير في خانة Criteria range ثم انقر موافق OK:
![]() |
فرز البيانات |
سيقوم البرنامج بعرض الصفوف التي تحقق المعيارين معا، وليس أحدهما.
|
أما إذا كنت تريد عرض الصفوف التي تحقق أحد المعيارين أو كلاهما، أي استخدام المعامل OR، قم بترتيب الخلايا في نطاق المعايير بشكل قطري، كما في الشكل:
|
انقر فوق خيارات متقدمة من تبويب بيانات واتبع نفس الطريقة السابقة. سيقوم البرنامج بعرض الصفوف التي تحتوي على "عمر" مساو لـ"30" أو "حالة" مساوية لـ"نجاح"
الفرز |
لإزالة التصفية انقر فوق مسح Clear من تبويب بيانات Data. يمكنك استخدام معايير أكثر تعقيدا، مثلا معيار "أكبر من" بدلا من معيار "مساو" الذي يطبق بشكل افتراضي والذي استخدمناه في المثال السابق. مثلا إذا أردنا عرض "العمر" أكبر من "30" سنستخدم علامة أكبر مع القيمة "30" في نطاق المعايير ليصبح بالشكل التالي:
خيارات الفرز |
وسنتبع نفس الطريقة بالنقر فوق خيارات متقدمة وتحديد نطاق الخلايا ونطاق المعايير لعرض الصف الذي يحقق المعيارين "أكبر من 30" و"نجاح"، يمكنك أيضا إنشاء معايير أخرى باستخدام أحرف البدل wildcard characters مثل النجمة (*) علامة الاستفهام (؟) أو الأسهم (<>) كما في المثال:
|
يستخدم المعيار الموضح في المثال أعلاه لعرض القيم في عمود "الحالة" مع البيانات المقترنة بها والتي لا تنتهي بحرف الحاء. هذه المرة سنقوم بعرض نتيجة التصفية في مكان آخر وليس تصفية الجدول الأصلي نفسه، كالتالي:
انقر فوق خيارات متقدمة من تبويب بيانات. في مربع حوار تصفية متقدمة حدد خيار النسخ إلى موقع آخر Copy to another location. في خانة List range حدد الجدول بأكمله، في خانة Criteria range حدد نطاق المعايير (أي الخليتين H1 + H2 في هذا المثال)، وفي خانة Copy to حدد الخلية التي تريد نقل نتيجة التصفية إليها (الخلية H8 في هذا المثال) ثم انقر موافق:
نتشرف بتعليقاتكم ووضع آرائكم