Tutorial bikin template Print Label Undangan Otomatis dengan Excel

• Tweet
• 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 bingung.
Oh ya,dibawah ini adalah tampilan label undangan otomatis yang kami buat dengan dilengkapi Button Hyperlink untuk memudahkan berpindah mode/halaman dari INPUT DATA ke PRINT LABEL

X-FiRE Label Undangan Otomatis (halaman INPUT DATA)

X-FiRE Label Undangan Otomatis (halaman PRINT LABEL)

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