Tổng quan về fương thức tìm kiếm ( Find Method) trong Exxcel

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tổng quan về fương thức tìm kiếm ( Find Method) trong Excel

Find Method


Phương thức Find tìm kiếm thông tin trong một vùng nào đó, kết quả trả về là ô đầu tiên chứa đựng thông tin được tìm thấy. Nếu không tìm thấy thông tin trong vùng tìm kiếm thì phương thức Find sẽ trả về Nothing.

Cú pháp phương thức như sau :

Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Expression : Vùng tìm kiếm. Biểu thức Expression phải được khai báo và nó trả về một đối tượng Range.

What : Dữ liệu hay thông tin cần tìm kiếm. Đây là một đòi hỏi bắt buộc phải khai báo. Dữ liệu tìm kiếm có thể là một chuỗi ký tự hay một dạng dữ liệu nào đó có trong Excel và được khai báo dưới dạng Variant.

After : Ô được chọn để xác định vị trí tìm kiếm, là tùy chọn, được khai báo dưới dạng Variant, có thể bỏ qua. Việc tìm kiếm sẽ bắt đầu từ ô này. Ô này tương ứng với vị trí của ô hiện hành sau khi việc tìm kiếm hoàn tất. Ô xác định vị trí tìm kiếm này sẽ không được đưa vào quá trình tìm kiếm trừ khi vùng tìm kiếm bao gồm cả ô này. Nếu đối số này không được khai báo thì việc tìm kiếm sẽ bắt đầu sau ô trên cùng bên trái của vùng cần tìm kiếm.

LookIn : Không bắt buộc, khai báo dạng Variant. Đây là một dạng thông tin và thường có giá trị là xlValues.

LookAt : Cách thức tìm kiếm. Là đối số tùy chọn, không bắt buộc, khai báo dưới dạng Variant. LookAt có 2 giá trị : xlWhole (Tìm toàn bộ) hoặc xlPart (tìm một phần).

SearchOrder : Xác định dạng thứ tự tìm kiếm, là tùy chọn không bắt buộc, khai báo dưới dạng Variant. SearchOrder có 2 dạng ứng với 2 hàng số : xlByRows (theo thứ tự dòng) hoặc xlByColumns (theo thứ tự cột)

SearchDirection : Hướng tìm kiếm, là tùy chọn. SearchDirection có 2 dạng ứng với 2 hằng số : xlNext (Tìm kế tiếp, là giá trị mặc định), xlPrevious (Tìm trước đó)

MatchCase : Là tùy chọn để xác định kiểu tìm kiếm có phân biệt chữ Hoa với chữ thường, khai báo dưới dạng Variant. Khai báo là True nếu ta muốn tìm kiếm chính xác. Giá trị mặc định là False.( Không phân biệt chữ in Hoa với chữ thường)

MatchByte : Không bắt buộc, khai báo dạng Variant. Chỉ sử dụng khi ta đã chọn hoặc cài đặt bộ hỗ trợ ngôn ngữ ký tự byte kép. Là True nếu ứng với bộ ký tự byte kép, False nếu ứng với bộ ký tự byte đơn.

SearchFormat : Tìm kiếm theo định dạng. Là tham số tùy chọn, khai báo dưới dạng Variant.

Lưu ý :

- Các thiết lập cho các đối số LookIn, LookAt, SearchOrderMatchByte sẽ được lưu mỗi lần ta sử dụng phương thức này (phương thức Find). Nếu ta không khai báo giá trị cho các đối số vào lần sử dụng phương thức Find tiếp theo, các giá trị thiết lập đã lưu trước đó sẽ được sử dụng. Việc thiết lập các đối số này làm thay đổi các tùy chọn thiết lập trong hộp thoại Find, và việc thay đổi các thiết lập trong hộp thoại Find sẽ làm thay đổi các giá trị đã lưu – là những giá trị được sử dụng nếu ta bỏ qua các đối số này. Để tránh xảy ra việc này, ta nên khai báo các đối số một cách rõ ràng mỗi lần sử dụng phương thức Find này.

- Ta có thể dùng phương thức FindNextFindPrevious để lặp lại việc tìm kiếm. Khi đến vị trí cuối của vùng tìm kiếm được xác định trước đó, excel sẽ bao phủ từ vị trí này đến vị trí đầu tiên của vùng tìm kiếm. Để ngưng việc tìm kiếm ngay khi động tác bao phủ này xảy ra, hãy lưu lại địa chỉ của ô đầu tiên tìm được, sau đó thử so sánh lần lượt mỗi địa chỉ ô được tìm thấy kế tiếp với địa chỉ ô vừa được lưu này.


Bài của HoangDanh282VN tại 'GiaiPhapExcel.COM'
=======================@@@=============== ========​
 
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm kiếm số liệu Ngày-Tháng-Năm

Để khảo sát vấn đề này, chúng ta nghiên cứu 1 CSDL như bảng dưới đây

PHP:
TT| Ma  | HoTen  |NgaySinh  |NgCT     |Ngay Luong               |Ngay CMND| Vô Đoàn
10|VMH00|Võ Mĩ Hà|01/17/1983|14/03/03 |Sunday, February 06, 2005| 01/28/98| 5-Jan-09

Có nghĩa là CSDL này đã nhập số liệu ngày tháng với nhiều định đạng khác nhau

Để thấy sự khác biệt trong khi tìm kiếm các ngày, ta thử viết 1 macro tìm trong CSDL những người có 1 ngày nào đó trùng với các ngày từ 01/01/1994 đến 12/31/2003 (~ 10 năm)

Macro đó có nội dung như sau:

PHP:
Option Explicit
Sub TimNgay()
 Dim Sh As Worksheet, Rng As Range, sRng As Range, Rg0 As Range
 Dim J As Long, W As Byte, Dat As Date:
 Dim fAdd As String, NFormat As String

 On Error GoTo GPE
 Sheets("BCao").Select
 Set Sh = ThisWorkbook.Worksheets("CSDL")
 Set Rg0 = Sh.Range(Sh.[c2], Sh.[c2].End(xlDown))
 [b4].CurrentRegion.Offset(1, 1).ClearContents
 Application.ScreenUpdating = False
 For J = 0 To 3650
    Dat = DateSerial(1994, 1, 1 + J)
    For W = 1 To 5
        Set Rng = Rg0.Offset(, W)
        NFormat = Rng.NumberFormat
9        Rng.NumberFormat = "mm/dd/yyyy"         '<=|'
        Set sRng = Rng.Find(Format(Dat, "mm/dd/yyyy"), , xlValues, xlWhole)
        If Not sRng Is Nothing Then
            fAdd = sRng.Address
            Do
                With [B9999].End(xlUp).Offset(1)
                    .Resize(, 2).Value = Sh.Cells(sRng.Row, "B").Resize(, 2).Value
                    Cells(.Row, sRng.Column).Value = sRng.Value
                End With
                Set sRng = Rng.FindNext(sRng)
            Loop While Not sRng Is Nothing And sRng.Address <> fAdd
        End If
        Rng.NumberFormat = NFormat
    Next W
 Next J
 Application.ScreenUpdating = True
LoiCT:                          Exit Sub
GPE:
    MsgBox Dat - 1, , Erl
    GoTo LoiCT
End Sub

Bình thường macro sẽ tìm ra được hơn 165 nhân vật thỏa điều kiện như vậy.

Nhưng nếu ta vô hiệu hóa dòng lệnh mang số 9 đi; chuyện sẽ trỡ nên khác hẵn

Đó là macro chạy mệt mõi như trên, nhưng chỉ tìm ra 1 người duy nhất ở cột có định dạng ngày-tháng theo kiểu Mĩ, tức là MM/dd/yyyy.

Các bạn xem file sẽ rõ hơn.
 

File đính kèm

  • gpeBaiTap.rar
    23,7 KB · Đọc: 242

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Một cách khác để loại số người có các cột ngày trùng lắp đều thỏa điều kiện

Vì như bài trên ta nêu: Nhiệm vụ là khảo sát toàn bộ 05 cột ghi ngày tháng để tìm ra những ngày trùng với thời gian từ #01/01/1994# cho đến ngày #12/31/2003#
Do đó macro chạy trong 2 vòng lặp:
Vòng thứ nhất là các ngày trong khỏng nêu trên
Vòng thứ 2 chạy trong 5 cột dữ liệu kiểu ngày

Cũng bỡi chạy như vậy nên kết quả tìm ra được có rất nhiều người trùng nhau được liệt kê.
Các bạn sẽ thấy rõ điều đó nếu ta sắp xếp dữ liệu kết quả theo mã hay theo tên.

Ta thử cách thứ hai; Đó là duyệt theo mã nhân viên theo dữ liệu ngày trong khoảng #01/01/1994# cho đến #12/31/2003#
Để làm việc này, chúng ta cần tạo ra cột dữ liệu chứa tất cả các ngày cần khảo sát.
Như vậy macro sẽ tốn nhiều thời gian do chạy 3 vòng lặp

1 là số nhân viên trong CTi
2 là số ngày trong khoảng qui định
& 3 là 5 số liệu kiểu ngày trong CSDL

Nhưng bù lại ta được danh sách không trùng lắp về người.

Macro như vậy có nội dung như sau:

PHP:
Option Explicit
Sub LapDSTheoNgay()
 Dim Cls As Range, Sh As Worksheet, Rg0 As Range, Cll As Range, Rng As Range, sRng As Range
 Dim J As Long, Tmr As Double, Rws As Long
 Dim Có As Boolean:                             ReDim Arr(4 To 8)
 
 Tmr = Timer():                                 Sheets("CSDL").Select
1 [AA1].Value = "Ngày"                          'Tao DS Ngày Càn Khao Sát:'
 For J = 0 To 3650
    Cells(J + 2, "AA").Value2 = DateSerial(1994, 1, 1 + J)
 Next J
 Set Rng = [AA2].Resize(3651)
 Rng.NumberFormat = "MM/DD/yyyy"
 Rws = [b2].CurrentRegion.Rows.Count
 Set Sh = ThisWorkbook.Worksheets("BCao")
2 Sh.[b4].Resize(Rws, 7).ClearContents          'Xóa Du Lieu Làn Chay Macro Truóc:'
 Application.ScreenUpdating = False
 For Each Cls In Range([b2], [b2].End(xlDown))
    Set Rg0 = Cls.Offset(, 2).Resize(, 5)
    For Each Cll In Rg0
        Set sRng = Rng.Find(Format(Cll.Value, "MM/dd/yyyy"), , xlValues, xlWhole)
        If Not sRng Is Nothing Then
            With Sh.Cells(Rws, "B").End(xlUp).Offset(1)
                Cls.Resize(, 7).Copy Destination:=.Offset()
                Sh.Cells(.Row, Cll.Column).Interior.ColorIndex = 30 + Cll.Column
            End With
            Exit For
        End If
    Next Cll
 Next Cls
 Application.ScreenUpdating = True
 Sh.[d1].Value = Timer() - Tmr
End Sub
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm dữ liệu trong những ô trộn theo cột

Giả dụ tôi có trang tính để nhập xuất vật tư được trích ra theo hình đính kèm
Find.JPG
Như vậy là 1 ngày có thể nhập hay xuất hàng nhiều lần;
Và người ta đã trộn các ô theo cột , nếu chúng có cùng ngày.

Để tìm kiếm dữ liệu của 1 ngày nào đó có bao nhiêu fiếu nhập hay fiếu xuất ta sẽ áp dụng fương thức FIND()

Để vậy, chúng ta cần xác định vùng chứa dữ liệu này; Cách thường nhất là xác định ô đầu tiên của cột có chứa dữ liệu, cũng như ô cuối của cột có chưa dữ liệu theo câu lệnh như sau:
PHP:
 Dim Rng As Range, Cls As Range 
 1   Set Rng = Range([B1], [B65500].End(xlUp))
Nếu ngay sau lệnh này ta áp ngay dòng lệnh
Mã:
MsgBox Rng.Cells.Count
Ta sẽ được câu trả lời là 14
Đây chính là dòng đầu tiên của ô trộn cuối cùng.
Nhưng nếu ta thay cho câu lệnh 1 bằng câu lệnh sau:
PHP:
11  Set Rng = Range([B1], [B65500].End(xlUp).Offset(1))

Tuy nhỏ nhoi, ta lấy thêm 1 ô vô vùng cần tìm, thì macro sẽ cho ta biết số ô trong vùng Rng của chúng ta là 21.

Điều này rất quyệt định đến sự nghiệp tìm kiếm của chúng ta khi áp dụng fương thức FIND()
Ở trường hợp đầu, macro cho chúng ta biết là chả có anh nào ngày 31/1/2014 tuy nó rành rành ra đó.
ở trường hợp sau, macro nó gật ngay tấp lự!
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm dữ liệu trong vùng có trộn ô theo hàng ngang

Bài trước ta đã thấy cách khắc fục khi cần tìm kiếm dữ liệu trong 1 cột có ô trộn theo cột;
Bài này ta sẽ tiếp tục tìm hiểu cách thức tìm kiếm dữ liệu trong cột, nhưng 1 số ô bị/được trộn theo hàng ngang

Xin mời các bạn trước tiên xem hình dưới đây:

TimCotTron.JPG

Như trong file ảnh, người ta lập chi tiết chi fí cho các công trình tại cột [E]
Nhưng khổ nổi, người lập bảng lại trộn 1 số ô theo hàng; Như dòng 6 trộn 3 ô; Dòng 10 trộn 2 ô & dòng 14 trộn 4 ô

Nhiệm vụ macro là tìm ra địa chỉ nơi có chứa nhóm từ 'Cong trình'
(Vì trong VBA không hỗ trợ tiếng Việt, nên ta lách từ 'Công' để nhằm cái chủ iếu của chúng ta là fương thức FIND() trong vùng có ô trộn ngang)

Khi chạy macro sẽ cho kết quả khác nhau trong mỗi vòng lặp.

Để tiện mình xin trích ra đây macro trong hình:

PHP:
Option Explicit
Sub TimHangTron()
 Dim Rng As Range, sRng As Range
 Dim J As Byte, SoLan As Byte
 Dim fAdd As String
 
 For J = 1 To 4
    Set Rng = Range([e1], [e1].End(xlDown)).Resize(, J)
    Set sRng = Rng.Find("Cong trình", , xlFormulas, xlPart)
    If Not sRng Is Nothing Then
        fAdd = sRng.Address
        Do
            SoLan = 1 + SoLan
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> fAdd
    End If
    If SoLan > 0 Then
        MsgBox SoLan, , "Thong báo: " & J
        SoLan = 0
    End If
 Next J
End Sub
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm dữ liệu trong vùng có vài hàng bị ẩn

Chúng ta có 1 CSDL của 1 hội cưu chiến binh theo như hình sau

TimDongAn.JPG

Trong ảnh, ta thấy 1 số dòng vì lí do gì đó, như đi về xứ thần tiên nên đã ẩn đi

Tuy nhiên các hàm trong excel như MATCH(), COUNTA() hay VLOOKUP() vẫn bình thản làm việc với những ô ẩn này.
(Kết quả các hàm được đưa ra trên cột [N] & hàm đã dùng được diễn dịch ở ô bên fải liền kề.)

Nếu ta dùng macro để tìm số liệu đã ẩn như:

PHP:
Option Explicit 

Sub FINDHideCells() 
 Dim Rng As Range, sRng As Range 
  
 Set Rng = Range([K1], [K1].End(xlDown)) 
9  Set sRng = Rng.Find("NVH14", , xlValues, xlWhole) 
 If sRng Is Nothing Then 
    MsgBox "No thing" 
 Else 
    MsgBox sRng.Address 
 End If 
End Sub


Nhưng khi chạy macro này thì sẽ nhận được câu trả lời fủ fàng, rằng "Không có"
:confused: :confused: :confused:

/(/hưng chớ lo, mọi chuyện sẽ khác nếu trong câu lệnh có gán số trong macro, thay vì xài xlValues, ta chuyển nó thành xlFormulas thì kết quả sẽ rất mĩ mãn!

Nếu chưa tin các bạn có thể thử, dù 1 lần để biết!
 
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm trong các Comments

MiMiChau:
Với Macro bài trên, khi em thay xlComments, xlWhole thì không tìm được trong Comments, nhưng khi thay xlWhole thành xlPart lại hoạt động tốt.
Theo em hiểu xlWhole là tìm toàn bộ, còn xlPart là tìm một phần.
Vậy vì sao tìm trong Comments lại phải dùng phương thức tìm một phần? Nhờ các bạn giải thích giùm. Xin cảm ơn nhiều.

Các bạn thử dùng macro này:

PHP:
Sub FINDComments()
 Dim Rng As Range, sRng As Range, MyAdd As String
 
 Set Rng = Range([b1], [c2].End(xlDown))
2 Set sRng = Rng.Find("*", , xlComments, xlWhole)
 If sRng Is Nothing Then
    MsgBox "No Find"
 Else
    MyAdd = sRng.Address
    Do
        MsgBox sRng.Address:                ConNhieuThuKhacNua
6        sRng = Rng.FindNext(sRng)
    Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
 End If
End Sub

Thứ nhất, mình thấy rằng, thêm vô chổ findWhat:= "*" thì ta có thể xài được thông số xlWhole mà macro không đi sai hướng đã định;

Thứ hai: Thay vì mình muốn tìm tất cả các ô chứa Comments thì macro đầu chưa cho ta thỏa mãn về kết quả, chắc phải tìm cách khác . . . (như gọi macro sau đây)

Mã:
[B]Sub ConNhieuThuKhacNua()[/B]
 Dim Comm As Comment
 
 For Each Comm In ActiveSheet.Comments
    If Comm.Author = "GPE.COM" Then
        Comm.Delete
    Else
        MsgBox Comm.Text, , Comm.Author
    End If
 Next
[B]End Sub[/B]

/(hi gọi macro thứ hai này, thì nó liệt kê tất tần tật các Comments có trên trang tính mà ta đang kích hoạt nó


(Có thể trả lời này không đủ thỏa đáng với các bạn gần xa, mong được thông cảm & cùng nhau, chúng ta ngâm cứu tiếp)
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm đến dòng cuối có dữ liệu của 1 trang tính

Người ta có nhiều cách để tìm đến dòng cuối chứa dữ liệu của 1 trang tính;

Sau đây mình xin giới thiệu cách hữu hiệu nhất, đó là ứng dụng fương thức FIND():

PHP:
Sub FindLastRow()
 Dim LastRow As Long
 If WorksheetFunction.CountA(Cells) > 0 Then
      'Search for any entry, by searching backwards by Rows.'
      LastRow = Cells.Find(What:="*", After:=[A1],  SearchOrder:=xlByRows, 	SearchDirection:=xlPrevious).Row
      MsgBox LastRow
 End If
End Sub

Sẵn đây, xin bạn bạn tham khảo luôn cách mà người ta tìm đến cột cuối:

PHP:
Sub FindLastColumn()
 Dim LastColumn As Integer
 If WorksheetFunction.CountA(Cells) > 0 Then
     'Search for any entry, by searching backwards by Columns.'
      LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns,  SearchDirection:=xlPrevious).Column
      MsgBox LastColumn
 End If 
End Sub

& cuối cùng là tìm đến ô cuối chứa dữ liệu

PHP:
Sub FindLastCell()
 Dim LastColumn As Integer,  LastRow As Long
 Dim LastCell As Range
 
 If WorksheetFunction.CountA(Cells) > 0 Then
    'Search for any entry, by searching backwards by Rows.'
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   'Search for any entry, by searching backwards by Columns.'
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    MsgBox Cells(LastRow, LastColumn).Address
 End If 
End Sub
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Xét fương thức FIND() với tùy chọn LookIn:=xlFormulas & XlValues

Để tiện theo dõi diễn biến sự việc, ta nên lấy 1 trang tính trống & tiến hành nhập dữ liệu như chỉ dẫn sau:

Tại [A3] ta nhập số '1' & tại ô dưới nó liền kề ta nhập số '56'

Tại [A6] ta nhập công thức =SUM(A3:A4)

Tại ô [A8] ta nhập công thức tương tự =SUM(A3,A4)

Tại ô [A11] ta nhập chỉ đơn giản là '57'

& tiến hành cho chạy macro như sau:

PHP:
Option Explicit
Sub FindMethodLookin()
'compare find method options LookIn:=xlFormulas vs LookIn:=xlValues'
Dim FtndValue As Range

Set FtndValue = Range("A1:A100").Find(What:="57", After:=Range("A1"), LookIn:=xlFormulas)
If Not FtndValue Is Nothing Then
    MsgBox FtndValue.Address, , "1"             'Returns $A$11  '
End If

Set FtndValue = Range("A1:A100").Find(What:="57", After:=Range("A1"), LookIn:=xlValues)
If Not FtndValue Is Nothing Then
    MsgBox FtndValue.Address, , "2"             'Returns $A$6  '
End If

Set FtndValue = Range("A1:A100").Find(What:="sum", After:=Range("A1"), LookIn:=xlValues)
If Not FtndValue Is Nothing Then
    MsgBox FtndValue.Address, , "3"
End If

Set FtndValue = Range("A1:A100").Find(What:="sum", After:=Range("A1"), LookIn:=xlFormulas)
If Not FtndValue Is Nothing Then
    MsgBox FtndValue.Address, , "4"             '?'
End If 
End Sub

Macro sẽ hiện cho ta chỉ 3 hộp thoại, thiếu hộp thoại mang số '3'

Nhưng để xuất hiện địa chỉ ô [A8] trong hộp thoại số '2' hay số '4' ta cần sửa chi tiết "A1" thành "A6" là được.

Chúc các bạn ngày cuối tuần vui vẻ!
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Fương thức FIND() trong hàm tự tạo

Mình xin ví dụ số liệu cho dễ hiểu:

Vùng từ A1:A8 có những dữ liệu & số liệu như sau {STT, 5, 4, 3, 5, 7, 3, 81}
Tương ứng từ B1:B8 = {Color, Red, Green, Brown, Yellow, Violet, Purple, Orange}​
C1 = 5
Hàm người dùng FINDNTHOCC(A1:A8,C1,2,0,1) có nghĩa là tìm vị trí số lần xuất hiện thứ 2 của C1 (=5) trong dãy A1:A8, thấy vị trí đó rồi chuyển sang 0 row và 1 col có nghĩa là vẫn giữ ở row đó rồi dịch sang 1 cột như vậy sẽ cho ra "Yellow".

Xin các bạn xem nội dung hàm trong file kèm theo

:confused: :cool: :p :)
 

File đính kèm

  • gpeUDF.rar
    8,3 KB · Đọc: 212
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Nếu các bạn cùng với mình, chúng ta không thích xài hàm tự tạo mà xài macro để nó hiển thị kết quả tìm con số 5 ở lần tìm thứ hai, thì ta có macro sau:

PHP:
Option Explicit
Sub FindSecond5()
 Dim Rng As Range, sRng As Range
 Dim Tmp As Byte
 Dim fAdd As String
2
 Set Rng = Range([A1], [A1].End(xlDown))
4 Set sRng = Rng.Find("5", , xlFormulas, xlWhole)
 If Not sRng Is Nothing Then
6    fAdd = sRng.Address
    Do
8        Tmp = Tmp + 1
        If Tmp = 2 Then
10            [e4].Value = sRng.Offset(, 1).Value
            Exit Do
12        End If
        Set sRng = Rng.FindNext(sRng)
14    Loop While Not sRng Is Nothing And sRng.Address <> fAdd
 End If
End Sub

Macro này sẽ đưa ra kết quả tại ô [E4] như câu lệnh có số 10 của macro iêu cầu.
(Xin các bạn xem dữ liệu trong trang tính)

Chú í 1 chút, ta sẽ thấy trong vòng lặp Do . . . Loop có 2 điều kiện (Đ/K) để thoát khỏi nó;
Đ/K đầu như ta biết ghi ở câu lệnh số 10; Đ/K thứ hai ghi ở dòng lệnh mang số 14
Dòng lệnh 14 này ta hiểu rằng sẽ thoát vòng lặp khi trị tìm thấy tại ô có địa chỉ trùng với địa chỉ mà ta đã lưu vô tham biến fAdd ngay từ khi tìm thấy trị '5' lần đầu tiên (Đó là trị ở ô [A2])
Lúc này fương thức FIND() đã đi giáp 1 vòng trong vùng qui định ( của tham biến Rng)

Nhưng trong thực tế, macro chưa đi giáp 1 vòng đã thoát, do tham biến để đếm Tmp đã đạt giá trị 2 cần thiết
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Sao trong hàm tự tạo không thể áp dụng fương thức FINDNEXT()

Sau khi bạn cùng chúng tôi đọc xong bài #9 & #10, sẽ dễ có câu hỏi sinh ra, là:

Sao không áp dụng fương thức FINDNEXT() trong hàm tự tạo?

& Xài nó sẽ như thế nào?

Trước tiên, ta khẳng định với nhau rằng, xài FINDNEXT() trong hàm không bị báo lỗi, nhưng buồn thay, nó không cho kết quả đúng.

Chuyện gì sẩy ra vậy?

Chúng ta xét hàm tự tạo sau:
PHP:
Option Explicit
Function FINDNext_(Rng As Range, Val As Integer, Lan As Byte, Rw As Long, Col As Byte)
 Dim sRng As Range, Rg0 As Range, Cls As Range
 Dim Num As Byte:                               Dim fAdd As String
 On Error GoTo Loi_CT
 
 Set sRng = Rng.Find(Val, , xlFormulas, xlWhole)
 If Not sRng Is Nothing Then
    fAdd = sRng.Address
    Do
        Num = Num + 1
1        Debug.Print sRng.Offset(Rw, Col).Value & " " & Num
        If Num = Lan Then
            FINDNext_ = sRng.Offset(Rw, Col).Value
            Exit Function
        End If
2        Set sRng = Rng.FindNext(sRng)
4    Loop While Not sRng Is Nothing And sRng.Address <> fAdd
6 End If
Err:               Exit Function
Loi_CT:
    MsgBox Error(), , Erl
    Resume Err
    '?FINDNEXT_(range("$A$1:$A$9"),5,2,0,1)'
End Function

Nếu ở 1 ô trống nào đó ta nhập cú fáp =FINDNEXT_(A1:A9,D1,2,0,1)
Ta, thật trớ trêu sẽ nhận được 1 thông báo lỗi ở dòng lệnh mang số 4; Đó là hàm báo lỗi có nội dung như sau:
Object varialble or With block varialble not set

Thực ra, nếu trước khi {ENTER} để hàm thực hiên, ta mở cửa sổ "trung gian" thì trên cừa sổ này sẽ ghi lại kết quả dòng lệnh mang số 1, sẽ như sau:
"Red 1"

Thế mới thấy rằng, tuy không thể hiện trên trang tính kết quả của quá trình FINDNEXT(), nhưng ở đâu đó trong VBE vẩn có kết quả hửng hờ nào đó trước khi báo lỗi!
 

File đính kèm

  • gpeUDF.rar
    11 KB · Đọc: 181
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tô màu trong cột số liệu theo điều kiện

Em có 1 cột dữ liệu chứa số 1 và 2, . . . tại mỗi ô; Em muốn tô màu cho 1 dãy ô liên tiếp có từ 4 ô chứa số 1 trở lên, tương tự cho số 2 cũng vậy;
Trong file em gửi lên là em tô màu thủ công, muốn dùng công thức hay hàm thì em phải làm thế nào vậy? các ace giúp em với.
Sau đây là cách làm với sự trợ giúp của macro:

PHP:
Option Explicit
Sub ToMau4SoTrung()
 Dim Rng As Range, sRng As Range, WF As Object, Rg0 As Range
 Dim J As Byte, Lb As Byte, Ub As Byte, Dem As Byte
 Dim fAdd As String

 Set Rng = Range([B1], [b65500].End(xlUp))
 Set WF = Application.WorksheetFunction
 Lb = WF.Min(Rng):          Ub = WF.Max(Rng)
 For J = Lb To Ub
    Set sRng = Rng.Find(J, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        fAdd = sRng.Address
        Do
            If Rg0 Is Nothing Then
                Set Rg0 = sRng
            ElseIf sRng.Offset(-1).Value = J Then
                Set Rg0 = Union(Rg0, sRng)
            ElseIf sRng.Offset(-1).Value <> J Then
                If Rg0.Cells.Count >= 4 Then
                    Rg0.Interior.ColorIndex = 34 + (Rg0(1).Row Mod 9)
                End If
                If Not Rg0 Is Nothing Then Set Rg0 = Nothing
            End If
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> fAdd
    End If
 Next J
End Sub
 

File đính kèm

  • gpeThuThuat.rar
    10,7 KB · Đọc: 192

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Lọc dữ liệu duy nhất, đếm số lần xuất hiện trùng & không trùng & tính tổng của dữ liệu được lọc

Chúng ta có 1 CSDL quản lí đất đai của 1 địa fương gồm các trường được trích ra như sau

PHP:
Tên chủ hộ |Thôn ấp|NamSinh|Số CMND|Ngày Cấp|Noi Cap  |Tên VC    |Thửa đất|Tờ BĐ| DTích
Lộc V. Bay |Thôn Cò|1979   |...392 |3/2/1999|CA Lg Sơn|Chu T. Nhi|  241   |  34 |174.5
Lí N. Khoa |Thôn Cò|1940   |...337 |6/5/1980|CA Lg Sơn|          |  347   |  48 |481.9
Lí N. Khoa |Thôn Cò|1940   |...337 |6/5/1980|CA Lg Sơn|          |  165   |  22 |430.5
Lý V. Hiệu |Thôn Cò|1961   |...349 |6/6/1980|CA Lg Sơn|Hỏa T. Mòn|  113   |  14 |222.3
Lý V. Hiệu |Thôn Cò|1961   |...349 |6/6/1980|CA Lg Sơn|Hỏa T. Mòn|  422   |  48 |121.9
Lý V. Hiệu |Thôn Cò|1961   |...349 |6/6/1980|CA Lg Sơn|Hỏa T. Mòn|  380   |  48 |186.3
Lý V. Hiệu |Thôn Cò|1961   |...349 |6/6/1980|CA Lg Sơn|Hỏa T. Mòn|  104   |  22 |286.3
Hồ V. Chơi |Bản Đôi|1961   |...352 |6/6/1980|CA Lg Sơn|          |  089   |  61 |228.4
Lộc V. SLơ |Bản Đôi|1964   |...327 |2/2/2001|CA Lg Sơn|Lỏ T. Phòn|  089   |  61 |253.3


/(/hiệm vụ chúng ta bây giờ là tạo ra bảng báo cáo có dạnh như sau:

PHP:
Tên chủ hộ |Thôn ấp|NamSinh|Số CMND|Ngày Cấp|Noi Cap  |Tên VC    |Thửa đất|Tờ BĐ| DTích
Lộc V. Bay |Thôn Cò|1979   |...392 |3/2/1999|CA Lg Sơn|Chu T. Nhi|    1   |  1  |174.5
Lí N. Khoa |Thôn Cò|1940   |...337 |6/5/1980|CA Lg Sơn|          |  2     |  2  |912.40
Lý V. Hiệu |Thôn Cò|1961   |...349 |6/6/1980|CA Lg Sơn|Hỏa T. Mòn|  4     |  03 |222.3
Hồ V. Chơi |Bản Đôi|1961   |...352 |6/6/1980|CA Lg Sơn|          |  01    |  01 |228.40
Lộc V. SLơ |Bản Đôi|1964   |...327 |2/2/2001|CA Lg Sơn|Lỏ T. Phòn|  01    |  01 |253.3

Có nghĩa là các chủ hộ chỉ là danh sách duy nhất;
Tuy nhiên tại cột [Thửa đất] là tổng số lượng thửa mà chủ hộ đang có;
Số tờ bản đồ đất thì lại liệt kê không tính đến tờ trùng mã số
Hơn nữa, diện tích đất toàn hộ được tính tổng diện tích mà hộ đó chiếm hữu;


Sau đây là nội dung macro để thực hiện việc này:

PHP:
Option Explicit
Sub LocDanhSach()
 Dim Rng As Range, sRng As Range, Cls As Range, Sh As Worksheet
 Dim fAdd As String, SoDo As String
 Dim Rws As Long, Thua As Byte, To2 As Byte, DT As Double
 
 Sheets("DuLieu").Select:                       Rws = [D2].CurrentRegion.Rows.Count
 Set Rng = [D1].Resize(Rws)
 Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
    "AA1"), Unique:=True
 Set Sh = ThisWorkbook.Worksheets("TongHop")
 Sh.[D2].CurrentRegion.Offset(1).ClearContents
 For Each Cls In Range([AA2], [AA2].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        fAdd = sRng.Address
        sRng.Offset(, -3).Resize(, 7).Copy Destination:=Sh.Cells(Rws, "A").End(xlUp).Offset(1)
        Do
            Thua = Thua + 1
            DT = DT + sRng.Offset(, 6)
            If InStr(SoDo, CStr(sRng.Offset(, 5).Value)) Then
            Else
                SoDo = SoDo & "@" & CStr(sRng.Offset(, 5).Value)
                To2 = To2 + 1
            End If
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> fAdd
        With Sh.Cells(Rws, "A").End(xlUp)
            .Offset(, 7).Value = Thua:          Thua = 0
            .Offset(, 8).Value = To2:           To2 = 0
            .Offset(, 9).Value = DT:            DT = 0
        End With
        SoDo = ""
    End If
 Next Cls
 Sh.Select
End Sub
 
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm kiếm 1 trong hai tiêu chí

Giả sử tại 1 trường fổ thông trung học nọ tổ chức thi đấu thể thao giữa các đội
Kết quả thi được lập ra như bảng sau:

PHP:
Tên đội  | Môn TT  | Điểm | Xếp hạng
Đội C    |Kéo co   | 5.8  |    II
Đội A    |Kéo co   | 8.0  |    I
Đội C    |Bóng ném | 7.8  |    I
Đội B    |Cờ vua   | 6.0  |    II
Đội C    |Bóng rỗ  | 6.8  |    II
Đội A    |Hít đất  | 8.0  |    I
. . . .  |. . . .  | . . .|. . .

Bây giờ ta cần thống kê theo hai tiêu chí' Hai tiêu chí này được chọn từ ô chọn [G1] có 2 tùy chọn
1 là chọn tên đội; Khi đó sẽ lọc ra các đội đang có ở cột [A]
2 là lọc ra thứ hạng đang có ở cột [D]

Hơn nữa khi ta chọn 1 trong 2 tiêu chí trên thì tại ô [I1] ta sẽ có sẵn danh sách ứng với 1 trong 2 trường hợp ta đã chọn ở [G1]
Có nghĩa là:
Nếu chọn tại [G1] là tên đội, thì tại [I1] sẽ xuất hiện danh sách các đội để ta chọn đội cụ thể nào đó
Còn nếu chọn 'Xếp hạng' thì ta sẽ có danh sách hạng I đến III trong [I1] để ta chọn 1 trong chúng

/(hi ta chọn 1 trong danh sách đỗ xuống tại [I1], ta sẽ được macro sự kiện tìm cho ta những kết quả mà ta mong muốn.
Các bạn xem file sẽ rõ hơn!
 

File đính kèm

  • gpeFind.rar
    12,2 KB · Đọc: 203

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm theo 2 tiêu chí cùng 1 lúc

Như bài trên ta thấy, ta có thể tìm theo từng tiêu chí, hoặc tìm theo đội hoặc tìm theo xếp hạng;

Giờ chúng ta lại muốn tìm trong danh sách đó 1 đội cụ thể nào đó xếp hạng cụ thể nào đó thì fải làm sao đây?

Chúng ta cần sử dụng hai macro sau (mà macro mẹ gọi macro con làm việc theo tham biến mà mẹ truyền cho):

PHP:
Option Explicit
Dim Rng As Range

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim fAdd As String
  
 If Not Intersect(Target, [G3]) Is Nothing Then
    [f5].CurrentRegion.Offset(1).ClearContents
    Set Rng = [A1].Resize([B2].CurrentRegion.Rows.Count)
    GPE Target
 ElseIf Not Intersect(Target, [H3]) Is Nothing Then
    [f5].CurrentRegion.Offset(1).ClearContents
    Set Rng = [D1].Resize([B2].CurrentRegion.Rows.Count)
    GPE Target, -3
 End If
End Sub


Mã:
Sub GPE(Targ As Range, Optional Col As Integer = 3)
 Dim sRng As Range, fAdd As String
 
 Set sRng = Rng.Find(Targ.Value, , xlFormulas, xlWhole)
 If Not sRng Is Nothing Then
    fAdd = sRng.Address
    Do
        If sRng.Offset(, Col).Value = Targ.Offset(, Col \ 3).Value Then
            With [f99].End(xlUp).Offset(1)
                .Resize(, 4).Value = sRng.Offset(, IIf(Col < 0, -3, 0)).Resize(, 4).Value
            End With
        End If
        Set sRng = Rng.FindNext(sRng)
    Loop While Not sRng Is Nothing And sRng.Address <> fAdd
 End If
End Sub

Các bạn sẽ rõ hơn khi xem bài 13 tại: http://www.giaiphapexcel.com/forum/showthread.php?96675-Dùng-Find-thay-cho-Advanced-Filter/page2
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Lập bảng kê theo nhóm hàng

Ta hình dung 1 kho nó có 16 nhóm hàng; mỗi nhóm hàng gồm hơn trăm mặt hàng, như số liệu đưa ra trong 2 cột như sau:
PHP:
Tên hàng | Nhóm hàng
 Coca    |Nước ngọt
 Sting   |Nước ngọt
 Pessi   |Nước ngọt
Trà xanh |Nước ngọt
 Cà chua |Sinh tố
 Rau má  |Sinh tố
 Bí đao  |Sinh tố
 Đu đủ   |Sinh tố
 Dưa leo |Sinh tố
. . . .

Giờ thì nhiệm vụ đề ra là thống kế số liệu theo bảng sau:

PHP:
Nước ngọt | Sinh tố | . . . 
Coca      | Cà chua | . . .
Sting     |Rau má   |
Pessi     |Bí đao   | . . .
Trà xanh  |Đu đủ    | . . .
          |Dua leo  |. . .

Macro sau đây sẽ làm điều đó dưới 1 giây với 1 CSDL hơn 3.350 dòng
PHP:
Sub GPE()
 Dim Rng As Range, Cls As Range, sRng As Range
 Dim Rws As Long:               Dim fAdd As String
 
 Sheet2.Select:                 Tmr = Timer()
 Rws = [b2].CurrentRegion.Rows.Count
 [d3].Resize(Rws, 16).ClearContents
 [b2].Resize(Rws).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
    "AB1"), Unique:=True
 Range("AB2").CurrentRegion.Offset(1).Copy
 Range("D3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
 Application.CutCopyMode = False
 Set Rng = [b2].Resize(Rws)
 Application.ScreenUpdating = False
 For Each Cls In Range([d3], [d3].End(xlToRight))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        fAdd = sRng.Address
        Do
            Cells(Rws, Cls.Column).End(xlUp).Offset(1).Value = sRng.Offset(, -1).Value
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> fAdd
    End If
 Next Cls
 Randomize
 Range([d3], [d3].End(xlToRight)).Interior.ColorIndex = 34 + 9 * Rnd() \ 1
 Application.ScreenUpdating = True
 [d1].Value = Timer() - Tmr
End Sub

Nhưng ta cũng cần biết 1 điều rằng: Có fương thức tiếp cận khác nhanh hơn fương thức FIND() này
Nhưng với dữ liệu & thời gian như vậy, ta cũng có thể chấp nhận được (với vốn kiến thức VBA nhất định)
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Lập danh sách trúng thưởng

Chào cả nhà, nhờ cả nhà giúp mình với.
Mình đang cần gấp, mà mất khá nhiều thời gian mà vẫn chưa tìm được giải pháp:
Làm cách nào để tìm kiếm tên khách hàng dựa vào số tem mà số tem thì nằm ở nhiều cột, nhiều dòng.
Nếu như danh sách mình có 2 người trùng tem nhau thì làm sao để khi quay số 1 lần hiện được tên của 2 người đó ở 2 dòng. Quay 1 lần sẽ có 2 người trúng.
Cảm ơn.
Ví dụ:
PHP:
TT|Mã KH| Họ và Tên   | Tên NVTT |Số thăm|             Thăm số                    |
01|FDS00|Đặng Duy Sơn |Ngô An Di |  09   |02 |03 | 04 | 30 | 14| 09 | 41 | 50 | 37|
02|BTT00|Bùi Thành Tuy|Ngô An Di |  02   |   |   | 104| 149|  |  |  | |
03|NVD00|Ngô Văn Dũng |Ngô An Di |  02   |   |   | 145| 197|  |  |  | |
04|NJH00|Nguyễn Hòang |Lê Văn Bay|  06   |103|404| 30 | 114| 409 | 241 |  | |
05|NAD00|Nguyễn An Dy |Lê Văn Bay|  01   |   |149|      |  |  | |
06|FTN00|Đoàn Tùng Nê |Lê Văn Bay|  06   | 13| 44| 09 | 194| 309 | 341 |  | |

Kết quả ta cần macro xử lí là:
Khi ta nhập giá trị thăm bất kì nào đó tại 1 ô ở cột như [B3] thì ô bên fải của nó xuất hiện Họ tên người tham gia có số thăm đó & ô fải kế tiếp ở cột [D] sẽ là tên nhân viên tiếp thị của CQ bán hàng.

Macro sự kiện tại [B3] có nội dung sau:

PHP:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("B3:B999")) Is Nothing Then
    If Target.Value = "" Then Exit Sub
    Dim fAdd As String
    Dim Rng As Range, sRng As Range, Sh As Worksheet
    Dim Rws As Long, Col As Byte, Dm As Byte
 
    Set Sh = ThisWorkbook.Worksheets("SO TEM")
    Rws = Sh.[c5].CurrentRegion.Rows.Count
    Col = Sh.[c5].CurrentRegion.Columns.Count
    Set Rng = Sh.[f5].Resize(Rws, Col)
    Target.Offset(, 1).Resize(Rws, Col).ClearContents
    Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
    If sRng Is Nothing Then
        Target.Offset(, 1).Value = "Nothing"
    Else
        fAdd = sRng.Address
        Do
            With Target.Offset(Dm, 1)
                .Value = Sh.Cells(sRng.Row, "C").Value
                .Offset(, 1).Value = Sh.Cells(sRng.Row, "d").Value
            End With
            Dm = Dm + 1
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> fAdd
    End If
 End If
End Sub

Các bạn có thể xem thêm tại: http://www.giaiphapexcel.com/forum/...ay-số-may-mắn-Tìm-kiếm-tên-người-trúng-thưởng
 

Tải bộ cài phần mềm Dự toán GXD, Đấu thầu GXD, Thanh Quyết toán GXD, Quản lý chất lượng GXD. Dành cho người mua bản quyền
Kích để xem khóa học Dự toán công trình
Kích để xem khóa học Dự toán công trình
Phần mềm quản lý chất lượng công trình QLCL GXD
Tìm hiểu khóa học Thanh Quyết toán GXD

Các bài viết mới

Top