הדקויות של עבודה עם מעברי שורות באקסל

מעברי שורות בתוך אותו תא, שנוספו באמצעות קיצור מקשים אחר+זן זה דבר מאוד נפוץ ונפוץ. לפעמים הם מיוצרים על ידי המשתמשים עצמם כדי להוסיף יופי לטקסט ארוך. לפעמים העברות כאלה מתווספות אוטומטית בעת פריקת נתונים מתוכניות עבודה כלשהן (שלום 1C, SAP וכו') הבעיה היא שאז אתה צריך לא רק להתפעל מטבלאות כאלה, אלא לעבוד איתן - ואז העברות התווים הבלתי נראים האלה יכולות להיות בְּעָיָה. ויכול להיות שהם לא יהפכו - אם תדע איך לטפל בהם נכון.

בואו נסתכל על סוגיה זו ביתר פירוט.

הסרת מעברי שורות על ידי החלפה

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

שילוב יעזור Ctrl+J - זו האלטרנטיבה אחר+זן בתיבות דו-שיח של Excel או בשדות קלט:

שימו לב שאחרי שתכניסו את הסמן המהבהב בשדה העליון ותלחצו Ctrl+J – שום דבר לא יופיע בשדה עצמו. אל תפחד - זה נורמלי, הסמל אינו נראה 🙂

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

ניואנס: לאחר ביצוע ההחלפה שהוכנסה עם Ctrl+J אופי בלתי נראה נשאר בשטח למצוא ועלול להפריע בעתיד - אל תשכח למחוק אותו על ידי מיקום הסמן בשדה זה ומספר פעמים (למען אמינות) לחיצה על המקשים מחק и Backspace.

הסרת מעברי שורות עם נוסחה

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

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

החלפת מעברי שורות בנוסחה

ואם אתה רוצה לא רק למחוק, אלא להחליף אחר+זן על, למשל, חלל, אז תידרש בנייה אחרת, קצת יותר מורכבת:

כדי להגדיר מקף בלתי נראה אנו משתמשים בפונקציה סמל (לְהַשְׁחִיר), שמוציא תו לפי הקוד שלו (10). ואז הפונקציה תחליף (תחליף) מחפש את המקפים שלנו בנתוני המקור ומחליף אותם בכל טקסט אחר, למשל ברווח.

חלוקה לעמודות לפי מעבר שורה

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

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

לאחר לחיצה על הַבָּא (הבא) ונעבור על כל שלושת השלבים של האשף, נקבל את התוצאה הרצויה:

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

חלקו לשורות באמצעות Alt + Enter דרך Power Query

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

זה לוקח הרבה זמן לעשות את זה ידנית, זה קשה עם נוסחאות, לא כל אחד יכול לכתוב מאקרו. אבל בפועל, בעיה זו מתרחשת לעתים קרובות יותר ממה שהיינו רוצים. הפתרון הפשוט והקל ביותר הוא להשתמש בתוסף Power Query עבור משימה זו, המובנית ב-Excel מאז 2016, ולגירסאות קודמות 2010-2013 ניתן להוריד אותה בחינם לחלוטין מאתר מיקרוסופט.

כדי לטעון את נתוני המקור לתוך Power Query, תחילה עליך להמיר אותם ל"טבלה חכמה" עם קיצור מקשים Ctrl+T או על ידי כפתור עיצוב כטבלה כרטיסייה עמוד הבית (בית - עיצוב כטבלה). אם מסיבה כלשהי אינך רוצה או לא יכול להשתמש ב"טבלאות חכמות", אז אתה יכול לעבוד עם "טיפשים". במקרה זה, פשוט בחר את הטווח המקורי ותן לו שם בכרטיסייה נוסחאות – מנהל שמות – חדש (נוסחאות - מנהל שמות - חדש).

אחרי זה, בכרטיסייה נתונים (אם יש לך Excel 2016 ואילך) או בכרטיסייה שאילתת כוח (אם יש לך אקסל 2010-2013) תוכל ללחוץ על הכפתור משולחן/טווח (מטבלה/טווח)כדי לטעון את הטבלה שלנו לעורך Power Query:

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

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

כדי שהכל מחולק לשורות, ולא לעמודות - אל תשכח להחליף את הבורר שורות (לפי שורות) בקבוצת האפשרויות המתקדמות.

כל מה שנותר הוא ללחוץ OK ותשיג את מה שאתה רוצה:

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

חשוב לציין כי בעת שימוש ב- Power Query, עליך לזכור שכאשר נתוני המקור משתנים, התוצאות אינן מתעדכנות אוטומטית, מכיוון. אלו לא נוסחאות. כדי לעדכן, עליך ללחוץ לחיצה ימנית על הטבלה הסופית בגיליון ולבחור בפקודה עדכן ושמור (לְרַעֲנֵן) או לחץ על הכפתור עדכון כל כרטיסייה נתונים (נתונים - רענן הכל).

מאקרו לחלוקה לשורות באמצעות Alt+Enter

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

Sub Split_By_Rows() עמום תא כטווח, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'קבע את מספר השברים cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'הוסף שורות ריקות מתחת לתא.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'הכנס לתוכם נתונים מהמערך Set cell = cell.Offset(n) + 1, 0) 'עבור לתא הבא Next i End Sub  

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

וואלה! מתכנתים הם, למעשה, אנשים מאוד עצלנים שמעדיפים לעבוד קשה פעם אחת ואז לא לעשות כלום 🙂

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