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

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

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

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

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

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

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

קבל מידע נוסף במאמר מבוא לWhat-If ניתוח.

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

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

השתמש בטבלת נתונים של משתנה אחד אם ברצונך לראות כיצד ערכים שונים של משתנה אחד בנוסחה אחת או יותר ישנה את התוצאות של נוסחאות אלה. לדוגמה, באפשרותך להשתמש בטבלת נתונים עם משתנה אחד כדי לראות כיצד תעריפי ריבית שונים משפיעים על תשלום משכנתה חודשי באמצעות הפונקציה 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 כדי לבצע ניתוח 'מה-אם' אם יש לך יעדים ספציפיים או קבוצות גדולות יותר של נתונים משתנים.

חתירה למטרה

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

Excel Solver

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