Giúp: lọc nội dung theo 1 điều kiện và chuyển sang sheet khác (1 người xem)

[phuongcnt]

Thân chào các bạn "giải pháp excel" mình có vấn đề cần các bạn giúp, đó là mình muốn lọc nội dung theo một điều kiện ở một sheet và sau đó tự động sao chép sang sheet khác.
Có file đính kèm
Trân trọng cảm ơn!

 

[Hoàng Trọng Nghĩa]

Hình như bạn muốn dùng macro để thao tác trên file của bạn?

Bạn phải mô tả rõ hơn là sau khi điều kiện lọc tại cột Bộ Môn, thì bảng lọc sẽ gồm những cột nào, sau đó chép vào sheet nào. Bạn cho 1 VD cụ thể nhé.

 

[phuongcnt]

Đúng rồi đó Bạn Nghĩa ah! mình muốn dùng Macro, điều kiện lọc là nếu Bộ môn là CNOTO ở bảng TRUONG thì được chuyển qua bảng 1 (CNOTO) còn cột thì giống nhau (form mẫu như nhau) gồm tất cả các cột từ TT cho đến Chủ nhật.
cảm ơn bạn!

 

[Hoàng Trọng Nghĩa]

Đúng rồi đó Bạn Nghĩa ah! mình muốn dùng Macro, điều kiện lọc là nếu Bộ môn là CNOTO ở bảng TRUONG thì được chuyển qua bảng 1 (CNOTO) còn cột thì giống nhau (form mẫu như nhau) gồm tất cả các cột từ TT cho đến Chủ nhật.
cảm ơn bạn!

Tức là điều kiện chọn là mã gì thì copy sang sheet tương ứng với mã đó phải không? Và Nguồn là sheet TRƯỜNG phải vậy không?

 

[phuongcnt]

Nguồn là Sheet TRUONG còn nếu bomon là CNOTO thì chép tất cả các hàng có CNOTO đó qua Sheet 1 (K.CNOTO)

 

[Hoàng Trọng Nghĩa]

Nguồn là Sheet TRUONG còn nếu bomon là CNOTO thì chép tất cả các hàng có CNOTO đó qua Sheet 1 (K.CNOTO)

Tôi có 1 đề nghị, Chỉ dùng 2 sheet đó là Sheet TRUONG và sheet KHOA được không? Vì chỉ cần chọn 1 mã thì mã đó đã lọc một bộ môn vào sheet KHOA này rồi, không cần dùng nhiều sheet.

Bạn cho tôi đầy đủ các mã bộ môn đi, tạm thời tôi biết có 6 mã:

CB
CNOTO
COKHI
DIEN
KT
NNTH

Mã nhìn chưa hay lắm vì số ký tự không đều nhau, nhưng đó cũng không là vấn đề.

 

[phuongcnt]

Đúng rồi bạn Nghĩa a!
Tức là thế này, mình bỏ bớt nội dung để file nhẹ tải lên thôi, thực tế trong sheet tổng TRUONG có rất nhiều dữ liệu trong 1 tuần, rất nhiều môn của 6 khoa đó, sau khi mình nhập dữ liệu trong sheet tổng TRUONG rồi thì những Sheet còn lại gồm K.CNOTO, K.COKHI, ... sẽ tự động nhận dữ liệu từ sheet tổng, mục đích là 6 sheet đầu nhận dữ liệu từ sheet tổng và khóa ở đây là bomon.

 

[Hoàng Trọng Nghĩa]

Đúng rồi bạn Nghĩa a!
Tức là thế này, mình bỏ bớt nội dung để file nhẹ tải lên thôi, thực tế trong sheet tổng TRUONG có rất nhiều dữ liệu trong 1 tuần, rất nhiều môn của 6 khoa đó, sau khi mình nhập dữ liệu trong sheet tổng TRUONG rồi thì những Sheet còn lại gồm K.CNOTO, K.COKHI, ... sẽ tự động nhận dữ liệu từ sheet tổng, mục đích là 6 sheet đầu nhận dữ liệu từ sheet tổng và khóa ở đây là bomon.

Trong sheet1 có cột CH/KSX thừa so với sheet TRƯỜNG, vậy thì cột này với mục đích gì? Cột đó có cần không? Tôi thường hỏi rất kỹ trước khi bắt tay vào việc vì tôi ghét phải chỉnh sửa khi đã làm xong.

 

[Hai Lúa Miền Tây]

Đúng rồi bạn Nghĩa a!
Tức là thế này, mình bỏ bớt nội dung để file nhẹ tải lên thôi, thực tế trong sheet tổng TRUONG có rất nhiều dữ liệu trong 1 tuần, rất nhiều môn của 6 khoa đó, sau khi mình nhập dữ liệu trong sheet tổng TRUONG rồi thì những Sheet còn lại gồm K.CNOTO, K.COKHI, ... sẽ tự động nhận dữ liệu từ sheet tổng, mục đích là 6 sheet đầu nhận dữ liệu từ sheet tổng và khóa ở đây là bomon.

Dùng 1 sheet nguồn và 1 sheet trích lọc. Sheet nguồn là sheet để nhập liệu, sheet trích lọc là sheet dùng để truy vấn. Không cần nhiều sheet thế, ta cần dữ liệu của trường nào thì ta chỉ cần chọn nó ra thôi.

 

[phuongcnt]

File đó mình gửi lên bị thiếu cột đó rồi, bên sheet TRUONG vẫn có cột đó, nhưng chắc mình xóa nhầm, cột đó vẫn có (CH/KXH) cột đó không quan trọng, khóa vẫn là cột bomon (6 khoa đó). Khung mẫu ở các sheet giống như nhau!
Cảm ơn anh nhiều ak!

 

[Hoàng Trọng Nghĩa]

File đó mình gửi lên bị thiếu cột đó rồi, bên sheet TRUONG vẫn có cột đó, nhưng chắc mình xóa nhầm, cột đó vẫn có (CH/KXH) cột đó không quan trọng, khóa vẫn là cột bomon (6 khoa đó). Khung mẫu ở các sheet giống như nhau!
Cảm ơn anh nhiều ak!

Sẳn đây tôi muốn tính cho bạn luôn phần này:

Tổng số gv:
Tổng số gv tham gia giảng dạy:
Tổng giờ giảng dạy trong tuần:
Tổng số lớp được giảng dạy:

Vậy cách tính thế nào để tôi cho nó tự động tính cho bạn luôn!

 

[phuongcnt]

Trước tiên mình xin cảm ơn sự nhiệt tình của bạn, thật lòng cảm ơn đó.
Cách tính như sau:
Tổng số gv: cái này là số cứng nghĩa là số nhân sự của khoa (cố định)
Tổng số gv tham gia giảng dạy: là tổng số gv khoa đó giảng dạy trong tuần, nếu lặp chỉ tính 1 thôi
Tổng giờ giảng dạy trong tuần: được tính là nếu từ tiết 1-5 là 5 tiết, 6-10 là 5 tiết 11-14 là 4 tiết
Tổng số lớp được giảng dạy: là sum từ trên xuống dưới của số lớp, đếm tất cả các lớp xuất hiện trong sheet
-- Các cách tính trên nói chung không quan trọng lắm---
Một lần nữa cảm ơn bạn nhiều!

 

[Hoàng Trọng Nghĩa]

Trước tiên mình xin cảm ơn sự nhiệt tình của bạn, thật lòng cảm ơn đó.
Cách tính như sau:
Tổng số gv: cái này là số cứng nghĩa là số nhân sự của khoa (cố định)
Tổng số gv tham gia giảng dạy: là tổng số gv khoa đó giảng dạy trong tuần, nếu lặp chỉ tính 1 thôi
Tổng giờ giảng dạy trong tuần: được tính là nếu từ tiết 1-5 là 5 tiết, 6-10 là 5 tiết 11-14 là 4 tiết
Tổng số lớp được giảng dạy: là sum từ trên xuống dưới của số lớp, đếm tất cả các lớp xuất hiện trong sheet
-- Các cách tính trên nói chung không quan trọng lắm---
Một lần nữa cảm ơn bạn nhiều!

Trước khi nói đến vấn đề tính toán, tôi đã làm thử như sau:

1) Tôi thêm 1 sheet Nguon, mục đích là bạn dùng sheet này, cụ thể là cột A để thêm bớt mã khoa, khi thêm mã, bạn bấm vào nút "Cập nhật Mã khoa" bạn nhé.

Thủ tục của nó:

Sub TaoMaKhoa()
      NGUON.Range("A2", NGUON.Range("A65536").End(xlUp)).Name = "KhoaHoc"
      MsgBox "Ma khoa da duoc cap nhat!", vbInformation, "Thông báo"
End Sub

2) Tại sheet KHOA tại ô C2 bạn chọn trong List Validation để chọn mã, sau khi chọn mã, sẽ lọc những gì thuộc về mã này từ sheet TRUONG vào sheet KHOA.

Thủ tục như sau:

Khi tác động lên sheet tại ô C2:

Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$C$2" Then Call LocKhoa
End Sub

Nếu đúng mã sẽ chạy thủ tục:

Sub LocKhoa()

      With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
                  
                  Dim r As Long, KhoaID As String
                  KHOA.Range("A12:AA65536").ClearContents
                  
                  KhoaID = KHOA.Range("C2").Value
                  If KhoaID = "" Then GoTo ExitSub
                  
                  r = TRUONG.Range("B65536").End(xlUp).Row
                  If r < 8 Then MsgBox "CSDL chua duoc nhap gi ca!", vbCritical, "Thông báo": GoTo ExitSub
      
                  
                  Dim sArray As Variant, FilterArr As Variant
                  Dim c As Long, h As Long, n As Long, v As Long
                  
                  sArray = TRUONG.Range("B8:B" & r).Resize(, 26).Value
                  
                  v = UBound(sArray): n = 1: r = 0
                  
                  ReDim FilterArr(1 To v, 1 To 27)
                  For h = 1 To v
                        If sArray(h, 3) = KhoaID Then
                              r = r + 1: n = n + 1
                              FilterArr(r, 1) = n
                              For c = 2 To 27
                                    FilterArr(r, c) = sArray(h, c - 1)
                              Next
                        End If
                  Next
                  
                  If r Then
                        KHOA.Range("A12").Resize(r, 27).Value = FilterArr
                  Else
                        MsgBox "Ma khoa nay chua co trong CSDL!", vbInformation, "Thông báo"
                  End If
ExitSub:
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .ScreenUpdating = True
      End With
End Sub

Bạn kiểm tra lại xem có đúng ý mình chưa rồi tính tiếp cho bạn phần tính toán bạn nhé.

 

[Hoàng Trọng Nghĩa]

Còn một vấn đề này nữa, tôi thấy trong mỗi cột S, C, T có các ô chứa những cái này:

A
(11-14)

Vậy nó là gì? Số tiết? Cấu trúc của nó có những dạng như thế nào? Lẽ ra bạn nên đưa ra dữ liệu tổng quát tôi sẽ tính cho bạn một cách nhanh chóng mà không cần hàm của Excel.

 

[phuongcnt]

Chân thành cảm ơn Bạn Nghĩa!
Mình đã hiểu rồi, nhiệt tình, uy tín và tài năng.
cảm ơn bạn nhé!

 

[Hoàng Trọng Nghĩa]

Chân thành cảm ơn Bạn Nghĩa!
Mình đã hiểu rồi, nhiệt tình, uy tín và tài năng.
cảm ơn bạn nhé!

Với lần cải tiến này, tôi nghĩ sẽ đúng với ý đồ của bạn nhiều hơn:

1) Với tiêu đề cột bạn sẽ gõ thủ công (Thứ hai (12/11)? Cũng vậy Tên bảng "THỜI KHÓA BIỂU ..." bạn cũng gõ thủ công?

Thì tôi đã kết hợp Công thức và định dạng để cho tiêu đề cột, tên bảng sẽ tự động "điền vào chỗ trống" cho bạn!

2) Bạn sẽ quá vất vã với các công thức tính số giáo viên tham gia giảng dạy? Thì tôi đã sử dụng hàm tự tạo để tính cho bạn!

Function DemKhongTrung(ByVal SrcArray As Variant) As Long
      If IsArray(SrcArray) Then
            Dim TmpArr, Tmp
            TmpArr = SrcArray
            With CreateObject("Scripting.Dictionary")
                  .CompareMode = vbTextCompare
                        For Each Tmp In TmpArr
                              If Not .Exists(Tmp) And Tmp <> "" Then .Add Tmp, ""
                        Next
                        DemKhongTrung = .Count
                  .RemoveAll
            End With
            Erase TmpArr
      Else
            DemKhongTrung = IIf(Trim(SrcArray) = "", 0, 1)
      End If
End Function

Vì thế, mọi sự đều tự động khi bạn nêu rõ chi tiết của mình thì sẽ dễ dàng có kết quả như ý:

Sub LocKhoa()

      With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
                  
                  Dim r As Long, KhoaID As String
                  KHOA.Range("A12:AA65536").ClearContents
                  KHOA.Range("C5:C7").ClearContents
                  
                  KhoaID = KHOA.Range("C2").Value
                  If KhoaID = "" Then GoTo ExitSub
                  
                  r = TRUONG.Range("B65536").End(xlUp).Row
                  If r < 8 Then MsgBox "CSDL chua duoc nhap gi ca!", vbCritical, "Thông báo": GoTo ExitSub
      
                  
                  Dim sArray As Variant, FilterArr As Variant
                  Dim c As Long, h As Long, n As Long, v As Long, Cnt As Long
                  Dim ArrItem, CalItem, Itm
                  sArray = TRUONG.Range("B8:B" & r).Resize(, 26).Value
                  
                  v = UBound(sArray): n = 1: r = 0: Cnt = 0
                  
                  On Error Resume Next
                  
                  ReDim FilterArr(1 To v, 1 To 27)
                  For h = 1 To v
                        If sArray(h, 3) = KhoaID Then
                              r = r + 1: n = n + 1
                              FilterArr(r, 1) = n
                              For c = 2 To 27
                                    ArrItem = sArray(h, c - 1)
                                    FilterArr(r, c) = ArrItem
                                    If c > 6 And Len(Trim(ArrItem)) > 0 Then
                                          CalItem = Trim(ArrItem)
                                          Itm = Left(CalItem, InStr(CalItem, "(") - 1)
                                          Cnt = Cnt + Abs(Evaluate(Replace(CalItem, Itm, ""))) + 1
                                    End If
                              Next
                        End If
                  Next
                  
                  If r Then
                        KHOA.Range("A12").Resize(r, 27).Value = FilterArr
                        KHOA.Range("C5").Value = DemKhongTrung(KHOA.Range("C12:C" & 11 + r))
                        KHOA.Range("C6") = Cnt
                        KHOA.Range("C7") = r
                  Else
                        MsgBox "Ma khoa nay chua co trong CSDL!", vbInformation, "Thông báo"
                  End If
ExitSub:
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .ScreenUpdating = True
      End With
End Sub

Bạn xem file nhé! Nếu công thức tính số tiết chưa đúng thì bạn báo ngay cho tôi chỉnh sửa!

 

[phuongcnt]

Tuyệt vời, hay quá bạn Nghĩa ơi! phải nói quá tuyệt vời, đúng là trong excel còn nhiều cái chúng ta chưa khai thác hết/
Cực hay!
--Ah! nếu mình muốn lấy luôn Ô tô màu của bảng tổng sang Sheet KHOA có được không?
--Và tổng số giáo viên thì tùy mỗi KHOA sẽ có số giáo viên khác nhau, vd: CNOTO: 14, COKHI:11,...
--Bạn có thể khởi tạo NGUỒN, khi nhập Họ và tên giáo viên thì Ô KHOA bên cạnh sẽ tự động điền không? ví dụ: khi gõ NGUYỄN HOÀNG THUYẾT thì Ô của cột KHOA sẽ tự biết là CNOTO
Cảm ơn bạn!

 

[Hoàng Trọng Nghĩa]

Tuyệt vời, hay quá bạn Nghĩa ơi! phải nói quá tuyệt vời, đúng là trong excel còn nhiều cái chúng ta chưa khai thác hết/
Cực hay!
1) --Ah! nếu mình muốn lấy luôn Ô tô màu của bảng tổng sang Sheet KHOA có được không?
2) --Và tổng số giáo viên thì tùy mỗi KHOA sẽ có số giáo viên khác nhau, vd: CNOTO: 14, COKHI:11,...
3) --Bạn có thể khởi tạo NGUỒN, khi nhập Họ và tên giáo viên thì Ô KHOA bên cạnh sẽ tự động điền không? ví dụ: khi gõ NGUYỄN HOÀNG THUYẾT thì Ô của cột KHOA sẽ tự biết là CNOTO
Cảm ơn bạn!

1) Ô màu vàng là ô nào vậy bạn?

2) Bạn đưa file thật lên đi, nói chung chung khó hình dung

3) Nói chung là bạn cứ đưa file thật lên, một cách tương đối đầy đủ và muốn tính gì làm gì, bạn cho một sheet riêng để nói kết quả mong muốn sẽ như thế nào thì tôi mới có thể làm tiếp cho bạn, chứ "thầy bói xem voi" nguy hiểm lắm.