Với một bảng tính đơn giản chúng ta có thể dùng hàm SUMIF và hàm SUMIFS để có thể cộng các giá trị thỏa mãn một hay nhiều điều kiện. Nhưng với hàng trăm dữ liệu khổng lồ có các ô có các đoạn mã giống nhau thì cách giải quyết bài toán đó khá khó khan ư. Thế nên bài viết này lập ra sẽ hướng dẫn các bạn 3 cách để tính tổng các dữ liệu giống nhau một cách hiệu quả trong Excel.
Mục lục nội dung
Tổng quan về tính tổng các dữ liệu giống nhau trong Excel
Bài viết này nhằm hướng dẫn các bạn tính tổng các dữ liệu giống nhau trong Excel, bằng ba cách cơ bản:
- Tạo cột phụ và sử dụng hàm SUMIF
- Tính tổng kết quả cùng hàm SUMPRODUCT
- Sử dụng ký tự đại diện và sử dụng hàm SUMIF
Chúng ta cùng giải đáp về 3 cách sử trên để tính tổng các dữ liệu giống nhau thông qua bảng thống kê về doanh thu laptop quý 2 2020
Tạo cột phụ và sử dụng hàm SUMIF để tính tổng các dữ liệu
Bước 1: Tạo cột phụ
Tạo cột phụ để nhận diện các dòng có các kí tự thỏa mãn điều kiện. Ở đây chúng ta sẽ tạo hai cột phụ là Macbook và Asus. Tại cột Macbook ta áp dụng công thức sau: =IFERROR(SEARCH(“Macbook”,B3),0)
Cụ thể là Excel sẽ tìm trong ô B3 có kí tự nào thỏa mãn Macbook và trả về vị trí của ký tự đó trong chuỗi. Nếu không có kí tự nào thỏa mãn điều kiện thì sẽ trả về vị trí 0.
Sao chép công thức cho toàn bộ các ô trong cột. Cho ra kết quả như bảng sau:
Tương tự cách trên ta chỉ cần thay Macbook thành Asus sẽ cho ra kết quả là:
Bước 2: Dùng hàm SUMIF để cộng các dòng có giá trị lớn hơn không
Cột phụ Macbook
Tại cột phụ Macbook ta áp dụng công thức sau: =SUMIF(F3:F12,”>0″,E3:E12). Kết quả đã cho ra tổng các ô dữ liệu Macbook giống nhau
Cột phụ Asus
Tại cột phụ Asus ta áp dụng công thức sau: =SUMIF(G3:G12,”>0″,E3:E12). Kết quả đã cho ra tổng các ô dữ liệu Asus giống nhau
Sử dụng ký tự đại diện và hàm SUMIF để tính tổng dữ liệu
Sử dụng kết hợp với dấu sao (*) là ký tự đại diện cho bất kì kí tự nào.
Cú pháp như sau : =SUMIF(mảng tham chiếu điều kiện,”*”& “chuỗi ký tự tìm kiếm“&”*”,vùng cần tính tổng).
Thay đổi mục chữ in nghiêng để phù hợp với từng yêu cầu.
Công thức tại ô I8 với điều kiện Macbook =SUMIF(B3:B12,”*”&H3&”*”,E3:E12)
Công thức tại ô I9 với điều kiện Asus =SUMIF(B3:B12,”*”&H4&”*”,E3:E12)
Dù ta áp dụng công thức nào điều cũng cho ra các kết quả chính xác và giống nhau.
Tính tổng kết quả cùng hàm SUMPRODUCT
Ngoài cách sử dụng hàm SUMIF và cột phụ như trên, cách tính tổng kết quả cùng hàm SUMPRODUCT sẽ cho ta chỉ một phép tính và đỡ rắc rối hơn. Ta áp dụng công thức sau để tính tổng của điều kiện Macbook.
Công thức tại ô J3 với điều kiện Macbook =SUMPRODUCT((E3:E12)*(IFERROR(SEARCH(H3,B3:B12),0)>0))
Nhấn tổ hợp phím Ctrl +Shift + Enter. Với công thức trên, hàm IFERROR(SEARCH(H3, B3:B12), 0) sẽ tìm kiếm các ô trong cột B có kí tự như ở ô H3 ( Macbook ) và trả về giá trị lớn hơn 0, nếu không có sẽ trả về bằng 0.
Khi đặt công thức trên vào hàm SUMPRODUCT, Excel sẽ so sánh kết quả đó có >0 hay không. Và cộng tổng doanh thu trong cột Tổng tiền (Cột E) tương ứng với giá trị trên >0.
Ở trường hợp thông thường ta không cần sử dụng tổ hợp phím Ctrl + Shift + Enter vì công thức SUMPRODUCT là công thức dạng mảnh. Tuy nhiên với trường hợp này ta buộc phải sử dụng tổ hợp phím Ctrl + Shift + Enter. Cho ra kết quả sau:
Ta áp tính điều kiện Asus tương tự như cách tính Macbook ta áp dụng công thức tại ô J4 với điều kiện Asus =SUMPRODUCT((E3:E12)*(IFERROR(SEARCH(H4,B3:B12),0)>0))
Ở đây ta nhấn tổ hợp phím Ctrl +Shift + Enter. Cho ra kết quả như sau:
Qua bài hướng dẫn chi tiết về tính tổng các dữ liệu giống nhau trong Excel, mong rằng các bạn sẽ nắm chắc kiến thức và áp dụng thành công vào trong thực tế và giải quyết khó khăn. Mindovermetal hy vọng bạn sẽ thích bài viết này!
