צור רשימה נפתחת של Excel עם הצעות חיפוש

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

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

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

הרשימה הנפתחת הניתנת לחיפוש ב- Excel

לצורך הדרכה זו, אני משתמש בנתוני 20 המדינות המובילות לפי התוצר.

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

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

כדי לעקוב, הורד את קובץ הדוגמה מכאן

יצירת הרשימה הנפתחת לחיפוש ב- Excel תהיה תהליך בן שלושה חלקים:

1. הגדרת תיבת החיפוש.
2. הגדרת הנתונים.
3. כתיבת קוד VBA קצר בכדי לגרום לזה לעבוד.

שלב 1 - הגדרת תיבת החיפוש

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

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

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

(תא B3 מקושר לתיבה המשולבת, מה שאומר שכל מה שאתה מקליד בתיבה המשולבת נכנס ב B3)

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

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

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

לשם כך נשתמש

• שלושה עמודי עזר.
• טווח אחד בשם דינמי.

טור עוזר 1

הכנס את הנוסחה הבאה לתא F3 וגרור אותה לכל העמודה (F3: F22)

=-ISNUMBER (IFERROR (SEARCH ($ B $ 3, E3,1), ""))

נוסחה זו מחזירה 1 כאשר הטקסט בתיבה המשולבת נמצא בשם המדינה מצד שמאל. לדוגמה, אם אתה מקליד UNI, אז רק את הערכים עבור חַדמדינות טד ו חַדted Kingdom הם 1 וכל הערכים הנותרים הם 0.

טור עוזר 2

הכנס את הנוסחה הבאה לתא G3 וגרור אותה לכל העמודה (G3: G22)

= IF (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

נוסחה זו מחזירה 1 עבור המופע הראשון שבו הטקסט המשולב תואם את שם המדינה, 2 עבור המופע השני, 3 עבור השלישי וכן הלאה. לדוגמה, אם תקליד UNI, תא G3 יציג 1 כפי שהוא תואם את ארצות הברית, ו- G9 יציג 2 כפי שהוא תואם את בריטניה. שאר התאים יהיו ריקים.

טור עוזר 3

הכנס את הנוסחה הבאה לתא H3 וגרור אותה לכל העמודה (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

נוסחה זו עורמת את כל השמות התואמים יחד ללא תאים ריקים ביניהם. לדוגמה, אם תקליד UNI, עמודה זו תציג 2 ו -9 יחד, וכל שאר התא יהיה ריק.

יצירת טווח שמות דינמי

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

הערה: בשלב 1 הזנו את DropDownList באפשרות ListFillRange. כעת ניצור את הטווח בשם בעל אותו שם.

להלן השלבים ליצירתו:

1. עבור אל נוסחאות -> מנהל שמות.
2. בתיבת הדו-שיח מנהל שמות לחץ על חדש. הוא יפתח תיבת דו -שיח שם חדש.
3. בשדה שם הזן DropDownList
4. בשדה הפניות לשדה הזן את הנוסחה: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

שלב 3 - הפעלת קוד VBA לעבודה

אנחנו כמעט שם.

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

כדי להוסיף את הקוד הזה לחוברת העבודה שלך:

1. לחץ באמצעות לחצן העכבר הימני על הכרטיסייה גליון עבודה ובחר הצג קוד.
2. בחלון VBA, העתק והדבק את הקוד הבא:

   תת שילוב פרטי Box1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown סיום תת

זהו זה!!

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

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

כדי לעקוב, הורד את הקובץ מכאן

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

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

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