Các macro hữu ích của Excel. Macro trong Excel - Hướng dẫn sử dụng. Ví dụ về macro Excel

Chào buổi chiều

Tôi muốn dành bài viết này cho một phần lớn của MS Excel như macro, hay nói đúng hơn là hãy bắt đầu lại từ đầu và xem cách tạo macro trong Excel, nó dùng để làm gì và cách sử dụng nó trong công việc của bạn.

Như bạn đã biết từ trải nghiệm riêng, khi làm việc với macro, có rất nhiều “quy trình”, tức là các thao tác và hành động giống nhau được thực hiện cần thiết để thu được kết quả, việc này có thể là điền cùng loại bảng hoặc biểu mẫu, xử lý dữ liệu, tương tự báo cáo hàng tuần, hàng tháng, v.v. Nhưng việc sử dụng macro sẽ cho phép bạn thực hiện những hành động này một cách tự động, sử dụng tối đa các khả năng của Excel, chuyển những thao tác thông thường và đơn điệu này lên đôi vai mạnh mẽ của Excel. Ngoài ra, lý do sử dụng macro có thể được thêm vào những cơ hội cần thiết, chưa được triển khai trong tiêu chuẩn Hàm Excel(ví dụ: xuất, thu thập dữ liệu trên một trang tính, v.v.).

Nếu bạn chưa bao giờ nghe nói về macro thì định nghĩa chính xác nhất về nó sẽ là: đây là những hành động được lập trình để một trình tự nhất định và được viết trong môi trường lập trình bằng ngôn ngữ Ngôn ngữ lập trình dành cho ứng dụng (VBA). Việc chạy macro có thể được thực hiện nhiều lần và điều này sẽ buộc Excel thực hiện bất kỳ chuỗi hành động nào chúng ta cần mà chúng ta không thích hoặc không muốn thực hiện theo cách thủ công. Mặc dù có rất nhiều ngôn ngữ lập trình cho toàn bộ khu phức hợp Microsoft Office VBA là tiêu chuẩn và nó hoạt động trong mọi ứng dụng văn phòng.

Như vậy có 2 cách tạo macro trong Excel:

Tạo macro trong Excelsử dụng máy ghi macro

Trước tiên, hãy làm rõ máy ghi macro là gì và macro có liên quan gì đến nó.

Máy ghi macro là một chương trình nhỏ được tích hợp trong Excel để diễn giải mọi hành động của người dùng bằng mã ngôn ngữ lập trình VBA và ghi vào mô-đun phần mềm các lệnh nhận được trong quá trình làm việc. Nghĩa là, nếu chúng ta tạo báo cáo hàng ngày mà chúng ta cần khi bật trình ghi macro, trình ghi macro sẽ ghi lại mọi thứ trong lệnh của nó theo từng bước và do đó, tạo một macro sẽ tự động tạo báo cáo hàng ngày.

Phương pháp này rất hữu ích cho những ai chưa có kỹ năng và kiến ​​thức làm việc trong môi trường ngôn ngữ VBA. Nhưng việc dễ dàng thực thi và ghi lại macro như vậy cũng có những nhược điểm cũng như những ưu điểm của nó:

• Trình ghi macro chỉ có thể ghi lại những gì nó có thể chạm vào, có nghĩa là nó chỉ có thể ghi lại các hành động khi các nút, biểu tượng, lệnh menu và mọi thứ tương tự được sử dụng; những tùy chọn như vậy không có sẵn cho nó;
• Nếu xảy ra lỗi trong quá trình ghi, lỗi đó cũng sẽ được ghi lại. Nhưng bạn có thể sử dụng nút hủy hành động cuối cùng, xóa lệnh cuối cùng mà bạn viết sai trong VBA;
• Việc ghi vào trình ghi macro chỉ được thực hiện trong phạm vi cửa sổ MS Excel và nếu bạn đóng chương trình hoặc bật chương trình khác, quá trình ghi sẽ bị dừng và không còn được thực hiện nữa.

Để bật trình ghi macro để ghi, bạn phải thực hiện các bước sau:

Bước tiếp theo khi làm việc với trình ghi macro sẽ là định cấu hình các tham số của nó để ghi thêm macro; việc này có thể được thực hiện trong cửa sổ "Ghi macro", Ở đâu:

• Trường tên macro- bạn có thể viết tên mà bạn hiểu bằng bất kỳ ngôn ngữ nào, nhưng tên đó phải bắt đầu bằng một chữ cái và không chứa dấu chấm câu hoặc dấu cách;
• Trường phím tắt- sẽ được bạn sử dụng trong tương lai cho bắt đầu nhanh vĩ mô của bạn. Trong trường hợp bạn cần đăng ký mới, tùy chọn này sẽ có sẵn trong menu “Công cụ” - “Macro” - “Macro” - “Chạy” hoặc trên tab "Nhà phát triển" nhấn một nút "Macro";
• trường "Lưu vào..."— bạn có thể chỉ định vị trí lưu văn bản macro (nhưng không được gửi) và có 3 tùy chọn:
  • "Cuốn sách này"— macro sẽ được ghi vào mô-đun của sổ làm việc hiện tại và chỉ có thể được thực thi nếu Cuốn sách này Excel sẽ mở ra;
  • "Một cuốn sách mới"- macro sẽ được lưu trong mẫu trên cơ sở mẫu trống được tạo trong Excel Một quyển sách mới, điều đó có nghĩa là macro sẽ có sẵn trong tất cả các sổ làm việc sẽ được tạo trên máy tính này kể từ bây giờ;
  • "Sách Macro Cá Nhân"- là một cuốn sách đặc biệt Macro Excel, được gọi là "Cá nhân.xls" và được sử dụng như một thư viện lưu trữ macro đặc biệt. Khi khởi động, macro từ sổ làm việc “Personal.xls” được tải vào bộ nhớ và có thể khởi chạy trong bất kỳ sổ làm việc nào vào bất kỳ lúc nào.
• Trường "Mô tả"- ở đây bạn có thể mô tả macro nên làm gì và như thế nào, tại sao nó được tạo và nó có những chức năng gì, đây là một trường thông tin thuần túy, được gọi là bộ nhớ.

Khi bạn đã chạy và ghi lại macro của mình, hãy hoàn thành tất cả hành động cần thiết, có thể dừng ghi âm bằng lệnh "Dừng ghi âm" và macro của bạn sẽ được tạo bằng trình ghi macro.

Tạo macro trong Exceltrong trình soạn thảo Visual Basic.

Trong phương pháp này, chúng ta sẽ xem cách tạo macro trong Excel thông qua trình soạn thảo chương trình VBA, như tôi đã nói ở trên, được tích hợp vào bất kỳ phiên bản Excel nào.

Việc khởi chạy trình soạn thảo chương trình VBA diễn ra khác nhau, tùy thuộc vào phiên bản chương trình Excel của bạn:

• trong các phiên bản Excel 2003 trở lên, chúng ta cần trong menu "Dịch vụ", chọn mục "Vĩ mô" và hãy nhấn "Trình soạn thảo Visual Basic";
• trong các phiên bản Excel 2007 trở lên, chúng tôi cần trên tab "Nhà phát triển" Nhấn nút "Trình soạn thảo Visual Basic". Nếu không tìm thấy tab này, bạn cần kích hoạt nó bằng cách chọn mục menu “Tệp” - “Tùy chọn” - “Tùy chỉnh Ribbon” và trong hộp thoại, hãy sử dụng hộp kiểm để kích hoạt tab "Nhà phát triển".

Trong cửa sổ mở ra, bạn có thể thấy giao diện của trình soạn thảo VBA, mọi thứ ở đây đều được viết bằng tiếng Anh và không có bản địa hóa, bạn không cần phải tìm mà chỉ cần chấp nhận và làm việc, đặc biệt là vì nó chỉ khó hiểu lúc đầu, sau đó mọi thứ sẽ quen thuộc.

Vậy, cách làm việc trong trình soạn thảo VBA, những gì được lưu trữ ở đâu và cách tạo macro trong Excel. Những câu hỏi thuộc loại này nảy sinh ngay lập tức khi bạn gặp người biên tập và bây giờ chúng ta sẽ xem xét tất cả chúng.

Tất cả các macro của chúng tôi sẽ được lưu trữ trong cái gọi là module phần mềm. Trong bất kỳ cuốn sách nào của bất kỳ phiên bản Excel nào, chúng ta có thể tạo bất kỳ module phần mềm với số lượng bất kỳ và đặt tất cả các macro chúng tôi tạo vào đó. Một mô-đun có thể chứa bất kỳ số lượng macro nào bạn cần hoặc tạo. Các mô-đun có sẵn trong cửa sổ "Thám hiểm dự án" và nằm ở góc trên bên trái của trình soạn thảo macro (bạn cũng có thể gọi nó bằng tổ hợp phím CTRL+R).

Các mô-đun chương trình trong trình soạn thảo VBA tồn tại ở một số loại và được sử dụng cho các tùy chọn và tình huống khác nhau:

Trên thực tế, bản thân macro khi làm việc trong một mô-đun tiêu chuẩn sẽ trông như thế này:

Hãy xem một ví dụ về macro đang hoạt động:

• Tất cả các macro trong bắt buộc sẽ bắt đầu với toán tử phụ, theo sau là tên macro của bạn và danh sách các đối số trong ngoặc đơn. Trong trường hợp không có đối số thì để trống dấu ngoặc đơn;
• Tất cả các macro phải kết thúc bằng toán tử Kết thúc phụ;
• Dữ liệu giữa các nhà khai thác phụ Và Kết thúc phụ, là phần thân macro sẽ chạy khi macro được chạy. Trong ví dụ này, macro sẽ kiểm tra và khi nhập dữ liệu, nó sẽ tìm thấy nó trong danh sách cơ sở dữ liệu và hiển thị giá trị được chỉ định theo tiêu chí.

Như bạn có thể thấy, phương pháp thứ hai khó sử dụng và khó hiểu hơn nếu bạn không có kinh nghiệm về lập trình nói chung hoặc VBA nói riêng. Rất khó để hiểu và tìm ra những lệnh nào được nhập và cách nhập, những đối số nào được sử dụng để macro bắt đầu thực hiện công việc của nó một cách tự động. Nhưng người đi sẽ làm chủ con đường, như các nhà hiền triết xưa đã nói, và do đó, người ta không nên bỏ cuộc mà hãy làm theo lời dặn của ông nội Lênin...

Tạo nút chạy macro trên thanh công cụ

Như tôi đã nói trước đó bạn có thể gọi một thủ tục macro sự kết hợp nóng bỏng các phím, nhưng thật tẻ nhạt khi nhớ tổ hợp nào được gán cho ai, vì vậy cách tốt nhất của bạn là tạo một nút để chạy macro. Các nút có thể được tạo thành nhiều loại, cụ thể là:

Phương pháp này có sẵn cho mọi phiên bản MS Excel và bao gồm thực tế là chúng tôi sẽ đặt nút trực tiếp trên bảng tính của mình dưới dạng đối tượng đồ họa. Để làm điều này bạn cần:

• Trong MS Excel 2003 trở lênđi tới thực đơn "Xem", chọn "Thanh công cụ" và nhấn nút "Các hình thức".
• Trong MS Excel 2007 trở lên bạn cần trên tab "Nhà phát triển" mở menu thả xuống "Chèn" và chọn một đối tượng "Cái nút".

Sau tất cả những điều này, bạn phải vẽ một nút trên trang tính của mình trong khi giữ nút chuột trái. Sau khi quá trình vẽ hoàn tất, một cửa sổ sẽ tự động mở nơi bạn cần chọn macro cần thực thi khi nhấp vào nút của mình.

Cách tạo hàm tùy chỉnh trong VBA

Về nguyên tắc, việc tạo cái gọi là hàm do người dùng xác định không khác lắm so với việc tạo macro thông thường trong mô-đun chương trình tiêu chuẩn. Sự khác biệt giữa các khái niệm này là macro sẽ thực hiện các hành động có trong nó với các đối tượng sách hoặc trang tính (đây là các công thức, ô, v.v.), nhưng hàm người dùng chỉ hoạt động với các giá trị mà nó nhận được từ chúng ta và đây là những đối số dữ liệu ban đầu để tính toán.

Ví dụ, để tạo chức năng tùy chỉnh thuế giá trị gia tăng, hay còn gọi là VAT, chúng ta cần mở trình soạn thảo VBA và thêm mô-đun mới, chọn từ trình đơn "Chèn"đoạn văn "Mô-đun" và nhập văn bản cho chức năng của chúng tôi ở đó: Cần lưu ý rằng sự khác biệt chính giữa hàm và macro là tiêu đề Chức năng thay thế phụ và có một danh sách đầy đủ các đối số, trong ví dụ của chúng tôi đây là tổng hợp. Khi mã của chúng tôi đã được nhập, nó sẽ có sẵn trong cửa sổ tiêu chuẩn Trình hướng dẫn chức năng, nằm trong menu "Công thức", đoạn văn "Chèn chức năng".
Và chọn một danh mục « Đã xác định người dùng» trong đó chức năng viết của chúng tôi sẽ được hiển thị "NDS".
Sau khi chọn hàm của chúng tôi, bạn có thể đặt con trỏ lên ô có đối số, đối số này sẽ chứa số tiền mà chúng tôi tính VAT, mọi thứ diễn ra như với một hàm thông thường.
Và đó là tất cả đối với tôi! Tôi thực sự hy vọng rằng bài viết hướng dẫn tạo macro trong Excel rõ ràng và hữu ích cho bạn. Tôi sẽ rất biết ơn những nhận xét của bạn, vì đây là dấu hiệu cho thấy khả năng đọc và truyền cảm hứng cho tôi viết bài mới! Chia sẻ những gì bạn đọc với bạn bè và thích nó!

Excel có khả năng mạnh mẽ nhưng hiếm khi được sử dụng để tạo các chuỗi hành động tự động bằng cách sử dụng macro. Macro là giải pháp lý tưởng nếu bạn đang xử lý cùng một loại tác 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 bằng mẫu được chuẩn hóa. Đồng thời, 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ếp tục - sau đó chúng tôi sẽ cùng bạn thực hiện toàn bộ quá trình tạo macro từng bước.

Macro là gì?

Một macro trong Microsoft Office (vâng, chức năng này hoạt động giống nhau trong nhiều ứng dụng trong bộ Microsoft Office) là Mã chương trình bằng ngôn ngữ lập trình (VBA), được lưu 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 trang HTML, thì 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 trên 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 Văn phòng.

Macro có thể thực hiện hầu hết mọi hành động mà bạn có thể muốn trong tài liệu. Dưới đây là một số trong số đó (một phần rất nhỏ):

• Áp dụng kiểu và định dạng.
• Thực hiện hoạt động khác nhau với dữ liệu số và văn bản.
• Sử dụng nguồn lực bên ngoài dữ liệu (tệp cơ sở dữ liệu, tài liệu văn bản vân vân.)
• Tạo một tài liệu mới.
• Thực hiện tất cả các bước trên theo bất kỳ sự kết hợp nào.

Tạo Macro - Ví dụ thực tế

Ví dụ, chúng ta hãy tận dụng tối đa tập tin thông thường CSV. Cái này bảng đơn giản 10x20, chứa đầy các số từ 0 đến 100 với tiêu đề cho 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 có thể trình bày được 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 chương trình mà không cần viết dòng mã nào, đó là điều chúng ta sẽ làm ngay bây giờ.

Để tạo macro, hãy mở Xem(Xem) > Macro(Macro) > Ghi macro(Ghi macro...)

Đặt tên cho macro của bạn (không có dấu cách) và nhấp vào ĐƯỢC RỒI.

Kể từ thời điểm này, TẤT CẢ hành động 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. Trước hết, trên menu Macro(Macro) - thay vì một dòng Ghi macro dòng (Ghi macro...) xuất hiện Dừng ghi(Dừng ghi âm).

Thứ hai, ở góc dưới bên trái cửa sổ Excel. Biểu tượng Dừng lại(hình vuông nhỏ) cho biết chế độ ghi macro đã được bật. Nhấp vào nó sẽ ngừng ghi. Ngược lại, khi chưa bật chế độ ghi, tại đây sẽ có biểu tượng kích hoạt ghi macro. Nhấp vào nó sẽ cho kết quả tương tự như cho phép ghi qua menu.

Bây giờ chế độ ghi macro đã được bật, hãy bắt tay vào nhiệm vụ của chúng ta. Trước hết, hãy thêm tiêu đề cho dữ liệu tóm tắt.

• =SUM(B2:K2) hoặc =SUM(B2:K2)
• =TRUNG BÌNH(B2:K2) hoặc =TRUNG BÌNH(B2:K2)
• =MIN(B2:K2) hoặc =MIN(B2:K2)
• =MAX(B2:K2) hoặc =MAX(B2:K2)
• = TRUNG BÌNH(B2:K2) hoặc = TRUNG BÌNH(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 tôi bằng cách kéo tay cầm tự động điền.

Sau khi hoàn thành bước này, tổng số tương ứng sẽ xuất hiện ở mỗi hàng.

Tương ứng:

• =SUM(L2:L21) hoặc =SUM(L2:L21)
• =TRUNG BÌNH(B2:K21) hoặc =TRUNG BÌNH(B2:K21)– để tính giá trị này cần lấy chính xác dữ liệu bảng gốc. Nếu bạn lấy giá trị trung bình của các giá trị trung bình cho từng hàng riêng lẻ thì kết quả sẽ khác.
• =MIN(N2:N21) hoặc =MIN(N2:N21)
• =MAX(O2:O21) hoặc =MAX(O2:O21)
• = TRUNG BÌNH(B2:K21) hoặc = TRUNG BÌNH(B2:K21)– chúng tôi tính toán bằng cách sử dụng dữ liệu bảng gốc vì lý do đã nêu ở trên.

Bây giờ chúng ta đã hoàn tất việc tính toán, hãy chuyển sang phần định dạng. Đầu tiên chúng ta hãy thiết lập định dạng hiển thị dữ liệu giống nhau cho tất cả các ô. Chọn tất cả các ô trên trang tính bằng phím tắt Ctrl+A, hoặc bấm 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ấn vào Kiểu dấu phẩy Tab (Định dạng được phân cách) Trang chủ(Trang chủ).

• Kiểu chữ đậm.
• Căn chỉnh trung tâm.
• Đổ đầy màu sắc.

Và cuối cùng, hãy thiết lập định dạng của tổng giá trị.

Cuối cùng nó sẽ trông như thế này:

Nếu bạn hài lòng với mọi thứ, hãy dừng ghi macro.

Chúc mừng! Bạn vừa tự mình ghi lại macro Excel đầu tiên của mình.

Để sử dụng macro đã tạo, chúng ta cần lưu tài liệu excelở định dạng hỗ trợ macro. Trước 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à. biến nó 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 mới nhất và phù hợp nhất vào đó.

Để xóa tất cả các ô dữ liệu, hãy nhấp vào click chuột phải chuột theo 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 mục 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 tất cả dữ liệu, trong khi macro vẫn được ghi lại. Chúng ta cần lưu cuốn sách dưới dạng Mẫu Excel kích hoạt macro, có phần mở rộng XLTM.

Tâm điểm! Nếu bạn lưu tệp có 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.

Sau khi lưu mẫ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ĩ bạn nên chú ý đến một số điểm quan trọng về 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 thay đổi bất kỳ tệp nào trong một thư mục Tai liệu của tôi. Vì lý do này, chỉ chạy và cho phép macro từ các nguồn bạn tin cậy.

Để 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 tiêu chuẩn security, thì khi bạn mở một tệp, một cảnh báo sẽ xuất hiện ở đầu bảng cho biết rằng macro đang chạy bị tắt và một nút để kích hoạt việc thực thi chúng. Vì chúng tôi tự tạo mẫu và tin tưởng vào chính mình nên chúng tôi nhấn nút Bật nội dung(Gồm nội dung).

Bước tiếp theo là nhập tập dữ liệu cập nhật mới nhất từ ​​tệp CSV(chúng tôi đã tạo macro của mình dựa trên một tệp như vậy).

Khi bạn nhập dữ liệu từ tệp CSV, Excel có thể yêu cầu bạn định cấu hình một số cài đặt để truyền đúng dữ liệu vào một bảng.

Khi quá trình nhập hoàn tất, hãy chuyển đến menu Macro tab (Macro) Xem(Xem) và chọn lệnh Xem macro(Macro).

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(Chạy).

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 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ẽ trông giống như bảng gốc mà chúng ta đã định dạng thủ công, chỉ khác dữ liệu trong các ô.

Chúng ta hãy xem xét kỹ hơn: Macro hoạt động như thế nào?

Như đã được đề cập nhiều lần, macro là mã chương trình trong ngôn ngữ lập trình Visual Basic cho ứ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 trên ngôn ngữ VBA. Nói một cách đơn giản, Excel viết mã cho bạn.

Để xem mã chương trình này, bạn cần vào menu Macro tab (Macro) Xem(Xem) bấm vào Xem macro(Macro) và trong hộp thoại mở ra, hãy nhấp vào Biên tập(Thay đổi).

Một cửa sổ sẽ mở ra Visual Basic cho ứng dụng, trong đó chúng ta sẽ thấy mã chương trình của macro mà chúng ta đã ghi. Có, bạn đã hiểu chính xác, tại đây bạn có thể thay đổi mã này và thậm chí tạo macro mới. Các hành động mà chúng ta đã thực hiện với bảng trong bài học này có thể được viết ra bằng cách sử dụng ghi âm tự động macro trong Excel. Nhưng các macro phức tạp hơn, với trình tự hành động và logic đượ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 tin gốc với dữ liệu dữ liệu.csvđược tạo tự động bởi một số quy trình và luôn được lưu trên đĩa ở cùng một nơi. Ví dụ, C:\Dữ liệu\data.csv– đường dẫn tới file chứa dữ liệu đã cập nhật. Quá trình mở tệp này và nhập dữ liệu từ nó cũng có thể được ghi lại trong macro:

1. Mở tệp mẫu mà chúng tôi đã lưu macro - Định dạngDữ liệu.
2. Tạo một macro mới có tên Tải dữ liệu.
3. Trong khi ghi macro Tải dữ liệu nhập dữ liệu từ tập tin dữ liệu.csv- như chúng ta đã làm ở phần trước của bài học.
4. Khi quá trình nhập hoàn tất, hãy dừng ghi macro.
5. Xóa tất cả dữ liệu khỏi ô.
6. Lưu tệp dưới dạng mẫu Excel có 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 còn lại định dạng dữ liệu.

Nếu bạn muốn bắt đầu lập trình, bạn có thể kết hợp hành động của hai macro này thành một - chỉ bằng cách sao chép mã từ Tải dữ liệuđến đầu mã Định dạngDữ liệu.

Bảng Excel là một trong những cách phổ biến nhất để lưu trữ và sắp xếp thông tin. Nếu bạn đưa macro vào Excel, khả năng của chương trình có thể tăng gần gấp đôi. Ví dụ: macro giúp tự động hóa quy trình thường lệ và đẩy nhanh công việc đơn điệu. Chúng thường được sử dụng bởi các chuyên gia dịch báo cáo từ 1C sang Excel. Với sự trợ giúp của mã nhúng, chúng có thể dễ dàng được rút gọn thành một mẫu tiêu chuẩn duy nhất.

Macro Excel là gì?

Macro không chỉ được sử dụng trong bảng mà còn được sử dụng trong toàn bộ Microsoft Office. Chúng là mã được tạo bằng Visual Basic for Application. Nếu bạn đã quen một phần với lập trình web, thì bạn có thể rút ra sự tương tự với Javascript. Các macro Excel thực hiện gần giống như Javascript thực hiện với dữ liệu HTML.

Một phần nhỏ những gì macro có thể làm trong các ứng dụng văn phòng:

Căn chỉnh tài liệu theo phong cách và định dạng nó;
. thực hiện các thao tác với dữ liệu số và văn bản;
. tìm kiếm sự giúp đỡ tập tin bên ngoài(văn bản, cơ sở dữ liệu và những thứ khác);
. tạo tài liệu mới từ đầu;
. kết hợp một số hành động và thực hiện chúng một cách tự động.

Để làm việc với bảng, bạn có thể tự tạo hoặc tải xuống tùy chọn làm sẵn từ trên mạng. Macro chỉ nên được tải xuống từ các trang web đáng tin cậy, nếu không bạn có thể đưa vi-rút vào Excel.

Macro trong Excel có thể nguy hiểm như thế nào?

Làm hỏng các tập tin trên PC của bạn;
. ăn cắp dữ liệu bí mật.

Virus được đưa vào trong quá trình hoạt động của một trong những Ứng dụng văn phòng- Word hoặc Excel. Sau khi bạn ngừng làm việc, họ sẽ bắt đầu hoạt động tự chủ virus và lây nhiễm vào toàn bộ hệ thống.

Một cách khác mà macro độc hại hoạt động là làm trung gian đưa các mối đe dọa vào hệ thống. Trong trường hợp này, nó là một loại cửa ngõ để giới thiệu và tạo ra phần mềm Trojan. Nó sẽ không còn được kiểm soát bởi macro đã sửa đổi nữa mà bởi hệ điều hành, và do đó mối đe dọa trở nên lớn hơn. Không phải lúc nào cũng có thể chữa khỏi những loại virus như vậy, đôi khi bạn phải thay đổi hoàn toàn hệ điều hành Windows hoặc ổ cứng PC (tùy theo nhiệm vụ mà hacker đặt ra khi viết mã).

Bạn có thể chọn bất kỳ phương pháp bảo vệ nào chống lại các macro như vậy. Tùy chọn phổ biến nhất là tăng mức độ bảo mật của Excel, nhưng trong trường hợp này chương trình có thể không còn hỗ trợ các macro hữu ích ban đầu của bạn nữa. Bạn không nên tin cậy các tập tin từ không biết nguồn gốc, vì họ có thể bị nhiễm bệnh. Ngay cả macro nhận được từ PC của đồng nghiệp của bạn cũng có thể nguy hiểm. Nếu một người bạn đưa cho bạn một tệp để làm việc hỗ trợ việc sử dụng macro, thì trước khi cho phép hệ thống sử dụng nó, hãy làm rõ cách anh ta nhận được macro này.

Tùy chọn tốt nhất là tự mình tìm hiểu nghệ thuật tạo macro cho nhu cầu của bạn. Trong trường hợp này, bạn hoàn toàn có thể đảm bảo về chất lượng và độ an toàn của chúng.

Cách ghi macro trong Excel

Cách dễ nhất để tạo macro là lập trình trực tiếp trong Excel. Để làm điều này, bạn sẽ cần một bảng chứa dữ liệu đã nhập và một chút kiến ​​​​thức để có thể ghi mã đó vào chương trình.

Quá trình tạo bắt đầu bằng menu Xem. Trong đó, bạn cần chọn vị trí “Macro” và lệnh “Ghi macro”. Tiếp theo, bạn cần chỉ định tên không có khoảng trắng, ví dụ: “Định dạng” (nếu mã sẽ chịu trách nhiệm thay đổi định dạng của văn bản hoặc ô).

Sau đó mọi hành động của bạn sẽ được tự động ghi lại và chuyển thành mã macro. Một nút có hình vuông nhỏ (“Dừng”) sẽ xuất hiện ở cuối tài liệu, bằng cách nhấp vào đó bạn có thể dừng ghi. Bằng cách này, bạn có thể tự động ghi lại các macro trong Excel, các ví dụ mà bạn có thể phân tích sâu hơn trong quá trình học.

Cách bật macro trong Excel

Để kích hoạt macro đã ghi trong các tài liệu khác, bạn phải lưu nó sau khi xóa bảng. Định dạng tài liệu phải được đặt là XLTM (đây là định dạng hỗ trợ macro).

Sau này, khi bạn truy cập tài liệu của mình để bật macro trong Excel, trước tiên bạn phải bật chúng trong cửa sổ bật lên dòng trên cùng. Sau đó, bạn cần nhập dữ liệu sẽ được macro chỉnh sửa từ bảng CSV thông thường. Sau khi quá trình nhập hoàn tất, bạn cần quay lại menu “Chế độ xem”, chọn “Macro” ở đó và tìm tên mã bạn đã tạo trong danh sách. Cuối cùng, nhấp vào nút “Chạy”. Sau đó, bạn sẽ thấy chương trình, theo thuật toán bạn đã chỉ định trước đó, thực hiện các phép tính và định dạng tài liệu như thế nào.

Làm cách nào để xem mã macro?

Tất cả các macro tạo trong Excel đều được viết tự động, không cần sự can thiệp của con người. Chương trình tự soạn mã chương trình, mã này có thể được mở từ bảng bằng cách gọi nó bằng menu.

Để làm điều này, bạn cần chạy các lệnh tuần tự:

Mở rộng tab “Xem”;
. mở "Macro";
. chọn tên của macro cần thiết;
. Gọi một cửa sổ có mã của nó bằng nút “Thay đổi”.

Trong hộp thoại mới, bạn có thể chỉnh sửa và thay đổi macro. Bạn sẽ thấy ở đó mã mà chương trình đã ghi lại tự động và bạn có thể thêm bất kỳ hành động nào theo cách thủ công. Sau khi lưu thông tin, macro sẽ hoạt động khác khi nhập dữ liệu mới.

Cách bắt đầu macro bằng cách nhấp vào một ô

Giả sử bạn muốn macro chạy khi ô A1 được chọn. Để ô được chọn trong Excel chạy macro, bạn cần thêm mã chương trình vào mô-đun đầu tiên:

Bảng tính phụ riêng tư_SelectionChange(ByVal Target As Range)
Nếu Target.Address = "$A$1" thì: Gọi module1
Kết thúc phụ

Cách tốt nhất để học macro là hướng dẫn excel. Có nhiều hơn một ví dụ tương tự, và đối với những người mới làm quen với tất cả các khả năng của bảng thì đây là Cách tốt nhất học cách sử dụng đầy đủ chức năng của nó.

Bạn có thể học cách làm việc với macro ở đâu?

Có một số cách để tìm hiểu cách tạo các macro phức tạp hơn. Chúng phải được phân biệt bằng logic và một chuỗi hành động được xác định rõ ràng. Các macro như vậy chỉ có thể được tạo bằng cách viết mã VBA theo cách thủ công. Để tạo các macro phức tạp, bạn sẽ cần dành chút thời gian để tìm hiểu sự phức tạp của ngôn ngữ Lập trình trực quan Cơ bản.

Bây giờ cùng khám phá nhé mọi người Khả năng của Excel Nhiều khóa học đặc biệt đã được tạo ra kéo dài trong vài tuần, nhưng để tham dự chúng, bạn sẽ phải đến trung tâm đào tạo hoặc tham dự hội thảo trên web (điều này không phải lúc nào cũng thuận tiện do chênh lệch múi giờ). Nhưng cách hiệu quả và đơn giản hơn để học cách tạo macro trong Excel là hướng dẫn kèm theo các ví dụ. Bạn có thể học nó bất cứ lúc nào thuận tiện cho bạn và sách giáo khoa từng bước sẽ giúp bạn phát triển các biến thể khác nhau: từ nhiều nhất mã đơn giảnđến phức tạp nhất. Microsoft Excel là một trình soạn thảo bảng tính phổ quát có thể được sử dụng để giải quyết nhiều vấn đề phức tạp.

Ví dụ

Có những mã cho phép bạn đạt được tự động hóa Các nhiệm vụ khác nhau. Vì vậy, để làm ví dụ, hãy xem macro chèn hàng. Nó trông như thế này:

Macro phụ1()

VBA được coi là ngôn ngữ kịch bản tiêu chuẩn cho các ứng dụng của Microsoft và hiện được đưa vào tất cả Ứng dụng văn phòng và thậm chí cả các ứng dụng từ các công ty khác. Vì vậy, khi đã thành thạo VBA cho Excel, bạn có thể chuyển ngay sang tạo macro cho phần mềm khác. Sản phẩm của Microsoft. Hơn nữa, bạn sẽ có thể tạo đầy đủ sản phẩm phần mềm, đồng thời sử dụng các chức năng của nhiều ứng dụng khác nhau.

Cách bật macro trong Excel

Theo mặc định, tab chịu trách nhiệm quản lý và điều hướng macro trong Excel bị ẩn. Để kích hoạt tùy chọn nàyđi tới tab Tài liệuđến nhóm Tùy chọn. Trong hộp thoại xuất hiện Tùy chọnExcelđi tới tab Tùy chỉnh ruy băng, trong hộp tổ hợp bên phải, đặt điểm đánh dấu đối diện với tab Nhà phát triển. Những hành động này có liên quan đến Các phiên bản Excel 2010 trở lên.

Xuất hiện trên băng phần mới Nhà phát triển với các điều khiển tự động hóa Excel.

Viết macro trong Excel

Trong tab Nhà phát triển trong nhóm Mã số, nhấn vào nút Ghi lại một macro. Một hộp thoại sẽ xuất hiện Ghi macro, yêu cầu một số thông tin về mã được viết trong tương lai. Nếu đây là lần đầu tiên bạn tạo macro, bạn chỉ cần nhấp vào nút ĐƯỢC RỒI. VỚI tại thời điểm này Excel sẽ ghi lại mọi hành động của người dùng trong mô-đun VBA, có thể là nhập dữ liệu, định dạng hoặc tạo biểu đồ. Để dừng ghi macro, hãy bấm vào nút Dừng ghiđó là trong cùng một nhóm Mã số.

Bạn cũng có thể tận dụng tùy chọn thay thế để ghi macro bằng cách sử dụng nút Ghi macro, nằm ở góc dưới bên trái của sổ làm việc Excel (bên phải trạng thái Sẵn sàng).

Bây giờ bạn có thể xem danh sách tất cả các macro đã tạo bằng cách nhấp vào nút vĩ mô, nằm trong nhóm Mã số. Trong hộp thoại xuất hiện, bạn có thể đặt thêm tên mô tả cho mã của mình hoặc đặt phím tắt để chạy một macro cụ thể. Lựa chọn thay thếđể khởi chạy cửa sổ này là nhấn Phím Alt+ F8.

Chỉnh sửa macro

Chúc mừng! Bạn đã viết macro đầu tiên của mình. Sẽ là hợp lý nếu bây giờ kiểm tra xem Excel đã tạo mã nào cho chúng ta. Mã được tạo được viết bằng VBA (Visual Basic for Application). Bạn phải mở nó ra để xem nó. Biên tập viênVB(VBE),được khởi chạy bằng cách nhấn Alt + F11 hoặc nút Thị giácNền tảng trên tab Nhà phát triển.

Để tránh nhầm lẫn trong trình chỉnh sửa, bạn chỉ có thể làm việc với một tab trong sổ làm việc, trang tính hoặc mô-đun. Đây là diện mạo của biên tập viên ngoài đời thực.

tôi đề nghị trên ở giai đoạn này Tìm hiểu thêm về các cửa sổ và menu khác nhau của trình soạn thảo VBA. Điều này sẽ giúp bạn tiết kiệm được rất nhiều thời gian sau này.

Để xem mã, hãy nhấp vào chủ đề Mô-đun trong cửa sổ dự án và nhấp đúp vào nhánh xuất hiện mô-đun1 . Trình chỉnh sửa sẽ mở một cửa sổ có mã, như trong hình.

Tại đây bạn có thể chỉnh sửa mã được tạo được viết khi làm việc trong Excel. Ví dụ: bạn cần điền vào một cột nhất định với các giá trị từ 1 đến 10. Bạn đã có ba bước đầu tiên, đó là nhập các giá trị 1, 2 và 3 vào ba ô đầu tiên của cột A. Chúng ta cần hoàn thành bảy bước còn lại.

Nếu bạn nhìn vào đoạn mã trên, bạn sẽ thấy macro theo một cách nào đó có cấu trúc. Trước tiên, ứng dụng sẽ di chuyển con trỏ đến ô bằng lệnh Range("A1").Select, sau đó chỉnh sửa nội dung của nó bằng ActiveCell.FormulaR1C1 = "1". Vì vậy đối với các bước còn lại chúng ta có thể lặp lại các bước này, thay đổi địa chỉ ô và giá trị bạn muốn ghi vào ô đó. Ví dụ: để đặt ô A4 thành 4, bạn sẽ viết:

Và lặp lại các bước tương tự cho các giá trị còn lại.

Sau khi chỉnh sửa xong, hãy lưu sách của bạn. Bạn có thể chạy macro bằng cách nhấn nút F5 hoặc bằng cách quay lại chế độ làm việc Sổ làm việc Excel, đi tới tab Nhà phát triểnđến nhóm Mã số -> Macro và chọn từ danh sách macro mà bạn quan tâm.

Hãy dành vài phút để nghiên cứu kỹ mã mà Excel đã tạo. Nếu bạn là người mới bắt đầu, việc đầu tư một vài phút vào việc học mã sẽ mang lại kết quả đáng kinh ngạc trong việc làm quen với các đối tượng VBA sau này. Xin lưu ý rằng ví dụ chúng tôi thảo luận chỉ là một minh họa. Có nhanh hơn và cách hiệu quảđạt được kết quả tương tự mà chúng ta sẽ thảo luận sau.

Tăng tốc độ thực thi macro Excel

Càng xa càng tốt. Hãy xem xét một số thủ thuật sẽ giúp tăng tốc độ thực thi macro. Hãy lấy đoạn mã trên làm ví dụ. Máy tính hiện đại sẽ xử lý mã được đề cập nhanh đến mức bạn thậm chí không nhận thấy nó. Nhưng nếu bạn cần thực hiện thao tác 50.000 lần thì sao? Điều này sẽ mất một thời gian. Nếu macro bạn viết dài hàng trăm dòng, bạn có thể tăng tốc độ thực thi mã bằng cách cắt bớt phần quy trình không được sử dụng trong quá trình thực thi macro.

Sử dụng lệnh Application.ScreenUpdating

Mẹo đầu tiên là tránh cập nhật màn hình trong khi macro đang chạy. Điều này sẽ cho phép Excel lưu khả năng tính toán máy tính và chỉ cập nhật màn hình với các giá trị mới nhất sau khi tất cả mã đã được thực thi. Để thực hiện việc này, bạn cần thêm lệnh tắt làm mới màn hình ở đầu macro và lệnh bật làm mới màn hình ở cuối macro.

Lệnh Application.ScreenUpdating yêu cầu Excel ngừng hiển thị dữ liệu được tính toán lại trên màn hình và trả về các giá trị đã hoàn thành khi kết thúc quá trình thực thi mã.

Sử dụng lệnh Ứng dụng. Phép tính

Thủ thuật thứ hai là tắt tính toán tự động. Hãy để tôi giải thích. Mỗi khi người dùng hoặc quy trình cập nhật một ô, Excel sẽ cố gắng tính toán lại tất cả các ô phụ thuộc vào nó. Vì vậy, giả sử nếu ô mà macro đang cố cập nhật ảnh hưởng đến 10.000 ô khác, Excel sẽ cố gắng tính toán lại tất cả chúng trước khi mã thực thi xong. Theo đó, nếu có toàn bộ dòngảnh hưởng đến các ô, việc tính toán lại có thể làm chậm đáng kể việc thực thi mã. Để ngăn điều này xảy ra, bạn có thể cài đặt lệnh Ứng dụng. Tính toán ở đầu mã, sẽ chuyển việc tính toán lại các công thức sang chế độ thủ công rồi trả về phép tính tự động ở cuối macro.

Hãy cẩn thận, đừng quên chuyển lại tùy chọn này thành chế độ tự độngở cuối macro. Nếu không, bạn sẽ cần thực hiện việc này trong Excel bằng cách nhấp vào tab Công thứcđến nhóm Phép tính và lựa chọn Các tùy chọn tính toán -> Tự động.

Tránh chọn ô và phạm vi

Trong chế độ ghi macro tự động, bạn có thể nhận thấy rằng Excel rất thường xuyên sử dụng lệnh chọn ô, ví dụ: Range("A1").Select. Trong ví dụ của chúng tôi, chúng tôi đã sử dụng lệnh này nhiều lần để chọn một ô và thay đổi giá trị của nó. Bạn có thể tránh điều này bằng cách chỉ định địa chỉ ô và đặt cho nó giá trị được yêu cầu (Macro đã ghi lại chuyển động của con trỏ từ ô này sang ô khác, do đó chèn các bước này. Tuy nhiên, chúng không cần thiết). Vì vậy, một mã hiệu quả hơn sẽ trông như thế này.

TRONG trong trường hợp này chúng tôi chỉ cần tham chiếu ô và cung cấp cho nó giá trị cần thiết mà không cần chọn nó. Phương pháp này nhanh hơn phương pháp trước.

Ví dụ về macro Excel

Dưới đây là một số mã VBA mẫu sẽ giúp bạn tự động hóa các tác vụ phổ biến nhất.