תוכן
היום אנו מתחילים סדרת מאמרים המתארים את אחת התכונות השימושיות ביותר של Excel - VPR (VLOOKUP). פונקציה זו, יחד עם זאת, היא מהמורכבות והפחות מובנות.
במדריך זה על VPR אנסה לפרוס את היסודות בצורה פשוטה ככל האפשר על מנת להפוך את תהליך הלמידה לברור ככל האפשר למשתמשים חסרי ניסיון. בנוסף, נלמד מספר דוגמאות עם נוסחאות אקסל שידגימו את מקרי השימוש הנפוצים ביותר לפונקציה VPR.
פונקציית VLOOKUP באקסל - תיאור כללי ותחביר
אז מה זה VPR? ובכן, קודם כל, זו פונקציית אקסל. מה היא עושה? הוא מחפש את הערך שציינת ומחזיר את הערך המתאים מהעמודה השנייה. דיבור טכני, VPR מחפש את הערך בעמודה הראשונה של הטווח הנתון ומחזיר את התוצאה מעמודה אחרת באותה שורה.
ביישום הנפוץ ביותר, הפונקציה VPR מחפש במסד הנתונים מזהה ייחודי נתון ומחלץ מידע הקשור אליו ממסד הנתונים.
האות הראשונה בשם הפונקציה VPR (VLOOKUP) פירושו Вאנכי (Vאֲנָכִי). לפי זה אתה יכול להבחין VPR החל מ- GPR (HLOOKUP), המחפש ערך בשורה העליונה של טווח - Гאופקי (Hאופקית).
פונקציה VPR זמין ב-Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP ו-Excel 2000.
תחביר של הפונקציה VLOOKUP
פונקציה VPR (VLOOKUP) יש את התחביר הבא:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
כפי שאתה יכול לראות, פונקציה VPR ב-Microsoft Excel יש 4 אפשרויות (או ארגומנטים). שלושת הראשונים הם חובה, האחרון הוא אופציונלי.
- lookup_value (lookup_value) - הערך שיש לחפש. זה יכול להיות ערך (מספר, תאריך, טקסט) או הפניה לתא (המכילה את ערך הבדיקה), או ערך המוחזר על ידי פונקציית Excel אחרת. לדוגמה, נוסחה זו תחפש את הערך 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
אם ערך הבדיקה קטן מהערך הקטן ביותר בעמודה הראשונה של הטווח הנבדק, הפונקציה VPR ידווח על שגיאה #בְּ (#N/A).
- טבלה (טבלה) - שתי עמודות או יותר של נתונים. זכור, הפונקציה VPR תמיד מחפש את הערך בעמודה הראשונה של הטווח שניתן בארגומנט טבלה (שולחן). הטווח הניתן לצפייה יכול להכיל נתונים שונים, כגון טקסט, תאריכים, מספרים, בוליאנים. הפונקציה אינה תלוית רישיות, כלומר תווים גדולים וקטנים נחשבים זהים. אז הנוסחה שלנו תחפש את הערך 40 בתאים מ A2 ל A15, כי A היא העמודה הראשונה של הטווח A2:B15 שניתן בארגומנט טבלה (שולחן):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number) הוא מספר העמודה בטווח הנתון שממנו יוחזר הערך בשורה שנמצאה. העמודה השמאלית ביותר בטווח הנתון היא 1, העמודה השנייה היא 2, העמודה השלישית היא 3 וכן הלאה. עכשיו אתה יכול לקרוא את כל הנוסחה:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)נוסחה מחפשת ערך 40 בטווח A2: A15 ומחזירה את הערך המתאים מעמודה B (מכיוון ש-B היא העמודה השנייה בטווח A2:B15).
אם הערך של הארגומנט col_index_num (מספר_עמודה) פחות מ 1אז VPR ידווח על שגיאה #ערך! (#ערך!). ואם זה יותר ממספר העמודות בטווח טבלה (טבלה), הפונקציה תחזיר שגיאה #REF! (#קישור!).
- טווח_תצוגה (טווח_חיפוש) - קובע מה לחפש:
- התאמה מדויקת, הארגומנט חייב להיות שווה שֶׁקֶר (שֶׁקֶר);
- התאמה משוערת, ארגומנט שווה קוד אמיתי (TRUE) או לא צוין בכלל.
פרמטר זה הוא אופציונלי, אך חשוב מאוד. בהמשך הדרכה זו על VPR אני אראה לך כמה דוגמאות המסבירות כיצד לכתוב נוסחאות למציאת התאמות מדויקות ומשוערות.
דוגמאות VLOOKUP
אני מקווה שהפונקציה VPR להיות לך קצת יותר ברור. עכשיו בואו נסתכל על כמה מקרי שימוש VPR בנוסחאות עם נתונים אמיתיים.
כיצד להשתמש ב-VLOOKUP כדי לחפש בגיליון אקסל אחר
בפועל, נוסחאות עם פונקציה VPR משמשים רק לעתים רחוקות לחיפוש נתונים באותו גליון עבודה. לעתים קרובות יותר מאשר לא, אתה תחפש ותשלוף ערכים תואמים מגיליון אחר.
על מנת להשתמש VPR, חפש בגיליון אחר של Microsoft Excel, אתה חייב בארגומנט טבלה (טבלה) ציין את שם הגיליון עם סימן קריאה ואחריו טווח של תאים. לדוגמה, הנוסחה הבאה מראה שהטווח A2: B15 נמצא בגיליון בשם Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
כמובן, אין צורך להזין את שם הגיליון באופן ידני. פשוט התחל להקליד את הנוסחה, ומתי זה מגיע לטיעון טבלה (טבלה), עבור לגיליון הרצוי ובחר את טווח התאים הרצוי עם העכבר.
הנוסחה המוצגת בצילום המסך למטה מחפשת את הטקסט "מוצר 1" בעמודה A (זו העמודה הראשונה בטווח A1:B2) בגליון עבודה מחירים.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
אנא זכור כי בעת חיפוש ערך טקסט, עליך לתחום אותו במרכאות (""), כפי שנעשה בדרך כלל בנוסחאות אקסל.
לטיעון טבלה (טבלה) רצוי להשתמש תמיד בהפניות מוחלטות (עם הסימן $). במקרה זה, טווח החיפוש יישאר ללא שינוי בעת העתקת הנוסחה לתאים אחרים.
חפש בחוברת עבודה אחרת עם VLOOKUP
לתפקד VPR עובד בין שתי חוברות עבודה של Excel, עליך לציין את שם חוברת העבודה בסוגריים מרובעים לפני שם הגיליון.
לדוגמה, להלן נוסחה שמחפשת את הערך 40 על הגיליון Sheet2 בספר Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
הנה הדרך הקלה ביותר ליצור נוסחה באקסל עם VPRשמקשרת לחוברת עבודה אחרת:
- פתח את שני הספרים. זה לא נדרש, אבל קל יותר ליצור נוסחה בדרך זו. אתה לא רוצה להזין את שם חוברת העבודה באופן ידני, נכון? בנוסף, זה יגן עליך מפני שגיאות הקלדה מקריות.
- התחל להקליד פונקציה VPRוכשזה מגיע לטיעון טבלה (טבלה), עבור לחוברת עבודה אחרת ובחר בה את טווח החיפוש הנדרש.
צילום המסך שלהלן מציג את הנוסחה עם החיפוש מוגדר לטווח בחוברת העבודה PriceList.xlsx על הגיליון מחירים.
פונקציה VPR יעבוד גם כאשר אתה סוגר את חוברת העבודה שחיפשת והנתיב המלא לקובץ חוברת העבודה מופיע בשורת הנוסחאות, כפי שמוצג להלן:
אם השם של חוברת העבודה או הגיליון מכיל רווחים, יש לכלול אותו באגרות:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
כיצד להשתמש בטווח או בטבלה בשם בנוסחאות עם VLOOKUP
אם אתה מתכנן להשתמש באותו טווח חיפוש במספר פונקציות VPR, אתה יכול ליצור טווח בעל שם ולהזין את שמו בנוסחה כארגומנט טבלה (שולחן).
כדי ליצור טווח בעל שם, פשוט בחר את התאים והזן שם מתאים בשדה שם פרטי, משמאל לשורת הנוסחאות.
כעת תוכלו לרשום את הנוסחה הבאה למציאת מחיר מוצר מוצר 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
רוב שמות הטווחים עובדים עבור כל חוברת העבודה של Excel, כך שאין צורך לציין את שם הגיליון עבור הארגומנט טבלה (טבלה), גם אם הנוסחה וטווח החיפוש נמצאים על גליונות עבודה שונים. אם הם נמצאים בחוברות עבודה שונות, אז לפני שם הטווח עליך לציין את שם חוברת העבודה, למשל, כך:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
אז הנוסחה נראית הרבה יותר ברורה, מסכים? כמו כן, שימוש בטווחים בעלי שם הוא חלופה טובה להפניות מוחלטות מכיוון שהטווח בעל השם אינו משתנה בעת העתקת הנוסחה לתאים אחרים. זה אומר שאתה יכול להיות בטוח שטווח החיפוש בנוסחה תמיד יישאר נכון.
אם תמיר טווח של תאים לגיליון אלקטרוני מלא של Excel באמצעות הפקודה טבלתי כרטיסיית (טבלה). הַכנָסָה (הוסף), אז כאשר אתה בוחר טווח עם העכבר, Microsoft Excel יוסיף אוטומטית את שמות העמודות (או את שם הטבלה אם תבחר את כל הטבלה) לנוסחה.
הנוסחה המוגמרת תיראה בערך כך:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
או אולי אפילו ככה:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
בעת שימוש בטווחים בעלי שם, הקישורים יפנו לאותם תאים לא משנה היכן תעתיקו את הפונקציה VPR בתוך חוברת העבודה.
שימוש בתווים כלליים בנוסחאות VLOOKUP
כמו בהרבה פונקציות אחרות, VPR אתה יכול להשתמש בתווים הכלליים הבאים:
- סימן שאלה (?) - מחליף כל תו בודד.
- כוכבית (*) - מחליפה כל רצף של תווים.
שימוש בתווים כלליים בפונקציות VPR יכול להיות שימושי במקרים רבים, למשל:
- כאשר אתה לא זוכר בדיוק את הטקסט שאתה צריך למצוא.
- כאשר אתה רוצה למצוא איזו מילה שהיא חלק מהתוכן של תא. יודע ש VPR מחפש לפי תוכן התא בכללותו, כאילו האפשרות מופעלת התאם את תוכן התא כולו (כל התא) בחיפוש הסטנדרטי של אקסל.
- כאשר תא מכיל רווחים נוספים בתחילת או בסוף התוכן. במצב כזה, אתה יכול לדפוק את המוח שלך במשך זמן רב, לנסות להבין מדוע הנוסחה לא עובדת.
דוגמה 1: מחפש טקסט שמתחיל או מסתיים בתווים מסוימים
נניח שאתה רוצה לחפש לקוח ספציפי במסד הנתונים המוצג להלן. אתה לא זוכר את שם המשפחה שלו, אבל אתה יודע שזה מתחיל ב"אק". הנה נוסחה שתעשה את העבודה בסדר גמור:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
עכשיו כשאתה בטוח שמצאת את השם הנכון, אתה יכול להשתמש באותה נוסחה כדי למצוא את הסכום ששולם על ידי לקוח זה. כדי לעשות זאת, פשוט שנה את הארגומנט השלישי של הפונקציה VPR למספר העמודה הרצוי. במקרה שלנו, זו עמודה C (מקום שלישי בטווח):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
הנה עוד כמה דוגמאות עם תווים כלליים:
~ מצא שם המסתיים ב"גבר":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ מצא שם שמתחיל ב"מודעה" ומסתיים ב"בן":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ אנו מוצאים את השם הפרטי ברשימה, המורכב מ-5 תווים:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
לתפקד VPR עם תווים כלליים פעלו כהלכה, בתור הארגומנט הרביעי שאתה צריך תמיד להשתמש שֶׁקֶר (שֶׁקֶר). אם טווח החיפוש מכיל יותר מערך אחד התואם את מונחי החיפוש עם תווים כלליים, אז הערך הראשון שנמצא יוחזר.
דוגמה 2: שלב תווים כלליים והפניות לתאים בנוסחאות VLOOKUP
עכשיו בואו נסתכל על דוגמה קצת יותר מורכבת של איך לחפש באמצעות הפונקציה VPR לפי ערך בתא. דמיינו שעמודה A היא רשימה של מפתחות רישיון, ועמודה B היא רשימה של שמות בעלי רישיון. בנוסף, יש לך חלק (מספר תווים) של מפתח רישיון כלשהו בתא C1, ואתה רוצה למצוא את שם הבעלים.
ניתן לעשות זאת באמצעות הנוסחה הבאה:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
נוסחה זו מחפשת את הערך מתא C1 בטווח הנתון ומחזירה את הערך המתאים מעמודה B. שימו לב שבארגומנט הראשון, אנו משתמשים בתו אמפרסנד (&) לפני ואחרי הפניה לתא כדי לקשר את מחרוזת הטקסט.
כפי שניתן לראות באיור למטה, הפונקציה VPR מחזיר את "ג'רמי היל" מכיוון שמפתח הרישיון שלו מכיל את רצף התווים מתא C1.
שימו לב שהטיעון טבלה (טבלה) בצילום המסך למעלה מכיל את שם הטבלה (טבלה 7) במקום לציין טווח של תאים. זה מה שעשינו בדוגמה הקודמת.
התאמה מדויקת או משוערת בפונקציית VLOOKUP
ולבסוף, בואו נסתכל מקרוב על הארגומנט האחרון שצוין עבור הפונקציה VPR - טווח_תצוגה (interval_view). כפי שהוזכר בתחילת השיעור, טענה זו חשובה מאוד. אתה יכול לקבל תוצאות שונות לחלוטין באותה נוסחה עם הערך שלה קוד אמיתי (אמת או שֶׁקֶר (שֶׁקֶר).
ראשית, בואו נגלה מה מתכוון Microsoft Excel בהתאמות מדויקות ומשוערות.
- אם הטיעון טווח_תצוגה (טווח_חיפוש) שווה ל שֶׁקֶר (FALSE), הנוסחה מחפשת התאמה מדויקת, כלומר בדיוק אותו ערך כפי שניתן בארגומנט lookup_value (ערך_חיפוש). אם בעמודה הראשונה של הטווח tמערך_יכול (טבלה) נתקל בשני ערכים או יותר התואמים את הארגומנט lookup_value (search_value), ואז הראשון ייבחר. אם לא נמצאו התאמות, הפונקציה תדווח על שגיאה #בְּ (#N/A). לדוגמה, הנוסחה הבאה תדווח על שגיאה #בְּ (#N/A) אם אין ערך בטווח A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - אם הטיעון טווח_תצוגה (טווח_חיפוש) שווה ל קוד אמיתי (TRUE), הנוסחה מחפשת התאמה משוערת. ליתר דיוק, קודם כל הפונקציה VPR מחפש התאמה מדויקת, ואם לא נמצאה, בוחר התאמה משוערת. התאמה משוערת היא הערך הגדול ביותר שאינו חורג מהערך שצוין בארגומנט. lookup_value (ערך_חיפוש).
אם הטיעון טווח_תצוגה (טווח_חיפוש) שווה ל קוד אמיתי (TRUE) או לא צוין, אז יש למיין את הערכים בעמודה הראשונה של הטווח בסדר עולה, כלומר מהקטן לגדול ביותר. אחרת, הפונקציה VPR עלול להחזיר תוצאה שגויה.
כדי להבין טוב יותר את חשיבות הבחירה קוד אמיתי (אמת) או שֶׁקֶר (FALSE), בואו נסתכל על עוד כמה נוסחאות עם הפונקציה VPR ותסתכל על התוצאות.
דוגמה 1: מציאת התאמה מדויקת עם VLOOKUP
כזכור, כדי לחפש התאמה מדויקת, הארגומנט הרביעי של הפונקציה VPR צריך לעניין שֶׁקֶר (שֶׁקֶר).
נחזור לטבלה כבר מהדוגמה הראשונה ונגלה איזו חיה יכולה לנוע במהירות 50 מיילים לשעה. אני מאמין שנוסחה זו לא תגרום לך לקשיים:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
שים לב שטווח החיפוש שלנו (עמודה A) מכיל שני ערכים 50 - בתאים A5 и A6. נוסחה מחזירה ערך מהתא B5. למה? כי כשמחפשים התאמה מדויקת, הפונקציה VPR משתמש בערך הראשון שנמצא התואם לזה שמחפשים.
דוגמה 2: שימוש ב-VLOOKUP כדי למצוא התאמה משוערת
כאשר אתה משתמש בפונקציה VPR כדי לחפש התאמה משוערת, כלומר כאשר הארגומנט טווח_תצוגה (טווח_חיפוש) שווה ל קוד אמיתי (TRUE) או מושמט, הדבר הראשון שעליך לעשות הוא למיין את הטווח לפי העמודה הראשונה בסדר עולה.
זה מאוד חשוב בגלל הפונקציה VPR מחזירה את הערך הבא בגודלו אחרי הערך הנתון, ואז החיפוש מפסיק. אם תזניחו את המיון הנכון, תקבלו תוצאות מוזרות מאוד או הודעת שגיאה. #בְּ (#N/A).
כעת תוכל להשתמש באחת מהנוסחאות הבאות:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
כפי שאתה יכול לראות, אני רוצה לברר למי מהחיות יש את המהירות הכי קרובה 69 מיילים לשעה. והנה התוצאה שהפונקציה החזירה לי VPR:
כפי שאתה יכול לראות, הנוסחה החזירה תוצאה אנטילופה (אנטילופה), שמהירותה 61 מיילים לשעה, אם כי הרשימה כוללת גם בַּרדְלָס (צ'יטה) שרץ במהירות 70 מייל לשעה, ו-70 זה קרוב יותר ל-69 מ-61, לא? למה זה קורה? בגלל הפונקציה VPR בעת חיפוש התאמה משוערת, מחזיר את הערך הגדול ביותר שאינו גדול מזה שמחפשים.
אני מקווה שהדוגמאות האלה שופכות קצת אור על העבודה עם הפונקציה VPR באקסל, ואתה כבר לא מסתכל עליה כעל אאוטסיידר. עכשיו לא מזיק לחזור בקצרה על נקודות המפתח של החומר שלמדנו כדי לתקן אותו טוב יותר בזיכרון.
VLOOKUP באקסל - אתה צריך לזכור את זה!
- פונקציה VPR אקסל לא יכול להסתכל שמאלה. הוא תמיד מחפש את הערך בעמודה השמאלית ביותר של הטווח שניתן על ידי הארגומנט טבלה (שולחן).
- בתפקוד VPR כל הערכים אינם תלויי רישיות, כלומר אותיות קטנות וגדולות שוות ערך.
- אם הערך שאתה מחפש נמוך מהערך המינימלי בעמודה הראשונה של הטווח הנבדק, הפונקציה VPR ידווח על שגיאה #בְּ (#N/A).
- אם טיעון שלישי col_index_num (מספר_עמודה) פחות מ 1פונקציה VPR ידווח על שגיאה #ערך! (#ערך!). אם הוא גדול ממספר העמודות בטווח טבלה (טבלה), הפונקציה תדווח על שגיאה #REF! (#קישור!).
- השתמש בהפניות תא מוחלטות בארגומנט טבלה (טבלה) כך שטווח החיפוש הנכון ישמר בעת העתקת הנוסחה. נסה להשתמש בטווחים או בטבלאות עם שם ב- Excel כחלופה.
- בעת חיפוש התאמה משוער, זכור שהעמודה הראשונה בטווח שאתה מחפש חייבת להיות ממוינת בסדר עולה.
- לבסוף, זכרו את חשיבות הטיעון הרביעי. השתמש בערכים קוד אמיתי (אמת) או שֶׁקֶר (שקר) בכוונה ותיפטרו מכאבי ראש רבים.
במאמרים הבאים של ערכת הפונקציות שלנו VPR באקסל, נלמד דוגמאות מתקדמות יותר, כמו ביצוע חישובים שונים באמצעות VPR, חילוץ ערכים ממספר עמודות ועוד. תודה שקראת את המדריך הזה ואני מקווה לראות אותך שוב בשבוע הבא!