فرز البيانات في Excel حسب الصفوف والأعمدة باستخدام الصيغ. فرز البيانات في الاستعلام

يعد فرز البيانات في Excel ميزة مفيدة جدًا، ولكن يجب استخدامها بحذر. إذا كان الجدول الكبير يحتوي على صيغ ودوال معقدة، فمن الأفضل إجراء عملية الفرز على نسخة من هذا الجدول.

أولاً، في الصيغ والوظائف قد يتم انتهاك الاستهداف في الروابط ومن ثم ستكون نتائج حساباتها خاطئة. ثانيًا، بعد الفرز المتكرر، يمكنك خلط بيانات الجدول بحيث يكون من الصعب العودة إلى شكلها الأصلي. ثالثا، إذا كان الجدول يحتوي على خلايا مدمجة، فيجب عليك فصلها بعناية، لأن هذا التنسيق غير مقبول للفرز.

فرز البيانات في Excel

ما الأدوات التي يستخدمها Excel لفرز البيانات؟ لإعطاء إجابة كاملة على هذا السؤال، دعونا نفكر فيه باستخدام أمثلة محددة.

إعداد جدول لفرز البيانات بشكل صحيح وآمن:

الآن لا يحتوي جدولنا على صيغ، ولكن فقط نتائج حساباتها. يتم أيضًا قطع اتصال الخلايا المدمجة. كل ما تبقى هو إزالة النص الزائد في العناوين ويصبح الجدول جاهزًا للفرز الآمن.

لفرز جدول بأكمله بناءً على عمود واحد، قم بما يلي:

البيانات مرتبة في جميع أنحاء الجدول بالنسبة لعمود "صافي الربح".



كيفية فرز عمود في إكسل

لنقم الآن بفرز عمود واحد فقط دون الرجوع إلى الأعمدة الأخرى والجدول بأكمله:

يتم فرز العمود بشكل مستقل عن الأعمدة الأخرى في الجدول.

فرز حسب لون الخلية في Excel

عندما نقوم بنسخ جدول إلى ورقة منفصلة، ​​فإننا ننقل قيمه فقط باستخدام لصق خاص. لكن إمكانيات الفرز تسمح لنا بالفرز ليس حسب القيم فحسب، بل حتى حسب ألوان الخطوط أو ألوان الخلايا. لذلك، نحتاج أيضًا إلى نقل تنسيقات البيانات. لهذا:

تحتوي نسخة الجدول الآن على القيم والتنسيقات. دعونا فرز حسب اللون:

1. حدد الجدول وحدد أداة "البيانات" - "الفرز".
2. في خيارات الفرز، حدد مرة أخرى المربع "تحتوي بياناتي على عناوين الأعمدة" وأشر إلى: "العمود" - صافي الربح؛ "الفرز" - لون الخلية؛ "الطلب" - أحمر، في الأعلى. وانقر فوق موافق.

في الأعلى لدينا الآن أسوأ أرقام صافي الربح أداءً.

ملحوظة. ثم يمكنك تحديد النطاق A4:F12 في هذا الجدول وتكرار الخطوة الثانية من هذا القسم، فقط حدد اللون الوردي في الأعلى. وبالتالي، فإن الخلايا ذات اللون سوف تذهب أولا، ثم العادية.

يعد الفرز في Excel وظيفة مضمنة يمكن للمستخدم من خلالها ترتيب البيانات في أعمدة على الورقة بترتيب مناسب للتحليل اللاحق.

يمكنك فرز المعلومات أبجديًا، أو عن طريق القيم التصاعدية أو التنازلية، حسب التاريخ أو حسب الرمز، حسب النص أو لون الخلية. وهذا هو بالضبط ما ستناقشه هذه المقالة.

أعداد

كل شيء بسيط للغاية هنا. لنأخذ الجدول التالي كمثال. لنقم بفرز البيانات حسب العمود C. للقيام بذلك، حدده وفي علامة التبويب "الصفحة الرئيسية"، انقر فوق الزر "الفرز والتصفية". في القائمة التالية، حدد أو "...من الحد الأدنى إلى الحد الأقصى"، أو "...من الحد الأقصى إلى الحد الأدنى". دعونا نختار الخيار الثاني.

الآن لدينا البيانات في لغة C مرتبة ترتيبًا تنازليًا.

لدي عمود C يقع بين عمودين آخرين، وهو مملوء بالبيانات. في هذه الحالة، يعتبر Excel العمود المحدد جزءًا من الجدول (ويقوم بالعد بشكل صحيح). ونتيجة لذلك، ظهرت الرسالة التالية. وبما أنني بحاجة إلى الفرز خصيصًا للفصل الدراسي، فأنا أسلط الضوء على العنصر باستخدام علامة "... ضمن التخصيص المحدد"وانقر على "فرز".

أبجديا

يتم ذلك وفقًا لنفس المبدأ الموضح أعلاه. حدد النطاق المطلوب واضغط على الزر "الفرز والتصفية". لقد تغيرت العناصر الموجودة في القائمة المنسدلة. اختر إما "من الألف إلى الياء" أو "من الياء إلى الألف".

يتم فرز قائمة الأسماء في المثال أبجديًا.

حسب التاريخ

لفرز التواريخ في Excel، انتبه أولاً إلى التنسيق الذي تم تعيينه للخلايا التي تمت كتابتها بها. حددها وفي علامة التبويب "الصفحة الرئيسية" انظر إلى مجموعة "الرقم". أفضل تنسيق هو "التاريخ"، قصير أو طويل، أو "(جميع التنسيقات)" - يمكن كتابة التاريخ بطرق مختلفة: DD.MM.YYYY، DD.MMM، MMM.YY.

هذه النقطة مهمة جدًا، لأنه بخلاف ذلك قد يتم فرز التواريخ ببساطة عن طريق تصاعدي أول رقمين، أو حسب الشهر بالترتيب الأبجدي.

بعد ذلك، حدد النطاق المطلوب من الخلايا وانقر على الزر "الفرز والتصفية". من القائمة يمكنك اختيار أو "من القديم إلى الجديد"، أو "من الجديد إلى القديم".

بواسطة الخلية أو لون النص

يمكن استخدام هذه الطريقة عندما يكون هناك نص في الخلايا في جدول Excel أو عندما تكون الخلايا نفسها مطلية بألوان مختلفة. على سبيل المثال، لنأخذ عمودًا من الأرقام المطلية بألوان مختلفة. لنقم بفرزها بحيث تأتي الأرقام الملونة باللون الأحمر أولاً، ثم الأخضر والأسود.

حدد النطاق بأكمله وانقر على الزر "الفرز والتصفية"واختر من القائمة "قابلة للتخصيص...".

في النافذة التالية، قم بإلغاء تحديد المربع إذا قمت بتحديدها بدون السطر العلوي، وهو رأس الجدول. ثم نختار العمود الذي سنفرز به، في المثال هو "أنا". في قسم "الفرز"، حدد "لون الخط" من القائمة المنسدلة. في قسم الطلب، حدد "اللون الأحمر" - "الأعلى". قمنا بفرز الأرقام باللون الأحمر.

أنت الآن بحاجة إلى أن يحتوي العمود على أرقام خضراء. انقر على الزر "إضافة مستوى". جميع الإعدادات هي نفسها، فقط اختر "اللون الأخضر". انقر فوق موافق.

يتم فرز عمودنا على النحو التالي.

كما ترون، الأرقام ليست بالترتيب. دعونا فرز الأرقام بترتيب تصاعدي. حدد العمود وانقر "الفرز والتصفية" – "قابلة للتخصيص...". في النافذة التي تفتح، انقر فوق الزر "إضافة مستوى". يبقى العمود "أنا"، في الحقل التالي نختار حسب "القيمة"، الترتيب "تصاعدي". انقر فوق موافق.

الآن يتم فرز عمودنا حسب لون النص وبترتيب تصاعدي للبيانات.

يتم فرز البيانات بنفس الطريقة حسب لون الخلية، فقط في قسم "الفرز"، حدد "لون الخلية" من القائمة.

الجداول

إذا كان لديك جدول تريد الفرز فيه حسب عدة أعمدة في وقت واحد، فقم بما يلي. حدد النطاق الكامل لخلايا الجدول مع الرأس. انقر على الزر "الفرز والتصفية"و اختار "قابلة للتخصيص...".

دعونا نفرز الفصل بترتيب تصاعدي، وبنفس الطريقة متوسط ​​​​الدرجات.

في نافذة الفرز، حدد المربع "بياناتي تحتوي على رؤوس". في قسم "العمود"، حدد "الفئة" من القائمة، وقم بالفرز حسب "القيمة"، ثم قم بالترتيب "تصاعدي".

للقيام بنفس الشيء بالنسبة للنتيجة المتوسطة، انقر فوق الزر "إضافة مستوى". في قسم "العمود"، حدد "متوسط ​​النتيجة". انقر فوق موافق.

يتم فرز البيانات الموجودة في الجدول.

الآن في عمود "الاسم"، قم بتلوين الخلايا ذات الأولاد باللون الأزرق والخلايا ذات البنات باللون الوردي. لتجنب القيام بذلك لكل خلية على حدة، اقرأ المقالة حول كيفية تحديد الخلايا في Excel - فهي تخبرك بكيفية تحديد الخلايا غير المتجاورة.

لنقم بفرز هذا العمود حسب لون الخلية: في البداية سيكون هناك فتيات، ثم أولاد. حدد الجدول بأكمله مرة أخرى، وانقر فوق "فرز" - "قابلة للتخصيص...".

يوجد في النافذة التي تفتح بالفعل مستويين قمنا بإنشائهما سابقًا. هذه المستويات لها الأولوية - الأول هو الأكبر، والثاني أصغر، وهكذا. أي أننا إذا أردنا فرز البيانات الموجودة في جدول البنات/الأولاد أولاً، ثم حسب الفصل، ثم حسب متوسط ​​الدرجات، نحتاج إلى ترتيب المستويات بهذا الترتيب.

انقر على الزر "إضافة مستوى". في قسم "العمود"، حدد "الاسم"، وفرز - "لون الخلية"، وترتيب - "وردي"، و"أعلى".

الآن، باستخدام الأسهم، انقل هذا الخط إلى أعلى القائمة. انقر فوق موافق.

يبدو الجدول الذي يحتوي على البيانات المصنفة هكذا.

يعد فرز البيانات في Excel أداة لتقديم المعلومات في نموذج سهل الاستخدام.

يمكن فرز القيم الرقمية بترتيب تصاعدي وتنازلي، ويمكن فرز القيم النصية أبجديًا وبترتيب عكسي. الخيارات متاحة - حسب اللون والخط، بأي ترتيب، وفقًا لعدة شروط. يتم فرز الأعمدة والصفوف.

ترتيب الفرز في Excel

هناك طريقتان لفتح قائمة الفرز:

يتم عرض طرق الفرز المستخدمة بشكل متكرر من خلال زر واحد على شريط المهام:

فرز الجدول حسب عمود واحد:

إذا قمت بتحديد الجدول بأكمله وفرزه، فسيتم فرز العمود الأول. ستصبح البيانات الموجودة في الصفوف متوافقة مع موضع القيم في العمود الأول.



فرز حسب لون الخلية والخط

يوفر Excel للمستخدم خيارات تنسيق غنية. لذلك، يمكنك العمل بتنسيقات مختلفة.

لنقم بإنشاء عمود "الإجمالي" في جدول التدريب ونملأ الخلايا بقيم ذات ظلال مختلفة. دعونا فرز حسب اللون:

يقوم البرنامج بفرز الخلايا حسب اللهجة. يمكن للمستخدم اختيار ترتيب فرز الألوان بشكل مستقل. للقيام بذلك، حدد "الفرز المخصص" في قائمة خيارات الأداة.

في النافذة التي تفتح، أدخل المعلمات الضرورية:

هنا يمكنك اختيار الترتيب الذي يتم به عرض الخلايا ذات الألوان المختلفة.

يتم فرز البيانات حسب الخط باستخدام نفس المبدأ.

الفرز في Excel حسب أعمدة متعددة

كيفية تعيين ترتيب الفرز الثانوي في إكسيل؟ لحل هذه المشكلة، تحتاج إلى تعيين عدة شروط الفرز.

يتيح لك البرنامج إضافة عدة معايير مرة واحدة لإجراء الفرز بترتيب خاص.

فرز الصفوف في Excel

افتراضيًا، يتم فرز البيانات حسب الأعمدة. كيفية الفرز حسب الصفوف في إكسيل:

هذه هي الطريقة التي تقوم بها بفرز جدول في Excel وفقًا لعدة معلمات.

فرز عشوائي في Excel

لا تسمح لك خيارات الفرز المضمنة بترتيب البيانات بشكل عشوائي في عمود. ستتولى الدالة RAND هذه المهمة.

على سبيل المثال، تحتاج إلى ترتيب مجموعة من الأرقام المحددة بترتيب عشوائي.

ضع المؤشر في الخلية المجاورة (يسارًا أو يمينًا، لا يهم). أدخل RAND() في شريط الصيغة. اضغط دخول. نقوم بنسخ الصيغة إلى العمود بأكمله - نحصل على مجموعة من الأرقام العشوائية.

لنقم الآن بفرز العمود الناتج بترتيب تصاعدي/تنازلي - سيتم ترتيب القيم الموجودة في النطاق الأصلي تلقائيًا بترتيب عشوائي.

الفرز الديناميكي للجدول في MS Excel

إذا قمت بتطبيق الفرز القياسي على جدول، فلن يكون ذا صلة عندما تتغير البيانات. نحتاج إلى التأكد من فرز القيم تلقائيًا. نحن نستخدم الصيغ.

إذا كنت بحاجة إلى إجراء فرز ديناميكي بترتيب تنازلي، فاستخدم الدالة LARGE.

لفرز القيم النصية ديناميكيًا، ستحتاج إلى صيغ صفائف.

وبعد ذلك، عند إضافة بيانات إلى الجدول، سيتم تنفيذ عملية الفرز تلقائيًا.

    يتم استخدام الأمر SORT لفرز سطور الملف النصي أو الإخراج القياسي أبجديًا.

تنسيق سطر الأوامر:

فرز [[محرك الأقراص 1:] [path1] اسم الملف 1] [path2]] [path3] اسم الملف 3]

خيارات سطر الأوامر:

/+ن- تحديد عدد الأحرف n قبل بداية كل مقارنة. يشير /+3 إلى أن كل مقارنة ستبدأ عند الحرف الثالث في كل سطر. يتم جمع الأسطر التي تقل عن n من الأحرف قبل كافة الأسطر الأخرى. افتراضيًا، تبدأ المقارنة عند الحرف الأول من كل سطر.

/ لغة إل- استبدال اللغة والتخطيط الافتراضيين المثبتين في النظام باللغة المحددة. في الوقت الحالي، هناك خيار واحد فقط: ""C"" - أسرع طريقة لترتيب التسلسل. الفرز دائمًا غير حساس لحالة الأحرف.

/ م كيلو بايت- ضبط مقدار الذاكرة الرئيسية المستخدمة للفرز بالكيلوبايت. يجب أن يكون حجم الذاكرة 160 كيلو بايت على الأقل. إذا قمت بتعيين حجم الذاكرة بشكل صريح، فهذا هو المقدار الذي سيتم استخدامه، بغض النظر عن مقدار الذاكرة الرئيسية المتوفرة في النظام. يمكنك تحقيق أفضل أداء من خلال عدم ضبط حجم الذاكرة. افتراضيًا، يتم إجراء الفرز في مسار واحد (بدون ملف مؤقت)، باستخدام الحد الأقصى لحجم الذاكرة المتوفرة. بخلاف ذلك، يتم إجراء الفرز في مسارين (تخزين البيانات التي تم فرزها جزئيًا في ملف مؤقت)، وبالتالي فإن مقدار الذاكرة المستخدمة لكلا التمريرتين هو نفسه. افتراضيًا، الحد الأقصى لحجم الذاكرة هو 90% من الذاكرة الرئيسية المتوفرة إذا كانت تدفقات الإدخال والإخراج عبارة عن ملفات على القرص، و45% من الذاكرة الرئيسية المتوفرة بخلاف ذلك.

/ أحرف REC- يحدد الحد الأقصى لعدد الأحرف في الإدخال (الافتراضي 4096، الحد الأقصى الممكن 65535).

/ ر- ترتيب الفرز العكسي؛ أولئك. يبدأ الترتيب من Z إلى A، ثم من 9 إلى 0.

[محرك الأقراص 1:] [المسار 1] اسم الملف 1- يحدد اسم الملف الذي تم فرزه. إذا تم حذفه، سيتم استخدام الإدخال القياسي. يعد تحديد الملف المراد فرزه بشكل صريح أسرع من إعادة توجيه نفس الملف كمدخل قياسي.

/T [محرك الأقراص 2:] [المسار 2]- يحدد المسار إلى المجلد الذي يحتوي على ملفات الفرز العاملة في حالة عدم احتواء البيانات في الذاكرة الرئيسية. بشكل افتراضي، يتم استخدام المجلد المؤقت للنظام.

/O [drive3:] [path3] اسم الملف 3- يحدد اسم الملف الذي يتم حفظ النتائج المصنفة فيه. إذا تم حذفها، تتم كتابة البيانات إلى الإخراج القياسي. يعد تحديد ملف الإخراج بشكل صريح أسرع من إعادة توجيه الإخراج القياسي إلى نفس الملف.

أمثلة على الاستخدام:

فرز mytxt.txt / الإخراجsortxt.txt- فرز أسطر الملف بالترتيب الأبجدي mytxt.txtمع كتابة النتيجة في ملف Sortxt.txt

فرزsortxt.txt- كما في المثال السابق، ولكن يتم استخدام إعادة توجيه المدخلات والمخرجات.

فرز /عكس mytxt.txt /الإخراجsortxt.txt- فرز سطور الملف بترتيب عكسي mytxt.txtمع كتابة النتيجة في ملف Sortxt.txt