זהו המאמר השני בסדרת חמשת החלקים על ניתוח נתונים ב- Excel. בחלק זה, אראה לך כיצד להשתמש בטבלת נתונים משתנים ב- Excel.
מאמרים נוספים בסדרה זו:
- טבלת נתונים משתנה אחת ב- Excel.
- מנהל תרחישים באקסל.
- חיפוש מטרות ב- Excel.
- פותר אקסל.
צפה בוידאו - טבלת נתונים בעלת שני משתנים ב- Excel
טבלת נתונים עם שני משתנים מתאימה ביותר במצבים שבהם אתה רוצה לראות כיצד התוצאה הסופית משתנה כאשר שניים ממשתני הקלט משתנים בו זמנית (לעומת טבלת נתוני משתנים אחת שבה רק אחד משתני הקלט משתנה).
אם אתה רוצה לנתח נתונים כאשר יותר משני משתנים משתנים, מנהל התרחישים הוא הדרך הטובה ביותר.
מתי להשתמש בטבלת נתונים משתנים ב- Excel
נניח שיש לך מערך נתונים כפי שמוצג להלן:
במערך הנתונים לעיל, יש לנו את סכום ההלוואה, ריבית ומספר התשלומים החודשיים. בהתבסס על 3 משתני הקלט הללו, התשלום החודשי מחושב (הוא באדום מכיוון שהוא זרימת כסף). הנוסחה הבאה משמשת לחישוב התשלום החודשי:
= PMT (B2/12, B3, B1)
כעת ייתכן שתרצה לבצע ניתוח כדי לראות מה אמור להיות השילוב האידיאלי של סכום ההלוואה ומספר התשלום החודשי שיתאים לדרישתך. לדוגמה, ייתכן שתרצה להשאיר את התשלום החודשי על $ 500 או פחות ולנתח מה שילוב סכום ההלוואה והשכירות יכול לתת לך בכך.
במצב כזה, יש להשתמש בטבלת נתונים משתנה.
הגדרת טבלת נתונים משתנים ב- Excel
להלן השלבים להגדרת טבלת נתונים משתנה ב- Excel:
- בעמודה, יש את כל הערכים השונים שברצונך לבדוק עבור מספר התשלומים החודשיים. בדוגמה זו, אנו בודקים את 72, 84, 96 … 240. במקביל, יש את ערכי סכום ההלוואה השונים בשורה ממש מעל לערכי העמודה (מתחילים תא אחד מימין) כפי שמוצג בתמונה למטה.
- הקלד = B4 בתא D1, הנמצא בשורה אחת מעל הערכים בעמודה. זהו מבנה שצריך לעקוב אחריו כאשר אתה עובד עם טבלת נתונים משתנה. כמו כן, ודא שהערך בתא D1 תלוי בשני המשתנים (מספר התשלומים החודשיים וסכום ההלוואה). זה לא יעבוד אם תזין את הערך ידנית בתא D1.
במקרה זה, תא D1 מתייחס לתא B4, בעל הערך המחושב באמצעות נוסחה המשתמשת בתאים B1, B2 ו- B3.
- כעת הנתונים מוכנים לשמש לחישוב טבלת נתונים משתנה.
- בחר את הנתונים (D1: J16). עבור ללשונית נתונים -> כלי נתונים -> מה אם ניתוח -> טבלת נתונים
- בתיבה דיאלוג טבלת נתונים, השתמש בהפניות הבאות:
- תא קלט שורה: $ B $ 1
- תא קלט עמודה: $ B $ 3
- לחץ על אישור. ברגע שאתה לוחץ על אישור, הוא ממלא באופן מיידי את כל התאים הריקים בטווח הנתונים שנבחר. הוא נותן לך במהירות תצוגה של תשלומים חודשיים עבור שילובים שונים של סכום ההלוואה ומספר התשלומים החודשיים.
לדוגמה, אם ברצונך לזהות את השילובים של סכום ההלוואה ומספר התשלום החודשי שיגרמו לתשלום חודשי של פחות מ- $ 500 לחודש, תוכל פשוט להשתמש בשיטת טבלת הנתונים המשתנה הזו.
הערה:
- לאחר שחישבת את הערכים באמצעות טבלת נתונים, לא ניתן לבטל זאת באמצעות Control + Z. עם זאת תוכל לבחור באופן ידני את כל הערכים ולמחוק אותם.
- אינך יכול למחוק/לשנות תא אחד במכלול הערכים המחושבים. מכיוון שמדובר במערך, יהיה עליך למחוק את כל הערכים.
