một người bạn của tôiTôi thường liên lạc với anh ấy về một số vấn đề trong thế giới thực mà anh ấy gặp phải khi làm việc với dữ liệu trong Excel.
Nhiều lần tôi dịch truy vấn của anh ấy thành một hướng dẫn Excel trên trang web này vì nó cũng có thể hữu ích cho những độc giả khác của tôi.
Đây cũng là một trong những hướng dẫn như vậy.
Bạn tôi đã gọi cho tôi vào tuần trước với những câu hỏi sau:
Tôi có dữ liệu địa chỉ trong một cột trong Excel và tôi muốn xác định / lọc các ô có chứa chuỗi văn bản (từ) lặp lại trong địa chỉ.
Đây là một tập dữ liệu tương tự trong đó anh ta muốn lọc các ô có chuỗi văn bản lặp lại trong chúng (các ô có mũi tên màu đỏ):
Điều làm cho việc này trở nên khó khăn hiện nay là dữ liệu không nhất quán.Vì đây là tập hợp các tập dữ liệu được tạo theo cách thủ công bởi các đại diện bán hàng, nên có thể có sự khác biệt trong các tập dữ liệu.
hãy nghĩ về nó:
- Bất kỳ chuỗi văn bản nào cũng có thể được lặp lại trong tập dữ liệu này.Ví dụ: nó có thể là tên vùng hoặc tên thành phố hoặc cả hai.
- Các từ được phân tách bằng ký tự khoảng trắng và không có sự nhất quán về việc tên thành phố có sau sáu hay tám ký tự hay không.
- Có hàng nghìn bản ghi như vậy và cần lọc những bản ghi có bất kỳ chuỗi văn bản lặp lại nào.
Sau khi xem xét nhiều tùy chọn như văn bản thành cột và công thức, cuối cùng tôi quyết định sử dụng VBA để thực hiện công việc.
Vì vậy, tôi đã tạo một hàm VBA tùy chỉnh ('IdDuplicate') để phân tích các ô này và cung cấp cho tôi TRUE nếu có các từ trùng lặp trong chuỗi văn bản và FALSE nếu không có từ trùng lặp (như được hiển thị bên dưới):
Chức năng tùy chỉnh này phân tích từng từ trong chuỗi văn bản và kiểm tra xem nó xuất hiện bao nhiêu lần trong văn bản.Trả về TRUE nếu số đếm lớn hơn 1;Nếu không, trả về FALSE.
Ngoài ra, nó được tạo ra để chỉ đếm các từ có nhiều hơn ba ký tự.
Khi tôi có dữ liệu TRUE / FALSE, tôi có thể dễ dàng lọc tất cả các bản ghi ĐÚNG.
Bây giờ hãy để tôi chỉ cho bạn cách thực hiện việc này trong Excel.
Mã VBA cho chức năng tùy chỉnh
Điều này được thực hiện bằng cách tạo một hàm tùy chỉnh trong VBA.Sau đó, hàm này có thể được sử dụng như bất kỳ hàm trang tính nào khác trong Excel.
Đây là mã VBA của nó:
Hàm IdDuplicates (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Bước -1 If Len (StringtoAnalyze (i)) <minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Kết thúc Hàm
Cách sử dụng mã VBA này
Bây giờ bạn đã có mã VBA, bạn cần đặt nó vào phần phụ trợ của Excel để nó hoạt động như một hàm trang tính thông thường.
Dưới đây là các bước để đặt mã VBA trong phần phụ trợ:
- Chuyển đến tab nhà phát triển.
- Nhấp vào Visual Basic (bạn cũng có thể sử dụng phím tắt ALT + F11)
- Trong chương trình phụ trợ trình soạn thảo VB đang mở, bấm chuột phải vào bất kỳ đối tượng sổ làm việc nào.
- Đi tới "Chèn" và nhấp vào "Mô-đun".Thao tác này sẽ chèn đối tượng mô-đun của sổ làm việc.
- Trong cửa sổ mã mô-đun, sao chép và dán mã VBA được đề cập ở trên.
Khi bạn có mã VBA trong phần phụ trợ, bạn có thể sử dụng hàm - 'IdDuplicates' như bất kỳ hàm trang tính thông thường nào khác.
Hàm này nhận một tham số, tham chiếu ô của ô mà bạn có văn bản.
Kết quả của hàm này là TRUE (nếu có các từ lặp lại trong đó) hoặc FALSE (nếu không có từ nào lặp lại).Khi bạn có danh sách TRUE / FALSE này, bạn có thể lọc những danh sách có TRUE để lấy tất cả các ô có chuỗi văn bản lặp lại trong đó.
LƯU Ý: Mã tôi tạo chỉ để tính các từ dài hơn ba ký tự.Điều này đảm bảo rằng nếu có các từ trong chuỗi văn bản dài 1, 2 hoặc 3 ký tự (ví dụ: 12 A, KGM hoặc LDA), thì những từ đó sẽ bị bỏ qua khi đếm trùng lặp.Bạn có thể dễ dàng thay đổi điều này trong mã nếu cần.
Tính năng này chỉ có sẵn trong sổ làm việc mà bạn đã sao chép mã trong mô-đun.Nếu bạn muốn mã này cũng có sẵn trong các sổ làm việc khác, bạn cần sao chép và dán mã này vào các sổ làm việc đó.Ngoài ra, bạn có thể tạo một bổ trợ (kích hoạt này sẽ làm cho tính năng này có sẵn trong tất cả các sổ làm việc trên hệ thống của bạn).
Ngoài ra, hãy nhớ lưu sổ làm việc này với phần mở rộng .xlsm (vì nó chứa mã macro).
