Trong nhiều trường hợp đôi khi công thức bình thường trong Excel không thể giải quyết được mà cần phải sử dụng công thức mảng. Và nếu như bạn đã biết sử dụng công thức mảng trong Excel rồi thì hãy cùng xem cách áp dụng nó vào trong VBA qua bài viết này của Học Excel Online.

Sử dụng VBA để điền công thức mảng

Nhắc lại về công thức mảng

Công thức mảng là một trong những phần nâng cao của Excel, công thức sẽ thực hiện tính toán trên một hoặc nhiều mảng và kết quả trả về là một giá trị hoặc một mảng giá trị. nghe có vẻ hơn rồi phải không nào. Để có thể hiểu kỹ hơn bạn có thể tham khảo một số bài viết về cách sử dụng công thức mảng của Học Excel Online dưới đây

Hướng dẫn cách sử dụng công thức mảng trong excel

Các hàm, công thức và những hằng số mảng trong excel, ví dụ và hướng dẫn sử dụng

Sử dụng VBA điền công thức mảng vào trong Excel

Khi bạn đã nắm được cách hoạt động của công thức mảng, bạn đã viết và sử dụng tốt công thức mảng trong Excel rồi thì giờ với VBA ta sẽ sử dụng như thế nào?.

Hàm mảng cần sử dụng

Ở đây mình có Bảng chi tiết DT bán hàng của các loại hàng hoá theo từng ngày. Yêu cầu là làm một bảng tổng hợp DT theo từng tháng một.

Với yêu cầu này bạn có khá nhiều cách để thực hiện VD như dùng PivotTable, tạo phụ là tháng rồi dùng SUMIF hay SUMIFS,

Và nếu bạn đã quên cách sử dụng hàm SUMIF, SUMIFS thì bạn có thể xem qua bài viết:

Cách sử dụng hàm sumifs và sumif với nhiều điều kiện – một số ví dụ công thức

Để tính hoặc nâng cao hơn và cụ thể trong bài viết này thì bạn có thể áp dụng công thức mảng với cú pháp như sau:

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

Và để hiểu về cách thức hoạt động của hàm này thì bạn có thể tham khảo các link trên hoặc thì tại ô F4 bạn tới nhóm Formula Auditing trong Tab Formulas và tìm tới Evaluate Formula để  có thể theo dõi được cách Excel tính toán. Do bài viết này trọng tâm là sử dụng VBA để điền công thức mảng nên mình xin phép không phân tích sâu về cách hoạt động của hàm mảng.

Sử dụng trong VBA

Với công thức trên, bạn có thể sử dụng theo 2 cách sau đây.

Cách 1:

• Đầu tiên bạn gán công thức mảng trên và gán vào ô F4 bằng thuộc tính (Range(“F4”).FormulaArray=)
• Tiếp theo, bạn FillDown công thức xuống cho các ô còn lại.

Có thể có bạn sẽ thắc mắc là tại sao không gán vào vùng F4:F6 một lần bằng thuộc  tính FormulaArray luôn mà chỉ gán cho ô F4 rồi sau đó mới FillDown công thức xuống, thì câu trả lời là:

Nếu bạn làm như thế thì vùng F4:F6 nó sẽ chỉ ra cùng 1 giá trị là 26.074 vì toàn bộ vùng đó đều có công thức là =SUM((MONTH($A$6:$A$21)=E4)*($C$6:$C$21)). Mà ở đây thì ta cần ô E4 sẽ thay đổi để nó có thể áp dụng cho các tháng còn lại, do đó không thể dùng cách gán công thức cho cả vùng từ F4:F6 được.

Cách 2:

• Bạn gán công thức cho vùng F4:F6 qua phương thức (Range(“F4:F6”).Formula=)
• Sau đó, sẽ tính toán cho công thức vừa gán xuống đó bằng dòng Code: Range(“F4:F6”).FormulaArray = Range(“F4:F6”).FormulaR1C1

Như vậy với 2 cách trên bạn đã có thể sử dụng VBA để gán công thức dạng mảng vào trong Excel rồi. Bạn có thể mở rộng vấn đề thêm VD như:

Thay vì gán công thức mảng xuống Excel (công thức mảng hoạt động khá nặng, với dữ liệu lớn sẽ khiến cho file của bạn ngày một chậm đi) thì bạn muốn là chỉ lấy giá trị sau khi việc tính toán đã hoàn thành rồi, lúc này bạn có thể sử dụng thêm dòng Code Range(“f4:f6”).Value = Range(“f4:f6”).Value. 

Để có thể ngày càng nâng cao kiến thức về Excel và VBA từ đó góp phần tăng hiệu suất trong công việc bạn có thể tham khảo các bài viết hay các khoá học của Học Excel Online tại đây.

VBA101 – Tự động hoá 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