הסרת תאים ריקים מטווח

ניסוח הבעיה

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

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

שיטה 1. גס ומהיר

1. בחירת הטווח המקורי
2. לחץ על המקש F5, כפתור הבא להבליט (מיוחד). בחלון שנפתח, בחר תאים ריקים(ריק) ולחץ על OK.

   

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

3. אנו נותנים פקודה בתפריט למחיקת תאים נבחרים: לחץ באמצעות לחצן העכבר הימני- מחק תאים (מחק תאים) עם העברה כלפי מעלה.

שיטה 2: נוסחת מערך

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

תן שם לטווח B3:B10 יש ריק, טווח D3:D10 – אין ריק. הטווחים חייבים להיות בדיוק באותו גודל, ויכולים להיות ממוקמים בכל מקום ביחס זה לזה.

כעת בחר את התא הראשון של הטווח השני (D3) והזן לתוכו את הנוסחה המפחידה הזו:

=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);"";INDIRECT(ADDRESS(LOWEST((IF(Empty<>“",ROW(Empty);ROW() + ROWS(יש ריקים))); LINE()-ROW(אין ריקים)+1); COLUMN(יש ריקים); 4)))

בגרסה האנגלית זה יהיה:

=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),",",INDIRECT(ADDRESS(SMALL((IF(Empty<>“",ROW(Empty),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))

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

שיטה 3. פונקציה מותאמת אישית ב-VBA

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

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

פונקציה NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Every Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1) ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End Function  

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

1. בחר טווח מספיק של תאים ריקים, למשל F3:F10.
2. עבור לתפריט הכנס - פונקציה (הוסף - פונקציה)או לחץ על הכפתור הכנס פונקציה (הכנס פונקציה) כרטיסייה נוסחה (נוסחאות) בגרסאות חדשות יותר של אקסל. בקטגוריה משתמש הוגדר (משתמש הוגדר) לבחור את הפונקציה שלנו נובליים.
3. ציין את טווח המקור עם חללים (B3:B10) כארגומנט הפונקציה ולחץ Ctrl + Shift + Enterכדי להזין את הפונקציה כנוסחת מערך.

:

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