Marcya có một danh sách dài các số được sắp xếp trong cột A của một trang tính.

Những con số này được cho là liên tiếp, nhưng cô ấy không biết điều đó có đúng không. Việc kiểm tra danh sách theo cách thủ công vừa tẻ nhạt vừa dễ xảy ra lỗi, vì vậy Marcya tự hỏi liệu có cách nào đó để làm nổi bật bất kỳ “số bị thiếu” nào (những số không liên tiếp với số trước đó) hoặc để tạo danh sách các số bị bỏ sót trong danh sách.

Có một số cách bạn có thể thực hiện để tìm ra nơi nào còn thiếu số. Đầu tiên là một trong những tôi sử dụng khá thường xuyên: Tôi thêm một cột trợ giúp bên cạnh cột A. Giả sử các số của bạn bắt đầu từ ô A1, tôi đặt nó vào ô B2:

=IF(A2<>A1+1,"Error","")

Sao chép công thức xuống nhiều ô nếu cần và bạn sẽ dễ dàng nhìn thấy từ “Lỗi” bên cạnh bất kỳ giá trị nào không liên tiếp với giá trị ngay phía trên nó. Nếu bạn muốn biết thêm một chút về lỗi, bạn có thể sử dụng công thức chi tiết hơn:

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

Một cách tiếp cận khác là sử dụng định dạng có điều kiện trên các ô trong cột A. Thực hiện theo các bước sau, một lần nữa giả sử rằng các giá trị của bạn bắt đầu trong ô A1:

  1. Chọn dải ô A2 đến giá trị cuối cùng trong cột A.

  2. Với tab Trang đầu của ruy-băng được hiển thị, hãy bấm vào tùy chọn Định dạng có Điều kiện trong nhóm Kiểu. Excel hiển thị một bảng các tùy chọn liên quan đến định dạng có điều kiện.

  3. Chọn Quy tắc ô tô sáng và sau đó chọn Quy tắc khác từ menu con kết quả. Excel sẽ hiển thị hộp thoại Quy tắc Định dạng Mới.

(Xem Hình 1.)

  1. Trong khu vực Chọn loại quy tắc ở đầu hộp thoại, hãy chọn Sử dụng công thức để xác định ô cần định dạng.

  2. Trong hộp Giá trị Định dạng Nơi Công thức Này Đúng, hãy nhập công thức này: = A2 <> A1 + 1. Bấm Định dạng để hiển thị hộp thoại Định dạng Ô.

  3. Sử dụng các điều khiển trong hộp thoại, chỉ định một định dạng mà bạn muốn sử dụng để đánh dấu các ô không liên tiếp.

  4. Bấm OK để loại bỏ hộp thoại Định dạng Ô. Định dạng bạn đã chỉ định trong bước 7 bây giờ sẽ xuất hiện trong khu vực xem trước cho quy tắc.

  5. Bấm OK.

Cuối cùng, nếu bạn muốn biên dịch danh sách các số bị thiếu trong một chuỗi liên tiếp, bạn có thể sử dụng công thức mảng. Đặt phần sau vào hàng 1 của cột trống:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135, MIN($A$1:$A$135)+ROW($1:$135)-1)=0,  MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

Hãy nhớ rằng đây là một công thức mảng duy nhất, vì vậy bạn cần nhập nó dưới dạng một dòng duy nhất bằng cách sử dụng Ctrl + Shift + Enter. Sau đó, bạn có thể sao chép công thức xuống một số ô, cho đến khi nó không trả về bất kỳ giá trị nào nữa. Ngoài ra, công thức giả định rằng chuỗi của bạn trong phạm vi A1: A135; nếu không, bạn sẽ cần sửa đổi công thức để phản ánh phạm vi thực tế.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (4315) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.