מיטוב משלוח

ניסוח הבעיה

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

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

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

מובן ש:

• הטבלה בצהוב בהיר (C4:G6) מתארת ​​את עלות משלוח פריט אחד מכל מחסן לכל חנות.
• תאים סגולים (C15:G14) מתארים את כמות הסחורה הנדרשת לכל חנות למכירה.
• תאים אדומים (J10:J13) מציגים את הקיבולת של כל מחסן - הכמות המקסימלית של סחורה שהמחסן יכול להכיל.
• תאים צהובים (C13:G13) וכחול (H10:H13) הם סכומי השורות והעמודות עבור תאים ירוקים, בהתאמה.
• עלות המשלוח הכוללת (J18) מחושבת כסכום המוצרים של מספר הסחורות ועלויות המשלוח המתאימות להן - לחישוב, הפונקציה משמשת כאן SUMPRODUCT (SUMPRODUCT).
  

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

פתרון

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

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

בואו נריץ את התוסף:

בחלון זה, עליך להגדיר את הפרמטרים הבאים:

• ייעול פונקציית היעד (קבע tכסף תָא) - כאן יש צורך לציין את המטרה העיקרית הסופית של האופטימיזציה שלנו, כלומר קופסה ורודה עם עלות המשלוח הכוללת (J18). ניתן למזער את תא היעד (אם מדובר בהוצאות, כמו במקרה שלנו), למקסם (אם מדובר למשל ברווח) או לנסות להביא אותו לערך נתון (למשל, להתאים בדיוק לתקציב שהוקצה).
• שינוי תאים משתנים (By משתנה תאים) - כאן אנו מציינים את התאים הירוקים (C10: G12), על ידי שינוי הערכים שלהם אנו רוצים להשיג את התוצאה שלנו - עלות המינימום של משלוח.
• תואם את ההגבלות (נושא ל מה היא אילוצים) – רשימת הגבלות שיש לקחת בחשבון בעת ​​ביצוע אופטימיזציה. כדי להוסיף הגבלות לרשימה, לחץ על הכפתור להוסיף (לְהוֹסִיף) והזן את התנאי בחלון שמופיע. במקרה שלנו, זה יהיה אילוץ הביקוש:

   

  והגבלת הנפח המרבי של מחסנים:

בנוסף למגבלות הברורות הקשורות לגורמים פיזיים (קיבולת מחסנים ואמצעי תחבורה, מגבלות תקציב וזמן וכו'), לעיתים יש צורך להוסיף הגבלות "מיוחדות לאקסל". כך, למשל, אקסל יכול לארגן לכם בקלות "לייעל" את עלות המשלוח על ידי הצעת הובלת סחורה מהחנויות חזרה למחסן - העלויות יהפכו לשליליות, כלומר נרוויח! 🙂

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

לאחר הגדרת כל הפרמטרים הדרושים, החלון אמור להיראות כך:

ברשימה הנפתחת בחר שיטת פתרון, עליך לבחור בנוסף את השיטה המתמטית המתאימה לפתרון מבחר של שלוש אפשרויות:

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

המשימה שלנו היא ליניארית בבירור: נמסר 1 חתיכה - בילה 40 רובל, נמסר 2 חתיכות - בילה 80 רובל. וכו', כך ששיטת הסימפלקס היא הבחירה הטובה ביותר.

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

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

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

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

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

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

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