פונקציית מסנן Excel - מוסברת באמצעות דוגמאות + וידאו

צפה בוידאו - דוגמאות לפונקציות של פילטר אקסל

Office 365 מביא כמה פונקציות מדהימות - כגון XLOOKUP, SORT ו- FILTER.

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

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

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

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

אם אתה רוצה לקבל את התכונות החדשות האלה ב- Excel, אתה יכול לשדרג ל- Office 365 (הצטרף לתכנית האינסיידר כדי לקבל גישה לכל התכונות/הנוסחאות)

פונקציית סינון Excel - תחביר

להלן תחביר הפונקציה FILTER:

= FILTER (מערך, כלול, [if_empty])

• מַעֲרָך - זהו טווח התאים שבו יש לך את הנתונים וברצונך לסנן ממנו כמה נתונים
• לִכלוֹל - זהו התנאי שאומר לפונקציה מה רשומות לסנן
• [if_empty] - זהו טיעון אופציונלי בו תוכל לציין מה להחזיר במקרה שלא נמצאו תוצאות על ידי הפונקציה FILTER. כברירת מחדל (כאשר לא צוין), הוא מחזיר את #CALC! שְׁגִיאָה

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

לחץ כאן להורדת קובץ הדוגמה והמשך

דוגמה 1: סינון נתונים המבוססים על קריטריונים אחד (אזור)

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

להלן נוסחת ה FILTER שתעשה זאת:

= פילטר ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")

הנוסחה לעיל משתמשת במערך הנתונים כמערך והתנאי הוא $ B $ 2: $ B $ 11 = "US"

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

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

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

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

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

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

לדוגמה, בדוגמה שלהלן, יש לי את ערך האזור בתא I2 ואז מציינים זאת בנוסחה:

= פילטר ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

זה הופך את הנוסחה ליותר שימושית ועכשיו אתה יכול פשוט לשנות את ערך האזור בתא I2 והמסנן ישתנה באופן אוטומטי.

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

דוגמה 2: סינון נתונים המבוססים על קריטריונים אחד (יותר או פחות מאשר)

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

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

הנוסחה שלהלן יכולה לעשות זאת:

= פילטר ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

ארגומנט המערך מתייחס למערך הנתונים כולו והתנאי, במקרה זה, הוא ($ C $ 2: $ C $ 11> 10000).

הנוסחה בודקת את כל הערך עבור הערך בעמודה C. אם הערך עולה על 10000, הוא מסונן, אחרת הוא מתעלם.

אם אתה רוצה לקבל את כל הרשומות פחות מ -10000, אתה יכול להשתמש בנוסחה שלהלן:

= פילטר ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

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

= פילטר ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = LARGE (C2: C11,3)))

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

לחץ כאן להורדת קובץ הדוגמה והמשך

דוגמה 3: סינון נתונים עם קריטריונים מרובים (AND)

נניח שיש לך את מערך הנתונים להלן ואתה רוצה לסנן את כל הרשומות בארה"ב שבהן ערך המכירה הוא יותר מ -10000.

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

להלן נוסחת FILTER שתסנן רשומות עם ארה"ב כאזור ומכירות של יותר מ -10000:

= פילטר ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))

שים לב שהקריטריון (הנקרא ארגומנט כולל) הוא ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000)

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

במקרה ואין רשומות שעונות על הקריטריונים, הפונקציה תחזיר את #CALC! שְׁגִיאָה.

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

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")*($ C $ 2: $ C $ 11> 10000), "דבר לא נמצא")

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

דוגמה 4: סינון נתונים עם קריטריונים מרובים (OR)

תוכל גם לשנות את הארגומנט 'כלול' בפונקציית הפילטר כדי לבדוק אם יש קריטריונים OR (כאשר כל אחד מהתנאים הנתונים יכול להיות נכון).

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

להלן הנוסחה שתעשה זאת:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "קנדה"))

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

דוגמה נוספת יכולה להיות כאשר אתה רוצה לסנן את כל הרשומות שבהן המדינה היא ארה"ב או ששווי המכירה הוא יותר מ -10000.

הנוסחה שלהלן תעשה זאת:

= פילטר ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))

הערה: בעת שימוש בקריטריונים AND בפונקציית FILTER, השתמש באופרטור הכפל (*) ובעת שימוש בקריטריונים OR, השתמש באופרטור החיבור (+).

דוגמה 5: סינון נתונים כדי להגיע לרשומות מעל/מתחת לממוצע

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

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

אתה יכול לעשות זאת באמצעות הנוסחה הבאה:

= פילטר ($ A $ 2: $ C $ 11, C2: C11> ממוצע (C2: C11))

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

= פילטר ($ A $ 2: $ C $ 11, C2: C11<>

לחץ כאן להורדת קובץ הדוגמה והמשך

דוגמה 6: סינון רק רשומות מספר EVEN (או רשומות מספר ODD)

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

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

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

להלן הנוסחה שתעשה זאת:

= FILTER ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 0)

הנוסחה לעיל משתמשת בפונקציה MOD כדי לבדוק את מספר השורות של כל רשומה (שניתנת על ידי הפונקציה ROW).

הנוסחה MOD (ROW (A2: A11) -1,2) = 0 מחזירה TRUE כאשר מספר השורות שווה ו- FALSE כשהוא מוזר. שים לב שהפסקתי 1 מהחלק ROW (A2: A11) מכיוון שהרשומה הראשונה נמצאת בשורה השנייה, וזה מתאים את מספר השורה כדי לשקול את השורה השנייה כרשומה הראשונה.

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

= FILTER ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 1)

דוגמה 7: מיין את הנתונים המסוננים באמצעות נוסחה

שימוש בפונקציית FILTER עם פונקציות אחרות מאפשר לנו לבצע הרבה יותר.

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

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

הנוסחה שלהלן תעשה זאת:

= מיון (פילטר ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

הפונקציה שלעיל משתמשת בפונקציית FILTER כדי לקבל את הנתונים שבהם ערך המכירה בעמודה C הוא יותר מ -10000. מערך זה המוחזר על ידי הפונקציה FILTER משמש לאחר מכן בתוך הפונקציה SORT למיון נתונים אלה על בסיס ערך המכירה.

הארגומנט השני בפונקציית SORT הוא 3, שהוא למיון לפי העמודה השלישית. והטיעון הרביעי הוא -1 שהוא מיון הנתונים האלה בסדר יורד.

לחץ כאן להורדת קובץ הדוגמא

אז אלו 7 דוגמאות לשימוש בפונקציית FILTER ב- Excel.

מקווה שמצאת הדרכה שימושית!

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

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