LINEST (ฟังก์ชัน LINEST)

บทความนี้จะอธิบายถึงไวยากรณ์ของสูตรและการใช้ฟังก์ชัน 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's    (ต้องระบุ) คือชุดของค่า y ที่คุณทราบค่าอยู่แล้วในความสัมพันธ์ y = mx + b

    • ถ้าช่วงของ known_y อยู่ในคอลัมน์เดียว แต่ละคอลัมน์ของ known_x's จะถูกแปลงเป็นตัวแปรแยกต่างหาก

    • ถ้าช่วงของ known_y อยู่ในแถวเดียว แต่ละแถวของ known_x จะถูกแปลงเป็นตัวแปรแยกต่างหาก

  • known_x's    (ระบุหรือไม่ก็ได้) คือชุดที่เลือกได้ของค่า x ที่คุณอาจทราบค่าอยู่แล้วในความสัมพันธ์ y = mx + b

    • ช่วงของ known_x's อาจประกอบด้วยชุดของตัวแปรหนึ่งชุดหรือมากกว่า ถ้ามีการใช้เพียงหนึ่งตัวแปร known_y's และ known_x's อาจจะเป็นช่วงที่มีรูปร่างแบบใดก็ได้ ตราบใดที่ยังมีขนาดของอาร์เรย์เท่ากันอยู่ ถ้ามีการใช้ตัวแปรมากกว่าหนึ่งตัวแปร known_y's ต้องเป็นแบบเวกเตอร์ (ซึ่งหมายความว่า ต้องเป็นช่วงที่มีความสูงหนึ่งแถวหรือความกว้างหนึ่งคอลัมน์)

    • ถ้าไม่ได้ใส่ค่าอาร์เรย์ known_x's อาร์เรย์จะถูกกำหนดเป็นอาร์เรย์ {1,2,3,...} ที่มีขนาดเท่ากับอาร์เรย์ known_y's

  • const    (ระบุหรือไม่ก็ได้) คือค่าตรรกะที่ระบุว่าให้บังคับให้ค่าคงที่ b เท่ากับ 0 หรือไม่

    • ถ้า const เป็น TRUE หรือละไว้ b จะถูกคำนวณตามวิธีปกติ

    • ถ้า const เป็น FALSE จะตั้งค่า b ให้เท่ากับ 0 และปรับค่า m ให้เหมาะกับสมการ y = mx

  • stats    (ระบุหรือไม่ก็ได้) คือค่าตรรกะที่ใช้ระบุว่าจะส่งกลับค่าสถิติการถดถอยเพิ่มเติมหรือไม่

    • ถ้า 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 จากการประมาณกับค่าจริง และช่วงของค่าจาก 0 ถึง 1 ถ้ามีค่าเท่ากับ 1 แสดงว่ามีสัมประสิทธิ์สมบูรณ์ในตัวอย่าง กล่าวคือ ไม่มีความแตกต่างระหว่างค่า y จากการประมาณกับค่า y จริง ในทางตรงข้าม ถ้าสัมประสิทธิ์ของการกำหนดเท่ากับ 0 สมการการถดถอยจะไม่สามารถนำมาช่วยทำนายค่า y สำหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการคำนวณ r2 ให้ดูที่ "ข้อสังเกต" ที่ส่วนท้ายของหัวข้อนี้

sey

ค่าความผิดพลาดมาตรฐานของค่าประมาณ y

F

สถิติ F หรือค่า F ที่ได้จากการสังเกต ให้ใช้สถิติ F ในการกำหนดการเกิดของความสัมพันธ์ระหว่างตัวแปรอิสระกับตัวแปรตามที่ได้จากการสังเกต

df

องศาความเป็นอิสระ ใช้องศาความเป็นอิสระเพื่อช่วยคุณในการหาค่าวิกฤต F ในตารางทางสถิติ เปรียบเทียบค่าที่คุณพบในตารางกับค่าสถิติ F ที่ฟังก์ชัน LINEST ส่งกลับมา เพื่อกำหนดระดับความเชื่อมั่นของแบบจำลอง สำหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการคำนวณ df ให้ดูที่ "ข้อสังเกต" ที่ส่วนท้ายของหัวข้อนี้ ตัวอย่างที่ 4 แสดงการใช้ค่า F และ df

ssreg

ผลรวมกำลังสองที่ถดถอย

ssresid

ผลรวมกำลังสองที่ตกค้าง สำหรับข้อมูลเกี่ยวกับวิธีการคำนวณ ssreg และ ssresid ให้ดูที่ "ข้อสังเกต" ที่ส่วนท้ายของหัวข้อนี้

ภาพประกอบตัวอย่างต่อไปนี้แสดงลำดับที่ส่งกลับค่าสถิติการถดถอยเพิ่มเติม

แผ่นงาน

ข้อสังเกต

  • คุณสามารถอธิบายเส้นตรงด้วยความชัน และจุดตัดบนแกน y

    ความชัน (m):
    เมื่อต้องการหาความชันของเส้นตรง ซึ่งโดยมากจะแทนด้วย 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 จะยิ่งมากขึ้นเท่านั้น 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's, known_x's) ของเส้นโค้งเอ็กซ์โพเนนเชียลได้โดยไม่ต้องใช้ new_x's ฟังก์ชันเหล่านี้จะส่งกลับอาร์เรย์ของค่า y ที่ทำนายได้จากเส้นตรงหรือเส้นโค้ง ณ จุดของข้อมูลจริงของคุณ จากนั้นคุณสามารถเปรียบเทียบค่าที่ทำนายกับค่าจริงได้ คุณอาจนำทั้งสองข้อมูลมาทำเป็นแผนภูมิเพื่อใช้ดูเปรียบเทียบด้วยตา

  • ในการวิเคราะห์การถดถอย แต่ละจุด Excel จะคำนวณผลต่างยกกำลังสองระหว่างค่า y ที่ประมาณไว้ และค่า y จริงที่จุดนั้น ผลรวมของผลต่างยกกำลังสองเหล่านี้เรียกว่า ผลรวมกำลังสองที่ตกค้าง (Residual Sum of Squares) หรือ ssresid จากนั้น Excel จะคำนวณผลรวมกำลังสองทั้งหมด หรือ sstotal เมื่ออาร์กิวเมนต์ const = TRUE หรือถูกละไว้ ผลรวมกำลังสองทั้งหมดจะได้แก่ผลรวมระหว่างผลต่างกำลังสองของค่า y ที่ได้มาจริงและค่าเฉลี่ยของค่า y เมื่ออาร์กิวเมนต์ const = FALSE ผลรวมกำลังสองทั้งหมดจะเป็นผลรวมของค่ากำลังสองของค่า y ที่ได้มาจริง (โดยไม่ได้ลบค่า y เฉลี่ยออกจากค่า y แต่ละค่า) จากนั้นสามารถหาผลรวมกำลังสองถดถอย หรือ ssreg ได้จาก ssreg = sstotal - ssresid เมื่อผลรวมกำลังสองที่ตกค้างน้อยลงเมื่อเปรียบเทียบกับผลรวมของค่ากำลังสอง ค่าสัมประสิทธิ์ของการกำหนดหรือ r2 จะมากขึ้น ซึ่งเป็นตัวชี้ว่าสมการที่มาจากการวิเคราะห์การถดถอยสามารถอธิบายความสัมพันธ์ระหว่างตัวแปรได้ดีเพียงใด โดยค่าของ r2 เท่ากับ ssreg/sstotal

  • ในบางกรณี คอลัมน์ X หนึ่งหรือหลายคอลัมน์ (สมมุติว่าค่า Y และ X อยู่ในคอลัมน์) อาจไม่ได้ช่วยให้สามารถทำนายได้แม่นยำขึ้นเมื่อมีคอลัมน์ X อื่นๆ ปรากฏอยู่แล้ว หรือกล่าวอีกนัยหนึ่ง การกำจัดคอลัมน์ X หนึ่งหรือหลายคอลัมน์อาจนำไปสู่ค่า Y จากการทำนายที่มีความถูกต้องเท่าเดิม ในกรณีดังกล่าวคอลัมน์ X ที่ ซ้ำซ้อนนี้ควรตัดออกจากแบบจำลองการถดถอย ปรากฏการณ์นี้เรียกว่า “ภาวะร่วมเส้นตรง” เนื่องจากคอลัมน์ X ซ้ำซ้อนใดๆ สามารถแสดงในรูปผลรวมของพหุคูณของคอลัมน์ X ที่ไม่ซ้ำซ้อนได้ ฟังก์ชัน LINEST จะตรวจหาภาวะร่วมเส้นตรงและเอาคอลัมน์ X ใดๆ ที่ซ้ำซ้อนออกจากแบบจำลองการถดถอยเมื่อพบคอลัมน์เหล่านั้น คอลัมน์ X ที่ถูกเอาออกสามารถดูได้จากผลลัพธ์ของ LINEST โดยจะมีค่าสัมประสิทธิ์เป็น 0 รวมทั้งค่า se ที่เป็น 0 ถ้ามีการเอาคอลัมน์หนึ่งหรือหลายคอลัมน์ออกเนื่องจากเป็นคอลัมน์ที่ซ้ำซ้อน ค่า 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’s และ const = TRUE หรือละไว้ ค่า df = n – k – 1 ถ้า const = FALSE ค่า df = n - k ในทั้งสองกรณี คอลัมน์ X แต่ละคอลัมน์ที่ถูกลบออกไปเนื่องจากภาวะร่วมเส้นตรงจะเพิ่มค่าของ df เท่ากับ 1

  • สูตรที่ส่งกลับค่าอาร์เรย์ต้องถูกใส่เป็นสูตรอาร์เรย์

    หมายเหตุ: ใน Excel Online คุณไม่สามารถสร้างสูตรอาร์เรย์ได้

  • เมื่อใส่ค่าคงที่อาร์เรย์ (เช่น known_x's) ให้เป็นอาร์กิวเมนต์ ให้ใช้เครื่องหมายจุลภาคคั่นระหว่างค่าแต่ละค่าที่อยู่ในแถวเดียวกัน และใช้เครื่องหมายอัฒภาคเพื่อคั่นระหว่างแถวแต่ละแถว อักขระตัวคั่นอาจมีความแตกต่างกันไปตามการตั้งค่าภูมิภาคของคุณ

  • จงจำไว้ว่าค่า y จากการทำนายโดยสมการการถดถอยอาจไม่ถูกต้องถ้าอยู่นอกช่วงของค่า y ที่คุณใช้กำหนดสมการ

  • อัลกอริทึมที่สำคัญในฟังก์ชัน LINEST นั้นแตกต่างจากอัลกอริทึมสำคัญที่ใช้ในฟังก์ชัน SLOPE และ INTERCEPT ความแตกต่างระหว่างอัลกอริทึมเหล่านี้อาจนำไปสู่ผลลัพธ์ที่แตกต่างกันเมื่อไม่ได้ระบุข้อมูลและข้อมูลร่วมเส้นตรง ตัวอย่างเช่น ถ้าจุดข้อมูลของอาร์กิวเมนต์ known_y's เท่ากับ 0 และจุดข้อมูลของอาร์กิวเมนต์ known_x's เท่ากับ 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

12

$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

2,471

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

สูตร (สูตรอาร์เรย์ที่ระบุใน A14:A18)

=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 and 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’s และ known_x’s จะถูกปฏิเสธเมื่อ 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 สองด้านที่มีองศาความเป็นอิสระเท่ากับ 6 และ alpha = 0.05 จะมีค่าเท่ากับ 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 ดังนั้น ตัวแปรทั้งหมดที่ใช้ในสมการการถดถอยจึงเป็นประโยชน์ในการประมาณค่าประเมินของอาคารสำนักงานในพื้นที่นี้

ขยายทักษะของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×