12 เดือน เฉลี่ยเคลื่อนที่ Excel

ค่าเฉลี่ยเคลื่อนที่ตัวอย่างนี้สอนวิธีคำนวณค่าเฉลี่ยเคลื่อนที่ของชุดเวลาใน Excel ค่าเฉลี่ยเคลื่อนที่จะใช้เพื่อทำให้เกิดความผิดปกติ (ยอดเขาและหุบเขา) เพื่อรับรู้แนวโน้มได้ง่ายขึ้น 1. ขั้นแรกให้ดูที่ซีรี่ส์เวลาของเรา 2. ในแท็บข้อมูลคลิกการวิเคราะห์ข้อมูล หมายเหตุ: ไม่สามารถหาปุ่ม Data Analysis คลิกที่นี่เพื่อโหลด Add-in Analysis ToolPak 3. เลือก Moving Average และคลิก OK 4. คลิกที่กล่อง Input Range และเลือกช่วง B2: M2 5. คลิกที่ช่อง Interval และพิมพ์ 6. 6. คลิกที่ Output Range box และเลือก cell B3 8. วาดกราฟของค่าเหล่านี้ คำอธิบาย: เนื่องจากเราตั้งค่าช่วงเป็น 6 ค่าเฉลี่ยเคลื่อนที่คือค่าเฉลี่ยของ 5 จุดข้อมูลก่อนหน้าและจุดข้อมูลปัจจุบัน เป็นผลให้ยอดเขาและหุบเขาจะเรียบออก กราฟแสดงแนวโน้มที่เพิ่มขึ้น Excel ไม่สามารถคำนวณค่าเฉลี่ยเคลื่อนที่สำหรับจุดข้อมูล 5 จุดแรกได้เนื่องจากไม่มีจุดข้อมูลก่อนหน้านี้เพียงพอ 9. ทำซ้ำขั้นตอนที่ 2 ถึง 8 สำหรับช่วงที่ 2 และช่วงที่ 4 ข้อสรุป: ช่วงที่ใหญ่กว่ายอดเนินและหุบเขาจะยิ่งเรียบขึ้น ช่วงค่าเฉลี่ยที่ใกล้เคียงกับค่าเฉลี่ยเคลื่อนที่จะอยู่ที่จุดข้อมูลจริงการคำนวณค่าเฉลี่ย DAX เฉลี่ย 12 เดือนการคำนวณค่าเฉลี่ยของเดือนที่ 12 เดือนใน DAX ดูเหมือนเป็นงานง่ายๆ แต่จะซ่อนความซับซ้อนบางอย่าง บทความนี้อธิบายถึงวิธีการเขียนสูตรที่ดีที่สุดเพื่อหลีกเลี่ยงข้อผิดพลาดทั่วไปที่ใช้ฟังก์ชันอัจฉริยะเวลา เราเริ่มต้นด้วยแบบจำลองข้อมูล AdventureWorks ปกติโดยมีตารางผลิตภัณฑ์การขายและปฏิทิน ปฏิทินมีการทำเครื่องหมายเป็นตารางปฏิทิน (จำเป็นต้องทำงานกับฟังก์ชันข่าวกรองในเวลาใด ๆ ) และเราสร้างลำดับชั้นที่เรียบง่ายขึ้นเมื่อเดือนปีเดือน ด้วยการตั้งค่านี้เป็นเรื่องง่ายมากที่จะสร้าง PivotTable แรกที่แสดงยอดขายเมื่อเวลาผ่านไป: เมื่อทำการวิเคราะห์แนวโน้มถ้ายอดขายขึ้นอยู่กับฤดูกาลหรือโดยทั่วไปถ้าคุณต้องการลบผลกระทบของยอดขายและการลดลงของยอดขาย เทคนิคทั่วไปคือการคำนวณค่าในช่วงเวลาที่กำหนดโดยปกติแล้วจะเป็น 12 เดือนและโดยเฉลี่ย ค่าเฉลี่ยของการกลิ้งมากกว่า 12 เดือนแสดงให้เห็นถึงแนวโน้มที่ดีและมีประโยชน์มากในแผนภูมิ เมื่อได้รับวันที่เราสามารถคำนวณค่าเฉลี่ยกลิ้ง 12 เดือนโดยใช้สูตรนี้ซึ่งยังคงมีปัญหาบางอย่างที่เราจะแก้ในภายหลัง: ลักษณะการทำงานของสูตรง่าย: คำนวณมูลค่าการขายหลังจากสร้างตัวกรองในปฏิทินที่ แสดงข้อมูลครบหนึ่งปี หลักของสูตรคือ DATESBETWEEN ซึ่งจะส่งคืนชุดวันที่ระหว่างขอบเขตทั้งสอง ด้านล่างคือ: การอ่านจากด้านในสุด: ถ้าเราแสดงข้อมูลเป็นเดือนหนึ่งพูดว่ากรกฎาคม 2007 เราจะใช้วันที่มองเห็นล่าสุดโดยใช้ LASTDATE ซึ่งจะส่งกลับในวันสุดท้ายในเดือนกรกฎาคม 2550 จากนั้นเราจะใช้ NEXTDAY เพื่อเข้าร่วมการแข่งขันครั้งที่ 1 ของเดือนสิงหาคม 2007 และในที่สุดเราก็ใช้ SAMEPERIODLASTYEAR เพื่อเปลี่ยนกลับมาเป็นเวลาหนึ่งปีโดยให้ผลผลิตตั้งแต่วันที่ 1 สิงหาคม 2549 ขอบเขตด้านบนเป็นเพียงแค่ LASTDATE คือปลายเดือนกรกฎาคม 2007 หากเราใช้สูตรนี้ในตารางสาระสำคัญผลลัพธ์ก็ดูดี แต่เรา มีปัญหาสำหรับวันที่ล่าสุด: ในความเป็นจริงตามที่เห็นในรูปภาพค่าจะถูกคำนวณอย่างถูกต้องจนถึงปี 2008 จากนั้นไม่มีค่าในปี 2009 (ซึ่งเป็นราคาที่ถูกต้องเราไม่มียอดขายในปี 2009) แต่มี มูลค่าที่น่าแปลกใจในเดือนธันวาคม 2010 ซึ่งสูตรของเราแสดงยอดรวมทั้งหมดแทนที่จะเป็นมูลค่าที่ว่างเปล่าตามที่เราคาดไว้ ในความเป็นจริงเมื่อวันที่ธันวาคม LASTDATE จะส่งคืนในวันสุดท้ายของปีและ NEXTDAY จะกลับวันที่ 1 มกราคม 2011 แต่ NEXTDAY เป็นฟังก์ชันข้อมูลอัจฉริยะเวลาและคาดว่าจะส่งคืนชุดวันที่ที่มีอยู่ ความจริงข้อนี้ไม่ค่อยชัดเจนเท่าไรนัก ฟังก์ชันอัจฉริยะเวลาไม่ได้ทำคณิตศาสตร์ในวันที่ ถ้าคุณต้องการใช้วันหลังจากวันที่ที่ระบุคุณสามารถเพิ่ม 1 ลงในคอลัมน์วันใดก็ได้และผลลัพธ์จะเป็นวันถัดไป แต่ฟังก์ชันการทำงานของข้อมูลอัจฉริยะเวลาจะเปลี่ยนชุดวันที่ไปมาตลอดเวลา ดังนั้น NEXTDAY ใช้ข้อมูลของตน (ในกรณีของเราคือตารางแถวเดียวที่มีวันที่ 31 ธันวาคม 2010) และจะเลื่อนออกไปหนึ่งวันในภายหลัง ปัญหาคือผลลัพธ์ควรเป็น 1 มกราคม 2011 แต่เนื่องจากตารางปฏิทินไม่ประกอบด้วยวันที่ดังกล่าวผลลัพธ์คือ BLANK ดังนั้นนิพจน์ของเราคำนวณยอดขายที่มีขอบเขตต่ำกว่าที่ว่างเปล่านั่นหมายถึงจุดเริ่มต้นของเวลาทำให้ผลผลิตรวมเป็นยอดขาย หากต้องการแก้ไขสูตรก็เพียงพอที่จะเปลี่ยนลำดับการประเมินผลของขอบเขตล่าง: ตามที่คุณเห็นตอนนี้เรียกว่า NEXTDAY หลังการเปลี่ยนแปลงหนึ่งปีย้อนหลัง ด้วยวิธีนี้เราใช้เวลา 31 ธันวาคม 2010 ย้ายไปที่ 31 ธันวาคม 2009 และใช้วันถัดไปซึ่งเป็นวันที่ 1 มกราคม 2010: วันที่มีอยู่ในตารางปฏิทิน ผลที่ได้คือตอนนี้เป็นที่คาดหวัง: ในตอนนี้เราต้องแบ่งเฉพาะตัวเลขดังกล่าวเป็น 12 เพื่อให้ได้ค่าเฉลี่ยของการกลิ้ง แต่เท่าที่คุณสามารถนึกภาพได้ง่ายเราไม่สามารถหารด้วย 12 ได้อย่างแท้จริงในตอนต้นของช่วงเวลาไม่มี 12 เดือนนับรวม แต่เป็นตัวเลขที่ต่ำกว่า เราจำเป็นต้องคำนวณจำนวนเดือนที่มียอดขาย ซึ่งสามารถทำได้โดยใช้การกรองข้ามของตารางปฏิทินด้วยตารางการขายหลังจากที่เราใช้บริบทใหม่ 12 เดือน เรากำหนดมาตรการใหม่ที่คำนวณจำนวนเดือนที่มีอยู่ในช่วง 12 เดือน: คุณสามารถดูในรูปถัดไปว่าการวัด Month12M คำนวณค่าที่ถูกต้อง: เป็นมูลค่าที่ควรทราบว่าสูตรนี้ไม่ได้ผลถ้าคุณเลือกช่วงเวลา นานกว่า 12 เดือนเนื่องจาก CalendarMonthName มีเพียง 12 ค่าเท่านั้น หากต้องการระยะเวลานานขึ้นคุณจะต้องใช้คอลัมน์ YYYYMM เพื่อให้สามารถนับได้มากกว่า 12 ส่วนที่น่าสนใจของสูตรนี้ที่ใช้การกรองข้ามคือการคำนวณจำนวนเดือนที่สามารถใช้งานได้แม้ว่าคุณจะใช้ตัวกรองอื่น แอตทริบิวต์ ตัวอย่างเช่นหากคุณเลือกสีฟ้าโดยใช้เครื่องตัดแล้วการขายจะเริ่มขึ้นในเดือนกรกฎาคม 2007 (ไม่ใช่ในปี 2005 เนื่องจากเกิดขึ้นกับสีอื่น ๆ ) การใช้ตัวกรองไขว้ในการขายสูตรนี้คำนวณได้อย่างถูกต้องว่าในเดือนกรกฎาคม 2007 มียอดขายใน Blue เพียงเดือนเดียว: ณ ขณะนี้ค่าเฉลี่ยของการกลิ้งคือ DIVIDE: เมื่อเราใช้ในตาราง Pivot เรายังคงใช้ มีปัญหาเล็กน้อย: ในความเป็นจริงค่าจะคำนวณเป็นเดือนที่ไม่มีการขาย (เช่นเดือนในอนาคต): สามารถแก้ไขได้โดยใช้คำสั่ง IF เพื่อป้องกันไม่ให้สูตรแสดงค่าเมื่อไม่มียอดขาย ฉันไม่มีอะไรต่อต้าน IF แต่สำหรับการติดยาเสพติดในหมู่คุณคุณควรจดจำว่า IF อาจเป็นฆาตกรที่มีประสิทธิภาพเนื่องจากอาจบังคับให้เครื่องยนต์สูตร DAX เตะขึ้นมาในกรณีเฉพาะนี้ความแตกต่างเล็กน้อย แต่ ตามกฎทั่วไปวิธีที่ดีที่สุดในการลบคุณค่าเมื่อไม่มีการขายคือการพึ่งพาสูตรเครื่องมือเก็บข้อมูลแบบสแตนด์อโลนเช่นนี้เปรียบเทียบแผนภูมิโดยใช้ Avg12M กับอีกรุ่นหนึ่งที่แสดงยอดขาย แสดงแนวโน้มในแบบที่สะอาดมาก: แจ้งให้ฉันทราบเกี่ยวกับบทความที่กำลังจะมาถึง (จดหมายข่าว) ยกเลิกการเลือกดาวน์โหลดไฟล์ได้อย่างอิสระวิธีการคำนวณค่าเฉลี่ยเคลื่อนที่ใน Excel การวิเคราะห์ข้อมูล Excel สำหรับ Dummies รุ่นที่ 2 คำสั่งการวิเคราะห์ข้อมูลมีเครื่องมือสำหรับคำนวณค่าเฉลี่ยที่เคลื่อนที่และค่าเฉลี่ยที่คล่องตัวใน Excel สมมติว่าเพื่อให้เห็นภาพประกอบคุณได้รวบรวมข้อมูลอุณหภูมิประจำวันแล้ว คุณต้องการคำนวณค่าเฉลี่ยเคลื่อนที่สามวัน 8212 โดยเฉลี่ยในสามวันที่ผ่านมา 8212 ซึ่งเป็นส่วนหนึ่งของการพยากรณ์อากาศที่เรียบง่าย หากต้องการคำนวณค่าเฉลี่ยเคลื่อนที่สำหรับชุดข้อมูลนี้ให้ทำตามขั้นตอนต่อไปนี้ หากต้องการคำนวณค่าเฉลี่ยเคลื่อนที่ก่อนคลิกปุ่มคำสั่ง Data Analysis ข้อมูล tab8217s เมื่อ Excel แสดงไดอะล็อกบ็อกซ์การวิเคราะห์ข้อมูลเลือกรายการย้ายค่าเฉลี่ยจากรายการแล้วคลิกตกลง Excel จะแสดงกล่องโต้ตอบ Moving Average ระบุข้อมูลที่คุณต้องการใช้เพื่อคำนวณค่าเฉลี่ยเคลื่อนที่ คลิกในกล่องข้อความ Input Range ของกล่องโต้ตอบ Moving Average จากนั้นระบุช่วงการป้อนข้อมูลโดยพิมพ์ที่อยู่ช่วงเวิร์กชีทหรือใช้เมาส์เพื่อเลือกช่วงของแผ่นงาน การอ้างอิงช่วงของคุณควรใช้ที่อยู่ของเซลล์สัมบูรณ์ แอดเดรสเซลล์ที่แน่นอนนำหน้าด้วยตัวอักษรคอลัมน์และหมายเลขแถวที่มีเครื่องหมายเช่นใน A1: A10 ถ้าเซลล์แรกในช่วงอินพุทของคุณมีป้ายข้อความเพื่อระบุหรืออธิบายข้อมูลของคุณให้เลือกช่องทำเครื่องหมายในช่องแรก ในกล่องข้อความช่วงบอก Excel จำนวนค่าที่จะรวมไว้ในการคำนวณค่าเฉลี่ยเคลื่อนที่ คุณสามารถคำนวณค่าเฉลี่ยเคลื่อนที่โดยใช้จำนวนค่าใด ๆ โดยค่าเริ่มต้น Excel จะใช้ค่าล่าสุดสามค่าในการคำนวณค่าเฉลี่ยเคลื่อนที่ หากต้องการระบุว่าจะใช้ค่าอื่น ๆ เพื่อคำนวณค่าเฉลี่ยเคลื่อนที่ให้ป้อนค่านั้นลงในช่องข้อความช่วงเวลา บอก Excel ให้ใส่ข้อมูลค่าเฉลี่ยเคลื่อนที่ ใช้กรอบข้อความ Output Range เพื่อระบุช่วงเวิร์กชีตที่คุณต้องการวางข้อมูลค่าเฉลี่ยเคลื่อนที่ ในตัวอย่างเวิร์กชีทข้อมูลค่าเฉลี่ยเคลื่อนที่จะถูกใส่ลงในช่วงเวิร์กชีท B2: B10 (ไม่บังคับ) ระบุว่าคุณต้องการใช้แผนภูมิหรือไม่ ถ้าคุณต้องการแผนภูมิที่แปลงข้อมูลค่าเฉลี่ยเคลื่อนที่ให้เลือกช่องทำเครื่องหมายแผนภูมิเอาท์พุท (ไม่บังคับ) ระบุว่าคุณต้องการคำนวณข้อมูลข้อผิดพลาดมาตรฐานหรือไม่ ถ้าคุณต้องการคำนวณข้อผิดพลาดมาตรฐานสำหรับข้อมูลให้เลือกกล่องกาเครื่องหมายข้อผิดพลาดมาตรฐาน Excel วางค่าความผิดพลาดมาตรฐานถัดจากค่าเฉลี่ยที่เคลื่อนที่ได้ (ข้อมูลข้อผิดพลาดมาตรฐานจะปรากฏเป็น C2: C10) หลังจากที่คุณระบุข้อมูลค่าเฉลี่ยเคลื่อนที่ที่คุณต้องการคำนวณและตำแหน่งที่คุณต้องการแล้วคลิกตกลง Excel คำนวณข้อมูลค่าเฉลี่ยเคลื่อนที่ หมายเหตุ: หาก Excel doesn8217t มีข้อมูลเพียงพอที่จะคำนวณค่าเฉลี่ยเคลื่อนที่สำหรับข้อผิดพลาดมาตรฐานระบบจะวางข้อความแสดงข้อผิดพลาดลงในเซลล์ คุณสามารถเห็นเซลล์หลายเซลล์ที่แสดงข้อความแสดงข้อผิดพลาดนี้เป็นค่า