Ketahui Cara Menggunakan Makro Excel untuk Mengautomasikan Tugas yang Membosankan

Salah satu fungsi Excel yang lebih kuat, tetapi jarang digunakan adalah kemampuan untuk membuat tugas automatik dan logik tersuai dalam makro dengan mudah. Makro menyediakan cara yang ideal untuk menjimatkan masa pada tugas yang dapat diramal, berulang dan menyeragamkan format dokumen - berkali-kali tanpa perlu menulis satu baris kod.

Sekiranya anda ingin tahu apakah makro atau bagaimana membuatnya, tidak ada masalah - kami akan memandu anda melalui keseluruhan proses.

Catatan:  proses yang sama harus dijalankan di kebanyakan versi Microsoft Office Tangkapan skrin mungkin kelihatan sedikit berbeza.

Apa itu Makro?

Makro Microsoft Office (kerana fungsi ini berlaku untuk beberapa Aplikasi MS Office) hanyalah kod Visual Basic for Applications (VBA) yang disimpan di dalam dokumen. Untuk analogi yang setanding, fikirkan dokumen sebagai HTML dan makro sebagai Javascript. Dengan cara yang sama seperti Javascript dapat memanipulasi HTML di laman web, makro dapat memanipulasi dokumen.

Makro sangat hebat dan dapat melakukan apa sahaja yang dapat dihasilkan oleh imaginasi anda. Sebagai senarai fungsi (sangat) yang boleh anda lakukan dengan makro:

  • Terapkan gaya dan format.
  • Memanipulasi data dan teks.
  • Berkomunikasi dengan sumber data (pangkalan data, fail teks, dll.).
  • Buat dokumen yang sama sekali baru.
  • Sebarang gabungan, mengikut urutan, mana-mana perkara di atas.

Membuat Makro: Penjelasan dengan Contoh

Kami mulakan dengan fail CSV pelbagai kebun anda. Tidak ada yang istimewa di sini, hanya sekumpulan angka 10 × 20 antara 0 dan 100 dengan tajuk baris dan lajur. Tujuan kami adalah untuk menghasilkan lembaran data yang diformat dengan baik dan merangkumi jumlah ringkasan untuk setiap baris.

Seperti yang kami nyatakan di atas, makro adalah kod VBA, tetapi salah satu perkara baik mengenai Excel adalah anda boleh membuat / merakamnya dengan pengekodan sifar yang diperlukan - seperti yang akan kita lakukan di sini.

Untuk membuat makro, pergi ke Lihat> Makro> Rakam Makro.

Tetapkan nama makro (tanpa ruang) dan klik OK.

Setelah ini selesai, semua tindakan anda direkodkan - setiap perubahan sel, tindakan tatal, ukuran tetingkap, anda namakannya.

Terdapat beberapa tempat yang menunjukkan Excel adalah mod rakaman. Salah satunya adalah dengan melihat menu Makro dan memperhatikan bahawa Berhenti Merakam telah menggantikan pilihan untuk Rekod Makro.

Yang lain berada di sudut kanan bawah. Ikon 'berhenti' menunjukkan bahawa ia berada dalam mod makro dan menekan di sini akan menghentikan rakaman (begitu juga ketika tidak berada dalam mod rakaman, ikon ini akan menjadi butang Rekod Makro, yang dapat anda gunakan dan bukannya pergi ke menu Makro).

Sekarang kita merakam makro kita, mari gunakan pengiraan ringkasan kami. Pertama, masukkan tajuk.

Seterusnya, gunakan formula yang sesuai (masing-masing):

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

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

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

Sekarang, kami ingin mendapatkan data ringkasan untuk keseluruhan helaian, jadi kami menerapkan beberapa pengiraan lagi:

Setiap satu:

  • = SUM (L2: L21)
  • = RATA-RATA (B2: K21) * Ini mesti dikira di semua data kerana rata-rata purata baris tidak semestinya sama dengan rata-rata semua nilai.
  • = MIN (N2: N21)
  • = MAX (O2: O21)
  • = MEDIAN (B2: K21) * Dihitung di semua data dengan alasan yang sama seperti di atas.

Setelah pengiraan selesai, kami akan menerapkan gaya dan format. Mula-mula menerapkan pemformatan nombor umum di semua sel dengan melakukan Pilih Semua (sama ada Ctrl + A atau klik sel antara tajuk baris dan lajur) dan pilih ikon "Gaya Koma" di bawah menu Laman Utama.

Seterusnya, gunakan format visual pada tajuk baris dan lajur:

  • Berani.
  • Berpusat.
  • Warna pengisian latar belakang.

Dan akhirnya, gunakan beberapa gaya untuk keseluruhannya.

Setelah semuanya selesai, seperti inilah lembaran data kami:

Oleh kerana kami berpuas hati dengan hasilnya, hentikan rakaman makro.

Tahniah - anda baru sahaja membuat makro Excel.

Untuk menggunakan makro yang baru direkodkan, kita harus menyimpan Buku Kerja Excel kami dalam format fail yang diaktifkan makro. Namun, sebelum kita melakukan itu, pertama-tama kita perlu membersihkan semua data yang ada agar tidak disisipkan dalam templat kita (ideanya setiap kali kita menggunakan templat ini, kita akan mengimport data yang paling terkini).

Untuk melakukan ini, pilih semua sel dan padamkannya.

Dengan data yang sekarang dihapus (tetapi makro masih disertakan dalam fail Excel), kami ingin menyimpan fail sebagai fail templat berkemampuan makro (XLTM). Penting untuk diperhatikan bahawa jika anda menyimpan ini sebagai fail templat standard (XLTX) maka makro tidak akan dapat dijalankan daripadanya. Sebagai alternatif, anda boleh menyimpan fail tersebut sebagai fail templat lama (XLT), yang akan membolehkan makro dijalankan.

Setelah menyimpan fail sebagai templat, teruskan dan tutup Excel.

Menggunakan Makro Excel

Sebelum merangkumi bagaimana kita dapat menerapkan makro yang baru direkodkan ini, penting untuk merangkumi beberapa perkara mengenai makro secara umum:

  • Makro boleh berniat jahat.
  • Lihat perkara di atas.

Kod VBA sebenarnya cukup kuat dan dapat memanipulasi fail di luar ruang lingkup dokumen semasa. Sebagai contoh, makro boleh mengubah atau menghapus fail rawak dalam folder Dokumen Saya. Oleh itu, penting untuk memastikan anda hanya menjalankan makro dari sumber yang dipercayai.

Untuk menggunakan makro format data kami, buka fail Templat Excel yang dibuat di atas. Apabila anda melakukan ini, dengan andaian bahawa tetapan keselamatan standard diaktifkan, anda akan melihat amaran di bahagian atas buku kerja yang mengatakan bahawa makro dilumpuhkan. Kerana kami mempercayai makro yang dibuat oleh kami sendiri, klik butang 'Dayakan Kandungan'.

Seterusnya, kami akan mengimport set data terbaru dari CSV (ini adalah sumber lembaran kerja yang digunakan untuk membuat makro kami).

Untuk menyelesaikan import fail CSV, anda mungkin perlu menetapkan beberapa pilihan agar Excel dapat menafsirkannya dengan betul (contohnya pembatas, tajuk yang ada, dll.).

Setelah data kami diimport, pergi ke menu Makro (di bawah tab Lihat) dan pilih Lihat Makro.

Dalam kotak dialog yang dihasilkan, kita melihat makro "FormatData" yang kita rakam di atas. Pilihnya dan klik Jalankan.

Setelah berjalan, anda mungkin melihat kursor melompat-lompat beberapa saat, tetapi seperti yang berlaku, anda akan melihat data dimanipulasi sama seperti yang kami rakam. Apabila semua yang dikatakan dan selesai, ia akan kelihatan seperti yang asal - kecuali dengan data yang berbeza.

Melihat Di Bawah Tudung: Apa yang Membuat Kerja Makro

Seperti yang telah kita sebutkan beberapa kali, makro didorong oleh kod Visual Basic for Applications (VBA). Apabila anda "merakam" makro, Excel sebenarnya menerjemahkan semua yang anda lakukan ke dalam arahan VBA masing-masing. Sederhananya - anda tidak perlu menulis kod kerana Excel sedang menulis kod untuk anda.

Untuk melihat kod yang menjadikan makro kita berjalan, dari dialog Makro klik butang Edit.

Tetingkap yang terbuka memaparkan kod sumber yang direkodkan dari tindakan kita semasa membuat makro. Sudah tentu, anda boleh mengedit kod ini atau bahkan membuat makro baru sepenuhnya di dalam tetingkap kod. Walaupun tindakan rakaman yang digunakan dalam artikel ini kemungkinan sesuai dengan kebanyakan keperluan, tindakan atau tindakan bersyarat yang lebih disesuaikan memerlukan anda mengedit kod sumber.

Mengambil Contoh Kita Selangkah Lebih Jauh ...

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

  1. Buka fail Templat Excel yang mengandungi makro "FormatData" kami.
  2. Rakam makro baru bernama "LoadData".
  3. Dengan rakaman makro, import fail data seperti biasa.
  4. Setelah data diimport, hentikan rakaman makro.
  5. Padam semua data sel (pilih semua kemudian hapus).
  6. Simpan templat yang dikemas kini (ingat untuk menggunakan format templat yang diaktifkan makro).

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

Sekiranya anda benar-benar ingin mengotori tangan anda dengan sedikit pengeditan kod, anda boleh dengan mudah menggabungkan tindakan ini ke dalam satu makro dengan menyalin kod yang dihasilkan dari "LoadData" dan memasukkannya pada awal kod dari "FormatData".

Muat turun Templat ini

Untuk kemudahan anda, kami telah memasukkan templat Excel yang dihasilkan dalam artikel ini dan juga contoh fail data untuk anda mainkan.

Muat turun Templat Makro Excel dari How-To Geek