Phương pháp tính tổng theo các ô có ký tự giống nhau trong Excel

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à Gitiho 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é.

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.

Phân tích bài toán và đưa ra cách để thực hiện bài toán

Cách gián tiếp tạo cột phụ:

Trong yêu cầu ở trên muốn tính được tổng phải tách hoặc dưa ra một điều kiện để phân biệt được từ khoá trong chuỗi. thành hàng song song với hàng chứa dữ liệu

Với  ký tự là ngẫu nhiên ở trong một chuỗi để tính toán được  trước tiên ta phải tìm ra nó và so sánh nó với một chuỗi chúng ta đưa vào, vậy chúng ta phải liên tưởng ngay đến hàm tìm kiếm dữ liệu bạn có thể tham khảo hai hàm tìm kiếm sau.

Hướng dẫn cách sử dụng hàm tìm kiếm

Sau khi tách được hàng cần thiết thì công việc của chúng ta còn lại là sử dụng hàm tính toán có điều kiện sumif hoặc sumifs hay sumporduct để thực hiện tính toán theo điều kiện là xong.

Cách trực tiếp

Với phương pháp làm giống hệt cách gián tiếp nhưng chúng ta không cần tạo ra cột phụ nữa mà chúng ta sẽ viết trực tiếp trên một dòng công thức.

Phương pháp giải quyết thật đơn giản đúng không nào? chúng ta cùng bắt đầu nhé.

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:

Cách 1: Tạo cột phụ nhận biết giá trị điều kiện tính

Cách sử dụng gián tiếp tạo ra cột phụ song song với cột chứa điều kiện.

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 #VALUE
• Thứ 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

Xem thêm: Hướng dẫn cách nhận biết loại lỗi và cách bẫy lỗi trong Excel

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.

Bạn có thể đọc bài viết sau để biết về dạng điều kiền trong khoảng nhé :  bạn có thể tham khảo bài viết sau:

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ới việc đặt ký tự * ở cả trước và sau nghĩa là không cần biết ở đằng trước hay sau có gì miễn là trong đó có cụm từ ở ô F3 thì đều được chấp nhận là đúng.
• 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.

Xem thêm: Hàm SUMIF / SUMIFS Tính tổng theo điều kiện

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.

Tương tự với hàm

=SUM(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))

Chúng ta viết cũng ra kết quả nhưng cần ấn tổ hợp phím Ctrl+Shift+Enter để tính toán kết quả.

* 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à Gitihio đã 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é.

Cảm ơn các bạn đã theo dõi bài viết nếu thấy hay và hữu ích hãy chia sẻ cho nhiều người biết và đừng quên coment ý kiến của mình để đội ngũ phát triển Gitiho đưa ra các bài viết chất lượng hơn nữa.

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

Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện