Ramalan dalam Excel: 6 cara yang terbukti

Peramalan adalah elemen yang sangat penting dari hampir mana-mana bidang aktiviti, mulai dari ekonomi dan berakhir dengan kejuruteraan. Terdapat sejumlah besar perisian yang mengkhususkan diri ke arah ini. Malangnya, tidak semua pengguna tahu bahawa pemproses Jadual Excel yang biasa ada dalam alat Arsenal untuk meramalkan, yang dalam keberkesanannya sedikit lebih rendah daripada program profesional. Mari kita cari jenis alat yang ada, dan bagaimana untuk membuat prognosis dalam amalan.

Prosedur ramalan

Tujuan apa-apa ramalan adalah untuk mengenal pasti trend semasa, dan definisi hasil yang diharapkan berhubung dengan objek yang dikaji pada titik tertentu pada masa akan datang.

Kaedah 1: garis trend

Salah satu spesies ramalan grafik yang paling popular di Excele adalah ekstrapolasi yang dibuat dengan membina garis trend.

Mari kita cuba meramalkan jumlah keuntungan syarikat selepas 3 tahun berdasarkan data mengenai penunjuk ini untuk 12 tahun yang lalu.

1. Kami sedang membina jadual berdasarkan data jadual yang terdiri daripada argumen dan nilai fungsi. Untuk melakukan ini, pilih kawasan meja, dan kemudian, semasa dalam tab "Masukkan", klik pada ikon jenis gambarajah yang dikehendaki, yang berada di blok "carta". Kemudian pilih jenis yang sesuai untuk keadaan tertentu. Adalah lebih baik untuk memilih gambarajah titik. Anda boleh memilih rupa lain, tetapi kemudian data dipaparkan dengan betul, anda perlu mengedit, khususnya, keluarkan garis argumen dan pilih skala lain dari paksi mendatar.



2. Sekarang kita perlu membina garis trend. Kami membuat klik kanan klik pada mana-mana titik rajah. Dalam menu konteks diaktifkan, anda menghentikan pemilihan di perenggan "Tambah Trend Line".



3. Tetingkap pemformatan trend yang dibuka. Ia boleh memilih salah satu daripada enam jenis anggaran:
   • Linear;
   • Logaritma;
   • Eksponen;
   • Kuasa;
   • Polinomial;
   • Penapisan linear.

   Marilah kita mula memilih perkiraan linear.

   Dalam blok tetapan "Ramalan" dalam bidang "Forward to", kami menetapkan nombor "3.0", kerana kami perlu membuat ramalan selama tiga tahun akan datang. Di samping itu, anda boleh memasang kicks berhampiran tetapan "Tunjukkan persamaan pada rajah" dan "letakkan nilai nilai anggaran (R ^ 2)" pada rajah. Penunjuk terakhir memaparkan kualiti garis trend. Selepas tetapan dibuat, klik pada butang "Tutup".



4. Garis trend dibina dan kita boleh menentukan jumlah keuntungan dalam tempoh tiga tahun. Seperti yang kita lihat, pada masa itu dia harus menterjemahkan dalam 4500 ribu rubel. Pekali R2, seperti yang dinyatakan di atas, memaparkan kualiti garis trend. Dalam kes kami, nilai R2 adalah 0.89. Semakin tinggi pekali, semakin tinggi kredibiliti garis. Nilai maksimum ia mungkin sama dengan 1. Adalah dipercayai bahawa dengan pekali lebih dari 0.85 garis trend boleh dipercayai.



5. Sekiranya anda tidak berpuas hati dengan tahap kebolehpercayaan, anda boleh kembali ke tetingkap Format Trend Line dan pilih mana-mana jenis penghampiran yang lain. Anda boleh mencuba semua pilihan yang tersedia untuk mencari yang paling tepat.

   

   Harus diingat bahawa ramalan yang berkesan menggunakan ekstrapolasi melalui garis trend mungkin jika tempoh ramalan tidak melebihi 30% dari tempoh yang dianalisis tempoh. Iaitu, ketika menganalisis tempoh 12 tahun, kita tidak boleh membuat ramalan yang berkesan selama lebih dari 3-4 tahun. Tetapi dalam kes ini, ia akan menjadi agak boleh dipercayai jika pada masa ini tidak akan ada force majeure atau bertentangan dengan keadaan yang sangat baik yang tidak dalam tempoh sebelumnya.

Pelajaran: Bagaimana untuk membina garis trend dalam Excel

Kaedah 2: Pengendali Ramalan

Ekstrapolasi untuk data jadual boleh dibuat melalui ciri standard ramalan Excel. Hujah ini merujuk kepada kategori instrumen statistik dan mempunyai sintaks berikut:

= Meramalkan (X; DAYA_STATIONS_Y; VALUES_X diketahui)

"X" adalah hujah, nilai fungsi yang anda perlukan untuk menentukan. Dalam kes kami, sebagai hujah akan menjadi tahun yang perlu dilakukan oleh peramalan.

"Nilai yang diketahui Y" - asas nilai-nilai fungsi yang diketahui. Dalam kes kita, dalam peranannya, magnitud keuntungan untuk tempoh sebelumnya.

"Nilai yang diketahui X" adalah hujah yang sepadan dengan nilai yang diketahui fungsi tersebut. Dalam peranan mereka, kita mempunyai penomboran tahun-tahun, yang mana maklumat mengenai keuntungan tahun-tahun sebelumnya dikumpulkan.

Secara semulajadi, sebagai hujah tidak semestinya menjadi segmen sementara. Sebagai contoh, mereka mungkin suhu, dan fungsi fungsi boleh menjadi tahap pengembangan air semasa pemanasan.

Apabila mengira kaedah ini, kaedah regresi linear digunakan.

Mari menganalisis nuansa penerapan pengendali meramalkan contoh tertentu. Ambil jadual yang sama. Kami perlu mengetahui ramalan keuntungan untuk 2018.

1. Kami menyerlahkan sel kosong di atas helaian di mana hasil pemprosesan dirancang. Klik pada butang "Fungsi Tampal".



2. Wizard dibuka. Dalam kategori "Statistik" memperuntukkan nama "meramalkan", dan kemudian klik pada butang "OK".



3. Tingkap argumen bermula. Dalam bidang "X", kami menyatakan nilai hujah yang mana nilai fungsi perlu dijumpai. Dalam kes kami, 2018. Oleh itu, kami memperkenalkan rekod "2018". Tetapi lebih baik untuk menentukan penunjuk ini dalam sel pada helaian, dan dalam bidang "X" semata-mata memberi pautan kepadanya. Ini akan membolehkan masa depan untuk mengautomasikan pengiraan dan mudah mengubah tahun ini.

   Dalam bidang "Nilai Y yang Dikenali", kami menentukan koordinat lajur "Keuntungan Enterprise". Ini boleh dilakukan dengan memasang kursor di lapangan, dan kemudian memanjat butang tetikus kiri dan menyerlahkan lajur yang sesuai pada helaian.

   Begitu juga, dalam bidang "Nilai X yang Dikenali", kami memperkenalkan alamat tahun ini dengan data untuk tempoh yang lalu.

   Selepas semua maklumat dibuat, klik pada butang "OK".



4. Pengendali mengira berdasarkan data yang dimasukkan dan memaparkan hasil pada skrin. Untuk 2018, ia dirancang untuk memberi keuntungan sekitar 4564.7 ribu rubel. Berdasarkan jadual yang dihasilkan, kita boleh membina jadual menggunakan alat penciptaan carta yang dibincangkan di atas.



5. Sekiranya anda menukar tahun dalam sel yang digunakan untuk memasuki hujah, hasilnya akan berubah dengan sewajarnya, dan jadual akan dikemas kini secara automatik. Sebagai contoh, menurut ramalan pada tahun 2019, jumlah keuntungan akan menjadi 4637.8 ribu rubel.

Tetapi jangan lupa bahawa, seperti dalam pembinaan garis trend, tempoh masa sehingga tempoh yang diramalkan tidak boleh melebihi 30% daripada jumlah tempoh yang mana pangkalan data telah terkumpul.

Pelajaran: Ekstrapolasi dalam Excel.

Kaedah 3: Trend Operator

Untuk meramalkan, anda boleh menggunakan fungsi lain - trend. Ia juga merujuk kepada kategori pengendali statistik. Sintaksinya sebahagian besarnya menyerupai sintaks alat predi dan kelihatan seperti ini:

= Kecenderungan (diketahui Value_y; Nilai Dikenali; NEW_DATION_X; [CONT])

Seperti yang kita lihat, hujah-hujah "nilai-nilai Y yang diketahui" dan "nilai-nilai x yang diketahui" sepenuhnya sesuai dengan unsur-unsur yang sama pengendali predspot, dan argumen "nilai baru X" sepadan dengan hujah "X" alat sebelumnya. Di samping itu, trend ini mempunyai hujah tambahan yang berterusan, tetapi ia tidak wajib dan hanya digunakan jika terdapat faktor tetap.

Pengendali ini sangat berkesan digunakan dengan kehadiran pergantungan pergantungan linear.

Mari lihat bagaimana alat ini akan berfungsi dengan pelbagai data yang sama. Untuk membandingkan hasil yang diperoleh, titik peramalan akan menentukan 2019.

1. Kami menghasilkan penamaan sel untuk memaparkan hasilnya dan menjalankan fungsi Sarjana dengan cara yang biasa. Dalam kategori "Statistik" kami mencari dan memperuntukkan nama "Trend". Klik pada butang "OK".



2. Tingkap Argumen Pengendali Kecenderungan terbuka. Dalam bidang "Nilai Y" Y ", kaedah koordinat keuntungan syarikat telah dijelaskan di atas. Dalam bidang "Nilai Yang Dikenali X", masukkan alamat lajur tahun. Dalam bidang "Nilai Baru X" kami memasuki pautan ke sel, di mana bilangan tahun di mana ramalan harus ditentukan. Dalam kes kami, ia adalah 2019. Bidang "malar" meninggalkan kosong. Klik pada butang "OK".



3. Pengendali memproses data dan memaparkan hasil pada skrin. Seperti yang anda lihat, jumlah keuntungan yang diramalkan untuk tahun 2019, dikira oleh kaedah pergantungan linear, akan seperti kaedah pengiraan sebelumnya, 4637.8 ribu rubel.

Kaedah 4: Pertumbuhan Pengendali

Fungsi lain yang boleh diramalkan dalam Excele, adalah pertumbuhan pengendali. Ia juga merujuk kepada kumpulan statistik instrumen, tetapi, tidak seperti yang sebelumnya, ia tidak menggunakan kaedah pergantungan linear, tetapi eksponen. Sintaks alat ini kelihatan seperti:

= Pertumbuhan (diketahui value_y; diketahui value_x; new_stations_x; [const])

Seperti yang kita lihat, hujah-hujah untuk ciri ini betul-betul diulangi trend hujah pengendali, jadi kita tidak akan menghentikan kali kedua pada keterangan mereka, tetapi kita akan segera meneruskan penggunaan alat ini dalam amalan.

1. Kami memperuntukkan sel output dan biasanya menyebabkan fungsi fungsi. Dalam senarai pengendali statistik, kami sedang mencari klausa "pertumbuhan", kami memperuntukkannya dan klik pada butang "OK".



2. Tetingkap hujah fungsi yang ditentukan diaktifkan. Kami memasuki bidang tetingkap ini, data sama sekali sama dengan bagaimana kami memasuki mereka dalam tetingkap hujah pengendali trend. Selepas maklumat dibuat, klik pada butang "OK".



3. Hasil pemprosesan data dipaparkan pada monitor dalam sel yang ditentukan sebelumnya. Seperti yang kita lihat, kali ini hasilnya adalah 4682.1 ribu rubel. Perbezaan dari hasil pemprosesan data oleh kecenderungan pengendali tidak penting, tetapi mereka boleh didapati. Ini disebabkan oleh fakta bahawa alat-alat ini menggunakan kaedah pengiraan yang berbeza: kaedah pergantungan linear dan kaedah ketagihan eksponen.

Kaedah 5: Linene Operator

Pengendali linear apabila mengira menggunakan kaedah anggaran linear. Ia tidak perlu dikelirukan dengan kaedah pergantungan linear yang digunakan oleh alat kecenderungan. Sintaksnya mempunyai jenis ini:

= Linene (Dikenali Value_y; Dikenali Value_x; new_stations_x; [const]; [statistik])

Dua hujah terakhir adalah pilihan. Dengan dua yang pertama kita kenal dalam cara sebelumnya. Tetapi anda mungkin menyedari bahawa tidak ada hujah dalam fungsi ini, menunjukkan nilai-nilai baru. Hakikatnya adalah bahawa alat ini hanya menentukan perubahan dalam jumlah pendapatan per unit tempoh, yang dalam kes kita adalah satu tahun, tetapi hasil umum untuk dikira secara berasingan, menambah hasil pengiraan operator linear, didarabkan oleh bilangan tahun.

1. Kami menghasilkan pemilihan sel di mana pengiraan akan dibuat dan menjalankan fungsi fungsi. Kami memperuntukkan nama "Linene" dalam kategori "Statistical" dan klik pada butang "OK".



2. Dalam bidang "Nilai R yang Dikenali", yang membuka tingkap hujah-hujah, memperkenalkan koordinat lajur "Keuntungan Enterprise". Dalam bidang "Nilai X terkenal", kami memperkenalkan alamat "Tahun" lajur. Bidang yang tinggal dibiarkan kosong. Kemudian klik pada butang "OK".



3. Program ini mengira dan memaparkan nilai trend linear ke sel yang dipilih.



4. Sekarang kita perlu mengetahui magnitud keuntungan yang diunjurkan untuk tahun 2019. Pasang "=" masuk ke dalam mana-mana sel kosong pada helaian. Klik pada sel, yang mengandungi nilai sebenar keuntungan untuk tahun lepas tahun (2016). Letakkan tanda "+". Klik lanjut pada sel, yang mengandungi trend linear yang telah dikira sebelum ini. Kami meletakkan tanda "*". Sejak antara tahun lepas tempoh yang dikaji (2016) dan tahun untuk membuat ramalan (2019) terletak satu tempoh tiga tahun, kami menetapkan nombor "3" di dalam sel. Untuk membuat pengiraan dengan mengklik pada butang Enter.

Seperti yang anda lihat, nilai keuntungan yang diunjurkan yang dikira oleh kaedah anggaran linear, pada tahun 2019 akan menjadi 4614.9 ribu rubel.

Kaedah 6: Operator LGrfpribl

Alat terakhir yang akan kita lihat akan menjadi lgrfpribl. Pengendali ini membuat pengiraan berdasarkan kaedah penghampiran eksponen. Sintaksinya mempunyai struktur berikut:

= Lgrfpriblin (diketahui value_y; diketahui value_x; new_stations_x; [const]; [statistik])

Seperti yang anda dapat lihat, semua hujah sepenuhnya mengulangi unsur-unsur yang sepadan dengan fungsi sebelumnya. Algoritma pengiraan ramalan akan berubah sedikit. Fungsi ini akan mengira trend eksponen yang akan menunjukkan berapa kali jumlah pendapatan diubah dalam satu tempoh, iaitu, untuk tahun ini. Kita perlu mencari perbezaan dalam keuntungan antara tempoh sebenar yang terakhir dan perancangan pertama, membiaknya dengan bilangan tempoh yang dirancang (3) dan menambah hasil daripada jumlah tempoh sebenar yang terakhir.

1. Dalam senarai pengendali Sarjana Fungsi, kami memperuntukkan nama "Lgrfpribl". Kami membuat klik pada butang "OK".



2. Tingkap argumen bermula. Di dalamnya kami memperkenalkan data seperti yang mereka lakukan apabila menggunakan fungsi linear. Klik pada butang "OK".



3. Hasil daripada trend eksponen dikira dan dilanjutkan ke sel yang ditetapkan.



4. Kami meletakkan tanda "=" ke dalam sel kosong. Kami membuka kurungan dan memilih sel yang mengandungi nilai pendapatan untuk tempoh sebenar yang terakhir. Kami meletakkan tanda "*" dan menyerlahkan sel yang mengandungi trend eksponen. Kami meletakkan tanda minus dan sekali lagi klik pada elemen di mana jumlah pendapatan dalam tempoh yang lalu. Kami menutup pendakap dan memandu watak-watak "* 3 +" tanpa petikan. Sekali lagi, klik pada sel yang sama, yang diperuntukkan untuk kali terakhir. Untuk menjalankan pengiraan, tekan butang Enter.

Jumlah keuntungan yang diramalkan pada tahun 2019, yang dikira dengan kaedah penghampiran eksponen, akan menjadi 4639.2 ribu rubel, yang sekali lagi tidak jauh berbeza dengan hasil yang diperoleh apabila mengira kaedah sebelumnya.

Pelajaran: Fungsi statistik lain dalam Excel

Kami mendapati apa kaedah yang boleh meramalkan dalam program Excel. Ia boleh dilakukan secara grafik melalui penggunaan garis trend, dan analitik - menggunakan beberapa fungsi statistik terbina dalam. Hasil daripada memproses data yang sama, pengendali ini mungkin menjadi hasil yang berbeza. Tetapi ia tidak menghairankan, kerana mereka semua menggunakan kaedah pengiraan yang berbeza. Sekiranya ayunannya kecil, maka semua pilihan ini digunakan untuk kes tertentu boleh dianggap agak boleh dipercayai.