วิธีการคำนวณน้อยที่สุดใน Excel การวิเคราะห์การถดถอย

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

คำแถลงของปัญหาในตัวอย่างคอนกรีต

สมมติว่ามีสองตัวชี้วัดของ X และ Y และ Y ขึ้นอยู่กับ X เนื่องจาก OLS เป็นที่น่าสนใจสำหรับเราจากมุมมองของการวิเคราะห์การถดถอย (ใน Excel วิธีการของระบบจะดำเนินการด้วยความช่วยเหลือของฟังก์ชันที่มีอยู่ภายใน) ดังนั้นจึงคุ้มค่าที่จะหันไปพิจารณาปัญหาเฉพาะ

ดังนั้นให้ X - พื้นที่การค้าของร้านขายของชำวัดเป็นตารางเมตรและ Y - ผลประกอบการประจำปีที่กำหนดไว้ในล้านรูเบิล

มันเป็นสิ่งจำเป็นที่จะทำให้การคาดการณ์สิ่งที่หมุนเวียน (Y)จะอยู่ที่ร้านถ้าเขามีพื้นที่ขาย เห็นได้ชัดว่าฟังก์ชัน Y = f (X) จะเพิ่มขึ้นเนื่องจากไฮเปอร์มาร์เก็ตขายสินค้ามากกว่าแผงลอย

คำไม่กี่คำเกี่ยวกับความถูกต้องของข้อมูลเริ่มต้นที่ใช้ในการคาดคะเน

สมมติว่าเรามีตารางที่สร้างขึ้นตามข้อมูลสำหรับ n ร้านค้า

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

สาระสำคัญของวิธีการ

ข้อมูลตารางสามารถแสดงบนเครื่องบินคาร์ทีเซียนในรูปแบบของจุด M₁ (x₁, y₁), ... M_n (x_n, y_n) ตอนนี้การแก้ปัญหาจะลดลงไปถึงการเลือกฟังก์ชัน x = f (x) ที่มีกราฟที่ให้ใกล้ที่สุดเท่าที่จะทำได้กับจุด M_1, M_{2, ..}M_n.

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

การประมาณความถูกต้อง

สำหรับการประมาณค่าใด ๆ การประมาณค่าความถูกต้องจะมีความสำคัญเป็นพิเศษ เราแสดงถึงโดย e_ผม ค่าเบี่ยงเบนระหว่างค่าฟังก์ชันและค่าทดลองสำหรับจุด x_ผมนั่นคือ e_ผม = y_ผม - f (x_ผม)

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

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

วิธีของสี่เหลี่ยมน้อยที่สุด

ใน Excel อย่างที่คุณทราบมีอยู่ภายในฟังก์ชัน Auto-sum ที่ช่วยในการคำนวณค่าของค่าทั้งหมดที่อยู่ในช่วงที่เลือก ดังนั้นไม่มีอะไรที่จะป้องกันเราจากการคำนวณค่าของนิพจน์ (e₁² + e₂² + e₃²+ ... e_n²)

ในสัญกรณ์คณิตศาสตร์นี้มีรูปแบบ:

ตั้งแต่เริ่มต้นการตัดสินใจได้ทำไปประมาณกับเส้นตรงเรามี:

ดังนั้นปัญหาของการหาสายที่อธิบายถึงการพึ่งพาเฉพาะของปริมาณ X และ Y จะลดลงในการคำนวณค่าต่ำสุดของฟังก์ชันสองตัวแปร:

เมื่อต้องการทำเช่นนี้เราต้องถือเอาศูนย์อนุพันธ์ย่อยบางส่วนในส่วนที่เกี่ยวกับตัวแปรใหม่ a และ b และแก้ปัญหาระบบดั้งเดิมซึ่งประกอบด้วยสมการสองตัวที่มี 2 unknowns:

หลังจากแปลงง่ายรวมถึงการหารด้วย 2 และจัดการกับจำนวนเงินที่เราได้รับ:

การแก้ปัญหาเช่นโดยวิธี Cramer เราได้จุดยึดที่มีค่าสัมประสิทธิ์บางส่วน a^{* * * *} และข^{* * * *}. นี่คือขั้นต่ำนั่นคือเพื่อคาดการณ์สิ่งที่หมุนเวียนจะอยู่ที่ร้านสำหรับพื้นที่บางเส้นตรง y = a^{* * * *}x + b^{* * * *}ซึ่งเป็นรูปแบบการถดถอยสำหรับตัวอย่างคำถาม แน่นอนว่ามันจะไม่ช่วยให้คุณสามารถหาผลลัพธ์ที่แน่นอนได้ แต่จะช่วยให้คุณทราบว่าการซื้อพื้นที่โดยเฉพาะจะเป็นการชำระคืนเงินกู้หรือไม่

วิธีการใช้วิธีการอย่างน้อยสี่เหลี่ยมใน Excel

ใน "Excel" มีหน้าที่ในการคำนวณค่าโดย MNCs มีรูปแบบดังนี้: "TRENDS" (ค่าที่รู้จักกันในค่า Y, ค่าที่รู้จักของ X, ค่าใหม่ของ X, const) เราใช้สูตรสำหรับคำนวณ OLS ใน Excel ในตารางของเรา

สำหรับนี้เซลล์ที่มีเราจะใส่เครื่องหมาย "=" และเลือกฟังก์ชั่น "TRENDS" ในหน้าต่างที่เปิดขึ้นให้กรอกข้อมูลในฟิลด์ที่เกี่ยวข้องโดยไฮไลต์:

• ช่วงของค่าที่รู้จักกันสำหรับ Y (ในกรณีนี้ข้อมูลสำหรับการหมุนเวียน);
• ช่วง x₁, ... x_nได้แก่ ขนาดของพื้นที่ค้าปลีก
• และค่าที่รู้จักและไม่รู้จักของ x ซึ่งคุณต้องทราบขนาดของการหมุนเวียน (สำหรับข้อมูลเกี่ยวกับตำแหน่งของพวกเขาในแผ่นงานให้ดูด้านล่าง)

นอกจากนี้สูตรประกอบด้วยตัวแปรตรรกะ "Konst." ถ้าคุณป้อน 1 ในฟิลด์ที่ตรงกับค่านี้หมายความว่าคุณควรทำการคำนวณสมมติว่า b = 0

ถ้าคุณต้องการทราบค่าพยากรณ์มากกว่าหนึ่งค่า x แล้วหลังจากป้อนสูตรคุณไม่ควรกด "Enter" แต่คุณต้องพิมพ์ "Shift" + "Control" + "Enter" บนแป้นพิมพ์

คุณลักษณะบางอย่าง

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

• หากคุณจัดช่วงของค่าที่รู้จักy ตัวแปรในหนึ่งแถวหรือคอลัมน์แล้วแต่ละแถว (คอลัมน์) กับค่าที่รู้จักของ x จะรับรู้โดยโปรแกรมเป็นตัวแปรแยก
• หากช่วงไม่ได้ระบุไว้ในหน้าต่าง "TRENDS"x ที่รู้จักกันแล้วในกรณีของการใช้ฟังก์ชันใน Excel โปรแกรมจะถือว่าเป็นอาร์เรย์ประกอบด้วยจำนวนเต็มจำนวนซึ่งสอดคล้องกับช่วงที่มีค่าที่กำหนดของตัวแปร y
• เพื่อหาอาร์เรย์ของค่าที่คาดการณ์ไว้ในเอาต์พุตนิพจน์สำหรับการคำนวณแนวโน้มต้องถูกป้อนเป็นสูตรอาร์เรย์
• ถ้าไม่ระบุค่าใหม่ของ x ให้ใช้ฟังก์ชัน"เทรนด์" คิดว่าพวกเขามีความเท่าเทียมกัน หากไม่ได้ระบุไว้อาร์เรย์ 1 จะถูกนำมาเป็นอาร์กิวเมนต์ 2; 3; 4; ... ซึ่งเป็นสัดส่วนกับช่วงที่มีพารามิเตอร์ y แล้ว
• ช่วงที่มีค่า x ใหม่ต้องประกอบด้วยแถวหรือแถวเดียวกันหรือมากกว่าเป็นช่วงที่มีค่าที่กำหนดของ y กล่าวอีกนัยหนึ่งมันต้องเป็นไปตามตัวแปรอิสระ
• ในอาร์เรย์กับค่าที่รู้จักกันของ x,มีหลายตัวแปร อย่างไรก็ตามถ้าเป็นเพียงหนึ่งเดียวต้องมีช่วงที่มีค่า x และ y เป็นค่าที่กำหนด ในกรณีของตัวแปรหลายตัวแปรจำเป็นต้องมีช่วงที่มีค่า y อยู่ในหนึ่งคอลัมน์หรือหนึ่งบรรทัด

ฟังก์ชัน "PREDICTION"

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

ตอนนี้คุณรู้สูตรใน Excel for Dummies ซึ่งช่วยให้คุณคาดการณ์มูลค่าของมูลค่าในอนาคตของตัวบ่งชี้ที่กำหนดตามแนวโน้มเชิงเส้น