Fungsi linest excel dengan contoh formula

Tutorial ini menerangkan sintaks fungsi linest dan menunjukkan cara menggunakannya untuk melakukan analisis regresi linear dalam Excel.

Microsoft Excel bukan program statistik, namun, ia mempunyai beberapa fungsi statistik. Salah satu fungsi sedemikian adalah linest, yang direka untuk melakukan analisis regresi linear dan statistik berkaitan pulangan. Dalam tutorial ini untuk pemula, kami akan menyentuh hanya ringan pada teori dan pengiraan asas. Tumpuan utama kami adalah untuk menyediakan anda dengan formula yang hanya berfungsi dan boleh disesuaikan dengan mudah untuk data anda.

• Fungsi Excel Linest - Sintaks dan Penggunaan Asas
• Cara Menggunakan Linest In Excel - Contoh Formula
  • Regresi Linear Mudah: cerun dan memintas
  • Regresi berganda: cerun dan memintas
  • Regresi Linear Mudah: Ramalkan pemboleh ubah bergantung
  • Regresi Pelbagai: Ramalkan pemboleh ubah bergantung
  • Formula Linest: Statistik Regresi Tambahan
• 5 perkara yang harus anda ketahui mengenai fungsi linest
• Excel linest tidak berfungsi

Fungsi Excel Linest - Sintaks dan Penggunaan Asas

Fungsi linest mengira statistik untuk garis lurus yang menerangkan hubungan antara pembolehubah bebas dan satu atau lebih pembolehubah bergantung, dan mengembalikan array yang menerangkan garis. Fungsi ini menggunakan kaedah sekurang -kurangnya untuk mencari yang terbaik untuk data anda. Persamaan untuk garis adalah seperti berikut.

Persamaan Regresi Linear Mudah:

y = bx a

Persamaan regresi berganda:

y = b ₁ x ₁ b ₂ x ₂ ... b _n x _n a

Di mana:

• Y - Pemboleh ubah bergantung yang anda cuba meramalkan.
• x - Pembolehubah bebas yang anda gunakan untuk meramalkan y .
• A - The Intercept (menunjukkan di mana garis memotong paksi Y).
• B - cerun (menunjukkan kecurian garis regresi, iaitu kadar perubahan untuk y sebagai perubahan x).

Dalam bentuk asasnya, fungsi linest mengembalikan pemintas (a) dan cerun (b) untuk persamaan regresi. Secara pilihan, ia juga boleh mengembalikan statistik tambahan untuk analisis regresi seperti yang ditunjukkan dalam contoh ini.

Sintaks fungsi linest

Sintaks fungsi linest Excel adalah seperti berikut:

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

Di mana:

• Dikenali_y (diperlukan) adalah pelbagai nilai y bergantung dalam persamaan regresi. Biasanya, ia adalah satu lajur atau satu baris.
• Dikenali_x (pilihan) adalah pelbagai nilai x bebas. Jika ditinggalkan, ia dianggap sebagai array {1,2,3, ...} dari saiz yang sama seperti yang diketahui_y .
• const (pilihan) - nilai logik yang menentukan bagaimana pemintas (malar a ) harus dirawat:
  • Jika benar atau ditinggalkan, pemalar A dikira secara normal.
  • Jika palsu, pemalar A dipaksa kepada 0 dan cerun (pekali b ) dikira agar sesuai dengan y = bx.
• Statistik (pilihan) adalah nilai logik yang menentukan sama ada untuk mengeluarkan statistik tambahan atau tidak:
  • Jika benar, fungsi linest mengembalikan array dengan statistik regresi tambahan.
  • Jika palsu atau ditinggalkan, linest hanya mengembalikan pekali pemalar dan cerun yang memintas (s).

Nota. Oleh kerana linest mengembalikan pelbagai nilai, ia mesti dimasukkan sebagai formula array dengan menekan shift Ctrl ENTER Pintasan. Jika ia dimasukkan sebagai formula biasa, hanya pekali cerun pertama yang dikembalikan.

Statistik tambahan dikembalikan dengan linest

Hujah statistik yang ditetapkan kepada Benar mengarahkan fungsi linest untuk mengembalikan statistik berikut untuk analisis regresi anda:

Statistik	Penerangan
Pekali cerun	nilai b dalam y = bx a
Memintas pemalar	nilai dalam y = bx a
Kesalahan standard cerun	Nilai ralat standard untuk pekali B.
Kesalahan standard pencegahan	Nilai ralat standard untuk pemalar a .
Koefisien penentuan (r 2 )	Menunjukkan seberapa baik persamaan regresi menerangkan hubungan antara pembolehubah.
Ralat standard untuk anggaran y	Menunjukkan ketepatan analisis regresi.
F statistik, atau nilai f-ookerved	Ia digunakan untuk melakukan ujian F untuk hipotesis nol untuk menentukan kebaikan keseluruhan model.
Darjah kebebasan (df)	Bilangan darjah kebebasan.
Jumlah regresi kuadrat	Menunjukkan berapa banyak variasi dalam pembolehubah bergantung dijelaskan oleh model.
Jumlah kuadrat sisa	Mengukur jumlah varians dalam pemboleh ubah bergantung yang tidak dijelaskan oleh model regresi anda.

Peta di bawah menunjukkan susunan di mana linest mengembalikan pelbagai statistik:

Dalam tiga baris terakhir, kesilapan #N/A akan muncul di lajur ketiga dan seterusnya yang tidak diisi dengan data. Ia adalah tingkah laku lalai fungsi linest, tetapi jika anda ingin menyembunyikan notasi ralat, bungkus formula linest anda ke Iferror seperti yang ditunjukkan dalam contoh ini.

Cara Menggunakan Linest In Excel - Contoh Formula

Fungsi linest mungkin sukar untuk digunakan, terutamanya untuk orang baru, kerana anda bukan sahaja harus membina formula dengan betul, tetapi juga mentafsir outputnya dengan betul. Di bawah, anda akan menemui beberapa contoh menggunakan formula linest di Excel yang diharapkan dapat membantu menenggelamkan pengetahuan teoritis dalam :)

Regresi Linear Mudah: Kirakan cerun dan memintas

Untuk mendapatkan pemintas dan cerun garis regresi, anda menggunakan fungsi linest dalam bentuk yang paling mudah: membekalkan pelbagai nilai bergantung untuk hujah yang diketahui dan pelbagai nilai bebas untuk hujah yang dikenali . Dua hujah terakhir boleh ditetapkan untuk benar atau ditinggalkan.

Sebagai contoh, dengan nilai y (nombor jualan) dalam nilai C2: C13 dan X (kos pengiklanan) dalam B2: B13, formula regresi linear kami semudah:

=LINEST(C2:C13,B2:B13)

Untuk memasukkannya dengan betul dalam lembaran kerja anda, pilih dua sel bersebelahan dalam baris yang sama, E2: F2 Dalam contoh ini, taipkan formula, dan tekan CTRL Shift Enter untuk menyelesaikannya.

Formula akan mengembalikan pekali cerun di sel pertama (E2) dan pemalar pemintasan di sel kedua (F2):

Cerun adalah kira -kira 0.52 (bulat ke dua tempat perpuluhan). Ini bermakna apabila x meningkat sebanyak 1, y meningkat sebanyak 0.52.

Y -Intercept adalah negatif -4.99. Ia adalah nilai yang dijangkakan y apabila x = 0. Jika diplot pada graf, ia adalah nilai di mana garis regresi melintasi paksi-y.

Membekalkan nilai di atas kepada persamaan regresi linear yang mudah, dan anda akan mendapat formula berikut untuk meramalkan nombor jualan berdasarkan kos pengiklanan:

y = 0.52*x - 4.99

Sebagai contoh, jika anda membelanjakan $ 50 untuk pengiklanan, anda dijangka menjual 21 payung:

0.52*50 - 4.99 = 21.01

Nilai cerun dan memintas juga boleh diperoleh secara berasingan dengan menggunakan fungsi yang sepadan atau dengan bersarang formula linest ke dalam indeks:

Cerun

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

Memintas

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

Seperti yang ditunjukkan dalam tangkapan skrin di bawah, ketiga -tiga formula menghasilkan hasil yang sama:

Regresi linear berganda: cerun dan memintas

Sekiranya anda mempunyai dua atau lebih pembolehubah bebas, pastikan anda memasukkannya ke dalam lajur bersebelahan, dan membekalkan julat keseluruhannya kepada hujah yang diketahui .

Sebagai contoh, dengan nombor jualan (nilai y ) dalam D2: D13, kos pengiklanan (satu set nilai x) dalam B2: B13 dan purata hujan bulanan (satu lagi nilai x ) dalam C2: C13, anda menggunakan formula ini:

=LINEST(D2:D13,B2:C13)

Oleh kerana formula akan mengembalikan pelbagai 3 nilai (2 koefisien cerun dan pemalar pemansuhan), kami memilih tiga sel bersebelahan dalam baris yang sama, masukkan formula dan tekan peralihan CTRL Masukkan pintasan.

Sila ambil perhatian bahawa formula regresi berganda mengembalikan pekali cerun dalam urutan terbalik pembolehubah bebas (dari kanan ke kiri), iaitu b _n , b _n-1 , ..., b ₂ , b ₁ :

Untuk meramalkan nombor jualan, kami membekalkan nilai -nilai yang dikembalikan oleh formula linest kepada persamaan regresi berganda:

y = 0.3*x ₂ 0.19*x ₁ - 10.74

Sebagai contoh, dengan $ 50 dibelanjakan untuk pengiklanan dan hujan bulanan purata 100 mm, anda dijangka menjual kira -kira 23 payung:

0.3*50 0.19*100 - 10.74 = 23.26

Regresi Linear Mudah: Ramalkan pemboleh ubah bergantung

Selain mengira nilai A dan B untuk persamaan regresi, fungsi Excel Linest juga boleh menganggarkan pemboleh ubah bergantung (Y) berdasarkan pembolehubah bebas yang diketahui (x). Untuk ini, anda menggunakan linest dalam kombinasi dengan fungsi jumlah atau sumroduct.

Sebagai contoh, inilah caranya anda boleh mengira bilangan jualan payung untuk bulan depan, katakan Oktober, berdasarkan jualan pada bulan -bulan sebelumnya dan anggaran pengiklanan Oktober $ 50:

=SUM(LINEST(C2:C10, B2:B10)*{50,1})

Daripada hardcoding nilai x dalam formula, anda boleh memberikannya sebagai rujukan sel. Dalam kes ini, anda perlu memasukkan 1 pemalar dalam beberapa sel juga kerana anda tidak boleh mencampurkan rujukan dan nilai dalam pemalar array.

Dengan nilai x dalam E2 dan pemalar 1 dalam F2, salah satu daripada formula di bawah akan berfungsi dengan baik:

Formula biasa (dimasukkan dengan menekan Enter ):

=SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

Formula Array (dimasukkan dengan menekan CTRL Shift Enter ):

=SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

Untuk mengesahkan hasilnya, anda boleh mendapatkan pencegahan dan cerun untuk data yang sama, dan kemudian gunakan formula regresi linear untuk mengira y :

=E2*G2 F2

Di mana E2 adalah cerun, G2 adalah nilai x , dan F2 adalah pemintas:

Regresi Pelbagai: Ramalkan pemboleh ubah bergantung

Sekiranya anda berurusan dengan beberapa peramal, iaitu beberapa set nilai x yang berbeza, termasuk semua peramal dalam pemalar array. Sebagai contoh, dengan belanjawan pengiklanan $ 50 (x ₂ ) dan hujan bulanan purata 100 mm (x ₁ ), formula berjalan seperti berikut:

=SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

Di mana D2: D10 adalah nilai y yang diketahui dan B2: C10 adalah dua set nilai x :

Sila perhatikan urutan nilai x dalam pemalar array. Seperti yang dinyatakan sebelum ini, apabila fungsi Excel Linest digunakan untuk melakukan regresi berganda, ia mengembalikan koefisien cerun dari kanan ke kiri. Dalam contoh kami, pekali pengiklanan dikembalikan terlebih dahulu, dan kemudian pekali hujan . Untuk mengira nombor jualan yang diramalkan dengan betul, anda perlu membiak pekali dengan nilai x yang sepadan, jadi anda meletakkan unsur -unsur pemalar array dalam urutan ini: {50,100,1}. Elemen terakhir adalah 1, kerana nilai terakhir yang dikembalikan oleh linest adalah pemintas yang tidak boleh diubah, jadi anda hanya membiaknya dengan 1.

Daripada menggunakan pemalar array, anda boleh memasukkan semua pembolehubah x dalam beberapa sel, dan merujuk sel -sel tersebut dalam formula anda seperti yang kami lakukan dalam contoh sebelumnya.

Formula biasa:

=SUMPRODUCT(LINEST(D2:D10, B2:C10)*(F2:H2))

Formula Array:

=SUM(LINEST(D2:D10, B2:C10)*(F2:H2))

Di mana f2 dan g2 adalah nilai x dan h2 adalah 1:

Formula Linest: Statistik Regresi Tambahan

Seperti yang anda ingat, untuk mendapatkan lebih banyak statistik untuk analisis regresi anda, anda meletakkan benar dalam hujah terakhir fungsi linest. Digunakan untuk data sampel kami, formula mengambil bentuk berikut:

=LINEST(D2:D13, B2:C13, TRUE, TRUE)

Memandangkan kami mempunyai 2 pembolehubah bebas dalam lajur B dan C, kami memilih kemarahan yang terdiri daripada 3 baris (dua nilai x memintas) dan 5 lajur, masukkan formula di atas, tekan CTRL Shift Enter , dan dapatkan hasil ini:

Untuk menghilangkan kesilapan #N/A, anda boleh bersarang ke Iferror seperti ini:

=IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

Tangkapan skrin di bawah menunjukkan hasilnya dan menerangkan apa maksud setiap nombor:

Koefisien cerun dan penentuan Y telah dijelaskan dalam contoh-contoh sebelumnya, jadi mari kita lihat dengan cepat statistik lain.

Koefisien penentuan (R ² ). Nilai R ² adalah hasil membahagikan jumlah regresi kuadrat dengan jumlah jumlah kuadrat. Ia memberitahu anda berapa banyak nilai y dijelaskan oleh pembolehubah x . Ia boleh menjadi nombor dari 0 hingga 1, iaitu 0% hingga 100%. Dalam contoh ini, R ² adalah kira -kira 0.97, yang bermaksud bahawa 97% daripada pembolehubah bergantung (jualan payung) dijelaskan oleh pembolehubah bebas (pengiklanan purata hujan bulanan), yang sangat sesuai!

Kesalahan standard . Umumnya, nilai -nilai ini menunjukkan ketepatan analisis regresi. Lebih kecil nombor, semakin pasti anda boleh menjadi model regresi anda.

F statistik . Anda menggunakan statistik F untuk menyokong atau menolak hipotesis nol. Adalah disyorkan untuk menggunakan statistik F dalam kombinasi dengan nilai P apabila memutuskan jika hasil keseluruhannya penting.

Darjah kebebasan (df). Fungsi linest dalam Excel mengembalikan tahap kebebasan sisa , iaitu jumlah DF tolak regresi DF . Anda boleh menggunakan darjah kebebasan untuk mendapatkan nilai F-kritikal dalam jadual statistik, dan kemudian bandingkan nilai F-kritikal ke statistik F untuk menentukan tahap keyakinan untuk model anda.

Jumlah regresi kuadrat (aka jumlah kuadrat yang dijelaskan , atau jumlah model kuadrat ). Ia adalah jumlah perbezaan kuadrat antara nilai y yang diramalkan dan min y, dikira dengan formula ini: = Σ (ŷ - ȳ) ² . Ia menunjukkan berapa banyak variasi dalam pembolehubah bergantung kepada model regresi anda.

Jumlah kuadrat sisa . Ia adalah jumlah perbezaan kuadrat antara nilai y sebenar dan nilai y yang diramalkan. Ia menunjukkan berapa banyak variasi dalam pembolehubah bergantung kepada model anda tidak dijelaskan. Semakin kecil jumlah kuadrat sisa berbanding dengan jumlah jumlah kuadrat, semakin baik model regresi anda sesuai dengan data anda.

5 perkara yang harus anda ketahui mengenai fungsi linest

Untuk menggunakan formula linest dengan cekap dalam lembaran kerja anda, anda mungkin ingin mengetahui lebih lanjut mengenai "mekanik dalaman" fungsi:

1. Dikenali_y dan dikenali_x . Dalam model regresi linear yang mudah dengan hanya satu set pembolehubah x, known_y dan dikenali_x boleh menjadi julat apa -apa bentuk selagi mereka mempunyai bilangan baris dan lajur yang sama. Jika anda melakukan analisis regresi berganda dengan lebih daripada satu set pembolehubah X bebas, diketahui_y mesti menjadi vektor, iaitu pelbagai baris atau satu lajur.
2. Memaksa pemalar kepada sifar . Apabila argumen const adalah benar atau ditinggalkan, pemalar (intercept) dikira dan dimasukkan ke dalam persamaan: y = bx a. Jika const ditetapkan kepada palsu, pemintas dianggap sama 0 dan ditinggalkan dari persamaan regresi: y = bx.

   Dalam statistik, ia telah diperdebatkan selama beberapa dekad sama ada masuk akal untuk memaksa pemansuhan tetap kepada 0 atau tidak. Banyak pengamal analisis regresi yang boleh dipercayai percaya bahawa jika menetapkan pencegahan kepada sifar (const = false) nampaknya berguna, maka regresi linear itu sendiri adalah model yang salah untuk set data. Lain -lain menganggap bahawa pemalar boleh dipaksa menjadi sifar dalam situasi tertentu, contohnya, dalam konteks reka bentuk ketidakselarasan regresi. Secara umum, adalah disyorkan untuk pergi dengan const lalai = benar atau ditinggalkan dalam kebanyakan kes.

3. Ketepatan . Ketepatan persamaan regresi yang dikira oleh fungsi linest bergantung kepada penyebaran titik data anda. Semakin linear data, semakin tepat hasil formula linest.
4. Nilai x berlebihan . Dalam sesetengah keadaan, satu atau lebih pembolehubah X bebas mungkin tidak mempunyai nilai ramalan tambahan, dan mengeluarkan pembolehubah tersebut dari model regresi tidak menjejaskan ketepatan nilai Y yang diramalkan. Fenomena ini dikenali sebagai "kollinearity". Pemeriksaan fungsi Excel Linest untuk kollinearity dan menghilangkan pembolehubah X yang berlebihan yang mengenal pasti dari model. Pembolehubah x yang ditinggalkan boleh diiktiraf oleh pekali 0 dan 0 nilai ralat standard.
5. Linest vs cerun dan memintas . Algoritma asas fungsi linest berbeza dari algoritma yang digunakan di cerun dan fungsi memintas. Oleh itu, apabila data sumber tidak dapat ditentukan atau collinear, fungsi -fungsi ini boleh mengembalikan hasil yang berbeza.

Fungsi linest excel tidak berfungsi

Sekiranya formula linest anda membuang ralat atau menghasilkan output yang salah, kemungkinannya adalah kerana salah satu sebab berikut:

1. Jika fungsi linest hanya mengembalikan satu nombor (pekali cerun), kemungkinan besar anda telah memasukkannya sebagai formula biasa, bukan formula array. Pastikan anda menekan CTRL Shift Enter untuk melengkapkan formula dengan betul. Apabila anda melakukan ini, formula akan ditutup dalam {kurungan keriting} yang dapat dilihat di bar formula.
2. #Ref! ralat. Berlaku jika julat yang dikenali dan dikenali sebagai dimensi yang berbeza.
3. #Value! ralat. Berlaku jika diketahui_x atau dikenali sebagai mengandungi sekurang -kurangnya satu sel kosong, nilai teks atau perwakilan teks nombor yang Excel tidak dikenali sebagai nilai angka. Juga, ralat #value berlaku jika hujah Const atau Stats tidak dapat dinilai kepada benar atau palsu.

Begitulah cara anda menggunakan Linest In Excel untuk analisis regresi linear yang mudah dan berganda. Untuk melihat lebih dekat formula yang dibincangkan dalam tutorial ini, anda dialu -alukan untuk memuat turun buku kerja sampel kami di bawah. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Fungsi Excel Linest (fail .xlsx)

Atas ialah kandungan terperinci Fungsi linest excel dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!