הפונקציה המגוונת והשימושית FILTER

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

הפונקציות ה VLOOKUP/XLOOKUP/HLOOKUP/INDEX&MATCH, מחזירות ערך אחד, הפונקציה FILTER מחזירה מערך נתונים המתאימים לתנאי שקבענו.

מבנה הפונקציה:

=FILTER(range, condition1, [condition2, ...])

בחלקה הראשון נזין את הטווח שאותו אנחנו רוצים לסנן,

בחלקה השני נזין את התנאי או התנאים שלפיהם נרצה לסנן.

דוגמאות:

לפנינו מסד נתונים המכיל מכוניות חשמליות לפי מודל, שנה וטווח הנסיעה על הנעה חשמלית.

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

נכתוב את הנוסחה:

=FILTER(A2:E81,C2:C81="BMW")

בחלק הראשון (בכתום) נסמן את טווח הנתונים ובחלק השני נכתוב את התנאי, במקרה שלנו שטווח C2:C81 יהיה שווה ל BMW.

בתמונה למטה אנחנו רואים שקיבלנו את כל הנתונים מכל הטבלה - רק אם הם מתאימים ל BMW.

שימוש בשני תנאים:

כעת אנחנו מעוניינים לא רק לקבל את כל הנתונים השייכים ל BMW אלא גם השייכים לשנת המודל 2019.

נכתוב את הנוסחה כך:

=FILTER(A2:E81,C2:C81="BMW",B2:B81=2019)

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

סיננו ע"פ שני תנאים גם טור C = BMW וגם טור B=2019.

לא חייבים להשתמש תמיד בסימן = אפשר גם להשתמש בתנאים גדול/קטן מ...

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

נכתוב את הנוסחה כך:

=FILTER(A2:E81,E2:E81>250)

נקבל רק את הנתונים המתאימים בטור E לגדול מ 250.

---

FILTER על FILTER

למי שמכיר את הפונקציות INDEX & MATCH יודע שאפשר להוציא נתון עם תנאי גם על ציר ה X וגם על ציר הY. שימוש פעמיים ב FILTER ישיג את אותה תוצאה.

לפנינו טבלת הוצאות עסק עם מחלקות בציר ה Y ושנים בציר הX אנחנו מעוניינים לקבל את הנתון עם שני תנאים.

נכתוב את הנוסחה:

=FILTER(FILTER(H4:M8,G4:G8=P2),H3:M3=O2)

שימוש ראשון בפונקציה FILTER (באדום) יחזיר לנו את הנתונים שמתאימים למחלקות בציר Y.

אותה תעטוף פונקציה FILTER (בכחול) נוספת שתסנן את הנתונים לפי השנה בציר ה X.

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

---

שימוש ב FILTER עם איחוד נתונים משתי טבלאות:

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

נכתוב את הנוסחה כך:

=FILTER({H3:I6;K3:L6},{I3:I6;L3:L6}>50)

בחלק של הטווח (באדום) נשתמש בסוגריים מסולסלים כדי לאחד את שתי הטבלאות,

למי שלא מכיר את כוחם של הסוגריים המסולסלים מוזמן לקרוא כאן.

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

אינסוף אפשרויות לפונקציה הכל כך יעילה הזו.

בהצלחה!

---