הגדרה ופתרון של בעיה באמצעות Solver

דוגמה של הערכת Solver

בדוגמה שלפניך, רמת הפרסום בכל רבעון משפיעה על מספר היחידות שנמכרו, וכך קובעת באופן עקיף את שיעור רווח המכירות, את ההוצאות הנלוות ואת הרווח. ל- Solver יש אפשרות לשנות את התקציבים הרבעוניים עבור הפרסום (התאים של משתני ההחלטה B5:C5) עד למגבלת תקציב כולל של $20,000 (תא מטרה F5), עד שהערך של הרווח הכולל (תא מטרה F7) יגיע לסכום המרבי האפשרי. הערכים בתאים הניתנים לשינוי משמשים לחישוב הרווח בכל רבעון, ולכן הם קשורים לנוסחה של תא המטרה F7, =SUM(Q1 Profit:Q2 Profit).

1. תאים הניתנים לשינוי

2. תא הגבלה

3. תא מטרה

לאחר ש- Solver יפעל, הערכים החדשים יהיו כדלקמן.

הגדרה ופתרון של בעיה

1. בכרטיסיה נתונים , בקבוצה ניתוח , בחר Solver.
   

   הערה

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

   

2. בתיבה 'הגדרת מטרה ', הזן הפניה לתא או שם עבור תא המטרה. על תא המטרה להכיל נוסחה.

3. בצע אחד מהשלבים הבאים.

   • אם ברצונך שהערך בתא המטרה יהיה גדול ככל האפשר, בחר מקסימום.
   • אם ברצונך שהערך בתא המטרה יהיה קטן ככל האפשר, בחר Min.
   • אם ברצונך שתא המטרה יקבל ערך מסוים, בחר Value of ולאחר מכן הקלד את הערך בתיבה.
   • בתיבה By Changing Variable Cells הזן שם או הפניה עבור כל טווח של תאים משתני החלטה. הפרד הפניות שאינן סמוכות זו לזו באמצעות פסיק. על התאים הניתנים לשינוי להיות מקושרים באופן ישיר או לא ישיר לתא המטרה. באפשרותך לציין עד 200 תאים ניתנים לשינוי.

4. בתיבה כפוף לאילוצים , הזן את כל האילוצים שברצונך להחיל על-ידי ביצוע השלבים הבאים.

   1. בתיבת הדו-שיח Solver Parameters , בחר 'הוסף'.

   2. בתיבה Cell Reference, הזן את ההפניה לתא או את השם של טווח התאים שעבורו ברצונך להפעיל אילוץ על הערך.

   3. בחר את קשר הגומלין ( <=, =, >=, int, bin, אוdif ) הרצוי בין התא שאליו מתבצעת הפניה לבין האילוץ. אם תבחר int, מספר שלם יופיע בתיבה Constraint . אם תבחר bin, המילה binary תופיע בתיבה Constraint . אם תבחר dif, המילה alldifferent תופיע בתיבה Constraint .

   4. אם תבחר <=, = או >= עבור הקשר בתיבה Constraint , הקלד מספר, הפניה לתא או שם של תא, או נוסחה.

   5. בצע אחד מהשלבים הבאים.

      • כדי לקבל את האילוץ ולהוסיף אחר בחר 'הוסף'.

      • כדי לקבל את האילוץ ולחזור לתיבת הדו-שיח Solver Parameters, בחר OK.

        הערה

        באפשרותך להחיל את קשרי הגומלין int, bin ו - dif רק באילוצים על תאים משתני החלטה.

   6. באפשרותך לשנות או למחוק אילוץ קיים על-ידי ביצוע הפעולות הבאות.

      • בתיבת הדו-שיח Solver Parameters , בחר את האילוץ שברצונך לשנות או למחוק.
      • בחר שנה ולאחר מכן בצע את השינויים או בחר מחק.

5. בחר 'פתור ובצע אחת מהפעולות הבאות'.

   • כדי לשמור את ערכי הפתרונות בגליון העבודה, בתיבת הדו-שיח Solver Results , בחר Keep Solver Solution.
   • כדי לשחזר את הערכים המקוריים לפני שבחרת Solve, בחר Restore Original Values.
   • באפשרותך להפסיק את תהליך הפתרון על-ידי הקשה על Esc. Excel יחשב מחדש את גליון העבודה באמצעות הערכים האחרונים שהוא מצא עבור התאים משתני ההחלטה.
   • כדי ליצור דוח המבוסס על הפתרון שלך לאחר ש- Solver מצא פתרון, בחר סוג דוח בתיבה 'דוחות ' ולאחר מכן בחר 'אישור'. הדוח נוצר בגליון עבודה חדש בחוברת העבודה. אם Solver לא מצא פתרון, יהיו זמינים דוחות מסוימים בלבד או שלא יהיו דוחות זמינים כלל.
   • כדי לשמור את הערכים של התאים משתני ההחלטה שלך כתרחיש שתוכל להציג מאוחר יותר, בחר באפשרות שמירת תרחיש בתיבת הדו-שיח Solver Results ולאחר מכן הקלד שם עבור התרחיש בתיבה Scenario Name .

התקדמות הדרגתית עבור פתרונות של נסיונות ב- Solver

1. לאחר שהגדרת בעיה, בחר 'אפשרויות ' בתיבת הדו-שיח Solver Parameters .

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

3. בתיבת הדו-שיח Solver Parameters , בחר פתרון.

4. בתיבת הדו-שיח 'הצג פתרון של ניסיון ', בצע אחת מהפעולות הבאות.

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

שינוי האופן בו Solver מחפש פתרונות

1. בתיבת הדו-שיח Solver Parameters , בחר אפשרויות.
2. בחר או הזן ערכים עבור אפשרויות בכרטיסיות All Methods,‏ GRG Nonlinear ו- Evolutionary בתיבת הדו-שיח.

שמירה או הטענה של מודל בעיה

1. בתיבת הדו-שיח Solver Parameters , בחר Load/Save.

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

   עצה

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

שיטות לפתרון בעיות המשמשות את Solver

באפשרותך לבחור בכל אחד משלושת האלגוריתמים שלהלן או בשיטות לפתרון בעיות בתיבת הדו-שיח Solver Parameters .

• שיפוע מופחת כללי (GRG) לא ליניארי: משמש עבור בעיות חלקות ושאינן ליניאריות.
• LP Simplex: משמש עבור בעיות ליניאריות.
• אבולוציוני: משמש עבור בעיות שאינן חלקות.

דוגמה של הערכת Solver

בדוגמה שלפניך, רמת הפרסום בכל רבעון משפיעה על מספר היחידות שנמכרו, וכך קובעת באופן עקיף את שיעור רווח המכירות, את ההוצאות הנלוות ואת הרווח. ל- Solver יש אפשרות לשנות את התקציבים הרבעוניים עבור הפרסום (התאים של משתני ההחלטה B5:C5) עד למגבלת תקציב כולל של $20,000 (תא מטרה D5), עד שהערך של הרווח הכולל (תא מטרה D7) יגיע לסכום המרבי האפשרי. הערכים בתאים הניתנים לשינוי משמשים לחישוב הרווח בכל רבעון, ולכן הם קשורים לנוסחה של תא המטרה D7, =SUM(Q1 Profit:Q2 Profit).

תאים הניתנים לשינוי

תא הגבלה

תא מטרה

לאחר ש- Solver יפעל, הערכים החדשים יהיו כדלקמן.

הגדרה ופתרון של בעיה

1. בחרבאפשרות 'פותרנתונים>'.

   

2. תחת Set Objective, הזן הפניה לתא או שם עבור תא המטרה.

   הערה

   על תא המטרה להכיל נוסחה.

3. בצע אחד מהשלבים הבאים.

   לשם	לבצע את הפעולה
   הפיכת הערך של תא המטרה לגדול ככל האפשר	בחר מקסימום.
   הפיכת הערך של תא המטרה לקטן ככל האפשר	בחר Min.
   הגדרת תא המטרה לערך מסוים	בחר Value Of ולאחר מכן הקלד את הערך בתיבה.

4. בתיבה By Changing Variable Cells הזן שם או הפניה עבור כל טווח של תאים משתני החלטה. הפרד הפניות שאינן סמוכות זו לזו באמצעות פסיק.
   על התאים הניתנים לשינוי להיות מקושרים באופן ישיר או לא ישיר לתא המטרה. באפשרותך לציין עד 200 תאים ניתנים לשינוי.

5. בתיבה כפוף לאילוצים , הוסף את כל האילוצים שברצונך להחיל.
   כדי להוסיף אילוץ, בצע שלבים אלה.

   1. בתיבת הדו-שיח Solver Parameters , בחר 'הוסף'.

   2. בתיבה Cell Reference, הזן את ההפניה לתא או את השם של טווח התאים שעבורו ברצונך להפעיל אילוץ על הערך.

   3. בתפריט המוקפץ <= קשר גומלין, בחר את קשר הגומלין הרצוי בין התא שאליו מתבצעת הפניה לבין האילוץ. אם תבחר <=, = או >=, בתיבה Constraint , הקלד מספר, הפניה לתא או שם של תא, או נוסחה.

      הערה

      באפשרותך להחיל את קשרי הגומלין int, bin ו - dif רק באילוצים על תאים משתני החלטה.

   4. בצע אחת מהפעולות הבאות.

   לשם	לבצע את הפעולה
   קבל את האילוץ והוסף אחר	בחר הוסף.
   קבל את האילוץ וחזור אל תיבת הדו-שיח Solver Parameters	בחר אישור.

6. בחר 'פתור' ולאחר מכן בצע אחת מהפעולות הבאות.

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

התקדמות הדרגתית עבור פתרונות של נסיונות ב- Solver

1. בחרבאפשרות 'פותרנתונים>'.

   

2. לאחר שהגדרת בעיה, בתיבת הדו-שיח Solver Parameters , בחר אפשרויות.

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

4. בתיבת הדו-שיח Solver Parameters , בחר פתרון.

5. בתיבת הדו-שיח 'הצג פתרון של ניסיון ', בצע אחת מהפעולות הבאות.

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

שינוי האופן בו Solver מחפש פתרונות

1. בחרבאפשרות 'פותרנתונים>'.

   

2. בחר אפשרויות ולאחר מכן, בתיבת הדו-שיח אפשרויות או אפשרויות Solver , בחר אחת או יותר מהאפשרויות הבאות:

   לשם	לבצע את הפעולה
   הגדרת זמן ואיטרציות עבור פתרון	בכרטיסיה כל השיטות תחת מגבלות פתרון בתיבה זמן מרבי (שניות), הקלד את מספר השניות שברצונך לאפשר בזמן הפתרון. לאחר מכן, בתיבה 'איטראציות ', הקלד את המספר המרבי של איטרציות שברצונך לאפשר. הערה: אם תהליך הפתרון מגיע לזמן המרבי או למספר האיטרציות המרבי לפני ש- Solver מוצא פתרון, Solver מציג את תיבת הדו-שיח Show Trial Solution .
   הגדרת מידת הדיוק	בכרטיסיה כל השיטות בתיבה דיוק אילוץ , הקלד את מידת הדיוק הרצויה. ככל שהמספר קטן יותר, כך רמת הדיוק גבוהה יותר.
   הגדרת מידת ההתכנסות	בכרטיסיה GRG Nonlinear או Evolutionary , בתיבה Convergence , הקלד את מידת השינוי היחסי שברצונך לאפשר בחמש האיטראציות האחרונות לפני ש- Solver יפסיק עם פתרון. ככל שהמספר קטן יותר, כך מותר שינוי יחסי פחות.

3. בחר אישור.

4. בתיבת הדו-שיח Solver Parameters , בחר 'פתור או סגור'.

שמירה או הטענה של מודל בעיה

1. בחרבאפשרות 'פותרנתונים>'.

   

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

   עצה

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

שיטות לפתרון בעיות המשמשות את Solver

1. בחרבאפשרות 'פותרנתונים>'.

   

2. בתפריט המוקפץ בחר שיטת פתרון , בחר אחת מהאפשרויות הבאות:

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

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