Panduan Lengkap Cara Menghitung Frekuensi Penjualan di Excel

Menghitung Frekuensi Penjualan di Excel dalam bisnis adalah langkah penting untuk memahami tren penjualan dan mengidentifikasi produk atau layanan yang paling diminati oleh pelanggan di tanggal atau hari-hari tertentu. Dengan Excel, Anda dapat dengan mudah menghitung frekuensi penjualan dengan rumus yang tepat. Dalam artikel ini, kita akan membahas langkah-langkahnya secara detail.

Langkah 1: Persiapkan Data Penjualan

Sebelum Menghitung Frekuensi Penjualan di Excel terlebih dahulu kita siapkan data penjualannya terlebih dahulu. Caranya adalah sebagai berikut:

1. Buka program Excel dan buatlah sebuah spreadsheet baru.
2. Buatlah kolom-kolom untuk data penjualan, seperti tanggal penjualan dan jumlah pesanan.
3. Masukkan data penjualan Anda ke dalam spreadsheet sesuai dengan format yang telah Anda siapkan.

Berikut adalah contoh tabel sederhana yang bisa kita gunakan untuk tabel awal menghitung frekuensi penjualan di Excel:

Langkah 2: Mengisi Kolom Jumlah Pesanan Masuk

Pada kolom “Jumlah Pesanan Masuk” di sel G5, rumus Excel untuk menghitung frekuensi jumlah pesanan masuk ketikkan rumus berikut:

=IF(ROWS(G$5:G5)>SUMPRODUCT(--(FREQUENCY($C$5:$C$21;$C$5:$C$21)>0));"";INDEX($C$5:$C$21;AGGREGATE(15;6;(ROW($C$5:$C$21)-ROW($C$5)+1)/(FREQUENCY($C$5:$C$21;$C$5:$C$21)>0);ROWS(G$5:G5))))

Tekan Enter untuk mengonfirmasi rumus. Kemudian, tarik ke bawah untuk mengisi seluruh kolom dengan rumus tersebut. Dan berikut adalah hasilnya:

Penjelasan Rumus

Rumus Excel ini adalah rumus yang digunakan untuk menghitung frekuensi penjualan dari daftar data penjualan yang ada di range C5:C21. Mari kita jelaskan langkah-langkahnya:

1. FREQUENCY($C$5:$C$21;$C$5:$C$21)>0: Bagian ini menghasilkan array boolean yang menunjukkan apakah setiap nilai dalam rentang $C$5:$C$21 lebih besar dari 0. Ini membantu dalam mengidentifikasi nilai-nilai yang valid untuk dihitung frekuensinya.
2. --(FREQUENCY($C$5:$C$21;$C$5:$C$21)>0): Mengonversi array boolean dari langkah pertama menjadi array 1 dan 0, di mana nilai 1 menunjukkan nilai yang valid.
3. SUMPRODUCT(--(FREQUENCY($C$5:$C$21;$C$5:$C$21)>0)): Menghitung jumlah nilai 1 dalam array yang dihasilkan pada langkah sebelumnya, yang sebenarnya merupakan jumlah nilai unik dalam rentang data penjualan.
4. ROWS(G$5:G5): Menghitung jumlah baris dari sel G5 yang digunakan dalam rumus. Ini berfungsi sebagai counter untuk membatasi jumlah nilai yang ingin diambil.
5. IF(ROWS(G$5:G5)>SUMPRODUCT(...)): Ini adalah bagian dari rumus IF yang memeriksa apakah jumlah baris yang sudah diambil melebihi jumlah nilai unik dalam data penjualan. Jika ya, maka rumus ini tidak akan menghasilkan output apa pun (“”).
6. INDEX($C$5:$C$21;...): Bagian ini menggunakan fungsi INDEX untuk mengambil nilai dari rentang data penjualan berdasarkan hasil dari langkah selanjutnya.
7. AGGREGATE(15;6;...): Fungsi AGGREGATE digunakan untuk menghitung peringkat dari array yang dihasilkan oleh fungsi FREQUENCY. Peringkat ini nantinya digunakan untuk menentukan nilai mana yang akan diambil berdasarkan urutan frekuensi mereka.
8. (ROW($C$5:$C$21)-ROW($C$5)+1)/(FREQUENCY($C$5:$C$21;$C$5:$C$21)>0): Bagian ini menghasilkan array peringkat nilai dalam rentang data penjualan berdasarkan frekuensi masing-masing nilai.
9. ROWS(G$5:G5) (kembali): Digunakan lagi untuk menentukan peringkat yang ingin diambil, berdasarkan jumlah baris yang sudah diambil.

Dengan demikian, rumus ini secara efektif mengambil nilai dari data penjualan berdasarkan frekuensi mereka, dengan memastikan bahwa nilai-nilai yang sudah diambil sebelumnya tidak diambil lagi.

Langkah 3: Analisis dan Menghitung Frekuensi Penjualan di Excel

Setelah kolom “jumlah pesanan masuk” diisi dengan rumus, Anda akan melihat angka-angka yang mewakili jumlah penjualan.

Anda dapat menggunakan fungsi COUNTIFS di Excel untuk menghitung kemunculan data pesanan dari tiap-tiap sel. Gunakan rumus COUNTIFS berikut di sel H5:

=IF(ISTEXT(G5);"";COUNTIFS($C$5:$C$21;G5))

Tekan Enter untuk mengonfirmasi rumus. Kemudian, tarik ke bawah untuk mengisi seluruh kolom dengan rumus tersebut. Dan berikut adalah hasilnya:

Penjelasan Rumus

Rumus Excel ini adalah rumus yang digunakan untuk menghitung jumlah kemunculan data tertentu dalam rentang sel tertentu, dengan pengecualian jika nilai dalam sel yang diperiksa adalah teks. Mari kita jelaskan lebih detail:

1. ISTEXT(G5): Fungsi ISTEXT digunakan untuk memeriksa apakah nilai dalam sel G5 merupakan teks. Jika benar, maka rumus ini akan mengembalikan nilai TRUE; jika tidak, nilai FALSE.
2. IF(ISTEXT(G5);"";...): Ini adalah bagian dari rumus IF yang mengecek apakah nilai dalam sel G5 adalah teks. Jika benar (nilai TRUE), maka rumus akan menghasilkan nilai kosong (“”); jika tidak (nilai FALSE), maka rumus akan melanjutkan ke langkah berikutnya.
3. COUNTIFS($C$5:$C$21;G5): Bagian ini menggunakan fungsi COUNTIFS untuk menghitung jumlah kemunculan nilai yang sama dengan nilai yang terdapat dalam sel G5 di rentang data $C$5:$C$21.

Jadi, secara keseluruhan, rumus ini bekerja dengan cara berikut:

• Jika nilai dalam sel G5 adalah teks, maka rumus akan mengembalikan nilai kosong (“”).
• Jika nilai dalam sel G5 bukan teks, maka rumus akan menghitung jumlah kemunculan nilai tersebut dalam rentang data $C$5:$C$21.

Langkah 4: mengurai Tanggal dengan Frekuensi Penjualan Tertentu

Langkah terakhir, kita akan uraikan di tanggal mana saja jumlah pesanan masuk dan frekuensinya yang ada di range G5:G21 dan H5:H21 tersebut. Dan di sini (range L5:L21) kita akan menggunakan array formula yang ditulis sekaligus di banyak kolom.

Langkahnya adalah blok range L5:L21 kemudian di formula bar ketik rumus berikut:

=IFERROR(INDEX(B1:B21;SMALL(IF(C1:C21=L4;ROW(1:21));ROW(1:21)));"")

Jika sudah, karena ini adalah formula array maka akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (jangan hanya Enter). Jika benar maka akan muncul tanda kurung kurawal yang mengapit rumusnya. Berikut adalah hasilnya:

Penjelasan Rumus

Rumus array Excel ini memiliki tujuan untuk mengurai tanggal dengan jumlah pesanan tertentu, kemudian menampilkan tanggal-tanggal tersebut dalam urutan kecil-terbesar sesuai dengan jumlah pesanan tersebut. Mari kita bahas langkah-langkahnya:

1. {=IFERROR(...)}: Ini menandakan bahwa rumus ini adalah sebuah rumus array. Rumus array beroperasi pada array data dan menghasilkan output dalam bentuk array juga.
2. INDEX(B1:B21;SMALL(IF(C1:C21=L4;ROW(1:21));ROW(1:21))): IF(C1:C21=L4;ROW(1:21)): Bagian ini menggunakan fungsi IF untuk memeriksa apakah nilai dalam kolom C (jumlah pesanan) sama dengan nilai yang terdapat di sel L4. Jika benar, maka akan mengembalikan nomor baris (ROW) dari array. Sementara SMALL(...;ROW(1:21)): Fungsi SMALL digunakan untuk mengambil nilai terkecil dari array yang dihasilkan oleh fungsi IF. Parameter kedua (ROW(1:21)) digunakan untuk menentukan urutan kecil mana yang ingin diambil. Dan terakhir untuk INDEX(B1:B21;...): Fungsi INDEX digunakan untuk mengambil nilai dari kolom B (tanggal) berdasarkan nomor baris yang dihasilkan oleh fungsi SMALL.
3. IFERROR(...;""): Bagian ini menangani kesalahan yang mungkin terjadi dalam rumus. Jika ada kesalahan (misalnya, tidak ada tanggal yang sesuai dengan jumlah pesanan tertentu), maka rumus akan mengembalikan nilai kosong (“”).

Jadi, secara keseluruhan, rumus ini bekerja untuk mencari tanggal-tanggal yang sesuai dengan jumlah pesanan tertentu yang terdapat di sel L4, kemudian mengurutkannya dari kecil ke besar, dan menampilkannya dalam bentuk array.

Kesimpulan

Menghitung frekuensi penjualan di Excel dapat memberikan wawasan berharga tentang kinerja produk atau layanan Anda. Dengan mengikuti langkah-langkah yang telah dijelaskan di atas, Anda dapat dengan mudah menganalisis tren penjualan dan membuat keputusan yang lebih baik dalam mengelola bisnis Anda. Semoga panduan ini bermanfaat bagi Anda dalam mengoptimalkan strategi penjualan dan pemasaran.

Pranala Luar

File Excel Panduan Lengkap Cara Menghitung Frekuensi Penjualan di Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel tentang Cara Menghitung Frekuensi Penjualan di Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!