פילטר מתקדם וקצת קסם

עבור הרוב המכריע של משתמשי אקסל, כאשר המילה "סינון נתונים" עולה בראשם, רק המסנן הקלאסי הרגיל מהכרטיסייה נתונים - מסנן (נתונים - מסנן):

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

בסיס

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

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

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

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

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

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

הוספת מאקרו

"טוב, איפה הנוחות כאן?" אתה שואל ותהיה צודק. לא רק שצריך להזין תנאים לתאים הצהובים עם הידיים, אלא גם לפתוח תיבת דו-שיח, להזין שם טווחים, ללחוץ OK. עצוב, אני מסכים! אבל "הכל משתנה כשהם באים ©" - פקודות מאקרו!

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

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then on Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFiltereriaRPlace, Criteria :=Range("A1").CurrentRegion End If End Sub  

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

אז הכל הרבה יותר טוב, נכון? 🙂

יישום שאילתות מורכבות

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

נקודות עדינות:

• הסימן * פירושו כל מספר של תווים כלשהם, ו-? - כל דמות אחת.
• ההיגיון בעיבוד טקסט ושאילתות מספריות שונה במקצת. כך, למשל, תא תנאי עם הספרה 5 לא אומר לחפש את כל המספרים שמתחילים בחמש, אלא תא תנאי עם האות B שווה ל-B*, כלומר יחפש כל טקסט שמתחיל באות B.
• אם שאילתת הטקסט לא מתחילה בסימן =, אז אתה יכול לשים * בסוף.
• יש להזין תאריכים בפורמט ארה"ב חודש-יום-שנה ובאמצעות חלק (גם אם יש לך אקסל והגדרות אזוריות).

חיבורים לוגיים AND-OR

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

הָהֵן. לסנן לי בננות ברבעון השלישי, דווקא במוסקבה ובמקביל מאוצ'אן.

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

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

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

• סופרפילטר על פקודות מאקרו
• מהן פקודות מאקרו, היכן וכיצד להכניס קוד מאקרו ב-Visual Basic
• טבלאות חכמות ב- Microsoft Excel