Memutuskan masalah perkiraan data eksperimen berarti membangun persamaan regresi. Masalah aproksimasi muncul ketika perlu untuk menggambarkan secara analitis, yaitu dalam bentuk hubungan matematis, fenomena nyata, yang pengamatannya diberikan dalam bentuk tabel yang berisi nilai-nilai indikator pada titik waktu yang berbeda. atau di arti yang berbeda argumen independen. Misalnya,
Indikator keuntungan diketahui (dapat dilambangkan Y) tergantung pada besarnya penanaman modal ( X);
Volume penjualan perusahaan diketahui ( Y) selama enam minggu pekerjaannya. Pada kasus ini, X adalah rangkaian minggu.
Terkadang mereka mengatakan bahwa Anda perlu membangun model empiris . Empiris disebut model yang dibangun berdasarkan pengamatan nyata. Jika suatu model dapat ditemukan, maka dapat dibuat ramalan tentang perilaku fenomena dan proses yang diteliti di masa depan dan, mungkin, arah perkembangannya yang optimal dapat dipilih.
Secara umum masalah perkiraan data eksperimen memiliki yang berikut ini memanggungkan :
Biarkan data yang diperoleh dengan cara praktis diketahui (selamaN percobaan atau observasi) yang dapat direpresentasikan dengan pasangan angka (X Saya; kamu aku) . Hubungan antara keduanya tercermin dalam tabel:
| X | x 1 | x 2 | x 3 | … | xn |
| Y | kamu 1 | kamu 2 | kamu 3 | … | kamu n |
Ada kelas dengan berbagai fungsiF . Diperlukan untuk menemukan ekspresi analitis (yaitu matematis) dari hubungan antara indikator-indikator ini, yaitu, perlu untuk memilih fungsi F dari himpunan fungsiF , seperti yang . yang jalan terbaik akan memuluskan ketergantungan eksperimental antar variabel dan, seakurat mungkin, mencerminkan tren umum ketergantungan antar variabelX Dan Y, tidak termasuk kesalahan pengukuran dan penyimpangan acak.
Anda dapat mengetahui jenis fungsinya baik dari pertimbangan teoritis atau dengan menganalisis lokasi titik (xi saya; kamu saya) pada bidang koordinat.
Selesaikan secara grafis masalah perkiraan artinya menggambar kurva yang titik-titiknya (x saya; ŷ saya) akan sedekat mungkin dengan titik awal (xi saya; kamu saya) , menampilkan data eksperimen.
Untuk solusi masalah perkiraan menggunakan metode kuadrat terkecil .
Dalam hal ini, fungsi tersebut dianggap sebagai perkiraan terbaik untuk , jika untuk itu jumlah simpangan kuadrat dari nilai-nilai "teoretis", yang ditemukan menggunakan rumus empiris, dari nilai-nilai eksperimen yang sesuai, memiliki nilai terkecil dibandingkan dengan fungsi lain yang perkiraannya diinginkan dipilih.
Notasi matematika Metode kuadrat terkecil mempunyai bentuk:
Di mana N - jumlah pengamatan indikator.
Dengan demikian, masalah aproksimasi dibagi menjadi dua bagian.
Pertama, jenis ketergantungan dan, dengan demikian, jenis rumus empiris ditetapkan, yaitu, mereka memutuskan apakah itu linier, kuadrat, logaritmik, atau lainnya. Jika tidak ada pertimbangan teoritis untuk memilih jenis rumus, ketergantungan fungsional biasanya dipilih dari yang paling sederhana, membandingkan grafiknya dengan grafik fungsi tertentu.
Setelah itu, nilai numerik dari parameter yang tidak diketahui dari rumus empiris yang dipilih ditentukan, yang perkiraannya terhadap fungsi yang diberikan adalah yang terbaik.
Jenis model empiris paling sederhana dengan dua parameter yang digunakan untuk memperkirakan hasil eksperimen adalah regresi linier, dijelaskan oleh fungsi linear:
Di mana a, b - parameter yang diperlukan.
Untuk model regresi linier, metode kuadrat terkecil (1) akan ditulis:
Untuk menyelesaikan (2) terhadap a dan b, turunan parsialnya ditetapkan ke nol:
Oleh karena itu, untuk mencari a dan b, perlu diselesaikan sistem persamaan aljabar linier yang berbentuk:
(3)
Ada berbagai cara untuk menerapkan metode kuadrat terkecil dalam kasus regresi linier di Excel.
1 cara. Bangunlah sistem persamaan aljabar linier, substitusikan semua nilai yang diketahui ke dalam (3), dan selesaikan, misalnya dengan menggunakan metode matriks (lihat tugas 4).
Dalam bentuk rumus, elemen tabel perhitungan ditunjukkan pada Gambar. 26.
Metode 2. Selesaikan masalah optimasi (2) di Excel menggunakan Menemukan solusi(lihat tugas 5).
Catatan 1. Perlu dicatat bahwa untuk fungsi tujuan S akan lebih mudah menggunakan fungsi bawaan fungsi matematika SUMQVAR(array1,array2), sebagai hasilnya, jumlah kuadrat selisih antara dua larik dihitung. Dalam kasus kita, kita harus menunjukkan rentang nilai awal sebagai array1, dan nilai "teoretis", yang dihitung dengan rumus, sebagai array2, di mana A Dan B – ini adalah alamat sel dengan nilai yang diinginkan.
Catatan 2. Di kotak dialog perintah Cari solusi, Anda harus mengatur sel target, arah tujuan - ke minimum dan sel yang akan diubah (Gbr. 28). Masalah ini tidak mengandung batasan.
Catatan 3. Model bentuk nonlinier juga dapat digunakan sebagai model empiris dengan dua parameter:
Metode yang dijelaskan untuk menyelesaikan metode kuadrat terkecil juga berlaku untuk ketergantungan nonlinier.
3 cara. Untuk menemukan nilai parameter A Dan B Dalam kasus regresi linier, Anda bisa menggunakan fungsi statistik berikut yang ada di Excel:
LERENG(Nilai_diketahui; Nilai_diketahui_X)
CUT(nilai_yang diketahui_Y, nilai_yang diketahui_X)
LINEST (nilai_yang diketahui_Y; nilai_yang diketahui_X)
Selain itu, fungsi SLOPE() mengembalikan nilai parameter A , fungsi CUT() mengembalikan nilai parameter B. Fungsi LINEST() mengembalikan kedua parameter hubungan linier secara bersamaan, karena ini adalah fungsi array. Oleh karena itu, untuk memasukkan fungsi LINEST() ke dalam tabel, Anda harus mengikuti aturan berikut:
Pilih dua sel yang berdekatan
· masukkan rumusnya
· Setelah selesai, tekan kombinasi tombol Ctrl+Shift+Enter secara bersamaan.
Hasilnya, sel kiri akan berisi nilai parameter A , dan di sebelah kanan - nilai parameter B.
Untuk memecahkan masalah perkiraan secara grafis di Excel Anda perlu membuat grafik berdasarkan data sumber, misalnya, petak sebar dengan nilai-nilai yang dihubungkan dengan garis-garis halus (lihat tugas 1). Excel dapat memplot bagan ini Garis tren. Garis tren dapat ditambahkan ke seri data apa pun yang digunakan jenis berikut bagan: bagan area, bagan garis, histogram, bagan batang, atau bagan sebar.
Saat membuat garis tren di Excel berdasarkan data grafik, satu atau beberapa perkiraan digunakan. Excel memungkinkan Anda memilih satu dari lima garis yang cocok atau menghitung garis yang menunjukkan rata-rata bergerak.
Selain itu, Excel menyediakan kemampuan untuk memilih nilai perpotongan garis tren dengan sumbu Y, serta menambahkan persamaan perkiraan dan nilai keyakinan perkiraan (R 2) ke grafik. Anda juga dapat menentukan nilai data masa depan dan masa lalu berdasarkan garis tren dan persamaan perkiraan terkait.
Perkiraan polinomial suatu fungsi kontinu pada suatu segmen.
Perkiraan (dari bahasa Latin "perkiraan" - "mendekati") adalah ekspresi perkiraan objek matematika apa pun (misalnya, angka atau fungsi) melalui objek lain yang lebih sederhana, lebih nyaman digunakan, atau lebih dikenal. Dalam penelitian ilmiah, pendekatan digunakan untuk mendeskripsikan, menganalisis, menggeneralisasi dan penggunaan lebih lanjut hasil empiris.
Seperti diketahui, mungkin ada hubungan yang tepat (fungsional) antara besaran, ketika satu nilai spesifik sesuai dengan satu nilai argumen, dan hubungan (korelasi) yang kurang tepat, ketika satu nilai argumen tertentu sesuai dengan nilai perkiraan atau sekumpulan nilai fungsi tertentu, yang pada tingkat tertentu berdekatan satu sama lain. Saat melakukan penelitian ilmiah Saat mengolah hasil observasi atau eksperimen, biasanya kita harus berhadapan dengan pilihan kedua. Ketika mempelajari ketergantungan kuantitatif berbagai indikator, yang nilainya ditentukan secara empiris, sebagai suatu peraturan, terdapat beberapa variabilitas. Hal ini sebagian ditentukan oleh heterogenitas benda mati yang dipelajari dan, khususnya, alam hidup, dan sebagian lagi ditentukan oleh kesalahan pengamatan dan pengolahan bahan secara kuantitatif. Komponen terakhir tidak selalu dapat dihilangkan sepenuhnya; hanya dapat diminimalkan dengan pemilihan metode penelitian yang memadai dan kerja yang cermat. Oleh karena itu, ketika melakukan pekerjaan penelitian apa pun, muncul masalah dalam mengidentifikasi sifat sebenarnya dari ketergantungan indikator yang diteliti, pada tingkat tertentu ditutupi oleh kegagalan memperhitungkan variabilitas nilai. Untuk tujuan ini, pendekatan digunakan - deskripsi perkiraan ketergantungan korelasi variabel dengan persamaan yang sesuai ketergantungan fungsional, menyampaikan kecenderungan utama ketergantungan (atau “trennya”).
Ketika memilih suatu perkiraan, seseorang harus melanjutkan dari masalah penelitian yang spesifik. Biasanya, semakin sederhana persamaan yang digunakan untuk perkiraan, semakin mendekati deskripsi hubungan yang dihasilkan.
Oleh karena itu, penting untuk membaca seberapa signifikan dan apa yang menyebabkan penyimpangan nilai tertentu dari tren yang dihasilkan. Saat menggambarkan ketergantungan secara empiris nilai-nilai tertentu Akurasi yang jauh lebih besar dapat dicapai dengan menggunakan persamaan multiparametrik yang lebih kompleks. Namun, tidak ada gunanya berusaha menyampaikan penyimpangan nilai secara acak dalam rangkaian data empiris tertentu dengan akurasi maksimum. Jauh lebih penting untuk memahami pola umum itu pada kasus ini paling logis dan dengan akurasi yang dapat diterima dinyatakan secara tepat oleh persamaan dua parameter fungsi daya. Jadi, ketika memilih metode aproksimasi, peneliti selalu membuat kompromi: ia memutuskan sejauh mana disarankan dan pantas untuk “mengorbankan” detail dalam kasus ini dan, oleh karena itu, seberapa umum ketergantungan variabel yang dibandingkan harus diungkapkan. Seiring dengan identifikasi pola yang ditutupi oleh penyimpangan acak dari data empiris pola umum, perkiraan juga memungkinkan Anda memecahkan banyak masalah penting lainnya: memformalkan ketergantungan yang ditemukan; temukan nilai yang tidak diketahui dari variabel terikat dengan interpolasi atau, jika sesuai, ekstrapolasi.
Di sini kita akan membahas pendekatan polinomial. Artinya, tugas kita, berdasarkan data awal (fungsi dan segmen), perlu mencari polinomial yang deviasi garisnya dari grafik fungsi awal minimal.
Metode pendekatan polinomial yang paling populer adalah metode kuadrat terkecil. Di Excel, ini diimplementasikan menggunakan grafik dan garis tren.
Mari kita menganalisis metode ini di Excel.
Data awal:
Pertama, kita perlu membagi segmen ini menggunakan partisi “Chebyshev”, karena tipe ini pemisahan selalu memberikan hasil yang lebih akurat.
Pada kolom I (Gbr. 1) kita tuliskan angka dari 0 sampai 8, karena Kami membagi segmen menjadi 8 bagian.
Pada kolom z, sel dihitung menggunakan rumus: COS(3.141593*I/8). Untuk menghitung setiap sel, kami menggunakan I yang sesuai.
Kita mencari nilai setiap x menggunakan rumus: 2*z + 1.
Di kolom F(x) kita menghitung nilai fungsi ini untuk setiap x.
Gambar 1
Selanjutnya, di sel H2, I2, J2, kita atur nilai awal koefisien a, b dan c pada polinomial yang diinginkan (Gbr. 2).
Gambar 2
Di kolom F dari sel 2 hingga 10 kami menghitung nilai deviasi, yaitu. modulus selisih antara nilai fungsi awal dan polinomial yang ditemukan.
Rumus: ABS((1+x^2)^0,5+2^(-x)-($H$2*x^2+$I$2*x+$J$2)).
Di sel B11, jumlah deviasi dihitung, dan di sel B12, deviasi rata-rata (Gbr. 3).
Gambar 3
Dengan menggunakan "Chart Wizard" kita membuat plot sebar berdasarkan data di kolom x dan F(x). Sekarang di tab “Diagram”, pilih “Tambahkan garis tren” dan centang kotak yang diperlukan untuk menampilkan persamaan pada grafik (Gbr. 4).
Gambar 4
Sekarang kita substitusikan koefisien dari persamaan yang dihasilkan ke dalam sel H2, I2 dan J2 (Gbr. 5).
Gambar 5
Seperti yang Anda lihat, deviasi rata-ratanya adalah 0,117006252.
Polinomial yang ditemukan: 0,363*x² - 0,6901*x + 2,2203.
Mari kita usulkan metode pendekatan polinomial yang lain.
Buka tab "Layanan" dan pilih "Cari solusi". Di jendela yang muncul, tentukan F11 sebagai sel target, dan sama dengan nilai minimum. Di bidang "mengubah sel" kami menunjukkan H2, I2 dan J2.
Klik tombol "Jalankan". Setelah melakukan prosedur, kita melihat bahwa hasilnya telah berubah (Gbr. 6).
Gambar 6
Kali ini rata-rata deviasinya sebesar 0,106084329.
Polinomial yang ditemukan: 0,35724*x² - 0,702*x + 2,259158.
Hasil ini jauh lebih akurat dibandingkan hasil sebelumnya, yang menegaskan keunggulan penggunaan minimalisasi jumlah deviasi dibandingkan dengan metode kuadrat terkecil.
KETERGANTUNGAN
Excel memiliki alat yang memungkinkan Anda memprediksi proses. Masalah aproksimasi muncul ketika perlu untuk menggambarkan secara analitis fenomena-fenomena yang terjadi dalam kehidupan dan diberikan dalam bentuk tabel yang berisi nilai-nilai argumen (argumen) dan fungsi. Jika ketergantungan dapat ditemukan, maka dimungkinkan untuk membuat prediksi tentang perilaku sistem yang diteliti di masa depan dan, mungkin, memilih arah optimal untuk pengembangannya. Fungsi analitik seperti itu (juga disebut tren) dapat dimiliki beda tipe dan tingkat kompleksitas yang bervariasi tergantung pada kompleksitas sistem dan keakuratan representasi yang diinginkan.
10.1. Regresi linier
Pendekatan garis lurus yang paling sederhana dan populer adalah regresi linier.
Mari kita dapatkan informasi aktual tentang tingkat keuntungan Y tergantung pada ukuran investasi X - Y(X). Pada Gambar. Gambar 10.1-1 menunjukkan empat titik M(Y,X). Mari kita juga mempunyai alasan untuk berasumsi bahwa ketergantungan ini linier, yaitu. seperti Y=A+BX. Jika kita dapat menemukan koefisien A dan B dan menggunakannya untuk membuat garis lurus (misalnya, seperti pada gambar), di masa depan kita dapat membuat asumsi yang tepat mengenai dinamika bisnis dan kemungkinan keadaan komersial. perusahaan di masa depan. Jelasnya, kita akan puas dengan garis lurus yang terletak sedekat mungkin dengan titik yang diketahui M(Y,X), yaitu. memiliki jumlah deviasi atau jumlah kesalahan minimum (pada gambar, deviasi ditunjukkan dengan garis putus-putus). Diketahui bahwa hanya ada satu garis seperti itu.
|
|
Untuk mengatasi masalah ini, digunakan metode kesalahan kuadrat terkecil. Perbedaan (kesalahan) antara nilai yang diketahui Y1 dari titik M1(Y1,X1) dan nilai Y(X1), dihitung dari persamaan garis lurus untuk nilai X1 yang sama, adalah
D1 = Y1 – SEBUAH – B X1.
Perbedaan yang sama
untuk X=X2 menjadi D2 = Y2 – A – B X2;
untuk X=X3 D3 = Y3 – A – B X3;
dan untuk X=X4 D4 = Y4 – A – B X4.
Mari kita tuliskan ekspresi jumlah kuadrat kesalahan ini
Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2
atau disingkat Ф(B,A) = å(Yi – A – BXi) 2.
Di sini kita mengetahui semua X dan Y serta koefisien A dan B yang tidak diketahui. Mari kita menggambar garis lurus yang diinginkan sedemikian rupa (yaitu, pilih A dan B sedemikian rupa) sehingga jumlah kesalahan kuadrat Ф(A,B) adalah minimal. Kondisi minimalis adalah relasi yang diketahui
¶Ф(A,B)/¶A=0 dan ¶Ф(A,B)/¶B=0.
Mari kita turunkan ekspresi berikut (kita hilangkan subskrip pada tanda penjumlahan):
¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)
¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).
Mari kita ubah rumus yang dihasilkan dan menyamakannya dengan nol
Memecahkan masalah perkiraan menggunakan Excel
Doktor Fisika – Matematika. Sains, Profesor Gavrilenko V.V. asisten Parokhnenko L.M.
(Universitas Transportasi Nasional)
Informasi teoritis. Dalam praktiknya, ketika memodelkan berbagai pro-
proses, khususnya ekonomi, fisik, teknis, sosial,
Satu atau lain metode untuk menghitung nilai perkiraan banyak digunakan.
fungsi berdasarkan nilainya yang diketahui pada beberapa titik tetap.
Masalah perkiraan fungsi seperti ini sering muncul:
∙ saat membuat rumus perkiraan untuk menghitung nilai karakteristik
nilai-nilai proses yang diteliti sesuai dengan tabel data yang diperoleh secara real-time
hasil percobaan fisik atau komputasi;
∙ dengan integrasi numerik, diferensiasi numerik, numerik
keputusan baru persamaan diferensial dll.;
∙ jika perlu menghitung nilai fungsi pada titik perantara,
kah dari interval yang dipertimbangkan;
∙ ketika menentukan nilai besaran karakteristik suatu proses di luar batas
interval yang sedang dilihat, khususnya, jika perlu, perhatikan
“masa lalu”), yaitu ketika menentukan nilai-nilai indikator proses hingga saat ini
mulai mengamati;
∙ dalam peramalan, yaitu saat menerima perkiraan awal akan-
nilai-nilai umum dari proses indikator yang menarik (kesempatan untuk melihat
untuk kedepannya").
Jika, untuk mensimulasikan proses tertentu yang ditentukan oleh tabel,
membangun fungsi yang kira-kira menggambarkan proses tertentu berdasarkan me-
metode kuadrat terkecil, maka disebut fungsi aproksimasi
(regresi), dan tugas membangun fungsi perkiraan itu sendiri disebut
Ini adalah masalah perkiraan.
Artikel ini membahas kemampuan paket Excel saat menyelesaikan
penelitian tentang masalah aproksimasi, yaitu metode dan teknik konstruksi
(membuat) regresi untuk fungsi tabulasi, yang merupakan dasar analisis regresi.
Excel memiliki fitur berikut untuk membuat regresi:
1) menambahkan regresi yang dipilih (garis tren) ke diagram yang dibangun berdasarkan tabel data untuk karakteristik proses yang dipelajari (alat ini hanya dapat digunakan jika ada yang dibangun
2) Menggunakan Fungsi Statistik Bawaan Lembar Kerja Unggul,
memungkinkan untuk memperoleh regresi (garis tren) berdasarkan tabel sumber
data (penggunaan alat ini tidak terlebih dahulu
dilengkapi dengan diagram yang sesuai).
Menambahkan garis tren ke grafik
Untuk tabel data yang menggambarkan proses tertentu dan diwakili oleh diagram, Excel memiliki alat yang efektif analisis regresi
untuk, mengizinkan:
∙ membangun berdasarkan metode kuadrat terkecil dan menambahkan lima jenis regresi (garis tren) ke diagram, yang, dengan berbagai tingkat akurasi,
mereka memodelkan proses yang diteliti;
∙ tambahkan persamaan regresi yang dibangun ke diagram;
∙ menentukan tingkat kesesuaian regresi yang dipilih dengan data yang ditampilkan pada grafik.
Model proses yang dibangun—garis tren—menunjukkan
kecenderungan perubahan data, memungkinkan untuk menentukan nilai penelitian
karakteristik tertentu pada titik tengah, memprediksi perilaku proses tertentu di masa depan (masalah ekstrapolasi), dan juga melihat masa lalunya.
Berdasarkan data bagan, Excel memungkinkan Anda memperoleh jenis regresi berikut:
sy atau garis tren, seperti linier, polinomial, logaritma, steril-
busa, eksponensial, yang diberikan oleh persamaan y = y(x) , di mana x independen
variabel terikat, yang sering kali mengambil nilai barisan bilangan asli (1; 2; 3; ...) dan menghasilkan, misalnya, hitungan mundur waktu dari proses yang diteliti.
1. Regresi linier baik untuk memodelkan karakteristik yang nilainya naik atau turun dengan laju yang konstan. Ini adalah model yang paling sederhana untuk dibangun, namun paling tidak akurat dari proses yang diteliti.
kamu = mx + b,
dimana m adalah sudut kemiringan regresi linier terhadap sumbu absis; b – koordinat titik potong regresi linier dengan ordinat.
2. Garis tren polinomial berguna untuk menggambarkan karakteristik
memiliki beberapa ekstrem yang jelas (maksimum dan minimum)
bergerak). Pilihan derajat garis tren polinomial (polinomial) ditentukan oleh jumlah ekstrem dari karakteristik yang diteliti. Jadi, polinomial derajat kedua dapat menggambarkan dengan baik suatu karakteristik yang hanya memiliki satu maksimum
ibu atau minimal; polinomial derajat ketiga – tidak lebih dari dua ekstrem; Oleh-
linier derajat keempat – tidak lebih dari tiga ekstrem, dll.
Dibangun sesuai dengan persamaan
y = c0 + c1 x + c2 x2 + c3 x3 + c4 x4 + c5 x5 + c6 x6,
dimana koefisien c 0 , c 1 , c 2 ,...c 6 adalah konstanta.
3. Logaritma garis tren berhasil digunakan dalam pemodelan
karakteristik, yang nilainya awalnya naik atau turun nilainya dengan cepat, dan kemudian secara bertahap menjadi stabil.
Dibangun sesuai dengan persamaan
kamu = c× ln(x)+ b,
4. Garis tren kekuatan memberikan hasil yang baik jika nilai-nilai dipelajari
Ketergantungan ini ditandai dengan perubahan konstan dalam tingkat pertumbuhan.
Contoh ketergantungan tersebut adalah grafik gerak dipercepat beraturan.
mobil. Jika terdapat nilai nol atau negatif pada data, Anda tidak dapat menggunakan garis tren pangkat.
Dibangun sesuai dengan persamaan
kamu = c×xb,
dimana koefisien b, c adalah konstanta.
5. Eksponensial Garis tren sebaiknya digunakan jika laju perubahan data terus meningkat. Untuk data yang mengandung nilai nol atau negatif, pendekatan jenis ini tidak berlaku.
Dibangun sesuai dengan persamaan
kamu = c×eb×x,
dimana koefisien b, c adalah konstanta.
Saat memilih garis tren, Excel secara otomatis menghitung nilai R 2 , yang mencirikan keandalan perkiraan: semakin dekat nilai R 2 ke kesatuan, semakin andal garis tren tersebut mendekati penelitian.
proses saya. Jika perlu, nilai R 2 selalu dapat ditampilkan
diagram.
Ditentukan oleh rumus
R 2 = 1- | Σ1 = ∑(yj − Yj )2 | S2 =∑Y j 2 - | × (∑Yj )2 | ||||||
Untuk menambahkan garis tren ke rangkaian data:
1. Aktifkan diagram yang dibuat berdasarkan serangkaian data, mis. klik-
dalam area diagram. Item Dia- akan muncul di menu utama.
2. Setelah mengklik item ini, sebuah menu akan muncul di layar di mana Anda harus memilih perintah Tambahkan garis tren.
Arahkan mouse ke grafik yang dibuat berdasarkan serangkaian data dan klik kanan
Arahkan mouse Anda dan pilih Tambah dari menu konteks yang muncul.
garis tren. Kotak dialog Garis Tren akan muncul di layar dengan
tab Tipe itu (Gbr. 1).
Gambar.1. Ketik tab pada kotak dialog Format Garis Tren
3. Pilih tipe garis tren yang diperlukan pada tab Type (tipe Linear dipilih secara default). Untuk tipe Polinomial, di bidang Derajat pelacakan
Anda dapat menentukan derajat polinomial yang dipilih.
4. Di bidang Seri yang dibangun, semua seri data dari bagan yang dimaksud dicantumkan. Untuk menambahkan garis tren ke seri data tertentu, pilih namanya di bidang Seri yang dibangun.
5. Jika perlu, dengan membuka tab Parameter (Gbr. 2), Anda bisa
menetapkan tren parameter berikut:
∙ Ubah nama garis tren di bidang Nama perkiraan
kurva (dihaluskan);
∙ Tetapkan jumlah periode (maju atau mundur) untuk prakiraan di Pro-
∙ Tampilkan persamaan garis tren pada area diagram, yang seharusnya Anda lakukan
Centang kotak untuk opsi “tunjukkan persamaan pada diagram”.
∙ Tampilkan nilai reliabilitas perkiraan R 2 di area diagram,
untuk itu Anda harus mencentang kotak opsi "tempatkan pada diagram berat"
keandalan perkiraan (R^2).”
∙ Tetapkan titik perpotongan garis tren dengan sumbu Y, yang harus Anda tetapkan
Pilih kotak centang untuk opsi “potongan kurva dengan sumbu Y di titik: “. 6. Tekan tombol OK.
Gambar.2. Tab opsi pada kotak dialog Trendline
Untuk mengedit garis tren yang sudah digambar:
1. Klik kiri pada garis tren yang Anda inginkan
mengubah.
2. Tekan tombol Format di menu utama, dan menu konteks muncul
Nude pilih perintah Selected trend line.
Poin 1–2 juga dapat dengan mudah diterapkan langkah berikutnya: mengirim indikasi-
Arahkan mouse ke grafik garis tren, klik kanan, dan di menu konteks yang muncul, pilih perintah Format garis tren.
Langkah 1–2 bahkan lebih mudah untuk diterapkan: klik dua kali tombol kiri mouse pada grafik garis tren.
3. Kotak dialog Trend Line Format akan muncul di layar (Gbr. 3), berisi
Ada tiga tab: Tampilan, Tipe, Parameter, dan isi tab Tipe,
Parameternya benar-benar sama dengan tab serupa di kotak dialog -
pada garis Tren (Gbr. 1–2).
4. Jika perlu, dengan membuka tab Lihat (Gbr. 3), Anda dapat mengatur jenis garis, warna dan ketebalan garis tren.
5. Tekan tombol OK.
Untuk menghapus garis tren yang sudah digambar, pilih garis tren yang akan dihapus dan tekan tombol Hapus.
Kelebihan alat analisis regresi ini adalah:
∙ relatif mudah dalam membuat garis tren pada grafik tanpa membuatnya
membuat tabel data untuk itu;
∙ daftar jenis garis tren yang diusulkan cukup luas, dan daftar ini mencakup regresi yang paling umum digunakan;
∙ kemampuan untuk memprediksi perilaku proses yang diteliti dalam produksi
sejumlah langkah maju dan mundur yang bebas (dalam batas akal sehat);
∙ kemampuan memperoleh persamaan garis tren dalam bentuk analitis;
∙ kemungkinan, jika perlu, untuk memperoleh penilaian terhadap reliabilitas tes
perkiraan hari.
Kerugiannya antara lain sebagai berikut:
∙ konstruksi garis tren hanya dilakukan jika terdapat diagram yang dibangun berdasarkan serangkaian data;
∙ proses menghasilkan rangkaian data untuk penelitian agak berantakan
karakteristik saya berdasarkan persamaan garis pelatihan yang diperoleh untuk itu
ya, karena koefisien persamaan ini dengan setiap perubahan nilai
Ya, data dihitung ulang, tetapi hanya dalam area grafik;
∙ di laporan PivotChart saat Anda mengubah tampilan bagan atau laporan terkait tabel pivot garis tren yang ada tidak disimpan,
yaitu, sebelum menggambar garis tren atau memformat laporan ringkasan
grafik, Anda harus memastikan bahwa tata letak laporan memenuhi kebutuhan
persyaratan kami.
Gambar.3. Lihat tab pada kotak dialog Format Trendline
Garis tren dapat digunakan untuk melengkapi rangkaian data yang disajikan dalam grafik.
bagan, histogram, diagram datar tidak standar dengan area, garis
grafik garis, sebar, gelembung, dan stok.
Tidak mungkin melengkapi garis tren dengan rangkaian data volumetrik yang dinormalisasi.
bagan mandi, radar, pai, dan donat. Saat mengganti jenis dia-
gram ke salah satu di atas, atau saat Anda mengubah tampilan laporan PivotChart atau laporan PivotTable terkait.
Garis tren yang terkait dengan data akan hilang.
Menggunakan fungsi bawaan Excel
Excel juga memiliki alat analisis regresi untuk merencanakan garis tren di luar area grafik. Ada sejumlah fungsi lembar kerja statistik yang bisa Anda gunakan untuk tujuan ini, namun semuanya hanya memungkinkan Anda membuat regresi linier atau eksponensial.
Excel memiliki beberapa opsi untuk membangun regresi linier (li-
tren linier), khususnya:
∙ menggunakan fungsi TREND;
∙ menggunakan fungsi LINEST;
∙ menggunakan fungsi TILT dan CUT.
Excel juga memiliki beberapa opsi untuk membuat garis tren eksponensial, khususnya:
∙ menggunakan fungsi PERTUMBUHAN;
∙ menggunakan fungsi LGRFPRIBL.
Perlu diperhatikan bahwa teknik membangun regresi menggunakan fungsi
TREND dan PERTUMBUHAN hampir sama. Hal yang sama juga berlaku pada pasangan fungsi LINEST dan LGRFPRIBL. Untuk keempat fungsi ini, saat membuat tabel nilai, fitur Excel seperti rumus array digunakan, yang agak mengacaukan proses pembuatan regresi.
ini. Perhatikan juga bahwa konstruksi (pembuatan) regresi linier, menurut pendapat kami, paling mudah dilakukan dengan menggunakan fungsi SLOPE dan INTERCEPT,
di mana yang pertama mendefinisikan lereng regresi linier, dan secara otomatis
surga – segmen yang dipotong oleh regresi pada sumbu ordinat.
Kelebihan alat analisis regresi ini adalah:
∙ proses yang cukup sederhana dan seragam untuk menghasilkan rangkaian data menggunakan
karakteristik berikut untuk semua fungsi statistik bawaan,
memberikan garis tren;
∙ metodologi standar untuk membangun garis tren berdasarkan seri data yang dihasilkan;
∙ kemampuan memprediksi perilaku proses yang diteliti sesuai kebutuhan
jumlah langkah maju atau mundur yang diperlukan.
Kerugian dari alat ini termasuk fakta bahwa Excel tidak memiliki fungsi bawaan untuk membuat fungsi lain (kecuali linier dan eksponensial).
berbeda) jenis garis tren. Keadaan ini seringkali tidak memungkinkan pemilihan model yang cukup akurat menggunakan fungsi bawaan di atas.
del dari proses yang diteliti, dan juga memperoleh perkiraan yang mendekati kenyataan.
Selain itu, saat menggunakan fungsi TREND dan GROWTH, persamaan garis tren tidak diketahui.
ini - aktif contoh spesifik menunjukkan kemampuan paket Excel saat menyelesaikan
Penelitian masalah aproksimasi; menunjukkan betapa efektifnya alat tersebut
Excel memiliki alat untuk membuat regresi dan perkiraan; profil-
mengilustrasikan betapa relatif mudahnya masalah tersebut dapat diselesaikan bahkan oleh pengguna yang tidak memiliki pengetahuan mendalam tentang analisis regresi.
Metodologi yang diusulkan dalam artikel untuk menguasai keterampilan memecahkan suatu solusi adalah
kami menggunakan Excel untuk masalah seperti ini (lihat juga, di mana metode untuk menyelesaikan sistem persamaan aljabar linier, persamaan nonlinier,
masalah optimasi, masalah transportasi) semoga bermanfaat dan menarik bagi pengguna. Hal ini disebabkan oleh fakta bahwa Excel diinstal di hampir setiap komputer modern, sementara spesialis terkenal
paket matematika berbasis seperti Mathematica, Maple, Matlab, Mathcad,
memiliki kemampuan yang lebih kuat untuk membangun regresi dan pro-
peramalan digunakan oleh audit pengguna yang jauh lebih sedikit
Di bawah ini adalah solusi untuk masalah tertentu menggunakan alat yang tercantum dalam paket Excel.
Tugas 1. Untuk tabel data laba suatu usaha angkutan motor tahun 1995–2002. Anda perlu melakukan hal berikut:
(Harap perhatikan bagian tambahan tertanggal 06/04/2017 di akhir artikel.)
Akuntansi dan kontrol! Mereka yang berusia di atas 40 tahun harus mengingat dengan baik slogan era pembangunan sosialisme dan komunisme di negara kita ini.
Tetapi tanpa akuntansi yang mapan, tidak akan berfungsinya suatu negara, wilayah, perusahaan, atau perusahaan secara efektif rumah tangga untuk setiap formasi sosial-ekonomi masyarakat! Untuk menyusun prakiraan dan rencana kegiatan dan pengembangan, diperlukan data awal. Dimana saya bisa mendapatkannya? Hanya satu dapat diandalkan sumbernya adalah milikmu catatan statistik periode waktu sebelumnya.
Memperhitungkan hasil kegiatan Anda, mengumpulkan dan mencatat informasi, mengolah dan menganalisis data, menerapkan hasil analisis untuk mengambil keputusan keputusan yang tepat di masa depan, menurut pemahaman saya, setiap orang waras harus melakukannya. Ini tidak lebih dari akumulasi dan penggunaan pengalaman hidup seseorang secara rasional. Jika Anda tidak menyimpan catatan data penting, maka setelah jangka waktu tertentu Anda akan melupakannya dan, ketika Anda mulai menangani masalah ini lagi, Anda akan kembali melakukan kesalahan yang sama seperti yang Anda lakukan saat pertama kali melakukan ini.
“Saya ingat 5 tahun yang lalu kami memproduksi hingga 1000 buah produk semacam itu per bulan, dan sekarang kami hampir tidak dapat merakit 700 buah!” Kami membuka statistik dan melihat bahwa 5 tahun yang lalu mereka bahkan tidak memproduksi 500 buah...
“Berapa harga satu kilometer mobil Anda, dengan memperhitungkan setiap orang biaya? Mari kita buka statistiknya – 6 rubel/km. Perjalanan ke tempat kerja – 107 rubel. Lebih murah daripada naik taksi (180 rubel) lebih dari satu setengah kali lipat. Dan ada kalanya naik taksi lebih murah...
“Berapa lama waktu yang dibutuhkan untuk membuat struktur baja menara komunikasi sudut setinggi 50m?” Kami membuka statistik - dan dalam 5 menit jawabannya sudah siap...
“Berapa biaya renovasi kamar di apartemen?” Kami mengambil catatan lama, menyesuaikan dengan inflasi selama beberapa tahun terakhir, memperhitungkan bahwa terakhir kali kami membeli bahan 10% lebih murah harga pasar dan – kita sudah mengetahui perkiraan biayanya...
Menyimpan catatan Anda aktivitas profesional, Anda akan selalu siap menjawab pertanyaan bos: “Kapan!!!???”. Dengan menyimpan catatan rumah tangga, akan lebih mudah untuk merencanakan pengeluaran untuk pembelian dalam jumlah besar, liburan, dan pengeluaran lainnya di masa depan, mengambil tindakan yang tepat untuk memperoleh penghasilan tambahan atau mengurangi pengeluaran yang tidak perlu saat ini.
Dalam artikel ini I contoh sederhana Saya akan menunjukkan bagaimana Anda dapat memproses data statistik yang dikumpulkan di Excel untuk digunakan lebih lanjut dalam memperkirakan periode mendatang.
Perkiraan data statistik di Excel dengan fungsi analitis.
Tempat produksi memproduksi struktur logam bangunan dari produk logam lembaran dan profil. Situs beroperasi secara stabil, jenis pesanannya sama, jumlah pekerja sedikit berfluktuasi. Terdapat data keluaran produk selama 12 bulan sebelumnya dan jumlah logam canai yang diproses selama periode waktu tersebut berdasarkan kelompok: lembaran, balok-I, saluran, sudut, pipa bundar, profil bagian persegi panjang, sewa bulat. Setelah analisis awal terhadap data awal, muncul asumsi bahwa total produksi bulanan struktur logam sangat bergantung pada jumlah sudut pesanan. Mari kita periksa asumsi ini.
Pertama-tama, beberapa kata tentang perkiraan. Kita akan mencari hukum - fungsi analitis, yaitu suatu fungsi diberikan oleh persamaan, yang lebih baik daripada yang lain menggambarkan ketergantungan total output struktur logam pada jumlah baja sudut dalam pesanan yang diselesaikan. Ini adalah perkiraan, dan persamaan yang ditemukan disebut fungsi perkiraan untuk fungsi aslinya, diberikan dalam bentuk tabel.
1. Nyalakan Excel dan letakkan tabel dengan data statistik pada selembar kertas.
2. Selanjutnya, kita membuat dan memformat plot pencar, di mana sepanjang sumbu X kita menetapkan nilai argumen - jumlah sudut yang diproses dalam ton. Sepanjang sumbu Y kami memplot nilai fungsi asli - total produksi struktur logam per bulan, yang ditentukan dalam tabel.
3. Kami “mengarahkan” mouse ke salah satu titik pada grafik dan klik kanan untuk membuka menu konteks (seperti yang dikatakan salah satu teman baik saya - saat bekerja di program asing, saat Anda tidak tahu harus berbuat apa, klik tombol kanan mouse lebih sering...). Di menu tarik-turun, pilih “Tambahkan garis tren…”.
4. Di jendela “Garis Tren” yang muncul, pada tab “Jenis”, pilih “Linear”.
6. Sebuah garis lurus muncul pada grafik, mendekati ketergantungan tabel kita.
Selain garis itu sendiri, kita melihat persamaan garis ini dan, yang terpenting, kita melihat nilai parameter R 2 - nilai reliabilitas pendekatan! Semakin dekat nilainya dengan 1, semakin akurat fungsi yang dipilih mendekati data tabel!
7. Kita membuat garis tren menggunakan pendekatan pangkat, logaritmik, eksponensial, dan polinomial dengan cara yang sama seperti kita membuat garis tren linier.
Dari semua fungsi yang dipilih, polinomial derajat kedua paling mendekati data kita; polinomial tersebut memiliki koefisien reliabilitas maksimum R 2 .
Namun, saya ingin memperingatkan Anda! Jika Anda mengambil polinomial dengan derajat yang lebih tinggi, Anda mungkin mendapatkan lebih banyak lagi skor tertinggi, tetapi kurvanya akan memiliki tampilan yang rumit…. Penting untuk dipahami di sini bahwa kita mencari fungsi yang dimilikinya arti fisik. Apa artinya ini? Ini berarti bahwa kita memerlukan fungsi perkiraan yang akan memberikan hasil yang memadai tidak hanya dalam rentang nilai X yang dipertimbangkan, tetapi juga di luar rentang tersebut, yaitu, fungsi ini akan menjawab pertanyaan: “Berapa keluaran struktur logam jika jumlah sudut yang diproses per bulan kurang dari 45 dan lebih dari 168 ton! Oleh karena itu, saya tidak menyarankan untuk terbawa oleh polinomial derajat tinggi, dan memilih parabola (polinomial derajat kedua) dengan hati-hati!
Jadi, kita perlu memilih fungsi yang tidak hanya menginterpolasi data tabel dengan baik dalam rentang nilai X = 45...168, tetapi juga memungkinkan ekstrapolasi yang memadai di luar rentang ini. Dalam hal ini, saya memilih fungsi logaritma, meskipun Anda juga dapat memilih fungsi linier, karena ini yang paling sederhana. Dalam contoh yang sedang dipertimbangkan, ketika memilih pendekatan linier di Excel, kesalahannya akan lebih besar daripada ketika memilih pendekatan logaritmik, tetapi tidak banyak.
8. Kami menghapus semua garis tren dari bidang grafik, kecuali fungsi logaritma. Untuk melakukan ini, klik kanan pada baris yang tidak perlu dan pilih “Hapus” dari menu konteks yang muncul.
9. Terakhir, kami akan menambahkan bilah kesalahan ke titik data tabel. Untuk melakukan ini, klik kanan pada salah satu titik pada grafik dan pilih “Format seri data…” di menu konteks dan konfigurasikan data pada tab “Kesalahan Y” seperti pada gambar di bawah.
10. Kemudian klik kanan pada salah satu garis rentang kesalahan, pilih “Format bilah kesalahan…” di menu konteks dan di jendela “Format bilah kesalahan” pada tab “Tampilan”, sesuaikan warna dan ketebalan garis.
Objek diagram lainnya diformat dengan cara yang sama.Unggul!
Hasil akhir grafik ditunjukkan pada tangkapan layar berikut.
Hasil.
Hasil dari semua tindakan sebelumnya adalah rumus yang dihasilkan untuk fungsi aproksimasi y=-172.01*ln (x)+1188.2. Mengetahui hal ini, dan jumlah sudut dalam rangkaian pekerjaan bulanan, dengan tingkat probabilitas yang tinggi (±4% - lihat bilah kesalahan) dimungkinkan untuk memprediksi total produksi struktur logam pada bulan tersebut! Misalnya, jika rencana untuk satu bulan adalah 140 ton sudut, maka total output, jika semua hal lain dianggap sama, kemungkinan besar adalah 338 ± 14 ton.
Untuk meningkatkan keandalan perkiraan, harus ada banyak data statistik. Dua belas pasang nilai saja tidak cukup.
Dari praktik, saya akan mengatakan bahwa menemukan fungsi perkiraan dengan koefisien reliabilitas R 2 >0,87 harus dianggap sebagai hasil yang baik. Hasil luar biasa– dengan R 2 >0,94.
Dalam praktiknya, mungkin sulit untuk mengidentifikasi satu faktor penentu terpenting (dalam contoh kita, massa sudut yang diproses dalam sebulan), namun jika Anda mencobanya, Anda selalu dapat menemukannya di setiap tugas tertentu! Tentu saja, total output selama sebulan sangat bergantung pada ratusan faktor, yang memerlukan biaya tenaga kerja yang signifikan dari pembuat standar dan spesialis lainnya. Tapi hasilnya masih perkiraan! Jadi, apakah layak mengeluarkan biaya ketika ada pemodelan matematika yang jauh lebih murah!
Pada artikel ini saya hanya menyentuh puncak gunung es yang disebut pengumpulan, pengolahan dan penggunaan praktis data statistik. Saya berharap dapat mengetahui apakah saya berhasil atau tidak membangkitkan minat Anda terhadap topik ini dari komentar dan penilaian artikel di mesin pencari.
Persoalan yang diangkat mengenai perkiraan suatu fungsi suatu variabel mempunyai jangkauan yang luas penggunaan praktis V daerah yang berbeda kehidupan. Namun solusi terhadap masalah perkiraan fungsi memiliki penerapan yang jauh lebih besar beberapa mandiri variabel... Baca tentang ini dan lebih banyak lagi di artikel blog berikut.
Langganan untuk pengumuman artikel di jendela yang terletak di akhir setiap artikel atau di jendela di bagian atas halaman.
Jangan lupa mengonfirmasi berlangganan dengan mengklik tautan dalam surat yang akan datang kepada Anda melalui surat yang ditentukan (mungkin sampai di folder « Spam » )!!!
Saya akan membaca komentar Anda dengan penuh minat, para pembaca yang budiman! Menulis!
P.S. (06/04/2017)
Penggantian data tabel yang sangat akurat dan indah dengan persamaan sederhana.
Anda tidak puas dengan akurasi perkiraan yang diperoleh (R 2<0,95) или вид и набор функций, предлагаемые MS Excel?
Apakah dimensi ekspresi dan bentuk garis mendekati polinomial derajat tinggi tidak enak dipandang?
Silakan merujuk ke halaman "" untuk mendapatkan hasil perkiraan data tabel Anda yang lebih akurat dan ringkas dan untuk mempelajari teknik sederhana untuk menyelesaikan masalah perkiraan presisi tinggi dengan fungsi satu variabel.
Saat menggunakan algoritme tindakan yang diusulkan, ditemukan fungsi yang sangat kompak yang memberikan akurasi perkiraan tertinggi: R 2 =0,9963!!!