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

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

נוסחת המערך כרוכה בקינון הפונקציה MATCH בתוך הפונקציה INDEX .

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

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

01 של 09

הזנת נתוני המדריך

השלב הראשון במדריך הוא להזין את הנתונים לתוך גיליון עבודה של Excel.

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

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

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

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

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

02 מתוך 09

הפעלת פונקציית INDEX

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

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

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

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

שלבי הדרכה

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

03 מתוך 09

הזנת ארגומנט מערך המעמד INDEX

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

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

שלבי הדרכה

1. בתיבת הדו-שיח פונקציית INDEX, לחץ על שורת המערכים .
2. הדגשת תאים D6 עד F11 בגליון העבודה כדי להזין את טווח לתוך תיבת הדו שיח.

04 של 09

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

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

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

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

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

הזנת התכונה 'בדיקת Lookup_value' של MATCH

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

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

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

זה נעשה על ידי שרשור או הצטרפות שניים או יותר הפניות תא יחד באמצעות סמל אמפרסנד " & ".

שלבי הדרכה

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

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

05 מתוך 09

הוספת Lookup_array עבור פונקציית MATCH

שלב זה מכסה את הוספת הטיעון Lookup_array עבור פונקציה MATCH מקוננות.

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

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

כדי להזין מערכים מרובים אנחנו שוב להשתמש אמפרסנד " & " כדי לשרשר את המערכים יחד.

שלבי הדרכה

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

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

06 מתוך 09

הוספת סוג ההתאמה והשלמת פונקציית 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 בתיבת הדו-שיח פונקציה INDEX.

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

07 מתוך 09

חזרה לפונקציה INDEX

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

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

שלבי הדרכה

1. לחץ על השורה Column_num בתיבת הדו-שיח.
2. הזן את המספר 3 " 3 " (ללא הצעות מחיר) בשורה זו, מכיוון שאנו מחפשים נתונים בעמודה השלישית של טווח D6 עד F11.
3. אל תלחץ על אישור או סגור את תיבת הדו-שיח פונקציה INDEX. זה חייב להישאר פתוח לשלב הבא של הדרכה - יצירת נוסחת מערך .

08 מתוך 09

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

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

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

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

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

שלבי הדרכה

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

09 של 09

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

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

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

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

שלבי הדרכה

1. לחץ על תא D3.
2. הקלד Widgets ולחץ על המקש Enter במקלדת.
3. לחץ על תא E3.
4. הקלד טיטניום ולחץ על מקש Enter במקלדת.
5. השם של הספק יישומונים בע"מ צריכה להופיע בתא F3 - המיקום של הפונקציה שכן הוא הספק היחיד שמוכר שמוכר טיטניום יישומונים.
6. כאשר אתה לוחץ על F3 התא להשלים את הפונקציה
   {= INDEX (D6: F11, MATCH (D3 ו- E3, D6: D11 ו- E6: E11, 0), 3)}
   מופיע בסרגל הנוסחה מעל גליון העבודה .

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