การพยากรณ์ใน Excel: 6 วิธีที่พิสูจน์แล้ว

การคาดการณ์เป็นองค์ประกอบที่สำคัญมากของกิจกรรมเกือบทุกแห่งตั้งแต่เศรษฐศาสตร์และการสิ้นสุดด้วยวิศวกรรม มีซอฟต์แวร์จำนวนมากที่เชี่ยวชาญในทิศทางนี้ น่าเสียดายที่ผู้ใช้ทุกคนไม่ทราบว่าโปรเซสเซอร์ EXCEL ตารางปกติมีอยู่ในเครื่องมืออาร์เซนอลสำหรับการทำนายซึ่งในประสิทธิภาพของมันมีน้อยกว่าโปรแกรมระดับมืออาชีพ ลองค้นหาเครื่องมือประเภทใดและวิธีการที่จะทำการพยากรณ์โรคในทางปฏิบัติ

การพยากรณ์ขั้นตอน

จุดประสงค์ของการคาดการณ์ใด ๆ คือการระบุแนวโน้มปัจจุบันและนิยามของผลลัพธ์ที่ตั้งใจเกี่ยวกับวัตถุที่กำลังศึกษาในบางจุดในอนาคต

วิธีที่ 1: เส้นแนวโน้ม

หนึ่งในสายพันธุ์การทำนายกราฟิกที่ได้รับความนิยมมากที่สุดใน Excele คือการคาดการณ์ที่ทำโดยการสร้างเส้นแนวโน้ม

ลองทำนายจำนวนเงินของกำไรของ บริษัท หลังจาก 3 ปีบนพื้นฐานของข้อมูลในตัวบ่งชี้นี้สำหรับ 12 ปีก่อนหน้า

1. เรากำลังสร้างตารางตามข้อมูลแบบตารางที่ประกอบด้วยอาร์กิวเมนต์และค่าฟังก์ชั่น เมื่อต้องการทำเช่นนี้ให้เลือกพื้นที่ตารางจากนั้นในขณะที่อยู่ในแท็บ "แทรก" คลิกที่ไอคอนของไดอะแกรมประเภทที่ต้องการซึ่งอยู่ในบล็อก "แผนภูมิ" จากนั้นเลือกประเภทที่เหมาะสมสำหรับสถานการณ์เฉพาะ เป็นการดีที่สุดที่จะเลือกแผนภาพจุด คุณสามารถเลือกรูปลักษณ์อื่นได้ แต่ข้อมูลจะปรากฏขึ้นอย่างถูกต้องคุณจะต้องแก้ไขโดยเฉพาะลบบรรทัดอาร์กิวเมนต์และเลือกสเกลของแกนแนวนอนอื่น



2. ตอนนี้เราต้องสร้างเส้นแนวโน้ม เราคลิกขวาที่จุดใดจุดหนึ่งของแผนภาพ ในเมนูบริบทที่เปิดใช้งานคุณจะหยุดการเลือกที่ย่อหน้า "เพิ่มแนวโน้ม"



3. หน้าต่างการจัดรูปแบบเส้นแนวโน้มจะเปิดขึ้น มันสามารถเลือกหนึ่งในหกประเภทของการประมาณ:
   • เชิงเส้น;
   • ลอการิทึม;
   • เลขชี้กำลัง;
   • พลัง;
   • พหุนาม;
   • การกรองเชิงเส้น

   ให้เราเลือกการประมาณเชิงเส้นก่อน

   ในการตั้งค่า "การคาดการณ์" บล็อกในฟิลด์ "ส่งต่อไปยัง" เราตั้งค่าหมายเลข "3.0" เนื่องจากเราต้องคาดการณ์เป็นเวลาสามปีข้างหน้า นอกจากนี้คุณสามารถติดตั้งเห็บใกล้การตั้งค่า "แสดงสมการบนไดอะแกรม" และ "วางค่าของค่าของการประมาณ (r ^ 2)" บนแผนภาพ ตัวบ่งชี้ล่าสุดแสดงคุณภาพของเส้นแนวโน้ม หลังจากทำการตั้งค่าให้คลิกที่ปุ่ม "ปิด"



4. เส้นแนวโน้มถูกสร้างขึ้นและเราสามารถกำหนดจำนวนกำไรโดยประมาณในรอบสามปี ตามที่เราเห็นในเวลานั้นเธอควรแปลใน 4,500,000 รูเบิล ค่าสัมประสิทธิ์ R2 ดังที่กล่าวไว้ข้างต้นแสดงคุณภาพของเส้นแนวโน้ม ในกรณีของเราค่าของ R2 คือ 0.89 ค่าสัมประสิทธิ์ที่สูงขึ้นเท่าใดความน่าเชื่อถือของเส้นจะสูงขึ้น ค่าสูงสุดของมันอาจเท่ากับ 1 เชื่อว่ามีค่าสัมประสิทธิ์มากกว่า 0.85 เส้นแนวโน้มมีความน่าเชื่อถือ



5. หากคุณไม่พอใจกับระดับความน่าเชื่อถือคุณสามารถกลับไปที่หน้าต่างรูปแบบเส้นแนวโน้มและเลือกการประมาณประเภทอื่น ๆ คุณสามารถลองใช้ตัวเลือกที่มีอยู่ทั้งหมดเพื่อค้นหาสิ่งที่ถูกต้องที่สุด

   

   ควรสังเกตว่าการคาดการณ์ที่มีประสิทธิภาพโดยใช้การอนุมานผ่านเส้นแนวโน้มอาจเป็นไปหากระยะเวลาการคาดการณ์ไม่เกิน 30% ของช่วงเวลาที่วิเคราะห์ระยะเวลา นั่นคือเมื่อวิเคราะห์ระยะเวลา 12 ปีเราไม่สามารถคาดการณ์ที่มีประสิทธิภาพนานกว่า 3-4 ปี แต่แม้ในกรณีนี้มันจะค่อนข้างน่าเชื่อถือหากในช่วงเวลานี้จะไม่มีเหตุสุดวิสัยหรือตรงข้ามกับสถานการณ์ที่ดีที่สุดที่ไม่ได้อยู่ในช่วงก่อนหน้า

บทเรียน: วิธีการสร้างเส้นแนวโน้มใน Excel

วิธีที่ 2: ผู้ดำเนินการทำนาย

การอนุมานสำหรับข้อมูลตารางสามารถทำได้ผ่านคุณสมบัติมาตรฐานของการทำนาย Excel อาร์กิวเมนต์นี้หมายถึงหมวดหมู่ของเครื่องมือทางสถิติและมีไวยากรณ์ต่อไปนี้:

= ทำนาย (x; known_stations_y; ที่รู้จักค่า _x)

"x" เป็นอาร์กิวเมนต์ค่าของฟังก์ชั่นที่คุณต้องกำหนด ในกรณีของเราในฐานะที่เป็นข้อโต้แย้งจะเป็นปีที่ควรคาดการณ์ว่าควรทำ

"ค่าที่รู้จัก Y" - ฐานของค่าฟังก์ชั่นที่รู้จัก ในกรณีของเราในบทบาทของเธอขนาดของผลกำไรสำหรับช่วงก่อนหน้า

"ค่าที่รู้จัก X" เป็นอาร์กิวเมนต์ที่ตรงกับค่าที่รู้จักของฟังก์ชั่น ในบทบาทของพวกเขาเรามีหมายเลขของปีซึ่งมีการรวบรวมข้อมูลเกี่ยวกับผลกำไรของปีก่อนหน้านี้

ตามธรรมชาติในฐานะที่เป็นอาร์กิวเมนต์ไม่จำเป็นต้องเป็นส่วนชั่วคราว ตัวอย่างเช่นพวกเขาอาจเป็นอุณหภูมิและฟังก์ชั่นของฟังก์ชั่นสามารถเป็นระดับของการขยายน้ำในระหว่างความร้อน

เมื่อคำนวณวิธีนี้จะใช้วิธีการถดถอยเชิงเส้น

มาวิเคราะห์ความแตกต่างของแอปพลิเคชันของผู้ดำเนินการทำนายในตัวอย่างเฉพาะ ใช้ตารางเดียวกัน เราจะต้องค้นหาการคาดการณ์กำไรสำหรับปี 2561

1. เราเน้นเซลล์ว่างบนแผ่นงานที่มีการวางแผนผลลัพธ์การประมวลผล คลิกที่ปุ่ม "วางฟังก์ชั่น"



2. วิซาร์ดเปิดขึ้น ในหมวดหมู่ "สถิติ" จัดสรรชื่อ "ทำนาย" แล้วคลิกที่ปุ่ม "ตกลง"



3. หน้าต่างอาร์กิวเมนต์เริ่มต้นขึ้น ในฟิลด์ "X" เราระบุค่าของอาร์กิวเมนต์ที่ต้องพบค่าฟังก์ชั่น ในกรณีของเรามันคือปี 2018 ดังนั้นเราแนะนำบันทึก "2018" แต่มันจะดีกว่าที่จะระบุตัวบ่งชี้นี้ในเซลล์บนแผ่นงานและในฟิลด์ "X" เพียงแค่ให้ลิงค์ไปที่มัน สิ่งนี้จะช่วยให้ในอนาคตเพื่อทำการคำนวณโดยอัตโนมัติและเปลี่ยนปีได้อย่างง่ายดาย

   ในฟิลด์ "ค่านิยมที่รู้จัก Y" เราระบุพิกัดของคอลัมน์ "กำไรขององค์กร" สามารถทำได้โดยการติดตั้งเคอร์เซอร์ในฟิลด์จากนั้นปีนขึ้นปุ่มเมาส์ซ้ายและไฮไลต์คอลัมน์ที่เหมาะสมบนแผ่นงาน

   ในทำนองเดียวกันในฟิลด์ "ค่า X ที่รู้จัก" เราแนะนำที่อยู่ของปีของปีด้วยข้อมูลสำหรับช่วงเวลาที่ผ่านมา

   หลังจากข้อมูลทั้งหมดถูกสร้างขึ้นให้คลิกที่ปุ่ม "ตกลง"



4. ตัวดำเนินการคำนวณบนพื้นฐานของข้อมูลที่ป้อนและแสดงผลลัพธ์บนหน้าจอ สำหรับปี 2561 มีการวางแผนที่จะทำกำไรประมาณ 4564.7,000 รูเบิล ขึ้นอยู่กับตารางผลลัพธ์เราสามารถสร้างตารางโดยใช้เครื่องมือสร้างแผนภูมิที่กล่าวถึงข้างต้น



5. หากคุณเปลี่ยนปีในเซลล์ที่ใช้ในการป้อนอาร์กิวเมนต์ผลลัพธ์จะเปลี่ยนไปตามลำดับและตารางจะได้รับการปรับปรุงโดยอัตโนมัติ ตัวอย่างเช่นตามการคาดการณ์ในปี 2562 จำนวนกำไรจะเป็น 4637.8,000 รูเบิล

แต่อย่าลืมว่าในการก่อสร้างของเส้นแนวโน้มระยะเวลาจนถึงระยะเวลาที่คาดการณ์ไม่ควรเกิน 30% ของระยะเวลาทั้งหมดที่ฐานข้อมูลสะสม

บทเรียน: การอนุมานใน Excel

วิธีที่ 3: แนวโน้มผู้ประกอบการ

เพื่อทำนายคุณสามารถใช้ฟังก์ชั่นอื่น - แนวโน้ม นอกจากนี้ยังหมายถึงหมวดหมู่ของผู้ประกอบการทางสถิติ ไวยากรณ์ส่วนใหญ่มีลักษณะคล้ายไวยากรณ์ของเครื่องมือการเรียนการเรียนและมีลักษณะดังนี้:

= แนวโน้ม (ค่าที่รู้จัก _y; ที่รู้จักค่า _x; new_dation_x; [const])

อย่างที่เราเห็นข้อโต้แย้ง "ค่า Y ที่รู้จัก" และ "รู้จักค่า X" อย่างเต็มที่สอดคล้องกับองค์ประกอบที่คล้ายกันของตัวดำเนินการ predspot และอาร์กิวเมนต์ "ค่าใหม่ x" สอดคล้องกับอาร์กิวเมนต์ "x" ของเครื่องมือก่อนหน้านี้ นอกจากนี้แนวโน้มมีการโต้แย้งเพิ่มเติมของค่าคงที่ แต่มันไม่ได้บังคับและใช้เฉพาะในกรณีที่มีปัจจัยถาวร

ผู้ประกอบการนี้ใช้อย่างมีประสิทธิภาพที่สุดในการปรากฏตัวของการพึ่งพาการพึ่งพาเชิงเส้น

มาดูกันว่าเครื่องมือนี้จะทำงานอย่างไรกับอาร์เรย์ข้อมูลเดียวกัน เพื่อเปรียบเทียบผลลัพธ์ที่ได้รับจุดพยากรณ์จะกำหนด 2019

1. เราผลิตการกำหนดของเซลล์เพื่อแสดงผลลัพธ์และเรียกใช้ฟังก์ชั่นหลักในลักษณะปกติ ในหมวดหมู่ "สถิติ" เราพบและจัดสรรชื่อ "เทรนด์" คลิกที่ปุ่ม "ตกลง"



2. หน้าต่างอาร์กิวเมนต์ผู้ประกอบการแนวโน้มเปิดขึ้น ในฟิลด์ "ค่าที่รู้จักของ Y" วิธีการของพิกัดผลกำไรของ บริษัท ได้รับการอธิบายไว้ข้างต้นแล้ว ในฟิลด์ "ค่าที่รู้จัก X" ให้ป้อนที่อยู่ของคอลัมน์ปี ในฟิลด์ "ค่าใหม่ x" เราป้อนลิงค์ไปยังเซลล์ที่จำนวนปีที่ควรระบุการคาดการณ์ ในกรณีของเรามันคือปี 2019 ฟิลด์ "คงที่" ปล่อยให้ว่างเปล่า คลิกที่ปุ่ม "ตกลง"



3. ผู้ประกอบการประมวลผลข้อมูลและแสดงผลลัพธ์บนหน้าจอ อย่างที่คุณเห็นจำนวนกำไรที่คาดการณ์ไว้ในปี 2019 ซึ่งคำนวณโดยวิธีการพึ่งพาเชิงเส้นจะเป็นไปตามวิธีการคำนวณก่อนหน้านี้ 4637.8,000 รูเบิล

วิธีที่ 4: การเติบโตของผู้ประกอบการ

ฟังก์ชั่นอื่นที่คุณสามารถคาดการณ์ได้ใน Excele คือการเติบโตของผู้ปฏิบัติงาน นอกจากนี้ยังหมายถึงกลุ่มของเครื่องมือทางสถิติ แต่แตกต่างจากรุ่นก่อนหน้านี้ไม่ได้ใช้วิธีการพึ่งพาเชิงเส้น แต่เลขชี้กำลัง ไวยากรณ์ของเครื่องมือนี้ดูเหมือนว่า:

= การเจริญเติบโต (ค่าที่รู้จัก _y; ที่รู้จักค่า _x; new_stations_x; [const])

อย่างที่เราเห็นข้อโต้แย้งสำหรับคุณสมบัตินี้ซ้ำแล้วซ้ำอีกเทรนด์อาร์กิวเมนต์ของผู้ปฏิบัติงานดังนั้นเราจะไม่หยุดครั้งที่สองในคำอธิบายของพวกเขา แต่เราจะไปที่การใช้เครื่องมือนี้ทันทีในทางปฏิบัติ

1. เราจัดสรรเซลล์เอาต์พุตและมักจะทำให้ฟังก์ชั่นของฟังก์ชั่นอยู่แล้ว ในรายการผู้ประกอบการทางสถิติเรากำลังมองหาประโยค "การเติบโต" เราจัดสรรและคลิกที่ปุ่ม "ตกลง"



2. หน้าต่างอาร์กิวเมนต์ของฟังก์ชั่นที่ระบุจะเปิดใช้งาน เราป้อนในฟิลด์ของหน้าต่างนี้ข้อมูลนั้นคล้ายกับวิธีที่เราป้อนในหน้าต่างอาร์กิวเมนต์ของผู้ปฏิบัติงานแนวโน้ม หลังจากข้อมูลถูกสร้างขึ้นให้คลิกที่ปุ่ม "ตกลง"



3. ผลลัพธ์ของการประมวลผลข้อมูลจะปรากฏบนจอภาพในเซลล์ที่ระบุไว้ก่อนหน้านี้ อย่างที่เราเห็นในครั้งนี้ผลลัพธ์คือ 4682.1,000 รูเบิล ความแตกต่างจากผลการประมวลผลข้อมูลโดยแนวโน้มของผู้ประกอบการนั้นไม่มีนัยสำคัญ แต่มีอยู่ นี่เป็นเพราะความจริงที่ว่าเครื่องมือเหล่านี้ใช้วิธีการคำนวณที่แตกต่างกัน: วิธีการพึ่งพาเชิงเส้นและวิธีการติดยาเสพติดแบบทวีคูณ

วิธีที่ 5: Linene ผู้ประกอบการ

ตัวดำเนินการเชิงเส้นเมื่อคำนวณใช้วิธีการประมาณเชิงเส้น ไม่จำเป็นต้องสับสนกับวิธีการพึ่งพาเชิงเส้นที่ใช้โดยเครื่องมือแนวโน้ม ไวยากรณ์ชนิดนี้:

= Linene (รู้จักค่าที่รู้จัก _y; ans__x; new_stations_x; [const]; [สถิติ])

ข้อโต้แย้งสองข้อสุดท้ายเป็นตัวเลือก กับสองคนแรกที่เราคุ้นเคยในวิธีการก่อนหน้านี้ แต่คุณอาจสังเกตเห็นว่าไม่มีการโต้แย้งในฟังก์ชั่นนี้ระบุค่าใหม่ ความจริงก็คือเครื่องมือนี้กำหนดเพียงการเปลี่ยนแปลงจำนวนรายได้ต่อหน่วยของระยะเวลาซึ่งในกรณีของเราคือหนึ่งปี แต่ผลลัพธ์ทั่วไปที่จะคำนวณแยกต่างหากเพิ่มผลลัพธ์ของการคำนวณเชิงเส้นผู้ประกอบการคูณด้วย จำนวนปี

1. เราผลิตการเลือกเซลล์ที่จะทำการคำนวณและเรียกใช้ฟังก์ชั่นของฟังก์ชั่น เราจัดสรรชื่อ "Linene" ในหมวดหมู่ "สถิติ" และคลิกที่ปุ่ม "ตกลง"



2. ในฟิลด์ "ค่า r ค่า r" ซึ่งเปิดหน้าต่างของอาร์กิวเมนต์แนะนำพิกัดของคอลัมน์ "กำไรขององค์กร" ในฟิลด์ "ค่า X ที่มีชื่อเสียง" เราแนะนำที่อยู่ของคอลัมน์ "ปี" ฟิลด์ที่เหลือจะเหลือว่างเปล่า จากนั้นคลิกที่ปุ่ม "ตกลง"



3. โปรแกรมคำนวณและแสดงค่าของแนวโน้มเชิงเส้นไปยังเซลล์ที่เลือก



4. ตอนนี้เราต้องค้นหาขนาดของกำไรที่คาดการณ์ไว้ในปี 2562 ติดตั้งเครื่องหมาย "=" ลงในเซลล์ที่ว่างเปล่าบนแผ่นงาน คลิกที่เซลล์ซึ่งมีมูลค่าที่แท้จริงของผลกำไรสำหรับปีที่แล้วของปี (2016) ใส่เครื่องหมาย "+" คลิกเพิ่มเติมบนเซลล์ซึ่งมีแนวโน้มเชิงเส้นที่คำนวณได้ก่อนหน้านี้ เราใส่เครื่องหมาย "*" ตั้งแต่ระหว่างปีที่ผ่านมาของช่วงเวลาที่ศึกษา (2559) และปีที่จะทำการคาดการณ์ (2019) อยู่ในระยะเวลาสามปีที่ผ่านมาเราตั้งค่าหมายเลข "3" ในเซลล์ เพื่อทำการคำนวณโดยคลิกที่ปุ่ม Enter

อย่างที่คุณเห็นมูลค่ากำไรที่คาดการณ์ไว้คำนวณโดยวิธีการประมาณเชิงเส้นในปี 2562 จะเป็น 4614.9 พันรูเบิล

วิธีที่ 6: ผู้ประกอบการ lgrfpribl

เครื่องมือสุดท้ายที่เราจะดูจะเป็น lgrfpribl ผู้ให้บริการรายนี้ทำการคำนวณตามวิธีการประมาณแบบเลขชี้กำลัง ไวยากรณ์ของมันมีโครงสร้างต่อไปนี้:

= lgrefpriblin (รู้จักค่า _y; ที่รู้จัก vORES_X; new_stations_x; [const]; [สถิติ])

อย่างที่คุณเห็นอาร์กิวเมนต์ทั้งหมดทำซ้ำองค์ประกอบที่สอดคล้องกันของฟังก์ชั่นก่อนหน้าอย่างสมบูรณ์ อัลกอริทึมการคำนวณการคาดการณ์จะเปลี่ยนไปเล็กน้อย ฟังก์ชั่นจะคำนวณแนวโน้มเลขชี้กำลังที่จะแสดงจำนวนรายได้จำนวนเท่าใดในช่วงเวลาหนึ่งซึ่งเป็นเวลาสำหรับปี เราจะต้องค้นหาความแตกต่างของผลกำไรระหว่างช่วงเวลาจริงล่าสุดและการวางแผนครั้งแรกคูณด้วยจำนวนงวดที่วางแผนไว้ (3) และเพิ่มผลรวมของผลรวมของช่วงเวลาจริงล่าสุด

1. ในรายการผู้ประกอบการของฟังก์ชั่นหลักเราจัดสรรชื่อ "lgrfpribl" เราคลิกที่ปุ่ม "ตกลง"



2. หน้าต่างอาร์กิวเมนต์เริ่มต้นขึ้น ในนั้นเราแนะนำข้อมูลที่ตรงตามที่พวกเขาใช้เมื่อใช้ฟังก์ชั่นเชิงเส้น คลิกที่ปุ่ม "ตกลง"



3. ผลของแนวโน้มเลขชี้กำลังคำนวณและขยายไปยังเซลล์ที่กำหนด



4. เราใส่เครื่องหมาย "=" ลงในเซลล์ที่ว่างเปล่า เราเปิดวงเล็บและเลือกเซลล์ที่มีมูลค่าของรายได้สำหรับช่วงเวลาจริงล่าสุด เราใส่เครื่องหมาย "*" และเน้นเซลล์ที่มีแนวโน้มเลขชี้กำลัง เราใส่สัญลักษณ์ของลบและคลิกอีกครั้งในองค์ประกอบที่จำนวนรายได้อยู่ในช่วงสุดท้าย เราปิดวงเล็บและขับตัวละคร "* 3 +" โดยไม่มีเครื่องหมายคำพูด อีกครั้งคลิกที่เซลล์เดียวกันซึ่งจัดสรรเป็นครั้งสุดท้าย ในการคำนวณการคำนวณให้กดปุ่ม ENTER

จำนวนเงินที่คาดการณ์ของกำไรในปี 2562 ซึ่งคำนวณโดยวิธีการประมาณแบบเลขชี้กำลังจะเป็น 4639.2,000 รูเบิลซึ่งไม่แตกต่างจากผลการวิจัยอีกครั้งเมื่อคำนวณวิธีการก่อนหน้านี้

บทเรียน: ฟังก์ชั่นสถิติอื่น ๆ ใน Excel

เราพบว่าวิธีการที่สามารถคาดการณ์ได้ในโปรแกรม Excel มันสามารถทำได้โดยกราฟิกผ่านการใช้สายเทรนด์และการวิเคราะห์ - ใช้ฟังก์ชั่นทางสถิติในตัวจำนวนมาก อันเป็นผลมาจากการประมวลผลข้อมูลที่เหมือนกันผู้ประกอบการเหล่านี้อาจกลายเป็นผลลัพธ์ที่แตกต่างกัน แต่มันไม่น่าแปลกใจเนื่องจากพวกเขาทั้งหมดใช้วิธีการคำนวณที่แตกต่างกัน หากการแกว่งมีขนาดเล็กตัวเลือกเหล่านี้ทั้งหมดที่ใช้กับกรณีที่เฉพาะเจาะจงนั้นถือได้ว่าค่อนข้างน่าเชื่อถือ