10 mẹo chuyên nghiệp để tạo macro Excel
*Mục Lục *
2. Sử dụng địa chỉ ô tương đối (không tuyệt đối)
4. Luôn điều hướng bằng các phím định hướng
8. Thêm macro của bạn vào nút tùy chỉnh trên thanh công cụ Truy cập nhanh
9. Thêm nút macro vào nhóm tùy chỉnh trên menu Ribbon
1. Tên macro
Giữ tên macro ngắn (nhưng có tính mô tả), đặc biệt nếu bạn ghi nhiều macro, để bạn có thể dễ dàng xác định chúng trong Hộp thoại Macro. Hệ thống cũng cung cấp một trường Mô tả, mặc dù không phải ai cũng sử dụng nó.
Tên macro phải bắt đầu bằng một chữ cái và không được chứa dấu cách, ký hiệu hoặc dấu chấm câu. Sau chữ cái đầu tiên, bạn có thể sử dụng thêm các chữ cái, số hoặc ký tự gạch dưới nhưng độ dài tối đa là 80 ký tự.
2. Sử dụng địa chỉ ô tương đối (không tuyệt đối)
Tuyệt đối có nghĩa là các vị trí ô chính xác được ghi vào macro—địa chỉ ô được mã hóa cứng chẳng hạn như A6 hoặc B12. Tham chiếu tuyệt đối giới hạn khả năng hoạt động của macro nếu có bất kỳ thay đổi nào, dữ liệu mới được thêm/xóa hoặc danh sách dài hơn. Tương đối có nghĩa là các lần nhấn phím được ghi lại của macro có liên quan đến vị trí của ô bắt đầu.
Giá trị mặc định trong Excel là Tuyệt đối, nhưng bạn có thể thay đổi giá trị này thành Tương đối trên thanh công cụ Dừng ghi:
Nhấp vào Nhà phát triển > Ghi Macro.
• Trong hộp thoại Ghi Macro, hãy nhập tên macro và Phím tắt (nếu có). Chọn Sổ làm việc Macro Cá nhân từ Cửa hàng Macro Trong danh sách lựa chọn thả xuống, nhập mô tả (nếu muốn) và bấm OK.
• Hộp thoại biến mất và nút Ghi macro thay đổi thành nút Dừng ghi. Nhấp vào nút Tham chiếu tương đối bên cạnh—nút này chuyển sang màu xanh đậm để cho biết rằng nó đang hoạt động.
• Nhập tổ hợp phím, công thức, v.v., sau đó nhấp vào nút Dừng ghi và chạy macro của bạn.
3. Luôn bắt đầu tại Nhà
Luôn bắt đầu ở vị trí Trang đầu (ô A1)—với con trỏ và dữ liệu của bạn. Nếu bạn đã lưu macro của mình trong Sổ làm việc Macro Cá nhân (được khuyến nghị), bạn có thể sử dụng lại macro này trên các trang tính khác có dữ liệu tương tự.
Bất kể con trỏ của bạn được định vị ở đâu khi bạn bắt đầu ghi macro, ngay cả khi nó đã nằm trong ô A1, tổ hợp phím macro đầu tiên của bạn phải là Ctrl+Home để đảm bảo bạn bắt đầu ở Home.
Ví dụ: Hãy tưởng tượng rằng mỗi tháng bạn nhận được hàng chục bảng tính từ tất cả các văn phòng chi nhánh mà trước tiên bạn phải kết hợp, sau đó tổ chức và tính toán để tạo ra một báo cáo hàng tháng. Bạn có thể viết macro để thực hiện tất cả các chức năng này, bao gồm mở và kết hợp tất cả các trang tính vào một bảng tính kết hợp. Đối với bài tập này, tôi sẽ chỉ giải quyết bảng tính cuối cùng với dữ liệu kết hợp.
3.1. Tạo sổ làm việc mới trong Excel (ví dụ: Báo cáo tháng 4). Con trỏ của bạn sẽ tự động được định vị ở Vị trí ban đầu (A1). Không di chuyển nó để phù hợp với tiêu đề, tiêu đề trường hoặc bất kỳ thứ gì khác. Nó phải được định vị trong A1.
3.2. Mở sổ làm việc nhánh đầu tiên và sao chép toàn bộ dữ liệu của bảng tính (trừ các tiêu đề cột) vào ô A1 trong bảng tính Báo cáo tháng 4 của bạn. Dữ liệu bắt đầu ở A1 và kết thúc ở G55.
3.3. Mở sổ làm việc nhánh thứ hai và sao chép toàn bộ dữ liệu của bảng tính (trừ các tiêu đề cột) vào ô A56 trong bảng tính Báo cáo tháng 4 của bạn. Tiếp tục quá trình này cho đến khi dữ liệu trong tất cả các sổ làm việc được sao chép vào bảng tính Báo cáo tháng 4 của bạn.
3.4. Nếu dữ liệu của bảng tính khác chứa công thức, hãy chọn Dán > Đặc biệt > Giá trị cho các tùy chọn Dán của bạn. Không sao chép các công thức hoặc các định dạng.
3.5. Tại thời điểm này, đừng lo lắng về tiêu đề trang tính, tiêu đề cột hoặc định dạng (chẳng hạn như độ rộng cột, phông chữ, v.v.). Chỉ cần sao chép dữ liệu và chạy macro.
3.6. Sau khi dữ liệu được tính toán, bạn có thể sử dụng Chèn hàng để thêm tiêu đề cột, tiêu đề bảng tính và thông tin khác. Sau đó, bạn có thể định dạng độ rộng cột, thay đổi phông chữ, thêm thuộc tính và đặt đường viền hoặc hộp nếu muốn. (Bạn cũng có thể tạo một macro bổ sung để thực hiện các tác vụ định dạng.)
4. Luôn điều hướng bằng các phím định hướng
Sử dụng các phím định hướng (End-Down, Ctrl+Up, v.v.) để định vị con trỏ của bạn để bạn có thể thêm, thay đổi hoặc xóa dữ liệu bên trong bảng tính nếu cần. Nếu bạn mã hóa cứng địa chỉ ô, bất kỳ thay đổi hoặc chỉnh sửa nào đối với bảng tính đều có thể làm mất hiệu lực các hàm macro. Ví dụ: nếu bạn mã hóa macro để tính tổng từ A1 đến A20, sau đó thêm nhiều hàng hơn (ví dụ: A1 đến A35), thì công thức sẽ không bao gồm các ô bổ sung và tổng sẽ sai.
Sử dụng chuột để điều hướng phức tạp hơn và không đáng tin cậy. Khi nói đến macro, chỉ sử dụng chuột để chọn hoặc nhấp vào các tùy chọn menu.
5. Giữ macro nhỏ và cụ thể
Giữ macro của bạn nhỏ và cụ thể cho các tác vụ hiện tại. Macro càng lớn thì chạy càng chậm, đặc biệt nếu cần thực hiện nhiều chức năng hoặc tính toán nhiều công thức trong một bảng tính lớn. Ngoài ra, nếu bạn kết hợp tất cả các tác vụ vào một macro dài và macro đó không thành công, bạn sẽ mất rất nhiều thời gian để xác định điểm xảy ra lỗi. Nếu bạn chạy riêng từng macro, bạn có thể nhanh chóng xem lại kết quả và xác minh độ chính xác.
Trong ví dụ về Báo cáo tháng 4, bạn sẽ không kết hợp macro tính toán với macro định dạng. Bạn sẽ sử dụng một macro để kết hợp tất cả sổ làm việc nhánh vào một bảng tính cuối cùng, macro thứ hai để tính toán dữ liệu, macro thứ ba để định dạng bảng tính, macro thứ tư để tạo biểu đồ và có thể là macro thứ năm để in.
Nếu một macro bị lỗi, bạn có thể khắc phục sự cố bằng cách lướt qua từng dòng một. Nó giống như chuyển động chậm, chỉ bạn điều khiển mỗi khi sự kiện tiếp theo xảy ra bằng phím F8. Nhấp vào Nhà phát triển > Macro, chọn macro của bạn từ danh sách, sau đó nhấp vào nút Bước vào. Thu nhỏ cửa sổ Mã và đặt nó ở dưới cùng bên phải của màn hình để bạn có thể nhìn thấy bảng tính của mình phía sau nó. Nhấn F8 mỗi lần bạn muốn macro chuyển sang bước tiếp theo. Macro dừng khi gặp lỗi. Sau đó, bạn có thể sửa lỗi hoặc ghi một macro mới.
6. Nhập dữ liệu tĩnh trước
Bất kể con trỏ của bạn được định vị ở đâu khi bạn bắt đầu ghi macro, ngay cả khi nó đã nằm trong ô A1, tổ hợp phím macro đầu tiên của bạn phải là Ctrl+Home để đảm bảo bạn bắt đầu ở Home.
Ví dụ: Hãy tưởng tượng rằng mỗi tháng bạn nhận được hàng chục bảng tính từ tất cả các văn phòng chi nhánh mà trước tiên bạn phải kết hợp, sau đó tổ chức và tính toán để tạo ra một báo cáo hàng tháng. Bạn có thể viết macro để thực hiện tất cả các chức năng này, bao gồm mở và kết hợp tất cả các trang tính vào một bảng tính kết hợp. Đối với bài tập này, tôi sẽ chỉ giải quyết bảng tính cuối cùng với dữ liệu kết hợp.
6.1. Tạo sổ làm việc mới trong Excel (ví dụ: Báo cáo tháng 4). Con trỏ của bạn sẽ tự động được định vị ở Vị trí ban đầu (A1). Không di chuyển nó để phù hợp với tiêu đề, tiêu đề trường hoặc bất kỳ thứ gì khác. Nó phải được định vị trong A1.
6.2. Mở sổ làm việc nhánh đầu tiên và sao chép toàn bộ dữ liệu của bảng tính (trừ các tiêu đề cột) vào ô A1 trong bảng tính Báo cáo tháng 4 của bạn. Dữ liệu bắt đầu ở A1 và kết thúc ở G55.
6.3. Mở sổ làm việc nhánh thứ hai và sao chép toàn bộ dữ liệu của bảng tính (trừ các tiêu đề cột) vào ô A56 trong bảng tính Báo cáo tháng 4 của bạn. Tiếp tục quá trình này cho đến khi dữ liệu trong tất cả các sổ làm việc được sao chép vào bảng tính Báo cáo tháng 4 của bạn.
6.4. Nếu dữ liệu của bảng tính khác chứa công thức, hãy chọn Dán > Đặc biệt > Giá trị cho các tùy chọn Dán của bạn. Không sao chép các công thức hoặc các định dạng.
6.5. Tại thời điểm này, đừng lo lắng về tiêu đề trang tính, tiêu đề cột hoặc định dạng (chẳng hạn như độ rộng cột, phông chữ, v.v.). Chỉ cần sao chép dữ liệu và chạy macro.
6.6. Sau khi dữ liệu được tính toán, bạn có thể sử dụng Chèn hàng để thêm tiêu đề cột, tiêu đề bảng tính và thông tin khác. Sau đó, bạn có thể định dạng độ rộng cột, thay đổi phông chữ, thêm thuộc tính và đặt đường viền hoặc hộp nếu muốn. (Bạn cũng có thể tạo một macro bổ sung để thực hiện các tác vụ định dạng.)
7. Lưu bảng tính
Sau khi bạn tạo macro trong Excel, tệp sẽ trở thành sổ làm việc hỗ trợ macro với phần mở rộng xlsm. Nếu bạn cố gắng lưu tệp dưới dạng bảng tính thông thường (xlsx), Excel sẽ hiển thị hộp thoại cảnh báo cho biết "Không thể lưu các tính năng sau trong sổ làm việc không có macro", rồi hiển thị danh sách các tính năng đó. Bấm vào nút Không, sau đó chọn Lưu dưới dạng một lần nữa và trong hộp thoại thả xuống Lưu dưới dạng, chọn Sổ làm việc hỗ trợ macro Excel (*.xlsm) từ danh sách. Nhấp vào nút Lưu.
8. Thêm macro của bạn vào nút tùy chỉnh trên thanh công cụ Truy cập nhanh
Để thuận tiện, bạn có thể thêm một nút trên thanh công cụ Truy cập nhanh cho các macro của mình. Vì có rất ít phím tắt nên đây là một giải pháp hữu ích.
8.1. Chọn Tệp > Tùy chọn > Thanh công cụ truy cập nhanh.
8.2. Trong hộp Chọn Lệnh Từ, nhấp vào mũi tên nhỏ và chọn Macro từ danh sách.
8.3. Trong hộp thoại bên dưới, Excel sẽ hiển thị danh sách tất cả các macro trong sổ làm việc này.
8.4. Chọn macro SumTotals, sau đó nhấp vào nút Thêm để di chuyển macro vào hộp thoại Thanh công cụ Tùy chỉnh Truy cập Nhanh.
8.5. Với macro SumTotals được tô sáng, nhấp vào nút Sửa đổi ở cuối bảng hộp thoại này.
8.6. Duyệt qua cửa sổ hộp thoại Modify Button và chọn một ký hiệu biểu tượng xác định rõ nhất macro mới của bạn (chúng tôi đã chọn máy tính).
8.7. Tiếp theo, nhập tên mô tả cho macro của bạn vào hộp Tên hiển thị.
8.8. Và cuối cùng, nhấp vào nút OK hai lần.
9. Thêm nút macro vào nhóm tùy chỉnh trên menu Ribbon
9.1. Chọn Tệp > Tùy chọn > Tùy chỉnh Ribbon.
9.2. Trước tiên, hãy đảm bảo rằng hộp Nhà phát triển được chọn trong bảng điều khiển Tab Chính bên dưới Tùy chỉnh Dải băng.
9.3. Chọn một tab cho các macro tùy chỉnh của bạn hoặc chọn/đánh dấu nhóm Phần bổ trợ trong tab Nhà phát triển và nhấp vào nút Nhóm mới.
9.4. Với Nhóm mới đã được chọn (được tô sáng), nhấp vào nút Đổi tên, nút này sẽ mở hộp thoại Đổi tên.
9.5. Hộp trường Tên hiển thị được chọn: Nhập tên cho nhóm này, chẳng hạn như Macro tùy chỉnh.
9.6. Tiếp theo, duyệt qua cửa sổ hộp thoại Biểu tượng và chọn một biểu tượng xác định tốt nhất macro này (chúng tôi đã chọn đồng hồ cát), sau đó nhấp vào OK.
9.7. Trong hộp Chọn Lệnh Từ, nhấp vào mũi tên nhỏ và chọn Macro từ danh sách.
9.8. Trong hộp thoại bên dưới, Excel sẽ hiển thị danh sách tất cả các macro trong sổ làm việc này.
9.9. Chọn macro SumTotals, sau đó nhấp vào nút Thêm để di chuyển macro sang nhóm mới của bạn có tên là Macro tùy chỉnh.
9.10. Nếu muốn, hãy nhấp lại vào nút Đổi tên và chọn một biểu tượng từ cửa sổ hộp thoại Ký hiệu (chúng tôi đã chọn lại máy tính, giống như nút trên Thanh công cụ truy cập nhanh.
9.11. Nhấn OK hai lần để đóng màn hình Tùy chỉnh.
10. Cảnh báo bảo mật Macro
Khi bạn mở sổ làm việc có chứa macro, bạn sẽ nhận được thông báo sau:
“Cảnh báo bảo mật: Macro đã bị tắt”, theo sau là nút có nội dung Bật nội dung.
Nếu bạn muốn sử dụng macro của mình, bạn phải nhấp vào nút này