לְהִצטַיֵן

פילטר עם קריטריונים רבים או

Filter With Multiple

נוסחת Excel: פילטר עם מספר קריטריונים ORסיכום

כדי לחלץ נתונים עם תנאי OR מרובים, תוכל להשתמש ב פונקציית פילטר ביחד עם ה פונקציית MATCH . בדוגמה המוצגת, הנוסחה ב- F9 היא:



 
= FILTER (B5:D16,  ISNUMBER ( MATCH (items,F5:F6,0))*  ISNUMBER ( MATCH (colors,G5:G6,0))*  ISNUMBER ( MATCH (cities,H5:H6,0)))

איפה פריטים (B3: B16), צבעים (C3: C16) ו- ערים (D3: D16) הם טווחים בשם .

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





הֶסבֵּר

בדוגמה זו, קריטריונים מוזנים בטווח F5: H6. ההיגיון של הנוסחה הוא:

הפריט הוא (חולצה או קפוצ'ון) והצבע הוא (אדום או כחול) והעיר היא (דנבר או סיאטל)



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

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

 
 MATCH (items,F5:F6,0) // check for tshirt or hoodie

מכיוון שיש 12 ערכים בנתונים, אנו התוצאה היא מַעֲרָך עם 12 ערכים כאלה:

 
{1#N/A#N/A2#N/A22#N/A1#N/A21}

מערך זה מכיל #שגיאות לא רלוונטיות (ללא התאמה) או מספרים (התאמה). מספרי ההודעה מתאימים לפריטים שהם חולצה או קפוצ'ון. כדי להמיר מערך זה לערכי TRUE ו- FALSE, הפונקציה MATCH עטופה בפונקציה ISNUMBER:

 
 ISNUMBER ( MATCH (items,F5:F6,0))

שמניב מערך כזה:

כיצד להעביר תאים ב- Excel מבלי להחליף
 
{TRUEFALSEFALSETRUEFALSETRUETRUEFALSETRUEFALSETRUETRUE}

במערך זה ערכי TRUE מתאימים לחולצה או קפוצ'ון.

הנוסחה המלאה מכילה שלושה ביטויים כמו האמור לעיל המשמשים לטיעון כולל של הפונקציה FILTER:

 
 ISNUMBER ( MATCH (items,F5:F6,0))* // tshirt or hoodie  ISNUMBER ( MATCH (colors,G5:G6,0))* // red or blue  ISNUMBER ( MATCH (cities,H5:H6,0))) // denver or seattle

לאחר הערכת MATCH ו- ISNUMBER, יש לנו שלושה מערכים המכילים ערכי TRUE ו- FALSE. פעולת המתמטיקה של הכפלת מערכים אלה יחדיו מכריחה את ערכי TRUE ו- FALSE ל- 1s ו- 0s, כך שנוכל לדמיין את המערכים בשלב זה כך:

 
{100101101011}* {101101000001}* {101001011001}

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

 
{100001000001}

שהופך לטיעון הכלול בפונקציית FILTER:

 
= FILTER (B5:D16,{100001000001})

התוצאה הסופית היא שלוש שורות הנתונים המוצגות ב- F9: H11

עם ערכים מקודדים

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

 
= FILTER (B5:D16,  ISNUMBER ( MATCH (items,{'Tshirt''Hoodie'},0))*  ISNUMBER ( MATCH (colors,{'Red''Blue'},0))*  ISNUMBER ( MATCH (cities,{'Denver''Seattle'},0)))
הסופר דייב בראנס


^