Sử Dụng Công Thức Mảng Trong VBA Excel

Excel là công cụ mạnh mẽ cho phép xử lý dữ liệu với nhiều hàm và công thức khác nhau. Tuy nhiên, trong một số trường hợp, công thức thông thường không đủ đáp ứng nhu cầu, đòi hỏi phải sử dụng công thức mảng. Nếu bạn đã quen thuộc với công thức mảng trong Excel, bài viết này sẽ hướng dẫn cách áp dụng chúng trong VBA, giúp tự động hóa và tối ưu hóa việc xử lý dữ liệu.

Trong bài viết này, bạn sẽ tìm hiểu cách sử dụng VBA để điền công thức mảng vào Excel, giúp bạn tiết kiệm thời gian và công sức khi làm việc với lượng dữ liệu lớn. Chúng ta sẽ cùng tìm hiểu qua ví dụ thực tế và hai phương pháp tiếp cận khác nhau.

Công Thức Mảng Trong Excel: Tổng Quan

Công thức mảng là công thức đặc biệt, thực hiện tính toán trên một hoặc nhiều mảng dữ liệu và trả về một giá trị hoặc một mảng giá trị. Để hiểu rõ hơn về công thức mảng, bạn có thể tham khảo các bài viết sau:

• Hướng dẫn sử dụng công thức mảng trong Excel
• Các hàm, công thức và hằng số mảng trong Excel

VBA và Công Thức Mảng: Ví Dụ Thực Tế

Giả sử chúng ta có bảng dữ liệu doanh thu bán hàng theo từng ngày và loại hàng hóa. Yêu cầu là tạo bảng tổng hợp doanh thu theo từng tháng.

Dữ liệu doanh thu bán hàng

Có nhiều cách để thực hiện yêu cầu này, chẳng hạn như sử dụng PivotTable, tạo cột phụ chứa tháng rồi dùng hàm SUMIF hoặc SUMIFS. Nếu bạn chưa rõ về cách sử dụng SUMIF và SUMIFS, có thể xem bài viết: Cách sử dụng hàm SUMIFS và SUMIF với nhiều điều kiện.

Trong trường hợp này, chúng ta sẽ sử dụng công thức mảng sau:

=SUM((MONTH($A$6:$A$21)=E4)*($C$6:$C$21))

Để hiểu rõ cách thức hoạt động của công thức này, bạn có thể sử dụng tính năng Evaluate Formula trong nhóm Formula Auditing (tab Formulas).

Áp Dụng Công Thức Mảng Trong VBA: Hai Cách Tiếp Cận

Dưới đây là hai cách sử dụng VBA để điền công thức mảng vào Excel:

Cách 1: Gán Công Thức và FillDown

1. Gán công thức mảng vào ô F4 bằng thuộc tính Range("F4").FormulaArray.
2. Sử dụng FillDown để sao chép công thức xuống các ô còn lại.

Lưu ý: Không nên gán công thức mảng trực tiếp cho cả vùng F4:F6 bằng FormulaArray vì nó sẽ dẫn đến tất cả các ô đều có cùng một giá trị. Việc sử dụng FillDown đảm bảo ô E4 sẽ thay đổi tương ứng với từng tháng.

Cách 2: Gán Công Thức Cho Cả Vùng và Chuyển Đổi

1. Gán công thức cho vùng F4:F6 bằng thuộc tính Range("F4:F6").Formula.
2. Chuyển đổi công thức thành công thức mảng bằng dòng code: Range("F4:F6").FormulaArray = Range("F4:F6").FormulaR1C1.

Ví dụ VBA

Tối Ưu Hóa Hiệu Suất

Với dữ liệu lớn, công thức mảng có thể làm chậm file Excel. Để tối ưu hiệu suất, sau khi tính toán xong, bạn có thể lấy giá trị kết quả và gán lại cho vùng đó bằng dòng code: Range("F4:F6").Value = Range("F4:F6").Value.

Nâng Cao Kỹ Năng Excel và VBA

Để nâng cao kiến thức về Excel và VBA, bạn có thể tham khảo các khóa học sau:

• VBA101 – Tự động hóa Excel với lập trình VBA cho người mới bắt đầu
• VBA201 – Lập trình VBA nâng cao trong Excel

Hy vọng bài viết này giúp bạn hiểu rõ hơn về cách sử dụng công thức mảng trong VBA Excel. Việc áp dụng VBA kết hợp với công thức mảng sẽ giúp bạn tự động hóa các tác vụ phức tạp, tiết kiệm thời gian và nâng cao hiệu suất làm việc.