‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫סיכום בנושא‪:‬‬ ‫פונקצית ‪ VLookUp‬בתוכנת אקסל ( ‪) Ms-Excel‬‬ ‫פונקצית ‪ VLookUp‬מחפשת ערך מסוים בעמודה הראשונה של טבלה‪ ,‬ומחזירה את ערך התא בשורה‬ ‫המתאימה ובעמודה שתבחר מהטבלה‪ .‬אנו נשתמש בפונקציה זו הינה בבואנו למשוך נתונים מטבלה‬ ‫קיימת‪ ,‬על סמך ערך מפתח כלשהו‪.‬‬ ‫פונקצית ‪ VLookUp‬הינה פונקציה שימושית עד מאוד‪ ,‬וחשוב להבין את שימושיה השונים‪ ,‬על כן‬ ‫נתחיל את ההסבר בדוגמא פשוטה‪:‬‬ ‫בגיליון שלפנינו ישנה פירוט נכסי המקרקעין שנמכרו על ידי סוכני התיווך של חברת חביב נכסים‬ ‫בע"מ‪ .‬תיווך הינה פעולה של התאמת מוכר מרצון לקונה מרצון‪ ,‬ותמיכה בכל הפעולות של העברת‬ ‫הבעלות והתשלומים בין הצדדים‪ .‬שכר התיווך נקבע כאחוז מסכום העסקה‪ ,‬ומשתנה מסוכן לסוכן‪.‬‬ ‫בנוסף לטבלה שמימין‪ ,‬של מחירי הנכסי דלא ניידי (נדל"ן)‪ ,‬ישנה טבלה נוספת (משמאל) ובה אחוז‬ ‫העמלה של כל סוכן וסוכן‪.‬‬ ‫בניית הגיליון בצורה שכזו טומנת בחובה יתרונות שונים‪ .‬הבולט שביתרונות הינה האפשרות לחשב‬ ‫מחדש את כל עמלות הסוכן‪ ,‬בשינוי של תא אחד בלבד (בטבלה השמאלית)‪.‬‬ ‫בעמודה ‪ E‬ברצוננו שירשם העמלה של כל סוכן כתלות בשם ה מופיע בעמודה ‪( C‬אחוז העמלה של‬ ‫הסוכן)‪ ,‬ובמחיר הנכס שנמכר‪ .‬העמלה תקבע כמכפלה של אחוז העמלה כפול מחיר הנכס‪.‬‬ ‫‪1‬‬ ‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫בתא ‪ - E3‬מה שנדרש בכדי לבצע את דרישתנו הינה פונקציה (‪ ) vlookup‬שתחפש את הערך המופיע‬ ‫בתא ‪( C3‬שם הסוכן) בתוך העמודה הראשונה (עמודה ‪ )I‬של הטבלה משמאל (‪ .)I2:J6‬לאחר שתמצא‬ ‫א ת ה ע ר ך ב ש ו ר ה מ ס ו י מ ת ש ל ט ב ל ת ה ח י פ ו ש ( ‪ ) I 2 : J6‬ה פ ו נ ק צ י ה ת ח ז י ר א ת ה ע ר ך ה מ ת א י ם ל ש ו ר ה ז ו‬ ‫ולעמודה השנייה מטבלת החיפוש (עמודה ‪.)J‬‬ ‫בהמשך‬ ‫נראה‬ ‫מהי‬ ‫הפונקציה‬ ‫שיש‬ ‫הספציפית‬ ‫לרשום‬ ‫לביצוע‬ ‫הפעולה‬ ‫המבוקשת‪.‬‬ ‫מ ב נ ה ה פ ו נ ק צ י ה ה ב ס י ס י ש ל ‪ , v lo o k u p‬ב נ ו י ל פ י ה ת ח ב י ר ה ב א ‪ ( :‬ה ס ב ר מ פ ו ר ט ב ה מ ש ך )‬ ‫)חיפוש מקורב? ‪ ,‬מאיזה מס' עמודה התוצאה? ‪ ,‬באיזה טבלה לחפש? ‪ ,‬מה לחפש?( ‪=VLookUp‬‬ ‫כ א מ ו ר ‪ ,‬פ ו נ ק צ י ת ‪ V L o o kU p‬מ ח פ ש ת ע ר ך מ ס ו י ם ב ע מ ו ד ה ה ר א ש ו נ ה ש ל ט ב ל ה ‪ ,‬ו מ ח ז י ר ה א ת ע ר ך ה ת א‬ ‫בשורה המתאימה ובעמודה שתבחר מהטבלה‪ .‬אנו נשתמש בפונקציה זו הינה בבואנו למשוך נתוני ם‬ ‫מטבלה קיימת‪ ,‬על סמך ערך מפתח כלשהו‪ .‬התחביר המפורט‪:‬‬ ‫)‪= V LookUp (look up_ value , t able_arr ay , col_index_num , r ange_lookup‬‬ ‫‪lookup_value‬‬ ‫ערך החיפוש הינו אותו ערך ספציפי אותו אנו מחפשים‪ .‬ערך זה נהוג‬ ‫לכנות שדה מפתח שכן הוא לרוב מקשר בין שני טבלאות שונים‪.‬‬ ‫בפועל‪ ,‬רכיב זה יכול להיות הפניה לתא אחר (וכך יהיה ברוב המקרים)‪,‬‬ ‫ואם בכוונתנו לגרור את הפונקציה לתאים נוספים‪ ,‬יש לוודא שאין בעיות‬ ‫של מיקום יחסי ו‪/‬או קבוע‪.‬‬ ‫‪table_array‬‬ ‫טבלת החיפוש הינה הטבלה אשר בעמודה הראשונה שלה נחפש את‬ ‫ערך החיפוש ‪ .‬טבלה זו תוגדר כטווח של תאים רציפים‪ ,‬דו מימדים‪,‬‬ ‫הכוללים את שדה החיפוש (העמודה הראשונה) ואת שדה התוצאה (כל‬ ‫עמודה אחרת)‪.‬‬ ‫חשוב‬ ‫עד‬ ‫מאוד‬ ‫להדגיש‬ ‫כי‬ ‫תנאי‬ ‫ראשוני‬ ‫לשימוש‬ ‫בפונקצית‬ ‫‪ vlookup‬הינה ששדה החיפוש בתוך טבלת החיפוש תהיה ממוינת‬ ‫בסדר עולה‪ ( .‬בהנחה שמדובר בחיפוש מקורב ‪ -‬ראה בהמשך)‪.‬‬ ‫‪col_indx_num‬‬ ‫מאיזה מספר עמודה להחזיר את התוצאה? מתוך טבלת החיפוש‪ ,‬יש‬ ‫לרשום את מספר העמודה שממנה תילקח התוצאה‪.‬‬ ‫‪2‬‬ ‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫מספר העמודה הינו יחסי לטבלת החיפוש‪ ,‬וראוי לשים לב כי שדה‬ ‫החיפוש יקרא במובן זה עמודה מספר ‪ . 1‬ברכיב זה של הפונקציה נכניס‬ ‫ערך מספרי‪.‬‬ ‫‪range_lookup‬‬ ‫האם לבצע חיפוש מקורב? לפונקציה זו אפשרות לביצוע חיפוש מקורב‬ ‫של‬ ‫ערך‬ ‫החיפוש‪,‬‬ ‫בשדה‬ ‫החיפוש‪.‬‬ ‫חיפוש‬ ‫מקורב‬ ‫אינו‬ ‫מגביל‬ ‫את‬ ‫הפונקציה למצב של זהות מוחלטת‪.‬‬ ‫רכיב זה של הפונקציה הינו בוליאני ומקבל ערך של נכון או לא נכון‬ ‫(‪.)True / False‬‬ ‫כעת‪ ,‬נחזור לדוגמא הראשונית של סוכני תיווך בנדל"ן ונדגים את השימוש בפונקציה (‪:)E3‬‬ ‫בשלב הראשון אנו רוצים למשוך את אחוז העמלה של הסוכן מהטבלה 'עמלות סוכנים' (משמאל) על‬ ‫פי שם הסוכן הנתון בעמודה ‪ C‬של הטבלה המרכזית (מימין)‪ .‬לצורך כך נפעיל את הפונקציה‬ ‫‪ vlookup‬כמודגם בתמונה הבאה‪:‬‬ ‫ראוי לציין כי נוסחא זו תקפה רק עבור התא ‪ C3‬ואם ברצוננו לגרור אותה למטה בכל טווח הנתונים‪,‬‬ ‫יש לקבע את טבלת החיפוש (‪.)Table Array‬‬ ‫שימו לב שברכיב (ארגומנט) הראשון של הפונקציה נתון שם הסוכן (לרשומה זו השם לקוח מהתא‬ ‫‪ ,)C3‬בארגומנט השני ‪ -‬טבלת החיפוש והתוצאה (ללא הכותרות)‪ ,‬בארגומנט השלישי – מספר‬ ‫העמודה של התוצאה (מתוך טבלת החיפוש) וברכיב האחרון – התשובה לשאלה‪ :‬חיפוש מקורב?‬ ‫(במקרה שלנו 'לא')‪.‬‬ ‫‪3‬‬ ‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫סיום הפונקציה בשלב הזה יוביל להופעת הערך המספרי ‪ 2.20.0‬בתא ‪ E3‬שזה בדיוק אותו הערך של‬ ‫‪ 0..0%‬מטבלת 'עמלות הסוכנים' (משמאל)‪ .‬הפונקציה לקחה את ערך החיפוש ('משה יניר') וחיפשה‬ ‫אותו בעמודה הראשונה של טבלת החיפוש (עמודה ‪ .) I‬כאשר נמצא הערך המדויק (הארגומנט הרביעי‬ ‫הודיע לפונקציה שלא לחפש ערך מקורב) בשורה מסוימת‪ ,‬הוחזר הערך באותה השורה אך בעמודת‬ ‫התוצאה (ניתנה ברכיב השלישי של הפונקציה)‪.‬‬ ‫לשם שלמות הדוגמא יש להדגיש כי לאחר שבנינו את הפונקציה ‪ vlookup‬ובדקנו שבאמת הכול תקין‬ ‫יש להכפיל את אחוז העמלה במחיר הנכס שנמכר‪ ,‬וכמובן לארגן את הטווחים לפי מיקום יחסי‬ ‫(וקבוע)‪ .‬הפונקציה הסופית שתופיע בתא ‪ E3‬ותועתק לשאר התאים עמודה ‪: E‬‬ ‫‪=VlookUp(C3,I$3:J$6,2,FALSE)*D3‬‬ ‫הערות חשובות בנוגע לפונקציה ‪ VLookUp‬בתוכנת אקסל ( ‪) MS - Excel‬‬ ‫ב ר כ י ב ה ש נ י ( ‪ ) T a b l e _ A r ra y‬י ש ל מ ל א א ת ה ט ו ו ח ש ל ה ט ב ל ה א ו ה ר ש י מ ה ש ב ה נ ח פ ש א ת ע ר ך‬ ‫ה ח י פ ו ש ( ‪ - L o o k u p _ V a l ue‬ב ע מ ו ד ה ר א ש ו נ ה ) ו מ מ נ ה נ ק ב ל א ת ע ר ך ה ת ו צ א ה ( ע ל פ י מ ס פ ר‬ ‫ה ע מ ו ד ה ש י נ ת ן ב ‪ C o l _ In d e x _ n u m -‬כ א ר ג ו מ נ ט ש ל י ש י ) ‪ .‬א ת ה ט ו ו ח נ י ת ן ל כ ת ו ב כ ט ו ו ח י ח ס י ‪ ,‬ט ו ו ח‬ ‫קבוע‪ ,‬טווח סופי‪ ,‬טווח אין‪ -‬סופי‪ ,‬ואפילו 'שם של טווח' בהפניה ישירה‪.‬‬ ‫א ם ה ח ל ט נ ו ל ב צ ע ח י פ ו ש מ ק ו ר ב ( ‪ R a n g e _ L o o k up = T R U E‬א ו ש א י ן ע ר ך ב ‪) R a n g e _ L o o k u p -‬‬ ‫א ז י י ש ל ו ו ד א כ י ה ע מ ו ד ה ה ר א ש ו נ ה ש ל ‪ T a b l e _ A r ra y‬ה י נ ה מ מ ו י נ ת ב ס ד ר ע ו ל ה ‪:‬‬ ‫) ס ו ף ( ‪ ) B e g i n n i n g - …, - 2 , -1 , 0 ,1 , 2 , …, A -Z ,FA LSE , T RUE - E n d‬ה ת ח לה (‬ ‫‪4‬‬ ‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫ניתן לסדר את הערכים בעמודה הראשונה על ידי שימוש בפעולה מיון (‪ )Sort‬מתפריט‬ ‫הנתונים (‪ ,) Data‬ובחירה באפשרות סדר יורד (‪.)Ascending‬‬ ‫הערכים שבעמודה הראשונה של ‪ Table_Array‬יכולים להיות טקסטואליים‪ ,‬מספריים או‬ ‫ערכים לוגיים‪.‬‬ ‫א ו ת י ו ת א נ ג ל י ת ג ד ו ל ו ת ( ‪ ) U p p e r c a s e‬ו ק ט נ ו ת ( ‪ ) L o w e r c a se‬ה י נ ם ש ו ו י ם מ ב ח י נ ת פ ו נ ק צ י ה‬ ‫זו‪.‬‬ ‫התהליך שמתרחש בחיפוש מקורב הינו פשוט‪ :‬אקסל סורק את הרשימה (עמודה ראשונה)‬ ‫ע ד ש מ ו צ א ע ר ך ש ג ד ו ל מ ע ר ך ה ח י פ ו ש ( ‪ . ) L o o k u p_ V a l u e‬א ם מ צ א ע ר ך ש כ ז ה ‪ ,‬א ק ס ל‬ ‫יחזיר את הערך המקביל שלפניו (עיין בדוגמא)‪ .‬היינו‪ ,‬הערך שימומש הינו הערך הגדול‬ ‫ביותר שהינו פחות מ‪.Lookup_Value -‬‬ ‫ה ר כ י ב ‪ C o l _ I n d e x_ N u m‬ה י נ ו מ ס פ ר ה ע מ ו ד ה ש מ מ נ ו י ו ח ז ר ה ע ר ך ‪ C o l _ I nd e x _ n u m .‬ח י י ב ל ה י ו ת‬ ‫בעל ערך מספרי ואם הינו ‪ 1‬הפונקציה תחזיר את הערך מהעמודה הראשונה של הטווח הנתון‬ ‫( ‪ . ) T a b l e _ A r r ay‬א ם ה י נ ו ‪ 0‬ה פ ו נ ק צ י ה ת ח ז י ר א ת ה ע ר ך מ ה ע מ ו ד ה ה ש נ י ה ש ל ה ט ו ו ח ה נ ת ו ן‬ ‫( ‪ ) T a b l e _ A r r ay‬ו כ ו ' ‪.‬‬ ‫א ם ה ע ר ך ש י נ ת ן ל ‪ C o l _I n d e x _ n u m -‬ה י נ ו פ ח ו ת מ ‪ 1 -‬ה פ ו נ ק צ י ה ת ח ז י ר א ת ע ר ך ה ש ג י א ה‬ ‫'!‪.'#VALUE‬‬ ‫א ם ה ע ר ך ש י נ ת ן ל ‪C o l _ I n d e x_ n u m -‬‬ ‫הינו גדול ממספר העמודות שבטווח הנתון‬ ‫( ‪ ) T a b l e _ A r r ay‬ה פ ו נ ק צ י ה ת ח ז י ר א ת ע ר ך ה ש ג י א ה ' ! ‪. ' # R E F‬‬ ‫אם ‪ VLookUp‬אינו יכול למצוא את ערך החיפוש ומדובר בחיפוש מדויק ( = ‪Range_Lookup‬‬ ‫‪ ) FALSE‬הפונקציה תחזיר את ערך השגיאה '‪ .' #N/A‬הודעת שגיאה זו תינתן גם במקרה ו‪-‬‬ ‫‪ R a n g e _ L o o k u p = TR UE‬א ו ש א י ן ע ר ך ב ‪ , R a n g e _ L o o k u p -‬כ א ש ר ע ר ך ה ח י פ ו ש ה י נ ו ק ט ן מ ה ע ר ך‬ ‫ה ק ט ן ב י ו ת ר ב ע מ ו ד ה ה ר א ש ו נ ה ש ל ‪ ( T a b l e _ A r r ay‬ט ו ו ח ה ח י פ ו ש ) ‪.‬‬ ‫שימו לב! כיוון גיליון העבודה (משמאל לימין – אנגלית ‪ /‬מימין לשמאל – עברית) יקבע מהי‬ ‫ה ע מ ו ד ה ה ר א ש ו נ ה ש ל ‪. T ab l e _ A r r a y‬‬ ‫ה א ו ת ‪ V‬ש ל ‪ V L o o kU p‬מ ס מ ל ‪ V e r t i c a l‬ה י י נ ו א נ כ י ( ל ע ו מ ת א ו פ ק י ) ‪ .‬ס י מ ו ן ז ה מ ב ט א א ת ה ע ו ב ד ה‬ ‫שהפונקציה מתייחסת לרשימות סדורות כשכל עמודה אנכית הינה משתנה וכל שורה מקרה או‬ ‫תצפית‪.‬‬ ‫‪5‬‬ ‫הפקולטה למדעי החברה‬ ‫המחלקה לכלכלה‬ ‫‪Faculty of Social Sciences‬‬ ‫‪Department of Economics‬‬ ‫יישומי מחשב ‪ -‬אקסל‬ ‫סמסטר ב' תשע"ב‬ ‫דוגמאות נוספות ל מימוש פונקצי ת ‪ VLookUp‬בתוכנת אקסל ( ‪) MS - Excel‬‬ ‫נניח כי נתון גיליון הנתונים הבאים (הגיליון מוגדר משמאל לימין)‪:‬‬ ‫‪C‬‬ ‫‪B‬‬ ‫‪A‬‬ ‫‪T e m pe r a t u r e‬‬ ‫‪500‬‬ ‫‪400‬‬ ‫‪300‬‬ ‫‪250‬‬ ‫‪200‬‬ ‫‪150‬‬ ‫‪100‬‬ ‫‪050‬‬ ‫‪000‬‬ ‫‪V i s c o s i ty‬‬ ‫‪3.55‬‬ ‫‪3.25‬‬ ‫‪2.93‬‬ ‫‪2.75‬‬ ‫‪2.57‬‬ ‫‪2.38‬‬ ‫‪2.17‬‬ ‫‪1.95‬‬ ‫‪1.71‬‬ ‫‪D e n s i ty‬‬ ‫‪0.457‬‬ ‫‪0.525‬‬ ‫‪0.616‬‬ ‫‪0.675‬‬ ‫‪0.746‬‬ ‫‪0.835‬‬ ‫‪0.946‬‬ ‫‪1.090‬‬ ‫‪1.290‬‬ ‫‪1‬‬ ‫‪2‬‬ ‫‪3‬‬ ‫‪4‬‬ ‫‪5‬‬ ‫‪6‬‬ ‫‪7‬‬ ‫‪8‬‬ ‫‪9‬‬ ‫‪10‬‬ ‫כעת נגדיר מספר דוגמאות לפונקציה ‪ VLookUp‬ואת תוצאותיהם (שימו לב שבגרסאות תוכנה שונות‬ ‫ייתכן ותקבלו תוצאות מעט שונות‪ ,‬בעיקר בסוגי הודעות השגיאה)‪:‬‬ ‫ה פ ו נ ק צי ה‬ ‫ה ת וצ א ה‬ ‫‪2 .1 7‬‬ ‫) ‪= V LOOKUP (1 ,A2 : C1 0,2‬‬ ‫‪100‬‬ ‫) ‪= V LOOKUP (1 ,A2 : C1 0,3 ,T RUE‬‬ ‫‪#N/A‬‬ ‫) ‪= V LOOKUP (.7 ,A2 : C10 ,3 ,FALSE‬‬ ‫! ‪# V ALUE‬‬ ‫) ‪= V LOOKUP (0 .1 ,A2 : C1 0 ,2 ,T RUE‬‬ ‫‪1 .7 1‬‬ ‫) ‪= V LOOKUP (2 ,A2 : C1 0,2 ,T RUE‬‬ ‫‪#N/A‬‬ ‫) ‪= V LOOKUP ( 5 ,A2 : C1 0,3 ,FALSE‬‬ ‫‪# V ALUE‬‬ ‫) ‪= V LOOKUP ( 2 ,A2 : C1 0,3 ,FALSE‬‬ ‫‪1 .2 9 0‬‬ ‫) ‪= V LOOKUP (1 .2 9 0 ,A:C,1 ,FALSE‬‬ ‫‪6‬‬