Membuat QR Code dan Barcode

Artikel kali ini akan membahas pembuatan QR Code dan Barcode, sebelum kita memulai dengan rumus dan makronya kita pahami dulu arti dan fungsi dari barcode dan qr code. Fungsi Barcode adalah sebagai sarana untuk mempermudah kita dalam menginput data dimana cara kerjanya input data otomatis dengan membaca kode. Barcode adalah kode batang dengan garis hitam hitam diatas background putih. Sedangkan Kode QR atau biasa dikenal dengan istilah QR (Quick Response) Code adalah bentuk evolusi kode batang dari satu dimensi menjadi dua dimensi. Penggunaan kode QR sudah sangat lazim di Jepang Hal ini dikarenakan kemampuannya menyimpan data yang lebih besar dari pada kode batang sehingga mampu mengkodekan informasi dalam bahasa Jepang sebab dapat menampung huruf kanji. Kode QR telah mendapatkan standarisasi internasional dan standarisasi dari Jepang berupa ISO/IEC18004 dan JIS-X-0510 dan telah digunakan secara luas melalui ponsel di Jepang.

Mari kita mulai penjelasannya, tetapi sebelum itu anda harus mengaktifkan makro anda dulu, biasanya fasilitas makro ini tidak diaktifkan oleh antivirus, mari kita ikuti langkah2xnya dibawah ini:
1. Bukalah excel anda kemudian klik Menu File, kemudian klik menu teks "Options":

2. Kemudian klik menu "Trust Center":

3. Kemudian akan muncul tombol yang bertuliskan "Trust Center Setting", di klik saja

4. Akan muncul menu seperti ini, silahkan klik pada "Macro Settings" (lihat gambar)

5. Klik pada sebelah kanan "Enable All Macros"

Setelah selesai sesuai alur diatas silahkan tutup excel anda.

KASUS 1: QR CODE

Nah diatas adalah tampilan dari QR Code, ini menggunakan makro karena sifatnya yang 2 dimensional dan syaratnya anda harus online karena kode makro ini memakai Google Chart API. Kode Makronya:

Function Insert_QR(codetext As String)
Dim URL As String, MyCell As Range

Set MyCell = Application.Caller
URL = "https://chart.googleapis.com/chart?chs=125x125&cht=qr&chl=" & codetext
On Error Resume Next
  ActiveSheet.Pictures("My_QR_" & MyCell.Address(False, False)).Delete 'delete if there is prevoius one
On Error GoTo 0
ActiveSheet.Pictures.Insert(URL).Select
With Selection.ShapeRange(1)
 .PictureFormat.CropLeft = 15
 .PictureFormat.CropRight = 15
 .PictureFormat.CropTop = 15
 .PictureFormat.CropBottom = 15
 .Name = "My_QR_" & MyCell.Address(False, False)
 .Left = MyCell.Left + 25
 .Top = MyCell.Top + 5
End With
Insert_QR = "" ' or some text to be displayed behind code
End Function

Makro ini membuat UDF atau "User Defined Function" atau semacam membuat fungsi baru pada excel, dengan sintaks sebagai berikut:

=Insert_QR(C4)

Fungsi ini akan memunculkan kode QR secara otomatis dan rumus ini dapat kita letakkan di bawah kode QR. Untuk mengaplikasikan kode makro ini, silahkan buat modul baru di halaman VBA dengan menekan tombol ALT+F11 tekan secara bersamaan. Jika sudah silahkan lihat gambar di bawah ini untuk pengetikan rumusnya.

Setelah mengetikkan rumus seperti di gambar tekanlah tombol ENTER, kemudian akan muncul QR code:

Setelah itu simpan file excel anda sebagai makro dengan ektensi XLSM, jangan sampai salah di simpan sebagai XLSX karena makro tidak akan jalan dan kode QR tidak akan tampil.

KASUS 2: BARCODE

Tampak diatas ada 2 macam bentuk barcode yaitu code 39 (yang atas) dan code 128 (bawah). Apa perbedaannya? Perbedaan utama antara Kode 39 dan Kode 128, seperti yang dapat Anda lihat dari contoh gambar di atas adalah bahwa Kode 128 memiliki kepadatan data yang jauh lebih tinggi daripada Kode 39. Oleh karena itu, kami menyarankan Anda memberi label barang sangat kecil menggunakan kode 128 barcode. Dan penerapan pada excel anda hanya butuh font atau jenis huruf barcode yang bisa anda download di artikel ini, dan ini merupakan jenis font barcode yang gratis ya karena ada juga yang lebih premium. Pertama anda harus menginstall nya dulu ke sistem windows supaya font barcode tersebut dapat digunakan di excel.

Cara Install Font Barcode
1. Anda download dulu disini font-nya KODE BARCODE 39 dan KODE BARCODE 128
2. Kemudian anda klik kanan dengan mouse anda kemudian akan muncul menu seperti ini

Kemudian klik pada "INSTALL", kemudian proses akan berjalan otomatis

Pada gambar diatas saya baru saja menginstall font barcode 39 dan lakukan hal yang sama dengan font barcode 128. Kemudian kita lanjutkan untuk mengaplikasikannya pada excel.
Langkahnya:
1. Bukalah excel anda
2. Ketiklah kalimat atau angka terserah anda di cell A2

3. Kemudian cari font atau jenis huruf dengan nama"Free 3 of 9 Extended"

Kemudian tekan ENTER setelah anda pilih font tersebut.
4. Setelah itu ketiklah rumus ini pada cell B2:
="*"&A2&"*"
Kenapa seperti itu? Karena kode 39 adalah simbologi yang paling populer di dunia non-ritel dan digunakan secara luas di bidang manufaktur, militer, dan aplikasi kesehatan. Setiap kode Kode 39 bar dibingkai oleh karakter start / stop diwakili oleh tanda bintang (*). Asterisk dicadangkan untuk tujuan ini dan tidak boleh digunakan di badan pesan.
5. Aplikasi untuk barcode 128

Untuk kode 128 rumusnya hanya cell referensi saja, letakkan di cell B5 =A5 setelah itu pilihlah jenis fontnya
Mudah2xan jelas yah, dan semoga artikel ini berguna untuk dapat memahami seputar qr code dan barcode.
FILE LATIHAN DOWNLOAD

Kumpulan Kasus Excel Jilid 8

Met siang ini saya mo share lagi kasus2x lookup ya mudah2xan dapat berguna buat bro dan sis semua

Kasus 1

Dari tabel diatas hasil yang kita inginkan seperti ini:

Rumus yang digunakan:
=INDEX(A:C;INT((ROWS($1:1)-1)/3)+3;MOD(ROWS($1:1)-1;3)+1)

Kasus 2

Hasil yang diharapkan

Rumus (yang warna kuning):
=IFERROR(VLOOKUP(B$23;IF(IFERROR(LOOKUP(ROW($4:$17);ROW($4:$17)/($A$4:$A$17<>"");$A$4:$A$17);"Jumlah Area 0")="Jumlah Area "&RIGHT($A24)-1;$B$4:$C$17);2;0);0)

Kasus 3

Untuk hasil yang diwarna kuning diatas, dan rumusnya:
=INDEX(D11:G17;MATCH(B2;C11:C17;0);MATCH(1;MMULT({1\1\1\1\1};--(B1=D6:G10));0))

Kasus 4

Kriteria dan hasil yang diharapkan dari tabel diatas

Untuk mata pelajaran yang berurutan dari skor tertinggi ke terendah:
=IF(C11="";"";INDEX($C$1:$G$1;AGGREGATE(15;6;COLUMN($C$1:$G$1)-COLUMN($B$1)/(INDEX($C$2:$G$6;MATCH($B$10;$B$2:$B$6;0);0)=C11);COUNTIF(C$11:C11;C11))))

Untuk Nilai pelajaran tertinggi yang diambil yang berurutan dari skor tertinggi ke terendah:
=IFERROR(LARGE(INDEX($C$2:$G$6;MATCH($B$10;$B$2:$B$6;0);0);ROWS($1:1));"")

Kasus 5

Kriteria dan hasil yang diharapkan dari tabel diatas:

Untuk hewan yang dipilih dengan tanda "X" dengan skor berurutan dari yang tertinggi:
=IFERROR(INDEX(LOOKUP(COLUMN(B$4:Q$4);COLUMN(B$4:Q$4)/(B$3:Q$3<>"");B$3:Q$3);AGGREGATE(15;6;COLUMN(B$4:Q$4)-COLUMN(A$4)/((B$4:Q$4/(INDEX(B$5:Q$10;MATCH(A$14;A$5:A$10;0);0)="x"))=C14);COUNTIF(C$14:C14;C14)));"")

Untuk Nilai/skor hewan yang dipilih dengan tanda "X" dengan skor berurutan dari yang tertinggi:
=IFERROR(AGGREGATE(14;6;B$4:Q$4/(INDEX(B$5:Q$10;MATCH(A$14;A$5:A$10;0);0)="x");ROWS($1:1));"")

Kasus 6

Hasil yang diharapkan adalah seperti ini:

Rumusnya:
=IFERROR(INDEX($J$3:$J$5;SMALL(INDEX((ISERROR(SEARCH($J$3:$J$5;$A2)))*10^10+(ROW($J$3:$J$5)-ROW($J$2)););COLUMNS($B1:B1)));"")

 Kasus 6

Hasil Yang diinginkan 

Rumusnya ada 3 versi:
1. =MAX(VLOOKUP(A9;$A$2:$C$5;2;0);VLOOKUP(A9;$A$2:$C$5;3;0))
2. =IFERROR(1/(1/VLOOKUP(A9;A2:B5;2;0));1/(1/VLOOKUP(A9;A2:C5;3;0)))
3. =IF(VLOOKUP(A9;A2:B5;2;FALSE)=0;VLOOKUP(A9;A2:C5;3;FALSE);VLOOKUP(A9;     A2:B5;2;FALSE))

FILE LATIHAN

Kumpulan Kasus Excel Jilid 7

Jumpa lagi di gelombang yang sama, maaf ya teman2x semua lama tak posting maklum makin kesini makin tak ada ide aku ini haha.. tapi yang penting sekarang posting, horeeee, langsung yak...

KASUS 1

Dari tabel diatas kita ingin rubah ke

Rumusnya (Array Formula):
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$2:$C$2;MATCH(0;IF(ISBLANK($A$2:$C$2);"";COUNTIF($F$1:F1;$A$2:$C$2));0));INDEX($A$3:$C$3;MATCH(0;IF(ISBLANK($A$3:$C$3);"";COUNTIF($F$1:F1;$A$3:$C$3));0)));INDEX($A$4:$C$4;MATCH(0;IF(ISBLANK($A$4:$C$4);"";COUNTIF($F$1:F1;$A$4:$C$4));0)));INDEX($A$5:$C$5;MATCH(0;IF(ISBLANK($A$5:$C$5);"";COUNTIF($F$1:F1;$A$5:$C$5));0)));INDEX($A$6:$C$6;MATCH(0;IF(ISBLANK($A$6:$C$6);"";COUNTIF($F$1:F1;$A$6:$C$6));0)))

KASUS 2

Kasus diatas misal kita punya nomor yang di merged (digabung) di beberapa cell, kita inginnya di unmerged atau di rubah menjadi

Rumusnya:
=IFERROR(INDEX(A:A;SMALL(INDEX((A$1:INDEX(A:A;MATCH(99^99;A:A))="")*99^99+ROW(A$1:INDEX(A:A;MATCH(99^99;A:A)));0);ROWS(C$2:C2)));"")

KASUS 3

Hasil yang diinginkan

Ini kasus lookup ya, rumusnya:
=IFERROR(INDEX($B$1:$B$19;MATCH($D2;$A$1:$A$19;0)+MATCH($E$1;INDIRECT("A"&MATCH($D2;$A$1:$A$19;0)&":A19");0)-1);"")

KASUS 4

Dari tabel diatas kita ingin hasilnya seperti ini

Rumus Array:
=IFERROR(INDEX(A$2:A$12;SMALL(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12))=LARGE(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));"");1);ROW($B$2:$B$12);"");1)-ROW($A$2)+1);"")

KASUS 5

Dari dua tabel diatas kita ingin merubah lay out tabelnya menjadi

Rumus kolom F
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$4:$C$6<>"";ROW($A$4:$C$6)*10^4+COLUMN($A$4:$C$6));ROWS($A$4:A4));"R0000C0000");0);"")

Rumus kolom G
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$10:$C$12<>"";ROW($A$10:$C$12)*10^4+COLUMN($A$10:$C$12));ROWS($A$10:B10));"R0000C0000");0);"")

Rumus kolom I (Array Formula)
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF(($A$4:$C$12<>"")*(LEFT($A$4:$C$12;5)<>"Table");ROW($A$4:$C$12)*10^4+COLUMN($A$4:$C$12));ROWS($A$4:A4));"R0000C0000");0);"")

KASUS 6

Hasil yang diharapkan

Rumus Non Array:
=SUMPRODUCT((LOOKUP(ROW($A$3:$A$14);ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

Rumus Array:
=SUM((VLOOKUP(N(IF({1};ROW($A$3:$A$14)));IF({1\0};ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14);2;1)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

KASUS 7

Kasus kali ini agak menarik ya yaitu mengurutkan angka dalam satu cell, rumusnya adalah rumus array atau rumus CSE:
=REPT(0;LEN(A2)-LEN(SUBSTITUTE(A2;0;"")))&SUM(10^(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))-1)*LARGE(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)+0;ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))))

KASUS 8

Kasus ke 8 ini adalah menyisipkan spasi di anatara angka, rumusnya:
=TEXT(A1;REPT("0 ";LEN(A1)))

KASUS 9

Kasus terakhir ini adalah lookup dengan mengganti teks berdasar kolom kata kunci (keyword)
Rumusnya
=IFERROR(INDEX($D$3:$D$5;LOOKUP(10^308;MATCH(TRIM(MID(SUBSTITUTE(A3;" ";REPT(" ";255));{1;2;3;4;5}*255-254;255));$D$3:$D$5;0));1);"")

KASUS 10

Kasus terkahir ini adalah cara menghilangkan angka dan menghilangkan teks
1. Rumus menghilangkan huruf dari sebuah kata (Rumus Array)
=NPV(-0,9;;IFERROR(MID(A2;1+LEN(A2)-ROW(OFFSET(A$1;;;LEN(A2)));1)%;""))
2. Rumus menghilangkan angka dari sebuah kata (Rumus Biasa)
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;""))

Unduh File Excel

Penjumlahan Dari Banyak Lembar Kerja (Summing From Multiple Sheets)

Selamat pagi rekan2x penggemar excel, hari ini ane ada kasus baru lagi buat elu2x pade, yaitu bagaimana caranya melakukan penjumlahan dari beberapa sheets sekaligus, tetapi syarat utama yang harus diperhatikan adalah data harus mempunyai lay out yang sama, kasusnya seperti ini

KASUS 1

pada gambar diatas saya mempunyai 3 lembar kerja yang saya beri nama 1, 2 dan 3, kemudian datanya seperti ini, sheet 1

Sheet 2

Sheet 3

Kemudian hasilnya yang ingin ditampilkan seperti ini

Rumus yang diwarna kuning adalah
=SUMPRODUCT(SUMIF(INDIRECT("'"&{1\2\3}&"'!A1:A10");$A2;INDIRECT("'"&{1\2\3}&"'!B1:B10")))

KASUS 2

Kasus ke 2 ini saya mempunyai 3 sheet yaitu sheet source1, source2 dan source3

Sheet source1

Sheet source2

Sheet source3

Hasil yang di harapkan

Rumus:
1. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A1");;{1\2\3\4}))=B$1;SUMIF(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");;{1\2\3\4}))))

2. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A1");;{1\2\3\4}))=B$1;SUMIF(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");;{1\2\3\4})))) --> rumus array

3. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A1");;{1;2;3;4}))=B$1;SUMIF(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A:A");;{1;2;3;4})))) --> umus array

DOWNLOAD File Latihan
File_1
File_2

Lookup Dari Multiple Sheets

Met malem readers, kadang-kadang saya dapat ide untuk artikel blog ini dari pembaca yang menanyakan kasusnya, nah kali ini artikel ini ide dari Bapak Dady Lucky, terima kasih pak sudah memberikan inspirasi malam ini dan kebetulan saya belum pernah posting artikel seperti ini. Sesuai judul artikel bagaimana sih melakukan Lookup dari beberapa Sheets? Langsung ya ke tkp.....................

1. Mengambil hasil dari beberapa tab sheets (One Results)

Dan kunci keberhasilan lookup dari beberapa tabel dari sheet yang berbeda, anda harus punya tabel dengan lay out yang sama walaupun datanya beda2x, jika tidak tidak akan berhasil. Dan saya punya beberapa tab sheets disini

Ada 5 sheets ya dan kemudian hasil yang diinginkan

Dan rumusya adalah, letakkan pada sheet "Hasil" pada cell B5 kemudian copas ke bawah dan ke samping:
=IFERROR(VLOOKUP($B$2;INDIRECT("'"&INDEX({1\2\3\4\5};AGGREGATE(15;6;1/(1/{1\2\3\4\5}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5}&"'!A2:A100");$B$2)=1));ROW(A1)))&"'!$A$2:$A$100");1;0);"")


2. Mengambil hasil dari beberapa tab sheets (Multiple Results)

Dan saya punya 3 sheet saja disini

Hasil yang diharapkan

Nah rumusnya (rumus array) :
=IF(ROWS($1:1)>SUMPRODUCT(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1));"";INDEX(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!A2:B10");SMALL(IF(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!B2:B10")=$B$1;ROW(INDIRECT("1:"&ROWS($A$2:$B$10))));IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1);1+MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1))));ROWS($1:1)));COLUMNS($A:A)))

Have good night and sleep tight readers......:)

Download :
File_1
File_2