פתרון פונקציה באקסל. הפעל, השתמש במקרה עם צילומי מסך

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

מה זה חיפוש פתרונות

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

כיצד להפעיל את התכונה "חפש פתרון".

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

1. פתח את "קובץ" על ידי לחיצה על השם המתאים.
2. לחץ על הקטע "הגדרות".
3. לאחר מכן בחר בסעיף המשנה "תוספות". כל התוספות של התוכנית יוצגו כאן, הכתובת "ניהול" תופיע למטה. בצד ימין שלו יהיה תפריט מוקפץ שבו עליך לבחור "תוספות אקסל". לאחר מכן לחץ על "עבור".
   

   

4. חלון נוסף "תוספות" יוצג על הצג. סמן את התיבה שליד הפונקציה הרצויה ולחץ על אישור.
5. הפונקציה הרצויה תופיע על הסרט מימין לקטע "נתונים".

על דוגמניות

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

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

משימות אופטימיזציה אופייניות:

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

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

שלב ההכנה

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

המשימה שלנו תהיה לחשב את ההנחה שבה מוכפלים הסכומים למכירת מוצרים שונים. 2 האלמנטים הללו מחוברים על ידי נוסחה הכתובה כך: =D13*$G$2. כאשר ב-D13 נכתבת הכמות הכוללת ליישום, ו-$G$2 היא הכתובת של האלמנט הרצוי.

שימוש בפונקציה והגדרתה

כאשר הנוסחה מוכנה, עליך להשתמש בפונקציה עצמה ישירות:

1. עליך לעבור לקטע "נתונים" וללחוץ על "חפש פתרון".

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

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

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

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

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

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

8. בנוסף, ניתן לבחור הגדרות מתקדמות נוספות. בצד ימין למטה יש אפשרות "אפשרויות" המאפשרת לך לעשות זאת.

9. בהגדרות, אתה יכול להגדיר את "דיוק ההגבלה" ו"מגבלות הפתרון". במצבנו אין צורך להשתמש באפשרויות אלו.

10. לאחר השלמת ההגדרות, הפונקציה עצמה מתחילה - לחץ על "מצא פתרון".

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

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

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

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

14. כאשר מתקבלת ההנחה הרצויה, נותר להחיל אותה כדי לחשב את כמות ההנחות לכל פריט. לשם כך, האלמנט הראשוני של העמודה "סכום הנחה" מודגש, הנוסחה נכתבת «=D2*$G$2» ולחץ על "Enter". סימני דולר מונחים כך שכאשר הנוסחה נמתחת לקווים סמוכים, G2 לא משתנה.

15. כעת יתקבל סכום ההנחה עבור הפריט הראשוני. לאחר מכן עליך להעביר את הסמן מעל פינת התא, כאשר הוא הופך ל"פלוס", ה-LMB נלחץ והנוסחה נמתחת לקווים הנדרשים.
16. לאחר מכן, השולחן יהיה מוכן סוף סוף.

טען/שמור אפשרויות חיפוש

אפשרות זו שימושית בעת החלת אפשרויות אילוץ שונות.

1. בתפריט אפשרויות מאתר הפתרונות, לחץ על טען/שמור.
2. הזן את הטווח עבור אזור הדגם ולחץ על שמור או טען.

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

חשוב! כדי לשמור את ההגדרות האחרונות בתפריט אפשרויות פתרון, חוברת עבודה נשמרת. לכל גיליון בו יש אפשרויות משלו לתוספת Solver. בנוסף, ניתן להגדיר יותר ממשימה אחת עבור גיליון על ידי לחיצה על כפתור "טען או שמור" על מנת לשמור משימות בודדות.

דוגמה פשוטה לשימוש ב-Solver

יש צורך להעמיס את המיכל במיכלים כך שהמסה שלו תהיה מקסימלית. נפח המיכל הוא 32 מ"ק. M. לקופסה מלאה משקל של 20 ק"ג, נפחה הוא 0,15 מ"ק. M. קופסה – 80 ק"ג ו-0,5 מ"ק. M. נדרש שמספר המיכלים הכולל יהיה לפחות 110 יח'. הנתונים מאורגנים כך:

משתני המודל מסומנים בירוק. פונקציית המטרה מסומנת באדום. הגבלות: לפי המספר הקטן ביותר של מכולות (גדול מ-110 או שווה ל-XNUMX) ולפי משקל (=SUMPRODUCT(B8:C8,B6:C6)‎ - משקל טרה כולל במיכל.

באנלוגיה, אנו רואים את הנפח הכולל: =SUMPRODUCT(B7:C7,B8:C8)‎. נוסחה כזו נחוצה כדי להגביל את הנפח הכולל של המיכלים. לאחר מכן, דרך "חפש פתרון", מוזנים קישורים לאלמנטים עם משתנים, נוסחאות והאינדיקטורים עצמם (או קישורים לתאים ספציפיים). כמובן שמספר הקונטיינרים הוא מספר שלם (זו גם מגבלה). אנו לוחצים על "מצא פתרון", כתוצאה מכך אנו מוצאים מספר כזה של מכולות כאשר המסה הכוללת היא מקסימלית וכל ההגבלות נלקחות בחשבון.

החיפוש אחר פתרון לא הצליח למצוא פתרונות

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

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

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

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

בדוגמה לעיל, מחוון הנפח המקסימלי הוא 16 מטר מעוקב. מ' במקום 32, מכיוון שהגבלה כזו סותרת את האינדיקטורים למספר המושבים המינימלי, שכן היא תתאים למספר של 16,5 מ"ק. M.

סיכום

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