Tìm hiểu cách sử dụng Macro Excel để tự động hóa các tác vụ nguy hiểm

Một trong những chức năng mạnh mẽ hơn nhưng hiếm khi được sử dụng của Excel là khả năng rất dễ dàng tạo các tác vụ tự động và logic tùy chỉnh trong các macro. Macro cung cấp một cách lý tưởng để tiết kiệm thời gian cho các nhiệm vụ có thể dự đoán được, lặp đi lặp lại cũng như chuẩn hóa các định dạng tài liệu - nhiều lần mà không phải viết một dòng mã.

Nếu bạn tò mò macro là gì hoặc làm thế nào để thực sự tạo ra chúng, không có vấn đề gì - chúng tôi sẽ hướng dẫn bạn toàn bộ quá trình.

Chú thích: quy trình tương tự sẽ hoạt động trong hầu hết các phiên bản Microsoft Office. Các ảnh chụp màn hình có thể trông hơi khác.

Macro là gì?

Microsoft Office Macro (vì chức năng này áp dụng cho một số Ứng dụng MS Office) chỉ đơn giản là mã Visual Basic for Application (VBA) được lưu trong tài liệu. Để có sự tương tự có thể so sánh, hãy nghĩ về một tài liệu là HTML và macro là Javascript. Theo cách tương tự như Javascript có thể thao tác HTML trên trang web, macro có thể thao tác với tài liệu.

Macro rất mạnh mẽ và có thể làm được khá nhiều thứ mà trí tưởng tượng của bạn có thể gợi lên. Là một danh sách ngắn (rất) các hàm bạn có thể thực hiện với macro:

• Áp dụng kiểu và định dạng.
• Thao tác dữ liệu và văn bản.
• Giao tiếp với các nguồn dữ liệu (cơ sở dữ liệu, tệp văn bản, v.v.).
• Tạo tài liệu hoàn toàn mới.
• Bất kỳ sự kết hợp, theo bất kỳ thứ tự nào, bất kỳ ở trên.

Tạo một Macro: Giải thích bằng ví dụ

Chúng tôi bắt đầu với tệp CSV giống vườn của bạn. Không có gì đặc biệt ở đây, chỉ là một bộ số 10 × 20 trong khoảng từ 0 đến 100 với cả tiêu đề hàng và cột. Mục tiêu của chúng tôi là tạo ra một bảng dữ liệu có thể định dạng tốt, bao gồm các tổng số tóm tắt cho mỗi hàng.

Như chúng tôi đã trình bày ở trên, macro là mã VBA, nhưng một trong những điều hay của Excel là bạn có thể tạo / ghi lại chúng với yêu cầu mã hóa bằng 0 - như chúng tôi sẽ làm ở đây.

Để tạo một macro, hãy đi đến Xem> Macro> Ghi macro.

Gán tên macro (không có dấu cách) và bấm OK.

Một khi điều này được thực hiện, tất cả các hành động của bạn được ghi lại - mọi thay đổi ô, hành động cuộn, thay đổi kích thước cửa sổ, bạn đặt tên cho nó.

Có một vài nơi chỉ ra Excel là chế độ ghi. Một là bằng cách xem menu Macro và lưu ý rằng Dừng ghi đã thay thế tùy chọn cho Record Macro.

Cái kia ở góc dưới bên phải. Biểu tượng 'dừng' cho biết nó ở chế độ macro và nhấn vào đây sẽ dừng ghi âm (tương tự, khi không ở chế độ ghi, biểu tượng này sẽ là nút Ghi macro, bạn có thể sử dụng thay vì vào menu Macros).

Bây giờ chúng tôi đang ghi lại macro của chúng tôi, hãy áp dụng các tính toán tóm tắt của chúng tôi. Đầu tiên thêm các tiêu đề.

Tiếp theo, áp dụng các công thức thích hợp (tương ứng):

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

Bây giờ, làm nổi bật tất cả các ô tính toán và kéo độ dài của tất cả các hàng dữ liệu của chúng tôi để áp dụng các tính toán cho mỗi hàng.

Khi điều này được thực hiện, mỗi hàng sẽ hiển thị tóm tắt tương ứng của chúng.

Bây giờ, chúng tôi muốn lấy dữ liệu tóm tắt cho toàn bộ trang tính, vì vậy chúng tôi áp dụng một vài tính toán khác:

Tương ứng:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *Điều này phải được tính trên tất cả dữ liệu vì trung bình của trung bình hàng không nhất thiết phải bằng trung bình của tất cả các giá trị.
• = MIN (N2: N21)
• = MAX (O 2: O21)
• = MEDIAN (B2: K21) * Tính trên tất cả các dữ liệu cho cùng một lý do như trên.

Bây giờ các tính toán đã được thực hiện, chúng tôi sẽ áp dụng kiểu dáng và định dạng. Trước tiên, áp dụng định dạng số chung trên tất cả các ô bằng cách thực hiện Chọn tất cả (Ctrl + A hoặc nhấp vào ô giữa các tiêu đề hàng và cột) và chọn biểu tượng Kiểu Comma Comma trong menu Home.

Tiếp theo, áp dụng một số định dạng trực quan cho cả tiêu đề hàng và cột:

• Dũng cảm.
• Trung tâm.
• Màu nền.

Và cuối cùng, áp dụng một số phong cách cho tổng số.

Khi tất cả kết thúc, đây là bảng dữ liệu của chúng tôi trông như sau:

Vì chúng tôi hài lòng với kết quả, hãy dừng ghi macro.

Xin chúc mừng - bạn vừa tạo một macro Excel.

Để sử dụng macro mới được ghi lại của chúng tôi, chúng tôi phải lưu Sổ làm việc Excel của chúng tôi ở định dạng tệp được bật macro. Tuy nhiên, trước khi thực hiện điều đó, trước tiên chúng tôi cần xóa tất cả dữ liệu hiện có để nó không được nhúng trong mẫu của chúng tôi (ý tưởng là mỗi khi chúng tôi sử dụng mẫu này, chúng tôi sẽ nhập dữ liệu cập nhật nhất).

Để làm điều này, chọn tất cả các ô và xóa chúng.

Với dữ liệu hiện đã bị xóa (nhưng các macro vẫn có trong tệp Excel), chúng tôi muốn lưu tệp dưới dạng tệp mẫu được bật macro (XLTM). Điều quan trọng cần lưu ý là nếu bạn lưu tệp này dưới dạng tệp chuẩn (XLTX) thì macro sẽ không phải có thể được chạy từ nó. Thay phiên, bạn có thể lưu tệp dưới dạng tệp kế thừa (XLT), điều này sẽ cho phép các macro chạy.

Khi bạn đã lưu tệp dưới dạng mẫu, hãy tiếp tục và đóng Excel.

Sử dụng Macro Excel

Trước khi trình bày cách chúng ta có thể áp dụng macro mới được ghi lại này, điều quan trọng là phải nói một vài điểm về macro nói chung:

• Macro có thể độc hại.
• Xem điểm trên.

Mã VBA thực sự khá mạnh và có thể thao tác các tệp bên ngoài phạm vi của tài liệu hiện tại. Ví dụ: một macro có thể thay đổi hoặc xóa các tệp ngẫu nhiên trong thư mục Tài liệu của tôi. Vì vậy, điều quan trọng là đảm bảo bạn chỉ có chạy macro từ các nguồn đáng tin cậy.

Để đặt macro định dạng dữ liệu của chúng tôi để sử dụng, hãy mở tệp Mẫu Excel đã được tạo ở trên. Khi bạn thực hiện việc này, giả sử bạn đã bật cài đặt bảo mật tiêu chuẩn, bạn sẽ thấy cảnh báo trên đầu của sổ làm việc cho biết macro bị tắt. Vì chúng tôi tin tưởng một macro do chính chúng tôi tạo ra, hãy nhấp vào nút 'Bật nội dung'.

Tiếp theo, chúng tôi sẽ nhập bộ dữ liệu mới nhất từ ​​CSV (đây là nguồn mà bảng tính được sử dụng để tạo macro của chúng tôi).

Để hoàn tất quá trình nhập tệp CSV, bạn có thể phải đặt một vài tùy chọn để Excel diễn giải chính xác (ví dụ: dấu phân cách, tiêu đề hiện tại, v.v.).

Khi dữ liệu của chúng tôi được nhập, chỉ cần chuyển đến trình đơn Macros (trong tab Xem) và chọn Xem macro.

Trong hộp thoại kết quả, chúng ta thấy macro Format Formatataata mà chúng ta đã ghi ở trên. Chọn nó và nhấp vào Chạy.

Sau khi chạy, bạn có thể thấy con trỏ nhảy xung quanh một lúc, nhưng vì nó sẽ thấy dữ liệu bị thao túng chính xác như chúng tôi đã ghi lại Khi tất cả được nói và thực hiện, nó sẽ trông giống như bản gốc của chúng tôi - ngoại trừ với các dữ liệu khác nhau.

Nhìn dưới mui xe: Điều gì tạo nên một tác phẩm vĩ mô

Như chúng tôi đã đề cập một vài lần, một macro được điều khiển bởi mã Visual Basic for Application (VBA). Khi bạn ghi lại một macro, Excel thực sự đang dịch mọi thứ bạn làm thành các hướng dẫn VBA tương ứng. Nói một cách đơn giản - bạn không phải viết bất kỳ mã nào vì Excel đang viết mã cho bạn.

Để xem mã làm cho macro của chúng tôi chạy, từ hộp thoại Macro, nhấp vào nút Chỉnh sửa.

Cửa sổ mở sẽ hiển thị mã nguồn được ghi lại từ các hành động của chúng tôi khi tạo macro. Tất nhiên, bạn có thể chỉnh sửa mã này hoặc thậm chí tạo các macro mới hoàn toàn bên trong cửa sổ mã. Mặc dù hành động ghi được sử dụng trong bài viết này có thể sẽ phù hợp với hầu hết các nhu cầu, nhưng hành động tùy chỉnh cao hơn hoặc hành động có điều kiện sẽ yêu cầu bạn chỉnh sửa mã nguồn.

Lấy ví dụ của chúng tôi Một bước xa hơn

Về mặt giả thuyết, giả sử tệp dữ liệu nguồn của chúng tôi, data.csv, được tạo bởi một quy trình tự động luôn lưu tệp vào cùng một vị trí (ví dụ: C: \ Data \ data.csv luôn là dữ liệu gần đây nhất). Quá trình mở tệp này và nhập tệp cũng có thể dễ dàng được tạo thành một macro:

1. Mở tệp Mẫu Excel có chứa macro Format Formatataata của chúng tôi.
2. Ghi lại một macro mới có tên là Tải LoadData.
3. Với bản ghi macro, nhập tệp dữ liệu như bạn thường làm.
4. Sau khi dữ liệu được nhập, dừng ghi macro.
5. Xóa tất cả dữ liệu di động (chọn tất cả sau đó xóa).
6. Lưu mẫu đã cập nhật (nhớ sử dụng định dạng mẫu được bật macro).

Khi điều này được thực hiện, bất cứ khi nào mẫu được mở sẽ có hai macro - một macro tải dữ liệu của chúng tôi và cái kia định dạng nó.

Nếu bạn thực sự muốn làm bẩn tay với một chút chỉnh sửa mã, bạn có thể dễ dàng kết hợp các hành động này thành một macro duy nhất bằng cách sao chép mã được tạo ra từ Tải LoadData, và chèn nó vào đầu mã từ Định dạng Định dạngData..

Tải xuống mẫu này

Để thuận tiện cho bạn, chúng tôi đã bao gồm cả mẫu Excel được tạo trong bài viết này cũng như tệp dữ liệu mẫu để bạn chơi xung quanh.

Tải xuống Mẫu Macro Excel từ How-To Geek