כיצד לבצע VLOOKUP באקסל עם דוגמאות וטריקים מתקדמים

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

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

מהי פונקציית VLOOKUP באקסל?

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

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

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

כיצד פועלת נוסחת VLOOKUP?

נוסחת VLOOKUP בנויה כך:

=BUSCARV(valor_búsqueda; intervalo; índice_columna; )

לכל אלמנט בפונקציה יש פונקציה ספציפית:

• ערך_חיפושאלו הנתונים שברצונך לחפש, בדרך כלל תא המכיל את המפתח הייחודי, לדוגמה קוד מוצר או שם של אדם.
• מרווחטווח התאים שבו נמצאים הנתונים. העמודה שבה נמצאים הנתונים ערך_חיפוש חייבת תמיד להיות העמודה הראשונה בטווח זה.
• אינדקס_עמודהזהו מספר העמודה, בטווח, שממנו ברצונך להחזיר את התוצאה. זכור שעמודת החיפוש היא מספר 1.
• : זה אופציונלי. ציין אם ברצונך התאמה מדויקת (מְזוּיָף או 0) או משוער אחד (אמיתי או 1). כברירת מחדל, זוהי התאמה מקורבת, אך בפועל, כמעט תמיד תרצו להשתמש בהתאמה מדויקת.

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

=BUSCARV("Manzana"; A2:C10; 3; FALSO)

בעזרת נוסחה זו, Excel יחפש את "Apple" בעמודה הראשונה של הטווח A2:C10. אם הוא ימצא אותו, הוא יחזיר את הערך בעמודה השלישית של שורה זו (לדוגמה, המחיר).

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

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

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

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

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

שלב אחר שלב ליצירת נוסחת VLOOKUP

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

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

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

1. בגיליון הזמנות רכיבים, נווטו לתא שבו ברצונכם ששם הספק יופיע.
2. לחץ על "=" כדי להתחיל להקליד את הנוסחה.
3. כתוב VLOOKUP( o חיפוש (VLOUP( אם האקסל שלך באנגלית.
4. בחר את התא עם שם המרכיב שברצונך לחפש (לדוגמה, B5).
5. הקלד פסיק ובחר את הטווח שבו נמצאים נתוני הספק (לדוגמה, הטבלה בגיליון רשימת ספקים, מ-A3 עד G13).
6. לחץ על F4 כדי להפוך את הטווח להפניה מוחלטת (סימני ה-$ יופיעו).
7. כתבו פסיק, ציינו את מספר העמודה המכילה את הנתונים שברצונכם להביא (לדוגמה, שם הספק נמצא בעמודה 2, מספר הטלפון בעמודה 7, תאריך האספקה ​​בעמודה 5...)
8. לבסוף, כתבו מְזוּיָף כדי לחפש התאמות מדויקות בלבד ולסגור את הסוגריים.

הנוסחה הסופית שלך לשם הספק עשויה להיראות כך: =BUSCARV(B5,'Lista de Proveedores'!$A$3:$G$13,2,FALSO)

עבור הטלפון, פשוט שנה את מספר העמודה (למשל, 7), ועבור יום המסירה, הזן את האינדקס המתאים.

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

פרטים מרכזיים על תחביר וארגומנטים של VLOOKUP

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

• ערך לחפש: זה יכול להיות טקסט, מספר, הפניה לתא אחר... הדבר החשוב הוא שזה אוניקו בעמודה הראשונה של הטווח. דוגמה: "102" או B5.
• טווח חיפוש: כלול את העמודה עם הנתונים שברצונך לחפש ואת כל העמודות שמהן ברצונך לחלץ מידע. דוגמה: A2:D10.
• אינדקס עמודות: זהו מספר שלם ותמיד מתחיל לספור מהעמודה השמאלית ביותר של הטווח (1 = עמודת חיפוש, 2 = הבא וכו'). לא יכול להיות קטן מ-1 או גדול ממספר העמודות בטווח.
• התאמה מדויקת או משוערת: מומלץ תמיד להגדיר FALSE כדי להימנע מהפתעות. השתמשו ב-TRUE רק אם עמודת החיפוש שלכם ממוינת ואתם מחפשים טווחים.

וב-Google Sheets? כל מה שכתוב למעלה רלוונטי ב-100%, למרות שב-Sheets הארגומנטים לפעמים יש שינויים קלים, כמו למשל is_sorted.

דוגמאות שימושיות מאוד של VLOOKUP

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

• חיפוש טקסט: =BUSCARV("Manzana";B4:D8;3;FALSO) → מחזיר את מחיר התפוח
• חיפוש לפי הפניה לתא:=BUSCARV(G9;B4:D8;3;FALSO) מצא את הערך של G9 ברשימה
• חיפוש לפי התאמה משוערת: =BUSCARV(102;A4:D8;2;VERDADERO) אם 102 לא קיים, הוא נותן לך את הערך הקרוב ביותר שקטן מ-102
• עם אינדקס עמודה משתנה: =BUSCARV(G3;B4:D8;2;FALSO) מצא את הכמות לפי הערך של G3
• שילוב קריטריונים (בגיליונות גוגל): אם עליך לחפש לפי שם פרטי ושם משפחה, תוכל ליצור עמודת עזר המקשרת בין השניים ולהשתמש בה כמפתח ייחודי.

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

שגיאות נפוצות וכיצד לפתור אותן

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

• נתונים כפולים: אם יש לך מספר רשומות עם אותו מפתח, רק הראשונה תוצג. הסר כפילויות מעמודת החיפוש שלך כדי למנוע בלבול.
• רווחים מובילים/נגררים: אם ישנם רווחים בלתי נראים לפני או אחרי הנתונים שלך, Excel לא יחזיר התאמה. השתמש בפונקציה SPACES כדי לנקות את הנתונים שלך.
• הפניה שגויה לטבלה: אם העתקת הנוסחה מזיזה את הטווח, החיפוש ייכשל. פתרון: השתמשו בהפניות מוחלטות (עם $).
• אינדקס העמודה מחוץ לטווח: אם תזין מספר גדול מהעמודות שמהן בחרת, תופיע השגיאה ‎#REF!‎.
• סדר שגוי: אם תשתמשו בהתאמה מקורבת מבלי למיין את עמודת החיפוש מהנמוך לגבוה, ייתכן שתקבלו תוצאות שגויות. ציינו תמיד FALSE אלא אם כן אתם בטוחים במה שאתם עושים.

ניתן להתאים אישית את שגיאת #N/A על ידי שילוב VLOOKUP עם IFNA:

• =SI.ERROR(BUSCARV(...), "No encontrado") בהצטיינות
• =SI.ND(BUSCARV(...), "No encontrado") ב-Google Sheets

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

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

1. השתמשו בהפניות מוחלטות בטווח
בכל פעם שאתם מעתיקים נוסחאות למטה, ודאו שטווח החיפוש לא משתנה. לכן, לאחר בחירת הטווח, לחצו על F4 כדי להזין את הסימן $. פעולה זו מבטיחה ש-Excel/Sheets לא ישנה אותו בעת העתקת הנוסחה.

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

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

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

5. השתמשו בתווים כלליים (wildcards) עבור התאמות חלקיות
אם ברצונך לחפש שמות שמתחילים באותו שם אך אינך יודע כיצד הם מסתיימים, תוכל להשתמש בכוכביות (*) או בסימני שאלה (?) בערך החיפוש שלך באמצעות VLOOKUP, תוך שימוש תמיד בהתאמה מדויקת (FALSE). דוגמה: BUSCARV("La*";...; FALSO) יחזיר את הנתונים הראשונים שמתחילים ב-"La".

6. VLOOKUP בגיליונות או ספרים שונים
ניתן לחפש בטבלאות הממוקמות בכרטיסייה אחרת (גיליון) או אפילו בקובץ אקסל אחר. פשוט ציינו את שם הגיליון והטווח כך: 'Hoja2'!A1:F20עבור חוברות עבודה אחרות, פתח אותן תחילה ובחר את הטווח; Excel יוסיף את הנתיב באופן אוטומטי.

VLOOKUP ב-Google Sheets: קווי דמיון ושוני

אם אתם משתמשים ב-Google Sheets, הפונקציה VLOOKUP פועלת כמעט באותו אופן כמו באקסל, אם כי ישנם שינויים קלים בשמות הארגומנטים :

• ערך_חיפוש: מה שאתה רוצה לחפש.
• הַפסָקָה: הטווח לחיפוש ולהבאת התוצאה.
• מַדָד: העמודה שאליה יש להביא את הנתונים, בתוך המרווח (1 היא העמודה הראשונה בטווח שנבחר).
• ממוין: בין אם אתם מחפשים התאמה מדויקת (FALSE) או התאמה מקורבת (TRUE).

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

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

מגבלות וחלופות ל-VLOOKUP

בעוד ש-VLOOKUP הוא כלי רב עוצמה, יש לו כמה מגבלות :

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

בגרסאות הנוכחיות של Excel, ניתן להשתמש בפונקציה XLOOKUP , אשר פותרת רבות מהבעיות הללו: היא מאפשרת חיפוש ימינה ושמאלה, מוצאת תוצאות בכל עמודה, והיא גמישה יותר.

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

• השתמש במפתחות ייחודיים: אם עמודת החיפוש שלך מכילה כפילויות, נקה את הנתונים לפני החלת VLOOKUP.
• שמרו על מיון הטווח רק אם אתם משתמשים בהתאמה מקורבתזה לא הכרחי להתאמה מדויקת, אבל אף פעם לא מזיק שיהיו נתונים נקיים.
• נעילת הפניות טווח עם $ לפני העתקת הנוסחה לתאים אחרים, פעולה זו תמנע שגיאות והזזות לא רצויות.
• ודא שהתאריכים והמספרים מעוצבים כהלכה (לא כטקסט).
• השתמש ב-IF.ERROR, IF.ND או IFNA כדי להתאים אישית הודעות שגיאה, במיוחד אם אתה משתף את הגיליון עם יותר משתמשים.