תוסף Excel Solver מבצע אופטימיזציה מתמטית. זה משמש בדרך כלל כדי להתאים מודלים מורכבים לנתונים או למצוא פתרונות איטרטיביים לבעיות. לדוגמה, ייתכן שתרצה להתאים עקומה דרך כמה נקודות נתונים, באמצעות משוואה. פותר יכול למצוא את הקבועים במשוואה שנותנים את ההתאמה הטובה ביותר לנתונים. יישום נוסף הוא שבו קשה לארגן מחדש מודל כדי להפוך את הפלט הנדרש לנושא של משוואה.
היכן פותר באקסל?
תוסף Solver כלול ב-Excel אבל הוא לא תמיד נטען כחלק מהתקנת ברירת מחדל. כדי לבדוק אם הוא נטען, בחר בכרטיסייה DATA וחפש את הסמל Solver בקטע Analysis.
אם אינך יכול למצוא את Solver בכרטיסייה DATA, תצטרך לטעון את התוספת:
-
בחר בכרטיסייה FILE ולאחר מכן בחר Options.
Image -
בתיבת הדו-שיח Options בחר Add-Ins מהכרטיסיות בצד שמאל.
Image -
בתחתית החלון, בחר Excel Add-ins מהתפריט הנפתח Manage ובחר עבור…
Image -
סמן את תיבת הסימון שליד תוסף פותר ובחר OK.
Image -
הפקודה Solver אמורה להופיע כעת בכרטיסייה DATA. אתה מוכן להשתמש ב-Solver.
Image
שימוש ב-Solver ב-Excel
בוא נתחיל עם דוגמה פשוטה כדי להבין מה הפותר עושה. תארו לעצמכם שאנחנו רוצים לדעת איזה רדיוס ייתן מעגל בשטח של 50 יחידות מרובעות. אנו מכירים את המשוואה עבור שטח המעגל (A=pi r2). נוכל, כמובן, לארגן מחדש את המשוואה הזו כדי לתת את הרדיוס הנדרש לאזור נתון, אבל לשם הדוגמה, בואו נעמיד פנים שאנחנו לא יודעים איך לעשות את זה.
צור גיליון אלקטרוני עם הרדיוס ב- B1 וחשב את השטח ב- B2 באמצעות המשוואה =pi()B1^2.
נוכל להתאים באופן ידני את הערך ב- B1 עד ש- B2 יציג ערך שקרוב מספיק ל-50. בהתאם למידת הדיוק שלנו צריך להיות, זו עשויה להיות גישה מעשית. עם זאת, אם אנחנו צריכים לדייק מאוד, ייקח הרבה זמן לבצע את ההתאמות הנדרשות.למעשה, זה בעצם מה שסולבר עושה. הוא מבצע התאמות לערכים בתאים מסוימים, ובודק את הערך בתא יעד:
- בחר בכרטיסייה DATA ו- Solver, כדי לטעון את תיבת הדו-שיח Solver Parameters
-
הגדר את המטרה התא להיות האזור, B2. זה הערך שייבדק, תוך התאמת תאים אחרים עד שהערך הזה יגיע לערך הנכון.
Image -
בחר את הלחצן עבור ערך של: והגדר ערך של 50. זה הערך ש-B2 צריך להשיג.
Image -
בתיבה שכותרתה על ידי שינוי תאים משתנים: הזן את התא המכיל את הרדיוס, B1.
Image -
השאר את האפשרויות האחרות כפי שהן כברירת מחדל ובחר Solve. האופטימיזציה מתבצעת, הערך של B1 מותאם עד ש-B2 הוא 50 והדיאלוג Solver Results מוצג.
Image -
בחר OK כדי לשמור את הפתרון.
Image
דוגמה פשוטה זו הראתה כיצד פועל הפותר. במקרה זה, יכולנו לקבל את הפתרון בקלות רבה יותר בדרכים אחרות. בשלב הבא נסתכל על כמה דוגמאות שבהן Solver נותן פתרונות שיהיה קשה למצוא בכל דרך אחרת.
התאמת דגם מורכב באמצעות תוסף Excel Solver
Excel כולל פונקציה מובנית לביצוע רגרסיה ליניארית, התאמת קו ישר דרך קבוצת נתונים. פונקציות לא-לינאריות נפוצות רבות ניתנות ללינאריות, כלומר ניתן להשתמש ברגרסיה ליניארית כדי להתאים פונקציות כמו אקספוננציאלים.לפונקציות מורכבות יותר ניתן להשתמש בפותר כדי לבצע 'מזעור הריבועים הקטנים ביותר'. בדוגמה זו, נשקול להתאים משוואה בצורה ax^b+cx^d לנתונים המוצגים להלן.
זה כולל את השלבים הבאים:
- סדר את מערך הנתונים עם ערכי x בעמודה A וערכי ה-y בעמודה B.
- צור את 4 ערכי המקדמים (a, b, c ו-d) איפשהו בגיליון האלקטרוני, ניתן לתת להם ערכי התחלה שרירותיים.
-
צור עמודה של ערכי Y מותאמים, תוך שימוש במשוואה בצורת ax^b+cx^d המתייחסת למקדמים שנוצרו בשלב 2 ולערכי x בעמודה A. שימו לב שכדי להעתיק את הנוסחה למטה בעמודה, ההפניות למקדמים חייבות להיות מוחלטות בעוד שההפניות לערכי x חייבות להיות יחסיות.
Image -
למרות שזה לא חיוני, אתה יכול לקבל אינדיקציה חזותית למידת ההתאמה של המשוואה על ידי שרטוט שתי העמודות y מול ערכי x בתרשים פיזור XY יחיד. הגיוני להשתמש בסמנים עבור נקודות הנתונים המקוריות, מכיוון שאלו ערכים בדידים עם רעש, ולהשתמש בקו עבור המשוואה המותאמת.
Image -
לאחר מכן, אנחנו צריכים דרך לכמת את ההבדל בין הנתונים והמשוואה המותאמת שלנו. הדרך הסטנדרטית לעשות זאת היא לחשב את סכום ההפרשים בריבוע. בעמודה שלישית, עבור כל שורה, ערך הנתונים המקורי של Y מופחת מערך המשוואה המותאמת, והתוצאה מרוחקת בריבוע. אז, ב- D2, הערך ניתן על ידי =(C2-B2)^2 לאחר מכן מחושב הסכום של כל הערכים בריבוע. מכיוון שהערכים בריבוע הם יכולים להיות רק חיוביים.
Image -
עכשיו אתה מוכן לבצע את האופטימיזציה באמצעות Solver. ישנם ארבעה מקדמים שצריך להתאים (a, b, c ו-d). יש לך גם ערך אובייקטיבי אחד למזער, סכום ההפרשים בריבוע. הפעל את הפותר, כמו לעיל, והגדר את הפרמטרים של הפותר כדי להתייחס לערכים אלה, כפי שמוצג להלן.
Image -
בטל את הסימון של האפשרות הפוך משתנים בלתי מוגבלים ללא-שלילי, זה יאלץ את כל המקדמים לקבל ערכים חיוביים.
Image -
בחר Solve ובדוק את התוצאות. התרשים יתעדכן וייתן אינדיקציה טובה לטיב ההתאמה. אם הפותר לא מייצר התאמה טובה בניסיון הראשון, תוכל לנסות להפעיל אותו שוב. אם ההתאמה השתפרה, נסה לפתור מהערכים הנוכחיים.אחרת, תוכל לנסות לשפר באופן ידני את ההתאמה לפני הפתרון.
Image - לאחר שהושגה התאמה טובה, תוכל לצאת מהפותר.
פתרון מודל באופן איטרטיבי
לפעמים יש משוואה פשוטה יחסית שנותנת פלט במונחים של קלט כלשהו. עם זאת, כאשר אנו מנסים להפוך את הבעיה לא ניתן למצוא פתרון פשוט. לדוגמה, ההספק שצורך רכב ניתן בקירוב על ידי P=av + bv^3 כאשר v היא המהירות, a הוא מקדם להתנגדות הגלגול ו-b הוא מקדם עבור גרר אווירודינמי. למרות שזו משוואה די פשוטה, לא קל לארגן מחדש כדי לתת משוואה של המהירות אליה יגיע הרכב עבור קלט כוח נתון. עם זאת, אנו יכולים להשתמש ב-Solver כדי למצוא באופן איטרטיבי את המהירות הזו. לדוגמה, מצא את המהירות שהושגה עם הספק של 740 W.
-
הגדר גיליון אלקטרוני פשוט עם המהירות, המקדמים a ו-b, וההספק המחושב מהם.
Image -
הפעל את הפותר והזן את הכוח, B5, בתור היעד. הגדר ערך אובייקטיבי של 740 ובחר את המהירות, B2, בתור התאים המשתנים לשינוי. בחר solve כדי להתחיל את הפתרון.
Image -
הפותר מתאים את ערך המהירות עד שההספק קרוב מאוד ל-740, מספק את המהירות שאנו דורשים.
Image - פתרון מודלים בדרך זו יכול להיות מהיר יותר ונוטה פחות לשגיאות מאשר היפוך מודלים מורכבים.
הבנת האפשרויות השונות הזמינות בפותר יכולה להיות די קשה.אם אתה מתקשה להשיג פתרון הגיוני, לעתים קרובות כדאי להחיל תנאי גבול על התאים הניתנים לשינוי. אלו הם ערכים מגבילים שמעבר להם אין להתאים אותם. לדוגמה, בדוגמה הקודמת, המהירות לא צריכה להיות פחות מאפס וניתן יהיה גם לקבוע גבול עליון. זו תהיה מהירות שאתה די בטוח שהרכב לא יכול לנסוע מהר ממנה. אם אתה מסוגל להגדיר גבולות עבור התאים המשתנים הניתנים לשינוי, אז זה גם גורם לאפשרויות מתקדמות אחרות לעבוד טוב יותר, כגון ריבוי הפעלה. פעולה זו תפעיל מספר פתרונות שונים, החל בערכים התחלתיים שונים עבור משתנים.
בחירת שיטת הפתרון יכולה להיות גם קשה. Simplex LP מתאים רק לדגמים ליניאריים, אם הבעיה אינה ליניארית היא תיכשל עם הודעה שתנאי זה לא התקיים. שתי השיטות האחרות מתאימות שתיהן לשיטות לא ליניאריות. GRG Nonlinear הוא המהיר ביותר אבל הפתרון שלו יכול להיות תלוי מאוד בתנאי ההתחלה הראשוניים.יש לו את הגמישות שהוא לא דורש גבולות להגדיר למשתנים. הפותר האבולוציוני הוא לרוב האמין ביותר, אך הוא מחייב את כל המשתנים בעלי גבול עליון ותחתון, שאולי קשה להבין אותם מראש.
תוסף Excel Solver הוא כלי רב עוצמה שניתן ליישם על בעיות מעשיות רבות. כדי לגשת במלואה לעוצמה של Excel, נסה לשלב את Solver עם פקודות מאקרו של Excel.
