תוכן
לאחרונה הקדשנו מאמר לאחת מפונקציות האקסל השימושיות ביותר שנקראות VPR והראה כיצד ניתן להשתמש בו כדי לחלץ את המידע הנדרש ממסד נתונים לתא גליון עבודה. הזכרנו גם שיש שני מקרי שימוש לפונקציה VPR ורק אחד מהם עוסק בשאילתות מסד נתונים. במאמר זה תלמד עוד דרך פחות מוכרת להשתמש בפונקציה VPR ב - Excel.
אם עדיין לא עשית זאת, הקפד לקרוא את המאמר האחרון על הפונקציה VPR, כי כל המידע להלן מניח שאתה כבר מכיר את העקרונות המתוארים במאמר הראשון.
בעבודה עם מסדי נתונים, פונקציות VPR מועבר מזהה ייחודי, המשמש לזיהוי המידע שאנו רוצים למצוא (לדוגמה, קוד מוצר או מספר זיהוי של לקוח). קוד ייחודי זה חייב להיות קיים במסד הנתונים, אחרת VPR ידווח על שגיאה. במאמר זה נסתכל על דרך זו של השימוש בפונקציה VPRכאשר המזהה אינו קיים במסד הנתונים כלל. כאילו הפונקציה VPR עבר למצב משוער, ובוחר אילו נתונים לספק לנו כשאנחנו רוצים למצוא משהו. בנסיבות מסוימות, זה בדיוק מה שצריך.
דוגמא מהחיים. קבענו את המשימה
בואו נמחיש את המאמר עם דוגמה מהחיים האמיתיים – חישוב עמלות על סמך מגוון רחב של מדדי מכירות. נתחיל עם אפשרות פשוטה מאוד, ואז נסבך אותה בהדרגה עד שהפתרון הרציונלי היחיד לבעיה הוא להשתמש בפונקציה VPR. התרחיש הראשוני עבור המשימה הפיקטיבית שלנו הוא כדלקמן: אם איש מכירות מרוויח יותר מ-30000$ במכירות בשנה, אז העמלה שלו היא 30%. אחרת, העמלה היא רק 20%. בוא נניח את זה בצורה של טבלה:
המוכר מזין את נתוני המכירות שלו בתא B1, והנוסחה בתא B2 קובעת את שיעור העמלה הנכון שהמוכר יכול לצפות לו. בתורו, התעריף המתקבל משמש בתא B3 כדי לחשב את העמלה הכוללת שהמוכר צריך לקבל (פשוט מכפילים את התאים B1 ו-B2).
החלק המעניין ביותר בטבלה נמצא בתא B2 - זוהי הנוסחה לקביעת שיעור העמלה. נוסחה זו מכילה פונקציית Excel הנקראת IF (אם). לקוראים שאינם מכירים את הפונקציה הזו, אסביר כיצד היא פועלת:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
מַצָב הוא ארגומנט פונקציה שלוקח את הערך של אחד מהם קוד אמיתי (אמת או שֶׁקֶר (שֶׁקֶר). בדוגמה למעלה, הביטוי B1
האם זה נכון ש-B1 קטן מ-B5?
או שאתה יכול להגיד את זה אחרת:
האם זה נכון שסכום המכירות הכולל לשנה נמוך משווי הסף?
אם נענה על השאלה הזו כן (TRUE), ואז הפונקציה חוזרת ערך אם נכון (ערך אם TRUE). במקרה שלנו, זה יהיה הערך של תא B6, כלומר שיעור העמלה כאשר סך המכירות מתחת לסף. אם נענה על השאלה לא (FALSE) ואז חוזר ערך אם שקר (ערך אם FALSE). במקרה שלנו, זה הערך של תא B7, כלומר שיעור העמלה כאשר סך המכירות הוא מעל הסף.
כפי שאתה יכול לראות, אם ניקח את סך המכירות של $20000, נקבל שיעור עמלה של 2% בתא B20. אם נזין ערך של $40000, שיעור העמלה ישתנה ב-30%:
כך עובד השולחן שלנו.
אנחנו מסבכים את המשימה
בואו נעשה את הדברים קצת יותר קשים. בואו נגדיר עוד סף: אם המוכר מרוויח יותר מ-$40000, אזי שיעור העמלה עולה ל-40%:
הכל נראה פשוט וברור, אבל הנוסחה שלנו בתא B2 הופכת מסובכת בצורה ניכרת. אם תסתכל מקרוב על הנוסחה, תראה את הארגומנט השלישי של הפונקציה IF (IF) הפך לעוד פונקציה מלאה IF (אם). בנייה זו נקראת קינון של פונקציות זו לתוך זו. Excel מאפשר בשמחה את המבנים האלה, והם אפילו עובדים, אבל הם הרבה יותר קשים לקריאה והבנה.
לא נעמיק בפרטים הטכניים - למה ואיך זה עובד, ולא ניכנס לניואנסים של כתיבת פונקציות מקוננות. אחרי הכל, זה מאמר המוקדש לפונקציה VPR, לא מדריך שלם לאקסל.
בכל מקרה, הנוסחה הופכת מסובכת יותר! מה אם נציג אפשרות נוספת לשיעור עמלה של 50% עבור אותם מוכרים שמרוויחים יותר מ-$50000 במכירות. ואם מישהו מכר יותר מ-$60000, האם הוא ישלם 60% עמלה?
כעת הנוסחה בתא B2, גם אם היא נכתבה ללא שגיאות, הפכה לבלתי קריאה לחלוטין. אני חושב שיש מעטים שרוצים להשתמש בנוסחאות עם 4 רמות של קינון בפרויקטים שלהם. חייבת להיות דרך קלה יותר?!
ויש דרך כזו! הפונקציה תעזור לנו VPR.
אנו מיישמים את הפונקציה VLOOKUP כדי לפתור את הבעיה
בואו נשנה קצת את עיצוב השולחן שלנו. נשמור את כל אותם שדות ונתונים, אבל נסדר אותם בצורה חדשה וקומפקטית יותר:
קח רגע וודא את השולחן החדש טבלת תעריפים כולל את אותם נתונים כמו טבלת הסף הקודמת.
הרעיון המרכזי הוא להשתמש בפונקציה VPR לקביעת תעריף התעריף הרצוי לפי הטבלה טבלת תעריפים בהתאם להיקף המכירות. שימו לב שהמוכר יכול למכור סחורה בסכום שאינו שווה לאחד מחמשת הספים בטבלה. לדוגמה, הוא יכול למכור ב-34988$, אבל אין סכום כזה. בוא נראה איך זה מתפקד VPR יכול להתמודד עם מצב כזה.
הוספת פונקציית VLOOKUP
בחר בתא B2 (שם נרצה להכניס את הנוסחה שלנו) ומצא VLOOKUP (VLOOKUP) בספריית הפונקציות של Excel: נוסחות (נוסחאות) > ספריית פונקציות (ספריית פונקציות) > חיפוש והפניה (הפניות ומערכים).
תופיע תיבת דו-שיח טיעוני פונקציה (טיעוני פונקציה). אנו ממלאים את ערכי הטיעונים אחד אחד, החל מ- Lookup_value (ערך_חיפוש). בדוגמה זו, זהו הסכום הכולל של המכירות מתא B1. שים את הסמן בשדה Lookup_value (Lookup_value) ובחר בתא B1.
לאחר מכן, עליך לציין את הפונקציות VPRהיכן לחפש נתונים. בדוגמה שלנו, זו טבלה טבלת תעריפים. שים את הסמן בשדה מערך_טבלה (טבלה) ובחר את כל הטבלה טבלת תעריפיםלמעט כותרות.
לאחר מכן, עלינו לציין מאיזו עמודה לחלץ נתונים באמצעות הנוסחה שלנו. אנו מעוניינים בשיעור העמלה, שנמצא בעמודה השנייה של הטבלה. לכן, לטיעון Col_index_num (Column_number) הזן את הערך 2.
ולבסוף, אנו מציגים את הטיעון האחרון - Range_lookup (מרווח_חיפוש).
חשוב: השימוש בארגומנט זה הוא שעושה את ההבדל בין שתי הדרכים ליישום הפונקציה VPR. בעבודה עם מסדי נתונים, הארגומנט Range_lookup (range_lookup) חייב להיות תמיד ערך שֶׁקֶר (FALSE) כדי לחפש התאמה מדויקת. בשימוש שלנו בפונקציה VPR, עלינו להשאיר שדה זה ריק, או להזין ערך קוד אמיתי (נָכוֹן). חשוב מאוד לבחור את האפשרות הזו בצורה נכונה.
כדי להבהיר את זה, נציג קוד אמיתי (TRUE) בשטח Range_lookup (מרווח_חיפוש). אמנם, אם תשאיר את השדה ריק, זו לא תהיה שגיאה, שכן קוד אמיתי הוא ערך ברירת המחדל שלו:
מילאנו את כל הפרמטרים. עכשיו אנחנו לוחצים OK, ו-Excel יוצר לנו נוסחה עם פונקציה VPR.
אם נתנסה במספר ערכים שונים עבור סכום המכירות הכולל, נוודא שהנוסחה פועלת כהלכה.
סיכום
כאשר הפונקציה VPR עובד עם מסדי נתונים, ארגומנט Range_lookup (טווח_חיפוש) חייב לקבל שֶׁקֶר (שֶׁקֶר). והערך הוזן כ Lookup_value (Lookup_value) חייב להתקיים במסד הנתונים. במילים אחרות, זה מחפש התאמה מדויקת.
בדוגמה שבדקנו במאמר זה, אין צורך לקבל התאמה מדויקת. זה המקרה כאשר הפונקציה VPR חייב לעבור למצב משוער כדי להחזיר את התוצאה הרצויה.
לדוגמה: אנו רוצים לקבוע באיזה תעריף להשתמש בחישוב העמלה עבור איש מכירות עם נפח מכירות של $34988. פוּנקצִיָה VPR מחזיר לנו ערך של 30%, וזה נכון לחלוטין. אבל למה הנוסחה בחרה את השורה המכילה בדיוק 30% ולא 20% או 40%? מה הכוונה בחיפוש משוער? בואו נהיה ברורים.
כאשר הטיעון Range_lookup (interval_lookup) יש ערך קוד אמיתי (TRUE) או הושמט, פונקציה VPR עובר דרך העמודה הראשונה ובוחר את הערך הגדול ביותר שאינו חורג מערך הבדיקה.
נקודה חשובה: כדי שסכמה זו תפעל, יש למיין את העמודה הראשונה בטבלה בסדר עולה.