Excel ניתוח רגישות (מדגם טבלת נתונים)

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

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

היכולות של טבלאות הנתונים הן אלמנטים של "מה אם" ניתוח מתבצעת לעתים קרובות באמצעות Microsoft Excel. זהו השם השני של ניתוח הרגישות.

כללי

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

מידע בסיסי אודות שולחנות נתונים

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

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

אחד

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

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

שקול את שיטת הניתוח בדוגמה של חנות ספרים קטנה, כאשר רק 100 ספרים זמינים. חלקם יכולים להיות נמכרים יותר יקר (50 $), השאר יעלה קונים זול יותר ($ 20). סך כל ההכנסות ממכירת כל הסחורה נועדו - הבעלים החליט כי במחיר גבוה של 60% מהספרים. יש צורך לגלות איך ההכנסות יגדל אם תגדיל את המחיר של נפח גדול יותר של סחורות - 70% וכן הלאה.

1. בחר מרחק תא חינם מקצה הסדין ולכתוב את הנוסחה בו: = התא של ההכנסות הכולל. לדוגמה, אם ההכנסה נרשמת בתא C14 (ייעוד אקראי מצוין), יש צורך לכתוב ככה: = C14.
2. אנו כותבים את כמות הסחורה בעמודה משמאל לתא זה - לא תחת זה, חשוב מאוד.
3. אנו מקצים את טווח התאים שבו עמודה הריבית נמצאת וקישור לסך ההכנסה.

3.

1. אנו מוצאים בכרטיסייה "נתונים" של "ניתוח" מה אם "" ולחץ עליו - בתפריט שנפתח, עליך לבחור את האפשרות "טבלת נתונים".

ארבעה

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

חָמֵשׁ

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

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

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

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

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

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

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

6.

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

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

7 האצת חישובים לגיליונות המכילים טבלאות נתונים

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

1. פתח את החלון הפרמטרים, בחר את הסעיף "נוסחה" בתפריט בצד ימין.
2. בחר את הפריט "באופן אוטומטי, למעט לוחות נתונים" ב "חישובים בספר".

שמונה

1. לבצע את החישוב של התוצאות בצלחת באופן ידני. בשביל זה אתה צריך להדגיש את הנוסחאות ולחץ על מקש F

כלים אחרים לביצוע ניתוח רגישות

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

1. "הבחירה של הפרמטר" פונקציה מתאימה אם התוצאה הרצויה ידועה, והיא נדרשת לגלות את ערך הקלט של המשתנה כדי לקבל תוצאה כזו.
2. "חיפוש פתרון" הוא תוספת כדי לפתור בעיות. יש צורך ליצור מגבלות ולהצביע עליהם, ולאחר מכן המערכת תמצא את התשובה. הפתרון נקבע על ידי שינוי הערכים.
3. ניתוח רגישות יכול להתבצע באמצעות מנהל התסריט. כלי זה נמצא בתפריט "מה אם" בכרטיסייה נתונים. הוא מחליף את הערכים למספר תאים - הסכום יכול להגיע 32. המשגר ​​משווה ערכים אלה, והמשתמש אינו חייב לשנות אותם באופן ידני. דוגמה של יישום מנהל Scripting:

תֵשַׁע

ניתוח הרגישות של פרויקט ההשקעה ב- Excel

שיטה לניתוח רגישות בתחום ההשקעה

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

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

נוּסחָה

ניתן לחשב את הרגישות של הקובץ המצורף באמצעות נוסחה זו: שנה את פרמטר הפלט ב-% / שינוי בפרמטר הקלט שב-%.

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

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

דוגמה לניתוח הרגישות של פרויקט ההשקעה ב- Excel

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

10.

1. למלא את הטבלה כדי לנתח את הפרויקט על זה.

אחד עשר

1. חישוב תזרים המזומנים באמצעות תפקוד התזוזה. בשלב הראשוני, הזרם שווה להשקעות. לאחר מכן אנו משתמשים בנוסחה: = אם (עקירה (מספר, 1,) = 2; סכומים (לזרוע 1: יצוא 1); סכומים (לזרוע 1: יצוא 1) + $ B $ 5) העניינים של תאים בנוסחה עשויים להיות שונה, זה תלוי בטבלת מיקום. בסופו של דבר, הערך של הנתונים הראשונים מתווסף - ערך החיסול.

12.

1. אנו מגדירים את המועד האחרון שעבורו ישתלם הפרויקט. לתקופה הראשונית, אנו משתמשים בנוסחה זו: = שקט (G7: G17; "0, ראשון d.potok, 0). הפרויקט נמצא בנקודת הפסקה במשך 4 שנים.

13.

1. צור עמודה למספרים של תקופות אלה כאשר הפרויקט משלם.

ארבעה עשר

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

חֲמֵשׁ עֶשׂרֵה

1. קביעת מקדם הניכרת עבור נוסחה זו: = 1 / (1 + דיסק.) מספר.

שש עשרה

1. לחשב את הערך הנוכחי על ידי כפל - תזרים המזומנים כפול שיעור הנחה.

17.

1. חישוב PI (מדד הרווחיות). הערך הנתון במגזר הזמן מחולק לקשרים בתחילת פיתוח הפרויקט.

שמונה עשרה

1. אנו מגדירים את קצב הרווחים הפנימי באמצעות הפונקציה של EMD: = FMR (טווח תזרים מזומנים).

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

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

פקטור ו ניתוח פיזור ב- Excel עם אוטומציה של חישובים

ניתוח פיזור ב- Excel

מטרת ניתוח כזה היא לחלק את השונות של גודל של שלושה מרכיבים:

1. השתנות כתוצאה מהשפעת ערכים אחרים.
2. שינויים בשל היחסים של ערכים המשפיעים עליו.
3. שינויים אקראיים.

בצע ניתוח פיזור באמצעות Excel Add-on ניתוח נתונים ". אם זה לא מופעל, זה יכול להיות מחובר בפרמטרים.

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

תשעה - עשר

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

עשרים

1. מלא את שורות תיבת הדו-שיח. Interlet Interval הוא כל התאים מבלי לקחת בחשבון את הכובעים והמספרים. אנו מקבחים על עמודות. תגיד תוצאות על גיליון חדש.

21.

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

ניתוח גורם ב Excel: דוגמה

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

22.

1. יש צורך לגלות אילו סחורות הביקוש לחודש השני גדלה בחודש השני. אנו עושים טבלה חדשה כדי לקבוע את הצמיחה ואת הפחתת הביקוש. הצמיחה מחושבת על פי נוסחה זו: = אם (((דרישה 2-דרישה 1)> 0; ביקוש 2 - ביקוש 1; 0). הנוסחה של הירידה: = אם (צמיחה = 0, הביקוש הוא 1- דרישה 2, 0).

23.

1. חישוב הצמיחה בביקוש לסחורות באחוזים: = אם (צמיחה / סה"כ 2 = 0; הפחתה / סה"כ 2, גובה / סה"כ 2).

24.

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

25 ניתוח פיזור של שני פקטור ב- Excel

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

26.

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

27.

המחוון F גדול מ F-Critical - משמעות הדבר היא כי הרצפה משפיעה על שיעור התגובה לצליל.

28.

סיכום

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

הודעה ניתוח הרגישות ב- Excel (טבלת נתונים לדוגמה) הופיעה תחילה לטכנולוגיית המידע.