סינון אוטומטי של Excel VBA: מדריך מלא עם דוגמאות

הרבה פונקציות של Excel זמינות גם לשימוש ב- VBA - ו- סינון אוטומטי שיטה היא פונקציונליות כזו.

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

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

אז למה בכלל להשתמש במסנן אוטומטי ב- VBA?

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

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

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

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

בתרחיש כזה, שימוש במסנן אוטומטי של VBA יכול להאיץ את העניינים ולחסוך זמן.

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

תחביר סינון אוטומטי של Excel VBA

ביטוי. סינון אוטומטי (_ Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)

• ביטוי: זהו הטווח שעליו ברצונך להחיל את המסנן האוטומטי.
• שדה: [טיעון אופציונלי] זהו מספר העמודה שברצונך לסנן. זה נספר משמאל במערך הנתונים. אז אם אתה רוצה לסנן נתונים על סמך העמודה השנייה, ערך זה יהיה 2.
• קריטריונים 1: [טיעון אופציונלי] זהו הקריטריונים המבוססים עליהם אתה רוצה לסנן את מערך הנתונים.
• מַפעִיל: [טיעון אופציונלי] במקרה שאתה משתמש גם בקריטריונים 2, תוכל לשלב את שני הקריטריונים הללו על סמך המפעיל. המפעילים הבאים זמינים לשימוש: xlAnd, xlOr, xlBottom10 פריטים, xlTop10 פריטים, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• קריטריונים 2: [טיעון אופציונלי] זהו הקריטריון השני שבו ניתן לסנן את מערך הנתונים.
• VisibleDropDown: [טיעון אופציונלי] תוכל לציין אם ברצונך שהסמל הנפתח של המסנן יופיע בעמודות המסוננות או לא. טיעון זה יכול להיות TRUE או FALSE.

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

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

Sub FilterRows () גליונות עבודה ("נתוני סינון"). טווח ("A1"). סיום אוטומטי של סינון אוטומטי

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

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

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

עכשיו בואו נראה כמה דוגמאות לשימוש במסנן האוטומטי של Excel VBA שיבהיר את השימוש בו.

דוגמה: סינון נתונים על בסיס תנאי טקסט

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

הקוד שלהלן יסנן את כל השורות שבהן הפריט הוא 'מדפסת'.

Sub FilterRows () גליונות עבודה ("גיליון 1"). טווח ("A1"). שדה סינון אוטומטי: = 2, קריטריונים 1: = תת מדפסת "סוף"

הקוד לעיל מתייחס ל- Sheet1 ובתוכו הוא מתייחס ל- A1 (שהוא תא במערך הנתונים).

שים לב שכאן השתמשנו בשדה: = 2, מכיוון שעמודת הפריט היא העמודה השנייה במערך הנתונים שלנו משמאל.

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

אתה צודק!

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

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

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

לחץ כאן להורדת קובץ הדוגמה והמשך.

דוגמה: קריטריונים מרובים (AND/OR) באותה עמודה

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

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

Sub FilterRowsOR () גליונות עבודה ("גיליון 1"). טווח ("A1"). שדה סינון אוטומטי: = 2, קריטריונים 1: = "מדפסת", מפעיל: = xlOr, קריטריונים 2: = "מקרן" קצה משנה

שים לב שכאן השתמשתי ב- xlOR מַפעִיל.

זה אומר ל- VBA להשתמש בשני הקריטריונים ולסנן את הנתונים אם מתקיים אחד משני הקריטריונים.

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

לדוגמה, אם ברצונך לסנן את כל הרשומות שבהן הכמות היא יותר מ -10 אך פחות מ -20, תוכל להשתמש בקוד שלהלן:

Sub FilterRowsAND () גליונות עבודה ("גיליון 1"). טווח ("A1"). שדה סינון אוטומטי: = 4, קריטריונים 1: = "> 10", _ אופרטור: = xlAnd, קריטריונים 2: = "<20" סיום תת

דוגמה: קריטריונים מרובים עם עמודות שונות

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

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

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

Sub FilterRows () עם גליונות עבודה ("גיליון 1"). טווח ("A1"). שדה AutoFilter: = 2, קריטריונים 1: = "מדפסת". שדה AutoFilter: = 3, קריטריונים 1: = "סימן" סיום עם קצה משנה

דוגמה: סנן את 10 הרשומות המובילות בשיטת סינון אוטומטי

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

להלן הקוד שיתן לך את 10 הרשומות המובילות (בהתבסס על עמודת הכמות):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). שדה סינון אוטומטי: = 4, קריטריונים 1: = "10", אופרטור: = xlTop10 פריטים סיום תת

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

שים לב שבדוגמה זו, אם אתה רוצה לקבל את 5 הפריטים המובילים, פשוט שנה את המספר קריטריונים 1: = ”10 ″ מ 10 עד 5.

אז עבור 5 הפריטים המובילים, הקוד יהיה:

Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). שדה סינון אוטומטי: = 4, קריטריונים 1: = "5", מפעיל: = xlTop10 פריטים סוף תת

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

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

Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). שדה סינון אוטומטי: = 4, קריטריונים 1: = "10", מפעיל: = xlBottom10 פריטים קצה תת

ואם אתה רוצה את 5 הפריטים התחתונים, שנה את המספר קריטריונים 1: = ”10 ″ מ 10 עד 5.

דוגמה: סנן את 10 האחוזים המובילים בשיטת סינון אוטומטי

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

להלן הקוד שיתן לך את 10 האחוזים המובילים (בהתבסס על עמודת הכמות):

Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). שדה סינון אוטומטי: = 4, קריטריונים 1: = "10", אופרטור: = xlTop10Percent End Sub

במערך הנתונים שלנו, מכיוון שיש לנו 20 רשומות, הוא יחזיר את 2 הרשומות המובילות (שהם 10% מכלל הרשומות).

דוגמה: שימוש בתווים כלליים בסינון אוטומטי

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

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

Sub FilterRowsWildcard () גליונות עבודה ("גיליון 1"). טווח ("A1"). שדה סינון אוטומטי: = 2, קריטריונים 1: = "*לוח*" סיום תת

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

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

דוגמה: העתק שורות מסוננות לגיליון חדש

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

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

Sub CopyFilteredRows () Dim rng As Range Dim ws As גליון עבודה אם גליונות עבודה ("גיליון 1"). AutoFilterMode = False ואז MsgBox "אין שורות מסוננות" יציאה מקצה משנה אם הגדר rng = גליונות עבודה ("גיליון 1"). AutoFilter.Range Set ws = גליונות עבודה.הוסף rng.Copy טווח ("A1") סוף משנה

הקוד לעיל יבדוק אם יש שורות מסוננות בגיליון 1 או לא.

אם אין שורות מסוננות, היא תציג תיבת הודעות המציינת זאת.

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

דוגמה: סינון נתונים המבוססים על ערך תא

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

משהו כפי שמוצג להלן:

לחץ כאן להורדת קובץ הדוגמה והמשך.

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

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

גליון משנה של Works Sub_Change (ByVal Target as Range) אם Target.Address = "$ B $ 2" ואז If Range ("B2") = "הכל" ואז טווח ("A5"). טווח סינון אוטומטי אחר ("A5"). שדה סינון אוטומטי : = 2, קריטריונים 1: = טווח ("B2") סוף אם סוף אם סוף תת

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

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

קוד זה אינו ממוקם במודול.

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

להלן השלבים להכנסת קוד זה לחלון קוד גליון העבודה:

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

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

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

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

הפעל/כבה את המסנן האוטומטי של Excel באמצעות VBA

בעת החלת המסנן האוטומטי על מגוון תאים, ייתכן שכבר יש כמה מסננים.

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

תת TurnOFFAutoFilter () גליונות עבודה ("Sheet1"). AutoFilterMode = Sub End False

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

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

תת TurnOFFAutoFilter () אם גליונות עבודה ("גיליון 1"). טווח ("A1"). סינון אוטומטי ואז גליונות עבודה ("גיליון 1"). טווח ("A1"). סינון אוטומטי אם סוף קצה

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

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

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

תת TurnOnAutoFilter () אם לא גליונות עבודה ("גיליון 1"). טווח ("A4"). סינון אוטומטי ואז גליונות עבודה ("גיליון 1"). טווח ("A4"). סינון אוטומטי אם סוף קצה

בדוק אם סינון אוטומטי כבר יושם

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

Sub CheckforFilters () אם ActiveSheet.AutoFilterMode = True ואז MsgBox "יש מסננים כבר במקום" אחר MsgBox "אין מסננים" סוף אם סוף תת

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

הצג את כל הנתונים

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

Sub ShowAllData () אם ActiveSheet.FilterMode ואז ActiveSheet.ShowAllData מסתיים

הקוד לעיל בודק אם ה- FilterMode הוא TRUE או FALSE.

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

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

שימוש בסינון אוטומטי על גיליונות מוגנים

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

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

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

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

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

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

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

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

Sub Workbook_Open () פרטי עם גליונות עבודה ("גיליון 1") .EnableAutoFilter = True .Protect Password: = "password", תוכן: = True, UserInterfaceOnly: = סוף נכון עם קצה משנה

קוד זה צריך להיות ממוקם בחלון קוד ThisWorkbook.

להלן השלבים להכנסת הקוד לחלון קוד ThisWorkbook:

1. פתח את עורך VB (קיצור מקשים - ALT + F11).
2. בחלונית סייר הפרויקטים, לחץ פעמיים על האובייקט ThisWorkbook.
3. בחלון הקוד שנפתח, העתק והדבק את הקוד לעיל.

ברגע שתפתח את חוברת העבודה ותפעיל פקודות מאקרו, הוא יפעיל את המאקרו באופן אוטומטי ויגן על גיליון 1.

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

כמו כן, הוא מגדיר את הארגומנט 'UserInterfaceOnly' ל- 'True'. המשמעות היא שבעוד שגיליון העבודה מוגן, קוד פקודות המאקרו VBA ימשיך לפעול.

אולי תאהב גם את מדריכי ה- VBA הבאים:

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