חישוב תוצאות מרובות באמצעות טבלת נתונים

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

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

סוגים של ניתוחי "מה-אם"    

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

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

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

קבל מידע נוסף במאמר מבוא ל \ניתוח "מה-אם".

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

טבלאות נתונים של משתנה אחד    

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

באיור הבא, תא D2 מכיל את נוסחת התשלום =PMT(B3/12,B4,-B5), המפנה לתא הקלט B3.

טבלאות נתונים עם שני משתנים    

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

באיור הבא, תא C2 מכיל את נוסחת התשלום =PMT(B3/12,B4,-B5), המשתמשת בשני תאי קלט, B3 ו- B4.

 

חישובי טבלת נתונים    

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

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

בצע שלבים אלה:

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

2. בצע אחת מהפעולות הבאות:

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

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

3. בחר את התא או את טווח התאים המכילים את הערכים, את עיצובי התא או את הנוסחאות שברצונך להעתיק. באיור שלעיל, טווח זה הוא C2:D5.

4. בכרטיסי נתונים, בחר ניתוח "מה-אם" > טבלת נתונים (בקבוצה כלי נתונים או בקבוצת תחזית של Excel 2016).

5. בצע אחת מהפעולות הבאות:

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

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

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

נוסחאות המשמשות בטבלת נתונים עם משתנה אחד חייבות להפנות לאותו תא קלט.

בצע שלבים אלה

1. בצע אחת מהפעולות הבאות:

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

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

2. בחר את טווח התאים המכיל את טבלת הנתונים ואת הנוסחה החדשה.

3. בכרטיסי נתונים, בחר ניתוח "מה-אם" > טבלת נתונים (בקבוצה כלי נתונים או בקבוצת תחזית של Excel 2016).

4. בצע אחת מהפעולות הבאות:

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

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

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

בצע שלבים אלה:

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

   בדוגמה הבאה - שבה הערכים ההתחלתיים של הנוסחה מוזנים בתאים B3,‏ B4 ו- B5, עליך להקליד את הנוסחה =PMT(B3/12,B4,-B5) בתא C2.

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

   במקרה זה, הקלד את תעריפי הריבית השונים בתאים C3,‏ C4 ו- C5.

3. הזן את הרשימה השנייה באותה שורה שבה מופיעה הנוסחה - משמאלה.

   הקלד את תנאי ההלוואה (בחודשים) בתאים D2 ו- E2.

4. בחר את טווח התאים המכיל את הנוסחה (C2), הן את שורת הערכים והן את עמודת הערכים (C3:C5 ו- D2:E2) ואת התאים שבהם ברצונך לחשב את הערכים המחושבים (D3:E5).

   במקרה זה, בחר את הטווח C2:E5.

5. בכרטיסיה נתונים, בקבוצה כלי נתונים או בקבוצה תחזית (ב- Excel 2016), בחר ניתוח "מה-אם" > טבלת נתונים (בקבוצה כלי נתונים או בקבוצה תחזית של Excel 2016).

6. בשדה תא הקלט שורה, הזן את ההפניה לתא הקלט עבור ערכי הקלט בשורה.
   הקלד את תא B4 בתיבה תא קלט של שורה.

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

8. בחר אישור.

דוגמה לטבלת נתונים בעלת שני משתנים

טבלת נתונים בעלת שני משתנים יכולה להציג כיצד שילובים שונים של תעריפי ריבית ותנאי הלוואה ישפיעו על תשלום משכנתה חודשי. באיור כאן, תא C2 מכיל את נוסחת התשלום =PMT(B3/12,B4,-B5), המשתמשת בשני תאי קלט, B3 ו- B4.

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

בצע שלבים אלה כדי לשפר את ביצועי החישוב:

1. בחר קובץ > אפשרויות > נוסחאות.

2. במקטע אפשרויות חישוב, בחר אוטומטי.

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

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

חתירה למטרה

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

כלי העזר לפתרון של Excel

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