Note: The other languages of the website are Google-translated. Back to English

Đế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)))

  • range_to_count: Phạm vi để đếm các giá trị bị thiếu. Ở đây đề cập đến danh sách B.
  • lookup_range: Phạm vi để so sánh với range_to_count. Ở đây đề cập đến danh sách A.
  • 0: Mô hình match_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 Đi vào để 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 match_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))

  • lookup_range: Phạm vi để so sánh với range_to_count. Ở đây đề cập đến danh sách A.
  • range_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 match_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 Đi vào để 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

Bạn có muốn hoàn thành công việc hàng ngày của mình một cách nhanh chóng và hoàn hảo? Kutools cho Excel mang đến 300 tính năng nâng cao mạnh mẽ (Kết hợp sổ làm việc, tính tổng theo màu, chia nhỏ nội dung ô, chuyển đổi ngày tháng, v.v.) và tiết kiệm 80% thời gian cho bạn.

  • Được thiết kế cho 1500 tình huống công việc, giúp bạn giải quyết 80% các vấn đề về Excel.
  • Giảm hàng nghìn lần nhấp chuột và bàn phím mỗi ngày, giảm mỏi mắt và mỏi tay.
  • Trở thành chuyên gia Excel trong 3 phút. Không còn cần phải nhớ bất kỳ công thức và mã VBA đau đớn nào.
  • 30 ngày dùng thử miễn phí không giới hạn. Đảm bảo hoàn tiền trong 60 ngày. Nâng cấp miễn phí và hỗ trợ trong 2 năm.
Ribbon của Excel (với Kutools cho Excel được cài đặ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 lại các tab hiệu quả cho Office (bao gồm Excel), Giống như Chrome, Firefox và Internet Explorer mới.
Ảnh chụp màn hình của Excel (với Office Tab được cài đặt)
Nhận xét (0)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất