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

คำอธิบาย

ฟังก์ชัน 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; r^2,sey; F,df; ssreg, ssresid}

  • ถ้า stats เป็น FALSE หรือถ้าไม่ใส่ค่าใดไว้ LINEST จะส่งกลับเฉพาะค่าสัมประสิทธิ์ m และค่าคงที่ b เท่านั้น

    สถิติการถดถอยเพิ่มเติมมีดังนี้

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

ข้อสังเกต

• คุณสามารถอธิบายเส้นตรงด้วยความชัน และจุดตัดบนแกน 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 ได้โดยตรงด้วยการใช้สูตรต่อไปนี้

  Slope:
  =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 ยิ่งผลรวมของกําลังสองที่เหลือมีขนาดเล็กลงเท่าใด เมื่อเปรียบเทียบกับผลรวมของกําลังสองทั้งหมด ยิ่งค่าของสัมประสิทธิ์ของการกําหนดมากน้อยเพียงใด r² ซึ่งเป็นตัวบ่งชี้ว่าสมการเป็นผลมาจากการวิเคราะห์การถดถอยมากเพียงใด จะอธิบายความสัมพันธ์ระหว่างตัวแปรต่างๆ ค่าของ r² เท่ากับ 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 จะส่งกลับค่าความน่าจะเป็น

ตัวอย่าง