LINEST (Fungsi LINEST)

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Artikel ini memerihalkan sintaks formula dan penggunaan fungsi LINEST dalam Microsoft Excel. Cari pautan untuk maklumat lanjut tentang Carta dan menjalankan analisis regresi dalam seksyen Lihat juga .

Perihalan

Fungsi LINEST mengira statistik bagi garis dengan menggunakan kaedah "kuasa dua terkecil" untuk mengira satu garis lurus yang paling menepati dengan data anda kemudian mengembalikan tatasusunan yang menghuraikan garisan. Anda juga boleh menggabungkan LINEST bersama fungsi lain untuk mengira statistik bagi jenis model linear lain dalam parameter yang tidak diketahui, termasuk polinomial, logaritma, eksponen dan siri kuasa. Oleh sebab fungsi ini mengembalikan tatasusunan nilai, ia mesti dimasukkan sebagai formula tatasusunan. Arahan mengikut contoh dalam artikel ini.

Persamaan untuk garis ini ialah:

y = mx + b

–atau–

y = m1x1 + m2x2 + ... + b

jika terdapat julat berbilang nilai-x yang mana nilai-y sandaran adalah fungsi nilai-x bebas. Nilai-m adalah pekali sepadan kepada setiap nilai-x dan b adalah nilai pemalar. Harap maklum bahawa y, x dan m boleh merupakan vektor. Tatasusunan yang fungsi LINEST kembalikan ialah {mn,mn-1,...,m1,b}. LINEST juga boleh mengembalikan statistik regresi tambahan.

Sintaks

LINEST(known_y's, [known_x's], [const], [stats])

Sintaks fungsi LINEST mempunyai argumen berikut:

Sintaks

  • known_y's    Diperlukan. Set nilai-y yang telah anda ketahui dalam perhubungan y = mx + b.

    • Jika julat known_y's adalah dalam lajur tunggal, setiap lajur known_x's ditafsirkan sebagai pemboleh ubah berasingan.

    • Jika julat known_y's mengandungi baris tunggal, setiap baris known_x's ditafsirkan sebagai pemboleh ubah berasingan.

  • known_x's    Pilihan. Set nilai-x yang telah anda ketahui dalam perhubungan y = mx + b.

    • Julat known_x's boleh termasuk satu atau lebih set pemboleh ubah. Jika hanya satu pemboleh ubah digunakan, known_y's dan known_x's boleh menjadi julat sebarang bentuk, selagi ia mempunyai dimensi yang sama. Jika lebih daripada satu pemboleh ubah digunakan, known_y's mestilah vektor (iaitu, julat dengan ketinggian satu baris atau kelebaran satu lajur).

    • Jika known_x's dikeluarkan, ia dianggapkan sebagai tatasusunan {1,2,3,...} yang mempunyai saiz yang sama dengan known_y's.

  • const    Pilihan. Nilai logik yang menentukan sama ada ingin memaksa pemalar b menjadi sama dengan 0.

    • Jika const ialah TRUE atau dikeluarkan, b dikira secara normal.

    • Jika const adalah FALSE, b adalah disetkan sama dengan 0 dan nilai-m dilaraskan agar sesuai dengan y = mx.

  • stats    Pilihan. Nilai logik yang menentukan sama ada untuk mengembalikan statistik regresi tambahan.

    • Jika stats adalah TRUE, LINEST mengembalikan statistik regresi tambahan; hasilnya, tatasusunan yang dikembalikan ialah {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Jika stats adalah FALSE atau dikeluarkan, LINEST mengembalikan hanya pekali-m dan pemalar b.

      Statistik regresi tambahan adalah seperti berikut.

Statistik

Perihalan

se1,se2,...,sen

Nilai ralat standard untuk pekali m1,m2,...,mn.

seb

Nilai ralat standard untuk pemalar b (seb = #N/A apabila const adalah FALSE).

r2

Pekali penentuan. Membandingkan nilai-y anggaran dan sebenar, serta julat dalam nilai dari 0 hingga 1. Jika ia adalah 1, terdapat korelasi sempurna dalam sampel — tidak terdapat perbezaan antara nilai-y anggaran dan nilai-y sebenar. Pada sudut yang lain, jika pekali penentuan adalah 0, persamaan regresi tidak membantu dalam meramalkan nilai-y. Untuk maklumat tentang cara r2 dikira, lihat "Catatan", di akhir topik ini.

sey

Ralat standard anggaran y.

URL

Statistik F atau nilai cerapan F. Gunakan statistik F untuk menentukan sama ada perhubungan cerapan antara pemboleh ubah sandaran dan bebas berlaku secara kebetulan.

df

Darjah kebebasan. Gunakan darjah kebebasan untuk membantu anda mencari nilai kritikal-F dalam jadual statistik. Bandingkan nilai yang anda dapati dalam jadual dengan statistik F yang dikembalikan oleh LINEST untuk menentukan tahap keyakinan bagi model. Untuk maklumat tentang cara df dikira, lihat "Catatan", di akhir topik ini. Contoh 4 menunjukkan penggunaan F dan df.

ssreg

Hasil tambah regresi kuasa dua.

ssresid

Sisa hasil tambah kuasa dua. Untuk maklumat tentang cara ssreg dan ssresid dikira, lihat "Catatan", di akhir topik ini.

Ilustrasi berikut menunjukkan susunan yang mana statistik regresi tambahan dikembalikan.

Kunci statistik regresi

Catatan

  • Anda boleh menghuraikan sebarang garis lurus dengan cerun dan pintasan-y:

    Slope (m):
    Untuk mencari kecerunan garisan, biasanya ditulis sebagai m, mengambil dua titik pada baris (x 1, y1) dan (x2, y2); kecerunan adalah sama dengan (y2 - y1) / (x 2 - x 1).

    Pintasan-Y (b):
    Pintasan-y garisan, biasanya ditulis sebagai b, adalah nilai y pada titik garisan yang melintasi paksi-y.

    Persamaan garis lurus ialah y = mx + b. Sebaik sahaja anda mengetahui nilai m dan b, anda boleh mengira sebarang titik pada garisan dengan meletakkan nilai-y atau -x ke dalam persamaan. Anda juga boleh menggunakan fungsi TREND.

  • Apabila anda hanya mempunyai satu pemboleh ubah-x bebas, anda boleh terus memperoleh nilai cerun dan pintasan-y dengan menggunakan formula berikut:

    Cerun:
    =INDEX(LINEST(known_y's,known_x's),1)

    Pintasan-Y:
    =INDEX(LINEST(known_y's,known_x's),2)

  • Ketepatan garis dikira oleh fungsi LINEST bergantung pada tahap serakan dalam data anda. Lebih linear data, lebih tepat model LINEST. LINEST menggunakan kaedah kuasa dua terkecil untuk menentukan kesesuaian terbaik bagi data. Apabila anda hanya mempunyai satu pemboleh ubah-x bebas, pengiraan untuk m dan b didasarkan pada formula berikut:

    Persamaan

    Persamaan

    yang mana sampel x dan y bermaksud; iaitu, x = AVERAGE(known x's) dan y = AVERAGE(known_y's).

  • Fungsi muatkan-garis dan -lengkok LINEST dan LOGEST boleh mengira garis lurus terbaik atau lengkok eksponen yang menepati data anda. Walau bagaimanapun, anda perlu memutuskan salah satu daripada dua hasil terbaik yang menepati data anda. Anda boleh mengira TREND(known_y's,known_x's) untuk garis lurus atau GROWTH(known_y's, known_x's) untuk lengkok eksponen. Fungsi-fungsi ini, tanpa argumen new_x's, mengembalikan tatasusunan nilai-y diramal sepanjang garis atau lengkok tersebut di titik data sebenar anda. Anda kemudian boleh membandingkan nilai diramal dengan nilai sebenar. Anda mungkin ingin mencartakan kedua-duanya bagi perbandingan visual.

  • Dalam analisis regresi, Excel mengira setiap titik perbezaan kuasa dua antara nilai-y dianggar bagi titik tersebut dan nilai-y yang sebenar. Hasil tambah perbezaan kuasa dua ini dipanggil sisa hasil tambah kuasa dua, ssresid. Excel kemudiannya mengira jumlah hasil tambah kuasa dua, sstotal. Apabila argumen const = TRUE atau dikeluarkan, jumlah hasil tambah kuasa dua ialah perbezaan kuasa dua antara nilai-y sebenar dan purata nilai-y. Apabila argumen const = FALSE, jumlah hasil tambah kuasa dua ialah hasil tambah kuasa dua nilai-y sebenar (tanpa menolak purata nilai-y daripada setiap nilai-y individu). Kemudian regresi hasil tambah kuasa dua, ssreg, boleh didapati daripada: ssreg = sstotal - ssresid. Semakin kecil sisa hasil tambah kuasa dua, dibandingkan dengan jumlah hasil tambah kuasa dua, semakin besar nilai pekali penentuan, r2 yang merupakan penunjuk bagaimana persamaan yang terhasil daripada analisis regresi yang menerangkan hubungan antara pemboleh ubah. Nilai r2 bersamaan ssreg/sstotal.

  • Dalam sesetengah kes, satu atau lebih lajur X (andaikan bahawa Y dan X dalam lajur) mungkin tidak mempunyai nilai ramalan tambahan dengan kehadiran lajur X yang lain. Dengan kata lain, menyingkirkan satu atau lebih lajur X mungkin membawa kepada nilai Y diramalkan sama tepat. Dalam kes tersebut, lajur X bertindih ini perlu dikeluarkan daripada model regresi. Fenomena ini digelar “kekolinearan” kerana sebarang lajur X bertindih boleh diungkapkan sebagai hasil tambah gandaan lajur X bukan bertindih. Fungsi LINEST menyemak kekolinearan dan membuang sebarang lajur X bertindih daripada model regresi apabila ia mengenal pasti mereka. Lajur X yang dibuang boleh dikenali dalam output LINEST sebagai mempunyai pekali 0 sebagai tambahan kepada set nilai 0. Jika satu atau lebih lajur dibuang kerana bertindih, df terjejas kerana df bergantung kepada bilangan lajur X yang sebenar digunakan untuk tujuan ramalan. Untuk perincian pengiraan df, lihat Contoh 4. Jika df diubah kerana lajur X bertindih dibuangkan,nilai sey dan F juga terjejas. Kekolinearan agak jarang dalam amalan. Walau bagaimanapun, satu kes yang mana mungkin berbangkit ialah apabila beberapa lajur X mengandungi hanya nilai 0 dan 1 sebagai penunjuk sama ada subjek dalam uji kaji adalah ahli atau bukan kepada kumpulan tertentu. Jika const = TRUE atau dikeluarkan, fungsi LINEST secara berkesan memasukkan lajur X tambahan bagi semua nilai 1 untuk modelkan pintasan. Jika anda mempunyai lajur dengan 1 untuk setiap subjek jika lelaki atau 0 jika tidak dan anda juga mempunyai lajur dengan 1 untuk setiap subjek jika perempuan atau 0 jika tidak, lajur terakhir ini bertindih kerana entri di dalamnya boleh diperoleh dengan menolak entri dalam lajur “penunjuk lelaki” daripada entri dalam lajur tambahan bagi semua nilai 1 yang ditambah oleh fungsi LINEST.

  • Nilai df dikira seperti berikut, apabila tiada lajur X dibuang daripada model kerana kekolinearan: jika terdapat lajur k bagi known_x’s dan const = TRUE atau dikeluarkan, df = n – k – 1. Jika const = FALSE, df = n - k. Dalam kedua-dua kes, setiap lajur X yang dibuang kerana kekolinearan meningkatkan nilai df sebanyak 1.

  • Formula yang mengembalikan tatasusunan mesti dimasukkan sebagai formula tatasusunan.

    Nota: Dalam Excel Online, anda tidak boleh mencipta formula tatasusunan.

  • Apabila memasukkan tatasusunan malar (seperti known_x's) sebagai argumen, gunakan koma untuk nilai berasingan yang terkandung dalam baris yang sama dan koma bernoktah untuk baris berasingan. Aksara pemisah mungkin berbeza bergantung kepada seting rantau anda.

  • Ambil perhatian bahawa nilai-y diramalkan oleh persamaan regresi mungkin tidak sah jika ia di luar julat nilai-y yang anda gunakan untuk menentukan persamaan.

  • Algoritma asas yang digunakan dalam fungsi LINEST adalah berbeza daripada algoritma asas yang digunakan dalam fungsi SLOPE dan INTERCEPT. Perbezaan di antara algoritma ini boleh mengembalikan hasil berbeza apabila data tidak ditentukan dan kolinear. Contohnya, jika titik data argumen known_y's adalah 0 dan titik data argumen known_x's adalah 1:

    • LINEST mengembalikan nilai 0. Algoritma fungsi LINEST direka bentuk untuk mengembalikan keputusan munasabah untuk data kolinear dan dalam kes ini, sekurang-kurangnya satu jawapan boleh diperoleh.

    • SLOPE dan INTERCEPT mengembalikan #DIV/0! . Algoritma fungsi SLOPE dan INTERCEPT direka bentuk untuk mencari hanya satu jawapan dan dalam kes ini, boleh terdapat lebih daripada satu jawapan.

  • Sebagai tambahan menggunakan LOGEST untuk mengira statistik bagi jenis regresi yang lain, anda boleh gunakan LINEST untuk mengira julat bagi jenis regresi yang lain dengan memasukkan fungsi pemboleh ubah x dan y sebagai siri x dan y untuk LINEST. Contohnya, formula berikut:

    =LINEST(yvalues, xvalues^COLUMN($A:$C))

    berfungsi apabila anda mempunyai lajur tunggal nilai-y dan lajur tunggal nilai-x untuk mengira kubus (polinomial 3 susunan) penghampiran bentuk:

    y = m1*x + m2*x^2 + m3*x^3 + b

    Anda boleh melaraskan formula ini untuk mengira regresi jenis lain tetapi dalam sesetengah kes ia memerlukan pelarasan nilai output dan statistik lain.

  • Nilai ujian F yang dikembalikan oleh fungsi LINEST berbeza dengan nilai ujian F yang dikembalikan oleh fungsi FTEST. LINEST mengembalikan statistik F, manakala FTEST mengembalikan kebarangkalian.

Contoh

Contoh 1 - Cerun dan Pintasan-Y

Salin data contoh dalam jadual berikut dan tampalkannya dalam sel A1 lembaran kerja Excel baru. Untuk memastikan formula menunjukkan hasil, pilihnya, tekan F2 kemudian tekan Enter. Jika perlu, anda boleh melaraskan lebar lajur untuk melihat semua data.

Known y

Known x

1

0

9

4

5

2

7

3

Hasil (kecerunan)

Hasil (pintasan y)

2

1

Formula (formula tatasusunan dalam sel A7:B7)

=LINEST(A2:A5,B2:B5,,FALSE)

Contoh 2 - Regresi Linear Mudah

Salin data contoh dalam jadual berikut dan tampalkannya dalam sel A1 lembaran kerja Excel baru. Untuk memastikan formula menunjukkan hasil, pilihnya, tekan F2 kemudian tekan Enter. Jika perlu, anda boleh melaraskan lebar lajur untuk melihat semua data.

Bulan

Jualan

1

$3,100

2

$4,500

3

$4,400

4

$5,400

5

$7,500

6

$8,100

Formula

Hasil

=SUM(LINEST(B1:B6, A1:A6)*{9,1})

$11,000

Mengira anggaran jualan dalam bulan kesembilan, berdasarkan jualan dalam bulan 1 hingga 6.

Contoh 3 - Regresi Linear Berganda

Salin data contoh dalam jadual berikut dan tampalkannya dalam sel A1 lembaran kerja Excel baru. Untuk memastikan formula menunjukkan hasil, pilihnya, tekan F2 kemudian tekan Enter. Jika perlu, anda boleh melaraskan lebar lajur untuk melihat semua data.

Ruang lantai (x1)

Pejabat (x2)

Pintu masuk (x3)

Usia (x4)

Nilai tertaksir (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

2471

2

2

34

$142,900

2494

3

3

23

$163,000

2517

4

4

55

$169,000

tahun 2540

2

3

22

$149,000

-234.2371645

13.26801148

0.996747993

459.7536742

1732393319

Formula (formula tatasusunan yang dimasukkan dalam A14:A18)

=LINEST(E2:E12,A2:D12,TRUE,TRUE)

Contoh 4 - Menggunakan Statistik F dan r2

Dalam contoh terdahulu, pekali penentuan atau r2, ialah 0.99675 (lihat sel A17 dalam output LINEST), yang menunjukkan perhubungan kukuh antara pemboleh ubah bebas dan harga jualan. Anda boleh gunakan statistik F untuk menentukan sama ada keputusan ini dengan nilai r2 yang tinggi, berlaku secara kebetulan.

Andaikan buat masa ini sebenarnya tiada hubungan antara pemboleh ubah tetapi seperti sampel yang anda lakarkan yang jarang berlaku, 11 bangunan pejabat yang menyebabkan analisis statistik bagi menunjukkan hubungan yang kukuh. Istilah "Alpha" digunakan untuk kebarangkalian tersilap menyimpulkan bahawa terdapatnya perhubungan.

Nilai F dan df dalam output daripada fungsi LINEST boleh digunakan untuk menilai kecenderungan nilai F yang lebih tinggi yang berlaku secara kebetulan. F boleh dibandingkan dengan nilai kritikal dalam jadual F-agihan yang diterbitkan atau fungsi FDIST dalam Excel boleh digunakan untuk mengira kebarangkalian nilai F yang lebih besar yang berlaku secara kebetulan. Agihan F yang sesuai mempunyai darjah kebebasan v1 dan v2. Jika n adalah nombor titik data dan const = TRUE atau dikeluarkan, maka v1 = n – df – 1 dan v2 = df. (Jika const = FALSE, maka v1 = n – df dan v2 = df). Fungsi FDIST — dengan sintaks FDIST(F,v1,v2) — akan mengembalikan kebarangkalian nilai F yang lebih tinggi yang berlaku secara kebetulan. Dalam contoh ini, df = 6 (sel B18) dan F = 459.753674 (sel A18).

Andaikan nilai Alpha 0.05, v1 = 11 – 6 – 1 = 4 dan v2 = 6, tahap kritikal F ialah 4.53. Kerana F = 459.753674 adalah jauh lebih tinggi daripada 4.53, ia sangat tidak berkemungkinan bahawa nilai F yang tinggi ini berlaku secara kebetulan. (Dengan Alpha = 0.05, hipotesis bahawa tidak terdapat hubungan antara known_y’s dan known_x’s ditolak apabila F melebihi tahap kritikal, 4.53). Anda boleh gunakan fungsi FDIST dalam Excel untuk memperoleh kebarangkalian bahawa nilai F yang tinggi ini berlaku secara kebetulan. Contohnya, FDIST(459.753674, 4, 6) = 1.37E-7, kebarangkalian yang sangat kecil. Anda boleh membuat kesimpulan, sama ada dengan mencari tahap kritikal F dalam jadual atau menggunakan fungsi FDIST, supaya persamaan regresi amat berguna dalam meramalkan nilai tertaksir bangunan pejabat di kawasan ini. Ingat bahawa ia adalah kritikal untuk menggunakan nilai yang betul bagi v1 dan v2 yang dikira dalam perenggan terdahulu.

Contoh 5 - Pengiraan Statistik-t

Satu lagi ujian hipotesis akan menentukan sama ada setiap pekali cerun berguna dalam menganggarkan nilai tertaksir bangunan pejabat dalam Contoh 3. Contohnya untuk menguji pekali usia untuk kepentingan statistik, bahagikan -234.24 (usia pekali cerun) dengan 13.268 (ralat standard anggaran pekali usia dalam sel A15). Berikut adalah nilai cerapan-t:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Jika nilai mutlak t adalah cukup tinggi, ia boleh disimpulkan bahawa pekali cerun berguna dalam menganggarkan nilai tertaksir bangunan pejabat dalam Contoh 3. Jadual berikut menunjukkan nilai mutlak bagi 4 cerapan-t.

Jika anda merujuk jadual manual statistik, anda akan mendapati bahawa kritikal-t, dua hujung dengan 6 darjah kebebasan dan Alpha = 0.05 adalah 2.447. Nilai kritikal ini juga boleh didapati dengan menggunakan fungsi TINV dalam Excel. TINV(0.05,6) = 2.447. Disebabkan nilai mutlak t (17.7) adalah lebih besar daripada 2.447, usia merupakan pemboleh ubah penting apabila menganggarkan nilai tertaksir bangunan pejabat. Setiap pemboleh ubah bebas yang lain boleh diuji untuk kepentingan statistik dalam cara yang serupa. Berikut adalah nilai cerapan-t bagi setiap pemboleh ubah bebas.

Pemboleh ubah

nilai cerapan-t

Ruang lantai

5.1

Bilangan pejabat

31.3

Bilangan pintu masuk

4.8

Umur

17.7

Nilai-nilai ini kesemuanya mempunyai nilai mutlak yang lebih besar daripada 2.447; ​​oleh itu, semua pemboleh ubah yang digunakan dalam persamaan regresi adalah berguna dalam meramalkan nilai tertaksir bangunan pejabat di kawasan ini.

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×