✅ Liệt kê các số bị bỏ qua trong trình tự - Excel & Google Trang tính

Mục lục

Liệt kê các số bị bỏ qua trong trình tự
Công thức hoạt động như thế nào?
Liệt kê các số bị bỏ qua theo trình tự trong Google Trang tính

Tải xuống Sổ làm việc Ví dụ

Tải xuống sổ làm việc mẫu

Hướng dẫn này sẽ trình bày cách liệt kê các số bị thiếu trong một phạm vi.

Liệt kê các số bị bỏ qua trong trình tự

Để tìm các số còn thiếu trong danh sách, chúng ta có thể sử dụng công thức sau:

1	= NHỎ (IF (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6)) = 0, ROW ($ 1: $ 6), ""), ROW (B1))

Ghi chú:

Đây là một công thức mảng! Khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER. Bạn sẽ biết công thức đã được nhập đúng cách khi bạn nhìn thấy dấu ngoặc nhọn xung quanh công thức.
Công thức này chỉ hoạt động với các số nguyên dương (số nguyên).

Công thức hoạt động như thế nào?

Đầu tiên, Hàm ROW được sử dụng để tạo một mảng số:

1	{1;2;3;4;5;6}

Trên thực tế, bạn thậm chí có thể nhập vào dãy số này theo cách thủ công:

1	= NHỎ (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1))

Tiếp theo, Hàm COUNTIF sẽ đếm các giá trị “nếu” chúng khớp với một giá trị được tìm thấy trong mảng.

Để hình dung điều này, trước tiên chúng ta sẽ chuyển đổi phạm vi $ B $ 3: $ B $ 8 thành mảng giá trị của nó {3; 2; 0; 6; 0; 5}:

1	= NHỎ (IF (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1))

Tiếp theo, chúng ta sẽ thực hiện thao tác COUNTIF:

1	= NHỎ (IF ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1))

Nếu không tìm thấy giá trị 0 sẽ được xuất ra. Hàm IF kiểm tra xem giá trị KHÔNG được tìm thấy và nếu có, trả về giá trị từ mảng, nếu không, nó sẽ xuất ra giá trị trống: