Konten
Excel memiliki kemampuan yang kuat, tetapi pada saat yang sama sangat jarang digunakan, untuk membuat urutan tindakan otomatis menggunakan makro. Makro adalah jalan keluar yang ideal jika Anda berurusan dengan jenis tugas yang sama yang diulang berkali-kali. Misalnya, pemrosesan data atau pemformatan dokumen sesuai dengan template standar. Dalam hal ini, Anda tidak memerlukan pengetahuan tentang bahasa pemrograman.
Apakah Anda sudah penasaran dengan apa itu makro dan bagaimana cara kerjanya? Kemudian dengan berani maju – maka kami akan selangkah demi selangkah melakukan seluruh proses pembuatan makro dengan Anda.
Apa itu Makro?
Makro di Microsoft Office (ya, fungsi ini berfungsi sama di banyak aplikasi paket Microsoft Office) adalah kode program dalam bahasa pemrograman Visual Basic untuk Aplikasi (VBA) disimpan di dalam dokumen. Agar lebih jelas, dokumen Microsoft Office dapat dibandingkan dengan halaman HTML, maka makro adalah analog dari Javascript. Apa yang dapat dilakukan Javascript dengan data HTML di halaman web sangat mirip dengan apa yang dapat dilakukan makro dengan data dalam dokumen Microsoft Office.
Macro dapat melakukan apa saja yang Anda inginkan dalam dokumen. Berikut adalah beberapa di antaranya (bagian yang sangat kecil):
- Terapkan gaya dan pemformatan.
- Lakukan berbagai operasi dengan data numerik dan teks.
- Gunakan sumber data eksternal (file database, dokumen teks, dll.)
- Buat dokumen baru.
- Lakukan semua hal di atas dalam kombinasi apa pun.
Membuat makro – contoh praktis
Sebagai contoh, mari kita ambil file yang paling umum CSV. Ini adalah tabel 10x20 sederhana yang diisi dengan angka dari 0 hingga 100 dengan judul untuk kolom dan baris. Tugas kita adalah mengubah kumpulan data ini menjadi tabel yang diformat dengan rapi dan menghasilkan total di setiap baris.
Seperti yang telah disebutkan, makro adalah kode yang ditulis dalam bahasa pemrograman VBA. Tetapi di Excel, Anda dapat membuat program tanpa menulis sebaris kode, yang akan kita lakukan sekarang.
Untuk membuat makro, buka View (Tipe) > Macro (Makro) > Rekam Makro (Rekaman makro…)
Beri nama makro Anda (tanpa spasi) dan klik OK.
Mulai dari saat ini, SEMUA tindakan Anda dengan dokumen dicatat: perubahan ke sel, menggulir tabel, bahkan mengubah ukuran jendela.
Excel memberi sinyal bahwa mode perekaman makro diaktifkan di dua tempat. Pertama, pada menu Macro (Makro) – alih-alih string Rekam Makro (Merekam makro…) baris muncul Hentikan Rekaman (Berhenti merekam).
Kedua, di sudut kiri bawah jendela Excel. ikon berhenti (kotak kecil) menunjukkan bahwa mode perekaman makro diaktifkan. Mengkliknya akan berhenti merekam. Sebaliknya, saat mode perekaman tidak diaktifkan, ada ikon untuk mengaktifkan perekaman makro di lokasi ini. Mengkliknya akan memberikan hasil yang sama seperti menyalakan rekaman melalui menu.
Sekarang mode perekaman makro diaktifkan, mari kita ke tugas kita. Pertama-tama, mari tambahkan header untuk data ringkasan.
Next, enter the formulas in the cells in accordance with the names of the headings (variants of the formulas for the English and versions of Excel are given, cell addresses are always Latin letters and numbers):
- =JUMLAH(B2:K2) or =JUMLAH(B2:K2)
- = RATA-RATA(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAKS(B2:K2) or =MAKS(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Sekarang pilih sel dengan rumus dan salin ke semua baris tabel kita dengan menyeret pegangan isi otomatis.
Setelah Anda menyelesaikan langkah ini, setiap baris harus memiliki total yang sesuai.
Selanjutnya, kami akan merangkum hasil untuk seluruh tabel, untuk ini kami melakukan beberapa operasi matematika lagi:
Masing-masing:
- =JUMLAH(L2:L21) or =JUMLAH(L2:L21)
- = RATA-RATA(B2:K21) or =СРЗНАЧ(B2:K21) – untuk menghitung nilai ini, perlu untuk mengambil data awal tabel dengan tepat. Jika Anda mengambil rata-rata rata-rata untuk setiap baris, hasilnya akan berbeda.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAKS(O2:O21) or =MAKS(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – kami mempertimbangkan untuk menggunakan data awal tabel, untuk alasan yang disebutkan di atas.
Sekarang setelah kita selesai dengan perhitungan, mari kita lakukan beberapa pemformatan. Pertama, mari kita atur format tampilan data yang sama untuk semua sel. Pilih semua sel pada lembar, untuk melakukan ini, gunakan pintasan keyboard Ctrl + Aatau klik ikon Pilih semua, yang terletak di persimpangan judul baris dan kolom. Lalu klik Gaya koma (Format Dibatasi) tab Beranda (Rumah).
Selanjutnya, ubah tampilan header kolom dan baris:
- Gaya huruf tebal.
- Penjajaran tengah.
- Isi warna.
Dan akhirnya, mari kita atur format totalnya.
Ini adalah bagaimana seharusnya terlihat seperti pada akhirnya:
Jika semuanya cocok untuk Anda, berhenti merekam makro.
Selamat! Anda baru saja merekam makro pertama Anda di Excel sendiri.
Untuk menggunakan makro yang dihasilkan, kita perlu menyimpan dokumen Excel dalam format yang mendukung makro. Pertama, kita perlu menghapus semua data dari tabel yang kita buat, yaitu membuatnya menjadi template kosong. Faktanya adalah bahwa di masa depan, bekerja dengan templat ini, kami akan mengimpor data terbaru dan relevan ke dalamnya.
Untuk menghapus semua sel dari data, klik kanan pada ikon Pilih semua, yang terletak di persimpangan judul baris dan kolom, dan dari menu konteks, pilih Delete (Menghapus).
Sekarang lembar kami benar-benar dibersihkan dari semua data, sementara makro tetap direkam. Kita perlu menyimpan buku kerja sebagai templat Excel berkemampuan makro yang memiliki ekstensi XLTM.
Poin penting! Jika Anda menyimpan file dengan ekstensi XLTX, maka makro tidak akan berfungsi di dalamnya. Omong-omong, Anda dapat menyimpan buku kerja sebagai templat Excel 97-2003, yang memiliki format XLT, itu juga mendukung makro.
Saat template disimpan, Anda dapat menutup Excel dengan aman.
Menjalankan Makro di Excel
Sebelum mengungkapkan semua kemungkinan makro yang Anda buat, saya pikir ada baiknya untuk memperhatikan beberapa poin penting mengenai makro secara umum:
- Makro bisa berbahaya.
- Baca kembali paragraf sebelumnya.
Kode VBA sangat kuat. Secara khusus, ia dapat melakukan operasi pada file di luar dokumen saat ini. Misalnya, makro dapat menghapus atau mengubah file apa pun dalam folder Dokumen saya. Untuk alasan ini, hanya jalankan dan izinkan makro dari sumber yang Anda percayai.
Untuk menjalankan makro pemformatan data kami, buka file template yang kami buat di bagian pertama tutorial ini. Jika Anda memiliki pengaturan keamanan standar, maka ketika Anda membuka file, peringatan akan muncul di atas tabel bahwa makro dinonaktifkan, dan tombol untuk mengaktifkannya. Karena kami membuat template sendiri dan kami percaya diri, kami menekan tombol Aktifkan Konten (Sertakan konten).
Langkah selanjutnya adalah mengimpor dataset terbaru yang diperbarui dari file CSV (berdasarkan file seperti itu, kami membuat makro kami).
Saat Anda mengimpor data dari file CSV, Excel mungkin meminta Anda untuk menyiapkan beberapa pengaturan untuk mentransfer data ke tabel dengan benar.
Ketika impor selesai, buka menu Macro (Makro) tab View (Lihat) dan pilih perintah Lihat Makro (makro).
Di kotak dialog yang terbuka, kita akan melihat garis dengan nama makro kita Format data. Pilih dan klik Run (Menjalankan).
Saat makro mulai berjalan, Anda akan melihat kursor tabel melompat dari sel ke sel. Setelah beberapa detik, operasi yang sama akan dilakukan dengan data seperti saat merekam makro. Ketika semuanya sudah siap, tabel akan terlihat sama dengan aslinya yang kita format dengan tangan, hanya dengan data yang berbeda di dalam sel.
Mari kita lihat di bawah tenda: Bagaimana cara kerja makro?
Seperti disebutkan lebih dari sekali, makro adalah kode program dalam bahasa pemrograman. Visual Basic untuk Aplikasi (VBA). Saat Anda mengaktifkan mode perekaman makro, Excel sebenarnya merekam setiap tindakan yang Anda lakukan dalam bentuk instruksi VBA. Sederhananya, Excel menulis kode untuk Anda.
Untuk melihat kode program ini, Anda perlu di menu Macro (Makro) tab View (tampilan) klik Lihat Makro (Makro) dan di kotak dialog yang terbuka, klik Edit (Mengubah).
Jendela terbuka. Visual Basic untuk Aplikasi, di mana kita akan melihat kode program makro yang kita rekam. Ya, Anda mengerti dengan benar, di sini Anda dapat mengubah kode ini dan bahkan membuat makro baru. Tindakan yang kami lakukan dengan tabel dalam pelajaran ini dapat direkam menggunakan perekaman makro otomatis di Excel. Tetapi makro yang lebih kompleks, dengan urutan dan logika tindakan yang disetel dengan baik, memerlukan pemrograman manual.
Mari tambahkan satu langkah lagi ke tugas kita…
Bayangkan bahwa file data asli kami data.csv dibuat secara otomatis oleh beberapa proses dan selalu disimpan pada disk di tempat yang sama. Sebagai contoh, C: Datadata.csv – jalur ke file dengan data yang diperbarui. Proses membuka file ini dan mengimpor data darinya juga dapat direkam dalam makro:
- Buka file template tempat kita menyimpan makro Format data.
- Buat makro baru bernama Muat Data.
- Saat merekam makro Muat Data impor data dari file data.csv – seperti yang kita lakukan di bagian pelajaran sebelumnya.
- Saat impor selesai, berhenti merekam makro.
- Hapus semua data dari sel.
- Simpan file sebagai templat Excel berkemampuan makro (ekstensi XLTM).
Jadi, dengan menjalankan template ini, Anda mendapatkan akses ke dua makro – satu memuat data, yang lain memformatnya.
Jika Anda ingin masuk ke pemrograman, Anda dapat menggabungkan tindakan kedua makro ini menjadi satu – cukup dengan menyalin kode dari Muat Data ke awal kode Format data.