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. Hãy cùng Học Excel Online tìm hiểu xem khó ở đâu và thú vị ở đâu nhé.
Giả sử tất cả 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?
Mục lục bài viết
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, tất cả 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 lạ lẫm gì với tất cả chúng ta phải không nào. Nếu bạn không nhớ hàm COUNTIF dùng thế nào thì xem tại đây nhé :
Hàm CountIf trong excel, hướng dẫn đơn cử và có ví dụ minh họa
Để phân biệt giá trị tại dòng đó Open lần thứ mấy thì tất cả 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ì xử lý nhu yếu trên khá thuận tiện .
Nhược điểm
Cách này tuy đơn thuần nhưng lại có điểm yếu kém là tính thủ công bằng tay. 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 tác dụng. 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à năng lực Excel của bạn tốt thì tại sao tất cả chúng ta không khám phá 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 giải quyết và xử lý dữ liệu dạng mảng, dùng để đếm số lần Open của những giá trị trong 1 khoanh vùng phạm vi nào đó. Trong nhu yếu của bài toán này, nếu không sử dụng cột phụ thì tất cả chúng ta phải sử dụng những công thức giải quyết và xử lý dữ liệu theo mảng để tính .
Nếu bạn chưa biết về hàm Frequency hoàn toàn có thể xem tại bài viết : Chức năng và cú pháp hàm FREQUENCY trong Excel
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 hiệu 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 những thành phần 1, 2, 3, 4, 5, 6, 7, 8, 10 trong đó thành phần 8 Open 2 lần do ngày 27/6/2018 Open 2 lần trong mảng. Điều này tương ứng với cách màn biểu diễn hàm MATCH như sau :
Khi phối 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 hiệu 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 tất cả chúng ta cũng ra được hiệu 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é.
Tham khảo :
Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện
Xem thêm: Đại từ – Wikipedia tiếng Việt
Tổng hợp những giải pháp vô hiệu dữ liệu lặp
Cách lọc ra một list những giá trị duy nhất và độc lạ trong Excel
Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại:
Source: https://bem2.vn
Category: TỔNG HỢP
