Tìm số nguyên dương không tồn tại trong dãy số

[Miccpro]

Xin chào các chị trên GPE!
Em có số dãy trong ô A2:A11 là:
1, 3, 6, 7, 12, 13, 14, 15, 17, 18
Em muốn tạo hàm trả về các số không theo dãy số trên, kết quả là: 2, 4, 5, 6, 8, 9, 10, 11, 16
Cụ thể như file excel đính kèm
Mong các anh chị giúp đỡ, em xin cảm ơn!

 

[VuVanHao]

@Miccpro Có hàm udf tham khảo trên diễn đàn. Chỉ tạo ra được chuỗi chứ chưa đưa vào từng ô 1 như ý bạn. Bạn tham khảo
=SoThieu(A2:A11)

Function SoThieu(Rng As Range) As String
    Dim X As Long, SoToiDa As Long
    SoToiDa = WorksheetFunction.Max(Rng)
    ReDim Nums(1 To SoToiDa)
    For X = 1 To SoToiDa
        If Rng.Find(X, LookAt:=xlWhole) Is Nothing Then
            SoThieu = SoThieu & ", " & X
        End If
    Next
    SoThieu = Mid(SoThieu, 3)
End Function

 

[Miccpro]

@Miccpro Có hàm udf tham khảo trên diễn đàn. Chỉ tạo ra được chuỗi chứ chưa đưa vào từng ô 1 như ý bạn. Bạn tham khảo
=SoThieu(A2:A11)

Function SoThieu(Rng As Range) As String
    Dim X As Long, SoToiDa As Long
    SoToiDa = WorksheetFunction.Max(Rng)
    ReDim Nums(1 To SoToiDa)
    For X = 1 To SoToiDa
        If Rng.Find(X, LookAt:=xlWhole) Is Nothing Then
            SoThieu = SoThieu & ", " & X
        End If
    Next
    SoThieu = Mid(SoThieu, 3)
End Function

Cảm ơn anh, em đang muốn đưa vào từng ô ạ

 

[befaint]

E5=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$100)/(0=COUNTIF($A$2:$A$11,ROW(INDIRECT(MIN($A$2:$A$11)&":"&MAX($A$2:$A$11))))),ROW($A1)),"")

 

[Miccpro]

E5=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$100)/(0=COUNTIF($A$2:$A$11,ROW(INDIRECT(MIN($A$2:$A$11)&":"&MAX($A$2:$A$11))))),ROW($A1)),"")

Cảm ơn anh, nhưng công thức này không đúng trong 1 số trường hợp, ví dụ thay ô A2 thành số 2 là không đúng rồi ạ

 

[SA_DQ]

Hàm mảng tự tạo đê, mãi zô:

1​
3​		2
7​		4
5​		6
9​		10	<=| =ThieuSo(B2:B11)
12​		11
8​		13
19​		14
17​		16
15​		18

Function ThieuSo(Rng As Range)
Dim WF As Object, sRng As Range
Dim J As Long, W As Integer, Max_ As Long, Min_ As Long

Set WF = Application.WorksheetFunction
Min_ = WF.Min(Rng):                Max_ = WF.Max(Rng)
ReDim Arr(1 To (Max_ + Min_), 1 To 1) As String
Arr(1, 1) = "Nothing"
For J = Min_ To Max_
    Set sRng = Rng.Find(J, , xlFormulas, xlWhole)
    If sRng Is Nothing Then
        W = W + 1:                  Arr(W, 1) = Str(J)
    End If
Next J
ThieuSo = Arr()
End Function

 

[befaint]

không đúng

.. .

 

[Miccpro]

Đã được rồi, em xin Cảm ơn các anh chị rất nhiều ạ!

 

[bebo021999]

Tham khảo thêm cách khác (đúng cho cả trường hợp chuỗi không theo thứ tự)

=IFERROR(AGGREGATE(15,6,ROW(INDIRECT(MIN($A$2:$A$11)&":"&MAX($A$2:$A$11)))/(1-FREQUENCY($A$2:$A$11,ROW(INDIRECT(MIN($A$2:$A$11)&":"&MAX($A$2:$A$11))))),ROW(1:1)),"")

 

[bebo021999]

.. .

Mặc định từ số 1 à, nếu số nhỏ nhất trong chuỗi là 2, thì sẽ bắt đầu xét từ 3 trở đi nhé bi phèn.

 

[befaint]

Mặc định từ số 1 à, nếu số nhỏ nhất trong chuỗi là 2, thì sẽ bắt đầu xét từ 3 trở đi nhé bi phèn.

Chủ thớt không có iêu cầu mà anh.
(Không iêu cầu tìm từ số nhỏ nhất trong dãy).
Nguyên dương = Tập số tự nhiên bỏ số 0.

 

[bebo021999]

(Không iêu cầu tìm từ số nhỏ nhất trong dãy).

Không đọc kỹ ghi chú trong file rồi

 

[befaint]

Không đọc kỹ ghi chú trong file rồi

Thì A2:A11 chính là dãy số cho trước để tìm mà anh. @@



Chủ thớt kêu được rồi, chắc là được rồi đó anh. Nếu chưa được thì tính tiếp sau.

 

[Lê Duy Thương]

Cảm ơn anh, nhưng công thức này không đúng trong 1 số trường hợp, ví dụ thay ô A2 thành số 2 là không đúng rồi ạ

Bạn thử công thức này xem sao

=SMALL(IFERROR(MATCH(ROW(INDIRECT("1:"&MAX($A$2:$A$11))),$A$2:$A$11,0)<1,ROW(INDIRECT("1:"&MAX($A$2:$A$11)))),ROWS($D$5:D5))