צפה בוידאו - איך למיין לפי שם משפחה ב- Excel
אם אתה עובד עם שמות נתונים של שמות, מיון זו אחת המשימות הנפוצות שתצטרך לבצע לעתים קרובות.
די קל למיין נתונים לפי האלף -בית על בסיס השם המלא, שבו Excel משתמש בתו הראשון של השם למיון.
אבל מה אם אתה רוצה מיין את הנתונים לפי שם המשפחה ב- Excel?
למרות שזה לא פשוט, זה עדיין יכול להיעשות (הרבה תלוי גם באופן שבו נתוני השמות מובנים).
לא משנה באיזו שיטה תשתמש, תצטרך, איכשהו, לחלץ את שם המשפחה מהשם המלא ולהכניס אותו לעמודה נפרדת. לאחר מכן תוכל להשתמש בעמודה זו כדי למיין את הנתונים שלך לפי שם המשפחה לפי האלף -בית.
במדריך אקסל זה אראה לך כיצד למיין טור עם שמות המבוססים על שם המשפחה.
אז בואו נתחיל!
חלץ ומיין לפי שם משפחה באמצעות חיפוש והחלפה
השלב הראשון למיון לפי שם המשפחה הוא קבלת שם המשפחה בעמודה נפרדת.
אתה יכול לעשות זאת על ידי החלפת הכל לפני שם המשפחה עם ריק, כך שנותר לך רק שם המשפחה.
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה למיין את הנתונים האלה לפי האלף -בית באמצעות שם המשפחה.
להלן השלבים למיון לפי שם המשפחה:
- בחר את מערך הנתונים כולל הכותרת (בדוגמה זו הוא יהיה A1: A10)
- העתק אותו בעמודה הסמוכה (אם העמודה הסמוכה אינה ריקה, הכנס עמודה חדשה ולאחר מכן העתק את השמות האלה)
- שנה את שם כותרת העמודה שהועתקה. בדוגמה זו, אני אקרא בשם 'שם משפחה'
- בחר את כל השמות שהועתקו (אל תבחר את הכותרת)
- החזק את מקש Ctrl ולאחר מכן הקש על מקש H. פעולה זו תפתח את תיבת הדו -שיח חפש והחלפה.
- בשדה מצא מה הזן * (סמל כוכבית ואחריו תו רווח)
- השאר את השדה החלף עם ריק
- לחץ על החלף הכל. זה יחליף מיידית את כל השם הפרטי ותישארו רק שמות משפחה.
השלבים שלעיל ישמרו על שם המשפחה ויסירו הכל לפניו. זה עובד היטב גם כשיש לך שמות אמצעיים או קידומות (כגון מר או גברת).
ברגע שיש לך את שמות המשפחה בעמודה הסמוכה, תוכל למיין בקלות את מערך הנתונים (כולל השמות המלאים) לפי האלף -בית על בסיס שם המשפחה.
להלן השלבים למיון לפי שם המשפחה:
- בחר את מערך הנתונים כולו עם כותרות (כולל השמות המלאים ושמות המשפחה שחולצו). תוכל גם לכלול עמודות אחרות שברצונך למיין יחד עם השמות
- לחץ על הכרטיסייה נתונים
- לחץ על מיון
- בתיבת הדו -שיח מיון, ודא שבחירת 'הנתונים שלי מכילים כותרות'.
- באפשרות 'מיין לפי', בחר את שם העמודה שיש לה רק את שם המשפחה
- ב'מיין ', בחר' ערכי תאים '
- באפשרות ההזמנה, בחר 'A עד Z'
- לחץ על אישור
השלבים שלעיל ימיין את כל מערך הנתונים שנבחר על בסיס שם המשפחה.
לאחר שתסיים, תוכל למחוק את העמודה בעלת שם המשפחה.
טיפ למקצוענים: בכל נקודת זמן, אם אתה חושב שאתה עשוי להזדקק לנתונים המקוריים בחזרה, עליך לקבל דרך לבטל את המיון של מערך הנתונים הזה. לשם כך, בעמודה הסמוכה (משמאל או מימין), יש מספרים סידוריים לפני המיון. עכשיו, אם אתה צריך את הנתונים המקוריים בחזרה, אתה מקבל אותם על ידי מיון לפי המספרים.לחלץ ולכתוב לפי שם משפחה באמצעות נוסחה
למרות שהשיטה המוצגת למעלה (באמצעות חיפוש והחלפה) היא מה שאני מעדיף לקבל את כל שמות המשפחה ולמיין על סמך זה, מגבלה אחת שלה היא שהנתונים המתקבלים הם סטטיים.
המשמעות היא שאם אוסיף עוד שמות לרשימה שלי, אצטרך לבצע את אותו תהליך שוב כדי לקבל את שמות המשפחה.
אם זה משהו שאתה לא רוצה, תוכל להשתמש בשיטת הנוסחה למיון נתונים לפי שמות משפחה.
נניח שיש לך את מערך הנתונים כפי שמוצג להלן.
להלן הנוסחה שתחלץ את שם המשפחה מהשם המלא:
= ימינה (A2, LEN (A2) -FIND ("", A2))
הנוסחה לעיל מסתמכת על התבנית עם שם מלא (המכילה רק את השם הפרטי ושם המשפחה בדוגמה זו). התבנית היא שתהיה תו רווח בין השם הפרטי ושם המשפחה.
הפונקציה FIND משמשת לקבלת המיקום של דמות המרחב. ערך זה לאחר מכן מופחת מהאורך הכולל של השם כדי לקבל את מספר התווים הכולל בשם המשפחה.
ערך זה משמש לאחר מכן בפונקציה RIGHT כדי לקבל את שם המשפחה.
לאחר שתהיה לך עמודת שם המשפחה, תוכל למיין נתונים אלה (מידע זה מפורט בשיטה הראשונה בפירוט).
הנוסחה לעיל תפעל כאשר יש לך רק שמות פרטיים ושמות משפחה.
אבל מה אם יש לך גם שם אמצעי. או שמא יש הצדעה לפני השם (כגון מר או גב ')
במקרה כזה, עליך להשתמש בנוסחה שלהלן:
= ימינה (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))
הנוסחה לעיל מוצאת את המיקום של תו הרווח האחרון ולאחר מכן משתמשת בו כדי לחלץ את שם המשפחה.
אני ממליץ לך להשתמש בנוסחה השנייה בכל המקרים, והיא הוכחה יותר לטיפש ויכולה להתמודד עם כל המקרים (כל עוד שם המשפחה נמצא בסוף השם).
הערה: שתי נוסחאות אלה מסתמכות על התנאי שיש רק תו רווח אחד בין כל רכיב שם. במקרה שיש רווחים כפולים, או רווחים מובילים/נגררים, נוסחה זו תיתן תוצאות לא נכונות. במקרה כזה, עדיף להשתמש בפונקציית TRIM כדי להיפטר תחילה מכל רווח מוביל, נגרר וכפול, ולאחר מכן להשתמש בנוסחה שלעיל.
למרות שזה אולי נראה כמו שיטה מסובכת, היתרון בשימוש בנוסחה הוא בכך שהיא הופכת את התוצאות לדינאמיות. אם אתה מוסיף שמות נוספים לרשימה שלך, כל שעליך לעשות הוא להעתיק את הנוסחה וזה ייתן לך את שם המשפחה.
שימוש בטקסט לעמודות
טקסט לעמודות היא שוב דרך פשוטה וקלה לפצל תאים ב- Excel.
אתה יכול לציין את המפריד (כגון פסיק או רווח) ולהשתמש בו כדי לפצל את תוכן התא. ברגע שיש לך את האלמנטים המפוצלים בעמודות נפרדות, תוכל להשתמש בעמודה בעלת שם המשפחה לאלף -פי של הנתונים.
נניח שיש לך מערך נתונים כפי שמוצג להלן:
להלן השלבים לשימוש בטקסט לעמודה למיון לפי שם המשפחה:
- בחר את העמודה בעלת השם (לא כולל הכותרת)
- לחץ על הכרטיסייה נתונים
- בקבוצה 'כלי נתונים', לחץ על האפשרות טקסט לעמודות. פעולה זו תפתח את אשף הטקסט לעמודות
- בשלב 1 של 'אשף המרת טקסט לעמודות', בחר 'מופרד' ולחץ על הבא
- בשלב 2, בחר 'רווח' כמפריד (והסר את הסימון של כל דבר אחר אם נבחר) ולאחר מכן לחץ על הלחצן הבא.
- בשלב 3, בחר את עמודת השם הפרטי בתצוגה המקדימה של נתונים ולאחר מכן בחר באפשרות 'אל תייבא עמודות (דלג)'. זה מבטיח שהשם הפרטי אינו חלק מהתוצאה ותקבל רק את שם המשפחה.
- כמו כן, בשלב 3, שנה את תא היעד לתא הסמוך לנתונים המקוריים. זה יוודא שאתה מקבל את שם המשפחה בנפרד ונתוני השמות המקוריים שלמים.
- לחץ על סיום
לאחר שתקבל את התוצאה, תוכל למיין לפי שם המשפחה.
תוכל גם לשלוח טקסט לעמודות כדי להפריד בין שמות משפחה ושם משפחה כאשר יש לך פסיק כמפריד.
שימוש במילוי פלאש
דרך נוספת ומהירה להשיג את שמות המשפחה היא באמצעות התכונה Fill Fill.
Flash Fill הוצג ב- Excel 2013 וזה עוזר לתפעל את הנתונים על ידי זיהוי דפוסים. כדי שזה יעבוד, עליך להציג Flash Fill את התוצאה שאתה מצפה לה כמה פעמים.
ברגע שהוא מזהה את התבנית, הוא יעשה עבורך במהירות את שאר העבודה.
נניח שיש לך את מערך הנתונים של השמות שלהלן.
להלן השלבים לשימוש במילוי Flash כדי לקבל את שם המשפחה ולאחר מכן למיין באמצעותו:
- בתא B2, הזן את הטקסט 'מאורי'. זו התוצאה שאתה מצפה לתא.
- עבור לתא הבא והזן את שם המשפחה של השם בתא הסמוך (אליוט בדוגמה זו).
- בחר את שני התאים
- העבר את הסמן מעל החלק הימני התחתון של הבחירה. תבחין כי הסמן משתנה לסמל פלוס.
- לחץ עליו פעמיים (או לחץ וגרור אותו כלפי מטה). זה ייתן לך תוצאה מסוימת בתאים (לא סביר שתהיה התוצאה שאתה רוצה)
- לחץ על הסמל אפשרויות מילוי אוטומטי.
- לחץ על מילוי פלאש
זה ייתן לך את התוצאה שכנראה תהיה שמות המשפחה בכל התאים.
אני אומר שסביר להניח שכן מילוי פלאש עשוי שלא לפעול במקרים מסוימים. מכיוון שזה תלוי בזיהוי תבנית, יתכן שהיא לא תוכל לעשות זאת תמיד. או שלפעמים, הדפוס שהוא מפענח אינו יכול להיות הנכון.
במקרים כאלה, עליך להזין תוצאה צפויה בתא אחד או שניים נוספים ולאחר מכן לבצע שלבים 4-7.
לאחר שיהיה לך את כל שמות המשפחה בעמודה, תוכל למיין את הנתונים על סמך שמות המשפחה האלה.
אז אלו ארבע דרכים שונות בהן ניתן להשתמש כדי למיין נתונים לפי שם המשפחה. השיטה הטובה ביותר תהיה להשתמש בטכניקת Find and Replace, אך אם ברצונך להפוך את התוצאות לדינאמיות, שיטת הנוסחה היא הדרך הטובה ביותר.
מקווה שמצאת הדרכה שימושית.
