קורס אקסל – Solver

מה זה solver?

מאתר התמיכה של מיקרוסופט:

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

תרגום:

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

ההגדרות, אותן ניתן להזין ל SOLVER הן:

1. תא המטרה – התא בו נרצה לראות את התשובה שנגדיר בסעיף 2 – תא זה נקשר לתאים אחרים בגיליון (ראה בסעיף 3).
2. ערך נדרש – הערך המבוקש או הערך אופטימלי (מקסימום או מינימום) – הערך שידוע לנו או הערך אותו נרצה לקבל בסוף.
3. תאי נוסחה אחרים בגיליון – סימון התאים בגיליון המשפיעים על התוצאה המבוקשת -התאים שהתשובה בהם תינתן ע"י ה Solver
4. אילוצים – כלומר, איזה מגבלות חלות על הנתונים כדי להגיע לתוצאה. האילוצים האפשריים הם:
   1. תא מסוים גדול או שווה ל X
   2. תא מסוים קטן או שווה ל X
   3. תא מסוים שווה ל X
   4. תא מסוים שונה ל X
   5. מספר שלם
   6. בינארי
5. שיטת הפתרון, ישנן שלוש מטודות סטטיסטיות שה Solver מאפשר לפתרון הבעיה:
   1. GRG Nonliniar
   2. Simplex LP
   3. Evolutionary

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

שלב ראשון, ללחוץ על כרטיסיית הקובץ > אפשרויות > תוספות:

בתחתית החלון שיפתח יש לוודא שהאפשרות שמופיעה היא: "תוספות של Excel"

וכעת יש ללחוץ על ביצוע

יפתח לנו חלון נוסף ובו נסמן את התוסף שיש להטעין – במקרה זה ה Solver כמובן ונלחץ על אישור.

לאחר שהטענו את ה Solver לאקסל, נוכל לראות אותו בכרטיסיית הנתונים:

כיצד להשתמש ב Solver?

דוגמא ראשונה

נתונים:

דני ונורית היו באותו גן.

לדני היו עשרה תפוחים.

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

ע"פ המידע שניתן ידוע (האילוצים):

1. שנורית אכלה התפוח –

— מכאן שהיה לה לפחות תפוח אחד (ומכיוון שיש שלושה בתים לשיר) אזי נורית קיבלה יותר מ 3 תפוחים.

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

— מכאן שלדני היה מספר שלם של תפוחים.

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

— מכאן שהיו לו לפחות 6 תפוחים.

המידע המבוקש:

1. כמה תפוחים נשארו לדני?
2. כמה תפוחים דני נתן לנורית?

פתרון:

ראשית, הכנסתי את שלושת הנתונים:

דני ונורית, כלומר תאים: C2:C3 – הם התאים אותם אכניס בהגדרה ה 3, תזכורת *התאים בגיליון המשפיעים על התוצאה המבוקשת.

(ניתן לראות שכרגע הם 0 – כי אינני יודע מה התשובה הנכונה), ואת תאים אלו בדיוק ה Solver יפתור

המלצה: כדי להקל על הכנסת האילוצים, כדאי לתת שמות לתאים המשתנים

א"כ, לתא C2 אקרא: dani ; ולתא C3 אקרא: nurit

בתא C6 אותו נשמש להיות "תא המטרה" כתבתי את הנוסחה: dani+nurit=
כדי לקשר לתאים שישתנו.

וכעת נראה כיצד זה נכתב ב Solver:

אם כן:

1. תא המטרה – התא שמחבר את התוצאה של דני ונורית.
2. ערך נדרש – הערך המבוקש הוא 10.
3. תאי נוסחה אחרים בגיליון – סימון התאים של דני ונורית.
   התשובה בהם כאמור, תינתן ע"י ה Solver.
4. אילוצים –
   במקרה זה הם:
   1. לדני היה מספר שלם של תפוחים.
   2. לדני היה לפחות שישה תפוחים.
   3. נורית קיבלה יותר משלושה תפוחים.
5. שיטת הפתרון.

בסיום הכנסת הנתונים נלחץ על הפקודה פתור – solve  , נקבל מיד את התוצאות במקום הנתונים שלנו:

ויפתח החלון הבא:

ה Solver "מצהיר" שהוא מצא פתרון.

יש אפשרות לבחור (לאחר שראינו את ההצעה שלו) – האם להשאיר את הפתרון שלו או – להחזיר את התוצאות שלנו

בסיום התהליך יש אפשרות לשמור את התרחיש:

דוגמא שנייה

סוחר קנה 10 בקבוקי יין משני סוגים.

* מחיר בקבוק מהסוג הראשון היה 20 שקלים

* ומחיר בקבוק מהסוג השני היה 25 שקלים.

סה"כ שילם הסוחר 220 שקלים.

כמה בקבוקים מכל סוג קנה הסוחר?

לאחר הצבת הנתונים להלן ה Solver