הפונקציה של VLOOKUP של Excel משמשת לחיפוש והחזרת מידע מטבלת נתונים המבוססת על ערך בדיקה שאתה בוחר.
בדרך כלל, VLOOKUP מחייב את ערך החיפוש להיות בעמודה השמאלית ביותר של טבלת הנתונים, והפונקציה מחזירה שדה נוסף של נתונים הממוקם באותה שורה מימין לערך זה.
עם זאת, על ידי שילוב VLOOKUP עם הפונקציה CHOOSE, ניתן ליצור נוסחת חיפוש שמאלית כי:
- מתיר את ערך החיפוש להיות מכל עמודה בטבלת הנתונים
- מחזירה מידע הממוקם בעמודה כלשהי משמאל לערך החיפוש
הערה: הוראות אלה חלות על גירסאות Excel 2019, 2016, 2013, 2010 ו- Excel עבור Office 365.
Friday of 05ערכת לימוד: שימוש בפונקציות VLOOKUP ו- CHOOSE במבנה של חיפוש שמאלי
כדי ליצור את נוסחת החיפוש השמאלית בתמונת הדוגמה, השתמש בנוסחה הבאה:
= V $ $, $ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
בדוגמה זו, הנוסחה מאפשרת למצוא את החלק המסופק על ידי החברות השונות המופיעות בעמודה 3 של טבלת הנתונים.
תפקידו של הפונקציה CHOOSE בנוסחה היא לטעות VLOOKUP להאמין כי עמודה 3 הוא עמודה 1. כתוצאה מכך, שם החברה יכול לשמש ערך בדיקת למצוא את שם החלק המסופק על ידי כל חברה.
הזן את נתוני המדריך
- הזן את הכותרת ספק לת D1.
- הזן את הכותרת חלק בתא E1.
- הזן את טבלת הנתונים שנראית בתמונה מעל לתאים D4 עד F9
- שורות 2 ו -3 נותרים ריקים כדי להתאים את קריטריוני החיפוש ואת נוסחת החיפוש השמאלית שנוצרה במהלך הדרכה זו
פתח את תיבת הדו-שיח VLOOKUP
למרות שניתן להקליד את הנוסחה ישירות לתוך התא F1 בגליון העבודה, אנשים רבים מתקשים עם התחביר של הנוסחה.
במקרה זה, בטוח יותר להשתמש בתיבת הדו-שיח VLOOKUP. כמעט לכל הפונקציות של Excel יש תיבת דו-שיח המאפשרת לך להזין כל אחד מארגומנטים של הפונקציה בשורה נפרדת.
- לחץ על התא E2 של גליון העבודה. E2 הוא המיקום שבו יוצגו תוצאות נוסחת החיפוש השמאלית.
- הקלק על ה נוסחאות הכרטיסייה של הסרט.
- הקלק על ה Lookup & הפניה אפשרות בסרט כדי לפתוח את הרשימה הנפתחת של הפונקציה.
- לחץ על VLOOKUP ברשימה כדי להציג את תיבת הדו-שיח של הפונקציה.
הזנת ארגומנטים לתיבת הדו שיח של VLOOKUP
ארגומנטים של פונקציה הם הערכים המשמשים את הפונקציה לחישוב תוצאה.
בתיבת הדו-שיח של פונקציה, השם של כל ארגומנט הוא בשורה נפרדת ולאחר מכן בשדה שבו להזין ערך.
הזן את הערכים הבאים עבור כל אחד מהטיעונים של VLOOKUP בשורה הנכונה של תיבת הדו-שיח, כפי שמוצג בתמונה הנלווית.
ערך בדיקת
ערך החיפוש הוא שדה המידע המשמש לחיפוש מערך הטבלה. VLOOKUP מחזיר שדה נתונים נוסף מאותה שורה כמו ערך החיפוש.
דוגמה זו משתמשת בהתייחסות תא למיקום שבו ייכנס שם החברה לגליון העבודה. היתרון של זה הוא שזה מקל לשנות את שם החברה ללא עריכת הנוסחה.
- הקלק על ה lookup_value בשורה בתיבת הדו-שיח.
- לחץ על התא D2 כדי להוסיף התייחסות זו התא lookup_value קו.
- לחץ על F4 מפתח על המקלדת כדי להפוך את התא התייחסות מוחלטת - $ D $ 2.
הפניות מוחלטים Cell
הפניות מוחלטים של התא משמשות לערך החיפוש ולארגומנטים של מערך הטבלה כדי למנוע שגיאות אם נוסחת החיפוש מועתקת לתאים אחרים בגליון העבודה.
כניסה לפונקציה CHOOSE
ארגומנט מערך הטבלה הוא גוש נתונים רציף שממנו מאוחזרים פרטים ספציפיים.
בדרך כלל, VLOOKUP נראה רק משמאל לארגומנט ערך החיפוש כדי למצוא נתונים במערך הטבלה. כדי לקבל את זה להסתכל שמאלה, VLOOKUP חייב להיות מרומה על ידי סידור מחדש של עמודות במערך הטבלה באמצעות הפונקציה CHOOSE.
בנוסחה זו, הפונקציה CHOOSE משיגה שתי משימות:
- הוא יוצר מערך טבלה, כי הוא רק שני עמודות רחב (עמודות D ו- F).
- הוא משנה את הסדר מימין לשמאל של העמודות במערך הטבלה, כך שעמודה F מגיעה ראשונה ועמוד D הוא השני.
הזנת פונקציות
בעת הזנת פונקציות באופן ידני, כל אחד מארגומנטים של הפונקציה חייב להיות מופרד באמצעות פסיק.
- בתיבת הדו-שיח פונקציה VLOOKUP, לחץ על טבלה קו.
- הזן את הפרטים הבאים בחר פונקCHOOSE ({1,2}, $ F: $ F, $ D: $ D)
מספר אינדקס עמודה
בדרך כלל, מספר אינדקס העמודה מציין איזה עמודה של מערך הטבלה מכילה את הנתונים שאחריהם. עם זאת, בנוסחה זו, הוא מתייחס לסדר העמודות שנקבע על ידי הפונקציה CHOOSE.
הפונקציה CHOOSE יוצרת מערך טבלאות בעל שתי עמודות רחבות עם עמודה F ואחריהן עמודה ד '. מאחר והמידע המבוקש - שם החלק - נמצא בעמודה D, יש להגדיר את הערך של ארגומנט אינדקס העמודה ל -2.
- הקלק על ה Col_index_num בשורה בתיבת הדו-שיח.
- סוג א 2 בשורה זו.
בדיקת טווח
הארגומנט Range_lookup של VLOOKUP הוא ערך לוגי (TRUE או FALSE בלבד) המציין אם ברצונך ש- VLOOKUP ימצא התאמה מדויקת או משוערת לערך החיפוש.
- אם TRUE או אם ארגומנט זה מושמט, VLOOKUP מחזירה התאמה מדויקת ל- Lookup_value, או אם התאמה מדויקת לא נמצאה, מחזירה VLOOKUP את הערך הבא בגודלו. עבור הנוסחה לעשות זאת, הנתונים בעמודה הראשונה של Table_array חייב להיות מסודר בסדר עולה.
- אם FALSE, VLOOKUP משתמש רק בהתאמה מדויקת ל Lookup_value. אם קיימים שני ערכים או יותר בעמודה הראשונה של Table_array שתואמת את ערך החיפוש, הערך הראשון שנמצא נמצא בשימוש. אם לא נמצאה התאמה מדויקת, תוצג שגיאה # N / A.
במדריך זה, מכיוון שאנו מחפשים שם חלק מסוים, Range_lookup מוגדר שקר כך שרק התאמות מדויקות מוחזרות על ידי הנוסחה.
- הקלק על ה Range_lookup בשורה בתיבת הדו-שיח.
- הקלד את המילה שקר בשורה זו כדי לציין שאנו רוצים ש- VLOOKUP יחזיר התאמה מדויקת לנתונים שאנו מחפשים.
- לחץ על בסדר כדי להשלים את נוסחת החיפוש השמאלית וסגירת תיבת הדו שיח.
- כי עדיין לא נכנסנו שם החברה לתא D2, שגיאה # N / A מופיע בתא E2.
נתונים מחזירים עם נוסחת בדיקת השמאל
כדי למצוא אילו חברות לספק אילו חלקים, הקלד את שם החברה לתא D2 ולחץ על להיכנס מפתח במקלדת.
שם החלק מוצג בתא E2.
- לחץ על התא D2 בגליון העבודה שלך.
- הקלד גאדג 'טים פלוס לת D2 ולחץ על להיכנס מפתח במקלדת.
- הטקסט "גאדג'טים" - החלק שסופק על ידי החברה גאדג 'טים פלוס - צריך להיות מוצג בתא E2.
בדוק את נוסחת חיפוש נוסף על ידי הקלדת שמות החברה אחרים לתוך D2 התא, ואת שם החלק המקביל צריך להופיע בתא E2.
אם מופיעה הודעת שגיאה כגון # N / A בתא E2, בדוק שגיאות כתיב בתא D2.
04 מתוך 05יצירת מערך טורים דו-טוריים
התחביר עבור הפונקציה CHOOSE הוא:
= CHOOSE (Index_number, Value1, Value2, … Value254)
הפונקציה CHOOSE מחזירה בדרך כלל ערך אחד מרשימת הערכים (Value1 to Value254) בהתבסס על מספר האינדקס שהוזן.
אם מספר האינדקס הוא 1, הפונקציה מחזירה את Value1 מהרשימה; אם מספר המדד הוא 2, הפונקציה מחזירה Value2 מהרשימה וכן הלאה.
כאשר מוזנים מספר אינדקסים מרובים, הפונקציה מחזירה ערכים מרובים בכל הסדר הרצוי. איך לבחור לחזור ערכים מרובים נעשה על ידי יצירת מערך.
כדי להיכנס למערך, הקף את המספרים שהוזנו עם הפלדה המסולסלת או בסוגריים. שני מספרים מוזנים עבור מספר האינדקס:{ 1,2 }.
יש לציין כי CHOOSE אינו מוגבל ליצירת טבלת שתי עמודות. על ידי הוספת מספר נוסף במערך - כגון {1,2,3} - וטווח נוסף בארגומנט הערך, CHOOSE יוצר טבלת שלוש עמודות.
עמודות נוספות מאפשרות לך להחזיר מידע אחר עם נוסחת החיפוש השמאלית על ידי שינוי הארגומנט של מספר העמודה של VLOOKUP למספר העמודה שמכילה את המידע הרצוי.
05 מתוך 05שינוי סדר העמודים עם בחירת פונקציה
בפונקציה CHOOSE המשמשת בנוסחה זו:
CHOOSE ({1,2}, $ F: $ F, $ D: $ D)
הטווח עבור טור F מופיע לפני העמודה D.
מכיוון שהפונקציה CHOOSE קובעת את מערך הטבלה של VLOOKUP (מקור הנתונים עבור אותה פונקציה), החלפת סדר העמודים בפונקציה CHOOSE מועברת ל- VLOOKUP.
מבחינת VLOOKUP, מערך הטבלה הוא רק שני עמודות רחב עם עמודה F בצד שמאל ועמוד D בצד ימין. מכיוון שעמודה F מכילה את שם החברה שאנו רוצים לחפש, ומאחר שהעמודה D מכילה את שמות החלקים, VLOOKUP יכול לבצע את משימות החיפוש הרגילות שלו במציאת נתונים הממוקמים משמאל לערך החיפוש.
כתוצאה מכך, VLOOKUP יכול להשתמש בשם החברה כדי למצוא את החלק שהם מספקים.
