Bỏ qua nội dung chính

Đế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ị còn thiếu 1

Đế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: Mô hình 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)))

đếm các giá trị còn thiếu 2

Giải thích công thức

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

  • TRẬN ĐẤU (F6: F8, B6: B10,0): Mô hình 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: Mô hình 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))

đếm các giá trị còn thiếu 3

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

Hàm SUMPRODUCT trong Excel

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

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 trong Excel

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

Tìm các giá trị còn thiếu

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.

Đếm ô bằng

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

Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao  |  Xóa hàng trống  |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu  |  Vòng không có công thức hữu ích. Cảm ơn !
Super VLookup: Nhiều tiêu chí  |  Nhiều giá trị  |  Trên nhiều trang tính  |  Tra cứu mờhữu ích. Cảm ơn !
Khuyến cáo. Danh sách thả xuống: Danh sách thả xuống dễ dàng  |  Danh sách thả xuống phụ thuộc  |  Danh sách thả xuống nhiều lựa chọnhữu ích. Cảm ơn !
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  So sánh các cột với Chọn các ô giống nhau và khác nhau hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới  |  Chế độ xem thiết kế  |  Thanh công thức lớn  |  Trình quản lý sổ làm việc & trang tính | Thư viện tài nguyên (Văn bản tự động)  |  Bảng chọn ngày  |  Kết hợp các bảng tính  |  Mã hóa/Giải mã ô  |  Gửi email theo danh sách  |  Siêu lọc  |  Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự ...)  |  50 + Biểu đồ Các loại (Biểu đồ Gantt ...)  |  40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh ...)  |  19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn ...)  |  12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ ...)  |  7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Tách ô Excel ...)  |  ... và nhiều hơn nữa

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations