Đếm các giá trị số duy nhất dựa trên tiêu chí trong Excel
Trong trang tính Excel, bạn có thể gặp phải sự cố đếm số lượng giá trị số duy nhất dựa trên một điều kiện cụ thể. Ví dụ: làm cách nào để tôi có thể đếm các giá trị Qty duy nhất của sản phẩm “áo phông” từ báo cáo như hình minh họa bên dưới? Trong bài viết này, tôi sẽ hiển thị một số công thức để đạt được nhiệm vụ này trong Excel.
- Đếm các giá trị số duy nhất dựa trên tiêu chí trong Excel 2019, 2016 trở về trước
- Đếm các giá trị số duy nhất dựa trên tiêu chí trong Excel 365
Đếm các giá trị số duy nhất dựa trên tiêu chí trong Excel 2019, 2016 trở về trước
Trong Excel 2019 và các phiên bản trước đó, bạn có thể kết hợp các hàm SUM, FREQUENCY và IF để tạo công thức đếm các giá trị duy nhất dựa trên tiêu chí, cú pháp chung là:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Phạm vi ô phù hợp với tiêu chí bạn đã chỉ định;
- criteria: Điều kiện mà bạn muốn đếm các giá trị duy nhất dựa trên;
- range: Phạm vi của các ô có giá trị duy nhất được đếm.
Vui lòng áp dụng công thức dưới đây vào một ô trống và nhấn Ctrl + Shift + Enter phím để có được kết quả chính xác, xem ảnh chụp màn hình:
Giải thích công thức:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Hàm IF này trả về giá trị trong cột C nếu sản phẩm trong cột A là “áo phông”, kết quả là một mảng như sau: {FALSE; 300; 500; FALSE; 400; FALSE; 300; FALSE; FALSE; SAI; 350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Hàm FREQUENCE được sử dụng để đếm từng giá trị số trong danh sách mảng và trả về kết quả như sau: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Kiểm tra xem mỗi giá trị trong mảng có lớn hơn 0 hay không và nhận được kết quả như sau: {FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}. Và sau đó, dấu âm kép chuyển đổi TRUE và FALSE thành 1 và 0, trả về một mảng như sau: {0; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Cuối cùng, sử dụng hàm SUM để cộng các giá trị này lên và nhận tổng số: 4.
Lời khuyên:
Nếu bạn muốn đếm các giá trị duy nhất dựa trên nhiều hơn một điều kiện, bạn chỉ cần thêm các tiêu chí khác vào công thức với ký tự *:
Đếm các giá trị số duy nhất dựa trên tiêu chí trong Excel 365
Trong Excel 365, sự kết hợp của các hàm ROWS, UNIQUE và FILTER có thể giúp đếm các giá trị số duy nhất dựa trên tiêu chí, cú pháp chung là:
- range: Phạm vi của các ô có giá trị duy nhất được đếm.
- criteria_range: Phạm vi ô phù hợp với tiêu chí bạn đã chỉ định;
- criteria: Điều kiện mà bạn muốn đếm các giá trị duy nhất dựa trên;
Vui lòng sao chép hoặc nhập công thức sau vào một ô và nhấn đăng ký hạng mục thi phím để trả về kết quả, xem ảnh chụp màn hình:
Giải thích công thức:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2: A12 = E2: Biểu thức này kiểm tra xem giá trị trong ô E2 có tồn tại trong phạm vi A2: A12 hay không và nhận được kết quả sau: {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}.
- FILTER(C2:C12,A2:A12=E2): Hàm FREQUENCE được sử dụng để đếm từng giá trị số trong danh sách mảng và trả về kết quả như sau: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Ở đây, hàm UNIQUE được sử dụng để trích xuất các giá trị duy nhất từ mảng danh sách để nhận được kết quả sau: {300; 500; 400; 350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Hàm ROWS trả về số hàng dựa trên phạm vi ô hoặc mảng, do đó, kết quả là: 4.
Lời khuyên:
1. Nếu giá trị phù hợp không tồn tại trong phạm vi dữ liệu, bạn sẽ nhận được giá trị lỗi, để thay thế giá trị lỗi bằng 0, vui lòng áp dụng công thức sau:
2. Để đếm các giá trị duy nhất dựa trên nhiều hơn một điều kiện, bạn chỉ cần thêm các tiêu chí khác vào công thức với ký tự * như sau:
Hàm tương đối được sử dụng:
- TỔNG:
- Hàm SUM trong Excel trả về tổng giá trị được cung cấp.
- TẦN SỐ:
- Hàm FREQUENCY tính toán tần suất xuất hiện của các giá trị trong một phạm vi giá trị, sau đó trả về một dãy số theo chiều dọc.
- HÀNG:
- Hàm ROWS trả về số hàng trong một tham chiếu hoặc mảng nhất định.
- DUY NHẤT:
- Hàm UNIQUE trả về danh sách các giá trị duy nhất trong danh sách hoặc phạm vi.
- LỌC:
- Hàm FILTER giúp lọc một loạt dữ liệu dựa trên tiêu chí bạn xác định.
Các bài viết khác:
- Đếm giá trị số hoặc ngày duy nhất trong một cột
- Giả sử bạn có một danh sách các số chứa một số trùng lặp, bây giờ, bạn muốn đếm số lượng các giá trị duy nhất hoặc các giá trị chỉ xuất hiện một lần trong danh sách như hình minh họa bên dưới. Trong bài viết này, chúng tôi sẽ nói về một số công thức hữu ích để giải quyết công việc này trong Excel một cách nhanh chóng và dễ dàng.
- Đếm tất cả các kết quả phù hợp / trùng lặp giữa hai cột
- So sánh hai cột dữ liệu và đếm tất cả các kết quả trùng khớp hoặc trùng lặp trong hai cột có thể là một nhiệm vụ phổ biến đối với hầu hết chúng ta. Ví dụ: bạn có hai cột tên, một số tên xuất hiện ở cả cột đầu tiên và cột thứ hai, bây giờ, bạn muốn đếm tất cả các tên phù hợp (các tên trùng khớp nằm ở bất kỳ vị trí nào trong hai cột) giữa hai cột như hình minh họa bên dưới, hướng dẫn này sẽ giới thiệu một số công thức để đạt được mục tiêu này trong Excel.
- Đếm số ô bằng một trong nhiều giá trị
- Giả sử, tôi có một danh sách các sản phẩm trong cột A, bây giờ, tôi muốn lấy tổng số các sản phẩm cụ thể Táo, Nho và Chanh được liệt kê trong phạm vi C4: C6 từ cột A như ảnh chụp màn hình bên dưới. Thông thường, trong Excel, các hàm COUNTIF và COUNTIFS đơn giản sẽ không hoạt động trong trường hợp này. Bài viết này, tôi sẽ nói về cách giải quyết công việc này một cách nhanh chóng và dễ dàng với sự kết hợp của hàm SUMPRODUCT và COUNTIF.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
