חישוב ממוצע נע ב- Excel (פשוט, משוקלל ומעריך)

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

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

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

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

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

מהו ממוצע נע?

אני בטוח שאתה יודע מה הערך הממוצע.

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

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

לדוגמה, ביום השלישי, אם אשאל אותך את הטמפרטורה הממוצעת בתנועה של 3 ימים, תיתן לי את ערך הטמפרטורה הממוצע של יום 1, 2 ו 3. ואם ביום 4 אשאל אותך את הטמפרטורה הממוצעת בת 3 ימים. , תיתן לי את הממוצע של יום 2, 3 ו -4.

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

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

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

סוגי ממוצעים נעים

ישנם שלושה סוגים של ממוצעים נעים:

• ממוצע נע פשוט (SMA)
• ממוצע נע משוקלל (WMA)
• ממוצע נע אקספוננציאלי (EMA)

ממוצע נע פשוט (SMA)

זהו הממוצע הפשוט של נקודות הנתונים בזמן הנתון.

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

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

ממוצע נע משוקלל (WMA)

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

סביר יותר שהטמפרטורה ביום 10 תהיה אינדיקטור טוב יותר למגמה בהשוואה ליום הראשון (מכיוון שהטמפרטורה יורדת בכל יום שעובר).

אם כן, טוב לנו יותר אם נסתמך יותר על הערך של יום 10.

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

זה נקרא הממוצע הנע המשוקלל.

ממוצע נע אקספוננציאלי (EMA)

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

הוא נקרא גם הממוצע הנע המשוקלל האקספוננציאלי (EWMA)

ההבדל בין WMA ל- EMA הוא שעם WMA, אתה יכול להקצות משקולות על סמך כל קריטריון. לדוגמה, בממוצע נע של 3 נקודות, תוכל להקצות גיל משקל של 60% לנקודת הנתונים העדכנית ביותר, 30% לנקודת הנתונים האמצעית ו -10% לנקודת הנתונים הוותיקה ביותר.

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

די להרצאה סטטיסטית.

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

חישוב ממוצע נע פשוט (SMA) באמצעות Toolpak Analysis Data in Excel

ל- Microsoft Excel כבר יש כלי מובנה לחישוב הממוצעים הנעים הפשוטים.

קוראים לזה ה חבילת ניתוח נתונים.

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

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

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

1. לחץ על הכרטיסייה קובץ
2. לחץ על אפשרויות
3. בתיבת הדו-שיח אפשרויות Excel, לחץ על תוספות
4. בתחתית תיבת הדו-שיח, בחר תוספות Excel בתפריט הנפתח ולאחר מכן לחץ על עבור.
5. בתיבת הדו-שיח תוספות שנפתחת, סמן את האפשרות Analysis Toolpak
6. לחץ על אישור.

השלבים לעיל יאפשרו את חבילת כלי ניתוח הנתונים ותראה אפשרות זו בכרטיסיה נתונים כעת.

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

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

1. לחץ על הכרטיסייה נתונים
2. לחץ על האפשרות ניתוח נתונים
3. בתיבת הדו -שיח ניתוח נתונים, לחץ על האפשרות ממוצע נע (ייתכן שיהיה עליך לגלול מעט כדי להגיע אליה).
4. לחץ על אישור. פעולה זו תפתח את תיבת הדו -שיח 'ממוצע נע'.
5. בטווח הקלט, בחר את הנתונים שעבורם ברצונך לחשב את הממוצע הנע (B2: B11 בדוגמה זו)
6. באפשרות מרווח, הזן 3 (כפי שאנו מחשבים ממוצע נע של שלוש נקודות)
7. בטווח פלט, הזן את התא שבו אתה רוצה את התוצאות. בדוגמה זו, אני משתמש ב- C2 כטווח הפלט
8. לחץ על אישור

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

שים לב כי לשני התאים הראשונים בעמודה C יש את התוצאה כשגיאת #N/A. הסיבה לכך היא שזה ממוצע נע של שלוש נקודות וצריך לפחות שלוש נקודות נתונים כדי לתת את התוצאה הראשונה. אז ערכי הממוצע הנע בפועל מתחילים לאחר נקודת הנתונים השלישית ואילך.

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

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

חישוב ממוצעים נעים (SMA, WMA, EMA) באמצעות נוסחאות ב- Excel

ניתן גם לחשב את הממוצעים הנעים באמצעות נוסחת AVERAGE.

למעשה, אם כל מה שאתה צריך הוא הערך הממוצע הנע (ולא השגיאה הסטנדרטית או התרשים), שימוש בנוסחה יכול להיות אופציה טובה יותר (ומהירה יותר) מאשר השימוש ב- Tool Analysis Toolpak.

כמו כן, Toolpak לניתוח נתונים נותן רק את הממוצע הנע (SMA), אך אם ברצונך לחשב WMA או EMA, עליך להסתמך על נוסחאות בלבד.

חישוב ממוצע נע פשוט באמצעות נוסחאות

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

בתא C4, הזן את הנוסחה הבאה:

= ממוצע (B2: B4)

העתק נוסחה זו עבור כל התאים והיא תיתן לך את ה- SMA עבור כל יום.

זכור: בעת חישוב SMA באמצעות נוסחאות, עליך לוודא שההפניות בנוסחה הן יחסיות. המשמעות היא שהנוסחה יכולה להיות = ממוצע (B2: B4) או = ממוצע ($ B2: $ B4), אך היא אינה יכולה להיות = ממוצע ($ B $ 2: $ B $ 4) או = ממוצע (B $ 2: B $ 4 ). חלק מספר השורה של ההפניה צריך להיות ללא סימן הדולר. תוכל לקרוא עוד על הפניות מוחלטות ויחסיות כאן.

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

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

עבור WMA, עליך לדעת את המשקלים שיוקצו לערכים.

לדוגמה, נניח שעליך לחשב את ה- WMA של 3 הנקודות עבור מערך הנתונים שלהלן, שבו ניתן משקל של 60% לערך האחרון, 30% לזה שלפניו ו -10% מזה שלפניו.

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

= 0.6*B4+0.3*B3+0.1*B2

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

חישוב ממוצע נע אקספוננציאלי באמצעות נוסחאות

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

להלן הנוסחה לחישוב ה- EMA עבור ממוצע נע של שלוש נקודות:

EMA = [ערך אחרון - ערך EMA קודם] * (2 / N + 1) + EMA קודם

… כאשר N יהיה 3 בדוגמה זו (כפי שאנו מחשבים EMA של שלוש נקודות)

הערה: עבור ערך ה- EMA הראשון (כאשר אין לך ערך קודם לחישוב EMA), פשוט קח את הערך כפי שהוא ושקול אותו כערך ה- EMA. לאחר מכן תוכל להשתמש בערך זה בהמשך.

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

בתא C2, הזן את אותו ערך כמו ב- B2. הסיבה לכך היא שאין ערך קודם לחישוב EMA.

בתא C3, הזן את הנוסחה שלהלן והעתק עבור כל התאים:

= (B3-C2)*(2/4)+C2

בדוגמה זו שמרתי על הפשטות והשתמשתי בערך העדכני ובערך EMA הקודם לחישוב ה- EMA הנוכחי.

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

הוספת קו מגמה ממוצע נע לתרשים עמודות

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

נניח שיש לך מערך נתונים כפי שמוצג להלן:

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

1. בחר את מערך הנתונים (כולל הכותרות)
2. לחץ על הכרטיסייה הוספה
3. בקבוצה תרשים, לחץ על הסמל 'הכנס עמודה או תרשים עמודות'.
4. לחץ על האפשרות תרשים עמודות מקובצות. פעולה זו תוסיף את התרשים בגליון העבודה.
5. כאשר התרשים נבחר, לחץ על הכרטיסייה עיצוב (כרטיסייה זו מופיעה רק כאשר התרשים נבחר)
6. בקבוצה פריסות תרשים, לחץ על 'הוסף רכיב תרשים'.
7. העבר את הסמן על האפשרות 'קו מגמה' ולאחר מכן לחץ על 'אפשרויות קו מגמה נוספות'
8. בחלונית עיצוב קו מגמה, בחר באפשרות 'ממוצע נע' וקבע את מספר התקופות.

זהו זה! השלבים שלעיל יוסיפו קו מגמה נע לתרשים העמודות שלך.

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

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

עיצוב קו המגמה הממוצע הנע

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

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

• צֶבַע של הקו. אתה יכול להשתמש בזה כדי להדגיש את אחד מקווי המגמה על ידי כך שהכל בתרשים יהיה בהיר בצבע והופך את קו המגמה לקופץ עם צבע עז.
• ה עוֹבִי של הקו
• ה שְׁקִיפוּת של הקו

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

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