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

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

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

Windows macOS אינטרנט



הערה: גירסאות של Solver לפני Excel 2007, שנקרא תא המטרה "תא היעד", ותאים של משתני החלטה כ"תאים משתנים" או "תאים ניתנים להתאמה". שיפורים רבים עשויים לתוספת Solver עבור Excel 2010, כך שאם אתה משתמש ב- Excel 2007, החוויה שלך תהיה מעט שונה.

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

לפני הערכת Solver

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

2. תא הגבלה

3. תא היעד

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

אחרי הערכת Solver

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

הערה: אם הפקודה פותר או הקבוצה ניתוח אינן זמינות, עליך להפעיל את התוספת‏ Solver‏. ראה: כיצד להפעיל את התוספת Solver.
בתיבה Set Objective, הזן הפניה לתא או שם עבור תא המטרה. על תא המטרה להכיל נוסחה.
בצע אחת מהפעולות הבאות:
- אם ברצונך שהערך בתא המטרה יהיה גדול ככל האפשר, לחץ על Max.
- אם ברצונך שהערך בתא המטרה יהיה קטן ככל האפשר, לחץ על Min.
- אם ברצונך שתא המטרה יקבל ערך מסוים, לחץ על Value of, ולאחר מכן הקלד את הערך בתיבה.
- בתיבה By Changing Variable Cells הזן שם או הפניה עבור כל טווח של תאים משתני החלטה. הפרד את ההפניות שאינן סמוכות באמצעות פסיקים. על התאים הניתנים לשינוי להיות מקושרים באופן ישיר או לא ישיר לתא המטרה. באפשרותך לציין עד 200 תאים ניתנים לשינוי.
בתיבה Subject to the Constraints, הזן את כל האילוצים שברצונך להחיל על-ידי ביצוע הפעולות הבאות.
1. בתיבת הדו-שיח Solver Parameters, לחץ על Add.
2. בתיבה Cell Reference, הזן את ההפניה לתא או את השם של טווח התאים שעבורו ברצונך להפעיל אילוץ על הערך.
3. לחץ על קשר הגומלין(<= , =, >=, int, bin, או dif ) הרצויים בין התא שאליו בוצעה הפניה לבין האילוץ. אם תלחץ על int, מספר שלם יופיע בתיבה אילוץ. אם תלחץ על סל, בינארי יופיע בתיבה אילוץ. אם תלחץ על dif, alldifferent יופיע בתיבה אילוץ.
4. אם תבחר <=, =, או >= עבור הקשר בתיבה Constraint, הקלד מספר, הפניה לתא או שם של תא, או נוסחה.
5. בצע אחת מהפעולות הבאות:
  - כדי לקבל את האילוץ ולהוסיף אחר לחץ על הוספה.
  - כדי לקבל את האילוץ ולחזור אל תיבת הדו-שיח Solver Parameters, לחץ על אישור.
    הערה באפשרותך להחיל את קשרי הגומלין int, ‏bin ו- dif רק באילוצים שבתאים משתני החלטה.
    
    באפשרותך לשנות או למחוק אילוץ קיים על-ידי ביצוע הפעולות הבאות:
6. בתיבת הדו-שיח Solver Parameters, לחץ על האילוץ שברצונך לשנות או למחוק.
7. לחץ על שינוי ולאחר מכן בצע את השינויים או לחץ על מחק.
לחץ על פתור ובצע אחת מהפעולות הבאות:
- כדי לשמור את ערכי הפתרונות בגליון העבודה, בתיבת הדו-שיח Solver Results, לחץ על Keep Solver Solution.
- כדי לשחזר את הערכים המקוריים שהופיעו לפני הלחיצה על Solve, לחץ על Restore Original Values.
- באפשרותך להפסיק את תהליך הפתרון על-ידי הקשה על Esc. ‏Excel יחשב מחדש את גליון העבודה באמצעות הערכים האחרונים שנמצאו עבור התאים משתני ההחלטה.
- כדי ליצור דוח המבוסס על הפתרון שלך לאחר ש- Solver מצא פתרון, באפשרותך ללחוץ על סוג דוח בתיבה Reports ולאחר מכן ללחוץ על OK. הדוח נוצר בגליון עבודה חדש בחוברת העבודה. אם Solver לא מצא פתרון, יהיו זמינים דוחות מסוימים בלבד או שלא יהיו דוחות זמינים כלל.
- כדי לשמור את הערכים של התאים משתני ההחלטה שלך כתרחיש שתוכל להציג מאוחר יותר, לחץ על שמירת תרחיש בתיבת הדו-שיח Solver Results ולאחר מכן הקלד שם עבור התרחיש בתיבה Scenario Name.

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

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

בתיבת הדו-שיח Solver Parameters, לחץ על Load/Save.
הזן טווח תאים עבור אזור המודל ולחץ על Save או על Load.

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

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

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

Generalized Reduced Gradient (GRG) Nonlinear משמש עבור בעיות חלקות ושאינן ליניאריות.
LP Simplex משמש עבור בעיות ליניאריות.
Evolutionary השתמש עבור בעיות שאינן חלקות.

חשוב: תחילה עליך להפוך את התוספת Solver לזמינה. לקבלת מידע נוסף, ראה טעינת התוספת Solver.

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

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

הסבר 1 משתנים

הסבר 2 תא מוגבל

הסבר 3 'מטרה'

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

הערכה לדוגמה של Solver עם ערכים חדשים

ב- Excel 2016 עבור Mac: לחץ על >Solver.

ב- Excel עבור Mac 2011: לחץ על הכרטיסיה נתונים, תחת ניתוח, לחץ על Solver.
ב- Set Objective, הזן הפניה לתא או שם עבור תא המטרה.

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

בצע אחת מהפעולות הבאות:

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

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

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

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

כדי להוסיף אילוץ, בצע את הפעולות הבאות:

בתיבת הדו-שיח Solver Parameters, לחץ על Add.
בתיבה Cell Reference, הזן את ההפניה לתא או את השם של טווח התאים שעבורו ברצונך להפעיל אילוץ על הערך.
בתפריט המוקפץ<= קשר גומלין, בחר את קשר הגומלין הרצוי בין התא שאליו בוצעה הפניה לבין האילוץ. אם תבחר <=, =, או> =, בתיבה אילוץ, הקלד מספר, הפניה לתא או שם או נוסחה.

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

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

לחץ על פתורולאחר מכן בצע אחת מהפעולות הבאות:

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

הערות:

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

ב- Excel 2016 עבור Mac: לחץ על >Solver.

ב- Excel עבור Mac 2011: לחץ על הכרטיסיה נתונים, תחת ניתוח, לחץ על Solver.
לאחר הגדרת בעיה, בתיבת הדו-שיח Solver Parameters, לחץ על אפשרויות.
בחר בתיבת הסימון הצג תוצאות איטרציה כדי לראות את הערכים של כל פתרון ניסיון ולאחר מכן לחץ על אישור.
בתיבת הדו-שיח פרמטרים של Solver, לחץ על פתור.

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

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

ב- Excel 2016 עבור Mac: לחץ על >Solver.

ב- Excel עבור Mac 2011: לחץ על הכרטיסיה נתונים, תחת ניתוח, לחץ על Solver.

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

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

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

ב- Excel 2016 עבור Mac: לחץ על >Solver.

ב- Excel עבור Mac 2011: לחץ על הכרטיסיה נתונים, תחת ניתוח, לחץ על Solver.
לחץ על טען/שמור, הזן טווח תאים עבור אזור המודל ולאחר מכן לחץ על שמור או על טען.

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

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

ב- Excel 2016 עבור Mac: לחץ על >Solver.

ב- Excel עבור Mac 2011: לחץ על הכרטיסיה נתונים, תחת ניתוח, לחץ על Solver.
בתפריט המוקפץ בחר שיטת פתרון, בחר אחת מהאפשרויות הבאות:

שיטת פתרון	תיאור
GRG (הדרגתי מופחת כללי) לא ליניארי	בחירת ברירת המחדל, עבור מודלים המשתמשים ברוב Excel הפונקציות האחרות מלבד הפונקציות IF , CHOOSE , LOOKUP ופונקציות "step" אחרות.
Simplex LP	השתמש בשיטה זו עבור בעיות תיכנות ליניאריות. המודל שלך אמור להשתמש בנוסחאות SUM , SUMPRODUCT , + - ו- * בנוסחאות התלויות בתאי המשתנה.
Evolutionary	שיטה זו, המבוססת על אלגוריתמים גנטיים, היא הטובה ביותר כאשר המודל שלך משתמש ב- IF, CHOOSE או LOOKUP עם ארגומנטים התלויים בתאים המשתנה.

הערה: חלקים מקוד התוכנית Solver הם זכויות יוצרים 1990-2010 על-ידי Frontline Systems, Inc. חלקים הם זכויות יוצרים 1989 על-ידי Optimal Methods, Inc.

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

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

עזרה נוספת אודות השימוש ב- Solver

לקבלת עזרה מפורטת יותר עבור איש הקשר של Solver:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
אתר אינטרנט: http://www.solver.com
דואר אלקטרוני: info@solver.com
Solver help at www.solver.com.

חלקים של קוד תוכנית Solver הם זכויות יוצרים 1990-2009 של Frontline Systems, Inc. חלקים הם זכויות יוצרים 1989 של Optimal Methods, Inc.