כיצד למיין לפי שם משפחה באקסל (מדריך קל)

אם אתה עובד עם מערך נתונים של שמות, מיון זה הוא אחת המשימות הנפוצות שעליך לבצע לעתים קרובות.

קל למיין נתונים בסדר אלפביתי לפי שם מלא, כאשר Excel משתמש בתו הראשון של השם כדי למיין.

עם זאת, אם אתה רוצהמיין נתונים לפי שם משפחה באקסלהתנהגותמייןאיך לעשות?

למרות שזה לא כל כך פשוט, זה עדיין יכול להיעשות (תלוי גם הרבה באופן שבו נתוני השם בנויים).

לא משנה באיזו שיטה אתה משתמש, אתה צריך איכשהו לחלץ את שם המשפחה מהשם המלא ולשים אותו בעמודה נפרדת.לאחר מכן תוכל להשתמש בעמודה זו כדי למיין את הנתונים בסדר אלפביתי לפי שם משפחה.

במדריך זה של Excel, אני אראה לך כיצד למיין עמודה עם שמות המבוססים על שם משפחה.

אז בואו נתחיל!

חלץ ומיון לפי שם משפחה באמצעות חיפוש והחלפה

הצעד הראשון למיון לפי שם משפחה הוא לקבל את שם המשפחה בעמודה נפרדת.

אתה יכול לעשות זאת על ידי החלפת כל מה לפני שם המשפחה בחסר, כך שתישאר רק עם שם המשפחה.

נניח שיש לך מערך נתונים כמו זה שלמטה, ואתה רוצה למיין את הנתונים האלה בסדר אלפביתי לפי שם משפחה.

להלן השלבים למיון לפי שם משפחה:

1. בחר את מערך הנתונים שמכיל את הכותרת (במקרה זה יהיה A1:A10)
2. העתק אותו לעמודה הסמוכה (אם העמודה הסמוכה לא ריקה, הוסף עמודה חדשה ולאחר מכן העתק את השמות)
3. שנה את שם כותרות העמודות שהועתקו.בדוגמה זו, אקרא לזה "שם משפחה"
4. בחר את כל השמות שהועתקו (אל תבחר כותרות)
5. החזק את מקש Control לחוץ ולאחר מכן הקש על מקש H.פעולה זו תפתח את תיבת הדו-שיח 'מצא והחלפה'.
6. בשדה מצא מה, הזן * (כוכבית ואחריה תו רווח)
7. השאר את השדה 'החלף ב' ריק
8. לחץ על החלף הכל.זה יחליף את כל השמות הפרטיים בבת אחת ותישארו עם שמות משפחה בלבד.

השלבים שלעיל ישמרו על שם המשפחה וימחקו את כל מה שלפניו.זה עובד גם אם יש לך שם אמצעי או קידומת כגון מר או גברת.

ברגע שיש לך שמות משפחה בעמודות סמוכות, תוכל למיין בקלות את מערך הנתונים (כולל שמות מלאים) לפי שם משפחה.

להלן השלבים למיון לפי שם משפחה:

1. בחר את מערך הנתונים כולו עם כותרות (כולל שמות מלאים ושמות משפחה שחולצו).אתה יכול גם לכלול עמודות אחרות כדי למיין עם השם
2. לחץ על הכרטיסייה נתונים
3. לחץ כדי למיין
4. בתיבת הדו-שיח מיון, ודא שהנתונים שלי כוללים כותרות מסומנת.
5. באפשרות "מיין לפי", בחר את שם העמודה עם שם משפחה בלבד
6. במיון לפי, בחר ערך תא
7. באפשרות "הזמנה", בחר "א עד ת"
8. לחץ על אישור

השלבים שלמעלה ימינו את כל מערך הנתונים שנבחר על סמך שם המשפחה.

לאחר שתסיים, תוכל לשחרר את העמודה עם שם המשפחה.

טיפים למקצוענים: בכל עת שאתה חושב שאולי תצטרך לחזור לנתונים המקוריים, תהיה לך דרך לבטל מיון מערך הנתונים.לשם כך, בעמודות סמוכות (משמאל או ימין), ישנם מספרי רצף לפני המיון.כעת, אם אתה צריך להחזיר את הנתונים המקוריים, אתה יכול לקבל אותם על ידי מיון על סמך מספרים.

חלץ ומיון לפי שם משפחה באמצעות נוסחה

בעוד שהשיטה המוצגת לעיל (באמצעות find and replace) היא מה שאני מעדיף לקבל את כל שמות המשפחה ולמיין על סמך זה, אחת המגבלות שלה היא שהנתונים המתקבלים הם סטטיים.

זה אומר שאם אוסיף עוד שמות פרטיים לרשימה, אצטרך לעבור שוב את אותו תהליך כדי לקבל את שם המשפחה.

אם זה לא מה שאתה רוצה, אתה יכול להשתמש בשיטת נוסחה כדי למיין את הנתונים לפי שם משפחה.

נניח שיש לך את מערך הנתונים המוצג להלן.

הנה הנוסחה לחילוץ שם המשפחה מהשם המלא:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

הנוסחה לעיל מסתמכת על תבנית עם שמות מלאים (בדוגמה זו רק שמות פרטיים ושמות משפחה).התבנית היא שיהיה תו רווח בין השם הפרטי לשם המשפחה.

הפונקציה FIND משמשת כדי לקבל את המיקום של תו הרווח.לאחר מכן ערך זה מופחת מהאורך הכולל של השם הפרטי כדי לקבל את המספר הכולל של התווים בשם המשפחה.

לאחר מכן השתמש בערך זה בפונקציה RIGHT כדי לקבל את שם המשפחה.

ברגע שיש לך את עמודת שם המשפחה, תוכל למיין נתונים אלה (זה מפורט בשיטה הראשונה).

הנוסחה לעיל תעבוד כאשר יש לך רק שם פרטי ושם משפחה.

אבל מה אם יש לך גם שם אמצעי.או אולי יש הצדעה לפני השם (למשל מר או גב')

במקרה זה, עליך להשתמש בנוסחה הבאה:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

הנוסחה לעיל מוצאת את המיקום של תו הרווח האחרון ולאחר מכן משתמשת בו כדי לחלץ את שם המשפחה.

אני מציע לך להשתמש בנוסחה השנייה בכל המקרים, היא פשוטה יותר ומטפלת בכל המקרים (כל עוד שם המשפחה נמצא בסוף השם הפרטי).

הערה: שתי נוסחאות אלו מסתמכות על התנאי שיש רק תו רווח אחד בין כל רכיב שם.נוסחה זו תיתן תוצאות שגויות אם יש רווחים כפולים או רווחים מובילים/נגררים.במקרה זה, עדיף להשתמש בפונקציית TRIM כדי להסיר תחילה רווחים מובילים, נגררים וכפולים, ולאחר מכן להשתמש בנוסחה לעיל.

למרות שזו עשויה להיראות כמו גישה מסובכת, היתרון בשימוש בנוסחה הוא בכך שהיא הופכת את התוצאות לדינמיות.אם תוסיף עוד שמות לרשימה, כל מה שאתה צריך לעשות הוא להעתיק את הנוסחה והיא תיתן לך את שם המשפחה.

השתמש בטקסט לעמודות

טקסט לעמודות היא עוד דרך קלה לפצל תאים ב- Excel.

אתה יכול לציין תוחם (כגון פסיק או רווח) ולהשתמש בו כדי לפצל את התוכן של תא.לאחר פיצול אלמנטים בעמודות נפרדות, ניתן להשתמש בעמודה עם שם המשפחה כדי לסדר את הנתונים באלפביתי.

נניח שיש לך מערך נתונים שנראה כך:

להלן השלבים למיון לפי שם משפחה באמצעות טקסט לעמודה:

1. בחר עמודות עם שמות (לא כולל כותרות)
2. לחץ על הכרטיסייה נתונים
3. בקבוצה כלי נתונים, לחץ על האפשרות טקסט לעמודות.פעולה זו תפתח את אשף טקסט לעמודות
4. בשלב 1 של אשף המרת טקסט לעמודות, בחר מופרד ולחץ על הבא
5. בשלב 2, בחר "רווח" כמפריד (בטל את הסימון של כל דבר אחר אם מסומן) ולחץ על הלחצן הבא.
6. בשלב 3, בחר את עמודת השם הפרטי בתצוגה המקדימה של הנתונים, ולאחר מכן בחר באפשרות "אל תייבא עמודה (דלג)".זה מבטיח שהשם הפרטי אינו חלק מהתוצאה, אתה מקבל רק את שם המשפחה.
7. גם בשלב 3, שנה את תא היעד לתא הסמוך לנתונים המקוריים.זה יבטיח שתקבל את שם המשפחה בלבד ושנתוני השם המקוריים מלאים.
8. לחץ על סיום

ברגע שיש לך את התוצאות, תוכל למיין לפי שם משפחה.

אתה יכול גם להשתמש בטקסט לעמודה כדי להפריד בין שם פרטי ושמות משפחה כאשר יש לך פסיקים כמפרידים.

ממלאים בנצנצים

דרך מהירה נוספת לקבל את שם המשפחה היא להשתמש בתכונת Flash Fill.

Flash Fill, שהוצג ב-Excel 2013, עוזר לתמרן נתונים על ידי זיהוי דפוסים.כדי לעשות זאת, עליך להציג Flash Fill מספר פעמים עבור התוצאה שאתה מצפה.

ברגע שהוא מזהה את הדפוס, הוא עושה במהירות את השאר בשבילך.

נניח שיש לך את מערך הנתונים הבא של שמות.

להלן השלבים לקבלת שם המשפחה באמצעות Flash Fill ולהשתמש בו כדי למיין:

1. בתא B2, הזן את הטקסט "מאורי".זה מה שהיית מצפה בתא.
2. עבור לתא הבא והזן את שם המשפחה של השם בתא הסמוך (אליוט במקרה זה).
3. בחר שני תאים
4. העבר את הסמן מעל החלק הימני התחתון של הבחירה.תבחין שהסמן משתנה לסמל פלוס.
5. לחץ עליו פעמיים (או לחץ וגרור מטה).זה ייתן לך תוצאה כלשהי בתא (לא סביר לזו שאתה רוצה)
6. לחץ על הסמל 'אפשרויות מילוי אוטומטי'.
7. לחץ על מילוי מהיר

זה ייתן לך תוצאה שעשויה להיות שם המשפחה בכל התאים.

אני אומר אולי כי ייתכן ש-Flash Fill לא יעבוד במקרים מסוימים.מכיוון שזה תלוי במצב הזיהוי, ייתכן שהוא לא תמיד יעשה זאת.או לפעמים, ייתכן שהתבנית שהוא מפענח אינה התבנית הנכונה.

במקרה זה, עליך להזין את התוצאה הצפויה בתא אחד או יותר ולאחר מכן לעבור את שלבים 4-7.

לאחר שתכלול את כל שמות המשפחה בעמודה, תוכל למיין את הנתונים על סמך אותם שמות משפחה.

לכן, אתה יכול למיין את הנתונים לפי שם משפחה בארבע דרכים שונות.הדרך הטובה ביותר היא להשתמש בטכניקת חיפוש והחלפה, אבל אם אתה רוצה להפוך את התוצאות שלך לדינמיות, שיטת הנוסחה היא הדרך ללכת.

מקווה שמצאת את המדריך הזה שימושי.