תיבת חיפוש מסנני Excel דינמית (חלץ נתונים בעת ההקלדה)

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

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

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

צפה בוידאו - יצירת תיבת חיפוש מסנני Excel דינמית

יצירת תיבת חיפוש של מסנני Excel דינאמיים

ניתן ליצור מסנן Excel דינמי זה בשלושה שלבים:

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

כך נראים הנתונים הגולמיים:

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

שלב 1 - קבלת רשימת פריטים ייחודית

1. בחר את כל המדינות והדבק אותו בגליון עבודה חדש.
2. בחר את רשימת המדינה -> עבור לנתונים -> הסר כפילויות.
3. בתיבת הדו -שיח הסר כפילויות, בחר את העמודה שבה יש לך את הרשימה ולחץ על אישור. פעולה זו תסיר כפילויות ותעניק לך רשימה ייחודית כפי שמוצג להלן:
4. שלב נוסף הוא ליצור טווח עם שם לרשימה ייחודית זו. כדי לעשות זאת:
   • עבור ללשונית נוסחה -> הגדר שם
   • בתיבת הדו -שיח הגדר שם:
     • שם: CountryList
     • היקף: חוברת עבודה
     • הכוונה ל: = UniqueList! $ A $ 2: $ A $ 9 (יש לי את הרשימה בכרטיסייה נפרדת בשם UniqueList ב- A2: A9. תוכל להתייחס לכל מקום בו הרשימה הייחודית שלך נמצאת)

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

שלב 2 - יצירת תיבת החיפוש של מסנני Excel דינמיים

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

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

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

1. עבור לכרטיסייה מפתחים -> פקדים -> הוספה -> פקדי ActiveX -> תיבת משולבת (פקדי ActiveX).
   • אם אין לך את הכרטיסייה למפתחים גלויה, להלן השלבים להפעיל אותה.
2. לחץ במקום כלשהו בגליון העבודה. הוא יכניס את הקופסה המשולבת.
3. לחץ לחיצה ימנית על תיבה משולבת ובחר מאפיינים.
4. בחלון המאפיינים, בצע את השינויים הבאים:
   • תא מקושר: K2 (אתה יכול לבחור כל תא שבו תרצה שיציג את ערכי הקלט. נשתמש בתא זה בהגדרת הנתונים).
   • ListFillRange: CountryList (זהו הטווח בשם שיצרנו בשלב 1. פעולה זו תציג את כל המדינות הנפתחות).
   • MatchEntry: 2-fmMatchEntryNone (זה מבטיח שמילה לא תושלם אוטומטית תוך כדי הקלדה)
5. כאשר תיבת המשולבות נבחרה, עבור לכרטיסייה מפתחים -> פקדים -> לחץ על מצב עיצוב (זה מוציא אותך ממצב העיצוב, ועכשיו אתה יכול להקליד כל דבר בתיבה המשולבת. עכשיו, כל מה שתקליד יבוא לידי ביטוי בתא K2 בזמן אמת)

שלב 3 - הגדרת הנתונים

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

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

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

ניתן לעשות זאת באמצעות שילוב של פונקציות IF, ISNUMBER ו- SEARCH.

להלן הנוסחה:

= IF (ISNUMBER (SEARCH ($ K $ 2, D4)), E4, "")

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

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

טור עוזר 3: בעמודה עוזרת 3, עלינו לערוך את כל מספרי השורות מתוך עמוד עוזר 2 יחד. לשם כך נוכל להשתמש בשילוב אם נוסחאות IFERROR ו- SMALL. להלן הנוסחה:

= IFERROR (קטן ($ F $ 4: $ F $ 23, E4), "")

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

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

= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

נוסחה זו כוללת 2 חלקים:
אינדקס - זה מחלץ את הנתונים על בסיס מספר השורה.
IFERROR - הדבר מחזיר ריק כאשר אין נתונים.

להלן תמונת מצב של מה שאתה מקבל סוף סוף:

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

להיות יצירתי! נסה כמה וריאציות

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

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

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

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