על ידי שילוב של פונקציית VLOOKUP של Excel עם הפונקציה COLUMN אתה יכול ליצור נוסחת חיפוש שמחזירה מספר ערכים משורה אחת של מסד נתונים או טבלת נתונים. למד כיצד ליצור נוסחת חיפוש שמחזירה מספר ערכים מרשומת נתונים אחת.
ההוראות במאמר זה חלות על Excel 2019, 2016, 2013, 2010; ו-Excel עבור Microsoft 365.
שורה התחתונה
נוסחת החיפוש מחייבת את הפונקציה COLUMN להיות מקוננת בתוך VLOOKUP. קינון פונקציה כרוך בהזנת הפונקציה השנייה כאחד הארגומנטים של הפונקציה הראשונה.
הזן את נתוני ההדרכה
במדריך זה, הפונקציה COLUMN מוזנת כארגומנט מספר אינדקס העמודה עבור VLOOKUP. השלב האחרון במדריך כולל העתקת נוסחת החיפוש לעמודות נוספות כדי לאחזר ערכים נוספים עבור החלק הנבחר.
השלב הראשון במדריך זה הוא להזין את הנתונים לגיליון עבודה של Excel. על מנת לבצע את השלבים במדריך זה, הזן את הנתונים המוצגים בתמונה למטה לתוך התאים הבאים:
- הזן את טווח הנתונים העליון לתאים D1 עד G1.
- הזן את הטווח השני לתאים D4 עד G10.
קריטריוני החיפוש ונוסחת החיפוש שנוצרו במדריך זה מוזנים בשורה 2 של גליון העבודה.
מדריך זה אינו כולל את העיצוב הבסיסי של Excel המוצג בתמונה, אך זה אינו משפיע על אופן הפעולה של נוסחת החיפוש.
צור טווח עם שם לטבלת הנתונים
טווח עם שם הוא דרך קלה להתייחס לטווח של נתונים בנוסחה. במקום להקליד את הפניות התא לנתונים, הקלד את שם הטווח.
יתרון שני בשימוש בטווח בעל שם הוא שההפניות לתאים עבור טווח זה לעולם לא משתנות גם כאשר הנוסחה מועתקת לתאים אחרים בגליון העבודה. שמות טווחים הם חלופה לשימוש בהפניות תא מוחלטות כדי למנוע שגיאות בעת העתקת נוסחאות.
שם הטווח אינו כולל את הכותרות או שמות השדות עבור הנתונים (כפי שמוצג בשורה 4), רק את הנתונים.
-
הדגש תאים D5 ל- G10 בגיליון העבודה.
Image -
מקם את הסמן בתיבת השם שממוקמת מעל לעמודה A, הקלד Table ולאחר מכן הקש Enter. לתאים D5 עד G10 יש את שם הטווח של Table.
Image - שם הטווח עבור ארגומנט מערך הטבלה VLOOKUP משמש מאוחר יותר במדריך זה.
פתח את תיבת הדו-שיח VLOOKUP
למרות שניתן להקליד את נוסחת החיפוש ישירות לתוך תא בגליון עבודה, אנשים רבים מתקשים לשמור על התחביר ישר - במיוחד עבור נוסחה מורכבת כמו זו המשמשת במדריך זה.
כחלופה, השתמש בתיבת הדו-שיח VLOOKUP Function Arguments. כמעט לכל הפונקציות של Excel יש תיבת דו-שיח שבה כל אחד מהארגומנטים של הפונקציה מוזן בשורה נפרדת.
-
בחר תא E2 של גליון העבודה. זהו המיקום שבו יוצגו התוצאות של נוסחת הבדיקה הדו-ממדית.
Image -
בסרט, עבור לכרטיסייה Formulas ובחר חיפוש והפניה.
Image -
בחר VLOOKUP כדי לפתוח את תיבת הדו-שיח Function Arguments.
Image - תיבת הדו-שיח Function Arguments היא המקום שבו מוזנים הפרמטרים של הפונקציה VLOOKUP.
הזן את ארגומנט ערך החיפוש
בדרך כלל, ערך החיפוש תואם לשדה נתונים בעמודה הראשונה של טבלת הנתונים. בדוגמה זו, ערך החיפוש מתייחס לשם של החלק שברצונך למצוא מידע. סוגי הנתונים המותרים עבור ערך החיפוש הם נתוני טקסט, ערכים לוגיים, מספרים והפניות לתאים.
הפניות תא אבסולוטיות
כאשר נוסחאות מועתקות ב-Excel, הפניות לתאים משתנות כדי לשקף את המיקום החדש. אם זה קורה, D2, הפניה לתא עבור ערך הבדיקה, משתנה ויוצר שגיאות בתאים F2 ו-G2.
הפניות אבסולוטיות לתאים אינן משתנות בעת העתקת נוסחאות.
כדי למנוע את השגיאות, המר את הפניה לתא D2 להפניה מוחלטת לתא. כדי ליצור הפניה מוחלטת לתא, הקש על מקש F4. זה מוסיף סימני דולרים סביב הפניה לתא, כגון $D$2.
-
בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט lookup_value. לאחר מכן, בגליון העבודה, בחר תא D2 כדי להוסיף הפניה לתא זה ל- lookup_value. תא D2 הוא המקום שבו יוזן שם החלק.
Image -
מבלי להזיז את נקודת ההכנסה, הקש על המקש F4 כדי להמיר D2 להפניה המוחלטת לתא $D$2.
Image - השאירו את תיבת הדו-שיח של פונקציית VLOOKUP פתוחה לשלב הבא במדריך.
הזן את ארגומנט מערך הטבלה
מערך טבלה הוא טבלת הנתונים שנוסחת החיפוש מחפשת כדי למצוא את המידע הרצוי. מערך הטבלה חייב להכיל לפחות שתי עמודות של נתונים.
העמודה הראשונה מכילה את ארגומנט ה-lookup value (שהוגדר בסעיף הקודם), בעוד שהעמודה השנייה מבוצעת על ידי נוסחת בדיקת המידע כדי למצוא את המידע שציינת.
יש להזין את הארגומנט של מערך הטבלה כטווח המכיל את הפניות התא עבור טבלת הנתונים או כשם טווח.
כדי להוסיף את טבלת הנתונים לפונקציית VLOOKUP, מקם את הסמן בתיבת הטקסט table_array בתיבת הדו-שיח והקלד Tableכדי להזין את שם הטווח עבור ארגומנט זה.
קן את הפונקציה COLUMN
בדרך כלל, VLOOKUP מחזיר רק נתונים מעמודה אחת של טבלת נתונים.עמודה זו נקבעת על ידי ארגומנט מספר אינדקס העמודה. עם זאת, בדוגמה זו יש שלוש עמודות, ויש לשנות את מספר אינדקס העמודה מבלי לערוך את נוסחת החיפוש. כדי להשיג זאת, קן את הפונקציה COLUMN בתוך הפונקציה VLOOKUP כארגומנט Col_index_num.
בעת קינון של פונקציות, Excel לא פותח את תיבת הדו-שיח של הפונקציה השנייה כדי להזין את הארגומנטים שלה. יש להזין את הפונקציה COLUMN באופן ידני. לפונקציה COLUMN יש רק ארגומנט אחד, ארגומנט Reference, שהוא הפניה לתא.
הפונקציה COLUMN מחזירה את מספר העמודה שסופקה כארגומנט Reference. זה ממיר את אות העמודה למספר.
כדי למצוא את המחיר של פריט, השתמש בנתונים בעמודה 2 של טבלת הנתונים. דוגמה זו משתמשת בעמודה B בתור הפניה להוספת 2 לארגומנט Col_index_num.
-
בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט Col_index_num והקלד COLUMN(. (הקפידו לכלול את הסוגר העגול הפתוחה.)
Image -
בגליון העבודה, בחר תא B1 כדי להזין את ההפניה לתא כארגומנט Reference.
Image - הקלד סוגר עגול סוגר כדי להשלים את הפונקציה COLUMN.
הזן את ארגומנט בדיקת טווח VLOOKUP
VLOOKUP של ארגומנט Range_lookup הוא ערך לוגי (TRUE או FALSE) המציין אם VLOOKUP צריך למצוא התאמה מדויקת או משוערת ל- Lookup_value.
- TRUE או הושמט: VLOOKUP מחזירה התאמה קרובה ל- Lookup_value. אם לא נמצא התאמה מדויקת, VLOOKUP מחזיר את הערך הבא בגודלו. יש למיין את הנתונים בעמודה הראשונה של Table_array בסדר עולה.
- FALSE: VLOOKUP משתמש בהתאמה מדויקת ל- Lookup_value. אם יש שני ערכים או יותר בעמודה הראשונה של Table_array התואמים את ערך הבדיקה, נעשה שימוש בערך הראשון שנמצא. אם לא נמצא התאמה מדויקת, מוחזרת שגיאת N/A.
במדריך זה, מידע ספציפי על פריט חומרה מסוים ייבדק, אז Range_lookup מוגדר ל-FALSE.
בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט Range_lookup והקלד False כדי לומר ל-VLOOKUP להחזיר התאמה מדויקת לנתונים.
בחר OK כדי להשלים את נוסחת החיפוש ולסגור את תיבת הדו-שיח. תא E2 יכיל שגיאת N/A מכיוון שקריטריוני הבדיקה לא הוזנו לתא D2. שגיאה זו היא זמנית. זה יתוקן כאשר קריטריוני החיפוש יתווסף בשלב האחרון של הדרכה זו.
העתק את נוסחת החיפוש והזן קריטריונים
נוסחת החיפוש מאחזרת נתונים ממספר עמודות של טבלת הנתונים בו-זמנית. לשם כך, נוסחת החיפוש חייבת להימצא בכל השדות שמהם אתה רוצה מידע.
כדי לאחזר נתונים מהעמודות 2, 3 ו-4 של טבלת הנתונים (המחיר, מספר החלק ושם הספק), הזן שם חלקי בתור Lookup_value.
מכיוון שהנתונים מונחים בתבנית קבועה בגליון העבודה, העתק את נוסחת החיפוש בתא E2 ל- תאים F2 ו G2 עם העתקת הנוסחה, Excel מעדכן את הפניה לתא היחסית בפונקציה COLUMN (תא B1) כדי לשקף את המיקום החדש של הנוסחה. Excel לא משנה את הפניה מוחלטת לתא (כגון $D$2) ואת הטווח בעל השם (טבלה) כאשר הנוסחה מועתקת.
יש יותר מדרך אחת להעתיק נתונים ב-Excel, אבל הדרך הקלה ביותר היא להשתמש ב-Fill Handle.
-
בחר תא E2, היכן ממוקמת נוסחת החיפוש, כדי להפוך אותה לתא הפעיל.
Image -
גרור את ידית המילוי אל תא G2. התאים F2 ו-G2 מציגים את השגיאה N/A שקיימת בתא E2.
Image -
כדי להשתמש בנוסחאות החיפוש כדי לאחזר מידע מטבלת הנתונים, בגליון העבודה בחר תא D2, הקלד Widget, והקש Enter.
Image המידע הבא מוצג בתאים E2 עד G2.
- E2: $14.76 - המחיר של יישומון
- F2: PN-98769 - מספר החלק של יישומון
- G2: Widgets Inc. - שם הספק ליישומונים
-
כדי לבדוק את נוסחת המערך VLOOKUP, הקלד את השם של חלקים אחרים בתא D2 וצפה בתוצאות בתאים E2 עד G2.
Image - כל תא המכיל את נוסחת החיפוש מכיל נתון אחר על פריט החומרה שחיפשת.
פונקציית VLOOKUP עם פונקציות מקוננות כמו COLUMN מספקת שיטה רבת עוצמה לחפש נתונים בתוך טבלה, תוך שימוש בנתונים אחרים כהפניה לחיפוש.
