Kubrick

Read More

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

sumber = Range("Rekap!J1")   'Range J1 pada sheet rekap adalah nama file target
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"

    

    Windows(sumber).Activate 'Mengaktifkan file sumber
    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()

    

    Sheets("BD").Activate        'Memilih sheet BD
    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

---

Terdapat 3 kode macro yaitu:

• 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!

Read More

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!

Read More

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!

Read More

Menjalankan Macro Otomatis Saat Sebuah Sel Berubah

Menjawab pertanyaan Saudara Huda melalui email tentang macro VBA yang otomatis dijalankan saat sebuah sel berubah nilainya. Berikut gambarannya:

Yang dishading hijau adalah sel-sel yang sudah ada formulanya di mana jika sel A1 berubah nilainya, maka isi tabel tersebut langsung berubah. Jadi bagaimana caranya jika sel A1 dirubah nilainya menjadi 1, 2, atau 3 dan otomatis baris yang nilainya kosong (baris 26 s/d 33) langsung ter-hidden?

Berikut caranya:

1. Di Excel silahkan buka Visual Basic Editor pada menu Tools > Macro > Visual basic Editor (Alt + F11).
2. Klik ganda pada sheetnya lalu masukkan code VBAnya:
   
   Private Sub Worksheet_Change(ByVal Target As Range)
   
   If Target.Address = "$A$1" Then
        Worksheet_Calculate                    'macro yang akan dijalankan saat sel A1 berubah
   End If
   End Sub
   
   
   
3. Pilih Insert > Module dan tuliskan code VBA berikut:
   
   Sub Worksheet_Calculate()
       Dim Rng As Integer
       Range("BASTB!H24:H33").Select        'Menentukan baris mana saja yang akan di hide
       Selection.EntireRow.Hidden = False      'Baris akan di unhide
       Rng = Selection.Rows.Count
       ActiveCell.Offset(0, 0).Select
       Application.ScreenUpdating = True
       For i = 1 To Rng
       If ActiveCell.Value = "-" Then                'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
       Selection.EntireRow.Hidden = True        'Baris akan di hide
       ActiveCell.Offset(1, 0).Select
       Else
       ActiveCell.Offset(1, 0).Select
       End If
       Next i
   
   'Jika ada baris lain yang akan dihide, maka tambahkan lagi codingnya
   
       Range("BASTB!H41:H50").Select        'Menentukan baris mana saja yang akan di hide
       Selection.EntireRow.Hidden = False      'Baris akan di unhide
       Rng = Selection.Rows.Count
       ActiveCell.Offset(0, 0).Select
       Application.ScreenUpdating = True
       For i = 1 To Rng
       If ActiveCell.Value = "-" Then                 'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
       Selection.EntireRow.Hidden = True        'Baris akan di hide
       ActiveCell.Offset(1, 0).Select
       Else
       ActiveCell.Offset(1, 0).Select
       End If
       Next i
   
   'Akhiri dengan menambahkan coding di bawah ini
       Range("BASTB!A1").Select          'menempatkan sel kembali ke A1
   End Sub
   
   
4. Jika ada beberapa baris yang akan dihide, maka ulangi code Simpan file tersebut dan silahkan coba
   

Read More

Memisahkan Nama Dengan 3 (Tiga) Kata

Menjawab pertanyaan saudara Yoga pada postingan saya yang berjudul "Memisahkan Nama Depan dan Nama Belakang" dan meminta saya untuk dibuatkan rumusnya, maka berikut adalah rumusnya.

Rumus saya buat dalam dua versi:

1. Mencari karakter spasi (" ") sebagai pemisah di antara ketiga nama (kolom B & C) sedang nama yang terpisah terdapat di kolom D, E, dan F.
2. Menggabungkan rumus (tanpa kolom B & C)

Lihat ilustrasi berikut:

Versi I:

Rumus:

• Kolom B: =(FIND(" ",A1,1)+1)
• Mencari karakter spasi pertama. Spasi ditemukan di karakter ke-8. Plus 1 (+1) artinya mencari karakter untuk nama kedua, yaitu karakter ke 9.
• Kolom C: =FIND(" ",A1,B1)
• Mencari karakter spasi kedua. Karakter spasi kedua ditemukan dikarakter ke-14.
• Kolom D: =LEFT(A1,FIND(" ",A1,1)-1)
• Mendapatkan nama pertama, yaitu karakter paling kiri sebanyak karakter sebelum (-1) spasi.
• Kolom E: =MID(A1,FIND(" ",A1,1)+1,(C1-B1))
• Mendapatkan nama tengah, yaitu dimulai dari karakter di kolom B, sebanyak karakter kolom C minus kolom B. Kolom C minus kolom B akan mendapatkan nilai sebanyak jumlah karakter nama kedua. 
• Kolom F: =MID(A1,FIND(" ",A1,C1)+1,100)
• Mendapatkan nama belakang, yaitu dimulai dari karakter di kolom C, asumsi sebanyak 100 karakter.

Versi II

Jika digabung, maka rumusnya akan sebagai berikut:

• Kolom B: =LEFT(A1,FIND(" ",A1,1)-1)
• Kolom C: =MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1)
• Kolom D: =MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100)

MENGATASI HASIL ERROR (#VALUE)

Ketiga rumus di atas harus berisikan 3 nama seperti "Maulana Malik Ibrahim". Jika namanya hanya "Maulana Malik" atau "Maulana" saja maka hasilnya akan menjadi #VALUE. Untuk mengatasi masalah ini maka rumus harus ditambah dengan rumus ISERROR dan IF.

Syntaxnya seperti ini:
=IF(ISERROR(rumus_awal)=TRUE,rumus_alternatif,rumus_awal)

Artinya: Jika rumus_awal mengasilkan nilai error (TRUE), maka jalankan rumus_alternatif, jika tidak error maka jalankan rumus_awal. Maka rumusnya akan seperti ini:

• Kolom B: =IF(ISERROR(FIND(" ",A1,1))=TRUE,LEFT(A1,100),LEFT(A1,FIND(" ",A1,1)-1))
• Kolom C: =IF(ISERROR(FIND(" ",A1,1))=TRUE,"",IF(ISERROR(MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1))=TRUE,MID(A1,FIND(" ",A1,1)+1,100),MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,(FIND(" ",A1,1)+1))+1)-(FIND(" ",A1,1)+1)-1)))
• Kolom D: =IF(ISERROR(MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100))=TRUE,"",MID(A1,FIND(" ",A1,(FIND(" ",A1,1)+1))+1,100))

Hasilnya memang akan menjadi panjang, tapi dengan rumus ini hasilnya akan menjadi fleksibel yaitu bisa dengan 1 nama, 2 nama, maupun 3 nama. Selamat mencoba!

Read More

Marionette

Read More