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

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

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

מה בדיוק זה חיפוש יעדים באקסל?

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

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

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

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

מושגים מרכזיים לפני השימוש ב-Goal Seek

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

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

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

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

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

איך Goal Seek עובד שלב אחר שלב: דוגמה להלוואה

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

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

1. הכינו את גיליון האלקטרוני של ההלוואה

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

• בגודל A1: "סכום ההלוואה". ב B1: ההון שברצונך לבקש, לדוגמה 100000.
• בגודל A2: "תקופת זמן בחודשים". ב B2מספר החודשים, לדוגמה 180 (שיהיו 15 שנים).
• בגודל A3: "ריבית". ב B3לעת עתה, השאירו אותו ריק, כי זה יהיה הלא נודע.
• בגודל A4"תשלום". ב B4הנה הנוסחה עם תשלום.

כעת רשמו את הנוסחה בתא B4. =PMT(B3/12;B2;B1)אתם מחלקים את B3 ב-12 מכיוון שהריבית מבוטאת בדרך כלל במונחים שנתיים, ואתם מחשבים את התשלום חודשי. מכיוון ש-B3 ריק, Excel יניח ריבית של 0% וייתן לכם תשלום חודשי המבוסס רק על התקופה והסכום.

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

2. הפעל את חיפוש המטרה כדי למצוא את הריבית

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

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

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

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

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

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

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

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

1. לדעת איזה ציון אתה צריך בבחינה האחרונה

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

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

ראשית, הזינו את הציונים הידועים בעמודה, לדוגמה, B2:B4. השאירו את תא B5 ריק מכיוון שזה יהיה הציון לבחינה הממשמשת ובאה, ובתא אחר, נניח B7, כתבו את נוסחת הממוצע: =AVERAGE(B2:B5)זה יהיה תא היעד שלך.

פתח חיפוש יעדים מנתונים > ניתוח "מה אם" > חיפוש יעדים ו אתה מגדיר את זה ככה:

• הגדר תאB7 (התא עם נוסחת הממוצע).
• ערך עבור: 70 (הממוצע שאתה רוצה להשיג).
• שינוי תאB5 (הציון לבחינה הקרובה).

לאחר האישור, אקסל יחשב את הערך שצריך להיות ב-B5 כך שהממוצע ב-B7 יהיה 70. לדוגמה, ייתכן שהוא יאמר לך שאתה צריך 85 במבחן האחרון כדי להגיע לממוצע הנדרש. ניתן להשתמש באותו רעיון עם ממוצעים משוקללים באמצעות פונקציות כגון SUMPRODUCT y SUMA, אם לכל מבחן יש משקל שונה.

2. ממוצעים משוקללים וציונים עם משקלים שונים

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

דרך אופיינית להגדיר זאת היא להציג את הדירוגים בעמודה אחת ואת המשקלים (כמספר עשרוני או באחוזים) בעמודה אחרת. ניתן להשתמש בשילוב SUMPRODUCT(ציונים;משקלים)/SUM(משקלים) כדי לקבל את הציון הסופי.

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

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

3. תכנון השקעות וחיסכון

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

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

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

4. זיכויים ומגבלות תקציב

אם נחזור להלוואות, אפשר ללכת צעד קדימה. דמיינו שחישבתם עם PAYMENT כמה עליכם לשלם מדי שנה עבור הלוואה של 12.000 אירו על פני 20 שנה עם ריבית של 5%. התוצאה היא תשלום של כ-962,91 אירו לשנה, אבל התקציב המקסימלי שלך הוא 900.

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

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

5. ניתוח יתרונות ומכירות נחוצות

בעולם העסקים, Goal Seek שימושי לשאלות כמו:כמה יחידות אני צריך למכור כדי להרוויח 50.000?"או באיזה מחיר אני צריך למכור כדי להשיג שולי רווח מסוימים, בהינתן עלות ויכולת ייצור?"

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

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

הבדלים בין חיפוש יעדים לפותר יעדים

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

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

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

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

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

בעיות נפוצות בעת שימוש ב-Goal Seek וכיצד להימנע מהן

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

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

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

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

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

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