Khi bắt đầu tiếp xúc với Excel thì chắc hàm SUM là một hàm mà các bạn sẽ gặp trước tiên trong công cuộc chinh phục bảng tính Excel. Khi bạn đã biết sử dụng và sử dụng thành thạo hàm này thì bạn đã bao giờ thử đặt vấn đề là làm sao để có thể vẫn tính tổng được mà trên bảng tính Excel không xuất hiện hàm trên bảng tính. Thì bài viết này của Hoc Excel Online sẽ giúp bạn làm điều đó.

Tự động tính tổng vào bảng tính không dùng công thức

Ví dụ về tổng hợp doanh thu bán hàng

Chúng ta có doanh thu bán hàng của 12 tháng trong năm và yêu cầu là làm sao ra được kết quả tổng doanh thu của cả năm mà lại không dùng hàm trực tiếp trong Excel.

Đầu tiên mình cần khai báo 1 biến thuộc kiểu Long để xác định dòng cuối cùng của dữ liệu cần tính tổng (cụ thể trong ví dụ này là biến lr), nếu như bạn đã có kiến thức cơ bản về VBA rồi thì chắc hẳn cũng không còn xa lạ gì với cách xác định dòng hay cột cuối cùng có dữ liệu phải không nào.

Tiếp theo, khi xác định được dòng cuối cùng có dữ liệu để phục vụ cho việc tính tổng rồi, ta cần xác định ô nào trong bảng tính Excel là ô mà ta muốn trả về kết quả và trả về kết quả gì thì dòng Code thứ nhất trong câu lệnh With sẽ giúp bạn thực hiện điều đó. Thuộc tính Value của Range(“b” & lr + 2) sẽ trả về giá trị của WorksheetFunction.Sum(Range(“b5:b” & lr).Value).

Và thuộc tính HasFormula ở dòng Code thứ 2 trong câu lệnh With sẽ giúp bạn kiểm tra xem ô ta vừa điền kết quả đó có phải là công thức hay không, ta thấy ở cửa sổ Immediate kết quả trả về là False, điều này chứng tỏ ô B18 không phải là công thức. Như vậy, bằng cách này chúng ta có thể tính được tổng thông qua Code VBA mà không dùng hàm SUM trực tiếp trên Excel.

Ví dụ khác

Có một chức năng rất hay trong Excel là tự tính tổng, đếm hay thống kê nhanh các thành phần,… khi bạn chọn một vùng, điều này rất thuận tiện khi ta cần thống kê nhanh dữ liệu trong vùng chọn nào đó. Ví dụ dưới đây chúng ta sẽ sử dụng Code VBA để có thể tạo ra được 1 tính tổng nhanh tương tự như Excel.

Trong Sheet3 chúng ta tạo 1 sự kiện SelectionChange, và gõ vào dòng Code như hình. Có thể giải thích ngắn gọn về Code như sau: Khi ô hay vùng bạn chọn KHÔNG giao với Range(“a4:e21”) trong Sheet3 thì tại ô B1 sẽ trả về vbNullString, ngược lại, VBA sẽ chạy vòng lặp For…Next, Nếu như biến cel (đã được khai báo là một đối tượng Range) này là kiểu số, thì cel này sẽ được cộng dồn cho tới hết vòng lặp và gán giá trị vào biến kqSUM (đã được khai báo thuộc kiểu Long) , sau đó in ra ô B1 kết quả.

Ở đây mình chọn 2 vùng là C8:E12 và A14:B18, ta thấy kết quả trả về ở ô B1 hoàn toàn giống với Excel đã tính tổng ở thanh Status Bar. Như vậy, với ví dụ này ta lại có một cách nữa cũng sử dụng Code VBA để tính tổng của những vùng chọn mà không cần phải dùng công thức trên Excel.

Kết luận

Vừa rồi Hoc Excel Online đã giới thiệu một số cách để vẫn có thể tính tổng được mà không phải dùng công thức trực tiếp trên Excel.

Có thể có bạn sẽ nghĩ là: “Tại sao phải phức tạp hoá vấn đề như thế, đơn giản chỉ cần đặt công thức = SUM(vùng cần tính tổng) thế là xong thôi!“, nhưng sẽ tới một lúc nào đó khi công thức, dữ liệu ngày càng nhiều thì lúc này bạn sẽ gặp vấn đề khá lớn trong việc tối ưu dữ liệu và lúc đó bạn sẽ nhận thấy sự cần thiết của VBA.

Để có thể ngày càng nâng cao kiến thức về Excel, VBA nhằm tiết kiệm được thời gian, nâng cao hiệu suất  trong công việc bạn có thể tham khảo các khoá học Excel từ cơ bản tới nâng cao, VBA101, VBA201, … của Hoc Excel Online.

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