إذا كنت تعمل باستخدام مجموعة بيانات من الأسماء ، فإن فرزها يعد أحد المهام الشائعة التي يجب عليك إجراؤها بشكل متكرر.
من السهل فرز البيانات أبجديًا حسب الاسم الكامل ، حيث يستخدم Excel الحرف الأول من الاسم للفرز.
ومع ذلك ، إذا كنت تريدفرز البيانات حسب الاسم الأخير في Excelاركبتسلسلكيف تفعل؟
على الرغم من أن الأمر ليس بهذه البساطة ، إلا أنه لا يزال من الممكن القيام به (يعتمد أيضًا كثيرًا على كيفية هيكلة بيانات الاسم).
أيًا كانت الطريقة التي تستخدمها ، يجب عليك بطريقة ما استخراج الاسم الأخير من الاسم الكامل ووضعه في عمود منفصل.يمكنك بعد ذلك استخدام هذا العمود لفرز البيانات أبجديًا حسب الاسم الأخير.
في هذا البرنامج التعليمي لبرنامج Excel ، سأوضح لك كيفية فرز عمود بأسماء بناءً على الاسم الأخير.
اذا هيا بنا نبدأ!
المحتويات
استخراج وفرز حسب الاسم الأخير باستخدام البحث والاستبدال
الخطوة الأولى للفرز حسب الاسم الأخير هي الحصول على الاسم الأخير في عمود منفصل.
يمكنك القيام بذلك عن طريق استبدال كل شيء قبل الاسم الأخير بالفراغات بحيث يتبقى لك الاسم الأخير فقط.
لنفترض أن لديك مجموعة بيانات مثل المجموعة أدناه ، وتريد فرز هذه البيانات أبجديًا حسب الاسم الأخير.
فيما يلي خطوات الفرز حسب الاسم الأخير:
- حدد مجموعة البيانات التي تحتوي على العنوان (في هذه الحالة سيكون A1: A10)
- انسخه في العمود المجاور (إذا لم يكن العمود المجاور فارغًا ، أدخل عمودًا جديدًا ، ثم انسخ الأسماء)
- أعد تسمية رؤوس الأعمدة المنسوخة.في هذا المثال ، سأطلق عليه اسم "اسم العائلة"
- حدد جميع الأسماء المنسوخة (لا تحدد العناوين)
- اضغط مع الاستمرار على مفتاح التحكم ، ثم اضغط على المفتاح H.سيؤدي هذا إلى فتح مربع الحوار "بحث واستبدال".
- في البحث عن الحقل ، أدخل * (علامة النجمة متبوعة بحرف مسافة)
- اترك الحقل "استبدال بـ" فارغًا
- انقر فوق "استبدال الكل".سيحل هذا محل جميع الأسماء الأولى مرة واحدة وستترك مع أسماء العائلة فقط.
ستحتفظ الخطوات المذكورة أعلاه بالاسم الأخير وتحذف كل شيء قبله.يعمل هذا حتى إذا كان لديك اسم وسط أو بادئة مثل Mr.
بمجرد أن يكون لديك أسماء العائلة في الأعمدة المجاورة ، يمكنك بسهولة فرز مجموعة البيانات (بما في ذلك الأسماء الكاملة) أبجديًا حسب الاسم الأخير.
فيما يلي خطوات الفرز حسب الاسم الأخير:
- حدد مجموعة البيانات بأكملها بالعناوين (بما في ذلك الأسماء الكاملة والأسماء الأخيرة المستخرجة).يمكنك أيضًا تضمين أعمدة أخرى للفرز بالاسم
- انقر فوق علامة التبويب البيانات
- انقر للترتيب
- في مربع حوار الفرز ، تأكد من فحص بياناتي التي تحتوي على رؤوس.
- في خيار "فرز حسب" ، حدد اسم العمود بالاسم الأخير فقط
- في فرز حسب ، حدد قيمة الخلية
- في خيار "الطلب" ، حدد "من الألف إلى الياء"
- انقر فوق موافق
ستعمل الخطوات المذكورة أعلاه على فرز مجموعة البيانات المحددة بالكامل بناءً على الاسم الأخير.
بمجرد الانتهاء ، يمكنك إسقاط العمود بالاسم الأخير.
نصائح احترافية: في أي وقت تعتقد أنك قد تحتاج إلى الرجوع إلى البيانات الأصلية ، تحتاج إلى طريقة لإلغاء فرز مجموعة البيانات هذه.لهذا ، في الأعمدة المجاورة (يسارًا أو يمينًا) ، توجد أرقام تسلسلية قبل الفرز.الآن ، إذا كنت بحاجة إلى استعادة البيانات الأصلية ، فيمكنك الحصول عليها بالفرز بناءً على الأرقام.
استخرج وفرز أبجديًا حسب الاسم الأخير باستخدام الصيغة
في حين أن الطريقة الموضحة أعلاه (باستخدام البحث والاستبدال) هي ما أفضله للحصول على جميع الأسماء الأخيرة والفرز بناءً عليها ، فإن أحد قيودها هو أن البيانات الناتجة ثابتة.
هذا يعني أنه إذا أضفت المزيد من الأسماء الأولى إلى القائمة ، فسيتعين علي إجراء نفس العملية مرة أخرى للحصول على الاسم الأخير.
إذا لم يكن هذا ما تريده ، يمكنك استخدام طريقة صيغة لفرز البيانات حسب الاسم الأخير.
افترض أن لديك مجموعة البيانات الموضحة أدناه.
ها هي الصيغة لاستخراج الاسم الأخير من الاسم الكامل:
= RIGHT (A2، LEN (A2) -FIND (""، A2))
تعتمد الصيغة أعلاه على نمط بأسماء كاملة (في هذا المثال فقط الاسم الأول والأخير).النمط هو أنه سيكون هناك مسافة بين الاسم الأول والأخير.
يتم استخدام وظيفة FIND للحصول على موضع حرف المسافة.يتم بعد ذلك طرح هذه القيمة من إجمالي طول الاسم الأول للحصول على العدد الإجمالي للأحرف في الاسم الأخير.
ثم استخدم هذه القيمة في الدالة RIGHT للحصول على الاسم الأخير.
بمجرد حصولك على عمود الاسم الأخير ، يمكنك فرز هذه البيانات (هذا مفصل في الطريقة الأولى).
ستعمل الصيغة أعلاه عندما يكون لديك الاسم الأول والأخير فقط.
ولكن ماذا لو كان لديك أيضًا اسم متوسط.أو قد يكون لها تحية قبل الاسم (على سبيل المثال السيد أو الآنسة)
في هذه الحالة ، تحتاج إلى استخدام الصيغة التالية:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
تعثر الصيغة أعلاه على موضع آخر حرف مسافة ثم تستخدمه لاستخراج الاسم الأخير.
أقترح عليك استخدام الصيغة الثانية في جميع الحالات ، فهي أبسط وتعالج جميع الحالات (طالما كان الاسم الأخير في نهاية الاسم الأول).
ملاحظة: تعتمد هاتان الصيغتان على شرط وجود حرف مسافة واحد فقط بين كل عنصر من عناصر الاسم.ستعطي هذه الصيغة نتائج غير صحيحة إذا كانت هناك مسافات مزدوجة أو مسافات بادئة / لاحقة.في هذه الحالة ، من الأفضل استخدام وظيفة TRIM لإزالة أي مسافات بادئة وزائدة ومزدوجة أولاً ، ثم استخدام الصيغة أعلاه.
في حين أن هذا قد يبدو وكأنه نهج معقد ، فإن فائدة استخدام الصيغ هي أنها تجعل النتائج ديناميكية.إذا أضفت المزيد من الأسماء إلى القائمة ، فكل ما عليك فعله هو نسخ الصيغة وستعطيك الاسم الأخير.
استخدم Text to Columns
يعد Text to Columns طريقة أخرى سهلة لتقسيم الخلايا في Excel.
يمكنك تحديد محدد (مثل فاصلة أو مسافة) واستخدامه لتقسيم محتويات الخلية.بعد تقسيم العناصر إلى أعمدة منفصلة ، يمكنك استخدام العمود بأسماء العائلة لترتيب البيانات أبجديًا.
افترض أن لديك مجموعة بيانات تبدو كالتالي:
فيما يلي خطوات الفرز حسب الاسم الأخير باستخدام Text to Column:
- حدد أعمدة بأسماء (باستثناء الرؤوس)
- انقر فوق علامة التبويب البيانات
- في مجموعة أدوات البيانات ، انقر فوق الخيار نص إلى أعمدة.سيؤدي هذا إلى فتح معالج النص إلى الأعمدة
- في الخطوة 1 من معالج تحويل النص إلى أعمدة ، حدد محدد وانقر فوق التالي
- في الخطوة 2 ، حدد "مسافة" كمحدد (قم بإلغاء تحديد أي شيء آخر إذا تم تحديده) وانقر فوق الزر "التالي".
- في الخطوة 3 ، حدد عمود الاسم الأول في معاينة البيانات ، ثم حدد خيار "عدم استيراد العمود (تخطي)".هذا يضمن أن الاسم الأول ليس جزءًا من النتيجة ، فأنت تحصل فقط على الاسم الأخير.
- أيضًا في الخطوة 3 ، قم بتغيير الخلية الهدف إلى الخلية المجاورة للبيانات الأصلية.سيضمن ذلك حصولك على الاسم الأخير وحده وأن بيانات الاسم الأصلي كاملة.
- انقر فوق "إنهاء"
بمجرد حصولك على النتائج ، يمكنك الفرز حسب الاسم الأخير.
يمكنك أيضًا استخدام نص إلى عمود للفصل بين الاسمين الأول والأخير عندما يكون لديك فاصلات كمحددات.
املأه بالجليتر
هناك طريقة سريعة أخرى للحصول على الاسم الأخير وهي استخدام ميزة Flash Fill.
تم تقديمه في Excel 2013 ، يساعد Flash Fill في معالجة البيانات عن طريق تحديد الأنماط.للقيام بذلك ، تحتاج إلى عرض التعبئة السريعة عدة مرات للنتيجة التي تتوقعها.
بمجرد أن يتعرف على النمط ، فإنه يقوم بالباقي بسرعة.
افترض أن لديك مجموعة البيانات التالية من الأسماء.
فيما يلي خطوات الحصول على الاسم الأخير باستخدام Flash Fill واستخدامه في الفرز:
- في الخلية B2 ، أدخل النص "موري".هذا ما تتوقعه في الخلية.
- انتقل إلى الخلية التالية وأدخل الاسم الأخير للاسم في الخلية المجاورة (إليوت في هذه الحالة).
- حدد خليتين
- قم بتمرير المؤشر فوق الجزء الأيمن السفلي من التحديد.ستلاحظ أن المؤشر يتغير إلى رمز زائد.
- انقر نقرًا مزدوجًا فوقه (أو انقر واسحب لأسفل).سيعطيك هذا بعض النتائج في الخلية (من غير المحتمل أن تكون الخلية التي تريدها)
- انقر فوق رمز خيارات الملء التلقائي.
- انقر فوق الملء السريع
سيعطيك هذا نتيجة قد تكون الاسم الأخير في جميع الخلايا.
أقول ربما لأن Flash Fill قد لا يعمل في بعض الحالات.نظرًا لأنه يعتمد على وضع التعرف ، فقد لا يقوم بذلك دائمًا.أو في بعض الأحيان ، قد لا يكون النمط الذي يزيل الشفرات هو النمط الصحيح.
في هذه الحالة ، يجب إدخال النتيجة المتوقعة في خلية واحدة أو أكثر ثم تنفيذ الخطوات من 4 إلى 7.
بعد أن يكون لديك جميع الأسماء الأخيرة في العمود ، يمكنك فرز البيانات بناءً على تلك الأسماء الأخيرة.
لذلك ، يمكنك فرز البيانات حسب الاسم الأخير بأربع طرق مختلفة.أفضل طريقة هي استخدام تقنيات البحث والاستبدال ، ولكن إذا كنت ترغب في جعل نتائجك ديناميكية ، فإن طرق الصيغة هي السبيل للذهاب.
آمل أن تكون قد وجدت هذا البرنامج التعليمي مفيد.
