Menghitung Interval Waktu Pesanan Pelanggan dengan Rumus Excel

Cara menghitung interval waktu pesanan pelanggan dengan rumus Excel sebenarnya hampir sama dengan rumus menghitung rata-rata. Maka dari itu fungsi yang digunakan pun adalah fungsi AVERAGEIF. Adapun fungsi dan kegunaan menghitung interval waktu pesanan ini berkaitan dengan analisa data pelanggan tentang seberapa sering pelanggan yang dimaksud melakukan order. Apakah pelanggan tersebut melakukan reapeat order atau tidak? kalau iya, maka order itu rata-rata dilakukan tiap berapa hari?

Disinilah pentingnya menghitung interval waktu order pelanggan. Karena ketika kita sudah mengetahui bahwa pelanggan ABC misalnya selalu order tiap 10 hari dari order terakhirnya maka hal yang perlu dilakukan bisnis kita adalah memastikan stok yang dibutuhkan pelanggan ABC tersedia.

Lantas apa saja rumus Excel yang perlu digunakan untuk menghitung interval waktu pesanan pelanggan? Berikut adalah langkah demi langkahnya:

Langkah 1. Persiapkan Data Pesanan Pelanggan

Hal yang pertama wajib dilakukan adalah menyiapkan data pesanan pelanggan. Karena dari data inilah kita akan mengetahui interval waktu pesanannya sekaligus rata-rata besaran pesanan yang biasa mereka lakukan.

Dan untuk menyederhanakan rumus Excel yang akan kita gunakan, sebaiknya urutkan data tersebut berdasarkan kode pelanggannya. Bisa melalui filter, bisa juga melalui sort ascending asal outputnya adalah data pelanggan yang berurut berdasarkan kode pelanggannya.

Berikut adalah contoh tabel yang kami buat yang kode pelanggannya telah kami urutkan dari A ke Z:

Langkah 2. Menghitung Interval Waktu Pesanan

Langkah berikutnya adalah menghitung interval pesanan pelanggan di kolom F. Di sini untuk pesanan pertama akan kita tandai dengan kalimat “Awal Pesan”. Disamping sebagai penanda agar lebih mudah dilihat juga menjadi semacam pembatas dengan pesanan dari pelanggan berikutnya.

Rumus Excel untuk menghitung interval pesanan pelanggan di sel F5 adalah sebagai berikut:

=IF(C4<>C5;"Awal Pesan";B5-B4)

Copy dan paste rumus di atas ke sel di bawahnya. Bisa dengan copy dan paste. Bisa juga dengan klik and drag. Jika benar maka hasilnya sebagai berikut:

Penjelasan Rumus

Mari kita jelaskan setiap bagian dari rumus tersebut:

1. IF: Ini adalah fungsi logika IF pada Excel yang digunakan untuk melakukan pengujian logika. Fungsi IF memerlukan tiga argumen: kondisi yang akan diuji, nilai yang akan dikembalikan jika kondisi benar, dan nilai yang akan dikembalikan jika kondisi salah.
2. C4<>C5: Ini adalah kondisi yang diuji. Dalam konteks ini, kondisi ini memeriksa apakah nilai di sel C4 tidak sama dengan nilai di sel C5.
3. “Awal Pesan”: Ini adalah nilai yang akan dikembalikan jika kondisi C4<>C5 benar. Dalam rumus ini, jika nilai di sel C4 tidak sama dengan nilai di sel C5, maka rumus akan mengembalikan teks “Awal Pesan”.
4. B5-B4: Ini adalah nilai yang akan dikembalikan jika kondisi C4<>C5 salah. Dalam rumus ini, jika nilai di sel C4 sama dengan nilai di sel C5, maka rumus akan menghitung selisih antara nilai di sel B5 dengan nilai di sel B4. Rumus ini mengasumsikan bahwa sel B5 dan B4 berisi nilai waktu atau tanggal yang dapat dikurangi untuk menghasilkan interval waktu.

Jadi, secara keseluruhan, rumus ini mengatakan: jika nilai di sel C4 tidak sama dengan nilai di sel C5, maka tulis “Awal Pesan”; jika tidak, hitung selisih waktu antara nilai di sel B5 dan B4. Rumus ini mungkin digunakan untuk mengidentifikasi pesanan pertama dalam suatu rangkaian pesanan dengan membandingkan nilai dalam kolom C (misalnya, nomor pesanan) dan menghitung interval waktu antara pesanan-pesanan berikutnya dalam kolom B (misalnya, tanggal pesanan).

Langkah 3. Mencari Kode Pelanggan

Langkah ketiga adalah mengambil nama atau kode pelanggan tanpa menyertakan data kembar. Jadi di sini kita hanya akan mengambil data unik dari kode pelanggan di range C5:C20 dan mengabaikan data ganda.

Rumus Excel yang akan kita gunakan di sel H5 ini adalah menggunakan formula array yang terdiri dari fungsi INDEX MATCH yang dikombinasikan dengan fungsi SUM untuk menjumlah dan COUNTIF untuk menghitung data. Dengan COUNTIF ini kita akan memastikan bahwa data yang terambil adalah data uniknya saja dan mengabaikan data kembarannya.

Dan formula array yang bisa digunakan di sel H5 adalah sebagai berikut:

=IFERROR(INDEX($C$5:$C$20;MATCH(SUM(COUNTIF(H$4:H4;$C$5:$C$20));COUNTIF($C$5:$C$20;"<" & $C$5:$C$20);0));"")

Karena ini adalah formula array maka jangan lupa untuk mengakhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter (jangan hanya enter). Jika dilakukan dengan benar maka akan ada simbol kurung kurawal yang akan mengapit rumusnya.

Copy dan paste formula array di atas ke sel yang ada di bawahnya. Dan berikut adalah hasilnya:

Penjelasan Rumus

Mari kita jabarkan setiap bagian dari formula ini:

1. IFERROR: Ini adalah fungsi Excel yang digunakan untuk menangani kesalahan. Jika ekspresi atau formula di dalamnya menghasilkan kesalahan, IFERROR akan mengembalikan nilai alternatif yang Anda tentukan.
2. INDEX($C$5:$C$20; …): Ini adalah fungsi INDEX yang digunakan untuk mengembalikan nilai dari sel atau rentang sel tertentu dalam sebuah array. Dalam konteks ini, INDEX mengembalikan nilai dari rentang sel C5:C20.
3. MATCH(…): Ini adalah fungsi MATCH yang digunakan untuk mencari nilai tertentu dalam sebuah array dan mengembalikan posisi relatif dari nilai tersebut dalam array. Dalam formula ini, MATCH digunakan untuk mencari posisi dari nilai yang ingin Anda temukan.
4. SUM(COUNTIF(H$4:H4;$C$5:$C$20)): Ini adalah ekspresi yang menghitung jumlah berapa kali nilai dalam rentang H4 ke atas muncul dalam rentang C5:C20. Ekspresi ini digunakan sebagai argumen pertama untuk fungsi MATCH.
5. COUNTIF($C$5:$C$20;”<” & $C$5:$C$20): Ini adalah fungsi COUNTIF yang menghitung berapa kali nilai dalam rentang C5:C20 kurang dari nilai yang bersesuaian di sel tersebut. Hasil dari COUNTIF ini digunakan sebagai argumen kedua untuk fungsi MATCH.
6. 0: Ini adalah argumen ketiga untuk fungsi MATCH, yang menunjukkan bahwa pencarian harus dilakukan secara tepat.
7. “”: Ini adalah nilai yang akan dikembalikan jika formula tidak menghasilkan nilai yang valid. Dalam hal ini, jika tidak ada nilai yang cocok, formula akan mengembalikan string kosong.

Jadi, secara keseluruhan, formula ini digunakan untuk mencari dan mengembalikan nilai dari rentang C5:C20 berdasarkan kriteria tertentu yang terhitung secara dinamis menggunakan COUNTIF dan SUM, dengan penanganan kesalahan menggunakan IFERROR. Formula ini dapat digunakan untuk berbagai tujuan, seperti mengambil nilai berikutnya dalam rangkaian data atau mencari nilai tertentu berdasarkan jumlah kemunculannya dalam data.

Langkah 4. Menghitung Jumlah Pesanan Pelanggan

Untuk menghitung jumlah pesanan pelanggan di sel I5 rumus yang bisa digunakan adalah fungsi dasar Excel untuk menghitung data yakni COUNTIF. Dengan fungsi COUNTIF ini kita bisa menghitung (menghitung dan bukan menjumlah ya) seberapa banyak kata di sel H5 muncul di range C5:C20.

Dan fungsi yang bisa digunakan di sel I5 ini adalah sebagai berikut:

=COUNTIF(C$5:C$20;H5)

Copy dan paste fungsi COUNTIF tersebut ke sel di bawahnya. Copy paste atau klik and drag bisa digunakan dalam hal ini. Dan berikut adalah hasilnya:

Penjelasan Rumus

Mari kita jabarkan setiap bagian dari rumus ini:

1. COUNTIF: Ini adalah fungsi Excel yang digunakan untuk menghitung berapa kali nilai tertentu muncul dalam sebuah rentang sel. Fungsi ini memerlukan dua argumen: rentang sel yang akan diperiksa dan nilai atau kriteria yang akan dicocokkan.
2. C$5:C$20: Ini adalah rentang sel yang akan diperiksa. Rentang ini mencakup sel-sel dari C5 hingga C20 secara vertikal.
3. ; (titik koma): Ini adalah pemisah argumen dalam fungsi COUNTIF. Dalam beberapa versi Excel, pemisah argumen bisa berupa koma (,) atau titik koma (;) tergantung pada pengaturan regional komputer Anda.
4. H5: Ini adalah nilai atau kriteria yang akan dicocokkan dalam rentang sel C5:C20. COUNTIF akan menghitung berapa kali nilai H5 muncul dalam rentang tersebut.

Jadi, secara keseluruhan, rumus ini digunakan untuk menghitung berapa kali nilai yang terdapat dalam sel H5 muncul dalam rentang sel C5:C20. Misalnya, jika Anda memiliki data pesanan dan ingin menghitung berapa kali kode produk tertentu muncul dalam data tersebut, Anda dapat menggunakan rumus ini untuk melakukan perhitungan tersebut.

Langkah 5. Menghitung Waktu Rata-rata Pesanan

Langkah terakhir adalah menghitung rata-rata waktu antar pesanan di sel J5. Dan seperti yang dusah dijelaskan di awal bahwa untuk keperluan menghitung interval waktu pesanan pelanggan ini fungsi yang akan kita gunakan adalah fungsi rata-rata dengan kondisi atau kriteria tertentu yakni AVERAGEIF yang nanti kita bungkus dengan fungsi ROUND untuk membulatkan desimal dibelakang angka dan IFERROR untuk mengganti error dengan kalimat tertentu.

Dan rumus Excel untuk menghitung interval waktu pesanan pelanggan di sel J5 adalah sebagai berikut:

=IFERROR(ROUND(AVERAGEIF(C$5:C$20;H5;F$5:F$20);)&" Hari";"Sekali Pesan")

Copy dan paste rumus di atas ke sel di bawahnya. Dan jika benar maka hasilnya sebagai berikut:

Penjelasan Rumus

Mari kita bahas setiap bagian dari rumus ini:

1. IFERROR: Ini adalah fungsi Excel yang digunakan untuk menangani kesalahan. Jika ekspresi atau formula di dalamnya menghasilkan kesalahan, IFERROR akan mengembalikan nilai alternatif yang Anda tentukan.
2. ROUND(…): Ini adalah fungsi Excel yang digunakan untuk membulatkan nilai. Dalam rumus ini, ROUND digunakan untuk membulatkan nilai rata-rata yang dihasilkan oleh fungsi AVERAGEIF.
3. AVERAGEIF(C$5:C$20;H5;F$5:F$20): Ini adalah fungsi AVERAGEIF yang digunakan untuk menghitung rata-rata nilai dalam rentang F5:F20 berdasarkan kriteria yang diberikan dalam rentang C5:C20. Kriteria yang diberikan adalah nilai yang terdapat dalam sel H5.
4. &” Hari”: Ini adalah operator gabungan (&) yang digunakan untuk menggabungkan hasil rata-rata dengan teks ” Hari”, sehingga hasilnya akan berupa nilai rata-rata diikuti dengan kata ” Hari”.
5. “Sekali Pesan”: Ini adalah nilai alternatif yang akan dikembalikan oleh fungsi IFERROR jika terjadi kesalahan dalam perhitungan. Dalam hal ini, jika fungsi AVERAGEIF atau fungsi ROUND menghasilkan kesalahan, maka rumus akan mengembalikan teks “Sekali Pesan”.

Jadi, secara keseluruhan, rumus ini digunakan untuk menghitung rata-rata nilai berdasarkan kriteria tertentu dalam data, kemudian membulatkannya dan menambahkan kata ” Hari” di belakangnya. Jika terjadi kesalahan dalam perhitungan, maka rumus akan mengembalikan teks “Sekali Pesan”. Rumus ini mungkin digunakan untuk menampilkan informasi tentang rata-rata interval waktu pesanan pelanggan dalam format yang lebih mudah dipahami.

Penutup

Dalam menghitung interval waktu pesanan pelanggan dengan rumus Excel, penting untuk diingat bahwa analisis data pelanggan dapat memberikan wawasan berharga bagi strategi bisnis Anda. Dengan menggunakan fungsi seperti AVERAGEIF untuk menghitung interval waktu pesanan, Anda dapat mengidentifikasi pola pemesanan pelanggan dan mengambil langkah-langkah yang sesuai, seperti mengelola stok dengan lebih efisien.

Memahami kapan dan seberapa sering pelanggan melakukan pesanan dapat membantu Anda merencanakan persediaan dengan lebih baik, memastikan ketersediaan produk yang dibutuhkan, dan meningkatkan kepuasan pelanggan melalui pengiriman yang tepat waktu. Dengan demikian, menghitung interval waktu pesanan pelanggan bukan hanya tentang analisis data, tetapi juga tentang mengoptimalkan operasi bisnis untuk mencapai kesuksesan jangka panjang.

Pranala Luar

File Excel cara menghitung rata-rata waktu antar pesanan pelanggan 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. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!