Membuat Rekapitulasi Omset Bulanan atau Juga Rentang Tanggal di Excel

Dalam mengelola bisnis dengan beberapa cabang, memantau dan menganalisis omset per cabang menjadi langkah penting untuk pengambilan keputusan yang lebih baik. Excel menyediakan alat yang sangat efektif untuk membuat laporan rekapitulasi omset bulanan atau bisa juga berdasarkan rentang tanggal.

Artikel ini akan membahas langkah-langkah cara membuat laporan rekapitulasi omset per cabang dengan Excel berdasarkan rentang tanggal yang dipilih yang memungkinkan pemilik bisnis untuk mendapatkan wawasan yang lebih mendalam. Tapi tools ini bisa juga digunakan untuk rekap omset per bulan jika memang dirasa rekap omset per bulan lebih efektif.

Langkah 1: Persiapkan Data Rekapitulasi Omset Bulanan

Mulailah dengan menyiapkan data penjualan harian gabungan dari setiap cabang. Data ini harus mencakup kolom seperti Tanggal Transaksi, Nama Cabang, dan Jumlah Omset. Berikut adalah contoh tabel untuk rekap omset per bulan tersebut:

Langkah 2: Buat Tabel Dinamis

Gunakan fitur Tabel Dinamis di Excel untuk memudahkan pengelolaan dan analisis data. Pilih range tabel data Anda, lalu pilih “Insert” > “Table” atau gunakan pintasan Ctrl + T. Dan inilah hasilnya:

Sampai sini sebenarnya sudah selesai. Ketika Anda membutuhkan rekapitulasi omset bulanan dari cabang tertentu atau juga rentang tanggal berapa ke berapa maka Anda hanya perlu klik “filter” di header kolom tanggal dan kotanya dan data omset pun akan tertampil yang bisa Anda jumlahkan kemudian.

Tapi bagaimana jika antara tabel “Data Penjualan Seluruh Kota” dan “Rakap Penjualan” tersebut berada di sheet berbeda atau bahkan workbook berbeda? Di sini Anda membutuhkan rumus Excel yang sayangnya lumayan kompleks, setidaknya itulah yang sementara bisa kami buat. Kita mulai…

Langkah 3: Filter Tanggal Transaksi dan Kota

Seperti yang ditulis si atas, kita akan membutuhkan rumus untuk mengambil data omset di kota “Jakarta” (seperti pada contoh) dengan rentang tanggal transaksi dari tanggal 01/03/2019 sampai dengan 31/12/2019, maka rumus atau formula rekapitulasi omset bulanan yang bisa digunakan adalah formula array yang ditulis sekaligus di banyak kolom.

Caranya blok range F8:F18, kemudian di formula bar Anda ketik formula berikut:

=IFERROR(INDEX(B$5:B$22;SMALL(IF(($B$5:$B$22>=$F$5)*($B$5:$B$22<=$G$5)*($C5:$C22=$H$5);ROW(1:18)-MIN(ROW(1:18))+1);ROW(1:18)));"")

Jika sudah, akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (jangan cuma Enter). Kurung kurawal {} akan muncul secara otomatis jika langkah ini dilakukan dengan benar. Dan ketika tanggal yang terfilter berdasarkan kriteria sudah muncul maka langkah berikutnya adalah copy formula array tersebut dan paste di range G8:G18 dan H8:H18. Atau bisa juga dengan cara arahkan kursor ke pojok sel F18 kemudian ketika muncul tanda “+” klik kemudian seret ke samping hingga sel H18.

Jika benar maka berikut adalah hasil akhirnya:

Penjelasan Formula Array

Formula array untuk rekapitulasi omset bulanan ini memiliki tujuan untuk mengekstrak nilai dari rentang data (kolom B) berdasarkan tiga kriteria tertentu yang diberikan pada kolom C, F, dan G. Mari kita breakdown formula tersebut secara detail:

1. IFERROR: Ini adalah fungsi yang digunakan untuk menangani kesalahan. Jika ada kesalahan dalam eksekusi formula array di dalamnya, IFERROR akan menggantikan nilai kesalahan dengan nilai yang ditentukan, dalam hal ini, menggunakan string kosong (“”).
2. INDEX(B$5:B$22, ...): Fungsi INDEX digunakan untuk mengambil nilai dari kolom B pada baris tertentu. Dalam konteks ini, INDEX(B$5:B$22, …) akan mengambil nilai dari rentang B5:B22 berdasarkan hasil dari fungsi yang berada di dalam kurung kurawal.
3. SMALL(IF(($B$5:$B$22>=$F$5)*($B$5:$B$22<=$G$5)*($C5:$C22=$H$5), ROW(1:18)-MIN(ROW(1:18))+1), ROW(1:18)): Bagian ini adalah inti dari formula array dan digunakan untuk menghasilkan baris-baris yang memenuhi kriteria tertentu. Berikut adalah penjelasan detailnya:

• ($B$5:$B$22>=$F$5)*($B$5:$B$22<=$G$5)*($C5:$C22=$H$5): Ini adalah bagian yang mengevaluasi kriteria. Pada setiap baris, ini menghasilkan TRUE jika nilai di kolom B berada di antara F5 dan G5, dan nilai di kolom C sama dengan H5.
• ROW(1:18)-MIN(ROW(1:18))+1: Ini memberikan nomor baris relatif untuk setiap baris yang memenuhi kriteria.
• IF(..., ROW(1:18)): Ini menggunakan fungsi IF untuk menghasilkan baris-baris yang memenuhi kriteria. Jika kriteria terpenuhi, maka ini akan memberikan nomor baris; jika tidak, akan memberikan nilai FALSE.
• SMALL(..., ROW(1:18)): Fungsi SMALL digunakan untuk mengurutkan dan mengambil nilai terkecil. Dalam hal ini, kita mengambil nilai terkecil (baris terkecil) yang memenuhi kriteria.

1. IFERROR(..., ""): Seperti yang telah disebutkan di awal, IFERROR digunakan untuk menangani kesalahan. Jika ada kesalahan dalam eksekusi formula array, maka ini akan menggantikan nilai kesalahan dengan string kosong (“”).

Perlu dicatat bahwa formula ini merupakan formula array, dan pengguna harus menekan Ctrl+Shift+Enter setelah menuliskannya, bukan Enter saja. Kurung kurawal {} akan muncul secara otomatis jika langkah ini dilakukan dengan benar.

Dengan menggunakan rumus ini, kita dapat mengekstrak nilai dari kolom B yang memenuhi tiga kriteria yang diberikan.

Langkah 4: Analisis dan Visualisasi

Langkah terakhir dari rekap omset per bulan ini, gunakan alat analisis dan visualisasi yang tersedia di Excel, seperti grafik batang atau grafik lingkaran, untuk memperjelas rekapitulasi omset per cabang. Hal ini dapat membantu Anda melihat tren dan perbandingan dengan lebih mudah.

Kesimpulan

Membuat laporan rekapitulasi omset bulanan per cabang dengan Excel dapat memberikan wawasan yang berharga untuk pengambilan keputusan bisnis. Dengan mengikuti langkah-langkah di atas, Anda dapat mengelola dan menganalisis data penjualan dengan lebih efisien, memungkinkan pemilik bisnis untuk fokus pada area yang perlu perhatian khusus dan meningkatkan performa cabang-cabang tertentu. Excel menjadi alat yang sangat berguna dalam menyusun laporan ini tanpa perlu keahlian pemrograman atau perangkat lunak khusus.

Download

Terakhir, barangkali ada yang membutuhkan file Excel untuk Membuat Rekapitulasi Omset Bulanan atau Juga Rentang Tanggal di Excel seperti contoh di atas, file tersebut bisa Anda unduh melalui 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!