Featured Posts
Copy ISI Banyak Sheet dalam Sekejap!
Mengkopi banyak sheet memang mudah dengan menu Move or Copy sheet. Tapi bagaimana jika ingin copy isinya saja dengan jumlah sheet yang banyak? Apakah Anda akan melakukan Copy dan Paste satu per satu sheet?
Kenapa harus copy isinya saja? Kenapa tidak copy sheet saja biar mudah dan tidak repot?
Jika Anda mempunyai file dengan banyak sheet di mana terdapat sheet rekap yang berisi rumus yang link dengan sheet-sheet tersebut, tentunya jika melakukan Copy Sheet dan Delete Sheet rumus Anda akan menjadi #REF! semua!
Biar mempermudah, berikut gambarannya:
File Sumber: Adalah file yang berisi data mentah yang tidak terdapat rumus apapun. Nama dan jumlah sheet adalah baku, misal terdapat 10 cabang dengan kode cabang BD, MT, SN, CD, AY, PK, PG, GR, BU, dan TP.
File Target: Adalah file yang terdapat 10 sheet cabang dengan nama BD, MT, SN, CD, AY, PK, PG, GR, BU, TP dan sheet Rekap yang merupakan rumus yang link ke sheet-sheet tersebut.
Dengan memanfaatkan macro, maka copy isi sheet akan sangat mudah dilakukan. Berikut caranya:
- Pada file target buka Visual Basic Editor (Alt + F11)
- Pilih Insert > Module
- Tuliskan kode macro berikut:
Sub copy_data()
Dim sumber As String
Dim target As String
target = Range("Rekap!J2") 'Range J2 pada sheet rekap adalah nama file sumber
On Error GoTo ErrorHandler
MsgBox "Apakah file " + sumber + " sudah dibuka?", Buttons:=vbOKOnly + vbQuestion, Title:="Copy Sheet dalam Sekejap - Powered by http://formula-excel.blogspot.com"
Sheets("BD").Activate 'Memilih sheet BD file sumber
Columns("A:P").Select 'Menyeleksi kolom A s/d P (kolom yang akan dicopy)
Selection.Copy 'Copy
Windows(target).Activate 'Mengaktifkan file target
Sheets("BD").Activate 'Memilih sheet BD file target
Columns("A:P").Select 'Menyeleksi kolom A s/d P (kolom yang akan dipaste)
ActiveSheet.Paste 'Paste
Application.CutCopyMode = False 'Menonaktifkan seleksi
Range("C1").Select 'Meletakkan kursor di C1
Windows(sumber).Activate
Sheets("MT").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("MT").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("SN").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("SN").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("CD").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("CD").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("AY").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("AY").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("PK").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("PK").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("PG").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("PG").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("GR").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("GR").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("BU").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("BU").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Windows(sumber).Activate
Sheets("TP").Activate
Columns("A:P").Select
Selection.Copy
Windows(target).Activate
Sheets("TP").Activate
Columns("A:P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
GoTo WrapUp
ErrorHandler:
MsgBox "Coba periksa lagi:" & vbCrLf & vbCrLf & "1. Apakah file sumber bernama " + sumber + "? " & vbCrLf & "2. Apakah file " + sumber + " sudah dibuka? " & vbCrLf & "3. Apakah file ini bernama " + target + "?" & vbCrLf & "4. Apakah jumlah dan nama sheet dari file target sama dengan jumlah dan nama sheet di file sumber? " & vbCrLf & vbCrLf & "Jika sudah benar, silahkan jalankan kembali.", vbOKOnly + vbCritical, "Copy Sheet dalam Sekejap - Powered by http://formula-excel.blogspot.com"
Err.Clear
WrapUp:
Application.ScreenUpdating = True
Application.Interactive = True
End Sub
Sub clear_format()
Columns("A:P").Select
Selection.UnMerge
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = True
Selection.Font.Bold = False
Range("C1").Select
End Sub
Sub delete_data()
clear_format 'Menjalankan macro clear_format
Sheets("MT").Activate
clear_format
Sheets("SN").Activate
clear_format
Sheets("CD").Activate
clear_format
Sheets("AY").Activate
clear_format
Sheets("PK").Activate
clear_format
Sheets("PG").Activate
clear_format
Sheets("GR").Activate
clear_format
Sheets("BU").Activate
clear_format
Sheets("TP").Activate
clear_format
End Sub
- copy_data() yang fungsinya untuk mengkopi sheet BD, MT, SN, CD, AY, PK, PG, GR, BU, TP dari file sumber ke file target
- delete_data() yang fungsinya untuk memilih sheet pada file target dan menjalankan macro clear_format.
- clear_format() yang fungsinya untuk menghapus isi (clear content - bukan hapus baris/kolom).
Kembali pada Excel:
Pada sheet Rekap sel J1 tuliskan nama file sumber. Misal File-Sumber.xls
Pada sheet Rekap sel J2 tuliskan nama file target. Misal File-Target.xls
Selanjutnya Anda dapat menjalankan macro copy_data() atau delete_data() melalui menu Tools > Macro > Macros atau Alt + F8.
Selamat Mencoba!
Menjinakkan Hasil Rumus Yang Error
Hasil rumus yang error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL! selain akan membuat tampilan tabel Excel Anda tidak enak dipandang juga tidak akan bisa dijumlahkan. Tapi hanya dengan rumus sederhana IFERROR maka hasilnya akan menjadi lebih baik.
Syntaxnya adalah =IFERROR(value, value_if_error)
- Value = nilai atau sel yang error
- Value_If_Error = nilai yang akan dimunculkan jika sel tersebut error. Bisa diisi dengan kosong "", nol (0), atau teks apa saja, misal "Asset Tidak Ada"
Contohnya sbb:
Baris 3 s/d 9 menggunakan rumus VLOOKUP biasa tanpa ditambahkan rumus IFERROR. Jika Nama Asset yang dicari tidak ada, maka hasilnya adalah #N/A. Begitu juga kolom Nilai. Jika dijumlahkan (baris 10) maka hasilnya akan #N/A juga.
Sedang baris 14 s/d 20 ditambahkan rumus IFERROR sehingga hasil #N/A akan diganti menjadi kalimat "Asset Tidak Ada" dan angka nol "0" pada kolom Nilai. Sehingga tetap bisa dijumlahkan (baris 21).
Penjelasan rumus:
- F14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,2,0),"Asset Tidak Ada")
- Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,2,0) adalah #N/A maka yang muncul adalah "Asset Tidak Ada".
- Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP.
- G14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,3,0),0)
- Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,3,0) adalah #N/A maka yang muncul adalah angka nol (0).
- Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP.
Rumus IFERROR ini juga bisa digunakan untuk hasil rumus yang error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!
Mengenal Lebih Dekat Fungsi TEXT
Apakah Anda pernah menggunakan fungsi TEXT? Fungsi TEXT adalah fungsi yang disediakan oleh Excel untuk merubah format angka, tanggal, persentase, waktu, dll menjadi format yang mudah dibaca ketika digabungkan dengan teks yang lain.
Syntaxnya mudah saja: =TEXT(value, format_text)
- Value = nilai yang akan diformat, bisa dirujuk ke sebuah sel.
- Format_Text = Format yang akan digunakan.
Contohnya sbb:
Berikut penjelasannya:
- A1: 3/8/2011 dengan format sel "d-mmm-yy"
- B1: ="Sales "&TEXT(A1,"mmmm yyyy")
- Menggabungkan kata "Sales " dengan tanggal di A1 yang formatnya adalah "mmmm yyyy" sehingga menghasilkan kalimat "Sales March 2011"
- Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Sales 40610"
- B3: ="Purchase "&TEXT(A1,"mmm-yy")
- Menggunakan format yang lain yaitu "mmm-yy" sehingga menghasilkan kalimat "Purchase Mar-11"
- A6: 10000 dengan format sel Accounting - Decimal Places = 2
- B6: ="Total Pembayaran "&TEXT(A6,"Rp 0.00")
- Menggabungkan kalimat "Total Pembayaran " dengan angka di A6 yang formatnya adalah "Rp 0.00" sehingga menghasilkan kalimat "Total Pembayaran Rp 10000.00"
- Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Total Pembayaran 10000"
- B8: ="Total Penerimaan "&TEXT(A6,"Rp 0,000")
- Menggunakan format yang lain yaitu "Rp 0,000" (dengan pemisah ribuan dan tanpa dua digit desimal) sehingga menghasilkan kalimat "Total Penerimaan Rp 10,000"
- B10: ="Sebanyak "&TEXT(A6,"0,000")&" orang menghadiri acara tersebut."
- Menggunakan format yang lain yaitu "0,000" (tanpa tanda Rp dan dengan pemisah ribuan) sehingga menghasilkan kalimat "Sebanyak 10,000 orang menghadiri acara tersebut."
- A12: 85.25%
- B12: ="Penerimaan bulan ini hanya mencapai "&TEXT(A12,"0.00%")&" dibanding bulan lalu."
- Menggabungkan kalimat "Penerimaan bulan ini hanya mencapai " dengan persentase di A12 dan "dibanding bulan lalu." sehingga menghasilkan kalimat "Penerimaan bulan ini hanya mencapai 85.25% dibanding bulan lalu."
- Jika tanpa rumus TEXT maka hasilnya akan menjadi "Penerimaan bulan ini hanya mencapai 0.8525 dibanding bulan lalu."
- B14: ="Pada tanggal "&TEXT(A1,"d-mmm-yy")&" diterima pembayaran sebesar "&TEXT(A6,"Rp 0,000.00")&" atau sebesar "&TEXT(A12,"0,00%")&" dari total yang seharusnya dibayar."
- Menggabungkan antara tanggal, nilai, dan persentase menjadi suatu kalimat lengkap dengan format yang mudah dibaca "Pada tanggal 8-Mar-11 diterima pembayaran sebesar Rp 10,000.00 atau sebesar 085% dari total yang seharusnya dibayar."
- Jika tanpa rumus TEXT maka hasilnya akan menjadi "Pada tanggal 40610 diterima pembayaran sebesar 10000 atau sebesar 0.8525 dari total yang seharusnya dibayar."
Silahkan selanjutnya Anda coba-coba dengan format yang lain. Untuk memudahkan Anda dalam menentukan format yang diinginkan, contek saja dari Custom Format Cells seperti berikut:
Mau lebih maksimal bereksplorasi dengan fungsi TEXT? Silahkan belajar langsung ke sumbernya! http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx
Selamat mencoba!
