Fungsi Statistik Excel: Tutorial dengan Contoh, Bagian Ketiga

Excel menyediakan berbagai fungsi statistik yang melakukan penghitungan mulai dari mean hingga distribusi statistik yang lebih kompleks dan fungsi garis tren.

Pada artikel ini kita akan mempelajari lebih dalam fungsi statistik Excel untuk menghitung fungsi garis tren.

Harap perhatikan bahwa beberapa fungsi statistik diperkenalkan di versi terbaru Excel dan oleh karena itu tidak tersedia di versi yang lebih lama.

Perkiraan waktu membaca: 12 minuti

Fungsi garis tren

Forecast

Fungsi Prakiraan Excel memprediksi titik masa depan pada garis tren linier yang sesuai dengan kumpulan nilai x dan y tertentu.

sintaksis

= FORECAST( x, known_y's, known_x's )

mata pelajaran

• x: Nilai x numerik yang ingin Anda prediksi nilai y barunya.
• known_y's: Array nilai y yang diketahui
• known_x's: Array nilai x yang diketahui

Perhatikan bahwa panjang array known_x harus sama dengan known_y dan varians dari known_x tidak harus nol.

contoh

Dalam spreadsheet berikut, fungsinya FORECAST Excel digunakan untuk memprediksi titik tambahan sepanjang garis lurus yang paling sesuai melalui serangkaian nilai x dan y yang diketahui (disimpan dalam sel F2:F7 dan G2:G7).

Seperti yang ditunjukkan pada sel F7 pada spreadsheet, fungsi untuk menghitung nilai y yang diharapkan pada x=7 adalah :=FORECAST( 7, G2:G7, F2:F7 )

Ini memberikan hasilnya 32.666667 .

Intercept

Di antara fungsi peramalan Excel kami menemukan Intercept. Fungsi Intercept di Excel menghitung intersep (nilai pada perpotongan sumbu y) dari garis regresi linier pada kumpulan nilai x dan y tertentu.

sintaksis

= INTERCEPT( known_y's, known_x's )

mata pelajaran

• known_y's: Array nilai y yang diketahui
• known_x's: Array nilai x yang diketahui

Perhatikan bahwa panjang array known_x harus sama dengan known_y dan varians dari known_x tidak harus nol.

contoh

Spreadsheet berikut menunjukkan contoh fungsinya Intercept Excel digunakan untuk menghitung titik di mana garis regresi linier melalui known_x dan known_y (tercantum dalam sel F2:F7 dan G2:G7) memotong sumbu y.

Le known_x dan known_y diplot pada grafik di spreadsheet.

Seperti yang ditunjukkan pada sel F9 pada spreadsheet, rumus untuk fungsi Intercept adalah :=INTERCEPT( G2:G7, F2:F7 )

yang memberikan hasilnya 2.4 .

Slope

Fungsi prediksi lain yang sangat menarik adalah Slope (Slope) Excel menghitung kemiringan garis regresi linier melalui sekumpulan nilai x dan y tertentu.

Sintaks fungsinya adalah:

sintaksis

= SLOPE( known_y's, known_x's )

mata pelajaran

• known_y's: Array nilai y yang diketahui
• known_x's: Array nilai x yang diketahui

Perhatikan bahwa panjang array known_x harus sama dengan known_y dan varians dari known_x tidak harus nol.

contoh

Spreadsheet berikut menunjukkan contoh fungsinya Slope (kemiringan) Excel yang digunakan untuk menghitung kemiringan garis regresi linier yang melalui known_x dan known_y, di sel F2:F7 dan G2:G7.

Le known_x dan known_y diplot pada grafik di spreadsheet.

Seperti yang ditunjukkan pada sel F9 pada spreadsheet, rumus untuk fungsi Intercept adalah :=SLOPE( G2:G7, F2:F7 )

yang memberikan hasilnya 4.628571429.

Trend

Fungsi perkiraan Excel yang sangat menarik adalah KECENDERUNGAN Excel (Trend) menghitung garis tren linier melalui kumpulan nilai y tertentu dan (opsional), kumpulan nilai x tertentu.

Fungsi tersebut kemudian memperluas garis tren linier untuk menghitung nilai y tambahan untuk kumpulan nilai x baru tambahan.

Sintaks fungsinya adalah:

sintaksis

= TREND( known_y's, [known_x's], [new_x's], [const] )

mata pelajaran

• known_y's: Array nilai y yang diketahui
• [known_x's]: Satu atau lebih array dengan nilai x yang diketahui. Ini adalah argumen opsional yang, jika diberikan, harus sama panjangnya dengan himpunan known_y's. Jika dihilangkan, himpunan [known_x's] mengambil nilai {1, 2, 3, …}.
• [baru_x]: Argumen opsional, menyediakan satu atau lebih larik nilai numerik yang mewakili sekumpulan nilai x baru, yang ingin Anda hitung nilai y barunya. Setiap larik [new_x's] harus cocok dengan array [known_x's]. Jika argumen [new_x's] dihilangkan, disetel ke [known_x's] .
• [biaya]: Argumen logis opsional yang menentukan apakah konstanta 'b', dalam persamaan linier y = m x + b , harus dipaksa sama dengan nol. Diri sendiri [biaya] adalah BENAR (atau jika argumen ini dihilangkan) konstanta b diperlakukan secara normal;
• Diri sendiri [biaya] adalah FALSE konstanta b diatur ke 0 dan persamaan garis lurus menjadi kamu = mx .

contoh

Pada spreadsheet berikut, fungsi Excel Trend digunakan untuk memperluas rangkaian nilai x dan y yang terletak pada garis lurus y = 2x + 10. Nilai x dan y yang diketahui disimpan di sel A2-B5 dari spreadsheet dan juga ditampilkan dalam grafik spreadsheet.

Perhatikan bahwa tidaklah penting bahwa titik-titik yang diberikan tepat berada di sepanjang garis lurus y = 2x + 10 (walaupun dalam contoh ini memang demikian). Fungsi Tren Excel akan menemukan garis yang paling sesuai untuk setiap kumpulan nilai yang Anda berikan.

Fungsi Trend menggunakan metode kuadrat terkecil untuk menemukan garis yang paling sesuai dan kemudian menggunakannya untuk menghitung nilai y baru untuk nilai x baru yang diberikan.

Dalam contoh ini, nilai [new_x's] disimpan di sel A8-A10, dan fungsi Tren Excel telah digunakan, di sel B8-B10, untuk menemukan nilai y baru yang sesuai. Seperti yang ditunjukkan pada bilah rumus, rumusnya adalah := TREN( B2:B5, A2:A5, A8:A10 )

Anda melihat bahwa fungsi Trend di bilah rumus diapit tanda kurung kurawal {}. Ini menunjukkan bahwa fungsi tersebut dimasukkan sebagai rumus susunan .

Growth

Di antara fungsi peramalan Excel kami menemukan Growth. Fungsinya Growth Excel menghitung kurva pertumbuhan eksponensial melalui sekumpulan nilai y tertentu dan (opsional), satu atau lebih kumpulan nilai x. Fungsi tersebut kemudian memperluas kurva untuk menghitung nilai y tambahan untuk kumpulan nilai x baru tambahan.

Sintaks fungsinya adalah:

sintaksis

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

mata pelajaran

• known_y's: Array nilai y yang diketahui
• [known_x's]: Satu atau lebih array dengan nilai x yang diketahui. Ini adalah argumen opsional yang, jika diberikan, harus sama panjangnya dengan himpunan known_y's. Jika dihilangkan, himpunan [known_x's] mengambil nilai {1, 2, 3, …}.
• [baru_x]: Sekumpulan nilai x baru, yang fungsinya menghitung nilai y baru yang sesuai. Jika dihilangkan, diasumsikan bahwa himpunan [new_x's] sama dengan [known_x's] dan fungsi tersebut mengembalikan nilai y yang terletak pada kurva pertumbuhan eksponensial yang dihitung.
• [biaya]: Argumen logis opsional yang menentukan apakah konstanta 'b', dalam persamaan linier y = b * m^x , harus dipaksa sama dengan 1. Jika [biaya] adalah BENAR (atau jika argumen ini dihilangkan) konstanta b diperlakukan secara normal; Diri sendiri [biaya] adalah FALSE konstanta b diatur ke 1 dan persamaan garis lurus menjadi y = mx .

contoh

Pada spreadsheet berikut, fungsi pertumbuhan Excel digunakan untuk memperluas rangkaian nilai x dan y yang terletak pada kurva pertumbuhan eksponensial y = 5 * 2^x. Ini disimpan di sel A2-B5 pada spreadsheet dan juga muncul di bagan spreadsheet.

Fungsi Pertumbuhan menghitung kurva pertumbuhan eksponensial yang paling sesuai dengan nilai x dan y yang diketahui. Dalam contoh sederhana ini, kurva yang paling sesuai adalah kurva eksponensial y = 5 * 2^x.

Setelah Excel menghitung persamaan kurva pertumbuhan eksponensial, Excel dapat menggunakannya untuk menghitung nilai y baru untuk nilai x baru yang disediakan di sel A8-A10.

Dalam contoh ini, nilai [new_x's] disimpan di sel A8-A10 dan fungsinya Growth Excel telah dimasukkan ke dalam sel B8-B10. Seperti yang ditunjukkan pada bilah rumus, rumusnya adalah:=Growth( B2:B5, A2:A5, A8:A10 )

Anda dapat melihat bahwa fungsi Pertumbuhan pada bilah rumus diapit tanda kurung kurawal {}. Ini menunjukkan bahwa fungsi tersebut dimasukkan sebagai rumus susunan .

Perhatikan bahwa meskipun titik-titik pada contoh di atas tepat berada di sepanjang kurva y = 5 * 2^x, hal ini tidak penting. Fungsinya Growth Excel akan menemukan kurva yang paling sesuai untuk setiap kumpulan nilai yang Anda berikan.

Fungsi keuangan

Efek

fungsi Effect Excel mengembalikan tingkat bunga tahunan efektif untuk tingkat bunga nominal tertentu dan sejumlah periode pemajemukan tertentu per tahun.

Suku bunga tahunan efektif

Suku bunga tahunan efektif adalah ukuran bunga yang menggabungkan kapitalisasi bunga dan sering digunakan untuk membandingkan pinjaman keuangan dengan persyaratan kapitalisasi yang berbeda.

Suku bunga efektif tahunan dihitung menggunakan persamaan berikut:

merpati nominal_rate adalah tingkat bunga nominal e npery adalah jumlah periode pemajemukan per tahun.

Sintaks fungsinya adalah:

sintaksis

= EFFECT( nominal_rate, npery )

mata pelajaran

• nominal_rate: Suku bunga nominal (harus berupa nilai numerik antara 0 dan 1)
• npery: Banyaknya periode pemajemukan per tahun (harus bilangan bulat positif).

contoh

Spreadsheet berikut memperlihatkan tiga contoh fungsi Efek Excel:

Jika hasil dari fungsinya Effect ditampilkan sebagai desimal atau menunjukkan 0%, kedua masalah ini kemungkinan besar disebabkan oleh pemformatan sel yang berisi fungsi tersebut Effect.

Oleh karena itu masalahnya dapat diselesaikan dengan memformat sel dalam persentase, dengan tempat desimal.

Untuk melakukan ini:

1. Pilih sel yang akan diformat sebagai persentase.
2. Buka kotak dialog “Format Sel” menggunakan salah satu metode berikut:
   • Klik kanan sel atau rentang yang dipilih dan pilih opsi Format Sel… dari menu konteks;
   • Klik Peluncur Kotak Dialog pada pengelompokan Angka pada tab Beranda Pita Excel;
   • Gunakan pintasan keyboard CTRL-1 (yaitu pilih tombol CTRL dan, sambil menahannya, pilih tombol “1” (satu)).
3. Di kotak dialog "Format Sel":
   • Pastikan kartunya Nomor di bagian atas kotak dialog dipilih.
   • memilih Persentase dari daftar Kategori di sisi kiri kotak dialog .Ini akan memunculkan opsi tambahan di sisi kanan kotak centang, memungkinkan Anda memilih jumlah tempat desimal yang ingin Anda tampilkan.
   • Setelah Anda memilih jumlah tempat desimal yang ingin Anda tampilkan, klik OK .

Nominal

fungsi Nominal Excel mengembalikan tingkat bunga nominal untuk tingkat bunga efektif tertentu dan jumlah periode pemajemukan tertentu per tahun.

Sintaks fungsinya adalah:

sintaksis

= NOMINAL( effect_rate, npery )

mata pelajaran

• effect_rate: Suku bunga efektif (nilai numerik antara 0 dan 1).
• npery: Banyaknya periode pemajemukan per tahun (harus bilangan bulat positif).

contoh

Dalam spreadsheet berikut, fungsinya Nominal Excel digunakan untuk menghitung tingkat bunga nominal tiga pinjaman dengan jangka waktu berbeda.

Ercole Palmeri