מה זה SOLVER

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

הפעלת התוסף

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

לאחר ההפעלה, ה- Solver יופיע בכרטיסיית 'נתונים':

מבנה חלון SOLVER

בעת הפעלת הכלי, נפתח חלון עם שלושה אזורים עיקריים:

1. Set Objective – התא שבו נמצא הערך שאותו נרצה למקסם, למזער או לקבוע לערך מסוים. זהו התא שמכיל את הנוסחה שמחושבת על פי הנתונים שהזנו.
2. By Changing Variable Cells – התאים שה-Solver רשאי לשנות כדי להגיע למטרה.
3. Subject to the Constraints – רשימת המגבלות שמחייבות את הפתרון, למשל, ערכים חיוביים בלבד, מספרים שלמים וכד'.

סוגי פתרון

ה-Solver מציע שלוש שיטות חישוב:
GRG Nonlinear – לבעיות לא לינאריות עם פונקציות רציפות.
Simplex LP – לבעיות לינאריות קלאסיות, שמשתמשות ב-4 פעולות החשבון (ללא חזקות, סינוס וכד')
Evolutionary – לבעיות לא לינאריות מורכבות או עם פונקציות לא רציפות.
בחירת השיטה תלויה באופי הבעיה ובקשרים בין הנתונים.
לכל שיטה תוכלו להוסיף הגדרות ומגבלות, בלחיצה על הכפתור Options.

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

ועכשיו, לפרקטיקה:

נניח שיש לנו שני מוצרים, כאשר הרווח עבור המוצר הראשון הוא 40 ₪ ליחידה, שעות המכונה הנדרשות הן 2 ושעות האדם הנדרשות לייצור המוצר -1.
אנחנו מבקשים לייצר מקסימום של 100 יחידות מהמוצר.
הרווח עבור המוצר השני הוא 36 ₪ ליחידה, עבור יצורו נדרשות 2 שעות מכונה, ושעת אדם אחת,
ואנחנו מבקשים לייצר מקסימום של 120 יחידות מהמוצר.
מבחינת המגבלות, אנחנו יכולים להפעיל את המכונה ל 160 שעות לכל היותר, ולהשתמש ב 150 שעות אדם לכל היותר, עבור שני המוצרים יחד.
בואו נראה את הנתונים בטבלה:

אני מבקשת לדעת כמה יחידות עליי לייצר ממוצר 1 וכמה ממוצר 2, כדי להגיע לרווח המקסימלי, בהתאם למגבלות.
לצורך כך בניתי טבלת אקסל, שבה הזנתי את הכמויות, שכרגע עומדות על 0
והוספתי חישובים עבור סה"כ שעות המכונה וסה"כ שעות האדם הנדרשות עבור הכמויות שמצוינות בתאים F2 ו-G2.
את סה"כ הרווח חישבתי על ידי הכפלת הכמות ברווח ליחידה:

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

אז איך עושים את זה?

בכרטיסיית 'נתונים' נלחץ על Solver
ואלה הנתונים שנזין:

Set Objective – נבקש להציג את הערך המקסימלי בתא F6 (כלומר – מהו הרווח המקסימלי שאנחנו יכולים להשיג)
By Changing Variable Cells – על ידי שינוי הכמויות שנמצאות בתאים F2 ו-G2
תוך כדי שאנחנו לוקחים בחשבון את המגבלות (Subject to the Constraints) .
כדי להוסיף מגבלה נלחץ על Add. תיפתח החלונית הבאה להזנת המגבלות:

בדוגמה לעיל, ביקשנו שהערך בתא F2 שמכיל את הכמות לייצור מוצר 1 יהיו נמוכות מהערך שבתא B5, שהוא הייצור המקסימלי הנדרש, ובמקרה שלנו – 100.
המגבלות הנוספות:
הכמות לייצור מוצר 2 תהיה נמוכה או שווה לערך שמוזן עבור הייצור המקסימלי הנדרש עבור מוצר 2, ובמקרה שלנו – המות לייצור מוצר 2 תהיה נמוכה מ-120
סה"כ שעות המכונה ושעות האדם, המחושבים בתאים F3 ו- F4 יהיו נמוכים מהמגבלות שרשומות בתאים B7 ו- B8 בהתאמה,
כמו כן, הכמויות צריכות להיות מספר שלם.
סימנתי V על האפשרות Make Unconstrained Variables Non-Negative, כדי לוודא שהכמויות לא יהיו שליליות (חסך לי הוספת מגבלות)
ובחרתי את המודל Simplex LP משום שמדובר בבעיה לינארית פשוטה.
לחצתי על Solve והתקבל החלון הבא שמודיע שהתקבלה תוצאה.
לחצתי על OK, וקיבלתי את התוצאה:

כלומר, אני יכולה להגיע לרווח מקסימלי של 3,936 ₪ על ידי יצור של 57 יחידות ממוצר 1 ו-46 יחידות ממוצר 2.