פונקציית FILTER באקסל: כמה קריטריונים, AND ו-OR

=FILTER(A2:C7,B2:B7="North") מחזירה כל שורה של A2:C7 שהאזור שלה בעמודה B הוא North. מקלידים אותה בתא אחד והשורות המתאימות נשפכות לתאים שמתחתיו ומימינו. שנו אזור בעמודה B ל-North, או North ל-South, והרשימה מתעדכנת.

נוסחאותאיפוס

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

רק ב-E2 יש נוסחה. שאר התאים המלאים ב-E:G הם התוצאה שנשפכה ממנה: לחצו על F3 ותראו שהוא שייך לנוסחה שב-E2. אם מקלידים משהו בתוך האזור הזה, FILTER מציגה #SPILL! במקום השורות (ראו שגיאות #SPILL!).

התחביר של FILTER

=FILTER(array, include, [if_empty])

• array הוא מה שרוצים לקבל בחזרה: עמודה אחת, כמה עמודות או הטבלה כולה.
• include הוא תנאי עם TRUE או FALSE אחד לכל שורה של array, כמו B2:B7="North". חייב להיות בו בדיוק אותו מספר שורות כמו ב-array. (כדי לסנן עמודות במקום, תנו לו ערך אחד לכל עמודה.)
• if_empty הוא מה שיוצג כששום שורה לא מתאימה. בלעדיו, תוצאה ריקה היא השגיאה #CALC!.

FILTER דורשת Excel 2021, Excel 2024 או Microsoft 365. ב-Excel 2019 ובגרסאות ישנות יותר היא מציגה #NAME?, ושם הלחצן סינון בכרטיסיה נתונים הוא הדרך לסנן. גם ב-Google Sheets יש FILTER, ושם אפשר לתת כל תנאי גם כארגומנט נפרד.

השוואות טקסט אינן רגישות לגודל אותיות: B2:B7="north" מתאים ל-North. FILTER שומרת על השורות בסדר המקורי שלהן; מיון התוצאה הוא שלב נפרד, שמוצג בהמשך.

סינון לפי ערך בתא

כתיבה קבועה של "North" בנוסחה פירושה לערוך את הנוסחה בכל פעם. שימו את הערך בתא והשוו לתא במקום. בחרו אזור אחר ב-F1 והתוצאה מתעדכנת:

נוסחאותאיפוס

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

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

ל-West אין שורות, ולכן בחירה בו מציגה את הטקסט של if_empty, No sales.

מספרים עובדים באותה דרך. C2:C7>=F1 עם 100 ב-F1 משאירה כל שורה עם מכירות של 100 לפחות, ו-C2:C7>F1 הופכת את זה לגדול ממש.

FILTER עם כמה קריטריונים (AND)

כדי להשאיר שורה רק כששני תנאים מתקיימים יחד, הכפילו אותם. זה מחזיר שורות של North עם מכירות מעל 100:

נוסחאותאיפוס

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Ann (120) ו-Cara (200) עוברים. Finn הוא North אבל ה-60 שלו לא מעל 100, ולכן הוא לא נכלל.

למה כפל: כל תנאי הוא עמודה של TRUE ו-FALSE, ובחשבון TRUE נחשב 1 ו-FALSE נחשב 0. שורה מקבלת 1 רק כשכל הגורמים הם 1, ולכן * עובד כמו AND. כל תנאי צריך סוגריים משלו, ואפשר לשרשר כמה שרוצים: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

AND() לא עובדת כאן. AND(B2:B7="North",C2:C7>100) מצמצמת את כל הטווח ל-TRUE או FALSE יחיד במקום אחד לכל שורה, ולכן FILTER מקבלת צורה לא נכונה.

FILTER עם OR

חברו את התנאים כדי להשאיר שורה כשלפחות אחד מהם מתקיים. זה מחזיר שורות של North ושל East:

נוסחאותאיפוס

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

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

שורה שעומדת בשני התנאים מסתכמת ב-2, ו-FILTER משאירה כל שורה שהתוצאה שלה אינה 0, ולכן החיבור עובד כמו OR. אפשר לשלב את השניים: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) פירושה (North או East) וגם מעל 100. כאן זה מחזיר את Ann, Cara ו-Dan.

FILTER מחזירה #CALC! כששום דבר לא מתאים

כשאף שורה לא עוברת, ל-FILTER אין מה להחזיר. בלי ארגומנט שלישי זו השגיאה #CALC!; עם ארגומנט כזה, מקבלים טקסט משלכם:

נוסחאותאיפוס

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! לחישוב אין תוצאה, למשל FILTER שלא מצא כלום.

E2 מציג #CALC! ו-F2 מציג No match. שנו את B3 מ-South ל-West ושתי הנוסחאות מחזירות את Ben. כדי לא להציג כלום, השתמשו במחרוזת ריקה: =FILTER(A2:A7,B2:B7="West","").

הגיליון הזה מראה גם סינון של עמודה אחת: array הוא A2:A7, ולכן חוזרים רק שמות. כדי לקבל חלק מהעמודות של טבלה, עטפו את התוצאה ב-CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) מחזירה שמות ומכירות בלי האזור. CHOOSECOLS דורשת Microsoft 365 או Excel 2024.

מיון התוצאה של FILTER

FILTER מחזירה שורות בסדר שבו הן מופיעות בטבלה. עטפו אותה ב-SORT כדי לסדר את התוצאה: כאן שורות North ממוינות לפי מכירות, מהגבוה לנמוך. ה-3 הוא העמודה של התוצאה שלפיה ממיינים, ו--1 פירושו סדר יורד.

נוסחאותאיפוס

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Cara (200) ראשונה, אחריה Ann (120) ו-Finn (60). כדי להחזיר רק את השורות העליונות, עטפו פעם נוספת ב-TAKE: =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) משאירה את השתיים הראשונות (TAKE דורשת Microsoft 365 או Excel 2024). ב-SORT ו-SORTBY יש את שאר אפשרויות המיון.

FILTER לשורות שמכילות טקסט

ב-FILTER אין תווים כלליים, ולכן B2:B7="*th*" מחפשת את הטקסט *th* כפשוטו. כדי להשאיר שורות שהשם שלהן מכיל טקסט כלשהו, בדקו כל תא עם SEARCH, שמחזירה מיקום כשהטקסט נמצא ושגיאה כשלא, ועטפו אותה ב-ISNUMBER:

נוסחאותאיפוס

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

זה מחזיר את Ann ואת Dan: SEARCH מתעלמת מגודל האותיות, ולכן "an" מתאים גם ל-An של Ann. השתמשו ב-FIND במקום SEARCH להתאמה רגישה לגודל אותיות.

תרגול: FILTER עם שני תנאים

נוסחאותאיפוס

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

תורכם: ב-E2, החזירו את השורות (כל שלוש העמודות) של אנשי המכירות מ-South עם מכירות מעל 85.

בדיקההצגת התשובה

תרגול: FILTER לפי תא, עם ערך חלופי

נוסחאותאיפוס

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

תורכם: ב-F3, הציגו את השמות (רק עמודה A) של אנשי המכירות באזור שמוקלד ב-F1. אם אין כאלה, הציגו None.

בדיקההצגת התשובה

טעויות נפוצות ב-FILTER

• טווחים בגבהים שונים. =FILTER(A2:C7,B2:B6="North") בודקת 5 שורות בטבלה של 6 שורות, ואקסל מחזיר #VALUE!. ודאו ש-include מתחיל ונגמר באותן שורות כמו array.
• אפסים במקום שבו המקור ריק. FILTER מחזירה 0 לתא ריק ב-array. החליפו את התאים הריקים בטקסט ריק לפני הסינון: =FILTER(IF(A2:C7="","",A2:C7),B2:B7="North").
• עמודות שלמות. =FILTER(A:C,B:B="North") עובדת, אבל אם הנוסחה עצמה נמצאת בעמודות A עד C היא מפנה לעצמה. שימו את התוצאה ליד הטבלה, או השתמשו בטווח קבוע כמו A2:C1000.
• מירכאות סביב מספרים. C2:C7>"100" משווה מספרים לטקסט ולא משאירה כלום. כתבו C2:C7>100.
• לצפות ללחצן הסינון. FILTER מעתיקה את השורות המתאימות למקום חדש ומשאירה את הטבלה כמו שהיא. כדי להסתיר שורות בטבלה עצמה, השתמשו ב-נתונים > סינון.