Berbagi Ilmu

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 :

Salam

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

Sumber bacaan :

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

Tips Sebelumnya :

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

Untuk saran dan kritik bisa dilayangkan ke

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

About omiyan

Cuman seorang newbie didunia maya, selalu merasa jika berbicara lewat tulisan itu lebih baik daripada kita berbicara tanpa ada jejak dalam sebuah tulisan.

Diskusi

23 thoughts on “Tips Microsoft Excel : Membuat Aplikasi Mendeteksi Variasi Nomor Sebuah Kartu

  1. sepintas mumet om, untungnya dipecah jadi mudah dipahami dan buat saya ini sih udah masuk ke kategori intermedit

    Posted by kabariberita | 31 Mei 2010, 08:40
  2. Pertamaaaaxxxxx dulu dahhhhh eh keduaaaaxxxxx…pertamaaxxxx tetap sayaaxxxx

    Posted by kabariberita | 31 Mei 2010, 08:42
  3. Aplikasi yang ini keren banget om apalagi membaca dan memecah angka tersebut

    Posted by kabariberita | 31 Mei 2010, 08:43
  4. dear Mas omiyan

    thank ya.. balasannya tapi bisa kirim ke email saya (sabarjhon@yahoo.com) ada trouble di my office ( tidak dapat mendowload). terima kasiiiiiiiiiiiiiiiiiiiih banget ya, saya tunggu ya

    Posted by jonsabar | 31 Mei 2010, 09:09
  5. (maaf) izin mengamankan KELIMAAXXXZZZZ dulu. Boleh kan?!
    Perlu ngebaca hingga dua kali biar rada paham.
    hehehehehe

    Posted by alamendah | 31 Mei 2010, 10:54
  6. akan kami rekomendasikan blog ini bila ada kawan2 yang ingin belajar excel lebih dalam lagi.Terima kasih atas ilmu yang dibagi. Salam sehat

    Posted by rumah-sehat afiat | 31 Mei 2010, 11:35
  7. wahhh… ga ngerti saya..🙂

    Posted by fitrimelinda | 31 Mei 2010, 12:49
  8. waduh saya excel cuma bisa nambah sama ngurangi aja….

    Posted by Ari | 31 Mei 2010, 13:37
  9. Mumpung membahas Excel nich Om, saya mau nanya2 lagi…
    Begini.
    Saya punya data 2 kolom
    Kolom pertama berisi Nama, kolom kedua berisi jenis kelamin (L/P)
    Nah yang pengin saya tau, ada nggak cara memformat kolom pertama agar begitu kolom kedua berisi L maka nama pada kolom pertama otomatis terformat berwarna merah, sebaliknya kalau kolom kedua berisi P maka nama di kolom pertama tetap hitam.
    Kayak conditional format itu, cuman disini kuncinya ada di kolom lain.
    Semoga ada solusinya

    Posted by marsudiyanto | 31 Mei 2010, 15:42
  10. Weh, saya malah baru tau ada fungsi len
    Saya jadi nambah ilmu

    Posted by marsudiyanto | 31 Mei 2010, 15:48
  11. boleh dicoba nih sepertinya…coba duluh ya mas dan mohon copas.
    terimakasih info yang bermanfaat ini.

    salam hangat selalu mas

    Posted by Cah Lapindo | 31 Mei 2010, 16:16
  12. keren,, ajib,,, mantab banget buat pemula kayak saya,,,

    mau tanya nih ?

    kalo artikel di atas tuh mengenai permainan angka, bisa nggak excel itu mendeteksi warna cell, sperti merah, biru dll. ilustrasinya

    cell warna merah ada 4 kolom, cell warna biru ada 2 kolom dan sebagainy…

    terima kasih.

    Posted by mathub2003 | 31 Mei 2010, 20:12
  13. Tambahan ilmu nih, selama ini, saya cuma menggunakan Excel untuk memasukkan data dan buat grafik.

    Posted by yantiyuliantiblog | 1 Juni 2010, 01:39
  14. thx atas sharing infonya, bermanfaat banget

    Posted by Sri Lestari | 1 Juni 2010, 02:16
  15. terima kasih infonya OM manfaat banget
    yang nyoba yang butuh biasanya OM

    Posted by shalimow | 1 Juni 2010, 04:45
  16. Ilmu baru …

    Posted by Rindu | 1 Juni 2010, 04:50
  17. alhamdulillah ngerti…🙂
    bru tau jg rumus validitas… thank’s pak…🙂

    Posted by ariefien | 1 Juni 2010, 08:30
  18. trimakasih atas infonya semoga bermanfaat… keep share knowledge

    Posted by Mymusic | 2 Juni 2010, 06:53
  19. vlookup m mid ko gamau digabungin ya? hasilnya #N/A (not available), saya coba robah2 tipe datanya tapi gak ngaruh, tapi kalo tanpa mid(langsung tulis cell referensinya) muncul tuh hasilnya, gimana tuh? bingung?

    Posted by Yuza | 14 Maret 2012, 09:37
  20. oh, dah ketemu, kalo mau pake mid, tabel array nya harus pake type text, baru nongol tu hasilnya…

    Posted by Yuza | 14 Maret 2012, 10:00
  21. kllo bisa soal tentang kerjaan perusahaan donk …

    Posted by atim | 9 September 2012, 22:38
  22. mas yan saya mau nanya
    misal data nya gini :
    A B
    1 4
    2 5 ==> Tabel
    3 7
    4 8
    5 10
    6 12

    C D, Hasil yg D ini melihat tabel di atas
    5
    4
    2
    1
    4
    6
    Mohon pencerahan mas yan, makasih sebelum nya…

    Posted by Samsul | 29 November 2012, 12:09
  23. Mas filenya ngga ada tuch……… mohon di upload lagi mas ….. aku pingin belajar

    Omiyan : ya om saya lagi nyari file aslinya hehehe belum nemu😀

    Posted by flo | 7 Januari 2013, 08:18

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

twitter

  • 2,461,813 Sejak 17 Nov 2008

Kategori

Yang Lagi OL

Langganan via Email

Bergabunglah dengan 929 pengikut lainnya

smadav antivirus indonesia

%d blogger menyukai ini: