Perkiraan di Excel: 5 cara sederhana

Di antara berbagai metode peramalan, tidak mungkin untuk tidak mengalokasikan perkiraan. Dengan itu, Anda dapat menghasilkan perhitungan perkiraan dan menghitung indikator yang direncanakan dengan mengganti objek sumber ke yang lebih sederhana. Di Excel juga, dimungkinkan untuk menggunakan metode ini untuk memprediksi dan menganalisis. Mari kita lihat bagaimana metode ini dapat diterapkan dalam program yang ditentukan dengan alat bawaan.

Eksekusi perkiraan

Nama metode ini berasal dari kata Latin Proxima - "terdekat" adalah aproksimasi dengan menyederhanakan dan menghaluskan indikator yang dikenal, membangunnya ke dalam tren dan merupakan dasarnya. Tetapi metode ini dapat digunakan tidak hanya untuk peramalan, tetapi juga untuk mempelajari hasil yang sudah ada. Bagaimanapun, pendekatan, pada kenyataannya, menyederhanakan data sumber, dan versi yang disederhanakan lebih mudah dijelajahi.

Alat utama yang dimiliki perataan yang dilakukan di Excel adalah membangun garis tren. Esensi ini adalah bahwa berdasarkan indikator yang sudah ada, jadwal suatu fungsi ditarik ke periode mendatang. Tujuan utama dari garis tren, bagaimana tidak sulit ditebak, ini adalah persiapan prakiraan atau mendeteksi tren umum.

Tetapi dapat dibangun menggunakan salah satu dari lima jenis perkiraan:

• Linear;
• Eksponensial;
• Logaritma;
• Polinomial;
• Kekuasaan.

Pertimbangkan masing-masing opsi secara lebih detail secara terpisah.

Pelajaran: Cara membangun garis tren di Excel

Metode 1: Smoothing linear

Pertama-tama, mari kita pertimbangkan pilihan pendekatan termudah, yaitu menggunakan fungsi linear. Di atasnya kita akan lebih detail lebih detail, karena kita akan menyajikan karakteristik momen umum dan untuk metode lain, yaitu pembangunan jadwal dan beberapa nuansa lain, di mana, ketika mempertimbangkan opsi selanjutnya, kita tidak akan berhenti.

Pertama-tama, kami akan membangun jadwal, berdasarkan mana kami akan melakukan prosedur smoothing. Untuk membangun grafik, kami mengambil meja di mana biaya unit yang diproduksi oleh perusahaan dan laba yang sesuai dalam periode ini diperkirakan. Fungsi grafis yang kami buat akan menampilkan ketergantungan peningkatan laba dari mengurangi biaya produk.

1. Untuk membangun grafik, pertama-tama, kami menyorot kolom "biaya unit produk" dan "laba". Setelah itu, kami pindah ke tab "Sisipkan". Selanjutnya, pada rekaman di blok alat "Diagram", klik tombol "Spot". Dalam daftar yang terbuka, pilih nama "Spot dengan kurva dan spidol yang halus." Ini adalah jenis diagram yang paling cocok untuk bekerja dengan garis tren, yang berarti untuk menerapkan metode aproksimasi untuk Excel.



2. Jadwal dibangun.



3. Untuk menambahkan garis tren, pilih dengan tombol mouse kanan. Menu konteks muncul. Pilih di dalamnya item "Tambahkan Trend Line ...".

   

   Ada opsi lain untuk menambahkannya. Dalam kelompok tab tambahan pada pita "bekerja dengan grafik", kami pindah ke tab "Tata Letak". Selanjutnya, di bilah alat "Analisis", klik tombol "Trend Line". Daftar terbuka. Karena kita perlu menerapkan pendekatan linier, maka kita memilih "pendekatan linier" dari posisi yang disajikan.



4. Jika Anda masih memilih opsi tindakan pertama dengan menambahkan melalui menu konteks, jendela format terbuka.

   Di blok parameter "Bangun garis tren (perkiraan dan smoothing)", kami mengatur sakelar ke posisi "linear".

   Jika mau, Anda dapat menginstal centang di dekat posisi "Tampilkan persamaan pada diagram". Setelah itu, diagram akan menampilkan persamaan fungsi smoothing.

   Juga, dalam kasus kami, penting untuk membandingkan berbagai opsi untuk pendekatan untuk menetapkan tanda centang tentang item "Tempatkan nilai pendekatan yang valid pada diagram (R ^ 2)". Indikator ini dapat bervariasi dari 0 hingga 1. Apa yang lebih tinggi, aproksimasi lebih baik (signifikan). Diyakini bahwa dengan besarnya indikator ini 0,85 dan di atas, smoothing dapat dianggap dapat diandalkan, dan jika indikatornya lebih rendah, maka tidak.

   Setelah menghabiskan semua pengaturan di atas. Klik tombol "Tutup", ditempatkan di bagian bawah jendela.



5. Seperti yang Anda lihat, garis tren dibangun di grafik. Dengan pendekatan linier, ditunjukkan oleh strip lurus hitam. Bentuk smoothing yang ditentukan dapat digunakan dalam kasus paling sederhana ketika data bervariasi agak cepat dan ketergantungan nilai fungsi argumen jelas.

Smoothing, yang digunakan dalam hal ini, dijelaskan oleh rumus berikut:

Y = AX + B

Secara khusus, formula kami mengambil jenis ini:

Y = -0.1156x + 72.255

Nilai keakuratan pendekatan sama dengan 0,9418, yang merupakan hasil yang cukup dapat diterima, mengkarakterisasi smoothing, yang dapat diandalkan.

Metode 2: Perkiraan eksponensial

Sekarang mari kita pertimbangkan jenis pendekatan eksponensial di Excel.

1. Untuk mengubah jenis garis tren, pilih dengan tombol mouse kanan dan pilih "format garis tren ..." di menu pembuka.



2. Setelah itu, jendela format yang sudah akrab bagi kami diluncurkan. Dalam unit pemilihan tipe aproksimasi, kami mengatur sakelar ke posisi "eksponensial". Pengaturan yang tersisa akan meninggalkan yang sama seperti pada kasus pertama. Klik pada tombol "Tutup".



3. Setelah itu, garis tren akan dibangun di atas grafik. Seperti yang kita lihat, saat menggunakan metode ini, ia memiliki bentuk yang agak melengkung. Dalam hal ini, tingkat keandalan sama dengan 0,9592, yang lebih tinggi daripada saat menggunakan pendekatan linier. Metode eksponensial paling baik digunakan dalam kasus ketika nilai pertama kali berubah dengan cepat, dan kemudian mengambil formulir seimbang.

Tampilan umum fungsi smoothing seperti ini:

y = menjadi ^ x

Di mana E adalah dasar dari logaritma alami.

Secara khusus, formula kami telah mengambil formulir berikut:

Y = 6282.7 * e ^ (- 0,012 * x)

Metode 3: Smoothing logaritmik

Sekarang giliran untuk mempertimbangkan metode pendekatan logaritmik.

1. Dengan cara yang sama, seperti waktu sebelumnya melalui menu konteks, kami memulai jendela format garis tren. Kami membuat sakelar ke posisi "logaritmik" dan klik tombol "Tutup".



2. Prosedur untuk membangun garis tren dengan perkiraan logaritmik terjadi. Seperti dalam kasus sebelumnya, lebih baik menggunakan opsi ini ketika awalnya data dengan cepat diubah, dan kemudian mengambil tampilan yang seimbang. Seperti yang Anda lihat, tingkat keandalan adalah 0,946. Ini lebih tinggi daripada saat menggunakan metode linear, tetapi lebih rendah dari kualitas garis tren selama smoothing eksponensial.

Secara umum, formula smoothing terlihat seperti ini:

y = a * ln (x) + b

Di mana ln adalah besarnya logaritma alami. Karenanya nama metode.

Dalam kasus kami, formula mengambil formulir berikut:

Y = -62,81LN (x) +404.96

Metode 4: Smoothing Polinomial

Itu datang untuk mempertimbangkan metode smoothing polinomial.

1. Pergi ke jendela format garis tren, seperti yang sudah dilakukan lebih dari satu kali. Di blok "tren line build", kami mengatur sakelar ke posisi "polinomial". Di sebelah kanan item ini adalah bidang "derajat". Saat memilih nilai "polinomial" itu menjadi aktif. Di sini Anda dapat menentukan nilai daya dari 2 (diinstal secara default) ke 6. Indikator ini menentukan jumlah maxima dan fungsi minima. Saat memasang polinomial tingkat kedua, hanya satu maksimum yang dijelaskan, dan ketika polinomial keenam diinstal, hingga lima Maxima dapat dijelaskan. Untuk memulainya, biarkan pengaturan default, yaitu, kami akan menentukan tingkat kedua. Pengaturan yang tersisa biarkan sama seperti kita menempatkan mereka dengan cara sebelumnya. Klik tombol "Tutup".



2. Garis tren menggunakan metode ini dibangun. Seperti yang Anda lihat, itu bahkan lebih melengkung daripada menggunakan perkiraan eksponensial. Tingkat keandalan lebih tinggi daripada dengan metode apa pun yang digunakan sebelumnya, dan 0,9724.

   

   Metode ini paling berhasil digunakan dalam acara yang secara konstan berubah. Fungsi yang menggambarkan jenis smoothing ini terlihat seperti:

   Y = A1 + A1 * X + A2 * X ^ 2 + ... + an * x ^ n

   Dalam kasus kami, formula mengambil jenis ini:

   Y = 0,0015 * x ^ 2-1,7202 * x + 507.01

3. Sekarang mari kita ubah derajat polinomial untuk melihat apakah hasilnya akan berbeda. Kembali ke jendela format. Jenis perkiraan ditinggalkan oleh polinomial, tetapi berlawanan di jendela sejauh, kami menetapkan nilai tertinggi - 6.



4. Seperti yang kita lihat, setelah itu, garis tren kita mengambil bentuk kurva yang jelas, di mana jumlah tertinggi sama dengan enam. Tingkat keandalan naik bahkan lebih, mencapai 0,9844.

Formula yang menggambarkan jenis smoothing ini, mengambil bentuk berikut:

Y = 8e-08x ^ 6-0.0003x ^ 5 + 0.3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2e + 09

Metode 5: Smoothing Power

Dalam penyelesaian, pertimbangkan metode perkiraan daya di Excel.

1. Kami pindah ke jendela "Format Garis Trend". Instal sakelar jenis smoothing ke posisi "Power". Pertunjukan persamaan dan tingkat keandalan, seperti biasa, tinggalkan yang disertakan. Klik tombol "Tutup".



2. Program ini membentuk garis tren. Seperti yang Anda lihat, dalam kasus kami, ini adalah garis dengan sedikit tikungan. Tingkat keandalan sama dengan 0,9618, yang merupakan indikator yang agak tinggi. Dari semua cara yang dijelaskan di atas, tingkat keandalan lebih tinggi hanya ketika menggunakan metode polinomial.

Metode ini efektif dalam kasus perubahan intensif dalam fungsi ini. Penting untuk mempertimbangkan bahwa opsi ini hanya berlaku jika fungsi dan argumen tidak mengambil nilai negatif atau nol.

Formula umum yang menjelaskan metode ini memiliki jenis ini:

y = bx ^ n

Secara khusus, untuk kasus kami terlihat seperti ini:

Y = 6E + 18x ^ (- 6,512)

Seperti yang bisa kita lihat, menggunakan data tertentu yang kita gunakan misalnya, tingkat keandalan terbesar menunjukkan metode pendekatan polinomial dengan polinomial pada tingkat keenam (0,9844), tingkat reliabilitas terkecil dalam metode linear (0,9418). Tetapi ini tidak berarti bahwa tren yang sama akan menggunakan contoh lain. Tidak, tingkat efisiensi dalam metode di atas dapat berbeda secara signifikan, tergantung pada jenis fungsi tertentu yang akan dibangun oleh garis tren. Oleh karena itu, jika untuk fungsi ini metode yang dipilih paling efektif, itu tidak berarti sama sekali bahwa itu juga akan optimal dalam situasi lain.

Jika Anda tidak dapat segera menentukan sekaligus, berdasarkan rekomendasi di atas, perkiraan seperti apa yang sesuai dengan kasus Anda, yaitu, masuk akal untuk mencoba semua metode. Setelah membangun garis tren dan melihat tingkat keandalannya, Anda dapat memilih opsi terbaik.