فرز البيانات وتصفيتها في excel
يعتبر فرز البيانات وتصفيتها في excel من اهم طرق تحليل البيانات حيث ان أدوات تحليل البيانات تعتبر من المهام الرئيسية في برنامج Microsoft Excel حيث تُستخدم أداة الفرز Sort لترتيب جداول البيانات حسب معيار محدد اما أداة التصفية Filter تستخدم لعرض جزء معين من البيانات والتي ينطبق عليها معيار محدد أيضاً ، ولتوضيح الفرق بين الفرز والتصفية نوضح ذلك من خلال المثالين التاليين فمثلاً لديك جدول ببيانات الطلاب بالأسماء والصفوف وتريد عرض بيانات الطلاب الموجودين في الصف الأول فقط فهنا يمكننا استخدام أداة filter اما اذا كنت تريد ترتيب أسماء هذه الطلاب حسب الترتيب الابجدي فهنا علينا باستخدام أداة sort ، لذلك سنتعرف في درس اليوم عن شرح وافي ومبسط عن كيفية استخدام أداة الفرز sort و أداة التصفية filter في فرز البيانات وتصفيتها في excel و الخصائص المتنوعة التي تشملها تلك الاداتين .
كيفية فرز البيانات Sort
أداة الفرز sort تعتبر من اهم الأدوات الموجودة في برنامج excel وذلك نظراً لاستخداماتها المتعددة وخصائصها المتنوعة حيث انها تقوم بعملية إعادة ترتيب جدول بيانات محدد حسب معيار محدد سنتعرف الان علي مثال حي نُطبق عليه كيفية فرز sort البيانات في اكسل
اذا كان لدينا جدول يحتوي على البيانات الموضحة في الصورة التالية وهي أسماء الموظفين وتاريخ تعيينهم و مرتباتهم ونُريد عمل الاتي :
1- ترتيب الموظفين حسب تاريخ التعيين من الاقدم للأحدث
2- ترتيب الموظفين حسب الترتيب الابجدي أي أ ب ت وهكذا
3- ترتيب الموظفين حسب مرتباتهم من الأقل للأعلى
قبل أي شيء علينا ان نعرف كيف يمكننا الوصول الي أداة الفرز
هناك ثلاثة طرق يمكننا فرز البيانات sort من خلالهم ، الطريقة الاولي :
– بعد ان نقوم بتظليل الجدول المطلوب عمل فرز له .. من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار sort فرز
الطريقة الثانية للوصول لأداة sort الفرز وهي :
– بعد ان نقوم بتظليل الجدول المطلوب عمل فرز له .. من خلال الشريط الرئيسي Home في قسم Editing التحرير قم باختيار sort & filter او الفرز والتصفية ومنها custom sort فرز مخصص
الطريقة الثالثة للوصول لأداة sort الفرز وهي :
– قم بتظليل الجدول المطلوب عمل فرز له ثم قم بالضغط على كليك يمين واختر sort او فرز ثم اختر custom sort او فرز مخصص
من المطلوب في المثال السابق يتضح لنا أنواع الفرز في برنامج Excel فهذه هي المعايير التي يتم فرز البيانات على أساسها :
1- فرز من الاقدم للأحدث Sort Oldest to Newest او العكس فرز من الاحدث للأقدم Sort Newest to Oldest
2- فرز حسب الترتيب الابجدي Sort A to Z او العكس Sort Z to A
3- فرز من الأصغر الي الأكبر Sort Smallest to Largest او العكس فرز من الأكبر للأصغر Sort Largest to Smallest
أولاً : ترتيب الموظفين حسب تاريخ التعيين من الاقدم للأحدث
– بعد فتح أداة فرز البيانات بأي من الطرق السابق ذكرها ستظهر لنا هذه النافذة في الصورة التالية
بطبيعة الحال فإن الجدول الذي موجود بين أيدينا الان له مقدمة header ( وهي المكتوب بها تاريخ التعيين والاسم وخلافه .. ) لذلك سنقوم بتحديد خيار My data has header او تحتوي البيانات على رؤوس ، عندها ستظهر رؤوس الجدول معنا في قائمة sort by مخصص حسب
من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” تاريخ التعيين ”
ومن قائمة order او الترتيب اختر Oldest to Newest او من الأقدم للأحدث
ثم الضغط على ok
سنلاحظ حينها انه تم ترتيب بيانات الموظفين حسب تاريخ التعيين من الأقدم للأحدث ( ليس فقط عمود التاريخ بل جميع الاعمدة والتي تم تظليلها من البداية )
ثانياً : ترتيب الموظفين حسب الترتيب الابجدي أي أ ب ت ( ترتيب الأسماء ابجديا Excel )
1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس
2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” الاسم ”
3- ومن قائمة order او الترتيب اختر A to Z او أ الي ي
ثالثاً : ترتيب الموظفين حسب مرتباتهم من الأقل للأعلى
1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس
2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” صافي المرتب ”
3- ومن قائمة order او الترتيب اختر Smallest to Largest او من الأصغر الي الأكبر
نلاحظ مما سبق ان :
- أيا كانت طريقة الفرز المطلوبة فإن الوصول الي أداة الفرز في اكسل واحدة
- و نلاحظ انه تختلف خيارات الفرز ( order / الترتيب ) حسب نوع البيانات ( sort by / فرز حسب ) سواء كانت نص أو رقم او تاريخ حيث ان :
عندما يكون الذي يتم الفرز على اساسه نص يكون الترتيب حسب الترتيب الابجدي Sort A to Z والعكس
وعندما يكون الفرز هو ارقام يكون الترتيب Sort Smallest to Largest من الأصغر الي الأكبر والعكس
واذا كان الفرز هو تاريخ يكون الترتيب Sort Oldest to Newest من الاقدم للأحدث والعكس
خيارات متقدمة في فرز البيانات في excel
– فرز عمودين او أكثر حيث يمكننا عمل ذلك من خلال اختيار add level او إضافة مستوي واتباع نفس الخطوات الفرز
– فرز حسب قائمة مخصصة Custom List
ماذا لو ان المطلوب هو فرز بيانات الموظفين حسب القسم التابع له بحيث ان نبدأ ب باحث ثم مصحح ثم مترجم ثم مكتبية ثم سكرتارية ثم أرشيف ثم دعم فني ثم خدمات معاونة
عندئذ نقوم بعمل الاتي :
1- بعد فتح أداة sort قم بتحديد خيار My data has header او تحتوي البيانات على رؤوس
2- من قائمة sort by اختر اسم العمود الذي سيتم الترتيب على أساسه ، في حالتنا هذه يكون هو ” القسم التابع له ”
3- ومن قائمة order او الترتيب اختر قائمة مخصصة او Custom List
4-ومن النافذة التي ستظهر لك قم بكتابة الأقسام بالترتيب الذي تريده في قسم list entries او ادخالات القائمة ثم قم بالضغط علي add اضافة ثم اخترها من custom lists او قوائم مخصصة ثم الضغط علي ok ثم ok مرة اخري
وستظهر معنا القائمة بالشكل التالي :
– فرز البيانات حسب تنسيق الخلية مثل لون النص او لون الخلية وذلك من خلال الاتي :
تعديل sort on الفرز الي cell color لون الخلية لفرز البيانات حسب لون الخلية
او font color لون الخط لفرز البيانات حسب لون الخط
واختيار اللون والمكان بالأعلى او بالأسفل من هنا
– فرز الصفوف في excel
يمكننا فرز الصفوف كما نقوم بفرز الاعمدة وذلك من خلال الاتي :
– بعد فتح أداة sort الفرز قم بالضغط على Options خيارات ثم قم بتحديد خيار Sort Right to Left الفرز من اليمين الي اليسار ثم ok
مع اتباع اياً من الحالات السابقة لفرز الاعمدة
كيفية تصفية البيانات Filter
اداة التصفية filter تستخدم لعمل تصفية للبيانات وذلك من خلال إظهار مجموعة جزئية من نطاق بيانات محددة فالفرق بين الفرز والتصفية هو ان الفرز تقوم بترتيب البيانات حسب معيار او عدة معايير محددة اما التصفية فتقوم بعرض جزئي للبيانات طبقاً لمعيار او عدة معايير محددة .
طريقة الوصول لأداة التصفية Filter في اكسل
يوجد طريقتان للوصول لأداة التصفية ، الطريقة الاولي :
– بعد ان نقوم بتظليل الجدول المطلوب عمل تصفية له .. من خلال الشريط الرئيسي Home في قسم Editing التحرير قم باختيار sort & filter او الفرز والتصفية ومنها اختر Filter تصفية
الطريقة الثانية للوصول لأداة التصفية :
– بعد ان نقوم بتظليل الجدول المطلوب عمل تصفية له .. من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار filter تصفية
بعد ان نقوم بتفعيل أداة التصفية في الجدول بأي طريقة من الطريقتين السابقتين سنلاحظ ظهور هذه الأسهم في رؤوس الجدول
تطبيق مثال :
فمثلاً عندنا جدول للموظفين ( مثل الجدول السابق ) ونريد عرض الموظفين الذين يعملون بوظيفة باحث و وظيفة مصحح فقط ففي هذه الحالة علينا باستخدام أداة filter التصفية لأننا نريد عرض لجزء من جدول البيانات لذلك فنقوم بعمل الاتي :
1- استخدام أداة التصفية filter بأي طريقة من الطريقتين السابقتين
2- الضغط على السهم الصغير الموجود عند عمود ” القسم التابع له ” ثم الضغط على select all لإزالة التحديد عن الكل ثم نقوم بالتحديد على العناصر التي نريد عرضها فقط ثم الضغط على ok
ليظهر معك الجدول بالشكل التالي :
وهذه التصفية تسمي بـ ” التصفية التلقائية AutoFilter “
المعايير الخاصة بأداة filter للتصفية
وسنتناول الان المعايير الخاصة بأداة filter للتصفية والتي يتم عرض البيانات على أساسها
بعد الضغط على السهم الخاص بأداة التصفية ستظهر لنا هذه القائمة
– Filter by color التصفية حسب اللون
وهو لعمل تصفية وعرض للخلايا الملونة بلون معين او حتي التي بدون لون blank
– Number Filters عوامل تصفية الأرقام
وهي تقوم بعمل تصفية للأرقام حسب معايير محددة ، فعند الوقوف عليها ستظهر لنا هذه القائمة :
وهذا هو معني كل اختيار من هذه الاختيارات
موضوع قد يهمك : تنسيق الأرقام في Excel
وعند الضغط على أي خيار منهم يظهر لك هذه النافذة :
فيمكن أيضاً تعديل الاختيار الذي قمت باختياره من هذه القائمة من خلال المستطيل رقم 1
اما المستطيل رقم 2 تقوم باختيار منه القيمة التي سيتم الاعتماد عليها في التصفية
ثم الضغط على ok
مع العلم ان هذه القائمة تختلف عندما يكون المحتوي عبارة عن نص وتختلف أيضاً عندما يكون المحتوي عبارة عن تاريخ ، سنتعرف الان على عوامل تصفية النصوص text filters ووظيفة كل واحدة منها و أيضاً سنتعرف علي عوامل تصفية التاريخ date filters ووظيفة كل واحدة منها من خلال الصورتين التاليين
– عوامل تصفية النصوص text filters
– عوامل تصفية التاريخ date filters
موضوع ذو صلة : كيفية تعديل صيغة التاريخ Excel
موضوع ذو علاقة يمكنك الاطلاع علي :
اسهل طريقة لحذف التكرار في excel
بقا لنا امر اخر لم يتم شرحه عن موضوع ” فرز البيانات وتصفيتها في excel ” في هذه الدرس لعدم إطالة الدرس اكثر من اللازم وهو ” التصفية المتقدمة للبيانات في الاكسل ” نظراً لأهميته وعلاقته ببرمجة الماكرو في الاكسل
ملاحظات علي التصفية في اكسل
1- إذا كانت هناك بعض الخلايا الفارغة في الجدول سيقوم البرنامج بتضمين هذا الخيار في قائمة التصفية بإسم ( Blanks ) او (فراغات)
2- لإيقاف التصفية التلقائية Auto filter بشكل تام من خلال تبويب Data بيانات في قسم sort & filter فرز وتصفية قم باختيار خيار filter تصفية
3- لعمل جدول بيانات بدون حدوث اخطاء بالاخص مع اداة filter يُفضل إلغاء اي دمج merge في الخلايا
موضوعات قد تُهمك :
– شرح مبسط عن دالة IF في excel بالأمثلة