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

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

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

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

הערה: באפשרותך לבצע חישובים מהירים יותר באמצעות טבלאות נתונים ו-Visual Basic for Applications (VBA). לקבלת מידע נוסף, ראה טבלאות נתונים של Excel מה-אם: חישוב מהיר יותר באמצעות VBA.

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

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

    • ב- Excel 2007, לחץ על לחצן Microsoft Office תמונת לחצן Office , לחץ על אפשרויות Excelולאחר מכן לחץ על הקטגוריה נוסחאות .

    • בכל הגירסאות האחרות, לחץ על File > options > נוסחאות.

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

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

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

חתירה למטרה

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

Excel Solver

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

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

כאן, טבלת הנתונים היא טווח התאים B2: D8. באפשרותך לשנות את הערך ב-B4, סכום ההלוואה והתשלומים החודשיים בעמודה D מתעדכנים באופן אוטומטי. באמצעות שיעור ריבית של 3.75%, הפונקציה D2 מחזירה תשלום חודשי של $1,042.01 באמצעות נוסחה זו: = PMT (C2/12, $B $3, $B $4).

טווח תאים זה, B2:D8, מהווה טבלת נתונים

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

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

בחוברת עבודה חיה זו, התא D2 מכיל את נוסחת התשלום =PMT (C2/12, $B $3, $B $4). תא B3 הוא התא המשתנה , שבו באפשרותך לחבר אורך טווח שונה (מספר תקופות תשלום חודשיות). בתא D2, הפונקציה PMT מתחברת בשיעור הריבית, המחברות ב-360 חודשים והלוואה של $225,000, ומחשבת את התשלום החודשי של $1,042.01.

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

בחוברת עבודה חיה זו, תא C3 מכיל את נוסחת התשלום, =PMT ($B $3/12, $B $2, B4), המשתמשת בשני תאים משתנים, B2 ו-B3. בתא C2, הפונקציה PMT מתחברת בשיעור הריבית 3.875%%, 360 חודשים והלוואה של $225,000, ומחשבת את התשלום החודשי של $1,058.03.

זקוק לעזרה נוספת?

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

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

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×