מחק שורות בהתבסס על ערך תא (או מצב) ב- Excel (מדריך קל)

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

לדוגמה, שקול את הדוגמאות הבאות:

1. יש לך נתוני נציג מכירות ואתה רוצה למחוק את כל הרשומות לאזור או למוצר ספציפי.
2. ברצונך למחוק את כל הרשומות בהן ערך המכירה נמוך מ -100.
3. אתה רוצה למחוק את כל השורות שבהן יש תא ריק.

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

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

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

סנן שורות על בסיס ערך/מצב ולאחר מכן מחק אותו

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

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

בואו נראה שתי דוגמאות בהן תוכלו לסנן את השורות ולמחוק אותן.

מחק שורות המכילות טקסט ספציפי

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

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

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

להלן השלבים למחיקת שורות המבוססות על הערך (כל רשומות המערב התיכון):

1. בחר תא כלשהו במערך הנתונים שממנו ברצונך למחוק את השורות
2. לחץ על הכרטיסייה נתונים
3. בקבוצה 'מיין וסנן', לחץ על סמל המסנן. פעולה זו תחיל מסננים על כל תאי הכותרות במערך הנתונים
4. לחץ על סמל המסנן בתא הכותרת אזור (זהו סמל משולש קטן כלפי מטה בפינה השמאלית העליונה של התא)
5. בטל את הבחירה בכל האפשרויות האחרות למעט האפשרות באמצע מערב (דרך מהירה לעשות זאת היא על ידי לחיצה על האפשרות בחר הכל ולאחר מכן לחיצה על האפשרות באמצע מערב). פעולה זו תסנן את מערך הנתונים ותציג רק רשומות עבור אזור אזור מערב מערב.
6. בחר את כל הרשומות המסוננות
7. לחץ לחיצה ימנית על כל אחד מהתאים שנבחרו ולחץ על 'מחק שורה'
8. בתיבת הדו -שיח שנפתחת, לחץ על אישור. בשלב זה, לא תראה רשומות במערך הנתונים.
9. לחץ על הכרטיסייה נתונים ולחץ על סמל המסנן. פעולה זו תסיר את המסנן ותראה את כל הרשומות למעט הרשומות שנמחקו.

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

כמה קיצורי דרך מועילים שכדאי לדעת כדי להאיץ את התהליך:

1. Control + Shift + L. כדי ליישם או להסיר את המסנן
2. שליטה + - (החזק את מקש הבקרה ולחץ על מקש המינוס) כדי למחוק את התאים/השורות שנבחרו

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

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

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

אוֹ

תוכל להשתמש בשיטות המוצגות בהמשך הדרכה זו (בשיטת מיון או בשיטת מצא הכל)

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

בדיוק כפי שהשתמשתי בשיטת הסינון כדי למחוק את כל השורות המכילות את הטקסט Mid-West, אתה יכול גם להשתמש בתנאי מספר (או תנאי תאריך).

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

להלן השלבים לביצוע פעולה זו:

1. בחר תא כלשהו בנתונים
2. לחץ על הכרטיסייה נתונים
3. בקבוצה 'מיין וסנן', לחץ על סמל המסנן. פעולה זו תחיל מסננים על כל תאי הכותרות במערך הנתונים
4. לחץ על סמל המסנן בתא הכותרת מכירות (זהו סמל משולש קטן כלפי מטה בפינה השמאלית העליונה של התא)
5. העבר את הסמן מעל האפשרות מסנני מספרים. זה יראה לך את כל אפשרויות הסינון הקשורות למספר ב- Excel.
6. לחץ על האפשרות 'פחות מ'.
7. בתיבת הדו -שיח 'סינון אוטומטי מותאם אישית' שנפתחת, הזן את הערך '200' בשדה
8. לחץ על אישור. פעולה זו תסנן ותציג רק את הרשומות שבהן ערך המכירה נמוך מ -200
9. בחר את כל הרשומות המסוננות
10. לחץ לחיצה ימנית על כל אחד מהתאים ולחץ על מחק שורה
11. בתיבת הדו -שיח שנפתחת, לחץ על אישור. בשלב זה, לא תראה רשומות במערך הנתונים.
12. לחץ על הכרטיסייה נתונים ולחץ על סמל המסנן. פעולה זו תסיר את המסנן ותראה את כל הרשומות למעט הרשומות שנמחקו.

ישנם מסנני מספרים רבים בהם תוכל להשתמש ב- Excel - כגון פחות מ/גדול מ, שווה/לא שווה, בין, 10 העליון, מעל או מתחת לממוצע וכו '.

הערה: אתה יכול גם להשתמש במסננים מרובים. לדוגמה, תוכל למחוק את כל השורות שבהן ערך המכירה גדול מ- 200 אך פחות מ- 500. במקרה זה, עליך להשתמש בשני תנאי סינון. תיבת הדו -שיח 'סינון אוטומטי מותאם אישית' מאפשרת שני קריטריונים של סינון (וגם OR).

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

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

מה אם אני רוצה למחוק רק רשומות ממערך הנתונים, אבל רוצה לשמור על הנתונים שנותרו על כנם.

אינך יכול לעשות זאת באמצעות סינון, אך תוכל לעשות זאת באמצעות מיון.

מיין את מערך הנתונים ולאחר מכן מחק את השורות

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

טכניקת מיון זו מומלצת רק כאשר רוצים למחוק את התאים עם הערכים ולא את כל השורות.

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

להלן השלבים לעשות זאת באמצעות מיון:

1. בחר תא כלשהו בנתונים
2. לחץ על הכרטיסייה נתונים
3. בקבוצה מיון וסינון, לחץ על סמל המיון.
4. בתיבת הדו -שיח מיין שנפתחת, בחר אזור בעמודה לפי מיון.
5. באפשרות מיין על, ודא שערכי התא נבחרו
6. באפשרות ההזמנה, בחר A עד Z (או Z עד A, לא ממש משנה).
7. לחץ על אישור. זה ייתן לך את מערך הנתונים הממוין כפי שמוצג להלן (ממוין לפי עמודה B).
8. בחר את כל הרשומות עם אזור אמצע מערב (כל התאים בשורות, לא רק עמודת האזור)
9. לאחר הבחירה, לחץ לחיצה ימנית ולאחר מכן לחץ על מחק. פעולה זו תפתח את תיבת הדו -שיח מחק.
10. ודא שהאפשרות 'העברת תאים למעלה' נבחרה.
11. לחץ על אישור.

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

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

להלן מדריך מפורט כיצד למיין נתונים ב- Excel

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

מצא ובחר את התאים המבוססים על ערך התא ולאחר מכן מחק את השורות

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

לאחר שבחרת בתאים אלה, תוכל למחוק את השורות בקלות.

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

להלן השלבים לביצוע פעולה זו:

1. בחר את מערך הנתונים כולו
2. לחץ על הכרטיסייה דף הבית
3. בקבוצת העריכה, לחץ על האפשרות 'מצא ובחר' ולאחר מכן לחץ על חפש (תוכל גם להשתמש בקיצור המקשים Control + F).
4. בתיבת הדו-שיח חפש והחלף, הזן את הטקסט 'מרכז מערב' בשדה 'מצא מה:'.
5. לחץ על מצא הכל. זה יראה לך באופן מיידי את כל מופעי הטקסט באמצע המערב שאקסל הצליחה למצוא.
6. השתמש בקיצור המקשים Control + A כדי לבחור את כל התאים שמצא Excel. תוכל גם לראות את כל התאים שנבחרו במערך הנתונים.
7. לחץ לחיצה ימנית על כל אחד מהתאים שנבחרו ולחץ על מחק. פעולה זו תפתח את תיבת הדו -שיח מחק.
8. בחר באפשרות 'כל השורה'
9. לחץ על אישור.

השלבים שלעיל ימחקו את כל התאים שבהם ערך האזור הוא מערב מערב.

הערה: מכיוון ש- Find and Replace יכולים להתמודד עם תווים כלליים, תוכל להשתמש בהם בעת מציאת נתונים ב- Excel. לדוגמה, אם ברצונך למחוק את כל השורות שבהן האזור נמצא באמצע מערב או בדרום מערב, תוכל להשתמש ב- '*מַעֲרָב'כטקסט שאפשר למצוא בתיבת הדו -שיח Find and Replace. זה ייתן לך את כל התאים שבהם הטקסט מסתיים במילה מערב.

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

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

זה ה עבור אל תאים מיוחדים option - המאפשרת לך לבחור במהירות את כל התאים הריקים. וברגע שבחרת את כל התאים הריקים, מחיקת אלה היא פשוטה ביותר.

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

להלן השלבים לביצוע פעולה זו:

1. בחר את מערך הנתונים כולו (A1: D16 במקרה זה).
2. הקש על F5 מַפְתֵחַ. פעולה זו תפתח את תיבת הדו -שיח 'עבור' (תוכל גם לקבל תיבת דו -שיח זו מבית -> עריכה -> חפש ובחר -> עבור אל).
3. בתיבת הדו -שיח 'עבור אל', לחץ על הלחצן מיוחד. פעולה זו תפתח את תיבת הדו -שיח 'עבור למיוחד'
4. בתיבת הדו -שיח מעבר למיוחד, בחר 'ריק'.
5. לחץ על אישור.

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

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

בתיבת הדו -שיח מחק, בחר באפשרות 'כל השורה' ולחץ על אישור. פעולה זו תמחק את כל השורות שיש בהן תאים ריקים.

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

סנן ומחק שורות בהתבסס על ערך התא (באמצעות VBA)

השיטה האחרונה שאני הולך להראות לך כוללת מעט VBA.

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

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

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

להלן קוד VBA שיעשה זאת.

Sub DeleteRowsWithSpecificText () 'מקור: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible). מחק סיום תת

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

שים לב שהשתמשתי ב- Offset בקוד שלמעלה כדי לוודא ששורת הכותרת שלי לא נמחקת.

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

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

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

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

Sub DeleteRowsinTables () 'מקור: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl כ- ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible). מחיקת סיום תת

מכיוון ש- VBA רואה את טבלת Excel כאובייקט רשימה (ולא טווח), הייתי צריך לשנות את הקוד בהתאם.

היכן לשים את קוד ה- VBA?

קוד זה צריך להיות ממוקם במערך האחורי של עורך VB במודול.

להלן השלבים שיראו לך כיצד לעשות זאת:

1. פתח את חוברת העבודה שבה ברצונך להוסיף קוד זה.
2. השתמש בקיצור המקשים ALT + F11 כדי לפתוח את חלון עורך VBA.
3. בחלון עורך VBA זה, משמאל, יש חלונית 'סייר פרויקטים' (המפרטת את כל חוברות העבודה ואובייקטים של דפי העבודה). לחץ באמצעות לחצן העכבר הימני על כל אובייקט בחוברת העבודה (שבו אתה רוצה שהקוד הזה יעבוד), העבר את הסמן מעל 'הכנס' ולאחר מכן לחץ על 'מודול'. זה יוסיף את אובייקט המודול לחוברת העבודה ויפתח גם את חלון קוד המודול מימין
4. בחלון המודול (שיופיע מימין), העתק והדבק את הקוד לעיל.

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

1. בחר כל שורה בתוך הקוד ולחץ על מקש F5.
2. לחץ על הלחצן הפעלה בסרגל הכלים בעורך VB
3. הקצה את המאקרו ללחצן או לצורה והפעל אותו על ידי לחיצה עליו בגליון העבודה עצמו
4. הוסף אותו לסרגל הכלים לגישה מהירה והפעל את הקוד בלחיצה אחת.

תוכל לקרוא הכל על הפעלת קוד המאקרו ב- Excel במאמר זה.

הערה: מכיוון שחוברת העבודה מכילה קוד מאקרו של VBA, עליך לשמור אותו בפורמט המאופשר למאקרו (xlsm).