Tìm các số còn thiếu trong một dãy số.

[LienDong]

Nhờ các bạn giúp mình như sau:
Mình muốn tìm các số còn thiếu ở cột A, tìm từ số 99 đến số 106, trong dãy này nó thiếu các số 101;104
Mình muốn kết quả ở cell E4
Cảm ơn các bạn

 

[befaint]

E x c e l l 2019

 

[LienDong]

E x c e l l 2019

View attachment 273715

cảm ơn bạn mình đang dùng excel 2010

 

[exthuctien]

cảm ơn bạn mình đang dùng excel 2010

E4=IFERROR(SMALL(IF(COUNTIF($A$7:$A$12,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12))))=0,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12)))),ROWS($E$4:E4)),"")
Kết thúc CTRL+SHIFT+ENTER => coppy xuống dưới

 

[VetMini]

Tôi vốn không phải lúc nào cũng sẵn sàng với VBA.
Nhưng trường hợp này dùng UDF dễ kiểm soát, dễ chỉnh sửa theo ý muốn hơn.

Câu hỏi đầu tiên là: vùng số ấy đã sắp xếp hay chưa sắp xếp?
Hai giải thuật khác nhau.

 

[Maika8008]

E4=IFERROR(SMALL(IF(COUNTIF($A$7:$A$12,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12))))=0,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12)))),ROWS($E$4:E4)),"")
Kết thúc CTRL+SHIFT+ENTER => coppy xuống dưới

Đọc công thức cái loạn não liền

 

[Phuocam]

E4=IFERROR(SMALL(IF(COUNTIF($A$7:$A$12,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12))))=0,ROW(INDIRECT(MIN($A$7:$A$12)&":"&MAX($A$7:$A$12)))),ROWS($E$4:E4)),"")
Kết thúc CTRL+SHIFT+ENTER => coppy xuống dưới

Công thức này hay, nhưng hạn chế của nó là chỉ tìm thiếu khúc giữa, bỏ qua kiểm tra đầu và cuối. (nhỏ hơn MIN hoặc lớn hơn MAX)

.

 

[batman1]

Công thức này hay, nhưng hạn chế của nó là chỉ tìm thiếu khúc giữa, bỏ qua kiểm tra đầu và cuối. (nhỏ hơn MIN hoặc lớn hơn MAX)

.

nhỏ hơn MIN hoặc lớn hơn MAX có nghĩa là gì? Theo tôi thì chủ thớt không biết diễn đạt. 99 và 106 không phải là số cứng nhắc, không thể dùng chúng trong công thức. Nếu tôi hiểu đúng thì số nhỏ nhất trong vùng đã cho là MIN, số lớn nhất trong vùng là MAX. Tìm các số > min và < max mà không có trong vùng.

 

[LienDong]

nhỏ hơn MIN hoặc lớn hơn MAX có nghĩa là gì? Theo tôi thì chủ thớt không biết diễn đạt. 99 và 106 không phải là số cứng nhắc, không thể dùng chúng trong công thức. Nếu tôi hiểu đúng thì số nhỏ nhất trong vùng đã cho là MIN, số lớn nhất trong vùng là MAX. Tìm các số > min và < max mà không có trong vùng.

Cái dãy số của em trong bảng tính trên đã được sort rồi
Bây giờ em xin mô tả như thế này
Có 1 dãy số tự nhiên, số Min=99; Max=106, tìm số còn thiếu?
Dãy số như sau: (chưa được sort)
100
99
102
100
103
106
105
như vậy kết quả là thiếu số: 101;104
vậy nhờ các anh chị viết công thức hay dùng hàm gì cũng được. Cảm ơn các bạn nhiều

 

[snow25]

Tôi vốn không phải lúc nào cũng sẵn sàng với VBA.
Nhưng trường hợp này dùng UDF dễ kiểm soát, dễ chỉnh sửa theo ý muốn hơn.

Câu hỏi đầu tiên là: vùng số ấy đã sắp xếp hay chưa sắp xếp?
Hai giải thuật khác nhau.

Nếu cái này viết UDF cần mấy vòng lặp anh nhỉ.

 

[batman1]

Cái dãy số của em trong bảng tính trên đã được sort rồi
Bây giờ em xin mô tả như thế này
Có 1 dãy số tự nhiên, số Min=99; Max=106, tìm số còn thiếu?
Dãy số như sau: (chưa được sort)
100
99
102
100
103
106
105
như vậy kết quả là thiếu số: 101;104
vậy nhờ các anh chị viết công thức hay dùng hàm gì cũng được. Cảm ơn các bạn nhiều

Nếu chỉ là 1 dãy như trên thì khỏi phải công thức, nhìn là thấy thiếu 101, 104.

Mấu chốt là xử lý dãy BẤT KỲ, đúng không? Vd. cho dãy BẤT KỲ có 100 số. Bạn sẽ bỏ công dò tìm bằng mắt, tìm số MIN và MAX để nhập vào công thức nào đó? Bạn có hiểu ý của tôi không? Còn nếu dãy BẤT KỲ nhưng luôn với MIN = 99 và MAX = 106 thì nói rõ ra, nói huỵch toẹt ra.

 

[VetMini]

Nếu cái này viết UDF cần mấy vòng lặp anh nhỉ.

Chịu khó code rắc rối thì chắc chỉ cần 1.
Vòng lặp for, đếm số nguyên rất hiệu quả. Tại sao các bạn lại thích chú trọng vào số vòng lặp để phải chịu code rắc rối? Điển hình là duyệt mảng 2 chiều, toi ví dụ vài lần rồi, nhìn bề ngoài thì 1 vòng lặp, nhưng code thì khó hiểu hionw mà số lần lặp lại cũng bao nhiêu ấy - người ta chỉ tóm lại 1 vòng khi code có Exit: tuỳ theo muốn thoát một lần ra luôn, hay canf xét lại trước khi lần thứ hai.

 

[VetMini]

Nếu cái này viết UDF cần mấy vòng lặp anh nhỉ.

Thuật toán chỉ dùng 1 vòng lặp (nhưng rất kém hiệu quả):
Giả sử dãy số là 1 mảng.
For i = Application.MIN(mang)+1 To Application.MAX(mang)-1
If IsError(application.Match(i, mang, 0) Then ghi vào danh sách
Next i

 

[Hong.Van]

Nhờ các bạn giúp mình như sau:
Mình muốn tìm các số còn thiếu ở cột A, tìm từ số 99 đến số 106, trong dãy này nó thiếu các số 101;104
Mình muốn kết quả ở cell E4
Cảm ơn các bạn

Gởi bạn 1 file đã rất xưa, nhập dãy số bất kỳ (không cần sort trước) vào cell A2 nhé

 

[VetMini]

Cũng như hầu hết thuật toán về số, mức độ "ưu việt" của code còn tuỳ thuộc vào tình trạng dữ liệu.
Điển hình, lượng số thiếu nhiều hơn hay ít hơn lượng số có sẵn trong dãy?

Ví dụ lượng số thiếu khá nhiều so với lượng số cho sẵn thì thuật toán sau đây khá tốt:

' giả sử các số nằm trong mảng a
so1 = Application.Small(a, 1)
For i = 2 To UBound(a) - LBound(a) + 1
so2 = Application.Small(a, i)
For i2 = so1 + 1 To so2 - 1
' các i2 là các số còn thiếu
Next i2
so1 = so2
Next i

 

[HieuCD]

Cũng như hầu hết thuật toán về số, mức độ "ưu việt" của code còn tuỳ thuộc vào tình trạng dữ liệu.
Điển hình, lượng số thiếu nhiều hơn hay ít hơn lượng số có sẵn trong dãy?

Ví dụ lượng số thiếu khá nhiều so với lượng số cho sẵn thì thuật toán sau đây khá tốt:

' giả sử các số nằm trong mảng a
so1 = Application.Small(a, 1)
For i = 2 To UBound(a) - LBound(a) + 1
so2 = Application.Small(a, i)
For i2 = so1 + 1 To so2 - 1
' các i2 là các số còn thiếu
Next i2
so1 = so2
Next i

Có cách nào dùng 2 for tách rời không bạn?

 

[VetMini]

Có cách nào dùng 2 for tách rời không bạn?

Có. Sort mảng a trước. Sau đó duyệt a(i)+1 đến a(i+1)-1 như trên.
Thực ra cách này đỡ tốn năng lượng máy hơn cách trên.

(nếu sort mảng a bằng cách cho nó vào một arraylist rồi gọi phương thức sort thì là 1 vòng lặp)

 

[HieuCD]

Có. Sort mảng a trước. Sau đó duyệt a(i)+1 đến a(i+1)-1 như trên.
Thực ra cách này đỡ tốn năng lượng máy hơn cách trên.

(nếu sort mảng a bằng cách cho nó vào một arraylist rồi gọi phương thức sort thì là 1 vòng lặp)

Tạo mảng a(min to max), for đầu ghi nhận số có, for 2 lọc lấy số thiếu

 

[snow25]

Tạo mảng a(min to max), for đầu ghi nhận số có, for 2 lọc lấy số thiếu

Vậy làm sao xác định được min và max nếu không dùng hàm excel.

 

[VetMini]

Tạo mảng a(min to max), for đầu ghi nhận số có, for 2 lọc lấy số thiếu

Đó là phép sort theo chỉ số. Rất hiệu quả, nhất là khi số thiếu rất nhiều so với số có.

Chú: với bộ nhớ lớn thì bạn có thể tạo mảng lớn. Lúc ấy không cần phải tính min và max trước. Vòng lặp đầu, ghi chỉ số thì lược lấy min/max luôn. Tuy nhiên, nếu code đại trà thì tính min/max trước và lập mảng vừa phải an toàn hơn.

Vậy làm sao xác định được min và max nếu không dùng hàm excel.

Hàm bảng tính thật ra được Microsoft chăm sóc kỹ nên rất ưu việt.
Gọi hàm bảng tính chỉ cần lo phí gọi hàm. Tức là những hàm tính chuyện nhỏ mới sợ phí. Hàm tính cả mảng như thì gọi khá hiệu quả.
Phí gọi hàm là những công việc như móc địa chỉ hàm, lập ngăn xếp, nạp tham số; chạy xong thì thoát hàm, giải phóng ngăn xếp.