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

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

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

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

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

לפני הערכת Solver

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

2. תא הגבלה

3. תא היעד

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

אחרי הערכת Solver

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

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

    תמונה של תיבת הדו Excel 2010 + Solver
  2. בתיבה Set Objective, הזן הפניה לתא או שם עבור תא המטרה. על תא המטרה להכיל נוסחה.

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

    • אם ברצונך שהערך בתא המטרה יהיה גדול ככל האפשר, לחץ על Max.

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

    • אם ברצונך שתא המטרה יקבל ערך מסוים, לחץ על Value of, ולאחר מכן הקלד את הערך בתיבה.

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

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

    1. בתיבת הדו-שיח Solver Parameters, לחץ על Add.

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

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

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

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

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

      • כדי לקבל את האילוץ ולחזור אל תיבת הדו-שיח Solver Parameters, לחץ על אישור.
        הערה    באפשרותך להחיל את קשרי הגומלין int, ‏bin ו- dif רק באילוצים שבתאים משתני החלטה.

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

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

    7. לחץ על שינוי ולאחר מכן בצע את השינויים או לחץ על מחק.

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

    • כדי לשמור את ערכי הפתרונות בגליון העבודה, בתיבת הדו-שיח Solver Results, לחץ על Keep Solver Solution.

    • כדי לשחזר את הערכים המקוריים שהופיעו לפני הלחיצה על Solve, לחץ על Restore Original Values.

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

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

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

  1. לאחר שהגדרת בעיה, לחץ על אפשרויות בתיבת הדו-שיח פרמטרים של Solver.

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

  3. בתיבת הדו-שיח פרמטרים של Solver, לחץ על פתור.

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

    • כדי לעצור את תהליך הפתרון ולהציג את תיבת הדו-שיח תוצאות של Solver, לחץ על עצור.

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

  1. בתיבת הדו-שיח פרמטרים של Solver, לחץ על אפשרויות.

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

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

  2. הזן טווח תאים עבור אזור המודל ולחץ על Save או על Load.

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

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

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

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

  • LP Simplex    משמש עבור בעיות ליניאריות.

  • אבולוציונית    השתמש עבור בעיות שאינן חלקות.

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

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

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

הסבר 1 תאים משתנים

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

הסבר 3  תא אובייקטיבי

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

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

  1. ב-Excel 2016 for Mac: לחץ על Data > Solver.

    Solver

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

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

  2. תחת Set אובייקטיבי, הזן הפניה לתא או name עבור התא האובייקטיבי.

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

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

    לשם

    בצע פעולה זו

    הפיכת הערך של התא האובייקטיבי לגדול ככל האפשר

    לחץ על Max.

    הפיכת הערך של התא האובייקטיבי לקטן ככל האפשר

    לחץ על Min.

    הגדרת התא האובייקטיבי לערך מסוים

    לחץ על ערךולאחר מכן הקלד את הערך בתיבה.

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

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

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

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

    1. בתיבת הדו-שיח Solver Parameters, לחץ על Add.

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

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

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

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

    לשם

    בצע פעולה זו

    קבל את האילוץ והוסף עוד

    לחץ על הוסף.

    קבלת האילוץ וחזרה לתיבת הדו ' פרמטרים של Solver '

    לחץ על אישור.

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

    לשם

    בצע פעולה זו

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

    לחץ על ' שמור על פתרון Solver ' בתיבת הדו ' תוצאות solver '.

    שחזור הנתונים המקוריים

    לחץ על שחזר ערכיםמקוריים.

הערות: 

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

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

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

  1. ב-Excel 2016 for Mac: לחץ על Data > Solver.

    Solver

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

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

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

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

  4. בתיבת הדו-שיח פרמטרים של Solver, לחץ על פתור.

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

    לשם

    בצע פעולה זו

    הפסקת תהליך הפתרון והצגת תיבת הדו ' תוצאות Solver '

    לחץ על עצור.

    המשך תהליך הפתרון והצגת פתרון הניסיון הבא

    לחץ על Continue.

  1. ב-Excel 2016 for Mac: לחץ על Data > Solver.

    Solver

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

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

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

    לשם

    בצע פעולה זו

    הגדרת זמן ואיטראציות של פתרונות

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

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

    הגדרת מידת הדיוק

    בכרטיסיה All שיטות , בתיבה דיוק אילוץ , הקלד את מידת הדיוק הרצויה. ככל שהמספר קטן יותר, כך הדיוק גבוה יותר.

    הגדרת מידת ההתכנסות

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

  3. לחץ על אישור.

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

  1. ב-Excel 2016 for Mac: לחץ על Data > Solver.

    Solver

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

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

  2. לחץ על ' טען/שמור', הזן טווח תאים עבור אזור המודל ולאחר מכן לחץ על ' שמור ' או ' טען'.

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

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

  1. ב-Excel 2016 for Mac: לחץ על Data > Solver.

    Solver

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

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

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

פתרון שיטה

תיאור

GRG (מילוי הדרגתי מצומצם) לא לינארי

בחירת ברירת המחדל, עבור מודלים המשתמשים ברוב הפונקציות של Excel, מלבד IF, בחירה, בדיקת מידע ופונקציות "step" אחרות.

LP

השתמש בשיטה זו לבעיות תיכנות ליניארי. המודל שלך אמור להשתמש ב-SUM, SUMPRODUCT, +-ו-* בנוסחאות התלויות בתאים המשתנים.

אבולוציונית

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

הערה: חלקים מקוד התוכנית של Solver מוצגים כזכויות יוצרים של 1990-2010 על-ידי מערכות כניסה, Inc. Ports הם זכויות יוצרים 1989 על-ידי שיטות אופטימליות, Inc.

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

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

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

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

מערכות כיתה,
Inc. P.O. Box 4288
שיפוע הכפר, NV
89450-4288 (775) 831-0300
אתר אינטרנט: http://www.solver.com
דואר אלקטרוני:
העזרה של info@solver.com solver ב-www.solver.com.

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

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

ניתן לשאול תמיד מומחה ב- קהילת Tech Community עבור Excel, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- User Voice עבור Excel.

למידע נוסף

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

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

מבוא לניתוח 'מה-אם'

מבט כולל על נוסחאות ב- Excel

כיצד להימנע מנוסחאות שגויות

זיהוי שגיאות בנוסחאות

קיצורי מקשים ב- Excel 2016 עבור Windows

קיצורי מקלדת ב- Excel 2016 עבור Mac

פונקציות של Excel (בסדר אלפביתי)

פונקציות של Excel (לפי קטגוריה)

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

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

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

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

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

×