Mừng Sinh Nhật GPE 11: sáng 25/6 Hà Nội; 1/7 Huế/Đà Nẵng; Trưa 2/7 TPHCM

Code lọc theo 4 điều kiện., gom 2 cột thành 1 cột & tách 1 cột thành 2 cột theo ĐK

Thảo luận trong 'Lập Trình với Excel' bắt đầu bởi Hong.Van, 31 Tháng một 2013.

  1. Hong.Van

    Hong.Van Busy

    Em chào Thầy cô & anh chị!
    Viết code giúp em để lọc theo 4 điều kiện, trong đó gom 2 cột thành 1 cột & tách 1 cột thành 2 cột theo ĐK như sau:
    I/ Em muốn lọc từ Sheet TH sang Sheet Loc theo 4 điều kiện
    (ĐK 1) Từ ngày (Cell D6)
    (ĐK 2) Đến ngày (Cell D6)
    (ĐK 3) Mã Tài Khoản (Cell H6)
    (ĐK 4) Mã Khách hàng (cell H7)

    Trong đó có 3 điều kiện bắt buộc phải có là điều kiện ĐK 1, ĐK 2, ĐK 3. Còn điều kiện ĐK 4 có thì lọc, không có thì bỏ qua

    II/ Trong File em có làm 3 ví dụ về các điều kiện lọc, Bây giờ em lấy Sheet "Lọc - Ví dụ Lọc 4 ĐK" để giải thích:
    1/ Ở Sheet Lọc
    a/ Dòng 12 (Số dư đầu kỳ) là cthức , để em viết
    b/ Sheet Loc sẽ lấy các cột AB, AC, AD, AF, AH của Sheet TH sang
    c/ Cột F (Tài khỏan) của Sheet Loc sẽ lấy từ cột AI và AJ và sẽ lấy những số tài khỏan khác với số tài khoản tại cell H6
    d/ Cột G, cột H (Nợ-Có) của Sheet Lọc sẽ lấy cột AK của Sheet TH như sau
    + Nếu tài khoản cell H6 có ở cột AI thì số tiền sẽ ở cột G
    + Ngược lại, Nếu tài khoản cell H6 có ở cột AJ thì số tiền sẽ ở cột H
    e/ Dòng "Cộng phát sinh" sẽ tổng cộng các dòng trên (trừ dòng số dư đầu kỳ)
    f/ Dòng "Số dư cuối kỳ" sẽ lấy theo nguyên tắc cthức như sau:
    + Cell G31
    PHP:
    =MAX(($G$12+G30-$H$12-H30);0)
    + Cell H31
    PHP:
    =MAX(($H$12+H30-$G$12-G30);0)
    Vì bài này em không viết được, nên em nhờ Thầy cô & anh chị giúp đỡ. Em cảm ơn!
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 31 Tháng một 2013
  2. Hoàng Trọng Nghĩa

    Hoàng Trọng Nghĩa .: Never Stand Still :. Staff Member

  3. Hong.Van

    Hong.Van Busy

    Với các bài lọc mà lấy nguyên cột qua thì em làm được! nhưng bài này có gộp & tách cột nên em không biết cách giải quyết nó! vì thế em nhờ Thầy cô & anh chị giúp em! Em cảm ơn!
     
  4. Hoàng Trọng Nghĩa

    Hoàng Trọng Nghĩa .: Never Stand Still :. Staff Member

    Hình như trong sheet Loc, 2 tên cột cuối ở file bạn không đúng tiêu đề thì phải? Tài khoản qua Số tiền?

    Bạn cho số dư đầu kỳ ở sheet nào luôn đi, đã làm code thì loại luôn công thức cho rồi!
     
    Lần chỉnh sửa cuối: 31 Tháng một 2013
  5. Hong.Van

    Hong.Van Busy

    Thành tiền của Sheet Lọc là như vậy đó Nợ - Có

    Cái này thì em viết được! Vì số tiền nó sẽ lấy từ tài khoản qua, hay là công nợ qua và lấy từ đầu kỳ của tháng nào nữa! Cái này thì em viết được sẽ đưa vào code sau!
    Em cảm ơn!
     
  6. ndu96081631

    ndu96081631 Ăn cùng GPE, Ở cùng GPE, Sống cùng GPE Staff Member Super Moderator

    Mấy cái vụ lọc, dù mấy điều kiện cũng chẳng khó khăn gì. Vấn đề là đếch có hiểu kế toán nên xem xong file vẫn thấy.. tối hù như trời 30
    Ẹc... Ẹc...
     
  7. Hong.Van

    Hong.Van Busy

    Thực ra cái này đơn giản lắm thầy, chì là điều kiện thôi! Nợ - Có giống như 1 - 0, nếu kg ở bên này thì sẽ ở bên kia!
     
  8. Hoàng Trọng Nghĩa

    Hoàng Trọng Nghĩa .: Never Stand Still :. Staff Member

    Không hiểu ở chỗ này, Mã TK (ô H6) thì có 2 cột, vậy sao biết lấy cột nào vì mình không là dân kế toán.
     
  9. VetMini

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

    Đại loại là bạn có một bảng tổng hợp phát sinh và cái bạn muốn là bảng phát sinh giao dịch trong tháng cho từng khách hàng (thắc mắc nhỏ: tại sao bạn mua mà gọi đối tượng là khách hàng?)

    Làm theo kiểu code không cần nhanh chậm thì không khó lắm. Tuy nhiên:

    Hình như bảng tổng hợp của bạn chỉ có khoản thanh toán chứ không có khoản phát sinh khác? Những khoản này lấy ở đâu ra?

    Gợi ý: thường thường ở cái bảng mẫu (cái template ở sheet Loc) ta chừa ra 1 dòng trống để ghi dữ liệu vào. Khi ghi, ta bắt đầu từ dòng trống, insert dòng mới vào và ghi. Sau khi ghi xong thì xoá dòng trống đi. Mục đích là để khỏi mất công kẻ lại mấy cái đường viến bảng, copy/paste style cho mệt.
     
  10. Hong.Van

    Hong.Van Busy

    Nó lấy ở cả 2 cột AI và AJ của Sheet TH luôn. Một trong hai bên, Bên nào có thì lấy!
     
  11. VetMini

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

    Những con số phát sinh trong bảng tổng hợp này đi cặp với nhau. Ví dụ như trong bảng là phát sinh thanh toán khoản nợ, cứ một cái vào bên nợ người bán (331) thì cái kia vào bên có ngân quỹ (1121). Ta chỉ cần chọn mấy tài khoản bắt đầu bằng "1" là đúng.

    Tuy nhiên, tôi không thể diễn giải thêm vì bảng tổng hợp trên không có chứa con số phát sinh lúc nhập hàng.

    (*) Nợ/Có là từ chuyên môn của kế toán.
     
  12. Hoàng Trọng Nghĩa

    Hoàng Trọng Nghĩa .: Never Stand Still :. Staff Member

    Đúng là khó hiểu thiệt, bởi cái sheet bạn đưa kết quả 4DK đó thì ô H6 có TK 131 thế nhưng cột Tài Khoản lại có rất nhiều mã TK khác nhau, lẽ ra tất cả là 131 chứ???

    Vì không hiểu rõ được cấu trúc nên khó lòng làm cho bạn được, tôi phải hỏi cặn kẻ.
     
  13. Hong.Van

    Hong.Van Busy

    1/ Đây là bảng tổng hợp phát sinh tất cả cho 1 năm
    2/ Đối tượng khách hàng bao gồm cả khách mua và khách bán, đồng thời có thể thêm các đối tượng khác như fải thu, fải trả khác ... Gom chung lại, mục đích là để theo dõi công nợ!
    3/ Dòng cột fát sinh và số dư cuối kỳ cũng từ code tạo ra, chỉ cần xác định được dòng cuối, còn các các đường viền Broder Thì mình có thể viết code được
    Cảm ơn sự góp ý của bạn!
     
  14. Hong.Van

    Hong.Van Busy

    A, sorry anh nó là tài khỏan 331
     
  15. Hoàng Trọng Nghĩa

    Hoàng Trọng Nghĩa .: Never Stand Still :. Staff Member

    Tôi hình dung nó như thế này, điều kiện là một tài khoản nào đó, thì khi lọc đúng anh này ở cột này, thì sẽ lấy giá trị của anh kia. Và số tiền của anh này ở cột nào thì nằm ở bên Nợ hay Có của TK đó phải không?

    Ôi tình yêu! Khó dữ vậy ta?
     
  16. Hong.Van

    Hong.Van Busy

    Đúng rồi đó anh!!!!!!!!!!!!!
     
  17. VetMini

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

    Bổ túc:

    Xin lỗi, tôi mới nhìn lại, trước đó sheet TH bị lọc mất các phát sinh chỉ chừa lại phần chuyển khoản thanh toán. Sau khi show ra hết rồi thì thấy như sau:

    Trong 2 cột nợ/có (AI/AJ), ta chọn bên tài khoản bắt đầu bằng số "1" (thực sự tài khoản kia chỉ là tài khoản công nợ, bắt đầu bằng số "3"). Nếu nguyên ở bên Có thì qua bên Loc chuyển thành Nợ, và ngược lại.
     
  18. Hong.Van

    Hong.Van Busy

    Em xin mô tả cách Sort bằng tay để lấy 4 điều kiện như sau: (lấy tháng 01, tài khoản 331, mã KH: M020)
    Tại Sheet TH,
    1/Sort cột AE lấy T01
    2/Sort cột AG lấy M020
    -----------
    3/ Sort cột AI lấy 331
    Chép các cột AB, AC, AD, AF, AH qua Sheet Loc
    Chép cột AJ sang cột F
    Chép cột AK sang cột G
    -----------
    4/ xả cột AI, rồi Sort cột AJ lấy 331
    Chép các cột AB, AC, AD, AF, AH qua Sheet Loc
    Chép cột AI sang cột F
    Chép cột AK sang cột H
    -------------
    Sau đó Sort lại Sheet Loc theo thứ tự từ nhỏ đến lớn!
     
  19. quanghai1969

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

    Bạn mô tả kiểu võ lâm thì mình làm theo kiểu thiếu lâm tự nhé, còn thiếu xíu nữa là hoàn chỉnh bạn tự bổ sung nhé. Khi nào siêng thì rút gọn code lại tí
    Đúng ra Sub này nên tách ra làm 2 sub. Nếu H7 <> "" thì chạy 1 Sub và ngược lại
    PHP:
    Sub loc()
    Dim Data(), Res(), ijk
    [A13:H5000].ClearContents
    Data 
    Sheet1.Range(Sheet1.[AB9], Sheet1.[AB65536].End(3)).Resize(, 10).Value
    ReDim Res
    (1 To UBound(Data), 1 To 8)
    For 
    1 To UBound(Data)
       If 
    Data(i1) >= [D5].Value Then
          
    If Data(i1) <= [D6].Value Then
             
    If [H7].Value "" Then
                
    If Data(i9) = [H6].Value Or Data(i8) = [H6].Value Then
                   k 
    1
                   Res
    (k1) = Data(i1)
                   
    Res(k2) = Data(i2)
                   
    Res(k3) = Data(i3)
                   
    Res(k4) = Data(i5)
                   
    Res(k5) = Data(i7)
                   If 
    Data(i8) = [H6].Value Then Res(k6) = Data(i9)
                   If 
    Data(i9) = [H6].Value Then Res(k6) = Data(i8)
                   If 
    Data(i8) = [H6].Value Then Res(k7) = Data(i10)
                   If 
    Data(i9) = [H6].Value Then Res(k8) = Data(i10)
                
    End If
             Else
                If 
    Data(i6) = [H7].Value Then
                    
    If Data(i9) = [H6].Value Or Data(i8) = [H6].Value Then
                      k 
    1
                      Res
    (k1) = Data(i1)
                      
    Res(k2) = Data(i2)
                      
    Res(k3) = Data(i3)
                      
    Res(k4) = Data(i5)
                      
    Res(k5) = Data(i7)
                      If 
    Data(i8) = [H6].Value Then Res(k6) = Data(i9)
                      If 
    Data(i9) = [H6].Value Then Res(k6) = Data(i8)
                      If 
    Data(i8) = [H6].Value Then Res(k7) = Data(i10)
                      If 
    Data(i9) = [H6].Value Then Res(k8) = Data(i10)
                   
    End If
                
    End If
             
    End If
          
    End If
       
    End If
    Next
    1
    Res
    (k7) = "=sum(R13C:R[-1]C)"
    Res(k8) = "=sum(R13C:R[-1]C)"
    [A13].Resize(k8) = Res
    End Sub
     
    Lần chỉnh sửa cuối: 31 Tháng một 2013
  20. quanghai1969

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

    Khi nào không thêm được dòng Max vào thì thử thế này
    k = k + 1
    Res(k, 7) = "=sum(R13C:R[-1]C)"
    Res(k, 8) = "=sum(R13C:R[-1]C)"
    Res(k + 1, 7) = "=Max(R12C+R[-1]C-R12C[1]-R[-1]C[1],0)"
    Res(k + 1, 8) = "=Max(R12C+R[-1]C-R12C[-1]-R[-1]C[-1],0)"

    [A13].Resize(k + 1, 8) = Res
     

Chia sẻ trang này