צפה בוידאו - מסנן מתקדם של Excel
מסנן מתקדם של Excel הוא אחת התכונות המוערכות והבלתי מנוצלות ביותר שנתקלתי בהן.
אם אתה עובד עם אקסל, אני בטוח שהשתמשת (או לפחות שמעת על מסנן האקסל הרגיל). הוא מסנן במהירות מערך נתונים המבוסס על בחירה, טקסט שצוין, מספר או קריטריונים אחרים כאלה.
במדריך זה, אראה לך כמה דברים מגניבים שאתה יכול לעשות באמצעות המסנן המתקדם של Excel.
אבל ראשית … מהו מסנן מתקדם של Excel?
מסנן מתקדם של Excel - כפי שהשם מרמז - הוא הגרסה המתקדמת של המסנן הרגיל. אתה יכול להשתמש בזה כשאתה צריך להשתמש בקריטריונים מורכבים יותר כדי לסנן את מערך הנתונים שלך.
להלן כמה הבדלים בין המסנן הרגיל למסנן מתקדם:
- בעוד שמסנן הנתונים הרגיל יסנן את מערך הנתונים הקיים, תוכל להשתמש במסנן מתקדם של Excel כדי לחלץ את מערך הנתונים גם למיקום אחר.
- מסנן מתקדם של Excel מאפשר לך להשתמש בקריטריונים מורכבים. לדוגמה, אם יש לך נתוני מכירות, תוכל לסנן נתונים על קריטריון שבו נציג המכירות הוא בוב והאזור הוא צפון או דרום (נראה כיצד לעשות זאת בדוגמאות). לתמיכה במשרד יש הסבר טוב בנושא.
- אתה יכול להשתמש במסנן המתקדם של Excel כדי לחלץ רשומות ייחודיות מהנתונים שלך (עוד על זה תוך שנייה).
מסנן מתקדם ב- EXCEL (דוגמאות)
עכשיו בואו נסתכל על דוגמה מסוימת לשימוש במסנן המתקדם ב- Excel.
דוגמא 1 - הפקת רשימה ייחודית
אתה יכול להשתמש במסנן מתקדם של Excel כדי לחלץ במהירות רשומות ייחודיות מערך נתונים (או במילים אחרות להסיר כפילויות).
ב- Excel 2007 וגירסאות מאוחרות יותר, יש אפשרות להסיר כפילויות ממערך נתונים. אבל זה משנה את מערך הנתונים הקיים שלך. כדי לשמור על הנתונים המקוריים על כנם, עליך ליצור עותק של הנתונים ולאחר מכן להשתמש באפשרות הסר כפילויות. מסנן Excel מתקדם יאפשר לך לבחור מיקום כדי לקבל רשימה ייחודית.בואו נראה כיצד להשתמש במסננים מתקדמים כדי לקבל רשימה ייחודית.
נניח שיש לך מערך נתונים כפי שמוצג להלן:
כפי שאתה יכול לראות, יש רשומות כפולות במערך הנתונים הזה (מסומן בכתום). אלה יכולים לנבוע משגיאה בהזנת נתונים או תוצאה של איסוף נתונים.
במקרה כזה, תוכל להשתמש בכלי סינון מתקדם של Excel כדי לקבל במהירות רשימה של כל הרשומות הייחודיות במיקום אחר (כך שהנתונים המקוריים שלך יישארו שלמים).
להלן השלבים לקבלת כל הרשומות הייחודיות:
- בחר את מערך הנתונים כולו (כולל הכותרות).
- עבור ללשונית נתונים -> מיין וסנן -> מתקדם. (ניתן גם להשתמש בקיצור המקשים - Alt + A + Q). פעולה זו תפתח את תיבת הדו -שיח 'מסנן מתקדם'.
- בתיבת הדו -שיח מסנן מתקדם, השתמש בפרטים הבאים:
- פעולה: בחר באפשרות 'העתק למיקום אחר'. זה יאפשר לך לציין את המיקום שבו תוכל לקבל את רשימת הרשומות הייחודיות.
- טווח רשימה: ודא שהוא מתייחס למערך הנתונים שממנו ברצונך למצוא רשומות ייחודיות. כמו כן, ודא שכותרות במערך הנתונים כלולות.
- טווח קריטריונים: השאר את זה ריק.
- העתק אל: ציין את כתובת התא שבה ברצונך לקבל את רשימת הרשומות הייחודיות.
- העתק רשומות ייחודיות בלבד: סמן אפשרות זו.
- לחץ על אישור.
זה ייתן לך מיד רשימה של כל הרשומות הייחודיות.
זְהִירוּת: כאשר אתה משתמש במסנן מתקדם כדי לקבל את הרשימה הייחודית, ודא שבחרת גם בכותרת. אם לא תעשה זאת, הוא יחשב את התא הראשון ככותרת.
דוגמה 2 - שימוש בקריטריונים במסנן מתקדם של Excel
קבלת רשומות ייחודיות היא אחד הדברים הרבים שאתה יכול לעשות עם מסנן מתקדם של Excel.
התועלת העיקרית שלה טמונה ביכולתה לאפשר שימוש בקריטריונים מורכבים לסינון נתונים.
הנה כוונתי בקריטריונים מורכבים. נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לקבל במהירות את כל הרשומות שבהן המכירות גדולות מ -5000 והאזור הוא ארה"ב.
כך תוכל להשתמש במסנן מתקדם של Excel כדי לסנן את הרשומות בהתאם לקריטריונים שצוינו:
- השלב הראשון בעת שימוש במסנן מתקדם של Excel עם קריטריונים מורכבים הוא ציון הקריטריונים. לשם כך, העתק את הכותרות והדבק אותן איפשהו בגליון העבודה.
- ציין את הקריטריונים שעבורם ברצונך לסנן את הנתונים. בדוגמה זו, מכיוון שאנו רוצים לקבל את כל הרשומות בארה"ב עם מכירות של יותר מ -5000, הזן 'ארה"ב' בתא שמתחת לאזור ו> 5000 בתא שמתחת למכירות. זה ישמש כעת כקלט במסנן מתקדם כדי לקבל את הנתונים המסוננים (כפי שמוצג בשלבים הבאים).
- בחר את מערך הנתונים כולו (כולל הכותרות).
- עבור ללשונית נתונים -> מיין וסנן -> מתקדם. פעולה זו תפתח את תיבת הדו -שיח 'מסנן מתקדם'.
- בתיבת הדו -שיח מסנן מתקדם, השתמש בפרטים הבאים:
- פעולה: בחר באפשרות 'העתק למיקום אחר'. זה יאפשר לך לציין את המיקום שבו תוכל לקבל את רשימת הרשומות הייחודיות.
- טווח רשימה: ודא שהוא מתייחס למערך הנתונים שממנו ברצונך למצוא רשומות ייחודיות. כמו כן, ודא שכותרות במערך הנתונים כלולות.
- טווח קריטריונים: ציין את הקריטריונים שבנינו בשלבים שלמעלה. בדוגמה זו, זה יהיה F1: I3.
- העתק אל: ציין את כתובת התא שבה ברצונך לקבל את רשימת הרשומות הייחודיות.
- העתק רשומות ייחודיות בלבד: סמן אפשרות זו.
- לחץ על אישור.
זה ייתן לך מיד את כל הרשומות שבהן האזור הוא ארה"ב והמכירות הן יותר מ -5000.
הדוגמה לעיל היא מקרה שבו הסינון מתבצע על בסיס שני קריטריונים (ארה"ב ומכירות גדולות מ -5000).
מסנן Excel מתקדם מאפשר לך ליצור שילובים רבים של קריטריונים.
להלן כמה דוגמאות כיצד תוכל לבנות מסננים אלה.
שימוש בקריטריונים AND
כשאתה רוצה להשתמש בקריטריונים AND, עליך לציין אותו מתחת לכותרת.
לדוגמה:
- כדי לסנן רשומות כשהאזור הוא ארה"ב ונציג המכירות הוא ג'ו.
- כדי לסנן רשומות כשהאזור הוא ארה"ב ושווי המכירות גדול מ -5000.
- כאשר האזור הוא ארה"ב והמכירות נרשמות לאחר 31-03-2017.
שימוש בקריטריונים OR
כשאתה רוצה להשתמש בקריטריונים OR, עליך לציין את הקריטריונים באותה עמודה.
לדוגמה:
- כדי לסנן רשומות כשהאזור הוא ארה"ב או שהאזור הוא אסיה.
- כדי לסנן רשומות כאשר נציג המכירות הוא בוב או מרתה.
דוגמה 3 - שימוש בתווי WILDCARD במסנן מתקדם ב- Excel
מסנן מתקדם של Excel מאפשר גם שימוש בתווים כלליים תוך בניית הקריטריונים.
יש שלוש תווים כלליים ב- Excel:
- * (כוכבית) - הוא מייצג כל מספר תווים. לדוגמה, ex* יכול להיות אקסל, אקסל, דוגמא, מומחה וכו '.
- ? (סימן שאלה) - הוא מייצג דמות אחת. לדוגמה, Tr? Mp יכול להיות טראמפ או טראמפ.
- ~ (tilde) - הוא משמש לזיהוי תו תווים כלליים (~, *,?) בטקסט.
כעת נראה כיצד אנו יכולים להשתמש בתווים כלליים אלה לביצוע סינון מתקדם ב- Excel.
- כדי לסנן רשומות שבהן שם נציג המכירות מתחיל מ- J.
שים לב ש * מייצגים כל מספר תווים. אז כל נציג עם השם שמתחיל ב- J יסונן עם הקריטריונים האלה.
באופן דומה, תוכל להשתמש גם בשני תווים כלליים אחרים.
הערה: במקרה שאתה משתמש ב- Office 365, עליך לבדוק את הפונקציה FILTER. זה יכול לעשות הרבה דברים שמסנן מתקדם יכול לעשות עם נוסחה פשוטה.
הערה:
- זכור, הכותרות בקריטריונים צריכות להיות זהות לחלוטין לזו שבמערכת הנתונים.
- לא ניתן לבטל סינון מתקדם בעת העתקה למיקומים אחרים.
