Cách Đếm Số Lần Xuất Hiện Trong Excel Mà Không Tính Trùng Lặp: Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Cách Copy Số Chết Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Mở Đầu

Trong công việc văn phòng, đặc biệt là khi làm việc với dữ liệu lớn, việc đếm số lần xuất hiện của các mục mà không tính trùng lặp là một nhu cầu rất phổ biến. Bạn có thể đang làm việc với danh sách khách hàng, mã sản phẩm, tên nhân viên, hay bất kỳ tập hợp dữ liệu nào mà bạn muốn biết có bao nhiêu giá trị duy nhất (unique values) trong đó. Excel cung cấp một loạt các hàm và kỹ thuật để thực hiện việc này, từ các hàm cơ bản như COUNTIF và COUNTIFS cho tới các hàm mảng tiên tiến như SUMPRODUCT, FREQUENCY, và trong các phiên bản mới hơn là UNIQUE, COUNTUNIQUE (trong Google Sheets) hoặc LET kết hợp với LAMBDA.

Bài viết này sẽ đi sâu vào các phương pháp đếm trong Excel mà không tính trùng lặp, cung cấp bước-by-bước, ví dụ thực tế, và lưu ý quan trọng để bạn có thể áp dụng ngay trong công việc. Dù bạn là người mới bắt đầu hay đã có kinh nghiệm, bạn sẽ tìm thấy những giải pháp phù hợp với nhu cầu và phiên bản Excel của mình.

Có thể bạn quan tâm: Cố Định Hình Ảnh Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Các Phương Pháp Đếm Không Trùng Lặp

Trong các phần tiếp theo, chúng ta sẽ đi sâu vào từng phương pháp, cung cấp công thức mẫu, giải thích từng bước và đưa ra ví dụ minh hoạ.

Có thể bạn quan tâm: Cách Copy File Excel Vào Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Đếm Giá Trị Duy Nhất Bằng Công Thức Mảng COUNTIF

2.1. Nguyên Lý

Công thức:

=SUM(1/COUNTIF(range, range))

• COUNTIF(range, range) trả về một mảng, trong đó mỗi phần tử là số lần xuất hiện của giá trị tương ứng trong range.
• 1/COUNTIF(...) sẽ tạo ra một mảng các giá trị 1 / số lần xuất hiện. Ví dụ, nếu một giá trị xuất hiện 3 lần, các phần tử tương ứng sẽ là 1/3.
• Khi cộng tổng (SUM) các giá trị này, các giá trị trùng lặp sẽ bảo đảm cộng lại thành 1 (vì 3 (1/3) = 1). Nhờ vậy, tổng cuối cùng chính là số giá trị duy nhất.

2.2. Cách Thực Hiện

1. Chọn ô nơi bạn muốn hiển thị kết quả (ví dụ: B1).
2. Nhập công thức trên, thay range bằng phạm vi dữ liệu thực tế, ví dụ: A2:A1000.
3. Nhấn Ctrl + Shift + Enter (đối với Excel 2026 và các phiên bản cũ) để nhập công thức mảng. Excel sẽ tự động bao quanh công thức bằng dấu {} để biểu thị đây là công thức mảng.
4. Kết quả sẽ hiển thị số lượng giá trị duy nhất trong phạm vi đã chọn.

2.3. Ví Dụ Cụ Thể

• Công thức: =SUM(1/COUNTIF(A2:A7, A2:A7)) (nhập mảng)
• Kết quả: 4 (có 4 khách hàng duy nhất: A, B, C, D)

2.4. Lưu Ý

• Nếu phạm vi chứa ô trống (""), chúng cũng sẽ được tính là một giá trị duy nhất. Để loại bỏ ô trống, bạn có thể dùng:

=SUM(IF(A2:A7<>"", 1/COUNTIF(A2:A7, A2:A7), 0))

• Công thức mảng có thể chậm khi làm việc với hàng chục hoặc hàng trăm nghìn dòng. Khi dữ liệu lớn, hãy cân nhắc các phương pháp khác (SUMPRODUCT, Power Query).

3. Sử Dụng SUMPRODUCT Để Đếm Không Trùng Lặp

3.1. Nguyên Lý

SUMPRODUCT cho phép thực hiện các phép tính mảng không cần Ctrl+Shift+Enter. Công thức tương đương với công thức mảng trên:

=SUMPRODUCT(1/COUNTIF(range, range))

3.2. Cách Thực Hiện

Giả sử dữ liệu trong cột A (A2:A1000):

=SUMPRODUCT(1/COUNTIF(A2:A1000, A2:A1000))

• Nhập công thức như bình thường và nhấn Enter. Không cần Ctrl+Shift+Enter.

3.3. Ví Dụ

Với cùng bảng danh sách khách hàng ở trên:

• Công thức: =SUMPRODUCT(1/COUNTIF(A2:A7, A2:A7))
• Kết quả: 4

3.4. Xử Lý Ô Trống

=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7, A2:A7))

Giải thích:
– (A2:A7<>"") trả về mảng TRUE/FALSE (1/0). Khi chia cho COUNTIF, các ô trống sẽ cho kết quả 0 và không ảnh hưởng tới tổng.

3.5. Khi Nào Nên Dùng SUMPRODUCT?

• Khi phiên bản Excel không hỗ trợ công thức mảng (ví dụ Excel 2003) hoặc bạn muốn tránh nhập công thức mảng.
• Khi độ phức tạp vừa phải (hàng chục nghìn) và bạn muốn độ nhanh tốt hơn công thức mảng.

4. Đếm Giá Trị Duy Nhất Với Hàm UNIQUE (Excel 365/2026)

4.1. Giới Thiệu

Excel 365 và Excel 2026 đã bổ sung hàm động mảng UNIQUE, cho phép trích xuất danh sách các giá trị duy nhất một cách tự động và liên tục cập nhật khi dữ liệu thay đổi.

4.2. Công Thức Cơ Bản

=COUNTA(UNIQUE(range))

• UNIQUE(range) trả về một mảng các giá trị duy nhất.
• COUNTA đếm số lượng các phần tử trong mảng đó (bao gồm cả văn bản, số, ngày tháng, nhưng không tính ô trống).

4.3. Ví Dụ Thực Tế

• Công thức: =COUNTA(UNIQUE(A2:A8))
• Kết quả: 4 (P001, P002, P003, P004). Ô trống không được tính.

4.4. Lọc Để Loại Bỏ Giá Trị Rỗng

Nếu muốn chắc chắn không tính ô rỗng, bạn có thể dùng FILTER:

=COUNTA(UNIQUE(FILTER(A2:A8, A2:A8<>"")))

4.5. Khi Nào Dùng UNIQUE?

• Khi bạn đang sử dụng Excel 365 hoặc Excel 2026 trở lên.
• Khi muốn tự động cập nhật danh sách duy nhất khi dữ liệu thay đổi.
• Khi cần kết hợp với các hàm khác (SORT, FILTER) để tạo báo cáo động.

5. Đếm Giá Trị Duy Nhất Với PivotTable

Có thể bạn quan tâm: Cách Sao Chép Dữ Liệu Từ Excel Sang Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

5.1. Lý Do Chọn PivotTable

• Không cần công thức; giao diện kéo thả.
• Dễ dàng phân nhóm, lọc, và tổng hợp các trường khác cùng lúc.
• Tự động cập nhật khi dữ liệu nguồn thay đổi (nếu bạn làm Refresh).

5.2. Các Bước Thực Hiện

1. Chọn dữ liệu (bao gồm tiêu đề cột). Ví dụ: A1:A1000.
2. Vào Insert → PivotTable.
3. Đặt PivotTable ở một vị trí mới hoặc hiện tại.
4. Kéo trường Tên cột (ví dụ “Khách hàng”) vào khu vực Rows.
5. Kéo cùng trường lại vào khu vực Values, mặc định sẽ là Count of ….
6. Kết quả sẽ hiển thị số lần xuất hiện cho mỗi giá trị duy nhất. Để biết tổng số giá trị duy nhất, bạn chỉ cần đếm số hàng trong phần Rows (hoặc sử dụng công thức =GETPIVOTDATA).

5.3. Đếm Tổng Số Giá Trị Duy Nhất Trực Tiếp

Sau khi tạo PivotTable, bạn có thể:

• Thêm một trường tính: Click vào bất kỳ ô nào trong vùng Values → Value Field Settings → Show Values As → % of Grand Total. Nhưng để đếm số hàng, cách nhanh nhất là sử dụng công thức:

=ROWS(PivotTable!A4:A1000)

(giả sử cột A chứa danh sách giá trị duy nhất trong PivotTable).

5.4. Ưu Nhược Điểm

• Ưu điểm: Dễ dùng, không cần công thức, có thể kết hợp nhiều trường.
• Nhược điểm: Không phải là công thức thuần, cần thao tác tạo bảng phụ; không phù hợp khi muốn nhúng trực tiếp trong một công thức tính toán khác.

6. Power Query (Get & Transform) – Giải Pháp Cho Dữ Liệu Lớn

6.1. Khi Nào Nên Dùng Power Query?

• Dữ liệu hàng trăm nghìn – hàng triệu dòng.
• Cần làm sạch, gộp, tách dữ liệu trước khi đếm.
• Muốn tự động hoá quy trình (kéo dữ liệu, làm sạch, đếm, tải lại).

6.2. Các Bước Cơ Bản

1. Select dữ liệu → Data → From Table/Range (hoặc Get Data → From File nếu dữ liệu ở file khác).
2. Power Query Editor mở ra. Chọn cột cần đếm duy nhất.
3. Home → Remove Rows → Remove Duplicates (hoặc Remove Duplicates trực tiếp trên cột).
4. Home → Close & Load → Only Create Connection (nếu bạn không muốn tạo bảng mới) hoặc Load To… → Table để tạo bảng đếm.
5. Đếm số dòng của bảng mới: trong Excel, dùng =ROWS(Table_Unique).

6.3. Ví Dụ Thực Tế

• Dữ liệu bán hàng: 2,5 triệu dòng, cột “Mã Khách Hàng”.
• Power Query loại bỏ trùng lặp, tạo bảng “UniqueCustomers” chứa 150,000 khách hàng duy nhất.
• Công thức =ROWS(UniqueCustomers) trả về 150,000.

6.4. Ưu Điểm

• Hiệu năng tốt với dữ liệu lớn.
• Tự động cập nhật khi nguồn dữ liệu thay đổi (Refresh).
• Không cần công thức phức tạp.

6.5. Nhược Điểm

• Cần học cách sử dụng Power Query (giao diện M).
• Không phù hợp nếu bạn chỉ muốn một công thức nhanh trong một ô.

7. VBA Macro Đếm Giá Trị Duy Nhất

7.1. Khi Nào Dùng VBA?

• Bạn muốn tạo nút bấm để người dùng cuối chỉ cần nhấn và nhận kết quả.
• Cần đếm duy nhất trong nhiều vùng dữ liệu khác nhau trong cùng một workbook.
• Muốn tự động ghi log, tạo báo cáo.

7.2. Mã VBA Mẫu

Function CountUnique(rng As Range) As Long Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Dim cell As Range For Each cell In rng If Len(cell.Value) > 0 Then 'Bỏ qua ô trống If Not dict.Exists(cell.Value) Then dict.Add cell.Value, 1 End If End If Next cell CountUnique = dict.Count
End Function

• Cách sử dụng: Trong ô Excel, nhập =CountUnique(A2:A1000).
• Hàm này sẽ trả về số lượng giá trị duy nhất, không tính ô trống.

7.3. Lưu Ý Khi Dùng VBA

• Cần kích hoạt macro (Trust Center → Enable macros).
• Đối với môi trường công ty có hạn chế macro, có thể không được phép.
• Hàm Scripting.Dictionary yêu cầu thư viện Microsoft Scripting Runtime (được tạo tự động khi dùng CreateObject).

8. So Sánh Hiệu Năng Các Phương Pháp

★: mức độ tốt, ☆: mức độ kém.

9. Các Trường Hợp Thực Tế & Giải Pháp Tối Ưu

9.1. Đếm Số Khách Hàng Độc Nhất Theo Khu Vực

Giả sử bảng:

Bạn muốn đếm số khách hàng duy nhất trong mỗi khu vực.

Giải pháp 1: PivotTable

• Thêm trường “Khu vực” vào Rows, “Khách hàng” vào Rows phía dưới, và Count of Khách hàng vào Values.
• Kéo “Khách hàng” vào Values lần nữa, đổi Value Field Settings thành Distinct Count (có trong Excel 2026+). Kết quả sẽ cho “Distinct Count” cho mỗi khu vực.

Giải pháp 2: Công Thức Mảng (Excel 365)

=LET( data, A2:B7, khu_vuc, UNIQUE(FILTER(data@Khu vực, data@Khu vực<>"")), result, MAP(khu_vuc, LAMBDA(k, COUNTUNIQUE(FILTER(data@Khách hàng, data@Khu vực=k)) )), HSTACK(khu_vuc, result)
)

• Công thức trên sử dụng LET, MAP, FILTER, COUNTUNIQUE (được mô phỏng bằng UNIQUE + COUNTA). Kết quả là một bảng hai cột: Khu vực và số khách hàng duy nhất.

9.2. Đếm Số Mã Sản Phẩm Độc Nhất Khi Có Nhiều Cột

Dữ liệu:

Bạn muốn đếm số mã duy nhất dựa trên sự kết hợp của 2 cột (cùng một mã và mã phụ được xem là một bộ).

Cách 1: Dùng UNIQUE với mảng đa cột

=COUNTA(UNIQUE(A2:B7))

Excel sẽ tự động tạo các cặp giá trị duy nhất.

Cách 2: Dùng SUMPRODUCT với CONCATENATE

=SUMPRODUCT(1/COUNTIF(A2:A7 & "|" & B2:B7, A2:A7 & "|" & B2:B7))

• Kết hợp 2 cột bằng ký tự phân cách (|) để tạo chuỗi duy nhất cho mỗi hàng.

9.3. Loại Bỏ Dòng Trùng Lặp Khi Đếm Dữ Liệu Khi Đã Có Các Ô Rỗng

Nếu dữ liệu chứa nhiều ô trống và bạn muốn bỏ qua chúng, luôn luôn lồng IF hoặc FILTER để lọc ra trước khi đếm.

=SUMPRODUCT((A2:A1000<>"")/COUNTIF(A2:A1000, A2:A1000))

Hoặc trong Excel 365:

=COUNTA(UNIQUE(FILTER(A2:A1000, A2:A1000<>"")))

10. Các Lưu Ý Thường Gặp Khi Đếm Không Trùng Lặp

1. Kiểu Dữ Liệu: Số và văn bản có thể trông giống nhau nhưng khác kiểu (ví dụ, "001" (chuỗi) vs 1 (số)). Đảm bảo đồng nhất kiểu dữ liệu bằng cách dùng TEXT hoặc VALUE nếu cần.
2. Dấu Cách/Space: Các giá trị "ABC" và "ABC " (có space ở cuối) sẽ được tính là khác nhau. Dùng TRIM để loại bỏ khoảng trắng.
3. Phân Biệt HOA/THƯỜNG: Excel mặc định không phân biệt hoa/thường trong COUNTIF. Nếu bạn cần phân biệt, hãy dùng công thức mảng kết hợp EXACT.
   excel =SUMPRODUCT(--(EXACT(A2:A7, A2:A7)))
4. Ngày Tháng: Ngày trong Excel là số nguyên (số serial). Đảm bảo không có định dạng khác nhau gây ra giá trị thực tế khác nhau.
5. Dữ Liệu Động (Dynamic Range): Khi thêm dữ liệu mới, hãy dùng Table (Ctrl+T) để tự động mở rộng phạm vi trong công thức.
6. Hiệu Năng: Khi làm việc với hàng triệu, ưu tiên Power Query, PivotTable, hoặc VBA Dictionary, tránh công thức mảng phức tạp.

11. Tổng Kết

• Đếm không trùng lặp là một nhu cầu thiết yếu trong phân tích dữ liệu.
• Bạn có nhiều công cụ trong Excel: công thức mảng (COUNTIF), SUMPRODUCT, hàm mới UNIQUE, PivotTable, Power Query, và VBA.
• Lựa chọn phương pháp dựa trên:
• Kích thước dữ liệu (hàng nghìn vs hàng triệu).
• Phiên bản Excel (có hỗ trợ hàm động mảng hay không).
• Yêu cầu tự động hoá (công thức nhanh, macro, Power Query).
• Mức độ phức tạp (đơn giản vs kết hợp nhiều tiêu chí).
• Thực hành: Hãy thử trên tập dữ liệu thực tế của bạn, so sánh thời gian tính và độ chính xác, rồi chọn giải pháp tối ưu.

Hy vọng bài viết này đã cung cấp toàn bộ kiến thức cần thiết để bạn đếm số giá trị duy nhất trong Excel một cách chắc chắn, nhanh chóng, và hiệu quả. Chúc bạn thành công trong mọi dự án dữ liệu!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.