วิธีกำลังสองน้อยที่สุดใช้ในการประมาณค่าพารามิเตอร์ของสมการถดถอย

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

1. ทางเลือกของรูปแบบการสื่อสาร (ประเภทของสมการถดถอยเชิงวิเคราะห์);
2. การประมาณค่าพารามิเตอร์ของสมการ
3. การประเมินคุณภาพของสมการถดถอยเชิงวิเคราะห์

ส่วนใหญ่มักใช้แบบฟอร์มเชิงเส้นเพื่ออธิบายความสัมพันธ์ทางสถิติของคุณลักษณะ ความใส่ใจต่อความสัมพันธ์เชิงเส้นอธิบายโดยการตีความทางเศรษฐศาสตร์ที่ชัดเจนของพารามิเตอร์ การแปรผันที่จำกัดของตัวแปร และข้อเท็จจริงที่ว่าในกรณีส่วนใหญ่ รูปแบบการสื่อสารที่ไม่เป็นเชิงเส้นสำหรับการคำนวณจะถูกแปลง (โดยลอการิทึมหรือการเปลี่ยนแปลงของตัวแปร) เป็นรูปแบบเชิงเส้น
ในกรณีของความสัมพันธ์แบบคู่เชิงเส้น สมการถดถอยจะอยู่ในรูปแบบ: y i = a + b x i + u i พารามิเตอร์ของสมการ a และ b นี้ประมาณการจากข้อมูลของการสังเกตทางสถิติ x และ y ผลลัพธ์ของการประเมินดังกล่าวคือสมการ โดยที่ ค่าประมาณของพารามิเตอร์ a และ b คือค่าของแอตทริบิวต์ที่มีประสิทธิผล (ตัวแปร) ที่ได้จากสมการถดถอย (ค่าที่คำนวณ)

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

ปัญหาการประมาณค่าพารามิเตอร์ของสมการคู่เชิงเส้นโดยวิธีกำลังสองน้อยที่สุดประกอบด้วยสิ่งต่อไปนี้: เพื่อให้ได้ค่าประมาณพารามิเตอร์ดังกล่าวซึ่งผลรวมของกำลังสองของการเบี่ยงเบนของค่าจริงของตัวบ่งชี้ที่มีประสิทธิภาพ - y i จากค่าที่คำนวณได้ - น้อยที่สุด
อย่างเป็นทางการ เกณฑ์ OLSสามารถเขียนได้ดังนี้ .

การจำแนกสี่เหลี่ยมน้อยที่สุด

1. วิธีกำลังสองน้อยที่สุด
2. วิธีความน่าจะเป็นสูงสุด (สำหรับแบบจำลองการถดถอยเชิงเส้นแบบคลาสสิกปกติ ค่าปกติของเศษเหลือจากการถดถอยจะถูกตั้งสมมติฐานไว้)
3. วิธี OLS กำลังสองน้อยที่สุดโดยทั่วไปจะใช้ในกรณีของความสัมพันธ์อัตโนมัติของข้อผิดพลาดและในกรณีของ heteroscedasticity
4. วิธีถ่วงน้ำหนักกำลังสองน้อยที่สุด (กรณีพิเศษของ OLS ที่มีเศษเหลือไม่เท่ากัน)

มาอธิบายสาระสำคัญกันเถอะ วิธีแบบกราฟิกกำลังสองน้อยที่สุดแบบคลาสสิก... ในการทำเช่นนี้ เราจะสร้างจุดพล็อตตามข้อมูลการสังเกต (x i, y i, i = 1; n) ในระบบพิกัดสี่เหลี่ยม (พล็อตจุดดังกล่าวเรียกว่าสนามสหสัมพันธ์) ลองหาเส้นตรงที่ใกล้กับจุดสนามสหสัมพันธ์มากที่สุดกัน ตามวิธีการของกำลังสองน้อยที่สุด เส้นจะถูกเลือกเพื่อให้ผลรวมของกำลังสองของระยะห่างแนวตั้งระหว่างจุดของสนามสหสัมพันธ์กับเส้นนี้มีค่าน้อยที่สุด

บันทึกทางคณิตศาสตร์ของปัญหานี้: .
เราทราบค่าของ y i และ x i = 1 ... n ซึ่งเป็นข้อมูลเชิงสังเกต ในฟังก์ชัน S พวกมันคือค่าคงที่ ตัวแปรในฟังก์ชันนี้เป็นค่าประมาณพารามิเตอร์ที่จำเป็น -, ในการหาค่าต่ำสุดของฟังก์ชัน 2 ตัวแปร จำเป็นต้องคำนวณอนุพันธ์ย่อยของฟังก์ชันนี้โดยเทียบกับพารามิเตอร์แต่ละตัวและนำมารวมกันเป็นศูนย์ กล่าวคือ .
เป็นผลให้เราได้ระบบสมการเชิงเส้นปกติ 2 สมการ:
ในการแก้ระบบนี้ เราพบการประมาณค่าพารามิเตอร์ที่จำเป็น:

ความถูกต้องของการคำนวณพารามิเตอร์ของสมการถดถอยสามารถตรวจสอบได้โดยการเปรียบเทียบผลรวม (อาจมีความคลาดเคลื่อนบ้างเนื่องจากการปัดเศษของการคำนวณ)
ในการคำนวณค่าประมาณพารามิเตอร์ คุณสามารถสร้างตารางที่ 1
เครื่องหมายของสัมประสิทธิ์การถดถอย b ระบุทิศทางของความสัมพันธ์ (ถ้า b> 0 ความสัมพันธ์เป็นแบบตรง ถ้า b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
อย่างเป็นทางการ ค่าของพารามิเตอร์ a คือค่าเฉลี่ยของ y ที่ x เท่ากับศูนย์ หากปัจจัยแอตทริบิวต์ไม่มีและไม่สามารถมีค่าเป็นศูนย์ได้ การตีความพารามิเตอร์ a ข้างต้นไม่สมเหตุสมผล

การประเมินความรัดกุมของความสัมพันธ์ระหว่างสัญญาณ ดำเนินการโดยใช้สัมประสิทธิ์สหสัมพันธ์คู่เชิงเส้น - r x, y สามารถคำนวณได้โดยใช้สูตร: ... นอกจากนี้ ค่าสัมประสิทธิ์สหสัมพันธ์เชิงเส้นคู่สามารถกำหนดได้โดยใช้ค่าสัมประสิทธิ์การถดถอย b: .
ช่วงของค่าที่ยอมรับได้ของสัมประสิทธิ์สหสัมพันธ์คู่เชิงเส้นคือตั้งแต่ –1 ถึง +1 เครื่องหมายของสัมประสิทธิ์สหสัมพันธ์ระบุทิศทางของการเชื่อมโยง ถ้า r x, y> 0 แสดงว่ามีการเชื่อมต่อโดยตรง ถ้า r x, y<0, то связь обратная.
หากสัมประสิทธิ์นี้ใกล้เคียงกับค่าสัมบูรณ์หนึ่งค่า ความสัมพันธ์ระหว่างคุณลักษณะสามารถตีความได้ว่าเป็นค่าเชิงเส้นที่ค่อนข้างใกล้เคียง หากโมดูลัสของมันเท่ากับหนึ่ง ê r x, y ê = 1 แสดงว่าการเชื่อมต่อระหว่างคุณลักษณะจะเป็นเชิงเส้นเชิงฟังก์ชัน หากจุดสนใจ x และ y มีความเป็นอิสระเชิงเส้น ดังนั้น r x, y จะเข้าใกล้ 0
ในการคำนวณ r x, y คุณสามารถใช้ตารางที่ 1 ได้เช่นกัน

ในการประเมินคุณภาพของสมการถดถอยที่ได้รับจะคำนวณค่าสัมประสิทธิ์ทางทฤษฎีของการกำหนด - R 2 yx:

,
โดยที่ d 2 คือความแปรปรวน y ที่อธิบายโดยสมการถดถอย
e 2 - เศษเหลือ (ไม่ได้อธิบายโดยสมการถดถอย) ความแปรปรวน y;
s 2 y คือความแปรปรวนรวม (ทั้งหมด) ของ y
สัมประสิทธิ์การกำหนดลักษณะสัดส่วนของการแปรผัน (ความแปรปรวน) ของลักษณะที่มีประสิทธิผล y อธิบายโดยการถดถอย (และด้วยเหตุนี้ ปัจจัย x) ในการแปรผันทั้งหมด (ความแปรปรวน) y สัมประสิทธิ์การกำหนด R 2 yx ใช้ค่าตั้งแต่ 0 ถึง 1 ดังนั้น ค่า 1-R 2 yx จะแสดงลักษณะสัดส่วนของความแปรปรวน y ที่เกิดจากอิทธิพลของปัจจัยอื่นๆ ที่ไม่ได้นำมาพิจารณาในข้อผิดพลาดของแบบจำลองและข้อมูลจำเพาะ
ด้วยการถดถอยเชิงเส้นคู่ R 2 yx = r 2 yx

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

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

การเปิดใช้งาน Solver add-in

ในการใช้ OLS ใน Excel คุณต้องเปิดใช้งาน add-in "ค้นหาวิธีแก้ปัญหา"ซึ่งถูกปิดใช้งานโดยค่าเริ่มต้น

ตอนนี้ฟังก์ชั่น หาทางออกใน Excel ถูกเปิดใช้งานและมีเครื่องมือปรากฏบนริบบอน

เงื่อนไขของปัญหา

ให้เราอธิบายการใช้งานของ OLS ด้วยตัวอย่างเฉพาะ เรามีตัวเลขสองแถว NS และ y ตามลำดับที่แสดงในภาพด้านล่าง

ฟังก์ชันนี้สามารถอธิบายการพึ่งพาอาศัยกันนี้ได้อย่างแม่นยำที่สุด:

นอกจากนี้ เป็นที่ทราบกันดีว่าสำหรับ x = 0 yก็เท่าเทียมกัน 0 ... ดังนั้นสมการนี้สามารถอธิบายได้โดยการพึ่งพา y = นx .

เราต้องหาผลรวมขั้นต่ำของกำลังสองของส่วนต่าง

สารละลาย

มาดูการอธิบายการใช้วิธีการโดยตรงกัน

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

วิธีกำลังสองน้อยที่สุด (OLS)

ระบบสมการเชิงเส้น m ที่มีค่าไม่ทราบค่า n มีรูปแบบดังนี้

เป็นไปได้สามกรณี: m NS. กรณีที่ m = n ถูกพิจารณาในส่วนที่แล้ว รูปร่าง

ถ้า m> n และระบบสอดคล้องกัน เมทริกซ์ A จะมีแถวที่ขึ้นกับเชิงเส้นอย่างน้อย m - n ที่นี่สามารถหาวิธีแก้ปัญหาได้โดยเลือกสมการอิสระเชิงเส้น n สมการ (ถ้ามี) และใช้สูตร X = A -1 CHV นั่นคือโดยการลดปัญหาลงเป็นสมการที่แก้ไขก่อนหน้านี้ ในกรณีนี้ คำตอบที่ได้จะเป็นไปตามสมการ m - n ที่เหลือเสมอ

อย่างไรก็ตามเมื่อใช้คอมพิวเตอร์จะสะดวกกว่าถ้าใช้วิธีทั่วไปมากขึ้น - วิธีกำลังสองน้อยที่สุด

พีชคณิตกำลังสองน้อยที่สุด

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

โดยการลดบรรทัดฐานแบบยุคลิดให้น้อยที่สุด

ขวาน? NS? > อินฟ. (1.2)

การวิเคราะห์ข้อมูลการทดลอง

พิจารณาการทดลองบางอย่าง ในช่วงเวลานั้น

วัดอุณหภูมิ Q (t) เช่น ให้ผลการวัดถูกกำหนดโดยอาร์เรย์

ให้เราสมมติว่าเงื่อนไขของการทดลองนั้นทำให้การวัดมีข้อผิดพลาดโดยเจตนา ในกรณีเหล่านี้ ให้ใช้กฎความแปรผันของอุณหภูมิ Q (t) โดยใช้พหุนาม

P (t) = + + + ... +,

การหาค่าสัมประสิทธิ์ไม่ทราบค่า ... จากการพิจารณาว่าค่า E (, ...,) กำหนดโดยความเท่าเทียมกัน

เกาส์พีชคณิต exel ประมาณ

เอาค่าต่ำสุด. เนื่องจากผลรวมของกำลังสองถูกย่อให้เล็กสุด วิธีนี้จึงเรียกว่าข้อมูลกำลังสองน้อยที่สุดพอดี

หากเราแทนที่ P (t) ด้วยนิพจน์ เราจะได้

ให้เราสร้างปัญหาในการกำหนดอาร์เรย์เพื่อให้ค่าน้อยที่สุด กล่าวคือ มากำหนดอาร์เรย์โดยใช้วิธีกำลังสองน้อยที่สุด ในการทำเช่นนี้ เราให้อนุพันธ์ย่อยบางส่วนเท่ากับศูนย์:

หากคุณป้อน m × n เมทริกซ์ A = (), i = 1, 2 ..., m; j = 1, 2, ..., n โดยที่

ผม = 1, 2 ..., ม.; เจ = 1, 2, ..., น,

แล้วความเท่าเทียมที่เป็นลายลักษณ์อักษรก็จะอยู่ในรูป

ให้เราเขียนความเท่าเทียมกันเป็นลายลักษณ์อักษรใหม่ในแง่ของการดำเนินการกับเมทริกซ์ เรามีตามนิยามของการคูณเมทริกซ์คอลัมน์

สำหรับเมทริกซ์ทรานสโพส ความสัมพันธ์ที่คล้ายคลึงกันจะเป็นดังนี้

ให้เราแนะนำสัญกรณ์: องค์ประกอบที่ i ของเวกเตอร์ขวานจะแสดงแทน ตามความเท่าเทียมกันของเมทริกซ์ที่เขียนไว้ เราจะมี

ในรูปแบบเมทริกซ์ ความเท่าเทียมกันนี้สามารถเขียนใหม่เป็น

เอ ที x = เอ ที บี (1.3)

โดยที่ A คือเมทริกซ์สี่เหลี่ยม m × n นอกจากนี้ ในปัญหาของการประมาณข้อมูล ตามกฎ m> n สมการ (1.3) เรียกว่าสมการปกติ

เป็นไปได้ตั้งแต่ต้นโดยใช้บรรทัดฐานของเวกเตอร์แบบยุคลิดเพื่อเขียนปัญหาในรูปแบบเมทริกซ์ที่เทียบเท่ากัน:

เป้าหมายของเราคือลดฟังก์ชันนี้ให้เหลือน้อยที่สุดเมื่อเทียบกับ x ในการที่จะไปถึงค่าต่ำสุดที่จุดสารละลาย อนุพันธ์อันดับ 1 เทียบกับ x ณ จุดนี้จะต้องเท่ากับศูนย์ อนุพันธ์ของฟังก์ชันนี้คือ

2A T B + 2A T ขวาน

ดังนั้นคำตอบจึงต้องเป็นไปตามระบบสมการเชิงเส้น

(A T A) x = (A T B)

สมการเหล่านี้เรียกว่าสมการปกติ ถ้า A เป็นเมทริกซ์ขนาด m × n แล้ว A> A - n × n เป็นเมทริกซ์ กล่าวคือ เมทริกซ์ของสมการปกติจะเป็นเมทริกซ์สมมาตรกำลังสองเสมอ ยิ่งไปกว่านั้น มันมีคุณสมบัติของการกำหนดเชิงบวกในแง่ที่ว่า (A> ขวาน, x) = (ขวาน, ขวาน)? 0.

ความคิดเห็น บางครั้งการแก้สมการของรูปแบบ (1.3) เรียกว่าคำตอบของระบบ Axe = B โดยที่ A คือเมทริกซ์สี่เหลี่ยมจัตุรัส m × n (m> n) โดยวิธีกำลังสองน้อยที่สุด

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

OLS เป็น Excel

อัลกอริทึมสำหรับการนำ OLS ไปใช้ใน Excel ด้านล่างถือว่าข้อมูลเริ่มต้นทั้งหมดเป็นที่รู้จักแล้ว สมการเมทริกซ์ทั้งสองด้าน AЧX = B ของระบบคูณทางซ้ายด้วยเมทริกซ์ทรานสโพสของระบบ AT:

AT AX = AT B

จากนั้นเราคูณทั้งสองข้างของสมการทางด้านซ้ายด้วยเมทริกซ์ (AT A) -1 ถ้าเมทริกซ์นี้มีอยู่ ระบบก็จะถูกกำหนด พิจารณาว่า

(AT A) -1 * (AT A) = E เราได้

X = (AT A) -1 AT B.

สมการเมทริกซ์ที่เป็นผลลัพธ์คือคำตอบของระบบสมการเชิงเส้น m ที่มีค่าไม่ทราบค่า n ค่าสำหรับ m> n

ลองพิจารณาการประยุกต์ใช้อัลกอริธึมข้างต้นสำหรับตัวอย่างเฉพาะ

ตัวอย่าง. ให้จำเป็นต้องแก้ระบบ

ใน Excel รายการที่มีโซลูชันในโหมดแสดงสูตรสำหรับงานนี้มีลักษณะดังนี้:

ผลการคำนวณ:

เวกเตอร์ X ที่ต้องการจะอยู่ในช่วง E11: E12

เมื่อแก้ระบบสมการเชิงเส้นที่กำหนด จะใช้ฟังก์ชันต่อไปนี้:

1. MOBRE - ส่งกลับค่าผกผันของเมทริกซ์ที่เก็บไว้ในอาร์เรย์

ไวยากรณ์: MOBR (อาร์เรย์)

Array - อาร์เรย์ตัวเลขที่มีจำนวนแถวและคอลัมน์เท่ากัน

2. MULTIPLE - ส่งคืนผลิตภัณฑ์ของเมทริกซ์ (เมทริกซ์ถูกเก็บไว้ในอาร์เรย์) ผลลัพธ์คืออาร์เรย์ที่มีจำนวนแถวเท่ากับ array1 และจำนวนคอลัมน์เท่ากับ array2

ไวยากรณ์: MULTIPLE (array1, array2)

Array1, array2 - อาร์เรย์คูณ

หลังจากป้อนฟังก์ชันในเซลล์ด้านซ้ายบนของช่วงอาร์เรย์แล้ว ให้เลือกอาร์เรย์ที่ขึ้นต้นด้วยเซลล์ที่มีสูตร จากนั้นกด F2 จากนั้นกด CTRL + SHIFT + ENTER

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

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

คำชี้แจงปัญหาโดยใช้ตัวอย่างเฉพาะ

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

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

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

คำสองสามคำเกี่ยวกับความถูกต้องของข้อมูลเบื้องต้นที่ใช้สำหรับการทำนาย

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

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

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

ข้อมูลตารางสามารถแสดงบนระนาบคาร์ทีเซียนเป็นจุด M 1 (x 1, y 1),… M n (x n, y n) ตอนนี้การแก้ปัญหาจะลดลงเหลือการเลือกฟังก์ชันการประมาณ y = f (x) ซึ่งมีกราฟผ่านใกล้จุด M 1, M 2, .. M n.

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

การประเมินความแม่นยำ

สำหรับการประมาณใด ๆ การประเมินความถูกต้องมีความสำคัญเป็นพิเศษ ให้เราแสดงด้วย e i ความแตกต่าง (ส่วนเบี่ยงเบน) ระหว่างค่าการทำงานและค่าทดลองสำหรับจุด x i นั่นคือ e i = y i - f (x i)

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

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

วิธีกำลังสองน้อยที่สุด

ดังที่คุณทราบใน Excel มีฟังก์ชัน autosum ในตัวที่ให้คุณคำนวณค่าของค่าทั้งหมดที่อยู่ในขอบเขตที่เลือก ดังนั้น ไม่มีอะไรขัดขวางเราจากการคำนวณค่าของนิพจน์ (e 1 2 + e 2 2 + e 3 2 + ... e n 2)

ในสัญกรณ์คณิตศาสตร์ดูเหมือนว่า:

ตั้งแต่แรกเริ่มตัดสินใจประมาณโดยใช้เส้นตรง เรามี:

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

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

หลังจากการแปลงอย่างง่าย รวมถึงการหารด้วย 2 และจัดการผลรวม เราจะได้:

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

วิธีการใช้วิธีกำลังสองน้อยที่สุดใน Excel

Excel มีฟังก์ชันสำหรับคำนวณค่า OLS มีรูปแบบดังนี้: "TREND" (ค่า Y ที่รู้จัก ค่า X ที่รู้จัก ค่า X ใหม่ const.) ลองใช้สูตรการคำนวณ OLS ใน Excel กับตารางของเรา

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

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

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

หากคุณต้องการทราบการคาดการณ์สำหรับค่า x มากกว่าหนึ่งค่าจากนั้นหลังจากป้อนสูตรแล้ว คุณไม่ควรกด "Enter" แต่คุณต้องพิมพ์ชุดค่าผสม "Shift" + "Control" + "Enter" บนแป้นพิมพ์ ("เข้า").

คุณสมบัติบางอย่าง

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

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

ฟังก์ชันพยากรณ์

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

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

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

วิธีเปิดใช้งาน Find Solution add-in

ก่อนอื่น มาดูวิธีเปิดใช้งาน Add-in นี้กันก่อน

1. ไปที่เมนู "ไฟล์" และเลือก "ตัวเลือก Excel"

2. ในหน้าต่างที่ปรากฏขึ้น เลือก "ค้นหาวิธีแก้ปัญหา" และคลิก "ไป"

3. ในหน้าต่างถัดไป ให้ทำเครื่องหมายที่หน้ารายการ "ค้นหาวิธีแก้ปัญหา" แล้วคลิก "ตกลง"

4. เปิดใช้งานส่วนเสริม - ตอนนี้สามารถพบได้ในรายการเมนู "ข้อมูล"

วิธีกำลังสองน้อยที่สุด

ตอนนี้สั้น ๆ เกี่ยวกับ วิธีกำลังสองน้อยที่สุด (OLS) และสามารถนำไปใช้ได้ที่ไหน

สมมติว่าเรามีชุดข้อมูลหลังจากที่เราทำการทดลอง ซึ่งเราศึกษาผลกระทบของค่า X ต่อค่า Y

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

ฉันจะยกตัวอย่างง่ายๆ (ดูรูป)

เป็นที่ชัดเจนว่าจุดต่างๆ เรียงต่อกันราวกับเป็นเส้นตรง ดังนั้นเราจึงถือว่าการพึ่งพาอาศัยกันของเราอธิบายโดยฟังก์ชันเชิงเส้น y = kx + b ในขณะเดียวกัน เราแน่ใจอย่างแน่นอนว่าเมื่อ X เท่ากับศูนย์ ค่าของ Y ก็จะเท่ากับศูนย์ด้วย ซึ่งหมายความว่าฟังก์ชันที่อธิบายการพึ่งพาอาศัยกันจะง่ายยิ่งขึ้น: y = kx (จำหลักสูตรของโรงเรียน)

โดยทั่วไป เราต้องหาสัมประสิทธิ์ k นี่คือสิ่งที่เราจะทำกับ OLS โดยใช้ส่วนเสริม "ค้นหาวิธีแก้ปัญหา"

วิธีการประกอบด้วยความจริงที่ว่า (ที่นี่ - ความสนใจ: คุณต้องคิดเกี่ยวกับมัน) ผลรวมของกำลังสองของความแตกต่างระหว่างค่าที่ได้จากการทดลองกับค่าที่คำนวณได้นั้นน้อยที่สุด นั่นคือเมื่อ X1 = 1 ค่าที่วัดได้จริง Y1 = 4.6 และค่าที่คำนวณได้ y1 = f (x1) คือ 4 ค่ากำลังสองของผลต่างจะเป็น (y1-Y1) ^ 2 = (4-4.6) ^ 2 = 0.36 ... เช่นเดียวกัน: เมื่อ X2 = 2 ค่าที่วัดได้จริง Y2 = 8.1 และ y2 ที่คำนวณได้คือ 8 ค่ากำลังสองของผลต่างจะเป็น (y2-Y2) ^ 2 = (8-8.1) ^ 2 = 0.01 . และผลรวมของกำลังสองเหล่านี้ควรมีค่าน้อยที่สุด

เรามาเริ่มฝึกการใช้ OLS และ ค้นหาโซลูชัน Add-in ของ Excel .

การใช้ add-in การค้นหาโซลูชัน

1. หากคุณไม่ได้เปิดส่วนเสริม "ค้นหาวิธีแก้ปัญหา" ให้กลับไปที่ point วิธีเปิดใช้งานการค้นหาโซลูชัน add-in และ enable 🙂

2. ในเซลล์ A1 ให้ป้อนค่า "1" หน่วยนี้จะเป็นค่าประมาณแรกของค่าจริงของสัมประสิทธิ์ (k) ของการพึ่งพาฟังก์ชัน y = kx

3. ในคอลัมน์ B เรามีค่าของพารามิเตอร์ X ในคอลัมน์ C - ค่าของพารามิเตอร์ Y ในเซลล์ของคอลัมน์ D เราป้อนสูตร: "สัมประสิทธิ์ k คูณด้วยค่าของ X ” ตัวอย่างเช่น ในเซลล์ D1 เราป้อน "= A1 * B1" ในเซลล์ D2 เราป้อน "= A1 * B2" เป็นต้น

4. เราเชื่อว่าสัมประสิทธิ์ k เท่ากับหนึ่ง และฟังก์ชัน f (x) = y = 1 * x เป็นการประมาณค่าแรกสำหรับโซลูชันของเรา เราสามารถคำนวณผลรวมของกำลังสองของส่วนต่างระหว่างค่าที่วัดได้ของ Y และค่าที่คำนวณโดยสูตร y = 1 * x เราสามารถทำสิ่งนี้ได้ด้วยตนเองโดยใส่การอ้างอิงเซลล์ที่เหมาะสมลงในสูตร: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... เป็นต้น ในที่สุดเราก็เป็น เข้าใจผิดและเข้าใจว่าเราเสียเวลาไปมากแล้ว ใน Excel สำหรับการคำนวณผลรวมกำลังสองของส่วนต่างมีสูตรพิเศษ "SUMKVRAZN" ซึ่งจะทำทุกอย่างให้เรา ป้อนในเซลล์ A2 และตั้งค่าเริ่มต้น ข้อมูล: ช่วงของค่าที่วัดได้ Y (คอลัมน์ C) และช่วงของค่า Y ที่คำนวณได้ (คอลัมน์ D)

4. คำนวณผลรวมของความแตกต่างของช่องสี่เหลี่ยมแล้ว - ตอนนี้เราไปที่แท็บ "ข้อมูล" และเลือก "ค้นหาวิธีแก้ปัญหา"

5. ในเมนูที่ปรากฏขึ้น ให้เลือกเซลล์ A1 (เซลล์ที่มีค่าสัมประสิทธิ์ k) เป็นเซลล์ที่จะเปลี่ยนแปลง

6. เลือกเซลล์ A2 เป็นเป้าหมายและตั้งค่าเงื่อนไข "ตั้งค่าให้เท่ากับค่าต่ำสุด" จำไว้ว่านี่คือเซลล์ที่เราคำนวณผลรวมของกำลังสองของส่วนต่างระหว่างค่าที่คำนวณและค่าที่วัดได้ และผลรวมนี้ควรมีค่าน้อยที่สุด คลิก "ดำเนินการ"

7. เลือกค่าสัมประสิทธิ์ k ตอนนี้คุณสามารถตรวจสอบได้ว่าค่าที่คำนวณได้นั้นใกล้เคียงกับค่าที่วัดได้มาก

ป.ล.

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

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