MENGOLAH DATA MENGGUNAKAN RECORD MACRO
3.1
Record Macro dengan Kombinasi Formula
Sekarang kita akan mencoba mengolah baris data yang lebih banyak dengan kombinasi rumus untuk mengisi kolom-kolom yang kosong dengan syarat dan kondisi tertentu. Berikut ini beberapa contoh soal data yang bisa Anda olah menggunakan Record Macro.
3.1.1 Menghitung Penjualan per Area Di sini Anda diminta untuk menggunakan Record Macro untuk mencari nilai hasil penjualan dikali dengan banyaknya penjualan dari area tertentu saja. Untuk lebih detailnya, buka file Bab3_SoalPenjualanArea.xlsx. 51
Gambar 3.1 Data Bab3_SoalPenjualanArea.xlsx
Tugas Anda adalah sebagai berikut: 1. Menggunakan referensi dari file PERSONAL.xlsb, buat kolom baru bernama Harga, dan mengisinya berdasarkan kriteria berikut ini: Kursi = 1000 Meja = 2000 Lemari = 3000 Rak = 4000 Kasur = 5000 Lampu = 6000 2. Menghapus seluruh baris data dengan syaratnya adalah data di kolom ‘Banyak Penjualan’ = 0. 3. Menghapus seluruh baris data dengan syaratnya adalah data di kolom ‘Area’ = Jakarta dan Bogor. 52
4. Isi kolom ‘Jumlah Penjualan Rp.’ Dari hasil kali kolom ‘Banyaknya Penjualan’ dengan kolom ‘Harga’.
Gambar 3.2 Data Bab3_SoalPenjualanArea.xlsx
Anda bisa coba kerjakan sendiri dahulu, atau Anda bisa ikuti langkah-langkahnya sebagai berikut: 1. Buka file: Bab3_SoalPenjualanArea.xlsx. 2. Pilih tab View, klik tombol Unhide. 3. Tekan OK. 4. Workbook PERSONAL keluar. 5. Klik di cell G1, ketik Harga. 6. Klik di cell G2, ketik 1000. 7. Klik di cell G3, ketik 2000. 8. Klik di cell G4, ketik 3000. 9. Klik di cell G5, ketik 4000. 53
10. Klik di cell G6, ketik 5000. 11. Klik di cell G7, ketik 6000.
Gambar 3.3 Data di workbook PERSONAL ditambah harga
12. Kembali ke workbook Bab3_SoalPenjualanArea. 13. Klik cell F1, tekan tombol Delete di keyboard. 14. Klik cell A1. 15. Pilih tab Developer dan klik tombol Record Macro: Macro name: Soal_PenjualanArea Shortcut: y Store macro in: Personal Macro Workbook Description: Jawaban untuk Soal_PenjualanArea 16. Klik OK. 17. Klik huruf A hingga kolom A terblok. 18. Tekan Ctrl+C. 19. Klik kanan pada huruf B sampai kolom B terblok vertical, dan keluar menu, pilih Insert Copied Cells.
54
Gambar 3.4 Blok di kolom B
20. Klik B1, ketik Harga. 21. Tekan Enter. 22. Di cell B2, ketik formula: =VLOOKUP( 23. Klik cell A2. 24. Tekan tombol koma di keyboard. 25. Pilih tab View, PERSONAL.xlsb.
klik
tombol
Switch
Windows,
pilih
26. Klik cell E1 tahan lalu bawa ke cell G7 sampai terblok kotak dari E1 ke G7.
55
Gambar 3.5 Blok di cell E1 ke G7
27. Tekan tombol koma pada keyboard. 28. Tekan angka 3 pada keyboard. 29. Tekan tombol koma pada keyboard. 30. Ketik ‘FALSE’. 31. Tekan Enter. 32. Kembali ke workbook Bab3_SoalPenjualanArea. 33. Klik B2. 34. Tekan Ctrl+C. 35. Tekan Shift tahan lalu tekan End lalu tekan panah ke bawah di keyboard. 36. Tekan Ctrl+V. 37. Setelah selesai membuat kolom ‘Harga’ sekarang kita isi kolom ‘Jumlah Penjualan Rp.’. 38. Klik huruf D sampai terblok kolom D.
56
Gambar 3.6 Blok di kolom E
39. Tekan Ctrl+C. 40. Klik di huruf E sampai terblok kolom E. 41. Tekan Ctrl+V. 42. Klik cell E1, ketik ‘Jumlah Penjualan’. 43. Di cell E2. 44. Ketik =B2*C2, tekan Enter. 45. Klik cell E2. 46. Tekan Ctrl+C. 47. Tekan Shift tahan lalu tekan End lalu tekan panah ke bawah di keyboard. 48. Tekan Ctrl+V.
57
Gambar 3.7 Blok di kolom E
49. Selesai mengisi kolom Jumlah penjualan, sekarang kita beralih untuk menghapus nilai 0 di kolom ‘Banyak Penjualan’. 50. Klik huruf C hingga terblok kolom C. 51. Tekan Ctrl+C. 52. Klik kanan di huruf D, keluar menu, pilih Insert Copied Cells. 53. Klik cell D2, ketik =IF(C2>0,1,0). 54. Klik lagi cell D2. 55. Tekan Ctrl+C. 56. Tekan Shift tahan lalu tekan End lalu tekan panah ke bawah di keyboard. 57. Tekan Ctrl+V.
58
58. Klik huruf D hingga terblok kolom D. 59. Tekan Ctrl+C. 60. Tekan Alt tahan lalu tekan E, kemudian tekan S, lalu tekan V. 61. Klik OK atau Enter. 62. Pilih tab Data klik tombol Sort.
Gambar 3.8 Kotak dialog Sort
63. Di bagian sort by pilih nama kolom ‘Banyak penjualan’ yang ke-2. 64. Pada bagian order, pilih Largest to Smallest. 65. Klik di huruf D sampai terblok vertical kolom D. 66. Pilih tab Home, klik tombol Find & Select, pilih Replace. 67. Keluar kotak dialog Find and Replace. 68. Isi di bagian Find what dengan 1, kosongkan bagian Replace with.
59
Gambar 3.9 Kotak dialog Find and Replace
69. Klik Replace All. 70. Klik OK, dan tutup Find and Replace, klik Close. 71. Klik cell D1. 72. Pada keyboard tekan tombol End lalu tombol panah ke bawah. 73. Kursor lompat ke cell D17. 74. Tekan Shift tahan lalu tekan End, lalu tekan panah ke bawah di keyboard. 75. Klik kanan pada area yang terblok, pilih Delete. 76. Pilih Entire Row, klik OK. 77. Klik kanan huruf D sampai terblok kolom D, pilih Delete.
Gambar 3.10 Data dengan nilai 0 dihapus
60
78. Setelah kita menghapus data dengan nilai 0 di kolom ‘Banyak Penjualan’, sekarang kita akan hapus Area Jakarta dan Bogor. 79. Klik di huruf D hingga terblok kolom D. 80. Tekan Ctrl+C. 81. Klik kanan di huruf E, pilih Insert Copied Cells. 82. Klik cell E2, ketik =IF(OR(D2="Jakarta",D2="Bogor"),0,1) tekan Enter. 83. Klik kembali cell E2. 84. Tekan Ctrl+C. 85. Tekan Shift tahan lalu tekan End, lalu tekan panah ke bawah di keyboard. 86. Tekan Ctrl+V. 87. Klik huruf E hingga terblok kolom E. 88. Tekan Ctrl+C. 89. Tekan Alt tahan lalu tekan E, kemudian tekan S, lalu tekan V. 90. Klik OK atau tekan Enter. 91. Klik cell E2. 92. Pilih tab Data, klik tombol Sort. 93. Di bagian sort by pilih nama kolom ‘Area’ yang ke-2. 94. Pada bagian order, pilih Largest to Smallest. 95. Klik di huruf E sampai terblok vertical kolom E. 96. Pilih tab Home, klik tombol Find & Select, pilih Replace. 97. Keluar kotak dialog Find and Replace. 98. Isi di bagian Find what dengan 1, kosongkan bagian Replace with.
61
Gambar 3.11 Kotak dialog Find and Replace
99. Klik Replace All. 100. Klik OK, dan tutup Find and Replace, klik Close. 101. Klik cell E1. 102. Pada keyboard tekan tombol End lalu tombol panah ke bawah. 103. Kursor lompat ke cell E12. 104. Tekan Shift tahan lalu tekan End, lalu tekan panah ke bawah di keyboard. 105. Klik kanan pada area yang terblok, pilih Delete. 106. Pilih Entire Row, klik OK. 107. Klik kanan huruf E sampai terblok kolom E, pilih Delete.
Gambar 3.12 Hasil akhir
62
108. Stop recording. 109. Buka workbook PERSONAL. 110. Pilih tab View, klik Hide. 111. Tutup dengan klik tanda ‘x’ di sudut kanan atas. 112. Muncul kotak dialog ‘Do you want to save…’ klik No. 113. Klik lagi tanda ‘x’ di sudut kanan atas, muncul kotak dialog bertuliskan ‘Do you want save the changes you made to the personal macro…’ klik Yes. 114. Buka kembali Bab3_SoalPenjualanArea.xlsx. 115. Mainkan record, tekan Ctrl+Y.
3.1.2 Menghitung Penjualan E-book Dengan tetap menggunakan Record Macro, Anda diharuskan untuk mencari nilai hasil penjualan ebook dari internet, tugas Anda adalah mengisi kolom yang kosong dengan nilai mata uang Jepang JPY=7000. Untuk lebih detailnya, buka file Bab3_SoalPenjualanEbook.xlsx, di dalamnya terdapat 2 sheet berisi data.
Gambar 3.13 Data di Sheet1 Bab3_SoalPenjualanEbook.xlsx
63
Gambar 3.14 Data di Sheet2 Bab3_SoalPenjualanEbook.xlsx
Anda bisa coba kerjakan sendiri dahulu, atau Anda bisa ikuti langkah-langkahnya sebagai berikut: 1. Buka file Bab3_SoalPenjualanEbook.xlsx. 2. Pilih tab View, klik tombol Unhide. 3. Pilih workbook PERSONAL. 4. Tambahkan di cell A4 ketik JPY, dan di cell B4 ketik 7000. 5. Kembali ke workbook SoalPenjualanEbook sheet1. 6. Pilih tab Developer dan klik tombol Record Macro: Macro name: Soal_PenjualanEbook Shortcut: u Store macro in: Personal Macro Workbook Description: Jawaban untuk Soal_PenjualanEbook 64
7. Klik OK. 8. Klik cell A1. 9. Pilih tab Data, klik tombol Remove Duplicates. 10. Kotak dialog Remove Duplicates muncul.
Gambar 3.15 Kotak dialog Remove Duplicates
11. Klik OK. 12. Hasilnya adalah mengambil 3 nama negara saja, sedangkan sisanya nama yang sama dihapus. 13. Klik huruf A sampai kolom A terblok vertikal ke bawah. 14. Tekan Ctrl+C. 15. Klik kanan pada huruf B hingga terblok kolom B, pilih Insert Copied Cells. 16. Klik cell B1, ketik ‘Jumlah Penjualan’ tekan Enter. 17. Klik cell B2. 18. Ketik =SUMIF( 19. Klik sheet2 di sudut kiri bawah. 20. Blok A1 sampai C25. 21. Tekan tombol F4 untuk mengikat nilainya agar tidak tergeser saat nanti di-copy. 65
22. Tekan tombol koma pada keyboard. 23. Klik Sheet1. 24. Klik cell A2. 25. Tekan tombol koma di keyboard. 26. Klik Sheet2. 27. Blok cell B1 hingga B25. 28. Tekan tombol F4 pada keyboard. 29. Tekan Enter. 30. Anda akan dibawa Bab3_SoalPenjualanEbook.
kembali
ke
workbook
Gambar 3.16 Formula untuk mengisi Jumlah Penjualan
31. Klik cell B2. 32. Tekan Ctrl+C. 33. Tekan Shift tahan lalu tekan tombol End, lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell B4. 34. Tekan Ctrl+V.
Gambar 3.17 Kolom Jumlah Penjualan terisi
35. Klik huruf A lagi sampai terblok. 36. Tekan Ctrl+C. 37. Klik kanan di huruf C hingga terblok, pilih Insert Copied Cells. 38. Klik cell C1, ketik ‘Refund’, tekan Enter. 66
39. Di cell C2, ketik =SUMIF( 40. Klik Sheet2. 41. Blok cell A1 sampai C25. 42. Tekan tombol F4 pada keyboard. 43. Tekan tombol koma pada keyboard. 44. Klik Sheet1. 45. Klik cell A2. 46. Tekan tombol koma pada keyboard. 47. Klik Sheet2. 48. Blok cell C1 sampai C25. 49. Tekan tombol F4 pada keyboard. 50. Tekan Enter. 51. Anda akan dibawa Bab3_SoalPenjualanEbook.
kembali
ke
workbook
Gambar 3.18 Formula untuk mengisi kolom Refund
52. Klik cell C2. 53. Tekan Ctrl+C. 54. Tekan Shift tahan lalu tekan tombol End, lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell C4. 55. Tekan Ctrl+V.
Gambar 3.19 Kolom Refund terisi
67
56. Klik huruf A sampai terblok. 57. Tekan Ctrl+C. 58. Klik kanan pada huruf D sampai terblok, pilih Insert Copied Cells. 59. Klik di cell D1, ketik ‘Rasio Refund ke Penjualan’. 60. Klik cell D2. 61. Ketik =C2/B2, tekan Enter. 62. Klik lagi cell D2. 63. Tekan Ctrl+C. 64. Tekan Shift tahan lalu tekan tombol End lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell D4. 65. Tekan Ctrl+V. 66. Pilih tab Home, klik tombol bertanda %.
Gambar 3.20 Kolom Rasio Refund ke Penjualan terisi
67. Klik huruf A lagi hingga terblok. 68. Tekan Ctrl+C. 69. Klik kanan pada huruf E hingga terblok vertical, pilih Insert Copied Cells. 70. Klik E1, ketik ‘Jenis Mata Uang’ tekan Enter. 71. Ketik =IF(A2="Amerika","USD",IF(A2="Singapore","SGD","JPY")) cell E2. 72. Tekan Enter. 73. Klik lagi cell E2. 74. Tekan Ctrl+C. 68
di
75. Tekan Shift tahan lalu tekan tombol End lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell E4. 76. Tekan Ctrl+V.
Gambar 3.21 Kolom Jenis Mata Uang terisi
77. Klik di huruf A hingga terblok. 78. Tekan Ctrl+C. 79. Blok pada huruf F dan G, klik kanan dan pilih Insert Copied Cells. 80. Klik pada cell F1 ketik ‘Kurs’ tekan Enter. 81. Pada cell F2, ketik =VLOOKUP(E2,[PERSONAL.XLSB]Sheet1!$A$1:$B$4,2,FALSE ) 82. Klik di cell F2, tekan Ctrl+C 83. Tekan Shift tahan lalu tekan tombol End lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell F4. 84. Tekan Ctrl+V. 85. Klik cell G1 ketik ‘Equivalent Rp.’ tekan Enter. 86. Di cell G2, ketik =(B2-C2)*F2 87. Tekan Enter 88. Klik cell G2. 89. Tekan Ctrl+C. 90. Tekan Shift tahan lalu tekan tombol End, lalu tekan tanda panah ke bawah di keyboard sampai terblok ke cell G4. 91. Tekan Ctrl+V.
69
92. Blok huruf H sampai L tekan tombol Delete. 93. Stop recording. 94. Buka workbook PERSONAL. 95. Pilih tab View, klik Hide. 96. Tutup dengan klik tanda ‘x’ di sudut kanan atas. 97. Muncul kotak dialog ‘Do you want to save…’ klik No. 98. Klik lagi tanda ‘x’ di sudut kanan atas, muncul kotak dialog bertuliskan ‘Do you want save the changes you made to the personal macro…’ klik Yes. 99. Buka kembali Bab3_SoalPenjualanEbook.xlsx. 100. Mainkan record, tekan Ctrl+U.
3.2
Mengambil Data dari Pivot
Pivot adalah sarana filter data sesuai dengan format yang kita butuhkan. Misalkan dari ribuan baris data kita hanya ingin mengetahui beberapa nilai tertentu saja sesuai dengan keinginan kita. Untuk lebih jelasnya ikuti langkah-langkah berikut ini: 1. Buka file: Bab3_Pivot.xlsx.
Gambar 3.22 Data file Bab3_Pivot
70
2. Pilih tab Developer dan klik tombol Record Macro: Macro name: Pivot_Bab3_Pivot Shortcut: b Store macro in: Personal Macro Workbook 3. Description: Pivot untuk Bab3_Pivot.xlsx. 4. Klik OK. 5. Klik di cell A1. 6. Pilih tab Insert, klik tombol PivotTable.
Gambar 3.23 Tab Insert
7. Muncul kotak dialog Create PivotTable, klik OK. 8. Anda akan dibawa ke sheet baru berisi PivotTable yang masih kosong.
Gambar 3.24 PivotTable yang masih kosong
71
9. Klik di cell A3. 10. Klik tombol kanan mouse, pilih PivotTable options. 11. Muncul kotak dialog PivotTable options.
Gambar 3.25 Tab Display di PivotTable Options
12. Pilih tab Display, centang checkbox ‘Classic Pivot Table layout. 13. Klik OK. 14. Arahkan pandangan Anda ke bagian paling kanan, di sana terlihat nama-nama kolom dari sumber data.
Gambar 3.26 Daftar nama-nama kolom yang akan difilter
15. Di kanan bawah Anda bisa melihat area di mana Anda ingin meletakkan nama-nama kolom dari sumber data. 72
16. Klik pada nama kolom ‘Cabang’ tahan kemudian bawa kursor ke kotak kosong Row Labels lalu lepas.
Gambar 3.27 Nama kolom ‘Cabang’ di dalam area Row Labels
17. Sama seperti sebelumnya, klik pada nama kolom ‘Penjualan’ tahan kemudian bawa kursor ke kotak kosong Values lalu lepas.
Gambar 3.28 Hasil pivot table
18. Stop Recording. 19. Klik tanda ‘x’ di sudut kanan atas untuk close. 20. Muncul kotak dialog ‘Do you want to save the changes..’ pilih No. 21. Buka kembali file: Bab3_Pivot.xlsx.
73
22. Mainkan record, dengan menekan Ctrl+B. 23. Save record.
3.3
Record untuk Lebih dari 1 Window
Record Macro juga bisa merekam jika Anda meng-copy paste dari satu window ke window lainnya. Menggunakan file contoh kita akan mencoba memindahkan hasil pivot dari satu workbook yang satu ke workbook yang lainnya. 1. Buka file: Bab3_2windows.xlsx. 2. Buka file: Bab3_2windows_b.xlsx. 3. Akan terbuka 2 window dengan isi yang berbeda.
Gambar 3.29 Data dari file Bab3_2windows.xlsx
Gambar 3.30 Data dari file Bab3_2windows_b.xlsx
74
4. Pilih workbook Bab3_2windows. 5. Pilih tab Developer dan klik tombol Record Macro: Macro name: CopyPivot_ Bab3_2windows Shortcut: n Store macro in: Personal Macro Workbook Description: CopyPivot untuk Bab22windows 6. Klik OK. 7. Klik di cell A1. 8. Pilih tab Insert, klik tombol PivotTable. 9. Muncul kotak dialog Create PivotTable, klik OK. 10. Klik di cell A3. 11. Klik tombol kanan mouse, pilih PivotTable options. 12. Muncul kotak dialog PivotTable options. 13. Pilih tab Display, centang checkbox ‘Classic Pivot Table layout’. 14. Klik OK. 15. Arahkan pandangan Anda ke bagian paling kanan, di sana terlihat nama-nama kolom dari sumber data. 16. Pindahkan semua nama kolom yang ada di bagian paling kanan itu ke kanan bawah hingga mengisi masing-masing kotak sebagai berikut: Row lables
: Produk
Column labels
: Periode
Values
: Penjualan
75
Gambar 3.31 Area telah diisi nama-nama kolom
Gambar 3.32 Hasil pivot
17. Arahkan kursor di huruf A, hingga kursor berubah menjadi tanda panah mengarah ke bawah, klik tahan dan bawa kursor ke D, hingga membentuk blok area abu-abu antara A hingga D.
76
Gambar 3.33 Blok antara kolom A hingga D
18. Tekan Ctrl+C. 19. Klik di cell F1. 20. Tekan Alt+E lalu tekan S kemudian tekan V secara bergiliran, hingga keluar kotak dialog Paste Special.
Gambar 3.34 Kotak dialog Paste Special
21. Klik OK.
77
Gambar 3.35 Hasil copy paste special
22. Arahkan kursor ke G5 klik tombol kiri mouse tahan, lalu bawa kursor ke H8, lepas tombol kiri mouse. 23. Tekan Ctrl+C. 24. Pilih workbook Bab3_2windows. 25. Arahkan kursor ke C5. 26. Tekan Alt+E lalu tekan S kemudian tekan V secara bergiliran, hingga keluar kotak dialog Paste Special dan klik OK.
Gambar 3.36 Hasil akhir
27. Pilih tab Developer, klik tombol Stop recording. 28. Klik tanda ‘x’ di sudut kanan atas untuk close. 29. Muncul kotak dialog ‘Do you want to save the changes..’ pilih No. 30. Buka kembali file: Bab3_2windows_b.xlsx.
Bab3_2windows.xlsx
31. Mainkan record, dengan menekan Ctrl+N. 32. Save record. 78
dan
