Chia sẻ công thức Excel lấy ra danh sách số còn thiếu trong dãy số liên tục

 Một số trường hợp cần tìm ra số còn thiếu trong dãy số liên tục như sau:

- Danh sách số hóa đơn cần tìm ra số bị khuyết

- Đánh mã nhân sự, đối tượng theo danh sách tăng dần và cần tìm ra số thứ tự bị khuyết

-...

Tình huống giả lập tìm số hóa đơn khuyết: Danh sách từ A2:A15 và trong đó có những số hóa đơn bị khuyết

Bước 1: Tìm số hóa đơn nhỏ nhất và lớn nhất trong dải số

- Số hóa đơn nhỏ nhất: 

=AGGREGATE(15,6,--$A$2:$A$15,1)

- Số hóa đơn lớn nhất:

=AGGREGATE(14,6,--$A$2:$A$15,1)

Bước 2: Tạo mảng danh sách số liên tục căn cứ vào số nhỏ nhất và lớn nhất

=ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1)))

Bước 3: Dùng hàm MATCH tham chiếu theo mảng liên tục đã tạo

=MATCH(ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1))),--A2:A15,0)

Bước 4: Đưa hàm AGGREGATE để lấy ra danh sách các số. Công thức tại ô B2 và kéo xuống cho các ô còn lại để ra danh sách số hóa đơn khuyết

=AGGREGATE(15,6,ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1)))/ISNA(MATCH(ROW(INDIRECT(AGGREGATE(15,6,--$A$2:$A$15,1) & ":"& AGGREGATE(14,6,--$A$2:$A$15,1))),--$A$2:$A$15,0)),ROW(1:1))

Với Office 365 thì công thức thay thế sẽ ngắn gọn hơn:

=LET(x,--$A$2:$A$15, y,ROW(INDIRECT(AGGREGATE(15,6,x,1) & ":"& AGGREGATE(14,6,x,1))),FILTER(y,ISNA(MATCH(y,x,0))))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334