1Aug
במאמר האחרון, הצגנו את הפונקציה Excel בשם VLOOKUP והסביר כיצד ניתן להשתמש בו כדי לאחזר מידע ממסד נתונים לתא בגליון עבודה מקומי.במאמר זה הזכרנו שיש שני שימושים עבור VLOOKUP, ורק אחד מהם עסק בבדיקת מסדי נתונים.במאמר זה, השני והאחרון בסדרה VLOOKUP, אנו בוחנים את זה, שימוש פחות ידוע יותר עבור הפונקציה VLOOKUP.
אם עדיין לא עשית זאת, קרא את המאמר הראשון של VLOOKUP - מאמר זה יניח כי רבים מהמושגים המוסברים במאמר זה כבר ידועים לקורא.
בעת עבודה עם מסדי נתונים, VLOOKUP מועבר "מזהה ייחודי" המשמש לזיהוי איזה תיעוד נתונים אנו רוצים למצוא במסד הנתונים( לדוגמה, קוד מוצר או מזהה לקוח).זה מזהה ייחודי חייב קיים במסד הנתונים, אחרת VLOOKUP מחזירה לנו שגיאה.במאמר זה, נבחן דרך להשתמש ב- VLOOKUP שבו המזהה אינו צריך להתקיים כלל במאגר הנתונים.זה כמעט כאילו VLOOKUP יכול לאמץ גישה "כמעט מספיק טוב מספיק" כדי להחזיר את הנתונים שאנחנו מחפשים.בנסיבות מסוימות, זה בדיוק מה שאנחנו צריכים.
אנו נמחיש את המאמר הזה בדוגמה של עולם אמיתי - זה של חישוב העמלות שנוצרו על סט של נתוני מכירות.נתחיל עם תרחיש פשוט מאוד, ואז בהדרגה לעשות את זה יותר מורכב, עד הפתרון הרציונלי היחיד לבעיה היא להשתמש VLOOKUP.התרחיש הראשוני בחברה הפיקטיבית שלנו עובד כך: אם איש מכירות יוצר יותר מ -30,000 דולר בשווי של מכירות בשנה מסוימת, העמלה שהם מרוויחים על המכירות האלה היא 30%.אחרת העמלה שלהם היא רק 20%.עד כה מדובר בגליון עבודה פשוט למדי:
כדי להשתמש בגליון עבודה זה, איש המכירות נכנס לנתוני המכירות שלהם בתא B1, והנוסחה בתא B2 חושבת את שיעור העמלה הנכון שהם זכאים לקבל, המשמשים בתא B3 לחישובאת סך עמלה כי איש המכירות הוא חייב( שהוא פשוט כפל של B1 ו B2).
התא B2 מכיל את החלק המעניין היחיד בגליון עבודה זה - הנוסחה לקביעת שיעור העמלה שיש להשתמש בו: מתחת ל הסף של 30,000 $, או אחד מעל הסף.נוסחה זו עושה שימוש בפונקציה Excel בשם IF .עבור הקוראים כי הם לא מכירים את IF, זה עובד ככה:
IF( מצב, ערך אם נכון, אם הערך כוזב )
כאשר מצב הוא ביטוי מעריך נכון או שווא.בדוגמה לעיל, מצב הוא הביטוי B1 & lt; B5 , אשר ניתן לקרוא כמו "האם B1 פחות מ B5?", או, במילים אחרות, "האם המכירות הכולל פחות סף".אם התשובה לשאלה זו היא "כן", אזי אנו משתמשים בערך אם הפרמטר האמיתי של הפונקציה, כלומר B6 במקרה זה - שיעור העמלה אם סך המכירות היה מתחת לסף .אם התשובה לשאלה היא "לא"( שקר), אז אנחנו משתמשים בערך אם פרמטר כוזב של הפונקציה, כלומר B7 במקרה זה - שיעור העמלה אם סך המכירות היה מעל את הסף.
כפי שאתה יכול לראות, באמצעות סך המכירות של 20,000 $ נותן לנו שיעור עמלה של 20% בתא B2.אם אנחנו נכנסים לערך של 40,000 $, אנחנו מקבלים שיעור עמלה אחר:
אז הגיליון האלקטרוני שלנו עובד.
בואו נעשה את זה יותר מורכב.בוא נציג סף שני: אם איש המכירות מרוויח יותר מ 40,000 $, אז שיעור עמלה שלהם עולה ל 40%:
קל מספיק כדי להבין בעולם האמיתי, אבל בתא B2 הנוסחה שלנו מקבל יותר מורכב.אם תסתכל מקרוב על הנוסחה, תראה שהפרמטר השלישי של הפונקציה IF המקורית( הערך אם כוזב) הוא כעת פונקציית IF שלמה בפני עצמה.זה נקרא פונקציה מקוננת ( פונקציה בתוך פונקציה).זה תקף לחלוטין ב- Excel( זה אפילו עובד!), אבל זה יותר קשה לקרוא ולהבין.
אנחנו לא הולכים להיכנס את האגוזים ואת הברגים של איך ולמה זה עובד, וגם לא נבחן את הניואנסים של פונקציות מקוננות.זהו מדריך על VLOOKUP, לא על Excel בכלל.
בכל מקרה, זה נהיה יותר גרוע!מה לגבי כאשר אנו מחליטים שאם הם מרוויחים יותר מ 50,000 $ אז הם זכאים עמלה של 50%, ואם הם מרוויחים יותר מ 60,000 $ אז הם זכאים ל 60% עמלה?
עכשיו הנוסחה בתא B2, בזמן הנכון, הפך כמעט בלתי קריא.אף אחד לא צריך לכתוב נוסחאות שבו הפונקציות מקוננות ארבע רמות עמוק!אין ספק שיש דרך פשוטה יותר?
יש בהחלט.VLOOKUP להצלה!
בואו לעצב מחדש את גליון העבודה קצת.אנו נשמור את כל אותם נתונים, אבל לארגן את זה בצורה חדשה, יותר דרך :
קח רגע ולוודא בעצמך כי החדש דרג שער עובד בדיוק כמו סדרת סף לעיל.
מבחינה מושגית, מה שאנחנו עומדים לעשות הוא להשתמש VLOOKUP לחפש את המכירות של המכירות הכולל( מ B1) בטבלה שיעור לחזור אלינו את שיעור עמלה המקביל.שים לב כי איש מכירות אולי אכן יצר מכירות לא אחד מחמשת הערכים בטבלה שיעור( $ 0, $ 30,000, $ 40,000, 50,000 $ או 60,000 $).הם אולי יצרו מכירות של 34,988 $.חשוב לציין כי $ 34,988 עושה לא להופיע בטבלה שיעור.בוא נראה אם VLOOKUP יכול לפתור את הבעיה שלנו בכל מקרה. ..
אנו בוחרים בתא B2( המיקום שאנחנו רוצים לשים את הנוסחה שלנו), ולאחר מכן הכנס את הפונקציה VLOOKUP מהכרטיסייה :
תיבת הדו-שיח פונקציה ארגומנטים עבור VLOOKUP מופיעה.אנו ממלאים את הטיעונים( פרמטרים) אחד אחד, החל Lookup_value , וזה, במקרה זה, סך המכירות מהתא B1.אנו מניחים את הסמן בשדה Lookup_value ולאחר מכן לחצו פעם אחת על התא B1:
הבא עלינו לציין ל- VLOOKUP איזה טבלה לחפש את הנתונים האלה. בדוגמה זו, זהו טבלת המחירים, כמובן.אנו מניחים את הסמן בשדה ולאחר מכן מדגישים את טבלת התעריפים כולה - למעט הכותרות :
הבא עלינו לציין איזה עמודה בטבלה מכילה את המידע שאנו רוצים שהנוסחה שלנו תחזור אלינו.במקרה זה אנחנו רוצים את שיעור הוועדה, אשר נמצא בעמודה השנייה בטבלה, ולכן אנו נכנסים 2 לתוך Col_index_num שדה:
לבסוף אנחנו מזינים ערך בשדה Range_lookup .
חשוב: זהו השימוש בשדה זה המבדיל בין שתי הדרכים לשימוש ב- VLOOKUP.כדי להשתמש ב- VLOOKUP עם מסד נתונים, הפרמטר הסופי, Range_lookup , חייב להיות מוגדר תמיד FALSE , אך עם שימוש אחר זה של VLOOKUP, עלינו להשאיר אותו ריק או להזין ערך של TRUE .בעת שימוש ב- VLOOKUP, חשוב שתבצע את הבחירה הנכונה עבור פרמטר סופי זה.
כדי להצהיר במפורש, אנו נכניס ערך נכון בשדה Range_lookup .זה יהיה גם בסדר להשאיר את זה ריק, שכן זהו ערך ברירת המחדל:
השלמנו את כל הפרמטרים.כעת אנו לוחצים על הלחצן אישור , ו- Excel בונה לנו את הנוסחה VLOOKUP עבורנו:
אם ננסה עם מספר סכומים שונים של מכירות, נוכל לספק את עצמנו כי הנוסחה פועלת.
מסקנה
בגירסת מסד הנתונים של VLOOKUP, שם הפרמטר Range_lookup FALSE , הערך המועבר בפרמטר הראשון( Lookup_value ) חייב להיות במסד הנתונים.במילים אחרות, אנחנו מחפשים התאמה מדויקת.
אבל בשימוש אחר זה של VLOOKUP, אנחנו לא בהכרח מחפשים התאמה מדויקת.במקרה זה, "קרוב מספיק מספיק טוב".אבל למה אנחנו מתכוונים "ליד מספיק"?נשתמש בדוגמה: כאשר מחפשים שיעור עמלה על סך מכירות של 34,988 $, הנוסחה שלנו VLOOKUP יחזיר לנו ערך של 30%, המהווה את התשובה הנכונה.מדוע בחר את השורה בטבלה המכילה 30%?מה, למעשה, אומר "קרוב מספיק" במקרה זה?בואו נהיה מדויקים:
כאשר
מוגדר כ- TRUE ( או מושמט), VLOOKUP ייראה בעמודה 1 ויתאים את הערך לערך הגבוה ביותר שאינו עולה על הפרמטר Lookup_value .
חשוב גם לציין כי עבור מערכת זו לעבוד, את השולחן יש למיין בסדר עולה על עמודה 1 !
אם אתה רוצה להתאמן עם VLOOKUP, את קובץ המדגם מאויר במאמר זה ניתן להוריד מכאן.