כיצד לפצל תאים ב- Excel (להפריד למספר עמודות)

יכולים להיות מצבים שבהם עליך לפצל תאים ב- Excel. אלה יכולים להיות כאשר אתה מקבל את הנתונים ממאגר נתונים או שאתה מעתיק אותם מהאינטרנט או מקבל אותם מעמית.

דוגמה פשוטה שבה אתה צריך לפצל תאים ב- Excel היא כשיש לך שמות מלאים וברצונך לפצל אותם לשם פרטי ולשם משפחה.

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

כיצד לפצל תאים ב- Excel

במדריך זה תלמד כיצד לפצל תאים ב- Excel באמצעות הטכניקות הבאות:

• שימוש בתכונה טקסט לעמודה.
• שימוש בפונקציות טקסט של Excel.
• שימוש במילוי Flash (זמין בשנים 2013 ו -2016).

בואו נתחיל!

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

להלן רשימה של שמות של כמה מהדמויות הבדיוניות האהובות עלי ואני רוצה לפצל את השמות האלה לתאים נפרדים:

להלן השלבים לפיצול שמות אלה לשם הפרטי ולשם המשפחה:

• בחר את התאים שבהם יש לך את הטקסט שברצונך לפצל (במקרה זה A2: A7).
• לחץ על הכרטיסייה נתונים
• בקבוצה 'כלי נתונים', לחץ על 'טקסט לעמודות'.
• באשף המרת טקסט לעמודות:
  • שלב 1 מתוך 3 באשף טקסט לעמודות: ודא שנבחר מסומן (זוהי בחירת ברירת המחדל). זה יאפשר לך להפריד בין השם הפרטי ושם המשפחה על סמך מפריד שצוין (מקש רווח במקרה זה).
  • לחץ על הבא.
  • שלב 2 מתוך 3 אשף טקסט לעמודות: בחר רווח כמפריד ומבטל את הבחירה בכל השאר. תוכל לראות כיצד תראה התוצאה שלך בקטע תצוגה מקדימה של נתונים בתיבת הדו -שיח.
  • לחץ על הבא.
  • שלב 3 מתוך 3 אשף טקסט לעמודות: בשלב זה, תוכל לציין את תבנית הנתונים והיכן ברצונך לקבל את התוצאה. אני אשמור את פורמט הנתונים כללי מכיוון שיש לי נתוני טקסט לפצל. יעד ברירת המחדל הוא A2 ואם תמשיך בכך, הוא יחליף את מערך הנתונים המקורי. אם ברצונך לשמור על הנתונים המקוריים על כנם, בחר תא אחר כיעד. במקרה זה, B2 נבחר.
  • לחץ על סיום.

זה יפצל באופן מיידי את הטקסט של התא לשתי עמודות שונות.

הערה:

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

פיצול תאים ב- Excel באמצעות פונקציות טקסט

פונקציות טקסט של Excel נהדרות כאשר אתה רוצה לחתוך ולחתוך מחרוזות טקסט.

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

פיצול שמות שיש להם שם פרטי ושם משפחה

נניח שיש לך את אותם הנתונים כפי שמוצג להלן:

חילוץ השם הפרטי

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

= LEFT (A2, SEARCH ("", A2) -1)

נוסחה זו תזהה את תו הרווח הראשון ולאחר מכן תחזיר את כל הטקסט לפני תו הרווח:

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

חילוץ שם המשפחה

באופן דומה, כדי לקבל את שם המשפחה, השתמש בנוסחה הבאה:

= ימינה (A2, LEN (A2)-חיפוש ("", A2))

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

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

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

פיצול שמות שיש להם שם פרטי, שם אמצעי ושם משפחה

ייתכנו מקרים בהם תקבל שילוב של שמות שבהם יש לשמות מסוימים גם שם אמצעי.

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

חילוץ השם הפרטי

כדי לקבל את השם הפרטי:

= LEFT (A2, SEARCH ("", A2) -1)

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

חילוץ השם האמצעי

כדי לקבל את השם האמצעי:

= IFERROR (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")

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

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

חילוץ שם המשפחה

כדי לקבל את שם המשפחה, השתמש בנוסחה שלהלן:

= IF (LEN (A2) -LEN (SUBSTITUTE (A2, "", "")) = 1, Right (A2, LEN (A2) -SEARCH ("", A2)), Right (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))

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

אבל אם יש 2, אז הוא מזהה את תו הרווח השני ומחזיר את מספר התווים אחרי הרווח השני.

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

פיצול תאים ב- Excel באמצעות מילוי פלאש

Flash Fill היא תכונה חדשה שהוצגה ב- Excel 2013.

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

לדוגמה, בואו ניקח את השם הפרטי ונתוני שם המשפחה:

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

כך תוכל לחלץ את השם הפרטי מהרשימה באמצעות מילוי פלאש:

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

כיצד פועל מילוי פלאש?

Flash Fill מחפש את התבניות במערך הנתונים ומשחזר את התבנית.

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

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

אם אחלץ את השם האמצעי במקרה כזה, Flash Fill יחזיר בטעות את שם המשפחה למקרה שאין שם פרטי.

למען האמת, זה עדיין קירוב טוב למגמה. עם זאת, זה לא מה שרציתי.

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

הנה דוגמה נוספת שבה Flash Fill עובד בצורה מבריקה.

יש לי מערך כתובות שמהן אני רוצה לחלץ במהירות את העיר.

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

באופן דומה, תוכל לפצל את הכתובת ולחלץ כל חלק מהכתובת.

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

במקרה שתנסה להשתמש ב- Fill Fill כשאין תבנית, היא תראה לך שגיאה כפי שמוצג להלן:

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

מקווה שמצאת הדרכה זו של Excel שימושית.