• Thứ Ba, 23/09/2008 09:04 (GMT+7)

    Lọc dữ liệu từ sheet-sheet trong EXCEL

    Lượt xem 20356
    Đánh giá

    Câu hỏi :
    1.Xin hỏi cách lọc dữ liệu từ 1 sheet Excel sang 1 sheet khác? Nếu dùng AutoFilter thì có thể tính các tổng con được không?
     
     2. Tôi có một file Excel gồm hai sheet, một sheet dùng để nhập giờ tăng ca của nhân viên có tên là "Overtime by Code", còn một sheet có tên là "Result" dùng để lưu kết quả thống kê theo tuần để làm báo cáo. Sheet "Overtime by Code" có các cột sau: STT, Ma NV, Bo phan, Ho va ten, các ngày trong tháng từ 1 tới 31. Tôi muốn hiển thị ở Sheet "Result" trong một khoảng thời gian là một tuần (7 ngày) mỗi bộ phận có bao nhiêu người tăng ca trên 48 tiếng, bao nhiêu người tăng ca trên 60 tiếng, ngày đầu tiên và ngày cuối của khoảng thời gian này sẽ được xác định lúc cần in ra kết quả thống kê, giờ tăng ca nhập vào ở dạng phút. Sheet "Result" có các cột như sau: "Bộ phận", "Từ 48 tiếng đến 60 tiếng", ">60 tiếng". 
     

    Trả lời :

    Yêu cầu thống kê thông tin của bạn rất riêng biệt nên bạn phải lập trình giải quyết. Bạn có thể dùng ngôn ngữ VBA viết 1 macro (1 thủ tục) để xử lý yêu cầu của mình. Thuật toán giải quyết gồm các bước thiết yếu sau:

    - Sắp xếp lại bảng dữ liệu thô theo thứ tự "bộ phận" để dễ thống kê.
    - Duyệt tìm các người cùng bộ phận, tính giờ tăng ca của từng người, kiểm tra giờ tăng ca và tăng "counter" thống kê tương ứng.
    - Mỗi khi chuyển sang "bộ phận" mới, chương trình sẽ lưu kết quả thống kê của "bộ phận" hiện hành lên Sheet "Result".

    Qui trình viết macro trong Excel như sau:
    1. Chạy Excel, dùng chức năng File.Open để mở file Excel chứa dữ liệu đã có (hoặc sẽ có).

    2. Chọn menu Tool.Macro.Macros để mở cửa sổ Macro. Nhập tên macro "Thongke" vao textbox "Macro name", chọn tên file Excel của bạn (thí dụ Yourdata.xls) trong listbox "Macro in", chọn button Create để tạo mới macro này.

    3. Khi cửa sổ soạn code hiển thị, hãy nhập đoạn code VBA do chúng tôi viết dùm bạn như sau vào:
    'macro thống kê số người tăng ca
    Sub Thongke()
    'khai báo các biến cần dùng
    Dim ngayd As Integer
    Dim ngayc As Integer
    Dim str As String
    Dim rgn1 As Range
    Dim rgn2 As Range
    Dim giotc As Double
    'Yêu cầu nhập ngày bắt đầu thống kê
    str = InputBox("Nhap ngay bat dau thong ke (1->31) : ")
    ngayd = CInt(str)
    'Yêu cầu nhập ngày kết thúc thống kê
    str = InputBox("Nhap ngay cuoi cung thong ke (1->31) : ")
    ngayc = CInt(str)
    'thiết lập các vùng cell cần xử lý
    Set rgn1 = Range("OvertimebyCode!B2:AI2000")
    Set rgn2 = Range("Result!A1:C2000")
    'xuất tiêu đề của bảng kết quả
    rgn2.Cells(1, 1).Value = "Ket qua thong ke :"
    rgn2.Cells(2, 1).Value = "Bo phan"
    rgn2.Cells(2, 2).Value = "So nguoi tang ca >=48 gio"
    rgn2.Cells(2, 3).Value = "So nguoi tang ca > 60 gio"
    ikq = 3
    'sắp xếp lại dữ liệu theo "bộ phận"
    rgn1.Sort Key1:=Range("OvertimebyCode!C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    i = 1
    'lặp xử lý
    Do
    sum48 = 0
    sum60 = 0
    bophan = rgn1.Cells(i, 2).Text
    'lặp thống kê từng bộ phận
    Do
    'tính số phút tăng ca của người đang xét
    phuttc = 0
    For j = ngayd To ngayc
    phuttc = phuttc + CInt(rgn1.Cells(i, j + 3).Text)
    Next j
    'kiểm tra và ghi nhận
    giotc = CDbl(phuttc) / 60#
    If (48 <= giotc) And (giotc <= 60) Then sum48 = sum48 + 1
    If (60 < giotc) Then sum60 = sum60 + 1
    i = i + 1
    Loop While bophan = rgn1.Cells(i, 2)
    'xuất kết quả của bộ phận hiện hành
    rgn2.Cells(ikq, 1).Value = bophan
    rgn2.Cells(ikq, 2).Value = sum48
    rgn2.Cells(ikq, 3).Value = sum60
    ikq = ikq + 1
    'qui định hàng cuối của bảng là hàng có cell "bộ phận" trống
    Loop While rgn1.Cells(i, 2).Text <> ""
    End Sub
    4. Chọn menu File.Close and return to Microsoft Excel để quay về cửa sổ bảng tính Excel.

    5. Nếu đã nhập dữ liệu rồi, bạn có thể chạy macro "Thongke" bất kỳ lúc nào bằng cách chọn menu Tool.Macro.Macros, chọn tên macro trong danh sách rồi ấn button "Run".

    Ý kiến phản hồi và bình luận      Gởi ý kiến của bạn ?
    Chuyên mục: Ứng dụng văn phòng