Lọc nhiều điều kiện & bố trí dữ liệu theo một số điều kiện (1 người xem)

[dongducnhiem]

Tôi có Sheet Data với các cột như sau: STT/ Ngày/…./Mã khách hàng/Diễn giải/Tài khỏan Nợ/Tài khỏan Có/ Số tiền.
Bên Sheet KetQua ( có bỏ và thêm ) các cột sau:Ngày/…./Mã khách hàng/Diễn giải/Tài khỏan đối ứng/ Số tiền Nợ/ Số tiền có
Tôi muốn lọc và bố trí dữ liệu theo các ĐK sau:
I-LỌC
1/ Từ ngày -> đến ngày
2/ Theo Mã khách hàng
3/ Theo số tài khoản, ví dụ: Tôi chọn tài khoản 1561 thì bên cột tài khoản Nợ hay Có mà có tài khỏan 1561 thì chọn(Các điều kiện nào được chọn thì lọc, còn bỏ trống thì kg lọc)
II-BỐ TRÍ DỮ LIỆU
1/Ở cột tài khỏan đối ứng thì lấy tài khỏan đối ứng ngược lại với tài khỏan 1561
2/Trong Shest Data, Nếu tài khoản 1561 ở cột "Tài khỏan Nợ", thì cột "Số tiền Nợ" của Sheet KetQua = Cột "số tiền" của Sheet Data, và ngược lại
3/ Dòng “ngày tháng năm & Giám đốc “ khi dùng công thức các bạn có thể bố trí dưới dòng tổng cộng một dòng.

Mời các bạn đọc trong File đính kèm.Vì tôi chưa có cơ bản về VBA, nên các bạn thiết lập công thức. Tôi đang sử dụng Excel 2003. Trường hợp Sheet Data có dòng trống thì có ảnh hưởng gì kg?
Rất mong các bạn hướng dẫn & và thiết lập công thức giùm. Cảm ơn các bạn đã giúp đỡ và chia sẻ.

 

[hoangexcell]

Bạn dùng vlookup cho các dữ liệu của mình thôi. Dò từ sheet data!? Xem thêm dò nhiều điều kiện nhé!
http://www.giaiphapexcel.com/forum/showthread.php?56832-Nh%E1%BB%9D-c%C3%A1c-b%E1%BA%A1n-h%C6%B0%E1%BB%9Bng-d%E1%BA%ABn-d%C3%B2-t%C3%ACm-theo-nhi%E1%BB%81u-%C4%91i%E1%BB%81u-ki%E1%BB%87n

 

[usagi90]

Bạn dùng vlookup cho các dữ liệu của mình thôi. Dò từ sheet data!? Xem thêm dò nhiều điều kiện nhé!
http://www.giaiphapexcel.com/forum/showthread.php?56832-Nhờ-các-bạn-hướng-dẫn-dò-tìm-theo-nhiều-điều-kiện

hự hự đã giúp người ta thì giúp cho trót đi a..a nói vậy thì tới bao giờ tụi e mới sáng ra được ạ..đâu phải ai cũng là siêu nhơn đâu khờ khờ ..nói cái hỉu thì thui khỏi cần hok lun hì

 

[dongducnhiem]

Bạn dùng vlookup cho các dữ liệu của mình thôi. Dò từ sheet data!? Xem thêm dò nhiều điều kiện nhé!
http://www.giaiphapexcel.com/forum/...các-bạn-hướng-dẫn-dò-tìm-theo-nhiều-điều-kiện

Tôi cái đường Link bạn cho và
câu hỏi của tôi kg giống các điều kiện, nếu có thể bạn giải giùm tôi bài này nhé! Tks

 

[linhngoc]

Gửi bạn, được gần hết yêu cầu của bạn.
-----------------
Bài này bạn xem thêm link này (Bài 3 của hoamattroicoi):
http://www.giaiphapexcel.com/forum/showthread.php?49805-Lọc-sổ-theo-ngày-tháng-phát-sinh.

 

[thanh_tks]

Tôi có Sheet Data với các cột như sau: STT/ Ngày/…./Mã khách hàng/Diễn giải/Tài khỏan Nợ/Tài khỏan Có/ Số tiền.
Bên Sheet KetQua ( có bỏ và thêm ) các cột sau:Ngày/…./Mã khách hàng/Diễn giải/Tài khỏan đối ứng/ Số tiền Nợ/ Số tiền có
Tôi muốn lọc và bố trí dữ liệu theo các ĐK sau:
I-LỌC
1/ Từ ngày -> đến ngày
2/ Theo Mã khách hàng
3/ Theo số tài khoản, ví dụ: Tôi chọn tài khoản 1561 thì bên cột tài khoản Nợ hay Có mà có tài khỏan 1561 thì chọn(Các điều kiện nào được chọn thì lọc, còn bỏ trống thì kg lọc)
II-BỐ TRÍ DỮ LIỆU
1/Ở cột tài khỏan đối ứng thì lấy tài khỏan đối ứng ngược lại với tài khỏan 1561
2/Trong Shest Data, Nếu tài khoản 1561 ở cột "Tài khỏan Nợ", thì cột "Số tiền Nợ" của Sheet KetQua = Cột "số tiền" của Sheet Data, và ngược lại
3/ Dòng “ngày tháng năm & Giám đốc “ khi dùng công thức các bạn có thể bố trí dưới dòng tổng cộng một dòng.

Mời các bạn đọc trong File đính kèm.Vì tôi chưa có cơ bản về VBA, nên các bạn thiết lập công thức. Tôi đang sử dụng Excel 2003. Trường hợp Sheet Data có dòng trống thì có ảnh hưởng gì kg?
Rất mong các bạn hướng dẫn & và thiết lập công thức giùm. Cảm ơn các bạn đã giúp đỡ và chia sẻ.

Mình có điều muốn hỏi lại là:
ở sheet "ketqua",như mẫu bạn điền vào thì có hai TK có 131 như nhau nhưng số tiền thì bên "nợ" =5.000;"có"=10.000 là thế nào?.
Nếu đúng hai TK này đều nằm bên "có" thì bài đã có thể giải quyết.

 

[dongducnhiem]

Điều chỉnh lại Sheet KetQua

Mình có điều muốn hỏi lại là:
ở sheet "ketqua",như mẫu bạn điền vào thì có hai TK có 131 như nhau nhưng số tiền thì bên "nợ" =5.000;"có"=10.000 là thế nào?.
Nếu đúng hai TK này đều nằm bên "có" thì bài đã có thể giải quyết.

Sorry bạn và các thành viên, mình chuyển KQuả sai, mình sẽ chuyển lại Sheet KetQua. Tks

 

[thanh_tks]

Sorry bạn và các thành viên, mình chuyển KQuả sai, mình sẽ chuyển lại Sheet KetQua. Tks

Thì ra mấy số đấy lại nằm ở cột "Nợ".
Bạn xem thử file đính kèm có đúng như ý bạn không?
đợi tin bạn.

 

[dongducnhiem]

Gửi bạn, được gần hết yêu cầu của bạn.
-----------------
Bài này bạn xem thêm link này (Bài 3 của hoamattroicoi):
http://www.giaiphapexcel.com/forum/showthread.php?49805-Lọc-sổ-theo-ngày-tháng-phát-sinh.

Cảm ơn bạn. Sau khi test File của bạn thì bị như sau: Nếu bỏ chọn điều kiện từ ngày mà vẫn chọn Mã KH và Tài khoản thì Ketqua khg có dòng nào!!! và ngược lại.
Mặc khác ta chọn từ ngày 02/01/11 đến 31/01/11 và Mã KH là M001 và Tài khoản là 1561, thì cũng kg có kết qủa nào, trong khi đó thì phải có 01 Kết qủa là dòng thứ 1 của Data.
Bạn lưu ý chỉ những điều kiện nào được chọn thì mới cho kết qủa, kg chọn thì bỏ qua

 

[thanh_tks]

Bạn đã xem file mình gửi chưa ???
sao không thấy ý kiến gì???

 

[dongducnhiem]

thì ra mấy số đấy lại nằm ở cột "nợ".
Bạn xem thử file đính kèm có đúng như ý bạn không?
đợi tin bạn.

Trên cả mong đợi đó bạn! Cảm ơn bạn đã bỏ thời gian & công sức đã giúp mình.

 

[hoamattroicoi]

Cảm ơn bạn. Sau khi test File của bạn thì bị như sau: Nếu bỏ chọn điều kiện từ ngày mà vẫn chọn Mã KH và Tài khoản thì Ketqua khg có dòng nào!!! và ngược lại.
Mặc khác ta chọn từ ngày 02/01/11 đến 31/01/11 và Mã KH là M001 và Tài khoản là 1561, thì cũng kg có kết qủa nào, trong khi đó thì phải có 01 Kết qủa là dòng thứ 1 của Data.
Bạn lưu ý chỉ những điều kiện nào được chọn thì mới cho kết qủa, kg chọn thì bỏ qua

Gửi bạn dongducnhiem @ :
Trong đường link của linhngoc gửi cho bạn là mình thiết kế mẫu sổ đó căn cứ vào điểu kiện là ngày tháng và tài khoản chọn để lọc.
Bạn nói test file của mình mà xóa ngày tháng coi như bạn bỏ qua điều kiện ngày tháng vậy thì sao gọi là lọc theo điều kiện ngày tháng nữa. Bài đó mình làm không kèm theo điều kiện là bỏ 1 trong 3 điều kiện vẫn ra kết quả, 3 điều kiện đó là : ngày đầu, ngày cuối và mã tài khoản vì nó đặt trong hàm And() nên nó phải thỏa mãn cả 3 điều kiện này.

 

[dongducnhiem]

Bạn xem thử file đính kèm có đúng như ý bạn không?
đợi tin bạn.

Bạn cho mình hỏi trong Name Loc, trong công thức ở cuối cùng, nếu đọc thì thấy chữ "k", còn dùng Paste List để copy các Name ra ngòai thì nó thể hiện hình vuông, bạn giải thích giùm mình được kg? Tks

 

[thanh_tks]

Bạn cho mình hỏi trong Name Loc, trong công thức ở cuối cùng, nếu đọc thì thấy chữ "k", còn dùng Paste List để copy các Name ra ngòai thì nó thể hiện hình vuông, bạn giải thích giùm mình được kg? Tks

Do name "loc" hơi dài nên bạn nhìn không hết name đó. chữ "K" đó có thể là tên sheet "ketqua" đó.
và cũng có thể bạn chưa copy hết name đó ra ngoài nên bị lỗi đó thôi.
CT trong name "loc" dài vì bạn yêu cầu nhiều điều kiện(4) cộng thêm bỏ lọc khi không có điều kiện đó
do vậy CT hơi phức tạp.
Các điều kiện trong CT được liên kết bằng toán tử "*" và "+" tương ứng với hàm ADD(,) và OR(,)
Ví Dụ =IF((DK1)*(DK2)*((DK3)+(DK4)),...,""). nhưng do các điều kiện này bỏ nếu là ô trống, vì vậy phải lồng thêm hàm IF() nữa vào DK:
=IF((IF("nếuA="","",DK1))*(.........,...,"").
Nói đến đây trắc bạn hiểu được rồi chứ???!!!
Chúc bạn vui vẻ và hòa nhã với các thành viên!

 

[linhngoc]

Đúng là file trước mình làm sai, mình gửi lại file sửa lại dưới đây.
(Gửi chỉ là để sửa lại chỗ sai, vì cách này dài dòng rắc rối khi có nhiều điều kiện chứ không hay được như cách làm của bạn thanh_tks)
---------
Tranh luận, góp ý để cùng nhau học tập tiến bộ. Hy vọng bạn bớt giận. Chúc bạn cuối tuần vui vẻ!

 

[dongducnhiem]

Đúng là file trước mình làm sai, mình gửi lại file sửa lại dưới đây.
(Gửi chỉ là để sửa lại chỗ sai, vì cách này dài dòng rắc rối khi có nhiều điều kiện chứ không hay được như cách làm của bạn thanh_tks)
---------
Tranh luận, góp ý để cùng nhau học tập tiến bộ. Hy vọng bạn bớt giận. Chúc bạn cuối tuần vui vẻ!

Rất cảm ơn bạn. Chúc bạn nhiều niềm vui & hạnh phúc

 

[dongducnhiem]

Gởi bạn thanh_tks & linhngoc:
Sau khi xem bài giải của bạn thanh_tks, tôi thấy mình còn 01 trường hợp chưa nghĩ đến đó là khi bỏ chọn Tài khỏan thì Sheet KetQua phải có các cột giống như Data, tôi chỉ giải bài này theo điều kiện lọc từ ngày -> đến ngày và Mã KH, còn muốn thêm Tài khỏan thì tôi phải thêm 01 cột phụ, nhưng khi Tài khoản kg chọn thì chắc kg ra được kết qủa như bạn thanh_tks.

 

[MonaLisa7775]

Đối với dạng lọc từ ngày đến ngày, có thể không lọc bằng cách đơn giản là chọn ngày bắt đầu là ngày nhỏ nhất hoặc 01/01, và ngày cuối là ngày lớn nhất, hoặc lấy 31/12.

Đối với các điều kiện khác, nếu không muốn lọc (lấy hết), thì chỉ cần thêm 1 cái If, không cần thêm cột phụ thứ 2.

Tuy nhiên, với cách này nghĩa là phải xoá trắng các ô điều kiện, coi như trắng là không lọc.

Có 1 cách khác, là dùng mỗi điều kiện lọc 1 cái checkbox: Chọn = có lọc và không chọn = không lọc.

Công thức cũng bảo đảm ngắn gọn hơn.

Bạn cứ thử thêm 1 cái If, không được thì tính tiếp.

 

[thanh_tks]

Gởi bạn thanh_tks & linhngoc:
Sau khi xem bài giải của bạn thanh_tks, tôi thấy mình còn 01 trường hợp chưa nghĩ đến đó là khi bỏ chọn Tài khỏan thì Sheet KetQua phải có các cột giống như Data, tôi chỉ giải bài này theo điều kiện lọc từ ngày -> đến ngày và Mã KH, còn muốn thêm Tài khỏan thì tôi phải thêm 01 cột phụ, nhưng khi Tài khoản kg chọn thì chắc kg ra được kết qủa như bạn thanh_tks.

Bạn làm name "DK" rất hay.
sau này nếu gặp trường hợp nào như thế này,mình sẽ theo cách của bạn.

 

[vodoi2x]

Gởi bạn thanh_tks & linhngoc:
Sau khi xem bài giải của bạn thanh_tks, tôi thấy mình còn 01 trường hợp chưa nghĩ đến đó là khi bỏ chọn Tài khỏan thì Sheet KetQua phải có các cột giống như Data, tôi chỉ giải bài này theo điều kiện lọc từ ngày -> đến ngày và Mã KH, còn muốn thêm Tài khỏan thì tôi phải thêm 01 cột phụ, nhưng khi Tài khoản kg chọn thì chắc kg ra được kết qủa như bạn thanh_tks.

Tổng hợp từ file của bạn, cũng như thừa kế của LinhNgoc, Thanh_tks, và thêm một chút công thức được kết quả như file gửi kèm,

Trong file gửi kèm, đã chỉnh sửa:

+ Thêm điều kiện cho Mã tài khoản, khi ô đ.k này là trống thì sẽ cột thay đổi theo như bảng data ban đầu (có tk nợ và có) và NGƯỢC LẠI thì ghi số tiền theo nợ có -- (hiện chỉ chưa định dạng số theo Formatting conditional cho column G-H -- việc nay đơn giản chắc bạn làm tốt được )

+ Tiêu đề các cột G-H sẽ thay đổi phù hợp với có hay không có MaTaiKhoan
hy vọng là kết quả như bạn mong muốn,

 

[dongducnhiem]

Có cách nào tạm ngưng họat động của 1 Sheet, khi cần thì cho họat động lại

Gởi bạn vodoi2x và các thành viên
Mình thường thấy các các file có Name động thì chạy rất chậm, ví dụ ở file của bạn, mình đưa vào file thực tế (có nhiều sheet khác nữa), khi làm việc với các sheet khác thì chạy rất chậm, chẳng lẽ lúc nào ta cũng bấm F9 thì oải quá! mình muốn tạm ngưng họat động của sheet Ketqua (hoặc các sheet mang tính chất lọc khác), khi cần thì ta mới cho chạy (vì thực tế khi cuối tháng hay ở thời điểm nào đó, ta mới cho chạy để in ấn ...)
Nếu viết bằng VBA, bạn có thể hướng dẫn cụ thể cho mình 1 tý (để có thể áp dụng cho các File thực tế), vì cái này mình chỉ có biết bấm Alt + F11.
Cảm ơn các thành viên giúp mình.

 

[vodoi2x]

* VBA thì chắc khỏi công thức và name luôn - để các thành viên khác hội bàn

* hoặc Dùng Pivot table cho việc lọc đa điều kiện

* Còn nếu là vẫn dùng giải pháp công thức, thì thử áp dụng các biện pháp sau cho các phần việc lọc:

- Tại sheet kết quả luôn chỉ để 1 dòng công thức đầu tiên thôi, - khi cần báo cáo thì mới kéo công thức xuống các dòng khác hết kết quả cần - sau đó chuyển thành value các dòng này (paste special value từ dòng 2 đến hết)

- không dùng các name động dieu kien nữa, dùng cột phụ như cách LinhNgoc (hay HoaMatTroicoi) làm ở các file trên (cột phụ nằm ở sheet Data sử dụng công thức điều kiện đơn theo dòng thôi) - rồi sheet Ketqua tra theo cot phu nay

- Áp dụng cả 2 biện pháp trên

 

[dongducnhiem]

* VBA thì chắc khỏi công thức và name luôn - để các thành viên khác hội bàn

* hoặc Dùng Pivot table cho việc lọc đa điều kiện

* Còn nếu là vẫn dùng giải pháp công thức, thì thử áp dụng các biện pháp sau cho các phần việc lọc:

- Tại sheet kết quả luôn chỉ để 1 dòng công thức đầu tiên thôi, - khi cần báo cáo thì mới kéo công thức xuống các dòng khác hết kết quả cần - sau đó chuyển thành value các dòng này (paste special value từ dòng 2 đến hết)

- không dùng các name động dieu kien nữa, dùng cột phụ như cách LinhNgoc (hay HoaMatTroicoi) làm ở các file trên (cột phụ nằm ở sheet Data sử dụng công thức điều kiện đơn theo dòng thôi) - rồi sheet Ketqua tra theo cot phu nay

- Áp dụng cả 2 biện pháp trên

Dùng Pivot table cho việc lọc đa điều kiện, mình thấy kg thuận tiện cho một số báo cáo phải theo khuông mẫu lắm.
Vừa rồi tôi có thử nghiệm 1 cách đơn giản và thấy thành công:
Ví dụ ở File bạn gởi nha, ta dùng cách đóng băng các Name động như sau:
Vào Difine Name ở khung Refers to: ta chọn những Name có ảnh hưởng đến Sheet ketqua ta bấm thêm một dấu nháy ' trước dấu bằng ('=congthuc) , như vậy ta đưa nó về dạng text, vì Sheet Ketqua chỉ tham số liệu của các sheet khác, chứ kg ai tham chiếu nó, chỉ khi nào cần thì bỏ nó thôi (khi này Name sẽ tồn tại ở hai dạng: '=congthuc hoặc ="'=congthuc". ở 2 dạng ta bỏ các dấu (màu đỏ) sao chỉ còn =congthuc)---> rồi bấm Add. Như vậy các công thức trên sheet ketqua ta vẫn để nguyên. Chỉ có 1 hạn chế nhỏ khi ta đóng băng nó,vì Name ở dạng text nên lúc chèn thêm dòng nó kg hiểu, ta điều chỉnh công thức có tham chiếu dãy thôi.
Mình đã thực nghiệm trên file thực tế và thấy nó chạy nhanh hơn 90%.
Thân

 

[vodoi2x]

Vừa rồi tôi có thử nghiệm 1 cách đơn giản và thấy thành công:

Mình đã thực nghiệm trên file thực tế và thấy nó chạy nhanh hơn 90%.
Thân

sao không nói 100% luôn bạn, hihiiii, bạn áp dụng kiểu quan niệm xưa cũ thế -không quản lý được thì cấm thì xóa vậy?

Nếu đổi giá trị của NAME vậy, thì chỉ cần đơn giản bạn thay đổi tham chiếu của name DS đi, chỉ 1 dòng thôi chẳng hạn, khi cần thì thay đổi lại tham chiếu đủ dòng của DATA
ví như
name DS =OFFSET(Data!$B$13,,,COUNTA(Data!$B$13:$B$5101),9)

giờ đổi thành
name DS =OFFSET(Data!$B$13,,,COUNTA(Data!$B$13:$B$13),9)

Khi cần báo cáo thì chỉ việc thay đổi lại tham chiếu hợp lý là xong,