Nội dung
Excel có một khả năng mạnh mẽ, nhưng đồng thời rất hiếm khi được sử dụng, để tạo chuỗi hành động tự động bằng cách sử dụng macro. Macro là một cách lý tưởng nếu bạn đang xử lý cùng một loại nhiệm vụ được lặp lại nhiều lần. Ví dụ, xử lý dữ liệu hoặc định dạng tài liệu theo một khuôn mẫu đã được chuẩn hóa. Trong trường hợp này, bạn không cần kiến thức về ngôn ngữ lập trình.
Bạn đã tò mò về macro là gì và nó hoạt động như thế nào chưa? Sau đó hãy mạnh dạn tiến hành - sau đó chúng tôi sẽ cùng bạn thực hiện từng bước toàn bộ quá trình tạo macro.
Macro là gì?
Macro trong Microsoft Office (vâng, chức năng này hoạt động giống nhau trong nhiều ứng dụng của gói Microsoft Office) là mã chương trình bằng ngôn ngữ lập trình Visual Basic cho các ứng dụng (VBA) được lưu trữ bên trong tài liệu. Để làm rõ hơn, một tài liệu Microsoft Office có thể được so sánh với một trang HTML, sau đó macro là một dạng tương tự của Javascript. Những gì Javascript có thể làm với dữ liệu HTML trong một trang web rất giống với những gì macro có thể làm với dữ liệu trong tài liệu Microsoft Office.
Macro có thể thực hiện bất cứ điều gì bạn muốn trong tài liệu. Dưới đây là một số trong số chúng (một phần rất nhỏ):
- Áp dụng kiểu và định dạng.
- Thực hiện các hoạt động khác nhau với dữ liệu số và văn bản.
- Sử dụng các nguồn dữ liệu bên ngoài (tệp cơ sở dữ liệu, tài liệu văn bản, v.v.)
- Tạo một tài liệu mới.
- Thực hiện tất cả những điều trên trong bất kỳ sự kết hợp nào.
Tạo macro - một ví dụ thực tế
Ví dụ: hãy lấy tệp phổ biến nhất CSV. Đây là một bảng 10 × 20 đơn giản chứa đầy các số từ 0 đến 100 với tiêu đề cho các cột và hàng. Nhiệm vụ của chúng tôi là biến tập dữ liệu này thành một bảng được định dạng rõ ràng và tạo tổng số trong mỗi hàng.
Như đã đề cập, macro là mã được viết bằng ngôn ngữ lập trình VBA. Nhưng trong Excel, bạn có thể tạo một chương trình mà không cần viết một dòng mã, điều này chúng ta sẽ thực hiện ngay bây giờ.
Để tạo macro, hãy mở Xem (Loại)> Macros (Macro)> Ghi Macro (Ghi macro…)
Đặt tên cho macro của bạn (không có khoảng trắng) và nhấp vào OK.
Bắt đầu từ thời điểm này, TẤT CẢ các thao tác của bạn với tài liệu đều được ghi lại: thay đổi ô, cuộn qua bảng, thậm chí thay đổi kích thước cửa sổ.
Excel báo hiệu rằng chế độ ghi macro được bật ở hai nơi. Đầu tiên, trên menu Macros (Macro) - thay vì một chuỗi Ghi Macro (Đang ghi macro…) xuất hiện dòng Dừng ghi âm (Dừng ghi).
Thứ hai, ở góc dưới bên trái của cửa sổ Excel. Biểu tượng Dừng (hình vuông nhỏ) cho biết rằng chế độ ghi macro đã được bật. Nhấp vào nó sẽ dừng ghi. Ngược lại, khi chưa bật chế độ ghi hình, tại vị trí này sẽ có biểu tượng cho phép ghi macro. Nhấp vào nó sẽ cho kết quả tương tự như bật ghi âm qua menu.
Bây giờ chế độ ghi macro đã được kích hoạt, chúng ta hãy bắt đầu công việc của mình. Trước hết, hãy thêm tiêu đề cho dữ liệu tóm tắt.
Tiếp theo, nhập các công thức vào các ô theo tên của các tiêu đề (có các biến thể của công thức tiếng Anh và phiên bản Excel, địa chỉ ô luôn là các chữ cái và số Latinh):
- = SUM (B2: K2) or = SUM (B2: K2)
- = AVERAGE (B2: K2) or = СРЗНАЧ (B2: K2)
- = MIN (B2: K2) or = MIN (B2: K2)
- = MAX (B2: K2) or = MAX (B2: K2)
- = MEDIAN (B2: K2) or = MEDIAN (B2: K2)
Bây giờ, hãy chọn các ô có công thức và sao chép chúng vào tất cả các hàng trong bảng của chúng ta bằng cách kéo chốt điều khiển tự động điền.
Sau khi bạn hoàn thành bước này, mỗi hàng sẽ có tổng số tương ứng.
Tiếp theo, chúng tôi sẽ tóm tắt kết quả cho toàn bộ bảng, để thực hiện thêm một số phép toán:
Tương ứng:
- = SUM (L2: L21) or = SUM (L2: L21)
- = AVERAGE (B2: K21) or = СРЗНАЧ (B2: K21) - để tính giá trị này cần lấy chính xác dữ liệu ban đầu của bảng. Nếu bạn lấy giá trị trung bình của các giá trị trung bình cho các hàng riêng lẻ, kết quả sẽ khác.
- = MIN (N2: N21) or = MIN (N2: N21)
- = MAX (O2: O21) or = MAX (O2: O21)
- = MEDIAN (B2: K21) or = MEDIAN (B2: K21) - chúng tôi xem xét sử dụng dữ liệu ban đầu của bảng, vì lý do đã nêu ở trên.
Bây giờ chúng ta đã hoàn thành các phép tính, hãy thực hiện một số định dạng. Đầu tiên, hãy đặt cùng một định dạng hiển thị dữ liệu cho tất cả các ô. Chọn tất cả các ô trên trang tính, để thực hiện việc này, hãy sử dụng phím tắt Ctrl + Ahoặc nhấp vào biểu tượng Chọn tất cả, nằm ở giao điểm của tiêu đề hàng và cột. Sau đó nhấp vào Kiểu dấu phẩy (Định dạng phân tách) Trang Chủ (Nhà).
Tiếp theo, thay đổi giao diện của tiêu đề cột và hàng:
- Kiểu phông chữ đậm.
- Căn giữa.
- Điền màu.
Và cuối cùng, hãy thiết lập định dạng của các tổng.
Cuối cùng thì nó sẽ trông như thế này:
Nếu mọi thứ phù hợp với bạn, hãy ngừng ghi macro.
Xin chúc mừng! Bạn vừa tự ghi macro đầu tiên của mình trong Excel.
Để sử dụng macro đã tạo, chúng ta cần lưu tài liệu Excel ở định dạng hỗ trợ macro. Đầu tiên, chúng ta cần xóa tất cả dữ liệu khỏi bảng mà chúng ta đã tạo, tức là làm cho nó trở thành một mẫu trống. Thực tế là trong tương lai, khi làm việc với mẫu này, chúng tôi sẽ nhập dữ liệu gần đây nhất và có liên quan vào đó.
Để xóa tất cả các ô khỏi dữ liệu, hãy nhấp chuột phải vào biểu tượng Chọn tất cả, nằm ở giao điểm của tiêu đề hàng và cột, và từ menu ngữ cảnh, hãy chọn Xóa bỏ (Xóa bỏ).
Bây giờ trang tính của chúng tôi bị xóa hoàn toàn khỏi tất cả dữ liệu, trong khi macro vẫn được ghi lại. Chúng tôi cần lưu sổ làm việc dưới dạng mẫu Excel hỗ trợ macro có phần mở rộng XLTM.
Một điểm quan trọng! Nếu bạn lưu tệp với phần mở rộng XLTX, thì macro sẽ không hoạt động trong đó. Nhân tiện, bạn có thể lưu sổ làm việc dưới dạng mẫu Excel 97-2003, có định dạng XLT, nó cũng hỗ trợ macro.
Khi mẫu được lưu, bạn có thể đóng Excel một cách an toàn.
Chạy Macro trong Excel
Trước khi tiết lộ tất cả các khả năng của macro bạn đã tạo, tôi nghĩ đúng là nên chú ý đến một số điểm quan trọng liên quan đến macro nói chung:
- Macro có thể có hại.
- Đọc lại đoạn trước.
Mã VBA rất mạnh mẽ. Đặc biệt, nó có thể thực hiện các thao tác trên các tệp bên ngoài tài liệu hiện tại. Ví dụ: macro có thể xóa hoặc sửa đổi bất kỳ tệp nào trong một thư mục Tài liệu của tôi. Vì lý do này, chỉ chạy và cho phép macro từ các nguồn mà bạn tin tưởng.
Để chạy macro định dạng dữ liệu của chúng tôi, hãy mở tệp mẫu mà chúng tôi đã tạo trong phần đầu tiên của hướng dẫn này. Nếu bạn có cài đặt bảo mật tiêu chuẩn, thì khi bạn mở tệp, cảnh báo sẽ xuất hiện phía trên bảng rằng macro bị tắt và một nút để bật chúng. Vì chúng tôi tự tạo mẫu và chúng tôi tin tưởng vào chính mình, chúng tôi nhấn nút Bật nội dung (Bao gồm nội dung).
Bước tiếp theo là nhập tập dữ liệu được cập nhật mới nhất từ tệp CSV (dựa trên một tệp như vậy, chúng tôi đã tạo macro của mình).
Khi bạn nhập dữ liệu từ tệp CSV, Excel có thể yêu cầu bạn thiết lập một số cài đặt để chuyển dữ liệu vào bảng một cách chính xác.
Khi quá trình nhập kết thúc, hãy chuyển đến menu Macros Tab (Macro) Xem (Xem) và chọn một lệnh Xem Macro (Vĩ mô).
Trong hộp thoại mở ra, chúng ta sẽ thấy một dòng có tên macro của chúng ta định dạngdữ liệu. Chọn nó và nhấp vào chạy (Hành hình).
Khi macro bắt đầu chạy, bạn sẽ thấy con trỏ bảng nhảy từ ô này sang ô khác. Sau một vài giây, các thao tác tương tự sẽ được thực hiện với dữ liệu như khi ghi macro. Khi mọi thứ đã sẵn sàng, bảng sẽ giống như bản gốc mà chúng tôi đã định dạng thủ công, chỉ khác với dữ liệu trong các ô.
Hãy cùng tìm hiểu kỹ: Macro hoạt động như thế nào?
Như đã đề cập nhiều lần, macro là mã chương trình trong ngôn ngữ lập trình. Visual Basic cho các ứng dụng (VBA). Khi bạn bật chế độ ghi macro, Excel thực sự ghi lại mọi hành động bạn thực hiện dưới dạng hướng dẫn VBA. Nói một cách đơn giản, Excel sẽ viết mã cho bạn.
Để xem mã chương trình này, bạn cần vào menu Macros Tab (Macro) Xem (xem) bấm Xem Macro (Macro) và trong hộp thoại mở ra, hãy nhấp vào Chỉnh sửa (Thay đổi).
Cửa sổ mở ra. Visual Basic cho các ứng dụng, trong đó chúng ta sẽ thấy mã chương trình của macro mà chúng ta đã ghi lại. Vâng, bạn đã hiểu đúng, ở đây bạn có thể thay đổi mã này và thậm chí tạo một macro mới. Các hành động mà chúng tôi đã thực hiện với bảng trong bài học này có thể được ghi lại bằng cách sử dụng tính năng ghi macro tự động trong Excel. Nhưng các macro phức tạp hơn, với trình tự và logic hành động được tinh chỉnh, yêu cầu lập trình thủ công.
Hãy thêm một bước nữa vào nhiệm vụ của chúng ta…
Hãy tưởng tượng rằng tệp dữ liệu ban đầu của chúng tôi dữ liệu.csv được tạo tự động bởi một số quy trình và luôn được lưu trữ trên đĩa ở cùng một nơi. Ví dụ, C: Datadata.csv - đường dẫn đến tệp với dữ liệu cập nhật. Quá trình mở tệp này và nhập dữ liệu từ tệp này cũng có thể được ghi lại trong một macro:
- Mở tệp mẫu nơi chúng tôi đã lưu macro - định dạngdữ liệu.
- Tạo một macro mới có tên Tải dữ liệu.
- Trong khi ghi macro Tải dữ liệu nhập dữ liệu từ tệp dữ liệu.csv - như chúng ta đã làm trong phần trước của bài học.
- Khi quá trình nhập hoàn tất, hãy dừng ghi macro.
- Xóa tất cả dữ liệu khỏi ô.
- Lưu tệp dưới dạng mẫu Excel hỗ trợ macro (phần mở rộng XLTM).
Do đó, bằng cách chạy mẫu này, bạn có quyền truy cập vào hai macro - một macro tải dữ liệu, macro kia định dạng chúng.
Nếu bạn muốn tham gia vào lập trình, bạn có thể kết hợp các hành động của hai macro này thành một - đơn giản bằng cách sao chép mã từ Tải dữ liệu đến đầu mã định dạngdữ liệu.