מצא שדות מרובים של נתונים עם Excel VLOOKUP

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

01 מתוך 10

החזרת מספר ערכים עם Excel VLOOKUP

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

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

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

02 מתוך 10

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

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

• הזן את טווח הנתונים העליון תאים D1 ל G1.
• הזן את הטווח השני לתוך תאים D4 ל G10.

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

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

03 מתוך 10

יצירת טווח בשם טבלת הנתונים

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

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

שם הטווח אינו כולל את הכותרות או שמות השדות של הנתונים (שורה 4) אלא רק את הנתונים עצמם.

1. הדגש תאים D5 ל G10 בגליון העבודה לבחירתם.
2. הקלק על ה שם התיבה הממוקם לעיל עמודה א.
3. הקלד שולחן לתוך ה שם התיבה.
4. לחץ על להיכנס מפתח במקלדת.
5. תאים D5 ל G10 עכשיו יש שם טווח של שולחן. אנו נשתמש בשם עבור VLOOKUP מערך טבלאות ארגומנט מאוחר יותר במדריך.

04 מתוך 10

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

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

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

שלבי הדרכה

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

05 מתוך 10

הזנת ארגומנט ערך בדיקה

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

הפניות מוחלטים Cell

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

הפניות מוחלטות של התא אינן משתנות כאשר נוסחאות מועתקות.

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

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

06 מתוך 10

הזנת ארגומנט מערך הטבלה

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

העמודה הראשונה מכילה את ארגומנט ערך החיפוש (השלב ​​הקודם במדריך), ואילו העמודה השנייה תיבדק על ידי נוסחת החיפוש כדי למצוא את המידע שאנו מציינים.

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

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

07 מתוך 10

קינון הפונקציה COLUMN

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

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

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

פונקציות קינון

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

הזנת פונקציה COLUMN ידנית

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

בחירת ארגומנט הפונקציה של פונקציית COLUMN

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

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

1. בתוך ה VLOOKUP תיבת הדו שיח פונקציה, לחץ על Col_index_num קו.
2. הקלד את שם הפונקציה טור ואחריו סוגר פתוח.
3. לחץ על תא B1 בגליון העבודה להזין את הפניה התא כמו התייחסות ויכוח.
4. סוג א סוגר סוגר עגול כדי להשלים את טור פונקציה
5. עזוב את ה VLOOKUP תיבת הדו-שיח פונקציה פתוחה עבור השלב הבא במדריך.

08 מתוך 10

הזנת טווח VLOOKUP בדיקת ארגומנט

VLOOKUP's Range_lookup הטיעון הוא ערך לוגי (אמת או FALSE) המציין אם אתה רוצה VLOOKUP כדי למצוא התאמה מדויקת או משוערת ל Lookup_value.

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

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

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

09 מתוך 10

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

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

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

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

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

כמו כן, Excel אינו משנה את התייחסות התא המוחלט $ D $ 2 ואת טווח שם שולחן כמו הנוסחה מועתקת.

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

1. לחץ על תא E2, שם נוסחת החיפוש נמצא, כדי להפוך אותו לתא פעיל.
2. מניחים את מצביע העכבר מעבר ל ריבוע שחור בפינה הימנית התחתונה - המצביע ישתנה לסימן פלוס כדי לציין את ידית המילוי.
3. לחץ על לחצן העכבר השמאלי ו גרור את ידית המילוי במקביל ל תא G2.
4. שחרר כפתור העכבר תא F3 צריך להכיל את הנוסחה דו מימדי בדיקת.
5. אם נעשה כראוי, תאים F2 ו G2 עכשיו צריך גם להכיל את # N / A השגיאה קיימת תא E2.

10 מתוך 10

הזנת קריטריונים לחיפוש

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

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

1. לחץ על תא D2 בגליון העבודה.
2. הקלד יישומון לתוך תא D2 ולחץ על להיכנס מפתח במקלדת.
3. יש להציג את המידע הבא תאים E2 ל G2.
   1. E2: $ 14.76 - המחיר של יישומון
   2. F2: PN-98769 - מספר החלק עבור רכיב widget
   3. G2: ווידג'טים בע"מ - שם הספק עבור יישומונים
4. בדוק את VLOOKUP נוסחת מערך נוסף על ידי הקלדת שם של חלקים אחרים לתוך תא D2 ותצפית על התוצאות תאים E2 ל G2