Panduan Lengkap Merekap Omset Penjualan dari Tiap Cabang dengan Microsoft Excel

oleh | 18 Des 2023 | Belajar Excel, Excel Elementary, Excel Intermediate | 0 Komentar

Dalam dunia bisnis, pemantauan dan juga merekap omset penjualan dari tiap cabang merupakan hal yang krusial untuk keberlanjutan dan pengembangan perusahaan. Microsoft Excel menjadi salah satu alat yang sangat berguna untuk merekap dan menganalisis data penjualan secara efisien. Artikel ini akan membahas langkah-langkah cara merekap omset penjualan dari tiap cabang menggunakan Microsoft Excel.

Membuat Tabel untuk Merekap Omset Penjualan

Langkah pertama adalah membuat tabel data penjualan yang menampilkan produk terjual dan omset penjualan dari tiap cabang. Buat kolom-kolom seperti Nama Cabang, Produk Terjual dan Total Omset.

tabel awal untuk merekap omset penjualan dari tiap cabang

Dan yang akan kita lakukan adalah mengambil data-data tersebut ke tabel terpisah yang khusus untuk menampilkan data per cabangnya. Nah untuk data penjualan di range F5:F9 dan G5:G9 kita akan gunakan formula array yang ditulis sekaligus dalam banyak sel.

Mengambil Nama Cabang dengan Dropdown List

Sebelum kita mengambil data penjualan dan total omsetnya, hal pertama yang harus kita lakukan adalah mencantumkan nama cabangnya. Dan untuk memastikan bahwa tidak ada typo dalam penulisan kita akan menggunakan dropdown list dari Data Validation.

Caranya adalah klik tab Data kemudian pilih Data Validation. Jika sudah, setting data validationnya seperti pada gambar di bawah ini:

data validasi penjualan

Mengambil Data Penjualan

Selanjutnya, kita perlu mengambil data produk yang terjual untuk cabang tertentu yang dalam hal ini kita contohkan untuk cabang Jakarta. Gunakan formula array yang kita tulis untuk beberapa sel sekaligus. Caranya blok range F5:F9 kemudian arahkan kursor ke formula bar dan tulis formula berikut:

=INDEX($B$3:$D$17;SMALL(IF(B3:D17=G2;ROW(1:15);"");ROW(1:15));2)

Dan karena ini formula array, maka setelah selesai menulis rumusnya akhiri penulisan dengan menekan Ctrl+Shift dan Enter (tidak hanya enter) dan hasilnya adalah tanda kurung kurawal yang otomatis muncul mengapit rumusnya. Berikut adalah hasilnya:

Baca Juga  Cara Menghitung Nilai Ijazah Sesuai Juknis dengan Rumus Excel
data produk terjual di cabang
Penjelasan Formula

Formula array ini sederhananya digunakan untuk mengambil nilai dari sel dalam rentang data tertentu berdasarkan kriteria tertentu. Mari kita bahas setiap bagian dari formula ini secara detail:

  1. INDEX($B$3:$D$17, ... , 2): Fungsi INDEX digunakan untuk mengambil nilai dari rentang sel ($B$3:$D$17). Parameter pertama adalah rentang sel, dan parameter kedua (dalam hal ini, 2) adalah nomor kolom di mana nilai akan diambil.
  2. SMALL(IF(B3:D17=G2, ROW(1:15), ""), ROW(1:15)): Fungsi SMALL digunakan untuk mengambil nilai terkecil dari larik angka. Bagian dalam kurung kurawal pertama adalah larik angka yang akan diurutkan, dan bagian dalam kurung kurawal kedua adalah peringkat kecil yang ingin diambil.
  • IF(B3:D17=G2, ROW(1:15), ""): Fungsi IF digunakan untuk menguji apakah nilai dalam rentang B3:D17 sama dengan nilai dalam sel G2 (mungkin merupakan nama cabang). Jika benar, maka ROW(1:15) memberikan nomor baris terkait; jika salah, maka diberikan string kosong (“”). Hasilnya adalah larik angka baris yang sesuai dengan kriteria.
  • ROW(1:15): Ini memberikan larik angka dari 1 hingga 15 (sesuaikan dengan ukuran rentang data Anda). Digunakan untuk memberikan peringkat kecil yang diinginkan oleh fungsi SMALL.
  • SMALL(..., ROW(1:15)): Mengambil nilai terkecil dari larik angka yang dihasilkan oleh fungsi IF, sesuai dengan peringkat kecil dari ROW(1:15).

3. {=INDEX(..., SMALL(..., ROW(1:15)), 2)}: Dengan menggabungkan fungsi INDEX dan SMALL, kita mendapatkan nilai dari sel yang sesuai dengan kriteria dan peringkat kecil yang diinginkan.

Jadi, secara keseluruhan, formula ini digunakan untuk mengambil nilai dari kolom ke-2 (parameter kedua dalam fungsi INDEX) dari rentang data $B$3:$D$17 yang memenuhi kriteria yang diberikan (nilai dalam rentang B3:D17 sama dengan nilai dalam sel G2, yang mungkin berisi nama cabang). Formula ini berguna untuk merekap penjualan di tiap cabang dengan mengambil nilai terkecil yang memenuhi kriteria tersebut.

Baca Juga  Cara Mudah Memahami Fungsi Logika Excel untuk Optimalisasi Pengambilan Keputusan

Menghitung Omset Tiap Cabang

Selanjutnya, kita perlu menghitung total omset di cabang Jakarta. Gunakan langkah yang sama seperti di langkah mengambil data penjualan yaitu blok range G5:G9 kemudian di formula bar tulis rumus yang sama dengan formula array di atas hanya saja untuk column_num-nya digeser dari 2 ke 3 untuk mengambil total penjualan berdasarkan nama cabang.

=INDEX($B$3:$D$17;SMALL(IF(B3:D17=G2;ROW(1:15);"");ROW(1:15));3)

Sekali lagi, ini formula array, maka setelah selesai menulis rumusnya akhiri penulisan dengan menekan Ctrl+Shift dan Enter (tidak hanya enter) dan hasilnya adalah tanda kurung kurawal yang otomatis muncul mengapit rumusnya. Berikut adalah hasilnya:

data omset di cabang

Membuat Grafik Visualisasi Data

Visualisasi data dapat membantu Anda memahami pola penjualan dan omset dengan lebih baik. Gunakan grafik batang atau grafik lingkaran untuk menampilkan perbandingan omset antar cabang. Sebagai contoh, kita buat visual grafik sederhana berikut:

hasil akhir merekap omset penjualan dari tiap cabang

Analisis Data dengan PivotTable

PivotTable adalah fitur hebat Excel yang memungkinkan Anda menganalisis data dengan cepat. Gunakan PivotTable untuk merekap omset penjualan berdasarkan cabang, produk, atau periode waktu tertentu.

Membuat Laporan Keuangan Cabang

Jangan lupa membuat laporan keuangan yang menyajikan rincian pendapatan, biaya, dan keuntungan untuk setiap cabang. Anda dapat menggunakan fungsi-fungsi Excel seperti SUM dan VLOOKUP untuk membuat laporan yang informatif.

Menerapkan Filter dan Sortir

Penerapan filter dan sortir akan memudahkan Anda dalam mengeksplorasi data penjualan. Anda dapat menyaring data berdasarkan tanggal, cabang, atau produk tertentu untuk mendapatkan wawasan yang lebih mendalam.

Kesimpulan

Dengan mengikuti langkah-langkah di atas, Anda dapat membuat sistem pelacakan penjualan dan omset yang efektif menggunakan Microsoft Excel. Menganalisis data dengan teliti akan memberikan wawasan berharga untuk pengambilan keputusan bisnis yang lebih baik dan pertumbuhan yang berkelanjutan.

Baca Juga  Tawaran Terbatas!! Paket Belajar Microsoft Excel

Terakhir bagi yang ingin memiliki file contoh Panduan Lengkap Merekap Omset Penjualan dari Tiap Cabang dengan Microsoft Excel di atas, file Excel bisa didownload melalui tautan berikut ini:

0 Komentar

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter