Excel là một trong những kỹ năng cơ bản của dân văn phòng. Khi tính tổng trong Excel, chắc chắn bạn sẽ nghĩ ngay đến hàm SUM; tuy nhiên, đối với các phép tính không liên tục, bạn không thể sử dụng hàng trên để tính toán được. Vì thế mà bài viết sẽ hướng dẫn các bạn cách tính tổng các ô không liền nhau trong Excel dành cho những bạn mới vừa làm quen với công cụ này hoặc cho các anh chị nhân viên văn phòng. Cùng mình tìm hiểu nhé!
Cách tính tổng các ô không liền nhau trong Excel bằng cách tạo thêm cột
Cách đầu tiên, các bạn phải dựa vào việc tạo một phụ để tương tác với các cột dữ liệu đã cho trước. Thực hiện theo các bước sau giúp tránh việc gặp lỗi khi áp dụng hàm.
Bước 1: Các bạn tạo thêm cột phụ cần tính tổng và sử dụng hàm như sau =IFERROR(SEARCH($D$2,A3),0)
Trong đó:
- IFERROR: sẽ giúp phép tính trên không xuất hiện lỗi. Trường hợp hàm SEARCH không tìm thấy giá trị và trả về lỗi #ERROR thì ngay lập tức kết quả sẽ trả về 0
- SEARCH: Tìm giá trị tại ô D2 (chính là ST) xem có nằm trong ô A3 hay không. Nếu có, trả về vị trí của ký tự bắt đầu xuất hiện trong ô A2. Ngược lại sẽ trả về lỗi #VALUE
Bước 2: Bạn áp dụng tương tự với các ô còn lại của cột KD1 và KD 2. Nếu đã ghim ô (dùng ký hiệu $) như hướng dẫn thì bạn chỉ cần giữ và kéo từ ô đầu tiên đến ô cuối cùng để áp dụng công thức là được.
Bước 3: Sau khi tính xong cột phụ, bạn sử dụng hàm SUMIF để tính tổng doanh thu. Áp dụng cho 3 cột ST, NHS và HV.
Công thức như sau: =SUMIF(D3:D14,">0",G3:G14)
Cách tính tổng các ô không liền nhau trong Excel bằng hàm SUMIF
Nếu bạn không muốn phải chèn thêm các cột phụ mà sử dụng trực tiếp hàm SUMIF để tính toán thì hãy tham khảo hướng dẫn dưới đây. Tuy nhiên cách này sẽ phức tạp hơn
Ở đây chúng tôi sẽ giải thích ý nghĩa của công thức và bạn sẽ áp dụng nó vào tình huống của riêng bạn.
Phạm vi điều kiện chúng tôi lấy là từ A3:A14 (ID của tất cả nhân viên)
Điều kiện là "*"&J4&"*" nghĩa là thêm dâu * vào trước và sau ô J3 (ô giá trị cần lấy).
Công thức hàm cho ví dụ bên dưới: =SUMIF(A3:A14,"*"&J4&"*",G3:G14)
Cách tính tổng các ô không liền nhau trong Excel bằng hàm SUMPRODUCT
Ngoài hàm SUMIF, bạn vẫn có thể sử dụng hàm SUMPRODUCT để tính tổng trong Excel. Với hàm PRODUCT, bạn chỉ cần tính toán duy nhất một bước thôi, nhưng công thức sẽ dài hơn so với sử dụng SUMIF. Công thức sẽ như sau
=SUMPRODUCT(($G$3:$G$14)*(IFERROR(SEARCH(J4,$A$3:$A$14),0)>0))
Trong đó:
- Các hàm như IFERROR và hàm SEARCH đã được giải thích như trên cách 1. Tuy nhiên, vì phương pháp này chỉ yêu cầu một bước nên việc thao tác sử dụng hàm tính toán sẽ mất nhiều thao tác hơn.
- Hàm SUMPRODUCT: giúp bạn so sánh kết quả trả về của phép tính trên với 0. Nếu kết quả trả về các giá trị lớn hơn 0 sẽ được dùng để tính tổng doanh thu.
- Dấu * có tác dụng nối giá trị trước và sau nó
Trên đây là hướng dẫn cách tính tổng các ô không liền nhau trong Excel mà chúng mình muốn gửi đến các bạn. Hi vọng qua bài viết sẽ mang đến cho bạn đọc nhiều thông tin và kiến thức bổ ích trong quá trình thao tác với trang tính Excel. Chúc các bạn thành công!
Xem thêm:
