סיכום
מאמר זה מתאר שלב אחר שלב כיצד למצוא נתונים בטבלה (או בטווח תאים) באמצעות פונקציות מוכללות שונות ב- Microsoft Excel. באפשרותך להשתמש בנוסחאות שונות כדי לקבל את אותה תוצאה.
יצירת גליון העבודה לדוגמה
מאמר זה משתמש בגליון עבודה לדוגמה כדי להמחיש את הפונקציות המוכללות של Excel. שקול את הדוגמה של הפניה לשם מעמודה A והחזרת גילו של אדם זה מעמודה C. כדי ליצור גליון עבודה זה, הזן את הנתונים הבאים לתוך גליון עבודה ריק של Excel.
תקליד את הערך שברצונך לחפש בתא E2. באפשרותך להקליד את הנוסחה בכל תא ריק באותו גליון עבודה.
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | שם | מחלקה | Age | חיפוש ערך | ||
| 2 | אורי | 501 | 28 | מרי | ||
| 3 | אנונימי | 201 | 19 | |||
| 4 | מרי | 101 | 22 | |||
| 5 | לארי | 301 | 29 |
הגדרות מונחים
מאמר זה משתמש במונחים הבאים כדי לתאר את הפונקציות המוכללות של Excel:
| מונח | הגדרה | דוגמה |
|---|---|---|
| מערך טבלה | טבלת בדיקת המידע כולה | A2:C5 |
| Lookup_Value | הערך שיש למצוא בעמודה הראשונה של Table_Array. | E2 |
| Lookup_Array -לחלופין- Lookup_Vector |
טווח התאים המכיל ערכי בדיקת מידע אפשריים. | ת2:A5 |
| Col_Index_Num | יש להחזיר את מספר העמודה Table_Array הערך התואם. | 3 (העמודה השלישית ב Table_Array) |
| Result_Array -לחלופין- Result_Vector |
טווח המכיל שורה אחת או עמודה אחת בלבד. הגודל חייב להיות זהה לגודל Lookup_Array או Lookup_Vector. | C2:C5 |
| Range_Lookup | ערך לוגי (TRUE או FALSE). אם ארגומנט זה הוא TRUE או מושמט, הפונקציה מחזירה התאמה מקורבת. אם ארגומנט זה הוא FALSE, הוא יחפש התאמה מדויקת. | FALSE |
| Top_cell | זוהי ההפניה שממנה ברצונך לבסס את הקיזוז. Top_Cell חייב להפנות לתא או לטווח של תאים סמוכים. אחרת, הפונקציה OFFSET מחזירה את #VALUE! ערך שגיאה. | |
| Offset_Col | זהו מספר העמודות, לשמאל או לימין, שברצונך שהתא הימני העליון יפנה אליו. לדוגמה, "5" כארגומנט Offset_Col מציין שהתא הימני העליון בהפניה ימוקם חמש עמודות משמאל ל- reference. Offset_Col יכול להיות חיובי (כלומר משמאל להפניה ההתחלתית) או שלילי (כלומר, מימין להפניה ההתחלתית). |
פונקציות
LOOKUP()
הפונקציה LOOKUP מוצאת ערך בשורה או בעמודה אחת ומתאימה אותו לערך באותו מיקום בשורה או בעמודה אחרת.
להלן דוגמה לתחביר הנוסחה LOOKUP:
=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)
הנוסחה הבאה מוצאת את גילה של מרים בגליון העבודה לדוגמה:
=LOOKUP(E2,A2:A5,C2:C5)
הנוסחה משתמשת בערך "מרי" בתא E2 ומוצאת את "מרי" בווקטור בדיקת המידע (עמודה A). לאחר מכן, הנוסחה מתאימה לערך באותה שורה בווקטור התוצאה (עמודה C). מכיוון ש"מריה" נמצאת בשורה 4, הפונקציה LOOKUP מחזירה את הערך משורה 4 בעמודה C (22).
הערה: הפונקציה LOOKUP דורשת מיון של הטבלה.
לקבלת מידע נוסף על הפונקציה LOOKUP , לחץ על מספר המאמר שלהלן כדי להציגו מתוך מאגר הידע Microsoft Knowledge Base:
כיצד להשתמש בפונקציה LOOKUP ב- Excel
VLOOKUP()
הפונקציה VLOOKUP או בדיקת מידע אנכית נמצאת בשימוש כאשר נתונים מפורטים בעמודות. פונקציה זו מחפשת ערך בעמודה הימנית ביותר ומתאימה אותו לנתונים בעמודה שצוינה באותה שורה. באפשרותך להשתמש בפונקציה VLOOKUP כדי למצוא נתונים בטבלה ממוינת או לא ממוינת. הדוגמה הבאה משתמשת בטבלה עם נתונים לא ממוינים.
להלן דוגמה לתחביר הנוסחה VLOOKUP :
=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)
הנוסחה הבאה מוצאת את גילה של מרים בגליון העבודה לדוגמה:
=VLOOKUP(E2,A2:C5,3,FALSE)
הנוסחה משתמשת בערך "מרי" בתא E2 ומוצאת את "מרי" בעמודה הימנית ביותר (עמודה A). לאחר מכן, הנוסחה מתאימה לערך באותה שורה ב Column_Index. בדוגמה זו נעשה שימוש ב- "3" Column_Index (עמודה C). מכיוון ש- "Mary" נמצא בשורה 4, הפונקציה VLOOKUP מחזירה את הערך משורה 4 בעמודה C (22).
לקבלת מידע נוסף על הפונקציה VLOOKUP , לחץ על מספר המאמר שלהלן כדי להציגו מתוך מאגר הידע Microsoft Knowledge Base:
כיצד להשתמש ב- VLOOKUP או ב- HLOOKUP כדי למצוא התאמה מדויקת
INDEX() ו- MATCH()
באפשרותך להשתמש בפונקציות INDEX ו- MATCH יחד כדי לקבל את אותן תוצאות כמו באמצעות LOOKUP או VLOOKUP.
להלן דוגמה לתחביר המשלב את INDEX ו - MATCH כדי להפיק את אותן תוצאות כמו LOOKUP ו - VLOOKUP בדוגמאות הקודמות:
=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)
הנוסחה הבאה מוצאת את גילה של מרים בגליון העבודה לדוגמה:
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)
הנוסחה משתמשת בערך "מרי" בתא E2 ומוצאת את "מרי" בעמודה A. לאחר מכן הוא מתאים לערך באותה שורה בעמודה C. מכיוון ש- "Mary" נמצאת בשורה 4, הנוסחה מחזירה את הערך משורה 4 בעמודה C (22).
הערה: אם אף אחד מהתאים ב- Lookup_Array אינו תואם Lookup_Value ("מרי"), נוסחה זו תחזיר #N/A.
לקבלת מידע נוסף על הפונקציה INDEX , לחץ על מספר המאמר שלהלן כדי להציגו מתוך מאגר הידע Microsoft Knowledge Base:
כיצד להשתמש בפונקציה INDEX כדי למצוא נתונים בטבלה
OFFSET() ו- MATCH()
באפשרותך להשתמש בפונקציות OFFSET ו - MATCH יחד כדי להפיק תוצאות זהות לאלה של הפונקציות בדוגמה הקודמת.
להלן דוגמה לתחביר המשלב את OFFSET ו- MATCH כדי להפיק את אותן תוצאות כמו LOOKUP ו- VLOOKUP:
=OFFSET(top_cell,MATCH(Lookup_Value,Lookup_Array,0),Offset_Col)
נוסחה זו מוצאת את גילה של מרים בגליון העבודה לדוגמה:
=OFFSET(A1,MATCH(E2,A2:A5,0),2)
הנוסחה משתמשת בערך "מרי" בתא E2 ומוצאת את "מרי" בעמודה A. לאחר מכן, הנוסחה מתאימה את הערך באותה שורה אך שתי עמודות ימינה (עמודה C). מכיוון ש"מריה" נמצאת בעמודה A, הנוסחה מחזירה את הערך בשורה 4 בעמודה C (22).
לקבלת מידע נוסף על הפונקציה OFFSET , לחץ על מספר המאמר שלהלן כדי להציגו מתוך מאגר הידע Microsoft Knowledge Base: