Sample data yang kita gunakan adalah produk handphone dari berbagai merek dan type beserta harganya.

Kita akan membuat beberapa kategori berdasarkan harganya, kemudian membuat kategori berdasarkan merek dan terakhir memberikan kode produk dari setiap handphone.

Seperti apa penerapannya?

Dalam tutorial kita kali ini, kita akan menggunakan beberapa fungsi, seperti; Fungsi IF, IFS, ROW, UPPER, LEFT, RIGHT, FIND, TEXT, INDEX, MATCH, XLOOKUP, dan UNIQUE.

Kita mempunyai daftar merek beberapa handphone beserta type dan harganya dan kita akan membuat kategori, merek dan kode produk.

Kategori yang akan kita buat berdasarkan harga dari masing-masing handphone, dimana harga di bawah 2 juta dikategorikan sebagai kategori LOW, sedangkan harga 2 juta - 8 juta dengan kategori MIDLE dan di atas 8 juta sebagai kategory HIGH.

Dari data produk ini juga sudah ada merek dari masing-masing handphone dan kita akan buatkan kolom tersendiri untuk merek dari masing-masing hand-phone tersebut. Yang terakhir kita akan membuat kode barang dengan format; Kode untuk merek, diikuti dengan kode untuk kategori dan terakhir nomor urut dari produk.

Untuk kategori level kita memberikan kode A untuk kategori LOW, kode B untuk kategori MIDLE dan kode C untuk kategori HIGH.

Sedangkan untuk merek kita akan memberikan kode berdasarkan karakter awal dan karakter akhir dari merek handphone tersebut. Sebagai contoh; merek SAMSUNG, karakter awalnya S dan karakter akhirnya G, maka kodenya adalah SG.

Sehingga untuk handphone Samsung dengan kategori LOW, maka Kode barangnya adalah; SGA-0001, handphone Xiaomi dengan kategori MIDLE, kode barangnya adalah; XIB-0002 dan handphone Apple dengan kategori HIGH kode barangnya dalahl AEC-0003 dan seterusnya.

Sekarang kita coba untuk membuat kategori dari masing-masing handphone.

Disini sudah ditentukan kategori dari masing-masing harga handphone, dimana dibawah 2juta sebagai LOW, 2jt - 8jt sebagai MIDLE dan diatas 8jt sebagai HIGH.

Maka dikolom ini kita masukkan nilai maksimum dari masing-masing kategori; LOW = 2jt, MIDLE 8jt dan HIGH, kita berikan nilai yang kira-kira harga yang paling mahal, kita berikan harga yang paling mahal adalah 99.999.999.

Sekarang kita akan buatkan kategori dari masing-masing handphone di atas.

Untuk membuat kategori ini bisa menggunakan fungsi IF, yaitu dengan fungsi:

=IF(J3<$D$3;$B$3;IF(J3<$D$4;$B$4;IF(J3<$D$5;$B$5;0)))

Sehingga kita dapatkan untuk kategori ini adalah LOW.

Baru kita copy ke bawah.

Maka kita dapat, ini adalah harga dibawah 2 juta, ini dibawah 8 juta, dan ini di atas 8 juta.

Cara kedua:

Sebenarnya untuk komparasi ketiga ini yang terakhir sebenarnya tidak diperlukan, karena HIGH itu adalah untuk data yang terakhir. Sehingga kita bisa langsung, kalau bukan dia 8 juta, berarti di atas 8 juta.Di atas 8 juta itu adalah HIGH, maka ini langsung kita dengan HIGH, dan kita buatkan absolute.

=IF(J3<$D$3;$B$3;IF(J3<$D$4;$B$4;$B$5))

Sehingga dapat seperti ini.

Kalau kita lihat perbedaannya, bahwa disini IF terakhirnya dihilangkan, langsung dimasukkan kategori terakhirnya.

Cara ketiga adalah dengan menggunakan fungsi IFS, yaitu dengan sintak:

=IFS(J5<$D$3;$B$3;J5<$D$4;$B$4;J5<$D$5;$B$5)

Cara keempat adalah dengan menggunakan fungsi XLOOKUP, yaitu;

=XLOOKUP(J6;$D$3:$D$5;$B$3:$B$5;;1)

Parameter 1 digunakan untuk mencari harga yang sama atau di atasnya. Berarti untuk Infinix Hot 9 dengan harga 1.600.000, maka dapat 2.000.000 dan harga 2.000.000 adalah kategori LOW.

Ini semua akan memberikan hasil yang sama, Cuma perbedaannya yang fungsi IFS hanya jalan di Excel 2019 dan 365 dan XLOOKUP hanya jalan di Excel 365.

Sekarang kita copy semua kebawah, sehingga kita mendapatkan kategori dari masing-masing handphone.

Handphone ini harga dibawah 2jt, maka kategorinya adalah LOW, yang kelompok ini harga antara 2jt - 8jt, maka kategorinya MIDEL dan ini adalah HIGH.

Sekarang kita akan membuat untuk kolom merek.

Dalam type-type handphone ini sebenarnya sudah ada mereknya dan kita akan menggunakan data ini untuk dimasukkan kedalam kolom merek.

Merek handphone selalu ada di kata pertama, sehingga kita bisa indentifikasi bahwa kata pertamanya adalah merek handphone. Merek handphone disambung dengan spasi

Berarti kita harus mendeteksi spasi ini ada di posisi karekater keberapa, agar kita bisa mengetahui panjang karatker dari merek.

Untuk mengetahui posisi karakter spasi, kita menggunakan fungsi FIND, yaitu;

=FIND(" ";I3)

Sehingga kita dapatkan untuk Realme 5i 3GB, karekater spasi pertamanya ada di karakter ke 7.

Berarti jumlah karakter Realme adalah 7-1 yaitu 6, karena karakter spasi tidak termasuk karakter dari merek.

Setelah kita mendapatkan jumlah karakter atau panjang karakter, kita bisa mendapatkan merek handphone dengan menggunakan fungsi LEFT, sehingga sintaknya akan menjadi:

=LEFT(I3;FIND(" ";I3)-1)

Berarti mengambil karakter yang ada di I3 sepanjang perhitungan yang dihasilkan oleh fungsi FIND(" ";I3)-1

Maka dapat hasilnya adalah Realme.

Sehingga kalau kita copy kebawah, maka kita mendapatkan merek untuk semua handphone.

Kode Barang

Dari kolom merek, kita bisa membuat daftar merek dengan menggunakan fungsi UNIQUE, yaitu:

=UNIQUE(G3:G21)

Fungsi UNIQUE adalah untuk mendapatkan data secara unik, artinya fungsi ini akan melakukan remove data yang double dan menyisakan satu saja. Fungsi ini hampir sama dengan fungsi REMOVE DUPLICATE.

Maka dapat hasilnya;  Realme, Samsung, Redmi, Infinix, OnePlus, dan seterusnya.

Sekarang dari data merek ini baru kita buatkan kode merek. Kode merek yang akan kita buat adalah dengan mengambil huruf pertama dan huruf terakhir dari merek, yaitu dengan fungsi LEFT dan RIGHT.

Maka sintaknya adalah:

Kita ambil dulu huruf pertama:

=LEFT(B8;1)

Maka dapat hasilnya huruf R

Kemudian kita tambahkan huruf akhir dari merek, sehingga menjadi:

=LEFT(B8;1)&RIGHT(B8;1)

Untuk menyambung kedua karakter, kita menggunakan &.

Sehingga kita dapatkan Re.

Karena hurf kedua adalah huruf kecil, maka kita konversi menjadi huruf besar semua, maka kita tambahkan fungsi UPPER, sehingga menjadi:

=UPPER(LEFT(B8;1)&RIGHT(B8;1))

Maka sampai disini, untuk kode merek sudah kita dapatkan.

Sekarang kita tinggal menggabungkan Kode Merek, Kode Level.

Kita mengunakan fungsi INDEX dan MATCH di sel F3.

=INDEX($A$8:$A$21;MATCH(G3;$B$8:$B$21;0))

Ini baru untuk kode merek, sekarang kita akan menambahkan kode level:

=INDEX($A$8:$A$21;MATCH(G3;$B$8:$B$21;0))&INDEX($A$3:$A$5;MATCH(H3;$B$3:$B$5;0))

Terakhir kita menambahkan nomor urutnya:

=INDEX($A$8:$A$21;MATCH(G3;$B$8:$B$21;0))&INDEX($A$3:$A$5;MATCH(H3;$B$3:$B$5;0))&ROW(E3)-ROW($E$2)

Maka dapat kode barangnya adalah : REA1

Agar nomor urutnya terdiri dari empat karakter, maka ditambahkan fungsi TEXT, sehingga menjadi:

=INDEX($A$8:$A$21;MATCH(G3;$B$8:$B$21;0))&INDEX($A$3:$A$5;MATCH(H3;$B$3:$B$5;0))&TEXT(ROW(E3)-ROW($E$2);"0000")

Maka kita dapatkan kode barangnya adalah : REA0001

Untuk mendapatkan pemisah antara kode level dengan nomor urut, kita tambahkan karakter -, sehingga menjadi:

=INDEX($A$8:$A$21;MATCH(G3;$B$8:$B$21;0))&INDEX($A$3:$A$5;MATCH(H3;$B$3:$B$5;0))&"-"&TEXT(ROW(E3)-ROW($E$2);"0000")

Maka dapat hasilnya: REA-0001

Sampai disini pembuatan kode barang sudah selesai, tinggal kita copy kebawa, sehingga kita dapatkan hasil akhirnya.

Demikian tutorial kita kali ini dan kita sudah belajar cara menggunakan beberapa fungsi dalam studi kasus pembuatan kode barang.

Untuk file contoh bisa didownload disini.