Ketahui Cara Menggunakan Makro Excel untuk Mengotomatisasi Tugas Yang Membosankan

Salah satu fungsi yang lebih kuat, tetapi jarang menggunakan Excel adalah keupayaan untuk membuat tugas-tugas automatik dan logik khusus dalam makro. Macros menyediakan cara yang ideal untuk menjimatkan masa pada tugas-tugas yang boleh diramalkan, berulang dan juga menyeragamkan format dokumen - berkali-kali tanpa perlu menulis satu baris kod.

Sekiranya anda ingin tahu apa makro atau cara sebenarnya membuatnya, tidak ada masalah - kami akan membimbing anda melalui keseluruhan proses.

Catatan: proses yang sama harus berfungsi di kebanyakan versi Microsoft Office. Petikan mungkin kelihatan sedikit berbeza.

Apakah Macro itu??

Microsoft Office Macro (kerana fungsi ini digunakan untuk beberapa Aplikasi MS Office) adalah kod Visual Basic for Applications (VBA) yang disimpan di dalam dokumen. Untuk analogi yang setanding, fikirkan dokumen sebagai HTML dan makro sebagai Javascript. Dalam banyak cara yang sama, Javascript boleh memanipulasi HTML pada halaman web, makro dapat memanipulasi dokumen.

Macros adalah sangat berkuasa dan boleh melakukan apa sahaja imaginasi anda dapat menimbulkan perasaan. Sebagai senarai pendek (sangat) fungsi yang boleh anda lakukan dengan makro:

• Terapkan gaya dan pemformatan.
• Manipulasi data dan teks.
• Berkomunikasi dengan sumber data (pangkalan data, fail teks, dll.).
• Buat dokumen yang sama sekali baru.
• Mana-mana kombinasi, dalam apa-apa perintah, mana-mana perkara di atas.

Mencipta Makro: Penjelasan oleh Contoh

Kami bermula dengan fail CSV pelbagai taman anda. Tiada apa yang istimewa di sini, hanya set 10x20 nombor antara 0 dan 100 dengan kedua-dua tajuk baris dan lajur. Matlamat kami adalah untuk menghasilkan lembaran data yang boleh diformatkan dengan baik, termasuk jumlah ringkasan untuk setiap baris.

Seperti yang dinyatakan di atas, makro adalah kod VBA, tetapi salah satu perkara yang menarik mengenai Excel ialah anda boleh membuat / merekodkannya dengan pengekodan sifar yang diperlukan - seperti yang akan kami lakukan di sini.

Untuk membuat makro, pergi ke Lihat> Macros> Rekod Makro.

Berikan makro nama (tiada ruang) dan klik OK.

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

Terdapat beberapa tempat yang menunjukkan mod rekod Excel. Satu adalah dengan melihat menu Macro dan mencatat bahawa Stop Rakaman telah menggantikan pilihan untuk Rekod Makro.

Yang lain berada di sudut kanan bawah. Ikon 'berhenti' menandakan ia berada dalam mod makro dan menekan di sini akan menghentikan rakaman (begitu pula, apabila tidak dalam mod rekod, ikon ini akan menjadi butang Rekod Makro, yang boleh anda gunakan daripada pergi ke menu Macros).

Sekarang kita sedang merekodkan makro kita, mari kita gunakan pengiraan ringkasan kita. Pertama tambahkan tajuk.

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

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

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

Apabila ini dilakukan, setiap baris harus memaparkan ringkasan masing-masing.

Sekarang, kami ingin mendapatkan data ringkasan untuk keseluruhan lembaran, jadi kami menggunakan beberapa pengiraan yang lebih banyak:

Memadai:

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

Sekarang pengiraan dilakukan, kami akan menggunakan gaya dan pemformatan. Pertama gunakan pemformatan nombor umum di semua sel dengan melakukan Select All (sama ada Ctrl + A atau klik sel antara baris dan tajuk lajur) dan pilih ikon "Comma Style" di bawah menu Laman Utama.

Seterusnya, gunakan beberapa format visual untuk kedua baris dan tajuk lajur:

• Bold.
• Berpusat.
• Warna pengisi latar belakang.

Dan akhirnya, gunakan beberapa gaya untuk jumlah keseluruhan.

Apabila semua selesai, inilah helaian data kami seperti:

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

Tahniah - anda baru saja mencipta makro Excel.

Untuk menggunakan makro yang baru direkodkan, kami harus menyimpan Buku Kerja Excel kami dalam format fail makro yang didayakan. Walau bagaimanapun, sebelum kita berbuat demikian, kita perlu menghapuskan semua data yang sedia ada supaya tidak tertanam dalam templat kami (idea itu setiap kali kita menggunakan templat ini, kita akan mengimport data yang paling terkini).

Untuk melakukan ini, pilih semua sel dan padamkannya.

Dengan data yang kini dibersihkan (tetapi makro masih termasuk dalam fail Excel), kami mahu menyimpan fail tersebut sebagai fail templat makro yang diaktifkan (XLTM). Adalah penting untuk diperhatikan bahawa jika anda menyimpannya sebagai fail template (XLTX) maka makro akan tidak boleh lari daripada itu. Selalunya, anda boleh menyimpan fail sebagai fail template (XLT) warisan, yang akan membolehkan makro dijalankan.

Sebaik sahaja anda telah menyimpan fail sebagai templat, teruskan dan tutup Excel.

Menggunakan Excel Macro

Sebelum meneliti bagaimana kita boleh menggunakan makro yang baru direkodkan, adalah penting untuk merangkumi beberapa perkara mengenai makro secara amnya:

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

Kod VBA sebenarnya cukup kuat dan dapat memanipulasi fail di luar skop dokumen semasa. Sebagai contoh, makro boleh mengubah atau memadam fail rawak dalam folder My Documents anda. Oleh itu, adalah penting untuk memastikan anda sahaja menjalankan makro daripada sumber yang dipercayai.

Untuk meletakkan makro format data kami untuk digunakan, buka fail Templat Excel yang telah dibuat di atas. Apabila anda melakukan ini, dengan mengandaikan bahawa anda mempunyai tetapan keselamatan standard yang dibolehkan, anda akan melihat amaran di bahagian atas buku kerja yang mengatakan bahawa makro dilumpuhkan. Oleh kerana kami mempercayai makro yang dicipta oleh diri kita sendiri, klik butang 'Enable Content'.

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

Untuk melengkapkan import fail CSV, anda mungkin perlu menetapkan beberapa pilihan agar Excel dapat menafsirkannya dengan betul (cth. Pemisah, tajuk, dan sebagainya).

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

Dalam kotak dialog yang dihasilkan, kami melihat makro "FormatData" yang kami rakamkan di atas. Pilihnya dan klik Run.

Setelah berjalan, anda mungkin melihat kursor melompat untuk beberapa saat, tetapi kerana anda akan melihat data dimanipulasi betul-betul seperti yang kita tercatat. Apabila semuanya dikatakan dan dilakukan, ia sepatutnya kelihatan seperti asal kami - kecuali dengan data yang berbeza.

Melihat Di Bawah Hood: 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. Untuk meletakkannya dengan mudah - anda tidak perlu menulis sebarang kod kerana Excel menulis kod untuk anda.

Untuk melihat kod yang membuat makro kami berjalan, dari dialog Macros, klik butang Edit.

Tingkap yang terbuka memaparkan kod sumber yang dirakamkan dari tindakan kami semasa membuat makro. Sudah tentu, anda boleh mengedit kod ini atau membuat makro baru sepenuhnya di dalam tetingkap kod. Walaupun tindakan rakaman yang digunakan dalam artikel ini berkemungkinan sesuai dengan kebanyakan keperluan, tindakan yang lebih disesuaikan atau tindakan bersyarat memerlukan anda untuk mengedit kod sumber.

Mengambil Contoh Kita Satu Langkah Lebih Jauh ...

Hypothetically, anggap fail data sumber kami, data.csv, dihasilkan oleh proses automatik yang sentiasa menyimpan fail ke lokasi yang sama (mis. C: \ Data \ data.csv sentiasa data paling terkini). Proses pembukaan fail ini dan mengimportnya dapat dengan mudah dibuat menjadi makro juga:

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

Sebaik sahaja ini selesai, apabila templat dibuka terdapat dua makro - satu yang memuatkan data kami dan yang lain formatnya.

Jika anda benar-benar ingin mendapatkan tangan anda kotor 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 permulaan kod dari "FormatData".

Muat turun Templat ini

Untuk kemudahan anda, kami telah memasukkan kedua template Excel yang dihasilkan dalam artikel ini serta fail data sampel untuk anda bermain-main dengannya.

Muat turun Templat Makro Excel dari How-To Geek