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

Excel מסוגל מאוד כאשר יש לך את כל הנתונים הדרושים לך לחישובים שלך.

אבל האם זה לא יהיה נחמד אם זה יכול לפתור משתנים לא ידועים ?

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

כיצד להשתמש בחיפוש מטרות ב- Excel

Search Seek כבר מובנה באקסל. זה מתחת ל נתונים הכרטיסייה, ב ניתוח מה-אם תַפרִיט:

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

כפי שאתה יכול לראות, סך המכירות הנוכחי הוא 114,706 יחידות. אם אנחנו רוצים למכור 250,000 עד סוף השנה, כמה אנחנו צריכים למכור ברבעון הרביעי? חיפוש המטרות של Excel יגיד לנו.

להלן אופן השימוש בחיפוש יעדים, צעד אחר צעד:

נְקִישָׁה נתונים> ניתוח מה-אם> חיפוש מטרות . תראה חלון זה:
הכנס את החלק 'שווה' למשוואה שלך ב- הגדר תא שדה. זהו המספר שאקסל תנסה לייעל. במקרה שלנו, זהו סך כל מספרי המכירות שלנו בתא B5.
הקלד את ערך המטרה שלך ב- להעריך שדה. אנו מחפשים בסך הכל 250,000 יחידות שנמכרו, אז נכניס '250,000' לתחום זה.
ספר ל- Excel לאיזה משתנה לפתור את על ידי שינוי התא שדה. אנחנו רוצים לראות מה המכירות שלנו ברבעון הרביעי צריכות להיות. אז נגיד ל- Excel לפתור עבור תא D2. זה יראה כך כאשר הוא מוכן לצאת לדרך:
מכה בסדר לפתור את המטרה שלך. כשזה נראה טוב, פשוט להכות בסדר . Excel יודיע לך כאשר Goal Seek ימצא פתרון.
נְקִישָׁה בסדר שוב תראה את הערך הפותר את המשוואה שלך בתא שבחרת עבורו על ידי שינוי התא .

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

שים לב ש- Excel מחליף את הנתונים הקודמים שלנו. זה רעיון טוב ל הפעל את Search Seek על עותק של הנתונים שלך . כדאי גם לרשום על הנתונים שהועתקו שהם נוצרו באמצעות Search Seek. אתה לא רוצה לבלבל את זה עם נתונים עדכניים ומדויקים.

כיצד לתקן יציאות USB שבורות

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

מה עושה הפותר של Excel?

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

זה יכול לפתור ערך מקסימלי של מספר, ערך מינימלי של מספר או מספר מדויק.

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

זוהי דרך מצוינת לפתור מספר משתנים לא ידועים ב- Excel. אך מציאתו והשימוש בו אינם פשוטים.

בואו נסתכל על טעינת התוסף Solver, ונקפוץ כיצד להשתמש ב- Solver ב- Excel 2016.

כיצד לטעון את התוסף לפותר

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

ראש אל קובץ> אפשרויות> תוספות . לאחר מכן לחץ על ללכת ליד נהל: תוספות Excel .

אם הנפתח הזה אומר משהו אחר מאשר 'תוספות Excel', יהיה עליך לשנות אותו:

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

כעת תראה את פּוֹתֵר כפתור ב אָנָלִיזָה הקבוצה של נתונים כרטיסייה:

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

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

כיצד להשתמש ב- Solver ב- Excel

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

נְקִישָׁה נתונים> פותר . תראה את חלון פרמטרי הפתרון למטה. (אם אינך רואה את לחצן הפותר, עיין בסעיף הקודם כיצד לטעון את התוסף לפתרון).
הגדר את מטרת התא שלך וספר ל- Excel את המטרה שלך. המטרה נמצאת בראש חלון הפותר, ויש לה שני חלקים: התא האובייקטיבי ובחירה של מקסימום, מזעור או ערך ספציפי. אם תבחר מקסימום , Excel יתאים את המשתנים שלך בכדי לקבל את המספר הגדול ביותר האפשרי בתא המטרה שלך. מינימום הוא ההפך: הפותר ימזער את המספר האובייקטיבי. ערך של מאפשר לך לציין מספר ספציפי ש- Solver צריך לחפש.
בחר את התאים המשתנים ש- Excel יכול לשנות. התאים המשתנים נקבעים עם על ידי שינוי תאים משתנים שדה. לחץ על החץ שליד השדה, ולאחר מכן לחץ וגרור כדי לבחור את התאים שאיתם Solver צריך לעבוד. שים לב שאלו הם כל התאים שיכול להשתנות. אם אינך רוצה שהתא ישתנה, אל תבחר אותו.
הגדר אילוצים על משתנים מרובים או בודדים. לבסוף, אנו מגיעים לאילוצים. זה המקום שבו Solver באמת עוצמתי. במקום לשנות כל אחד מהתאים המשתנים לכל מספר שהוא רוצה, תוכל לציין אילוצים שצריך לעמוד בהם. לפרטים, עיין בסעיף כיצד להגדיר אילוצים להלן.
לאחר שכל המידע הזה קיים, לחץ על לִפְתוֹר כדי לקבל את התשובה שלך. Excel תעדכן את הנתונים שלך כך שיכללו את המשתנים החדשים (זו הסיבה מדוע אנו ממליצים ליצור עותק של הנתונים שלך תחילה).

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

כיצד להגדיר אילוצים ב- Solver

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

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

להלן המפעילים הזמינים:

<= (פחות מ או שווה ל)
= (שווה ל)
=> (גדול מ או שווה ל)
int (חייב להיות מספר שלם)
אני (חייב להיות 1 או 0)
הכל שונה

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

לבסוף, הוסף את הערך לאילוץ.

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

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

דוגמא לפתרון

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

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

להלן האילוצים בהם נשתמש:

אין עבודות יכול לרדת מתחת לארבע שעות.
תפקיד 2 חייב להיות יותר משמונה שעות .
משרה 5 חייבת להיות פחות מאחת עשרה שעות .
סך כל שעות העבודה חייב להיות שווה ל 40 .

זה יכול להיות מועיל לכתוב את האילוצים שלך כך לפני השימוש ב- Solver.

כך הגדרנו זאת ב- Solver:

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

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

בואו נכה לִפְתוֹר ולראות מה קורה.

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

איפה כפתור ההפעלה ב- macbook pro

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

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

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

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

פתרונות אפשרויות מתקדמות

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

הברורה ביותר היא שיטת הפתרון:

אתה יכול לבחור בין GRG Nonlinear, Simplex LP ו- Evolutionary. Excel מספק הסבר פשוט לגבי מתי עליך להשתמש בכל אחד מהם. הסבר טוב יותר דורש ידע כלשהו בסטטיסטיקה ורגרסיה.

כדי להתאים הגדרות נוספות, פשוט לחץ על אפשרויות לַחְצָן. אתה יכול לספר ל- Excel על אופטימיות שלמה, להגדיר אילוצי זמן לחישוב (שימושי עבור מערכי נתונים מאסיביים), ולהתאים את אופן ביצוע שיטות החישוב שלהם GRG ושיטות הפתרון האבולוציוני.

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

חיפוש מטרות ופתרון: העלאת Excel לשלב הבא

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

Search Seek יכול לעזור לך לחסוך זמן על ידי ביצוע חישובים מהירים יותר, ו- Solver מוסיף כמות עצומה של כוח יכולות החישוב של Excel .

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

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

לַחֲלוֹק לַחֲלוֹק צִיוּץ אימייל 5 טיפים להטעין את מכונות הלינוקס של VirtualBox

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

קרא הבא נושאים קשורים

פִּריוֹן
גיליון אלקטרוני
Microsoft Excel
טיפים של Microsoft Office

על הסופר ואז אולברייט(פורסמו 506 מאמרים)

Dann הוא אסטרטגיית תוכן ויועץ שיווקי המסייע לחברות לייצר ביקוש והובלות. הוא גם כותב בלוגים אסטרטגיה ושיווק תוכן ב- dannalbright.com.

עוד מאת דן אולברייט

הירשם לניוזלטר שלנו

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

לחצו כאן להרשמה