מציאת המספר הקרוב ביותר

תוכן

בפועל, לעתים קרובות מאוד יש מקרים שבהם אתה ואני צריכים למצוא את הערך הקרוב ביותר בקבוצה (טבלה) ביחס למספר נתון. זה יכול להיות, למשל:

חישוב הנחה בהתאם לנפח.
חישוב גובה הבונוסים בהתאם ליישום התכנית.
חישוב תעריפי משלוח בהתאם למרחק.
בחירת מכולות מתאימות לסחורות וכו'.

יתרה מכך, ייתכן שיידרש עיגול הן למעלה והן למטה, בהתאם למצב.

ישנן מספר דרכים – ברורות ולא כל כך ברורות – לפתור בעיה כזו. בואו נסתכל עליהם ברצף.

ראשית, נדמיין ספק שנותן הנחות בסיטונאות, ואחוז ההנחה תלוי בכמות הסחורה הנרכשת. למשל בקנייה של יותר מ-5 חלקים ניתנת הנחה של 2% ובקנייה מ-20 חלקים כבר 6% וכו'.

כיצד לחשב במהירות ובצורה יפה את אחוז ההנחה בעת הזנת כמות הסחורה הנרכשת?

שיטה 1: IFs מקוננים

שיטה מהסדרה "מה יש לחשוב - צריך לקפוץ!". שימוש בפונקציות מקוננות IF (אם) כדי לבדוק ברצף אם ערך התא נכנס לכל אחד מהמרווחים ולהציג הנחה עבור הטווח המתאים. אבל הנוסחה במקרה זה יכולה להתברר כמסורבלת מאוד:

מציאת המספר הקרוב ביותר

אני חושב שזה ברור שלניפוי באגים ב"בובת מפלצת" כזו או לנסות להוסיף לה כמה תנאים חדשים לאחר זמן מה זה כיף.

בנוסף, ל-Microsoft Excel יש מגבלת קינון לפונקציית ה-IF – 7 פעמים בגרסאות ישנות יותר ו-64 פעמים בגרסאות חדשות יותר. מה אם אתה צריך יותר?

שיטה 2. VLOOKUP עם תצוגת מרווחים

שיטה זו היא הרבה יותר קומפקטית. כדי לחשב את אחוז ההנחה, השתמש בפונקציה האגדית VPR (VLOOKUP) במצב חיפוש משוער:

מציאת המספר הקרוב ביותר

איפה

B4 – ערך כמות הסחורה בעסקה הראשונה שבגינה אנו מחפשים הנחה
$G$4:$H$8 – קישור לטבלת ההנחות – ללא "כותרת" ועם הכתובות קבועות עם הסימן $.
2 - המספר הסידורי של העמודה בטבלת ההנחה שממנה אנו רוצים לקבל את ערך ההנחה
נכון - כאן קבור ה"כלב". If כארגומנט הפונקציה האחרון VPR לפרט שֶׁקֶר (שֶׁקֶר) או 0, אז הפונקציה תחפש התאמה קפדנית בעמודת הכמות (ובמקרה שלנו זה ייתן שגיאה #N/A, מכיוון שאין ערך 49 בטבלת הנחות). אבל אם במקום שֶׁקֶר לכתוב נכון (נָכוֹן) או 1, אז הפונקציה תחפש לא את המדויק, אבל הקרוב ביותר הקטן ביותר ערך וייתן לנו את אחוז ההנחה שאנו צריכים.

החיסרון של שיטה זו הוא הצורך למיין את טבלת ההנחות בסדר עולה לפי העמודה הראשונה. אם אין מיון כזה (או שהוא נעשה בסדר הפוך), הנוסחה שלנו לא תעבוד:

מציאת המספר הקרוב ביותר

בהתאם לכך, ניתן להשתמש בגישה זו רק כדי למצוא את הערך הקטן ביותר הקרוב ביותר. אם אתה צריך למצוא את הגדול הקרוב ביותר, אז אתה צריך להשתמש בגישה אחרת.

שיטה 3. מציאת הגדול הקרוב ביותר באמצעות הפונקציות INDEX ו-MATCH

עכשיו בואו נסתכל על הבעיה שלנו מהצד השני. נניח שאנו מוכרים מספר דגמים של משאבות תעשייתיות ביכולות שונות. טבלת המכירות משמאל מציגה את ההספק הנדרש על ידי הלקוח. עלינו לבחור משאבה בעוצמה המקסימלית הקרובה או שווה, אך לא פחות ממה שנדרש על ידי הפרויקט.

הפונקציה VLOOKUP לא תעזור כאן, אז תצטרך להשתמש באנלוגי שלה - חבורה של פונקציות INDEX (אינדקס) וחשופים יותר (התאמה):

מציאת המספר הקרוב ביותר

כאן, פונקציית MATCH עם הארגומנט האחרון -1 פועלת במצב של מציאת הערך הגדול ביותר הקרוב, והפונקציה INDEX מחלצת את שם הדגם שאנו צריכים מהעמודה הסמוכה.

שיטה 4. פונקציה חדשה VIEW (XLOOKUP)

אם יש לך גרסה של Office 365 עם כל העדכונים מותקנים, אז במקום VLOOKUP (VLOOKUP) אתה יכול להשתמש באנלוגי שלו - פונקציית VIEW (XLOOKUP), שכבר ניתחתי בפירוט:

מציאת המספר הקרוב ביותר

כאן:

B4 – הערך ההתחלתי של כמות המוצר עבורה אנו מחפשים הנחה
$G$4:$G$8 - הטווח שבו אנו מחפשים התאמות
$H$4:$H$8 – מגוון התוצאות שמהן תרצו להחזיר את ההנחה
טיעון רביעי (-1) כולל את החיפוש אחר המספר הקטן הקרוב ביותר שאנו רוצים במקום התאמה מדויקת.

היתרונות של שיטה זו הם שאין צורך למיין את טבלת ההנחות ויכולת לחפש במידת הצורך לא רק את הערך הקטן ביותר, אלא גם את הערך הגדול ביותר הקרוב ביותר. הטיעון האחרון במקרה זה יהיה 1.

אבל, למרבה הצער, עדיין לא לכולם יש את התכונה הזו - רק בעלים מאושרים של Office 365.

שיטה 5. Power Query

אם אתה עדיין לא מכיר את תוסף Power Query העוצמתי והחינמי לחלוטין עבור Excel, אז אתה כאן. אם אתה כבר מכיר, אז בואו ננסה להשתמש בו כדי לפתור את הבעיה שלנו.

בוא נעשה תחילה עבודת הכנה:

בואו נמיר את טבלאות המקור שלנו לדינמיות (חכמות) באמצעות קיצור מקשים Ctrl+T או צוות בית - עיצוב כטבלה (בית - עיצוב כטבלה).
למען הבהירות, בואו ניתן להם שמות. מבצעים и מנצח כרטיסייה בנאי (לְעַצֵב).
טען כל אחת מהטבלאות בתורו לתוך Power Query באמצעות הכפתור מתוך טבלה/טווח כרטיסייה נתונים (נתונים - מטבלה/טווח). בגרסאות האחרונות של Excel, השם של לחצן זה שונה ל- עם עלים (מהגיליון).
אם לטבלאות יש שמות עמודות שונים עם כמויות, כמו בדוגמה שלנו ("כמות סחורה" ו"כמות מ..."), אז יש לשנות את שמם ב-Power Query ולקרוא אותו.
לאחר מכן, תוכל לחזור ל-Excel על ידי בחירת הפקודה בחלון עורך השאילתה של Power בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען אל...) ולאחר מכן אפשרות פשוט צור קשר (יצירת חיבור בלבד).
ואז מתחיל המעניין ביותר. אם יש לך ניסיון ב-Power Query, אז אני מניח שקו המחשבה הנוסף צריך להיות בכיוון של מיזוג שתי הטבלאות הללו עם שאילתת join (merge) a la VLOOKUP, כפי שהיה בשיטה הקודמת. למעשה, נצטרך להתמזג במצב הוספה, מה שכלל לא מובן מאליו במבט ראשון. בחר בלשונית Excel נתונים - קבל נתונים - שילוב בקשות - הוסף (נתונים - קבל נתונים - שילוב שאילתות - הוסף) ואז השולחנות שלנו מבצעים и מנצח בחלון שמופיע:
לאחר לחיצה על OK השולחנות שלנו יודבקו למכלול אחד - אחד מתחת לשני. שימו לב שהעמודות עם כמות הסחורה בטבלאות אלו נפלו זו מתחת לזו, משום. יש להם אותו שם:
אם רצף השורות המקורי בטבלת המכירות חשוב לך, אז כדי שאחרי כל התמורות הבאות תוכל לשחזר אותו, הוסף עמודה ממוספרת לטבלה שלנו באמצעות הפקודה הוספת עמודה - עמודת אינדקס (הוסף עמודה - עמודת אינדקס). אם רצף השורות לא משנה לך, אז אתה יכול לדלג על שלב זה.
כעת, באמצעות הרשימה הנפתחת בכותרת הטבלה, מיין אותה לפי עמודה כמות עולה:
והטריק העיקרי: לחץ לחיצה ימנית על כותרת העמודה הנחה לבחור צוות למלא למטה (למלא למטה). תאים ריקים עם ריק מילא אוטומטית את ערכי ההנחה הקודמים:
נותר לשחזר את רצף השורות המקורי על ידי מיון לפי עמודה מדד (תוכלו למחוק אותו בבטחה מאוחר יותר) ולהיפטר מקווים מיותרים עם פילטר ריק לפי טור קוד ביצוע: