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

למצבים אלו (ורבים נוספים) תוכנת האקסל מספקת שני פתרונות – כלי החתירה למטרה (Goal Seek) וכלי ה- Solver.

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

כלי החתירה למטרה

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

ניתן לאתר את הכלי תחת לשונית Data – לחצו על הכפתור What-If Analysis, ובתפריט שנפתח לחצו על Goal Seek:

לצורך הדגמת השימוש בכלי, נתבונן בדוגמא הבאה:

בחנות הרהיטים שלנו מוכרים את הרהיטים הבאים – ספות, ארונות ושולחנות:

על מנת לסיים את השנה ברווח, על החנות להגיע למכירות של 1,250,430 ש”ח.

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

ראשית כל – נשים לב שעל מנת לעשות שימוש הן בכלי החתירה למטרה והן בכלי ה- Solver, אנו נצטרך לוודא שהתא שמכיל את היעד אליו אנו חותרים להגיע (במקרה שלנו מדובר בתא D6 הצבוע בכתום) מקיים את שני הכללים הבאים:

1. התא מכיל נוסחה

2. הנוסחה בתא מושפעת (באופן ישיר או עקיף) מהתא אותו נשנה  (במקרה שלנו – תא C4 הצבוע בצהוב – מחיר השולחנות) 

• הנוסחה בתא D6 במקרה שלנו הינה נוסחת SUM שסוכמת את המכירות מכל הפריטים. שימו לב שתא D4, אשר משפיע ישירות על הסכום המופיע ב- D6, מושפע בעצמו מהמכפלה של B4 (כמות שולחנות) ב- C4 (מחיר ליח’), ולכן ניתן להגיד שתא C4 משפיע על תא D6 ועומד בקריטריון שלנו.

בואו נראה כיצד עושים זאת באמצעות כלי החתירה למטרה:

1. ב- Set Cell נגדיר את התא שמכיל את התוצאה הסופית של החישוב – במקרה שלנו מדובר בתא D6. כפי שהגדרנו מקודם – תא D6 חייב להכיל נוסחה. הנוסחה בתא הזה הינה סכימה של כל המכירות מכל הפריטים שנמכרו.
2. ב- To Value נגדיר את הערך שאליו אנו שואפים להגיע – 1,250,430
3. ב- By Changing Cell נגדיר את התא שעל ידי שינויו אנו נגיע לתוצאה 1,250,430 – נלחץ על תא C4.
4. לאחר שנלחץ OK, נראה את התוכנה מתחילה לנחש את הערכים בתא C4, עד שנגיע לתוצאה הסופית:

ניתן לראות שעל ידי קביעת מחיר שולחן בודד ל- 4,252.15 – היעד של מכירות בסך 1,250,430 מושג בהצלחה.

שימו לב – בכלי החתירה למטרה ניתן לשנות בכל הרצה אך ורק תא בודד. 

שאלה: מה היה קורה אילו היינו מעוניינים שמחירו של שולחן יהיה מספר שלם (נניח 4,252 או 4,253)?

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

כלי ה- Solver

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

על מנת שנוכל להשתמש בכלי זה, ראשית ניגש ללשונית Data ונבדוק אם כלי ה- Solver מופיע:

במידה והכלי אינו מופיע, עלינו לאפשר אותו בתוכנה:

1. נלך ל- File – Options
2. נגש ללשונית Add Ins. ניתן לראות שתוסף ה- Solver שלנו (מודגש בכחול) נמצא כרגע במצב Disabled, ולכן עלינו לאפשר את השימוש בו:

3. בתחתית החלון נסמן ב- Manage את האופציה Excel Add-ins ונלחץ Go.

4. בתפריט שנפתח, נסמן V על ה- Solver Add In ונלחץ OK.

5. כעת תוסף ה- Solver שלנו מוכן לשימוש. נוכל למצוא אותו תחת לשונית Data:

פתרון בעיה בסיסית ב- Solver

כעת נחזור לדוגמא הקודמת שלנו, שבה נאלצנו למצוא את המחיר לשולחן שיביא אותנו להכנסות של 1,250,430.

כעת, נוסיף לבעיה שלנו אילוץ – אנו מעוניינים שהמחיר יהיה מספר שלם, ולכן התוצאה שקיבלנו מקודם – 4,252.15 – אינה מתאימה לנו.

בואו נבחן כיצד נראה ה- Solver:

מה רואים כאן?

1 – כאן נקבע את תא המטרה שלנו שיהווה בעצם התוצאה הסופית של החישוב

2 – מה אנו רוצים לעשות עם תא המטרה שלנו – להביאו למקסימום? למינימום? או לערך ספציפי מסוים?

3 – אילו תאים אנו רוצים לשנות כדי להשיג את התוצאה שלנו? ניתן לראות שניתן לשחק עם יותר מתא אחד, זאת בניגוד לכלי החתירה למטרה.

4 – כאן יופיעו האילוצים השונים שנבקש לכפות על המשתנים שלנו. שימו לב שניתן לכפות אילוצים על כל התאים שמשתתפים בחישוב – גם התאים שאנו מבקשים לשנות, וגם תא המטרה!

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

6 – שיטת הפתרון הרצויה – ישנן 3 שיטות מתמטיות שניתן להפעיל על מנת להגיע לפתרון. אנו נרחיב עליהן בהמשך, אך למי שקצר בזמן או פחות מתעניין בהסבר – אנו ממליצים לעבוד עם שיטת GRG Nonlinear שתהיה ברוב הזמן המתאימה ביותר עבור החישוב שלכם.

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

 

אז איך פותרים את הבעיה שלנו עם Solver?

1 – ראשית, נגדיר ב- Set Objective את ערך המטרה שלנו – תא D6

2 – כעת, עלינו להחליט מה לעשות עם ערך המטרה – האם לקבע אותו לערך מסוים? או לנסות להביאו למינימום/מקסימום?

האינסטיקנט הראשוני שלנו יהיה להגיד ל- Solver שתא D6 צריך להיות ל- 1,250,430. אך אליה וקוץ בה – במידה ונעשה כן, כלי ה- Solver לא יצליח להגיע לפתרון. הסיבה לכך נעוצה באילוץ שבחרנו לכפות על תא C4 כאשר ביקשנו שיהיה מספר שלם. כבר ראינו מקודם לכן שהתוצאה שמתקבלת בתא C4 הינה מספר לא שלם (4,252.15), ולכן לא ניתן לצפות שהפעם נגיע לפתרון אחר. נוכל לעקוף את הבעיה הזו על ידי כך שנבקש מתא D6 להיות מינימלי ככל הניתן, ובנוסף – נוסיף אילוץ נוסף שאומר שתא D6 חייב להיות גדול או שווה ל- 1,250,430.

3 – נגדיר ל- Solver את התא שברצוננו לשנות על מנת להגיע לתוצאה הרצויה – כמו בדוגמא הקודמת – נלחץ על תא C4.

4 – כעת הגענו לשלב המעניין, והוא הגדרת האילוצים. על מנת להוסיף אילוץ חדש נלחץ הכפתור Add. כך זה נראה:

ב- Cell Reference נזין את התא (או תאים) שעליהם נרצה לכפות את האילוץ

ברשימה הנפתחת ניתן לראות את האופציות השונות לאילוצים שמציע הסולבר:

=> קטן שווה

=   שווה

=< גדול שווה

בשלושת האילוצים הללו יש להזין ב- Constraint את המספר המבוקש

int – התא/תאים חייב להיות מספר שלם. נציג דוגמא לכך מיד.

bin – התא/תאים חייבים להיות בינאריים – או 1 או 0. נציג דוגמא לכך בהמשך.

dif – ה- N תא/תאים חייבים להיות כולם שונים אחד מהשני, להיות שלמים, וכן להיות בטווח של מ- 1 עד N. נניח שסימנו את התאים A1:A3 – יש לנו 3 תאים, לכן על שלושת התאים להכיל מספרים מ- 1 עד 3 (ושכמובן כל אחד מהתאים יהיה שונה).

בשלושת האילוצים הללו אין צורך להזין ב- constraint דבר לאחר שנבחר האילוץ

 

נחזור לדוגמא שלנו.

עלינו להגדיר שני אילוצים:

האילוץ הראשון: תא C4 צריך להיות מספר שלם. נעשה זאת על ידי שימוש באפשרות int בתפריט האילוצים שלנו.

האילוץ השני: תא D6 צריך להיות גדול או שווה למספר 1,250,430.

5 – נוודא שהשיטת הניחוש שבחרנו הינה GRG Nonlinear.

 

כך זה ייראה, רגע לפני ההרצה:

 

זהו, הכל מוכן – כעת נותר רק ללחוץ Solve!

והרי התוצאה לפניכם:

באופן לא מפתיע, התוצאה שהתקבלה היא 4,253 – שזה אכן הגיוני, שכן זהו המספר השלם הבא אחרי 4,252.15 🙂

כעת תוכלו לראות בתיבת דוח השיח שבאפשרותכם לשמור את הפתרון שהתקבל (ברירת מחדל) או לשחזר את הערכים המקוריים. בנוסף, במידה וברצונכם להריץ מחדש את החישוב מסיבה כלשהו – אם תסמנו V ב- Return to Solver Parameters Dialog זה ייקח אתכם חזרה.

 

ניחוש מספרים המרכיבים סכום מסוים – דוגמא לנפלאות כלי ה- Solver

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

כך זה נראה:

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

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

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

כך זה נראה בדוגמא שבה חלק מהפריטים שלנו מקבלים את הערך 1 וחלק מקבלים את הערך 0:

התוצאה שקיבלנו בחישוב הזה הינה 161. כעת נראה איך אנו מגיעים לתוצאה המדויקת – 241.

(שימו לב – לצורך הדוגמא מחקנו את ה- 1 וה- 0 שהוזנו בדוגמא הקודמת, אך זה נעשה מטעמי נוחות בלבד)

1 – ניכנס ל- Solver, נגדיר את תא D11 בתור התא שמכיל את התוצאה שלנו

2 – נורה ל- Solver להגיע לתוצאה 241

3 – שימו לב לטריק – אנו נבקש מה- Solver לשנות את טווח הערכים שנמצא בין C2 ל- C9 (או 0 או 1) כדי להחליט אם לכלול מוצר מסוים או שלא

4 – נוסיף אילוץ שכופה על C2 עד C9 להיות בינאריים (כלומר או 1 או 0). נעשה זאת דרך כפתור Add, ובחירת האפשרות bin.

נלחץ Solve ונראה כיצד הכלי מגיע לתוצאה הרצויה 🙂 :

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

Solver – הכלי המושלם (אם מודעים לחסרונותיו…)

על אף השמחה הרבה עקב הצלחת ה- Solver, מן הראוי להאיר את צדדים יותר “אפלים” בכלי הזה:

1. לפעמים יש יותר מפתרון אחד נכון – בדוגמא הקודמת קיבלנו את התוצאה המושלמת. אך האם היא בהכרח התוצאה הנכונה היחידה? מה היה קורה אילו היינו משנים את מחיר מכנס הג’ינס מ- 33 ל- 24? במקרה הנ”ל – Solver ייעצר בפתרון הראשון שיתקבל, אך לא יטרח לעדכן אותנו שיש אולי עוד אפשרות נכונה.
2. לפעמים אין אף פתרון אחד נכון – בדוגמא הקודמת ביקשנו התאמה מדויקת. ומה אם לא היינו מוצאים כזו? במקרה שכזה, היינו נאלצים במקרה הטוב לקבל הודעה מאכזבת שלא נמצא פתרון, או במקרה הרע – לראות בעיניים כלות כיצד ה- Solver ממשיך לרוץ ולרוץ. במידה וה- Solver משדר אותות מצוקה (לרוב ריצה של מעל דקה בבעיות פשוטות מעידה שאתם לא בכיוון), ניתן ללחוץ על Esc לעצירת הריצה. אם זה לא עוזר – ללחוץ Alt + Tab יכול לסייע. אם גם זה לא עוזר – חכו בסבלנות, בשלב מסוים ה- Solver יתייאש. ואם לא – תקוו ששמרתם מראש את הקובץ שלכם 🙂

דברים נוספים ששווה להכיר ב- Solver:

• סימון האפשרות Answer בתוך חלון ה- Reports בעת שמסיימים ריצה של Solver תפתח גיליון שמציג את פירוט החישובים שנעשו, ונתונים מעניינים נוספים (כמו “מחיר צל”, ניתן לראות על כך הרחבה בקישור הראשון שצירפתי בסוף המדריך)
• ניתן להיעזר ב- Solver גם על מנת להגיע לתוצאות שהן בקירוב המספר אליו שאפתם להגיע. בואו נניח ששינינו את מחיר משקפי השמש מ- 80 ל- 79.5. אם שאפתם להגיע לתוצאה 241 אך הניחוש הכי טוב היה 240.5 – זה לא ייחשב כפתרון לגיטימי. אלא אם כן, בעת קביעת הפרמטרים של ה- Solver, נלחץ על Options ליד ה- Solving Method, ונשחק עם ה- Constraint Precision. במקום 0.000001 (ברירת המחדל), בואו נשנה את הערך ל- 0.01 – כלומר נגמיש במעט את הדיוק הנדרש מהאילוצים שלנו… לאחר שנעשה זאת, נראה שהאקסל מציע לנו את התוצאה הבאה:

שימו לב – הקטנת ה- Constraint Precision עשויה לבוא על חשבון מציאת פתרון מדויק (לדוגמא – הקטנה ל- 0.01 בדוגמא של המספרים השלמים עשויה הייתה לתת לנו תוצאה לא מדויקת שכן מרגע שה- Solver מצא פתרון שעונה על הקריטריון דיוק שהוגדר לו הוא מפסיק לחפש!). לכן השתמשו בטריק הזה בזהירות וזכרו לאפס את המספר בתום השימוש ל- 0.000001 ! 

נ.ב. – איך ידעתי שצריך לשים 0.01 ולא 0.001? עניין של ניסוי וטעייה. או במילים אחרות, לא רק לסולבר מותר לנחש 🙂

 

מהו האלגוריתם הנכון בשבילי?

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

Simplex LP – מתאים רק לבעיות לינאריות. חיבור, חיסור, כפל – ידע לבצע. אם אתם רוצים לחשב חזקות או בעיות מורכבות – תשכחו מזה…

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

GRG Nonlinear – זהו הזוכה שלנו! אמנם הוא מוצא “רק” אופטימום מקומי, אבל הוא יודע לאכול בעיות לא לינאריות, ל99% מאיתנו זה יהיה מספיק טוב, וגם ככה אנחנו לא באמת מבינים מתמטיקה – אז רק נסתפק בזה שעבור רוב הבעיות זה יהיה האלגוריתם הכי יעיל והכי מתאים – הן מבחינת רמת הדיוק והן מבחינת זמן הריצה 🙂

קריאה נוספת

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

1. https://www.excel-easy.com/data-analysis/solver.html – מלבד ההסבר המקיף על הכלי – יש כאן דוגמאות מעניינות לשימוש שאפשר לעשות בכלי.
2. http://www.engineerexcel.com/excel-solver-solving-method-choose – הסבר על האלגוריתמים השונים ש- Solver עושה בהם שימוש.