سيوضح لك هذا الدليل كيفية الترتيب الأبجدي في Excel باستخدام ميزة الفرز والتصفية ، والصيغ لترتيب البيانات وتنسيقها بالترتيب.
يعد Microsoft Excel برنامجًا فعالاً لتنظيم البيانات المهمة ، مثل الشؤون المالية وبيانات المبيعات ومعلومات العملاء وما إلى ذلك. يعد ترتيب البيانات أبجديًا في أوراق العمل أحد أفضل الطرق لتنظيم هذه البيانات والوصول إليها والرجوع إليها بسرعة. يعني الترتيب الأبجدي لمجموعة من الخلايا فرز نطاق أبجديًا في Excel. يمكن فرز البيانات في كلا الاتجاهين ، إما بترتيب تصاعدي أو تنازلي.
على سبيل المثال ، لنفترض أن لديك مجموعة بيانات كبيرة من معلومات طلب العميل وتريد العثور على سجل الطلبات لعميل معين. سيصبح التمشيط في القائمة بأكملها عملاً روتينيًا. بدلاً من إضاعة الوقت في التمرير ، يمكنك ترتيب الأعمدة أبجديًا للعثور على بياناتك بسرعة.
هناك طرق مختلفة يمكنك استخدامها لترتيب البيانات أبجديًا في Excel ، اعتمادًا على نوع مجموعة البيانات التي تعمل عليها. يسمح لك Excel بفرز عمود واحد أو صف واحد أو نطاق محدد أو ورقة عمل كاملة ، بالإضافة إلى خيارات لتجميع عدة أعمدة أو صفوف معًا أثناء فرزها. دعونا نتعلم كيفية ترتيب البيانات أبجديًا في جداول بيانات Excel حسب الصفوف أو الأعمدة.
لماذا ترتيب البيانات أبجديًا في Excel
سيكون لترتيب البيانات أبجديًا مزايا عديدة:
- يجعل البيانات أكثر منطقية ويسهل قراءتها.
- يسهل على المستخدم البحث عن قيمة معينة أو اسم عميل في ورقة بيانات Excel.
- يساعد المستخدم على تحديد السجلات المكررة بصريًا حتى نتمكن من منع أخطاء إدخال البيانات.
- يتيح للمستخدمين تجميع الأعمدة أو القوائم معًا بسهولة بحيث يمكن للمستخدمين النظر إليها جنبًا إلى جنب.
يعد الترتيب الأبجدي (الفرز أبجديًا) طريقة سهلة وشائعة لفرز معلوماتك بسرعة. إنه مفيد بشكل خاص لمجموعات البيانات الكبيرة جدًا. تمامًا ، هناك 4 طرق لترتيب البيانات أبجديًا في Excel: الزر A-Z أو Z-A ، وميزة الفرز ، ووظيفة التصفية ، والصيغ.
أبجديًا عمود في Excel
أسرع طريقة للفرز أبجديًا في Excel هي استخدام ميزة الفرز المضمنة.
أولاً ، افتح جدول البيانات الذي يحتوي على مجموعة البيانات. حدد الخلايا أو العمود الذي تريد فرزه وتأكد من عدم تحديد أي خلايا فارغة. بعد ذلك ، انتقل إلى علامة التبويب "البيانات" وانقر إما على "A-Z" للترتيب تصاعديًا أو "Z-A" للترتيب تنازليًا في مجموعة الفرز والتصفية.
إذا كان هناك أي خلايا فارغة بينهما ، يفترض Excel أنه تم فرز جميع البيانات وتتوقف فوق الخلية السوداء. إذا كنت ترغب في فرز العمود بأكمله ، فما عليك سوى تحديد أي خلية في العمود واستخدام الخيار أعلاه.
يمكن أيضًا الوصول إلى نفس الخيارات ضمن أداة "الفرز والتصفية" في قسم التحرير في علامة التبويب "الصفحة الرئيسية".
بمجرد النقر فوق الزر ، قم بترتيب القائمة أبجديًا على الفور.
كيفية الفرز والاحتفاظ بالصفوف معًا في Excel
يمكنك بسهولة فرز عمود إذا كان لديك عمود واحد فقط في ورقة العمل الخاصة بك ، ولكن الأمر يصبح معقدًا بعض الشيء عندما تحتوي ورقة العمل الخاصة بك على أعمدة متعددة بجانب العمود الذي تريد فرزه. أيضًا ، في بعض الأحيان ، نعمل على جداول البيانات حيث نحتاج إلى الحفاظ على الصفوف المقابلة سليمة بعد فرز العمود.
على سبيل المثال ، إذا كان لدينا جدول بيانات بقائمة علامات الطلاب. عندما نقوم بفرز عمود أسماء الطلاب بالترتيب الأبجدي ، فإننا نريد أيضًا أن تتحرك العلامات التي تم إدخالها في الصفوف المجاورة لهم وفقًا لذلك.
في مثل هذه الحالات ، يمكنك استخدام الزر "A-Z" أو "Z-A" لفرز أحد الأعمدة بالترتيب الأبجدي وسيقوم تلقائيًا بترتيب البيانات في أعمدة أخرى ، مع الاحتفاظ بالصفوف معًا. دعونا نرى كيف يعمل ذلك مع مثال آخر.
أولاً ، حدد العمود الأول الذي تريد فرز الأعمدة الأخرى وفقًا له. ثم حدد إما "A-Z" أو "Z-A" ضمن علامة التبويب "البيانات" أو علامة التبويب "الصفحة الرئيسية" لفرز البيانات في عمود واحد ، وسيقوم Excel تلقائيًا بإعادة ترتيب الأعمدة الأخرى وفقًا لذلك.
بمجرد النقر فوق زر الفرز ، ستظهر نافذة حوار تحذير الفرز. حدد زر الاختيار لـ "توسيع التحديد" وانقر فوق الزر "فرز".
سيؤدي ذلك إلى إبقاء الصفوف معًا أثناء فرز الأعمدة ، دون تطابق أي بيانات (كما هو موضح أدناه).
إذا حددت الخيار "متابعة مع التحديد الحالي" ، فسيقوم Excel بفرز العمود المحدد فقط.
كيفية الفرز أبجديًا حسب الأعمدة المتعددة
إذا كنت تريد فرز مجموعة بيانات أبجديًا حسب أكثر من عمود واحد ، فيمكنك القيام بذلك باستخدام ميزة الفرز في Excel.
على سبيل المثال ، نريد ترتيب الجدول التالي أبجديًا أولاً حسب البلد ، ثم حسب الاسم الأول:
أولاً ، حدد الجدول بأكمله الذي تريد ترتيبه حسب الترتيب الأبجدي ، ثم انتقل إلى علامة التبويب "البيانات" وفي مجموعة الفرز والتصفية ، انقر فوق "فرز".
أو انقر على رمز "فرز وتصفية" في قسم التعديل في علامة التبويب "الصفحة الرئيسية" وحدد "فرز مخصص" للوصول إلى نفس الميزة.
سيظهر مربع الحوار "فرز" ويعرض العمود الأول (المعلمة) حيث سيتم فرز الجدول. في مربع القائمة المنسدلة "فرز حسب" ، حدد العمود الأساسي الذي تريد ترتيب البيانات أبجديًا من خلاله أولاً ، و "البلد" في حالتنا ، وفي القائمة المنسدلة "ترتيب" اختر إما "أ إلى ي" أو "ي إلى أ" .
ثم انقر فوق الزر "إضافة مستوى" لإضافة مستوى الفرز الثاني. حدد العمود الثاني (في حالتنا الاسم الأول) الذي تريد من خلاله ترتيب البيانات أبجديًا في مربع "فرز حسب" وحدد إما "أ إلى ي" أو "ي إلى أ". إذا كان جدولك يحتوي على رؤوس في الأعلى ، فحدد مربع الاختيار "My Data Has Headers" حتى يتمكن من تخطي الرؤوس أثناء فرزها. أضف المزيد من مستويات الفرز إذا لزم الأمر ، وانقر فوق "موافق".
الجدول مرتبة أبجديًا: أولاً حسب الدولة ، ثم حسب الاسم الأول كما هو موضح أدناه.
كيفية فرز الصفوف أبجديًا في Excel
قد ترغب أحيانًا في ترتيب الصفوف أبجديًا في Excel ، بدلاً من الأعمدة. يمكن القيام بذلك باستخدام ميزة الفرز في Excel. على سبيل المثال ، لنفترض أن لديك جدول بيانات حيث يحتوي عمود الصف الأول من B إلى T على أسماء المدن حيث يحتوي عدد المتاجر المتاحة ضمن أنواع مختلفة من البيع بالتجزئة والعمود A على نوع بائع التجزئة. تُستخدم الخلايا لتتبع عدد المتاجر في كل مدينة في فئات بائعي التجزئة المختلفة.
أولاً ، حدد جميع الصفوف التي تريد ترتيبها أبجديًا ؛ إذا كنت لا تريد نقل تسميات الصفوف ، فاتركها خارج التحديد. الآن ، انتقل إلى علامة التبويب "البيانات" وانقر على "فرز" في مجموعة الفرز والتصفية.
في مربع الحوار "فرز" ، انقر فوق الزر "خيارات".
سيظهر مربع حوار صغير آخر ، يمنحك خيار تحديد "فرز من اليسار إلى اليمين" أو "فرز من أعلى إلى أسفل". حدد الخيار "فرز من اليسار إلى اليمين" للترتيب الأبجدي حسب الصف بدلاً من ذلك ، ثم انقر فوق "موافق" للرجوع إلى مربع الحوار "فرز".
الآن ، حدد الصف الذي تريد ترتيبه أبجديًا في القائمة المنسدلة "فرز حسب" (الصف 1 في هذا المثال). في الحقل الآخر ، احتفظ بقيم الخلية في مربع "الفرز عند" ، وحدد "من الألف إلى الياء" (ترتيب تصاعدي) في مربع الطلب. ثم انقر فوق "موافق" لإغلاق مربع الحوار.
الآن ، يتم ترتيب الصف الأول في جدولنا بترتيب أبجدي (تصاعدي) ، ويتم إعادة ترتيب الصفوف المتبقية وفقًا لذلك كما هو موضح أدناه.
كيفية فرز عمود أبجديًا باستخدام عامل تصفية
هناك طريقة سريعة أخرى للفرز أبجديًا في Excel وهي استخدام ميزة التصفية. بمجرد تطبيق المرشحات على الأعمدة ، يمكن الوصول إلى خيارات الفرز لجميع الأعمدة بنقرة واحدة بالماوس.
لإضافة فلتر إلى الأعمدة ، حدد عمودًا واحدًا أو أكثر وانقر على الخيار "تصفية" في مجموعة الفرز والتصفية في علامة التبويب "البيانات". إذا كنت تريد تطبيق الفلتر على الجدول بأكمله ، فحدد أي خلية في الجدول وانقر على خيار "تصفية".
يمكنك أيضًا الوصول إلى خيار التصفية بالنقر فوق أداة "الفرز والتصفية" في مجموعة التحرير بعلامة التبويب "الصفحة الرئيسية" ، وتحديد "تصفية".
سيظهر سهم منسدل صغير في كل من رؤوس الأعمدة. انقر فوق سهم أي عمود تريد ترتيبه أبجديًا ، وحدد إما "فرز من أ إلى ي" أو "فرز من ي إلى أ".
سيؤدي هذا إلى ترتيب العمود الذي تريده بالترتيب الأبجدي الذي اخترته ، وإذا اخترت "فرز من أ إلى ي" ، فسيظهر سهم صغير إلى الأعلى على زر الفلتر يشير إلى ترتيب الفرز (تصاعديًا):
الفرز المتقدم في ترتيب مخصص
لا يعتبر الفرز أبجديًا مثاليًا لكل البيانات الأبجدية. في بعض الأحيان ، يمكن ترتيب البيانات أبجديًا ، ولكنها ليست الطريقة الأكثر فعالية لفرزها.
دعنا نتخيل موقفًا قد يكون لديك فيه قائمة تحتوي على أسماء الأشهر أو أيام الأسبوع ، فالترتيب الأبجدي ليس مفيدًا في هذا الموقف. من المنطقي فرز تلك القائمة ترتيبًا زمنيًا. إذا قمت بالفرز من الألف إلى الياء ، فسيتم ترتيب الأشهر بالترتيب الأبجدي ، وسيأتي أبريل أولاً ، ثم أغسطس ، وفبراير ، ويونيو ، وما إلى ذلك. لكن هذا ليس ما تريده. لحسن الحظ ، من السهل جدًا الترتيب زمنيًا في Excel باستخدام خيار Advanced Custom Sort.
ابدأ بتحديد الجدول الذي تريد فرزه. ثم حدد "ترتيب مخصص" ضمن "فرز وتصفية" في قسم التعديل من علامة التبويب "الصفحة الرئيسية".
في مربع حوار الفرز ، اختر العمود الذي يحتوي على شهور السنة في قسم العمود ، وفرز حسب "القيم" ، كالعادة ، وفي قسم الترتيب ، اختر "قائمة مخصصة".
في مربع الحوار "القوائم المخصصة" ، يمكنك إنشاء قائمة خاصة بك. هناك بعض القوائم المخصصة الافتراضية بما في ذلك أيام الأسابيع والأشهر المختصرة وشهور السنة وما إلى ذلك. اختر خيار الفرز المناسب حسب حاجتك (في حالتنا يناير ، فبراير ، ..) وانقر على "موافق". ثم انقر فوق "موافق" مرة أخرى لترتيب قائمتك ترتيبًا زمنيًا.
ستلاحظ أنك نجحت في فرز القائمة وفقًا للترتيب الشهري.
كيفية الفرز أبجديًا في Excel باستخدام صيغ Excel
إذا كنت من محبي الصيغ ، فيمكنك استخدام صيغ Excel لترتيب القائمة أبجديًا. الصيغتان اللتان يمكن استخدامهما لترتيب البيانات أبجديًا هما COUNTIF و VLOOKUP.
على سبيل المثال ، لدينا قائمة بالأسماء التي نريد ترتيبها أبجديًا باستخدام الصيغة.
لفرز ذلك ، سنضيف عمودًا مؤقتًا يسمى "ترتيب الفرز" إلى الجدول الحالي.
في الخلية (A2) بجوار البيانات ، استخدم صيغة COUNTIF التالية:
= COUNTIF ($ B $ 2: $ B $ 20، "<=" & B2) تقارن الصيغة أعلاه قيمة نصية في الخلية B2 مع جميع القيم النصية الأخرى (B3: B20) وتُرجع ترتيبها النسبي. على سبيل المثال ، في الخلية B2 ، تقوم بإرجاع 11 ، حيث توجد 11 قيمة نصية أقل من أو تساوي النص "نانسي" بترتيب أبجدي. والنتيجة هي ترتيب الفرز الخاص باسم الموظف في الخلية B2.
ثم استخدم مقبض التعبئة واسحب هذه الصيغة لملء النطاق بأكمله. هذا يعطي ترتيب الفرز لكل اسم في القائمة.
الآن ، يتعين علينا ترتيب البيانات الموضحة في لقطة الشاشة أعلاه بناءً على رقم ترتيب الفرز ، وللقيام بذلك سنستخدم وظيفة VLOOKUP.
النحو:
= VLOOKUP (، A: B، 2،0)هنا يمثل "رقم الفرز" الأرقام بترتيب تصاعدي من 1 - 20. للترتيب التنازلي ، يجب أن تكون الأرقام من 20 إلى 1.
هنا ، لدينا العمود D للأسماء التي تم فرزها. بالنسبة للخلية D2 ، أدخل صيغة VLOOKUP التالية:
= VLOOKUP (1، A: B، 2،0)وبالمثل ، بالنسبة للخلايا الثانية والثالثة ، يجب عليك استخدام الصيغة على النحو التالي:
= VLOOKUP (2، A: B، 2،0) و = VLOOKUP (3، A: B، 2،0) وما إلى ذلك وهلم جرا…
بعد إدخال صيغة VLOOKUP هذه في كل خلية بجوار البيانات ، يتم ترتيب القائمة أبجديًا.
بدلاً من إدخال الصيغة المذكورة أعلاه يدويًا (1-20) في كل خلية ، يمكنك أيضًا استخدام وظيفة الصف لتسهيل عملك. ترجع الدالة Row () رقم صف الخلية الحالية. لذلك ، بمساعدة دالة الصف ، ستكون الصيغة:
= VLOOKUP (ROW () - 1، A: B، 2،0)ستعطيك هذه الصيغة نفس نتيجة الصيغة أعلاه.
ثم استخدم مقبض التعبئة لسحب هذه الصيغة لملئها في النطاق بأكمله.
كيفية ترتيب الإدخالات أبجديًا حسب الاسم الأخير
في بعض الأحيان ، غالبًا ما نضطر إلى فرز أوراق البيانات أبجديًا بأسماء العائلة. في مثل هذه الحالات ، على الرغم من أن الأسماء تبدأ بالأسماء الأولى ، فإنك تحتاج إلى ترتيبها أبجديًا حسب الاسم الأخير. يمكنك القيام بذلك باستخدام صيغ Excel Text.
للقيام بذلك ، سيتعين عليك أولاً استخراج الاسمين الأول والأخير من الأسماء الكاملة إلى عمودين مختلفين. ثم اعكس الأسماء ، وفرزها ، ثم اعكسها مرة أخرى إلى شكلها الأصلي.
باستخدام الاسم الكامل في A2 ، أدخل الصيغ التالية في خليتين مختلفتين (B2 و C2) ، ثم انسخ الصيغ أسفل الأعمدة (باستخدام مقبض التعبئة) حتى الخلية الأخيرة بالبيانات:
لاستخراج الاسم الأول ، أدخل هذه الصيغة في الخلية C2:
= LEFT (A2، SEARCH (""، A2) -1)
لاستخراج الاسم الأخير ، أدخل هذه الصيغة في الخلية D2:
= RIGHT (A2، LEN (A2) -SEARCH (""، A2،1))
وبعد ذلك ، في الخلية E2 ، سلسل الأسماء الأولى والأخيرة بترتيب عكسي مفصولة بفاصلة:
= D2 & "،" & C2
لقد فصلنا الاسم الأول والأخير وعكسناهما. الآن علينا ترتيبها أبجديًا وعكسها مرة أخرى. كما ترى أعلاه ، عندما نستخرج الأسماء باستخدام الصيغة ، تحتوي الأعمدة C و D و E فعليًا على صيغ ولكن يتم تنسيقها لتبدو مثل القيم. لذا علينا تحويل الصيغة إلى قيم.
للقيام بذلك ، حدد أولاً جميع خلايا الصيغة (E1: E31) واضغط Ctrl + C لنسخها. انقر بزر الماوس الأيمن فوق الخلايا المحددة ، وانقر على رمز "القيم" ضمن "خيارات اللصق" ، واضغط على مفتاح "إدخال".
ثم حدد أي خلية في العمود الناتج ، وانقر على الخيار "أ إلى ي" أو "ي إلى أ" في علامة التبويب "البيانات" أو "الصفحة الرئيسية" حسب احتياجاتك.
سيظهر مربع حوار Sort Warning بمجرد النقر فوق الزر. اختر خيار "توسيع التحديد" وانقر على الزر "فرز".
كما ترى ، تم ترتيب العمود بالكامل أبجديًا حسب الاسم الأخير.
الآن ، إذا كنت ترغب في عكسه مرة أخرى إلى التنسيق الأصلي "الاسم الأول اسم العائلة" ، عليك فقط إدخال نفس الصيغ ولكن باستخدام مراجع خلايا مختلفة.
يجب عليك فصل الاسم (E2) إلى جزأين مرة أخرى باستخدام الصيغ التالية:
في G2 ، استخرج الاسم الأول:
= RIGHT (E2، LEN (E2) -SEARCH (""، E2))
في G2 ، اسحب الاسم الأخير:
= LEFT (E2، SEARCH (""، E2) -2)
وانضم إلى الجزأين معًا للحصول على الاسم الكامل الأصلي:
= G2 & "" & H2
في كل مرة تدخل فيها الصيغة في الخلية الأولى ، تأكد من نسخ الصيغة أسفل العمود (باستخدام مقبض التعبئة) حتى الخلية الأخيرة بالبيانات.
الآن كل ما عليك فعله هو تحويل الصيغ إلى تحويل قيم مرة أخرى كما فعلنا أعلاه ، وقد انتهيت. هذا القيام به و.