Pelajari Cara Menggunakan Excel Macro untuk Mengotomatiskan Tugas yang membosankan

Salah satu fungsi Excel yang lebih kuat, tetapi jarang digunakan adalah kemampuan untuk dengan mudah membuat tugas otomatis dan logika khusus di dalam makro. Macro memberikan cara yang ideal untuk menghemat waktu pada tugas yang dapat diprediksi dan berulang serta membakukan format dokumen - berkali-kali tanpa harus menulis satu baris kode pun.

Jika Anda ingin tahu apa itu makro atau bagaimana cara membuatnya, tidak masalah - kami akan memandu Anda melalui seluruh proses.

catatan: proses yang sama harus berfungsi di sebagian besar versi Microsoft Office. Tangkapan layar mungkin terlihat sedikit berbeda.

Apa itu Makro??

Microsoft Office Macro (karena fungsi ini berlaku untuk beberapa Aplikasi MS Office) hanyalah kode Visual Basic for Applications (VBA) yang disimpan di dalam dokumen. Untuk analogi yang sebanding, pikirkan sebuah dokumen sebagai HTML dan makro sebagai Javascript. Dalam banyak cara yang sama Javascript dapat memanipulasi HTML pada halaman web, makro dapat memanipulasi dokumen.

Makro sangat kuat dan dapat melakukan apa saja yang dapat dibayangkan oleh imajinasi Anda. Sebagai (sangat) daftar fungsi singkat yang dapat Anda lakukan dengan makro:

• Terapkan gaya dan format.
• Memanipulasi data dan teks.
• Berkomunikasi dengan sumber data (database, file teks, dll.).
• Buat dokumen yang sama sekali baru.
• Kombinasi apa pun, dengan urutan apa pun, dari salah satu di atas.

Membuat Makro: Penjelasan dengan Contoh

Kami mulai dengan file CSV variasi kebun Anda. Tidak ada yang istimewa di sini, hanya set angka 10 × 20 antara 0 dan 100 dengan header baris dan kolom. Tujuan kami adalah untuk menghasilkan lembar data yang diformat dengan baik dan rapi yang mencakup total ringkasan untuk setiap baris.

Seperti yang kami nyatakan di atas, makro adalah kode VBA, tetapi salah satu hal yang menyenangkan tentang Excel adalah Anda dapat membuat / merekamnya dengan nol pengkodean yang diperlukan - seperti yang akan kami lakukan di sini.

Untuk membuat makro, buka Lihat> Makro> Rekam Makro.

Tetapkan nama makro (tanpa spasi) dan klik OK.

Setelah ini selesai, semua tindakan Anda direkam - setiap perubahan sel, tindakan gulir, ukuran jendela, beri nama.

Ada beberapa tempat yang menunjukkan Excel adalah mode rekam. Salah satunya adalah dengan melihat menu Makro dan mencatat bahwa Stop Recording telah mengganti opsi untuk Record Macro.

Yang lainnya ada di sudut kanan bawah. Ikon 'berhenti' menunjukkan itu dalam mode makro dan menekan di sini akan menghentikan rekaman (juga, saat tidak dalam mode rekaman, ikon ini akan menjadi tombol Rekam Makro, yang dapat Anda gunakan alih-alih pergi ke menu Makro).

Sekarang kami merekam makro kami, mari kita terapkan perhitungan ringkasan kami. Pertama tambahkan header.

Selanjutnya, terapkan formula yang sesuai (masing-masing):

• = SUM (B2: K2)
• = AVERAGE (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Sekarang, sorot semua sel perhitungan dan seret panjang semua baris data kami untuk menerapkan perhitungan pada setiap baris.

Setelah ini selesai, setiap baris harus menampilkan ringkasan masing-masing.

Sekarang, kami ingin mendapatkan data ringkasan untuk seluruh sheet, jadi kami menerapkan beberapa perhitungan lagi:

Masing-masing:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *Ini harus dihitung di semua data karena rata-rata rata-rata baris tidak harus sama dengan rata-rata semua nilai.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Dihitung di semua data untuk alasan yang sama seperti di atas.

Sekarang setelah perhitungan selesai, kami akan menerapkan gaya dan pemformatan. Pertama menerapkan pemformatan angka umum di semua sel dengan melakukan Select All (Ctrl + A atau klik sel antara header baris dan kolom) dan pilih ikon "Gaya Koma" di bawah menu Home.

Selanjutnya, terapkan beberapa pemformatan visual untuk tajuk baris dan kolom:

• Berani.
• Berpusat.
• Warna mengisi latar belakang.

Dan akhirnya, terapkan beberapa gaya ke total.

Ketika semua sudah selesai, seperti inilah lembar data kami:

Karena kami puas dengan hasilnya, hentikan perekaman makro.

Selamat - Anda baru saja membuat makro Excel.

Untuk menggunakan makro kami yang baru direkam, kami harus menyimpan Buku Kerja Excel kami dalam format file yang diaktifkan makro. Namun, sebelum kita melakukannya, pertama-tama kita harus menghapus semua data yang ada sehingga tidak tertanam dalam templat kita (gagasannya adalah setiap kali kita menggunakan templat ini, kita akan mengimpor data yang paling terbaru).

Untuk melakukan ini, pilih semua sel dan hapus mereka.

Dengan data yang sekarang dihapus (tetapi makro masih termasuk dalam file Excel), kami ingin menyimpan file sebagai file template yang didukung makro (XLTM). Penting untuk dicatat bahwa jika Anda menyimpan ini sebagai file templat standar (XLTX) maka makro akan melakukannya tidak bisa lari darinya. Sebagai alternatif, Anda dapat menyimpan file sebagai file template lama (XLT), yang memungkinkan makro dijalankan.

Setelah Anda menyimpan file sebagai templat, lanjutkan dan tutup Excel.

Menggunakan Excel Macro

Sebelum membahas bagaimana kami dapat menerapkan makro yang baru direkam ini, penting untuk membahas beberapa poin tentang makro secara umum:

• Macro bisa berbahaya.
• Lihat poin di atas.

Kode VBA sebenarnya cukup kuat dan dapat memanipulasi file di luar ruang lingkup dokumen saat ini. Misalnya, makro bisa mengubah atau menghapus file acak di folder My Documents Anda. Karena itu, penting untuk memastikan Anda hanya jalankan makro dari sumber tepercaya.

Untuk menggunakan makro format data kami, buka file Templat Excel yang dibuat di atas. Ketika Anda melakukan ini, dengan asumsi Anda memiliki pengaturan keamanan standar diaktifkan, Anda akan melihat peringatan di bagian atas buku kerja yang mengatakan bahwa makro dinonaktifkan. Karena kami percaya pada makro yang dibuat oleh kami sendiri, klik tombol 'Aktifkan Konten'.

Selanjutnya, kita akan mengimpor kumpulan data terbaru dari CSV (ini adalah sumber lembar kerja yang digunakan untuk membuat makro kami).

Untuk menyelesaikan impor file CSV, Anda mungkin harus menetapkan beberapa opsi agar Excel menafsirkannya dengan benar (mis. Pembatas, tajuk yang ada, dll.).

Setelah data kami diimpor, cukup buka menu Macro (di bawah tab View) dan pilih View Macros.

Di kotak dialog yang dihasilkan, kita melihat makro "FormatData" yang kami rekam di atas. Pilih dan klik Jalankan.

Setelah berjalan, Anda mungkin melihat kursor melompat-lompat selama beberapa saat, tetapi saat Anda melakukannya, Anda akan melihat data sedang dimanipulasi persis saat kami merekamnya. Ketika semua dikatakan dan dilakukan, itu akan terlihat seperti aslinya - kecuali dengan data yang berbeda.

Mencari di Bawah Tudung: Apa yang Membuat Pekerjaan Makro

Seperti yang telah kami sebutkan beberapa kali, makro didorong oleh kode Visual Basic for Applications (VBA). Saat Anda "merekam" makro, Excel sebenarnya menerjemahkan semua yang Anda lakukan ke instruksi VBA masing-masing. Sederhananya - Anda tidak harus menulis kode apa pun karena Excel menulis kode untuk Anda.

Untuk melihat kode yang menjalankan makro kami, dari dialog Macro klik tombol Edit.

Jendela yang terbuka menampilkan kode sumber yang direkam dari tindakan kami saat membuat makro. Tentu saja, Anda dapat mengedit kode ini atau bahkan membuat makro baru sepenuhnya di dalam jendela kode. Meskipun tindakan perekaman yang digunakan dalam artikel ini kemungkinan akan sesuai dengan sebagian besar kebutuhan, tindakan yang lebih khusus atau tindakan bersyarat akan mengharuskan Anda untuk mengedit kode sumber.

Mengambil Contoh Kami Satu Langkah Lebih Jauh ...

Secara hipotesis, anggap file data sumber kami, data.csv, dihasilkan oleh proses otomatis yang selalu menyimpan file ke lokasi yang sama (mis. C: \ Data \ data.csv selalu merupakan data terbaru). Proses membuka file ini dan mengimpornya dapat dengan mudah dibuat menjadi makro juga:

1. Buka file Excel Template yang berisi makro "FormatData" kami.
2. Rekam makro baru bernama "LoadData".
3. Dengan rekaman makro, impor file data seperti biasanya.
4. Setelah data diimpor, berhenti merekam makro.
5. Hapus semua data sel (pilih semua lalu hapus).
6. Simpan templat yang diperbarui (ingat untuk menggunakan format templat yang diaktifkan makro).

Setelah ini selesai, setiap kali template dibuka akan ada dua makro - yang memuat data kami dan yang lainnya memformatnya.

Jika Anda benar-benar ingin tangan Anda kotor dengan sedikit pengeditan kode, Anda dapat dengan mudah menggabungkan tindakan ini ke dalam satu makro dengan menyalin kode yang dihasilkan dari "LoadData" dan memasukkannya di awal kode dari "FormatData".

Unduh Template ini

Demi kenyamanan Anda, kami telah menyertakan templat Excel yang dihasilkan dalam artikel ini serta file data sampel untuk Anda mainkan.

Unduh Excel Macro Template dari How-To Geek