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

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

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

שיטה 1. חפש תאים ריקים

זו אולי לא הכי נוחה, אבל בהחלט ראוי לציין שהדרך הקלה ביותר.

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

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

1. בחר טווח עם ערים (B2:B26)
2. לחץ על המקש F5 ולאחר מכן לחץ על להבליט (עבור לספיישל) או בחר בכרטיסייה בית — מצא ובחר — בחר קבוצת תאים (בית - מצא ובחר - עבור אל מיוחד).
3. בחלון שנפתח, בחר באפשרות תאים ריקים (ריק) ולחץ OK - יש לבחור את כל התאים הריקים בעמודה הראשונה של הטבלה שלנו.
4. כעת בחר בכרטיסייה עמוד הבית פיקוד מחק - מחק שורות מהגיליון (מחק - מחק שורות) או הקש על קיצור המקלדת Ctrl+מינוס – והמשימה שלנו נפתרה.

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

שיטה 2: חפש שורות ריקות

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

עיין בטבלה הבאה, למשל, עבור מקרה כזה:

כאן הגישה תהיה קצת יותר מסובכת:

1. הזינו בתא A2 את הפונקציה COUNT (COUNTA), שיחשב את מספר התאים המלאים בשורה מימין ותעתיק את הנוסחה הזו לטבלה כולה:
2. בחר תא A2 והפעל את המסנן עם הפקודה נתונים - מסנן (נתונים - מסנן) או קיצור מקלדת Ctrl+משמרת+L.
3. בואו נסנן אפסים לפי העמודה המחושבת, כלומר כל השורות שבהן אין נתונים.
4. נותר לבחור את השורות המסוננות ולמחוק אותן עם הפקודה בית - מחק -' מחק שורות מהגיליון (בית - מחק - מחק שורות) או קיצור מקלדת Ctrl+מינוס.
5. אנו מכבים את המסנן ומקבלים את הנתונים שלנו ללא שורות ריקות.

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

שיטה 3. מאקרו להסרת כל השורות והעמודות הריקות בגיליון

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

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

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы הגדר rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub  

סגור את העורך וחזור לאקסל. 

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

שיטה 4: Power Query

דרך נוספת לפתור את הבעיה שלנו ותרחיש נפוץ מאוד היא להסיר שורות ועמודות ריקות ב- Power Query.

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

כעת אנו משתמשים בפקודה Data – Get data – From table / range (Data – Get Data – From table / range) וטוען הכל לתוך Power Query:

ואז הכל פשוט:

1. אנו מוחקים שורות ריקות בפקודה Home – Reduce Lines – Delete Lines – Delete Lines ריקות (Home – Remove Rows – Remove שורות ריקות).
2. לחץ לחיצה ימנית על כותרת העמודה הראשונה של העיר ובחר בפקודה Unpivot Other Columns מתפריט ההקשר. הטבלה שלנו תהיה, כפי שהיא נקראת באופן טכני נכון, מנורמל – הומר לשלוש עמודות: עיר, חודש וערך מהצומת של העיר וחודש מהטבלה המקורית. הייחודיות של הפעולה הזו ב-Power Query היא שהיא מדלגת על תאים ריקים בנתוני המקור, וזה מה שאנחנו צריכים:
3. כעת אנו מבצעים את הפעולה ההפוכה - אנו הופכים את הטבלה המתקבלת חזרה לדו מימדית על מנת להחזיר אותה לצורתה המקורית. בחר את העמודה עם חודשים ובכרטיסייה טרנספורמציה לבחור צוות עמודת ציר (טרנספורמציה - עמודת ציר). בחלון שנפתח, כעמודת ערכים, בחר את האחרון (Value), ובאפשרויות המתקדמות – את הפעולה לא לצבור (לא לצבור):
4. נותר להעלות את התוצאה בחזרה לאקסל עם הפקודה בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען אל...)

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