Tìm hiểu cách sử dụng macro Excel để tự động hóa các tác vụ tẻ nhạt

2024 Tác giả: Geoffrey Carr | [email protected]. Sửa đổi lần cuối: 2023-12-17 11:02

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 tạo ra các nhiệm vụ tự động và logic tùy chỉnh trong các macro một cách dễ dàng. Macro cung cấp một cách lý tưởng để tiết kiệm thời gian cho các tác vụ có thể dự đoán, 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 cần phải viết một dòng mã.

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

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

Macro là gì?

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

Macros cực kỳ mạnh mẽ và có thể làm khá nhiều thứ mà trí tưởng tượng của bạn có thể gợi lên. Như một danh sách ngắn (rất) các hàm bạn có thể làm 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 nào, theo bất kỳ thứ tự nào, của bất kỳ điều nào ở trên.

Tạo Macro: Giải thích bằng Ví dụ

Chúng tôi bắt đầu với tệp CSV vườn của bạn. Không có gì đặc biệt ở đây, chỉ cần 10 × 20 bộ số 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 định dạng, có thể trình bày, bao gồm tổng số tóm tắt cho mỗi hàng.

Như chúng tôi đã nói ở trên, một macro là mã VBA, nhưng một trong những điều tốt đẹp về Excel là bạn có thể tạo / ghi lại chúng với mã hóa không bắt buộc - như chúng ta sẽ làm ở đây.

Để tạo macro, hãy chuyển đến Xem> Macro> Macro bản ghi.

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

Một khi điều này được thực hiện, tất cả cá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 cho biết Excel là chế độ ghi. Một là bằng cách xem menu Macro và lưu ý rằng Stop Recording đã thay thế tùy chọn cho Record Macro.

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

Bây giờ chúng tôi đang ghi lại macro của mình, hãy áp dụng các tính toán tóm tắt của chúng tôi. Đầu tiên hãy thêm 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ờ, đánh dấu tất cả các ô tính toán và kéo chiều 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 phép tính 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:

= SUM (L2: L21)
= AVERAGE (B2: K21) *Điều này phải được tính toán trên tất cả dữ liệu bởi vì mức trung bình của hàng trung bình không nhất thiết phải bằng mức trung bình của tất cả các giá trị.
= MIN (N2: N21)
= MAX (O2: 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 và định dạng. Trước tiên, hãy á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 tiêu đề hàng và cột) và chọn biểu tượng “Kiểu Comma” trong menu Trang chủ.

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

Dũng cảm.
Căn giữa.
Màu tô nền.

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

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

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

Xin chúc mừng - bạn vừa tạo 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 chúng ta làm điều đó, trước tiên chúng ta cần xóa tất cả dữ liệu hiện có để nó không được nhúng vào mẫu của chúng ta (ý tưởng là mỗi khi chúng ta sử dụng mẫu này, chúng ta sẽ nhập dữ liệu cập nhật mới nhất).

Để thực hiện việc này, hãy chọn tất cả các ô và xóa chúng.

Với dữ liệu đã được xóa (nhưng các macro vẫn được bao gồm 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 tiêu chuẩn (XLTX) thì macro sẽ không phải có thể được chạy từ nó. Cách khác, bạn có thể lưu tệp dưới dạng tệp mẫu cũ (XLT), tệp này sẽ cho phép 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 bao quát 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 My Documents của bạn.Như vậy, điều quan trọng là phải đả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 chuẩn, bạn sẽ thấy cảnh báo trên đầu sổ làm việc cho biết các macro bị tắt. Bởi vì chúng tôi tin tưởng một macro được tạo bởi chính chúng ta, hãy nhấp vào nút ‘Bật nội dung’.

Tiếp theo, chúng tôi sẽ nhập tập dữ liệu mới nhất từ CSV (đây là nguồn trang 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 đề, v.v.).

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

Trong hộp thoại kết quả, chúng ta thấy macro “FormatData” mà chúng tôi đã ghi ở trên. Chọn nó và bấm Chạy.

Sau khi chạy, bạn có thể thấy con trỏ nhảy xung quanh trong một vài phút, nhưng vì bạn sẽ thấy dữ liệu đang được xử lý chính xác khi chúng tôi ghi lại nó. 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 dữ liệu khác nhau.

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

Như chúng tôi đã đề cập một vài lần, một macro được thúc đẩy bởi Visual Basic cho các ứng dụng (VBA) mã. Khi bạn "ghi lại" một macro, Excel thực sự dịch tất cả mọi thứ bạn làm vào các hướng dẫn VBA tương ứng của nó. 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ở ra 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 âm được sử dụng trong bài viết này có thể sẽ phù hợp với hầu hết nhu cầu, hành động được 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…

Theo 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ó thể dễ dàng được tạo thành macro:

Mở tệp Mẫu Excel chứa macro “FormatData” của chúng tôi.
Ghi lại một macro mới có tên là "LoadData".
Với tính năng ghi macro, nhập tệp dữ liệu như bình thường.
Khi dữ liệu được nhập, dừng ghi macro.
Xóa tất cả dữ liệu ô (chọn tất cả rồi xóa).
Lưu mẫu đã cập nhật (nhớ sử dụng định dạng mẫu đã bật macro).

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