الفرز Sort والتصفية Filter من أدوات تحليل البيانات الرائعة التي يوفرها اكسل وهي من أكثر الخصائص المستخدمة، وتكون مفيدة جدًّا في جداول البيانات الكبيرة على وجه الخصوص.
يستخدم أمر الفرز لترتيب البيانات في نطاق من الخلايا أو جدول حسب معيار محدد. ويستخدم أمر التصفية لعرض مجموعة جزئية من البيانات في نطاق من الخلايا أو جدول والتي ينطبق عليها معيار محدد أيضا.
سنستخدم الجدول التالي كمثال لتطبيق أوامر الفرز والتصفية وتوضيحها:
لعمل فرز لمحتويات أي عمود، انقر على أحد الخلايا داخل العمود بزر الفأرة الأيمن، ثم اذهب إلى الخيار فرز Sort لعرض الخيارات المتاحة:
تختلف خيارات الفرز حسب نوع البيانات سواء كانت نصية أو رقمية، وبما أنّ البيانات في عمود "العمر" رقمية فالخيارات المتاحة هي فرز من الأكبر إلى الأصغر Sort Largest to Smallest، وفرز من الأصغر إلى الأكبر Sort Smallest to Largest، وهذه هي الخيارات لأغلب البيانات الرقمية.
سنقوم بعمل فرز من الأكبر إلى الأصغر، وسيقوم البرنامج بترتيب الأعمار من الأصغر إلى الأكبر مع الاحتفاظ بما يقابلها من البيانات في بقية الأعمدة:
لعمل تصفية للبيانات بإظهار مجموعة جزئية من نطاق البيانات، قم بتحديد خلية داخل أحد اللأعمدة، ثم انقر على فرز وتصفية Sort & Filter من تبويب الصفحة الرئيسية Home واختر تصفية Filter:
عند تطبيق أمر تصفية ستظهر أسهم في عناوين الأعمدة يمكنك استخدامها لعمل تصفية، علما أنّ هذه الأسهم ستكون موجودة من الأصل فيما لو قمت بتنسيق البيانات بصيغة جدول Table (في هذا المثال قمت بعمل حدود وتنسيقات للخلايا ولكن لم أقم بتنسيقها بصيغة جدول).
انقر على السهم في عناوين أحد الأعمدة التي تريد عمل تصفية لها، ثم قم بتأشير الخيارات التي تريد عرضها.
في مثالنا هذا سأقوم بعمل تصفية لعمود "اسم الدورة" لإظهار مجموعة "اللغة الإنجليزية" فقط. لذلك سأقوم بإلغاء تأشير تحديد الكل Select All وأقوم بتأشير "اللغة الإنجليزية":
وسيقوم البرنامج بعرض نطاق الخلايا المرتبط باللغة الإنجليزية فقط (لاحظ ظهور أيقونة "قمع" في عنوان عمود "اللغة الإنجليزية" إشارة لتصفيته):
خيارات الفرز
في المثال السابق قمنا بفرز عمود يحتوي على بيانات رقمية عادية. يمكنك عمل فرز لبيانات رقمية بأنواع أخرى (كالتواريخ أو الأوقات)، أو فرز البيانات النصية. وتختلف خيارات الفرز كل حسب نوع البيانات.
مثلا لو أردنا فرز البيانات في عمود "اسم الدورة"، وهي بيانات نصية، ستكون خيارات الفرز حسب الأبجدية؛ فرز من الألف إلى الياء Sort A to Z، أو فرز من الياء إلى الألف Sort Z to A. سنقوم بعمل فرز من الألف إلى الياء:
وإذا قمنا بعمل فرز للبيانات في عمود "تاريخ البداية"، وهي بيانات رقمية بصيغة تاريخ، ستكون خيارات الفرز من الأقدم إلى الأحدث Sort Oldest to Newest، أو فرز من الأحدث إلى الأقدم Sort Newest to Oldest. سنقوم بعمل فرز من الأحدث إلى الأقدم:
هناك خيارات أخرى متقدمة للفرز، كفرز عمودين أو أكثر معا، يمكنك الوصول إليها من تبويب بيانات. أولا عليك تحديد خلية في نطاق الخلايا أو الجدول، اذهب إلى تبويب بيانات Data وانقر فوق فرز Sort لفتح نافذة خيارات الفرز:
من الخيارات المفيدة لأمر فرز هو خيار تحتوي البيانات على عناوين My Data has headers. ويقصد بها عناوين أعمدة الجدول (أي "الرقم التعريفي للمتدرب"، "العمر"، إلخ في هذا المثال). عند تأشير هذه الخيار ستظهر عناوين الأعمدة في خانة فرز حسب Sort By:
وهذا الأمر يسهل اختيار العمود الذي تريد فرزه. لأنه عند إلغاء تأشير هذا الخيار ستظهر عناوين الأعمدة بأسمائها الأصلية (Column A، Column B، إلخ) وقد يؤدي ذلك إلى الالتباس عندما يكون الجدول كبيرا ولا تتذكر أي عمود هو الذي يحتوي على البيانات التي تريد فرزها.
سنقوم هنا بعمل فرز لعمودي "اسم الدورة" و"تاريخ البداية"، بحيث يتم ترتيب العمود الأول حسب الأبجدية أولا، بعدها يتم ترتيب العمود الثاني من الأقدم إلى الأحدث.
في خانة فرز حسب Sort سنحدد "اسم الدورة"، وفي خانة الترتيب Order سنحدد من الألف إلى الياء A to Z. ثم سنقوم بإضافة مستوى فرز آخر للعمود الثاني Add Level. في المستوى الثاني سنحدد "تاريخ البداية في خانة ثم حسب Then By، وفي خانة الترتيب سنحدد من الأقدم إلى الأحدث Oldest to Newest:
وستكون نتيجة الفرز كالتالي (لاحظ عمودي "اسم الدورة" و"تاريخ البداية")
ليس هذا فحسب، إذ تستطيع عمل فرز حسب قائمة مخصصة تضيفها بنفسك. مثلا لو أردنا فرز عمود "الحالة" ستكون الخيارات المتاحة هي فرز حسب الأبجدية (أي اخفاق، توقف، ثم نجاح) لأنّ البيانات نصية. لكن أرغب هنا بعمل ترتيب بحيث تُعرض البيانات المرتبطة بالتوقف أولا، ثم النجاح، ثم الإخفاق. والخطوات هي كالتالي:
من تبويب بيانات Data انقر فوق فرز Sort. في نافذة فرز ومن خانة فرز حسب Sort By قم بتحديد العمود الذي تريد فرزه (عمود "الحالة" في هذا المثال). ومن خانة الترتيب اختر قائمة مخصصة Custom List:
في حقل List entries قم بإدخال أول قيمة ثم اضغط Enter، ادخل ثاني قيمة ثم اضغط Enter وهكذا لحين الانتهاء من كتابة جميع القيم في العمود الذي تريد فرزه (أو اكتب قيم القائمة على شكل سطر تفصلها فارزة بدلا من استخدام مفتاح Enter). في مثالنا، ستكون أول قيمة "توقف"، ثاني قيمة "نجاح"، وثالث قيمة "اخفاق". بعدها انقر فوق إضافة Add ثم موافق OK:
وسيكون الترتيب حسب القائمة التي قمنا بإضافتها، كالتالي:
يمكنك أيضا أن تقوم بالفرز حسب تنسيق الخلية، كلون النص أو لون الخلية.
من نافذة فرز حدد العمود الذي تريد فرزه حسب التنسيق (يجب أن تكون خلايا منسقة بنسق معين) من خانة فرز حسب Sort by. ومن خانة فرز Sort On حدد التنسيق الذي تريد فرز العمود على أساسه سنختار لون الخلية مثلا. ومن خانة الترتيب Order اختر اللون وترتيبه. سأقوم هنا بعرض الخلايا باللون الأحمر في الأعلى On Top:
أما اللونان المتبقيان (الأصفر وبلا لون) فسنقوم بنسخ المستوى لتحديد ترتيبهما. انقر فوق نسخ المستوى Copy level ثم حدد اللون وترتيبه. سأقوم بعرض الخلايا بلا لون بعد اللون الأحمر، أي سيكون في الأعلى أيضا وبذلك سيكون اللون الأصفر هو اللون الأخير في الترتيب:
وستكون النتيجة كالتالي:
يمكنك أيضا أن تقوم بفرز الصفوف كما تقوم بفرز الأعمدة لكن يجب أولا أن تعد البرنامج لذلك. من تبويب:
بيانات Data > فرز Sort > خيارات Options > فرز من اليمين إلى اليسار Sort Right to Left > موافق OK
ثم اتبع نفس خطوات فرز الأعمدة:
إذا رغبت في التراجع عن أمر الفرز استخدم زر التراجع في شريط الوصول السريع، أو استخدم الاختصار 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:
في مربع حوار تصفية متقدمة، ومن خانة List range حدد نطاق الخلايا/العمود الذي يحتوي على القيم الفريدة بالنقر والسحب من أول خلية في النطاق/العمود إلى آخر خلية. بعد تحديد الخلايا حدد خيار السجلات الفريدة فقط Unique records only وانقر موافق OK:
في المثال أعلاه قمت بتحديد عمود "العمر"، علما أنني سأحصل على النتيجة نفسها فيما لو قمت بتحديد أحد الأعمدة الأخرى لأن جميعها تحتوي على قيمة فريدة:
لاحظ كيف قام البرنامج بتصفية القيمة الفريدة. الخيار الآخر هو استخدام أمر إزالة التكرارات Remove Duplicates في تبويب بيانات Data، لكن في هذه الحالة سيتم حذف القيم المتكررة وليس تصفيتها فقط:
تستطيع أيضا استخدام التصفية المتقدمة للتصفية حسب معايير متعددة. لكن أولا عليك أن تقوم بإنشاء نطاق المعايير. ويجب أن تكون الخلايا العليا لنطاق المعايير هي نفسها عناوين الأعمدة التي تريد تصفيتها حسب تلك المعايير.
في المثال أدناه قمت بإنشاء نطاق المعايير لعرض الصفوف التي تحتوي على "عمر" مساو لـ"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 في هذا المثال) ثم انقر موافق:
وبالتالي ستعرض الصفوف التي لا تنتهي قيمها بحرف الحاء بجدول آخر خارج الجدول الأصلي:
أفضل التعليقات
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.