Đếm các giá trị còn thiếu
Giả sử bạn có hai danh sách, để đếm tổng số giá trị trong một danh sách mà không tồn tại trong danh sách khác, bạn có thể sử dụng SUMPRODUCT công thức với sự giúp đỡ của Đội hình thi đấu và các chức năng ISNA, hoặc COUNTIF chức năng.
Đếm các giá trị bị thiếu với SUMPRODUCT, MATCH và ISNA
Đếm các giá trị bị thiếu bằng SUMPRODUCT và COUNTIF
Đếm các giá trị bị thiếu với SUMPRODUCT, MATCH và ISNA
Để đếm tổng số giá trị trong danh sách B bị thiếu trong danh sách A như được hiển thị ở trên, trước tiên bạn có thể sử dụng hàm MATCH để trả về một mảng vị trí tương đối của các giá trị từ danh sách B trong danh sách A. Nếu một giá trị không tồn tại trong danh sách A, lỗi # N / A sẽ được trả về. Sau đó, hàm ISNA sẽ xác định các lỗi # N / A và SUMPRODUCT sẽ đếm tổng số lỗi.
Cú pháp chung
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- phạm vi_to_count: Phạm vi để đếm các giá trị bị thiếu. Ở đây đề cập đến danh sách B.
- phạm vi tra cứu: Phạm vi để so sánh với phạm vi_to_count. Ở đây đề cập đến danh sách A.
- 0: Sản phẩm trận đấu_type 0 buộc MATCH thực hiện một kết hợp chính xác.
Để đếm tổng số giá trị trong danh sách B bị thiếu trong danh sách A, vui lòng sao chép hoặc nhập công thức bên dưới vào ô H6 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= SUMPRODUCT (- ISNA (MATCH (F6: F8,B6: B10, 0)))
Giải thích công thức
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- TRẬN ĐẤU (F6: F8, B6: B10,0): Sản phẩm trận đấu_type 0 buộc hàm MATCH trả về các giá trị số cho biết vị trí tương đối của các giá trị thông qua các ô F6 đến F8 trong phạm vi B6: B10. Nếu một giá trị không tồn tại trong danh sách A, lỗi # N / A sẽ được trả về. Vì vậy, kết quả sẽ nằm trong một mảng như thế này: {2; 3; # N / A}.
- ISNA (TRẬN ĐẤU (F6: F8, B6: B10,0)) = ISNA ({2; 3; # N / A}): ISNA hoạt động để tìm hiểu xem một giá trị có phải là lỗi “# N / A” hay không. Nếu có, hàm sẽ trả về một TURE; Nếu không, nó sẽ trả về FALSE. Vì vậy, công thức ISNA sẽ trả về {FALSE; FALSE; TRUE}.
- SUMPRODUCT (-ISNA (TRẬN ĐẤU (F6: F8, B6: B10,0))) = SUMPRODUCT (-{FALSE; FALSE; TRUE}): Dấu âm kép chuyển đổi TRUE thành 1s và FALSEs thành 0s: {0; 1; 0}. Sau đó, hàm SUMPRODUCT trả về tổng: 1.
Đếm các giá trị bị thiếu bằng SUMPRODUCT và COUNTIF
Để đếm tổng số giá trị trong danh sách B bị thiếu trong danh sách A, bạn cũng có thể sử dụng hàm COUNTIF để biết một giá trị có tồn tại trong danh sách A hay không với điều kiện “= 0”, vì số 0 sẽ tạo ra nếu một giá trị bị thiếu. SUMPRODUCT sau đó sẽ đếm tổng số giá trị bị thiếu.
Cú pháp chung
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- phạm vi tra cứu: Phạm vi để so sánh với phạm vi_to_count. Ở đây đề cập đến danh sách A.
- phạm vi_to_count: Phạm vi để đếm các giá trị bị thiếu. Ở đây đề cập đến danh sách B.
- 0: Sản phẩm trận đấu_type 0 buộc MATCH thực hiện một kết hợp chính xác.
Để đếm tổng số giá trị trong danh sách B bị thiếu trong danh sách A, vui lòng sao chép hoặc nhập công thức bên dưới vào ô H6 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= SUMPRODUCT (- (COUNTIF (B6: B10,F6: F8) = 0))
Giải thích công thức
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF (B6: B10, F6: F8): Hàm COUNTIF đếm số lần xuất hiện của các ô qua F6 đến F8 trong phạm vi B6: B10. Kết quả sẽ là một mảng như thế này: {1; 1; 0}.
- - (COUNTIF (B6: B10, F6: F8)= 0) = - ({1; 1; 0}= 0): Đoạn mã {1; 1; 0} = 0 mang lại một mảng TRUE và FALSE {FALSE; FALSE; TRUE}. Sau đó, dấu âm kép chuyển TRUEs thành 1s và FALSEs thành 0s. Mảng cuối cùng là như thế này: {0; 0; 1}.
- GIỚI THIỆU (- (COUNTIF (B6: B10, F6: F8)= 0)) = SUMPRODUCT ({0; 0; 1}): Sau đó hàm SUMPRODUCT trả về tổng: 1.
Các chức năng liên quan
Trong Excel, 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ủa các sản phẩm. Trên thực tế, hàm SUMPRODUCT là một hàm hữu ích có thể giúp đếm hoặc tính tổng các giá trị ô với nhiều tiêu chí như hàm COUNTIFS hoặc SUMIFS. Bài viết này sẽ giới thiệu cú pháp hàm và một số ví dụ cho hàm SUMPRODUCT này.
Hàm MATCH trong Excel 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ị.
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í. Nó hỗ trợ các toán tử logic (<>, =,>, và <), và các ký tự đại diện (? Và *) để đối sánh từng phần.
Công thức liên quan
Có những trường hợp khi bạn cần so sánh hai danh sách để kiểm tra xem một giá trị của danh sách A có tồn tại trong danh sách B trong Excel hay không. Ví dụ: bạn có một danh sách các sản phẩm và bạn muốn kiểm tra xem các sản phẩm trong danh sách của mình có tồn tại trong danh sách sản phẩm do nhà cung cấp của bạn cung cấp hay không. Để hoàn thành nhiệm vụ này, chúng tôi liệt kê ba cách dưới đây, hãy chọn cách bạn thích.
Bài viết này sẽ tập trung vào các công thức Excel để đếm các ô chính xác bằng một chuỗi văn bản bạn chỉ định hoặc một phần bằng chuỗi văn bản đã cho như ảnh chụp màn hình bên dưới. Đầu tiên, nó sẽ giải thích cú pháp công thức và đối số, cung cấp các ví dụ để bạn hiểu rõ hơn.
Đếm số ô không nằm giữa hai số đã cho
Để đế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.
