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

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

5

10

5

7

6

9

 

מה יהיה הצירוף של שלושה מספרים שייתן לי את הסכום המינימלי? 

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

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

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

אז איך פותרים את הבעיה?

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

כך נראה המצב ההתחלתי שלנו:

בדוגמא הזו, אנו רוצים למצוא צירוף של 7 מספרים שיביאו אותנו לסכום המינימלי ביותר.

המספרים שאנו רוצים לסכום מופיעים בעמודה C.

הוספנו לאחר מכן את עמודה D בה יהיה ניתן להזין רק 1 או 0 – 1 אומר “להשתמש במספר”, 0 אומר “לא להשתמש”. אנחנו נבקש מהאקסל לשחק עם המספרים בעמודה D ולנסות לשנות את הערכים בה (0 או 1) כדי להגיע לתוצאה המבוקשת שלי – סכום מינימלי. 

בתאים E6 עד E17 תוצג תוצאת המכפלה שלי במספר מעמודה C יחד עם ההחלטה שלי מעמודה D, כלומר – 1 או 0 (לקחת או לא לקחת).

בתא E18 יוצג הסכום של המספר שהחלטנו לקחת בתאריך E6 עד E17 (כל המספרים שקיבלו 1 יוצגו בעמודה E, מי שקיבל 0 פשוט יהיה 0 ולכן לא ישפיע על הסכום. תא E18 הוא בעצם תא המטרה שלנו – כלי הסולבר ינסה להביא אותו למינימום. איך? על ידי שינוי עמודה D שבה כאמור ניתן לבחור 1 או 0. 

כעת, נוסיף בתא E19 בדיקה שסופרת באמצעות נוסחת COUNTIF פשוטה את כמות התאים בעמודה D שעומדים על ערך 1 (כלומר לקחת אותם בחשבון). אנחנו נכניס תא זה כחלק מהאילוץ שלנו, ונבקש שתא זה יהיה שווה לכמות שביקשנו לחשב בתא D2. 

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

כאמור:

1. ביקשנו להביא למינימום את תא E18 – זוהי תוצאת הסכימה שלי שמתחשבת בתאים שביקשנו לקחת בחשבון (רק אלו שקיבלו 1 בעמודה D)
2. ביקשנו מאקסל לשנות את התאים בין D6 ל- D17 ולהכניס בהם את הערכים 1 או 0 כדי שנצליח למצוא את השילוב שמביא לתוצאה הנמוכה ביותר
3. הוספנו אילוץ שמבקש שטווח התאים בין D6 ל- D17 יהיה בינארי – כלומר ניתן להזין בתוכו רק 0 או 1
4. הוספנו אילוץ שמוודא שכמות המספרים שלקחנו בסכימה (E19) תואמת לכמות המספרים שביקשנו בהתחלה (D2)מספרים שביקשנו מלכתחילה (D2)
5. הגדרנו את שיטת הפתרון ל- Simplex LP. שימו לב – אם היינו בוחרים בשיטת GRG Nonlinear, לא היינו מצליחים להגיע לפתרון בדוגמא זו. על מנת לא להיכנס להסברים מורכבים מדי, נמליץ לבדוק את שתי האפשרויות (גם Simplex LP  וגם GRG Nonliear) במידה ואחת אינה עובדת. ניתן גם לנסות לפתור באמצעות שיטת Evolutionary, אך אנו מזהירים מראש כי היא לוקחת זמן רב יותר. הסבר על האלגוריתמים תוכלו למצוא במדריך ה- Solver שלנו.

וכך תיראה התוצאה הסופית שלנו:

 

וכך זה נראה – מההתחלה ועד הסוף:

מקווים שהחכמתם 🙂