Dữ liệu trong Excel rất đa dạng và cần có nhiều cách xử lý khác nhau để phù hợp với từng loại dữ liệu. Trong bài viết này chúng ta cùng tìm hiểu về một chủ đề khó nhưng rất thú vị là: Đếm số giá trị không trùng trong 1 danh sách.
Giả sử chúng ta có 1 bảng dữ liệu như sau:
Một yêu cầu đặt ra là đếm xem trong cột Nhân viên có bao nhiêu người, cột Ngày có bao nhiêu ngày, cột Tên hàng có bao nhiêu mặt hàng nhưng không tính các giá trị trùng nhau. Vậy chúng ta sẽ giải quyết ra sao với bài toán này?
Cách thứ 1: Thêm cột phụ và dùng hàm COUNTIF
Đây là cách làm cơ bản, dễ thực hiện và cũng dễ hiểu. Nhược điểm duy nhất của cách này là làm qua nhiều bước và tăng số công thức phải tính lên nhiều.
Ví dụ với cột Ngày, chúng ta sẽ tạo 1 cột phụ là cột F và sử dụng hàm COUNTIF tại đó. Hàm COUNTIF không còn xa lạ gì với chúng ta phải không nào.
Để phân biệt giá trị tại dòng đó xuất hiện lần thứ mấy thì chúng ta dùng hàm COUNTIF như sau:
- Thứ 1: Đếm số lần xuất hiện tại mỗi dòng trong vùng tính từ dòng đầu tiên tới dòng đang đếm. Điều này rất quan trọng bởi khi tính theo cách này thì giá trị COUNTIF đếm được sẽ lần lượt là 1, 2 , 3… tăng dần theo số lần xuất hiện của điều kiện (criteria) đang xét. Khác với việc chọn Range là cả vùng dữ liệu từ C2:C11, ở đây chỉ xét từ C2 tăng dần theo các dòng. Giá trị điểm đầu C2 trong vùng Range được cố định.
- Thứ 2: Sau khi dùng hàm COUNTIF chúng ta sẽ ra được kết quả tại cột F từ F2:F11. Mỗi 1 giá trị số 1 thể hiện là ngày tương ứng tại cột C xuất hiện lần thứ 1, tức là chưa bị trùng. Các giá trị lớn hơn 1 là trùng. Khi đó kết quả đếm giá trị không trùng chỉ cần dùng hàm COUNTIF trong cột F với điều kiện là số 1
Kết quả thu được là 9
Như vậy khi sử dụng hàm COUNTIF trong cột phụ thì giải quyết yêu cầu trên khá dễ dàng.
Nhược điểm
Cách này tuy đơn giản nhưng lại có nhược điểm là tính thủ công. Bạn phải chọn 1 cột phụ. Dùng công thức tại cột phụ. Dùng công thức tính kết quả. Rất nhiều bước phải không nào? Hơn nữa với bảng dữ liệu lớn thì việc này khiến tăng thêm 1 lượng công thức rất lớn, với mỗi dòng dữ liệu là 1 công thức.
Vậy nên trong những bảng dữ liệu lớn và khả năng Excel của bạn tốt thì tại sao chúng ta không tìm hiểu cách thứ 2 nhỉ?
Cách thứ 2: Công thức mảng SUMPRODUCT kết hợp FREQUENCY
Hẳn bạn thấy FREQUENCY là một hàm hơi lạ phải không. Đây là một hàm xử lý dữ liệu dạng mảng, dùng để đếm số lần xuất hiện của các giá trị trong 1 phạm vi nào đó. Trong yêu cầu của bài toán này, nếu không sử dụng cột phụ thì chúng ta phải sử dụng các công thức xử lý dữ liệu theo mảng để tính.
Cấu trúc hàm FREQUENCY như sau:
=FREQUENCY(data_array,bins_array)
Nguyên tắc của hàm Frequency là đếm xem các giá trị trong tham số bins_array được lặp lại bao nhiêu lần trong data_array. Về bản chất thì đây giống như việc dùng hàm COUNTIF để đếm từng phần tử trong bins_array (criteria) trong vùng data_array (range) vậy.
Ứng dụng nguyên tắc trên, chúng ta xét 2 nội dung sau:
- Số thứ tự dòng của mỗi dòng dữ liệu trong cột cần đếm giá trị không trùng sẽ lần lượt là từ 1 đến 10 (ứng với các dòng từ C2:C11). Coi vùng này là bins_array. Giả sử xét A2:A11 chính là nội dung chúng ta cần.
- Đếm xem mỗi giá trị xuất hiện trong cột ngày là giá trị thứ mấy với hàm MATCH. Vùng này được coi là data_array. Hàm MATCH được viết như sau:
Khi kiểm tra kết quả hàm MATCH bằng cách bôi đen công thức trên thanh Formulas Bar và nhấn phím F9 ta có:
Các giá trị lần lượt trả về là 1 mảng gồm các phần tử 1, 2, 3, 4, 5, 6, 7, 8, 10 trong đó phần tử 8 xuất hiện 2 lần do ngày 27/6/2018 xuất hiện 2 lần trong mảng. Điều này tương ứng với cách biểu diễn hàm MATCH như sau:
Khi kết hợp 2 thành phần trên vào hàm Frequency ta được:
Việc còn lại là đếm số giá trị lớn hơn 0 trong mảng tạo ra bởi hàm Frequency là ra kết quả.
Tuy nhiên vì công thức chỉ đặt trong 1 ô nên hàm đếm giá trị lớn hơn 0 phải dùng đến hàm SUMPRODUCT. Cách làm như sau:
- Xét các giá trị tạo ra bởi mảng trong hàm Frequency so sánh với 0, lấy các giá trị >0
- Việc so sánh Frequency>0 sẽ tạo ra các mảng là kết quả True/False. Khi đó chuyển các giá trị True/False về dạng 1/0 bằng cách thêm 2 dấu trừ ở trước biểu thức so sánh đó
Kết quả cũng ra là 9
Như vậy chỉ cần đặt 1 biểu thức tại 1 ô C13 chúng ta cũng ra được kết quả giống như cách thứ 1.
Ưu điểm:
- Khắc phục được nhược điểm của cách 1
- Giúp chúng ta nâng cao hơn khả năng sử dụng Excel và các tư duy về sử dụng hàm, xử lý dữ liệu, loại dữ liệu trên Excel.
- Ứng dụng trong việc trích xuất dữ liệu không trùng từ bảng dữ liệu bằng cách sử dụng hàm
Nhược điểm:
- Công thức khá phức tạp nên cần hiểu kỹ về bản chất dữ liệu và cách sử dụng công thức mảng
Nâng cao:
Không phải lúc nào chúng ta cũng sử dụng cột STT trong bảng dữ liệu. Vậy nên việc sử dụng trực tiếp vùng A2:A11 vào tham số bins_array trong hàm Frequency phải thay đổi bằng cách khác. Theo bạn thì chúng ta có thể dùng cách nào? Hãy chia sẻ với Học Excel Online cách giải quyết trong trường hợp này nhé.
