บทความนี้จะอธิบายเกี่ยวกับไวยากรณ์ของสูตรและการใช้ฟังก์ชัน LINEST ใน Microsoft Excel
คำอธิบาย
ฟังก์ชัน LINEST จะคํานวณสถิติของเส้นโดยใช้วิธี "สี่เหลี่ยมน้อยที่สุด" เพื่อคํานวณเส้นตรงที่เหมาะกับข้อมูลของคุณที่สุด แล้วส่งกลับอาร์เรย์ที่อธิบายเส้น คุณยังสามารถรวม LINEST กับฟังก์ชันอื่นๆ เพื่อคํานวณสถิติสําหรับโมเดลชนิดอื่นๆ ที่เป็นเชิงเส้นในพารามิเตอร์ที่ไม่รู้จัก รวมถึงโพลิโนเมียล ลอการิทึม เอ็กซ์โพเนนเชียล และพาวเวอร์ซีรีย์ เนื่องจากฟังก์ชันนี้ส่งกลับอาร์เรย์ของค่าต่างๆ จึงต้องใส่เป็นสูตรอาร์เรย์ คําแนะนําให้ทําตามตัวอย่างในบทความนี้
สมการของเส้นตรงคือ
y = mx + b
หรือ
y = m1x1 + m2x2 + ... + b
ถ้ามีช่วงของค่า x หลายช่วง โดยที่ค่า y ที่ไม่เป็นอิสระคือฟังก์ชันของค่า x ที่เป็นอิสระ ค่า m คือสัมประสิทธิ์ที่สอดคล้องกับแต่ละค่า x และ b เป็นค่าคงที่ โปรดทราบว่า y, x และ m สามารถเป็นเวกเตอร์ได้ อาร์เรย์ที่ฟังก์ชัน LINEST ส่งกลับคือ {mn,mn-1,...,m1,b} LINEST ยังสามารถส่งกลับค่าสถิติการถดถอยเพิ่มเติมได้
ไวยากรณ์
LINEST(known_y's, [known_x's], [const], [stats])
ไวยากรณ์ของฟังก์ชัน LINEST มีอาร์กิวเมนต์ดังนี้
ไวยากรณ์
-
ของ known_y จำเป็น ชุดของค่า y ที่คุณทราบอยู่แล้วในความสัมพันธ์ y = mx + b
-
ถ้าช่วงของ known_y อยู่ในคอลัมน์เดียว แต่ละคอลัมน์ของ known_x's จะถูกแปลงเป็นตัวแปรแยกต่างหาก
-
ถ้าช่วงของ known_y อยู่ในแถวเดียว แต่ละแถวของ known_x จะถูกแปลงเป็นตัวแปรแยกต่างหาก
-
-
ของ known_x ไม่จำเป็น ชุดของค่า x ที่คุณอาจทราบอยู่แล้วในความสัมพันธ์ y = mx + b
-
ช่วงของ known_x อาจประกอบด้วยชุดของตัวแปรตั้งแต่หนึ่งชุดขึ้นไป ถ้ามีการใช้ตัวแปรเพียงตัวเดียว known_y และของ known_x สามารถเป็นช่วงของรูปร่างใดๆ ก็ได้ ตราบใดที่มีมิติเท่ากัน ถ้ามีการใช้ตัวแปรมากกว่าหนึ่งตัวแปร known_y ต้องเป็น เวกเตอร์ (นั่นคือ ช่วงที่มีความสูงหนึ่งแถวหรือความกว้างหนึ่งคอลัมน์)
-
ถ้าไม่ได้ใส่ค่าอาร์เรย์ known_x's อาร์เรย์จะถูกกำหนดเป็นอาร์เรย์ {1,2,3,...} ที่มีขนาดเท่ากับอาร์เรย์ known_y's
-
-
const ไม่จำเป็น ค่าตรรกะที่ระบุว่าจะบังคับให้ค่าคงที่ b เท่ากับ 0 หรือไม่
-
ถ้า const เป็น TRUE หรือละไว้ b จะถูกคำนวณตามวิธีปกติ
-
ถ้า const เป็น FALSE จะตั้งค่า b ให้เท่ากับ 0 และปรับค่า m ให้เหมาะกับสมการ y = mx
-
-
สถิติ ไม่จำเป็น ค่าตรรกะที่ระบุว่าจะส่งกลับสถิติการถดถอยเพิ่มเติมหรือไม่
-
ถ้า stats เป็น TRUE ฟังก์ชัน LINEST จะส่งกลับค่าสถิติการถดถอยเพิ่มเติม ดังนั้น อาร์เรย์ที่ส่งกลับคือ {mn,mn-1,...,m1,b; sen, sen-1,...,se1,seb; r2,sey; F,df; ssreg, ssresid}
-
ถ้า stats เป็น FALSE หรือถ้าไม่ใส่ค่าใดไว้ LINEST จะส่งกลับเฉพาะค่าสัมประสิทธิ์ m และค่าคงที่ b เท่านั้น
สถิติการถดถอยเพิ่มเติมมีดังนี้
-
|
สถิติ |
คำอธิบาย |
|---|---|
|
se1,se2,...,sen |
ค่าความผิดพลาดมาตรฐานของสัมประสิทธิ์ m1,m2,...,mn |
|
seb |
ค่าความผิดพลาดมาตรฐานของค่าคงที่ b (seb = #N/A เมื่อ const เป็น FALSE) |
|
r2 |
สัมประสิทธิ์ของการกําหนด เปรียบเทียบค่า y โดยประมาณและค่า y จริง และช่วงในค่าตั้งแต่ 0 ถึง 1 ถ้าเป็น 1 แสดงว่ามีสหสัมพันธ์ที่สมบูรณ์แบบในตัวอย่าง — ไม่มีความแตกต่างระหว่างค่า y โดยประมาณและค่า y จริง ในทางตรงกันข้าม ถ้าสัมประสิทธิ์ของการกําหนดเป็น 0 สมการการถดถอยจะไม่เป็นประโยชน์ในการคาดเดาค่า y สําหรับข้อมูลเกี่ยวกับวิธีคํานวณ2 ให้ดูที่ "ข้อสังเกต" ต่อไปในหัวข้อนี้ |
|
sey |
ค่าความผิดพลาดมาตรฐานของค่าประมาณ y |
|
F |
สถิติ F หรือค่า F ที่ได้จากการสังเกต ใช้สถิติ F เพื่อตรวจสอบว่าความสัมพันธ์ที่สังเกตได้ระหว่างตัวแปรที่ไม่เป็นอิสระและตัวแปรอิสระเกิดขึ้นโดยบังเกิลหรือไม่ |
|
df |
ระดับความเป็นอิสระ ใช้องศาความเป็นอิสระเพื่อช่วยให้คุณค้นหาค่าวิกฤต F ในตารางทางสถิติ เปรียบเทียบค่าที่คุณพบในตารางกับสถิติ F ที่ส่งกลับโดย LINEST เพื่อกําหนดระดับความเชื่อมั่นสําหรับตัวแบบ สําหรับข้อมูลเกี่ยวกับวิธีคํานวณ df ให้ดูที่ "ข้อสังเกต" ต่อไปในหัวข้อนี้ ตัวอย่างที่ 4 แสดงการใช้ F และ df |
|
ssreg |
ผลรวมกำลังสองที่ถดถอย |
|
ssresid |
ผลรวมกําลังสองที่เหลืออยู่ สําหรับข้อมูลเกี่ยวกับวิธีการคํานวณ ssreg และ ssresid ให้ดูที่ "ข้อสังเกต" ต่อไปในหัวข้อนี้ |
ภาพประกอบตัวอย่างต่อไปนี้แสดงลำดับที่ส่งกลับค่าสถิติการถดถอยเพิ่มเติม
ข้อสังเกต
-
คุณสามารถอธิบายเส้นตรงด้วยความชัน และจุดตัดบนแกน y
ความชัน (ม.): เมื่อต้องการหาความชันของเส้นตรง ซึ่งมักจะเขียนเป็น m ให้ใช้จุดสองจุดบนเส้นตรง (x1,y1) และ (x2,y2) ความชันเท่ากับ (y2 - y1)/(x2 - x1)
จุดตัดแกน Y (b): จุดตัดแกน y ของเส้นตรง ซึ่งมักจะเขียนเป็น b คือค่าของ y ณ จุดที่เส้นตรงตัดแกน y
สมการของเส้นตรงคือ y = mx + b เมื่อคุณทราบค่า m และ b คุณสามารถคํานวณจุดใดก็ได้บนเส้นโดยการเสียบค่า y หรือ x เข้ากับสมการนั้น คุณยังสามารถใช้ฟังก์ชัน TREND ได้อีกด้วย
-
เมื่อคุณมีตัวแปร x ที่เป็นตัวแปรอิสระเพียงตัวเดียว คุณสามารถหาความชัน และจุดตัดแกน y ได้โดยตรงด้วยการใช้สูตรต่อไปนี้
ลาด: =INDEX(LINEST(known_y's,known_x's),1)
จุดตัดแกน Y: =INDEX(LINEST(known_y's,known_x's),2)
-
ความถูกต้องของเส้นที่คํานวณโดยฟังก์ชัน LINEST จะขึ้นอยู่กับระดับของการกระจายในข้อมูลของคุณ ยิ่งข้อมูลมีเชิงเส้นมากเท่าไหร่LINEST ใช้วิธีกําลังสองน้อยที่สุดในการกําหนดว่าเหมาะสมที่สุดสําหรับข้อมูล เมื่อคุณมีตัวแปร x อิสระเพียงตัวเดียว การคํานวณสําหรับ m และ b จะยึดตามสูตรต่อไปนี้
เมื่อ x และ y เป็นค่าเฉลี่ยของตัวอย่าง นั่นคือ x = AVERAGE(known x's) และ y = AVERAGE(known_y's)
-
ฟังก์ชันการปรับเส้นตรง LINEST และฟังก์ชันการปรับเส้นโค้ง LOGEST สามารถคํานวณเส้นตรงหรือเส้นโค้งที่เหมาะสมกับข้อมูลของคุณที่สุด อย่างไรก็ตาม คุณต้องตัดสินใจว่าผลลัพธ์ใดจากสองผลลัพธ์ที่เหมาะสมกับข้อมูลของคุณที่สุด คุณสามารถคํานวณ TREND(known_y's,known_x's) สําหรับเส้นตรง หรือ GROWTH(known_y'known_x s) สําหรับเส้นโค้งเอ็กซ์โพเนนเชียล ฟังก์ชันเหล่านี้ที่ไม่มีอาร์กิวเมนต์ ของ new_x ให้ส่งกลับอาร์เรย์ของค่า y ที่ถูกทํานายตามเส้นหรือเส้นโค้งนั้นที่จุดข้อมูลจริงของคุณ คุณสามารถเปรียบเทียบค่าที่ทํานายกับค่าจริง คุณอาจต้องการสร้างแผนภูมิทั้งสองสําหรับการเปรียบเทียบด้วยภาพ
-
ในการวิเคราะห์การถดถอย Excel จะคํานวณผลต่างยกกําลังสองระหว่างค่า y ที่ประเมินไว้สําหรับจุดนั้นและค่า y ที่แท้จริง ผลรวมของผลต่างยกกําลังสองเหล่านี้เรียกว่าผลรวมของค่ายกกําลังสองที่เหลือ ssresid Excel จะคํานวณผลรวมทั้งหมดของค่ายกกําลังสอง ซึ่งคือค่าสโตตาล เมื่ออาร์กิวเมนต์ const = TRUE หรือละไว้ ผลรวมยกกําลังสองทั้งหมดคือผลรวมของผลต่างยกกําลังสองระหว่างค่า y จริงและค่าเฉลี่ยของค่า y เมื่ออาร์กิวเมนต์ const = FALSE ผลรวมผลรวมของค่ายกกําลังสองคือผลรวมของค่า y จริง (โดยไม่ลบค่า y ค่าเฉลี่ยจากแต่ละค่า y) จากนั้นหาผลรวมของกําลังสอง ssreg ถดถอยได้จาก: ssreg = sstotal - ssresid ยิ่งผลรวมของกําลังสองที่เหลือมีขนาดเล็กลงเท่าใด เมื่อเปรียบเทียบกับผลรวมของกําลังสองทั้งหมด ยิ่งค่าของสัมประสิทธิ์ของการกําหนดมากน้อยเพียงใด r2 ซึ่งเป็นตัวบ่งชี้ว่าสมการเป็นผลมาจากการวิเคราะห์การถดถอยมากเพียงใด จะอธิบายความสัมพันธ์ระหว่างตัวแปรต่างๆ ค่าของ r2 เท่ากับ ssreg/sstotal
-
ในบางกรณี คอลัมน์ X อย่างน้อยหนึ่งคอลัมน์ (สมมติว่า Y และ X's อยู่ในคอลัมน์) อาจไม่มีค่าที่ทํานายเพิ่มเติมเมื่อมีคอลัมน์ X อื่นๆ กล่าวอีกนัยหนึ่ง การกําจัดคอลัมน์ X อย่างน้อยหนึ่งคอลัมน์อาจทําให้ค่า Y ที่ทํานายมีความถูกต้องเท่าๆ กัน ในกรณีดังกล่าว คอลัมน์ X ที่ซ้ําซ้อนเหล่านี้ควรถูกละเว้นจากรูปแบบการถดถอย ปรากฏการณ์นี้เรียกว่า "ภาวะร่วมเส้นตรง" เนื่องจากคอลัมน์ X ที่ซ้ําซ้อนใดๆ สามารถแสดงเป็นผลรวมของแผนภูมิพหุคูณของคอลัมน์ X ที่ไม่ซ้ําซ้อน ฟังก์ชัน LINEST จะตรวจสอบความเป็นเส้นตรง และนําคอลัมน์ X ที่ซ้ําซ้อนออกจากตัวแบบข้อมูลการถดถอยเมื่อระบุคอลัมน์เหล่านั้น คอลัมน์ X ที่ถูกเอาออกสามารถถูกรับรู้ได้ในผลลัพธ์ ของ LINEST ว่ามีสัมประสิทธิ์ 0 ค่านอกเหนือจากค่า 0 se ถ้าคอลัมน์อย่างน้อยหนึ่งคอลัมน์ถูกเอาออกเป็นคอลัมน์ที่ซ้ําซ้อน df จะได้รับผลกระทบเนื่องจาก df จะขึ้นอยู่กับจํานวนของคอลัมน์ X ที่ใช้เพื่อทํานายจริงๆ สําหรับรายละเอียดเกี่ยวกับการคํานวณ df โปรดดูตัวอย่างที่ 4 ถ้า df ถูกเปลี่ยนแปลงเนื่องจากคอลัมน์ X ซ้ําซ้อนถูกเอาออก ค่าของ sey และ F จะได้รับผลกระทบเช่นกัน ความเป็นเส้นตรงควรค่อนข้างหายากในทางปฏิบัติ อย่างไรก็ตาม กรณีหนึ่งที่มีโอกาสเกิดขึ้นคือเมื่อคอลัมน์ X บางคอลัมน์มีค่าเพียง 0 และ 1 เป็นตัวระบุว่าเรื่องของการทดลองเป็นสมาชิกของกลุ่มใดกลุ่มหนึ่งหรือไม่ ถ้า const = TRUE หรือละไว้ ฟังก์ชัน LINEST จะแทรกคอลัมน์ X เพิ่มเติมของค่า 1 ทั้งหมดเพื่อสร้างแบบจําลองจุดตัดแกนอย่างมีประสิทธิภาพ ถ้าคุณมีคอลัมน์ที่มีค่า 1 สําหรับแต่ละหัวข้อถ้าเป็นผู้ชาย หรือ 0 ถ้าไม่มี และคุณยังมีคอลัมน์ที่มี 1 สําหรับหัวข้อแต่ละเรื่องถ้าเป็นผู้หญิง หรือ 0 ถ้าไม่ คอลัมน์หลังนี้ซ้ําซ้อนเนื่องจากรายการในคอลัมน์นั้นสามารถหาได้จากการลบรายการในคอลัมน์ "ตัวบ่งชี้ชาย" จากรายการในคอลัมน์เพิ่มเติมของค่า 1 ทั้งหมดที่เพิ่มโดยฟังก์ชัน LINEST
-
ค่าของ df จะถูกคํานวณดังนี้ เมื่อไม่มีการลบคอลัมน์ X ออกจากตัวแบบเนื่องจากภาวะร่วมเส้นตรง: ถ้ามีคอลัมน์ k ของ known_x และ const = TRUE หรือละไว้ df = n – k – 1 ถ้า const = FALSE, df = n - k ในทั้งสองกรณี คอลัมน์ X แต่ละคอลัมน์ที่ถูกเอาออกเนื่องจากความเป็นเส้นตรงจะเพิ่มค่า df ขึ้น 1
-
เมื่อใส่ค่าคงที่อาร์เรย์ (เช่น known_x)เป็นอาร์กิวเมนต์ ให้ใช้เครื่องหมายจุลภาคคั่นระหว่างค่าที่อยู่ในแถวเดียวกัน และใช้เครื่องหมายอัฒภาคคั่นระหว่างแถวแต่ละแถว อักขระตัวคั่นอาจแตกต่างกันขึ้นอยู่กับการตั้งค่าภูมิภาคของคุณ
-
จงจำไว้ว่าค่า y จากการทำนายโดยสมการการถดถอยอาจไม่ถูกต้องถ้าอยู่นอกช่วงของค่า y ที่คุณใช้กำหนดสมการ
-
อัลกอริทึมพื้นฐานที่ใช้ในฟังก์ชัน LINEST แตกต่างจากอัลกอริทึมพื้นฐานที่ใช้ในฟังก์ชัน SLOPE และ INTERCEPT ความแตกต่างระหว่างอัลกอริทึมเหล่านี้อาจนําไปสู่ผลลัพธ์ที่แตกต่างกันเมื่อไม่ได้ระบุข้อมูลและร่วมเส้นตรง ตัวอย่างเช่น ถ้าจุดข้อมูลของอาร์กิวเมนต์ ของ known_y เป็น 0 และจุดข้อมูลของอาร์กิวเมนต์ ของ known_x คือ 1:
-
LINEST ส่งกลับค่า 0 อัลกอริทึมของฟังก์ชัน LINEST ถูกออกแบบมาเพื่อให้ส่งกลับผลลัพธ์ที่สมเหตุสมผลสําหรับข้อมูลร่วมเส้นตรงและในกรณีนี้สามารถพบได้อย่างน้อยหนึ่งคําตอบ
-
ฟังก์ชัน SLOPE และ INTERCEPT จะส่งกลับค่า #DIV/0! ข้อผิดพลาด อัลกอริทึมของฟังก์ชัน SLOPE และ INTERCEPT ได้รับการออกแบบมาให้ค้นหาคําตอบเดียวเท่านั้นและในกรณีนี้อาจมีมากกว่าหนึ่งคําตอบ
-
-
นอกจากการใช้ LOGEST เพื่อคํานวณสถิติสําหรับชนิดการถดถอยอื่นๆ แล้ว คุณสามารถใช้ LINEST เพื่อคํานวณช่วงของชนิดการถดถอยอื่นๆ ได้โดยการใส่ฟังก์ชันของตัวแปร x และ y เป็นชุดข้อมูล x และ y สําหรับ LINEST ตัวอย่างเช่น สูตรต่อไปนี้:
=LINEST(yvalues, xvalues^COLUMN($A:$C))
จะทำงานเมื่อคุณมีคอลัมน์เดียวของค่า y และคอลัมน์เดียวของค่า x เพื่อคำนวณค่าประมาณ (โพลิโนเมียลอันดับ 3) แบบลูกบาศก์ของฟอร์ม
y = m1*x + m2*x^2 + m3*x^3 + b
คุณสามารถปรับสูตรนี้เพื่อคำนวณการถดถอยชนิดอื่นได้ แต่ในบางกรณีจำเป็นที่จะต้องปรับค่าผลลัพธ์และสถิติอื่นด้วย
-
ค่า F-test ที่ส่งกลับโดยฟังก์ชัน LINEST แตกต่างจากค่า F-test ที่ส่งกลับโดยฟังก์ชัน FTEST LINEST จะส่งกลับค่าสถิติ F ขณะที่ FTEST จะส่งกลับค่าความน่าจะเป็น
ตัวอย่าง
ตัวอย่าง 1 - ความชันและจุดตัดแกน Y
คัดลอกข้อมูลตัวอย่างในตารางต่อไปนี้ และวางในเซลล์ A1 ของเวิร์กชีต Excel ใหม่ สำหรับสูตรที่จะแสดงผลลัพธ์ ให้เลือกสูตร กด F2 แล้วกด Enter ถ้าคุณต้องการ คุณสามารถปรับความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมดได้
|
ค่า y ที่ทราบแล้ว |
ค่า x ที่ทราบแล้ว |
|---|---|
|
1 |
0 |
|
9 |
4 |
|
5 |
2 |
|
7 |
3 |
|
ผลลัพธ์ (ความชัน) |
ผลลัพธ์ (จุดตัดแกน y) |
|
2 |
1 |
|
สูตร (สูตรอาร์เรย์ในเซลล์ A7:B7) |
|
|
=LINEST(A2:A5,B2:B5,,FALSE) |
ตัวอย่าง 2 - การถดถอยเชิงเส้นแบบเชิงเดียว
คัดลอกข้อมูลตัวอย่างในตารางต่อไปนี้ และวางในเซลล์ A1 ของเวิร์กชีต Excel ใหม่ สำหรับสูตรที่จะแสดงผลลัพธ์ ให้เลือกสูตร กด F2 แล้วกด Enter ถ้าคุณต้องการ คุณสามารถปรับความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมดได้
|
เดือน |
ยอดขาย |
|---|---|
|
1 |
$3,100 |
|
2 |
$4,500 |
|
3 |
$4,400 |
|
4 |
$5,400 |
|
5 |
$7,500 |
|
6 |
$8,100 |
|
สูตร |
ผลลัพธ์ |
|
=SUM(LINEST(B1:B6, A1:A6)*{9,1}) |
$11,000 |
|
คำนวณค่าประมาณของยอดขายในเดือนที่เก้า โดยใช้ยอดขายในเดือนที่ 1 ถึงเดือนที่ 6 |
ตัวอย่าง 3 - การถดถอยเชิงเส้นแบบพหุคูณ
คัดลอกข้อมูลตัวอย่างในตารางต่อไปนี้ และวางในเซลล์ A1 ของเวิร์กชีต Excel ใหม่ สำหรับสูตรที่จะแสดงผลลัพธ์ ให้เลือกสูตร กด F2 แล้วกด Enter ถ้าคุณต้องการ คุณสามารถปรับความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมดได้
|
พื้นที่ของชั้น (x1) |
สำนักงาน (x2) |
ทางเข้า (x3) |
อายุ (x4) |
ค่าที่ประเมินได้ (y) |
|---|---|---|---|---|
|
2310 |
2 |
2 |
20 |
$142,000 |
|
2333 |
2 |
2 |
1.2 |
$144,000 |
|
2356 |
3 |
1.5 |
33 |
$151,000 |
|
2379 |
3 |
2 |
43 |
$150,000 |
|
2402 |
2 |
3 |
53 |
$139,000 |
|
2425 |
4 |
2 |
23 |
$169,000 |
|
2448 |
2 |
1.5 |
99 |
$126,000 |
|
2471 |
2 |
2 |
34 |
$142,900 |
|
2494 |
3 |
3 |
23 |
$163,000 |
|
2517 |
4 |
4 |
55 |
$169,000 |
|
2540 |
2 |
3 |
22 |
$149,000 |
|
-234.2371645 |
||||
|
13.26801148 |
||||
|
0.996747993 |
||||
|
459.7536742 |
||||
|
1732393319 |
||||
|
สูตร (สูตรอาร์เรย์แบบไดนามิกที่ใส่ใน A19) |
||||
|
=LINEST(E2:E12,A2:D12,TRUE,TRUE) |
ตัวอย่างที่ 4 - การใช้สถิติ F และ r2
ในตัวอย่างก่อนหน้านี้ สัมประสิทธิ์ของการกําหนด หรือ r2 คือ 0.99675 (ดูเซลล์ A17 ในผลลัพธ์สําหรับ LINEST) ซึ่งจะระบุความสัมพันธ์ที่แข็งแกร่งระหว่างตัวแปรอิสระและราคาขาย คุณสามารถใช้สถิติ F เพื่อตรวจสอบว่าผลลัพธ์เหล่านี้มีค่า r2 สูงเกิดขึ้นโดยบังเกิลหรือไม่
สมมติว่าในขณะที่ในความเป็นจริงไม่มีความสัมพันธ์ระหว่างตัวแปร แต่คุณได้วาดตัวอย่างที่หายากของอาคารสํานักงาน 11 ที่ทําให้เกิดการวิเคราะห์ทางสถิติเพื่อแสดงความสัมพันธ์ที่แข็งแกร่ง คําว่า "Alpha" จะใช้สําหรับความน่าจะเป็นของการสรุปอย่างไม่ถูกต้องว่ามีความสัมพันธ์
ค่า F และ df ในเอาต์พุตจากฟังก์ชัน LINEST สามารถใช้เพื่อประเมินความเป็นไปได้ที่ค่า F ที่สูงกว่าจะเกิดขึ้นโดยบังเกิล สามารถเปรียบเทียบ F กับค่าวิกฤตในตารางการแจกแจง F หรือฟังก์ชัน FDIST ที่เผยแพร่ใน Excel สามารถใช้เพื่อคํานวณความน่าจะเป็นของค่า F ที่มีขนาดใหญ่ขึ้นโดยบังเกิล การแจกแจง F ที่เหมาะสมมีระดับความเป็นอิสระ v1 และ v2 ถ้า n เป็นจํานวนจุดข้อมูล และ const = TRUE หรือละไว้ v1 = n – df – 1 และ v2 = df (ถ้า const = FALSE แล้ว v1 = n – df และ v2 = df.) ฟังก์ชัน FDIST ที่มีไวยากรณ์ FDIST(F,v1,v2) จะส่งกลับค่าความน่าจะเป็นของค่า F ที่สูงกว่าที่เกิดขึ้นตามโอกาส ในตัวอย่างนี้ df = 6 (เซลล์ B18) และ F = 459.753674 (เซลล์ A18)
ถ้าค่า Alpha เท่ากับ 0.05, v1 = 11 – 6 – 1 = 4 และ v2 = 6 ระดับวิกฤตของ F คือ 4.53 เนื่องจาก F = 459.753674 สูงกว่า 4.53 มาก จึงไม่น่าเป็นไปได้ที่ค่า F สูงนี้เกิดขึ้นโดยบังเอิญ (ด้วย Alpha = 0.05 สมมติฐานว่าไม่มีความสัมพันธ์ระหว่าง known_y และ known_x จะถูกปฏิเสธเมื่อ F สูงกว่าระดับวิกฤตที่ 4.53) คุณสามารถใช้ฟังก์ชัน FDIST ใน Excel เพื่อหาความน่าจะเป็นที่ค่า F ซึ่งเกิดขึ้นโดยบังเกิล ตัวอย่างเช่น FDIST(459.753674, 4, 6) = 1.37E-7 ซึ่งเป็นความน่าจะเป็นที่เล็กมาก คุณสามารถสรุปได้ไม่ว่าจะโดยการค้นหาระดับวิกฤตของ F ในตารางหรือโดยใช้ฟังก์ชัน FDIST ว่าสมการการถดถอยมีประโยชน์ในการทํานายค่าประเมินของอาคารสํานักงานในพื้นที่นี้ โปรดจําไว้ว่าการใช้ค่าที่ถูกต้องของ v1 และ v2 ที่คํานวณในย่อหน้าก่อนหน้านั้นเป็นสิ่งสําคัญ
ตัวอย่าง 5 - การคำนวณสถิติ t
การทดสอบสมมติฐานอีกอย่างหนึ่งจะเป็นตัวกําหนดว่าสัมประสิทธิ์ความชันแต่ละค่ามีประโยชน์ในการประมาณค่าประเมินของอาคารสํานักงานใน ตัวอย่างที่ 3 หรือไม่ ตัวอย่างเช่น เมื่อต้องการทดสอบสัมประสิทธิ์อายุของนัยสําคัญทางสถิติ ให้หาร -234.24 (สัมประสิทธิ์ความชันของอายุ) ด้วย 13.268 (ข้อผิดพลาดมาตรฐานโดยประมาณของสัมประสิทธิ์อายุในเซลล์ A15) ต่อไปนี้คือค่า t ที่สังเกตได้:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
ถ้าค่าสัมบูรณ์ของ t สูงพอก็สามารถสรุปได้ว่าค่าสัมประสิทธิ์ความชันมีประโยชน์ในการประมาณค่าประเมินของอาคารสํานักงานในตัวอย่างที่ 3 ตารางต่อไปนี้แสดงค่าสัมบูรณ์ของค่า t ที่สังเกตได้ 4 ค่า
ถ้าคุณศึกษาตารางในคู่มือสถิติ คุณจะพบว่า t-critical, two tailed, with 6 degrees of freedom and Alpha = 0.05 is 2.447. ค่าวิกฤตนี้ยังสามารถพบได้โดยใช้ฟังก์ชัน TINV ใน Excel TINV(0.05,6) = 2.447 เนื่องจากค่าสัมบูรณ์ของ t (17.7) มากกว่า 2.447 อายุจึงเป็นตัวแปรสําคัญเมื่อประเมินค่าประเมินของอาคารสํานักงาน ตัวแปรอิสระอื่นๆ แต่ละตัวสามารถทดสอบสําหรับนัยสําคัญทางสถิติในลักษณะที่คล้ายกัน ต่อไปนี้เป็นค่า t ที่สังเกตได้สําหรับตัวแปรอิสระแต่ละตัว
|
ตัวแปร |
ค่า t ที่ได้จากการสังเกต |
|---|---|
|
พื้นที่ของชั้น |
5.1 |
|
จำนวนสำนักงาน |
31.3 |
|
จำนวนทางเข้า |
4.8 |
|
อายุ |
17.7 |
ค่าเหล่านี้ล้วนมีค่าสัมบูรณ์ที่มากกว่า 2.447 ดังนั้น ตัวแปรทั้งหมดที่ใช้ในสมการการถดถอยจึงเป็นประโยชน์ในการประมาณค่าประเมินของอาคารสำนักงานในพื้นที่นี้
