משמאל בדיקת פורמולה באמצעות

01 מתוך 03

מצא נתונים לשמאל

Excel סקירה סקירה פורמולה

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

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

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

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

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

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

הנוסחה

= V $ $, $ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

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

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

שלבי הדרכה - הזנת נתוני המדריך

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

פתיחת פורמט בדיקת השמאל - פתיחת תיבת הדו-שיח VLOOKUP

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

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

שלבי הדרכה

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

02 מתוך 03

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

הטענות של VLOOKUP

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

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

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

ערך בדיקת

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

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

שלבי הדרכה

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

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

מערך הטבלה: כניסה לתפריט CHOOSE

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

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

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

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

פרטים על אופן הפונקציה CHOOSE משיגה את המשימות הללו ניתן למצוא בעמוד 3 של המדריך .

שלבי הדרכה

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

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

מספר אינדקס העמודה

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

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

שלבי הדרכה

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

בדיקת טווח

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

• אם TRUE או אם ארגומנט זה מושמט, מחזירה VLOOKUP התאמה מדויקת ל- Lookup_value, או, אם לא נמצאה התאמה מדויקת, מחזירה 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 מתוך 03

בדיקת הנוסחה בדיקת השמאל

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

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

שם החלק יופיע בתא E2.

שלבי הדרכה

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

הודעות שגיאה של VLOOKUP

אם הודעת שגיאה כגון # N / A מופיעה בתא E2, בדוק תחילה שגיאות כתיב בתא D2.

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

לשבור את התפקיד של CHOOSE עבודה

כאמור, בנוסחה זו, הפונקציה CHOOSE כוללת שתי משרות:

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

יצירת שני טורים

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

= CHOOSE (Index_number, Value1, Value2, ... Value254)

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

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

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

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

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

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

שינוי סדר העמודים עם פונקציית CHOOSE

בפונקציה CHOOSE המשמשת בנוסחה זו: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , טווח הטור F מופיע לפני העמודה D.

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

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

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