VLOOKUP ב - Excel, חלק 2 באמצעות VLOOKUP ללא מסד נתונים
במאמר האחרון, הצגנו את הפונקציה 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, זה עובד ככה:
אם(מצב, ערך אם נכון, ערך אם שקר)
איפה ה תנאי הוא ביטוי מעריך גם נכון או שקר. בדוגמה לעיל, תנאי הוא הביטוי B1
כפי שאתה יכול לראות, באמצעות סך המכירות של 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 מתוך נוסחאות tab you
ה ארגומנטים פונקציה תיבת VLOOKUP מופיעה. אנחנו ממלאים את הטיעונים (פרמטרים) אחד אחד, החל עם Lookup_value, אשר, במקרה זה, סך המכירות מהתא B1. אנחנו מניחים את הסמן ב Lookup_value שדה ולאחר מכן לחץ פעם אחת על תא B1:
הבא אנחנו צריכים לציין כדי VLOOKUP מה השולחן לבדוק את הנתונים האלה פנימה בדוגמה זו, זה שולחן שיעור, כמובן. אנחנו מניחים את הסמן ב טבלה ולאחר מכן סמן את טבלת המחירים כולה - למעט הכותרותYou
הבא עלינו לציין איזה עמודה בטבלה מכילה את המידע שאנו רוצים שהנוסחה שלנו תחזור אלינו. במקרה זה אנחנו רוצים את שיעור העמלה, אשר נמצא בעמודה השנייה בטבלה, ולכן אנו נכנסים 2 לתוך ה Col_index_num שדה:
לבסוף אנו מזינים ערך ב Range_lookup שדה.
חשוב: השימוש בשדה זה מבדיל בין שתי הדרכים לשימוש ב- VLOOKUP. כדי להשתמש ב- VLOOKUP עם מסד נתונים, פרמטר סופי זה, Range_lookup, חייב להיות מוגדר תמיד FALSE, אבל עם שימוש זה אחר של VLOOKUP, אנחנו חייבים להשאיר את זה ריק או להזין ערך של אמת. בעת שימוש ב- VLOOKUP, חשוב שתבצע את הבחירה הנכונה עבור פרמטר סופי זה.
כדי להיות מפורש, אנו נכניס ערך של נכון בתוך ה Range_lookup שדה. זה יהיה גם בסדר להשאיר את זה ריק, שכן זהו ערך ברירת המחדל:
השלמנו את כל הפרמטרים. עכשיו אנחנו לוחצים על בסדר כפתור, ו- Excel בונה נוסחה VLOOKUP שלנו עבורנו:
אם אנחנו מתנסים עם כמה סכומים שונים של מכירות, אנחנו יכולים לספק את עצמנו כי הנוסחה פועלת.
סיכום
בגירסת "מסד הנתונים" של VLOOKUP, שם Range_lookup הפרמטר הוא FALSE, הערך המועבר בפרמטר הראשון (Lookup_value) צריך להיות נוכח במסד הנתונים. במילים אחרות, אנחנו מחפשים התאמה מדויקת.
אבל בשימוש אחר זה של VLOOKUP, אנחנו לא בהכרח מחפשים התאמה מדויקת. במקרה זה, "קרוב מספיק מספיק טוב". אבל למה אנחנו מתכוונים "ליד מספיק"? נשתמש בדוגמה: כאשר מחפשים שיעור עמלה על סך מכירות של 34,988 $, הנוסחה שלנו VLOOKUP יחזיר לנו ערך של 30%, שהיא התשובה הנכונה. מדוע בחר את השורה בטבלה המכילה 30%? מה, למעשה, אומר "קרוב מספיק" במקרה זה? בואו נהיה מדויקים:
מתי Range_lookup נקבע ל אמת (או מושמט), VLOOKUP ייראה בעמודה 1 ומתאימים הערך הגבוה ביותר שאינו גדול מ ה Lookup_value פרמטר.
חשוב גם לציין כי עבור מערכת זו לעבוד, יש לסדר את השולחן בסדר עולה בעמודה 1!
אם אתה רוצה להתאמן עם VLOOKUP, את קובץ המדגם מאויר במאמר זה ניתן להוריד מכאן.
