כיצד להשתמש בפונקציית FILTER של Excel

Office 365 מביא כמה תכונות נהדרות כמו XLOOKUP, SORT ו-FILTER.

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

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

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

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

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

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

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

=FILTER(array,include,[if_empty])

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

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

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

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

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

=FILTER($A$2:$C$11,$B$2:$B$11="US")

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

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

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

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

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

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

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

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

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

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

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

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

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

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

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

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

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

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

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

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

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

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

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

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

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

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

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

=FILTER($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),"Nothing Found")

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

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

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

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

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

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

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

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

הנוסחה הבאה תעשה זאת:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

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

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

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

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

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

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

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

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

דוגמה 6: סנן רק רשומות זוגיות (או רשומות אי-זוגיות)

אם אתה צריך לסנן ולחלץ במהירות את כל הרשומות בשורות זוגיות או אי זוגיות, תוכל להשתמש בפונקציית 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 כאשר הוא אי זוגי.שימו לב שהפחתי 2 מהחלק ROW(A11:A1) מכיוון שהרשומה הראשונה נמצאת בשורה השנייה, מה שמתאים את מספרי השורות להתייחס לשורה השנייה כרשומה הראשונה.

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

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

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

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

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

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

הנוסחה הבאה תעשה זאת:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

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

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

אז אלו הן 7 דוגמאות לשימוש בפונקציית FILTER באקסל.

מקווה שמצאת את המדריך הזה שימושי.