Tips Microsoft Excel : Membuat Aplikasi Mendeteksi Variasi Nomor Sebuah Kartu

Ini adalah jawaban saya buat mas (inisial) John sabar tentang pertanyaannya di Microsoft Excel : Tips Menghitung “PPh Terutang” Dalam SPT Tahunan dan semoga bisa memuaskan …… silahkan diunduh disini.

Tips kali ini mungkin judulnya agak kepanjangan, kata orang pinter mah ga SEO banget hehehehe, maklum saya sengaja mencari judulnya supaya nanti tidak terjadi kesalahpahaman dalam isi yang saya sajikan.

Tip kali ini adalah membuat Aplikasi sederhana dalam medeteksi atau membaca dari suatu variasi nomor dalam sebuah kartu, anggaplah kartu Kredit… tapi ingat contoh yang saya sajikan hanyalah bohongan.

Disini ada 4 Nomor Kartu yang ingin kita identifikasikan dan informasi yang kita harapkan adalah nama Bank Penerbit dan Jenis kartunya, yaitu :

• 1234151185371120
• 1234961315690210
• 1234731225884660
• 1234881255811560

Sekarang apa yang ada dibenak anda, seandainya Data Base yang tersedia yang hanya menyajikan informasi 2 digit angka beserta keterangan nama Bank dan jenis Kartu yang diterbitkan ? ……

itu cukup mudah dan saran saya sebelum melanjutkan perhatikan dan simak sekali lagi 4 nomor tersebut diatas.

Nah dalam mengidentifikasinya ada beberapa persyaratan yang inginkan kita penuhi diantaranya :

1. Identifikasi dari Nomor tersebut (Jumlah Digit)
2. Nama Bank yang mengeluarkan kartu tersebut
3. Jenis kartu yang diterbitkan
4. Kebenaran dari Kartu tersebut (Validitas)

Sudah siap yuk kita lanjutkan…..Umpamakan kita telah mempunyai Data Base (DB)  Di Sheet1 (rename menjadi DBKK) yang berisi Data Kode Bank yang mengeluarkan Kartu dan jenis kartu yang diterbitkan, contoh seperti dibawah ini :

Jika sudah sekarang buka Sheet2, dan buatlah data seperti berikut ini :

Sudah beres nah sekarang yuk kita penuhi 4 persyaratan dalam membuat aplikasi ini satu persatu, dan kita mulai dari mengidentifikasi variasi nomor tersebut :

Identifikasi dari Nomor tersebut (Jumlah Digit)

Identifikasi disini ketika mengetikkan 16 digit nomor yang dimaksud maka secara otomatis nomor tersebut terpisah secara berurutan masing-masing 4 digit dengan pemisah tanda (-), nah untuk mewujudkannya kita menggunakan rumus Membaca Nilai tengah atau Mid, jadi rumus di Cell B2 adalah seperti ini :

=MID(B1,1,4)&”-”&MID(B1,5,4)&”-”&MID(B1,9,4)&”-”&MID(B1,13,4)

Mid(B1,1,4) bisa juga kita ganti dengan  dengan rumus Left(B1,4) bertujuan untuk membaca 4 nomor diawal (perhatikan nilai 1 dan 4) atas angka yang kita ketikkan di Cell B1

Mid(B1,5,4) bertujuan untuk membaca 4 nomor selanjutnya dimulai dari angka ke 5  tersebut.

“-” bertujuan sebagai tanda pemisah….

Cukup dimengerti kan ….. berarti persyaratan pertama sudah kita penuhi, jadi ketika kita memasukkan nomor 1234731225884660 di Cell B1, maka di Cell B2 yang muncul adalah

1234-7312-2588-4660

Nama Bank yang mengeluarkan kartu tersebut

Syarat kedua adalah kita harus mampu mendeteksi nama Bank penerbit kartu tersebut, padahal Database sendiri hanya menyediakan informasi 2 digit angka, sedangkan digit angka yang kita ketikkan berjumlah 16 Digit ….?????

Nah ini awalnya cukup sulit tapi mudah dan ada dua cara yang bisa kita pakai yaitu :

• Variasi antara fungsi If dan Mid
• Menggunakan Vlookup dan Mid

Variasi antara Fungsi If dan Mid, jelas akan memakan rumus yang cukup panjang tapi menurut saya ini perlu kita lakukan agar membiasakan yang sulit maka untuk hal mudah makin terasa mudah….. Disini kita memadukan nilai Mid dari 16 digit yang kita ketikkan, perhatikan contoh (Cell B1) :

1234731225884660

Adakah yang sama dengan 2 Digit Database yang sudah kita buat …. (simak baik-baik) ….. betul … jika kita teliti ada yang sama yaitu pada digit ke 6 yaitu angka 31 dan di Data Base DBKK yang ada angka 31 menunjukkan nama Bank Muara…… pahami baik-baik.

Jadi ungkapan rumus yang ingin kita bangun adalah

Jika 2 (dua) nilai tengah dimulai dari digit ke 6 muncul maka kita memberikan nama Bank sama persis dengan nama Bank di DBKK

Adapun rumusnya yang kita buat di Cell B3 adalah :

=IF(MID($B$1,6,2)=”51″,”Bank Cahaya“,IF(MID($B$1,6,2)=”61″,”Bank Kita“,IF(MID($B$1,6,2)=”31″,”Bank Muara“,IF(MID(B1,6,2)=”81″,”Bank Persaudaraan“))))

Maka yang muncul di Adalah

Bank Muara

Sekarang jika rumus yang ktia gunakan adalah perpaduan Vlookup dengan Mid……. disini ungkapan rumus yang ingin kita bangun adalah

Melihat 2 nilai tengah yang muncul dimulai dari digit ke 6 dengan menyandingkan data yang ada di DBKK (CellB2:CellC5)

Maka rumus yang kita bangun tersebut adalah

=VLOOKUP(MID(B1,6,2),DBKK!B2:C5,2,FALSE)

Dan hasilnya tetap sama dengan muncl nama Bank Muara.

Jenis kartu yang diterbitkan

Persyaratan Kedua sudah terpenuhi, maka kita melangkah ke persyaratan ketiga, intinya hampir sama dengan bentuk diatas, coba sekarang kita simak nomor dibawah ini (Cell B1) :

1234731225884660

Perhatikan dan simak baik-baik sekarang adakah persamaan untuk 2 digit jenis kartu yang diterbitkan …. siippppp hebat banget bisa langsung nebak … angka tersebut berada pada digit ke 9 yaitu 25, di DBKK sendiri angka 25 menunjukkan Jenis Kartu Platinum Card.

Jadi rumus yang kita bangun (Cell B4) dengan variasi fungsi If dan Mid adalah :

=IF(MID($B$1,9,2)=”85″,”Gold Card“,IF(MID($B$1,9,2)=”25″,”Platinum Card“,IF(MID(B1,9,2)=”55″,”Silver Card“,IF(MID(B1,9,2)=”15″,”VIP Card“))))

Sedangkan jika kita menggunakan variasi Vlookup dan Mid adalah :

=VLOOKUP(MID(B1,9,2),DBKK!B8:C11,2,FALSE)

Dan hasil yang kita harapkan akan muncul

Platinum Card

Kebenaran dari Kartu tersebut (Validitas)

Syarat yang keempat ini hanyalah sebagai pelengkap dengan tujuan untuk mengantisipasi Human error ketika dalam pengetikkan nomor kartu tersebut karena bisa jadi seharusnya angka yang diketika berjumlah 16 digit tapi kenyataannya hanya 15 digit.

Caranya mudah yaitu dengan menjumlahkan banyaknya jumlah angka yang kita ketikkan, disini yang kita gunakan bukan Sum tapi Fungsi Len

Asumsi yang kita harapkan adalah :

Jika jumlah huruf (angka) dari angka yang diketikkan tersebut berjumlah 16 huruf (angka) maka kita nyatakan Benar (Valid), jika kurang maka salah (Invalid).

Dan rumusnya (Cell B5) adalah :

=IF(LEN(B1)=16,”Valid“,”Invalid“)

So semua persyaratan sudah kita penuhi jadi ketika kita memasukkan 4 nomor tersebut maka akan secara otomatis mendeteksi data yang kita perlukan tersebut……. dan hasilnya seperti gambar dibawah ini :

Mudahkan … dan selamat mencoba…

Jika masih bingung buat contoh latihan bisa didownload disini :

• Via Rapidshare

Salam

——————————————-00000000000——————————————-

Sumber bacaan :

• Mengungkap Kedahsyatan Fungsi IF karangan Johar Arifin. PT. Elex Media Komputindo
• 25 Aplikasi Bisni Excel 2007 . Maxikom
• Mempermudah dan Mempercepat Pekerjaan Menggunakan Formula Excel 2007. Media Kita
• Microsoft Excel 2007 Membangun Rumus dan Fungsi. andipublisher

——————————————-00000000000——————————————-

Tips Sebelumnya :

• Microsoft Excel : Membuat Grafik
  
• Microsoft Excel : Memecah Suatu (Angka) Bilangan
• Microsoft Excel : Tips Membuat Keterangan “Hasil Ujian”
• Microsoft Excel : Tips Menghitung “PPh Terutang” Dalam SPT Tahunan
• Microsoft Excel : Membuat Aplikasi Sederhana “Pengeluaran Per Bulan”
• Microsoft Excel : Membuat Aplikasi Sederhana “Kapan Kamu Mengenal Cinta”
• Microsoft Excel : Tips Membaca Angka Dalam Bentuk Teks (Kalimat)
• Mengenal Mutasi Habis SPPT PBB
• Microsoft Excel : Membuat Data Base Sederhana Dan Tabel Pencarian Data
• Microsoft Excel : Menggabungkan Data Kota Dan Tanggal Lahir
• Microsoft Excel : Tips Menggabungkan Dua Kata Dari Dua Kolom
• Microsoft Excel : Tips Menambah dan Menghilangkan Tanda Baca

——————————————-00000000000——————————————-

Untuk saran dan kritik bisa dilayangkan ke

bunga.liani@yahoo.co.id atau ke blognyaomiyan@yahoo.co.id