อัตราส่วน การ เคลื่อนไหว เฉลี่ย วิธี ใน excel

Moving Average ตัวอย่างนี้สอนวิธีการคำนวณค่าเฉลี่ยเคลื่อนที่ของชุดข้อมูลเวลาใน Excel ค่าเฉลี่ยเคลื่อนที่จะใช้เพื่อทำให้จุดสูงสุดและที่ราบสูงเป็นไปอย่างราบรื่นเพื่อให้ทราบถึงแนวโน้มต่างๆได้ง่ายขึ้นอันดับแรกลองดูที่ชุดข้อมูลเวลาของเรา คลิกการวิเคราะห์ข้อมูลคลิกที่นี่เพื่อโหลด Add-In Toolkit การวิเคราะห์ 3 เลือก Moving Average และคลิก OK.4 คลิกในกล่อง Input Range และเลือกช่วง B2 M2 5. คลิกที่ช่อง Interval และพิมพ์ 6.6 คลิกที่ Output Range และเลือกเซลล์ B3.8 วาดกราฟของค่าเหล่านี้การอธิบายเนื่องจากเราตั้งค่าช่วงเป็น 6 ค่าเฉลี่ยเคลื่อนที่คือค่าเฉลี่ยของ 5 จุดข้อมูลก่อนหน้าและ จุดข้อมูลปัจจุบันเป็นผลให้ยอดและหุบเขาถูกทำให้ราบเรียบกราฟแสดงแนวโน้มการเพิ่มขึ้น Excel ไม่สามารถคำนวณค่าเฉลี่ยเคลื่อนที่สำหรับจุดข้อมูล 5 จุดแรกเนื่องจากไม่มีจุดข้อมูลก่อนหน้านี้มากพอ 9 ทำซ้ำตามขั้นตอนที่ 2 ถึง 8 สำหรับช่วง 2 และช่วงเวลา 4. บทสรุป The la rger ช่วงเวลาที่ยอดเนินและหุบเขาจะเรียบขึ้นช่วงเวลาที่มีขนาดเล็กยิ่งใกล้กับค่าเฉลี่ยเคลื่อนที่จะเป็นจุดข้อมูลที่แท้จริงเมื่อคำนวณค่าเฉลี่ยเคลื่อนที่ที่ใช้งานอยู่การวางค่าเฉลี่ยในช่วงเวลากลางจะทำให้รู้สึกได้ ตัวอย่างก่อนหน้านี้เราคำนวณค่าเฉลี่ยของช่วงเวลา 3 ช่วงแรกและวางไว้ข้างงวด 3 เราสามารถวางค่าเฉลี่ยในช่วงกลางของช่วงเวลาสามช่วงคือถัดจากช่วงเวลา 2 นี่ทำงานได้ดีกับช่วงเวลาแปลก ๆ , แต่ไม่ดีสำหรับช่วงเวลาที่แม้กระทั่งเวลาที่เราจะวางค่าเฉลี่ยเคลื่อนที่ครั้งแรกเมื่อ M 4.Techically, Moving Average จะลดลงที่ t 2 5, 3 5. เพื่อหลีกเลี่ยงปัญหานี้เราเรียบ MA s ใช้ M 2 ดังนั้น เราเรียบค่าที่ราบรื่นหากเราเฉลี่ยจำนวนแม้ของคำที่เราต้องเรียบค่าเรียบตารางต่อไปนี้จะแสดงผลโดยใช้ M 4. การดำเนินการตามตารางการปรับฤดูกาลและเรียบ smoothing. It เป็นตรงไปตรงมาเพื่อดำเนินการตามฤดูกาล adjustm ent และให้พอดีกับรูปแบบการคำนวณแบบเลขแจงโดยใช้ Excel ภาพหน้าจอและแผนภูมิด้านล่างนี้นำมาจากสเปรดชีตที่ได้รับการตั้งค่าเพื่อแสดงการปรับฤดูกาลตามฤดูกาลและการเพิ่มความเรียบตามเชิงเส้นให้กับข้อมูลการขายรายไตรมาสต่อไปนี้จาก Outboard Marine เพื่อรับสำเนาสเปรดชีต ไฟล์ตัวเองคลิกที่นี่รุ่นเรียบเรียบชี้แจงที่จะใช้ที่นี่เพื่อวัตถุประสงค์ในการสาธิตเป็นรุ่น Brown s เพียงเพราะสามารถใช้กับคอลัมน์เดียวของสูตรและมีเพียงหนึ่งเรียบสม่ำเสมอเพื่อเพิ่มประสิทธิภาพมักจะดีกว่า เพื่อใช้รุ่นของ Holt ที่มีค่าคงที่ที่ราบเรียบแยกต่างหากสำหรับระดับและแนวโน้มกระบวนการคาดการณ์ดำเนินการดังนี้ i ก่อนข้อมูลมีการปรับฤดูกาล ii แล้วการคาดการณ์จะถูกสร้างขึ้นสำหรับข้อมูลที่ปรับฤดูกาลด้วยการทำให้เป็นลอนตามข้อมูลเชิงเส้นและ iii ในที่สุดการคาดการณ์ที่ปรับฤดูกาลแล้วคือ ไม่ได้รับการคาดการณ์สำหรับชุดเดิมการปรับฤดูกาล กระบวนการดำเนินการในคอลัมน์ D ถึง G ขั้นตอนแรกในการปรับฤดูกาลคือการคำนวณค่าเฉลี่ยเคลื่อนที่ที่ศูนย์กลางที่ดำเนินการในคอลัมน์ D ซึ่งสามารถทำได้โดยการใช้ค่าเฉลี่ยโดยเฉลี่ยของทั้งสองปีที่สองซึ่งจะหักล้างด้วยระยะเวลาหนึ่ง สัมพัทธ์กับแต่ละอื่น ๆ การรวมกันของค่าเฉลี่ยออฟเซ็ตสองค่าแทนที่จะเป็นค่าเฉลี่ยเพียงอย่างเดียวเป็นสิ่งจำเป็นสำหรับจุดศูนย์กลางเมื่อจำนวนของฤดูกาลเป็นขั้นตอนต่อไปคือการคำนวณอัตราส่วนเป็นค่าเฉลี่ยเคลื่อนที่ - เช่นเดิมข้อมูลหารด้วยค่าเฉลี่ยเคลื่อนที่ใน ในแต่ละช่วงเวลาซึ่งทำในคอลัมน์ E นี่เรียกว่าส่วนประกอบของเทรนด์วัฏจักรของรูปแบบตราบใดที่แนวโน้มและผลกระทบของวงจรธุรกิจอาจได้รับการพิจารณาว่าเป็นข้อมูลทั้งหมดที่ยังคงอยู่หลังจากเฉลี่ยในช่วงข้อมูลทั้งปี แน่นอนการเปลี่ยนแปลงรายเดือนที่ไม่ได้เกิดจากฤดูกาลอาจถูกกำหนดโดยปัจจัยอื่น ๆ จำนวนมาก แต่ค่าเฉลี่ยเฉลี่ย 12 เดือนจะราบเรียบเหนือพวกเขาในระดับที่ดีดัชนีฤดูกาลโดยประมาณสำหรับแต่ละฤดูกาลจะคำนวณโดย f irst เฉลี่ยทุกอัตราส่วนสำหรับฤดูเฉพาะที่ทำในเซลล์ G3-G6 โดยใช้สูตร AVERAGEIF อัตราส่วนโดยเฉลี่ยจะถูกปรับใหม่เพื่อให้ผลรวมถึง 100 เท่าของจำนวนงวดในหนึ่งฤดูกาลหรือ 400 ในกรณีนี้ ซึ่งจะทำในเซลล์ H3-H6 ด้านล่างในคอลัมน์ F สูตร VLOOKUP ใช้เพื่อแทรกค่าดัชนีตามฤดูกาลที่เหมาะสมในแต่ละแถวของตารางข้อมูลตามไตรมาสของปีที่แสดงถึงค่าเฉลี่ยเคลื่อนที่ที่ศูนย์กลางและข้อมูลที่ปรับฤดูกาล ดูเหมือนว่าค่าเฉลี่ยเคลื่อนที่โดยปกติแล้วดูเหมือนว่าจะมีซีรีส์ที่ปรับเปลี่ยนตามฤดูกาลแบบเรียบขึ้นและสั้นลงทั้งสองด้านแผ่นงานอื่น ๆ ในไฟล์ Excel เดียวกันแสดงการประยุกต์ใช้รูปแบบการเพิ่มความเรียบแบบเชิงเส้นให้เป็นตัวเลขตามฤดูกาล ข้อมูลที่ปรับแล้วค่าเริ่มต้นในคอลัมน์ค่า GA สำหรับอัลฟาคงที่ที่ราบเรียบถูกป้อนเหนือคอลัมน์พยากรณ์ที่นี่ในเซลล์ H9 และเพื่อความสะดวกมันถูกกำหนดให้ชื่อช่วงอัลฟ่าชื่อมีการกำหนด โดยใช้คำสั่ง Insert Name Create โมเดล LES ได้รับการเตรียมใช้งานโดยการตั้งค่าการคาดการณ์สองชุดแรกเท่ากับค่าที่แท้จริงครั้งแรกของชุดที่ปรับฤดูกาลตามฤดูกาลสูตรที่ใช้ในการพยากรณ์ LES คือรูปแบบการเรียกซ้ำรูปสมการเดียวของแบบ Brown's สูตรนี้ ถูกป้อนลงในเซลล์ที่สอดคล้องกับระยะเวลาสามที่นี่เซลล์ H15 และคัดลอกลงจากที่นั่นสังเกตว่าการคาดการณ์ LES สำหรับรอบระยะเวลาปัจจุบันหมายถึงข้อสังเกตที่สองก่อนหน้าและสองข้อผิดพลาดที่คาดการณ์ก่อนหน้าเช่นเดียวกับค่าของ alpha ดังนั้น สูตรการคาดการณ์ในแถว 15 หมายถึงเฉพาะข้อมูลที่มีอยู่ในแถว 14 และก่อนหน้านี้แน่นอนว่าถ้าเราต้องการใช้แบบเรียบง่ายแทนการใช้การทำให้เรียบแบบเสวนาเชิงเส้นเราสามารถแทนที่สูตร SES ได้ที่นี่แทนนอกจากนี้เรายังสามารถใช้ Holt แทนได้ Brown's LES model ซึ่งจะต้องใช้สองคอลัมน์เพิ่มเติมของสูตรในการคำนวณระดับและแนวโน้มที่ใช้ในการคาดการณ์ข้อผิดพลาดจะคำนวณในคอลัมน์ถัดไปที่นี่คอลัมน์ J โดยการลบค่าพยากรณ์ออกจากค่าที่แท้จริงข้อผิดพลาดหลักหมายถึงกำลังสองคำนวณเป็นรากที่สองของการแปรปรวนของข้อผิดพลาดบวกสี่เหลี่ยมของค่าเฉลี่ยดังต่อไปนี้จากข้อผิดพลาดทางคณิตศาสตร์ข้อผิดพลาดของตัวแปร MSE ข้อผิดพลาด AVERAGE 2 ในการคำนวณค่าเฉลี่ยและความแปรปรวนของ ข้อผิดพลาดในสูตรนี้สองช่วงแรกจะถูกแยกออกเนื่องจากโมเดลไม่เริ่มคาดการณ์จริงจนกว่าจะถึงช่วงที่สามของช่วง 15 ในสเปรดชีตค่าที่ดีที่สุดของอัลฟาสามารถหาได้ด้วยการเปลี่ยนอัลฟาด้วยตนเองจนกว่าจะพบ RMSE ต่ำสุดหรือ ค่าอื่น ๆ ที่คุณสามารถใช้ Solver เพื่อดำเนินการลดค่าที่แน่นอนค่า alpha ที่ Solver พบจะแสดงที่นี่ alpha 0 471. โดยปกติแล้วควรวางแผนให้ข้อผิดพลาดของโมเดลในหน่วยแปลงและคำนวณและแปลงความสัมพันธ์กัน ที่ล่าช้าถึงหนึ่งฤดูกาลนี่คือพล็อตชุดอนุกรมเวลาของข้อผิดพลาดที่ปรับตามฤดูกาลข้อผิดพลาดเกี่ยวกับการคำนวณอัตโนมัติจะคำนวณโดยใช้ฟังก์ชัน CORREL เพื่อคำนวณค่าความร่วมมือ ความสัมพันธ์ของข้อผิดพลาดกับตัวเองล่าช้าโดยหนึ่งหรือหลายงวด - รายละเอียดจะแสดงในรูปแบบสเปรดชีตนี่คือพล็อตของ autocorrelations ของข้อผิดพลาดที่ห้าล่าช้าแรก autocorrelations ที่ lags 1 ถึง 3 มีความใกล้เคียงกับศูนย์, แต่ความล่าช้าที่ความล่าช้า 4 มีค่าเป็น 0 35 เป็นเรื่องลำบากเล็กน้อย - มันแสดงให้เห็นว่ากระบวนการปรับฤดูกาลไม่ได้รับความสำเร็จอย่างสมบูรณ์ แต่ก็เป็นเพียงเล็กน้อยสำคัญ 95 ความสำคัญวงสำหรับการทดสอบว่า autocorrelations แตกต่างกันอย่างมีนัยสำคัญจากศูนย์เป็นประมาณ plus-or-minus 2 SQRT nk โดยที่ n คือขนาดของกลุ่มตัวอย่างและ k คือความล่าช้าที่นี่ n คือ 38 และ k จะแตกต่างกันไปตั้งแต่ 1 ถึง 5 ดังนั้นรากที่สี่ของ - n-minus-k มีค่าประมาณ 6 สำหรับทั้งหมด ของพวกเขาและด้วยเหตุนี้ขีด จำกัด สำหรับการทดสอบความสำคัญทางสถิติของการเบี่ยงเบนจากศูนย์เป็นประมาณบวกหรือลบ 2 6 หรือ 0 33 ถ้าคุณเปลี่ยนแปลงค่าของอัลฟาด้วยมือในรูปแบบ Excel นี้คุณสามารถสังเกตเห็นผลกระทบต่อ อนุกรมเวลาและพล็อตอิสระของ e rrors และข้อผิดพลาดของราก - หมายถึงสี่เหลี่ยมซึ่งจะแสดงด้านล่างที่ด้านล่างของสเปรดชีตสูตรการคาดการณ์จะถูกบูตเข้าสู่อนาคตโดยการแทนการคาดการณ์ค่าจริง ณ จุดที่ข้อมูลจริงทำงาน out - คือในกรณีที่อนาคตเริ่มต้นขึ้นกล่าวอีกนัยหนึ่งในเซลล์แต่ละเซลล์ที่มีค่าข้อมูลในอนาคตจะมีการอ้างอิงเซลล์ซึ่งจะชี้ไปที่การคาดการณ์ที่ทำขึ้นในช่วงเวลานั้นทุกสูตรอื่น ๆ จะถูกคัดลอกมาจากด้านบน ข้อผิดพลาดสำหรับการคาดการณ์ในอนาคตจะคำนวณทั้งหมดเป็นศูนย์ซึ่งไม่ได้หมายความว่าข้อผิดพลาดที่เกิดขึ้นจริงจะเป็นศูนย์ แต่เป็นเพียงการสะท้อนถึงข้อเท็จจริงที่ว่าเพื่อวัตถุประสงค์ในการคาดการณ์เราจะสมมติว่าข้อมูลในอนาคตจะเท่ากับการคาดการณ์โดยเฉลี่ย ส่งผลให้การคาดการณ์ LES สำหรับข้อมูลที่ปรับฤดูกาลแล้วมีลักษณะเช่นนี้ด้วยค่า alpha นี้โดยเฉพาะซึ่งเป็นค่าที่เหมาะสมสำหรับการคาดการณ์ในหนึ่งรอบข้างแนวโน้มที่คาดการณ์จะเพิ่มขึ้นเล็กน้อย rend ที่ได้รับการตรวจสอบในช่วง 2 ปีที่ผ่านมาหรือสำหรับค่าอัลฟาอื่น ๆ อาจมีการคาดการณ์แนวโน้มที่แตกต่างกันได้โดยปกติการดูแนวโน้มในระยะยาวเมื่ออัลฟามีความแตกต่างกัน ที่ดีที่สุดสำหรับการคาดการณ์ในระยะสั้นจะไม่จำเป็นต้องเป็นค่าที่ดีที่สุดสำหรับการทำนายอนาคตอันไกลไกลมากขึ้นตัวอย่างเช่นนี่คือผลที่ได้รับถ้าค่าของอัลฟาถูกตั้งด้วยตนเองเป็น 0 25 คาดการณ์แนวโน้มในระยะยาวคือ ตอนนี้เชิงลบมากกว่าบวกด้วยค่า alpha ที่เล็กลงรูปแบบจะให้น้ำหนักกับข้อมูลเก่ามากขึ้นในการประมาณระดับปัจจุบันและแนวโน้มและการคาดการณ์ระยะยาวจะสะท้อนถึงแนวโน้มการลดลงที่เกิดขึ้นในช่วง 5 ปีที่ผ่านมาแทนที่จะเป็น แนวโน้มที่เพิ่มขึ้นล่าสุดแผนภูมินี้ยังแสดงให้เห็นอย่างชัดเจนว่ารูปแบบที่มีค่าน้อยลงของอัลฟาจะช้าลงเพื่อตอบสนองต่อจุดหักเหของข้อมูลและดังนั้นจึงมีแนวโน้มที่จะทำให้ข้อผิดพลาดของเครื่องหมายเดียวกันในช่วงเวลาหลายช่วงเวลา ข้อผิดพลาดในการคาดการณ์ล่วงหน้า 1 ขั้นตอนมีขนาดใหญ่กว่าค่าเฉลี่ยที่ได้รับก่อน RMSE เท่ากับ 34 4 มากกว่า 27 4 และมีความเกี่ยวพันกันในทางบวกอย่างมากความคลาดเคลื่อนของความสัมพันธ์ที่ล่าช้า 0 56 มีค่ามากกว่า 0 33 ที่คำนวณข้างต้นอย่างมีนัยสำคัญทางสถิติ zero ในฐานะที่เป็นทางเลือกหนึ่งในการลดค่า alpha เพื่อให้เป็นที่อนุรักษ์นิยมมากขึ้นในการคาดการณ์ในระยะยาวปัจจัยการผันผวนของแนวโน้มบางครั้งจะถูกเพิ่มลงในแบบจำลองเพื่อที่จะทำให้แนวโน้มที่คาดการณ์แผ่ออกไปหลังจากไม่กี่ขั้นตอนขั้นตอนสุดท้าย ดังนั้นการคาดการณ์ในคอลัมน์ I เป็นเพียงผลิตภัณฑ์ของดัชนีตามฤดูกาลในคอลัมน์ F และการคาดการณ์ LES ที่ปรับตามฤดูกาลในคอลัมน์ H. นี่คือการคาดการณ์ของ LES โดยใช้ดัชนีตามฤดูกาลที่เหมาะสม ค่อนข้างง่ายในการคำนวณช่วงความเชื่อมั่นสำหรับการคาดการณ์ล่วงหน้าอย่างน้อยหนึ่งครั้งที่ทำโดยโมเดลนี้ก่อนคำนวณ RMSE root-mean-squared error ซึ่งเป็นเพียง s quare ของ MSE และคำนวณช่วงความเชื่อมั่นสำหรับการคาดการณ์ที่ปรับตามฤดูกาลโดยการเพิ่มและลบสองครั้ง RMSE โดยทั่วไปช่วงความเชื่อมั่น 95 สำหรับการคาดการณ์ล่วงหน้าหนึ่งรอบจะเท่ากับการคาดการณ์จุดบวกหรือลบ - สองครั้งค่าเบี่ยงเบนมาตรฐานโดยประมาณของข้อผิดพลาดในการคาดการณ์สมมติว่าการกระจายข้อผิดพลาดมีค่าประมาณปกติและขนาดตัวอย่างมีขนาดใหญ่พอพูดว่า 20 หรือมากกว่านี้ RMSE แทนที่จะเป็นค่าเบี่ยงเบนมาตรฐานของข้อผิดพลาดคือค่าประมาณที่ดีที่สุด ค่าเบี่ยงเบนมาตรฐานของข้อผิดพลาดในการคาดการณ์ในอนาคตเนื่องจากจะใช้รูปแบบการสุ่มและความผันผวนของการคาดการณ์ด้วยความมั่นใจขีดจำกัดความเชื่อมั่นสำหรับการคาดการณ์ที่ปรับฤดูกาลแล้วจะถูกปรับใหม่พร้อมกับการคาดการณ์โดยคูณด้วยดัชนีตามฤดูกาลที่เหมาะสมในกรณีนี้ RMSE จะเท่ากับ 27 4 และการคาดการณ์ที่ปรับฤดูกาลสำหรับงวดแรกในเดือน ธ. ค. -96 เป็น 273 2 ดังนั้นช่วงความเชื่อมั่น 95 ที่ปรับฤดูกาลแล้วจะมาจาก 273 2-2 27 4 218 4 to 273 2 2 27 4 328 0 การคูณข้อ จำกัด เหล่านี้ตามดัชนีฤดูกาลธันวาคมของ 68 61 เราได้รับความเชื่อมั่นด้านล่างและด้านบนของ 149 8 และ 225 0 รอบการคาดการณ์จุดที่ 93 ของ 187 4. ข้อ จำกัด ของการคาดการณ์สำหรับการคาดการณ์เพิ่มเติม เนื่องจากระยะเวลาที่คาดการณ์จะเพิ่มขึ้นเนื่องจากความไม่แน่นอนเกี่ยวกับระดับและแนวโน้มตลอดจนปัจจัยตามฤดูกาล แต่เป็นการยากที่จะคำนวณโดยทั่วไปด้วยวิธีการวิเคราะห์วิธีที่เหมาะสมในการหาค่าความเชื่อมั่นสำหรับ LES คาดการณ์โดยใช้ทฤษฎี ARIMA แต่ความไม่แน่นอนในดัชนีตามฤดูกาลเป็นเรื่องอื่นหากคุณต้องการช่วงความเชื่อมั่นที่สมจริงสำหรับการคาดการณ์มากกว่าหนึ่งรอบระยะเวลาข้างหน้าโดยคำนึงถึงแหล่งที่มาทั้งหมดของข้อผิดพลาดในบัญชีทางออกที่ดีที่สุดของคุณคือการใช้วิธีเชิงประจักษ์สำหรับ ตัวอย่างเช่นหากต้องการได้รับช่วงความเชื่อมั่นสำหรับการคาดการณ์ล่วงหน้า 2 ก้าวคุณสามารถสร้างคอลัมน์อื่นในสเปรดชีตเพื่อคำนวณการคาดการณ์ล่วงหน้า 2 ขั้นตอนในทุกๆช่วงเวลาโดยการเริ่มต้นใช้งานขั้นตอนเดียวล่วงหน้าสำหรับ recast จากนั้นคำนวณ RMSE ข้อผิดพลาดในการคาดการณ์ล่วงหน้า 2 ขั้นตอนและใช้ข้อมูลนี้เป็นพื้นฐานสำหรับช่วงความมั่นใจแบบ 2 ขั้นตอน