Cara Menghitung Total Omzet dan Menemukan Salesman Terbaik di Excel

Cara menghitung total omzet yang artinya untuk memahami performa penjualan produk dan mengidentifikasi kontribusi terbesar dari salesman adalah kunci untuk meningkatkan strategi penjualan dalam sebuah bisnis. Dengan menggunakan Microsoft Excel dan beberapa rumus kunci, Anda dapat dengan mudah menghasilkan laporan yang informatif dan memberikan wawasan yang berharga tentang total omzet dan kinerja salesman.

Persiapan Data Menghitung Total Omzet

Pertama, pastikan Anda memiliki data penjualan yang lengkap dan terstruktur. Sebagai contoh, kita akan buat dua tabel. Tabel pertama untuk tabel data penjualan dan tabel berikutnya yang akan kita jadikan sebagai tabel rekap penjualan yang mencakup nama produk, total unit yang terjual, penjualan terbanyak dan salesman terbaik yang menjual produk tersebut.

Dan berikut contoh tabelnya:

Mengambil Data Produk

Dengan contoh tabel di atas, langkah pertama yang akan kita lakukan adalah mengambil data produk. Data Produk yang akan kita ambil ini melalui proses filtering data ganda. Jadi, dari sekian banyak data produk di tabel pertama, kita hanya akan mengambil data uniknya saja.

Dan rumus yang akan kita gunakan adalah array formula. Jadi pastikan mengakhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (tidak hanya Enter). Untuk memastikan bahwa formula arraynya diinput dengan benar, akan ada kurung kurawal yang mengapit rumusnya. Dan formula array yang digunakan di sel G5 adalah sebagai berikut:

=IFERROR(INDEX($C$5:$C$22;MATCH(SUM(COUNTIF(G$4:G4;$C$5:$C$22));COUNTIF($C$5:$C$22;"<" & $C$5:$C$22);0));"")

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

Penjelasan Rumus

Formula array Excel ini digunakan untuk mengambil data produk berdasarkan data di tabel penjualan. Mari kita jelaskan bagaimana formula ini bekerja:

1. COUNTIF(G$4:G4;$C$5:$C$22): Bagian ini menghitung berapa kali setiap produk muncul dalam kolom G dari baris 4 hingga baris sebelumnya dari sel yang sedang diperhitungkan. Ini memungkinkan kita untuk mengetahui kemunculan nama produk berdasarkan nama produk yang telah diproses sebelumnya.
2. SUM(COUNTIF(G$4:G4;$C$5:$C$22)): Bagian ini menjumlahkan hasil dari langkah pertama, yaitu berapa kali setiap produk muncul hingga baris sebelumnya dari sel yang sedang diperhitungkan. Ini membantu dalam menentukan urutan produk.
3. MATCH(...): Fungsi ini mencari posisi pertama di mana nama produk muncul. Dalam konteks array formula, fungsi MATCH beroperasi pada seluruh rentang data, bukan hanya pada satu sel tunggal. Ini penting karena kita mencari posisi pertama nama produk secara keseluruhan, bukan hanya pada satu baris tertentu.
4. INDEX($C$5:$C$22;...): Fungsi INDEX kemudian digunakan untuk mengambil data produk dari kolom C berdasarkan hasil pencarian MATCH. Jadi, kita mendapatkan nama produk pada posisi yang dicari oleh MATCH.
5. IFERROR(...): Bagian ini menangani kesalahan yang mungkin terjadi, seperti jika tidak ada produk yang dicari sama sekali atau jika ada kesalahan dalam formula sebelumnya. Jika terjadi kesalahan, formula akan mengembalikan teks kosong (“”).

Jadi, secara keseluruhan, formula array Excel ini bekerja untuk mengambil data produk dengan menghitung kemunculan setiap produk dan kemudian mencocokkan dengan posisi yang ditemukan.

Cara Menghitung Total Omzet Produk

Langkah kedua, kita ingin menghitung total omzet untuk setiap produk. Gunakan rumus SUMIFS di sel H5 untuk melakukan cara menghitung total omzet. Caranya dengan memasukkan rumus sebagai berikut:

=SUMIFS($E$5:$E$22;$C$5:$C$22;G5)

Rumus ini akan menjumlahkan total unit terjual (kolom E) berdasarkan produk (kolom C) yang sesuai dengan produk pada baris tertentu di Tabel Rekap Penjualan.

Menemukan Salesman Terbaik

Selanjutnya, kita ingin mengetahui salesman terbaik berdasarkan penjualan terbanyak. Gunakan rumus AGGREGATE untuk mencari nilai maksimum dari penjualan terbanyak. Rumusnya adalah sebagai berikut:

=IFERROR(AGGREGATE(14;6;$E$5:$E$22/($C$5:$C$22=G5);1);"")

Rumus ini akan menghitung penjualan terbanyak untuk setiap produk (kolom G) dan mengembalikan nilai maksimum, yang menunjukkan sales terbaik.

Copy dan paste rumus tersebut ke sel yang ada di bawahnya. Dan berikut hasilnya:

Mencari Penjualan Terbanyak

Langkah berikutnya untuk cara menghitung total omzet ini adalah mencari jumlah penjualan terbanyak dari nama produk yang ada di sel G5. Rumus Excel untuk mencari jumlah penjualan terbanyak di sel I5 adalah:

=IFERROR(AGGREGATE(14;6;$E$5:$E$22/($C$5:$C$22=G5);1);"")

Copy dan paste rumus tersebut ke sel di bawahnya. Dan berikut adalah hasilnya:

Penjelasan Rumus

Formula Excel di atas adalah formula yang bekerja dengan array, tetapi bukan formula array Excel yang sebenarnya. Ini adalah formula array dalam arti bahwa ia memanipulasi array nilai (hasil dari kondisi yang diperiksa) untuk menghasilkan nilai tunggal.

Mari kita jelaskan rumus yang digunakan untuk cara menghitung total omzet ini:

1. ($C$5:$C$22=G5): Bagian ini menghasilkan array TRUE/FALSE yang menunjukkan apakah nilai di kolom C (produk terjual) sama dengan nilai yang ada di sel G5.
2. $E$5:$E$22/($C$5:$C$22=G5): Bagian ini menghasilkan array yang berisi nilai-nilai dari kolom E (total unit terjual) yang terbagi berdasarkan kondisi yang diperiksa di langkah pertama. Ini akan memberikan nilai total unit terjual hanya untuk produk yang sama dengan produk yang sedang diproses.
3. AGGREGATE(14;6;...;1): Fungsi AGGREGATE digunakan di sini dengan parameter sebagai berikut:

• 14 menunjukkan fungsi AGGREGATE yang digunakan adalah fungsi MAX, yang akan mengembalikan nilai maksimum dari array.
• 6 menunjukkan opsi untuk mengabaikan nilai-nilai dalam array yang berisi teks atau nilai kesalahan.
• ... adalah array hasil dari langkah kedua, yaitu total unit terjual untuk produk yang sama dengan produk yang sedang diproses.
• 1 menunjukkan bahwa kita ingin mengembalikan nilai maksimum dari array.

4. IFERROR(AGGREGATE(...);""): Bagian ini menangani kesalahan yang mungkin terjadi saat menggunakan fungsi AGGREGATE. Jika ada kesalahan, seperti jika tidak ada penjualan untuk produk tertentu, formula akan mengembalikan teks kosong (“”).

Jadi, secara keseluruhan, formula Excel ini bekerja untuk menghitung jumlah penjualan terbanyak berdasarkan produk tertentu dengan menggunakan fungsi AGGREGATE untuk mencari nilai maksimum dari total unit terjual yang memenuhi kondisi yang diberikan.

Mengidentifikasi Salesman Terkait Penjualan Terbanyak

Terakhir, kita ingin mengetahui nama salesman yang terkait dengan penjualan terbanyak untuk setiap produk. Gunakan rumus INDEX MATCH array untuk mencocokkan penjualan terbanyak dan produk dengan nama salesman yang sesuai. Rumusnya di sel J5 adalah sebagai berikut:

=IFERROR(INDEX($D$5:$D$22;MATCH(I5&G5;$E$5:$E$22&$C$5:$C$22;0));"")

INgat ya, ini array formula. Jadi akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (tidak hanya Enter).

Rumus ini akan mencocokkan penjualan terbanyak dan produk pada baris tertentu dengan nama salesman yang sesuai dari Tabel Data Penjualan. Copy dan paste rumus tersebut ke sel yang ada di bawahnya. Dan berikut hasilnya:

Penjelasan Rumus

Formula array Excel ini digunakan untuk mengambil data salesman yang terkait dengan penjualan terbanyak untuk setiap produk dari tabel data penjualan. Mari kita jelaskan bagaimana formula ini bekerja:

1. I5&G5: Bagian ini menggabungkan nilai dari sel I5 (penjualan terbanyak untuk produk tertentu) dengan nilai dari sel G5 (nama produk). Misalnya, jika penjualan terbanyak untuk produk A adalah 100 dan produk A adalah “X”, maka hasil dari gabungan ini adalah “100X”.
2. $E$5:$E$22&$C$5:$C$22: Bagian ini menggabungkan dua array, yaitu kolom E (total unit terjual) dan kolom C (nama produk), untuk seluruh rentang data. Ini akan menghasilkan array yang berisi kombinasi total unit terjual dan nama produk dari setiap baris data penjualan.
3. MATCH(I5&G5;$E$5:$E$22&$C$5:$C$22;0): Fungsi MATCH digunakan untuk mencari posisi pertama di mana hasil dari gabungan penjualan terbanyak dan nama produk yang sedang diproses cocok dengan kombinasi yang ada dalam array yang telah dibuat sebelumnya. Ini mengembalikan posisi baris yang sesuai dalam rentang data.
4. INDEX($D$5:$D$22;...): Fungsi INDEX kemudian digunakan untuk mengambil data salesman dari kolom D berdasarkan hasil pencarian MATCH. Jadi, kita mendapatkan nama salesman yang terkait dengan penjualan terbanyak dan produk yang sedang diproses.
5. {=IFERROR(...;"")}: Tanda kurung kurawal mengindikasikan bahwa ini adalah formula array Excel. Bagian ini menangani kesalahan yang mungkin terjadi saat menggunakan fungsi INDEX MATCH array. Jika tidak ada kesalahan, formula akan mengembalikan nama salesman yang terkait dengan penjualan terbanyak untuk produk tertentu. Jika terjadi kesalahan, seperti jika tidak ada penjualan untuk produk tertentu, formula akan mengembalikan teks kosong (“”).

Jadi, secara keseluruhan, formula array Excel ini bekerja untuk mengambil data salesman yang terkait dengan penjualan terbanyak untuk setiap produk dengan menggabungkan nilai penjualan terbanyak dan nama produk, mencocokkannya dengan data penjualan, dan mengembalikan nama salesman yang sesuai.

Kesimpulan

Dengan menggunakan rumus-rumus Excel yang tepat, Anda dapat dengan mudah menghasilkan laporan rekap penjualan yang memberikan insight tentang total omzet produk dan mengidentifikasi sales terbaik. Dengan memahami langkah-langkah cara menghitung total omzet ini adalah langkah awal yang penting dalam mengoptimalkan strategi penjualan Anda dan meningkatkan performa bisnis Anda secara keseluruhan. Mulailah eksplorasi Anda dengan Excel hari ini dan lihatlah bagaimana Anda dapat mengambil keputusan yang lebih baik berdasarkan data penjualan yang akurat dan informatif.

Pranala Luar

File Excel Cara Menghitung Total Omzet dan Menemukan Salesman Terbaik 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. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!