Cara Membuat Rekapitulasi Omzet dan Analisis Pareto dengan Rumus Excel

oleh | 8 Apr 2024 | Belajar Excel, Excel Advanced, Excel Intermediate, Tips Excel | 0 Komentar

Dalam dunia bisnis, efisiensi dalam mengelola data penjualan dan menganalisis kontribusi produk atau pelanggan terhadap omzet sangatlah penting. Artikel ini akan membantu Anda mempelajari cara membuat rekapitulasi omzet penjualan secara otomatis dan melakukan analisis Pareto dengan rumus Excel yang informatif. Kombinasi fungsi SUMIFS untuk menghitung total omzet penjualan dan fungsi INDEX MATCH untuk analisis Pareto akan menjadi fokus utama dalam pembahasan kali ini.

Mengapa Analisis Pareto Penting dalam Bisnis?

Sebelum lebih jauh, penting untuk memahami konsep Pareto dalam bisnis. Prinsip Pareto, juga dikenal sebagai aturan 80/20, menyatakan bahwa sekitar 80% hasil atau kontribusi seringkali berasal dari 20% usaha atau penyebab. Dalam konteks penjualan, ini berarti sebagian kecil produk atau pelanggan dapat memberikan sebagian besar omzet. Dengan menerapkan analisis Pareto, Anda dapat mengidentifikasi area-area yang memberikan dampak terbesar pada kinerja penjualan Anda.

Langkah-langkah Membuat Rekapitulasi Omzet Penjualan di Excel

Persiapkan Data Penjualan

Mulailah dengan mengumpulkan data penjualan Anda dalam spreadsheet Excel. Pastikan data tersebut mencakup informasi seperti nama produk, jumlah terjual, harga satuan, dan total omzet.

tabel pareto

Gunakan Formula Array untuk Filter Produk

Langkah berikutnya setelah kita memiliki tabel di atas maka kita akan memfilter data produk tersebut kolom produk di range G5:G14. Dan formula array yang bisa digunakan adalah:

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

Dan karena ini adalah array formula maka akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (jangan hanya Enter). Jika benar maka akan muncul kurung kurawal yang mengapit rumusnya. Berikut hasilnya:

rekap produk di excel
Penjelasan Rumus

Formula array ini memiliki beberapa komponen yang harus dijelaskan secara terpisah:

1. COUNTIF(G$4:G4;$B$5:$B$25):

  • Ini adalah fungsi COUNTIF yang digunakan untuk menghitung berapa kali nilai dalam rentang G$4:G4 muncul dalam rentang $B$5:$B$25.
  • Fungsi ini mengembalikan jumlah kemunculan nilai-nilai dari rentang G$4:G4 di dalam rentang $B$5:$B$25.

2. SUM(…):

  • Ini adalah fungsi SUM yang digunakan untuk menjumlahkan hasil dari fungsi COUNTIF sebelumnya.
  • Fungsi ini mengembalikan total jumlah kemunculan nilai-nilai dari rentang G$4:G4 di dalam rentang $B$5:$B$25.

3. COUNTIF($B$5:$B$25;”<” & $B$5:$B$25):

  • Ini adalah fungsi COUNTIF yang digunakan untuk menghitung berapa kali nilai dalam rentang $B$5:$B$25 lebih kecil dari nilai-nilai dalam rentang itu sendiri.
  • Fungsi ini mengembalikan jumlah nilai-nilai yang lebih kecil dari nilai-nilai dalam rentang $B$5:$B$25.
Baca Juga  Cara Mudah Menghitung Selisih Waktu di Excel dengan Fungsi DATEDIF

4. MATCH(…):

  • Ini adalah fungsi MATCH yang digunakan untuk mencari posisi relatif dari suatu nilai dalam sebuah array.
  • Argumen pertamanya adalah hasil dari fungsi SUM dan COUNTIF sebelumnya, yaitu jumlah kemunculan nilai-nilai dari rentang G$4:G4 di dalam rentang $B$5:$B$25.
  • Argumen kedua adalah hasil dari fungsi COUNTIF yang menghitung nilai-nilai yang lebih kecil dari nilai-nilai dalam rentang $B$5:$B$25.
  • Argumen ketiganya adalah 0, yang menunjukkan pencarian yang tepat.

5. INDEX($B$5:$B$25;…):

  • Ini adalah fungsi INDEX yang digunakan untuk mengambil nilai dari rentang $B$5:$B$25 berdasarkan posisi yang ditemukan oleh fungsi MATCH sebelumnya.

6. IFERROR(…):

  • Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan.
  • Argumen pertamanya adalah hasil dari fungsi INDEX, yang mengambil nilai dari rentang $B$5:$B$25 sesuai dengan posisi yang ditemukan oleh fungsi MATCH.
  • Argumen kedua adalah string kosong (“”) yang akan dikembalikan jika terjadi kesalahan.

Jadi, secara keseluruhan, formula array ini digunakan untuk mencari dan mengambil nilai dari rentang $B$5:$B$25 berdasarkan jumlah kemunculan nilai-nilai tertentu dari rentang G$4:G4 di dalam rentang $B$5:$B$25. Jika tidak ada nilai yang sesuai, formula akan mengembalikan string kosong.

Gunakan Rumus SUMIFS untuk Menjumlah Omset

Dalam contoh ini, kita akan belajar menggunakan rumus SUMIFS untuk menghitung total omzet penjualan secara otomatis, baik jumlahnya maupun total omsetnya. Rumus ini sangat berguna karena dapat menjumlah data dengan kriteria lebih dari satu.

Dan berikut rumus SUMIFS di sel H5:

=SUMIF($B$5:$B$25;G5;$D$5:$D$25)

Dan berikut hasilnya:

rekap kuantitas excel

Sementara untuk Total Penjualan di sel H5 adalah:

=SUMIF($B$5:$B$25;G5;$E$5:$E$25)

Dan berikut hasilnya:

total omset penjualan excel

Mengenal Analisis Pareto dengan Rumus Excel

Setelah berhasil membuat rekapitulasi omzet, langkah berikutnya adalah menerapkan analisis Pareto untuk mengidentifikasi kontribusi terbesar pada omzet penjualan Anda. Di sini, kita akan menggunakan rumus INDEX MATCH juga, yang memungkinkan kita untuk mencari nilai dalam rentang data berdasarkan kriteria tertentu.

Mengurutkan Qty Penjualan dari Terbesar ke Terkecil

Hal pertama yang perlu kita lakukan untuk membuat analisis pareto dengan rumus Excel ini adalah kita akan mengambil dan mengurutkan kuantitas penjualan dari range H5:H14 dari yang terbesar ke yang terkecil. Rumus yang bisa digunakan di sel H19 adalah:

=IF(LARGE($H$5:$H$16;ROW(A1))=0;"";LARGE($H$5:$H$16;ROW(A1)))

Copy dan paste rumusnya ke sel di bawahnya. Dan berikut hasilnya:

mencari qty terbesar di excel
Penjelasan Rumus

Mari kita bahas setiap bagian dari rumus membuat analisis pareto dengan rumus Excel ini:

1. LARGE($H$5:$H$16;ROW(A1)):

  • Fungsi LARGE digunakan untuk mengembalikan nilai terbesar dari rentang yang ditentukan.
  • $H$5:$H$16 adalah rentang dari mana nilai terbesar akan diambil.
  • ROW(A1) mengembalikan nomor baris dari baris pertama yang digunakan dalam rumus, dalam hal ini baris 1.
  • Jadi, fungsi ini mengambil nilai terbesar dari rentang $H$5:$H$16 berdasarkan nomor baris yang sedang dievaluasi.
Baca Juga  Cara Mudah Membuat Klasemen Sepak Bola di Excel dengan Rumus Sederhana

2. IF(…=0;””;LARGE($H$5:$H$16;ROW(A1))):

  • Fungsi IF digunakan untuk menguji suatu kondisi dan mengembalikan nilai alternatif berdasarkan hasil pengujian tersebut.
  • LARGE($H$5:$H$16;ROW(A1))=0 adalah pengujian apakah nilai terbesar dari rentang tersebut adalah 0.
  • Jika hasil pengujian adalah benar (nilai terbesar adalah 0), maka rumus akan mengembalikan string kosong (""), yang artinya tidak ada nilai.
  • Jika hasil pengujian adalah salah (nilai terbesar bukan 0), maka rumus akan mengembalikan nilai terbesar dari rentang tersebut.

Jadi, secara keseluruhan, rumus ini digunakan untuk mengambil nilai terbesar dari rentang $H$5:$H$16, namun jika nilai terbesarnya adalah 0, maka rumus akan mengembalikan string kosong (""). Rumus ini berguna dalam situasi di mana Anda ingin menampilkan nilai terbesar dari rentang tetapi tidak ingin menampilkan nilai 0 jika itu yang menjadi nilai terbesar seperti untuk membuat analisis pareto dengan rumus Excel ini.

Mengambil Total Penjualan

Berikut adalah rumus Excel untuk mengambil total penjualan di sel I19:

=IFERROR(INDEX($I$5:$I$14;MATCH(H19;$H$5:$H$14;0));"")

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

mengambil total penjualan
Penjelasan Rumus

Mari kita bahas secara lengkap setiap komponen dari rumus ini:

1. MATCH(H19;$H$5:$H$14;0):

  • Fungsi MATCH digunakan untuk mencari posisi relatif suatu nilai dalam sebuah rentang.
  • H19 adalah nilai yang akan dicari posisinya.
  • $H$5:$H$14 adalah rentang tempat pencarian dilakukan.
  • Argumen terakhir adalah 0, yang menandakan pencarian nilai yang persis sama dengan nilai yang dicari.
  • Fungsi ini mengembalikan posisi relatif dari nilai yang dicari dalam rentang pencarian.

2. INDEX($I$5:$I$14;…):

  • Fungsi INDEX digunakan untuk mengembalikan nilai dari sebuah array berdasarkan nomor baris atau kolom tertentu.
  • $I$5:$I$14 adalah rentang dari mana nilai akan diambil.
  • ... adalah hasil dari fungsi MATCH, yaitu posisi relatif dari nilai yang dicari dalam rentang pencarian.
  • Fungsi ini mengambil nilai dari rentang $I$5:$I$14 berdasarkan posisi yang ditemukan oleh fungsi MATCH.

3. IFERROR(…;””):

  • Fungsi IFERROR digunakan untuk menangani kesalahan dan mengembalikan nilai alternatif jika terjadi kesalahan.
  • ... adalah hasil dari fungsi INDEX dan MATCH.
  • "" adalah nilai alternatif yang akan dikembalikan jika terjadi kesalahan.

Jadi, rumus ini digunakan untuk mencari nilai yang sesuai dengan nilai di sel H19 dalam rentang kolom H ($H$5:$H$14), kemudian mengembalikan nilai tersebut dari rentang kolom I ($I$5:$I$14). Jika tidak ada nilai yang sesuai, maka rumus akan mengembalikan string kosong (""). Rumus ini berguna dalam pencarian nilai berdasarkan satu kriteria tertentu dalam sebuah array.

Baca Juga  Cara Mengambil 10 Besar Siswa Berdasarkan Total Nilai di Microsoft Excel

Mengambil Nama Produk

Rumus yang digunakan untuk mengambil nama produk hampir sama dengan rumus yang digunakan di pengambilan produk di rekap omset, hanya saja tak perlu lagi melakukan filter karena sudah terfilter di tabel awal. Yang dilakukan INDEX MATCH di sini hanya untuk mengambil data yang cocok dengan data QTY di sampingnya. Dan berikut adalah rumusnya:

=IFERROR(INDEX($G$5:$G$14;MATCH(H19&I19;$H$5:$H$14&$I$5:$I$14;0));"")

Dan seperti di awal ini juga array formula. Jadi akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter. Lalu copy dan paste ke sel yang ada di bawahnya. Dan berikut hasilnya:

analisis pareto dengan rumus Excel
Penjelasan Rumus

Mari kita bahas bagian-bagian dari rumus ini:

1. INDEX($G$5:$G$14;MATCH(H19&I19;$H$5:$H$14&$I$5:$I$14;0)):

  • Fungsi INDEX digunakan untuk mengembalikan nilai dari sebuah array berdasarkan nomor baris dan kolom tertentu.
  • $G$5:$G$14 adalah rentang dari mana nilai akan diambil.
  • MATCH(H19&I19;$H$5:$H$14&$I$5:$I$14;0) adalah fungsi pencocokan yang mencari posisi relatif dari nilai gabungan dari sel H19 dan I19 dalam rentang gabungan dari kolom H dan I di rentang $H$5:$H$14 dan $I$5:$I$14.
  • Jadi, fungsi INDEX akan mengambil nilai dari rentang $G$5:$G$14 berdasarkan posisi yang ditemukan oleh fungsi MATCH.

2. IFERROR(…;””):

  • Fungsi IFERROR digunakan untuk menangani kesalahan dan mengembalikan nilai alternatif jika terjadi kesalahan.
  • ... adalah hasil dari fungsi INDEX dan MATCH.
  • "" adalah nilai alternatif yang akan dikembalikan jika terjadi kesalahan.

Jadi, analisis pareto dengan rumus Excel ini digunakan untuk mencari nilai yang sesuai dengan nilai gabungan dari sel H19 dan I19 dalam kolom H dan I, kemudian mengembalikan nilai tersebut. Jika tidak ada nilai yang sesuai, maka rumus akan mengembalikan string kosong (""). Rumus ini berguna dalam pencarian nilai yang berdasarkan dua kriteria atau lebih dalam sebuah array.

Kesimpulan

Dengan mengikuti langkah-langkah yang diajarkan dalam video ini, Anda dapat mengoptimalkan strategi penjualan Anda dengan Excel. Dengan memiliki rekapitulasi omzet yang rapi dan analisis Pareto dengan rumus Excel yang kuat, Anda dapat mengambil keputusan yang lebih cerdas dan fokus pada area-area yang memberikan dampak terbesar pada kinerja bisnis Anda. Jangan ragu untuk menyukai, membagikan, dan berlangganan artikel Excel lainnya untuk terus belajar dan meningkatkan keterampilan Anda dalam menggunakan Excel untuk bisnis Anda.

Pranala Luar

File Excel Cara Membuat Rekapitulasi Omzet dan Analisis Pareto dengan Rumus Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkah Cara Membuat Rekapitulasi Omzet dan Analisis Pareto dengan Rumus Excel secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!

0 Komentar

Trackbacks/Pingbacks

  1. Cara Mengambil 10 Besar Siswa Berdasarkan Total Nilai di Microsoft Excel - Depot Excel - […] Formula Excel ini cukup kompleks, dan akan lebih mudah dimengerti jika diuraikan secara rinci. Kami akan mencoba memberikan penjelasan…

Kirim Komentar

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

Join Our Newsletter