image

Trong bài viết này, chúng ta sẽ tìm hiểu về cách tra cứu các giá trị còn thiếu trong bảng trong Excel bằng công thức hiển thị trong hình trên.

Tình huống:

Ví dụ: chúng tôi có một danh sách các giá trị và chúng tôi cần xác nhận về vị trí của các tên được yêu cầu trong danh sách. Giả sử chúng tôi muốn đếm tên của các VIP bị thiếu (VIP_names) trong danh sách khách.

Đối với điều này, chúng tôi sẽ xem xét tình hình và đề xuất một số công thức để làm điều tương tự. Chúng ta sẽ sử dụng các thao tác hàm khác nhau tùy thuộc vào mức độ dễ dàng của table_array.

Các hàm được sử dụng và công thức Cú pháp:

Chức năng sau sẽ được sử dụng. Vì vậy, hãy tìm hiểu một chút về, trước khi sử dụng nó.

Chúng tôi sẽ xây dựng một công thức từ nó. Đầu tiên, hàm COUNTIF nhận từng giá trị một trong phạm vi tiêu chí và kiểm tra nó trong phạm vi được cung cấp. Hàm SUMPRODUCT trả về tổng các giá trị TRUE (là 1) và FALSE (là 0) trong mảng.

Công thức Cú pháp:

=SUMPRODUCT(--(COUNTIF(long_list,required)=0))

-: toán tử dùng để chuyển giá trị TRUE thành giá trị FALSE. Có thể nói là hàm NOT dùng cho một mảng.

long_list: danh sách tra cứu cần có: danh sách cần thiết

Ví dụ:

Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới. Ở đây chúng tôi có dữ liệu có Giá của các sản phẩm nhận được vào các ngày. Ở đây chúng ta cần tìm số lượng các mặt hàng cho tất cả các mặt hàng thiết yếu. Vì vậy, chúng tôi đã gán 2 danh sách là danh sách đã nhận và các yếu tố cần thiết trong một cột cho công thức. Bây giờ chúng ta sẽ sử dụng công thức dưới đây để tính số lượng sản phẩm.

Sử dụng công thức:

=SUMPRODUCT(--(COUNTIF(C3:C16,E3:E12)=0))

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

  1. Hàm COUNTIF đếm từng mục hoặc ô trong E3: E12 (các mục cần thiết)

trong danh sách đã nhận (C3: C16).

  1. = 0 trích xuất danh sách các yếu tố cần thiết trong mảng. . – toán tử trả về phủ định của mảng, tức là các giá trị TRUE thành giá trị FALSE.

  2. SUMPRODUCT tìm tổng sẽ là số lượng các yếu tố cần thiết không có trong danh sách đã nhận.

image

Ở đây, mảng đối số của hàm được đưa ra dưới dạng tham chiếu ô.

image

Như bạn có thể thấy công thức trả về 3 là số lượng các mặt hàng thiết yếu không nhận được. Trong danh sách 14 vật phẩm nhận được, vẫn có 3 vật phẩm không có trong đó.

Nếu – (toán tử phủ định không được sử dụng)

Chắc hẳn bạn đang thắc mắc toán tử phủ định (-) này làm gì. Hãy sử dụng công thức trên mà không sử dụng ký tự phủ định (-).

Cú pháp của công thức:

=SUMPRODUCT((COUNTIF(C3:C16,E3:E12)=0))

Công thức này trả về 7 là số lượng vật phẩm cần thiết trong các vật phẩm đã nhận. Công thức này chỉ hoạt động khi không có mục nào trùng lặp trong cả hai danh sách. Kiểm tra các bản sao trước khi sử dụng công thức.

Như bạn có thể thấy từ công thức trên trả về Đếm các giá trị bị thiếu trong danh sách. Dưới đây là một số kết quả quan sát sử dụng công thức trên.

Ghi chú:

  1. Sử dụng các tham chiếu ô đến các ô chính xác nếu không kết quả sẽ bị thay đổi.

  2. Hàm COUNTIF hỗ trợ Ký tự đại diện (,?) Giúp trích xuất các giá trị có cụm từ.

  3. (-) toán tử phủ định các dấu hiệu 1s đến 0s và 0s đến 1s.

  4. Đối số mảng cho hàm có thể được cung cấp dưới dạng tham chiếu ô hoặc phạm vi được đặt tên.

  5. Bạn có thể tùy chỉnh các công thức này theo yêu cầu bằng cách sử dụng hàm excel khác.

  6. Hàm trả về tổng các giá trị thỏa mãn tất cả các điều kiện.

Hy vọng bài viết này về Cách đếm giá trị bị thiếu trong danh sách trong Excel là giải thích. Tìm thêm các bài viết về công thức COUNTIF tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với những người bạn thân của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến ​​của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].

Các bài viết liên quan

Bài viết phổ biến

Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.