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

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

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

1.1.1.1 תאים משתנים

2.2.2.2 תא מוגבל

שלוש . תא יעד

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

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

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

   הערה

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

   

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

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

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

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

   1. בתיבת הדו-שיח Solver Parameters , בחר Add.

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

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

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

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

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

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

        הערה

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   עצה

   באפשרותך לשמור את הבחירות הבאות בתיבת הדו-שיח 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. בתיבה הגדר יעד, הזן הפניה לתא או שם עבור תא המטרה.

   הערה

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

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

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

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

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

   1. בתיבת הדו-שיח Solver Parameters , בחר Add.

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

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

      הערה

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

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

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

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

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

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

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

   

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

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

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

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

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

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

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

   

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

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

3. בחר אישור.

4. בתיבת הדו-שיח Solver Parameters , בחר Solve או Close.

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

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

   

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

   עצה

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

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

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

   

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

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

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