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

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

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

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

רכיבים של פונקציית חיפוש יעדים

פונקציית חיפוש המטרה מורכבת משלושה פרמטרים, כלומר:

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

כיצד להשתמש ב-Goal Seek ב-Excel: דוגמה 1

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

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

אתה משתמש בנוסחה הבאה בתא B13 כדי לחשב את אחוז הרווח:

=(B12-B11)/B12

כעת, אתה רוצה לחשב את שולי הרווח כך שאחוז הרווח שלך יהיה 30%. אתה יכול לעשות זאת עם Goal Seek באקסל.

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

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

תיבת הדו-שיח Goal Seek תופיע עם 3 שדות:

הגדר תא – הזינו את הפניה לתא המכילה את הנוסחה (B13). זהו התא שיקבל את הפלט הרצוי.

להעריך – הזן את התוצאה הרצויה שאתה מנסה להשיג (30%).

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

כשתסיים, לחץ על 'אישור' כדי לבדוק זאת.

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

לחץ על 'אישור' ו-Excel ישנה את ערכי התא או לחץ על 'ביטול' כדי למחוק את הפתרון ולשחזר את הערך המקורי.

ערך הקלט (1635.5) בתא B12 הוא מה שגילינו באמצעות Goal Seek על מנת להשיג את המטרה שלנו (30%).

דברים שכדאי לזכור בעת שימוש ב-Goal Seek

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

מה לעשות כאשר Excel Goal Seek לא עובד

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

בדוק את הפרמטרים והערכים של חיפוש יעד

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

התאמת הגדרות האיטרציה

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

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

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

בקטע 'אפשרויות חישוב', שנה את ההגדרות הבאות:

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

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

צילום המסך למטה מציג את ערך ברירת המחדל:

ללא הפניות חוזרות

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

דוגמה 2 לחיפוש יעדים באקסל

נניח שאתה לווה כסף של '25,000$' ממישהו. הם מלווים לך את הכסף בריבית של 7% לחודש לתקופה של 20 חודשים, מה שהופך את ההחזר של '$1327' לחודש. אבל אתה יכול להרשות לעצמך לשלם רק "$1,000" לחודש למשך 20 חודשים עם ריבית של 7%. Goal Seek יכול לעזור לך למצוא את סכום ההלוואה שמייצר תשלום חודשי (EMI) של '$1000'.

הזן את שלושת משתני הקלט שאתה צריך כדי לחשב את חישוב ה-PMT שלך, כלומר ריבית של 7%, טווח של 20 חודשים וסכום קרן של $25,000.