Solution Finder הוא תוסף של Microsoft Excel המסייע לך למצוא את הפתרון האופטימלי לבעיה, תוך התחשבות באילוצים שצוינו על ידי המשתמש.

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

• יצירת מודל אופטימיזציה על גיליון MS EXCEL
• הגדרה מציאת פתרון;
• דוגמה פשוטה (מודל ליניארי).

התקנה חפש פתרון

קְבוּצָה מציאת פתרוןנמצא בקבוצה אָנָלִיזָהבכרטיסייה נתונים.

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

• בכרטיסייה קוֹבֶץתבחר קבוצה אפשרויות, ולאחר מכן הקטגוריה תוספות;
• בשטח לִשְׁלוֹטבחר ערך תוספות אקסלולחץ על הכפתור ללכת;
• בשטח תוספות זמינותסמן את התיבה שליד הפריט מציאת פתרוןולחץ על אישור.

הערה. חַלוֹן תוספותזמין גם בכרטיסייה מפתח. כיצד להפעיל את הכרטיסייה הזו.

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

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

לגבי דגמים

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

עֵצָה. לפני השימוש מציאת פתרוןאנו ממליצים בחום ללמוד את הספרות על פתרון בעיות אופטימיזציה ובניית מודלים.

להלן תוכנית חינוכית קטנה בנושא זה.

מִבנֶה עִילִי מציאת פתרוןעוזר לקבוע הדרך הכי טובה לַעֲשׂוֹת משהו:

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

הנה כמה דוגמאות טיפוסיות לבעיות אופטימיזציה:

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

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

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

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

הכנת מודל אופטימיזציה ב-MS EXCEL

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

עֵצָה. ארגן את נתוני הדגם כך שיהיה רק ​​דגם אחד בגיליון MS EXCEL אחד. אחרת, כדי לבצע חישובים תצטרך לשמור ולטעון כל הזמן הגדרות מציאת פתרון(ראה למטה).

הבה נציג אלגוריתם לעבודה איתו מציאת פתרון, המומלץ על ידי המפתחים עצמם (www.solver.com):

• הגדר תאים עם משתני מודל (משתני החלטה);
• צור נוסחה בתא שתחשב את הפונקציה האובייקטיבית של המודל שלך;
• צור נוסחאות בתאים שיחשבו ערכים בהשוואה לאילוצים (צד שמאל של הביטוי);
• שימוש בתיבת הדו-שיח מציאת פתרוןהזן קישורים לתאים המכילים משתנים, לפונקציית המטרה, לנוסחאות לאילוצים ולערכים של האילוצים עצמם;
• לָרוּץ מציאת פתרוןכדי למצוא את הפתרון האופטימלי.

הבה נעבור על כל השלבים הללו באמצעות דוגמה פשוטה.

דוגמה לשימוש פשוט מציאת פתרון

יש צורך להעמיס על המכולה סחורה כך שמשקל המכולה יהיה מקסימלי. המכולה בנפח של 32 קוב. הפריטים כלולים בקופסאות ובארגזים. כל קופסת סחורה שוקלת 20 ק"ג, נפחה הוא 0.15 מ"ק. קופסה - 80 ק"ג ו-0.5 מ"ק, בהתאמה. יש צורך כי המספר הכולל של מכולות יהיה לפחות 110 חתיכות.

אנו מארגנים מודלים אלה באופן הבא (ראה קובץ לדוגמה).

משתני מודל (כמות של כל סוג של מיכל) מסומנים בירוק.
הפונקציה האובייקטיבית (המשקל הכולל של כל הקופסאות והארגזים) היא באדום.
מגבלות הדגם: כמות מינימלית של מיכלים (>=110) ונפח כולל (<=32) – синим.
פונקציית המטרה מחושבת באמצעות הנוסחה =SUMPRODUCT(B8:C8,B6:C6)‎הוא המשקל הכולל של כל הארגזים והארגזים שהועמסו למיכל.
באופן דומה, אנו מחשבים את הנפח הכולל - =SUMPRODUCT(B7:C7,B8:C8). נוסחה זו נחוצה כדי להגביל את הנפח הכולל של קופסאות וארגזים (<=32).
כמו כן, כדי להגדיר את מגבלת המודל, אנו מחשבים את המספר הכולל של מיכלים =SUM(B8:C8) .
כעת משתמשים בתיבת הדו-שיח מציאת פתרוןבואו נזין קישורים לתאים המכילים משתנים, פונקציה אובייקטיבית, נוסחאות לאילוצים וערכי האילוצים עצמם (או קישורים לתאים המתאימים).
ברור שמספר הקופסאות והארגזים חייב להיות מספר שלם – זו מגבלה נוספת של המודל.

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

סיכום

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

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

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

הערה. אתה יכול לקרוא על השפעת אי-לינאריות המודל על תוצאות החישוב בחלק האחרון של המאמר.

בכל מקרה (ליניארי או לא ליניארי), תחילה עליך לנתח את המודל לצורך עקביות של אילוצים, כלומר תנאים שלא ניתן לעמוד בהם בו-זמנית. לרוב זה נובע מבחירה שגויה של יחס (לדוגמה,<= вместо >=) או ערך גבול.
אם, למשל, בדוגמה שנדונה לעיל, ערך הנפח המרבי נקבע ל-16 מ"ק במקום 32 מ"ק, אזי הגבלה זו תסתור את ההגבלה על מספר המושבים המינימלי (110), מכיוון מספר המקומות המינימלי מתאים לנפח השווה ל-16.5 מ"ק (110 * 0.15, כאשר 0.15 הוא נפח הקופסה, כלומר המיכל הקטן ביותר). על ידי הגדרת מגבלת הנפח המקסימלית ל-16 מ"ק, מציאת פתרוןלא ימצא פתרון.

עם מגבלה של 17 מ"ק מציאת פתרוןימצא פתרון.

כמה הגדרות מציאת פתרון

שיטת פתרון
המודל שנדון לעיל הוא ליניארי, כלומר. הפונקציה האובייקטיבית (M הוא המשקל הכולל שיכול להיות מקסימלי) באה לידי ביטוי במשוואה הבאה M=a1*x1+a2*x2, כאשר x1 ו-x2 הם משתני המודל (מספר הקופסאות והמגירות), a1 ו-a2 הם המשקולות שלהם. במודל ליניארי, האילוצים חייבים להיות גם פונקציות ליניאריות של המשתנים. במקרה שלנו, מגבלת הנפח V=b1*x1+b2*x2 באה לידי ביטוי גם בתלות ליניארית. ברור שגם מגבלה נוספת - מספר מיכלים מרבי (n) - היא גם ליניארית x1+x2 בעיות ליניאריות נפתרות בדרך כלל בשיטת Simplex. על ידי בחירת שיטת פתרון זו בחלון מציאת פתרוןאתה יכול גם לבדוק את המודל עצמו ללינאריות. במקרה של דגם לא ליניארי, תקבל את ההודעה הבאה:

במקרה זה, יש צורך לבחור שיטה לפתרון הבעיה הלא ליניארית. דוגמאות לתלות לא לינארית: V=b1*x1*x1; V=b1*x1^0.9; V=b1*x1*x2, כאשר x הוא משתנה ו-V הוא פונקציה אובייקטיבית.

לחצנים הוסף, ערוך, מחק
לחצנים אלה מאפשרים לך להוסיף, לערוך ולמחוק אילוצי מודל.

כפתור אתחול
כדי להסיר את כל ההגדרות מציאת פתרוןלחץ על הכפתור אִתחוּל- תיבת הדו-שיח תתבטל.

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

דיוק
בעת יצירת מודל, לחוקר יש בתחילה אומדן מסוים של טווחי השונות של פונקציית המטרה והמשתנים. בהתחשב בחישובים ב-MS EXCEL, מומלץ שטווחי שונות אלו יהיו גבוהים משמעותית מדיוק החישוב (בדרך כלל הוא מוגדר מ-0.001 ל-0.000001). ככלל, הנתונים במודל מנורמלים כך שטווחי הווריאציה של פונקציית המטרה והמשתנים הם בטווח של 0.1 - 100,000. כמובן, הכל תלוי במודל הספציפי, אבל אם המשתנים שלך משתנים ביותר מ-5-6 סדרי גודל, אז אולי כדאי "לחספס" את המודל, למשל, באמצעות פעולת הלוגריתם.

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

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

פתרון בעיות אופטימיזציה באקסל

מודלי אופטימיזציה משמשים בתחום הכלכלי והטכני. המטרה שלהם היא לבחור פתרון מאוזן אופטימלי בתנאים ספציפיים (מספר מכירות ליצירת הכנסה מסוימת, התפריט הטוב ביותר, מספר טיסות וכו').

ב- Excel, הפקודות הבאות משמשות לפתרון בעיות אופטימיזציה:

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

מַצָב. החברה מייצרת מספר סוגי יוגורט. באופן קונבנציונלי - "1", "2" ו- "3". לאחר שמכרה 100 צנצנות יוגורט "1", החברה מקבלת 200 רובל. "2" - 250 רובל. "3" - 300 רובל. מכירות נקבעו, אך כמות חומרי הגלם הזמינים מוגבלת. אתה צריך למצוא איזה סוג של יוגורט ובאיזה נפח אתה צריך לעשות על מנת לקבל הכנסה מקסימלית ממכירה.

אנו מכניסים את הנתונים הידועים (כולל שיעורי צריכת חומרי גלם) לטבלה:

על סמך נתונים אלה, ניצור גליון עבודה:

1. אנחנו עדיין לא יודעים את מספר המוצרים. אלו הם המשתנים.
2. הנוסחאות הבאות מוזנות בעמודה "רווח": =200*B11, =250*B12, =300*B13.
3. צריכת חומרי הגלם מוגבלת (אלו מגבלות). הנוסחאות הבאות מוזנות לתאים: =16*B11+13*B12+10*B13 ("חלב"); =3*B11+3*B12+3*B13 ("מחמצת"); =0*B11+5*B12+3*B13 ("בולם זעזועים") ו-=0*B11+8*B12+6*B13 ("סוכר"). כלומר, הכפלנו את שיעור הצריכה בכמות.
4. המטרה היא למצוא את הרווח הגבוה ביותר האפשרי. זהו תא C14.

הפעל את הפקודה "חפש פתרון" והזן את הפרמטרים.

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

האפשרות הטובה ביותר היא להתרכז בייצור יוגורט "3" ו- "1". אין לייצר יוגורט "2".



פתרון בעיות פיננסיות באקסל

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

בואו נעצב את נתוני המקור בצורה של טבלה:

מכיוון שהריבית אינה משתנה לאורך כל התקופה, אנו משתמשים בפונקציה PS (RATE, NPER, PMT, BS, TYPE).

מילוי הטיעונים:

1. התעריף הוא 20%/4, כי הריבית מחושבת מדי רבעון.
2. Nper – 4*4 (תקופת הפקדה כוללת * מספר תקופות צבירה בשנה).
3. PMT – 0. אנחנו לא כותבים כלום, כי... הפיקדון לא יתחדש.
4. סוג - 0.
5. BS הוא הסכום שאנו רוצים לקבל בתום תקופת ההפקדה.

המשקיע צריך להשקיע את הכסף הזה, אז התוצאה שלילית.

כדי לבדוק את נכונות הפתרון, אנו משתמשים בנוסחה: PS = BS / (1 + הימור) nper. בוא נחליף את הערכים: PS = 400,000 / (1 + 0.05) 16 = 183245.

פתרון אקונומטריה באקסל

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

ישנם 2 טווחי ערכים:

ערכי X ישחקו את התפקיד של מאפיין גורם, Y - המתקבל. המשימה היא למצוא את מקדם המתאם.

כדי לפתור בעיה זו, הפונקציה CORREL (מערך 1; מערך 2) מסופקת.

פתרון בעיות לוגיות באקסל

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

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

1. מקם את הסמן בתא C1. לחץ על סמל הפונקציות. בחר "אם".
2. מלא את הטיעונים. ביטוי לוגי – B1>=4. זהו התנאי שבו הערך הבוליאני הוא TRUE.
3. אם נכון - "עבר את המבחן." FALSE - "לא עברתי את המבחן."

פתרון בעיות מתמטיקה באקסל

באמצעות הכלים של התוכנית ניתן לפתור הן את הבעיות המתמטיות הפשוטות ביותר והן מורכבות יותר (פעולות עם פונקציות, מטריצות, משוואות לינאריות וכו').

תנאי משימת הלמידה. מצא את היפוך של מטריצה ​​B עבור מטריצה ​​A.

1. אנו מכינים טבלה עם הערכים של מטריצה ​​A.
2. בחר אזור באותו גיליון עבור המטריצה ​​ההפוכה.
3. לחץ על כפתור "הוסף פונקציה". קטגוריה - "מתמטית". סוג - "MOBR".
4. בשדה הארגומנט "מערך" נזין את הטווח של מטריצה ​​A.
5. הקש Shift+Ctrl+Enter בו-זמנית - זהו תנאי מוקדם להזנת מערכים.

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

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

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

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

כל מה שנותר הוא לציין "תוספות Excel" בעמודה "ניהול", ולאחר מכן ללחוץ על כפתור "עבור".

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

הבה נבחן כיצד להשתמש נכון בחיפוש הפתרונות ב- Excel 2010 עם כמה דוגמאות פשוטות.

דוגמה אחת .

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

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

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

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

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

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

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

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

« 5 » — כפתור למעבר לחישובים אינטראקטיביים המבוססים על התוכנית שציינו.

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

לשם כך, תוכל להשתמש במספר סמלים ספציפיים (ומוכרים לכל משתמשי Excel 2010) "=", ">=", "<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

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

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

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

הפתרון הנכון לבעיה לדוגמה צריך להיראות כך

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

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

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

דגם "A" דורש 3 מ"ר של לוחות, ודגם "B" דורש 1 מ"ר יותר (כלומר, 4). מהספקים שלך אתה מקבל לכל היותר 1700 מ"ר של לוחות בשבוע. במקרה זה, דגם "A" נוצר ב-12 דקות של פעולת המכונה, ו-"B" ב-30 דקות. בסך הכל, המכונה יכולה לעבוד לא יותר מ-160 שעות שבועיות.

השאלה היא: כמה מוצרים (ואיזה דגם) צריכה חברה לייצר בשבוע כדי להשיג את הרווח המקסימלי האפשרי, אם מדף "A" נותן רווח של 60 רובל, ו-"B" - 120?

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

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

אז בואו נסתכל מה יש לנו. תא היעד F7 מכיל נוסחה שתחשב את הרווח. הגדרנו את פרמטר האופטימיזציה למקסימום. בין התאים הניתנים להחלפה יש לנו "F3: G3". מגבלות - כל הערכים שנתגלו חייבים להיות מספרים שלמים לא שליליים, הכמות הכוללת של זמן המכונה המושקע אינו עולה על 160 (תא D9 שלנו), כמות חומרי הגלם אינה עולה על 1700 (תא D8).

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

אנחנו מפעילים את התוכנית, והיא מכינה פתרון.

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

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

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

אז היזהר מאוד בעתיד.

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

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

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

יש צורך למצוא תכנית להובלה אופטימלית של מטען (אלו וממנה), שבה העלות הכוללת של ההובלה תהיה מינימלית.

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

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

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

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

זה הכל בנושא הזה.

חיפשנו פתרונות באקסל 2010 - לפתרון בעיות מורכבות

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

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

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

אתה יכול לעשות את זה ככה:

עבור גירסאות ישנות יותר מ-Excel 2007 באמצעות הפקודהתַפרִיט שירות --> תוספות;

מאז אקסל 2007 באמצעות תיבת דו-שיחאפשרויות אקסל

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

בגרסאות שקדמו לאקסל 2007, פקודה דומה תופיע בתפריטשֵׁרוּת

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

דוגמה 1. חלוקת בונוסים

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

קודם כל, אנו יוצרים טבלה עם הנתונים והנוסחאות הראשוניות שבאמצעותן יש לקבל את התוצאה. במקרה שלנו, התוצאה היא סכום הפרמיה הכולל. חשוב מאוד שתא המטרה (C8) יהיה מקושר באמצעות נוסחאות לתא הרצוי לשינוי (E2). בדוגמה, הם מחוברים באמצעות נוסחאות ביניים המחשבות את סכום הבונוס עבור כל עובד (C2:C7).

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

מאז אקסל 2010

לפני אקסל 2010

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

מאז אקסל 2010

לפני אקסל 2010

אם התוצאה שאתה רואה בטבלה מתאימה לך, אז בתיבת הדו-שיח תוצאות חיפוש פתרונותללחוץ בסדרורשום את התוצאה בטבלה. אם התוצאה לא מתאימה לך, לחץ לְבַטֵלולחזור למצב הקודם של הטבלה.

הפתרון לבעיה זו נראה כך

חָשׁוּב:עבור כל שינוי בנתוני המקור כדי לקבל תוצאה חדשה מציאת פתרוןיצטרך לרוץ שוב.

בואו נסתכל על בעיית אופטימיזציה נוספת (מקסום רווח)

דוגמה 2. ייצור רהיטים (מקסום רווח)

החברה מייצרת שני דגמים A ו-B של מדפי ספרים מוכנים.

הייצור שלהם מוגבל על ידי זמינות חומרי גלם (לוחות באיכות גבוהה) וזמן עיבוד מכונה.

כל מוצר מדגם A דורש 3 מ"רלוחות, ולמוצר דגם B - 4 מ"ר. החברה יכולה לקבל מהספקים שלה עד 1,700 מ"ר של לוחות בשבוע.

כל מוצר מדגם A דורש 12 דקות של זמן מכונה, ולמוצר דגם B - 30 דקות. ניתן להשתמש ב-160 שעות של זמן מכונה בשבוע.

כמה מוצרים מכל דגם צריכה החברה לייצר בשבוע כדי להשיג רווח מקסימלי אם כל מוצר מדגם A מביא 60 רובל. רווח, וכל מוצר מדגם B עולה 120 רובל. הגיע?

אנחנו כבר מכירים את ההליך.

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

בואו נשיק מציאת פתרוןובתיבת הדו-שיח הגדר את הפרמטרים הדרושים

1. תא יעד B12 מכיל את הנוסחה לחישוב הרווח
2. פרמטר אופטימיזציה - מקסימום
3. תאים שונה B9:C9
4. הגבלות: הערכים שנמצאו חייבים להיות מספר שלם, לא שלילי; הכמות הכוללת של זמן המכונה לא תעלה על 160 שעות (הפניה לתא D16); הכמות הכוללת של חומרי הגלם לא תעלה על 1700 מ"ר (תא ייחוס D15). כאן, במקום קישורים לתאים D15 ו-D16, ניתן היה לציין מספרים, אך בעת שימוש בקישורים, ניתן לבצע כל שינוי בהגבלות ישירות בטבלה
5. לחץ על הכפתור מצא פתרון (הפעלה)ולאחר אישור נקבל את התוצאה

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

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

הראשון מבין הפרמטרים שנבחרו אחראי על דיוק החישובים. על ידי הקטנתו תוכלו להגיע לתוצאה מדויקת יותר, במקרה שלנו - ערכי מספר שלמים. השנייה מבין האפשרויות המודגשות (זמינה החל מ-Excel 2010) עונה על השאלה: כיצד ניתן להשיג תוצאות חלקיות בעת הגבלהכֹּל? מסתבר מציאת פתרוןמגבלה זו פשוט התעלמה בהתאם לדגל המסומן.

דוגמה 3. בעיית הובלה (מזעור עלויות)

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

נתון: עתודות חול במחצבות; צרכי חול באתר בנייה; עלויות הובלה בין כל זוג ספק-צרכן.

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

דוגמה למיקום של תאים עם נתוני מקור והגבלות, התאים הרצויים ותא היעד מוצגת באיור

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

אנו משיקים את החיפוש אחר פתרון ומגדירים את הפרמטרים הדרושים (ראה איור)

נְקִישָׁה מצא פתרון (הפעלה)וקבל את התוצאה המוצגת למטה

לסיכום, אני מציע לך לנסות את היד שלך בשימוש מציאת פתרוןוהשתמש בו כדי לפתור בעיה ישנה:

איכר קנה 100 ראשי בקר בשוק תמורת 100 רובל. שור עולה 10 רובל, פרה 5 רובל, עגל 50 קופיקות. כמה שוורים, פרות ועגלים קנה האיכר?

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

שיטה 1: שיטת מטריצה

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

14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

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



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



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

   MOBR(מערך)

   טַעֲנָה "מַעֲרָך"- זוהי, למעשה, הכתובת של טבלת המקור.

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



4. מתחיל אשפי פונקציות. עבור לקטגוריה "מָתֵימָטִי". ברשימה המוצגת אנו מחפשים את השם "MOBR". לאחר שהוא נמצא, בחר אותו ולחץ על הכפתור "בסדר".



5. MOBR. מבחינת מספר הארגומנטים, יש לו רק שדה אחד - "מַעֲרָך". כאן אתה צריך לציין את הכתובת של הטבלה שלנו. למטרות אלו, מקם את הסמן בשדה זה. לאחר מכן החזק את לחצן העכבר השמאלי לחוץ ובחר את האזור בגיליון שבו ממוקמת המטריצה. כפי שאתה יכול לראות, נתונים על קואורדינטות המיקום מוזנים אוטומטית לשדה החלון. לאחר השלמת משימה זו, הדבר הברור ביותר יהיה ללחוץ על הכפתור "בסדר", אבל אל תמהר. העובדה היא שלחיצה על כפתור זה שווה ערך לשימוש בפקודה להיכנס. אבל כשעובדים עם מערכים, לאחר שסיימת להזין את הנוסחה, אסור ללחוץ על הכפתור להיכנס, והגדר קיצור מקלדת Ctrl+Shift+Enter. בואו נבצע את הפעולה הזו.



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



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

   MUMSET(מערך1, מערך2)

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



8. בקטגוריה "מָתֵימָטִי", הושק אשפי פונקציות, הדגש את השם "MUMNOZH"ולחץ על הכפתור "בסדר".



9. חלון הארגומנטים של הפונקציה מופעל מומניפה. בשטח "מערך 1"אנו מכניסים את הקואורדינטות של המטריצה ​​ההפוכה שלנו. לשם כך, כמו בפעם הקודמת, מקם את הסמן בשדה ובחזק את לחצן העכבר השמאלי, בחר את הטבלה המתאימה עם הסמן. אנו מבצעים פעולה דומה להזנת קואורדינטות בשטח "מערך 2", רק שהפעם אנו מדגישים את ערכי העמודות ב. לאחר השלמת השלבים לעיל, אל תמהר ללחוץ שוב על הכפתור "בסדר"או מפתח להיכנס, והקלד את צירוף המקשים Ctrl+Shift+Enter.



10. לאחר פעולה זו, שורשי המשוואה יוצגו בתא שנבחר קודם לכן: X1, X2, X3ו X4. הם יאותרו ברצף. לפיכך, אנו יכולים לומר שפתרנו את המערכת הזו. כדי לבדוק את נכונות הפתרון, די להחליף את התשובות הללו במערכת הביטויים המקורית במקום השורשים המתאימים. אם השוויון מתקיים, המשמעות היא שמערכת המשוואות המוצגת נפתרה בצורה נכונה.



שיטה 2: בחירת פרמטרים

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






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

שיטה 3: שיטת קרמר

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

14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21


1. כמו בשיטה הראשונה, אנו יוצרים מטריצה אממקדמי משוואות וטבלה במהערכים שמגיעים אחרי השלט "שווים".



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



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

   MOPRED(מערך)

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

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



4. החלון מופעל אשפי פונקציות. עבור לקטגוריה "מָתֵימָטִי"ובין רשימת המפעילים, בחר את השם שם "MOPRED". לאחר מכן, לחץ על הכפתור "בסדר".



5. חלון הארגומנטים של הפונקציה נפתח MOPRED. כפי שאתה יכול לראות, יש לו רק שדה אחד - "מַעֲרָך". בשדה זה אנו מכניסים את הכתובת של המטריצה ​​הראשונה שעברה טרנספורמציה. לשם כך, מקם את הסמן בשדה ולאחר מכן בחר את טווח המטריצה. לאחר מכן, לחץ על הכפתור "בסדר". פונקציה זו מפלטת את התוצאה בתא אחד, לא במערך, כך שאין צורך ללחוץ על צירוף מקשים כדי לקבל את החישוב Ctrl+Shift+Enter.



6. הפונקציה מחשבת את התוצאה ומציגה אותה בתא שנבחר מראש. כפי שאנו רואים, במקרה שלנו הקובע שווה ל -740 , כלומר, אינו שווה לאפס, מה שמתאים לנו.



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



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



9. עכשיו הגיע הזמן למצוא את שורשי המשוואה. שורש המשוואה יהיה שווה ליחס בין הדטרמיננטה של ​​המטריצה ​​שעברה טרנספורמציה המקבילה לבין הקובע של הטבלה הראשונית. לפיכך, מחלקים בתורו את כל ארבעת הקובעים של המטריצות המומרות במספר -148 , שהוא הקובע של הטבלה המקורית, נקבל ארבעה שורשים. כפי שאנו יכולים לראות, הם שווים לערכים 5 , 14 , 8 ו 15 . אז הם זהים בדיוק לשורשים שמצאנו באמצעות המטריצה ​​ההפוכה שיטה 1, המאשר את נכונות הפתרון של מערכת המשוואות.



שיטה 4: שיטה גאוסית

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

14x1+2x2+8x3=110
7x1-3x2+5x3=32
5x1+x2-2x3=17


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



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

   B8:E8-$B$7:$E$7*(B8/$B$7)

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

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



3. לאחר מכן, העתק את השורה שהתקבלה והדבק אותה בשורה למטה.



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



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



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

   B13:E13-$B$12:$E$12*(C13/$C$12)

   לאחר הזנת הנוסחה, בחר את כל השורה והשתמש בקיצור המקלדת Ctrl+Shift+Enter.



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

   לפיכך, נחלק את השורה האחרונה שחישבנו במקדם השלישי שלה. לאחר שהקלדת את הנוסחה, בחר את כל השורה והקש על צירוף המקשים Ctrl+Shift+Enter.



8. עלה שורה אחת למעלה והזן לתוכה את נוסחת המערך הבאה:

   =(B16:E16-B21:E21*D16)/C16

   אנו לוחצים על צירוף המקשים שאנו כבר מכירים כדי ליישם את נוסחת המערך.



9. בוא נעלה עוד שורה אחת. אנו מכניסים לתוכו נוסחת מערך בצורה הבאה:

   =(B15:E15-B20:E20*C15-B21:E21*D15)/B15

   שוב, בחר את כל השורה והשתמש בקיצור המקלדת Ctrl+Shift+Enter.



10. כעת אנו מסתכלים על המספרים שהתבררו בעמודה האחרונה של גוש השורות האחרון שחישבנו קודם לכן. אלו המספרים ( 4 , 7 ו 5 ) יהיו השורשים של מערכת משוואות זו. אתה יכול לבדוק זאת על ידי החלפתם במקום הערכים X1, X2ו X3לתוך ביטויים.



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