Cara Mudah dan Cepat Menjumlah Omset Antar Divisi di Excel dan Menghitung Persentasenya

Dalam sebuah perusahaan dengan beberapa divisi atau departemen, mengelola dan menganalisis omset setiap divisi sangat penting. Hal ini membantu manajemen dalam pengambilan keputusan yang lebih baik. Dalam artikel ini, kita akan membahas cara mudah untuk menjumlah omset antar divisi di Excel dan juga membuat persentasenya menggunakan rumus Microsoft Excel.

Persiapkan Data untuk Menjumlah Omset Antar Divisi di Excel

Sebelum memulai menjumlah omset antar divisi di Excel, pastikan Anda memiliki data omset yang terorganisir. Buatlah tabel dengan kolom-kolom sebagai berikut:

Hitung Total Penjualan Antar Divisi

Gunakan fungsi SUMIFS dan SUBSTITUTE untuk menjumlah omset antar divisi di Excel dari masing-masing sales. Dan untuk keperluan tersebut gunakan rumus berikut di sel I4:

=IF(ISBLANK(H4);SUMIFS($E$4:$E$19;$D$4:$D$19;SUBSTITUTE(G4;"JUMLAH OMSET ";""));SUMIFS($E$4:$E$19;$C$4:$C$19;H4))

Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Rumus Excel yang Anda berikan adalah rumus yang menggunakan fungsi IF, ISBLANK, SUMIFS, dan SUBSTITUTE. Mari kita bahas setiap bagian dari rumus tersebut secara detail:

1. Fungsi ISBLANK(H4)

Fungsi ini digunakan untuk mengecek apakah sel H4 kosong atau tidak. Jika sel H4 kosong, fungsi ini mengembalikan nilai TRUE, dan jika tidak kosong, mengembalikan nilai FALSE.

2. Fungsi SUBSTITUTE(G4; "JUMLAH OMSET "; "")

Fungsi ini digunakan untuk mengganti teks “JUMLAH OMSET ” di dalam sel G4 dengan string kosong (“”). Fungsi ini berguna jika teks “JUMLAH OMSET ” harus dihapus agar sisa teks dapat digunakan dalam rumus.

3. Fungsi SUMIFS($E$4:$E$19; $D$4:$D$19; SUBSTITUTE(G4; "JUMLAH OMSET "; ""))

Fungsi SUMIFS digunakan untuk menjumlahkan sel-sel di dalam rentang E4:E19 yang memenuhi kriteria tertentu. Dalam hal ini, fungsi ini menjumlahkan nilai di kolom E yang sesuai dengan dua kriteria:

• Nilai di kolom D (tanggal) harus sesuai dengan nilai di sel G4 setelah penggantian teks “JUMLAH OMSET ” dihapus.
• Jika sel H4 kosong (berdasarkan hasil dari fungsi ISBLANK), maka fungsi ini akan mengembalikan total omset dari tanggal tertentu.
• Jika sel H4 tidak kosong, fungsi ini akan menggunakan nilai di H4 sebagai kriteria tambahan, sehingga hanya jumlah omset yang sesuai dengan tanggal dan nama divisi yang diberikan yang akan dijumlahkan.

4. Fungsi SUMIFS($E$4:$E$19; $C$4:$C$19; H4)

Fungsi ini serupa dengan fungsi sebelumnya, namun kali ini menggunakan nilai di sel H4 sebagai kriteria tanpa memeriksa apakah sel H4 kosong atau tidak. Ini akan menghitung jumlah omset untuk divisi yang sesuai dengan nilai di H4.

5. Fungsi IF(ISBLANK(H4); ...; ...)

Fungsi utama ini adalah fungsi IF, yang digunakan untuk mengevaluasi apakah sel H4 kosong atau tidak. Jika sel H4 kosong, maka rumus akan menggunakan hasil dari fungsi SUMIFS yang pertama, dan jika tidak kosong, maka akan menggunakan hasil dari fungsi SUMIFS yang kedua.

Dengan demikian, rumus ini memberikan fleksibilitas untuk menghitung jumlah omset berdasarkan dua kriteria (tanggal dan divisi), dengan mempertimbangkan apakah divisi yang diinginkan telah dimasukkan di sel H4 atau tidak.

Hitung Persentase Total

Untuk menghitung persentase total omset di range J4:J16 bisa gunakan rumus Excel sederhana sebagai berikut:

=I4/$I$16

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

Hitung Persentase Per Divisi

Untuk menghitung persentase per divisi di range K4:K16, rumus Excel yang digunakan di sel K4 adalah sebagai berikut:

=I4/INDEX(I4:$I$16;MATCH(TRUE;INDEX(ISBLANK(H4:$H$16););0))

Copy-paste rumus di atas ke sel di atasnya hingga ke sel K16. Dan berikut adalah hasilnya:

Penjelasan Rumus

Rumus Excel yang Anda berikan melibatkan beberapa fungsi seperti INDEX, MATCH, ISBLANK, dan operasi pembagian (/). Mari kita bahas setiap bagian rumus secara detail:

1. INDEX(I4:$I$16;MATCH(TRUE;INDEX(ISBLANK(H4:$H$16););0))

• ISBLANK(H4:$H$16): Fungsi ini menghasilkan larik (array) boolean yang menunjukkan apakah sel dalam rentang H4:H16 kosong atau tidak.
• INDEX(ISBLANK(H4:$H$16);): Fungsi INDEX digunakan untuk mengonversi larik boolean tersebut menjadi larik yang dapat diakses oleh fungsi MATCH.
• MATCH(TRUE; ... ;0): Fungsi MATCH digunakan untuk mencari posisi pertama (indeks) di mana nilai TRUE ditemukan dalam larik hasil dari INDEX(ISBLANK(H4:$H$16);).
• INDEX(I4:$I$16; ... ): Fungsi INDEX digunakan untuk mengambil nilai dari sel dalam rentang I4:I16 pada posisi yang ditemukan oleh fungsi MATCH.

Jadi, keseluruhan bagian ini menghasilkan nilai dalam rentang I4:I16 yang sesuai dengan posisi pertama di mana sel dalam rentang H4:H16 yang kosong.

2. I4/...

Ini adalah bagian dari rumus yang membagi nilai di sel I4 dengan nilai yang dihasilkan dari langkah sebelumnya. Ini mungkin dimaksudkan untuk menghitung rasio atau perbandingan antara dua nilai tersebut.

Hitung Persentase Per Kolom

Untuk menghitung persentase per kolom di range L4:L16, rumus Excel yang digunakan di sel L4 adalah sebagai berikut:

=IF(OR(ISBLANK(H4);H4="JUMLAH TOTAL");J4;K4)

Copy-paste rumus di atas ke sel di atasnya hingga ke sel L16. Dan berikut adalah hasilnya:

Penjelasan Rumus

Rumus Excel ini adalah rumus yang menggunakan fungsi IF, OR, dan ISBLANK. Mari kita bahas setiap bagian rumus secara detail:

1. ISBLANK(H4): Fungsi ini menghasilkan nilai TRUE jika sel H4 kosong dan FALSE jika tidak.
2. H4="JUMLAH TOTAL": Fungsi ini menghasilkan nilai TRUE jika nilai di sel H4 sama dengan teks “JUMLAH TOTAL” dan FALSE jika tidak.
3. OR(ISBLANK(H4), H4="JUMLAH TOTAL"): Fungsi OR menghasilkan nilai TRUE jika setidaknya satu dari kondisi yang diuji menghasilkan nilai TRUE. Dalam hal ini, itu akan menghasilkan TRUE jika sel H4 kosong atau jika nilainya adalah “JUMLAH TOTAL”.
4. IF(OR(ISBLANK(H4), H4="JUMLAH TOTAL"), J4, K4): Fungsi IF digunakan untuk membuat keputusan berdasarkan kondisi yang diuji oleh fungsi OR.

• Jika kondisi tersebut benar (TRUE), maka rumus akan mengembalikan nilai dari sel J4.
• Jika kondisi tersebut salah (FALSE), maka rumus akan mengembalikan nilai dari sel K4.

Rumus ini berfungsi sebagai kondisional statement, dimana jika sel H4 kosong atau berisi teks “JUMLAH TOTAL”, maka nilai yang diambil dari sel J4 akan menjadi hasilnya; sebaliknya, jika tidak memenuhi kedua kondisi tersebut, maka nilai yang diambil dari sel K4 akan menjadi hasilnya. Rumus ini memberikan fleksibilitas untuk menentukan nilai yang akan diambil berdasarkan kondisi tertentu pada sel H4.

Format Angka dan Persentase

Pastikan untuk memformat angka-angka omset dan persentase agar lebih mudah dibaca. Pilih sel yang berisi angka dan gunakan opsi di bagian atas Excel untuk mengatur format angka atau persentase.

Update Data Secara Berkala

Selalu perbarui data Anda setiap bulan untuk mendapatkan analisis yang akurat. Gunakan teknik copy-paste atau sumber data eksternal jika diperlukan.

Kesimpulan

Dengan mengikuti langkah-langkah ini, Anda dapat dengan mudah menjumlah omset antar divisi di Excel dan membuat persentasenya di Microsoft Excel. Analisis ini akan memberikan wawasan yang berharga kepada manajemen dalam membuat keputusan bisnis yang lebih baik. Selalu pantau dan evaluasi performa divisi secara teratur untuk mengidentifikasi peluang dan tantangan yang mungkin muncul.

Terakhir, bagi yang membutuhkan file contoh Cara Mudah dan Cepat Menjumlah Omset Antar Divisi di Excel dan Menghitung Persentasenya di atas, file Excel bisa didownload melalui tautan berikut: