Tìm kiếm dữ liệu theo nhiều điều kiện trong một khoảng thời gian

[hellboy2008]

Xin chào mọi người!
Mình có một bảng tính đơn giá rất rất rất phức tạp. Đó là tìm kiếm đơn giá trong một bảng kết hợp 6 điều kiện trong đó 1 điều kiện con có thêm 3 điều kiện nữa. Mình loay hoay cả ngày mà không tìm được cách giải quyết. Mong mỏi mọi người giúp đỡ với.
Xin chân thành cảm ơn mọi người!

 

[dazkangel]

Xin chào mọi người!
Mình có một bảng tính đơn giá rất rất rất phức tạp. Đó là tìm kiếm đơn giá trong một bảng kết hợp 6 điều kiện trong đó 1 điều kiện con có thêm 3 điều kiện nữa. Mình loay hoay cả ngày mà không tìm được cách giải quyết. Mong mỏi mọi người giúp đỡ với.
Xin chân thành cảm ơn mọi người!

Bạn xem file sau:

 

[hellboy2008]

Bạn xem file sau:

Đúng ý mình rồi. Cám ơn bạn nhiều!
Mình có Hàm tìm kiếm ở cột N, nếu gộp chung vào Hàm của bạn thì có được không. Bạn xem dùm mình

 

[batman1]

Thế nếu có 2 giá khác nhau cùng thỏa các điều kiện thì lấy giá nào?

Vì bạn không mô tả dữ liệu nên có câu hỏi: Bạn có dám chắc chắn là sẽ chỉ có nhiều nhất là 1 giá thỏa các điều kiện?

Nhiều khi đặc thù của dữ liệu cho phép có nhiều lựa chọn hàm.

 

[HieuCD]

Xin chào mọi người!
Mình có một bảng tính đơn giá rất rất rất phức tạp. Đó là tìm kiếm đơn giá trong một bảng kết hợp 6 điều kiện trong đó 1 điều kiện con có thêm 3 điều kiện nữa. Mình loay hoay cả ngày mà không tìm được cách giải quyết. Mong mỏi mọi người giúp đỡ với.
Xin chân thành cảm ơn mọi người!

Công thức mảng

=LOOKUP(2,1/($A$16:$A$119<=U16)/($B$16:$B$119>=U16)/($C$16:$C$119=N16)/($G$16:$G$119=O16)/(IF(OR(N16={"LS","NITA","XMK","IN","HT"}),$E$16:$E$119=IF(ISERR(--LEFT(T16)),"Thuy","Bo"),1)/(IF(OR(N16={"IN","HT"}),$D$16:$D$119=Q16,1))),$H$16:$H$119)

Gộp công thức N16

=LOOKUP(2,1/($A$16:$A$119<=U16)/($B$16:$B$119>=U16)/($C$16:$C$119=IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0)))/($G$16:$G$119=O16)/(IF(OR(IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0))={"LS","NITA","XMK","IN","HT"}),$E$16:$E$119=IF(ISERR(--LEFT(T16)),"Thuy","Bo"),1)/(IF(OR(IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0))={"IN","HT"}),$D$16:$D$119=Q16,1))),$H$16:$H$119)

 

[hellboy2008]

Thế nếu có 2 giá khác nhau cùng thỏa các điều kiện thì lấy giá nào?

Vì bạn không mô tả dữ liệu nên có câu hỏi: Bạn có dám chắc chắn là sẽ chỉ có nhiều nhất là 1 giá thỏa các điều kiện?

Nhiều khi đặc thù của dữ liệu cho phép có nhiều lựa chọn hàm.

Thì xét theo ngày, nếu nó nằm trong phạm vi từ ngày nào đến ngày nào thì lấy giá đó. Bạn thử kiểm tra lại dùm, phần bảng giá có ngày áp dụng và ngày kết thúc đó

 

[hellboy2008]

Công thức mảng

=LOOKUP(2,1/($A$16:$A$119<=U16)/($B$16:$B$119>=U16)/($C$16:$C$119=N16)/($G$16:$G$119=O16)/(IF(OR(N16={"LS","NITA","XMK","IN","HT"}),$E$16:$E$119=IF(ISERR(--LEFT(T16)),"Thuy","Bo"),1)/(IF(OR(N16={"IN","HT"}),$D$16:$D$119=Q16,1))),$H$16:$H$119)

Gộp công thức N16

=LOOKUP(2,1/($A$16:$A$119<=U16)/($B$16:$B$119>=U16)/($C$16:$C$119=IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0)))/($G$16:$G$119=O16)/(IF(OR(IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0))={"LS","NITA","XMK","IN","HT"}),$E$16:$E$119=IF(ISERR(--LEFT(T16)),"Thuy","Bo"),1)/(IF(OR(IF(TYPE(MATCH(T16,$J$40:$J$57,0))=1,"HDVC",VLOOKUP(M16,$J$16:$K$39,2,0))={"IN","HT"}),$D$16:$D$119=Q16,1))),$H$16:$H$119)

Bạn thử kiểm tra dùm, khi chay công thức, dòng đâu tiên ra kết quả là 80. NCC: in Mặt Hàng Insee - bao 50kg, Nơi Nhận Cát Lái, lấy hàng đường bộ ngày 5/5. Dò theo bảng thì 81.000 phải không

 

[HieuCD]

Bạn thử kiểm tra dùm, khi chay công thức, dòng đâu tiên ra kết quả là 80. NCC: in Mặt Hàng Insee - bao 50kg, Nơi Nhận Cát Lái, lấy hàng đường bộ ngày 5/5. Dò theo bảng thì 81.000 phải không

Nhập công thức xong, nhấn Ctrl+Shift+Enter

 

[hellboy2008]

Nhập công thức xong, nhấn Ctrl+Shift+Enter

Giờ thì đúng rồi cám ơn bạn

 

[batman1]

Thì xét theo ngày, nếu nó nằm trong phạm vi từ ngày nào đến ngày nào thì lấy giá đó. Bạn thử kiểm tra lại dùm, phần bảng giá có ngày áp dụng và ngày kết thúc đó

Ngày là 1 điều kiện (đk 1). Tôi hỏi là nếu có 2 giá thỏa tất cả các điều kiện, tức gồm cả đk 1 thì lấy giá nào?

Hoặc câu hỏi khác: Liệc có chắc chắn là không sảy ra trường hợp 2 giá cùng thỏa mọi đk hay không.

Bởi nếu luôn chỉ có 1 giá thỏa các điều kiên thì vd. có thể dùng SUMIFS. Bây giờ bạn hiểu tại sao tôi lại hỏi? Vì đơn giản là nhiều khi đặc thù của dữ liệu cho phép có nhiều hơn lựa chọn hàm.

 

[hellboy2008]

Ngày là 1 điều kiện (đk 1). Tôi hỏi là nếu có 2 giá thỏa tất cả các điều kiện, tức gồm cả đk 1 thì lấy giá nào?

Hoặc câu hỏi khác: Liệc có chắc chắn là không sảy ra trường hợp 2 giá cùng thỏa mọi đk hay không.

Bởi nếu luôn chỉ có 1 giá thỏa các điều kiên thì vd. có thể dùng SUMIFS. Bây giờ bạn hiểu tại sao tôi lại hỏi? Vì đơn giản là nhiều khi đặc thù của dữ liệu cho phép có nhiều hơn lựa chọn hàm.

Hiểu ý bạn rồi. Nếu có 2 giá khớp với tất cả điều kiện, nghĩa là mình điền sai dữ liệu. Trường hợp mình không muốn xảy ra. Có cách nào khắc phục không nhỉ?

 

[hellboy2008]

Nhập công thức xong, nhấn Ctrl+Shift+Enter

Mình còn 1 câu hỏi cuối cùng, bạn giup mình nốt nhé. Đó là tính "Chương trình giảm giá" nó cũng tương tự như tính đơn giá mà khác một chút ở cột NCC 2.

 

[batman1]

Hiểu ý bạn rồi. Nếu có 2 giá khớp với tất cả điều kiện, nghĩa là mình điền sai dữ liệu.

Nếu chắc chắn chỉ có 1 giá thỏa mọi điều kiên thì bạn có thể dùng SUMIFS.
Bạn đợi chút tôi sẽ đưa công thức.

 

[batman1]

Công thức về cơ bản chỉ dùng SUMIFS và IF nên dễ hiểu.
Công thức cho S16, kết thúc bằng Enter.

=SUMIFS(H$16:H$119,A$16:A$119,"<=" & U16,B$16:B$119,">=" & U16,C$16:C$119,N16,D$16:D$119,IF(OR(N16="IN",N16="HT"),Q16,"<>="),E$16:E$119,IF(OR(N16={"IN";"HT";"LS";"NITA";"XMK"}),IF(ISERR(--MID(T16,1,2)),"Thuy","Bo"),"<>="),G$16:G$119,O16)

---------------
Trong công thức trên tôi dùng thủ thuật "bỏ điều kiện". Tức vd. Nếu Nếu NCC Gốc = IN, HT thì xét điều kiên 3 (Nơi Nhận có trong cột Nơi Nhận của BẢNG GIÁ), ngược lại thì không xét điều kiên 3, tức "bỏ điều kiện" cho cột Nơi Nhận (cột D).

Nếu điều kiện là "<>=" thì mọi ô trong vùng bất kỳ đều thỏa. Tức cũng đồng nghĩa với việc không áp đặt điều kiện cho vùng đó.

Ứng dụng: Giả sử bạn có cột A là "Cửa hàng" (A, B, C, ...), cột B là "Loại xe" (Honda, BMW, ...), cột C là "Số lượng". Trong E1 bạn có Data Validation (A, B, C, ...), F1 có data Validation (Honda, BMW, ...), trong G1 bạn nhập điều kiện về số lượng, vd. <5, >=10, 0

Yêu cầu: Nếu chỉ có E1 còn F1, G1 trống thì đếm số dòng có "Cửa hàng" bằng E1. Nếu có 2, 3 ô có dữ liệu trong E1:G1 thì đếm theo 2, 3 điều kiện. Nếu cả 3 ô E1:F1 trống thì đếm tất cả các dòng.

Lúc đó thì công thức là

=COUNTIFS(A2:A100,IF(E1<>"",E1,"<>="),B2:B100,IF(F1<>"",F1,"<>="),C2:C100,IF(G1<>"",G1,"<>="))

"<>=" bạn có thể dùng với SUMIFS, COUNTIFS, AVERAGEIFS

 

[HieuCD]

Mình còn 1 câu hỏi cuối cùng, bạn giup mình nốt nhé. Đó là tính "Chương trình giảm giá" nó cũng tương tự như tính đơn giá mà khác một chút ở cột NCC 2.

=IFERROR(LOOKUP(2,1/($Z$16:$Z$23<=W16)/($AA$16:$AA$23>=W16)/($AB$16:$AB$23=X16)/($AF$16:$AF$23=P16)/(IF(IF(TYPE(MATCH(V16,$J$40:$J$57,0))=1,"HDVC",IF(VLOOKUP(M16,$J$16:$K$39,2,0)<>"HDVC",M16,VLOOKUP(M16,$J$16:$K$39,2,0)))<>"HDVC",$AE$16:$AE$23=IF(ISERR(--LEFT(V16)),"Thuy","Bo"),1)/(IF(OR(IF(TYPE(MATCH(V16,$J$40:$J$57,0))=1,"HDVC",IF(VLOOKUP(M16,$J$16:$K$39,2,0)<>"HDVC",M16,VLOOKUP(M16,$J$16:$K$39,2,0)))={"IN","HT","KM"}),$AD$16:$AD$23=R16,1))),$AG$16:$AG$23),"")

Ctrl+Shift+Enter

 

[hellboy2008]

Cám ơn dazkangel, HieuCD và batman1 nhé. Nhớ mọi người mà mình có thêm giải pháp và biết thêm hàm mới. Giờ mới biết cái bỏ điều kiện này "<>="