Dùng Macro lọc dữ liệu bảng trong Excel (2 người xem)

handung107 · 20/6/06

Trích www.echip.com.vn

Giả sử bạn có một bảng chứa danh sách lý lịch nhân viên (hoặc bất cứ danh sách gì như ở dưới):

Sheet1:

Và bạn muốn chỉ lấy lý lịch những nhân viên có mã số 0252, 0253, .... Nếu bạn chọn từng người trong danh sách (có thể là vài trăm đến vài nghìn nhân viên) thì sẽ rất mất thời gian.
Để thực hiện nhanh, ta sẽ tạo macro lọc những nhân viên cần lấy như sau:
- Tạo vòng lặp từ đầu tới cuối của danh sách mã số nhân viên, lấy từng mã số NV, sau đó tạo tiếp vòng lặp để duyệt từ đầu tới cuối danh sách lý lịch, khi tìm thấy thì cho ngừng vòng lặp và tạo tiếp một vòng lặp khác duyệt qua các cột của mẫu tin tìm thấy để chép (copy) sang Sheet2.
- Thực hiện:
+ Tạo một sheet để chứa kết quả lọc, (Sheet2)
+ Tạo một sheet chứa mã NV:

Sheet3:

Trong Excel, vào menu Tools\Macro\Visual Basic Editor:
Trong cửa sổ bên trái, nhấn đúp vào VBAProject để bung các nhánh con trong đó ra. Bạn sẽ thấy có ba sheet mà mình tạo ở trên, nhấn đúp vào một sheet bất kỳ để mở cửa sổ soạn thảo code của sheet đó, sau đó thực hiện soạn mã như ở dưới:

Sub CopyRecord()
Dim i, j, k, n, n1 As Integer
Dim temp
'n số mẫu tin của sheet 3
'n1 số mẫu tin của sheet 1
n = 100
n1 = 1500
'vòng lặp duyệt từ mẫu tin thứ 2 đến mẫu tin cuối cùng của sheet chứa mã nhân viên(trong ví dụ là 100)
For i = 2 To n
'lưu giái trị của hàng thứ i, cột thứ 2 của sheet3 vào biến temp (trong ví dụ là mã NV).
'như vậy biến temp sẽ lần lượt chứa mã số nhân viên của từng người có trong danh sách
temp = Sheet3.Cells(i, 2)
'vòng lặp duyệt từ mẫu tin thứ 2 đến mẫu tin cuối cùng của sheet chưa danh sách nhân viên
For j = 2 To n1
'kiểm tra nếu mã nhân viên trong Sheet1 (danh sách nhân viên) trùng với biến temp thì tạo tiếp vòng lặp duyệt qua các cột của mẫu tin tìm thấy.
If temp = Sheet1.Cells(j, 2) Then
k = 2
Do While Not IsEmpty(Sheet1.Cells(j, k))
'Cập nhật các ô vào sheet2
Sheet2.Cells(i, k) = Sheet1.Cells(j, k)
k = k + 1
Loop
Exit For
End If
Next
Next
End Sub

Sau khi đã viết xong code, ta nhấn F5 để chạy macro này, chờ vài giây để macro lọc dữ liệu, sau khi maco thực hiện xong nhiệm vụ, bạn hãy mở Sheet2 ra coi. Cha cha! nó đã copy đầy đủ theo danh sách mã số của bạn.
Nếu bạn muốn marco tự động chạy thì thực hiện như sau:
Macro chạy mỗi khi bạn thay đổi dữ liệu trong sheet lý lịch: Chép đoạn mã trên (không chép hàng đầu tiên và cuôi cùng: Sub CopyRecord và End Sub). Nhấn đúp vào Sheet1 để mở cửa sổ code, trong listbox bên trái phía trên của cửa số code, chọn Worksheet, bên phải chọn Change, bạn sẽ thấy như sau:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Dán đoạn mã đã copy vào trong thủ tục này. Bây giờ thì mỗi lần bạn thay đổi dữ liệu trong Sheet1 thì macro sẽ tự động cập nhật lại dữ liệu. Nếu dữ liệu quá nhiều và bạn phải làm việc thường xuyên trên Sheet1 thì không nên dùng cách này vì nó sẽ làm chậm quá trình xử lý của bạn (nhớ xóa đoạn mã cũ sau khi bạn đã copy nó sang sheet khác). Bạn có thể áp dụng cách này để có thể làm được nhiều việc khác nữa.

Đào Việt Cường · 20/6/06

Làm việc với MSE, cần khai thác các tính năng sẵn có!

Dear handung107,
-----------------
Theo em thì phương pháp này không được khuyến khích sử dụng trong MS Excel. Nói cách khác phương pháp này chỉ thích hợp trong lập trình Visual Basic mà không liên quan đến Excel.
Một cách thông thường mà em vẫn thường làm để in tất cả các sổ cái là tận dụng các phương thức và hành động của các đối tượng trong workbook. Em thấy việc nêu thêm một phương pháp nữa trong việc trích lọc dữ liệu trong MS Excel là cần thiết vì theo em cách mà chị giới thiệu sẽ dẫn người lập trình không khai thác tối đa các đặc trưng cơ bản của MSE - đối tượng mà chúng ta đang làm việc.
Vẫn bài toán trên, em minh hoạ phương pháp của em thông qua việc sử dụng phương thức Filter hoặc tính năng cao cấp Advandced Filter. Việc lựa chọn 1 trong 2 phương thức này tuỳ thuộc vào số điều kiện trích lọc. Trong bài toán này có hơn một điều kiện lọc nên em chọn phương pháp lọc Advandced Filter:
Bước 1: Xây dựng vùng điều kiện lọc (Criteria range)
Điều kiện lọc phải đảm bảo độ chính xác các chỉ tiêu cần lọc. Nghĩa là các cột điều kiện phải thuộc bảng dữ liệu. Trong bài tập này, em có thể sử dụng bảng điều kiện của chị (chỉ cần một cột là Mã NV).
Bước 2: Trích lọc bằng thủ tục lọc ra Bảng tạm (Temporary sheet)
Bảng tạm là bảng kết quả của Advanfilter do đó bảng này chứa ít nhất cột điều kiện lọc (Mã NV). Tuỳ thuộc vào thông tin kết xuất mà ta có thể lấy ra bao nhiêu cột trên bảng nguồn. Đôi khi chúng ta có thể đưa luôn kết quả lọc này vào báo cáo.
Thủ tục lọc được minh hoạ như sau:

Sub Range_AdvancedFilter (rgFilterRange as Range, rgCriteriaRange as Range, rgCopytoRange as Range)
rgFilterRange.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopytoRange
End Sub

So sánh thủ tục này với một vòng duyệt khoảng vài 1.000 dòng chúng ta sẽ thấy sự khác biệt đáng kể.

Bước 3: Căn chỉnh báo cáo (Report sheet)
Đây là công việc đương nhiên phải làm nhưng hầu hết chúng ta coi nhẹ nó. Để in một sổ cái cần thiết phải hiệu chỉnh sao cho dữ liệu luôn sẵn sàng để in. Phương pháp đơn giản nhất là sử dụng MSE Template (*.xlt) để tạo ra các mẫu có sẵn và sử dụng lại.
Hi vọng đây cũng là một phương pháp giúp cho công việc kế toán của chúng ta tiết kiệm được thời gian hơn - dù chỉ là vài tích tắc!

SA_DQ · 20/6/06

=DFINDns( Sh1 As Object, Sh2 As Object, Str1 As String, Str2 As String)

Chắc tác gia bên echip viết không hết ~ gì đã làm! Họ chỉ hé mở 1 phần việc. . . .
Tôi vừa hoàn thành hàm =DFINDns( Sh1 As Object, Sh2 As Object, Str1 As String, Str2 As String) lọc bảng nhân sự từ 2 sheets:
Đề bài gồm Sheet1 như trên; Sheet2 chứa các trường dữ liệu như sau:
QHe( [MaNV], [QH], [HoTen]. . .); trường [QH] gồm B: bố; M: mẹ; Ch: chồng; V: vợ; A: anh, em; C: chị, em; C1, C2, C3. . .: các con. Bàng này quan hệ 1 - nhiều với CSDL chính.
Yêu cầu nêu ra là nếu nhập Str1 ='Ch' & Str2 = 'V' thì hàm sẽ trả về là bảng dữ liệu liệt kê ~ trường: [MaNV], [Ten], [QH], [HoTen] của ~ người đã có vợ hoặc chồng trong CSDL Sh1
Đúng như Việt Cường nêu; nên dùng lọc Advance ban đầu để tiết kiệm nguồn lực! Tiện đây nhờ VCường & các bạn góp ý cho Sub này với:

Mã:

[B]Sub LocNhSu()[/B] [COLOR="Blue"]' Keyboard Shortcut: Ctrl+Shift +N[/COLOR]
 Dim SChu As String [COLOR="blue"] ' Loc Du Lieu & Copy Sang Sheet 'S0"[/COLOR]
     Application.ScreenUpdating = 0
     Sheets("S0").Select:                    Columns("AA:AG").Select
     Selection.ClearContents
     Application.Goto Reference:="GiaDinh"
    Range("GiaDinh").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("ChGDinh"), Unique:=False
     Range("GiaDinh").Select:                 Selection.Copy
     Sheets("S0").Select:                    Range("AA1").Select
     ActiveSheet.Paste
     Sheets("S1").Select:                    Application.CutCopyMode = False
On Error Resume Next
    ActiveSheet.ShowAllData
[B]End Sub[/B]

Tháng sau mới được phép bà nhà gởi hàm =DFINDns(Sh1 As Object, Sh2 as Object, StrC As String) lên, nếu ai đó có nhu cầu!

Đào Việt Cường · 21/6/06

Dear SA_DQ,
------------
Trong chủ đề này em muốn nhấn mạnh đến cách thức để xây dựng một thủ tục VBA trên MSE.
Chúng ta phải thừa nhận rằng việc xây dựng một thủ tục trước hết để giải quyết yêu cầu bài toán và điều quan trọng nữa là cần quan tâm đến tính hiệu quả và tối ưu của giải pháp. Hãy so sánh 2 ví dụ sau đây để thấy rằng lập trình trên Excel không giống như lập trình "không phải với Excel":

Public Sub AutoFill_ByForNext()
Dim rgSothutu As Range
Dim i As Long
Set rgSothutu = Range("A10:A30010")
For i = 1 To 30000
rgSothutu.Cells(i) = i
Next
End Sub

và thủ tục:

Public Sub AutoFill_ByFormula()
Dim rgSothutu As Range
Set rgSothutu = Range("A10:A3009")
rgSothutu.Formula ="=Row()-9"
End Sub

Còn về thủ tục LocNhSu(), em thấy thủ tục này còn mang nặng "màu sắc Macro": Sử dụng nhiều hành động. Chúng ta tối ưu mã lệnh bằng cách hạn chế các hành động Select, Copy-Paste. Ví dụ
Thay vì:
Range("GiaDinh").Select
Selection.Copy
thì có thể viết thành:
Range("GiaDinh").Copy
Thay vì:
Range("GiaDinh").Copy
Sheets("S0").Select
Range("AA1").Select
ActiveSheet.Paste
thì có thể viết thành:
Range("GiaDinh").Copy Sheets("S0").Range("AA1")
Em có một vài ý kiến nhỏ như vậy, rất mong lĩnh hội thêm các kinh nghiệm từ anh chị!

workman · 9/7/06

Đào Việt Cường đã viết:
[/COLOR]thì có thể viết thành:
Range("GiaDinh").Copy Sheets("S0").Range("AA1")
Em có một vài ý kiến nhỏ như vậy, rất mong lĩnh hội thêm các kinh nghiệm từ anh chị!

Cường thân mến

Tôi không phải là dân lập trình chuyên nghiệp, nên thường có cách làm hơi khác. Bạn xem giúp xem hộ.

Thông thường khi ta làm lệnh copy - paste, thì viết lệnh như Cường là ổn lắm rồi. Nhưng thực tế thông thường cái mà ta cần đâu phải là copy-paste, mà là lệnh lấy dữ liệu ở sheet này chuyển sang sheet kia.

Lúc này ta làm lệnh

Sheets("S0").Range("AA1") = Range("GiaDinh")

Lúc này, excel không phải dùng lệnh paste copy cổ điển và tốn bộ nhớ, mà chỉ đơn giản là một lệnh gán giá trị thuần túy.

Tôi nhận thấy lệnh copy - paste ngoài việc chuyển giá trị còn tham chiếu rất nhiều dữ liệu khác như format, border.., do đó khi làm một vòng lặp nhiều ô, xử lý trên nhiều sheets, thì thời gian chờ đợi sẽ là khá lâu.

Theo kinh nghiệm của tôi thì việc gán giá trị thay vi dùng lệnh copy - paste sẽ làm cho chương trình chạy nhanh RẤT đáng kể.

binhlt · 7/8/07

Giup minh voi ban oi

Mình nhờ bạn giup vấn đề này nhé:
Mình có 1 bảng tính như File đính kèm
Yêu cầu đưa ra là:
- Vấn đề thứ nhất: Tạo 1 Form trong đó khi nhập tên LN bao nhiêu đấy (Nhập mã LN ở cột Mã cán bộ) và một nút TIM KIEM thì nó tự động tìm đến LN đó và đưa ra tất cả các thông tin của LN cần tìm và đưa sang một Sheet khác.
- Vấn đề thứ 2: Lấy dữ liệu 7 ngày gần đây hoặc những ngày kế tiếp của cột (NGAY DEN HAN) và đưa ra một cột mới gọi là cột (Ngày lưu ý),

ThuNghi · 7/8/07

LN42001040: LN
LN42001041: LN
HOANGMH : ??? lấy mã gì
Bạn muốn dùng công thức hay VBA.
Bạn làm 1 report mẫu có những yêu cầu của bạn
Nếu dùng công thức thì sử dụng index, match và Vlookup hay dùng advance filter.

Nguyễn Duy Tuân · 7/8/07

binhlt đã viết:
Mình nhờ bạn giup vấn đề này nhé:
Mình có 1 bảng tính như File đính kèm
Yêu cầu đưa ra là:
- Vấn đề thứ nhất: Tạo 1 Form trong đó khi nhập tên LN bao nhiêu đấy (Nhập mã LN ở cột Mã cán bộ) và một nút TIM KIEM thì nó tự động tìm đến LN đó và đưa ra tất cả các thông tin của LN cần tìm và đưa sang một Sheet khác.
- Vấn đề thứ 2: Lấy dữ liệu 7 ngày gần đây hoặc những ngày kế tiếp của cột (NGAY DEN HAN) và đưa ra một cột mới gọi là cột (Ngày lưu ý),

Từ CSDL của bạn, hứng lên tôi tạo một loạt sổ luôn.

binhlt · 7/8/07

binhlt gưi lại File

Mình gửi lại File mẫu cho các bạn
Nhờ các bạn giúp

Nguyễn Duy Tuân · 7/8/07

Bạn xem kỹ file tôi đã gửi đi, tất cả đã làm rồi, chỉ cần vào các sheet "Lọc..." thay đổi trong ô là báo cáo tự cập nhật.

Bảng dữ liệu DATA của bạn kể lên đến 40.000 dòng vẫn chạy nhanh thôi.

chungocoai · 29/11/08

các anh chị cho em nhờ chút với.
hiện em có 1 thư mục với 30 workbook.(files excel ấy ạ)
trong mỗi sheets lại có các trường như: HOTEN, LUONG, NGAYCONG, HESO
nhưng nó sắp xếp lộn xộn, không theo thứ tự nào cả, vì dữ liệu theo hàng ngày nên em nhặt khổ quá.
bây giờ tạo một báo cáo cho từng người theo tháng mà nhặt mãi không xong.
anh chị chỉ cho em cách nào để có thể làm 1 files excel để mỗi lần click chuột là em có thể liên kết các file đó lại và đưa ra một báo cáo chi tiết cho từng người.
em xin chân thành cảm ơn!

sealand · 29/11/08

Bạn đưa File mấu cần tổng hợp và 1 vài file dữ liệu (nhớ xóa bớt cho nhẹ) lên thì chắc là có cách tổng hợp . Nghe bạn nói khó hình dung quá.

yeudoi · 30/11/08

Mình nghĩ 30 file mà sắp sếp các trường trong đó không theo thứ tự thì rất khó tạo báo cáo đấy bạn ah.có lẽ chúng ta nên tạo một chủ đề về các cách để lập dữ liệu và đưa vào báo cáo, bởi việc lọc dữ liệu chắc là ai sử dụng excel cũng rất cần. Nhưng mà để có một phương pháp tối ưu để lọc thì không phải ai cũng biết.Vì vậy mong mọi người cùng thảo luận tiếp.

nguyenvietviet · 21/5/09

Dùng Macro lọc dữ liệu

Các bạn dùng phương pháp rất hay nhưng phải up ví dụ lên chứ.
Tôi có một file dùng macro lọc nhưng nó xử lý dữ liệu rất chậm.
Tôi dùng macro ở sheet "bk" lọc theo 2 điều kiện : Mã tàu và mã tổ của sheet "NKNX".
File của tôi xử lý rất chậm dữ liêu nhập vào ở sheet " nknx" và lọc ở sheet " BK".
Mong các bạn xem qua và chỉ giáo giúp tôi. Có cách nào lọc nhanh hơn không?

ndu96081631 · 21/5/09

nguyenvietviet đã viết:
Các bạn dùng phương pháp rất hay nhưng phải up ví dụ lên chứ.
Tôi có một file dùng macro lọc nhưng nó xử lý dữ liệu rất chậm.
Tôi dùng macro ở sheet "bk" lọc theo 2 điều kiện : Mã tàu và mã tổ của sheet "NKNX".
File của tôi xử lý rất chậm dữ liêu nhập vào ở sheet " nknx" và lọc ở sheet " BK".
Mong các bạn xem qua và chỉ giáo giúp tôi. Có cách nào lọc nhanh hơn không?

Bạn nói sao chứ tôi mở file, lọc thử thấy chạy vèo vèo
Có khi nào "chậm" là do bạn mở thêm file khác không ---> vì khi mở 1 file có vấn đề về tính toán thì sẽ ảnh hưởng tốc độ đến tất cả file khac ---> Bạn kiểm tra lại thử
(cái này sao nhìn giống "sản phẩm" của mình thế nhỉ)

nguyenvietviet · 26/5/09

Đúng. Nó là sản phẩm của bạn.
Nhưng nó chạy rất chậm khi nhập nhiều dữ liệu ở sheet " nknx".
Hay bạn có thể sửa lại: Khong cho nó chạy tự động macro ở sheet "bk" mà thêm nút "Thực hiện" thì nó mới chạy.
Bạn xem lại nhé!

ndu96081631 · 26/5/09

nguyenvietviet đã viết:
Đúng. Nó là sản phẩm của bạn.
Nhưng nó chạy rất chậm khi nhập nhiều dữ liệu ở sheet " nknx".
Hay bạn có thể sửa lại: Khong cho nó chạy tự động macro ở sheet "bk" mà thêm nút "Thực hiện" thì nó mới chạy.
Bạn xem lại nhé!

Vậy có lẻ bạn phải kiểm tra lại máy tính của bạn!
Tôi đang thử nghiệm với dử liệu giả lập 20.000 dòng vẫn chạy vèo vèo
Advanced Filter thì không thể chậm được bạn à.. Trừ phi bạn có mở thêm các file khác hoặc máy tính bạn có vấn đề mà thôi!

sealand · 26/5/09

Thực sự ban nên xem lại máy tính của bạn. Mình test trênmáy xách tay mà không kịp hiện đồng hồ cát

nguyenvietviet · 29/5/09

Máy của minh cấu hình thuộc loại cũng khủng đấy! Chậy văn phòng không thành vấn đề.
A cho mình hỏi chút "ndu":
Mình muốn chỉ lọc theo mã hàng được không?
Ví dụ: Cũng là D11 tên hàng là Dây hàn CO2 và Dây hàn trần thì lúc nó lọc tổng thì ra Dây hàn CO2 là 1000 kg và Dây hàn trần là 1000 kg.
Giờ mình muốn chỉ lọc một D11 thôi nghĩa là chỉ Dây hàn CO2: 1000 kg hoặc Dây hàn trần 1000 kg.
Mong bạn sớm có câu trả lời.

quynhcvn · 2/11/10

Dùng Advance filter là có thể giải quyết được bài toán lọc như trên. Tuy nhiên em có một bài toán khác, mong các cao thủ chỉ giúp:
Có 2 vùng dữ liệu khác nhau, nhưng lại có những dữ liệu giống nhau. Vùng dữ liệu A và vũng dữ liệu B có các dữ liệu giống nhau C:

Như vậy, làm thế nào để lọc ra các dữ liệu giống nhau C??? +-+-+-+

Em đang gặp phải trường hợp này, nhưng suy nghĩ mại mà chưa có cách giải quyết. Nếu phương pháp tìm thủ công thì rất mất thời gian vì dữ liệu lớn. Mong mọi người chỉ giúp. Thanks

Dùng Macro lọc dữ liệu bảng trong Excel (2 người xem)

Người dùng đang xem chủ đề này

Thành viên gắn bó

Cu Tí sành điệu

/(hông là gì!

Cu Tí sành điệu

Thành viên chính thức

Thành viên mới

Hãy cho rồi sẽ nhận!

Nghị Hách

File đính kèm

Thành viên mới

Nghị Hách

Thành viên mới

Thành viên gạo cội

Thành viên gắn bó

Thành viên hoạt động

Huyền thoại GPE

Thành viên hoạt động

Huyền thoại GPE

Thành viên gạo cội

Thành viên hoạt động

Thành viên mới

Đếm ngược thời gian