Tổng Quan Về Fương Thức Tìm Kiếm ( Find Method) Trong Exxcel

• Diễn đàn Bài viết mới
• Có gì mới? Featured content Bài viết mới Hoạt động mới nhất
• Phần mềm Quản lý chất lượng GXD Dự toán GXD Dự thầu GXD Quyết toán GXD
• Đăng ký học
• Quản lý dự án Học dự toán Tra cứu định mức online Hồ sơ chất lượng Tư liệu Quản lý dự án Lập dự án đầu tư Thanh quyết toán Đấu thầu và lựa chọn nhà thầu

Đăng nhập Đăng ký Có gì mới?

• Bài viết mới

Menu Đăng nhập Đăng ký Install the app Install How to install the app on iOS

Follow along with the video below to see how to install our site as a web app on your home screen.

Ghi chú: This feature may not be available in some browsers.

• Diễn đàn
• CÔNG TY GXD: TƯ VẤN - PHẦN MỀM - ĐÀO TẠO
• Phần mềm (PM) và giải pháp GXD
• Tin học văn phòng
• Word, Excel, PowerPoint
• Microsoft Excel VBA

You are using an out of date browser. It may not display this or other websites correctly.You should upgrade or use an alternative browser. Tổng quan về fương thức tìm kiếm ( Find Method) trong Exxcel

• Khởi xướng Khởi xướng Sa DQ
• Ngày gửi Ngày gửi 24/6/14
• Tags Tags Không có

S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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, SearchOrder và MatchByte 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 FindNext và FindPrevious để 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: 25/6/14 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 gpeBaiTap.rar 23,7 KB · Đọc: 340

S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 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ự! S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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: 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 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 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ó" /(/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: 27/6/14 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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. Click to expand...

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) S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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ẻ! S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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". Click to expand...

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

File đính kèm

• gpeUDF.rar gpeUDF.rar 8,3 KB · Đọc: 315

Last edited by a moderator: 13/7/14 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 gpeUDF.rar 11 KB · Đọc: 273

Last edited by a moderator: 31/7/14 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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. Click to expand...

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 gpeThuThuat.rar 10,7 KB · Đọc: 301

S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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: 26/8/14 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 gpeFind.rar 12,2 KB · Đọc: 300

S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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 S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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) S

Sa DQ

Thành viên năng động

Tham gia 17/6/14 Bài viết 76 Điểm tích cực 16 Đ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. Click to expand...

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ên Gửi trả lời Share: Facebook X Bluesky LinkedIn Pinterest WhatsApp Email Share Link

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

• D Cầu thang sắt nghệ thuật điểm nhấn cho không gian sống
  • Latest: Dương Văn Long
  • Hôm qua, lúc 10:23
  Ô tô xe máy tham gia giao thông
• X Gợi ý xe đạp điện cho học sinh cấp 2 đi học
  • Latest: xedienxanhsaigon
  • Hôm qua, lúc 10:12
  Ô tô xe máy tham gia giao thông
• Đ Địa chỉ sản xuất phụ kiện xe tải uy tín, chất lượng
  • Latest: ĐinhthịngocAnh
  • Hôm qua, lúc 08:56
  Ô tô xe máy tham gia giao thông
• Xe khách Hyundai dẫn sóng thị trường năm 2026
  • Latest: hyundaiviethan
  • Thứ năm lúc 08:20
  Ô tô xe máy tham gia giao thông
• Ống mềm inox 304 chịu nhiệt, Khớp nối mềm cao su nối ren, khớp nối mềm cao su mặt bích, khớp nối mềm inox mặt bích, khớp nối mềm inox DN65
  • Latest: Vattukythuatqt
  • Thứ ba lúc 23:58
  Máy và thiết bị chuyên dụng
• X Top mẫu xe máy 50cc nhỏ gọn, dễ chạy, ít hao xăng
  • Latest: xedienxanhsaigon
  • Thứ ba lúc 15:44
  Ô tô xe máy tham gia giao thông
• Đ Nguồn cung cấp sỉ sản lề lá xe tải uy tín cho xưởng đóng thùng & garage xe tải
  • Latest: ĐinhthịngocAnh
  • Thứ hai lúc 21:57
  Ô tô xe máy tham gia giao thông
• Xu Hướng Xe Tải Việt Nam Giai Đoạn 2025–2026: Hyundai, Teraco Và Foton Được Dự Báo Dẫn Đầu Thị Trường
  • Latest: hyundaiviethan
  • Thứ hai lúc 10:24
  Ô tô xe máy tham gia giao thông
• Đ Có nên sử dụng móc buộc bạt xe tải, móc bạt ô tô cho thùng xe tải?
  • Latest: ĐinhthịngocAnh
  • 13/12/25
  Ô tô xe máy tham gia giao thông
• L Kiểm định nhà xưởng định kỳ – Việc nên làm sớm thay vì xử lý sự cố muộn
  • Latest: lylinh15
  • 13/12/25
  Hoạt động diễn đàn

• Diễn đàn
• CÔNG TY GXD: TƯ VẤN - PHẦN MỀM - ĐÀO TẠO
• Phần mềm (PM) và giải pháp GXD
• Tin học văn phòng
• Word, Excel, PowerPoint
• Microsoft Excel VBA

• Chào Khách. Để tải bộ cài phần mềm GXD mời bạn kích vào đây

Back Top