איך ליצור נוסחת בדיקת אקסל עם קריטריונים מרובים

2024 מְחַבֵּר: Abigail Brown | [email protected]. שונה לאחרונה: 2024-01-07 19:03

מה צריך לדעת

• תחילה, צור פונקציה INDEX, ולאחר מכן התחל את פונקציית MATCH המקוננת על ידי הזנת הארגומנט Lookup_value.
• לאחר מכן, הוסף את הארגומנט Lookup_array ואחריו את הארגומנט Match_type, ולאחר מכן ציין את טווח העמודות.
• לאחר מכן, הפוך את הפונקציה המקוננת לנוסחת מערך על ידי הקשה על Ctrl+ Shift+ Enter. לבסוף, הוסף את מונחי החיפוש לגיליון העבודה.

מאמר זה מסביר כיצד ליצור נוסחת חיפוש המשתמשת במספר קריטריונים ב-Excel כדי למצוא מידע במסד נתונים או בטבלת נתונים באמצעות נוסחת מערך.נוסחת המערך כוללת קינון של פונקציית MATCH בתוך הפונקציה INDEX. המידע מכסה את Excel עבור Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 ו-Excel עבור Mac.

עקוב אחרי ההדרכה

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

• הזן את טווח הנתונים העליון לתאים D1 עד F2.
• הזן את הטווח השני לתאים D5 עד F11.

צור פונקציית INDEX ב-Excel

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

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

בצע את השלבים הבאים כדי ליצור את הפונקציה INDEX:

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

2. עבור אל נוסחאות.

   

3. בחר חיפוש והפניה כדי לפתוח את הרשימה הנפתחת של הפונקציות.
4. בחר INDEX כדי לפתוח את תיבת הדו-שיח Select Arguments.
5. בחר מערך, row_num, column_num.
6. בחר OK כדי לפתוח את תיבת הדו-שיח Function Arguments. ב-Excel for Mac, Formula Builder נפתח.
7. מקם את הסמן בתיבת הטקסט Array.

8. הדגש תאים D6 עד F11 בגיליון העבודה כדי להזין את הטווח לתיבת הדו-שיח.

   השאירו את תיבת הדו-שיח Function Arguments פתוחה. הנוסחה לא הסתיימה. תשלים את הנוסחה בהוראות למטה.

   

התחל את הפונקציה Nested MATCH

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

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

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

The Lookup_value מקבל רק קריטריון חיפוש אחד או מונח אחד. כדי לחפש קריטריונים מרובים, הרחב את Lookup_value על ידי שרשור, או צירוף, של שני הפניות תאים או יותר באמצעות סמל האמפרסנד (&).

1. בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט Row_num.
2. הזן MATCH(.
3. בחר תא D3 כדי להזין את הפניה לתא הזה לתיבת הדו-שיח.
4. הזן & (האמפרסנד) אחרי הפניה לתא D3 כדי להוסיף הפניה לתא שני.
5. בחר תא E3 כדי להזין את הפניה לתא השני.

6. הזן , (פסיק) אחרי הפניה לתא E3 כדי להשלים את הזנת הארגומנט Lookup_value של הפונקציה MATCH.

   

   בשלב האחרון של המדריך, ה- Lookup_values יוזנו לתאים D3 ו-E3 של גליון העבודה.

השלם את הפונקציה Nested MATCH

שלב זה מכסה את הוספת הארגומנט Lookup_array עבור הפונקציה המקוננת MATCH. מערך Lookup_ הוא טווח התאים שפונקציית ה-MATCH מחפשת כדי למצוא את הארגומנט Lookup_value שנוסף בשלב הקודם של המדריך.

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

1. מקם את הסמן בסוף הנתונים בתיבת הטקסט Row_num. הסמן מופיע אחרי הפסיק בסוף הערך הנוכחי.
2. הדגש תאים D6 עד D11 בגיליון העבודה כדי להיכנס לטווח. טווח זה הוא המערך הראשון שהפונקציה מחפשת.
3. הזן & (סימן אמפרסנד) אחרי ההפניות לתא D6:D11. סמל זה גורם לפונקציה לחפש שני מערכים.

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

5. הזן , (פסיק) אחרי הפניה לתא E3 כדי להשלים את הזנת הארגומנט Lookup_array של פונקציית MATCH.

   

6. השאירו את תיבת הדו-שיח פתוחה לשלב הבא במדריך.

הוסף את ארגומנט סוג ה-MATCH

הארגומנט השלישי והאחרון של הפונקציה MATCH הוא הארגומנט Match_type. ארגומנט זה אומר לאקסל כיצד להתאים את Lookup_value לערכים במערך Lookup_. האפשרויות הזמינות הן 1, 0 או -1.

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

• אם Match_type=1 או מושמט, MATCH מוצא את הערך הגדול ביותר הקטן או שווה ל- Lookup_value. יש למיין את נתוני Lookup_array בסדר עולה.
• אם Match_type=0, MATCH מוצא את הערך הראשון ששווה ל- Lookup_value. ניתן למיין את נתוני Lookup_array בכל סדר.
• אם Match_type=-1, MATCH מוצא את הערך הקטן ביותר שגדול או שווה ל- Lookup_value. יש למיין את נתוני Lookup_array בסדר יורד.

הזן את השלבים האלה אחרי הפסיק שהוזן בשלב הקודם בשורה Row_num בפונקציית INDEX:

1. הזן 0 (אפס) אחרי הפסיק בתיבת הטקסט Row_num. מספר זה גורם לפונקציה המקוננת להחזיר התאמות מדויקות למונחים שהוזנו בתאים D3 ו-E3.

2. הזן ) (סוגר סגירה) כדי להשלים את הפונקציה MATCH.

   

3. השאירו את תיבת הדו-שיח פתוחה לשלב הבא במדריך.

סיים את פונקציית INDEX

פונקציית MATCH הושלמה. הגיע הזמן לעבור לתיבת הטקסט Column_num של תיבת הדו-שיח ולהזין את הארגומנט האחרון עבור הפונקציה INDEX. ארגומנט זה אומר לאקסל שמספר העמודה נמצא בטווח D6 עד F11. טווח זה הוא המקום בו הוא מוצא את המידע המוחזר על ידי הפונקציה.במקרה זה, ספק עבור ווידג'טים טיטניום.

1. מקם את הסמן בתיבת הטקסט Column_num.

2. הזן 3 (המספר שלוש). מספר זה אומר לנוסחה לחפש נתונים בעמודה השלישית של הטווח D6 עד F11.

   

3. השאירו את תיבת הדו-שיח פתוחה לשלב הבא במדריך.

צור את נוסחת המערך

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

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

1. בחר OK כדי לסגור את תיבת הדו-שיח. ב-Excel עבור Mac, בחר Done.
2. בחר תא F3 כדי להציג את הנוסחה, ולאחר מכן מקם את הסמן בסוף הנוסחה בסרגל הנוסחאות.
3. כדי להמיר את הנוסחה למערך, הקש CTRL+ SHIFT+ ENTER.
4. A N/A שגיאה מופיעה בתא F3. זהו התא שבו הוזנה הפונקציה.

5. שגיאת N/A מופיעה בתא F3 מכיוון שהתאים D3 ו-E3 ריקים. D3 ו-E3 הם התאים שבהם הפונקציה מחפשת למצוא את Lookup_value. לאחר הוספת נתונים לשני התאים הללו, השגיאה מוחלפת במידע ממסד הנתונים.

   

הוסף את קריטריוני החיפוש

השלב האחרון הוא הוספת מונחי החיפוש לגיליון העבודה. שלב זה תואם את המונחים ווידג'טים מעמודה 1 וטיטניום מעמודה 2.

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

1. בחר תא D3.
2. הזן Widgets.
3. בחר תא E3.
4. הקלד Titanium והקש Enter.
5. שם הספק, Widgets Inc., מופיע בתא F3. זהו הספק היחיד הרשום שמוכר ווידג'טים של טיטניום.

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

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   בדוגמה זו, יש רק ספק אחד עבור ווידג'טים של טיטניום. אם היה יותר מספק אחד, הספק הרשום הראשון במסד הנתונים מוחזר על ידי הפונקציה.