Cara Mudah Mengelompokkan Data Produk Berdasarkan Supplier di Excel

Dalam dunia bisnis, terutama dalam manajemen inventaris, sering kali kita perlu mengelompokkan data produk berdasarkan pemasok atau supplier. Hal ini dapat membantu kita dalam mengatur dan menganalisis data produk dengan lebih efisien. Dalam artikel ini, kami akan membahas cara mengelompokkan data produk berdasarkan supplier menggunakan formula array di Microsoft Excel.

Mengapa Mengelompokkan Data Produk Penting?

Mengelompokkan data produk berdasarkan supplier adalah langkah penting dalam manajemen inventaris dan analisis bisnis. Beberapa alasan mengapa ini penting termasuk:

1. Pengawasan Stok: Ini memungkinkan perusahaan untuk melacak inventaris produk yang diterima dari setiap supplier dengan lebih baik.
2. Pengambilan Keputusan: Data yang terorganisir membantu dalam mengambil keputusan bisnis yang lebih baik terkait dengan persediaan dan pembelian.
3. Pelaporan: Pengelompokkan data ini akan mempermudah dalam pembuatan laporan yang jelas dan informatif.

Langkah-langkah untuk Mengelompokkan Data Produk Berdasarkan Supplier

Berikut adalah langkah-langkah untuk mengelompokkan data produk berdasarkan supplier menggunakan formula array Excel:

Langkah 1: Persiapan Data

Pastikan data yang akan Anda kelompokkan sudah siap dalam lembar kerja Excel. Data ini biasanya terdiri dari kolom seperti Nama Produk dan Nama Supplier. Teman-teman bisa menambahkan sendiri kalau mau, agar informasi yang disajikan lebih informatif.

Langkah 2: Membuat Daftar Supplier Unik

Anda perlu membuat daftar unik dari semua supplier yang ada dalam data Anda. Untuk melakukan ini, Anda dapat menggunakan fitur “Data Validation” atau hanya membuat daftar manual di lembar kerja Anda.

Langkah 3: Membuat Rumus Array

Ini adalah langkah kunci dalam proses ini. Anda dapat menggunakan formula array Excel untuk mengelompokkan produk berdasarkan supplier. Berikut ini contoh formula yang dapat digunakan:

{=UNIQUE(FILTER(B4:C20;B5:B20=E4))}

Patut diketahui bahwa tanda kurung kurawal disini tidak ditambahkan secara manual tapi hasil otomatis dari perlakuan pada penulisan rumusnya untuk menandakan bahwa ini adalah formula array. Untuk menambahkan kurung kurawal, setelah menulis rumus: =UNIQUE(FILTER(B4:C20;B5:B20=E4)) akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter secara bersamaan.

• UNIQUE digunakan untuk mendapatkan nilai unik dari hasil filter.
• FILTER digunakan untuk memfilter data berdasarkan supplier (dalam contoh ini, nilai dari sel D2).

Langkah 4: Hasilnya

Hasilnya adalah daftar produk yang terkait dengan supplier yang Anda pilih. Anda dapat menambahkan formula ini ke sel lain dengan mengganti sel D2 dengan supplier lainnya. Dan inilah hasilnya:

Langkah 5: Penyempurnaan

Anda dapat menyesuaikan dan mempercantik tampilan hasil kelompok produk Anda dengan menambahkan judul, format, dan data tambahan yang diperlukan.

Sayangnya, formula array ini hanya bisa digunakan pada Microsoft Excel 2011 ke atas. Lalu bagaimana jika Microsoft Excel yang kita punya masih yang 2010? Tenang. Bisa kok. Hanya saja memang formula array yang digunakan jadi lebih panjang.

Dan inilah formula array yang digunakan:

=IF(ISERR(SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16)));””;OFFSET($B$4;SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16));0))

Dan hasilnya adalah seperti pada gambar berikut:

Penjelasan Rumus

Karena formula array yang digunakan cukup kompleks, saya akan memberikan penjelasan langkah demi langkah untuk membantu Anda memahaminya. Formula ini digunakan untuk mencari nilai dalam kolom B (dari baris 5 hingga 20) yang sesuai dengan kriteria tertentu di kolom C, dan mengembalikan nilai tersebut. Mari kita bahas lebih rinci:

1. IF($C$5:$C$20=F4;ROW(1:16);ROW(1:16)):
   Ini adalah bagian pertama dari rumus dan merupakan bagian pertama dari fungsi IF. Ini menguji apakah nilai dalam kolom C (baris 5 hingga 16) sama dengan nilai yang ada di sel F4. Jika sesuai, maka ROW(1:16) akan mengembalikan array baris dari 1 hingga 16. Jika tidak sesuai, itu juga akan mengembalikan array baris dari 1 hingga 16. Dalam kasus kedua, ini akan digunakan nanti untuk menghindari kesalahan.
2. SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16)):
   Ini adalah fungsi SMALL yang digunakan untuk mencari nilai terkecil dari array yang dihasilkan dalam langkah sebelumnya. Ini berarti mencari baris pertama yang sesuai dengan kriteria yang kita tentukan.
3. ISERR(SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16))):
   Ini adalah fungsi ISERR yang digunakan untuk menguji apakah fungsi SMALL menghasilkan kesalahan. Jika ada kesalahan, ini akan mengembalikan TRUE, dan jika tidak, ini akan mengembalikan FALSE. Ini digunakan untuk menentukan apakah ada hasil yang ditemukan atau tidak.
4. OFFSET($B$4;SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16));0):
   Ini adalah bagian terakhir dari rumus dan digunakan untuk mengambil nilai dari kolom B yang sesuai dengan baris yang ditemukan dalam langkah kedua. OFFSET digunakan untuk memindahkan referensi sel dari sel B4 ke sel yang sesuai. Argumen pertama OFFSET adalah sel awal (B4), argumen kedua adalah jumlah baris yang akan dipindahkan (nilai yang dihasilkan oleh SMALL), dan argumen ketiga adalah jumlah kolom yang akan dipindahkan (0 dalam kasus ini, yang berarti tetap di kolom yang sama).
5. {=IF(ISERR(SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16)));"";OFFSET($B$4;SMALL(IF($C$5:$C$20=F4;ROW(1:16));ROW(1:16));0))}:
   Tanda kurung kurawal di luar rumus menunjukkan bahwa ini adalah formula array. Formula ini menggabungkan langkah-langkah sebelumnya. Jika fungsi SMALL menghasilkan kesalahan (tidak ada hasil yang ditemukan), maka sel akan tetap kosong (“”). Jika tidak, itu akan mengambil nilai yang sesuai dari kolom B.

Jadi, secara keseluruhan, formula ini mencari nilai dalam kolom B yang sesuai dengan nilai dalam sel F4 berdasarkan kriteria yang ada di kolom C. Jika hasilnya ditemukan, itu akan ditampilkan dalam sel yang menggunakan rumus ini, dan jika tidak, sel akan tetap kosong.

Kesimpulan

Mengelompokkan data produk berdasarkan supplier adalah langkah yang sangat berguna dalam manajemen inventaris dan analisis bisnis. Dengan menggunakan formula array Excel seperti yang telah dijelaskan di atas, Anda dapat dengan mudah mengatur dan menganalisis data produk Anda berdasarkan supplier dengan lebih efisien. Ini akan membantu Anda membuat keputusan bisnis yang lebih baik dan melacak inventaris Anda dengan lebih baik lagi. Semoga artikel ini bermanfaat dalam meningkatkan efisiensi operasional bisnis Anda.

Jika ada teman-teman yang membutuhkan file ini, file Excel bisa di download di: SINI (2010) dan di SINI (2011 ke atas)