การพยากรณ์ใน Excel โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ การคำนวณค่าเฉลี่ยเคลื่อนที่ใน Excel และการคาดการณ์
- คำนวณ ค่าสัมประสิทธิ์ฤดูกาล;
- เลือก ระยะเวลาในการคำนวณค่าเฉลี่ยค่านิยม;
- คำนวณการคาดการณ์, เช่น. คูณค่าเฉลี่ยด้วยปัจจัยฤดูกาล
- บัญชี ปัจจัยเพิ่มเติมซึ่งส่งผลกระทบอย่างมีนัยสำคัญต่อยอดขาย
คำนวณ พยากรณ์โดยใช้วิธีเคลื่อนที่ธรรมดามาก แค่. สำหรับสิ่งนี้เราใช้เวลา ค่าเฉลี่ยเช่น ยอดขายเฉลี่ยในช่วง 3 เดือนที่ผ่านมา และ คูณด้วยปัจจัยตามฤดูกาลภายใน 3 เดือน - และการคาดการณ์สำหรับเดือนนั้นก็พร้อมแล้ว เราทำเช่นเดียวกันในเดือนหน้า เฉพาะเดือนคาดการณ์ก่อนหน้าเท่านั้นที่จะรวมอยู่ในการคำนวณแล้ว
1. มาคำนวณค่าสัมประสิทธิ์ฤดูกาลสำหรับการพยากรณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่
สำหรับสิ่งนี้เรานับ ค่าสัมประสิทธิ์ฤดูกาลเคลียร์การเติบโตดังที่อธิบายไว้ในบทความ “จะคำนวณค่าสัมประสิทธิ์ฤดูกาลที่ปราศจากการเติบโตได้อย่างไร” . จากนั้นเราก็กำหนด ค่าสัมประสิทธิ์ฤดูกาลกับช่วงเวลาก่อนหน้า, ภายใน 1 เดือน, 2 เดือน, 3 เดือน ฯลฯ ขึ้นอยู่กับช่วงเวลาที่เราใช้ค่าเฉลี่ยในการพยากรณ์ยอดขาย ตัวอย่างเช่น ลองคำนวณค่าสัมประสิทธิ์ฤดูกาลรายเดือน (ดูเอกสารแนบ "การคำนวณค่าสัมประสิทธิ์")
ค่าสัมประสิทธิ์เดือนมกราคม - อัตราส่วนของค่าสัมประสิทธิ์ฤดูกาลเดือนมกราคมที่เคลียร์การเติบโตต่อเดือนธันวาคม
กุมภาพันธ์ - กุมภาพันธ์ ค่าสัมประสิทธิ์ถึงมกราคม;
มีนาคม - มีนาคมถึงกุมภาพันธ์
สำหรับเดือนมกราคม - อัตราส่วนของค่าสัมประสิทธิ์ฤดูกาลของเดือนมกราคมต่อค่าเฉลี่ยของเดือนธันวาคมและพฤศจิกายน
สำหรับเดือนกุมภาพันธ์-กุมภาพันธ์ หารด้วยค่าเฉลี่ยของค่าสัมประสิทธิ์เดือนมกราคมและธันวาคม
สำหรับเดือนมีนาคม-มีนาคม ถึงค่าเฉลี่ยของค่าสัมประสิทธิ์เดือนกุมภาพันธ์และมกราคม
เพื่อกำหนดค่าสัมประสิทธิ์ฤดูกาลของเดือนมกราคมสำหรับเดือนที่ 3 เราจะหารค่าสัมประสิทธิ์ฤดูกาลของเดือนมกราคม โดยล้างการเติบโตออกด้วยค่าเฉลี่ยของค่าสัมประสิทธิ์ฤดูกาล ซึ่งไม่รวมการเติบโต สำหรับเดือนธันวาคม พฤศจิกายน ตุลาคม
สำหรับเดือนกุมภาพันธ์ - ค่าสัมประสิทธิ์เดือนกุมภาพันธ์หารด้วยค่าเฉลี่ยของค่าสัมประสิทธิ์เดือนพฤศจิกายน ธันวาคม และมกราคม
สำหรับเดือนมีนาคม - อัตราส่วนของเดือนมีนาคมต่อค่าเฉลี่ยของค่าสัมประสิทธิ์ฤดูกาลที่เคลียร์จากการเติบโตของเดือนธันวาคม มกราคม และกุมภาพันธ์
ภายใน 1 เดือน:
ภายใน 2 เดือน:
ภายใน 3 เดือน:
เราได้คำนวณค่าสัมประสิทธิ์ฤดูกาลสำหรับช่วงเวลาก่อนหน้านี้แล้ว ตอนนี้เราจะพิจารณา ช่วงเวลาไหนจะดีกว่าถ้าเอาค่าเฉลี่ยมามากกว่านี้ การคาดการณ์ที่แม่นยำ คุณยังสามารถคำนวณค่าสัมประสิทธิ์ฤดูกาลได้อย่างง่ายดายและรวดเร็วโดยใช้โปรแกรม Forecast4AC ซึ่งเป็นผู้ช่วยที่เชื่อถือได้ในทุกขั้นตอนของการคาดการณ์
2. เลือกช่วงเวลาในการคำนวณค่าเฉลี่ยสำหรับการพยากรณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่
ในการดำเนินการนี้ เราทำการคาดการณ์สำหรับช่วงสุดท้ายและช่วงสุดท้ายซึ่งเป็นข้อมูลที่เราทราบในสามวิธีขึ้นไป เพื่อกำหนดระยะเวลาที่เหมาะสมในการคำนวณค่าเฉลี่ย(ดูไฟล์แนบ “การเลือกระยะเวลา”) และมาดูกันว่าตัวเลือกใดที่ทำให้การคาดการณ์แม่นยำยิ่งขึ้น:
- เราจะคำนวณยอดขายโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ของเดือนที่ 1:
ธันวาคม = ปริมาณการขายในเดือนพฤศจิกายนคูณด้วยค่าสัมประสิทธิ์ฤดูกาลของเดือนธันวาคมกับเดือนก่อนหน้า
- ลองคำนวณการคาดการณ์ยอดขายโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่เป็นเวลา 2 เดือน:
ธันวาคม = ปริมาณการขายเฉลี่ยสำหรับเดือนตุลาคมและพฤศจิกายน คูณด้วยค่าสัมประสิทธิ์ฤดูกาลของเดือนธันวาคมเป็นเวลา 2 เดือน
- เราคำนวณการคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่เป็นเวลา 3 เดือน:
ธันวาคม = ปริมาณการขายเฉลี่ยในเดือนกันยายน ตุลาคม และพฤศจิกายน คูณด้วยค่าสัมประสิทธิ์ฤดูกาลของเดือนธันวาคมเป็นเวลา 3 เดือน
ขณะนี้เราได้คำนวณการคาดการณ์สำหรับเดือนธันวาคมได้สามวิธี เราจะคำนวณในลักษณะเดียวกันสำหรับเดือนพฤศจิกายน
ตอนนี้ เปรียบเทียบมูลค่าที่แท้จริงสำหรับเดือนพฤศจิกายนและธันวาคม โดยพยากรณ์ได้ 3 วิธี. เราเห็นสิ่งนั้นในตัวอย่างของเรา การคาดการณ์ที่แม่นยำที่สุดคำนวณโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่เป็นเวลา 2 เดือน,เอามาเป็นฐานเลย. ในกรณีของคุณ การคาดการณ์ที่แม่นยำยิ่งขึ้นอาจเป็นสำหรับช่วงก่อนหน้า, 3 ช่วงก่อนหน้า หรือ 4 ช่วงก่อนหน้า
3. ลองคำนวณการคาดการณ์ยอดขายโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่
เพราะ เราเลือกการคาดการณ์โดยอิงจากค่าเฉลี่ยในช่วง 2 เดือนที่ผ่านมา จากนั้นสำหรับการคาดการณ์ในเดือนมกราคม ยอดขายเฉลี่ยในเดือนพฤศจิกายนและธันวาคม ให้คูณค่าสัมประสิทธิ์ฤดูกาลของเดือนมกราคมด้วย 2 เดือน.
สำหรับคำพยากรณ์ประจำเดือนกุมภาพันธ์เราคูณปริมาณการขายเฉลี่ยของเดือนมกราคมและธันวาคมด้วยปัจจัยฤดูกาลของเดือนกุมภาพันธ์
ตามตรรกะนี้ เรากำลังขยายการคาดการณ์ไปจนถึงสิ้นปี คาดการณ์ยอดขายประจำปีพร้อมแล้ว
4. ปัจจัยเพิ่มเติมที่ต้องพิจารณาเมื่อคำนวณการคาดการณ์การขายของคุณ
สิ่งสำคัญคือต้องเพิ่มความแม่นยำของการพยากรณ์:
- ลบปัจจัยจากงวดที่ผ่านมา, ที่ ส่งผลต่อปริมาณการขายอย่างมาก, แต่จะไม่เกิดซ้ำในเดือนที่คาดการณ์ไว้(โปรโมชั่นการขาย, จัดส่งครั้งเดียวให้กับลูกค้ารายใหญ่ที่ไม่ประจำ, ถอนตัวจากรายใหญ่ เครือข่ายการค้าปลีกฯลฯ)
- เพิ่มปัจจัยให้กับเดือนที่คาดการณ์ซึ่งจะส่งผลต่อการขายอย่างมีนัยสำคัญ - การเริ่มทำงานกับเครือข่ายขนาดใหญ่, การทำแคมเปญส่งเสริมการขายขนาดใหญ่, การเปิดตัวผลิตภัณฑ์ใหม่, แคมเปญโฆษณา ฯลฯ
การคาดการณ์ที่แม่นยำสำหรับคุณ!
โปรแกรม Forecast4AC PRO จะคำนวณการคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ พร้อมกันมากกว่า 1,000 อนุกรมเวลาด้วยการกดแป้นพิมพ์เพียงครั้งเดียวอย่างเห็นได้ชัด ประหยัดเวลาของคุณ ด้วยวิธีใดวิธีหนึ่งจาก 4 วิธี:
สู่ค่าเฉลี่ยของสองช่วงก่อนหน้านี้
สู่ค่าเฉลี่ยของสามช่วงก่อนหน้า
สู่ค่าเฉลี่ยในช่วง 4 งวดก่อนหน้า
ค่าเฉลี่ยสองเท่าของช่วงที่ 3 และ 4 ก่อนหน้า
เข้าร่วมกับเรา!
ดาวน์โหลดแอปพยากรณ์และวิเคราะห์ธุรกิจฟรี:
- Novo พยากรณ์ Lite- อัตโนมัติ การคำนวณการคาดการณ์วี เอ็กเซล.
- 4การวิเคราะห์ - การวิเคราะห์ ABC-XYZและการวิเคราะห์การปล่อยมลพิษ เอ็กเซล
- คลิ้ก เซนส์เดสก์ทอป และ QlikViewPersonal Edition - ระบบ BI สำหรับการวิเคราะห์ข้อมูลและการแสดงภาพ
ทดสอบความสามารถของโซลูชันแบบชำระเงิน:
- โนโวพยากรณ์ PRO- การคาดการณ์ใน Excel สำหรับชุดข้อมูลขนาดใหญ่
วิธีค่าเฉลี่ยเคลื่อนที่เป็นเครื่องมือทางสถิติที่สามารถใช้เพื่อแก้ปัญหาประเภทต่างๆ โดยเฉพาะมักใช้ในการพยากรณ์ค่อนข้างบ่อย ใน โปรแกรมเอ็กเซลเครื่องมือนี้ยังสามารถใช้เพื่อแก้ไขปัญหาต่างๆ ได้อีกด้วย มาดูวิธีการใช้ค่าเฉลี่ยเคลื่อนที่ใน Excel
ความหมาย วิธีนี้คือด้วยความช่วยเหลือ ค่าไดนามิกสัมบูรณ์ของชุดข้อมูลที่เลือกจะถูกเปลี่ยนเป็นค่าเฉลี่ยเลขคณิต ช่วงระยะเวลาหนึ่งโดยการปรับข้อมูลให้เรียบ เครื่องมือนี้ใช้สำหรับการคำนวณทางเศรษฐกิจ การพยากรณ์ ในกระบวนการซื้อขายในตลาดหลักทรัพย์ ฯลฯ วิธีที่ดีที่สุดคือใช้วิธีค่าเฉลี่ยเคลื่อนที่ใน Excel โดยใช้เครื่องมือประมวลผลข้อมูลทางสถิติที่มีประสิทธิภาพที่เรียกว่า แพ็คเกจการวิเคราะห์. นอกจากนี้ คุณสามารถใช้ฟังก์ชัน Excel ในตัวเพื่อจุดประสงค์เดียวกันได้ เฉลี่ย.
วิธีที่ 1: แพ็คเกจการวิเคราะห์
แพ็คเกจการวิเคราะห์คือ Add-in ของ Excel ที่ถูกปิดใช้งานตามค่าเริ่มต้น ดังนั้นก่อนอื่นคุณต้องเปิดใช้งานก่อน
หลังจากการดำเนินการนี้แพ็คเกจ "การวิเคราะห์ข้อมูล"เปิดใช้งานแล้วและปุ่มที่เกี่ยวข้องปรากฏบน Ribbon ในแท็บ "ข้อมูล".
ตอนนี้เรามาดูกันว่าคุณสามารถใช้ความสามารถของแพ็คเกจได้โดยตรงอย่างไร การวิเคราะห์ข้อมูลสำหรับการทำงานโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ มาพยากรณ์สำหรับเดือนที่ 12 โดยอาศัยข้อมูลรายได้บริษัท 11 งวดก่อนหน้ากันดีกว่า ในการดำเนินการนี้ เราจะใช้ตารางที่เต็มไปด้วยข้อมูลตลอดจนเครื่องมือต่างๆ แพ็คเกจการวิเคราะห์.
- ไปที่แท็บ "ข้อมูล"และกดปุ่ม "การวิเคราะห์ข้อมูล"ซึ่งอยู่บนแถบเครื่องมือในบล็อก "การวิเคราะห์".
- รายการเครื่องมือที่มีอยู่ใน แพ็คเกจการวิเคราะห์. เลือกชื่อจากพวกเขา “ค่าเฉลี่ยเคลื่อนที่”และกดปุ่ม "ตกลง".
- หน้าต่างป้อนข้อมูลสำหรับการคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่จะเปิดขึ้น
ในสนาม "ช่วงเวลาอินพุต"เราระบุที่อยู่ของช่วงที่มีจำนวนรายได้ต่อเดือนโดยไม่มีเซลล์ที่ควรคำนวณข้อมูล
ในสนาม "ช่วงเวลา"คุณควรระบุช่วงเวลาในการประมวลผลค่าโดยใช้วิธีปรับให้เรียบ ขั้นแรก ให้ตั้งค่าการปรับให้เรียบเป็นสามเดือน จากนั้นจึงป้อนตัวเลข "3".
ในสนาม “ช่วงเอาท์พุต”คุณต้องระบุช่วงว่างโดยพลการบนแผ่นงานซึ่งข้อมูลที่จะแสดงหลังการประมวลผล ซึ่งควรเป็นเซลล์ที่มีขนาดใหญ่กว่าช่วงอินพุตหนึ่งเซลล์
คุณควรทำเครื่องหมายในช่องถัดจากพารามิเตอร์ด้วย “ข้อผิดพลาดมาตรฐาน”.
หากจำเป็น คุณสามารถเลือกช่องถัดจากรายการได้ “กราฟเอาท์พุต”สำหรับการสาธิตด้วยภาพ แม้ว่าในกรณีของเราจะไม่จำเป็นก็ตาม
หลังจากทำการตั้งค่าทั้งหมดแล้วให้คลิกที่ปุ่ม "ตกลง".
- โปรแกรมแสดงผลการประมวลผล
- ตอนนี้เราจะดำเนินการปรับให้เรียบภายในระยะเวลาสองเดือนเพื่อพิจารณาว่าผลลัพธ์ใดถูกต้องมากกว่า เพื่อจุดประสงค์เหล่านี้ เราจะเปิดตัวเครื่องมืออีกครั้ง “ค่าเฉลี่ยเคลื่อนที่” แพ็คเกจการวิเคราะห์.
ในสนาม "ช่วงเวลาอินพุต"เราปล่อยให้ค่าเดียวกันกับในกรณีก่อนหน้า
ในสนาม "ช่วงเวลา"ใส่ตัวเลข "2".
ในสนาม “ช่วงเอาท์พุต”เราระบุที่อยู่ของช่วงว่างใหม่ ซึ่งจะต้องเป็นหนึ่งเซลล์ที่มีขนาดใหญ่กว่าช่วงอินพุตอีกครั้ง
เราปล่อยให้การตั้งค่าที่เหลือเหมือนเดิม หลังจากนั้นคลิกที่ปุ่ม "ตกลง".
- ต่อไปนี้โปรแกรมจะทำการคำนวณและแสดงผลบนหน้าจอ เพื่อพิจารณาว่ารุ่นใดในทั้งสองรุ่นมีความแม่นยำมากกว่า เราจำเป็นต้องเปรียบเทียบข้อผิดพลาดมาตรฐาน น้อย ตัวบ่งชี้นี้ยิ่งความน่าจะเป็นของความแม่นยำของผลลัพธ์ที่ได้ก็จะยิ่งสูงขึ้นเท่านั้น อย่างที่คุณเห็น สำหรับค่าทั้งหมด ข้อผิดพลาดมาตรฐานเมื่อคำนวณค่าเฉลี่ยเคลื่อนที่สองเดือนจะน้อยกว่าตัวบ่งชี้เดียวกันเป็นเวลา 3 เดือน ดังนั้นค่าที่ทำนายไว้สำหรับเดือนธันวาคมจึงถือเป็นค่าที่คำนวณโดยวิธีเลื่อนสำหรับ ช่วงสุดท้าย. ในกรณีของเราค่านี้คือ 990.4 พันรูเบิล
วิธีที่ 2: การใช้ฟังก์ชัน AVERAGE
มีอีกวิธีหนึ่งในการใช้วิธีค่าเฉลี่ยเคลื่อนที่ใน Excel หากต้องการใช้งานคุณต้องใช้ฟังก์ชันโปรแกรมมาตรฐานจำนวนหนึ่งซึ่งพื้นฐานสำหรับจุดประสงค์ของเราคือ เฉลี่ย. ตัวอย่างเช่น เราจะใช้ตารางรายได้องค์กรเดียวกันกับในกรณีแรก
เช่นเดียวกับครั้งก่อน เราจะต้องสร้างอนุกรมเวลาที่ราบรื่น แต่คราวนี้การกระทำจะไม่เป็นไปโดยอัตโนมัติ คุณควรคำนวณค่าเฉลี่ยทุก ๆ สองและสามเดือนเพื่อให้สามารถเปรียบเทียบผลลัพธ์ได้
ก่อนอื่น เรามาคำนวณค่าเฉลี่ยของสองช่วงก่อนหน้าโดยใช้ฟังก์ชันกันก่อน เฉลี่ย. เราสามารถทำได้ตั้งแต่เดือนมีนาคมเท่านั้น เนื่องจากสำหรับวันต่อๆ ไป จะมีการแตกหักของค่า
- เลือกเซลล์ในคอลัมน์ว่างในแถวของเดือนมีนาคม จากนั้นคลิกที่ไอคอน "แทรกฟังก์ชัน"ซึ่งอยู่ใกล้กับแถบสูตร
- หน้าต่างถูกเปิดใช้งาน ตัวช่วยสร้างฟังก์ชั่น. ในหมวดหมู่ "สถิติ"กำลังมองหาความหมาย "เฉลี่ย"เลือกและคลิกที่ปุ่ม "ตกลง".
- หน้าต่างอาร์กิวเมนต์ของตัวดำเนินการจะเปิดขึ้น เฉลี่ย. ไวยากรณ์ของมันมีดังนี้:
ค่าเฉลี่ย(หมายเลข 1, หมายเลข 2,...)
จำเป็นต้องมีอาร์กิวเมนต์เดียวเท่านั้น
ในกรณีของเราในสนาม "หมายเลข 1"เราต้องระบุลิงก์ไปยังช่วงที่ระบุรายได้สำหรับสองช่วงก่อนหน้า (มกราคมและกุมภาพันธ์) วางเคอร์เซอร์ในช่องแล้วเลือกเซลล์ที่เกี่ยวข้องบนแผ่นงานในคอลัมน์ "รายได้". หลังจากนั้นคลิกที่ปุ่ม "ตกลง".
- อย่างที่คุณเห็น ผลลัพธ์ของการคำนวณค่าเฉลี่ยสำหรับสองช่วงเวลาก่อนหน้านั้นแสดงอยู่ในเซลล์ เพื่อที่จะทำการคำนวณที่คล้ายกันสำหรับเดือนอื่นๆ ทั้งหมดของรอบระยะเวลานั้น เราจำเป็นต้องคัดลอก สูตรนี้ไปยังเซลล์อื่นๆ เมื่อต้องการทำเช่นนี้ ให้วางเคอร์เซอร์ไว้ที่มุมขวาล่างของเซลล์ที่มีฟังก์ชันนี้ เคอร์เซอร์จะเปลี่ยนเป็นจุดจับเติมที่ดูเหมือนกากบาท กดปุ่มซ้ายของเมาส์ค้างไว้แล้วลากลงไปจนสุดคอลัมน์
- เราได้รับการคำนวณผลลัพธ์ของค่าเฉลี่ยสำหรับสองเดือนก่อนหน้าก่อนสิ้นปี
- ตอนนี้เลือกเซลล์ในคอลัมน์ว่างถัดไปในแถวเดือนเมษายน การเรียกหน้าต่างอาร์กิวเมนต์ของฟังก์ชัน เฉลี่ยในลักษณะเดียวกับที่อธิบายไว้ข้างต้น ในสนาม "หมายเลข 1"ป้อนพิกัดของเซลล์ในคอลัมน์ "รายได้"ตั้งแต่เดือนมกราคมถึงเดือนมีนาคม จากนั้นคลิกที่ปุ่ม "ตกลง".
- ใช้เครื่องหมายเติม คัดลอกสูตรลงในเซลล์ตารางด้านล่าง
- ดังนั้นเราจึงคำนวณค่าต่างๆ ตอนนี้เช่นเดียวกับใน ครั้งก่อนเราจะต้องพิจารณาว่าการวิเคราะห์ประเภทใดดีกว่า: โดยการปรับให้เรียบเป็นเวลา 2 หรือ 3 เดือน ในการทำเช่นนี้ คุณควรคำนวณค่าเบี่ยงเบนมาตรฐานและตัวชี้วัดอื่นๆ ขั้นแรก เรามาคำนวณค่าเบี่ยงเบนสัมบูรณ์โดยใช้มาตรฐานกันก่อน ฟังก์ชันเอ็กเซล เอบีเอสซึ่งแทนที่จะเป็นค่าบวกหรือ ตัวเลขติดลบส่งคืนโมดูลของพวกเขา ค่านี้จะเท่ากับความแตกต่างระหว่างตัวบ่งชี้รายได้จริงสำหรับเดือนที่เลือกและที่คาดการณ์ไว้ วางเคอร์เซอร์ในคอลัมน์ว่างถัดไปในแถวเดือนพฤษภาคม กำลังโทร ตัวช่วยสร้างฟังก์ชัน.
- ในหมวดหมู่ "คณิตศาสตร์"เน้นชื่อของฟังก์ชัน "เอบีเอส". คลิกที่ปุ่ม "ตกลง".
- หน้าต่างอาร์กิวเมนต์ของฟังก์ชันจะเปิดขึ้น เอบีเอส. ในสนามเดียว "ตัวเลข"ระบุความแตกต่างระหว่างเนื้อหาของเซลล์ในคอลัมน์ "รายได้"และ "2 เดือน"สำหรับเดือนพฤษภาคม จากนั้นคลิกที่ปุ่ม "ตกลง".
- ใช้เครื่องหมายเติม คัดลอกสูตรนี้ไปยังทุกแถวของตารางจนถึงเดือนพฤศจิกายน
- เราคำนวณค่าเฉลี่ยของค่าเบี่ยงเบนสัมบูรณ์ตลอดระยะเวลาโดยใช้ฟังก์ชันที่เราคุ้นเคยอยู่แล้ว เฉลี่ย.
- เราดำเนินการขั้นตอนที่คล้ายกันเพื่อคำนวณค่าเบี่ยงเบนสัมบูรณ์สำหรับค่าเฉลี่ยเคลื่อนที่ 3 เดือน ขั้นแรกเราใช้ฟังก์ชัน เอบีเอส. เฉพาะครั้งนี้เท่านั้นที่เราคำนวณความแตกต่างระหว่างเนื้อหาของเซลล์ที่มีรายได้จริงและรายได้ตามแผน โดยคำนวณโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่เป็นเวลา 3 เดือน
- ต่อไป เราจะคำนวณค่าเฉลี่ยของข้อมูลส่วนเบี่ยงเบนสัมบูรณ์ทั้งหมดโดยใช้ฟังก์ชัน เฉลี่ย.
- ขั้นตอนต่อไปคือการคำนวณค่าเบี่ยงเบนสัมพัทธ์ มันเท่ากับอัตราส่วนของค่าเบี่ยงเบนสัมบูรณ์ต่อตัวบ่งชี้ที่แท้จริง เพื่อหลีกเลี่ยง ค่าลบเราจะใช้ประโยชน์จากโอกาสที่ผู้ให้บริการเสนออีกครั้ง เอบีเอส. คราวนี้ เมื่อใช้ฟังก์ชันนี้ เราจะหารค่าเบี่ยงเบนสัมบูรณ์เมื่อใช้วิธีค่าเฉลี่ยเคลื่อนที่ 2 เดือนด้วยรายได้จริงสำหรับเดือนที่เลือก
- แต่ค่าเบี่ยงเบนสัมพัทธ์มักจะแสดงเป็นเปอร์เซ็นต์ ดังนั้นให้เลือกช่วงที่เกี่ยวข้องบนแผ่นงานแล้วไปที่แท็บ "บ้าน"ซึ่งอยู่ในบล็อกเครื่องมือ "ตัวเลข"ในฟิลด์การจัดรูปแบบพิเศษเราตั้งค่ารูปแบบเปอร์เซ็นต์ หลังจากนี้ ผลลัพธ์ของการคำนวณค่าเบี่ยงเบนสัมพัทธ์จะแสดงเป็นเปอร์เซ็นต์
- เราทำการดำเนินการที่คล้ายกันเพื่อคำนวณค่าเบี่ยงเบนสัมพัทธ์กับข้อมูลโดยใช้การปรับให้เรียบเป็นเวลา 3 เดือน เฉพาะในกรณีนี้ ในการคำนวณเป็นเงินปันผล เราใช้อีกคอลัมน์หนึ่งของตารางที่เราตั้งชื่อไว้ “หน้าท้อง ปิด (3ม.)". แล้วเราก็แปล ค่าตัวเลขในรูปแบบเปอร์เซ็นต์
- หลังจากนั้นเราจะคำนวณค่าเฉลี่ยของทั้งสองคอลัมน์ที่มีค่าเบี่ยงเบนสัมพัทธ์เหมือนก่อนใช้ฟังก์ชัน เฉลี่ย. เนื่องจากในการคำนวณเราใช้ค่าเปอร์เซ็นต์เป็นอาร์กิวเมนต์ของฟังก์ชันจึงไม่จำเป็นต้องทำการแปลงเพิ่มเติม ตัวดำเนินการเอาต์พุตจะสร้างผลลัพธ์ในรูปแบบเปอร์เซ็นต์
- ทีนี้ เรามาคำนวณค่าเบี่ยงเบนมาตรฐานกัน ตัวบ่งชี้นี้จะช่วยให้เราเปรียบเทียบคุณภาพของการคำนวณได้โดยตรงเมื่อใช้การปรับให้เรียบเป็นเวลาสองและสามเดือน ในกรณีของเรา ค่าเบี่ยงเบนมาตรฐานจะเท่ากับรากที่สองของผลรวมของกำลังสองของความแตกต่างระหว่างรายได้จริงกับค่าเฉลี่ยเคลื่อนที่ หารด้วยจำนวนเดือน ในการคำนวณในโปรแกรมเราต้องใช้ฟังก์ชันจำนวนหนึ่งโดยเฉพาะ ราก, ผลรวมผลต่างและ ตรวจสอบ. ตัวอย่างเช่น ในการคำนวณค่าเบี่ยงเบนมาตรฐานเมื่อใช้เส้นปรับให้เรียบเป็นเวลาสองเดือนในเดือนพฤษภาคม ในกรณีของเรา จะใช้สูตรต่อไปนี้:
SQRT(สรุป(B6:B12,C6:C12)/COUNT(B6:B12))
เราคัดลอกไปยังเซลล์อื่นของคอลัมน์และคำนวณค่าเบี่ยงเบนมาตรฐานโดยใช้เครื่องหมายเติม
- เราทำการดำเนินการที่คล้ายกันเพื่อคำนวณค่าเบี่ยงเบนมาตรฐานสำหรับค่าเฉลี่ยเคลื่อนที่ราย 3 เดือน
- หลังจากนั้น เราจะคำนวณค่าเฉลี่ยตลอดระยะเวลาของตัวบ่งชี้ทั้งสองนี้โดยใช้ฟังก์ชัน เฉลี่ย.
- เมื่อเปรียบเทียบการคำนวณโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่กับการปรับให้เรียบที่ 2 และ 3 เดือนในแง่ของตัวบ่งชี้ เช่น ค่าเบี่ยงเบนสัมบูรณ์ ส่วนเบี่ยงเบนสัมพัทธ์ และค่าเบี่ยงเบนมาตรฐาน เราสามารถพูดได้อย่างมั่นใจว่าการปรับให้เรียบเป็นเวลาสองเดือนให้ผลลัพธ์ที่เชื่อถือได้มากกว่าการใช้การปรับให้เรียบเป็นเวลาสามเดือน . สิ่งนี้เห็นได้จากข้อเท็จจริงที่ว่าตัวบ่งชี้ข้างต้นสำหรับค่าเฉลี่ยเคลื่อนที่สองเดือนนั้นน้อยกว่าค่าเฉลี่ยเคลื่อนที่สามเดือน
- ดังนั้นรายได้ที่คาดการณ์ไว้ขององค์กรในเดือนธันวาคมจะอยู่ที่ 990.4 พันรูเบิล อย่างที่คุณเห็น ค่านี้เกิดขึ้นพร้อมกันกับค่าที่เราได้รับเมื่อคำนวณโดยใช้เครื่องมือ แพ็คเกจการวิเคราะห์.
เราคำนวณการคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ได้สองวิธี อย่างที่คุณเห็นขั้นตอนนี้ใช้เครื่องมือได้ง่ายกว่ามาก แพ็คเกจการวิเคราะห์. อย่างไรก็ตาม ผู้ใช้บางรายอาจไม่เชื่อถือการคำนวณอัตโนมัติเสมอไป และต้องการใช้ฟังก์ชันนี้ในการคำนวณ เฉลี่ยและผู้ประกอบการร่วมเพื่อตรวจสอบตัวเลือกที่น่าเชื่อถือที่สุด แม้ว่าหากทุกอย่างถูกต้องแล้วผลลัพธ์สุดท้ายของการคำนวณก็ควรจะเหมือนกันทั้งหมด
ในธุรกิจเช่นเดียวกับกิจกรรมอื่น ๆ บุคคลต้องการทราบว่าจะเกิดอะไรขึ้นต่อไป เป็นเรื่องยากที่จะจินตนาการถึงความมั่งคั่งของผู้โชคดีที่สามารถคาดเดาอนาคตได้อย่างแม่นยำ 100% แต่น่าเสียดาย (หรือโชคดี) ของประทานแห่งการมองการณ์ไกลนั้นหาได้ยากมาก แต่... อย่างน้อยก็พยายาม โครงร่างทั่วไปแนะนำ ธุรกิจในอนาคตผู้ประกอบการมีหน้าที่ต้องจัดการกับสถานการณ์เท่านั้น
ตอนแรกฉันต้องการเขียนในโพสต์เดียวเกี่ยวกับเทคนิคที่ง่ายและสะดวกหลายประการในคราวเดียว แต่โพสต์เริ่มกลายเป็นเรื่องยาวมาก ดังนั้นจะมีการโพสต์หลายรายการเกี่ยวกับหัวข้อการพยากรณ์โดยเฉพาะ ในโพสต์นี้ เราจะอธิบายวิธีการพยากรณ์ที่ง่ายที่สุดวิธีหนึ่งโดยใช้ความสามารถของ Excel - วิธีค่าเฉลี่ยเคลื่อนที่
ส่วนใหญ่แล้วในทางปฏิบัติการวิจัยการตลาดจะมีการทำนายค่าต่อไปนี้:
- ปริมาณการขาย
- ขนาดตลาดและความจุ
- ปริมาณการผลิต
- ปริมาณการนำเข้า
- การเปลี่ยนแปลงของราคา
- และอื่นๆ
สำหรับการคาดการณ์ที่เรากำลังพิจารณาในโพสต์นี้ ฉันขอแนะนำให้คุณปฏิบัติตามอัลกอริทึมง่ายๆ ต่อไปนี้:
1. การรวบรวมข้อมูลทุติยภูมิเกี่ยวกับปัญหา(โดยเฉพาะอย่างยิ่งทั้งเชิงปริมาณและเชิงคุณภาพ) ตัวอย่างเช่น หากคุณคาดการณ์ขนาดของตลาด คุณจะต้องรวบรวมข้อมูลทางสถิติในตลาด (ปริมาณการผลิต การนำเข้า การเปลี่ยนแปลงของราคา ปริมาณการขาย ฯลฯ) รวมถึงแนวโน้ม ปัญหา หรือโอกาสทางการตลาด หากคุณกำลังคาดการณ์ยอดขาย คุณต้องมีข้อมูลการขายสำหรับช่วงเวลานั้น สำหรับการคาดการณ์ ยิ่งคุณพิจารณาข้อมูลในอดีตมากเท่าไรก็ยิ่งดีเท่านั้น ขอแนะนำให้เสริมการพยากรณ์ด้วยการวิเคราะห์ปัจจัยที่มีอิทธิพลต่อปรากฏการณ์ที่คาดการณ์ไว้ (คุณสามารถใช้ SWOT การวิเคราะห์ PEST หรืออื่นๆ ก็ได้) สิ่งนี้จะช่วยให้คุณเข้าใจตรรกะของการพัฒนา และคุณจะสามารถตรวจสอบความน่าเชื่อถือของโมเดลเทรนด์หนึ่งๆ ได้
2. นอกจากนี้ยังเป็นที่พึงปรารถนา ตรวจสอบข้อมูลเชิงปริมาณ. ในการทำเช่นนี้ คุณจะต้องเปรียบเทียบค่าของตัวบ่งชี้เดียวกัน แต่ได้มาจากแหล่งที่ต่างกัน หากทุกอย่างลงตัว คุณสามารถ "ขับเคลื่อน" ข้อมูลลงใน Excel ได้ ข้อมูลต้องเป็นไปตามข้อกำหนดต่อไปนี้ด้วย:
- ข้อมูลพื้นฐานประกอบด้วยผลลัพธ์ของการสังเกต - ตั้งแต่แรกสุดไปจนถึงล่าสุด
- ช่วงเวลาพื้นฐานทั้งหมดมีระยะเวลาเท่ากัน ตัวอย่างเช่น ข้อมูลจากหนึ่งวัน ไม่ควรผสมกับค่าเฉลี่ยสามวัน
- การสังเกตจะถูกบันทึกที่จุดเดียวกันในแต่ละช่วงเวลา ตัวอย่างเช่น ควรวัดปริมาณการเข้าชมพร้อมกัน
- ไม่อนุญาตให้ข้ามข้อมูล การละเว้นผลการสังเกตแม้แต่รายการเดียวนั้นเป็นสิ่งที่ไม่พึงประสงค์เมื่อคาดการณ์” ดังนั้น หากการสังเกตของคุณขาดผลลัพธ์ในช่วงเวลาสั้นๆ ให้ลองกรอกข้อมูลด้วยข้อมูลโดยประมาณเป็นอย่างน้อย
3. หลังจากตรวจสอบข้อมูลแล้ว ก็สามารถ ใช้เทคนิคการพยากรณ์ต่างๆ. ฉันอยากจะเริ่มจากจุดเริ่มต้น วิธีการง่ายๆ – วิธีการย้ายค่าเฉลี่ย
วิธีการย้ายค่าเฉลี่ย
วิธีค่าเฉลี่ยเคลื่อนที่นั้นค่อนข้างใช้งานง่าย แต่ง่ายเกินไปที่จะสร้างการคาดการณ์ที่แม่นยำ เมื่อใช้วิธีนี้ การพยากรณ์ในช่วงเวลาใดๆ จะไม่มีอะไรมากไปกว่าการหาค่าเฉลี่ยของการสังเกตอนุกรมเวลาก่อนหน้าหลายๆ ครั้ง ตัวอย่างเช่น หากคุณเลือกค่าเฉลี่ยเคลื่อนที่สามเดือน การคาดการณ์สำหรับเดือนพฤษภาคมจะเป็นค่าเฉลี่ยของเดือนกุมภาพันธ์ มีนาคม และเมษายน ด้วยการเลือกค่าเฉลี่ยเคลื่อนที่สี่เดือนเป็นวิธีการคาดการณ์ คุณสามารถประเมินตัวเลขเดือนพฤษภาคมเป็นค่าเฉลี่ยของตัวเลขในเดือนมกราคม กุมภาพันธ์ มีนาคม และเมษายน
โดยทั่วไปแล้ว การคาดการณ์ค่าเฉลี่ยเคลื่อนที่จะถูกมองว่าเป็นการคาดการณ์สำหรับช่วงเวลาถัดจากระยะเวลาสังเกต ในเวลาเดียวกัน การคาดการณ์ดังกล่าวจะมีผลบังคับใช้เมื่อปรากฏการณ์ที่กำลังศึกษาพัฒนาตามลำดับ เช่น มีแนวโน้มบางอย่าง และเส้นโค้งค่าไม่ได้กระโดดไปรอบๆ ไดอะแกรมอย่างบ้าคลั่ง
เพื่อกำหนดจำนวนข้อสังเกตที่คุณต้องการรวมไว้ในค่าเฉลี่ยเคลื่อนที่ คุณต้องอาศัยประสบการณ์ก่อนหน้าและข้อมูลที่คุณมีเกี่ยวกับชุดข้อมูล จะต้องมีความสมดุลระหว่างการตอบสนองที่เพิ่มขึ้นของค่าเฉลี่ยเคลื่อนที่ต่อการสังเกตล่าสุด 2-3 ครั้ง และความแปรปรวนขนาดใหญ่ของค่าเฉลี่ยนั้น
แล้วจะทำยังไงล่ะอิน.เอ็กเซล
1. สมมติว่าคุณมีปริมาณการขายต่อเดือนในช่วง 29 เดือนที่ผ่านมา และคุณต้องการกำหนดว่ายอดขายในเดือนที่ 30 จะเป็นเท่าใด แต่บอกตามตรงว่าไม่จำเป็นเลยที่จะต้องดำเนินการด้วยค่าในอดีต 30 ค่าเมื่อคำนวณค่าพยากรณ์ เนื่องจากวิธีนี้จะใช้เพียงไม่กี่ค่าในการคำนวณค่าเฉลี่ย เดือนที่ผ่านมา. ดังนั้นเพียงไม่กี่เดือนที่ผ่านมาก็เพียงพอสำหรับการคำนวณ
2. เรานำตารางนี้มาเป็นรูปแบบ Excel ที่เข้าใจได้เช่น เพื่อให้ค่าทั้งหมดอยู่ในแถวเดียวกัน
3. ต่อไปเราจะแนะนำสูตรการคำนวณค่าเฉลี่ยโดยพิจารณาจากค่าสาม (สี่, ห้า? ตามที่คุณเลือก) ก่อนหน้า (ดูใน) สะดวกที่สุดในการใช้ 3 ค่าสุดท้ายในการคำนวณเพราะว่า หากพิจารณามากขึ้นข้อมูลก็จะเฉลี่ยเกินไป หากพิจารณาน้อยลงก็จะไม่ถูกต้อง
4. การใช้ฟังก์ชันเติมข้อความอัตโนมัติสำหรับค่าที่ตามมาทั้งหมดจนถึง 30 ซึ่งเป็นเดือนที่คาดการณ์ ดังนั้นฟังก์ชันจะคำนวณการคาดการณ์สำหรับเดือนมิถุนายน 2553 ตามค่าพยากรณ์ ยอดขายในเดือนมิถุนายนจะอยู่ที่ประมาณ 408 หน่วยสินค้า แต่โปรดทราบว่าหากแนวโน้มขาลงคงที่ดังตัวอย่างของเรา การคำนวณการคาดการณ์ตามค่าเฉลี่ยจะถูกประเมินสูงเกินไปเล็กน้อย หรือดูเหมือนจะ "ล้าหลัง" ช้ากว่ามูลค่าจริง
เราดูที่หนึ่งมากที่สุด เทคนิคง่ายๆการพยากรณ์ – วิธีค่าเฉลี่ยเคลื่อนที่ ในโพสต์ต่อไปนี้ เราจะดูเทคนิคอื่นๆ ที่แม่นยำและซับซ้อนยิ่งขึ้น ฉันหวังว่าโพสต์ของฉันจะเป็นประโยชน์กับคุณ
การสร้างแบบจำลองสถานการณ์ทางเศรษฐกิจเชิงปฏิบัติเกี่ยวข้องกับการพัฒนาการคาดการณ์ การใช้เครื่องมือ Excel คุณสามารถใช้งานสิ่งต่อไปนี้ วิธีที่มีประสิทธิภาพการคาดการณ์เช่น: การปรับให้เรียบแบบเอกซ์โปเนนเชียล, การสร้างการถดถอย, ค่าเฉลี่ยเคลื่อนที่ มาดูการใช้วิธีค่าเฉลี่ยเคลื่อนที่กันดีกว่า
การใช้ค่าเฉลี่ยเคลื่อนที่ใน Excel
วิธีค่าเฉลี่ยเคลื่อนที่เป็นหนึ่งในวิธี วิธีการเชิงประจักษ์สำหรับการปรับให้เรียบและคาดการณ์อนุกรมเวลา สาระสำคัญ: ค่าสัมบูรณ์ของซีรีย์ไดนามิกเปลี่ยนเป็นค่าเฉลี่ย ค่าเลขคณิตในช่วงเวลาหนึ่ง การเลือกช่วงเวลาจะดำเนินการโดยใช้วิธีการเลื่อน: ระดับแรกจะค่อยๆถูกลบออกและระดับต่อมาจะเปิดขึ้น ผลลัพธ์ที่ได้คือชุดค่าไดนามิกที่ราบรื่นขึ้น ซึ่งช่วยให้สามารถติดตามแนวโน้มการเปลี่ยนแปลงของพารามิเตอร์ที่กำลังศึกษาได้อย่างชัดเจน
อนุกรมเวลาคือชุดของค่า X และ Y ที่เกี่ยวข้องกัน X – ช่วงเวลา ตัวแปรคงที่ Y – ลักษณะของปรากฏการณ์ที่กำลังศึกษา (เช่น ราคาใช้ได้ในช่วงระยะเวลาหนึ่ง) ตัวแปรตาม เมื่อใช้ค่าเฉลี่ยเคลื่อนที่ คุณสามารถระบุลักษณะของการเปลี่ยนแปลงค่า Y เมื่อเวลาผ่านไป และคาดการณ์พารามิเตอร์นี้ในอนาคตได้ วิธีการนี้ใช้งานได้เมื่อค่าแสดงแนวโน้มที่ชัดเจนในการเปลี่ยนแปลง
ตัวอย่างเช่น คุณต้องคาดการณ์ยอดขายในเดือนพฤศจิกายน ผู้วิจัยเลือกจำนวนเดือนก่อนหน้าเพื่อวิเคราะห์ (จำนวนที่เหมาะสมที่สุด m ของเงื่อนไขค่าเฉลี่ยเคลื่อนที่) การคาดการณ์สำหรับเดือนพฤศจิกายนจะเป็นค่าเฉลี่ยของพารามิเตอร์สำหรับเดือนก่อนหน้า
งาน. วิเคราะห์รายได้ของบริษัท 11 เดือน และคาดการณ์เดือนที่ 12
เรามาสร้างอนุกรมเวลาแบบเรียบโดยใช้วิธีค่าเฉลี่ยเคลื่อนที่โดยใช้ฟังก์ชัน AVERAGE กันดีกว่า ลองหาค่าเบี่ยงเบนเฉลี่ยของอนุกรมเวลาที่ปรับให้เรียบจากอนุกรมเวลาที่กำหนด
การเบี่ยงเบนสัมพัทธ์:
ส่วนเบี่ยงเบนมาตรฐาน:
เมื่อคำนวณความเบี่ยงเบน จะมีการสังเกตจำนวนเท่ากัน นี่เป็นสิ่งจำเป็นเพื่อดำเนินการ การวิเคราะห์เปรียบเทียบข้อผิดพลาด
หลังจากเปรียบเทียบตารางที่มีการเบี่ยงเบน เป็นที่ชัดเจนว่าหากต้องการคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ใน Excel เกี่ยวกับแนวโน้มการเปลี่ยนแปลงในรายได้ขององค์กร ควรใช้แบบจำลองค่าเฉลี่ยเคลื่อนที่ 2 เดือน มีข้อผิดพลาดในการคาดการณ์เพียงเล็กน้อย (เทียบกับสามและสี่เดือน)
มูลค่าคาดการณ์รายได้เดือนที่ 12 อยู่ที่ 9,430 USD
การใช้ Add-in ของ Analysis Pack
ลองใช้ปัญหาเดียวกันนี้เป็นตัวอย่าง
บนแท็บ "ข้อมูล" เราพบคำสั่ง "การวิเคราะห์ข้อมูล" ในกล่องโต้ตอบที่เปิดขึ้น ให้เลือก “ค่าเฉลี่ยเคลื่อนที่”:
มากรอกกันได้เลย ช่วงเวลาอินพุต – ค่าเริ่มต้นของอนุกรมเวลา ช่วงเวลา – จำนวนเดือนที่รวมอยู่ในการคำนวณค่าเฉลี่ยเคลื่อนที่ เนื่องจากเราจะสร้างอนุกรมเวลาที่ราบรื่นขึ้นก่อนโดยอิงจากข้อมูลของสองเดือนก่อนหน้า ให้ป้อนหมายเลข 2 ลงในฟิลด์ ช่วงเวลาเอาท์พุตคือช่วงของเซลล์สำหรับแสดงผลลัพธ์ที่ได้รับ
เมื่อทำเครื่องหมายที่ช่อง "ข้อผิดพลาดมาตรฐาน" เราจะเพิ่มคอลัมน์ลงในตารางโดยอัตโนมัติ การประเมินทางสถิติข้อผิดพลาด
ในทำนองเดียวกัน เราจะพบค่าเฉลี่ยเคลื่อนที่ในช่วงสามเดือน เฉพาะช่วงเวลา (3) และช่วงเอาท์พุตเท่านั้นที่เปลี่ยนแปลง
เมื่อเปรียบเทียบข้อผิดพลาดมาตรฐานแล้ว เรามั่นใจว่าแบบจำลองค่าเฉลี่ยเคลื่อนที่สองเดือนมีความเหมาะสมมากกว่าสำหรับการปรับให้เรียบและการคาดการณ์ มีข้อผิดพลาดมาตรฐานน้อยกว่า มูลค่าคาดการณ์รายได้เดือนที่ 12 อยู่ที่ 9,430 USD
การคาดการณ์โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่นั้นง่ายและมีประสิทธิภาพ เครื่องมือนี้สะท้อนการเปลี่ยนแปลงในพารามิเตอร์หลักของช่วงเวลาก่อนหน้าอย่างแม่นยำ แต่เป็นไปไม่ได้ที่จะไปไกลกว่าข้อมูลที่ทราบ ดังนั้นจึงใช้วิธีการอื่นในการพยากรณ์ระยะยาว
ฉันทำซ้ำ พฤติกรรมของฝูงชนเป็นแบบเฉื่อย ซึ่งหมายความว่าโอกาสที่ฝูงชนในวันพรุ่งนี้จะมีพฤติกรรมเหมือนกับเมื่อวานและวันก่อนเมื่อวานนั้นสูงกว่าแนวโน้มที่จะเปลี่ยนอารมณ์มาก
เพื่อติดตามพฤติกรรมของฝูงชนในตลาด มีตัวบ่งชี้ MACD โบราณ ย่อมาจาก ค่าเฉลี่ยเคลื่อนที่ convergence-divergence หรือในภาษารัสเซีย convergence-divergence ของค่าเฉลี่ยเคลื่อนที่ (ความหมาย ความหมายทางประวัติศาสตร์ราคาหุ้นหรือตราสารอื่น ๆ)
ความหมายเชิงกราฟิกของฮิสโตแกรม MACD คือการยืนยันความต่อเนื่องของแนวโน้ม (ทิศทางการพัฒนา) ของการเคลื่อนไหวของราคา พูดง่ายๆ ก็คือ ราคาหุ้นยังคงลดลงหรือราคาสูงขึ้น ทิศทางการเคลื่อนไหวของราคาถูกกำหนดจากความแตกต่างระหว่างแท่งสองแท่งที่อยู่ติดกัน
เราใช้ Excel เพื่อพล็อตฮิสโตแกรม MACD
1) ก่อนอื่น เราต้องการข้อมูลในอดีตเพื่อการวิเคราะห์ ในบทความก่อนหน้านี้ ฉันได้ยกตัวอย่างว่าสามารถรับข้อมูลดังกล่าวได้จากที่ไหน ทำตามตัวอย่างนี้และไปที่หน้าการส่งออกข้อมูลนายหน้าซื้อขายหลักทรัพย์:
เมื่อกำหนดข้อกำหนดสำหรับรูปแบบของข้อมูลที่ดาวน์โหลดแล้ว เราได้รับไฟล์ที่มีข้อมูลในรูปแบบ CSV ซึ่ง Excel เข้าใจ นอกจากนี้ ข้อมูลประวัติของตราสารที่เราสนใจสามารถดาวน์โหลดได้จากเว็บไซต์ของโบรกเกอร์ CJSC FINAM ลิงค์นี้
2) ข้อมูลควรได้รับการจัดรูปแบบตามที่อธิบายไว้ใน
ในที่สุดคุณควรจะได้ชุดดังนี้:
3) ตอนนี้เรามาสร้างกัน ใบใหม่ในหนังสือ Excel สำหรับการคำนวณและการสร้างแผนภูมิการวิเคราะห์ทางเทคนิค เรามาเรียกเอกสารนี้ว่า "การคำนวณ MACD" จากนั้นคัดลอกคอลัมน์วันที่ไปยังแผ่นงานนี้
4) ทีนี้มาคำนวณค่าเฉลี่ยเคลื่อนที่แบบเอ็กซ์โปเนนเชียลด้วยกรอบเวลา 12 วัน (EMA 12) EMA 12 คำนวณโดยใช้สูตร:
ลองใส่สูตรนี้ลงในคอลัมน์ทางด้านขวาของราคาปิด
ลองคัดลอกสูตรผลลัพธ์ลงในเซลล์ด้านล่างแล้วแก้ไขเล็กน้อย: แทนที่จะเป็นค่าจากเซลล์ B3 ในส่วนที่สองของสูตร ให้แทนที่ค่าจากเซลล์ C2 C2 - นี่จะเป็น EMA12 ของวันก่อนหน้า
มันควรมีลักษณะเช่นนี้:
ทีนี้ลองคูณสูตรที่ได้รับในเซลล์ที่สองสำหรับคอลัมน์ EMA12 ทั้งหมด ในการดำเนินการนี้ ให้คลิกหนึ่งครั้งด้วยเมาส์ในเซลล์ C3 เพื่อให้กรอบตัวหนาสีดำปรากฏขึ้นรอบๆ เซลล์ จากนั้นเลื่อนเคอร์เซอร์ไปที่มุมขวาล่างของกรอบตัวหนาสีดำ เพื่อให้เคอร์เซอร์อยู่ในรูปแบบของกากบาทสีดำตัวหนา และ ดับเบิลคลิกปุ่มซ้ายของเมาส์เพื่อคูณสูตรให้ทั่วทั้งคอลัมน์ แบบนี้:
ในทำนองเดียวกัน ลองคำนวณค่าเฉลี่ยเคลื่อนที่แบบเอ็กซ์โปเนนเชียลด้วยกรอบเวลา 26 วัน (EMA 26) EMA 26 คำนวณโดยใช้สูตร:
วางสูตรนี้ในคอลัมน์ทางด้านขวาของ EMA12 ที่คำนวณได้ ในการดำเนินการนี้ เราเริ่มเขียนลงในเซลล์ที่มีสัญลักษณ์ “=” ซึ่งจะแจ้งให้โปรเซสเซอร์ Excel ทราบว่าจะมีการป้อนสูตร สำหรับเซลล์แรก สูตรจะแตกต่างจากเซลล์ที่เหลือเล็กน้อย เนื่องจากควรแทนที่ราคาปิดของวันนี้ แทนที่จะเป็น EMA26 ของเมื่อวาน แบบนี้:
ลองคัดลอกสูตรผลลัพธ์ลงในเซลล์ด้านล่างแล้วแก้ไขเล็กน้อย: แทนที่ค่าจากเซลล์ B3 ในส่วนที่สองของสูตร ให้แทนที่ค่าจากเซลล์ D2 D2 คือ EMA26 ของวันก่อนหน้า มันควรมีลักษณะเช่นนี้:
ทีนี้ลองคูณสูตรที่ได้รับในเซลล์ที่สองสำหรับคอลัมน์ EMA26 ทั้งหมด ในการดำเนินการนี้ ให้คลิกหนึ่งครั้งด้วยเมาส์ในเซลล์ D3 เพื่อให้กรอบตัวหนาสีดำปรากฏขึ้นรอบๆ เซลล์ จากนั้นเลื่อนเคอร์เซอร์ไปที่มุมขวาล่างของกรอบตัวหนาสีดำ เพื่อให้เคอร์เซอร์อยู่ในรูปแบบของกากบาทสีดำตัวหนา และ ดับเบิลคลิกปุ่มซ้ายของเมาส์เพื่อคูณสูตรสำหรับทั้งคอลัมน์ แบบนี้:
ยินดีด้วย! เราเชี่ยวชาญการคำนวณค่าเฉลี่ยเลขชี้กำลังแล้ว ตอนนี้คุณควรจะได้เส้น MACD ที่ "เร็ว" ในการดำเนินการนี้ คุณจะต้องลบ EMA26 ออกจาก EMA12 มาใส่สูตรนี้ในคอลัมน์ถัดไปทางขวา:
ตอนนี้เราจำเป็นต้องคำนวณค่าเฉลี่ยเคลื่อนที่แบบเอ็กซ์โปเนนเชียลเก้าวันสำหรับเส้น MACD ที่รวดเร็ว เส้นผลลัพธ์จะเรียกว่าเส้น “สัญญาณ” ของ MACD เราจะทำการคำนวณโดยใช้สูตรต่อไปนี้:
ในทำนองเดียวกัน ให้ป้อนสูตรการคำนวณใน Excel ลงในเซลล์ทางด้านขวาของเส้น MACD “เร็ว”:
ในเซลล์ แถวล่างสุดเราปรับสูตรในลักษณะเดียวกับที่เราทำเมื่อคำนวณค่าเฉลี่ยเคลื่อนที่แบบเอ็กซ์โปเนนเชียลยี่สิบหกวันและสิบสองวัน นี่คือสิ่งที่สูตรควรอยู่ในเซลล์ F3:
สุดท้ายนี้ เราสามารถคำนวณคอลัมน์สุดท้ายของข้อมูลเพื่อสร้างฮิสโตแกรม MACD ได้ ค่าของคอลัมน์นี้สำหรับการสร้างฮิสโตแกรมคือความแตกต่างระหว่างเส้น MACD “เร็ว” และ “สัญญาณ” เราป้อนสูตรสุดท้ายสำหรับการคำนวณข้อมูลเพื่อสร้างฮิสโตแกรม:
จะสะดวกกว่ามากในการพิจารณาฮิสโตแกรม MACD ถัดจากกราฟความผันผวนของราคาสำหรับเครื่องมือที่วิเคราะห์ ในบทความก่อนหน้านี้ ฉันได้อธิบายรายละเอียดวิธีสร้างกราฟดังกล่าวอย่างละเอียด ในการสร้างแผนภูมิราคาสำหรับตราสาร เราจะคัดลอกข้อมูลที่จำเป็นบางส่วนลงในแผ่นงานแยกต่างหาก บางสิ่งเช่นนี้:
วิธีที่ง่ายที่สุดในการสร้างแผนภูมิหุ้นอยู่ที่นี่ ในเอกสารนี้ จากนั้น คุณควรคัดลอกไปยังแผ่นงานแยกต่างหาก ซึ่งเป็นแผ่นที่เราจะวางฮิสโตแกรม MACD
เราสร้างชีตแยกต่างหากสำหรับกราฟของเรา วางไดอะแกรมที่คัดลอกมาจากคลิปบอร์ดและปรับแต่งเล็กน้อย รูปร่าง. หน้าต่างกราฟจะขยายและย่อความยาวและความกว้าง คล้ายกับหน้าต่างใน Windows เอง
และด้วยการคลิกซ้ายบนสเกลที่มีค่าราคา คุณสามารถเปลี่ยนรูปแบบข้อมูลของแกนแผนภูมิได้ หลังจากการกระตุ้นดังกล่าว มาตราส่วนของค่าของแกนแนวตั้ง (ในกรณีของเรา) จะถูกเน้นด้วยกรอบสี่เหลี่ยม ทันทีที่เฟรมดังกล่าวปรากฏขึ้นให้คลิกปุ่มเมาส์ขวาเพื่อเปิดเมนูบริบท ในเมนูบริบทด้วยปุ่มซ้ายของเมาส์ให้เลือกบรรทัด<Формат оси…>, แบบนี้:
ในกล่องโต้ตอบที่เปิดขึ้นมาเพื่อตั้งค่าพารามิเตอร์ของแกนกราฟเราจะกำหนดค่า ค่าต่ำสุด(80) และสูงสุด (160) นี่คือสองบรรทัดบนสุดในกล่องโต้ตอบที่เปิดขึ้น รูปด้านล่างแสดงตำแหน่งที่ต้องการของปุ่มตัวเลือกและป้อนค่า 80 และ 160 ในบรรทัดที่เกี่ยวข้อง:
ใต้หน้าต่างกราฟราคา เราจะแทรกหน้าต่างสำหรับฮิสโตแกรม MACD ในอนาคต ในเมนูหลัก ให้เลือกแท็บ<<Вставка>> จากนั้นเมนูย่อย<<Гистограмма>> และในเมนูแบบเลื่อนลง ให้เลือกไอคอนฮิสโตแกรมด้านซ้ายบน ไอคอนนี้จะถูกเน้นด้วยสีเหลืองในภาพหน้าจอด้านล่าง:
สิ่งสำคัญคืออย่าลืมยกเลิกการเลือกอันแรกก่อนที่จะแทรกกราฟที่สอง มิฉะนั้น กราฟหนึ่งอาจถูกแทนที่ด้วยอีกกราฟหนึ่ง และเราต้องการทั้งสองกราฟ
ก่อนจะเรียกเมนู<<Гистограмма>> เป็นความคิดที่ดีที่จะวางเคอร์เซอร์ไว้เหนือเซลล์ A16 แล้วกดปุ่มซ้ายของเมาส์ หลังจากแทรกฮิสโตแกรมแล้ว เราจำเป็นต้องระบุคอลัมน์การคำนวณฮิสโตแกรม MACD ของเรา เมื่อต้องการทำเช่นนี้ ให้เลื่อนเคอร์เซอร์ของเมาส์ไปเหนือฮิสโตแกรมแล้วคลิกปุ่มเมาส์ขวาเพื่อเปิดเมนูบริบทสำหรับจัดการแผนภูมิ ในเมนูบริบทที่เปิดขึ้น ให้เลือกรายการ<Выбрать данные>:
หลังจากกดปุ่มแล้ว<<Добавить>> ในหน้าต่างก่อนหน้า เราควรพิมพ์ชื่อแผนภูมิของเรา - “MACD” และในแถวล่างสุดให้คลิกปุ่มทางด้านขวาของแถว:
หลังจากคลิกที่ปุ่มทางด้านขวาของแถวล่าง หน้าต่าง "เปลี่ยนแถว" แคบๆ จะเปิดขึ้น โดยไม่ต้องปิดหน้าต่างนี้ ให้ใช้เมาส์เพื่อไปยังแผ่นงานที่เรียกว่า MACD:
หลังจากที่คอลัมน์ข้อมูลถูกปกคลุมไปด้วยเส้นประบางๆ ในหน้าต่าง "เปลี่ยนแถว" ให้คลิกปุ่มทางด้านขวา หลังจากนี้ หน้าต่าง "แก้ไขซีรี่ส์" จะเปิดขึ้นโดยมีสองบรรทัด ที่นี่ในหน้าต่างนี้คุณสามารถคลิกปุ่มได้<
กลับไปที่แผ่นงานที่มีชื่อ "CHARTS" ในหน้าต่างเพื่อเลือกข้อมูลสำหรับสร้างฮิสโตแกรมเราก็กดปุ่มด้วย<
คุณสามารถเล่นกับขนาดของหน้าต่างกราฟได้เล็กน้อยและได้ผลลัพธ์ที่ดูชัดเจนยิ่งขึ้น:
และนี่คือแผนภูมิเดียวกันกับที่สร้างโดยระบบการซื้อขาย QUIK ดูเหมือนว่าจะได้ผลสำหรับคุณและฉันใช่ไหม?
เรียนผู้อ่าน! หากคุณตัดสินใจที่จะสร้างกราฟเหล่านี้และมีบางอย่างไม่เหมาะกับคุณ โปรดฝากคำถามไว้ในความคิดเห็น แล้วเราจะร่วมกันค้นหาคำตอบและเรียนรู้วิธีสร้างกราฟใน Excel
คุณสามารถดาวน์โหลดไฟล์ Excel ต้นฉบับที่ใช้จับภาพหน้าจอและสร้างกราฟได้