עיקרי תוכנה כיצד ליצור נוסחת חיפוש שמאל של Excel באמצעות VLOOKUP
תוכנה

כיצד ליצור נוסחת חיפוש שמאל של Excel באמצעות VLOOKUP

כיצד ליצור נוסחת חיפוש שמאל של Excel באמצעות VLOOKUP
Anonim

שלב בין VLOOKUP ו- CHOOSE של Excel ליצירת נוסחת חיפוש שמאל

  • מסמכים
  • מצגות
  • הוצאת שולחן עבודה
  • עיצוב גרפי
  • מאגרי מידע
  • אנימציה ווידאו
  • byTed צרפתית

    מומחה לגליונות אלקטרוניים שהוא אמן ב- Excel ו- Google Sheets.

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

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

    עם זאת, על ידי שילוב של VLOOKUP עם הפונקציה CHOOSE, ניתן ליצור נוסחת חיפוש שמאלית ש:

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

    הוראות אלה חלות על גרסאות Excel של 2019, 2016, 2013, 2010 ו- Excel עבור Office 365.

    01

    של 05

    הדרכה: שימוש בפונקציות VLOOKUP ו- CHOOSE בנוסחת בדיקת שמאל

    ליצירת הנוסחה השמאלית לחיפוש שנראה בתמונה לדוגמה, השתמש בנוסחה:

    = VLOOKUP ($ D $ 2, בחר ({1, 2}, $ F: $ F, $ D: $ D), 2, FALSE)

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

    תפקיד הפונקציה CHOOSE בנוסחה הוא להערים על VLOOKUP להאמין שעמודה 3 היא עמודה 1. כתוצאה מכך, ניתן להשתמש בשם החברה כערך החיפוש כדי למצוא את שם החלק שסופק על ידי כל חברה.

    הזן את נתוני ההדרכה

    1. הזן את הכותרת ספק לתא D1 .
    2. הזן את הכותרת חלק בתא E1 .
    3. הזן את טבלת הנתונים שנראתה בתמונה למעלה לתאים D4 עד F9
    4. שורות 2 ו -3 נשארות ריקות כדי להתאים לקריטריוני החיפוש ונוסחת החיפוש השמאלית שנוצרה במהלך הדרכה זו

    פתח את תיבת הדו-שיח VLOOKUP

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

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

    1. לחץ על תא E2 בגליון. E2 הוא המיקום בו יוצגו תוצאות הנוסחה לחיפוש שמאלי.
    2. לחץ על לשונית הנוסחאות של הסרט.
    3. לחץ על האפשרות Lookup & Reference ברצועת הכלים כדי לפתוח את הרשימה הנפתחת של הפונקציה.
    4. לחץ על VLOOKUP ברשימה כדי להעלות את תיבת הדו-שיח של הפונקציה.

    02

    של 05

    הזנת טיעונים לתיבת הדו-שיח VLOOKUP

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

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

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

    ערך ה- Lookup

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

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

    1. לחץ על שורת ה- lookup_value בתיבת הדו-שיח.
    2. לחץ על תא D2 כדי להוסיף הפניה לתא זה לשורת lookup_value .
    3. לחץ על מקש F4 במקלדת כדי להפוך את התייחסות התא למוחלטת - $ D $ 2.

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

    כניסה לפונקציית הבחירה

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

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

    בנוסחה זו, פונקציית ה- CHOOSE מבצעת שתי משימות:

    1. זה יוצר מערך טבלה ברוחב של שתי עמודות בלבד (עמודות D ו- F).
    2. זה משנה את הסדר מימין לשמאל של העמודות במערך הטבלה כך שעמודה F מגיעה ראשונה ועמודה D השנייה.

    פונקציות כניסה

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

    1. בתיבת הדו-שיח של פונקצית VLOOKUP, לחץ על השורה Table_array .
    2. הזן את הפונקציה הבאה בחר : בחר ({1, 2}, $ F: $ F, $ D: $ D)

    מספר אינדקס טור

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

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

    1. לחץ על קו Col_index_num בתיבת הדו-שיח.
    2. הקלד 2 בשורה זו.

    בדיקת טווח

    טיעון Range_lookup של VLOOKUP הוא ערך הגיוני (TRUE או FALSE בלבד) המציין אם אתה רוצה ש- VLOOKUP ימצא התאמה מדויקת או התאמה לערך האיתור.

    • אם TRUE או אם טיעון זה מושמט, VLOOKUP מחזיר התאמה מדויקת לחיפוש_ערך או, אם לא נמצא התאמה מדויקת, VLOOKUP מחזיר את הערך הבא הגדול ביותר. על מנת שנוסחה תעשה זאת, יש למיין את הנתונים בעמודה הראשונה של Table_array בסדר עולה.
    • אם FALSE, VLOOKUP משתמש רק בהתאמה מדויקת ל- Lookup_value. אם יש שני ערכים או יותר בעמודה הראשונה של Table_array התואמים לערך האיתור, נעשה שימוש בערך הראשון שנמצא. אם לא נמצא התאמה מדויקת, שגיאת # N / A מוחזרת.

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

    1. לחץ על השורה Range_lookup בתיבת הדו-שיח.
    2. הקלד את המילה False בשורה זו כדי לציין שאנחנו רוצים ש- VLOOKUP יחזיר התאמה מדויקת לנתונים שאנו מחפשים.
    3. לחץ על אישור כדי להשלים את הנוסחה לחיפוש שמאלי ולסגירת תיבת הדו-שיח.
    4. מכיוון שעדיין לא הזנו את שם החברה לתא D2, מופיעה שגיאה מס 'N / A בתא E2.

    03

    של 05

    החזרת נתונים עם הנוסחה לחיפוש שמאלי

    כדי למצוא אילו חברות מספקות אילו חלקים, הקלד שם חברה לתא D2 ולחץ על מקש ENTER במקלדת.

    שם החלק מוצג בתא E2.

    1. לחץ על תא D2 בגליון העבודה שלך.
    2. הקלד גאדג'טים פלוס בתא D2 ולחץ על מקש ENTER במקלדת.
    3. יש להציג את הטקסט "גאדג'טים" - החלק המסופק על ידי חברת גאדג'טים פלוס - בתא E2.

    בדוק את נוסחת האיתור עוד יותר על ידי הקלדת שמות חברות אחרים בתא D2, ושם החלק המתאים אמור להופיע בתא E2.

    אם מופיעה הודעת שגיאה כמו מספר N / A בתא E2, בדוק אם יש שגיאות כתיב בתא D2.

    04

    של 05

    יצירת מערך טבלה עם שתי עמודות

    התחביר לפונקציה בחר הוא:

    = בחר (אינדקס_מספר, ערך 1, ערך 2, … ערך 254)

    פונקציית הבחירה מחזירה בדרך כלל ערך אחד מרשימת הערכים (Value1 ל- Value254) בהתבסס על מספר האינדקס שהוזן.

    אם מספר האינדקס הוא 1, הפונקציה מחזירה את Value1 מהרשימה; אם מספר האינדקס הוא 2, הפונקציה מחזירה את Value2 מהרשימה וכן הלאה.

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

    כדי להזין מערך, הקף את המספרים שהוזנו בסוגריים או סוגריים מתולתלים. שני מספרים מוזנים עבור מספר האינדקס: {1, 2} .

    יש לציין כי CHOOSE אינו מוגבל ליצירת טבלה בעלת שתי עמודות. על ידי הכללת מספר נוסף במערך - כגון {1, 2, 3} - וטווח נוסף בארגומנט הערך, CHOOSE יוצר טבלת שלוש עמודות.

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

    05

    של 05

    שינוי סדר העמודות בפונקציה בחר

    בפונקציה בחר שבנוסחה זו:

    בחר ({1, 2}, $ F: $ F, $ D: $ D)

    הטווח עבור עמודה F מופיע לפני עמודה D.

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

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

    כתוצאה מכך, VLOOKUP יכול להשתמש בשם החברה כדי למצוא את החלק שהם מספקים.