Lọc tìm giá trị duy nhất và loại bỏ giá trị trùng lặp là hai nhiệm vụ tương tự vì mục tiêu là trình bày danh sách các giá trị duy nhất. Tuy nhiên, có sự khác biệt quan trọng: Khi bạn lọc các giá trị duy nhất, các giá trị trùng lặp chỉ bị ẩn tạm thời. Tuy nhiên, việc loại bỏ các giá trị trùng lặp có nghĩa là bạn sẽ xóa vĩnh viễn các giá trị trùng lặp.
Giá trị trùng lặp là giá trị trong đó tất cả các giá trị trong ít nhất một hàng giống nhau cho tất cả các giá trị trong hàng khác. Việc so sánh các giá trị trùng lặp phụ thuộc vào giá trị xuất hiện trong ô — chứ không phải giá trị cơ bản được lưu trữ trong ô. Ví dụ: nếu bạn có cùng một giá trị ngày trong các ô khác nhau, một giá trị được định dạng là "08/03/2006" và giá trị còn lại là "08/03/2006", các giá trị là duy nhất.
Kiểm tra trước khi loại bỏ các mục trùng lặp: Trước khi loại bỏ các giá trị trùng lặp, trước tiên, bạn nên thử lọc theo — hoặc định dạng có điều kiện trên các giá trị duy nhất để xác nhận rằng bạn đã đạt được kết quả mong đợi.
Hãy nói rằng bạn cần tính tổng các giá trị với nhiều hơn một điều kiện, chẳng hạn như tổng doanh thu sản phẩm trong một khu vực cụ thể. Đây là một trường hợp tốt cho việc sử dụng hàm Sumifs trong công thức. Hãy xem ví dụ này mà chúng tôi có hai điều kiện: chúng tôi muốn tổng doanh số bán thịt [từ cột C] ở khu vực phía nam [từ cột a].
Đây là một công thức bạn có thể sử dụng để acomplish này:
= SUMIFS [D2: D11, A2: A11, "miền Nam", C2: C11, "thịt"]
Kết quả là giá trị 14.719.
Hãy xem xét chặt chẽ hơn ở từng phần của công thức.
=SUMIFS là công thức số học. Nó tính toán số, trong trường hợp này nằm trong cột D. Bước đầu tiên là xác định vị trí của các số:
=SUMIFS[D2:D11,
Nói cách khác, bạn muốn công thức tính tổng các số trong cột đó nếu phù hợp với các điều kiện. Phạm vi ô đó là đối số đầu tiên trong công thức này — đoạn đầu tiên của dữ liệu mà hàm đó yêu cầu là đầu vào.
Tiếp theo, bạn muốn tìm dữ liệu đáp ứng hai điều kiện, vì vậy bạn nhập điều kiện đầu tiên của mình bằng cách chỉ định hàm vị trí của dữ liệu [A2: A11] và cũng là nội dung nào — vốn là "miền Nam". Thông báo dấu phẩy giữa các đối số riêng biệt:
=SUMIFS[D2:D11,A2:A11,”Miền Nam”,
Dấu ngoặc kép xung quanh "miền Nam" xác định rằng dữ liệu văn bản này.
Cuối cùng, bạn nhập biến số cho điều kiện thứ hai – phạm vi ô [C2:C11] chứa từ “thịt,” cộng với từ đó [nằm trong dấu ngoặc kép] để Excel có thể tìm dữ liệu phù hợp. Kết thúc công thức với dấu đóng ngoặc ] , rồi nhấn Enter. Kết quả, một lần nữa, là 14.719.
=SUMIFS[D2:D11,A2:A11,”Miền Nam”,C2:C11,”Thịt”]
Khi bạn nhập hàm SUMIFS trong Excel, nếu bạn không nhớ các đối số, trợ giúp đã sẵn sàng ở bàn tay. Sau khi bạn nhập = Sumifs [, công thức AutoComplete xuất hiện bên dưới công thức, với danh sách các đối số theo thứ tự phù hợp của họ.
Xem hình ảnh của công thức AutoComplete và danh sách các đối số, trong ví dụ của chúng sum_rangelà D2: D11, cột của các số mà bạn muốn tính tổng; criteria_range1là A2. A11, cột dữ liệu nơi criteria1 "miền Nam" nằm.
Khi bạn nhập, phần còn lại của biến số sẽ xuất hiện trong Công thức AutoComplete [không hiện ở đây]; criteria_range2 là C2:C11, cột dữ liệu nơi criteria2 “Thịt” có trong đó.
Nếu bạn bấm vào Sumifs trong công thức AutoComplete, một bài viết sẽ mở ra để giúp bạn có thêm trợ giúp.
Nếu bạn muốn thử nghiệm với hàm SUMIFS, đây là một số dữ liệu mẫu và công thức sử dụng hàm.
Bạn có thể làm việc với dữ liệu mẫu và công thức ngay tại đây, trong sổ làm việc Excel dành cho web này. Thay đổi các giá trị và công thức, hoặc thêm các giá trị và công thức của riêng bạn và xem trực tiếp kết quả thay đổi như thế nào.
Sao chép tất cả các ô trong bảng bên dưới và dán vào ô A1 trong trang tính mới trong Excel. Bạn có thể muốn điều chỉnh độ rộng cột để xem công thức tốt hơn
| Khu vực | Nhân viên bán hàng | Loại | Doanh số |
| Miền Nam | Ito | Nước giải khát | 3571 |
| Miền Tây | Lannin | Sản phẩm bơ sữa | 3338 |
| Miền Đông | Makovec | Nước giải khát | 5122 |
| Miền Bắc | Makovec | Sản phẩm bơ sữa | 6239 |
| Miền Nam | Jordan | Nông sản | 8677 |
| Miền Nam | Lannin | Thịt | 450 |
| Miền Nam | Lannin | Thịt | 7673 |
| Miền Đông | Makovec | Nông sản | 664 |
| Miền Bắc | Lannin | Nông sản | 1500 |
| Miền Nam | Jordan | Thịt | 6596 |
| Công thức | Mô tả | Kết quả | |
| ' = SUMIFS [D2: D11, A2: A11, | Tổng doanh thu thịt trong Cột C ở phía nam khu vực trong cột A [kết quả là 14719]. | = SUMIFS [D2: D11, A2: A11, |
Lưu ý:
-
Bạn muốn thêm ví dụ? Bạn sẽ tìm thấy nhiều ví dụ hơn nữa trong bài viết về hàm SUMIFS.
-
Nếu bạn muốn tính tổng giá trị cho một phạm vi dựa trên giá trị của phạm vi khác, dùng hàm SUMIF.
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é.
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 thực ra ta có rất nhiều cách tính tổng trong Excel và bạn sẽ bất ngờ khi biết 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
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 #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.
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.
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.
* 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
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