Pelajari Cara Menggunakan Makro Excel untuk Mengotomatiskan Tugas yang Membosankan

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

Jika Anda penasaran apa itu makro atau bagaimana cara membuatnya, tidak masalah - kami akan memandu Anda melalui keseluruhan proses.

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

Apa itu Makro?

Makro Microsoft Office (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 dokumen sebagai HTML dan makro sebagai Javascript. Dengan cara yang hampir sama seperti Javascript dapat memanipulasi HTML pada halaman web, makro dapat memanipulasi dokumen.

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

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

Membuat Makro: Penjelasan dengan Contoh

Kami mulai dengan file CSV varietas taman Anda. Tidak ada yang istimewa di sini, hanya kumpulan 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 menyenangkan tentang Excel adalah Anda dapat membuat / merekamnya tanpa memerlukan pengkodean - seperti yang akan kita lakukan di sini.

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

Tetapkan nama makro (tanpa spasi) dan klik OK.

Setelah ini selesai, semua tindakan Anda dicatat - setiap perubahan sel, tindakan gulir, ukuran jendela, apa saja.

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

Yang lainnya ada di pojok kanan bawah. Ikon 'stop' menunjukkan itu dalam mode makro dan menekan di sini akan menghentikan perekaman (demikian pula, bila tidak dalam mode rekam, ikon ini akan menjadi tombol Rekam Makro, yang dapat Anda gunakan daripada membuka menu Makro).

Sekarang setelah kami merekam makro kami, mari terapkan penghitungan ringkasan kami. Pertama tambahkan header.

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

  • = SUM (B2: K2)
  • = RATA-RATA (B2: K2)
  • = MIN (B2: K2)
  • = MAKS (B2: K2)
  • = MEDIAN (B2: K2)

Sekarang, sorot semua sel kalkulasi dan seret panjang semua baris data kita untuk menerapkan kalkulasi ke setiap baris.

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

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

Masing-masing:

  • = SUM (L2: L21)
  • = RATA-RATA (B2: K21) * Ini harus dihitung di semua data karena rata-rata dari rata-rata baris tidak harus sama dengan rata-rata semua nilai.
  • = MIN (N2: N21)
  • = MAKS (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-tama terapkan pemformatan angka umum di semua sel dengan melakukan Select All (baik Ctrl + A atau klik sel di antara header baris dan kolom) dan pilih ikon "Comma Style" di bawah menu Home.

Selanjutnya, terapkan beberapa pemformatan visual ke judul baris dan kolom:

  • Mencolok.
  • Tengah.
  • Warna isian latar belakang.

Dan terakhir, terapkan beberapa gaya ke total.

Setelah semua selesai, seperti inilah lembar data kami:

Karena kami puas dengan hasilnya, hentikan perekaman makro.

Selamat - Anda baru saja membuat makro Excel.

Untuk menggunakan makro yang baru direkam, kami harus menyimpan Buku Kerja Excel kami dalam format file yang mendukung makro. Namun, sebelum kita melakukannya, pertama-tama kita perlu menghapus semua data yang ada agar tidak disematkan di template kita (idenya adalah setiap kali kita menggunakan template ini, kita akan mengimpor data paling up-to-date).

Untuk melakukan ini, pilih semua sel dan hapus.

Dengan data yang sekarang telah dihapus (tetapi makro masih disertakan dalam file Excel), kami ingin menyimpan file sebagai file templat berkemampuan makro (XLTM). Penting untuk dicatat bahwa jika Anda menyimpan ini sebagai file template standar (XLTX), maka makro tidak akan dapat dijalankan darinya. Alternatifnya, Anda dapat menyimpan file sebagai file template lama (XLT), yang memungkinkan makro dijalankan.

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

Menggunakan Makro Excel

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

  • Makro bisa berbahaya.
  • Lihat poin di atas.

Kode VBA sebenarnya cukup kuat dan dapat memanipulasi file di luar cakupan dokumen saat ini. Misalnya, makro dapat mengubah atau menghapus file acak di folder Dokumen Saya. Karena itu, penting untuk memastikan Anda hanya menjalankan makro dari sumber tepercaya.

Untuk menggunakan makro format data kita, buka file Template Excel yang telah dibuat di atas. Saat Anda melakukan ini, dengan asumsi Anda telah mengaktifkan pengaturan keamanan standar, Anda akan melihat peringatan di bagian atas buku kerja yang mengatakan bahwa makro dinonaktifkan. Karena kami mempercayai makro yang dibuat sendiri, klik tombol 'Aktifkan Konten'.

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

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

Setelah data kami diimpor, cukup buka menu Makro (di bawah tab Tampilan) dan pilih Lihat Makro.

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

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

Melihat Di Balik Terpal: Apa yang Membuat Makro Bekerja

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 dalam instruksi VBA masing-masing. Sederhananya - Anda tidak perlu menulis kode apa pun karena Excel sedang menulis kode untuk Anda.

Untuk melihat kode yang membuat makro kita berjalan, dari dialog Makro klik tombol Edit.

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

Mengambil Teladan Kita Selangkah Lebih Jauh…

Secara hipotesis, asumsikan file data sumber kita, 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 juga dapat dengan mudah dibuat menjadi makro:

  1. Buka file Template Excel yang berisi makro "FormatData" kami.
  2. Rekam makro baru bernama "LoadData".
  3. Dengan perekaman makro, impor file data seperti biasa.
  4. Setelah data diimpor, hentikan perekaman makro.
  5. Hapus semua data sel (pilih semua lalu hapus).
  6. Simpan template yang diperbarui (ingat untuk menggunakan format template yang mendukung makro).

Setelah ini selesai, setiap kali templat dibuka akan ada dua makro - satu yang memuat data kita dan yang lainnya memformatnya.

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

Unduh Template ini

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

Unduh Template Makro Excel dari How-To Geek