Hỏi cách lọc danh sách HS theo lớp (1 người xem)

[tanlanh]

Mình có danh sách học sinh của 1 khối, mình muốn trích lọc danh sách theo lớp thì dùng hàm gì hè. Nhờ các anh chị giúp sức.
Bảng dữ liệu như sau: Sheet1 là danh sách học sinh toàn khối, Sheet2 là bảng tổng hợp cần trích lọc theo lớp.
Ở sheet2, nếu ô J5 = 7/1 thì sẽ trích lọc toàn bộ danh sách học sinh lớp 7/1 vào khung dưới.
Và khi thay đổi J5 = 7/2 thì cho ra danh sách của lớp 7/2.
Xin các anh chị giúp mình.
Cảm ơn mọi người rất nhiều.

 

[DOSNET]

Cái này dùng VLOOKUP cho đơn giản bạn nhé, tham khảo thêm một số bài LỌC trên diễn đàn

 

[BNTT]

Cái này dùng VLOOKUP cho đơn giản bạn nhé

Bạn Khang đọc không kỹ đề rồi. Chỉ dùng VLOOKUP được sau khi đã tách riêng ra từng lớp.
Còn để tách ra từng lớp, tôi nghĩ chắc phải dùng VBA mới được.

 

[yeudoi]

Có nhiều cách để trích lọc danh sách bạn có thể dùng hàm INDEX, hoặc dùng Advance filter để lọc

 

[tanlanh]

Có nhiều cách để trích lọc danh sách bạn có thể dùng hàm INDEX, hoặc dùng Advance filter để lọc

Nhờ mọi người giúp mình ngay trên file đính kèm. Nói thật mình còn rất "gà" về Excel.
Cảm ơn mọi người.

 

[DOSNET]

Bạn Khang đọc không kỹ đề rồi. Chỉ dùng VLOOKUP được sau khi đã tách riêng ra từng lớp.
Còn để tách ra từng lớp, tôi nghĩ chắc phải dùng VBA mới được.

Vâng, Vlookup chắc không xơi nổi, Em dùng công thức kết hợp với name để lọc !

 

[tanlanh]

thanks phamnhukhang nhiều nhiều. Mình hỏi thêm là khi trích lọc như vậy mình có thể sắp xếp họ tên theo ABC được không hè.

 

[DOSNET]

thanks phamnhukhang nhiều nhiều. Mình hỏi thêm là khi trích lọc như vậy mình có thể sắp xếp họ tên theo ABC được không hè.

Nan giải đấy, nếu gộp Lọc và Sort luôn thì hơi căng. Mình sẽ nghiên cứu và cần mọi người trợ giúp thêm !

 

[boyxin]

Vâng, Vlookup chắc không xơi nổi, Em dùng công thức kết hợp với name để lọc !

Vào Insert \ Name \ Define trong file của bác em phát hoảng, mượn file của bác em chỉnh sửa chút (chỉ cần duy nhất 2 name), dung lượng file giảm đáng kể [từ 162 KB (166,400 bytes) giảm xuống còn 88.5 KB (90,624 bytes) ]

có thể dùng INDEX + MATCH, hoặc VLOOKUP, hoặc OFFSET

 

[nhatthai]

Thêm cách này để tác giả lựa chọn, nhiều khi sử dụng cột phụ cũng đơn giản.
Không hiểu sao file của Boyxin chay nó cứ giật màn hình?

 

[boyxin]

Nan giải đấy, nếu gộp Lọc và Sort luôn thì hơi căng. Mình sẽ nghiên cứu và cần mọi người trợ giúp thêm !

Nếu gộp Lọc và Sort đúng là nan giải nếu chỉ dùng công thức đặc biệt là sort, có thể dùng phương pháp nào đó sort phần nguồn trước rồi lọc sau thì tính khả thi khi dùng công thức sẽ cao
Nếu nhất thiết phải gộp Lọc và Sort thì dùng VBA thì tính khả thi cao, các bác giúp về phần VBA nhé, VBA thì em mù tịt

 

[chibi]

File đính kèm dùng VBA để lọc và sắp xếp.

 

[Mr Okebab]

File đính kèm dùng VBA để lọc và sắp xếp.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dong_cuoi As Integer, rg As Range, so_hs As Integer, _
    gt, i As Integer, dong As Integer
    If Target.Address = "$J$6" Then
        gt = Target.Value
        dong_cuoi = Sheets("DS").Range("B65536").End(xlUp).Row
        Set rg = Sheets("DS").Range("C2:C" & dong_cuoi)
        so_hs = Application.WorksheetFunction.CountIf(rg, gt)
        Range("A8:M5000").Clear
        If so_hs = 0 Then Exit Sub
        dong = 8
        For i = 2 To dong_cuoi
            If Sheets("DS").Range("C" & i).Value = gt Then
                Range("A" & dong).Value = dong - 7
                Range("B" & dong & ":M" & dong).Value = _
                Sheets("DS").Range("D" & i & ":O" & i).Value
                dong = dong + 1
            End If
        Next
        dong_cuoi = Range("B5000").End(xlUp).Row
        Range("B8:M" & dong_cuoi).Sort key1:=Columns("C")
    End If
End Sub

Viết cho gọn lại xíu bác ạ. (Những cái gì dùng 1 lần thì không nên đặt biến bác ạ, nếu muốn tìm kiếm xem có tồn tại không nhanh hơn thì nên dùng Match hoặc phương thức Find bác ạ)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Address <> "$J$6" Then Exit Sub
    Application.ScreenUpdating = True
    Dim HC As Long, i As Long
    HC = 7
    With Sheet2
        .Range("A8:A10000").ClearContents
        For i = 2 To Sheet1.Range("C65000").End(xlUp).Row
            If Sheet1.Range("C" & i).Value = Target.Value Then
                HC = HC + 1
                .Range("A" & HC).Value = HC - 7
                .Range("B" & HC & ":M" & HC).Value = Sheet1.Range("D" & i & ":O" & i).Value
        Next
        .Range("B8:M" & HC).Sort key1:=Columns("C")
    End If
    Application.ScreenUpdating = False
End Sub

 

[SA_DQ]

Trước tiên xin được chúc mừng OKeBAP ở đây!​

Viết cho gọn lại xíu bác ạ. (Những cái gì dùng 1 lần thì không nên đặt biến bác ạ,

1        For i = 2 To Sheet1.Range("C65000").End(xlUp).Row
            If Sheet1.Range("C" & i).Value = Target.Value Then
                HC = HC + 1
                .Range("A" & HC).Value = HC - 7
                .Range("B" & HC & ":M" & HC).Value = Sheet1.Range("D" & i & ":O" & i).Value
        Next
2    Application.ScreenUpdating = False
End Sub

Có 2 vấn đề mà BAP ta khuyên bậy đó, các bạn à:
1*/ Sheet1.Range("C65000").End(xlUp).Row là hoàn toàn không dùng 1 lần đâu; Cứ sau mỗi lần lặp excel sẽ phải kiểm xem đã tới hạn chưa; nếu chưa thì làm tiếp (cũng giống nhu Do . . .Loop vậy thôi);
Nên ta phải
Dim DgCuoi As Long
DgCuoi = Sheet1.Range("C65000").End(xlUp).Row
2*/ Câu lệnh mình đánh số 2 đó là BAP ta hại các bạn sau khi chạy macro cũa BAP đó!

 

[DOSNET]

Vào Insert \ Name \ Define trong file của bác em phát hoảng, mượn file của bác em chỉnh sửa chút (chỉ cần duy nhất 2 name), dung lượng file giảm đáng kể [từ 162 KB (166,400 bytes) giảm xuống còn 88.5 KB (90,624 bytes) ]

có thể dùng INDEX + MATCH, hoặc VLOOKUP, hoặc OFFSET

Có gì đâu mà hoảng bạn, tớ không thích show ra thì tớ nhé vào name, chẳng qua là cách sử dụng name của mỗi người thôi. Thực ra tớ ko thích dùng cột phụ ở sheet TH, mà ko có cột mã thì không thể VLOOKUP vì sẽ có trường hợp trùng họ tên. Dung lượng file của bạn có giảm nhưng có cái nó cũng giảm theo đấy. . Tớ thì iu tiên tốc độ !

Không hiểu sao file của Boyxin chay nó cứ giật màn hình?

 

[Mr Okebab]

1*/ Sheet1.Range("C65000").End(xlUp).Row là hoàn toàn không dùng 1 lần đâu; Cứ sau mỗi lần lặp excel sẽ phải kiểm xem đã tới hạn chưa; nếu chưa thì làm tiếp (cũng giống nhu Do . . .Loop vậy thôi);
Nên ta phải
Dim DgCuoi As Long
DgCuoi = Sheet1.Range("C65000").End(xlUp).Row

Cảm ơn bác, cái vụ này để em xem lại vì chưa thử nghiệm lại xem cái For... Next (or Do...Loop) có tính lại giá trị sau To hay không. Nhưng chắc là Bác SA mà nói thì là có.
Dù sao cũng cảm ơn bác nhiều.

2*/ Câu lệnh mình đánh số 2 đó là BAP ta hại các bạn sau khi chạy macro cũa BAP đó!

Xin lỗi em nhầm (Trên và dưới, bác nhìn vào cũng biết mà). Đáng lý phải là

. . . .
Application.ScreenUpdating = False

.......
Application.ScreenUpdating = True
..................


Thân!

 

[tanlanh]

Mọi người hay ghê. Mấy cái vụ trích lọc bằng VBA thì mình chịu, chẳng biết đường nào mà làm.
Hỏi thêm boyxin cái này. Làm thế nào để tạo danh sách các lớp ở ô J6 như đã làm thế hè. Mình làm hoài chẳng được.

 

[DOSNET]

Cái ô J6 àh bạn ? click vào J6, chọn Data\Validation... Xem tham chiếu của List ở mục Source nhé. Thân !

 

[boyxin]

Mọi người hay ghê. Mấy cái vụ trích lọc bằng VBA thì mình chịu, chẳng biết đường nào mà làm.
Hỏi thêm boyxin cái này. Làm thế nào để tạo danh sách các lớp ở ô J6 như đã làm thế hè. Mình làm hoài chẳng được.

Chọn vào cell J6 rồi vào Data \ validation... sẽ thấy mọi điều​

 

[boyxin]

Dung lượng file của bạn có giảm nhưng có cái nó cũng giảm theo đấy. . Tớ thì iu tiên tốc độ !

Không hiểu sao file của Boyxin chay nó cứ giật màn hình?

Chạy chậm là do CF trang trí thôi, bỏ bớt CF thì file vẫn chạy vù vù

Thử thì mới biết những gì tăng và giảm​

 

[SA_DQ]

Nhưng chắc là Bác SA mà nói thì là có.

BAB lại quá nặng lời với mình rồi!
Cái này mình nghe ai đó trên diễn đàn nói, mình nói lại thôi
Cũng là phỏng chừng vậy thôi chứ không khẳng định được;
Trên cơ sở

 For jJ = 1 to 9
     MsgBox "XIN CHÚC MỪNG THÀNH CÔNG!"
 Next jJ

Cái chữ jJ cuối đó làm mình cho là vị đó nói đúng hơn BAB
BAB thông cảm & tìm cách khẳng định thêm xem sao, nha!

 

[cadafi]

cái vụ này để em xem lại vì chưa thử nghiệm lại xem cái For... Next (or Do...Loop) có tính lại giá trị sau To hay không. Nhưng chắc là Bác SA mà nói thì là có.

Giả định cột A:A không có dữ liệu, khi kích đoạn code này chạy thì giá trị ô A1 là 1, A2 là 2
Khi kích chạy lần 2, lần 3, .... lần n thì hiểu ngay mà.

Sub TEST()
For I = 1 To Sheets("SHEET1").Range("A65000").End(xlUp).Row+1
Sheets("SHEET1").Cells(I, 1) = I
Next I
End Sub

Suy ra sau to thì không tính lại giá trị, chỉ tính lại khi vòng lập được chạy lại. Như vậy, khi nạp tham số vào, đầu tiên VB sẽ xác lập tham số đầu và cuối vào vòng lặp For..next trước rồi mới làm các động tác tiếp theo. Nếu For..Next tính lại giá trị sau To, thì đoạn code trên sẽ lặp lại đến 65001 lần mới thôi à??

Đây cũng là sự khác biệt giữa For...Next và Do..Loop, nếu không có sự khác biệt thì mắc gì ông Microsoft lập ra hai dạng thức này??

 

[boyxin]

Nan giải đấy, nếu gộp Lọc và Sort luôn thì hơi căng. Mình sẽ nghiên cứu và cần mọi người trợ giúp thêm !

Dùng công thức Lọc và Sort luôn ra danh sách HS​

 

[tanlanh]

Hỏi thêm mấy huynh về chuyện lọc.
Nếu mình thêm 1 sheet (sheet: Hocphi - sheet này chỉ lọc họ tên và cột học phí). Sheet hocphi mình cũng muốn lọc theo lớp như "sheet TH", thì trong Define name mình phải add thêm các công thức tương tự (tất nhiên phải thay đổi tên sheet) hay có thể viết chung một công thức khác mà có thể lọc dữ liệu ở cả TH và Hocphi.
Nhờ các huynh chỉ giáo.

 

[boyxin]

Hỏi thêm mấy huynh về chuyện lọc.
Nếu mình thêm 1 sheet (sheet: Hocphi - sheet này chỉ lọc họ tên và cột học phí). Sheet hocphi mình cũng muốn lọc theo lớp như "sheet TH", thì trong Define name mình phải add thêm các công thức tương tự (tất nhiên phải thay đổi tên sheet) hay có thể viết chung một công thức khác mà có thể lọc dữ liệu ở cả TH và Hocphi.
Nhờ các huynh chỉ giáo.

Tham khảo cách lập bảng thu tiền trong phần chữ ký của tôi
"Hỗ trợ công tác chủ nhiệm lớp" pas UnProtect sheet:4b1b7b1b1b5b3​

 

[phucmini]

Vào Insert \ Name \ Define trong file của bác em phát hoảng, mượn file của bác em chỉnh sửa chút (chỉ cần duy nhất 2 name), dung lượng file giảm đáng kể [từ 162 KB (166,400 bytes) giảm xuống còn 88.5 KB (90,624 bytes) ]

có thể dùng INDEX + MATCH, hoặc VLOOKUP, hoặc OFFSET

Bài bác viết khá hay, nhung sheet data phải sắp xếp theo lớp hả, nếu kg kết quả lọc sẽ sai.
có cách nào kg cần sắp xếp kg?

 

[ca_trich]

mấy anh chỉ rỏ dùm em làm cái chổ mà chon cái lớp thì nó tự nhảy được không. em cũng đang cần làm 1 file giống như trên vậy

 

[Gà Công Nghệ]

xin ad lọc ds lớp bằng vba cho file này giùm (mình ko biết gì về vba hết)

 

[thangbithu]

Mình góp thêm 1 cách bằng hàm nhưng không sắp xếp được thứ tự
Trong sheet tổng hợp

B1=IF(ROWS(th!$B$7:$B7)<=COUNTIF(ds!$C$2:$C$124;th!$J$5);INDEX(OFFSET(ds!$C$1;MATCH(th!$J$5;ds!$C$2:$C$124;0);;COUNTIF(ds!$C$2:$C$124;th!$J$5);8);ROWS(th!$B$7:$B7);2);"")

C1=IF(ROWS(th!$B$7:$B7)<=COUNTIF(ds!$C$2:$C$124;th!$J$5);INDEX(OFFSET(ds!$C$1;MATCH(th!$J$5;ds!$C$2:$C$124;0);;COUNTIF(ds!$C$2:$C$124;th!$J$5);8);ROWS(th!$B$7:$B7);3);"")

 

[giao]

Lọc bằng Filter cho nó nhanh đỡ phức tạp. Công thức tổng dùng SUBTOTAL. Nhanh, gọn, đẽ hiểu.

 

[Gà Công Nghệ]

Xin cam on ad. Minh copy doan ma dan vo, nhung stt no khong sap xep duoc. Ad co the lam gium minh 1 cach hoan chinh di. Cam on ad nhieu.

 

[Gà Công Nghệ]

ad loc gium file mẫu danh sách lớp giùm. cam on ad nhieu lam.

 

[Gà Công Nghệ]

xin ad loc gium em file nay cho hoan chinh, xin cam on ad nhieu lam.

 

[hunghung82]

Em muốn lọc danh sách học sinh cấp 1, cấp 2, cấp 3 thì phải làm như thế nào các anh chị? Em không chuyên về excell

 

[Lê Duy Thương]

Em muốn lọc danh sách học sinh cấp 1, cấp 2, cấp 3 thì phải làm như thế nào các anh chị? Em không chuyên về excell

Bạn nên đưa file mẫu thì sẽ được trợ giúp nhé, ở đây mà bạn đưa file hình ảnh như vậy thì không ai rãnh mà ngồi gõ lại dữ liệu cho bạn.

 

[hunghung82]

Bạn nên đưa file mẫu thì sẽ được trợ giúp nhé, ở đây mà bạn đưa file hình ảnh như vậy thì không ai rãnh mà ngồi gõ lại dữ liệu cho bạn.

Cảm ơn bạn, hình ảnh này là ví dụ thôi, mình muốn nhờ mọi người chỉ cho mình công thức để mình làm trên file khác. File đó còn đang dở dang và chưa nhập hết danh sách.

 

[SA_DQ]

Cảm ơn bạn, hình ảnh này là ví dụ thôi, mình muốn nhờ mọi người chỉ cho mình công thức để mình làm trên file khác. File đó còn đang dở dang và chưa nhập hết danh sách.

File giả lập không cần đủ số lượng; Miễn chất lượng nó đủ đầy!
Có nghĩa là lớp nào cũng có & cấp nào cũng có, đa dạng nhưng không nhiều về số liệu.

 

[Gà Công Nghệ]

Paste code này vào sheet2 (Chon Lop)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$4" Then
    Range("H5").Value = Range("I4").Value
    Sheet1.Range("A7:F1000").AdvancedFilter 2, Range("H4:H5"), Range("A7:F7")
End If
End Sub

Bạn cho mình hỏi code này không sắp xếp số thứ tự. Giờ muốn sắp xếp số thứ tự thì điều chỉnh code như thế nào vậy bạn.

 

[Ba Tê]

Bạn cho mình hỏi code này không sắp xếp số thứ tự. Giờ muốn sắp xếp số thứ tự thì điều chỉnh code như thế nào vậy bạn.

Thử thêm 1 dòng này trên End If

Range("A8", Range("A8").End(xlDown)) = "=Row()-7"

 

[Gà Công Nghệ]

Thử thêm 1 dòng này trên End If

Range("A8", Range("A8").End(xlDown)) = "=Row()-7"

Dạ, vâng. Stt đã được. Thầy cho em hỏi thêm là đa số danh sách thường có dòng cuối là: Danh sách này có.....học sinh. Vậy có cách nào lọc được danh sách và đếm số học sinh được không thầy. Nếu lớp 1A có 50 học sinh thì dòng cuối sẽ là: Danh sách này có 50 học sinh. Em xin cám ơn thầy ạ.