שימוש ב-Solver לקביעת התמהיל המיטבי של המוצר

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

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

כיצד ניתן לקבוע את תמהיל המוצרים החודשי המגדיל את הרווחיות?

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

  • לתמהיל המוצרים אין אפשרות להשתמש במשאבים רבים יותר מאלה הזמינים.

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

כעת נפתור את הדוגמה הבאה של הבעיה ' ערבוב מוצרים '. באפשרותך למצוא את הפתרון לבעיה זו בקובץ Prodmix. xlsx, המוצג באיור 27-1.

תמונת ספר

נניח שאנו עובדים עבור חברת תרופות שמייצרת שישה מוצרים שונים במפעל שלהם. ייצור של כל מוצר דורש עבודה וחומרי גלם. שורה 4 באיור 27-1 מציגה את שעות העבודה הדרושות להפקת פאונד של כל מוצר, ושורה 5 מציגה את קילוגרמי חומר הגלם הדרושים להפקת פאונד של כל מוצר. לדוגמה, הפקת פאונד של מוצר 1 דורשת שש שעות של עבודה ו-3.2 קילוגרמים של חומרי גלם. עבור כל תרופה, המחיר לפאונד מופיע בשורה 6, עלות היחידה לכל לירה מתבצעת בשורה 7, והתרומה לכל לירה ניתנת בשורה 9. לדוגמה, Product 2 מוכר עבור $11.00 לכל לירה, תוך הקשה על עלות יחידה של $5.70 לכל לירה, והיא תורמת רווחים של $5.30 לכל לירה. דרישת החודש עבור כל תרופה ניתנת בשורה 8. לדוגמה, דרישה עבור Product 3 היא 1041 פאונד. החודש, 4500 שעות העבודה ו-1600 קילו של חומרי גלם זמינים. כיצד יכולה חברה זו למקסם את הרווח החודשי שלה?

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

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

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

(העבודה בשימוש לכל ליטרת התרופה 1) * (התרופה 1 קילוגרם הופק) +
(העבודה בשימוש לכל ליטרת של תרופה 2) * (התרופה 2 קילוגרם הופק) +...
(העבודה בשימוש לכל ליטרת התרופה 6) * (הסם 6 קילוגרמים המופק)

אנו יכולים לחשב את השימוש בעבודה בצורה מייגעת יותר, כמו D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. באופן דומה, השימוש בחומר גלם עשוי להיות מחושב כ- D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. עם זאת, הזנת נוסחאות אלה בגליון עבודה עבור שישה מוצרים גוזלת זמן רב. תאר לעצמך כמה זמן יידרש אם עבדת עם חברה שמפיקה, לדוגמה, 50 מוצרים במפעל שלהם. דרך קלה הרבה יותר לחשב את השימוש בעבודה וחומרי גלם היא להעתיק מ-D14 ל-D15 את הנוסחה SUMPRODUCT ($D $2: $I $2, D4: I4). נוסחה זו מחשבת D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (שהוא שימוש בעבודה שלנו) אך קל הרבה יותר להיכנס! שים לב שאני משתמש בסימן $ עם טווח D2: I2 כך שכאשר אני מעתיק את הנוסחה אני עדיין תופס את תמהיל המוצרים משורה 2. הנוסחה בתא D15 מחשבת שימוש בחומר גלם.

בצורה דומה, הרווח שלנו נקבע על-ידי

(התרופה 1 רווח לכל לירה) * (התרופה 1 קילוגרם הופק) +
(תרופה 2 הרווח לכל לירה) * (התרופה 2 קילוגרם הופק) +...
(התרופה 6 רווח לכל לירה) * (התרופה 6 קילוגרמים הופק)

הרווח מחושב בקלות בתא D12 עם הנוסחה SUMPRODUCT (D9: i9ב, $D $2: $I $2).

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

  • תא יעד.המטרה שלנו היא למקסם את הרווח (מחושב בתא D12).

  • שינוי תאים.מספר הקילוגרמים המופקים מכל מוצר (המופיע בטווח התאים D2: I2)

  • אילוצים. יש לנו את האילוצים הבאים:

    • אין להשתמש ביותר עבודה או חומר גלם מאלה שזמינים. כלומר, הערכים בתאים D14: D15 (המשאבים הנמצאים בשימוש) חייבים להיות קטנים או שווים לערכים בתאים F14: F15 (המשאבים הזמינים).

    • אין להפיק יותר מתרופה מאשר בדרישה. כלומר, הערכים בתאים D2: I2 (קילוגרמים המופקים מכל תרופה) חייבים להיות קטנים או שווים לביקוש לכל תרופה (המופיעה בתאים D8: I8).

    • לא ניתן לייצר כמות שלילית של תרופה כלשהי.

אראה לך כיצד להזין את תא היעד, לשנות את התאים והאילוצים ב-Solver. לאחר מכן, כל שעליך לעשות הוא ללחוץ על לחצן ' פתור ' כדי למצוא תמהיל מוצרים להגדלת הרווח!

כדי להתחיל, לחץ על הכרטיסיה נתונים ובקבוצה ניתוח, לחץ על Solver.

הערה:  כפי שמוסבר בפרק 26, "מבוא למיטוב עם Excel Solver", solver מותקן על-ידי לחיצה על לחצן Microsoft Office, ולאחר מכן אפשרויות Excel, ולאחר מכן תוספות. ברשימה נהל, לחץ על תוספות של Excel, סמן את התיבה Solver add-in ולאחר מכן לחץ על אישור.

תיבת הדו Solver Parameters תופיע, כפי שמוצג באיור 27-2.

תמונת ספר

לחץ על התיבה הגדר תא יעד ולאחר מכן בחר את תא הרווח שלנו (cell D12). לחץ על התיבה על-ידי שינוי תאים ולאחר מכן הצבע על טווח D2: I2, המכיל את הקילוגרמים המופקים מכל תרופה. תיבת הדו אמורה להיראות כעת באיור 27-3.

תמונת ספר

אנו מוכנים כעת להוסיף אילוצים למודל. לחץ על לחצן הוסף. תראה את תיבת הדו הוספת אילוץ, המוצגת באיור 27-4.

תמונת ספר

כדי להוסיף את אילוצי שימוש במשאבים, לחץ על התיבה הפניה לתא ולאחר מכן בחר את טווח D14: D15. בחר < = מהרשימה האמצעית. לחץ על התיבה אילוץ ולאחר מכן בחר את טווח התאים F14: F15. תיבת הדו הוספת אילוץ אמורה להיראות כעת כאיור 27-5.

תמונת ספר

הבטיחו כעת ש-Solver מנסה ערכים שונים עבור התאים המשתנים, רק צירופים המחייבים הן את D14< = F14 (העבודה הנמצאת בשימוש היא קטנה או שווה לעבודה זמינה) ו- D15< = F15 (חומר גלם שנמצא בשימוש קטן או שווה ל- חומר גלם זמין) ייחשב. לחץ על הוסף כדי להזין את אילוצי הביקוש. מלא את תיבת הדו הוספת אילוץ כפי שמוצג באיור 27-6.

תמונת ספר

הוספת אילוצים אלה מבטיחה ש-Solver מנסה צירופים שונים עבור ערכי התאים המשתנים, רק צירופים העונים על הפרמטרים הבאים ייחשבו:

  • D2< = D8 (הסכום המופק מהסם 1 קטן או שווה לביקוש עבור התרופה 1)

  • E2< = E8 (כמות התוצרת של התרופה 2 היא קטנה או שווה לביקוש עבור התרופה 2)

  • F2< = F8 (הכמות המופקת מהסם 3 שבוצעה היא קטנה או שווה לביקוש לסמים 3)

  • G2< = G8 (הסכום המופק מהסם 4 שנוצר קטן או שווה לביקוש עבור תרופה 4)

  • H2< = H8 (הכמות המופקת מהסם 5 שבוצעה היא קטנה או שווה לביקוש לסמים 5)

  • I2< = I8 (הסכום המופק מהסם 6 שנוצר הוא קטן או שווה לביקוש לתרופה 6)

לחץ על אישור בתיבת הדו הוספת אילוץ. החלון Solver אמור להיראות כמו איור 27-7.

תמונת ספר

אנו מזינים את האילוץ שמשתנה תאים חייב להיות לא שלילי בתיבת הדו אפשרויות Solver. לחץ על לחצן ' אפשרויות ' בתיבת הדו ' פרמטרים של Solver '. סמן את התיבה ' הנח מודל ליניארי ' ואת התיבה ' הנח שאינם שליליים ', כפי שמוצג באיור 27-8 בעמוד הבא. לחץ על 'אישור'.

תמונת ספר

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

  • תא היעד מחושב על-ידי הוספת התנאים של הטופס (שינוי התא) * (קבוע).

  • כל אילוץ עונה על "דרישת מודל ליניארי". משמעות הדבר היא שכל אילוץ מוערך על-ידי הוספת התנאים של הטופס (שינוי תא) * (קבוע) והשוואת הסכומים לקבוע.

מדוע בעיה זו ליניארית של Solver? תא היעד שלנו (רווח) מחושב

(התרופה 1 רווח לכל לירה) * (התרופה 1 קילוגרם הופק) +
(תרופה 2 הרווח לכל לירה) * (התרופה 2 קילוגרם הופק) +...
(התרופה 6 רווח לכל לירה) * (התרופה 6 קילוגרמים הופק)

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

אילוץ העבודה שלנו מוערך על-ידי השוואת הערך שנגזר ממנו (העבודה הנמצאת בשימוש לכל ליטרת התרופה 1) * (התרופה 1 ליש ט הופק) + (העבודה בשימוש לכל ליטרת התרופה 2) * (התרופה 2 קילוגרמים שהופקו) +... (העבודה שלנוed לכל ליטרת התרופה 6) * (הסם הופק ב-6 קילוגרם) לעבודה הזמינה.

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

אילוצי הביקוש שלנו מקבלים את הטופס

(תרופה 1 הופק) < = (תרופה 1 דרישה)
(תרופה 2 הופק) < = (התרופה 2 דרישה)
§
(תרופה 6 הופק) < = (התרופה 6 דרישה)

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

לאחר שהראית שמודל תמהיל המוצרים שלנו הוא מודל ליניארי, למה שיהיה אכפת לנו?

  • אם מודל Solver הוא ליניארי ואנו בוחרים ' הנח מודל ליניארי ', Solver מובטח למצוא את הפתרון המיטבי למודל Solver. אם מודל Solver אינו ליניארי, ייתכן ש-solver עשוי למצוא את הפתרון המיטבי או לא למצוא אותו.

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

לאחר לחיצה על אישור בתיבת הדו ' אפשרויות solver ', אנו חוזרים לתיבת הדו solver הראשי, המוצגת מוקדם יותר באיור 27-7. כאשר אנו לוחצים על ' פתור ', Solver מחשב פתרון אופטימלי (אם קיים אדם כלשהו) עבור מודל תמהיל המוצרים שלנו. כאמור, בפרק 26, הפתרון המיטבי למודל תמהיל המוצרים יהיה קבוצה של שינוי ערכי תא (קילוגרמים המופקים מכל תרופה) המגדילים את הרווח בקבוצה של כל הפתרונות האפשריים. שוב, פתרון אפשרי הוא קבוצה של שינוי ערכי תא המספקים את כל האילוצים. ערכי התאים המשתנים המוצגים באיור 27-9 הם פתרון אפשרי מכיוון שכל רמות הייצור אינן שליליות, רמות ייצור אינן חורגות מהביקוש, ושימוש במשאבים אינו חורג מהמשאבים הזמינים.

תמונת ספר

ערכי התאים המשתנים המוצגים באיור 27-10 בעמוד הבא מייצגים פתרון לא מתאים מהסיבות הבאות:

  • אנו מייצרים יותר מהסם 5 מהביקוש לכך.

  • אנו משתמשים ביותר עבודה ממה שזמין.

  • אנו משתמשים בחומרי גלם רבים יותר ממה שזמין.

תמונת ספר

לאחר לחיצה על פתור, Solver מוצא במהירות את הפתרון המיטבי המוצג באיור 27-11. עליך לבחור באפשרות ' שמור על פתרון Solver ' כדי לשמר את ערכי הפתרונות האופטימליים בגליון העבודה.

תמונת ספר

חברת התרופות שלנו יכולה למקסם את הרווח החודשי שלה ברמה של $6,625.20 על-ידי הפקת 596.67 קילוגרמים של תרופה 4, 1084 קילוגרמים של תרופה 5, ואף אחת מהתרופות האחרות! אין באפשרותנו לקבוע אם ניתן להשיג את הרווח המרבי של $6,625.20 בדרכים אחרות. כל מה שאנחנו יכולים להיות בטוחים בו הוא שהמשאבים והביקושים המוגבלים שלנו, אין דרך להרוויח יותר מ-$6,627.20 החודש.

נניח שביקוש לכל מוצר חייב להתקיים. (ראה גליון העבודה ללא פתרון אפשרי בקובץ Prodmix. xlsx.) לאחר מכן נאלץ לשנות את אילוצי הביקוש שלנו מ -d2: I2< = D8: I8 to D2: I2> = D8: I8. לשם כך, פתח את Solver, בחר באפשרות D2: I2< = D8: I8 constraint ולאחר מכן לחץ על שנה. תיבת הדו שינוי אילוץ, המוצגת באיור 27-12, מופיעה.

תמונת ספר

בחר > = ולאחר מכן לחץ על אישור. הבטיחו כעת ש-Solver ישקול שינוי ערכי תא בלבד העונים על כל הדרישות. כאשר תלחץ על ' פתור ', תראה את ההודעה "Solver לא הצליח למצוא פתרון אפשרי". הודעה זו אינה מתכוונת לכך שעשינו טעות במודל שלנו, אך בהתאם למשאבים המוגבלים שלנו, לא ניתן לעמוד בדרישות עבור כל המוצרים. Solver פשוט מספר לנו שאם נרצה לעמוד בדרישות עבור כל מוצר, אנו צריכים להוסיף עוד עבודה, חומרי גלם נוספים או יותר משניהם.

בוא נראה מה קורה אם אנו מאפשרים דרישת בלתי מוגבלת עבור כל מוצר, ואנו מאפשרים כמויות שליליות לייצור של כל סם. (באפשרותך לראות בעיה זו של Solver בערכים הקבועים אינם מתכנסים בגליון העבודה בקובץ Prodmix. xlsx.) כדי למצוא את הפתרון המיטבי למצב זה, פתח את Solver, לחץ על לחצן אפשרויות, ונקה את התיבה ' הנח שלילי '. בתיבת הדו Solver Parameters, בחר את אילוץ הביקוש D2: I2< = D8: I8 ולאחר מכן לחץ על מחק כדי להסיר את האילוץ. בעת לחיצה על ' פתור ', הפונקציה Solver מחזירה את ההודעה "הגדרת ערכי תא אינה מתכנסת". משמעות הדבר היא שאם תא היעד יהיה מוגדל (כמו בדוגמה שלנו), קיימים פתרונות אפשריים לערכי תא יעד גדול שרירותי. (אם תא היעד הוא ממוזער, ההודעה "הגדרת ערכי תא אינה מתכנסת" פירושו שקיימים פתרונות אפשריים לערכי תא יעד קטנים שאינם שרירותי.) במצב שלנו, על-ידי מתן אפשרות לייצור שלילי של תרופה, אנו משפיעים על "צור" משאבים שניתן להשתמש בהם להפקת כמויות גדולות ושרירותי של סמים אחרים. בהתחשב בביקוש בלתי מוגבל, הדבר מאפשר לנו ליצור רווחים ללא הגבלה. במצב ממשי, לא ניתן להרוויח סכום כסף אינסופי. בקצרה, אם תראה "Set values אינם מתכנסים", למודל שלך יש שגיאה.

  1. נניח שחברת התרופות שלנו יכולה לרכוש עד 500 שעות עבודה ב$1 יותר לשעה מאשר עלויות העבודה הנוכחיות. כיצד ניתן למקסם את הרווח?

  2. במפעל לייצור שבב, ארבעה טכנאים (a, B, C ו-D) מייצרים שלושה מוצרים (מוצרים 1, 2 ו-3). בחודש זה, יצרן השבב יכול למכור יחידות 80 של מוצר 1, 50 יחידות של מוצר 2, וברוב 50 של המוצר 3. טכנאי A יכול ליצור רק מוצרים 1 ו-3. טכנאי B יכול ליצור רק מוצרים 1 ו-2. טכנאי C יכול ליצור רק מוצר 3. הטכנאי D יכול ליצור רק מוצר 2. עבור כל יחידה המופקת, המוצרים תורמים את הרווח הבא: product 1, $6; מוצר 2, $7; ו-Product 3, $10. השעה (בשעות) כל טכנאי צריך לייצר מוצר הוא כדלקמן:

    מוצר

    טכנאי

    טכנאי B

    טכנאי מכש

    טכנאי

    1

    2

    2.5

    אין אפשרות לבצע

    אין אפשרות לבצע

    2

    אין אפשרות לבצע

    3

    אין אפשרות לבצע

    3.5

    3

    3

    אין אפשרות לבצע

    4

    אין אפשרות לבצע

  3. כל טכנאי יכול לעבוד עד 120 שעות בחודש. כיצד יכול יצרן הצ למקסם את הרווח החודשי שלו? נניח שניתן לייצר מספר שברים של יחידות.

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

    עכברים

    קלידים

    ג'ויסטיקים

    רווח/יחידה

    $8

    $11

    $9

    שימוש בעבודה/יחידה

    .2 ש'

    .3 שעות

    24 שעות

    זמן מחשב/יחידת

    .04 שעה

    .055 לשעה

    .04 שעה

    דרישה חודשית

    15,000

    27,000

    11,000

  5. מדי חודש, סך הכל 13,000 שעות עבודה ושעות 3000 של זמן מחשב זמינים. כיצד היצרן יכול למקסם את תרומתו הרווחית החודשית מהמפעל?

  6. פתור את דוגמה התרופה שלנו בהנחה שדרישה מינימלית של יחידות 200 לכל תרופה חייבת להתקיים.

  7. ג'ייסון מכין צמידי יהלומים, שרשראות ועגילים. הוא מעוניין לעבוד עד 160 שעות לכל חודש. יש לו אונקיות של 800 יהלומים. הרווח, זמן העבודה ואונקיות היהלומים הנדרשים להפקת כל מוצר ניתנים להלן. אם הביקוש לכל מוצר אינו מוגבל, כיצד ג'ייסון יכול למקסם את הרווח שלו?

    מוצר

    רווח בין יחידות

    שעות עבודה לכל יחידה

    אונקיות יהלומים לכל יחידה

    צמיד

    $300

    .35

    1.2

    שרשרת

    ₪200

    .15

    .75

    עגילים

    $100

    ‎.05

    .5

זקוק לעזרה נוספת?

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×