Nhờ giúp Công thức lọc dữ liệu thiếu

Liên hệ QC
T

thuhiennguyen1888

Thành viên chính thức
Tham gia
16/10/14
Bài viết
78
Được thích
0
Chào các Bạn,
Trong file đính kèm mình nhờ các Bạn giúp cho công thức để tại cột M thể hiện những số còn thiếu khi đối chiếu dữ liệu cột L với các số từ 0 đến 9.
Cám ơn các Bạn nhiều.
 

File đính kèm

File đính kèm

thuhiennguyen1888 đã viết:
Cám ơn Bạn.
Nếu dùng được bằng Công thức thông thường thì tốt quá.
Mong Bạn & các Bạn tiếp tục giúp cho.
Nhấp chuột vào đây để mở rộng...
Thực ra bạn nên dùng như anh Phuocam cũng dễ dùng
Locso=Locso&"_"&i nó ra kết quả như yêu cầu nhưng có thêm dấu trừ thế này -7
sau dùng hàm mid tách từ vi trí thứ hai thôi, bạn dựa vào đó chỉnh sửa cho phù hợp nhé
 
thuhiennguyen1888 đã viết:
Chào các Bạn,
Trong file đính kèm mình nhờ các Bạn giúp cho công thức để tại cột M thể hiện những số còn thiếu khi đối chiếu dữ liệu cột L với các số từ 0 đến 9.
Cám ơn các Bạn nhiều.
Nhấp chuột vào đây để mở rộng...

Mình cũng góp 1 hàm tự tạo!

=FindNumberMiss(chuỗi cần kiểm tra)

PHP: 
Function FindNumberMiss(Chuoi As String) As String
    Dim Arr, dArr
    
    Dim i As Integer
    Dim tmp As Integer
    Dim strResult As String
    Arr = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
    
    dArr = Split(Chuoi, "_")
    For i = 0 To UBound(Arr)
    tmp = InStr(Chuoi, Arr(i))
    If tmp = 0 Then
        strResult = strResult & i & "_"
        FindNumberMiss = Left(strResult, Len(strResult) - 1)
    End If
    Next i
End Function
 
thuhiennguyen1888 đã viết:
Cám ơn Bạn.
Nếu dùng được bằng Công thức thông thường thì tốt quá.
Mong Bạn & các Bạn tiếp tục giúp cho.
Nhấp chuột vào đây để mở rộng...
Mã: 
=TRIM(MID(SUBSTITUTE($L$5,"_",REPT(" ",255)),COLUMNS($A:A)*255-254,255))
rồi nếu làm công thức thì thế này rôì lại đi lọc trùng rồi so sánh hơi mất thời gian
Nhưng theo mình hiểu chuỗi bạn còn thêm nhiều hơn nên bạn sợ ko hiểu code thì ko vọc lại dược đúng ko.
Bạn có thể dùng code của phuyen89 nếu chuỗi dài hơn thì thêm phần tử vào Array("0",...) là được
Mã: 
Public Function LocSo(Chuoi As String) As String
Dim i As Byte, k 
For i = 0 To 9
    If InStr(Chuoi, i) = 0 Then
        k = k & i & "_"
        LocSo = Left(k, Len(k) - 1)
    End If
Next i
End Function
 
Lần chỉnh sửa cuối:
Công thức là
Mã: 
=IFERROR(LEFT(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""),LEN(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""))-1),"")
 
phuyen89 đã viết:
Mình cũng góp 1 hàm tự tạo!

=FindNumberMiss(chuỗi cần kiểm tra)

PHP: 
Function FindNumberMiss(Chuoi As String) As String
    Dim Arr, dArr
    
    Dim i As Integer
    Dim tmp As Integer
    Dim strResult As String
    Arr = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
    
    dArr = Split(Chuoi, "_")
    For i = 0 To UBound(Arr)
    tmp = InStr(Chuoi, Arr(i))
    If tmp = 0 Then
        strResult = strResult & i & "_"
        FindNumberMiss = Left(strResult, Len(strResult) - 1)
    End If
    Next i
End Function
Nhấp chuột vào đây để mở rộng...
Mã: 
dArr = Split(Chuoi, "_")
Đoạn này hơi thừa, có thể bỏ đi phuyen89 nhẩy
 
Hau151978 đã viết:
Công thức là
Mã: 
=IFERROR(LEFT(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""),LEN(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""))-1),"")
Nhấp chuột vào đây để mở rộng...
Sợ đại ca quá đi mất, anh viết khiếp quá.
 
thuhiennguyen1888 đã viết:
Cám ơn Bạn.
Nếu dùng được bằng Công thức thông thường thì tốt quá.
Mong Bạn & các Bạn tiếp tục giúp cho.
Nhấp chuột vào đây để mở rộng...
Thêm một cách bằng công thức:
Mã: 
=MID(IF(COUNTIF(L5,"*0*")=0,"_0","")&IF(COUNTIF(L5,"*1*")=0,"_1","")&IF(COUNTIF(L5,"*2*")=0,"_2","")&IF(COUNTIF(L5,"*3*")=0,"_3","")&IF(COUNTIF(L5,"*4*")=0,"_4","")&IF(COUNTIF(L5,"*5*")=0,"_5","")&IF(COUNTIF(L5,"*6*")=0,"_6","")&IF(COUNTIF(L5,"*7*")=0,"_7","")&IF(COUNTIF(L5,"*8*")=0,"_8","")&IF(COUNTIF(L5,"*9*")=0,"_9",""),2,20)
 
Hau151978 đã viết:
Công thức là
Mã: 
=IFERROR(LEFT(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""),LEN(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""))-1),"")
Nhấp chuột vào đây để mở rộng...

Cám ơn Bạn.
Cám ơn ACE trong Diễn đàn.
Thật tuyệt vời.
Có những bài tập em tưởng như khó có lời giải vậy mà các ACE vẫn dành thời gian, công sức giúp cho ra đáp án.
Cám ơn ACE, cám ơn Diễn đàn rất nhiều.
 
Hau151978 đã viết:
Công thức là
Mã: 
=IFERROR(LEFT(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""),LEN(IF(LEN(L5)=LEN(SUBSTITUTE(L5,0,"")),"0_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,1,"")),"1_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,2,"")),"2_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,3,"")),"3_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,4,"")),"4_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,5,"")),"5_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,6,"")),"6_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,7,"")),"7_","") &IF(LEN(L5)=LEN(SUBSTITUTE(L5,8,"")),"8_","") & IF(LEN(L5)=LEN(SUBSTITUTE(L5,9,"")),"9_",""))-1),"")
Nhấp chuột vào đây để mở rộng...
Sao bạn không dùng COUNTIF cho nó gọn?
 
thuhiennguyen1888 đã viết:
Cám ơn Bạn.
Cám ơn ACE trong Diễn đàn.
Thật tuyệt vời.
Có những bài tập em tưởng như khó có lời giải vậy mà các ACE vẫn dành thời gian, công sức giúp cho ra đáp án.
Cám ơn ACE, cám ơn Diễn đàn rất nhiều.
Nhấp chuột vào đây để mở rộng...
Đó mới là GPE mà, các ACE nếu ko bận đều rất nhiệt tình bạn ah
 
Công thức của bác phuocam có lỗi nếu chuỗi gốc chỉ có 1 số (ví dụ là 1), kết quả vẫn ra 0_1_2_...9 mà không phải là 0_2_3...9
 
Hau151978 đã viết:
Công thức của bác phuocam có lỗi nếu chuỗi gốc chỉ có 1 số (ví dụ là 1), kết quả vẫn ra 0_1_2_...9 mà không phải là 0_2_3...9
Nhấp chuột vào đây để mở rộng...

Đó là vấn đề của COUNTIF
Nếu cell chỉ có số 1 nhưng được định dạng TEXT sẽ OK liền
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

SA_DQ
  • Question Question
[Bài tập VBA cho người rỗi rảnh] Hãy viết 1 macro tìm ra 3 số nguyên tố liên tiếp
Trả lời
15
Đọc
138
ptm0412
ptm0412
L
  • Question Question
[Hlep] Xóa ảnh theo tên và kích thước
Trả lời
3
Đọc
63
HUONGHCKT
H
Thanh271188
Tìm giá trị xuất hiện bất kì (không phải giá trị đầu, cuối) trong excel
    • Cười
Trả lời
3
Đọc
80
VetMini
V
T
Nhờ hỗ trợ hàm SUMIF không phân biệt được các ký tự "0" trước một số trong chuỗi
Trả lời
3
Đọc
146
tan10ckn
T
huuthang_bd
Add-in DeleteVirusMacro - Xóa và ngăn chặn virus macro
    • Thích
    • Yêu thích
    • Chấp nhận
2 3
Trả lời
46
Đọc
14K
tuantran203
T
ongke0711
  • Question Question
Trộn dữ liệu từ Excel sang Word mở rộng (phiên bản ongke0711)
    • Thích
    • Yêu thích
    • Cảm ơn
12 13 14
Trả lời
260
Đọc
27K
hoangtuan.net
hoangtuan.net
HeSanbi
  • Đã giải quyết
WeatherXL - Ứng dụng lấy dữ liệu Thời tiết siêu nhanh (*15/01/2024)
    • Thích
    • Yêu thích
3 4 5
Trả lời
88
Đọc
18K
vc_đi chơi
vc_đi chơi
L
[Help] tự động lấy dữ liệu cổ phiếu từ vietstock về file excel
    • Thích
Trả lời
3
Đọc
764
ketoan113
K
N
Tách chuỗi và tìm kiếm theo dữ liệu gần đúng trong chuỗi ký tự
Trả lời
8
Đọc
159
Ngocminh19
N
Mr.hieudoanxd
  • Question Question
[Cần hướng dẫn] Về lập trình Viết Function với Settimer và Killtimer
    • Thích
Trả lời
8
Đọc
285
nguyendang95
N
P
Các Bác cho e hỏi làm thế nào để ẩn công thức trên GOOGLE SHEET với ạ?
2
Trả lời
21
Đọc
18K
học excel googlesheet
học excel googlesheet
Emyeuexcel
  • Question Question
Code VBA mở nhiều file, value các sheet, lưu và đóng file
Trả lời
3
Đọc
94
Emyeuexcel
Emyeuexcel
V
Cho 2 biến - kết quả trả về giá trị của hàng đầu tiên
    • Chấp nhận
Trả lời
5
Đọc
110
Duy Tùng 9x
Duy Tùng 9x
D
Giúp đỡ thống kê dữ liệu theo điều kiện
Trả lời
8
Đọc
213
HieuCD
H
bachloc1212
Đưa Text từ các dòng khác nhau về chung 1 dòng
Trả lời
10
Đọc
232
VetMini
V
N
Sửa lỗi code vba khi chạy cho ra kết quả không đúng
Trả lời
6
Đọc
171
VetMini
V
Ha Manh-LD
  • Question Question
Lấy dữ liệu từ file csv
Trả lời
1
Đọc
128
ptm0412
ptm0412
LuuAnh980
Add in tạo QR code miễn phí.
Trả lời
13
Đọc
441
LuuAnh980
LuuAnh980
HeSanbi
  • Đã giải quyết
VBA Nâng cao: Hướng dẫn tạo biểu thức tìm kiếm với Regular Expression
    • Thích
    • Yêu thích
Trả lời
9
Đọc
908
HeSanbi
HeSanbi
Tienvinh191
  • Question Question
Nhờ viết Code lấy dữ liệu tờ khai hải quan vào bảng tổng hợp
2
Trả lời
28
Đọc
3K
quyenpv
quyenpv
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
Chúc mừng năm mới Ất Tỵ 2025
Back
Top Bottom