Xin chào các cao thủ về Excell mình cũng ít khi dùng Excel và đang gặp phải vấn đề lọc data mong mọi người giúp với.- Mình có một bảng Excell như file đính kèm- Mình muốn lọc data cột C trong sheet1 ra cột H những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490- Sau đó lọc bỏ những SĐT trùng lặp ở cột H đi và chuyển về định dạng theo chuẩn SĐT thông thường thay vì có 008491xxxxx mà thay bắng 091xxxxx & 090xxxxxxx
Lọc SĐT theo điều kiện & loại bỏ dữ liệu trùng lắp từ cột (2 người xem)
- Thread starter htlamvdc
- Ngày gửi
Người dùng đang xem chủ đề này
thaytu
Thành viên hoạt động
- Tham gia
- 30/5/08
- Bài viết
- 118
- Được thích
- 164
Vấn đề những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490: Bạn tìm hiểu chức năng DATA, FILTER, AUTO FILTER lấy bắt đầu bằng thì chọn BEGIN WITH
Vấn đề lọc lấy dòng không trùng số đt thì thêm 1 cột phụ sau cột C, ô D2 gõ số 1, D3 =COUNTIF(C$2:C3,C3) và kéo xống
lúc này ra kết quả là số, nếu số 1 tức là số đt đó xuất hiện lần đầu từ trên xuống, 2, 3... thì xóa bỏ vì trùng.
Vấn đề lọc lấy dòng không trùng số đt thì thêm 1 cột phụ sau cột C, ô D2 gõ số 1, D3 =COUNTIF(C$2:C3,C3) và kéo xống
lúc này ra kết quả là số, nếu số 1 tức là số đt đó xuất hiện lần đầu từ trên xuống, 2, 3... thì xóa bỏ vì trùng.
- Tham gia
- 2/8/10
- Bài viết
- 5,225
- Được thích
- 9,025
- Giới tính
- Nam
Đặt một Name lọc theo điều kiện:
Trích rút qua cột H, H2:
Lọc duy nhất, (công thức mảng: kết thúc với tổ hợp phím Ctrl+Shift+Enter), I2:
Mã:
dk=IF((LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008490")+(LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008491"),ROW(Sheet1!$C$2:$C$84),"")
Mã:
=IF(ROW(A1)<=COUNT(dk),"0"&RIGHT(TRIM(INDEX($C$2:$C$84,SMALL(dk,ROW(A1))-1,)),9),"")
Mã:
=INDEX($H$2:$H$17,MATCH(0,COUNTIF(I$1:I1,$H$2:$H$17),0),)
hieuxd
Thành viên tích cực
- Tham gia
- 27/5/09
- Bài viết
- 854
- Được thích
- 1,162
- Nghề nghiệp
- Xây Dựng
Bạn dùng lọc Advanced nhé
Điều kiện lọc
=OR(LEFT(C4;7)=" 008491";LEFT(C4;7)=" 008490") có một dấu "cách" ở đầu trước hai số 00
khi lọc chọn recod only (lọc duy nhất luôn)
sau khi lọc bạn bôi đen vùng vừa lọc được nhấn Ctrl+h
Find nhập 0084 (có một dấu cách ở đầu)
Replace để trống
nhấn Replace All
Thêm số 0 ở đầu thì dùng định dạng Custom "0"0
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,124
- Được thích
- 17,588
- Giới tính
- Nam
Bạn muốn làm thử thì bạn làm theo từng bước nhé:
1/ Tô chọn từ C1:C84.
2/ Vào Data - Filter - Advanced Filter ... chọn Copy to another location, Copy to nhập $F$1 - Chọn Unique records only - OK
3/ Ô G2 nhập công thức: ="0" & MID(TRIM(F2);5;20)
Nếu không muốn thấy công thức nữa thì Copy cột G Paste Value lại tại chỗ, Xóa cột F, bạn đã có số ĐT bằng cách thay 4 số đầu bằng số 0.
Có khuyến mãi cho bạn thêm một Sub trong Module, cho Run sẽ có kết quả ở cột H.
PHP:
Dim Dic As Object, sArr(), dArr(), I As Long, K As Long, Tem As String
Set Dic = CreateObject("Scripting.Dictionary")
sArr = Range([C2], [C65000].End(xlUp)).Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 1)
For I = 1 To UBound(sArr, 1)
Tem = Application.WorksheetFunction.Trim(sArr(I, 1))
If Not Dic.Exists(Tem) Then
K = K + 1
Dic.Add Tem, K
dArr(K, 1) = "'0" & Mid(Tem, 5, 20)
End If
Next I
[H2].Resize(K).Value = dArr
Set Dic = Nothing
End Sub
dhn46
Hướng tới tương lai
- Tham gia
- 1/3/11
- Bài viết
- 3,251
- Được thích
- 3,870
Bài này nếu dung Pivot Table (hoặc Data => Remove Duplicate) để trích lọc duy nhất rồi dùng công thức để chuyển đổi định dạnh số điện thoại là 1 cách dễ dàng cho người mới tiếp cận
Ngoài ra có thể dùng lập trình (ví dụ như ADO)
Bạn tham khảo File đính kèm, trong đó có sử dụng Pivot table + Công thức và ADO để giải quyết vấn đề của bạn
Ngoài ra có thể dùng lập trình (ví dụ như ADO)
Bạn tham khảo File đính kèm, trong đó có sử dụng Pivot table + Công thức và ADO để giải quyết vấn đề của bạn
- Tham gia
- 18/3/08
- Bài viết
- 8,310
- Được thích
- 15,867
- Giới tính
- Nam
- Nghề nghiệp
- Làm ruộng.
Thêm cách = ado.
Mã:
Sub Loc_HLMT()
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select distinct '0' & right(f3,len(f3)-5) " & _
"from [sheet1$a2:e1000] " & _
"where left(F3,7) in (' 008491',' 008490')"
End With
With Sheet1
.[H2:H1000].ClearContents
.[H2].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub
Lần chỉnh sửa cuối:
- Tham gia
- 2/8/10
- Bài viết
- 5,225
- Được thích
- 9,025
- Giới tính
- Nam
Nếu có sdt 008491xx0084x thì sao nhỉ?
Phải ngăn ngừa trường hợp này.
hungpecc1
Thành viên gắn bó
- Tham gia
- 24/8/12
- Bài viết
- 1,709
- Được thích
- 2,304
- Giới tính
- Nam
Có nhiều giải pháp, --> mình xin bổ sung thêm 1 giải pháp cho phong phú hoa lá cành :
[GPECODE=vb]
Sub GPE()
Dim cnn As Object, rst As Object
Dim SQL$
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=microsoft.ACE.OLEDB.12.0;" & _
"Data source=" & ThisWorkbook.FullName & _
";Extended properties=""Excel 12.0;HDR=No;IMEX=1"";"
.Open
End With
SQL = "SELECT DISTINCT REPLACE(f3,'0084','0') FROM [sheet1$] " & _
"WHERE f3 LIKE '%008491%' OR f3 LIKE '%008490%'"
rst.Open SQL, cnn, 3, 1, 1
[H2:H1000].ClearContents: [H2].CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
Click vào nút button1 trong file đính kèm, xem kết quả thế nào !
thanks KQ bạn leonguyenz rất ok đúng với mong muốn của mình. Thanks bạn nhiều, nhưng bạn giúp mình hướng dẫn chi tiết cách thức thực hiện với. Mình mò nãy giờ mà vẫn ko hiểu được để làm như bạn."Đặt một Name lọc theo điều kiện:Code:dk=IF((LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008490")+(LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008491"),ROW(Sheet1!$C$2:$C$84),"")cái này là đặt ở đâu vậy bạn ???
hieuxd
Thành viên tích cực
- Tham gia
- 27/5/09
- Bài viết
- 854
- Được thích
- 1,162
- Nghề nghiệp
- Xây Dựng
Em cũng đã nghĩ qua cái trường hợp này
Tùy vào dữ liệu cụ thể (dữ liệu thực để cho ra cách đơn giản nhất thôi anh a)
Trắc anh cũng biết dữ liệu ở đầu có 1 dấu cách nên anh dùng hàm "Trim" chuột để bỏ nó đi
Với dữ liệu của bạn ấy thì Nếu có sdt 008491xx0084x cũng không ảnh hưởng, kết quả vẫn trùng theo cách của anh Leo
hieuxd
Thành viên tích cực
- Tham gia
- 27/5/09
- Bài viết
- 854
- Được thích
- 1,162
- Nghề nghiệp
- Xây Dựng
Bạn nhấn F3 sẽ nhìn thấy nó
- Tham gia
- 18/3/08
- Bài viết
- 8,310
- Được thích
- 15,867
- Giới tính
- Nam
- Nghề nghiệp
- Làm ruộng.
ADO: Thiếu điều kiện lọc.
Sẽ lọc thiếu chính xác nếu như trong số điện thoại có những số gần giống 008491 và 008490, hàm replace trên máy tôi không hoạt động được.
Lần chỉnh sửa cuối:
hungpecc1
Thành viên gắn bó
- Tham gia
- 24/8/12
- Bài viết
- 1,709
- Được thích
- 2,304
- Giới tính
- Nam
Cái này thì em biết , ban đầu định là LIKE ' 008491%' thôi !
còn cái vụ hàm REPLACE trên máy anh không hoạt động được <-------- cái này em không giải thích được ?, vì máy em vấn chạy được mà ()!
- Tham gia
- 18/3/08
- Bài viết
- 8,310
- Được thích
- 15,867
- Giới tính
- Nam
- Nghề nghiệp
- Làm ruộng.
Hàm replace không sử dụng được do đâu thì bạn google sẽ thấy. Nếu sử dụng được đi chăng nữa thì cũng không nên dùng, vì nó sẽ replace những số giống có trong dãi số, ta phải kết hợp hàm left để replace, nhưng vừa left vừa replace thì ta dùng 1 hàm right cho gọn.
Lê Duy Thương
Cạo lấy gì gội (Dịch quá không gội được)
- Tham gia
- 14/10/09
- Bài viết
- 3,112
- Được thích
- 4,848
món này là thơm nhất ,đỉnh nhất.khoi
đau đầu
- Tham gia
- 12/6/06
- Bài viết
- 3,186
- Được thích
- 7,637
Để góp vui với mọi người mình cũng gửi lên đây một công thức nhưng sử dụng google spreadsheet mong mọi người chỉ xem đây là một cách để tham khảo
https://docs.google.com/spreadsheet/ccc?key=0AtuWUkMv4lmjdHZYdmtHTVh0OGpUQ3NYdVpzYTRodlE&usp=sharing
https://docs.google.com/spreadsheet/ccc?key=0AtuWUkMv4lmjdHZYdmtHTVh0OGpUQ3NYdVpzYTRodlE&usp=sharing
Bài viết mới nhất
- Question
- Question
