חיזוי מחיר המניות בשוק ניירות הערך באקסל. מודל ממוצע נע

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

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

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

אנו משתמשים ב- Excel כדי לשרטט את ההיסטוגרמה של MACD.

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

לאחר שקבענו את הדרישות לפורמט של הנתונים שהורדת, אנו מקבלים קובץ עם נתונים בפורמט csv, אשר אקסל מבין. כמו כן, ניתן להוריד נתונים היסטוריים על המכשיר המעניין אותנו באתר האינטרנט של הברוקר ZAO FINAM po הקישור הזה.

2) יש לעצב את הנתונים כמתואר ב.

התוצאה הסופית צריכה להיות משהו כזה:

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

4) עכשיו בואו נחשב את הממוצע הנע המעריכ עם חלון של 12 ימים (EMA 12). EMA 12 מחושב לפי הנוסחה:

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

הבה נעתיק את הנוסחה שהתקבלה לתא למטה ונערוך אותה מעט: במקום הערך מתא B3, בחלק השני של הנוסחה, נחליף את הערך מתא C2. C2 - זה יהיה ה-EMA12 של היום הקודם.

זה אמור להתברר כך:

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

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

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

הבה נעתיק את הנוסחה שהתקבלה לתא שלמטה ונערוך אותה מעט: במקום הערך מתא B3, בחלק השני של הנוסחה, נחליף את הערך מתא D2. D2 - זה יהיה ה-EMA26 של היום הקודם. זה אמור להתברר כך:

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

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

כעת עלינו לחשב את הממוצע הנע המעריכי של תשעה ימים עבור קו ה-MACD ה"מהיר". הקו המתקבל ייקרא קו MACD "אות". נחשוב לפי הנוסחה הבאה:

באופן דומה, אנו ממלאים את נוסחת החישוב באקסל בתא מימין לשורת ה-MACD ה"מהירה":

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

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

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

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

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

ובאמצעות לחיצה עם כפתור העכבר השמאלי על הסולם עם ערכי המחיר, ניתן לשנות את פורמט הנתונים של ציר התרשים של הגרף. לאחר פריצה כזו, סולם הערכים של הציר האנכי (במקרה שלנו) מודגש עם מסגרת מלבנית. ברגע שמופיעה מסגרת כזו, עליך ללחוץ על לחצן העכבר הימני כדי לקרוא לתפריט ההקשר. בתפריט ההקשר עם לחצן העכבר השמאלי, בחר את השורה<Формат оси…>, ככה:

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

תחת חלון תרשים המחירים, הכנס חלון להיסטוגרמת MACD העתידית. בחר את הכרטיסייה בתפריט הראשי<<Вставка>> ואז תפריט משנה<<Гистограмма>> ובחר את סמל ההיסטוגרמה השמאלי העליון מהתפריט הנפתח, סמל זה מסומן בצהוב בצילום המסך למטה:

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

לפני שקוראים לתפריט<<Гистограмма>> זה יהיה נחמד להעביר את הסמן מעל תא A16 וללחוץ על לחצן העכבר השמאלי. לאחר הכנסת ההיסטוגרמה, עלינו לציין את העמודה שלנו עם הנתונים המחושבים של היסטוגרמה MACD. לשם כך, העבר את סמן העכבר מעל ההיסטוגרמה ולחץ על לחצן העכבר הימני כדי לקרוא לתפריט ההקשר לניהול הדיאגרמה. בתפריט ההקשר שנפתח, בחר את הפריט<Выбрать данные>:

לאחר לחיצה על הכפתור<<Добавить>> בחלון הקודם, עלינו להקליד את שם התרשים שלנו - "MACD", ובשורה התחתונה, לחץ על הכפתור מימין לשורה:

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

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

נחזור לגיליון עם השם "GRAPHS" בחלון בחירת הנתונים לבניית היסטוגרמה, נלחץ גם על הכפתור<>:

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

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

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

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

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

שימוש בממוצעים נעים באקסל

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

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

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

מְשִׁימָה. נתח את הכנסות החברה במשך 11 חודשים וערוך תחזית לחודש ה-12.

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

סטיות יחסיות:

סטיות תקן:

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

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

ערך ההכנסה החזוי לחודש ה-12 הוא 9,430 דולר.



שימוש בתוסף Analysis ToolPak

לדוגמה, בואו ניקח את אותה בעיה.

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

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

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

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

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

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

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

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

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

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

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

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

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

שיטה זו משמשת לחיזוי לטווח קצר. נוסחת העבודה שלו היא:

דוגמה לשימוש בשיטת הממוצע הנע לפיתוח תחזית

מְשִׁימָה . ישנם נתונים המאפיינים את רמת האבטלה באזור, %

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

פתרון ממוצע נע

כדי לחשב את ערך התחזית באמצעות שיטת הממוצע הנע, עליך:

1. קבע את הערך של מרווח ההחלקה, למשל שווה ל-3 (n = 3).

2. חשב את הממוצע הנע לשלוש התקופות הראשונות
m פברואר \u003d (Uyanv + Ufev + U March) / 3 \u003d (2.99 + 2.66 + 2.63) / 3 \u003d 2.76
הערך המתקבל מוזן בטבלה באמצע התקופה שנלקחה.
לאחר מכן, אנו מחשבים את m עבור שלוש התקופות הבאות פברואר, מרץ, אפריל.
מרץ \u003d (Ufev + Umart + Uapr) / 3 \u003d (2.66 + 2.63 + 2.56) / 3 \u003d 2.62
בנוסף, באנלוגיה, אנו מחשבים את m עבור כל שלוש תקופות סמוכות ומכניסים את התוצאות בטבלה.

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

כאשר t + 1 היא תקופת התחזית; t היא התקופה שקדמה לתקופת התחזית (שנה, חודש וכו'); Уt+1 - מחוון חזוי; mt-1 - ממוצע נע לשתי תקופות לפני התחזית; n הוא מספר הרמות הכלולות במרווח ההחלקה; Ut - ערכה בפועל של התופעה הנבדקת לתקופה הקודמת; Уt-1 הוא הערך האמיתי של התופעה הנחקרת במשך שתי תקופות שקדמו לתקופת התחזית.

נובמבר = 1.57 + 1/3 (1.42 - 1.56) = 1.57 - 0.05 = 1.52
קבע את הממוצע הנע m לאוקטובר.
m = (1.56+1.42+1.52) /3 = 1.5
אנחנו עושים תחזית לדצמבר.
דצמבר = 1.5 + 1/3 (1.52 - 1.42) = 1.53
קבע את הממוצע הנע m לנובמבר.
m = (1.42+1.52+1.53) /3 = 1.49
אנחנו עושים תחזית לינואר.
ינואר = 1.49 + 1/3 (1.53 - 1.52) = 1.49
שמנו את התוצאה בטבלה.

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

ε = 9.01/8 = 1.13% דיוק התחזיתגָבוֹהַ.

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

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

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

1. חשב את מקדמי העונתיות לתחזית בשיטת הממוצע הנע.

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

עד חודש:

• מקדם ינואר - היחס בין מקדם עונתיות ינואר שנוקה מצמיחה לדצמבר;

  פברואר - מקדם פברואר עד ינואר;

  מרץ - מרץ עד פברואר;

עד חודשיים:

• עבור ינואר - היחס בין גורם העונתיות של ינואר לערך הממוצע של דצמבר ונובמבר

  עבור פברואר - פברואר מחולק בערך הממוצע של המקדמים של ינואר ודצמבר

  עבור מרץ - מרץ לממוצע של מקדמי פברואר וינואר

עד 3 חודשים:

• כדי לקבוע את מקדם העונתיות של ינואר ב-3 חודשים, אנו מחלקים את מקדם העונתיות של ינואר שנוקה מצמיחה בערך הממוצע של מקדם העונתיות שנוקה מצמיחה עבור דצמבר, נובמבר, אוקטובר;

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

  לחודש מרץ - היחס בין מרץ לערך הממוצע של מקדמי עונתיות שנוקו מצמיחה בדצמבר, ינואר ופברואר;

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

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

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

1. בואו לחשב את תחזית המכירות בשיטת הממוצע הנע עד החודש הראשון:

נפח המכירות של דצמבר \u003d נובמבר מוכפל בגורם העונתיות של דצמבר לחודש הקודם.

1. חשב את תחזית המכירות בשיטת הממוצע הנע לחודשיים:

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

1. אנו מחשבים את התחזית בשיטת הממוצע הנע ל-3 חודשים:

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

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

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

3. חשב את תחזית המכירות בשיטת הממוצע הנע.

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

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

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

4. גורמים נוספים שיש לקחת בחשבון בעת ​​חישוב תחזית המכירות.

כדי לשפר את דיוק התחזית, חשוב:

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

תחזיות מדויקות עבורך!

Forecast4AC PRO מחשב את התחזית בשיטת הממוצע הנע בו זמנית עבור יותר מ-1000 סדרות זמןבלחיצת כפתור, באופן משמעותי חוסך לך זמן אחת מ-4 דרכים:

לממוצע של שתי התקופות הקודמות

לממוצע של שלוש התקופות הקודמות

לממוצע של 4 התקופות הקודמות

ממוצע כפול ל-3 ו-4 תקופות קודמות

הצטרף אלינו!

הורד אפליקציות חינמיותעבור חיזוי וניתוח עסקי:

• Novo Forecast Lite- אוטומטי חישוב תחזית V לְהִצטַיֵן.
• 4analytics- ניתוח ABC-XYZוניתוח פליטות ב לְהִצטַיֵן.
• Qlik Senseשולחן עבודה ו-QlikViewמהדורה אישית - מערכות BI לניתוח נתונים והדמיה.

בדוק את התכונות של פתרונות בתשלום:

• Novo Forecast PRO- חיזוי באקסל עבור מערכי נתונים גדולים.

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

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

שיטה 1: חבילת ניתוח

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

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

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

1. עבור לכרטיסייה "נתונים"ולחץ על הכפתור "ניתוח נתונים", אשר ממוקם על סרט הכלים בבלוק "אָנָלִיזָה".



2. רשימה של כלים זמינים ב- חבילת ניתוח. בחרו שם מהם "ממוצע נע"ולחץ על הכפתור בסדר.



3. חלון הזנת הנתונים עבור חיזוי ממוצע נע מופעל.

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

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

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

   כדאי גם לסמן את התיבה שליד "שגיאות סטנדרטיות".

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

   לאחר ביצוע כל ההגדרות, לחץ על הכפתור בסדר.



4. התוכנית מציגה את תוצאת העיבוד.



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

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

   בשטח "הַפסָקָה"לשים מספר "2".

   בשטח "מרווח יציאה"ציין את הכתובת של הטווח הריק החדש, אשר, שוב, צריך להיות תא אחד יותר מרווח הקלט.

   שאר ההגדרות נשארות זהות. לאחר מכן לחץ על הכפתור בסדר.



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

שיטה 2: השתמש בפונקציה AVERAGE

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

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

קודם כל, אנו מחשבים את הערכים הממוצעים עבור שתי התקופות הקודמות באמצעות הפונקציה מְמוּצָע. אנחנו יכולים לעשות זאת רק החל ממרץ, שכן לתאריכים מאוחרים יותר יש הפסקה בערכים.

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



2. החלון מופעל אשפי פונקציות. קטגוריה "סטָטִיסטִי"מחפש משמעות "מְמוּצָע", בחר בו ולחץ על הכפתור בסדר.



3. נפתח חלון טיעוני מפעיל מְמוּצָע. התחביר שלו הוא הבא:

   AVERAGE(מספר1, מספר2,...)

   נדרש רק טיעון אחד.

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



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



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



6. כעת בחר את התא בעמודה הריקה הבאה בשורה עבור אפריל. קריאה לחלון הארגומנטים של הפונקציה מְמוּצָעבאותו אופן כפי שתואר קודם לכן. בשטח "מספר 1"הזן את הקואורדינטות של התאים בעמודה "הַכנָסָה"מינואר עד מרץ. לאחר מכן לחץ על הכפתור בסדר.



7. בעזרת ידית המילוי, העתק את הנוסחה לתאי הטבלה למטה.



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



9. קטגוריה "מָתֵימָטִי"סמן את שם הפונקציה שרירי בטן. לחץ על הכפתור בסדר.



10. חלון הארגומנטים של הפונקציה מופעל שרירי בטן. בתחום היחיד "מספר"ציין את ההבדל בין תוכן התאים בעמודות "הַכנָסָה"ו "2 חודשים"למאי. לאחר מכן לחץ על הכפתור בסדר.



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



12. אנו מחשבים את הערך הממוצע של הסטייה המוחלטת עבור כל התקופה באמצעות הפונקציה שכבר מוכרת לנו מְמוּצָע.



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



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



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



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



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



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



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

    SQRT(SUMDIFF(B6:B12,C6:C12)/COUNT(B6:B12))

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



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



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



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



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

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