יצירת מסנן נפתח לחילוץ נתונים על סמך מבחר

צפה בוידאו - חלץ נתונים באמצעות רשימה נפתחת ב- Excel

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

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

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

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

להלן השלבים ליצירת מסנן נפתח שיחלץ נתונים עבור הפריט שנבחר:

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

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

צור רשימה ייחודית של פריטים

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

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

להלן השלבים לקבלת רשימה ייחודית:

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

כעת נשתמש ברשימה ייחודית זו ליצירת הרשימה הנפתחת.

ראה גם: המדריך האולטימטיבי לאיתור והסרה של כפילויות ב- Excel.

יצירת המסנן הנפתח

להלן השלבים ליצירת רשימה נפתחת בתא:

1. עבור אל נתונים -> אימות נתונים.
2. בתיבת הדו -שיח אימות נתונים, בחר בכרטיסייה הגדרות.
3. בכרטיסיה הגדרות, בחר "רשימה" בתפריט הנפתח ובשדה 'מקור' בחר את הרשימה הייחודית של המדינות שיצרנו.
4. לחץ על אישור.

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

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

צור עמודות עוזר לחילוץ הרשומות של הפריט הנבחר

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

ניתן לעשות זאת באמצעות שלוש עמודות עזר.

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

• טור עוזר מס '1 - הזן את המספר הסידורי של כל הרשומות (20 במקרה זה, תוכל להשתמש בפונקציה ROWS () לשם כך).
• טור עוזר מס '2 - השתמש בפונקציית הפונקציה הפשוטה הזו של IF: = IF (D4 = $ H $ 2, E4, ””)
  • נוסחה זו בודקת אם המדינה בשורה הראשונה תואמת את זו שבתפריט הנפתח. אז אם אני בוחר בהודו, היא בודקת אם בשורה הראשונה יש הודו כמדינה או לא. אם זה נכון, זה מחזיר את מספר השורה, אחרת הוא מחזיר ריק (""). כעת כאשר אנו בוחרים מדינה כלשהי, מוצגים רק מספרי השורות האלה (בעמודה העוזרת השנייה) שבה נמצאת המדינה שנבחרה. (לדוגמה, אם הודו תיבחר, ​​זה ייראה כמו התמונה למטה).

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

• טור עוזר שלישי - השתמש בשילוב הבא של פונקציות IFERROR ו- SMALL:
  = IFERROR (קטן ($ F $ 4: $ F $ 23, E4), "")

זה ייתן לנו משהו כפי שמוצג להלן בתמונה:

עכשיו כשיש לנו את המספר ביחד, אנחנו רק צריכים לחלץ את הנתונים במספר הזה. ניתן לעשות זאת בקלות באמצעות הפונקציה INDEX (השתמש בנוסחה זו בתאים שבהם עליך לחלץ את התוצאה):
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), "")

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

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

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

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

הורד את קובץ הדוגמה

אהבת את ההדרכה? ספר לי את דעתך בסעיף ההערות.

אתה עשוי גם למצוא את ההדרכות הבאות שימושיות:

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