Nhờ lọc cột ngày tháng (cột A) tùy theo điều kiện của cột lãi, lỗ (cột B) (1 người xem)

Dauthivan · 6/12/11

Mục đích của em là lọc những ngày tháng tại cột A theo điều kiện cột B (sheet1) thỏa mãn cả 2 điều kiện:
1. Chỉ lọc ngày tháng nào mà có giá trị cột B là số dương (>0) tức lãi.
2. Những khoảng thời gian nào mà có cùng số lãi như nhau thì chỉ lọc ra ngày đầu và ngày cuối (bỏ qua không lọc khoảng thời gian của các ngày giữa).

Kết quả lọc sẽ được thể hiện ở cột A của sheet2

em ví dụ về kết quả file đính kèm của em thì kết quả lọc ra sẽ là các ngày 26/4/2010; 05/5/2010; 06/5/2010;16/5/2010;...(ví dụ từ ngày 26/4/2010 đến ngày 05/5/2010 do giá trị cột B đều bằng 4.410.251.000 nên chỉ lấy 2 thời điểm đầu và cuối thôi).

Bài toán có thể làm mọi cách, nhưng nếu làm bằng VBA giúp em thì càng tốt (em đang tìm hiểu kiến thức về VBA)
Do sơ suất nên yêu cầu của bài toán do em đưa ra sai, em xin lỗi mọi người, em xin phép được gửi lại yêu cầu xuống sau bài trả lời của thày Concogia, xin kính mong mọi người giúp đỡ.

concogia · 6/12/11

Dauthivan đã viết:
Mục đích của em là lọc những ngày tháng tại cột A theo điều kiện cột B (sheet1) thỏa mãn cả 2 điều kiện:
1. Chỉ lọc ngày tháng nào mà có giá trị cột B là số dương (>0) tức lãi.
2. Những khoảng thời gian nào mà có cùng số lãi như nhau thì chỉ lọc ra ngày đầu và ngày cuối (bỏ qua không lọc khoảng thời gian của các ngày giữa).

Kết quả lọc sẽ được thể hiện ở cột A của sheet2

em ví dụ về kết quả file đính kèm của em thì kết quả lọc ra sẽ là các ngày 26/4/2010; 05/5/2010; 06/5/2010;16/5/2010;...(ví dụ từ ngày 26/4/2010 đến ngày 05/5/2010 do giá trị cột B đều bằng 4.410.251.000 nên chỉ lấy 2 thời điểm đầu và cuối thôi).

Bài toán có thể làm mọi cách, nhưng nếu làm bằng VBA giúp em thì càng tốt (em đang tìm hiểu kiến thức về VBA)

Thử code này xem sao

Mã:

Public Sub Loc()
    Dim Vung, I, Mg(), K
    Vung = Range([A3], [A50000].End(xlUp)).Resize(, 2).Value
    ReDim Mg(1 To UBound(Vung), 1 To 1)
        For I = 2 To UBound(Vung)
            If Vung(I, 2) > 0 Then
                If Vung(I, 2) = Vung(I - 1, 2) And Vung(I, 2) <> Vung(I + 1, 2) Then
                    K = K + 1
                    Mg(K, 1) = Vung(I, 1)
                ElseIf Vung(I, 2) <> Vung(I - 1, 2) Then
                    K = K + 1
                    Mg(K, 1) = Vung(I, 1)
                End If
            End If
        Next I
[C4:C10000].ClearContents
[C4].Resize(UBound(Mg), 1) = Mg
End Sub

Ái chà chà, có một anh muốn lao vào con đường đau......đầu nữa rồi. Híc
Thân

Dauthivan · 6/12/11

Nhờ thày và mọi người làm cụ thể giúp em theo mẫu báo cáo của cơ quan

Em định thông qua kết quả bài toán thày giúp để làm kết quả trung gian, căn cứ vào đó em có thể làm báo cáo. Tuy nhiên, do em đưa yêu cầu bài toán vừa rồi chưa chuẩn, chưa sử dụng làm báo cáo được, em rất xin lỗi thày và mọi người.
Em xin mạn phép phép được nhờ lại thày và mọi người giúp đỡ em lần nữa để em làm báo cáo theo đúng mẫu của cơ quan (sau đây em xin gửi hình ảnh kết quả mẫu báo cáo em cần, dữ liệu đầu vào vẫn như file đính kèm ở trên)

TrungChinhs · 6/12/11

Dauthivan đã viết:
Mục đích của em là lọc những ngày tháng tại cột A theo điều kiện cột B (sheet1) thỏa mãn cả 2 điều kiện:
1. Chỉ lọc ngày tháng nào mà có giá trị cột B là số dương (>0) tức lãi.
2. Những khoảng thời gian nào mà có cùng số lãi như nhau thì chỉ lọc ra ngày đầu và ngày cuối (bỏ qua không lọc khoảng thời gian của các ngày giữa).

Kết quả lọc sẽ được thể hiện ở cột A của sheet2

em ví dụ về kết quả file đính kèm của em thì kết quả lọc ra sẽ là các ngày 26/4/2010; 05/5/2010; 06/5/2010;16/5/2010;...(ví dụ từ ngày 26/4/2010 đến ngày 05/5/2010 do giá trị cột B đều bằng 4.410.251.000 nên chỉ lấy 2 thời điểm đầu và cuối thôi).

Bài toán có thể làm mọi cách, nhưng nếu làm bằng VBA giúp em thì càng tốt (em đang tìm hiểu kiến thức về VBA)

Cách 1: dùng công thức đánh dấu thời điểm lọc vào cột phụ rồi dùng Autofilter để lọc:
Tại C4 nhập công thức =IF(AND(B4>0;OR(B4<>B3;B4<>B5));"x";"") rồi Fill xuống sau đó dùng Autofilter để lọc "x"

Cách 2: dùng VBA đánh dấu thời điểm lọc vào cột phụ rồi ẩn dòng trống:

Mã:

Sub Loc()
    On Error Resume Next
    Set Rng = Range([b4], [b65000].End(3))
    For Each cls In Rng
        If cls > 0 And (cls <> cls(0) Or cls <> cls(2)) Then cls(1, 2) = "x"
    Next
    Rng.Offset(, 1).SpecialCells(4).EntireRow.Hidden = 1
    Rng.Offset(, 1).Clear
End Sub

hic... Post xong thì mới thấy đề bài đã thay đổi.

concogia · 6/12/11

Dauthivan đã viết:
Em định thông qua kết quả bài toán thày giúp để làm kết quả trung gian, căn cứ vào đó em có thể làm báo cáo. Tuy nhiên, do em đưa yêu cầu bài toán vừa rồi chưa chuẩn, chưa sử dụng làm báo cáo được, em rất xin lỗi thày và mọi người.
Em xin mạn phép phép được nhờ lại thày và mọi người giúp đỡ em lần nữa để em làm báo cáo theo đúng mẫu của cơ quan (sau đây em xin gửi hình ảnh kết quả mẫu báo cáo em cần, dữ liệu đầu vào vẫn như file đính kèm ở trên)

Bạn kiểm tra giúp, nhìn mấy con số này mình....chóng mặt quá
Thân
Muốn đem kết quả đi chỗ nào để làm báo cáo thì vào code mà sửa nhé

ndu96081631 · 6/12/11

Dauthivan đã viết:
Mục đích của em là lọc những ngày tháng tại cột A theo điều kiện cột B (sheet1) thỏa mãn cả 2 điều kiện:
1. Chỉ lọc ngày tháng nào mà có giá trị cột B là số dương (>0) tức lãi.
2. Những khoảng thời gian nào mà có cùng số lãi như nhau thì chỉ lọc ra ngày đầu và ngày cuối (bỏ qua không lọc khoảng thời gian của các ngày giữa).

Kết quả lọc sẽ được thể hiện ở cột A của sheet2

em ví dụ về kết quả file đính kèm của em thì kết quả lọc ra sẽ là các ngày 26/4/2010; 05/5/2010; 06/5/2010;16/5/2010;...(ví dụ từ ngày 26/4/2010 đến ngày 05/5/2010 do giá trị cột B đều bằng 4.410.251.000 nên chỉ lấy 2 thời điểm đầu và cuối thôi).

Bài toán có thể làm mọi cách, nhưng nếu làm bằng VBA giúp em thì càng tốt (em đang tìm hiểu kiến thức về VBA)
Do sơ suất nên yêu cầu của bài toán do em đưa ra sai, em xin lỗi mọi người, em xin phép được gửi lại yêu cầu xuống sau bài trả lời của thày Concogia, xin kính mong mọi người giúp đỡ.

Thử dùng Advanced Filter theo điều kiện này xem:

PHP:

=AND(NOT(AND($B4=$B3,$B4=$B5)),$B4>0)

Dauthivan · 6/12/11

ndu96081631 đã viết:
Thử dùng Advanced Filter theo điều kiện này xem:

PHP:

=AND(NOT(AND($B4=$B3,$B4=$B5)),$B4>0)

View attachment 74909

Ồ, vậy là cái này em chưa biết rồi, em cảm ơn thày nhiều. Từ trước đến nay do chưa biết nhiều đến chức năng này, em vẫn nghĩ cái Advanced Filter nó chỉ có mỗi việc lọc duy nhất là đáng kể.

Dauthivan · 6/12/11

concogia đã viết:
Bạn kiểm tra giúp, nhìn mấy con số này mình....chóng mặt quá
Thân
Muốn đem kết quả đi chỗ nào để làm báo cáo thì vào code mà sửa nhé

Đa tạ thày nhiều, kết quả em test quá chuẩn rồi thày ah. Cuối năm nay em có thời gian tha hồ sắm tết vì coi như không phải lo cái vụ Báo cáo quyết toán năm nữa.

Dauthivan · 6/12/11

concogia đã viết:
Bạn kiểm tra giúp, nhìn mấy con số này mình....chóng mặt quá
Thân
Muốn đem kết quả đi chỗ nào để làm báo cáo thì vào code mà sửa nhé

Em xin hỏi thêm một chút, liệu bài toán này có thể làm theo công thức Excel thông thường được không? em nghĩ mãi mà chưa ra. Nếu được, xin thày và mọi người bớt chút thời gian giúp em học thêm kiến thức.

ndu96081631 · 6/12/11

Dauthivan đã viết:
Em xin hỏi thêm một chút, liệu bài toán này có thể làm theo công thức Excel thông thường được không? em nghĩ mãi mà chưa ra. Nếu được, xin thày và mọi người bớt chút thời gian giúp em học thêm kiến thức.

Đương nhiên được rồi... Bạn thấy đấy, tôi viết được công thức điều kiện cho Advanced Filter, vậy nếu dùng công thức thì điều kiện lọc cũng gần giống với công thức này thôi
-----------
Tuy nhiên, khuyên bạn đừng nên dùng công thức! Thà rằng dùng Advanced Filter, còn không thì code, công thức chỉ nên dùng trong những trường hợp đơn giản, phức tạp quá sẽ làm cho file nặng nề thêm

Dauthivan · 6/12/11

ndu96081631 đã viết:
Đương nhiên được rồi... Bạn thấy đấy, tôi viết được công thức điều kiện cho Advanced Filter, vậy nếu dùng công thức thì điều kiện lọc cũng gần giống với công thức này thôi
-----------
Tuy nhiên, khuyên bạn đừng nên dùng công thức! Thà rằng dùng Advanced Filter, còn không thì code, công thức chỉ nên dùng trong những trường hợp đơn giản, phức tạp quá sẽ làm cho file nặng nề thêm

Vâng, em biết là dùng công thức sẽ làm cho file có dung lượng lớn. Cái mà em muốn tìm hiểu là em muốn biết thuật toán của nó như thế nào thôi. Em cũng hình dung nếu bài này làm có lẽ dùng name mà công thức rất phức tạp, khi nào thày có thời gian rỗi thày chỉ cho em cách này thày nhé.

ndu96081631 · 6/12/11

Dauthivan đã viết:
Vâng, em biết là dùng công thức sẽ làm cho file có dung lượng lớn. Cái mà em muốn tìm hiểu là em muốn biết thuật toán của nó như thế nào thôi. Em cũng hình dung nếu bài này làm có lẽ dùng name mà công thức rất phức tạp, khi nào thày có thời gian rỗi thày chỉ cho em cách này thày nhé.

Cũng dễ thôi! Tôi làm theo dữ liệu của bài 1 nhé
- Đặt name

PHP:

DL =only!$A$4:$B$278

PHP:

Von =only!$B$4:$B$278

PHP:

VT =IF(NOT((Von=OFFSET(Von,-1,))*(Von=OFFSET(Von,1,)))*(Von>0),ROW(INDIRECT("1:"&ROWS(DL))),"")

- Công thức tại F4:

PHP:

=IF(ROWS($1:1)>COUNT(VT),"",INDEX(DL,SMALL(VT,ROWS($1:1)),1))

- Công thức tại G4:

PHP:

=IF(F4="","",INDEX(DL,SMALL(VT,ROWS($1:1)),2))

Kéo fill 2 công thức này xuống
-----------------
Để ý đoạn NOT((Von=OFFSET(Von,-1,))*(Von=OFFSET(Von,1,)))*(Von>0) và hãy so sánh nó với công thức điều kiện của Advanced Filter, chỉ khác nhau 2 điểm:
- Công thức ở Advanced Filter là dùng cho 1 cell còn công thức trong name là dùng cho 1 vùng
- Công thức ở Advanced Filter ta dùng hàm AND còn công thức trong name dùng toán tử NHÂN (đương nhiên với 1 mảng nhiều cell, thì AND phải được thay bằng phép NHÂN)

Dauthivan · 6/12/11

ndu96081631 đã viết:
Cũng dễ thôi! Tôi làm theo dữ liệu của bài 1 nhé
- Đặt name

PHP:

DL =only!$A$4:$B$278

PHP:

Von =only!$B$4:$B$278

PHP:

VT =IF(NOT((Von=OFFSET(Von,-1,))*(Von=OFFSET(Von,1,)))*(Von>0),ROW(INDIRECT("1:"&ROWS(DL))),"")

- Công thức tại F4:

PHP:

=IF(ROWS($1:1)>COUNT(VT),"",INDEX(DL,SMALL(VT,ROWS($1:1)),1))

- Công thức tại G4:

PHP:

=IF(F4="","",INDEX(DL,SMALL(VT,ROWS($1:1)),2))

Kéo fill 2 công thức này xuống
-----------------
Để ý đoạn NOT((Von=OFFSET(Von,-1,))*(Von=OFFSET(Von,1,)))*(Von>0) và hãy so sánh nó với công thức điều kiện của Advanced Filter, chỉ khác nhau 2 điểm:
- Công thức ở Advanced Filter là dùng cho 1 cell còn công thức trong name là dùng cho 1 vùng
- Công thức ở Advanced Filter ta dùng hàm AND còn công thức trong name dùng toán tử NHÂN (đương nhiên với 1 mảng nhiều cell, thì AND phải được thay bằng phép NHÂN)

Em đã làm như thày hướng dẫn rồi, xin thày gợi ý cho em làm kết quả như yêu cầu của bài 2 với ah.

ndu96081631 · 6/12/11

Dauthivan đã viết:
Em đã làm như thày hướng dẫn rồi, xin thày gợi ý cho em làm kết quả như yêu cầu của bài 2 với ah.

Như yêu cầu bài đó là.. hơi căng à nha
(để suy nghĩ xem)

Dauthivan · 6/12/11

Dauthivan đã viết:
Em định thông qua kết quả bài toán thày giúp để làm kết quả trung gian, căn cứ vào đó em có thể làm báo cáo. Tuy nhiên, do em đưa yêu cầu bài toán vừa rồi chưa chuẩn, chưa sử dụng làm báo cáo được, em rất xin lỗi thày và mọi người.
Em xin mạn phép phép được nhờ lại thày và mọi người giúp đỡ em lần nữa để em làm báo cáo theo đúng mẫu của cơ quan (sau đây em xin gửi hình ảnh kết quả mẫu báo cáo em cần, dữ liệu đầu vào vẫn như file đính kèm ở trên)

Đây mới là bài toán em cần, đầu bài lần trước em nhầm thày ah.

TrungChinhs · 6/12/11

Bạn thử File này xem, tôi chưa kiểm tra kỹ vì nhiều số nên ngại lần sờ.

Chú ý: nhấn phím End để tạo Báo cáo nha (vụ Onkey này vừa học được của Ndu).

Mã:

Sub Auto_Close()
  Application.OnKey "{END}"
End Sub

Mã:

Sub Auto_Open()
  Application.OnKey "{END}", "BaoCao"
End Sub

Mã:

Sub BaoCao()
    Application.ScreenUpdating = False
    On Error Resume Next
    Set Rng = Range([b4], [b65000].End(3))
    Rng.Offset(, 2).Resize(, 4).Clear
    For Each cls In Rng
        If cls > 0 And cls <> cls(0) Then
            cls(1, 0).Copy [d65000].End(3)(2)
            cls.Copy [g65000].End(3)(2)
        End If
        If cls > 0 And cls <> cls(2) Then cls(1, 0).Copy [e65000].End(3)(2)
    Next
    Set Ngay = Rng.Offset(, 2).SpecialCells(2)
    Ngay.Offset(, 2) = "=RC[-1]-RC[-2]+1"
    Ngay.Offset(, 2) = Ngay.Offset(, 2).Value
    Ngay.Offset(, 3).Copy
    [f4].PasteSpecial 4
    Application.CutCopyMode = False
    Range("D4").Select
End Sub

Dauthivan · 7/12/11

TrungChinhs đã viết:

Bạn thử File này xem, tôi chưa kiểm tra kỹ vì nhiều số nên ngại lần sờ.

Chú ý: nhấn phím End để tạo Báo cáo nha (vụ Onkey này vừa học được của Ndu).

Mã:

Sub Auto_Close()
  Application.OnKey "{END}"
End Sub

Mã:

Sub Auto_Open()
  Application.OnKey "{END}", "BaoCao"
End Sub

Mã:

Sub BaoCao()
    Application.ScreenUpdating = False
    On Error Resume Next
    Set Rng = Range([b4], [b65000].End(3))
    Rng.Offset(, 2).Resize(, 4).Clear
    For Each cls In Rng
        If cls > 0 And cls <> cls(0) Then
            cls(1, 0).Copy [d65000].End(3)(2)
            cls.Copy [g65000].End(3)(2)
        End If
        If cls > 0 And cls <> cls(2) Then cls(1, 0).Copy [e65000].End(3)(2)
    Next
    Set Ngay = Rng.Offset(, 2).SpecialCells(2)
    Ngay.Offset(, 2) = "=RC[-1]-RC[-2]+1"
    Ngay.Offset(, 2) = Ngay.Offset(, 2).Value
    Ngay.Offset(, 3).Copy
    [f4].PasteSpecial 4
    Application.CutCopyMode = False
    Range("D4").Select
End Sub

Cảm ơn bác và mọi người đã giúp. Được bác TrungChinhs và thày Ndu, thày concogia viết giúp Code nhờ đó mà công việc hằng ngày của em rất nhẹ nhàng; trước kia em rất khổ vì nó , bởi đây là một trong các báo cáo em hay phải làm nhất.

Trước kia, khi trưa biết một chút gì về VBA em cố gắng giải quyết nó bằng công thức Excel thông thường, nhưng không thể nào tìm ra hướng giải quyết đành làm bằng cách nhặt thủ công.

Đến nay, em vẫn tò mò muốn khám phá xem cách giải quyết bài này hoàn toàn bằng Excel thì thuật toán phải viết ra sao, điều mà em trăn trở bấy lâu nay không làm nổi.

bebo021999 · 7/12/11

Dauthivan đã viết:
Đến nay, em vẫn tò mò muốn khám phá xem cách giải quyết bài này hoàn toàn bằng Excel thì thuật toán phải viết ra sao, điều mà em trăn trở bấy lâu nay không làm nổi.

Thấy bạn vẫn còn băn khoăn về làm công thức, thôi thì đóng góp 1 cách vậy.

1- Xác định vị trí ngày đầu:
Name: DAU
Nếu số tiền <>số tiền liền kề phía trên và >0, thì đánh dấu vị trí trong cột

Mã:

=IF((only!$B$4:$B$278<>only!$B$3:$B$277)*(only!$B$4:$B$278>0)=0,"",ROW(INDIRECT("1:"&ROWS(only!$B$4:$B$278))))

2- Xác định vị trí ngày cuối:
Name: CUOI
Nếu số tiền <>số tiền liền kề phía dưới và >0, thì đánh dấu vị trí trong cột

Mã:

=IF((only!$B$4:$B$278<>only!$B$5:$B$279)*(only!$B$4:$B$278>0)=0,"",ROW(INDIRECT("1:"&ROWS(only!$B$4:$B$278))))

3-Dùng INDEX để trích lọc bình thường

Chạy thử xem có nặng lắm không.

Mượn file của concogia để so sánh nhé!

Dauthivan · 7/12/11

bebo021999 đã viết:
Thấy bạn vẫn còn băn khoăn về làm công thức, thôi thì đóng góp 1 cách vậy. 1- Xác định vị trí ngày đầu: Name: DAU Nếu số tiền số tiền liền kề phía trên và >0, thì đánh dấu vị trí trong cột

Mã:

=IF((only!$B$4:$B$278only!$B$3:$B$277)*(only!$B$4:$B$278>0)=0,"",ROW(INDIRECT("1:"&ROWS(only!$B$4:$B$278))))

2- Xác định vị trí ngày cuối: Name: CUOI Nếu số tiền số tiền liền kề phía dưới và >0, thì đánh dấu vị trí trong cột

Mã:

=IF((only!$B$4:$B$278only!$B$5:$B$279)*(only!$B$4:$B$278>0)=0,"",ROW(INDIRECT("1:"&ROWS(only!$B$4:$B$278))))

3-Dùng INDEX để trích lọc bình thường Chạy thử xem có nặng lắm không. Mượn file của concogia để so sánh nhé!

Hic...hic, khó như thế em chào thua là phải. Nhìn lại, em thấy kiến thức Excel của mình còn quá nhiều hạn chế để có thể giải quyết các bài toán phức tạp.

Cả 2 cách này đều chắc chắn sẽ giúp ích cho em cả trong công việc lẫn trong việc tìm tòi, nghiên cứu về sau. Tùy từng trường hợp cụ thể, nếu số lượng dữ liệu đầu vào quá nhiều em sẽ sử dụng Code; nếu một số trường hợp số lượng dữ liệu đầu vào ít, em sẽ cố gắng bố trí dữ liệu đầu vào khoa học, chỉ có những bước cần thiết em mới sử dụng Name thôi vì cách này nó bao gồm tính linh động cao (đầu ra tự thay đổi theo đầu vào).

Em xin hỏi thêm một chút nữa vì em chưa biết cách: Giả sử trong công thức mảng phức tạp như thế này, em chưa hiểu rõ từng thành phần cấu tạo nên nó có ý nghĩa gì, ví dụ như đoạn sau:
=IF((only!$B$4:$B$278only!$B$3:$B$277)*(only!$B$4:$B$278>0)=0,"",ROW(INDIRECT("1:"&ROWS(only!$B$4:$B$278))))
Phần bôi đậm em muốn biết thực chất của nó là gì thì em phải làm như thế nào (vì em hiểu cái khó của mảng là nó có nhiều phần tử chứ không phải 1 giá trị như thông thường).

Một lần nữa em thực sự không biết nói gì hơn để bày tỏ lòng biết ơn của mình đối với các thày, tất cả mọi người đã quan tâm giúp đỡ em, em xin cảm ơn rất nhiều.

ndu96081631 · 7/12/11

Dauthivan đã viết:
Đây mới là bài toán em cần, đầu bài lần trước em nhầm thày ah.

Gữi bạn file làm bằng công thức theo yêu cầu thứ 2

Nhờ lọc cột ngày tháng (cột A) tùy theo điều kiện của cột lãi, lỗ (cột B) (1 người xem)

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

Thành viên tiêu biểu

File đính kèm

Gội rồi mới Cạo

File đính kèm

Thành viên tiêu biểu

Thành viên tích cực

File đính kèm

Gội rồi mới Cạo

File đính kèm

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tiêu biểu

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tích cực

File đính kèm

Thành viên tiêu biểu

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

File đính kèm

Thành viên tiêu biểu

Huyền thoại GPE

File đính kèm

Đếm ngược thời gian