Đánh giá lequocthai.com:
Mặc dù đây là một yêu cầu rất phổ biến, Excel không có công thức nào có thể trực tiếp đếm các giá trị độc nhất trong một phạm vi.
Trong bài viết này sẽ giới thiệu 5 cách khác nhau để đếm các giá trị độc nhất trong Excel.
Trước khi bắt đầu, hãy làm rõ mục tiêu.
Nhìn vào hình ảnh trên, có thể hiểu rõ cách đếm giá trị độc nhất trong Excel.
Phương pháp – 1: Sử dụng công thức SUMPRODUCT và COUNTIF
Cách đơn giản và dễ nhất để đếm các giá trị khác nhau trong Excel là dùng công thức SUMPRODUCT và COUNTIF.
Công thức tổng quát bạn có thể sử dụng là:
=SUMPRODUCT(1/COUNTIF(data,data))`’data’` – đại diện cho phạm vi chứa các giá trị
Cách công thức này hoạt động
Công thức này bao gồm hai hàm kết hợp với nhau. Đầu tiên, hàm COUNTIF sẽ xem xét trong phạm vi dữ liệu (ví dụ B2:B9) và đếm số lần mỗi giá trị xuất hiện. Kết quả là một mảng các số, ví dụ:
{2;2;3;3;3;2;2;1}
Tiếp theo, các kết quả của hàm COUNTIF được dùng làm mẫu số, với tử số là 1. Nhờ đó, các giá trị xuất hiện một lần sẽ cho kết quả 1, còn các giá trị lặp lại sẽ cho các phân số tương ứng với số lần xuất hiện (ví dụ 1/4 = 0,25).
Cuối cùng, hàm SUMPRODUCT cộng dồn tất cả các phần tử trong mảng và trả về kết quả cuối cùng.
Đọc các bài viết này để biết chi tiết hơn về hàm COUNTIF hoặc hàm SUMPRODUCT.
Lưu ý:
Công thức này hoạt động tốt khi các giá trị liên tục trong một phạm vi; nếu có ô trống, COUNTIF sẽ trả về 0 và 1/0 sẽ gây lỗi chia cho 0. Trong những trường hợp này, công thức sẽ trả về lỗi “divide by zero”.
Để khắc phục, bạn có thể dùng biến thể công thức sau, bỏ qua các ô trống:
=SUMPRODUCT(((data<>"")/COUNTIF(data,data&"")))`’data’` – đại diện cho phạm vi chứa các giá trị
Phương pháp – 2: Sử dụng công thức mảng SUM, FREQUENCY và MATCH
Công thức trên phù hợp cho phạm vi nhỏ. Khi dữ liệu lớn, SUMPRODUCT và COUNTIF chậm và có thể làm bảng tính trở nên chậm.
Vì vậy, đối với bộ dữ liệu lớn, bạn có thể chuyển sang công thức dựa trên hàm FREQUENCY.
Công thức tổng quát như sau:
=SUM(IF(FREQUENCY(IF(data<>"",MATCH(data,data,0)),ROW(data)-ROW(firstcell)+1),1))Trong đó,
`’data’` đại diện cho phạm vi chứa các giá trị.
`’firstcell’` đại diện cho ô đầu tiên của phạm vi.
Lưu ý: Đây là công thức mảng; sau khi nhập, nhấn Ctrl+Shift+Enter và công thức sẽ được bao quanh bởi dấu ngoặc nhọn {} như hình dưới.
Cách công thức này hoạt động
Hàm FREQUENCY chỉ làm việc với số, vì vậy cần chuyển các giá trị thành dãy số. Bắt đầu từ bên trong, hàm MATCH trả về vị trí (số thứ tự) của lần xuất hiện đầu tiên của mỗi giá trị trong phạm vi. Nếu có giá trị trùng lặp, MATCH chỉ trả về vị trí của lần xuất hiện đầu tiên.
Sau MATCH, có một câu lệnh IF. Câu lệnh IF dùng để thay thế lỗi #N/A khi MATCH gặp ô trống; các ô trống được lọc bằng `data<>“”`.
Mảng kết quả chứa một tập hợp các số và FALSE cho các ô trống, ví dụ:
{1;1;3;3;3;6;FALSE;6;9}
Mảng này được đưa vào hàm FREQUENCY, hàm trả về tần suất xuất hiện của mỗi số; hàm IF bên ngoài chuyển các giá trị độc nhất thành 1, các giá trị lặp thành FALSE.
Kết quả cuối cùng sẽ là 4.
Phương pháp – 3: Sử dụng PivotTable (Chỉ hoạt động trong Excel 2013 trở lên)
Sự tích hợp của Power Pivot (Mô hình dữ liệu) với Excel đã cung cấp một số tính năng mạnh mẽ cho người dùng. Giờ đây PivotTable cũng có thể giúp bạn lấy được số đếm riêng biệt của các giá trị độc nhất.
Thực hiện các bước sau:
- Truy cập mục Insert trên ribbon và nhấn PivotTable.
- Trong hộp thoại PivotTable, chọn phạm vi dữ liệu và đánh dấu ô Add this data to Data Model, sau đó nhấn OK.
- Kéo cột chứa dữ liệu Values vào khu vực Rows.
- Nhấp chuột phải vào cột Count, chọn Value Field Settings.
- Trong cửa sổ Value Field Settings, chọn Distinct Count và nhấn OK.
PivotTable sẽ hiển thị số đếm riêng biệt cho các giá trị.
Phương pháp – 4: Sử dụng hàm SUM và COUNTIF
Đây là một công thức mảng dùng hàm SUM và COUNTIF để đếm các giá trị độc nhất trong một phạm vi.
=SUM(IF(ISTEXT(range),1/COUNTIF(range,range),""))Trong đó,
`’range’` đại diện cho phạm vi chứa các giá trị.
Lưu ý: Đây là công thức mảng; sau khi nhập, nhấn Ctrl+Shift+Enter và công thức sẽ được bao quanh bởi dấu ngoặc nhọn {}.
Cách công thức này hoạt động
Hàm ISTEXT trả về TRUE cho các ô chứa văn bản và FALSE cho các ô khác. Khi giá trị là văn bản, hàm COUNTIF sẽ đếm số lần xuất hiện của nó trong phạm vi. Kết quả của COUNTIF được dùng làm mẫu số với tử số 1, vì vậy giá trị xuất hiện 4 lần sẽ cho 1/4 = 0,25, còn xuất hiện một lần sẽ cho 1. Cuối cùng, hàm SUM cộng dồn tất cả các giá trị và trả về kết quả.
Phương pháp 5 – Hàm người dùng tự định nghĩa COUNTUNIQUE
Nếu không có phương pháp nào ở trên đáp ứng nhu cầu, bạn có thể tự tạo một hàm người dùng định nghĩa (UDF) để đếm các giá trị độc nhất.
Dưới đây là mã để viết UDF của riêng bạn:
Function COUNTUNIQUE(DataRange As Range, CountBlanks As Boolean) As Integer
Dim CellContent As Variant
Dim UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellContent In DataRange
If CountBlanks = True Or IsEmpty(CellContent) = False Then
UniqueValues.Add CellContent, CStr(CellContent)
End If
Next
COUNTUNIQUE = UniqueValues.Count
End Function
Cách chèn hàm này vào Excel
- Nhấn Alt + F11 trong Excel, cửa sổ VBA sẽ mở.
- Trong VBA, nhấp chuột phải vào Microsoft Excel Objects → Insert → Module.
- Đối tượng Module sẽ mở ra; dán đoạn mã UDF vào cửa sổ mô-đun.
- Lưu bảng tính; công thức đã sẵn sàng để sử dụng.
Cách sử dụng UDF
Để sử dụng, chỉ cần nhập tên hàm COUNTUNIQUE như một hàm Excel thông thường:
=COUNTUNIQUE(data_range, count_blanks)`’data_range’` – đại diện cho phạm vi chứa các giá trị.
`’count_blanks’` – tham số kiểu Boolean, nhận giá trị TRUE hoặc FALSE. Nếu đặt TRUE, các ô trống sẽ được tính là một giá trị độc nhất; nếu FALSE, các ô trống sẽ bị bỏ qua.
Như vậy đã giới thiệu tất cả các phương pháp giúp bạn đếm giá trị độc nhất trong Excel. Hãy cho chúng tôi biết các phương pháp hoặc mẹo của bạn để thực hiện cùng việc này.
