Trong trường hợp phải tính tổng theo điều kiện là những ký tự giống nhau ở trong 1 ô thì dùng hàm gì? Đây là câu hỏi rất thú vị mà Học Excel Online thường nhận được. Hãy cùng tìm hiểu cách làm trong trường hợp này nhé. Cách Tính Tổng Các ô Không Liền Nhau Trong Excel 2003, 2007, 2010, 2013, 365, 2016
Bạn đang xem: Cách tính tổng các ô không liền nhau trong excel
Ví dụ chúng ta có yêu cầu như sau:
Trong cột ID nhân viên, chúng ta thấy các mã ID có chứa các ký tự là KD1, KD2. Nhưng vị trí của các ký tự này không giống nhau, tức là nó có thể nằm ngẫu nhiên ở bất kỳ vị trí nào trong ô, miễn là có đúng ký tự đó.
Yêu cầu là tính tổng doanh thu của những nhân viên có mã ID lần lượt là KD1 và KD2.
Cách tính tổng theo các ô có ký tự giống nhau trong Excel
Trong bài toán trên, mới đọc thì thấy khá phức tạp. Nhưng chắc hẳn bạn sẽ bất ngờ khi chúng ta có nhiều cách giải:
Tư Khóa Liên Quan: Tính tổng các ô xen kẽ trong Excel, Cách tính tổng các mã giống nhau trong Excel, Hàm SUMPRODUCT tính tổng nhiều điều kiện, Tính tổng các ô cách đều nhau trong Excel, Cách tính tổng hàng ngang trong Excel, Hàm tính tổng các phần tử trong Excel, Lọc và tính tổng trong Excel, Công thức tính tổng lương trong Excel,
Cách 1: Tạo cột phụ nhận biết giá trị điều kiện tính
Bởi vì các ký tự nằm lẫn trong chuỗi ký tự ở mỗi ô nên chúng ta có thể dùng hàm xác định riêng từng ký tự xem ô nào chứa ký tự đó. Chèn thêm cột phụ phía sau cột Doanh thu và dùng hàm SEARCH xác định như sau:
Trong câu lệnh IFERROR(SEARCH($C$1,A2),0) ta có:
Thứ 1: Search giá trị trong ô C1 (là KD1) xem có trong ô A2 hay không. Kết quả trả về nếu có sẽ là vị trí ký tự bắt đầu xuất hiện trong ô A2. Nếu không sẽ trả về lỗi #VALUEThứ 2: Kết hợp hàm IFERROR để tránh trường hợp lỗi #VALUE khi không tìm thấy bởi hàm SEARCH, lúc đó giá trị lỗi sẽ được thay bằng số 0
Áp dụng tương tự với giá trị ở ô D1 là KD2
Khi đó kết quả >0 tức là có giá trị cần tìm, =0 là không có giá trị cần tìm.
Bây giờ bài toán trở nên khá đơn giản, chúng ta chỉ cần sử dụng hàm SUMIF để tính kết quả như sau:
Kết quả tổng doanh thu của nhân viên có mã ID là KD1 =SUMIF(C2:C10,”>0″,B2:B10)
Tương tự nhân viên có mã KD2 là =SUMIF(D2:D10,”>0″,B2:B10)
Cách 2: Tính trực tiếp không dùng cột phụ với hàm SUMIF
Việc thêm cột phụ khiến chúng ta phải thêm mỗi điều kiện là 1 cột, tại mỗi cột lại sử dụng nhiều công thức. Tại sao không làm trực tiếp để tiết kiệm tài nguyên của Excel nhỉ? Câu trả lời là hoàn toàn có thể làm trực tiếp được.
Xem thêm: Hướng dẫn sửa lỗi mở máy tính không vào được Windows
Cách viết công thức như sau:
Với điều kiện là KD1, chúng ta có:
H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)
Vùng điều kiện là ở cột A, từ A2:A10 là ID nhân viênĐiều kiện là “*”&F3&”*” có nghĩa là thêm 2 dấu * ở trước và sau giá trị trong ô F3. Vì dấu * là ký tự đặc biệt nối với ô F3 nên cần đặt trong dấu nháy kép, sau đó sử dụng dấu & để nối ký tự.Vùng tính tổng là cột Doanh thu, từ B2:B10
Kết quả cũng bằng với cách thứ 1.
Nếu không muốn sử dụng hàm SUMIF, các bạn có thể thay bằng hàm SUMIFS như sau:
H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)
Các nội dung trong hàm này vẫn giống hàm SUMIF, nhưng thứ tự có thay đổi 1 chút khi vùng tính tổng được đưa lên thành phần thứ 1 ở trong hàm.
Cách 3: Sử dụng hàm SUMPRODUCT
Hẳn bạn đã nghe nói tới hàm SUMPRODUCT có thể thay thế hoàn toàn cho hàm SUMIF, SUMIFS. Trong ví dụ này chúng ta sẽ xem hàm SUMPRODUCT có thể sử dụng được không nhé:
Công thức tại ô I3 sử dụng hàm SUMPRODUCT như sau:
=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))
Các bạn có thể thấy phần IFERROR(SEARCH(F3,$A$2:$A$10),0) chính là nội dung trong cột phụ mà chúng ta đã làm ở trên.
Khi đặt trong hàm SUMPRODUCT, chúng ta sẽ so sánh kết quả đó có >0 hay không, và với những giá trị >0 sẽ dùng để tính tổng trong cột Doanh thu.
* Lưu ý:
Cách viết sau đây không ra kết quả:
=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)
Rất thú vị phải không nào. Như vậy là Học Excel Online đã giới thiệu cho bạn 3 cách làm bài toán này. Bạn có cách nào khác không? Hãy chia sẻ cùng chúng tôi nhé.
Ngoài ra bạn có thể tham khảo thêm một số bài viết cùng chủ đề:
Tại sao nên dùng hàm SUMIFS thay cho hàm SUMPRODUCT tính tổng theo nhiều điều kiện
Hi vọng với những chia sẻ hữu ích trên đây sẽ giúp các bạn sử dụng thành thạo cách tính tổng trong excel một cách chính xác để thuận tiện cho công việc. Đặc biệt, với dân văn phòng chuyên làm việc với con số, dữ liệu cần nắm rõ vừa tiết kiệm thời gian và hiệu quả công việc cao. Đừng quên cập nhật thêm nhiều kỹ năng mới nhé!
