Đếm số ô không bằng nhiều giá trị trong Excel
Trong Excel, bạn có thể dễ dàng nhận được số ô không bằng một giá trị cụ thể bằng cách sử dụng hàm COUNTIF, nhưng bạn đã bao giờ thử đếm các ô không bằng nhiều giá trị chưa? Ví dụ: tôi muốn lấy tổng số sản phẩm trong cột A nhưng loại trừ các mục cụ thể trong C4: C6 như ảnh chụp màn hình bên dưới. Bài viết này, tôi sẽ giới thiệu một số công thức để giải quyết công việc này trong Excel.
Đếm số ô không bằng nhiều giá trị với công thức 1
Để đếm các ô không bằng nhiều giá trị được chỉ định, bạn có thể sử dụng kết hợp các hàm MATCH, ISNA và SUMPRODUCT, cú pháp chung là:
- range: Danh sách các ô bạn muốn đếm;
- exclude_values: Các giá trị cần bỏ qua khỏi danh sách phạm vi.
1. Nhập hoặc sao chép công thức dưới đây vào một ô trống mà bạn muốn xuất kết quả:
2. Sau đó nhấn đăng ký hạng mục thi để 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:
- TRẬN ĐẤU (A2: A13, C2: C4,0): Hàm MATCH này trả về vị trí của từng giá trị phù hợp dưới dạng số và trả về giá trị lỗi # N / A nếu không tìm thấy. Có nghĩa là, khi các giá trị trong A2: A13 bằng với các giá trị trong C2: C4, nó sẽ trả về một số, ngược lại, nó sẽ trả về lỗi #NA. Ở đây kết quả là: {1; # N / A; # N / A; 1; 3; 2; # N / A; # N / A; 3; # N / A; # N / A; # Không / A;}.
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Hàm ISNA này buộc các giá trị lỗi trở thành TRUE và các số trở thành FASLE, vì vậy bạn sẽ nhận được điều này: {FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE} .
- - (ISNA (MATCH (A2: A13, C2: C4,0)))) = - ({FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}): Toán tử - phủ định kép chuyển đổi tất cả TRUE thành 1 và FALSE thành 0 như sau: SUMPRODUCT (- (ISNA (MATCH (A2: A13, C2: C4,0)))) = SUMPRODUCT ({0; 1; 1 ; 0; 0; 0; 1; 1; 0; 1; 1; 1;}).
- Và khi đó hàm SUMPRODUCT dùng để tính tổng các số trong mảng và sẽ nhận được kết quả: 7.
Đếm số ô không bằng nhiều giá trị với công thức 2
Để giải quyết công việc này trong Excel, bạn cũng có thể áp dụng kết hợp các hàm COUNTA, SUMPRODUCT và COUNTIF, cú pháp chung là:
- range: Danh sách các ô bạn muốn đếm;
- exclude_values: Các giá trị cần bỏ qua khỏi danh sách phạm vi.
1. Vui lòng nhập hoặc sao chép công thức dưới đây vào một ô trống để đặt kết quả:
2. Sau đó nhấn đăng ký hạng mục thi để nhận được kết quả như hình ảnh chụp màn hình sau:
Giải thích công thức:
- COUNTA (A2: A13): Hàm COUNTA này trả về số lượng tất cả các ô không trống trong phạm vi A2: A13. Và nó sẽ nhận được kết quả: 12.
- COUNTIF (A2: A13, C2: C4): Hàm COUNTIF này đếm các ô trong phạm vi A2: A13 bằng với tiêu chí trong C2: C4, do đó, bạn sẽ nhận được kết quả này: {2; 1; 2} có nghĩa là Apple xuất hiện hai lần, Nho xuất hiện một lần và Chanh xuất hiện hai lần.
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): Hàm SUMPRODUCT này tính tổng tất cả các mục trong mảng và trả về kết quả cuối cùng: 5.
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 : Kết quả (5) của hàm SUMPRODUCT được trừ khỏi tổng số tất cả các ô không trống (12) để có kết quả cuối cùng mà bạn cần.
Lưu ý: Cả hai công thức trên đều không phân biệt chữ hoa chữ thường.
Hàm tương đối được sử dụng:
- Đội hình thi đấu:
- Hàm MATCH tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về vị trí tương đối của giá trị này.
- ISNA:
- Hàm ISNA được sử dụng để kiểm tra xem một công thức có trả về lỗi # N / A hay không.
- SUMPRODUCT:
- Hàm SUMPRODUCT có thể được sử dụng để nhân hai hoặc nhiều cột hoặc mảng với nhau, sau đó lấy tổng các tích.
- COUNTIF:
- Hàm COUNTIF là một hàm thống kê trong Excel được sử dụng để đếm số ô đáp ứng một tiêu chí.
- COUNTA:
- Hàm COUNTA trả về số ô trừ các ô trống.
Các bài viết khác:
- Đếm số ô bằng một trong nhiều giá trị trong Excel
- 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.
- Đếm số ô giữa hai giá trị / ngày trong Excel
- Bạn đã bao giờ cố gắng lấy hoặc đếm số ô giữa hai số hoặc ngày đã cho trong Excel như hình minh họa dưới đây chưa? Bài viết này sẽ nói về một số công thức hữu ích để đối phó với nó.
- Đếm số ô không nằm giữa hai số nhất định trong Excel
- Để đếm số ô giữa hai số là một nhiệm vụ phổ biến đối với chúng tôi trong Excel, nhưng, trong một số trường hợp nhất định, bạn có thể muốn đếm các ô không nằm giữa hai số nhất định. Ví dụ: tôi có một danh sách sản phẩm với doanh số bán hàng từ Thứ Hai đến Chủ Nhật, bây giờ, tôi cần lấy số ô không nằm giữa số thấp và cao cụ thể như ảnh chụp màn hình bên dưới. Bài viết này, tôi sẽ giới thiệu một số công thức để xử lý tác vụ này trong Excel.
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.
