לְהִצטַיֵן

נוסחה ממוצעת נעה

Moving Average Formula

נוסחת Excel: נוסחה ממוצעת נעהסיכום

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



כיצד לחשב את שווי ההשקעה העתידי באקסל
 
= AVERAGE (C5:C7)

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

להלן גמיש יותר אפשרות המבוססת על פונקציית OFFSET המטפל בתקופות משתנות.





לגבי ממוצעים נעים

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

הֶסבֵּר

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



 
= AVERAGE (C5:C7) // 3-day average

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

 
= AVERAGE (C5:C7) // 5-day average = AVERAGE (C5:C11) // 7-day average

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

כאשר הערכים מתווים בתרשים קווים, אפקט ההחלקה ברור:

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

מידע לא מספק

אם אתה מתחיל את הנוסחאות ב- ראשון בשורה של הטבלה, לנוסחאות הראשונות לא יהיו מספיק נתונים לחישוב ממוצע מלא, מכיוון שהטווח יתרחב מֵעַל שורת הנתונים הראשונה:

בעיית טווח ממוצע נע

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

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

 
= IF ( ROW ()- ROW ($C)+1<3, NA (), AVERAGE (C3:C5))

החלק הראשון של הנוסחה פשוט יוצר מספר שורה 'מנורמל', החל מ -1:

 
 ROW ()- ROW ($C)+1 // relative row number

בשורה 5 התוצאה היא 1, בשורה 6 התוצאה היא 2 וכן הלאה.

כאשר מספר השורה הנוכחי קטן מ -3, הנוסחה מחזירה #N/A. אחרת, הנוסחה מחזירה ממוצע נע כמו קודם. זה מחקה את אופן הפעולה של גרסת Analysis Toolpak של ממוצע נע, שיוצא מספר N/A עד להגעה לתקופה המלאה הראשונה.

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

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

תקופות משתנות עם OFFSET

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

 
= AVERAGE ( OFFSET (A1,0,0,-n,1))

כאשר n הוא מספר התקופות לכלול בכל ממוצע. כאמור, OFFSET מחזיר טווח המועבר לפונקציית AVERAGE. להלן תוכל לראות נוסחה זו בפעולה, כאשר 'n' הוא ה- טווח בשם E2. החל מתא C5, OFFSET בונה טווח המשתרע בחזרה לשורות קודמות. זה מושג באמצעות גובה שווה ל- n השלילי. כאשר E5 משתנה למספר אחר, הממוצע הנע מחושב מחדש בכל השורות:

איך עוברים לשורה הבאה באקסל

ממוצע נע עם פונקציית OFFSET

הנוסחה ב- E5, שהועתקה למטה, היא:

 
= AVERAGE ( OFFSET (C5,0,0,-n,1))

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

שנה ערכי ציר x באקסל

בדוגמה המוצגת, הממוצעים מחשבים בהצלחה מכיוון שפונקציית הממוצע מתעלם אוטומטית מערכי טקסט ותאים ריקים , ואין ערכים מספריים אחרים מעל C5. אז, בעוד שהטווח שעבר ל- AVERAGE ב- E5 הוא C1: C5, יש רק ערך אחד לממוצע, 100. עם זאת, ככל שתקופות יגדלו, OFFSET ימשיך ליצור טווח המשתרע. מעל תחילת הנתונים , בסופו של דבר נתקל בחלק העליון של גליון העבודה ומחזיר שגיאת #REF.

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

ממוצע נע עם פונקציית OFFSET וטווח מכוסה

 
= AVERAGE ( OFFSET (C5,0,0,-( MIN ( ROW ()- ROW ($C)+1,n)),1))

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

 
 MIN ( ROW ()- ROW ($C)+1,n)

בתוך MIN, הערך הראשון הוא a מספר שורה יחסי , מחושב עם:

 
 ROW ()- ROW ($C)+1 // relative row number..1,2,3, etc.

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

תכונה נחמדה באפשרות OFFSET היא שניתן לשנות בקלות את n. אם נשנה את n ל -7 ונשרטט את התוצאות, נקבל תרשים כזה:

תרשים ממוצע נע עם פונקציית OFFSET

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

קבצים מצורפים קוֹבֶץ ממוצע נע exceljet. xlsx הסופר דייב בראנס


^