כיצד ליצור Excel פורמולה בדיקת עם מספר קריטריונים

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

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

01 מתוך 08

הכן את חוברת העבודה של Excel עם נתונים

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

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

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

02 מתוך 08

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

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

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

1. לחץ על התא F3 כדי להפוך אותו לתא הפעיל - זה המקום שבו ניכנס לתפקוד המקונן.
2. הקלק על ה נוסחאות הכרטיסייה של תפריט הסרט.
3. בחר בדיקה ועיון מסרגל הכלים כדי לפתוח את הפונקציה הנפתחת.
4. לחץ על אינדקס ברשימה כדי להעלות את בחר ארגומנטים תיבת דיאלוג.
5. בחר את array, row_num, col_num אפשרות בתיבת הדו - שיח.
6. לחץ על בסדר כדי לפתוח את תיבת הדו-שיח פונקציה INDEX; זה יפתח את פורמולה בונה ב- Excel.
7. בתוך ה פורמולה בונה, הקלק על ה מערך קו.
8. תאים להדגיש D6 ל F11, על ידי לחיצה וגרירה, בגליון העבודה כדי להזין את הטווח לתוך הקבלן.

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

03 מתוך 08

הפעל את פונקציית MATCH המקוננת

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

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

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

בדרך כלל Lookup_value מקבל רק קריטריון חיפוש אחד או מונח. כדי לחפש קריטריונים מרובים, עלינו להאריך את Lookup_value; tשלו נעשה על ידי שרשור או הצטרפות שניים או יותר הפניות תא יחד באמצעות סמל אמפרסנד - &.

1. בתוך ה פורמולה בונה, הקלק על ה Row_num קו.
2. הקלד את שם הפונקציה התאמה ואחריו סוגר פתוח עגול.
3. לחץ על התא D3 כדי להזין את הפניה תא לתוך תיבת הדו שיח.
4. הקלד אמפרסנד לאחר הפניה לתא D3 על מנת להוסיף התייחסות תא השני.
5. לחץ על התא E3 כדי להזין את הפניה התא השני.
6. הקלד פסיק לאחר הפניה לתא E3 כדי להשלים את הכניסה של הפונקציה MATCH Lookup_value ויכוח.

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

04 מתוך 08

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

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

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

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

1. הקלק על ה Row_num שורה אחרי פסיק כדי למקם את נקודת ההכנסה בסוף הערך הנוכחי.
2. תאים להדגיש D6 ל D11 בגליון העבודה כדי להיכנס לטווח - זהו המערך הראשון הפונקציה היא לחפש.
3. הקלד אמפרסנד לאחר הפניות לתא D6: D11 כי אנחנו רוצים את הפונקציה לחפש שני מערכים.
4. תאים להדגיש E6 ל E11 בגליון העבודה כדי להיכנס לטווח - זהו המערך השני הפונקציה היא לחפש.
5. הקלד פסיק לאחר הפניה לתא E3 כדי להשלים את הכניסה של התאמה פונקציה Lookup_array ויכוח.
6. עזוב את ה אינדקס תיבת הדו-שיח פונקציה פתוחה עבור השלב הבא במדריך.

05 מתוך 08

הוסף את הסוג 'סוג MATCH'

הטיעון השלישי והאחרון של הפונקציה MATCH הוא Match_type הוויכוח; זה אומר Excel איך להתאים את Lookup_value עם ערכים ב- Lookup_array - האפשרויות הזמינות הן 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 שורה ב אינדקס פונקציה.

1. לאחר הפסיק על Row_num שורה, הקלד אפס - 0 - מכיוון שאנו רוצים שהפונקציה המקוננת תחזיר התאמות מדויקות לתנאים שהכנסנו לתאים D3 ו E3.
2. סוג סוגר סוגר עגול - ) - כדי להשלים את התאמה פונקציה.
3. עזוב את ה פורמולה בונה פתח את השלב הבא במדריך.

06 מתוך 08

גימור פונקציית INDEX

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

1. הקלק על ה Column_num קו.
2. הזן את מספר שלוש - 3 - על הקו הזה מאז אנחנו מחפשים נתונים בעמודה השלישית של הטווח D6 ל F11.
3. שוב, לעזוב את פורמולה בונה פתח את השלב הבא במדריך.

07 מתוך 08

יצירת נוסחת מערך

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

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

1. סגור את פורמולה בונה על ידי לחיצה על בוצע כפתור.
2. לאחר מכן, בחר תא F4 ולחץ על להיכנס כדי להציג את הנוסחה.
3. כדי להמיר את הנוסחה למערך, לחץ בו-זמנית CTRL + SHIFT + ENTER על המקלדת שלך.
4. אם נעשה כראוי א # N / A השגיאה תופיע בתא F3 - התא שבו נכנסנו לתפקוד.
5. ה # N / A שגיאה מופיעה בתא F3 כי תאים D3 ו E3 ריקים. D3 ו E3 הם התאים שבהם אמרנו את הפונקציה כדי למצוא את Lookup_values. לאחר הוספת נתונים לשני תאים אלה, השגיאה תוחלף על ידי מידע ממסד הנתונים.

08 מתוך 08

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

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

1. לחץ על התא D3.
2. הקלד ווידג'טים ולחץ על להיכנס מפתח במקלדת.
3. לחץ על התא E3.
4. הקלד טיטניום ולחץ על להיכנס מפתח במקלדת.
5. שם הספק ווידג'טים בע"מ אמור להופיע בתא F3 - המיקום של הפונקציה שכן הוא הספק היחיד שמוכר שמוכר טיטניום יישומונים.
6. כאשר אתה לוחץ על F3 התא הפונקציה המלאה מופיעה בסרגל הנוסחה מעל גליון העבודה.

{= INDEX (D6: F11, MATCH (D3 ו- E3, D6: D11 ו- E6: E11, 0), 3)}

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