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]

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 đỡ.

 

[Dauthivan]

Em chưa biết nhiều về Mảng, em xin chụp hình ảnh bài toán nên cho dễ nhìn, xin thày giải thích giúp em thành phần bôi đậm ở name VT có nghĩa là gì ah?

 

[ndu96081631]

Em mới biết nhiều về Mảng, em xin chụp hình ảnh bài toán nên cho dễ nhìn, xin thày giải thích giúp em thành phần bôi đậm ở name VT có nghĩa là gì ah?

Ý bạn hỏi hàm OFFSET chăng? Nó dùng để thay đổi kích thước và vị trí của 1 vùng
Cú pháp: =OFFSET(Vùng, Vị trí dòng, vị trí cột, Số dòng, Số cột)
Với Von là name có vùng tham chiếu =B4:B278
Vậy thì: OFFSET(Von, -1, ) có nghĩa là "dịch" vùng Von lên phía trên 1 dòng, nó sẽ thành B3:B277
-------------
Đoạn =IF((Von<>OFFSET(Von,-1,))*(Von>0).... nghĩa là muốn nói: Nếu 1 cell có giá trị <> giá trị cell trên nó, đồng thơi cell ấy > 0 thì...
Còn đoạn ROW(INDIRECT("1:"&ROWS(Von))) chẳng qua chỉ là "đánh dấu" ví trí (có đánh dấu thì mới biết đường mà trích lọc chứ)

 

[Dauthivan]

Ý bạn hỏi hàm OFFSET chăng? Nó dùng để thay đổi kích thước và vị trí của 1 vùng
Cú pháp: =OFFSET(Vùng, Vị trí dòng, vị trí cột, Số dòng, Số cột)
Với Von là name có vùng tham chiếu =B4:B278
Vậy thì: OFFSET(Von, -1, ) có nghĩa là "dịch" vùng Von lên phía trên 1 dòng, nó sẽ thành B3:B277

Ý em muốn hỏi cả cụm em bôi đậm ây được kết quả là cái gì ah?

 

[bebo021999]

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$278<>only!$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).

(1) $B$4:$B$278<>$B$3:$B$277: So sánh từng phần tử tương ứng giữa 2 mảng, tạo 1 mảng mới: {B4<>B3;B5<>B4;B6<>B5;...;B278<>B277} => ví dụ = {0;1;1;0;...;0} trong đó 1 là thỏa (TRUE)
(2) $B$4:$B$278>0: So sánh từng phần tử với 0, nếu >0 thì trả về 1
(1)*(2) : nếu 2 đk thỏa, trả về 1, 1 trong 2 không thỏa, trả về 0
(3) ROW(...): Tạo chuỗi số liên tiếp từ 1 đến 275
IF((1)*(2)=0,"",(3)) : Tạo chuỗi số liên tiếp từ 1 đến 275 , trong đó xét từng vị trí, nếu không thỏa trả về rỗng, thỏa thì trả về số thứ tự tương ứng.
Bạn có thể bôi đen từng phần, nhấn F9 xem kết quả.
Chúc thành công.

 

[Dauthivan]

Ý bạn hỏi hàm OFFSET chăng? Nó dùng để thay đổi kích thước và vị trí của 1 vùng
Cú pháp: =OFFSET(Vùng, Vị trí dòng, vị trí cột, Số dòng, Số cột)
Với Von là name có vùng tham chiếu =B4:B278
Vậy thì: OFFSET(Von, -1, ) có nghĩa là "dịch" vùng Von lên phía trên 1 dòng, nó sẽ thành B3:B277
-------------
Đoạn =IF((Von<>OFFSET(Von,-1,))*(Von>0).... nghĩa là muốn nói: Nếu 1 cell có giá trị <> giá trị cell trên nó, đồng thơi cell ấy > 0 thì...
Còn đoạn ROW(INDIRECT("1:"&ROWS(Von))) chẳng qua chỉ là "đánh dấu" ví trí (có đánh dấu thì mới biết đường mà trích lọc chứ)

Khi tách riêng cái này INDIRECT("1:"&ROWS(Von)) =? thì em chưa hiểu được.

 

[Phanhanhdai]

Khi tách riêng cái này INDIRECT("1:"&ROWS(Von)) =? thì em chưa hiểu được.

Đối với các bài toán lọc, trích dữ liệu kiểu gì trước sau cũng đến phần này, rắc rối nhất là hàm Indirect; ngày trước mình cũng khó khăn khi tiếp cận nó nhưng nay thấy nó hữu ích vô cùng, phần giải thích của chị Handung giờ mình vẫn lưu trong máy, xin trích dẫn lại cho bạn tham khảo:

Hàm {=ROW(INDIRECT("1:n")} sẽ cho ra 1 dãy số gồm các số nguyên liên tục từ 1 đến n. Và đây cũng chính là công thức mảng giúp bạn nhập số thứ tự vào 1 dãy chọn trước. Ta thử xem hàm này họat động ra sao nhé
Hàm ROW trong Excel trả kết quả về một số hàng. Nếu ta nhập công thức mảng : {=ROW(1:12)} vào một dãy A1:A12 nằm dọc có 12 ô, ta sẽ nhận được một mảng gồm các số nguyên liên tục từ 1 đến 12. Nhưng nếu ta chèn thêm một hàng vào dãy trên nằm ở trên A1, ta sẽ thấy công thức trên trở thành {=ROW(2:13)} và mảng của ta sẽ có 12 số nguyên liên tục từ 2 đến 13.
Do đó, ta phải sử dụng kết hợp với hàm INDIRECT. Hàm INDIRECT lấy đối số là một chuỗi Text. INDIRECT("1:12") sẽ luôn luôn cho kết quả là (1:12), vì Excel không thể điều chỉnh được các tham số chuỗi trong hàm INDIRECT được. Sự kết hợp này luôn đảm bảo cho hàm {=ROW(INDIRECT("1:12")} cho kết quả là một mảng gốm các số từ 1 đến 12 trong mọi trường hợp.

Các loại bài toán này bao giờ cũng có công thức mà phần đuôi của nó dạng: ROW(INDIRECT("1:n"); trong đó n sẽ được thể hiện gián tiếp qua các bài khác nhau

 

[bebo021999]

Khi tách riêng cái này INDIRECT("1:"&ROWS(Von)) =? thì em chưa hiểu được.

Cụ thể hóa giải thích của bạn phananhdai trong file của bạn:
Vùng dữ liệu vốn của bạn (VON) nằm từ B4:B278, bao gồm ROWS(VON) = 278-4+1=275 dòng. Muốn tạo 1 mảng số thứ tự gồm từ 1 đến 275 ({1;2;3...;275}) bạn có thể dùng: =ROW(1:275). Đặt trường hợp VON thay đổi tăng hay giảm số dòng, thì công thức ROW(1:275) không thay đổi theo, kết quả sẽ sai. Vì vậy, người ta thường thay 275 bằng 1 con số "động", co giãn theo kính thước của VON. Như vậy, sẽ là ROW(1:ROWS(VON)). Tuy nhiên đến đây sẽ báo lỗi, vì 1:ROWS(VON) không phải là địa chỉ tham chiếu phải qua hàm INDIRECT để chuyển đổi: INDIRECT("1:"&ROWS(VON)) để excel hiểu là dòng 1:dòng 275.

 

[Dauthivan]

Đối với các bài toán lọc, trích dữ liệu kiểu gì trước sau cũng đến phần này, rắc rối nhất là hàm Indirect; ngày trước mình cũng khó khăn khi tiếp cận nó nhưng nay thấy nó hữu ích vô cùng, phần giải thích của chị Handung giờ mình vẫn lưu trong máy, xin trích dẫn lại cho bạn tham khảo:

Hàm {=ROW(INDIRECT("1:n")} sẽ cho ra 1 dãy số gồm các số nguyên liên tục từ 1 đến n. Và đây cũng chính là công thức mảng giúp bạn nhập số thứ tự vào 1 dãy chọn trước. Ta thử xem hàm này họat động ra sao nhé
Hàm ROW trong Excel trả kết quả về một số hàng. Nếu ta nhập công thức mảng : {=ROW(1:12)} vào một dãy A1:A12 nằm dọc có 12 ô, ta sẽ nhận được một mảng gồm các số nguyên liên tục từ 1 đến 12. Nhưng nếu ta chèn thêm một hàng vào dãy trên nằm ở trên A1, ta sẽ thấy công thức trên trở thành {=ROW(2:13)} và mảng của ta sẽ có 12 số nguyên liên tục từ 2 đến 13.
Do đó, ta phải sử dụng kết hợp với hàm INDIRECT. Hàm INDIRECT lấy đối số là một chuỗi Text. INDIRECT("1:12") sẽ luôn luôn cho kết quả là (1:12), vì Excel không thể điều chỉnh được các tham số chuỗi trong hàm INDIRECT được. Sự kết hợp này luôn đảm bảo cho hàm {=ROW(INDIRECT("1:12")} cho kết quả là một mảng gốm các số từ 1 đến 12 trong mọi trường hợp.

Các loại bài toán này bao giờ cũng có công thức mà phần đuôi của nó dạng: ROW(INDIRECT("1:n"); trong đó n sẽ được thể hiện gián tiếp qua các bài khác nhau

Hơn nữa, phần chứ ký của mình có giải thích thêm bạn xem nhé.

Tức là xét về bản chất công thức bôi đỏ, đậm trong phần của bác trường hợp này Indirect phải đi cùng với Row thì mới có ý nghĩa, cụ thể phải viết đầy đủ là Row(Indirect("1:n")), chứ nếu bỏ riêng cái thằng Indirect("1:n") thì nó chẳng có ý nghĩa gì đúng không ah?

 

[ndu96081631]

Tức là xét về bản chất công thức bôi đỏ, đậm trong phần của bác trường hợp này Indirect phải đi cùng với Row thì mới có ý nghĩa, cụ thể phải viết đầy đủ là Row(Indirect("1:n")), chứ nếu bỏ riêng cái thằng Indirect("1:n") thì nó chẳng có ý nghĩa gì đúng không ah?

Không hẳn vậy
Ví dụ: INDIRECT("1:5") giống như bạn đang nói đến nguyên 1 vùng từ dòng 1 đến dòng 5 (toàn bộ cột) ---> Nó tương đương với A1:IV5 (với Excel 2003)
Thí nghiệm vầy sẽ thấy:
Gõ vào đâu đó (nằm từ dòng 6 trở đi) công thức =COUNT(INDIRECT("1:5"))
Xong, gõ gì đó vào bất cứ cell nào từ dòng 1 đến dòng 5 (gõ số hoặc chữ) xem công thức trên ra kết quả gì
(Để ý, nếu công thức này được gõ ở bất cứ cell nào trong vùng A1:IV5 thì sẽ báo lỗi tham chiếu vòng)

 

[Dauthivan]

Không hẳn vậy
Ví dụ: INDIRECT("1:5") giống như bạn đang nói đến nguyên 1 vùng từ dòng 1 đến dòng 5 (toàn bộ cột) ---> Nó tương đương với A1:IV5 (với Excel 2003)
Thí nghiệm vầy sẽ thấy:
Gõ vào đâu đó (nằm từ dòng 6 trở đi) công thức =COUNT(INDIRECT("1:5"))
Xong, gõ gì đó vào bất cứ cell nào từ dòng 1 đến dòng 5 (gõ số hoặc chữ) xem công thức trên ra kết quả gì
(Để ý, nếu công thức này được gõ ở bất cứ cell nào trong vùng A1:IV5 thì sẽ báo lỗi tham chiếu vòng)

Giờ thì em hiểu được rồi, thảo nào để đơn giản vấn đề hôm qua bài của thày em xoá bớt số dòng đi để Von chỉ có 12 thành phần thôi; khi em bôi đen INDIRECT("1:"&ROWS(Von)) tức là INDIRECT("1:12") sau đó nhấn F9 mà sao thấy nó quá nhiều phần tử thế mà chẳng hiểu gì?

Hoá ra cái anh Indirect đáo để thật, đặc điểm tham chiếu gián tiếp nên bổ trợ cho Row trường hợp này hay ghê, nhờ có "nó đóng băng"thành phần "1:n" nên địa chỉ tham chiếu của hàm Row không bị ảnh hưởng khi chèn dòng, xoá dòng.

 

[ndu96081631]

Giờ thì em hiểu được rồi, thảo nào để đơn giản vấn đề hôm qua bài của thày em xoá bớt số dòng đi để Von chỉ có 12 thành phần thôi; khi em bôi đen INDIRECT("1:"&ROWS(Von)) tức là INDIRECT("1:12") sau đó nhấn F9 mà sao thấy nó quá nhiều phần tử thế mà chẳng hiểu gì?

Hoá ra cái anh Indirect đáo để thật, đặc điểm tham chiếu gián tiếp nên bổ trợ cho Row trường hợp này hay ghê, nhờ có "nó đóng băng"thành phần "1:n" nên địa chỉ tham chiếu của hàm Row không bị ảnh hưởng khi chèn dòng, xoá dòng.

Tóm lại: Bạn chỉ cần nhớ hàm INDIRECT dùng để biến 1 chuổi có dạng tham chiếu trở thành 1 tham chiếu thật sư
Ví dụ: INDIRECT("A1") chính là cell A1

 

[597335]

Em chưa thành thạo về sử dụng Advanced Filter, nhờ mọi người giải thích cho em hiểu công thức của thày ndu96081631:

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

Đặt biệt là các AND thứ 2 (=AND(NOT(AND($B4=$B3,$B4=$B5)),$B4>0)) nó có tác dụng chi nhỉ?


Có phải công thức tại B4 sẽ là ô mẫu cho toàn bộ vùng B4 đến B278 đúng không ah?

 

[nghiaphuc]

Em chưa thành thạo về sử dụng Advanced Filter, nhờ mọi người giải thích cho em hiểu công thức của thày ndu96081631:

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

Đặt biệt là các AND thứ 2 (=AND(NOT(AND($B4=$B3,$B4=$B5)),$B4>0)) nó có tác dụng chi nhỉ?

Biểu thức AND($B4=$B3,$B4=$B5) tương đương với đẳng thức kép $B3=$B4=$B5, tức là 3 ô này có giá trị bằng nhau. Như vậy, NOT(AND($B4=$B3,$B4=$B5)) được hiểu là có ít nhất 1 giá trị khác với 2 giá trị còn lại (trong 3 ô $B3, $B4, $B5)

 

[nguyenmanhnam]

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

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

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)

Em đang ôn luyện về Advance Filter, xin thày cho phép em hỏi có cách nào mà ra được kết quả như tác giả mong muốn ở hình trên không?