15 April 2015

Tutorial bikin template Print Label Undangan Otomatis dengan Excel

  • Share
  • [i]

Biasanya untuk mencetak label undangan kita membuat data di excel, kemudian untuk mencetaknya dengan Ms Word dengan fasilitas mail merge, serta harus mengatur ukuran kertas serta margin labelnya, kemudian isi fieldnya.
kali ini kita buat saja semuanya menjadi ringkas, buat data di excel, kemudian langsung cetak di kertas label tanpa harus njelimet setang-seting.
tutorial kali ini kita ingin membuat template untuk mencetak label undangan otomatis pada kertas label type 103, yang mana dalam 1 lembar kerta bisa memuat 12 label, dengan layout 3 kolom x 4 baris.

1. Buat sheet baru 
beri nama sheet “Data undangan” untuk memasukkan data undangan beserta alamatnya

 - Bikin kolom yang berisikan no, nama, alamat 1, alamat2
 - Bikin Status info jumlah total undangan
Klik cell G2 kemudian masukkan rumus COUNTA(B2:B1000)
Menghitung banyaknya data antara cell B2 hingga B1000 (max 1000 undangan)
     

    2. Buat Sheet Baru untuk mencetak ke printer
    Sheet ini adalah yang paling rumit/njelimet, penuh dengan rumus-rumus yang saling terkait satu sama lain
    langhkah ke 3 dan seterusnya dibuat pada sheet ini.
    sebaiknya namai sheet ini sesuai dengan kertas label yang akan dipakai nantinya (misal: cetak 103)

    3. Buat info print kertas label 103
    Kertas Label tipe 103
    Kertas label 103 berformat 3 kolom x 4 baris, sehingga dapat memuat 12 undangan per lembar
    Print per lembar > isi dengan 12
    Jumlah undangan > ='Data undangan'!G2 (ambil data dari sheet Data undangan pada cell G2)
    Lembar yang diperlukan >  =ROUNDUP(W4/W3, 0) (jumlah undangan dibagi print perlembar, kemudian dibulatkan ke atas)



    Info status Print
    4. Menampilkan tab Developer Ribbon
    Aktifkan menu developer (klik logo office di kiri atas kemudian klik excel option>popular, centang show developer tab in the ribbon)

    Cara menampilkan tab Developer

    5. Insert Spin Button
    Setelah mengaktifkan menu developer ribbon, maka akan muncul tab Developer, kemudian Insert spinbar yang berfungsi untuk scroll antar halaman dengan cara: klik ribbon tab developer>Insert> Spin Button
    Cara insert spin button

    6.  Setting Spin Button
    Klik kanan>Format Control
    Isi :
    Minimun Value = 1
    Incremental Change = 12 (karena 1 lembar kertas label 103 dapat mencetak 12 undangan)
    cell link = $S$2 
    kemudian OK
    Setting Spin Button
    Coba chek klik panah ke atas atau ke bawah, maka cell s2 akan berubah dengan kelipatan 12
    Isi cell R2 dengan ” Awal cetak” sebagai keterangan  awal data  yg akan dicetak
    Lanjutkan isi rumus cell W5 print lembar ke : pada Info Label 103
    W5 diisi =IF(S2=1,1,ROUNDUP(S2/W3,0))
    (jika awal cetak S2=1 maka isi W5=1 , jika tidak =1 maka awal cetak dibagi print per lembar dan dibulatkan ke atas)

    7. Membuat data bantuan 
    data bantuan untuk membantu fungsi vlookup mengambil data dari sheet Data undangan


    Isi cell :
    L2 diisi dengan Nama
    L3 diisi dengan Alamat 1
    L3 diisi dengan Alamat 2

    Keterangan:
    Kolom M berfungsi untuk mengambil data dengan Vlookup pada kolom 2 (nama), 3 (Alamat 1), 4 (Alamat 3) dari sheet Data undangan

    ------
    Kolom N , O,  P adalah Nomor Record yang akan diambil dengan fungsi Vlookup
    N = Kolom pertama, O = Kolom kedua, P = Kolom ketiga (kertas label 103 = 3 kolom)
    Pada N2 diisi dengan S2, sehingga perubahan pada spin button akan merubah nilai N2

    Kolom N-O-P adalah no record yang diambil dengan fungsi Vlookup
    Sebagai pengingat buat keterangan kolom 1,2,3 dan baris (warna kuning)
    Arah Flow data
    Bantuan Baris 1
    M2 diisi 2    untuk mengambil data Nama dari sheet Data Undangan
    N2 diisi S2                          O2 diisi =N2+1                                 P2 diisi =O2+1
    M3 diisi 3    untuk mengambil data Alamat 1 dari sheet Data Undangan
    N3 diisi =N2                       O3 diisi =O2                                     P3 diisi P2
    M4 diisi 4    untuk mengambil data Alamat 2 dari sheet Data Undangan
    N4 diisi =N3                       O4 diisi =O3                                     P4 diisi P3

    Bantuan Baris 2
    M7 diisi 2    untuk mengambil data Nama dari sheet Data Undangan
    N7 diisi =P4+1                   O2 diisi =N7+1                                 P2 diisi =O7+1
    M8 diisi 3    untuk mengambil data Alamat 1 dari sheet Data Undangan
    N8 diisi =N7                       O3 diisi =O7                                     P3 diisi P7
    M9 diisi 4    untuk mengambil data Alamat 2 dari sheet Data Undangan
    N9 diisi =N8                       O4 diisi =O8                                     P4 diisi P8

    Cara mudah
    Copy cell  K1:P4 ke K7, kemudian pada N7 ganti dengan P4+1

    Bantuan Baris 3
    Copy cell  K7:P9 ke K12

    Bantuan Baris 4
    Copy cell  K12:P14 ke K17

    Data Bantuan yang telah jadi
    Coba klik Spin button ke atas & ke bawah, jika semua telah benar maka harus terjadi perubahan serempak pada baris dan kolom bantuan

    Penekanan spin button akan merubah data

    Lanjutkan pada Info Label 103
    Print dari Record ke …. s/d ….
    W7 diisi  =S2 &" s/d "&P17
    (S2 adalah awal cetak, P17 adalah record terakhir per lembarnya)

    8. Ambil Data Nama, Alamat 1, Alamat 2 dari sheet Data undangan yang sesuai
    Kolom 1 , Baris 1
    Pada cell B2 isikan rumus =VLOOKUP(N2,'Data undangan'!$A$2:$D$1000,M2)
    Ambil data N2 (dalam hal ini data nomor 1) dari sheet Data undangan antara cell range $A$2 sampai dengan D1000 , dan data yang diambil adalah data dari kolom kedua pada sheet Data undangan yang berisikan data Nama

     

    Untuk cell B3 (Alamat 1) dan B4 (Alamat 2) tinggal copy rumus dari cell B2
    Sekarang coba klik tombol Spin Button ke atas / kebawah, maka pada kolom label baris 1  B2,B3,B4 akan berubah sesuai dengan Data undangan
    Tapi ternyata ketika menampilkan data yang tidak komplit, misal data dengan nama tanpa alamat maka data kosong tersebut akan ditampilkan dengan angka 0

     
    Data nomor 13, hanya ada Nama dan Alamat 1, sedangkan alamat 2 kosong, tapi ternyata ditampilkan dengan angka ”0”

    Kita harus ubah rumus agar memfilter karakter “0” dan tidak menampilkannya
    =IF(VLOOKUP(N3,'Data undangan'!$A$2:$D$1000,M3)=0,"",VLOOKUP(N3,'Data undangan'!$A$2:$D$1000,M3))
    dengan fungsi IF  kita modifikasi dari rumus sebelumnya (warna merah),
    jika data = 0 maka ditampilkan “” , jika tidak maka ditampilkan datanya
    kemudian copykan rumusnya ke sel B3 dan B4

    sekarang coba lagi klik spin button hingga menemui data kosong, jika data kosong tidak ditampilkan dengan angka “0” alias tetap kosong, berarti sudah bener rumusnya.

    Kolom 2, Baris 1
    copy rumus B2 ke sel D2 kemudian sesuaikan sehingga menampilkan data dengan tepat
    setelah menampilkan data dengan tepat tinggal copy ke D2 ke sel D3 dan D4

    Kolom 3 , Baris 1
    ulangi langkah diatas

    Baris 2, Kolom 1, 2, 3
    Blok sel B2:F4 , kemudian copy(Ctrl+C)
    Kemudian letakkan kursor ke sel B7, kemudian Paste (Ctrl+V)
    Letakkan lagi kursor ke sel B12, kemudian Paste (Ctrl+V) untuk mengisi Baris 3, Kolom 1, 2, 3
    Letakkan lagi kursor ke sel B17, kemudian Paste (Ctrl+V) untuk mengisi Baris 4, Kolom 1, 2, 3

    hasil akhir

    9. Setting print Area
    Blok sel A1 hingga G20, kemudian klik tab Page Layout klik set Print Area
    Print area
    Sesuaikan lebar dan tinggi kolom kemudian coba print ke kertas label, lakukan cara ini berulang-ulang hingga pas dengan template kertas label 103 (trial & error)

    10. Hidden semua data Bantuan yang tidak perlu ditampilkan
    dengan cara hide Sel K sampai dengan S
    11. Buat menu hyperlink antar sheet yang besar di tiap-tiap sheet (mirip tombol menu), sehingga user awam tidak binggung
    12. Protect sheet dengan password untuk melindungi rumus, lebar & tinggi kolom dari perubahan yang tidak disengaja dengan cara klik tab review protect

    NB: 
    - untuk membuat ukuran dan layout kertas label yang berbeda dapat dulangi mulai dari langkah ke 2, sedangkan data undangan cukup dibuat 1 saja.
    - Kertas label berbeda merk meski typenya sama 103, tetapi ukuran marginnya berbeda, yang sama hanya ukuran label, serta layout 3 kolom x 4 baris saja,  maka untuk mendapatkan hasil print yang tepat harus disesuaikan ulang pada langkah ke 9, setelah ok disimpan dan dinamai sesuai merk kertas label yang anda gunakan


    Selamat Mencoba
    Tutorial by CROSSFiRE team 2015

    0 comments: