Pelajari Cara Menggunakan Excel Macro untuk Mengotomatiskan Tugas-Tugas yang membosankan

2024 Pengarang: Geoffrey Carr | [email protected]. Terakhir diubah: 2023-12-17 10:58

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

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 dokumen sebagai HTML dan makro sebagai Javascript. Dalam banyak cara yang sama seperti Javascript dapat memanipulasi HTML di halaman web, makro dapat memanipulasi dokumen.

Makro sangat kuat dan dapat melakukan banyak hal yang dapat dibayangkan oleh imajinasi Anda. Sebagai (sangat) daftar pendek fungsi 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 sepenuhnya baru.
Kombinasi apa pun, dalam urutan apa pun, dari semua hal di atas.

Membuat Makro: Penjelasan oleh Contoh

Kami mulai dengan file CSV berbagai taman Anda. Tidak ada yang istimewa di sini, hanya 10 × 20 set angka antara 0 dan 100 dengan header baris dan kolom. Tujuan kami adalah untuk menghasilkan lembar data yang dapat diformat dengan baik, 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 pengkodean nol yang diperlukan - seperti yang akan kita lakukan di sini.

Untuk membuat makro, masuk ke Lihat> Makro> Rekam Makro.

Tetapkan makro nama (tanpa spasi) dan klik OK.

Setelah ini selesai, semua dari tindakan Anda dicatat - setiap perubahan sel, gulir tindakan, mengubah ukuran jendela, Anda nama itu.

Ada beberapa tempat yang mengindikasikan 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 'berhenti' menunjukkan itu dalam mode makro dan menekan di sini akan menghentikan perekaman (juga, ketika tidak dalam mode rekam, ikon ini akan menjadi tombol Rekam Makro, yang dapat Anda gunakan daripada pergi ke menu Makro).

Sekarang kami merekam makro kami, mari kita menerapkan perhitungan ringkasan kami. Pertama, tambahkan tajuk.

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

= SUM (B2: K2)
= RATA-RATA (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

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

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

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

= SUM (L2: L21)
= AVERAGE (B2: K21) *Ini harus dihitung di semua data karena rata-rata dari rata-rata baris tidak selalu 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.

Setelah perhitungan selesai, kami akan menerapkan gaya dan pemformatan. Pertama, terapkan pemformatan angka umum di semua sel dengan melakukan Pilih Semua (baik Ctrl + A atau klik sel antara judul baris dan kolom) dan pilih ikon "Gaya Koma" di bawah menu Beranda.

Selanjutnya, terapkan beberapa format visual ke header baris dan kolom:

Berani.
Dipusatkan.
Warna isi latar belakang.

Dan akhirnya, terapkan beberapa gaya ke total.

Ketika semua sudah selesai, inilah tampilan lembar data kami:

Karena kita puas dengan hasilnya, hentikan perekaman makro.

Selamat - Anda baru saja membuat makro Excel.

Untuk menggunakan makro yang baru kami rekam, kami harus menyimpan Workbook Excel kami dalam format file makro yang diaktifkan. Namun, sebelum kita melakukan itu, pertama-tama kita perlu menghapus semua data yang ada sehingga tidak tertanam dalam template kami (gagasannya setiap kali kita menggunakan template ini, kita akan mengimpor data paling terkini).

Untuk melakukan ini, pilih semua sel dan hapus.

Dengan data yang sekarang sudah bersih (tetapi makro masih dimasukkan dalam file Excel), kami ingin menyimpan file sebagai file makro aktif template (XLTM). Penting untuk dicatat bahwa jika Anda menyimpan ini sebagai file template standar (XLTX) maka makro akan tidak dapat dijalankan dari itu. Sebagai alternatif, Anda dapat menyimpan file sebagai file warisan template (XLT), yang akan memungkinkan makro untuk dijalankan.

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

Menggunakan Excel Makro

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 lingkup dokumen saat ini. Misalnya, makro bisa mengubah atau menghapus file acak di folder My Documents Anda.Karena itu, penting untuk memastikan Anda hanya menjalankan makro dari sumber tepercaya.

Untuk menempatkan makro format data kami untuk digunakan, buka file Template Excel yang dibuat di atas. Ketika Anda melakukan ini, dengan asumsi Anda memiliki pengaturan keamanan standar yang diaktifkan, Anda akan melihat peringatan di bagian atas buku kerja yang mengatakan bahwa makro dinonaktifkan. Karena kami mempercayai makro yang dibuat oleh diri kami sendiri, klik tombol ‘Aktifkan Konten’.

Selanjutnya, kita akan mengimpor set 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 menginterpretasikannya dengan benar (misalnya pembatas, tajuk, dll.).

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

Pada 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 karena Anda akan melihat data yang dimanipulasi persis saat kami merekamnya. Ketika semua dikatakan dan dilakukan, seharusnya terlihat seperti aslinya - kecuali dengan data yang berbeda.

Mencari Under the Hood: Apa yang Membuat Kerja Makro

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

Untuk melihat kode yang membuat makro kami berjalan, dari dialog Macros 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 dan tindakan bersyarat akan mengharuskan Anda mengedit kode sumber.

Mengambil Contoh Kita Satu Langkah Lebih Jauh…

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

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

Setelah ini selesai, kapan pun template dibuka, akan ada dua makro - satu yang memuat data kami dan yang lain yang memformatnya.